[EXCEL]MATCH INDIRECT函数综合应用之合并单元格查找求和

左侧为数据源,要求:按姓名进行求和,如图所示:

解析:

求和的关键在于区域的判断,分三步（开始行+结束行+区域求和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，群主会拉你入群