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

389
PancakeBimmer

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

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

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

Image 1

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

Image 2

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

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"[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
1
Я не знаю, как это сделать с помощью Power Query, но это можно сделать с помощью программирования на VBA. Ron Rosenfeld 6 лет назад 0

1 ответ на вопрос

0
p._phidot_

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

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

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

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

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

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

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

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

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

Похожие вопросы