【原】【实用公式解析】统计入职年份

分类：计数

文末有视频，详细解析本文公式。

问题

---

假设有数据如下图：

左表中记录了每个人的入职日期，希望在右表中统计每个年份的入职人数。

分析

---

最简单的当然是数据透视表：

对这类问题来说，数据透视表方便快捷，是首选方案。

但是数据透视表在某些场景中不是很合适，比如，在制作Dashboard时，没有办法让透视表适应Dashboard的风格和格式要求，很多时候必须放过来，需要让Dashboard根据透视表做格式的的改变。

又比如，如果我们这个计算是中间结果，那么实用数据透视表就相当于将自动化过程拆分成了两个过程，中间必须手动处理。

这时候，需要使用公式解决。

要求是计数，首选是使用COUNTIFS。

但是COUNTIFS函数只能将入职时间的整列区域作比较，不能比较入职时间的年份部分，尽管我们可以将其中的年份拆分出来。

但是我们可以使用辅助列。

辅助列方案

---

我们可以添加一个辅助列，

其中入职年份使用公式：

=YEAR(C5)

然后使用公式：

=COUNTIFS(D5:D24,F5:F19)

可以得到结果：

这个方案可以完美解决问题，但是辅助列“入职年份”，实际上是个冗余信息。

如果表格比较大，这样的信息会造成空间的浪费，同时多了一列公式，从性能上来说也不经济。

SUMPRODUCT方案

---

我们可以使用SUPRODUCT函数，这样就可以在不添加辅助列的情况下进行年份统计。公式如下：

=SUMPRODUCT(--(YEAR($C$5:$C$24)=E5))

在这里，我们使用YEAR函数将日期列的年份取出，并分别与当前的年份进行比较，其中的：

--(YEAR($C$5:$C$24)=E5)

部分，实际上是一个数组：

{0;0;0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0;1}

你可以自己推导一下这个数组的产生过程。

将整个SUMPRODUCT公式双击填充到结果区域，即完成需求：

这个公式有一个缺陷，如果我们的数据发生了变化，增加了新人，或者有人的入职年份做了调整，结果区域的年这一列需要手动调整，另外人数列的公式也需要重新填充到新的年份。因此，这个SUMPRODUCT方案是个半自动的，不完善的方案。

LAMBDA方案

---

利用新的LAMBDA函数，可以制作出完全自动的方案，不仅仅根据年份自动统计人数，还可以根据源数据自动获得年份并排序。

公式如下：

=LET(    dates, C5:C24,    all_year, YEAR(dates),    u_year, SORT(UNIQUE(all_year)),    rows, COUNT(u_year),    MAKEARRAY(        rows,2,        LAMBDA(r,c,IF(c=1,INDEX(u_year,r),SUMPRODUCT(--(all_year=INDEX(u_year,r)))))    ))

LET函数的作用是提高可读性，首先定义了四个参数：

• dates，数据区域的入职日期列

• all_year，入职日期列中所有日期的年份，通过对dates参数使用YEAR函数获得

• u_year，all_year中所有的不重复列表，并且是升序排序过的。对all_year参数使用UNIQUE函数获得不重复列表，然后再使用SORT函数进行排序

• rows，u_year的个数，代表了结果区域的行数

LET函数的最后一个参数是MAKEARRAY函数，这个函数返回一个行数为rows，列数为2的数组。数组的值用IF函数定义：

• 对于第一列，直接返回u_year中的对应值

• 对于第二列，返回其中的SUMPRODUCT公式代表的值。

详细解析，请看视频

Excel+Power Query+Power Pivot+Power BI

---

Power Excel 知识库    按照以下方式进入知识库学习

Excel函数   底部菜单：知识库->Excel函数

自定义函数  底部菜单：知识库->自定义函数

Excel如何做  底部菜单：知识库->Excel如何做

面授培训  底部菜单：培训学习->面授培训

也可以在历史文章中学习Excel，Power Query，Power Pivot，Power BI，Power Automate各种技巧。