Bila izaicinājums "Kā jūs tīrītu šos datus" - Excel padomi

Satura rādītājs

Kad es rīkoju tiešraidē Power Excel semināru, es piedāvāju, ka, ja kādam no telpā kādreiz ir nepāra Excel problēma, viņš to var nosūtīt man pēc palīdzības. Tā es saņēmu šo datu attīrīšanas problēmu. Kādam bija kopsavilkuma darblapa, kas izskatās šādi:

Kopsavilkuma darblapa

Viņi vēlējās pārformatēt datus šādi:

Vēlamie pārformatētie dati

Viens interesants pavediens par šiem datiem: 18 G4, šķiet, ir H4: K4 starpsumma. Ir vilinoši noņemt kolonnas G, L un tā tālāk, bet vispirms jums jāizņem darbinieka vārds no G3, L3 utt.

Svētdien, 9. februārī, bija plkst. 4:00, kad ieslēdzu videoreģistratoru un ierakstīju dažus sarežģītus soļus Power Query, lai atrisinātu problēmu. Ņemot vērā to, ka bija svētdiena, diena, kurā parasti neveidoju video, es lūdzu, lai cilvēki iesūta savas idejas, kā atrisināt problēmu. Iesūtīti 29 risinājumi.

Katrs risinājums piedāvā dažus lieliskus jaunus uzlabojumus manā procesā. Mans plāns ir sākt rakstu sēriju, kurā parādīti dažādi manas metodes uzlabojumi.

Skatīties video

Pirms sāku šo procesu, es aicinu jūs iepazīties ar manu risinājumu:

Un M kods, ko man ģenerēja 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"

Pirms sākam iepazīties ar risinājumiem, pievērsīsimies daudziem izplatītiem komentāriem:

  • Daži no jums teica, ka dodaties atpakaļ, lai saprastu, kāpēc vispirms dati tiek rādīti šajā formātā. Es novērtēju šos komentārus. Visi, kas teica, ka tas ir labāks cilvēks nekā es. Gadu gaitā esmu iemācījies, ka, kad jūs jautājat "Kāpēc?" atbilde parasti ir saistīta ar šo bijušo darbinieku, kurš sāka šo ceļu pirms 17 gadiem, un visi to turpina izmantot, jo mēs visi esam pie tā pieraduši tagad.
  • Tāpat - daudzi no jums - teica, ka galīgajam risinājumam jābūt augstam vertikālam galdam un pēc tam izmantojiet pagrieziena tabulu, lai iegūtu galīgos rezultātus. Džonatans Kūpers to rezumēja vislabāk: "Es piekrītu arī dažiem citiem YouTube komentāriem, ka pareizai datu kopai nebūtu" Kopsummas "un beigās tā nebūtu jāpagriež. Bet, ja lietotājs patiešām vēlas vienkāršu vecs galds, tad jūs viņiem dodat to, ko viņi vēlas. " Es faktiski redzu abas tā puses. Man patīk pagrieziena galds, un vienīgais, kas ir jautrāk par Power Query, ir Power Query ar jauku pagrieziena galdu augšpusē. Bet, ja mēs visu varam paveikt Power Query, tad par vienu lietu jāsalauž mazāk.

Šeit ir hipersaites uz dažādiem paņēmieniem

  • Power Query metodes

    • Ierakstu grupu numerācija
    • Izvelk divas kreisās rakstzīmes
    • Kolonna kopā
    • Citādi, ja klauzulas
    • Vairāki identiski galvenes enerģijas vaicājumā
    • Ko dzēst
    • Sadalīt pa Q
    • Rindu vienumu kārtošana
    • Power Query risinājumi no Excel MVP

  • Pārvietošanās ārpus Power Query saskarnes

    • Tabula. Sadalījums
    • Bila Šyša pasaule

  • Formulu risinājumi

    • Viena dinamiskā masīva formula
    • Vecās skolas palīgu kolonnas
    • Formulu risinājumi

  • Salikts no visām idejām no augšas un gala video

    • Visu labāko ideju apvienojums

Interesanti raksti...