如何在Excel中统计不同元素同ID的共现次数？

前提处理：拆分多元素行到单行 #

原表格中一个ID对应多个Content元素，首先需要将其拆分为ID-单个元素的一对一结构，这是后续统计的基础：

• 若使用Excel 365/Google Sheets，可直接用公式实现拆分：
  假设原数据在A2:B100（A列ID，B列多元素内容），输入公式：

  =TOCOL(HSTACK(A2:A100, TEXTSPLIT(B2:B100, ", ", , TRUE)), 2)
  

  这个公式会自动将每个ID对应的元素拆成独立行，同时过滤空值，得到干净的基础数据表。
• 若数据量较大，优先用Power Query（Excel）或数据编辑器（Google Sheets）拆分，操作更稳定。

生成无冗余的共现统计表格 #

1. 提取唯一元素列表

从拆分后的元素列中提取不重复值，作为统计表格的行/列标题：

=UNIQUE(拆分后的元素列)

将这些值分别放在统计表格的第一行（列标题）和第一列（行标题）。

2. 统计共现次数（避免镜像冗余）

在统计单元格中使用公式，仅统计行元素 < 列元素的组合（跳过镜像重复的行）：
假设行标题在E2:E10，列标题在F1:Z1，拆分后的ID列是A:A，元素列是C:C，单元格F2的公式：

=IF(E2 >= F1, "", SUMPRODUCT(--(COUNTIFS(A:A, A:A, C:C, E2) > 0), --(COUNTIFS(A:A, A:A, C:C, F1) > 0)))

公式逻辑：

• 先判断行元素是否大于等于列元素，是则留空（避免Rock-Sand和Sand-Rock重复统计）
• SUMPRODUCT统计同时包含两个元素的唯一ID数量

3. 大数据量优化方案

如果数据行数超过1000，公式会卡顿，改用Power Query流程：

1. 加载拆分后的ID-元素数据表
2. 生成元素的笛卡尔积组合，添加自定义列判断元素1 < 元素2，过滤掉不满足的行
3. 按元素1和元素2分组，统计ID的唯一值数量，直接生成最终统计表格

解决无效行问题 #

拆分数据后，先对ID-元素列执行删除重复值操作，确保每个组合仅出现一次，避免统计时的无效计数。

内容的提问来源于stack exchange，提问作者Az JRC