构建数组，查找数据，计数，求和，筛选，排序：这叫一个乱啊——其实，几个函数综合应用就搞定了

Excel数据分析之道

2025-05-27

下图是一个员工加班记录表，下面要根据这个表格制作几个统计分析包报告。

报告1：

如何将加班总时间超过指定数值（例如22小时）的员工制作一个统计表，这个统计表有4列数据：姓名，部门，加班总次数，加班总时间，并且按照加班总时间进行降序排序？

报告2：

如果将双休日加班总时间超过10小时的员工制作一个统计表，这个统计表有6列数据：姓名，部门，加班总次数，加班总时间，双休日加班次数，双休日加班总时间，并且按照双休日加班总时间进行降序排序？

报告3：

如果将工作日加班总时间超过15小时的员工制作一个统计表，这个统计表有6列数据：姓名，部门，加班总次数，加班总时间，工作日加班次数，工作日加班总时间，并且按照工作日加班总时间进行降序排序？

特别说明，要求直接根据原始数据，使用函数公式制作报表，不能使用数据透视表（实际上，当行字段有两个以上时，也无法对值字段进行整体排序，因此数据透视表不能得到这样的报表）。

先看第1个报告。

这个报告的基本逻辑思路是：

统计表有4列数据：姓名，部门，加班总次数，加班总时间，前2列在原始表个是存在的，但是需要处理不重复数据；后2列需要对每个人计算出来（一个是计数，一个是求和），因此，需要使用CHOOSE函数来构建数组。
第1列姓名，可以使用UNIQUE函数去重（假设为X1）：

X1 = UNIQUE( A2:A86 )
第2列部门，可以使用VLOOKUP函数匹配每个人的部门（假设为X2）：

X2 = VLOOKUP( UNIQUE(A2:A86),A2:B86,2,0 )
第3列加班总次数，可以使用COUNTIF函数来统计（假设为X3）：

X3 = COUNTIF( A2:A86,UNIQUE(A2:A86) )
第4列加班总时间，可以使用SUMIF函数来求和（假设为X4）：

X4 = SUMIF( A2:A86,UNIQUE(A2:A86),D2:D86 )
这样，CHOOSE函数构建有4列数据的数组为（假设为Y）：

Y = CHOOSE( {1,2,3,4}, X1, X2, X3, X4 )
使用FILTER函数对这个数组进行筛选，筛选条件是加班总时间大于指定的数值（假设为A），FILTER函数的返回值是新的数组（假设为Z）

Z = FILTER( Y, X4>=A )
最后使用SORT函数对这个新数组的第4列降序排序：
= SORT( Z, 4, -1 )

经过上述的分析，就可以快速设计出一个综合公式，如下所示，这里单元格J2指定的最低加班时间数值：

=SORT(FILTER(CHOOSE({1,2,3,4},UNIQUE(A2:A86),VLOOKUP(UNIQUE(A2:A86),A2:B86,2,0),COUNTIF(A2:A86,UNIQUE(A2:A86)),SUMIF(A2:A86,UNIQUE(A2:A86),D2:D86)),SUMIF(A2:A86,UNIQUE(A2:A86),D2:D86)>=J2),4,-1)