Cabaran Bill "Bagaimana Anda Bersihkan Data Ini" - Petua Excel

Isi kandungan

Semasa saya melakukan seminar Power Excel secara langsung, saya menawarkan bahawa jika ada orang di dalam bilik yang mempunyai masalah Excel yang ganjil, mereka boleh menghantarnya kepada saya untuk mendapatkan bantuan. Itulah cara saya menerima masalah pembersihan data ini. Seseorang mempunyai lembaran kerja ringkasan yang kelihatan seperti ini:

Lembaran kerja ringkasan

Mereka ingin memformat semula data agar kelihatan seperti ini:

Data yang diformat semula yang diinginkan

Satu petunjuk menarik mengenai data ini: 18 di G4 nampaknya merupakan subtotal H4: K4. Adalah menggoda untuk membuang lajur G, L, dan lain-lain tetapi pertama sekali anda mesti mengeluarkan nama pekerja dari G3, L3, dan sebagainya.

Jam 4 pagi pada hari Ahad 9 Februari ketika saya menghidupkan perekam video dan merakam beberapa langkah kaku dalam Power Query untuk menyelesaikan masalah. Memandangkan hari itu adalah hari Ahad, hari yang biasanya saya tidak membuat video, saya meminta orang untuk menghantar idea mereka tentang cara menyelesaikan masalah. 29 penyelesaian telah dihantar.

Setiap penyelesaian menawarkan beberapa peningkatan baru yang hebat dalam proses saya. Rancangan saya adalah untuk memulakan satu siri artikel yang menunjukkan pelbagai penambahbaikan kaedah saya.

Tonton Video

Sebelum saya memulakan proses itu, saya menjemput anda untuk melihat penyelesaian saya:

Dan kod-M yang dihasilkan oleh 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 mula mencari penyelesaian, mari kita menangani banyak komen umum:

  • Sebilangan daripada anda mengatakan anda akan mundur untuk mengetahui mengapa data muncul dalam format ini sebagai permulaan. Saya menghargai komen ini. Semua orang yang mengatakan ini adalah orang yang lebih baik daripada saya. Saya telah mengetahui selama bertahun-tahun bahawa apabila anda bertanya "Mengapa?" jawapannya biasanya melibatkan bekas pekerja ini yang memulakannya 17 tahun yang lalu dan semua orang terus menggunakannya dengan cara ini kerana kita semua sudah terbiasa sekarang.
  • Juga - banyak daripada anda - mengatakan penyelesaian terakhir mestilah meja menegak tinggi dan kemudian gunakan jadual pangsi untuk menghasilkan hasil akhir. Jonathan Cooper merangkum yang terbaik: "Saya juga setuju dengan beberapa komen YouTube lain bahawa set data yang betul tidak akan mempunyai" Jumlah "dan tidak perlu diputar pada akhir. Tetapi jika pengguna benar-benar menginginkan dataran meja lama maka anda memberi mereka apa yang mereka mahukan. " Saya sebenarnya dapat melihat kedua-dua bahagian ini. Saya suka jadual pangsi dan satu-satunya perkara yang lebih menyeronokkan daripada Power Query ialah Power Query dengan jadual pangsi yang bagus di atas. Tetapi jika kita dapat melakukan semuanya dalam Power Query, maka satu perkara yang kurang dapat kita hancurkan.

Berikut adalah Hyperlink ke Pelbagai Teknik

  • Teknik Pertanyaan Kuasa

    • Penomboran Kumpulan Rekod
    • Mengekstrak Kiri Dua Karakter
    • Jumlah Lajur
    • Lain jika Klausa
    • Pelbagai Tajuk Sama Dalam Kueri Kuasa
    • Apa yang hendak dipadamkan
    • Berpisah dengan Q
    • Menyusun Item Baris
    • Penyelesaian Pertanyaan Kuasa dari MVP Excel

  • Bergerak Di Luar Antaramuka Kuasa Kuasa

    • Jadual. Split
    • Dunia Bill Szysz

  • Penyelesaian Formula

    • Formula Satu Array Dinamik
    • Tiang Pembantu Sekolah Lama
    • Penyelesaian Formula

  • Gabungan semua Idea dari Video Di Atas dan Akhir

    • Gabungan Idea Terbaik Dari Semua

Artikel menarik...