Предизвикателство на Бил "Как бихте изчистили тези данни" - Съвети за Excel

Съдържание

Когато правя семинар на Power Excel на живо, предлагам, че ако някой в ​​стаята някога има странен проблем с Excel, може да ми го изпрати за помощ. По този начин получих този проблем с почистването на данни. Някой имаше обобщен работен лист, който изглежда така:

Обобщен работен лист

Те искаха да преформатират данните, за да изглеждат така:

Желани преформатирани данни

Една интересна улика за тези данни: 18-те в G4 изглежда са междинен сбор от H4: K4. Изкушаващо е да премахнете колони G, L и т.н., но първо трябва да извлечете името на служителя от G3, L3 и т.н.

Беше 4 ч. Сутринта в неделя, 9 февруари, когато включих видеорекордера и записах няколко неудобни стъпки в Power Query за решаване на проблема. Като се има предвид, че беше неделя, ден, в който обикновено не правя видеоклипове, помолих хората да изпратят своите идеи как да разрешат проблема. Изпратени са 29 решения.

Всяко решение предлага някои страхотни нови подобрения спрямо моя процес. Планът ми е да започна серия от статии, които показват различните подобрения на моя метод.

Гледам видео

Преди да започна този процес, ви каня да видите моето решение:

И М-кодът, който 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"

Преди да започнем да се впускаме в решенията, нека разгледаме много често срещани коментари:

  • Някои от вас казаха, че ще се върнат назад, за да разберат защо данните се показват в този формат за начало. Оценявам тези коментари. Всеки, който каза, че е по-добър човек от мен. През годините научих, че когато попитате „Защо?“ отговорът обикновено включва този бивш служител, който е тръгнал по този път преди 17 години и всички продължават да го използват по този начин, тъй като всички сме свикнали сега.
  • Също така - много от вас - казаха, че крайното решение трябва да бъде висока вертикална таблица и след това да се използва обобщена таблица, за да се получат крайните резултати. Джонатан Купър обобщи това най-добре: „Съгласен съм и с някои от другите коментари в YouTube, че подходящият набор от данни няма да има„ Общо “и не би трябвало да се върти в края. Но ако потребителят наистина иска обикновен стара маса, след което им давате това, което искат. " Всъщност виждам и двете страни на това. Обичам обобщена таблица и единственото нещо, което е по-забавно от Power Query, е Power Query с хубава обобщена таблица отгоре. Но ако можем да направим цялото нещо в Power Query, тогава едно нещо по-малко да прекъснем.

Ето хипервръзки към различни техники

  • Техники на Power Query

    • Групи за номериране на записи
    • Извличане на левите два знака
    • Обща колона
    • В противен случай клаузи
    • Няколко идентични заглавки в Power Query
    • Какво да изтриете
    • Разделено по Q
    • Сортиране на договорени покупки
    • Power Query Solutions от Excel MVPs

  • Преминаване отвъд интерфейса на Power Query

    • Таблица.Разделена
    • Светът на Бил Шиш

  • Формула Решения

    • Формула с един динамичен масив
    • Колони за помощници от старото училище
    • Формула Решения

  • Композит от всички идеи отгоре и окончателно видео

    • Композит от най-добрите идеи от всички

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