无宏实现Excel不规则表格按名称统计任务类型与状态

问题说明 #

现有Excel报表结构不规则：仅A列部分行包含人员名称，名称下方的连续行对应该人员的任务状态（C列）与任务类型（D列）；A列的数字为报表自带统计值，无需处理。需要纯公式实现自动化统计指定人员的特定状态/任务类型数量，适配行数从几行到数千行的场景，供非技术员工使用（禁止宏）。此前规范格式报表使用的统计公式为：=COUNTIFS(人员列区域, 目标人员, 任务类型列区域, 目标类型)。

解决方案分步骤实现 #

1. 自动提取唯一人员列表（用于统计时的下拉选择） #

适用于Excel 365/2021（支持动态数组）

在空白列（如E列）的第一个单元格输入公式，自动生成所有唯一人员名称：

=UNIQUE(FILTER(A:A, NOT(ISNUMBER(A:A))*(A:A<>"")))

• 逻辑：FILTER筛选A列中非数字且非空的单元格（即人员名称行），UNIQUE去重后自动溢出到下方单元格，无需手动下拉填充。

适用于旧版Excel（无动态数组）

在空白列（如E列）的E2单元格输入数组公式（输入后按Ctrl+Shift+Enter确认），然后下拉直到出现#NUM!：

=INDEX(A:A, MIN(IF(NOT(ISNUMBER(A:A))*(A:A<>"")*(COUNTIF($E$1:E1,A:A)=0), ROW(A:A), 99999)))

• 逻辑：逐行筛选未在已提取列表中出现的人员名称，生成唯一列表。

2. 核心统计公式实现 #

方法一：添加辅助列（直观易维护）

在空白列（如F列）的F2单元格输入公式，下拉填充整列，给每一行任务匹配所属人员：

=IF(A2<>"", A2, XLOOKUP(TRUE, A$1:A1<>"", A$1:A1, "", 0, -1))

• 逻辑：如果当前行A列是人员名称，则直接引用；否则向上查找最近的非空A列值，即所属人员。

之后使用标准COUNTIFS公式统计（比如统计G2单元格指定人员、H2指定状态、I2指定类型的数量）：

=COUNTIFS(F:F, G2, C:C, H2, D:D, I2)

方法二：无辅助列嵌套公式（无需修改原表格结构）

直接使用SUMPRODUCT配合LOOKUP实现多条件统计，无需额外辅助列：

=SUMPRODUCT(--(C:C=H2), --(D:D=I2), --(LOOKUP(ROW(A:A), ROW(A:A)*(A:A<>""), A:A)=G2))

• 逻辑：
  1. LOOKUP(ROW(A:A), ROW(A:A)*(A:A<>""), A:A)：为每一行任务匹配最近的上方人员名称
  2. --将布尔判断结果转换为1/0数值
  3. SUMPRODUCT通过乘法求和实现多条件计数

3. 封装成易用模板（给非技术员工使用） #

设置一个统计交互区域，让用户通过下拉选择完成统计：

• G1单元格输入“人员名称”，G2设置数据验证下拉列表（数据源选择第一步提取的唯一人员列表）
• H1单元格输入“任务状态”，H2设置数据验证下拉列表（数据源用UNIQUE(C:C)提取所有状态）
• I1单元格输入“任务类型”，I2设置数据验证下拉列表（数据源用UNIQUE(D:D)提取所有类型）
• J2单元格输入上述统计公式，用户选择下拉选项后自动显示统计结果

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