如何在Excel中基于项目与姓名列生成0/1关联矩阵？

我正在进行网络分析，需要生成关联矩阵（affiliation matrix）：将所有项目列为表头，所有姓名作为行，若姓名与项目存在关联（如Name a参与过Project 1）则填1，无关联则填0。

现有数据集 #

我的数据包含两列：B列为项目，C列为对应项目的关联姓名，示例数据如下：

期望输出 #

关联矩阵的预期格式如下：

遇到的问题 #

我尝试过使用MATCH函数但效果不佳，目前最接近的公式是：

=IF((IF($G$1=B10;1;0)+IF(E10=C10;1;0)-1)>0;1;0)

但存在两个核心问题：

1. 我的数据集有1500个姓名和3000+个项目，该公式计算效率极低；
2. 当矩阵中的姓名列表与数据列中的姓名单元格不完全匹配时，公式无法正常工作。

解决方案 #

针对大数据量场景，推荐以下三种高效方法：

方法1：COUNTIFS函数（兼容多数Excel版本） #

1. 提取唯一姓名列表：在空白列（如E列）使用UNIQUE(C:C)（Excel 365/2021），或通过「数据」选项卡→「删除重复值」提取所有唯一姓名；
2. 提取唯一项目列表：在空白行（如第1行）使用UNIQUE(B:B)，或同样用删除重复值提取后作为表头；
3. 在矩阵第一个数据单元格（如F2）输入公式：

   =--COUNTIFS($B:$B,F$1,$C:$C,$E2)>0
   

   回车后向右向下填充。
   • 补充：若存在姓名/项目的空格差异，可配合TRIM函数处理：=--COUNTIFS($B:$B,TRIM(F$1),$C:$C,TRIM($E2))>0

方法2：Power Query（超大数据量首选） #

Power Query处理大规模数据更高效，且支持自动更新：

1. 选中原始数据区域→「数据」选项卡→「从表格/区域」导入Power Query编辑器；
2. 选择「Name」列→「转换」选项卡→「透视列」；
3. 透视设置：值列选「Project」，高级选项选「不要聚合」，点击确定；
4. 替换值：选中所有项目列→「转换」→「替换值」，先将非空内容替换为1，再将空值替换为0；
5. 点击「关闭并上载」，将结果导入Excel，后续数据更新只需右键表格→「刷新」即可。

方法3：动态数组公式（Excel 365/2021专属） #

输入一次公式自动生成完整矩阵，支持自动刷新：

=LET(
    names, UNIQUE(C:C),
    projects, UNIQUE(B:B),
    matrix, IF(COUNTIFS(B:B,TRANSPOSE(projects),C:C,names)>0,1,0),
    HSTACK(names, matrix)
)

内容的提问来源于stack exchange，提问作者Kristine Kvist Johannsen