在Excel数据处理的世界里，统计求和是一项常见且关键的任务。今天，我们将聚焦于一个具有代表性的问题，如下图，求每科成绩都在80以上的最高总分。
这个问题的解决思路，不仅能帮助我们应对当下的挑战，还能为今后类似的题目提供宝贵的借鉴。接下来，就让我们一同探索几种由简入繁，但都十分有效的方法。
一、辅助列法：化繁为简的基础操作
对于这类多条件求和问题，添加辅助列是一种简单直接的方法。首先，我们在表格中添加一个辅助列，用于计算总成绩。此时，问题似乎变得一目了然。
在单元格 E2 中输入公式=MAX((A2:A14>80)*(B2:B14>80)*(C2:C14>80)*D2:D14)，然后按下三键回车（ Ctrl+Shift+Enter）。
这个公式遵循 “条件条件条件数值区域” 的结构。其中，“(A2:A14>80)”“(B2:B14>80)”“(C2:C14>80)” 分别表示三科成绩大于 80 的条件，通过乘法运算将这些条件组合起来，只有当三个条件都满足时，对应行的成绩才会参与最终的最大值计算。
二、SUBTOTAL函数法：无辅助列的灵活应对
当不允许添加辅助列时，SUBTOTAL 函数便派上了用场。在单元格 E2 中输入公式 =MAX((A2:A14>80)*(B2:B14>80)*(C2:C14>80)*SUBTOTAL(9,OFFSET($A$1:$C$1,ROW(A1:A13),0)))，同样按下三键回车。
与上一个公式相比，这里用 SUBSTOTAL 函数替代了单元格区域 D2:D14。
具体思路如下：
OFFSET($A$1:$C$1,ROW(A1:A13),0)部分，以$A$1:$C$1为基点，通过ROW函数依次分别向下移动 1、2、…、13 行，形成一个三维内存数组。由于内存数组是三维的，SUM函数无法直接对其求和，而SUBTOTAL函数则支持三维直接求和。最后，再通过MAX函数提取出满足条件的最大值。
三、简化条件的SUBTOTAL函数法：简洁高效的选择
如果觉得写三组条件过于繁琐，我们还可以采用另一种SUBTOTAL函数的用法。在单元格E2中输入公式 =MAX((SUBTOTAL(5,OFFSET(A1:C1,ROW(A2:A14)-1,0))>80)*SUBTOTAL(9,OFFSET(A1:C1,ROW(A2:A14)-1,0)))，三键回车即可。
这里用到了两次SUBTOTA函数。第一个SUBTOTAL函数（参数为5）返回每一行的最小值，并与80作比较；第二个SUBTOTAL函数（参数为 9）则计算每一行的合计。通过这样的组合，先筛选出每科成绩都大于80的行，再计算这些行的总分，最后由MAX函数返回最大值。
四、MMULT函数法：公式精简的进阶之路
最后，我们介绍一种使用 MMULT 函数的方法，它能使公式更加简短。在单元格E2中输入公式=MAX((MMULT((A2:C14>80)*1,ROW(1:3)^0)=3)*MMULT(A2:C14,ROW(1:3)^0))，三键回车。
这个公式连续使用了两次MMULT函数。第一个MMULT函数通过将成绩大于 80 的条件矩阵（由 (A2:C14>80)*1 生成）与单位向量（由ROW(1:3)^0生成）相乘，判断每一行是否三科成绩都大于80（结果为3表示满足条件）。第二个MMULT函数则将成绩矩阵与单位向量相乘，计算出每一行的总成绩。最后，MAX函数从满足条件的行中返回最大值。
通过以上几种方法的介绍，相信大家对Excel多条件求最大和值的问题有了更深入的理解。无论是基础的辅助列法，还是进阶的函数应用，每种方法都有其独特的优势和适用场景。在实际工作中，我们可以根据具体需求和数据特点，灵活选择合适的方法，提升数据处理的效率和准确性。
评论 (0)