讓同事傻眼的技能：10個Excel 數(shù)組公式，用過一次就徹底上癮??！

wangweiqin168 2024-07-14 發(fā)布于浙江

展開全文

在日常的Excel數(shù)據(jù)處理中，我們經(jīng)常會遇到一些復(fù)雜的計算和操作需求。

而單一的Excel函數(shù)可能無法滿足這些需求，這時就需要借助數(shù)組公式來解決問題了。

數(shù)組公式是Excel中強大且靈活的功能之一，通過合理運用數(shù)組公式，我們可以實現(xiàn)條件求和、在混合文本里提取指定內(nèi)容，等復(fù)雜的數(shù)據(jù)處理任務(wù)，大大提高工作效率。

今天我們一起來探索數(shù)組公式的奧秘吧！內(nèi)容干貨十足，記得右上角收藏，有備無患哦~

SUM+SUMIF

此函數(shù)組合用于條件求和，如下圖，輸入公式為：

=SUM(SUMIF(A2:A5,{'球球';'青青'},C2:C5))

SUM+COUNTIF

這兩個函數(shù)結(jié)合，可用來統(tǒng)計不重復(fù)的姓名個數(shù)，如下圖，輸入公式為：

=SUM(1/COUNTIF(A2:A11,A2:A11))

之后要按Ctrl+Shift+Enter組合鍵，即可完成~

COUNTIF(A2:A11,A2:A11)：對區(qū)域進行統(tǒng)計判斷~
1/COUNTIF(A2:A11,A2:A11)：將重復(fù)值刪除。若僅出現(xiàn)一個值，1除以1即為1；若出現(xiàn)兩個則1除以2為1/2，兩個1/2求和也為1。相當(dāng)于得出不重復(fù)的姓名個數(shù)~

VLOOKUP+IF

使用VLOOKUP函數(shù)查找時，如果查找值不在第一列的話查找會出錯，這時結(jié)合IF函數(shù)就可以完成查找，輸入公式：

=VLOOKUP(E2,IF({1,0},B2:B5,A2:A5),2,0)

?IF({1,0},B2:B5,A2:A5)

{1,0}是一個由數(shù)字1和0構(gòu)成的常量數(shù)組，分別用1和0作為IF函數(shù)的第1個參數(shù)進行計算，把B2:B5的值放在1的位置，把A2:A5的值放在0的位置，重構(gòu)一個2列4行的數(shù)組~
所以，這里只是把原B列放在查找區(qū)域的第1列，A列放在第2列，IF({1,0}把兩列值轉(zhuǎn)換位置，實現(xiàn)了VLOOKUP的從左到右查找~

VLOOKUP+MATCH

此函數(shù)組合多用于交叉查詢，輸入公式：

=VLOOKUP(G2,A1:E4,MATCH(H2,A1:E1,0),0)

1.先用MATCH(H2,A1:E1,0)確定姓名所在的列~

2.之后再使用VLOOKUP函數(shù)查找引用~

INDEX+MATCH

這一對組合函數(shù)，也是多用于數(shù)據(jù)查詢引用，輸入公式：

=INDEX(B2:D5,MATCH(F2,A2:A5,0),MATCH(G1,B1:D1,0))

1.第一個MATCH函數(shù)：查找青青在【A2:A5】單元格區(qū)域中的位置，返回數(shù)字3~

2.第二個MATCH函數(shù)：查找數(shù)學(xué)在【B1:D1】單元格區(qū)域中的位置，返回數(shù)字2~

3.將兩個MATCH函數(shù)返回的結(jié)果，作為INDEX函數(shù)的第二、第三個參數(shù)，表示返回【B2:D5】單元格區(qū)域中第3行、第2列的值，即91~

INDEX+SMALL+IF

這個函數(shù)組合常用于獲取滿足條件的多個值~

輸入公式1：

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

輸入公式2：

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

?公式原理：

SMALL函數(shù)：用于定位所有E2在A列中的位置（從小到大）~
4^8是一個比較大的數(shù)：在IF公式中若單元格區(qū)域A1:A13的值等于E2，即顯示E2在A列里的行號，若不等于則顯示一個較大的數(shù)~
SMALL函數(shù)獲得行號之后，再結(jié)合INDEX函數(shù)一對多查找所需的值~
最后的&''是將結(jié)果轉(zhuǎn)換為文本格式，確保在沒有匹配結(jié)果的時候顯示為空字符串~