Khi tôi tổ chức hội thảo Power Excel trực tiếp, tôi đề nghị rằng nếu bất kỳ ai trong phòng gặp vấn đề kỳ lạ về Excel, họ có thể gửi cho tôi để được giúp đỡ. Đó là cách tôi nhận được sự cố làm sạch dữ liệu này. Ai đó đã có một bảng tính tóm tắt giống như sau:
Họ muốn định dạng lại dữ liệu để trông giống như sau:
Một manh mối thú vị về dữ liệu này: 18 trong G4 dường như là tổng phụ của H4: K4. Rất hấp dẫn để loại bỏ các cột G, L, v.v. nhưng trước tiên bạn phải trích xuất tên nhân viên từ G3, L3, v.v.
Lúc đó là 4 giờ sáng Chủ nhật ngày 9 tháng 2 khi tôi bật trình ghi video và ghi lại một số bước khó hiểu trong Power Query để giải quyết vấn đề. Cho rằng đó là ngày Chủ nhật, một ngày mà tôi không thường làm video, tôi đã yêu cầu mọi người gửi ý tưởng của họ về cách giải quyết vấn đề. 29 giải pháp đã được gửi đến.
Mỗi giải pháp cung cấp một số cải tiến mới thú vị trong quy trình của tôi. Kế hoạch của tôi là bắt đầu một loạt các bài báo cho thấy những cải tiến khác nhau đối với phương pháp của tôi.
Xem video
Trước khi tôi bắt đầu quá trình đó, tôi mời bạn xem giải pháp của tôi:
Và mã M mà Power Query đã tạo cho tôi:
let Source = Excel.CurrentWorkbook()((Name="UglyData"))(Content), #"Promoted Headers" = Table.PromoteHeaders(Source, (PromoteAllScalars=true)), #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",(("Category Description", type text), ("Dept. Total", type number), ("Q1", type number), ("Q2", type number), ("Q3", type number), ("Q4", Int64.Type), ("Employee 1", type number), ("Q1_1", type number), ("Q2_2", type number), ("Q3_3", Int64.Type), ("Q4_4", Int64.Type), ("Employee 2", Int64.Type), ("Q1_5", Int64.Type), ("Q2_6", Int64.Type), ("Q3_7", Int64.Type), ("Q4_8", Int64.Type), ("Employee 3", Int64.Type), ("Q1_9", Int64.Type), ("Q2_10", Int64.Type), ("Q3_11", Int64.Type), ("Q4_12", Int64.Type), ("Employee 4", type number), ("Q1_13", type number), ("Q2_14", type number), ("Q3_15", type number), ("Q4_16", Int64.Type))), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", ("Category Description"), "Attribute", "Value"), #"Split Column by Delimiter" = Table.SplitColumn(#"Unpivoted Other Columns", "Attribute", Splitter.SplitTextByEachDelimiter(("_"), QuoteStyle.Csv, false), ("Attribute.1", "Attribute.2")), #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",(("Attribute.1", type text), ("Attribute.2", Int64.Type))), #"Removed Columns" = Table.RemoveColumns(#"Changed Type1",("Attribute.2")), #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",(("Attribute.1", "TextValue"))), #"Added Custom" = Table.AddColumn(#"Renamed Columns", "Length", each Text.Length((TextValue))), #"Added Conditional Column" = Table.AddColumn(#"Added Custom", "Name", each if (Length)> 2 then (TextValue) else null), #"Filled Down" = Table.FillDown(#"Added Conditional Column",("Name")), #"Reordered Columns" = Table.ReorderColumns(#"Filled Down",("Category Description", "Name", "TextValue", "Value", "Length")), #"Filtered Rows" = Table.SelectRows(#"Reordered Columns", each ((TextValue) = "Q1" or (TextValue) = "Q2" or (TextValue) = "Q3" or (TextValue) = "Q4")), #"Filtered Rows1" = Table.SelectRows(#"Filtered Rows", each (Name) "Dept. Total"), #"Removed Columns1" = Table.RemoveColumns(#"Filtered Rows1",("Length")), #"Pivoted Column" = Table.Pivot(#"Removed Columns1", List.Distinct(#"Removed Columns1"(TextValue)), "TextValue", "Value", List.Sum), #"Sorted Rows" = Table.Sort(#"Pivoted Column",(("Name", Order.Ascending))), #"Added Custom1" = Table.AddColumn(#"Sorted Rows", "Total", each (Q1)+(Q2)+(Q3)+(Q4)) in #"Added Custom1"Trước khi bắt đầu đi vào các giải pháp, hãy giải quyết rất nhiều nhận xét chung:
- Một số bạn nói rằng bạn sẽ quay ngược lại để tìm ra lý do tại sao dữ liệu lại hiển thị ở định dạng này ngay từ đầu. Tôi đánh giá cao những ý kiến này. Tất cả những người đã nói đây là một người tốt hơn tôi. Qua nhiều năm tôi đã học được điều đó khi bạn hỏi "Tại sao?" câu trả lời thường liên quan đến nhân viên cũ này, người đã bắt đầu con đường này cách đây 17 năm và mọi người tiếp tục sử dụng nó theo cách này vì tất cả chúng ta đã quen với nó bây giờ.
- Ngoài ra - nhiều bạn - cho biết giải pháp cuối cùng nên là một chiếc bàn thẳng đứng cao và sau đó sử dụng bảng xoay để đưa ra kết quả cuối cùng. Jonathan Cooper đã tóm tắt điều này hay nhất: "Tôi cũng đồng ý với một số nhận xét khác trên YouTube rằng một tập dữ liệu phù hợp sẽ không có" Tổng "và không cần phải xoay vòng ở cuối. Nhưng nếu người dùng thực sự muốn bàn cũ thì bạn cho họ thứ họ muốn. " Tôi thực sự có thể thấy cả hai mặt của điều này. Tôi thích một bảng xoay và điều duy nhất thú vị hơn Power Query là Power Query với một bảng xoay đẹp mắt ở trên cùng. Nhưng nếu chúng ta có thể thực hiện toàn bộ mọi thứ trong Power Query, thì bạn sẽ bớt được một điều gì đó.
Đây là các siêu liên kết đến các kỹ thuật khác nhau
-
Kỹ thuật truy vấn nguồn
- Đánh số nhóm hồ sơ
- Trích xuất hai ký tự còn lại
- Tổng số cột
- Khác nếu điều khoản
- Nhiều tiêu đề giống hệt nhau trong Power Query
- Những gì cần xóa
- Chia theo Q
- Sắp xếp Mục hàng
- Giải pháp truy vấn Power từ Excel MVPs
-
Di chuyển ra ngoài giao diện truy vấn nguồn
- Table.Split
- Thế giới của Bill Szysz
-
Giải pháp công thức
- Một công thức mảng động
- Cột trợ giúp trường cũ
- Giải pháp công thức
-
Tổng hợp tất cả các Ý tưởng từ Video trên và Video cuối cùng
- Tổng hợp các ý tưởng tốt nhất từ tất cả
