专栏名称: 秋叶Excel

和秋叶一起学Excel，免费获取Excel模板大全、插件合集！还有Excel真人视频、图文教程，助你轻松掌握Excel函数、图表、透视表……一起成为办公效率达人！

目录

相关文章推荐

发现新西兰 · 奥克兰北岸发生持刀伤人事件，大批武装警察封路 · 4 天前

发现新西兰 · “新西兰签证突然被取消！”中国游客遭遇大无语 ... · 4 天前

发现新西兰 · 一瓶可乐，让华人男子被新西兰酒店拉黑，“法庭见！” · 3 天前

发现新西兰 · 大S数亿身家谁继承？孩子谁照顾？海外华人重组 ... · 3 天前

发现新西兰 · 突发：大S流感去世，年仅48岁 · 5 天前

51好读 › 专栏 › 秋叶Excel

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

秋叶Excel · 公众号 · · 2024-03-04 11:36

正文

本文作者：小花

本文编辑：卫星酱

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

你还记得吗？

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

多数值众数公式

N3 单元格数组公式如下：

=INDEX(B2:M2,MODE(MATCH(B2:M2,B2:M2,0)))

公式说明：

先使用 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 天集训营

课程原价 99 元

现在 免费报名！

↓ ↓ ↓

多数值众数公式

D2:D3 公式如下：

{=MODE.MULT($B$2:$B$11)}

公式说明：

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

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

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

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

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

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

N2 公式如下：

=CONCAT(INDEX(B2:M2,N(IF(1,MODE.MULT(MATCH(B2:M2,B2:M2,0))))))

公式说明 ：

❶ 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 公式如下：

=CONCAT(CHOOSE(MODE.MULT(MATCH(B2:M2,{"东莞";"佛山";"广州"},0)),"东莞","佛山","广州"))

公式说明：

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

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

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

UNIQUE 生成唯一数组取众数

N2 公式如下：

=CONCAT(INDEX(UNIQUE(B2:M2),MODE.MULT(MATCH(B2:M2,UNIQUE(B2:M2),0))))

公式说明：

请到「今天看啥」查看全文

推荐文章

发现新西兰 · 奥克兰北岸发生持刀伤人事件，大批武装警察封路

4 天前

发现新西兰 · “新西兰签证突然被取消！”中国游客遭遇大无语事件；“啥时候给中国免签？”总理回应了

4 天前

发现新西兰 · 一瓶可乐，让华人男子被新西兰酒店拉黑，“法庭见！”

3 天前

发现新西兰 · 大S数亿身家谁继承？孩子谁照顾？海外华人重组家庭须知

3 天前

发现新西兰 · 突发：大S流感去世，年仅48岁

5 天前

张德芬空间 · 好的婚姻，看厨房

8 年前

西子湖畔 · 要火！“小拳拳捶你胸口”惠州话版、客家话版出炉，笑S了

8 年前

虹膜 · 这辈子一定要去一次西班牙，去看望威尔斯和阿莫多瓦

7 年前

理想国imaginist · 每个城市都有一首属于自己的歌

7 年前

深夜发媸 · 加起来100岁的人了还这么年轻，王菲刘嘉玲吃防腐剂了吗？ | 明星照妖镜

7 年前

Sov5搜索 · 小百科 · 今天看啥 · 移动版

51好读 - 好文章就要读起来!