HI,大家好,我是偏爱函数公式,爱用Excel图表管理仓库的大叔Mr赵~
有没有小伙伴喜欢做这样一件事:
为了让自己做的表格看起来美观,突显明了,直接在表格内合并单元格!
可是!就因为这个合并操作,给我们后面的数据统计增加了不小的难度~
如下图,根据B列合并的单元格内容,求后面数据的合计。
是不是感觉脑瓜子嗡嗡的?
其实要解决这个问题,对数组还不太熟悉的小伙伴,可以借助辅助列的方法倒是一个不错的选择。
方法1:辅助列方法。
首先在D2单元格输入如下公式,下拉填充:
=IF(B2='',D1,B2)
得到的效果,如下图D列:
这样就相当于把A列的数据都填充上了。
然后在F2单元格输入如下公式,下拉填充。
=SUMIF(D:D,E2,C:C)
得到的结果如下图F列,很轻松的搞定了!
接下来,我们就省掉辅助列,介绍几个数组公式的写法:
方法2:LOOKUP函数方法。
在F2单元格输入公式:
=SUM((LOOKUP(ROW($2:$17),ROW($2:$17)/(B$2:B$17<>''),B$2:B$17)=E2)*C$2:C$17)
▲ 左右滑动查看
公式解析:
ROW($2:$17)/(B$2:B$17<>''),如果单元格区域不为空就返回行号,否则返回错误值,得到如下图的一个垂直数组:
LOOKUP(ROW($2:$17),ROW($2:$17)/(B$2:B$17<>''),B$2:B$17):根据LOOKUP函数忽略错误值,返回小于等于查找值的最大值的特性,补全合并单元格中间的空单元格。得到如下图效果的数组:
最后用SUM函数条件求和,得到最终结果。
方法3:SUBTOTAL函数多维应用。
在F2单元格输入公式:
=LOOKUP(1,SUBTOTAL({3,9},OFFSET(B$1,MATCH(E2,B:B,)-1,{0,1},ROW(1:20))))
▲ 左右滑动查看
方法4:SCAN函数法。
在F2单元格输入公式:
=SUM((SCAN(,B$2:B$17,LAMBDA(x,y,IF(y='',x&y,y)))=E2)*C$2:C$17)
▲ 左右滑动查看
这两个公式以后再解释吧!
联系客服