1.糾錯(cuò)

公式：=IFERROR(A2/B2,'有誤')

說(shuō)明：如果是錯(cuò)誤值則顯示為“有誤”，否則正常顯示。

糾錯(cuò)

2.統(tǒng)計(jì)兩表重復(fù)

公式：=COUNTIF(A:A,C2)

說(shuō)明：結(jié)果大于0說(shuō)明在另一個(gè)表中存在，0則不存在。

統(tǒng)計(jì)重復(fù)

3.模糊條件求和

公式：=SUMIF(A:A,D2&'*',B:B)

說(shuō)明：SUMIF/SUMIFS支持通配符”*”,根據(jù)模糊條件求和。

模糊條件求和

4.隔列求和

公式：=SUM(OFFSET(A3,,{1,3,5}))

說(shuō)明：A3單元格右邊第1個(gè)，第3個(gè)，第5個(gè)單元格求和

公式：=SUM(OFFSET(A3,,{2,4,6}))

A3單元格右邊第2個(gè)，第4個(gè)，第6個(gè)單元格求和

隔列求和

5.多表相同位置求和

公式=SUM(Sheet2:Sheet5!B2)

說(shuō)明：對(duì)Sheet1到Sheet5中的B2單元格求和。

多表相同位置求和

6.多條件判斷

公式：=IFS(B2<=60,'不及格',

B2<=80,'良好',

B2<=99,'優(yōu)秀',

B2=100,'人才')

說(shuō)明：從上往下依次判斷，條件滿足時(shí)輸出對(duì)應(yīng)的值，并終止判斷。

多條件判斷

7.多條件查詢

公式=INDEX(B2:C7,

MATCH(F1,A2:A7,0),

MATCH(F2,B1:C1,0))

INDEX+MATCH是多條件查詢中的經(jīng)典組合，兩個(gè)MATCH函數(shù)定位指定條件的坐標(biāo)，作為INDEX的兩個(gè)參數(shù)實(shí)現(xiàn)精準(zhǔn)查詢。

多條件查詢

8.關(guān)鍵字模糊查詢

VLOOKUP可支持通配符模糊查詢。

公式：=VLOOKUP('*'&D2&'*',A:B,2,0)

高版本EXCEL新增查詢函數(shù)XLOOKUP,可視為VLOOKUP的增強(qiáng)版本，同樣支持通配符模糊查詢。

公式：=XLOOKUP('*'&D3&'*',A:A,B:B,,2)

關(guān)鍵字模糊查詢

9.合并單元格內(nèi)容

公式：=TEXTJOIN(',',TRUE,

IF(B2:B9>2000,A2:A9,''))

說(shuō)明：IF函數(shù)篩選出滿足條件的水果，Textjoin將其連接，用逗號(hào)隔開(kāi)。

合并單元格內(nèi)容

10.求滿足條件的最?。ù螅┲?/strong>

公式：=MINIFS(B:B,A:A,'菠蘿')

說(shuō)明：滿足條件的最小日期

公式：=MAXIFS(B:B,A:A,'荔枝')

說(shuō)明：滿足條件的最大日期

最小（大）值

11.一堆數(shù)據(jù)中對(duì)正數(shù)（或負(fù)數(shù)）求和

公式：=SUMIF(A1:E10,'>0')

公式： =SUMIF(A1:E10,'<0')

條件求和

12.一堆數(shù)據(jù)中，統(tǒng)計(jì)正數(shù)（或負(fù)數(shù)）的個(gè)數(shù)

公式：=COUNTIF(A1:E10,'>0')

公式：=COUNTIF(A1:E10,'<0')

條件計(jì)數(shù)

13.自動(dòng)生成序列號(hào)

公式：A2=IF(B2<>'',ROW(A1),'')下拉填充

說(shuō)明：B列新增項(xiàng)目時(shí)，A列將自動(dòng)產(chǎn)生序列號(hào)。

自動(dòng)生成序列號(hào)

14.對(duì)篩選的數(shù)據(jù)求和

公式：=SUBTOTAL(9,B2:B11)

說(shuō)明：未篩選狀態(tài)下對(duì)所有數(shù)據(jù)求和，篩選狀態(tài)下只對(duì)篩選出來(lái)的數(shù)據(jù)求和。

未篩選求和

篩選后求和

15.提取年月日信息

公式：=YEAR(A1)

公式：=MONTH(A1)

公式：=DAY(A1)

提取年月日信息

16.字符替換

公式：=SUBSTITUTE(A2,'-','')

說(shuō)明：”-“替換為空，整理電話號(hào)碼

字符替換

17.截取指定字符之后的字符

公式：=MID(A2,FIND('-',A2)+1,100)

說(shuō)明：FIND函數(shù)的作用是返回”-“在字符串中的位置，從該位置開(kāi)始截取后面的所有字符。

截取字符

18.一列轉(zhuǎn)多列

公式：=INDEX(A1:A12,

SEQUENCE(4,3,1,1),1)

說(shuō)明：函數(shù)SEQUENCE產(chǎn)生一個(gè)4行3列的數(shù)組作為INDEX的第二參數(shù)，從而實(shí)現(xiàn)一列轉(zhuǎn)多列。

一列轉(zhuǎn)多列

19.隱藏電話號(hào)碼中間四位

公式：

=LEFT(A1,3)&REPT('*',4)&RIGHT(A1,5)

說(shuō)明：函數(shù)REPT的作用是產(chǎn)生四個(gè)連續(xù)的”*”.

隱藏電話號(hào)碼中間四位

20.日期轉(zhuǎn)星期

公式：=TEXT(A1,'ddd')

公式：=TEXT(A1,'aaaa')

說(shuō)明：TEXT第二參數(shù)”ddd”轉(zhuǎn)為英文縮寫(xiě),”aaaa”轉(zhuǎn)為中文。

日期轉(zhuǎn)星期

21.公式運(yùn)用于條件格式

公式：=OR(TEXT(A1,'ddd')='Sun',

TEXT(A1,'ddd')='Sat')

說(shuō)明：突出顯示周六和周日

公式：=ISFORMULA(A1)

說(shuō)明：突出顯示包含公式的單元格

公式：=OR(A1=MIN($A$1:$A$8),

A1=MAX($A$1:$A$8))

說(shuō)明：突出顯示最大值和最小值

公式：=ISNUMBER(FIND('廣東',A1))

說(shuō)明：突出顯示含關(guān)鍵字“廣東”的單元格

……

突出顯示周六和周日

突出顯示含關(guān)鍵字的單元格

22.禁止輸入空格

公式：=NOT(ISNUMBER(FIND(' ',A1)))

說(shuō)明：公式應(yīng)用于數(shù)據(jù)驗(yàn)證，防止輸入空格或其他任意字符。過(guò)程如動(dòng)圖所示。

禁止輸入空格

23.按條件篩選并排序

公式：=SORT(FILTER(A2:C13,B2:B13='1班'),3,-1)

說(shuō)明：FILTER篩選”1班”的數(shù)據(jù)，SORT按成績(jī)由高到低排序。

篩選并排序

24.去除重復(fù)

公式：

=IFERROR(OFFSET($A$1,MATCH

(,COUNTIF($C$1:C1,$A$2:$A$13),0),),'')

=UNIQUE(A2:A13)

說(shuō)明：UNIQUE為專業(yè)的去重函數(shù)，存在于高版本EXCEL中。低版本中可直接套用方法一。

去除重復(fù)

你還知道哪些常用的公式呢？歡迎補(bǔ)充！