Задача Билла "Как бы вы очистить эти данные" - Советы по Excel

Содержание

Когда я провожу семинар по Power Excel в режиме реального времени, я предлагаю, чтобы, если у кого-нибудь в комнате когда-нибудь возникнет странная проблема с Excel, они могут отправить ее мне за помощью. Вот так я и получил эту проблему с очисткой данных. У кого-то был итоговый лист, который выглядит так:

Сводный рабочий лист

Они хотели переформатировать данные, чтобы они выглядели так:

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

Один интересный ключ к этим данным: 18 в G4, по-видимому, являются промежуточным итогом H4: K4. Заманчиво удалить столбцы G, L и так далее, но сначала вы должны извлечь имя сотрудника из G3, L3 и так далее.

Было 4 утра в воскресенье 9 февраля, когда я включил видеомагнитофон и записал в Power Query несколько неуклюжих шагов для решения проблемы. Учитывая, что это было воскресенье, день, когда я обычно не снимаю видео, я попросил людей присылать свои идеи о том, как решить проблему. Отправлено 29 решений.

Каждое решение предлагает новые крутые улучшения по сравнению с моим процессом. Я планирую начать серию статей, в которых будут показаны различные улучшения моего метода.

Смотреть видео

Прежде чем я начну этот процесс, я приглашаю вас увидеть мое решение:

И M-код, который для меня сгенерировал 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

    • Нумерация групп записей
    • Извлечение двух левых символов
    • Итоговый столбец
    • Else if Clauses
    • Несколько одинаковых заголовков в Power Query
    • Что удалить
    • Разделить на Q
    • Сортировка позиций
    • Решения Power Query от Excel MVP

  • Выход за рамки интерфейса Power Query

    • Таблица.Split
    • Мир Билла Шиша

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

    • Формула одного динамического массива
    • Колонны Old School Helper
    • Формула Решения

  • Сочетание всех идей сверху и финального видео

    • Сочетание лучших идей из всех

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