Excel众数上下限公式全解析：从基础MODE到动态数组
更新时间：2023年10月 | 阅读时间：约8分钟
什么是众数及其在Excel中的基础应用
在统计学中，众数（Mode）是指一组数据中出现次数最多的数值。它反映了数据的集中趋势，特别是在处理分类数据或非正态分布数据时，众数往往比平均值和中位数更具代表性。例如，在服装销售中，了解最畅销的尺码（众数）比计算平均尺码更有指导意义。
1.1 Excel中的众数函数
Excel提供了三个主要的众数计算函数，它们在处理逻辑和兼容性上有所不同：
| 函数名称 | 适用版本 | 功能描述 | 多众数处理 |
|---|---|---|---|
| MODE | Excel 2003及以前 | 返回数据集中的众数。如果数据没有重复值，返回错误值#N/A。 | 仅返回第一个找到的众数（通常是最小值） |
| MODE.SNGL | Excel 2010+ | 功能同MODE，但已弃用，建议替换为MODE.SNGL以提高代码可读性。 | 仅返回一个众数 |
| MODE.MULT | Excel 2010+ | 返回数据集中所有众数的垂直数组。需要作为数组公式输入。 | 返回所有众数 |
尽管MODE.MULT功能强大，但它有一个显著的局限性：它返回的是一个数组，且在旧版Excel中需要复杂的数组公式操作。更重要的是，当我们需要从多个众数中提取“最大众数”或“最小众数”时，MODE.MULT本身并不能直接提供这个逻辑，我们需要借助更底层的数组公式来实现，这就是众数上下限公式存在的意义。
多众数场景下的核心痛点
在实际业务分析中，数据往往呈现出“多峰分布”的特征。例如，某商场销售两种不同价位的手机，导致价格数据中出现两个明显的峰值。此时，简单的MODE函数只能返回其中一个，这可能导致决策偏差。
场景一：价格区间分析
数据：10, 10, 20, 20, 30
众数：10 和 20
痛点：如果我们要找“最主流的高价位”，MODE返回10，这不符合“高价位”的语境。我们需要众数上限（20）。
场景二：客户满意度评分
数据：1, 2, 2, 3, 3, 3, 4, 4, 5
众数：3 是唯一众数。但在某些噪声数据中，可能出现 2,2,3,3,4,4，此时众数为2,3,4。
痛点：如果我们要评估“最低的好评率”，可能需要2（假设2代表一般，3代表好，4代表很好，这里仅为示例逻辑）。我们需要众数下限。
场景三：库存补货策略
数据：多种SKU的销量，多个SKU销量并列第一。
痛点：仓库空间有限，优先补货销量众数中体积最大的SKU，或者体积最小的SKU以节省空间。这需要结合其他条件筛选众数。
为了解决这些痛点，我们需要构建能够识别所有众数，并从中提取最大值或最小值的高级数组公式。
众数上下限公式深度拆解
以下公式适用于Excel 2003及以上版本（作为数组公式使用），是解决多众数问题的经典方案。假设数据区域为 A2:A100。
3.1 获取众数最大值（众数上限）
这个公式的逻辑是：首先找出所有出现次数等于最大出现次数的数值，然后在这些数值中取最大值。
公式原理解析：
MODE(A2:A100)：首先计算数据集中的众数。如果有多个众数，MODE函数返回其中最小的一个。假设众数是10和20，MODE返回10。COUNTIF(A2:A100,A2:A100)：对数据集中的每一个值进行计数，生成一个数组，表示每个值出现的次数。例如{3, 5, 3, 2, ...}。COUNTIF(...) = MODE(...)：将每个值的出现次数与MODE返回的众数次数进行比较。注意，这里有一个隐含逻辑：MODE返回的是值，而COUNTIF返回的是次数。我们需要比较的是“出现次数”。
⚠️ 修正说明：上述经典公式在严格意义上存在逻辑陷阱。MODE函数返回的是“值”，而不是“出现次数”。因此，MODE(A2:A100) 返回的是数值（如10），而 COUNTIF 返回的是次数（如5）。直接比较 次数 = 值 是错误的。
正确的经典数组公式应为：
正确原理解析：
MAX(COUNTIF(A2:A100,A2:A100))：找出数据集中最高的出现频次（例如5次）。COUNTIF(A2:A100,A2:A100)=MAX(...)：创建一个逻辑数组，如果某个值的出现次数等于最高频次，则为TRUE，否则为FALSE。IF(..., A2:A100)：如果条件为TRUE，则保留该值，否则返回FALSE。MAX(...)：在所有保留的值（即所有众数）中，取最大值。这就是众数上限。
3.2 获取众数最小值（众数下限）
逻辑与上限公式完全一致，只是最外层函数由MAX变为MIN。
3.3 操作演示
| 步骤 | 操作 | 预期结果/说明 |
|---|---|---|
| 1 | 在A2:A10输入数据：1, 2, 2, 3, 3, 3, 4, 4, 5 |
众数为3（出现3次），2和4出现2次。此例无多众数。 |
| 2 | 修改数据为：1, 2, 2, 3, 3, 4, 4, 5 |
众数为2和4（均出现2次）。MODE返回2。 |
| 3 | 输入众数上限公式 | 返回4。因为2和4都是众数，MAX取4。 |
| 4 | 输入众数下限公式 | 返回2。因为2和4都是众数，MIN取2。 |
注意：输入上述数组公式后，必须按 Ctrl + Shift + Enter 键，Excel会自动在公式两端加上大括号 {}，表示这是一个数组公式。
现代Excel的高级解决方案
随着Excel版本的迭代，微软引入了动态数组函数，使得众数上下限的计算变得更加直观和强大。
利用 MODE.MULT 和 SORTBY
在Excel 365中，MODE.MULT 可以直接返回所有众数的动态数组。结合 SORT 或 SORTBY 函数，可以轻松提取上下限。
如果只需要上限：
优势：无需数组公式快捷键，公式简洁，易读性强。
UNIQUE + COUNTIF + FILTER 组合
这是一种更通用的方法，适用于需要自定义复杂条件的场景。
优势：逻辑步骤清晰，便于调试。可以轻易扩展为“出现次数大于N的众数”等复杂逻辑。
Power Query 方法
对于大数据集或需要定期重复处理的任务，Power Query是最佳选择。
- 将数据导入Power Query。
- 添加一列“计数”，使用
List.Count(List.Select([GroupedColumn], each _ = [CurrentRow]))或分组依据统计频率。 - 按“频率”降序排序。
- 筛选出频率等于最高频率的行。
- 对这些行按“数值”排序，提取第一个和最后一个。
优势：处理百万级数据性能优异，无需编写复杂公式，自动化程度高。
Excel众数函数演进时间轴
了解Excel函数的发展历史，有助于我们选择最适合当前环境的解决方案。
MODE 函数的诞生
引入了MODE函数，解决了基本的众数计算问题。但仅支持返回单个众数，且对多众数情况处理模糊。
统计函数专业化
微软将MODE拆分为MODE.SNGL（单众数）和MODE.MULT（多众数）。这是一个巨大的进步，允许用户直接获取所有众数，但需要数组公式支持，对新手不友好。
动态数组的预演
虽然尚未完全引入动态数组，但MODE.MULT的使用体验略有改善，错误提示更加明确。
动态数组革命
引入了UNIQUE, FILTER, SORT 等动态数组函数。MODE.MULT 不再需要数组公式输入，直接返回溢出结果。结合LET函数，公式编写变得极其灵活和高效。
常见问题解答 (FAQ)
是的，传统的MODE函数（包括MODE.SNGL）在遇到多个众数时，默认只返回其中最小的那个众数值。如果需要获取所有众数或特定条件的众数（如最大众数），需要使用数组公式或MODE.MULT函数。
MODE.MULT函数是Excel 2010及以后版本引入的函数，它可以返回数据集中的所有众数作为一个数组。但是，它要求用户将其作为数组公式输入（在旧版中），并选中与结果数量相匹配的连续单元格区域，否则可能只显示第一个结果或报错。在Excel 365中，它支持动态数组，直接输入即可溢出显示所有结果。
在Excel 365和Excel 2021中，数组公式支持动态数组。如果公式结果需要占据多个单元格，而目标区域被其他数据占用，就会显示#SPILL!错误。请确保公式下方有足够的空白单元格，或者修改公式以返回单个值（如使用INDEX或MAX/MIN包装）。
MODE函数会自动忽略文本和逻辑值，但会忽略空单元格。如果数据中包含错误值（如#N/A），MODE函数会返回错误。建议先用IFERROR函数清理数据，或者在公式中加入IF(ISNUMBER(...))判断，确保只处理数值。
在Excel 365中，使用 MODE.MULT 配合 SORT 或 INDEX 是最简便的方法，且不需要按Ctrl+Shift+Enter。例如：=SORT(MODE.MULT(A2:A100),,1) 可以直接得到排序后的所有众数，第一个是最小众数，最后一个是最大众数。