【WPS】SUMIFS 和 COUNTIFS 函数

在连载系列文章中，我们使用 COUNTIFS 多条件计数函数完成了在职状态、性别以及学历的统计。

本节，我们将要完成的是在职人员薪酬水平以及平均工龄的统计。

查找数据的基础表

首先是工资表：

工资表包含了每位员工的工资明细，其中合计列为每位员工的总报酬，计算公式为：=SUM(D2:H2) 即将基础工资、绩效、加班工资、福利津贴以及五险一金相加得出。

是否离职列通过 =IF(VLOOKUP($A2,员工编制表!$A:$D,4,FALSE)="离职","是","否") 计算而来，判断条件为 VLOOKUP($A2,员工编制表!$A:$D,4,FALSE)="离职"，条件为真则返回 "是"，条件不成立则返回 "否"。

其中，判断条件中使用了 VLOOKUP 函数，该函数查找的表为：

员工编制表

接下来是人员信息表：

人员信息表中列出了员工是否离职以及工龄。其中是否离职和工资表中的计算方法相同，这里不再赘述。

简单介绍一下工龄的计算方法：=YEAR(TODAY())-YEAR(F2)：TODAY() 函数返回当前的日期，YEAR() 返回日期中的年份。工龄的计算使用当前年份减去入职年份。

计算在职人员的平均薪资

首先，利用 SUMIFS 函数计算各部门在职人员的总薪资。SUMIFS 的两个条件分别是部门和在职，在 L4 单元格录入如下公式：

=SUMIFS(工资表!$J:$J,工资表!$D:$D,$B4,工资表!$C:$C,"否")

其中 工资表!$J:$J 为工资表中合计列的绝对引用，为求和区域。

蓝色框中的部分为判断区域 1 和条件 1 ，其中判断区域 工资表!$D:$D 为工资表中的部门列的绝对引用，条件 $B4 为当前表格的部门列，使用混合索引锁定行。

绿色框中为判断区域 2 和条件 2 ，其中判断区域 工资表!$C:$C 为工资表中是否离职列的绝对引用，判断条件为等于“否”。

至此，我们求出了人事部薪资总和，为了获得平均薪水，需要计算出人事部在职总人数，我们自然想到的就是多条件求和函数 COUNTIFS ，由于 COUNTIFS 的判断条件也为部门和在职，因此和 SUMIFS 的条件是一样的，直接复制过来即可：

最后使用自动填充计算剩余的单元格。

计算在职人员的平均工龄

计算各部门在职人员的平均工龄的流程和计算各部门平均薪酬是一样的，同样分解为计算各部门总工龄和总在职人数。

计算各部门在职人员的总工龄，多条件求和的两个条件分别是：部门和在职。在 M4 单元格录入 SUMIFS 函数：

其中求和区域为人事信息表中的工龄列，并使用绝对引用。判断区域 1 为人事信息表中的是否离职列，并使用绝对引用，判断条件 1 为等于“否”。

判断区域 2 为人事信息表中的部门列，并使用绝对引用；判断条件 2 为等于 $B4 ，即当前表格的部门列，使用混合引用锁定行。

最后，补充 COUNTIFS 函数，使得SUMIFS 计算结果除以 COUNTIFS 计算结果，求出平均工龄。

这里，由于多条件计数和多条件求和时使用的条件完全一致，不再赘述：

使用自动填充，计算剩余值就好啦~

以上就是今天的全部内容了~文章围绕 SUMIFS 和 COUNTIFS 的应用实例展开，进行数据统计，分别计算了各部门的平均薪酬和平均工龄。