12個經(jīng)典函數(shù)組合

wisdom163 2019-11-01

展開全文

VLOOKUP + IF

用VLOOKUP函數(shù)進行查找時，如果查找值不在首列，會出現(xiàn)錯誤結(jié)果。這時需要結(jié)合IF函數(shù)來實現(xiàn)正常查找。如下圖表格，輸入公式：

=VLOOKUP(F2,IF({1,0},B2:B11,A2:A11),2,0)

說明：利用IF({I,0},區(qū)域1,區(qū)域2)對查找的數(shù)據(jù)區(qū)域進行重新構(gòu)建。

VLOOKUP + MATCH

這個函數(shù)組合經(jīng)常用來進行交叉查詢，如下圖表格，輸入公式：=VLOOKUP(A11,$A$1:$F$7,MATCH(B11,$A$1:$F$1,0),0)

說明：先用MATCH(B11,$A$1:$F$1,0)確定姓名所在列，再用VLOOKUP函數(shù)進行查找引用。

VLOOKUP + INDIRECT

VLOOKUP跨表或多表查詢時，經(jīng)常結(jié)合INDIRECT函數(shù)。如下圖表格，輸入公式：

=IFERROR(VLOOKUP($B2,INDIRECT(C$1&'!B:C'),2,0),'')

說明：這里用VLOOKUP函數(shù)和INDIRECT函數(shù)結(jié)合，對各個明細表B、C兩列數(shù)據(jù)查找區(qū)域進行動態(tài)引用。公式中還結(jié)合了IFERROR函數(shù)進行容錯處理。

SUM+SUMIF

這對函數(shù)組合用來進行條件求和，如下圖表格，輸入公式：

=SUM(SUMIF(A2:A11,{'李霞霞';'戴夢夢'},D2:D11))

SUM + COUNTIF

SUM函數(shù)結(jié)合COUNTIF函數(shù)使用，可以用來統(tǒng)計不重復(fù)個數(shù)。如下圖表格，輸入公式：

=SUM(1/COUNTIF(A2:A14,A2:A14))，按Ctrl+Shift+Enter組合鍵完成。

說明：

COUNTIF(A2:A14,A2:A14)：對每個單元格進行統(tǒng)計判斷；1/COUNTIF(A2:A14,A2:A14）：刪除重復(fù)值，假如只有出現(xiàn)一個值，1除以1就等于1，若是出現(xiàn)2個，那么1除以2等于1/2，所有1/2求和也等于1，相當于獲取不重復(fù)人數(shù)。

OFFSET+ MATCH

這對函數(shù)組合用來進行多條件查找引用。如下圖表格，輸入公式：

=OFFSET(A1,MATCH(H2,A2:A11,0),MATCH(G2,B1:E1,))

說明：先用MATCH函數(shù)分別定位出指定月份和產(chǎn)品在A2:A11和B1:E1區(qū)域中的位置，作為OFFSET函數(shù)的第2和第3個參數(shù)，然后以A1為基準位置偏移對應(yīng)的行數(shù)和列數(shù)即可。

INDEX + MATCH

這對函數(shù)組合，也是常用于數(shù)據(jù)的查詢引用。如下圖表格，輸入公式=INDEX(B2:B26,MATCH(D2,A2:A26,0))

說明：先判斷姓名在指定區(qū)域的位置，再結(jié)合INDEX函數(shù)獲取相應(yīng)的手機號。

INDEX + SMALL + IF

非常經(jīng)典的一個函數(shù)組合，被稱之為查找三劍客，用來獲取滿足條件的多個值。如下圖表格，輸入公式：

=INDEX(B:B,SMALL(IF($A$1:$A$11=$E$2,ROW($A$1:$A$11),4^8),ROW(A1)))&''

說明：

SMALL函數(shù)用來定位所有E2在A列中的位置(從小到大)4^8這里指的是一個比較大的數(shù)，在這個IF函數(shù)公式中，如果單元格區(qū)域A1:A11的值等于E2，就顯示E2在A列中所在的行號，如果不等于就顯示一個較大的數(shù)當我們利用SMALL函數(shù)得到行號之后，結(jié)合INDEX函數(shù)一對多查找需要的值最后的&''是用來進行容錯處理。

INDIRECT+MATCH

INDIRECT函數(shù)結(jié)合MATCH函數(shù)使用，可以實現(xiàn)逆向查詢。如下圖表格，輸入公式：

=INDIRECT('A'&MATCH('王五',C2:C8,0)+1)

說明：先用MATCH函數(shù)返回王五在查找區(qū)域中處于第4行，即'A'&MATCH('王五',C2:C8,0)+1返回的結(jié)果為A5，然后結(jié)合INDIRECT引用函數(shù)返回該單元格的引用。

LEFT/RIGHT + LEN + LENB

這兩對函數(shù)組合可以用來在混合文本中提取指定的內(nèi)容。如下圖表格，輸入公式：

=LEFT(C2,LENB(C2)-LEN(C2))

=RIGHT(C2,2*LEN(C2)-LENB(C2))

SUMPRODUCT + SUBSTITUTE

SUMPRODUCT函數(shù)結(jié)合SUBSTITUTE函數(shù)使用，可以用來對帶單位的數(shù)據(jù)進行求和統(tǒng)計。如下圖表格，輸入公式：

=SUMPRODUCT(SUBSTITUTE(C2:C11,'元','')*1)&'元'

說明：SUBSTITUTE(D2:D10,'元','')先將C列中的“元”全部替換為空值，乘以1將文本轉(zhuǎn)換為數(shù)值，再利用SUMPRODUCT函數(shù)求和。