Сортиране на договорените покупки - Съвети на Excel

Съдържание

Забележка

Това е една от поредицата статии, описващи подробно решения, изпратени за предизвикателството на Podcast 2316.

Един от проблемите с моето решение е, че крайната последователност на категориите не съвпадаше непременно с първоначалната последователност на колоните. Разбрах това в самия край на видеото си и тъй като не беше особено важно, не се притеснявах за това.

Джош Джонсън обаче изпрати решение, което се справи с него. Когато Джош каза, че използва индексна колона, предположих, че това е като Индекс и Modulo в Power Query: Брой групи записи от 1 до 5 многократно. Но употребата на Джош беше съвсем различна.

Забележка: Excel MVP John MacDougall също използва този метод, но той обединява индексната колона в края на описанието на категорията. Вижте видеото на Джон тук: https://www.youtube.com/watch?v=Dqmb6SEJDXI и прочетете повече за кода му тук: Excel MVP атакуват проблема с изчистването на данни в Power Query.

В началото на процеса, когато Джош все още имаше само шест записа, той добави индекс, започващ от 1. Джош щракна в лентата с формули и преименува колоната Индекс като Категория.

Променено име в лентата с формули

Графата Категория беше новата последна колона. Той използва Move, за да започне, за да го премести, за да бъде първи:

Преминаване към началото

След това се случват много други стъпки. Това са стъпки, които са иновативни, но досега са разгледани най-вече в останалите статии. След много такива стъпки започнах да мисля, че категориите от 1 до 6 са просто грешка. Мислех, че евентуално Джош ще ги изтрие, без да ги използва.

Josh Unpivots, след това условна колона, след това запълване, след това pivots, добавя общата сума. Изглежда, че никога не използва тази графа Категория. След много стъпки той е тук:

Добавете общо

Но след това в последните стъпки Джош сортира данните по име на служител, след това по категория!

Сортирай по име на служител от категория

В този момент той може да изтрие колоната Категория. Последната разлика: PTO идва преди проект A, точно както беше в оригиналните колони. Това е хубаво докосване.

Също така ще отбележа, че Джош изпрати видеоклип, на който преминава през тези стъпки. Слава на Джош за използване на клавишни комбинации вътре в 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))), #"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"

Върнете се на главната страница за предизвикателството Podcast 2316.

Прочетете следващата статия от тази поредица: Excel MVP атакуват проблема с почистването на данни в Power Query.

Интересни статии...