Как создать таблицу перекрестных ссылок в Excel?

Question

У меня в Excel есть такая таблица значений:

Col1 Col2
---- ----
 a    1
 b    2
 c    3
 d    1
 e    1
 f    2

Я хочу, чтобы результирующая таблица перекрестных ссылок была создана на основе некоторых функций:

   NewCol1     NewCol2
   -------     -------
      1        a, d, e
      2        b, f
      3        c

Как мне это сделать? Возможно ли это?

Answer 1

Поместите курсор в любое место таблицы, затем используйте Данные>Получить и преобразовать данные>Из таблицы/диапазона. Если у вас более ранняя версия Excel, это может быть доступно через надстройку Power Query.

Редактор Power Query должен открыться и показать вам следующее:

Выберите Col2, щелкнув по заголовку, и используйте «Главная»>«Преобразовать»>«Группировать по», например:

Когда вы нажмете «ОК», вы увидите, что Summing Col1 вызывает ошибку:

Используйте Главная>Запрос>Расширенный редактор, чтобы увидеть следующее:

Теперь измените часть, которая гласит следующее:

List.Sum([Col1])

К этому:

Text.Combine([Col1],", ")

Итак, у вас есть это:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Col1", type text}, {"Col2", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Col2"}, {{"Col1", each Text.Combine([Col1],", "), type text}})
in
    #"Grouped Rows"

Нажав «ОК», вы получите желаемые результаты:

Теперь вы можете использовать Home>Close & Load, чтобы загрузить результаты в вашу рабочую книгу. Когда новые данные добавляются в исходную таблицу, просто щелкните правой кнопкой мыши таблицу в рабочей книге и выберите «Обновить», чтобы снова запустить преобразование.