Виклик Білла "Як би ви очистили ці дані" - Поради Excel

Коли я проводжу семінар Power Excel у прямому ефірі, я пропоную, що якщо хтось із присутніх коли-небудь має дивну проблему Excel, він може надіслати його мені за допомогою. Ось так я отримав цю проблему очищення даних. Хтось мав зведений аркуш, який виглядає так:

Короткий аркуш

Вони хотіли переформатувати дані, щоб виглядати так:

Бажані переформатовані дані

Один цікавий підказка щодо цих даних: 18 у G4, здається, є проміжними підсумками H4: K4. Спокусливо видалити стовпці G, L тощо, але спочатку потрібно витягти ім’я співробітника з G3, L3 тощо.

У неділю, 9 лютого, було 4 ранку, коли я увімкнув відеореєстратор і записав кілька незграбних кроків у 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 MVP

  • Перехід за межі інтерфейсу Power Query

    • Стіл
    • Світ Білла Шиша

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

    • Формула одного динамічного масиву
    • Колони помічників старої школи
    • Формула Рішення

  • Складене з усіх ідей зверху та остаточне відео

    • Склад найкращих ідей з усіх

Цікаві статті...