分类:计数
文末有视频,详细解析本文公式。
假设数据如下图:
左表中记录了一个许多条目的列表,其中有很多条目是通过大小写区分的。在右表中希望统计每个条目发生的次数。
这个统计本身是一个计数的需求,常规应该用COUNTIFS函数:
注意,这是动态数组的形式。如果你的Excel不支持动态数组,可以使用公式:
=COUNTIFS(B5:B19,E5)
然后用鼠标拖拽填充。
仔细分析这个结果,会发现,实际上大小写没有区分:excel,Excel,EXCEL返回的结果是一样的。
这是因为,COUNTIFS是大小写不敏感的!
实际上,除非特别设置,Excel的绝大部分函数和功能都是大小写不敏感的!
因此,这条路行不通。
如果不能使用COUNTIFS计数,我们就需要考虑替代方案:SUMPRODUCT函数。
我们可以使用如下公式:
=SUMPRODUCT(
--EXACT($B$5:$B$19,E5)
)
这个公式中,我们使用了EXACT函数来进行准确的对比(这个对比是大小写敏感的),其中,
EXACT($B$5:$B$19,E5)
返回一个数组:
{FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE},
通过--运算,转换成数组:
{0;1;0;0;0;0;0;1;0;0;0;0;0;1;0}
外层的SUMPRODUCT相当于:
=SUMPRODUCT({0;1;0;0;0;0;0;1;0;0;0;0;0;1;0})
这个函数仍然有固有的缺陷,因为条目列表是手动产生的,导致没有办法完全自动化。当数据变化造成条目列表变长或变短时,总需要手工调整。
我们可以考虑传统的方法:使用LAMBDA函数来实现自动化(这个公式的详细解析请参见):
=LET(
names, B5:B19,
u_name, SORT(UNIQUE(names)),
rows, COUNTA(u_name),
MAKEARRAY(
rows, 2,
LAMBDA(r,c,IF(c=1, INDEX(u_name,r), SUMPRODUCT(--EXACT(names,INDEX(u_name,r)))))
)
)
但是,很遗憾,这个公式并不能实现我们的目的:
究其原因,是因为我们通过UNIQUE函数取出其中的不重复数据,但是UNIQUE函数本身是大小写不敏感的。
对这个需求,实现起来比较复杂,我们首先应该找到一个方法,可以在大小写敏感的情况下取出所有的不重复数据。我们会在后面相关公式解析中详细分析实现方式。
更详细解析,请看视频
Excel+Power Query+Power Pivot+Power BI
自定义函数 底部菜单:知识库->自定义函数
面授培训 底部菜单:培训学习->面授培训
也可以在历史文章中学习Excel,Power Query,Power Pivot,Power BI,Power Automate各种技巧。
联系客服