左侧为数据源,要求:按姓名进行求和,如图所示: 解析: 求和的关键在于区域的判断,分三步(开始行+结束行+区域求和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,群主会拉你入群 |
|