我有一列由一个或多个字母组成的字符串，每个字母代表一个组，如下所示：
| A | |
|---|---|
| 1 | A |
| 2 | AB |
| 3 | C |
| 4 | 交流 |
| 5 | 远程控制 |
| 6 | 特效 |
| 7 | R |
| 8 | 射频 |
| 9 | 交流 |
| 10 | AB |
| 11 | 光盘 |
| 12 | XD |
其中多字母字符串表示该项目属于多个组，因此项目 1group A仅位于 中，而项目 2 位于group A和 中group B，依此类推。
组本身从 A 到 Z（即共有 26 个组），并且每个单独的项目最多可以属于两个组。
我正在尝试构建一个 Excel 函数，该函数将输出 A...Z 中每组成对交集的大小，以便计算重叠系数和其他此类统计数据。
编辑来自评论中的建议：
实际计算是偶然的 - 我遇到的问题是找到所有设定的交叉点大小。
我编辑了样本数据，使输出更有趣/多样化。在这种情况下，交叉点将具有以下大小：
| A | 乙 | |
|---|---|---|
| 1 | A | 5 |
| 2 | AB | 2 |
| 3 | C | 4 |
| 4 | 交流 | 2 |
| 5 | 远程控制 | 2 |
| 6 | 特效 | 1 |
| 7 | R | 2 |
| 8 | 射频 | 1 |
| 9 | 交流 | 2 |
| 10 | AB | 2 |
| 11 | 光盘 | 1 |
| 12 | XD | 2 |
但我想要的是输出一个 26x26 矩阵，包含所有可能的成对交点。这就是我遇到的问题。理想情况下，输入的顺序无关紧要，也就是说，它将BA被计算为与 相同AB。
提前致谢！
答案1
如果使用固定宽度的“文本到列”将两个值分开，这可能会更容易1
然后，您可以对左侧列（列A，公式在C下面的示例中位于列中）使用以下公式：
=IFERROR(COUNTIF(A:A,A1)/MIN(COUNTIF(A:B,A1),COUNTIF(A:B,B1)),"")
这是右侧列（列B，在下面的示例中，公式位于列中D）：
=IFERROR(COUNTIF(A:A,B1)/MIN(COUNTIF(A:B,A1),COUNTIF(A:B,B1)),"")
为了获得我认为你想要的比例（如果这不是你想要的，请编辑你的问题添加模拟数据的预期结果。
示例结果：
答案2
您可以尝试以下公式：
=IF(LEN(A1)=1, COUNT(SEARCH("*" & A1 & "*",$A$1:$A$12)), COUNT(SEARCH("*" & A1 & "*",$A$1:$A$12)) + COUNT(SEARCH("*" & TEXTJOIN("",1,MID(A1,SEQUENCE(LEN(A1),,LEN(A1),-1),1)) & "*",$A$1:$A$12)))
请注意，它仅适用于最多 2 个字符组合，即ABor RorYZ但不是ABCnor LNR。您还需要 Excel 365。
答案3
我找到了一个答案，虽然不是特别优雅，但却给出了我想要的交集大小矩阵：
矩阵的公式如下（Ctrl+Shift+Enter并拖动）：
=IF($C2=D$1,(SUM(LEN($A$1:$A$12)-LEN(SUBSTITUTE($A$1:$A$12,$C2,"")))),(SUM(LEN($A$1:$A$12)-LEN(SUBSTITUTE($A$1:$A$12,$C2&D$1,"")))/LEN($C2&D$1))+(SUM(LEN($A$1:$A$12)-LEN(SUBSTITUTE($A$1:$A$12,D$1&$C2,"")))/LEN(D$1&$C2)))
此外，如果有人感兴趣的话，这里是针对并集而不是交叉集的：
=IF($C30=D$29,(SUM(LEN($A$1:$A$12)-LEN(SUBSTITUTE($A$1:$A$12,$C30,"")))/LEN($C30)),(SUM(LEN($A$1:$A$12)-LEN(SUBSTITUTE($A$1:$A$12,$C30,"")))/LEN($C30))+(SUM(LEN($A$1:$A$12)-LEN(SUBSTITUTE($A$1:$A$12,D$29,"")))/LEN(D$29)))