你好，我是刘卓。欢迎来到我的公号，excel函数解析。今天来分享群友提供的一个案例，我将从基础到进阶的方法来展示下解题的过程，希望对你有所帮助。先来看案例，如下图所示，A2:I11是数据源，记录的是各班级加分和扣分的数据。现在的要求是计算加分减去扣分后，分数最高的班级。这里最高分班级是"六班"。
基础解法
先来看基础解法，首先添加一个辅助列。在J3单元格输入公式=SUMIF(B$2:I$2,"加分",B3:I3)-SUMIF(B$2:I$2,"扣分",B3:I3)，下拉填充，得到每个班级的最后得分。也就是用每个班级的总加分减去总扣分。如果你稍懂一点技巧的话，上面的公式也可以写为=SUM(SUMIF(B$2:I$2,{"加分","扣分"},B3:I3)*{1,-1})这个公式，结果是一样的，如下图所示。然后在L3单元格输入公式=INDEX(A3:A11,MATCH(MAX(J3:J11),J3:J11,))，即可得到最后的结果。这个公式的意思是用match查找最高分在J3:J11中的位置，然后用index返回A3:A11中相应位置的班级。这样，我们通过简单的两步就把问题解决了。这就是辅助列的好处，可以简化问题的难度。初学者强烈建议这个方法。
上面第二步查找最高分对应的班级时，先用max把最高分求出来，然后再用match查找最高分的位置，最后用index返回相应的班级。其实我们也可以用frequency直接定位最高分的位置，然后用lookup查找到这个位置返回相应的班级。
如下图所示，在K3单元格输入公式=FREQUENCY(-9^9,-J3:J11)，结果如K3:K12所示。可以看到在最高分1357的位置计数1，这样就定位到最高分的位置了。J3:J11前面加负号是为了将最大值1357变为最小值-1357，而-9^9是一个很小的数字，frequency会将其在最小值-1357的位置上计数1。然后通过lookup查找到该位置，返回相应的班级。0/frequency的部分如K列所示，作为lookup的第二参数。在第二参数中找第一参数0的位置，返回第三参数中相应的”六班“。公式为=LOOKUP(0,0/FREQUENCY(-9^9,-J3:J11),A3:A11)。lookup+frequency是好搭档，可以查找最大、最小值问题。以上是基础解法，都是通过辅助列完成的。下面看下不用辅助列是如何完成的。
进阶用法
如果你觉得自己基础用法掌握的差不多了，就可以考虑进阶用法了，不用辅助列，直接一步到位。免辅助列的方法之前也说过，那就是多维引用和mmult。
用辅助列的方法，是对单个班级求最后的得分。而用多维引用和mmult可以同时对多个班级求最后的得分，结果得到一个数组。
在L3单元格输入公式=LOOKUP(,0/FREQUENCY(-9^9,-MMULT(SUMIF(B2:I2,{"加分","扣分"},OFFSET(B2:I2,ROW(1:9),)),{1;-1})),A3:A11)，搞定。外层的lookup+frequency和最开始说的是一样的，这里主要看sumif的部分SUMIF(B2:I2,{"加分","扣分"},OFFSET(B2:I2,ROW(1:9),))。
sumif的第一、第二参数没什么好说的，一个是条件区域，一个是条件。主要是第三参数，它是一个多维引用。使用offset函数将B2:I2分别向下偏移1，2，3……9行，得到了由B3:I3,B4:I4,……，B11:I11这9个区域组成的多维引用。
SUMIF(B2:I2,{"加分","扣分"},OFFSET(B2:I2,ROW(1:9),))这部分的意思是分别计算这9个区域中，"加分"的总和，"扣分"的总和。结果是一个二维数组，如下图所示。然后用mmult将每个班级"加分"的总和减去"扣分"的总和，得到每个班级最后的得分。查找最高分对应的班级就用lookup+frequency。2.mmult
在L3单元格输入公式=LOOKUP(,0/FREQUENCY(-9^9,-MMULT(B3:I11,TRANSPOSE((B2:I2="加分")*2-1))),A3:A11)，按ctrl+shift+enter三键，完成。同样地，我们只看mmult那部分MMULT(B3:I11,TRANSPOSE((B2:I2="加分")*2-1))，也就是如何得到每个班级最后的分数。
mmult的第1参数就是B3:I11，第2参数用了transpose转置函数。(B2:I2="加分")*2-1这部分返回的结果为{1,-1,1,-1,1,-1,1,-1}。意思就是标题行是”加分“的返回1，是”扣分“的返回-1。
你也可以用if函数，IF(B2:I2="加分",1,-1)。加了transpose后，就变为了纵向数组{1;-1;1;-1;1;-1;1;-1}。这样是为了满足mmult的规则。mmult最后返回的结果如下图所示。https://pan.baidu.com/s/1JwhNnnpW3aFkB_jAx2t3Cw
本站仅提供存储服务，所有内容均由用户发布，如发现有害或侵权内容，请
点击举报。
