Как я могу создать матрицу отношений в Excel

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

1 A X
1 A Y
1 B X             A   B   C
1 B Z         1  X,Y X,Z
2 A Z   ==>   2   Z   X   Y
2 B X         3   Y   Z
3 A Y
3 A Z
2 C Y

(Как) это можно сделать в Excel/VB/PowerBI или аналогичных?

Источник

2 ответа

Решение

Используя формулу массива, вы можете сгенерировать массив, который будет содержать либо значение третьего столбца, либо пробел в зависимости от того, следует ли включить это значение. Вы можете присоединиться к ним, используя TEXTJOIN. TEXTJOIN является новым в Office 365; если у вас его нет, вам сначала нужно определить его как функцию следующим образом (взято из MrExcel):

 Функция TEXTJOIN(разделитель как строка, IgnoreBlanks как логическое значение, ParamArray Text() как вариант) как строка
  Тусклый предмет как вариант, V как вариант, Arr как вариант
  Для каждого элемента в тексте
    If VarType(Item) > 8191 Тогда
      Для каждого V в элементе
        Если Len(V) > 0 или (Len(V) = 0 и не игнорировать поля), то TEXTJOIN = TEXTJOIN & Delimiter & V
      следующий
    еще
      TEXTJOIN = TEXTJOIN & Delimiter & Item
    End If
  следующий
  TEXTJOIN = Mid(TEXTJOIN, Лен (разделитель) + 1)
Конечная функция

Теперь вернемся к вашей проблеме, предполагая, что ваши данные находятся в A1:C9, а требуемая таблица - в F2:I5 (со строкой 2 и столбцом F, содержащим индексы), вам нужно использовать эту формулу в G3: {=TEXTJOIN(",";TRUE;IF($A$1:$A$9=$F3;IF($B$1:$B$9=G$2;$C$1:$C$9;"");""))}:

Если вы не знакомы с формулами массива, имейте в виду, что для его ввода вам понадобится сочетание клавиш CTRL+SHIFT+ENTER. Затем вам нужно будет скопировать G3 и вставить его в другие ячейки таблицы (в Excel это будет сложно, если вы попытаетесь вставить в диапазон, включая ячейку, из которой вы скопировали формулу массива, поэтому вам, возможно, придется это сделать в несколько страз); и формула будет использовать правильные индексы, так как $F3 и G$2 являются относительными.

Источник

Может быть, вы бы рассмотреть возможность использования надстройки

Следуйте этому руководству, чтобы получить этот инструмент

И тогда у вас есть это из коробки:)

Это руководство очень полезно

Источник
Другие вопросы по тегам