【原】SUMIF函数和MMULT函数两种方法同证累计和值问题，值得你收藏！

EXCEL应用之家 2023-02-07 发布于上海

展开全文

送人玫瑰，手有余香，请将文章分享给更多朋友

动手操作是熟练掌握EXCEL的最快捷途径！

【置顶公众号】或者【设为星标】及时接收更新不迷路

小伙伴们好，今天要和大家分享一道有关“阀值”方面的题目。简单说，就是当某一条件达到一定的范围、程度后引发的其它操作。

这里我们不讨论如何引发后续的操作，我们讨论的重点放在如何确定达到了“阀值”，即如何确定满足了条件。

原题是这样子的：

根据左侧源数据的记录，计算出每个人达标的时间。要计算达标时间，这里一定要用到累加的方法。下面我们就一起来看看如何书写公式吧！

LOOKUP+SUMIF+OFFSET函数

OFFSET函数配合SUMIF函数可以实现累加的效果。

在单元格G2中输入公式“=LOOKUP(F2,SUMIF(OFFSET($B$1,,,ROW($1:$15)),E2,$C$1),$A$2:$A$16)”，并向下拖曳即可。

思路：

前面提到了，解决这个问题要用到累加的方法。OFFSET($B$1,,,ROW($1:$15))部分，为实现了数据的逐行累加创造条件。这里要特别注意，数据移动的基点是单元格B1
SUMIF(OFFSET($B$1,,,ROW($1:$15)),E2,$C$1)部分，以OFFSET函数返回的内存数组为条件区域，对C列按条件求和。其结果为{0;102;102;102;1102;1102;1102;2602;2602;2602;3302;3302;3302;4102;4302}，正好是对A的逐行累加
LOOKUP函数二分法返回正确答案

请注意这里逐行累加的基点是B1，对应源数据中的第一个日期。请大家注意理解！

INDEX+MATCH+MMULT函数

MMULT函数有一个常用技巧也能实现累加的效果。

在单元格G2中输入公式“=INDEX($A$2:$A$16,MATCH(1=1,MMULT(N(ROW($1:$15)>=TRANSPOSE(ROW($1:$15))),($B$2:$B$16=E2)*($C$2:$C$16))>=F2,))”，三键回车并向下拖曳即可。

思路：

ROW($1:$15)>=TRANSPOSE(ROW($1:$15)部分，返回一个TURE值的三角型矩阵，可以实现数据的累加
N()部分将TURE转换为1， FALSE转换为0
($B$2:$B$16=E2)*($C$2:$C$16)部分是满足条件的求和区域。它是一个15行1列的内存数组
MMULT(N(ROW($1:$15)>=TRANSPOSE(ROW($1:$15))),($B$2:$B$16=E2)*($C$2:$C$16))部分对矩阵求和，实现了对数据的逐行累加，其结果为{102;102;102;1102;1102;1102;2602;2602;2602;3302;3302;3302;4102;4302;4302}
上述结果和条件相比，并利用MATCH函数返回第一个TRUE的位置
INDEX函数返回相对应的日期

-END-

我就知道你“在看”