加班做表到凌晨?这40个Excel万能公式,3分钟替你干完1小时的活
在日常办公中,掌握Excel核心公式是提升数据处理效率的关键。以下内容系统梳理了数据查找、计算统计、文本处理、日期时间及逻辑判断五大场景下的40个高频函数,通过具体公式示例与实操要点,帮助快速解决复杂数据问题。
01数据查找:精准定位信息
- VLOOKUP
语法:=VLOOKUP(查找值, 查找区域, 返回列数, 0)
要点:用于纵向查找,第四参数必须为0以确保精确匹配;查找区域建议使用绝对引用。 - HLOOKUP
语法:=HLOOKUP(查找值, 查找区域, 返回行数, 0)
要点:适用于表头在首行的横向查找,如根据季度查询销售额。 - INDEX+MATCH
语法:=INDEX(返回列, MATCH(查找值, 查找列, 0))
要点:组合使用可实现反向或多向查找,不受“从左向右”限制,效率高于VLOOKUP。 - XLOOKUP
语法:=XLOOKUP(查找值, 查找数组, 返回数组, [未找到时返回])
要点:支持Office 365/2021+,功能更强大,支持默认精确匹配及自定义错误提示。 - LOOKUP
语法:=LOOKUP(1, 0/((条件区域1=条件1)*(条件区域2=条件2)), 返回列)
要点:利用模糊匹配特性,常用于提取满足条件的最后一条记录。 - OFFSET
语法:=OFFSET(起始单元格, 偏移行, 偏移列, [高度], [宽度])
要点:常结合COUNTA函数构建动态数据区域,实现图表或数据源的自动更新。 - CHOOSE
语法:=CHOOSE(索引号, 值1, 值2, ...)
要点:根据索引返回对应值,适用于建立数字代码与文本内容的映射关系。 - INDIRECT
语法:=INDIRECT(文本字符串)
要点:将文本转换为引用,常用于跨表动态引用或多表数据联动汇总。
02计算统计:数据条件汇总
统计类公式的核心在于灵活运用“条件”参数。SUMIFS、COUNTIFS等函数支持多条件计算,而SUMPRODUCT则能处理复杂的数组运算。
- SUMIF/SUMIFS
语法:=SUMIFS(求和区域, 条件区域1, 条件1, [条件区域2, 条件2]...)
要点:SUMIFS支持多条件求和,条件参数支持通配符(如“A*”)。 - COUNTIF/COUNTIFS
语法:=COUNTIFS(条件区域1, 条件1, [条件区域2, 条件2]...)
要点:用于统计满足条件的单元格数量,空文本和0会被计入。 - AVERAGEIF/MAXIFS/MINIFS
语法:同SUMIFS逻辑,分别用于求平均值、最大值、最小值。
要点:MAXIFS/MINIFS为2016+版本新增,早期版本可用数组公式替代。 - SUMPRODUCT
语法:=SUMPRODUCT((数组1)*(数组2), 求和区域)
要点:多功能瑞士军刀,可直接进行加权计算或处理多条件(AND/OR逻辑)求和。 - SUBTOTAL
语法:=SUBTOTAL(功能代码, 区域)
要点:仅对筛选后可见的数据进行计算,代码109代表忽略隐藏行的求和。 - ROUND/ROUNDUP/ROUNDDOWN
语法:=ROUND(数值, 保留位数)
要点:ROUND四舍五入,ROUNDUP向上进位,ROUNDDOWN向下舍去,常用于财务计算消除浮点误差。
03文本处理:清洗与格式化
- CONCAT/TEXTJOIN/&
语法:=TEXTJOIN("分隔符", TRUE, 区域)
要点:&用于简单连接;TEXTJOIN可指定分隔符并自动忽略空单元格,适合批量合并。 - LEFT/RIGHT/MID/LEN
语法:=MID(文本, 开始位置, 提取长度)
要点:MID配合LEN可提取身份证生日等信息;LEFT/RIGHT提取首尾字符;LEN计算长度。 - TRIM/SUBSTITUTE/REPLACE
语法:=SUBSTITUTE(文本, 旧文本, 新文本)
要点:TRIM清除多余空格;SUBSTITUTE替换指定字符;REPLACE按位置替换(如手机号打码)。
04日期时间:周期与工时计算
- TODAY/DATEDIF
语法:=DATEDIF(开始日, 结束日, "Y")
要点:TODAY返回当前日期;DATEDIF计算日期差(年、月、日),常用于计算工龄。 - EDATE/EOMONTH
语法:=EDATE(开始日, 月份数)
要点:EDATE推算指定月数后的日期;EOMONTH返回指定月份的最后一天。 - HOUR/MINUTE/NETWORKDAYS
语法:=NETWORKDAYS(开始日, 结束日, [节假日])
要点:提取时间成分;NETWORKDAYS计算两个日期之间的工作日(自动排除周末)。
05逻辑判断:构建自动化思维
- IF/IFS
语法:=IFS(条件1, 结果1, 条件2, 结果2...)
要点:IFS用于多级判断,按顺序执行,需注意条件从严格到宽松排列。 - AND/OR/NOT
语法:=AND(条件1, 条件2)
要点:AND表示所有条件都满足,OR表示任一条件满足,NOT表示逻辑取反,常嵌套在IF中使用。 - ISNUMBER/IFERROR
语法:=IFERROR(公式, "出错时返回")
要点:ISNUMBER检测是否为数值;IFERROR用于捕获并处理错误,使报表更友好。
06实战应用自测
| 场景描述 | 正确答案 | 解析 |
|---|---|---|
| 横向报表,根据B1季度(如Q3)查找销售额(A1:Q1为表头,A2:Q2为数据) | =HLOOKUP(B1, A1:Q2, 2, 0) | HLOOKUP专门用于首行横向查找。 |
| 统计“销售部”中“张三”的业绩总额(B列部门,A列姓名,C列业绩) | =SUMIFS(C:C, B:B, "销售部", A:A, "张三") | SUMIFS是多条件求和的标准函数,简洁高效。 |
| 将日期格式中的“/”或“.”统一替换为“-”,并标准化为“2026-01-01” | 其他三项均可,唯独D项TRIM无法达成 | TRIM仅去除空格,不替换字符;需用SUBSTITUTE替换,再用TEXT或DATEVALUE格式化。 |
当前文章无评论,是时候发表评论了