Попытка транспонировать таблицу из двух столбцов в Excel с использованием первого столбца для группировки значений во втором столбце

Question

У меня есть таблица из двух столбцов в Microsoft Excel 2016, которая просто перечисляет категорию расходов в первом столбце и сумму расходов во втором столбце. Категория расходов не уникальна и будет повторяться несколько раз.

Выходные данные, которые я ищу, будут иметь уникальные категории расходов в качестве заголовков столбцов и все значения расходов, перечисленные в этой конкретной категории расходов.

Я пытался это с помощью Excel Query. После двух шагов по умолчанию в запросе, мой первый шаг - группировать строки по столбцу "Категория" с новым именем столбца, установленным в "CategoryValues", в моем случае, и операция в этом столбце - "Все строки". Это создает таблицу с уникальными категориями в первом столбце и ссылками на таблицы во втором столбце. Следующим шагом является транспонирование таблицы и продвижение первой строки в заголовки. После этих двух шагов у меня есть уникальные категории в виде столбцов с правильными заголовками, и первая строка данных для каждого столбца содержит ссылку на таблицу для другой таблицы, где первый столбец является уникальной категорией и значения только в этой категории.

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

Это именно то, чего я пытаюсь достичь, но со всеми категориями.

let
    Source = Excel.Workbook(File.Contents("C:\Users\Pancake\Documents\TestQuery.xlsx"), null, true),
    TblExpenses_Table = Source{[Item="TblExpenses",Kind="Table"]}[Data],
    #"Grouped Rows" = Table.Group(TblExpenses_Table, {"Category"}, {{"CategoryValues", each _, type table}}),
    #"Transposed Table" = Table.Transpose(#"Grouped Rows"),
    #"Promoted Headers" = Table.PromoteHeaders(#"Transposed Table", [PromoteAllScalars=true]),
    #"Adv  Fee" = #"Promoted Headers"{0}[Adv. Fee],
    #"Removed Columns" = Table.RemoveColumns(#"Adv  Fee",{"Category"})
in
    #"Removed Columns"

Вот пример данных

Category    Amount
Toll        3.65
Toll        4.8
Toll        120.35
Toll        10
DEF         23.32
DEF         15
Toll        13.25
Toll        122.35
DEF         8.66
Fax         2
Fax         2
Scale       11
Scale       2
Toll        3.5
Adv. Fee    0.99
Adv. Fee    12.95
Oil         17.98
Fax         2
Fax         5
DEF         30

Answer 1

Применяя эту очень хорошую ссылку на ваш вопрос, вот моя версия .. Предполагая, что указанные данные находятся в A1:B21.

1. Выберите столбец A и скопируйте его в столбец D (пустой столбец). Используйте Данные> удалить дубликаты, чтобы получить уникальные значения из списка. Мы попадем в D1:D7:

D1 ---> Category D2 ---> Toll D3 ---> DEF D4 ---> Fax D5 ---> Scale D6 ---> Adv. Fee D7 ---> Oil

2. Затем выберите уникальные значения, скопируйте. затем щелкните правой кнопкой мыши F1 и выберите Transpose. (Он будет вставлять скопированные данные столбца в виде строк .. поэтому в F1:K1 мы получим:

F1 ---> Toll G1 ---> DEF H1 ---> Fax I1 ---> Scale J1 ---> Adv. Fee K1 ---> Oil

3. В F2 вставьте это и нажмите Ctrl + Shift + Enter:

=IFERROR(INDEX($B:$B,SMALL(IF($A:$A=F$1,ROW($B:$B)-MIN(ROW($B:$B))+1),$E2)),"")

4. Затем перетащите его до K9. Готово.

Надеюсь, поможет.. (: