Giải pháp tổng hợp cho thử thách Podcast 2316 - Mẹo Excel

Ghi chú

Đây là một trong loạt bài viết chi tiết các giải pháp được gửi đến cho thử thách Podcast 2316.

Sau khi nghiên cứu tất cả các ý tưởng được gửi từ người xem, tôi đã chọn kỹ thuật yêu thích của mình từ mỗi video. Giải pháp cuối cùng của tôi sử dụng các bước sau:

• Nhận dữ liệu, từ phạm vi được đặt tên
• Xóa hai bước bổ sung đã thêm vào Quảng cáo Tiêu đề và Thay đổi Loại. Điều này tránh phải ngắt hậu tố khỏi các phần tư. Cảm ơn Jason M, Ondřej Malinský và Peter Bartholomew về ý tưởng này.
• Transpose
• Quảng cáo tiêu đề
• Xóa, Hàng trên cùng, 5 Hàng trên cùng. Mẹo hay từ MF Wong.
• Thay Q1 bằng _Q1. Lặp lại trong ba phần tư khác. Cảm ơn Jonathan Cooper.
• Phân tách bằng Dấu phân cách tại dấu _. Bước tuyệt vời này giữ tên trong một cột và chuyển các phần tư sang cột tiếp theo. Do Fowmy đề xuất, Jonathan Cooper hoàn thiện.
• (Không phải là một bước!) Tiếp cận Thanh Công thức và đổi tên các cột thành Nhân viên và Quý. Cảm ơn Josh Johnson
• Trong cột Nhân viên, không thay thế gì bằng null
• Điền vào
• Trong cột Quý, thay đổi null thành Tổng. Ý tưởng này từ Michael Karpfen
• Bỏ chia các cột khác. Đổi tên thuộc tính thành danh mục trong thanh công thức
• Pivot Quarters
• Di chuyển tổng số cột đến cuối

Đây là mã cuối cùng của tôi:

let Source = Excel.CurrentWorkbook()((Name="UglyData"))(Content), #"Transposed Table" = Table.Transpose(Source), #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", (PromoteAllScalars=true)), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",(("Category Description", type text), ("Administrative", type number), ("Holiday", Int64.Type), ("PTO/LOA/Jury Duty", Int64.Type), ("Project A", type number), ("Project B", type number), ("Project C", type number))), #"Removed Top Rows" = Table.Skip(#"Changed Type",5), #"Replaced Value" = Table.ReplaceValue(#"Removed Top Rows","Q1","_Q1",Replacer.ReplaceText,("Category Description")), #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Q2","_Q2",Replacer.ReplaceText,("Category Description")), #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1","Q3","_Q3",Replacer.ReplaceText,("Category Description")), #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2","Q4","_Q4",Replacer.ReplaceText,("Category Description")), #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value3", "Category Description", Splitter.SplitTextByDelimiter("_", QuoteStyle.Csv), ("Employee", "Qtr")), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",(("Employee", type text), ("Qtr", type text))), #"Replaced Value4" = Table.ReplaceValue(#"Changed Type1","",null,Replacer.ReplaceValue,("Employee")), #"Filled Down" = Table.FillDown(#"Replaced Value4",("Employee")), #"Replaced Value5" = Table.ReplaceValue(#"Filled Down",null,"Total",Replacer.ReplaceValue,("Qtr")), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Replaced Value5", ("Employee", "Qtr"), "Attribute", "Value"), #"Pivoted Column" = Table.Pivot(#"Unpivoted Other Columns", List.Distinct(#"Unpivoted Other Columns"(Qtr)), "Qtr", "Value", List.Sum), #"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",("Employee", "Attribute", "Q1", "Q2", "Q3", "Q4", "Total")) in #"Reordered Columns"

Mọi người được đề cập trong các bài viết hoặc video này đều thắng bản vá lỗi Excel Guru. Tôi đã gửi một số thư. Nếu không nhận được, hãy để lại bình luận ở video bên dưới.