Биллов изазов „Како бисте очистили ове податке“ - Екцел савети

Преглед садржаја

Када радим семинар Повер Екцел уживо, нудим да ако неко у соби икада има необичан Екцел проблем, може ми га послати за помоћ. Тако сам дошао до овог проблема са чишћењем података. Неко је имао резиме радног листа који изгледа овако:

Резиме радног листа

Желели су да поново форматирају податке како би изгледали овако:

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

Један занимљив траг о овим подацима: Чини се да је 18 у Г4 подзброј Х4: К4. Примамљиво је уклонити колоне Г, Л и тако даље, али прво морате да издвојите име запосленог из Г3, Л3 и тако даље.

У недељу 9. фебруара било је 4 ујутро када сам укључио видео рекордер и снимио неке незграпне кораке у Повер Куери-у како бих решио проблем. С обзиром на то да је била недеља, дан у којем иначе не радим видео записе, замолио сам људе да пошаљу своје идеје како да реше проблем. Послато је 29 решења.

Свако решење нуди неко ново кул побољшање у мом процесу. Мој план је да започнем серију чланака који показују разна побољшања моје методе.

Погледајте видео

Пре него што започнем тај процес, позивам вас да видите моје решење:

И М-код који ми је Повер Куери генерисао:

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 година и сви га и даље користе на овај начин јер смо сви на то навикли.
  • Такође - многи од вас - рекли су да би коначно решење требало да буде високи вертикални сто, а затим помоћу пивот табеле да би се добили коначни резултати. Јонатхан Цоопер је ово најбоље резимирао: „Такође се слажем са неким другим ИоуТубе коментарима да одговарајући скуп података не би имао„ Укупне вредности “и да не би требало да се окреће на крају. Али ако корисник заиста жели обичан стари сто онда им дајете шта желе “. Заправо видим обе стране овога. Волим пивот табелу и једина ствар која је забавнија од Повер Куерија је Повер Куери са лепом пивот табелом на врху. Али ако све то можемо да урадимо у Повер Куери-у, онда ћемо разбити још једну ствар.

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

  • Повер Куери технике

    • Групе за нумерисање записа
    • Издвајање левих два знака
    • Укупна колона
    • Иначе ако клаузуле
    • Више идентичних заглавља у Повер Куери-у
    • Шта да избришем
    • Поделио К
    • Сортирање ставки поруџбина
    • Повер Куери решења из Екцел МВП-ова

  • Прелазак преко Повер Куери интерфејса

    • Табела.Сплит
    • Свет Билла Сзисза

  • Формула Солутионс

    • Једна динамичка формула низа
    • Колоне помоћника старе школе
    • Формула Солутионс

  • Композиција свих идеја одозго и завршни видео

    • Композиција најбољих идеја из свих

Занимљиви Чланци...