1.纠错
公式：=IFERROR(A2/B2,'有误')
说明：如果是错误值则显示为“有误”，否则正常显示。
纠错
2.统计两表重复
公式：=COUNTIF(A:A,C2)
说明：结果大于0说明在另一个表中存在，0则不存在。
统计重复
3.模糊条件求和
公式：=SUMIF(A:A,D2&'*',B:B)
说明：SUMIF/SUMIFS支持通配符”*”,根据模糊条件求和。
模糊条件求和
4.隔列求和
公式：=SUM(OFFSET(A3,,{1,3,5}))
说明：A3单元格右边第1个，第3个，第5个单元格求和
公式：=SUM(OFFSET(A3,,{2,4,6}))
A3单元格右边第2个，第4个，第6个单元格求和
隔列求和
5.多表相同位置求和
公式=SUM(Sheet2:Sheet5!B2)
说明：对Sheet1到Sheet5中的B2单元格求和。
多表相同位置求和
6.多条件判断
公式：=IFS(B2<=60,'不及格',
B2<=80,'良好',
B2<=99,'优秀',
B2=100,'人才')
说明：从上往下依次判断，条件满足时输出对应的值，并终止判断。
多条件判断
7.多条件查询
公式=INDEX(B2:C7,
MATCH(F1,A2:A7,0),
MATCH(F2,B1:C1,0))
INDEX+MATCH是多条件查询中的经典组合，两个MATCH函数定位指定条件的坐标，作为INDEX的两个参数实现精准查询。
多条件查询
8.关键字模糊查询
VLOOKUP可支持通配符模糊查询。
公式：=VLOOKUP('*'&D2&'*',A:B,2,0)
高版本EXCEL新增查询函数XLOOKUP,可视为VLOOKUP的增强版本，同样支持通配符模糊查询。
公式：=XLOOKUP('*'&D3&'*',A:A,B:B,,2)
关键字模糊查询
9.合并单元格内容
公式：=TEXTJOIN(',',TRUE,
IF(B2:B9>2000,A2:A9,''))
说明：IF函数筛选出满足条件的水果，Textjoin将其连接，用逗号隔开。
合并单元格内容
10.求满足条件的最小（大）值
公式：=MINIFS(B:B,A:A,'菠萝')
说明：满足条件的最小日期
公式：=MAXIFS(B:B,A:A,'荔枝')
说明：满足条件的最大日期
最小（大）值
11.一堆数据中对正数（或负数）求和
公式：=SUMIF(A1:E10,'>0')
公式： =SUMIF(A1:E10,'<0')
条件求和
12.一堆数据中，统计正数（或负数）的个数
公式：=COUNTIF(A1:E10,'>0')
公式：=COUNTIF(A1:E10,'<0')
条件计数
13.自动生成序列号
公式：A2=IF(B2<>'',ROW(A1),'')下拉填充
说明：B列新增项目时，A列将自动产生序列号。
自动生成序列号
14.对筛选的数据求和
公式：=SUBTOTAL(9,B2:B11)
说明：未筛选状态下对所有数据求和，筛选状态下只对筛选出来的数据求和。
未筛选求和
筛选后求和
15.提取年月日信息
公式：=YEAR(A1)
公式：=MONTH(A1)
公式：=DAY(A1)
提取年月日信息
16.字符替换
公式：=SUBSTITUTE(A2,'-','')
说明：”-“替换为空，整理电话号码
字符替换
17.截取指定字符之后的字符
公式：=MID(A2,FIND('-',A2)+1,100)
说明：FIND函数的作用是返回”-“在字符串中的位置，从该位置开始截取后面的所有字符。
截取字符
18.一列转多列
公式：=INDEX(A1:A12,
SEQUENCE(4,3,1,1),1)
说明：函数SEQUENCE产生一个4行3列的数组作为INDEX的第二参数，从而实现一列转多列。
一列转多列
19.隐藏电话号码中间四位
公式：
=LEFT(A1,3)&REPT('*',4)&RIGHT(A1,5)
说明：函数REPT的作用是产生四个连续的”*”.
隐藏电话号码中间四位
20.日期转星期
公式：=TEXT(A1,'ddd')
公式：=TEXT(A1,'aaaa')
说明：TEXT第二参数”ddd”转为英文缩写,”aaaa”转为中文。
日期转星期
21.公式运用于条件格式
公式：=OR(TEXT(A1,'ddd')='Sun',
TEXT(A1,'ddd')='Sat')
说明：突出显示周六和周日
公式：=ISFORMULA(A1)
说明：突出显示包含公式的单元格
公式：=OR(A1=MIN($A$1:$A$8),
A1=MAX($A$1:$A$8))
说明：突出显示最大值和最小值
公式：=ISNUMBER(FIND('广东',A1))
说明：突出显示含关键字“广东”的单元格
……
突出显示周六和周日
突出显示含关键字的单元格
22.禁止输入空格
公式：=NOT(ISNUMBER(FIND(' ',A1)))
说明：公式应用于数据验证，防止输入空格或其他任意字符。过程如动图所示。
禁止输入空格
23.按条件筛选并排序
公式：=SORT(FILTER(A2:C13,B2:B13='1班'),3,-1)
说明：FILTER筛选”1班”的数据，SORT按成绩由高到低排序。
筛选并排序
24.去除重复
公式：
=IFERROR(OFFSET($A$1,MATCH
(,COUNTIF($C$1:C1,$A$2:$A$13),0),),'')
=UNIQUE(A2:A13)
说明：UNIQUE为专业的去重函数，存在于高版本EXCEL中。低版本中可直接套用方法一。
去除重复
你还知道哪些常用的公式呢？欢迎补充！
联系客服
