如何在 Excel 中创建关系矩阵 [英] How can I create a relationship matrix in Excel

问题描述

给定一个三元组列表，我想得到如下关系矩阵.

1 A X1个1 B X A B C1 B Z 1 X,Y X,Z2 A Z ==>2 Z X Y2 B X 3 Y Z3个3 AZ2 C Y

(如何)这可以在 Excel/VB/PowerBI 或类似工具中完成吗?

解决方案

使用数组公式，您可以生成一个数组，该数组将包含第三列的值或空白，具体取决于是否应包含该值.然后，您可以使用 TEXTJOIN 加入它们.TEXTJOIN 是 Office 365 中的新功能；如果你没有它，你首先需要以这种方式将它定义为一个函数(取自

如果您不熟悉数组公式，请注意您需要使用 CTRL+SHIFT+ENTER 才能输入.然后，您需要复制 G3 并将其粘贴到表格的其他单元格中(如果您尝试粘贴到包含您从中复制数组公式的单元格的范围内，Excel 会对此很挑剔，因此您可能必须这样做在几个糊状物中)；并且公式将使用适当的索引，因为 $F3 和 G$2 是相对的.

Given a list of triples I want to get a relationship matrix as follows.

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

(How) can this be done in Excel/VB/PowerBI or similar ?

解决方案

Using an array formula, you can generate an array which will hold either the value of the third column or a blank depending on whether the value should be included. You can then join them using TEXTJOIN. TEXTJOIN being new in Office 365; if you don't have it you'll first need to define it as a function this way (taken from 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

Now back to your problem, assuming your data is in A1:C9 and the table you want is at F2:I5 (with line 2 and column F containing the indexes) you'll need to use this formula in G3: {=TEXTJOIN(",";TRUE;IF($A$1:$A$9=$F3;IF($B$1:$B$9=G$2;$C$1:$C$9;"");""))}:

If you're unfamiliar with array formulas, please note that you'll need to use CTRL+SHIFT+ENTER to enter it. You'll then need to copy G3 and paste it to the other cells of your table (excel will be finicky about that if you try to paste to a range including the cell you copied an array formula from, so you might have to do it in several pastes); and the formula will use the proper indexes as $F3 and G$2 are relative.

这篇关于如何在 Excel 中创建关系矩阵的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！