分类:计数
文末有视频,详细解析本文公式。
假设有数据如下图:
左表中记录了每个人的入职日期,希望在右表中统计每个年份的入职人数。
最简单的当然是数据透视表:
对这类问题来说,数据透视表方便快捷,是首选方案。
但是数据透视表在某些场景中不是很合适,比如,在制作Dashboard时,没有办法让透视表适应Dashboard的风格和格式要求,很多时候必须放过来,需要让Dashboard根据透视表做格式的的改变。
又比如,如果我们这个计算是中间结果,那么实用数据透视表就相当于将自动化过程拆分成了两个过程,中间必须手动处理。
这时候,需要使用公式解决。
要求是计数,首选是使用COUNTIFS。
但是COUNTIFS函数只能将入职时间的整列区域作比较,不能比较入职时间的年份部分,尽管我们可以将其中的年份拆分出来。
但是我们可以使用辅助列。
我们可以添加一个辅助列,
其中入职年份使用公式:
=YEAR(C5)
然后使用公式:
=COUNTIFS(D5:D24,F5:F19)
可以得到结果:
这个方案可以完美解决问题,但是辅助列“入职年份”,实际上是个冗余信息。
如果表格比较大,这样的信息会造成空间的浪费,同时多了一列公式,从性能上来说也不经济。
我们可以使用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函数,可以制作出完全自动的方案,不仅仅根据年份自动统计人数,还可以根据源数据自动获得年份并排序。
公式如下:
=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
自定义函数 底部菜单:知识库->自定义函数
面授培训 底部菜单:培训学习->面授培训
也可以在历史文章中学习Excel,Power Query,Power Pivot,Power BI,Power Automate各种技巧。
联系客服