Excel 常用函数速查

在区域的第一列里找查找值，找到后返回同一行第 N 列的值。最经典也最容易踩坑的查找函数。

参数：查找值（要找的内容，可以是单元格、数字或文本）；表格区域（查找值必须落在这一区域的第一列，建议用 $A$2:$D$100 锁住）；返回列号（从区域第一列开始数，第 1 列不算在里面要写成 2）；匹配方式（FALSE 或 0 表示精确匹配，TRUE 或省略表示近似匹配）

示例：=VLOOKUP(A2, 员工表!$A$2:$D$100, 3, FALSE)

常见报错：#N/A 查找值不在首列；或两边一个是文本一个是数字；或单元格带了看不见的空格；#REF! 返回列号大于区域的总列数，返回列被删掉后也会变成这个错；#VALUE! 把列号写成了非数字，或者整列引用后列号算错

提示：最后一位省略不写会变成近似匹配，排序过的数据会悄悄返回一个近似值，务必显式写 FALSE。

VLOOKUP 的替代品，不用数列号，能向左查、能设默认值、找不到不会直接报 #N/A，推荐新表格优先用它。

参数：查找值（要找的内容）；查找数组（在哪一列或哪一行里找，可以不是区域的第一列）；返回数组（找到后从哪一列取值，长度要和查找数组一致）；未找到时（留空则返回 #N/A，写 "未找到" 则原样返回文字）；匹配模式（0 精确匹配（默认），-1 精确或下一个较小项，1 精确或下一个较大项，2 通配符）；搜索模式（1 从前往后，-1 从后往前，2 或 -2 为二分查找（要求已排序））

示例：=XLOOKUP(A2, 员工表!$A$2:$A$100, 员工表!$C$2:$C$100, "查无此人")

常见报错：#N/A 查找值和未找到参数都留空，且确实没匹配到；#VALUE! 查找数组和返回数组的行列数不一致；#NAME? Excel 2019 及更早版本没有这个函数，会被当成未定义的名称

提示：搜索模式传 -1 可以取「最后一条」记录，查最新订单时比先排序再查方便。

用 MATCH 找出位置，再用 INDEX 按位置取值。不受「查找值必须在第一列」限制，也能少一次整列扫描。

参数：返回区域（真正要取值的那一列）；MATCH 查找值（要匹配的内容）；MATCH 查找区域（在哪一列里定位）；MATCH 匹配类型（0 精确匹配，1 升序近似，-1 降序近似）

示例：=INDEX(员工表!$C$2:$C$100, MATCH(A2, 员工表!$A$2:$A$100, 0))

常见报错：#N/A MATCH 没找到，和 VLOOKUP 的成因一样：文本数字混用、多余空格、真空白；#REF! INDEX 的区域被删行删列后越界

提示：MATCH 的第三个参数千万别省，省略默认是 1，会变成近似匹配。

返回查找值在区域里的第几个位置，常和 INDEX 搭配，也能单独用来判断某值存不存在。

参数：查找值（要找的内容）；查找区域（只能是一行或一列）；匹配类型（0 精确匹配，1 小于等于（升序），-1 大于等于（降序））

示例：=MATCH("张三", A2:A100, 0)

常见报错：#N/A 没找到。用 ISNA() 包一层可以把它变成可判断的布尔值

提示：MATCH 支持通配符 * 和 ?，配合 "王*" 可以做前缀匹配。

在区域的第一行里找，返回同一列第 N 行的值。表头横着排的时候才用得上，比 VLOOKUP 少见。

参数：查找值（要在首行里找的内容）；表格区域（查找值必须落在这一区域的第一行）；返回行号（从区域第一行开始数）；匹配方式（FALSE 精确匹配，TRUE 近似匹配）

示例：=HLOOKUP("一月", $B$1:$M$12, 5, FALSE)

常见报错：#N/A 查找值不在首行，或首行里带了合并单元格；#REF! 返回行号超过区域总行数

从起点按行列偏移后返回一个新的区域，配合 COUNTA 可以做成会随数据增减的「动态区域」。

参数：起点（偏移的基准单元格或区域）；行偏移（正数向下，负数向上）；列偏移（正数向右，负数向左）；高度 / 宽度（返回区域的行数和列数，省略则和起点相同）

示例：=SUM(OFFSET($A$1, 0, 0, COUNTA($A:$A), 1))

常见报错：#REF! 偏移后跑出了工作表边界；#VALUE! 高度或宽度填了负数或非数字

提示：OFFSET 是易失性函数，每次重算都会重新取区域，表很大时会让整体变慢，能用结构化引用就不要用它。

把区域或一串数值加起来，忽略文本、逻辑值和文本形式的数字。

参数：数值1（要相加的区域、单元格或常量，后面的参数可继续追加）

示例：=SUM(B2:B100)

常见报错：结果偏小 区域里有一部分「数字」其实是文本，SUM 会当 0 处理，用 SUM(VALUE()) 或先分列转成数字

提示：SUM 会自己忽略文本，如果结果明显不对，先看数字是否左对齐（左对齐通常是文本）。

按一个条件筛选后求和，条件区域和求和区域可以不同列，但长度必须一样。

参数：条件区域（拿来做判断的列）；条件（可以是数字、文本、">100" 这类表达式，或用 & 拼的通配符）；求和区域（真正要求和的列，省略时对条件区域本身求和）

示例：=SUMIF(A2:A100, "华东", C2:C100)

常见报错：结果偏大 求和区域和条件区域行数不一致，Excel 会按左上角对齐错位求和

提示：拆解条件用 &，例如 ">"&D1 表示大于 D1 里的数，直接写 ">D1" 会当字符串处理。

多个条件同时满足才求和。注意求和区域在最前面，顺序和 SUMIF 正好相反，这是最常见的记错点。

参数：求和区域（要求和的列，放在第一个参数）；条件区域 / 条件（成对出现，最多 127 组）

示例：=SUMIFS(C2:C100, A2:A100, "华东", B2:B100, ">=2024/1/1")

常见报错：结果一直是 0 把求和区域放到了第二个参数（当成了条件区域）

提示：所有条件区域和求和区域的行数必须完全一致，用整列引用（A:A）时尤其容易出问题。

数一数区域里有多少个单元格满足条件，去重前的统计和查重都靠它。

参数：条件区域（要统计的列）；条件（数字、文本、表达式或通配符）

示例：=COUNTIF(A2:A100, "完成")

常见报错：#VALUE! 条件区域是一个多行多列的大区域时，个别版本会报错，改成单行或单列

提示：COUNTIF(A:A, A2)=1 可以判断 A2 是否唯一，是标记重复值最省事的公式。

多个条件同时成立才计数，参数是成对出现的。

参数：条件区域 / 条件（成对出现，区域之间行数必须一致）

示例：=COUNTIFS(A2:A100, "华东", B2:B100, "完成")

常见报错：结果不对 条件区域行数不一致，或者把条件写成了区域

数出区域里所有非空单元格的个数，文本、数字、错误值都算，用来判断有没有填。

参数：值1（要统计的区域）

示例：=COUNTA(A2:A100)

常见报错：结果偏大 公式返回的空字符串 "" 也算非空，用 COUNTBLANK 或 ISBLANK 配合判断

把多个数组对应位置相乘再求和，是多条件求和万金油，老版本里还能替代 SUMIFS 做多列横向统计。

参数：数组1（第一个数组或区域，后面每个数组的长度要相同）

示例：=SUMPRODUCT((A2:A100="华东")*(B2:B100="完成")*C2:C100)

常见报错：#VALUE! 各数组长度不一致；或数组里有文本，需要先乘 1 转换

提示：条件表达式要用括号包起来再相乘，乘号会把 TRUE/FALSE 自动转成 1/0。

最基础的分支：条件为真返回一个值，否则返回另一个值。嵌套超过三层就该换 IFS 或 XLOOKUP 了。

参数：条件（返回 TRUE 或 FALSE 的表达式）；成立时（条件为真时的结果，可以是值也可以是另一个公式）；不成立时（省略时返回 FALSE）

示例：=IF(B2>=60, "及格", "不及格")

常见报错：#VALUE! 把文本写进了要参与比较的位置，比如 IF("90">60, ...) 里文本没加引号；结果全一样 条件被写成了带引号的字符串，永远为真

按顺序判断多个条件，返回第一个成立的结果，替代一长串嵌套 IF，读起来从上往下就是逻辑顺序。

参数：条件 / 结果（成对出现，条件按书写顺序依次判断）

示例：=IFS(B2>=90, "优", B2>=80, "良", B2>=60, "及格", TRUE, "不及格")

常见报错：#N/A 所有条件都不成立且没写兜底的 TRUE 分支

提示：最后一定补一对 TRUE 加兜底值，否则漏掉的情况会直接报 #N/A。

表达式只要报任何错误就返回指定值，用来把 #N/A、#DIV/0! 换成空白或文字，让报表干净。

参数：表达式（可能出错的公式）；出错时的值（出错时返回的内容，常用 "" 或 0）

示例：=IFERROR(VLOOKUP(A2, 员工表!$A:$C, 3, FALSE), "查无此人")

常见报错：掩盖真问题 IFERROR 会把所有错误一起吞掉，调试阶段先用 ISNA 定位是哪种错

只处理查找类函数最常见的 #N/A，其它错误照样抛出来，排查时不至于把问题藏起来。

参数：表达式（通常是一个查找公式）；#N/A 时的值（找不到时返回的内容）

示例：=IFNA(VLOOKUP(A2, 员工表!$A:$C, 3, FALSE), 0)

常见报错：#VALUE! IFNA 只兜 #N/A，别的错误会照常显示，这不是函数坏了

提示：新写查找公式建议用 IFNA 而不是 IFERROR，能保留 #REF! 这类真正需要修的错。

AND 要求全部成立，OR 只要有一个成立。常用来把多个条件塞进一个 IF 里。

参数：条件（返回 TRUE/FALSE 的表达式，可以有多个）

示例：=IF(AND(B2>=60, C2>=60), "全科及格", "有不及格")

常见报错：#VALUE! 参数里塞了文本或空白单元格，空白会被当成 FALSE

把数字或日期按指定的显示格式转成文本，做拼字符串、导出编号前缀时必用。

参数：值（要格式化的数字、日期或计算结果）；格式代码（如 0.00、"¥"#,##0、yyyy-mm-dd、AAAA（中文星期））

示例：=TEXT(A2, "yyyy年m月d日") & " 入职"

常见报错：#VALUE! 格式代码里用了中文引号，或把格式代码写在了引号外面；结果不对 TEXT 返回的是文本，再参与计算会失去数值特性，需要先用 VALUE 还原

提示：想让 0 显示成「无」可以用正负零分段格式：0;-0;"无"。

把文本形式的数字或日期转成真正的数值/日期序列号，解决从系统导出后数字不能计算的问题。

参数：文本（要转换的内容，带千分位和货币符号也能识别）

示例：=VALUE(A2)

常见报错：#VALUE! 文本里有看不见的空白字符（如不换行空格），先用 SUBSTITUTE 清掉

从左边或右边截取指定个数的字符，取前缀后缀、截时间的小时数都用它。

参数：文本（原字符串）；字符数（要截多少个字符，省略则取 1 个）

示例：=LEFT(A2, 3) & "***"

常见报错：结果少字 中文一个字算一个字符，英文一个字母也算一个；按字节算的场合要另用 LEFTB 或先转半角

从第几个字符开始截取指定长度，从身份证号里取出生日期、从编码里取流水号都靠它。

参数：文本（原字符串）；起始位置（第一个字符的位置是 1，不是 0）；字符数（要截多少个字符）

示例：=MID(A2, 7, 8)

常见报错：#VALUE! 起始位置小于 1 或字符数是负数；截不到 起始位置不是 1 起算（MATCH 和 MID 的位置口径不同，别混）

返回字符个数，中文一个字算一个。常配合 TRIM 找出「看起来一样但匹配不上」的数据。

参数：文本（要计算长度的内容）

示例：=LEN(A2)

常见报错：长度偏大 字符串里有多余空格或不换行字符，用 LEN(A2)-LEN(TRIM(A2)) 能看出多了几个空格

去掉首尾空格，并把中间连续的空格压成一个。查找匹配不上时，第一件事就是套一个 TRIM。

参数：文本（要清理的内容）

示例：=VLOOKUP(TRIM(A2), 员工表!$A:$C, 3, FALSE)

常见报错：还是匹配不上 TRIM 去不掉不换行空格（CHAR(160)），要用 SUBSTITUTE(A2, CHAR(160), "")

把指定内容替换成别的内容，和「查找替换」一样，是清洗数据的常用手段。

参数：文本（原字符串）；旧文本（要被替换掉的部分，区分大小写）；新文本（替换成什么，写 "" 表示删除）；第几次出现（只替换第 N 次出现的位置，省略则全部替换）

示例：=SUBSTITUTE(A2, "-", "")

常见报错：没替换 大小写不一致；需要忽略大小写时改用 REPLACE 配合 SEARCH

把多个区域的内容用统一分隔符接起来，比一串 & 干净，能自动跳过空白单元格。

参数：分隔符（放在中间的符号，如 "、" 或 ", "）；是否忽略空白（TRUE 跳过空单元格，FALSE 会留下连续分隔符）；文本1（要拼接的内容，可以传区域）

示例：=TEXTJOIN("、", TRUE, B2:E2)

常见报错：#VALUE! 结果超过 32767 个字符；#NAME? Excel 2016 及更早版本没有这个函数

把内容连成一串。CONCAT 能直接接受区域，& 更短，日常拼两个字段用 & 就够。

参数：文本（要连接的单元格、常量或区域）

示例：=A2 & "-" & B2

常见报错：日期变成数字 日期和 & 拼接会变成序列号，要先 TEXT(A2, "yyyy-mm-dd")

返回子串第一次出现的位置。FIND 区分大小写且不支持通配符，SEARCH 都不区分。

参数：找什么（要查找的子串）；在哪找（被查找的原字符串）；起始位置（从第几个字符开始找，默认 1）

示例：=IF(ISNUMBER(SEARCH("@", A2)), "是邮箱", "不是邮箱")

常见报错：#VALUE! 找不到子串时就会报这个错，所以判断是否存在要包 ISNUMBER

用三个数字拼成一个日期，月或日超出范围会自动进位，比如 DATE(2024, 13, 1) 等于 2025/1/1。

参数：年（年份，小于 1900 时按 1900 加算）；月（月份，可大于 12 或小于 1，会自动进位或退位）；日（日期，同样支持进位）

示例：=DATE(2024, 1, 1)

常见报错：#VALUE! 参数不是数字；年份写成了文本；结果变数字 结果本身是序列号，把单元格格式设成日期即可

算两个日期间的差距，单位可以是整年、整月、整日，是算年龄和工龄的标准做法。它是隐藏函数，输入时不会有参数提示。

参数：开始日期（较早的日期）；结束日期（较晚的日期，比开始日期早会报错）；单位（"Y" 整年、"M" 整月、"D" 整日、"MD" 忽略年月后的天数、"YM" 忽略年后的月数、"YD" 忽略年后的天数）

示例：=DATEDIF(A2, TODAY(), "Y")

常见报错：#NUM! 开始日期比结束日期晚；#NAME? 极个别版本把名字写错成 DATEDIFF 之类

提示：算「X 岁 Y 个月」要用两段：DATEDIF(.., "Y") 和 DATEDIF(.., "YM")。

TODAY 返回今天，NOW 返回当前时刻。两个都是易失性函数，每次重算都会更新。

这个函数不需要参数，写成 TODAY() 这样就行。

示例：=TODAY()-A2

常见报错：结果成了数字 相减得到的是天数差，格式设成「常规」才能看到数字

提示：想固定住某天的日期，选中后按 Ctrl+; 直接输入静态日期，比公式稳。

EDATE 往后推若干个月，EOMONTH 取推完那个月的最后一天，算到期日和账期口径常用。

参数：开始日期（起点日期）；月数（正数往后，负数往前）

示例：=EOMONTH(A2, 0)

常见报错：#VALUE! 开始日期不是合法日期；月数用了小数

提示：EOMONTH(A2, 0) 取当月最后一天，EOMONTH(A2, -1)+1 取当月第一天。

从日期里把年、月、日单独取出来，做按年按月汇总时先取出来再当条件。

参数：日期（日期值或日期序列号）

示例：=MONTH(A2)

常见报错：#VALUE! 参数是文本且不符合日期格式，先用 DATEVALUE 转一下

算两个日期之间有多少个工作日，自动排除周末，可以传一份法定节假日清单一起排除。

参数：开始日期 / 结束日期（两端都算在内）；节假日（要额外排除的日期区域，可以留空）

示例：=NETWORKDAYS(A2, B2, 节假日!$A$2:$A$30)

常见报错：结果偏大 国内调休上班的周末不在函数逻辑里，需要人工修正，或者用 NETWORKDAYS.INTL 指定休息日

一键列出不重复的值，结果会自动溢出到下方单元格，替代老式的高级筛选和数组公式。

参数：区域（要去重的数据范围）；按列还是按行（FALSE 按行比较（默认），TRUE 按列比较）；是否只出现一次（TRUE 只返回在整个区域里恰好出现一次的值）

示例：=UNIQUE(A2:A100)

常见报错：#SPILL! 结果要占用的位置被别的内容挡住了，清空下方单元格即可；#NAME? Excel 2019 及更早版本不支持，需要升级到 365 / 2021

用公式代替筛选按钮，条件一改结果立刻变，结果自动溢出，适合做看板和动态报表。

参数：数组（要返回的数据区域）；条件（返回 TRUE/FALSE 的表达式，长度要和数组行数一致）；没有结果时（筛选不到时返回的内容，省略则报 #CALC!）

示例：=FILTER(A2:C100, B2:B100="华东", "无记录")

常见报错：#CALC! 没有任何行满足条件且没写第三个参数；#VALUE! 条件的行列方向和数组不一致；#SPILL! 溢出区域被占用了

提示：多条件用乘号：FILTER(A:C, (B:B="华东")*(C:C>100))。

返回排好序的副本，原数据不动，配合 FILTER 可以做出全动态的排名表。

参数：区域（要排序的数据）；按第几列排（区域内的列序号，默认 1）；升序还是降序（1 或省略为升序，-1 为降序）；按行还是按列（FALSE 按行（默认），TRUE 按列）

示例：=SORT(A2:C100, 3, -1)

常见报错：#VALUE! 排序依据的列序号超出了区域宽度；#SPILL! 溢出位置被挡住

按指定的另一列排序，能多级排序，比 SORT 灵活，因为排序依据不必在返回区域里。

参数：区域（要返回的数据）；排序依据（用哪一列的值来决定顺序）；顺序（1 升序（默认），-1 降序）

示例：=SORTBY(A2:B100, C2:C100, -1)

常见报错：#VALUE! 排序依据和返回区域的行数不一致

把一个字符串按分隔符拆成多列或多行，替代老式的「分列」操作，而且结果会随源数据自动更新。

参数：文本（要拆分的字符串或单元格）；列分隔符（按什么横向拆分，如 ","）；行分隔符（按什么纵向拆分）；忽略连续分隔符（TRUE 把连续分隔符当成一个）

示例：=TEXTSPLIT(A2, ",")

常见报错：#NAME? 需要 Excel 365 的较新版本；#SPILL! 拆分后的区域被占用了

按指定小数位四舍五入、向上取整或向下取整。金额计算要在最后一步 ROUND，避免浮点误差累积。

参数：数值（要处理的数字或公式结果）；小数位（正数保留几位小数，0 取整，负数则对整数部分取舍）

示例：=ROUND(A2*B2, 2)

常见报错：合计对不上 每行都四舍五入后再求和，和先求和再四舍五入会差几分，口径要统一

INT 取不大于它的最大整数，TRUNC 直接砍掉小数部分。两者对正数结果一样，对负数不同。

参数：数值（要取整的数字）；小数位（TRUNC 专用，保留几位小数后截断）

示例：=INT(-1.5)

常见报错：负数结果不同 INT(-1.5) 得到 -2，TRUNC(-1.5) 得到 -1，按业务语义选

取绝对值，算差额、波动幅度时把正负去掉只看量级。

参数：数值（任意数字）

示例：=ABS(A2-B2)

常见报错：#VALUE! 参数是文本

算某个数在一组数里排第几，并列时给相同名次。RANK.EQ 是新写法，老写法 RANK 仍可用。

参数：数值（要排名的数字）；区域（和谁比较，通常要锁住绝对引用）；顺序（0 或省略为降序（大的排前面），非 0 为升序）

示例：=RANK.EQ(B2, $B$2:$B$100, 0)

常见报错：#N/A 数值不在区域里；名次跳号 并列会占名次，出现 1、2、2、4，想不跳号要配合 COUNTIF 调整