使用 LARGE 和 SMALL 查找最大值与最小值

MAX 会返回单个最大值，MIN 会返回单个最小值。但如果您想查找第 2 大的值或第 3 小的值，该怎么办？

这正是 LARGE 和 SMALL 的用途。它们可以从一个范围中提取第 n 大或第 n 小的值，这是生成前 N 名和后 N 名报告的基础。

LARGE(range, k) 会返回第 k 大的值。第二个参数 k 用于指定位置：1 表示最大值，2 表示第二大值，以此类推。

因此，LARGE(A2:A20,1) 等于 MAX(A2:A20)，而 LARGE(A2:A20,2) 会返回第二大值。

SMALL(range, k) 与 LARGE 的作用相反，它从最小值一侧开始计算。SMALL(A2:A20,1) 等于 MIN(A2:A20)，而 SMALL(A2:A20,3) 会返回第三小的值。

您可以使用 SMALL 找出表现最差的对象、价格最低的选项或数据集中的最早日期。

要列出前三个值，可以将三个 k 分别为 1、2、3 的 LARGE 公式依次排列。更简洁的方法是引用一个辅助数字，这样就可以向下填充公式。

如果 D2 单元格中为 1，D3 中为 2，D4 中为 3，那么将下面这个公式向下填充后，就会生成完整的前三名列表。

您可以使用 ROW 函数自动生成 k，因此无需辅助列。ROW()-1 在第一行得到 1，在下一行得到 2，依此类推。

向下填充后，它会构建一个排名列表，无需额外设置。请调整偏移量，使其与列表的起始位置相匹配。

在新版 Excel 和 Google Sheets 中，您可以将 SEQUENCE 作为 k 参数，从而用一个公式溢出整个前 5 项列表。SEQUENCE(5) 会生成 1、2、3、4、5。

随后 LARGE 会一次返回五个值，并自动向下溢出。一个公式即可得到完整的排名列表。

一个常见的业务问题是：我的前三位客户贡献了多少？将 LARGE 与 SUM 和 SEQUENCE（或数组常量）结合起来，对前 N 个值求总和。

下方公式会将该区域中最大的三个值相加，在一个单元格中给出前三项的合计贡献。

如果 k 小于 1，或大于区域中数字的个数，LARGE 和 SMALL 会返回 #NUM! 错误。

例如，在只有 19 个数字的列表中请求第 30 大的值会失败。在报表中，请将调用封装在 IFERROR 中，以防止出现这种情况。

只知道数值不如同时知道是谁取得了这个数值有用。将 LARGE 与 INDEX 和 MATCH 结合起来，获取与最大值对应的姓名。

这会找出最大的销售额，定位其所在行，并从 A 列返回匹配的姓名，因此您的报表会显示“最佳销售员：Maria。”

与其他统计函数一样，LARGE 和 SMALL 只统计数字。区域中的文本和空白单元格会被跳过，因此不会影响哪个值是第 n 大。

这意味着您可以放心地将它们指向包含标题行的列。不过，请注意那些被意外存储为文本的数字，因为它们会被忽略，并可能改变 LARGE 或 SMALL 返回的值。

您在 B2:B20 中有每月销售额。管理层希望了解表现最佳和最差的三个月。

• 最佳三个月：=LARGE($B$2:$B$20,{1,2,3})
• 最差三个月：=SMALL($B$2:$B$20,{1,2,3})

将每个公式放在可以溢出的单元格中，您就能立即得到一份涵盖最佳和最差表现的概览，为仪表板提供数据。

测试您对 LARGE 和 SMALL 的理解。

现在您已经可以超越 MAX 和 MIN：

• LARGE(range,k) 返回第 k 大的值；SMALL(range,k) 返回第 k 小的值。
• k = 1 与 MAX / MIN 相同；增大 k 即可获取后续排名。
• 将 SEQUENCE 或类似 {1,2,3} 的数组传入，即可用一个公式构建或汇总前 N 项列表。
• 与 INDEX-MATCH 配对以标记值，并用 IFERROR 包裹以处理超出范围的 k。