Tantangan Bill tentang "Bagaimana Anda Membersihkan Data Ini" - Tip Excel

Daftar Isi

Ketika saya melakukan seminar Power Excel secara langsung, saya menawarkan bahwa jika ada orang di ruangan itu yang memiliki masalah Excel yang aneh, mereka dapat mengirimkannya kepada saya untuk meminta bantuan. Begitulah cara saya menerima masalah pembersihan data ini. Seseorang memiliki lembar kerja ringkasan yang terlihat seperti ini:

Lembar kerja ringkasan

Mereka ingin memformat ulang data agar terlihat seperti ini:

Data diformat ulang yang diinginkan

Satu petunjuk menarik tentang data ini: Angka 18 di G4 tampaknya merupakan subtotal dari H4: K4. Sangat menggoda untuk menghapus kolom G, L, dan seterusnya, tetapi pertama-tama Anda harus mengekstrak nama karyawan dari G3, L3, dan seterusnya.

Saat itu jam 4 pagi pada hari Minggu tanggal 9 Februari ketika saya menyalakan perekam video dan merekam beberapa langkah kikuk di Power Query untuk menyelesaikan masalah. Mengingat saat itu hari Minggu, hari di mana saya biasanya tidak membuat video, saya meminta orang untuk mengirimkan ide mereka tentang bagaimana menyelesaikan masalah. 29 solusi telah dikirim.

Setiap solusi menawarkan beberapa peningkatan baru yang keren atas proses saya. Rencana saya adalah memulai serangkaian artikel yang menunjukkan berbagai perbaikan pada metode saya.

Menonton video

Sebelum saya memulai proses itu, saya mengundang Anda untuk melihat solusi saya:

Dan kode M yang dihasilkan Power Query untuk saya:

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"

Sebelum kita mulai membahas solusinya, mari kita bahas banyak komentar umum:

  • Beberapa dari Anda mengatakan Anda akan mundur untuk mencari tahu mengapa data muncul dalam format ini untuk memulai. Saya menghargai komentar ini. Setiap orang yang mengatakan ini adalah orang yang lebih baik dari saya. Saya telah belajar selama bertahun-tahun bahwa ketika Anda bertanya "Mengapa?" jawabannya biasanya melibatkan mantan karyawan ini yang memulai jalur ini 17 tahun yang lalu dan semua orang terus menggunakannya karena kita semua terbiasa sekarang.
  • Juga - banyak dari Anda - mengatakan solusi akhir harus berupa tabel vertikal yang tinggi dan kemudian gunakan tabel pivot untuk menghasilkan hasil akhir. Jonathan Cooper meringkas ini dengan sangat baik: "Saya juga setuju dengan beberapa komentar YouTube lainnya bahwa kumpulan data yang tepat tidak akan memiliki" Total "dan tidak perlu diputar di bagian akhir. Tetapi jika pengguna benar-benar menginginkan meja tua maka Anda memberi mereka apa yang mereka inginkan. " Saya benar-benar bisa melihat kedua sisi ini. Saya suka tabel pivot dan satu-satunya hal yang lebih menyenangkan daripada Power Query adalah Power Query dengan tabel pivot yang bagus di atasnya. Tapi jika kita bisa melakukan semuanya di Power Query, maka satu hal yang harus dihentikan.

Berikut adalah Hyperlink ke Berbagai Teknik

  • Teknik Power Query

    • Penomoran Grup Rekaman
    • Mengekstrak Dua Karakter Kiri
    • Kolom Total
    • Lain jika Klausul
    • Beberapa Header Identik Di Power Query
    • Apa yang Harus Dihapus
    • Pisahkan dengan Q
    • Menyortir Item Baris
    • Solusi Power Query dari MVP Excel

  • Bergerak Melampaui Antarmuka Power Query

    • Tabel. Pisahkan
    • Dunia Bill Szysz

  • Solusi Formula

    • Satu Formula Array Dinamis
    • Kolom Old School Helper
    • Solusi Formula

  • Gabungan dari semua Ide dari Atas dan Video Akhir

    • Gabungan Ide Terbaik Dari Semua

Artikel yang menarik...