左侧为数据源,要求:按姓名进行求和,如图所示:
解析:
求和的关键在于区域的判断,分三步(开始行+结束行+区域求和SUM函数),以单元格J2为例说明:
第一、开始行查找,利用MATCH函数的精确查找
开始行=MATCH(E2,A:A,0)=2,即要查找的A1值在A列中的第2行出现
第二、结束行查找,这个相对麻烦些,结合INDIRECT函数及数组运算来实现;它的原理 是在开始行再下一行开始,选取较大的区间(目的:构建一个较大的数组,此区间要大于数据源所在的区间,此区间可根据实际情况进行选取):
1、构建区间:INDIRECT('a'&MATCH(E2,A:A,0)+1 & ':a20')=INDIRECT('a'& 2+1 & ':a20')=INDIRECT('a3:a20')
2、将此区间进行转化,公式=INDIRECT('a3:a20')<>'',返回结果是{FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE}
其目的在于构建一个TRUE与FALSE的数组,即当单元格为空时返回FALSE,单元格为非空时返回TRUE
3、在构建的区间数组中查找第一次出现的非空单元格,即查找TRUE所处的位置
公式=MATCH(1=1,INDIRECT('a3:a20')<>'',0)=MATCH(TRUE,{FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE;FALSE;TRUE;FALSE;TRUE;FALSE;FALSE;FALSE},0)=2
即下一次出现非空单元格的位置=开始行+2=2+2=4
4、综上可知:结束行=下一次出现非空单元格的位置向上偏移一个单元格=4-1=3
结束行的公式=MATCH(E2,A:A,0)+MATCH(1=1,INDIRECT('a'&MATCH(E2,A:A,0)+1 & ':a20')<>'',0)-1
重要说明:查找的是从下一行开始起的第一个非空单元格,因此需要对最后一行数据A16的下一行即A17单元格 填写一个空格,即让单元格A17成为非空单元格,否则查找A16值时会出现错误!
第三、在B列构建求和区域,即对于B列的开始行与结束行区间进行求和(SUM函数),公式=SUM(INDIRECT('b'&MATCH(E2,A:A,0)&':b'&MATCH(E2,A:A,0)+MATCH(1=1,INDIRECT('a'& G2+1 & ':a20')<>'',0)-1))=SUM(INDIRECT('b2:b3')
公式输入后记得要同时按三个键:CTRL+SHIFT+回车键(它是数组运算)
更多内容请关注 微信公众号:PTEXCEL
想参与EXCEL交流的朋友,请加微信号:893316488,群主会拉你入群
联系客服