如何统计Excel表格中员工数字对的共同工作周次？

需求说明 #

需要统计所有员工编号对共同出现在同一周的次数，当前处理12周数据，后续需扩展至52周。

方法一：修正SUMPRODUCT公式（适合单对查询或小范围统计） #

如果你之前用SUMPRODUCT没得到正确结果，大概率是没处理好“同一周内同时存在两个员工”的判断逻辑。以查询员工5和3的共同周数为例，假设周数在A列，员工数据在B:G列，使用以下公式：

=SUMPRODUCT(--(MMULT(--(B2:G13=5),ROW(INDIRECT("1:"&COLUMNS(B:G)))^0)>0), --(MMULT(--(B2:G13=3),ROW(INDIRECT("1:"&COLUMNS(B:G)))^0)>0))

• 核心逻辑：先用MMULT判断每一周是否存在目标员工，再用SUMPRODUCT统计两周判断都为真的次数
• 若要统计任意员工对，可把公式里的5和3换成单元格引用（比如H2和I2），同时为避免重复统计（如5&3和3&5），可固定引用的员工编号顺序（比如H2<I2）

方法二：Power Query批量生成所有员工对统计（适合扩展到52周） #

如果需要一次性统计所有可能的员工对，且后续要新增周数据，Power Query是更高效的方案，步骤如下：

1. 选中数据区域，点击「数据」选项卡→「从表格/区域」，导入Power Query编辑器
2. 选中「周数」列，点击「转换」→「逆透视列」→「逆透视其他列」，把所有员工编号转为单列（列名默认是「值」）
3. 移除「属性」列，筛选「值」列去掉空值（若存在）
4. 点击「添加列」→「自定义列」，输入公式：=List.RemoveItems(List.Distinct(#"逆透视列"[值]), {[值]})，生成当前员工以外的所有员工列表
5. 点击自定义列的展开按钮，将列表展开为新行
6. 添加自定义列「员工对」，输入公式：=Text.Combine({Text.From(List.Min({[值],[自定义]})), Text.From(List.Max({[值],[自定义]}))}, "&")，统一员工对的顺序（避免重复统计）
7. 按「员工对」和「周数」分组，选择「计数行」，得到每个员工对在单周的出现标记
8. 再按「员工对」分组，选择「求和」对计数结果求和，得到共同工作的总周数
9. 点击「关闭并上载」，将结果导出到Excel表格

后续新增周数据后，只需右键点击结果表格→「刷新」，就能自动更新统计结果，适合大规模数据处理。

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