如何用Excel找出重复项中的最大值？

Question

在处理销售数据或学生成绩等场景时，常需从存在重复项的数据中找出每个重复组内的最大值。例如，多个区域有多次销售额记录，如何快速提取每个区域的最高销售额？许多用户尝试使用基础筛选或简单公式（如MAX）但无法按类别分组求最大值。常见问题在于：如何结合IF函数与数组公式，或利用SUMPRODUCT、MAXIFS（适用于Excel 2019及Office 365）等函数，精准识别并返回各重复项对应的最大数值？同时，在不使用复杂VBA的前提下，如何通过辅助列或动态数组公式高效实现该功能？

Answer 1

一、基础概念：理解分组求最大值的核心需求

在处理销售数据或学生成绩等结构化数据时，经常面临“重复项”与“聚合统计”的挑战。例如，某公司在全国多个区域设有分支机构，每个区域每月上报多次销售额，我们需要提取每个区域的最高单次销售额。

若仅使用MAX()函数，将返回全局最大值，无法实现按类别（如区域）分组后的局部最大值计算。因此，必须引入条件判断与数组运算机制。

典型数据结构如下表所示：

区域	销售人员	销售额（万元）
华北	张三	85
华东	李四	92
华北	王五	96
华南	赵六	78
华东	钱七	98
华北	孙八	89
华南	周九	83
华东	吴十	95
华北	郑一	102
华南	陈二	80
华东	黄三	99

目标是生成一个结果集，列出每个区域对应的最大销售额。

二、进阶方法：利用数组公式结合IF实现分组最大值

对于Excel 2016及更早版本，不支持MAXIFS函数，此时可采用数组公式技术。核心思路是使用IF函数筛选特定区域的数据，再嵌套MAX函数求最大值。

假设A2:C12为数据区域，“区域”在A列，“销售额”在C列。在E列列出唯一区域（如E2:E4分别为华北、华东、华南），则F2中输入以下公式并按<kbd>Ctrl+Shift+Enter</kbd>确认：

{=MAX(IF(A$2:A$12=E2, C$2:C$12))}

该公式含义为：如果A列等于当前E2中的区域，则返回对应的C列销售额，然后取其中最大值。由于是数组运算，需以数组形式提交。

此方法虽有效，但存在维护成本高、易出错等问题，尤其当数据量大时性能下降明显。

三、现代函数方案：MAXIFS的高效应用

从Excel 2019和Office 365开始，Microsoft引入了MAXIFS函数，极大简化了条件最大值查询过程。

语法格式为：MAXIFS(max_range, criteria_range1, criteria1, ...)

仍以上述数据为例，在F2单元格输入：

=MAXIFS(C$2:C$12, A$2:A$12, E2)

该公式直接返回A列等于E2所指定区域时，C列中的最大值。无需数组输入，公式自然下拉即可完成所有区域的最大值提取。

优势在于简洁、高效、易于调试，适合大规模数据分析场景。

四、灵活替代方案：SUMPRODUCT实现逻辑判断

在不支持MAXIFS且不愿使用数组公式的环境中，SUMPRODUCT可作为折中选择。

其原理是通过布尔逻辑构造权重矩阵，筛选目标数据子集后求最大值。示例公式如下：

=SUMPRODUCT(MAX((A$2:A$12=E2)*(C$2:C$12)))

解释：(A$2:A$12=E2)生成真假数组，乘以销售额列后，非目标区域变为0，再用MAX提取最大值。注意此处MAX作用于数值数组，而非条件判断。

虽然不如MAXIFS直观，但在兼容性要求高的环境中仍具实用价值。

五、自动化与动态化：结合UNIQUE与FILTER构建动态数组模型

在Excel 365中，借助动态数组函数可完全摆脱辅助列束缚，实现全自动结果输出。

使用UNIQUE提取所有唯一区域，再配合FILTER和MAX进行逐组处理。例如：

=LET(
        region, A2:A12,
        sales, C2:C12,
        uniqueReg, UNIQUE(region),
        maxSales, BYROW(uniqueReg, LAMBDA(r, MAX(FILTER(sales, region=r)))),
        HSTACK(uniqueReg, maxSales)
    )

该LET结构清晰地定义变量，并通过BYROW对每个唯一区域应用LAMBDA函数，最终用HSTACK横向合并区域与最大值。

输出结果自动溢出至相邻单元格，无需手动拖拽，真正实现“一次设置，永久生效”。

六、流程可视化：分组求最大值的决策路径图

以下是根据不同Excel版本与功能支持情况选择合适方法的决策流程图：

graph TD
            A[开始] --> B{是否使用Excel 365/2019?}
            B -- 是 --> C[优先使用MAXIFS]
            B -- 否 --> D{是否允许数组公式?}
            D -- 是 --> E[使用MAX+IF数组公式]
            D -- 否 --> F[尝试SUMPRODUCT模拟]
            C --> G[可进一步结合UNIQUE+FILTER构建动态数组]
            E --> G
            F --> G
            G --> H[输出各组最大值]

该流程体现了从环境适配到技术选型的系统性思维，适用于企业级报表自动化设计。