介紹幾個Excel萬能套路公式，包括求和、查找、拆分提取、去重統(tǒng)計、排名等方面。

隔列匯總

按條件隔列累加，用SUM或者SUMPRODUCT方便。

=SUMPRODUCT((B25:E25="a")*B26:E34)

=SUM((B25:E25="a")*B26:E34)(低版本要三鍵輸入)

說明：將條件判斷作為其中一個數(shù)組，然后乘以求和區(qū)域，最后累加。

統(tǒng)計不重復個數(shù)

=SUMPRODUCT(1/COUNTIF(A38:A46,A38:A46))

說明：先用COUNTIF獲得每個數(shù)據(jù)的重復次數(shù)；然后用1除以各個重復次數(shù)；最后累加。

身份證等超長數(shù)字查重

超過15位的數(shù)字在查重的時候用COUNTIF不好使，用SUMPRODUCT方便。

=IF(SUMPRODUCT(($B$50:$B$57=B50)*1)>1,"重復","")

說明：用比較符號進行判斷可以規(guī)避數(shù)字超過15位最后幾位都當作0進行處理的不足，從而準確判斷是否存在重復。

中國式排名

中國式排名即便有并列，名次也是連續(xù)的。

說明：實際就是統(tǒng)計大于等于當前成績的不重復分數(shù)的個數(shù)。所以把統(tǒng)計不重復數(shù)公式的分子1變成了一個條件判斷。

水平和垂直雙向查找

一個條件需在水平方向上查找，一個條件需在垂直方向上查找。

低版本：=VLOOKUP($F$72,$A$72:$D$79,MATCH(G71,$A$71:$D$71,0),)

高版本：=XLOOKUP($F$72,$A$72:$A$79,XLOOKUP(G71,$B$71:$D$71,$B$72:$D$79))

提取漢字和字母

如果數(shù)據(jù)中只有字母和漢字，可以判斷是否比“啊”大來拆分漢字和英文。

提取英文：=TRIM(CONCAT(IF(MID(A83,ROW($1:$50),1)>="啊"," ",MID(A83,ROW($1:$50),1))))

提取漢字：=TRIM(CONCAT(IF(MID(A83,ROW($1:$50),1)<"啊"," ",MID(A83,ROW($1:$50),1))))

說明：在Excel中所有中文大于英文，中文中最小的又是“啊”字，因此可以逐個拆出字符與啊字比較，從而判斷出是英文還是中文。

提取任意位置的一串數(shù)字

提取字符中任意位置的一串數(shù)字。

=CONCAT(IFERROR(--MID(A89,ROW($1:$50),1),""))

說明：逐個提取字符并做雙負運算，數(shù)字保留，不是數(shù)字的變成空。

多對一查找

多對一查找，低版本用LOOKUP套路最方便；高版本就用XLOOKUP的條件合并。

=LOOKUP(1,0/((A95:A102=E95)*(B95:B102=F95)),C95:C102)

=XLOOKUP(E95&F95,A95:A102&B95:B102,C95:C102)

說明：用LOOKUP多條件查找，主要就是把多個條件判斷進行相乘，并用在LOOKUP精確查找的套路中。

本文配套的練習課件請加入QQ群：902294808下載。

做Excel高手，快速提升工作效率，部落窩教育Excel精品好課任你選擇！

掃下方二維碼關(guān)注公眾號，可隨時隨地學習Excel：

相關(guān)推薦：

Xlookup用法12例

多區(qū)域查找用R1C1

LOOKUP-LEFT-MID-FIND無往不勝

3種關(guān)于連續(xù)的統(tǒng)計方法

版權(quán)申明：

本文作者小窩；部落窩教育享有稿件專有使用權(quán)。若需轉(zhuǎn)載請聯(lián)系部落窩教育。