Kiedy prowadzę seminarium Power Excel na żywo, oferuję, że jeśli ktokolwiek w pokoju kiedykolwiek ma jakiś dziwny problem z Excelem, może wysłać mi go o pomoc. W ten sposób otrzymałem ten problem z czyszczeniem danych. Ktoś miał arkusz podsumowujący, który wygląda następująco:
Chcieli przeformatować dane, aby wyglądały następująco:
Jedna interesująca wskazówka dotycząca tych danych: 18 w G4 wydaje się być sumą częściową H4: K4. Kuszące jest usunięcie kolumn G, L i tak dalej, ale najpierw trzeba wyodrębnić nazwisko pracownika z G3, L3 i tak dalej.
Była 4 rano w niedzielę 9 lutego, kiedy włączyłem magnetowid i nagrałem kilka niezgrabnych kroków w Power Query, aby rozwiązać problem. Biorąc pod uwagę, że była to niedziela, dzień, w którym normalnie nie robię filmów, poprosiłem ludzi o przesłanie swoich pomysłów, jak rozwiązać problem. Nadesłano 29 rozwiązań.
Każde rozwiązanie oferuje nowe, fajne ulepszenia mojego procesu. Planuję rozpocząć serię artykułów, które pokażą różne ulepszenia mojej metody.
Obejrzyj wideo
Zanim zacznę ten proces, zapraszam do obejrzenia mojego rozwiązania:
Oraz kod M wygenerowany dla mnie przez Power Query:
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"Zanim zaczniemy zajmować się rozwiązaniami, zajmijmy się wieloma typowymi komentarzami:
- Niektórzy z was powiedzieli, że cofniecie się, aby dowiedzieć się, dlaczego dane na początku są wyświetlane w tym formacie. Doceniam te komentarze. Każdy, kto powiedział, że to jest lepsza osoba niż ja. Przez lata nauczyłem się, że kiedy pytasz „Dlaczego?” odpowiedź zwykle dotyczy byłego pracownika, który rozpoczął tę ścieżkę 17 lat temu i wszyscy używają jej w ten sposób, ponieważ wszyscy jesteśmy do tego przyzwyczajeni.
- Ponadto - wielu z was - powiedziało, że ostatecznym rozwiązaniem powinien być wysoki pionowy stół, a następnie użycie tabeli przestawnej do uzyskania ostatecznych wyników. Jonathan Cooper podsumował to najlepiej: „Zgadzam się również z niektórymi innymi komentarzami YouTube, że właściwy zestaw danych nie miałby„ Totals ”i nie musiałby być na końcu obracany. Ale jeśli użytkownik naprawdę chce stary stół, wtedy dajesz im to, czego chcą ”. Właściwie widzę obie strony tego. Uwielbiam tabelę przestawną, a jedyną fajniejszą rzeczą niż dodatek Power Query jest dodatek Power Query z ładną tabelą przestawną na górze. Ale jeśli możemy zrobić wszystko w dodatku Power Query, to o jedną rzecz mniej do zepsucia.
Oto hiperłącza do różnych technik
-
Techniki dodatku Power Query
- Numerowanie grup rekordów
- Wyodrębnianie dwóch lewych znaków
- Całkowita kolumna
- Inaczej, jeśli klauzule
- Wiele identycznych nagłówków w dodatku Power Query
- Co usunąć
- Podzielone przez Q
- Sortowanie elementów zamówienia
- Rozwiązania Power Query firmy Excel MVP
-
Wyjście poza interfejs Power Query
- Tabela Podziel
- Świat Billa Szysza
-
Formula Solutions
- Jedna dynamiczna formuła tablicy
- Kolumny pomocnika starej szkoły
- Formula Solutions
-
Złożenie wszystkich pomysłów z góry i końcowego wideo
- Kompozycja najlepszych pomysłów ze wszystkich
