|
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中。低版本中可直接套用方法一。 去除重复 你还知道哪些常用的公式呢？欢迎补充！ |
|
|
