Billov izziv "Kako bi očistili te podatke" - Nasveti za Excel

Ko opravim seminar Power Excel v živo, ponudim, da če ima kdo v sobi nenavadno težavo z Excelom, mi ga lahko pošlje na pomoč. Tako sem prišel do te težave s čiščenjem podatkov. Nekdo je imel povzetek delovnega lista, ki je videti takole:

Povzetek delovnega lista

Podatke so želeli preoblikovati tako, da so videti tako:

Zaželeni preoblikovani podatki

Zanimiv namig o teh podatkih: 18 v G4 je videti kot vmesni seštevek H4: K4. Vabljivo je odstraniti stolpce G, L itd., Vendar morate najprej izluščiti ime zaposlenega iz G3, L3 itd.

V nedeljo, 9. februarja, je bila ura zjutraj, ko sem vklopil video snemalnik in v Power Query posnel nekaj nerodnih korakov za rešitev težave. Glede na to, da je bila nedelja, dan, ko običajno ne delam videoposnetkov, sem prosil, naj ljudje pošljejo svoje ideje, kako rešiti težavo. Poslanih je 29 rešitev.

Vsaka rešitev ponuja nekaj novega, novega izboljšanja mojega postopka. Moj načrt je začeti serijo člankov, ki prikazuje različne izboljšave moje metode.

Oglejte si video

Preden začnem s tem postopkom, vas vabim, da si ogledate mojo rešitev:

In M-koda, ki mi jo je ustvaril 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"

Preden se lotimo rešitev, se lotimo številnih pogostih komentarjev:

  • Nekateri ste rekli, da boste šli nazaj in ugotovili, zakaj se podatki prikazujejo v tej obliki. Cenim te komentarje. Vsi, ki so rekli, da je to boljša oseba kot jaz. Z leti sem se naučil, da ko vprašate "Zakaj?" odgovor običajno vključuje nekdanjega uslužbenca, ki se je po tej poti začel pred 17 leti in ga vsi še naprej uporabljajo na ta način, saj smo tega zdaj že vajeni.
  • Prav tako - mnogi od vas - so rekli, da bi morala biti končna rešitev visoka navpična tabela, nato pa uporabite končno tabelo za končne rezultate. Jonathan Cooper je to najbolje povzel: "Strinjam se tudi z nekaterimi drugimi komentarji v YouTubu, da ustrezen nabor podatkov ne bi imel" Vsote "in ga ne bi bilo treba vrteti na koncu. Če pa uporabnik resnično želi navaden stara miza, potem jim daš, kar hočejo. " Pravzaprav vidim obe strani tega. Obožujem vrtilno tabelo in edina stvar, ki je zabavnejša od Power Query, je Power Query z lepo vrtilno tabelo na vrhu. Če pa lahko v Power Queryju naredimo celotno stvar, potem je treba razbiti še eno stvar manj.

Tu so hiperpovezave do različnih tehnik

  • Tehnike Power Query

    • Skupine oštevilčenja zapisov
    • Izvleček levih dveh znakov
    • Skupni stolpec
    • V nasprotnem primeru klavzule
    • Več enakih glav v Power Query
    • Kaj izbrisati
    • Razdeljeno po Q
    • Razvrščanje postavk vrstice
    • Rešitve Power Query iz Excelovih MVP-jev

  • Preseganje vmesnika Power Query

    • Tabela.Razdeljena
    • Svet Billa Szysza

  • Rešitve formule

    • Ena dinamična matrična formula
    • Stolpci pomočnikov stare šole
    • Rešitve formule

  • Sestavljen iz vseh idej od zgoraj in končnega videa

    • Kompozicija najboljših idej od vseh

Zanimive Članki...