小窩總結(jié)了工作中常用的公式，由于篇幅所限，今天列出的是計(jì)數(shù)公式和查找類公式。

首先補(bǔ)充上篇去重復(fù)公式

上篇中去重復(fù)都是單條件去重，今天補(bǔ)充多條件去重。

5.多條件判斷是否重復(fù)

下方小組和姓名都相同才算重復(fù)。

=IF(COUNTIFS($B$2:$B$18,B2,$A$2:$A$18,A2)>1,"重復(fù)","")

6.多條件去重后的值

低版本

=INDEX($A$2:$B$18,MATCH(0,COUNTIFS($D$1:D1,$A$2:$A$18,$E$1:E1,$B$2:$B$18),0),)

高版本：

=UNIQUE(A2:B18,0)

第六組：計(jì)數(shù)

1.有多少個(gè)數(shù)字

=COUNT(A2:A7)

記著這點(diǎn)：COUNT這家伙只統(tǒng)計(jì)數(shù)字，其他文字、錯(cuò)誤值、邏輯值、空單元格等都被忽略。

2.有多少個(gè)數(shù)據(jù)——非空單元格

=COUNTA(A15:A24)

為何是9個(gè)而不是8個(gè)呢？用CTRL+G定位空單元格就發(fā)現(xiàn)，只有A19是真空單元格。A23實(shí)際填充了公式=""。

3.多少個(gè)空、文本、錯(cuò)誤單元格？

空單元格個(gè)數(shù)=COUNTBLANK(A15:A24)

文本個(gè)數(shù)=SUMPRODUCT(ISTEXT(A15:A24)*1)

錯(cuò)誤值個(gè)數(shù)=SUMPRODUCT(ISERROR(A15:A24)*1)

注意：空有真空和假空之分，CTRL+G定位的就是真空。后續(xù)我們會(huì)出教程幫助大家區(qū)分真空和假空。

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

單條件

=COUNTIF(A2:A7,">2")

多條件

=COUNTIFS(A2:A7,">2",A2:A7,"<6")

5.統(tǒng)計(jì)不重復(fù)人數(shù)

單條件：

=SUMPRODUCT(1/COUNTIF(E2:E18,E2:E18))

多條件（小組和姓名均相同才算重復(fù)）：

=SUMPRODUCT(1/COUNTIFS(I2:I18,I2:I18,J2:J18,J2:J18))

6.按區(qū)間統(tǒng)計(jì)數(shù)字個(gè)數(shù)

=FREQUENCY(N2:N18,{69,85})

7.統(tǒng)計(jì)連續(xù)數(shù)

如統(tǒng)計(jì)某數(shù)據(jù)連續(xù)出現(xiàn)2次及以上的次數(shù)

=SUMPRODUCT((FREQUENCY(IF(S2:S16=S2,ROW(S1:S15)),IF(S2:S16<>S2,ROW(S1:S15)))>1)*1)

8.通配符進(jìn)行包含計(jì)數(shù)

=COUNTIF(W2:W18,"韓*")

第七組：查找

1.單條件查找

=VLOOKUP(E2,A2:C6,3,)

2.多條件查找

=LOOKUP(1,0/((A9:A13=E9)*(B9:B13=F9)),C9:C13)

3.雙向查找

=INDEX($B$16:$D$20,MATCH(F16,$A$16:$A$20,0),MATCH(G16,$B$15:$D$15,0))

或者

=VLOOKUP(F16,$A$16:$D$20,MATCH(G16,$A$15:$D$15,0),0)

4.反向查找

高版本

=XLOOKUP(F23,D23:D26,A23:A26)

低版本

=VLOOKUP(F23,IF({1,0},D23:D26,A23:A26),2,0)

5.一對(duì)多查找

低版本，典型的萬(wàn)金油查找公式

=IFERROR(INDEX($A$29:$A$36,SMALL(IF($B$29:$B$36=$D$29,ROW($1:$8),999),ROW(1:1)),),"")

高版本很簡(jiǎn)單

=FILTER(A29:A36,B29:B36=D29)

6.查找最近一次入庫(kù)數(shù)量

低版本：

=LOOKUP(1,0/(B39:B45=E39),C39:C45)

高版本：

=XLOOKUP(E39,B39:B45,C39:C45,,0,-1)

7.查找每列最后一個(gè)值

=LOOKUP(1,0/(B48:B54<>""),B48:B54)

或者最粗暴的，查一個(gè)極大值直接返回zui后一個(gè)值

=LOOKUP(9E+307,B48:B54)

8.查找第一個(gè)大于30的數(shù)字

低版本：

=INDEX($A$83:$A$93,MATCH(TRUE,A83:A93>30,0),)

高版本：

=XLOOKUP(TRUE,A83:A93>30,A83:A93)

9.通配符查找

=VLOOKUP("李*",A58:B65,2,0)

說明：返回的是第一個(gè)李姓人員部門。如果查最后一個(gè)李姓人員部門，需用LOOKUP或者XLOOKUP查找。

=LOOKUP(1,0/SEARCH("李*",A58:A65),B58:B65),返回最后一個(gè)李姓人員部門“財(cái)務(wù)部”。

=XLOOKUP("李*",A58:A65,B58:B65,,2,-1)，同樣返回最后一個(gè)李姓人員部門“財(cái)務(wù)部”。

10.多對(duì)多查找

也就是多條件查找同時(shí)返回多個(gè)符合條件的值。

高版本：

=FILTER(B68:B76,(A68:A76=A79)*(C68:C76=B79))

低版本，用經(jīng)典的多對(duì)多公式：

=IFERROR(INDEX($B$68:$B$76,SMALL(IF(($A$68:$A$76=$A$79)*($C$68:$C$76=$B$79),ROW(A$1:A$9),999),ROW(A1)),),"")

11.分類查詢

在分類表中查詢各明細(xì)的大類，如按省份查片區(qū)。

=INDIRECT("r99"&"c"&MAX((A109=$A$100:$H$106)*COLUMN(A1:H1)),0)

12.多區(qū)域查找

譬如在多個(gè)區(qū)域中查找某人的語(yǔ)文、數(shù)學(xué)成績(jī)。

=INDIRECT(TEXT(MAX(($A130=$A$118:$D$127)*ROW($A$118:$A$127)/1%+COLUMN(B118:E127)),"r0c00"),0)

若有不明白的可以留言，我們會(huì)在公開課中講解。

本文配套的練習(xí)課件請(qǐng)加入QQ群：902294808下載。

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

掃下方二維碼關(guān)注公眾號(hào)，可隨時(shí)隨地學(xué)習(xí)Excel：

相關(guān)推薦：

工作中最常用的公式，你都會(huì)就很棒（上）

函數(shù)課堂第十一課：COUNTIF用法

VLOOKUP最新用法詳解

圖表設(shè)計(jì)思路

版權(quán)申明：

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