如何在Excel中无需宏高效简化多COUNTIFS统计变量组合次数

问题场景 #

现有Excel数据：A1区域包含第1周（Mod1）、第2周（Mod2）对应周一至周五的人员分配数据，需在A10区域生成统计表格，统计每个姓名在Mod1、Mod2下的总出现次数。当前采用多个COUNTIFS()叠加的方式操作繁琐，寻求无宏的高效替代方案。

解决方案 #

1. SUMPRODUCT 通用解法（兼容多数Excel版本）

适用于所有支持SUMPRODUCT的Excel版本，无需动态数组支持。
假设：

• Mod1的人员数据列范围为$B$2:$F$6（周一至周五）
• Mod2的人员数据列范围为$G$2:$K$6
• 统计表格中，姓名位于C10单元格，模态（Mod1/Mod2）位于D10单元格

在统计单元格输入公式：

=SUMPRODUCT((($B$2:$F$6=C10)*(D10="Mod1"))+(($G$2:$K$6=C10)*(D10="Mod2")))

• 原理：用*实现「列范围匹配姓名+模态匹配」的AND逻辑，+实现Mod1/Mod2的OR逻辑
• 固定原数据区域的绝对引用（$），直接拖拽填充统计表格的行列即可

2. Excel 365/2021 动态数组解法（一键生成统计矩阵）

如果使用支持动态数组的Excel版本，可一次性生成整个统计表格，无需逐格拖拽：
假设：

• 待统计姓名列表在C10:C15
• 模态标签（Mod1/Mod2）在D9:E9

在D10单元格输入公式，自动填充整个统计区域：

=BYROW(C10:C15,LAMBDA(name,BYCOL(D9:E9,LAMBDA(mod,SUMPRODUCT((INDIRECT(IF(mod="Mod1","B2:F6","G2:K6"))=name)*1)))))

• 原理：BYROW遍历每个姓名，BYCOL遍历每个模态，INDIRECT根据模态自动切换数据列范围，最终批量计算次数

3. Power Query 可视化操作（适合大量数据）

无宏、可视化操作，处理大量数据时更高效：

1. 选中A1区域的原始数据，点击「数据」选项卡→「从表格/区域」（勾选「我的表格有标题」）进入Power Query编辑器
2. 选中Mod1对应的周一至周五列，右键→「逆透视列」→「仅逆透视选定列」，新增一列命名为「模态」，填充值为Mod1
3. 重复步骤2，处理Mod2的列，新增「模态」列填充Mod2
4. 点击「主页」→「追加查询」→「追加查询为新」，将两个逆透视后的表合并
5. 选中「姓名」和「模态」列，点击「转换」→「分组依据」，设置：
   • 分组依据：选择「姓名」和「模态」
   • 新列名：次数
   • 操作：计数行
6. 点击「主页」→「关闭并上载」，将统计结果导入Excel，直接得到所需的统计表格

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