[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，群主會拉你入群