Billova výzva „Ako by ste vyčistili tieto údaje“ - Excel tipy

Keď robím živý seminár programu Power Excel, ponúknem, že ak má niekto v miestnosti niekedy zvláštny problém s programom Excel, môže mi ho poslať s prosbou o pomoc. Takto som prišiel k tomuto problému s čistením údajov. Niekto mal súhrnný pracovný hárok, ktorý vyzerá takto:

Súhrnný list

Chceli preformátovať údaje tak, aby vyzerali takto:

Požadované preformátované údaje

Jedna zaujímavá stopa o týchto údajoch: 18 v G4 sa javí ako medzisúčet H4: K4. Je lákavé odstrániť stĺpce G, L atď., Najskôr však musíte extrahovať meno zamestnanca z G3, L3 atď.

V nedeľu 9. februára boli štyri hodiny ráno, keď som zapol videorekordér a zaznamenal niektoré neohrabané kroky v aplikácii Power Query, aby sa problém vyriešil. Vzhľadom na to, že bola nedeľa, deň, keď bežne nerobím videozáznamy, som požiadal ľudí, aby mi zaslali svoje nápady, ako problém vyriešiť. Bolo zaslaných 29 riešení.

Každé riešenie ponúka nejaké super nové vylepšenie môjho procesu. Mojím plánom je založiť sériu článkov, ktoré ukazujú rôzne vylepšenia mojej metódy.

Pozeraj video

Pred začatím tohto procesu vás pozývam, aby ste videli moje riešenie:

A M-kód, ktorý mi 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"

Skôr ako sa pustíme do riešenia, poďme sa venovať mnohým častým komentárom:

  • Niektorí z vás povedali, že sa vrátite späť, aby ste zistili, prečo sa dáta najskôr zobrazujú v tomto formáte. Vážim si tieto komentáre. Každý, kto to povedal, je lepší človek ako ja. Za tie roky som sa naučil, že keď sa pýtate „Prečo?“ odpoveď zvyčajne spočíva v tomto bývalom zamestnancovi, ktorý sa touto cestou vydal pred 17 rokmi, a všetci to takto používajú, pretože na to sme si teraz všetci zvyknutí.
  • Tiež - mnohí z vás - ste povedali, že konečným riešením by mal byť vysoký vertikálny stôl a potom pomocou kontingenčnej tabuľky dosiahnuť konečné výsledky. Najlepšie to zhrnul Jonathan Cooper: „Súhlasím tiež s niektorými ďalšími komentármi na YouTube, že správny súbor údajov nebude obsahovať„ súčty “a na konci ho nebude treba otáčať. Ak však používateľ chce obyčajnú rovinu starý stôl, potom im dáte, čo chcú. “ V skutočnosti vidím obe strany. Páči sa mi kontingenčná tabuľka a jediná vec zábavnejšia ako Power Query je Power Query s pekným kontingenčným stolom navrchu. Ale ak dokážeme celú vec v Power Query, potom je tu ešte jedna vec, ktorú treba pokaziť.

Tu sú odkazy na rôzne techniky

  • Techniky Power Query

    • Skupiny číslovania záznamov
    • Extrahovanie ľavých dvoch znakov
    • Celkový stĺpec
    • Inak ako doložky
    • Viaceré identické hlavičky v Power Query
    • Čo odstrániť
    • Rozdelené podľa Q
    • Zoradenie riadkových položiek
    • Riešenia Power Query od MVP programu Excel

  • Presahujúci rámec Power Query

    • Tabuľka. Rozdeliť
    • Svet Billa Szysza

  • Riešenia vzorcov

    • Jeden vzorec dynamického poľa
    • Stĺpy pomocníka starej školy
    • Riešenia vzorcov

  • Zložený zo všetkých Nápadov zhora a Záverečného videa

    • Zložený z najlepších nápadov zo všetkých

Zaujímavé články...