Zoradenie riadkových položiek - tipy pre Excel

Obsah

Poznámka

Toto je jeden zo série článkov s podrobnými riešeniami zaslanými k výzve Podcast 2316.

Jedným z problémov môjho riešenia je, že výsledná postupnosť kategórií sa nemusí zhodovať s pôvodnou postupnosťou stĺpcov. Uvedomil som si to na samom konci môjho videa a keďže to nebolo nijako zvlášť dôležité, nemal som z toho obavy.

Josh Johnson však poslal riešenie, ktoré to zvládlo. Keď Josh povedal, že použil stĺpec Index, predpokladal som, že je to ako Index a Modulo v Power Query: Číselné skupiny záznamov ako 1 až 5 opakovane. Joshovo použitie bolo ale úplne iné.

Poznámka: Túto metódu použil aj Excel MVP John MacDougall, ktorý však zreťazil stĺpec indexu na koniec popisu kategórie. Johnovo video nájdete tu: https://www.youtube.com/watch?v=Dqmb6SEJDXI a viac informácií o jeho kóde nájdete tu: Aplikácie MVP programu Excel útočia na problém čistenia údajov v Power Query.

Na začiatku procesu, keď mal Josh stále iba šesť záznamov, pridal index začínajúci na 1. Josh klikol do panela vzorcov a premenoval stĺpec Index na kategóriu.

Názov zmenený vo vzorcovom paneli

Stĺpec Kategória bol novým posledným stĺpcom. Na začiatok použil Move, aby bol prvý:

Presunúť na začiatok

Potom sa stane veľa ďalších krokov. Sú to kroky, ktoré sú inovatívne, ale zatiaľ sa nimi väčšinou zaoberali ďalšie články. Po mnohých takýchto krokoch som si začal myslieť, že čísla kategórie 1 až 6 sú iba chybou. Myslel som si, že ich možno Josh odstráni bez toho, aby ich použil.

Josh Unpivots, potom podmienený stĺpec, potom vyplňte, potom otočte, pridá súčet. Zdá sa, že ten stĺpec kategórie nikdy nepoužíva. Po mnohých krokoch je tu:

Sčítajte celkom

Ale potom v posledných krokoch Josh triedi údaje podľa mena zamestnanca a potom podľa kategórie!

Zoradiť podľa mena zamestnanca ako od kategórie

V tomto okamihu môže odstrániť stĺpec Kategória. Posledný rozdiel: PTO prichádza pred Projektom A, rovnako ako v pôvodných stĺpcoch. Je to príjemný dotyk.

Tiež upozorním, že Josh poslal video, ako tieto kroky prešiel. Kudos Joshovi za používanie klávesových skratiek vo vnútri Power Query!

Klávesové skratky

Tu je Joshov kód:

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))), #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Category", 1, 1), #"Reordered Columns" = Table.ReorderColumns(#"Added Index",("Category", "Category Description", "Dept. Total", "Q1", "Q2", "Q3", "Q4", "Employee 1", "Q1_1", "Q2_2", "Q3_3", "Q4_4", "Employee 2", "Q1_5", "Q2_6", "Q3_7", "Q4_8", "Employee 3", "Q1_9", "Q2_10", "Q3_11", "Q4_12", "Employee 4", "Q1_13", "Q2_14", "Q3_15", "Q4_16")), #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Reordered Columns", ("Category", "Category Description"), "Attribute", "Value"), #"Extracted Text Before Delimiter" = Table.TransformColumns(#"Unpivoted Other Columns", (("Attribute", each Text.BeforeDelimiter(_, "_"), type text))), #"Added Conditional Column" = Table.AddColumn(#"Extracted Text Before Delimiter", "Employee Name", each if not Text.StartsWith((Attribute), "Q") then (Attribute) else null), #"Filled Down" = Table.FillDown(#"Added Conditional Column",("Employee Name")), #"Filtered Rows" = Table.SelectRows(#"Filled Down", each ((Attribute) = "Q1" or (Attribute) = "Q2" or (Attribute) = "Q3" or (Attribute) = "Q4") and ((Employee Name) "Dept. Total")), #"Pivoted Column" = Table.Pivot(#"Filtered Rows", List.Distinct(#"Filtered Rows"(Attribute)), "Attribute", "Value", List.Sum), #"Inserted Sum" = Table.AddColumn(#"Pivoted Column", "Total", each List.Sum(((Q1), (Q2), (Q3), (Q4))), type number), #"Sorted Rows" = Table.Sort(#"Inserted Sum",(("Employee Name", Order.Ascending), ("Category", Order.Ascending))), #"Removed Columns" = Table.RemoveColumns(#"Sorted Rows",("Category")) in #"Removed Columns"

Vráťte sa na hlavnú stránku výzvy Podcast 2316.

Prečítajte si nasledujúci článok v tejto sérii: Programy MVP programu Excel útočia na problém s čistením údajov v Power Query.