Billova výzva „Jak byste vyčistili tato data“ - Excel tipy

Když provádím živý seminář Power Excel, nabízím, že pokud má někdo v místnosti někdy zvláštní problém s Excelem, může mi jej poslat o pomoc. Tak jsem přišel k tomuto problému s očištěním dat. Někdo měl souhrnný list, který vypadá takto:

Souhrnný list

Chtěli přeformátovat data, aby vypadala takto:

Požadovaná přeformátovaná data

Jedna zajímavá stopa o těchto datech: 18 v G4 se jeví jako mezisoučet H4: K4. Je lákavé odebrat sloupce G, L atd., Ale nejprve musíte extrahovat jméno zaměstnance z G3, L3 atd.

V neděli 9. února byly 4 hodiny ráno, když jsem zapnul videorekordér a zaznamenal některé neohrabané kroky v Power Query, abych problém vyřešil. Vzhledem k tomu, že byla neděle, den, kdy běžně nedělám videa, jsem požádal lidi, aby zaslali své nápady, jak problém vyřešit. Bylo zasláno 29 řešení.

Každé řešení nabízí několik skvělých nových vylepšení oproti mému procesu. Mým plánem je zahájit sérii článků, které ukazují různá vylepšení mé metody.

Sledovat video

Než zahájím tento proces, vyzývám vás, abyste viděli moje řešení:

A M-kód, který pro mě vygeneroval 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"

Než se pustíme do řešení, pojďme se zabývat mnoha běžnými komentáři:

  • Někteří z vás řekli, že se vrátíte zpět, abyste zjistili, proč se data začínají zobrazovat v tomto formátu. Oceňuji tyto komentáře. Každý, kdo to řekl, je lepší člověk než já. Za ta léta jsem se naučil, že když se zeptáte „Proč?“ Odpověď obvykle zahrnuje tohoto bývalého zaměstnance, který se vydal touto cestou před 17 lety, a každý to používá tímto způsobem, protože jsme si už všichni zvyklí.
  • Mnozí z vás také uvedli, že konečným řešením by měl být vysoký vertikální stůl a poté k vytvoření konečných výsledků použít kontingenční stůl. Jonathan Cooper to shrnul nejlépe: „Souhlasím také s některými dalšími komentáři YouTube, že správná sada dat nebude mít„ součty “a nebude ji třeba na konci otáčet. Pokud však uživatel opravdu chce prostý text starý stůl, pak jim dáte, co chtějí. “ Ve skutečnosti vidím obě strany. Miluji kontingenční stůl a jediná věc zábavnější než Power Query je Power Query s pěknou kontingenční tabulkou nahoře. Ale pokud dokážeme celou věc udělat v Power Query, pak o jednu věc méně.

Zde jsou hypertextové odkazy na různé techniky

  • Techniky Power Query

    • Číslování skupin záznamů
    • Extrahování levých dvou postav
    • Celkový sloupec
    • Jinak pokud klauzule
    • Více identických záhlaví v Power Query
    • Co smazat
    • Rozdělit podle Q
    • Řazení řádkových položek
    • Řešení Power Query od MVP Excel

  • Přechod za rozhraní Power Query

    • Table.Split
    • Svět Billa Szysze

  • Řešení receptur

    • Jeden vzorec dynamického pole
    • Pomocné sloupy staré školy
    • Řešení receptur

  • Složený ze všech nápadů shora a závěrečné video

    • Složený z nejlepších nápadů ze všech

Zajímavé články...