[Pivot table] Chuyển dữ liệu từ cột thành dòng (1 người xem)

[Nhattanktnn]

Chào các bác, anh chị và các bạn!
Làm việc lâu lâu đụng tới pivot mà cái này chưa gặp bao giờ nên cũng không biết pivot có làm được không, mong mọi người xem giúp!
Mình muốn chuyển nhiều cột thành một dòng như file, loay hoay thử thì không được. Nếu pivot không có khả năng thì power pivot có thể không? Mình đang dùng office 2010 nên cái power pivot của nó cũng bị hạn chế một số hàm.
Mong mọi người giúp đỡ (Mình không muốn chuyển hướng sang VBA hoặc hàm nhé, đại loại là sử dụng công cụ làm báo cáo)

 

[excel_lv1.5]

Chào các bác, anh chị và các bạn!
Làm việc lâu lâu đụng tới pivot mà cái này chưa gặp bao giờ nên cũng không biết pivot có làm được không, mong mọi người xem giúp!
Mình muốn chuyển nhiều cột thành một dòng như file, loay hoay thử thì không được. Nếu pivot không có khả năng thì power pivot có thể không? Mình đang dùng office 2010 nên cái power pivot của nó cũng bị hạn chế một số hàm.
Mong mọi người giúp đỡ (Mình không muốn chuyển hướng sang VBA hoặc hàm nhé, đại loại là sử dụng công cụ làm báo cáo)

Cái này chỉ là chuẩn hóa số liệu thôi, dùng Power Query là được!

 

[Nhattanktnn]

Cái này chỉ là chuẩn hóa số liệu thôi, dùng Power Query là được!

Power Query em có tải về mà chưa dùng thử, cũng chưa biết dùng sao luôn tại công việc ít sử dụng, bác có thể hỗ trợ em cái file đó và chỉ em mấy đường cơ bản em thử xem được không?

 

[excel_lv1.5]

Power Query em có tải về mà chưa dùng thử, cũng chưa biết dùng sao luôn tại công việc ít sử dụng, bác có thể hỗ trợ em cái file đó và chỉ em mấy đường cơ bản em thử xem được không?

Bạn nhận file trước

 

[Nhattanktnn]

Bạn nhận file trước

Hình như vẫn giống file ban đầu em gửi, em có thấy hiện lên Design hay gì đâu bác ơi

 

[excel_lv1.5]

Hình như vẫn giống file ban đầu em gửi, em có thấy hiện lên Design hay gì đâu bác ơi

Bạn nhận lại file, xem cách làm ở video này:

 

[Nhattanktnn]

Bạn nhận lại file, xem cách làm ở video này:

Cảm ơn bác đã nhiệt tình giúp đỡ, để em về nhà xem video có gì không hiểu em hỏi lại bác sau.
Hiện tại ở công ty họ khóa youtube nên không xem được (Power query đúng em chưa biết gì luôn)

 

[Binbo2020]

Bạn nhận lại file, xem cách làm ở video này:

Hoa mắt quá chưa biết cái này, cho mình hỏi muốn đọc tài liệu nghiên cứu thử thì lên mạng tìm từ khóa là gì nhỉ

 

[VetMini]

Chuyển cột thành dòng theo tiếng nghề gọi là UN-Pivot. Tức là ngược lại của Pivot (Crosstab)

Excel 2019 giải quyết vụ này qua Power Query. 2013, 2016 có thể tải về công cụ này để làm việc. 2010 thì không chắc.

 

[xuongrongdat]

Hoa mắt quá chưa biết cái này, cho mình hỏi muốn đọc tài liệu nghiên cứu thử thì lên mạng tìm từ khóa là gì nhỉ

Ủa chớ không phải diễn đàn mình có tài liệu này rồi sao?!!

Quà tặng từ Smod Ptm0412 - Tài liệu về PowerPivot nhân sinh nhật GPE13

Tài liệu về Pivot Table, PowerPivot, Power Query (được chia sẻ miễn phí bởi smod ptm0412) Thân gởi mọi người, Trong buổi tiệc sinh nhật GPE13 tại TPHCM, anh Mỹ Smod @ptm0412 đã có chia sẻ về những điểm độc đáo trong PowerPivot (và PowerQuery). Cùng với đó là tài liệu liên quan đến PowerPivot...

www.giaiphapexcel.com

 

[excel_lv1.5]

Hoa mắt quá chưa biết cái này, cho mình hỏi muốn đọc tài liệu nghiên cứu thử thì lên mạng tìm từ khóa là gì nhỉ

Trên đầu trang có tài liệu của thầy Ptm đấy bạn!

 

[Binbo2020]

Ủa chớ không phải diễn đàn mình có tài liệu này rồi sao?!!

Trên đầu trang có tài liệu của thầy Ptm đấy bạn!

Hix nhìn thấy suốt ngày mà không biết nó là cái gì cứ nghĩ kiểu kết hợp với VBA mà VBA thì mình mù tịt nên không lưu vào bộ nhớ

 

[hocexcel_1991]

Bạn nhận lại file, xem cách làm ở video này:

Mình thấy bạn sử dụng các hàm M trong Power Query hay quá, mình đọc mấy hàm đó không hiểu cách dùng thế nào (trừ hàm IF ). Chỉ biết 1ít thao tác cơ bản.
Chỗ phần xử lý ở Query1 mình dùng thao tác bằng tay Unpivot => hàm if => FillUp => Fillter... mình thấy kết quả đúng. Không biết có hạn chế gì không khi dữ liệu phát sinh
Code

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type number}, {"Column4", type number}, {"Column5", type number}, {"Column6", type text}, {"Column7", type number}, {"Column8", type text}, {"Column9", type number}, {"Column10", type text}, {"Column11", type number}, {"Column12", type text}, {"Column13", type number}, {"Column14", type text}, {"Column15", type number}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column3", "Column4", "Column5","Column6","Column7"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Column1", "Column2"}, "Attribute", "Value"),
    #"Removed Columns1" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns1", "Custom", each [Value]+0),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Added Custom", {{"Custom", null}}),
    #"Added Custom1" = Table.AddColumn(#"Replaced Errors", "Custom.1", each if[Custom] = null then 1 else null),
    #"Filled Up" = Table.FillUp(#"Added Custom1",{"Custom"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([Custom.1] = 1)),
    #"Removed Columns2" = Table.RemoveColumns(#"Filtered Rows",{"Custom.1"})
in
    #"Removed Columns2"

 

[excel_lv1.5]

Mình thấy bạn sử dụng các hàm M trong Power Query hay quá, mình đọc mấy hàm đó không hiểu cách dùng thế nào (trừ hàm IF ). Chỉ biết 1ít thao tác cơ bản.
Chỗ phần xử lý ở Query1 mình dùng thao tác bằng tay Unpivot => hàm if => FillUp => Fillter... mình thấy kết quả đúng. Không biết có hạn chế gì không khi dữ liệu phát sinh
Code

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type number}, {"Column4", type number}, {"Column5", type number}, {"Column6", type text}, {"Column7", type number}, {"Column8", type text}, {"Column9", type number}, {"Column10", type text}, {"Column11", type number}, {"Column12", type text}, {"Column13", type number}, {"Column14", type text}, {"Column15", type number}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column3", "Column4", "Column5","Column6","Column7"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Column1", "Column2"}, "Attribute", "Value"),
    #"Removed Columns1" = Table.RemoveColumns(#"Unpivoted Other Columns",{"Attribute"}),
    #"Added Custom" = Table.AddColumn(#"Removed Columns1", "Custom", each [Value]+0),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Added Custom", {{"Custom", null}}),
    #"Added Custom1" = Table.AddColumn(#"Replaced Errors", "Custom.1", each if[Custom] = null then 1 else null),
    #"Filled Up" = Table.FillUp(#"Added Custom1",{"Custom"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Up", each ([Custom.1] = 1)),
    #"Removed Columns2" = Table.RemoveColumns(#"Filtered Rows",{"Custom.1"})
in
    #"Removed Columns2"

Được đấy bạn có nhiều cách làm cái query1, nhìn code lằng nhằng nó vậy chứ thực tế như là kiểu đặt Name trong excel, mảng 2 chiều, mảng 1 chiều, .... có thể xem nó là kết hợp của hàm và VBA, nhưng nó mạnh ở chỗ tất cả các hàm nó đều dùng xử lý mảng mà hàm và VBA không có (tốc độ thì chắc không bằng VBA vì chức năng chủ yếu của nó là Clean, design data...), nên gần như không cần dùng loop ... biết phối hợp hàm là được, mà hàm của nó thì quá nhiều, cho bạn thêm một cách giống kiểu Index trong excel

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Column2", type text}, {"Column3", type number}, {"Column4", type number}, {"Column5", type number}, {"Column6", type text}, {"Column7", type number}, {"Column8", type text}, {"Column9", type number}, {"Column10", type text}, {"Column11", type number}, {"Column12", type text}, {"Column13", type number}, {"Column14", type text}, {"Column15", type number}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type",{"Column3", "Column4", "Column5","Column6","Column7"}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Removed Columns", {"Column1", "Column2"}, "Attribute", "Value"),
    #"Added Index" = Table.AddIndexColumn(#"Unpivoted Other Columns", "Index", 0, 1, Int64.Type),
    #"Added Custom" = Table.AddColumn(#"Added Index", "Custom", each let
Lst=#"Added Index"[Value]
in
if Number.IsEven([Index]) then Lst{[Index]+1} else 0),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Custom] <> 0)),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows",{"Column1", "Column2", "Value", "Custom"})
in
    #"Removed Other Columns"

Có thể thay đoạn #"Filtered Rows" thành
#"Filtered Rows" = Table.SelectRows(#"Added Custom", each (Value.Type([Value])=Text.Type)),

 

[VetMini]

... (tốc độ thì chắc không bằng VBA vì chức năng chủ yếu của nó là Clean, design data...),...

Chưa chắc. Điểm mượt (sweet spot) của nó là số lượng dữ liệu.
Power Query dựa trên kinh nghiệm của MS với SQL Server cho nên lượng dữ liệu cao thì nó càng hiệu quả.

 

[phuongvq123]

Hix nhìn thấy suốt ngày mà không biết nó là cái gì cứ nghĩ kiểu kết hợp với VBA mà VBA thì mình mù tịt nên không lưu vào bộ nhớ

Bạn không hay sử dụng power query à

 

[excel_lv1.5]

Chưa chắc. Điểm mượt (sweet spot) của nó là số lượng dữ liệu.
Power Query dựa trên kinh nghiệm của MS với SQL Server cho nên lượng dữ liệu cao thì nó càng hiệu quả.

Có lẽ là như vậy, nếu mà xử lý trên Column và Table thì tốc độ rất nhanh, nhưng mà có xử lý trên Row hay cells thì rất chậm
Tôi viết hàm countifS đếm 12 điều kiện cho 1 triệu dòng thì Power query load không nổi (chắc do code cùi), excel cũng vậy, VBA Dic trên 5p,
Nếu dùng Group xét column trong PQ thì chỉ mất 8s, trong Modeling+Dax khoảng 4s (Data Modeling đã loại bỏ Row và cells)

Bài đã được tự động gộp: 26/11/20

 

[ptm0412]

Nếu tôi là người lập báo cáo, và dùng Power query thì tôi làm thế này, tính tổng theo loại dễ dàng



Nếu bị ép làm kiểu của tác giả, tôi làm ăn gian thế này, rồi dấu dòng, thêm tiêu đề gì đó cho giống



Ghi chú: Code M của query tôi làm đơn giản như người mới học, không tự viết kiểu như @excel_lv1.5

 

[excel_lv1.5]

Nếu tôi là người lập báo cáo, và dùng Power query thì tôi làm thế này, tính tổng theo loại dễ dàng

View attachment 250075View attachment 250078

Nếu bị ép làm kiểu của tác giả, tôi làm ăn gian thế này, rồi dấu dòng, thêm tiêu đề gì đó cho giống

View attachment 250076

Ghi chú: Code M của query tôi làm đơn giản như người mới học, không tự viết kiểu như @excel_lv1.5

Chủ thread yêu cầu làm sơn nước nên tôi làm phần đó thôi, dữ liệu đúng ra là nên thiết kế như bạn nói, tuy nhiên nếu dữ liệu gốc là là bảng dài bao gồm tất cả các loại sơn tôi nghĩ bạn nên nạp bảng đó vào power query rồi thiết kế lại, chứ không nên tạo 3 bảng ngoài worksheet rồi mới nạp vào PQ sẽ mất thời gian chỉnh sữa mỗi lần có dữ liệu mới

 

[ptm0412]

Chủ thread yêu cầu làm sơn nước nên tôi làm phần đó thôi, dữ liệu đúng ra là nên thiết kế như bạn nói, tuy nhiên nếu dữ liệu gốc là là bảng dài bao gồm tất cả các loại sơn tôi nghĩ bạn nên nạp bảng đó vào power query rồi thiết kế lại, chứ không nên tạo 3 bảng ngoài worksheet rồi mới nạp vào PQ sẽ mất thời gian chỉnh sữa mỗi lần có dữ liệu mới

Đúng như bạn nói:
- Đầu bài viết tôi ghi nếu tôi là người báo cáo, chứ không bị ép như tác giả
- Tôi đang làm kiểu bạn nói là 1 bảng dài (nhiều cột như sheet gốc), Select Columns 3 lần ra 3 bảng con, rồi bắt đầu xử lý như file cũ: unpivot từng bảng con, rồi merge lại