【原】工作中最常用的公式大全（中）：计数和查找，会一半的人都是高手！

编按：

小窝总结了工作中常用的Excel公式，由于篇幅所限，今天列出的是计数公式和查找类公式。

首先补充上篇去重复公式

上篇中去重复都是单条件去重，今天补充多条件去重。

5.多条件判断是否重复

下方小组和姓名都相同才算重复。

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

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)

扫码进群，下载Excel课件，同步操作练习。

第六组：计数

1.有多少个数字

=COUNT(A2:A7)

记住这点：COUNT这家伙只统计数字，其他文字、错误值、逻辑值、空单元格等都被忽略。

2.有多少个数据——非空单元格

=COUNTA(A15:A24)

为何是9个而不是8个呢？用CTRL+G定位空单元格就发现，只有A19是真空单元格。A23实际填充了公式=""。

3.多少个空、文本、错误单元格？

空单元格个数=COUNTBLANK(A15:A24)

文本个数=SUMPRODUCT(ISTEXT(A15:A24)*1)

错误值个数=SUMPRODUCT(ISERROR(A15:A24)*1)

注意：空有真空和假空之分，CTRL+G定位的就是真空。后续我们会出教程帮助大家区分真空和假空。

4.条件计数

单条件

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

多条件

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

5.统计不重复人数

单条件：

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

多条件（小组和姓名均相同才算重复）：

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

6.按区间统计数字个数

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

7.统计连续数

如统计某数据连续出现2次及以上的次数

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

8.通配符进行包含计数

=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.一对多查找

低版本，典型的万金油查找公式

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

高版本很简单

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

6.查找最近一次入库数量

低版本：

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

高版本：

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

7.查找每列最后一个值

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

或者最粗暴的，查一个极大值直接返回最后一个值

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

8.查找第一个大于30的数字

低版本：

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

高版本：

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

9.通配符查找

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

说明：返回的是第一个李姓人员部门。如果查最后一个李姓人员部门，需用LOOKUP或者XLOOKUP查找。

=LOOKUP(1,0/SEARCH("李*",A58:A65),B58:B65),返回最后一个李姓人员部门“财务部”。

=XLOOKUP("李*",A58:A65,B58:B65,,2,-1)，同样返回最后一个李姓人员部门“财务部”。

10.多对多查找

也就是多条件查找同时返回多个符合条件的值。

高版本：

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

低版本，用经典的多对多公式：

=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.分类查询

在分类表中查询各明细的大类，如按省份查片区。

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

12.多区域查找

譬如在多个区域中查找某人的语文、数学成绩。

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