Excel 5个众数经典公式，最后一个，90%的人都不会！

本文作者：小花

本文编辑：卫星酱

小花在之前的一篇文章中，分享了如何使用 INDEX+MATCH+MODE 函数组合，求解文本数据众数的问题。

你还记得吗？

正文开始前，让我们先稍微回顾下那个公式：

多数值众数公式

N3 单元格数组公式如下：

公式说明：

先使用 MATCH(B2:M2,B2:M2,0)函数返回 B2:M2 中每一个文本首次出现的位置序数值，得到数组{1,1,3,3,5,1,1,3,1,1,1,5}，再使用 MODE 函数统计这些序数值的众数 1，最后通过 INDEX 函数索引位置序数值众数对应位置的文本。

该公式可以直接进行数组运算，无须按【Ctrl+Shift+Enter】也能准确运算。

眼尖的小伙伴这时候一定发现了，电视柜品类 （案例图中第 6 行） 中，广州和东莞获得月冠军的次数是相同的，都是 5 次，但公式结果却只有其中一个，这怎么解决呢？

这就要看今天的正文了↓↓↓

解决多个文本众数的问题存在两个卡点：

一是 MODE 函数遇到多个众数时，只能返回其中的一个；

二是 INDEX 函数的第二个参数不支持数组。

第一个卡点，可以使用 MODE 函数的孪生兄弟MODE.MULT 函数来解决，它可以返回所有众数并按列罗列。

还是以双色球为例，统计近 10 期双色球蓝球开奖号码的众数，发现 8 号和 11 号都出现了两次，这种情况就可以用 MODE.MULT 函数来处理。

如果你想学习更多函数知识和 Excel 小技巧，提升你的办公效率，避免无效加班！

那我推荐你加入秋叶 Excel 3 天集训营！有大神带你学习表格飞速排版、数据高效整理、图表美化设计……！！

秋叶 Excel 3 天集训营

现在免费报名！

多数值众数公式

D2:D3 公式如下：

公式说明：

MODE.MULT 函数的用法与 MODE 函数几乎一致，只是前者返回数组结果，因此需选择多个单元格后输入公式，再按【Ctrl+Shift+Enter】才能准确运算。

第二个卡点解决的思路有两个：

一个是使用 N+IF 结构来绕开 INDEX 函数对第二参数的限制；

二是弃用 INDEX 函数，改用 CHOOSE 函数来索引数值。

回到计算电视柜品类年度销冠的问题上来，使用N+IF 结构，公式相对简单，且当文本集合元素较多时，公式更为简便。

多文本众数公式-INDEX+N+IF

N2 公式如下：

公式说明：

❶ MODE.MULT(MATCH(B2:M2,B2:M2,0))

该部分与上文公式一几乎一致，只是将只能返回单个众数的 MODE 函数替换为能够返回多个众数的 MODE.MULT 函数。

MATCH(B2:M2,B2:M2,0)的作用还是将文本数组转化为代表各个文本首次出现位置序数的数值数组，MODE.MULT 函数返回多个众数文本的位置序数值组成的数组。

❷ N(IF(1,❶))

N 和 IF(1 组成的连环套没有实际内容上的含义，即 N(IF(1,❶))和片段❶本身在内容上是完全一致的，之所以需要给片段❶穿上 N+IF 外衣，是为了将片段❶代表的数组包装成 INDEX 能够正确运算的数值形式。

N+IF 在很多高阶公式中都有应用，具体原理有兴趣的小伙伴可以自行深入研究，此处一时难以讲解清楚，不再赘述。

❸ INDEX(B2:M2,❷）

根据片段❷返回的一组位置序数值，从 B2:M2 中索引对应位置的文本。

❹ CONCAT(❸)

CONCAT 函数的功能是将文本直接连结起来，此处可将片段❸中计算得出得多个文本连结起来，这样 N2 单元格才能显示最终的计算结果。

这是因为 Excel 中数组计算结果可以时多个值，但每个单元格只能显示其中的一个，如果数组公式范围内的单元格数不足，那溢出的内容将无法显示。

如果此处我们不使用 CONCAT 函数，那么 N2 单元格将只显示数组结果的第一个值。

而如果选择同一列足够多个的单元格区域再输入上述数组公式，按三键运算，则可以达到分行单独显示所有众数文本的效果。

像上例中，不重复的文本数量不多，我们也可以使用 CHOOSE 函数来设置公式，更易于理解。

多文本众数公式-CHOOSE

N2 公式如下：

公式说明：

{"东莞";"佛山";"广州"}是唯一值数组，函数 MATCH 将 B2:M2 转化为其在唯一数组中出现位置的序数值。

MODE.MULT 函数返回这组位置序数值的多个众数，再通过 CHOOSE 函数索引对应的文本，最后再使用 CONCAT 函数完成多文本的连结，从而在 N2 中完整展示所有众数文本。

在 Excel 2021 以上版本中，利用 CHOOSE 函数提供的思路，我们可以用UNIQUE 函数来解决唯一值数组生成的问题，从而对公式四进行优化，使之能够更好地适应唯一值较多的情况。

UNIQUE 生成唯一数组取众数

N2 公式如下：

公式说明：

使用 UNIQUE 函数生成唯一值数组后，通过 MATCH 匹配 B2:M2 中每个值在唯一值中的位置值，MODE.MULT 获取所有众数后，由 INDEX 函数在唯一值数组中索引对应值，最后由 CONCAT 函数将所有众数连结，赋予 B2 单元格，避免溢出。

如不使用 CONCAT 函数，Excel2021 也会自动将溢出内容显示在下方单元格中。

此处，由于 UNIQUE 函数的存在，INDEX 函数的参数 2 限制被解除，各中原由小伙伴们可自行深究，此处就不挖了。

以上，就是小花分享的众数问题进阶公式，包含：

❶ 使用 MODE.MULT 返回多个众数值；

❷ 由 INDEX+N+IF+MODE.MULT+MATCH 构成的多文本众数公式；

❸ 使用 CHOOSE 和唯一数组构建的简易公式；

❹ 新函数 UNIQUE 优化公式

除了上述公式，你还知道哪些解决众数问题的 Excel 函数公式，不妨留言与我们分享吧。

如果你想利用空闲时间学点 Excel 技巧，让工作更轻松，我推荐你加入秋叶 Excel 3 天集训营~

在秋叶 Excel 集训营，每天学习大概 30 分钟，从日常的功能出发，全程演示，一课一练，夯实进阶每一步。

秋叶 Excel 3 天集训营

现在只需 0 元

每天学习 30 分钟

你也有可能成为 Excel 高手！

现在就扫码报名吧！

以上内容包含广告