39 个函数速查 · 表格不上传

Excel 公式助手

用大白话描述需求就能拿到可直接粘贴的公式，也能把看不懂的长公式逐段拆开、把 #N/A 之类的报错讲清楚。新函数一律附带旧版本与 WPS 的替代写法。

需求 → 公式

按工号从另一张表带出部门

=VLOOKUP($A2,$F$2:$H$999,3,0)

$A2 —— 列锁行不锁，向下填充不跑偏

0 —— 精确匹配，省略会变近似匹配

用大白话描述你想要的结果，说明数据在哪几列会更准

你想实现什么

数据布局（可选，填了准确率明显更高）

内置方案完全离线、不限次；AI 生成会把你填写的文字发送到服务端，表格文件本身不会被上传。

结果

已给出内置方案，可直接复制使用。

内置方案

公式

=VLOOKUP($A2,$F$2:$H$999,3,0)

用 A 列的值到 F:H 区域首列查找，返回该区域第 3 列的内容，精确匹配。

假设布局：主表 A 列为编号；副表 F 列为编号、H 列为要取回的值。

逐段拆解

$A2

要查的值，列号锁定、行号不锁，方便向下填充。

$F$2:$H$999

查找区域，必须整体锁定，否则下拉时区域会跟着跑偏。

返回区域内第几列，从查找区域的第一列开始数。

精确匹配。写 1 或省略会变成近似匹配，是错值的主要来源。

替代写法

=XLOOKUP($A2,$F$2:$F$999,$H$2:$H$999,"未找到")

Excel 2021 / 365：不用数列号，还能自定义找不到时的提示。

=INDEX($H$2:$H$999,MATCH($A2,$F$2:$F$999,0))

全版本通用，且支持向左查找。

注意事项

· 查找值必须在查找区域的第一列，否则要改用 INDEX+MATCH 或 XLOOKUP。
· 一侧是文本数字、另一侧是真数字时会返回 #N/A，先统一格式再匹配。

函数速查表

39 个高频函数，含语法、说明与版本要求，点击即可复制语法。完全离线。

VLOOKUP

VLOOKUP(查找值, 查找区域, 返回第几列, 0)

在区域首列查找，返回同一行指定列的值。最后一个参数必须写 0（精确匹配）。

=VLOOKUP(A2,$F$2:$H$100,3,0)

XLOOKUP

XLOOKUP(查找值, 查找数组, 返回数组, [找不到时返回])

VLOOKUP 的替代品，可向左查找、不依赖列号，还能自定义找不到时的返回值。

=XLOOKUP(A2,$F$2:$F$100,$H$2:$H$100,"未找到")

需要 Microsoft 365 / Excel 2021 及以上，WPS 需较新版本

INDEX

INDEX(区域, 行号, [列号])

按行列位置取值，常与 MATCH 搭配实现任意方向查找。

=INDEX($H$2:$H$100,5)

MATCH

MATCH(查找值, 查找区域, 0)

返回查找值在一维区域中的位置序号，第三参数 0 表示精确匹配。

=MATCH(A2,$F$2:$F$100,0)

HLOOKUP

HLOOKUP(查找值, 查找区域, 返回第几行, 0)

横向版 VLOOKUP，在区域首行查找并返回同列指定行的值。

=HLOOKUP(A1,$B$1:$Z$5,3,0)

OFFSET

OFFSET(基准单元格, 行偏移, 列偏移, [高度], [宽度])

以某个单元格为起点偏移取区域，常用于动态区域，但会随表格变化重算。

=SUM(OFFSET($A$1,1,0,10,1))

INDIRECT

INDIRECT(引用文本)

把文本转成真实引用，可跨工作表动态取数，代价是无法追踪引用关系。

=INDIRECT("'"&A2&"'!B2")

SUMIFS

SUMIFS(求和区域, 条件区域1, 条件1, ...)

多条件求和，条件区域与求和区域必须等长。

=SUMIFS($D$2:$D$999,$A$2:$A$999,"华东",$B$2:$B$999,">=2026-01-01")

COUNTIFS

COUNTIFS(条件区域1, 条件1, ...)

多条件计数，可用于统计满足若干条件的记录数。

=COUNTIFS($A$2:$A$999,"已完成",$C$2:$C$999,">100")

AVERAGEIFS

AVERAGEIFS(求平均区域, 条件区域1, 条件1, ...)

多条件求平均，区域为空或无匹配时返回 #DIV/0!。

=AVERAGEIFS($D$2:$D$999,$A$2:$A$999,"华南")

SUMPRODUCT

SUMPRODUCT(数组1, [数组2], ...)

对应元素相乘再求和，是旧版本里实现多条件统计与加权计算的通用解法。

=SUMPRODUCT(($A$2:$A$999="华东")*($D$2:$D$999))

SUBTOTAL

SUBTOTAL(功能号, 区域)

只统计筛选后可见的单元格，功能号 109 求和、103 计数。

=SUBTOTAL(109,$D$2:$D$999)

RANK.EQ

RANK.EQ(数值, 区域, [0 降序/1 升序])

计算排名，并列时取相同名次并跳号。

=RANK.EQ(D2,$D$2:$D$999,0)

ROUND

ROUND(数值, 小数位数)

四舍五入到指定位数，金额计算务必显式取整避免累计误差。

=ROUND(D2*0.06,2)

MEDIAN

MEDIAN(数值区域)

取中位数，比平均值更抗极端值干扰。

=MEDIAN($D$2:$D$999)

TEXTJOIN

TEXTJOIN(分隔符, 是否忽略空值, 文本1, ...)

按分隔符拼接多个单元格，第二参数写 TRUE 可跳过空单元格。

=TEXTJOIN("、",TRUE,A2:D2)

需要 Excel 2019 及以上

TEXTSPLIT

TEXTSPLIT(文本, 列分隔符, [行分隔符])

按分隔符把一格文本拆成多列，结果自动溢出到相邻单元格。

=TEXTSPLIT(A2,",")

需要 Microsoft 365 专属

LEFT / RIGHT / MID

MID(文本, 起始位置, 长度)

按位置截取字符串，常配合 FIND 定位分隔符。

=MID(A2,FIND("-",A2)+1,10)

FIND / SEARCH

FIND(要找的字符, 文本, [起始位置])

返回字符位置。FIND 区分大小写且不支持通配符，SEARCH 反之。

=FIND("@",A2)

SUBSTITUTE

SUBSTITUTE(文本, 旧文本, 新文本, [第几次])

按内容替换，可指定只替换第 N 次出现的位置。

=SUBSTITUTE(A2," ","")

TRIM / CLEAN

TRIM(文本)

清理首尾空格与词间多余空格，粘贴外部数据后匹配不上时优先试它。

=TRIM(A2)

TEXT

TEXT(数值, 格式代码)

把数字或日期格式化为文本，常用于拼接展示。

=TEXT(B2,"yyyy-mm-dd")

LEN

LEN(文本)

返回字符数，排查看不见的空格与全角字符时很有用。

=LEN(A2)

DATEDIF

DATEDIF(开始日期, 结束日期, "Y"/"M"/"D")

计算两个日期相隔的整年、整月或天数，常用于工龄、年龄。

=DATEDIF(B2,TODAY(),"Y")

EOMONTH

EOMONTH(起始日期, 月数)

返回指定月份的最后一天，月数写 0 表示当月。

=EOMONTH(B2,0)

NETWORKDAYS

NETWORKDAYS(开始日期, 结束日期, [节假日区域])

计算工作日天数，自动排除周末，节假日需自己列一张表传入。

=NETWORKDAYS(B2,C2,$H$2:$H$30)

WORKDAY

WORKDAY(开始日期, 工作日数, [节假日区域])

推算若干工作日之后的日期，常用于排期与交付日。

=WORKDAY(B2,5,$H$2:$H$30)

YEAR / MONTH / DAY

MONTH(日期)

拆出日期的年月日，配合数据透视做同比环比。

=YEAR(B2)&"-"&TEXT(MONTH(B2),"00")

TODAY / NOW

TODAY()

返回当前日期（或日期时间），每次重算都会变化。

=TODAY()-B2

IF(条件, 成立返回, 不成立返回)

最基础的条件判断，嵌套超过三层建议改用 IFS 或查找表。

=IF(D2>=100,"达标","未达标")

IFS

IFS(条件1, 结果1, 条件2, 结果2, ...)

多条件顺序判断，比嵌套 IF 可读性好；需要兜底时最后写 TRUE。

=IFS(D2>=90,"A",D2>=75,"B",TRUE,"C")

需要 Excel 2019 及以上

IFERROR

IFERROR(表达式, 出错时返回)

把 #N/A、#DIV/0! 等错误替换成友好内容，但会掩盖真实问题，排查阶段先别加。

=IFERROR(VLOOKUP(A2,$F:$H,3,0),"未匹配")

AND / OR / NOT

AND(条件1, 条件2, ...)

组合多个条件，配合 IF 使用。

=IF(AND(D2>100,C2="已付款"),"可发货","待处理")

SWITCH

SWITCH(表达式, 值1, 结果1, ..., [默认值])

按值精确匹配返回结果，适合状态码转文字。

=SWITCH(A2,1,"待付款",2,"已发货","其它")

需要 Excel 2019 及以上

FILTER

FILTER(数组, 条件, [无结果时返回])

按条件筛选出整块数据并自动溢出，替代复杂的数组公式。

=FILTER($A$2:$D$999,$A$2:$A$999="华东","无数据")