如何用Excel公式生成列中A#值的共现计数矩阵？

步骤1：提取唯一值作为矩阵行/列标题 #

假设原始数据存放在A1:C4区域，先从中提取所有唯一的A#值作为矩阵的行、列标题：

• 若使用Excel 365/2021版本，在单元格E1输入公式：=UNIQUE(TOCOL(A1:C4,1))，按回车后会自动溢出所有唯一值（即示例中的A5、A6、A7、A8、A9）。
• 将溢出的水平值作为矩阵的列标题（E1:I1），再复制一份作为行标题（E2:E6）。

步骤2：编写共现统计公式 #

根据你提供的示例矩阵逻辑，核心是统计行标题值所在行中，列标题值的出现次数总和（自身与自身的共现记为0），可以用以下组合公式实现：

以矩阵统计起始单元格F2（对应行A5、列A6）为例，输入公式：

=IF($E2=F$1,0,SUMPRODUCT(--(BYROW($A$1:$C$4,LAMBDA(r,ISNUMBER(MATCH($E2,r,0))))),BYROW($A$1:$C$4,LAMBDA(r,COUNTIF(r,F$1)))))

输入完成后，将公式向右、向下填充至整个矩阵区域即可。

公式说明：

• IF($E2=F$1,0, ...)：对角线位置（行标题与列标题相同）直接显示0，匹配示例要求。
• BYROW($A$1:$C$4,LAMBDA(r,ISNUMBER(MATCH($E2,r,0))))：逐行判断当前行是否包含行标题值，生成布尔值数组。
• BYROW($A$1:$C$4,LAMBDA(r,COUNTIF(r,F$1)))：逐行统计当前行中列标题值的出现次数，生成次数数组。
• SUMPRODUCT：将两个数组对应相乘后求和，仅累加包含行标题值的行中，列标题值的出现次数总和。

旧版Excel兼容方案 #

若使用不支持动态数组和LAMBDA的旧版Excel，可改用以下数组公式（输入时需按Ctrl+Shift+Enter确认）：

=IF($E2=F$1,0,SUM(IF(MMULT(--($A$1:$C$4=$E2),ROW($A$1:$C$1)^0)>0,COUNTIF(INDIRECT("A"&ROW($A$1:$A$4)&":C"&ROW($A$1:$A$4)),F$1),0)))

此公式通过MMULT判断每行是否包含行标题值，再统计对应行中列标题值的次数并求和。

内容的提问来源于Stack Exchange，提问作者Bruno Miguel