Как я могу создать матрицу отношений в 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 или подобных?


powerbi excel enterprise-architecture
person Bastl    schedule 17.01.2019    source источник
comment
Очень похоже: stackoverflow.com/questions/32767117/   -  person Bastl    schedule 17.01.2019

Ответы (2)


arrow_upward
1
arrow_downward

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

Function TEXTJOIN(Delimiter As String, IgnoreBlanks As Boolean, ParamArray Text() As Variant) As String
  Dim Item As Variant, V As Variant, Arr As Variant
  For Each Item In Text
    If VarType(Item) > 8191 Then
      For Each V In Item
        If Len(V) > 0 Or (Len(V) = 0 And Not IgnoreBlanks) Then TEXTJOIN = TEXTJOIN & Delimiter & V
      Next
    Else
      TEXTJOIN = TEXTJOIN & Delimiter & Item
    End If
  Next
  TEXTJOIN = Mid(TEXTJOIN, Len(Delimiter) + 1)
End Function

Теперь вернемся к вашей проблеме, если ваши данные находятся в 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 являются относительными.

person Joubarc    schedule 17.01.2019
comment
выглядит очень хорошо, но я борюсь. Я ввожу формулу без фигурных скобок, верно? Когда CTRL-SHIFT-ENTER, это не распознается как формула, курсор показывает ошибку в запятой-разделителе... - person Bastl; 17.01.2019
comment
Извините, я тестировал это только в Excel 2010 с texjoin как UDF, как указано выше. Однако я забыл принять во внимание, что моя локаль может отличаться и использует ; в качестве разделителя параметров, в то время как вам, вероятно, требуется запятая. Можете ли вы проверить это таким образом? Если это сработает лучше, я соответствующим образом отредактирую ответ. - person Joubarc; 17.01.2019
comment
Большой! это была (моя) проблема. Всегда находил странным, что формулы excel и vb-код локализованы :-) - person Bastl; 18.01.2019
comment
Просто применил его к моей актуальной проблеме: супер полезно и очень просто! Спасибо! - person Bastl; 18.01.2019

arrow_upward
0
arrow_downward

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

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

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

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

person Daut    schedule 17.01.2019
comment
но это только для числовых данных не так ли? Мне нужны качественные данные (строки, точнее список строк) в ячейках. - person Bastl; 17.01.2019
comment
@Bastl tbh Я не проверял это, поэтому не знаю. Вы проверили это? Я хочу сохранить этот ответ, но добавлю, что он для чисел, только если у вас есть - person Daut; 17.01.2019