Billo iššūkis „Kaip jūs išvalytumėte šiuos duomenis“ - „Excel“ patarimai

Kai vedu tiesioginį „Power Excel“ seminarą, siūlau, kad jei kam nors iš kambario kada nors kiltų keista „Excel“ problema, jis galėtų nusiųsti man pagalbos. Štai kaip aš sulaukiau šios duomenų valymo problemos. Kažkas turėjo suvestinės darbalapį, kuris atrodo taip:

Santraukos darbalapis

Jie norėjo iš naujo suformatuoti duomenis taip:

Norimi performatuoti duomenys

Vienas įdomus užuomina apie šiuos duomenis: 18 G4 atrodo kaip H4: K4 tarpinė suma. Gundoma pašalinti G, L ir tt stulpelius, tačiau pirmiausia turite išskleisti darbuotojo vardą iš G3, L3 ir pan.

Vasario 9 d., Sekmadienį, buvo 4 val., Kai įjungiau vaizdo registratorių ir „Power Query“ užfiksavau keletą nepatogių žingsnių problemai išspręsti. Atsižvelgdamas į tai, kad buvo sekmadienis - diena, kai paprastai neveikiu vaizdo įrašų, paprašiau žmonių atsiųsti savo idėjas, kaip išspręsti problemą. Buvo išsiųsti 29 sprendimai.

Kiekvienas sprendimas siūlo keletą puikių mano proceso patobulinimų. Mano planas yra pradėti straipsnių seriją, kurioje būtų parodyti įvairūs mano metodo patobulinimai.

Žiūrėti video

Prieš pradėdamas šį procesą, kviečiu pamatyti mano sprendimą:

M kodas, kurį „Power Query“ sugeneravo man:

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"

Prieš pradėdami ieškoti sprendimų, atkreipkime dėmesį į daugybę bendrų komentarų:

  • Kai kurie iš jūsų sakė, kad eisite atgal ir išsiaiškinsite, kodėl pirmiausia duomenys rodomi tokiu formatu. Vertinu šiuos komentarus. Visi, kurie tai pasakė, yra geresnis žmogus nei aš. Per daugelį metų sužinojau, kad kai paklausite „Kodėl?“ atsakymas paprastai apima šį buvusį darbuotoją, kuris pradėjo šį kelią prieš 17 metų, ir visi taip naudojasi, nes dabar visi esame įpratę.
  • Be to, daugelis iš jūsų, sakė, kad galutinis sprendimas turėtų būti aukšta vertikali lentelė, o tada naudokite sukamą lentelę galutiniams rezultatams gauti. Geriausiai tai apibendrino Jonathanas Cooperis: „Aš taip pat sutinku su kai kuriais kitais„ YouTube “komentarais, kad tinkamame duomenų rinkinyje nebūtų„ Totals “ir jo nereikėtų gale pasukti. Bet jei vartotojas tikrai nori paprasto seną stalą, tada duodi jiems tai, ko jie nori “ Aš iš tikrųjų matau abi puses. Man patinka sukamasis stalas ir vienintelis dalykas, kuris yra smagesnis nei „Power Query“, yra „Power Query“ su gražia pasukimo lentele viršuje. Bet jei mes galime padaryti viską „Power Query“, tada reikia nutraukti dar vienu dalyku.

Čia pateikiamos hipersaitai į įvairias technikas

  • „Power Query Techniques“

    • Įrašų grupių numeravimas
    • Dviejų kairiųjų simbolių ištraukimas
    • Bendras stulpelis
    • Kitaip, jei sąlygos
    • Keli identiški antraštės vykdant užklausą
    • Ką ištrinti
    • Padalinta pagal Q
    • Eilutės elementų rūšiavimas
    • „Power Query Solutions“ iš „Excel“ MVP

  • Judėjimas už „Power Query“ sąsajos

    • Lentelė. Skaldyti
    • Billo Szyszo pasaulis

  • Formulės sprendimai

    • Viena dinaminio masyvo formulė
    • Senosios mokyklos pagalbininkų kolonos
    • Formulės sprendimai

  • Visų idėjų iš viršaus ir paskutinio vaizdo įrašo junginys

    • Geriausių visų idėjų junginys

Įdomios straipsniai...

Rekomenduojama