基于唯一任务分组计算计数中位数的动态数组函数需求

原始数据表格 #

解决方案 #

1. Excel 365/2021 动态数组公式（自动扩展） #

如果使用支持动态数组的Excel版本，可直接用以下公式一次性生成所有唯一任务的中位数，无需下拉操作，且表格行数增加时会自动适配：

提取按出现顺序排列的唯一任务

=UNIQUE(FILTER(A2:A1048576, A2:A1048576<>""))

（注：A2:A1048576 覆盖A列所有数据行，自动忽略空行与表头）

计算对应任务的中位数

=BYROW(UNIQUE(FILTER(A2:A1048576, A2:A1048576<>"")), LAMBDA(task, MEDIAN(FILTER(B2:B1048576, (A2:A1048576=task)*(B2:B1048576<>"")))))

• UNIQUE(FILTER(...))：过滤任务列的空值，提取按原始顺序排列的唯一任务
• BYROW：遍历每个唯一任务并执行后续计算
• FILTER(B:B, ...)：筛选当前任务对应的非空计数，再用MEDIAN计算中位数

2. 旧版Excel 数组公式（需按Ctrl+Shift+Enter确认） #

对于不支持动态数组的旧版Excel，需分两步操作：

步骤1：提取唯一任务（C2单元格输入，按Ctrl+Shift+Enter后下拉）

=INDEX($A:$A, MATCH(0, COUNTIF($C$1:C1, $A$2:INDEX($A:$A, COUNTA($A:$A))), 0)+1)

• INDEX($A:$A, COUNTA($A:$A))：动态获取A列最后一行非空数据，实现行数自动适配
• COUNTIF($C$1:C1, ...)：避免重复提取已出现的任务

步骤2：计算对应中位数（D2单元格输入，按Ctrl+Shift+Enter后下拉）

=MEDIAN(IF(($A:$A=C2)*($B:$B<>""), $B:$B))

• IF(($A:$A=C2)*($B:$B<>""), $B:$B)：筛选当前任务对应的非空计数
• MEDIAN：计算筛选后数据的中位数

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