[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

 

[ptm0412]

Đã làm theo ý trên: Data gốc, tách ra, unpivot, và gộp lại

 

[Binbo2020]

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

Không công việc của mình không liên quan nhiều đến excel toàn tìm kiếm với tính tổng là nhiều, chẳng mấy khi động đến hàm. Vì thích nên hay lên mạng tìm hiểu cho biết thôi, chẳng học bài bản bao giờ toàn thầy Google dạy nên nhiều cái cơ bản có khi không biết, thấy có gì mới, hay mà không biết là google thôi.

 

[Nhattanktnn]

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ày của thầy Mỹ cũng rất là hay, lúc đầu em không biết pivot làm được không nên em đăng hỏi trước, còn giờ biết query có khả năng làm điều này thì xuất báo cáo ra như vậy cũng rất là hay. Nhưng thầy cho em hỏi, chỗ bảng query đó là mình lấy dữ liệu từ 1 bảng gốc, hay phải thủ công tách bảng gốc ra 3 bảng nhỏ sơn bột, nước, ed mới xử lý được?

 

[ptm0412]

Ý này của thầy Mỹ cũng rất là hay, lúc đầu em không biết pivot làm được không nên em đăng hỏi trước, còn giờ biết query có khả năng làm điều này thì xuất báo cáo ra như vậy cũng rất là hay. Nhưng thầy cho em hỏi, chỗ bảng query đó là mình lấy dữ liệu từ 1 bảng gốc, hay phải thủ công tách bảng gốc ra 3 bảng nhỏ sơn bột, nước, ed mới xử lý được?

Bạn lấy file bài 21: bảng gốc tách ra sau đó mới merge
Lưu ý là khi sử dụng unpivot thì bảng gốc chỉ được thêm dòng, không được thêm cột. Thêm cột là phải sửa query thậm chí phải viết query khác

 

[Nhattanktnn]

Bạn lấy file bài 21: bảng gốc tách ra sau đó mới merge
Lưu ý là khi sử dụng unpivot thì bảng gốc chỉ được thêm dòng, không được thêm cột. Thêm cột là phải sửa query thậm chí phải viết query khác

Ý của thầy chắc em đã nắm bắt được, tức là:
Đưa dữ liệu gốc vào Power query, dùng query tách 3 bảng sơn ed, sơn bột, còn sơn nước đảo cột thành dòng.
Từ 3 bảng này merge lại thành một bảng duy nhất theo dòng
Rồi sau đó dùng pivot tạo báo cáo
Hướng làm về cơ bản em nắm bắt được là vậy, còn cách làm em còn mông lung lắm
Em sẽ nghiên cứu thêm phần này, cảm ơn thầy đã chỉ dẫn và hỗ trợ

 

[ptm0412]

Đưa dữ liệu gốc vào Power query, dùng query tách 3 bảng sơn ed, sơn bột, còn sơn nước đảo cột thành dòng.

Cả 3 bảng con đều phải đảo cột thành dòng, nhưng sơn nước phức tạp nhất: đảo xong còn phải tạo cột duy nhất chứa mã chứ không thì đang là nhiều cột mã không chuyển thành dòng được

 

[Lê Duy Thương]

Cả 3 bảng con đều phải đảo cột thành dòng, nhưng sơn nước phức tạp nhất: đảo xong còn phải tạo cột duy nhất chứa mã chứ không thì đang là nhiều cột mã không chuyển thành dòng được

Nếu vất vả thế sao cứ phải queries làm chi vậy sư phụ. có thể dùng VBA để sếp dữ liệu cho data của pivot mà.

 

[ptm0412]

Nếu vất vả thế sao cứ phải queries làm chi vậy sư phụ. có thể dùng VBA để sếp dữ liệu cho data của pivot mà.

VBA là ưu tiên chót nhá

 

[Nhattanktnn]

VBA là ưu tiên chót nhá

Thầy Mỹ cho em hỏi đoạn này với, làm sao để nó hiểu code-1746,code-1747,code-1748 với tên là "mã" vậy ạ?

 

[ptm0412]

Thầy Mỹ cho em hỏi đoạn này với, làm sao để nó hiểu code-1746,code-1747,code-1748 với tên là "mã" vậy ạ?

Tên 2 cột mới sau khi unpivot bằng tay mặc định là "Attribute" và "Value", mình sửa mặc định trong câu lệnh luôn. Bạn chưa quen thì cứ làm tay (chọn cột cần chuyển, nhấn nút unpivot), rồi sửa tên cột

 

[Nhattanktnn]

Tên 2 cột mới sau khi unpivot bằng tay mặc định là "Attribute" và "Value", mình sửa mặc định trong câu lệnh luôn. Bạn chưa quen thì cứ làm tay (chọn cột cần chuyển, nhấn nút unpivot), rồi sửa tên cột

Thầy cho em hỏi, nếu dùng lệnh:

let
    Source = Table.Combine({#"Sơn ED", #"Sơn bột", #"Sơn nước"}),
    #"Filtered Rows" = Table.SelectRows(Source, each [Mã] <> null and [Mã] <> "")
in
    #"Filtered Rows"

Thì khi mình muốn khác null và khác rỗng trực tiếp từ lệnh table.combine thì có được không?
Kiểu như là vậy thầy, nhưng mà chắc không đúng cú pháp hay sao đó mà ra lỗi:

let
    Source = Table.Combine({#"Sơn ED", #"Sơn bột", #"Sơn nước"},each [Mã] <> null and [Mã] <> "")
in
    Source

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

Hiện tại em đang sử dụng code này, cho ra kết quả đúng nhưng không biết có rút ngắn theo ý tưởng trên được không

let
    Source = Table.Combine({#"Sơn ED", #"Sơn bột", #"Sơn nước"}),
    #"Filtered Rows" = Table.SelectRows(Source, each [Mã] <> null and [Mã] <> ""),
    #"Removed Blank Rows" = Table.SelectRows(#"Filtered Rows", each not List.IsEmpty(List.RemoveMatchingItems(Record.FieldValues(_), {"", null})))
in
    #"Removed Blank Rows"

 

[ptm0412]

Kiểu như là vậy thầy, nhưng mà chắc không đúng cú pháp hay sao đó mà ra lỗi:

Sai cú pháp thật. Thêm 1 step filter đi (Table.SelectRows)
Mà lạ, tôi chạy dữ liệu mẫu không thấy mã bị rỗng?

 

[Nhattanktnn]

Mà lạ, tôi chạy dữ liệu mẫu không thấy mã bị rỗng?

Dạ đó là mẫu em rút ra còn rất ít dữ liệu, vì sơn nước có mã 4 loại sơn, mã 3 loại, nên loại nào 3 loại nó ra null hoặc blanks. Thực tế em vẫn xử lý được (như phần merge của bài #31), nhưng em muốn hỏi xem có thể ngắn gọn hơn không

 

[ptm0412]

Dạ đó là mẫu em rút ra còn rất ít dữ liệu, vì sơn nước có mã 4 loại sơn, mã 3 loại, nên loại nào 3 loại nó ra null hoặc blanks. Thực tế em vẫn xử lý được (như phần merge của bài #31), nhưng em muốn hỏi xem có thể ngắn gọn hơn không

Hình như câu #"Removed Blank Rows" dư, và <> null với <> "" chỉ cần một

 

[Nhattanktnn]

.

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

Hình như câu #"Removed Blank Rows" dư, và <> null với <> "" chỉ cần một

Có lẽ như này là ngắn gọn nhất em có thể làm:

let
    Source = Table.Combine({#"Sơn ED", #"Sơn bột", #"Sơn nước"}),
    #"Removed Blank Rows" = Table.SelectRows(Source,each not List.IsEmpty(List.RemoveMatchingItems({[Mã]}, {null})))
in
    #"Removed Blank Rows"