什么值得买App下载 App ›
连续签到专享现金活动

加班做表到凌晨?这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格式化。

评论

评论区空态插画

当前文章无评论,是时候发表评论了