【实用公式解析】大小写敏感的汇总方式

分类：计数

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

问题

---

假设数据如下图：

左表中记录了一个许多条目的列表，其中有很多条目是通过大小写区分的。在右表中希望统计每个条目发生的次数。

分析

---

这个统计本身是一个计数的需求，常规应该用COUNTIFS函数：

注意，这是动态数组的形式。如果你的Excel不支持动态数组，可以使用公式：

=COUNTIFS(B5:B19,E5)

然后用鼠标拖拽填充。

仔细分析这个结果，会发现，实际上大小写没有区分：excel，Excel，EXCEL返回的结果是一样的。

这是因为，COUNTIFS是大小写不敏感的！

实际上，除非特别设置，Excel的绝大部分函数和功能都是大小写不敏感的！

因此，这条路行不通。

SUMPRODUCT方案

---

如果不能使用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

---

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

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

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

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

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

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