Eilutės elementų rūšiavimas - „Excel“ patarimai

Turinys

Pastaba

Tai vienas iš straipsnių serijos, kuriame pateikiami išsamūs sprendimai, atsiųsti iššūkiui „Podcast 2316“.

Viena iš mano sprendimo problemų yra ta, kad galutinė kategorijų seka nebūtinai sutapo su pradine stulpelių seka. Tai supratau pačioje savo vaizdo įrašo pabaigoje ir kadangi tai nebuvo ypač svarbu, dėl to nesijaudinau.

Tačiau Joshas Johnsonas atsiuntė sprendimą, kuris jį sprendė. Kai Joshas pasakė, kad naudojo indekso stulpelį, aš maniau, kad tai panašu į „Power Query“ indeksą ir „Modulo“: įrašų grupės pakartotinai skaičiuojamos nuo 1 iki 5. Tačiau Josh naudojimas buvo visiškai kitoks.

Pastaba: „Excel MVP“ Johnas MacDougallas taip pat naudojo šį metodą, tačiau indekso stulpelį susiejo iki kategorijos aprašymo pabaigos. Žiūrėkite Jono vaizdo įrašą čia: https://www.youtube.com/watch?v=Dqmb6SEJDXI ir daugiau apie jo kodą skaitykite čia: „Excel MVPs Attack the Data Cleansing Problem in Power Query“.

Proceso pradžioje, kai Joshas dar turėjo tik šešis įrašus, jis pridėjo indeksą, prasidedantį skaičiumi 1. Joshas spustelėjo formulės juostą ir indekso stulpelį pavadino kategorija.

Formulės juostoje pakeistas vardas

Kategorijos stulpelis buvo naujas paskutinis stulpelis. Jis naudojo „Pradėti“ judėti, kad būtų pirmas:

Pereiti į pradžią

Po to įvyksta daugybė kitų žingsnių. Tai yra naujoviški žingsniai, tačiau iki šiol dažniausiai aptariami kituose straipsniuose. Po daugelio tokių žingsnių pradėjau galvoti, kad kategorijos numeriai nuo 1 iki 6 buvo tik klaida. Pamaniau, kad galbūt Džošas juos ištrins nenaudodamas.

Josh Unpivots, tada sąlyginis stulpelis, tada užpildykite, tada pasukdami, pridėsite sumą. Atrodo, kad jis niekada nenaudoja tos kategorijos stulpelio. Po daugelio žingsnių jis yra čia:

Pridėti bendrą

Bet tada atlikdamas paskutinius veiksmus, Josh surūšiuoja duomenis pagal darbuotojo vardą, tada kategoriją!

Rūšiuoti pagal darbuotojo vardą, o ne kategoriją

Šiuo metu jis gali ištrinti stulpelį Kategorija. Galutinis skirtumas: PTO ateina prieš projektą A, kaip ir originaliuose stulpeliuose. Tai malonus prisilietimas.

Taip pat atkreipsiu dėmesį, kad Joshas atsiuntė vaizdo įrašą, kuriame jis mato šiuos veiksmus. Pagarba Joshui už sparčiųjų klavišų naudojimą „Power Query“ viduje!

Klaviatūros nuorodos

Čia yra Josho kodas:

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"

Grįžkite į pagrindinį „Podcast 2316“ iššūkio puslapį.

Perskaitykite kitą šios serijos straipsnį: „Excel MVPs“ - „Power Query“ užkirsti kelią duomenų valymo problemai.