Excel 函数大全：在可编辑的表格里学公式
在可以编辑的表格里讲解 Excel 公式和函数：VLOOKUP、XLOOKUP、IF、SUMIF、COUNTIF、日期、文本和动态数组。改一个数字或公式，表格就会在浏览器里重新计算。
开始课程公式基础
- SUM在一列数字下方输入 =SUM(B2:B6) 就能把它们加起来，也可以按 Alt+= 让自动求和替你写公式。在可编辑的表格中练习对行、不相邻的单元格和其他工作表求和。
- 减法Excel 没有 SUBTRACT 函数：输入 =B2-C2 就能用一个单元格减去另一个单元格。在可编辑的表格中练习整列相减、一次减去多个单元格、减去百分比和日期相减。
- 乘法和除法Excel 中用星号做乘法：=B2*C2，用斜杠做除法：=B2/C2。本文用可编辑的表格讲解整列乘以同一个数、PRODUCT 函数，以及怎样避免 #DIV/0! 错误。
- AVERAGE=AVERAGE(B2:B7) 把 B2:B7 中的数字相加，再除以数字的个数。本文讲解空白单元格和 0 如何影响结果、如何忽略 0，以及如何求前 3 名的平均值。
- COUNT 和 COUNTA=COUNT(B2:B8) 统计包含数字的单元格，=COUNTA(B2:B8) 统计所有非空单元格，=COUNTBLANK(B2:B8) 统计空单元格。在可编辑的表格中对比这三个函数。
- 绝对引用像 $E$1 这样的绝对引用在复制公式时保持不变，而像 E1 这样的相对引用会跟着移动。按 F4 即可加上美元符号。在可编辑的表格上亲手看看两者的区别。
- 百分比Excel 百分比公式是 =部分/总数，例如 =B2/C2，并把单元格设置为百分比格式。占总数的百分比、一个数的百分之几、加上或减去一个百分比，都在在线表格中演示。
- 百分比变化Excel 中百分比变化的公式是 =(新值-旧值)/旧值，例如 =(C2-B2)/B2，并设置为百分比格式。结果为负表示下降。在线表格涵盖环比变化、起始值为零和百分点。
逻辑函数
- IF=IF(B2>=50,"Pass","Fail") 检查 B2 是否大于等于 50，是就返回 Pass，否则返回 Fail。本文讲解 IF 的语法、IF 判断文本、IF 搭配计算、判断单元格是否为空，以及让 IF 返回错误结果的常见错误。
- 嵌套 IF=IF(B2>=90,"A",IF(B2>=80,"B",IF(B2>=70,"C","F"))) 把一个 IF 放进另一个 IF 中，在两个以上的结果之间选择。了解嵌套 IF 的执行顺序、为什么条件的顺序很重要，以及什么时候用 IFS 或查找表更好。
- IFS=IFS(B2>=90,"A",B2>=80,"B",B2>=70,"C",TRUE,"F") 按顺序检查每个条件，返回第一个为 TRUE 的条件所对应的值。本文讲解 IFS 的语法、用 TRUE 设置默认值、IFS 为什么返回 #N/A，以及它和嵌套 IF 的对比。
- AND、OR、NOT=AND(B2>=10,B2<=20) 只有在所有条件都成立时才返回 TRUE，而 =OR(B2="North",B2="South") 只要有一个条件成立就返回 TRUE。本文讲解 AND、OR、NOT 和 XOR 的单独用法及在 IF 中的用法，如何判断数字是否在两个值之间，以及如何在数组公式中表示 AND 和 OR。
- IFERROR=IFERROR(B2/C2,0) 返回 B2/C2 的结果，除法出错时返回 0。本文讲解 IFERROR 搭配 VLOOKUP、出错时返回空白、为什么查找时 IFNA 是更好的选择，以及为什么掩盖所有错误可能会藏住真正的问题。
- SWITCH=SWITCH(B2,"N","North","S","South","Unknown") 依次把 B2 和每个值比较，返回第一个完全匹配的值所对应的结果，都不匹配时返回 Unknown。本文讲解 SWITCH 的语法、默认值、SWITCH(TRUE,...) 写法，以及什么时候改用 IFS 或嵌套 IF。
- ISBLANK、ISNUMBERB2 为空时 =ISBLANK(B2) 返回 TRUE，B2 中是数字时 =ISNUMBER(B2) 返回 TRUE。学习 ISBLANK、ISNUMBER、ISTEXT、ISERROR、ISNA、ISEVEN 和 ISODD，了解为什么返回 "" 的公式不算空，以及怎样用 ISNUMBER(SEARCH()) 判断单元格是否包含某段文本。
查找
- VLOOKUP=VLOOKUP(F2,A2:D6,3,FALSE) 在 A2:D6 的第一列中查找 F2，并返回同一行第三列的值。学习精确匹配和近似匹配、#N/A 的解决方法、跨工作表查找和双条件查找。
- XLOOKUP=XLOOKUP(F2,A2:A6,C2:C6) 在 A2:A6 中查找 F2，并返回 C2:C6 同一行的值。找不到时显示的文本、一次返回多列、向左查找、查找最后一个匹配项、近似匹配和通配符匹配。
- INDEX MATCH=INDEX(C2:C6,MATCH(F2,A2:A6,0)) 在 A 列中找到 F2 所在的行，并返回 C 列同一行的值。它能向左查找、做双向查找，而且在所有 Excel 版本中都能用。
- INDEX=INDEX(A2:C6,3,2) 返回 A2:C6 中第三行第二列的值。用它取列表中的第 n 项、整行或整列，以及 MATCH 找到的位置上的值。
- MATCH=MATCH(E2,A2:A6,0) 返回 E2 在 A2:A6 中的位置：如果它是第四项，就返回 4。本文讲解匹配类型 0、1 和 -1、通配符、区分大小写的匹配，以及检查一个值是否在列表中。
- HLOOKUP=HLOOKUP("Mar",A1:E3,2,FALSE) 在 A1:E3 的第一行中查找 Mar，并返回同一列第二行的值。本文讲解精确匹配和近似匹配，以及什么时候用 XLOOKUP 更好。
- XMATCH=XMATCH(E2,A2:A6) 返回 E2 在 A2:A6 中的位置，默认精确匹配。它还能在不排序的情况下查找下一个较小值或较大值，从底部开始查找，以及使用通配符。
- 多条件查找=XLOOKUP(1,(A2:A7=E2)*(B2:B7=F2),C2:C7) 返回 A 列等于 E2 且 B 列等于 F2 的那一行中的值。本文还介绍 INDEX MATCH 写法、VLOOKUP 的辅助列，以及用 FILTER 返回所有匹配项。
- VLOOKUP 与 XLOOKUPVLOOKUP 能做的 XLOOKUP 都能做，而且默认精确匹配、不用列号、能向左查找，还有“未找到”参数。当文件必须在 Excel 2019 或更早版本中打开时，仍然要用 VLOOKUP。
- INDIRECT=INDIRECT("C"&E2) 读取地址以文本形式拼出的单元格：C 列，第 E2 行。用它按单元格中的名称选择工作表、用数字构造区域，以及制作二级联动下拉列表。
- OFFSET=OFFSET(A1,3,2) 返回从 A1 向下 3 行、向右 2 列的单元格。加上高度后它会返回整个区域，这正是计算最后 N 行合计或滚动平均值的方法。
- CHOOSE=CHOOSE(B2,"Low","Medium","High") 在 B2 为 1 时返回 Low，为 2 时返回 Medium，为 3 时返回 High。把数字映射成名称、选择要求和的区域、替代嵌套 IF，以及用 CHOOSECOLS 选取列。
按条件计数与求和
- COUNTIF=COUNTIF(B2:B7,"North") 统计 B2:B7 中内容为 North 的单元格。在可编辑的在线表格中按文本、数字、通配符、空白和日期计数，并找出重复项。
- COUNTIFS=COUNTIFS(A2:A7,"North",C2:C7,">50") 统计地区为 North 且销售额大于 50 的行。在线表格演示如何统计两个数字或两个日期之间的数量、使用“或”逻辑以及处理空白。
- SUMIF=SUMIF(A2:A7,"North",C2:C7) 对 A 列为 North 的各行中 C2:C7 的值求和。在可编辑的表格中练习大于某个值求和、包含某段文本求和、按日期求和以及跨工作表求和。
- SUMIFS=SUMIFS(C2:C7,A2:A7,"North",B2:B7,"Apple") 把地区为 North 且产品为 Apple 的 C2:C7 销售额加起来。日期范围、“或”逻辑和可选筛选条件，都配有可编辑的表格。
- AVERAGEIF=AVERAGEIF(A2:A7,"North",C2:C7) 对 A 列为 North 的行求 C2:C7 中数值的平均值。本文还讲解多条件的 AVERAGEIFS、忽略 0 的平均值、#DIV/0! 的解决方法，以及 MAXIFS 和 MINIFS。
- 统计文本单元格=COUNTIF(A2:A8,"*") 统计 A2:A8 中包含文本的单元格，跳过数字、日期和空单元格。还能统计包含某个词的单元格，以及在单元格包含文本时返回一个值。
- COUNTIF 非空=COUNTIF(B2:B8,"<>") 统计 B2:B8 中不为空的单元格，结果与 COUNTA 相同。用 COUNTIFS 加上其他条件，并处理只是看起来为空的单元格。
- 统计不重复值=COUNTA(UNIQUE(A2:A9)) 统计 A2:A9 中有多少个不同的值。旧版 Excel 使用 =SUMPRODUCT(1/COUNTIF(A2:A9,A2:A9))。还能统计只出现一次的值、按条件统计，以及跳过空白。
- SUMPRODUCT=SUMPRODUCT(B2:B6,C2:C6) 把每个数量乘以对应的单价，再把结果加起来。配合 (A2:A7="North")*C2:C7 这样的条件，它能完成 SUMIFS 做不到的求和与计数：按月份、两列比较、“或”条件。
- SUBTOTAL=SUBTOTAL(9,C2:C8) 像 SUM 一样对 C2:C8 求和，但会忽略区域中其他 SUBTOTAL 行和被筛选隐藏的行。本文讲解函数编号 9 和 109、统计可见行数，以及忽略错误的 AGGREGATE。
- 加权平均=SUMPRODUCT(B2:B5,C2:C5)/SUM(C2:C5) 就是加权平均：每个值乘以它的权重，乘积相加，再用总和除以权重之和。适用于成绩、按学分计算的 GPA 和按数量计算的价格。
文本
- 连接文本`=A2&" "&B2` 把 A2 和 B2 中的文本用一个空格连接起来。CONCATENATE 和 CONCAT 的作用相同；连接数字和日期时，用 TEXT 让它们保持易读的格式。
- TEXTJOIN`=TEXTJOIN(", ",TRUE,A2:A6)` 把 A2:A6 的每个单元格合并成一段文本，各项之间用逗号加空格分隔，并跳过空单元格。再加上 FILTER，就能只合并满足条件的行。
- 拆分文本`=TEXTBEFORE(A2," ")` 从 `Ana Silva` 中返回名字，`=TEXTAFTER(A2," ")` 返回姓氏。TEXTSPLIT 一次把一个单元格拆分成多列；在旧版 Excel 中，LEFT、MID 和 FIND 也能做到。
- LEFT、RIGHT、MID`=LEFT(A2,3)` 返回 A2 的前 3 个字符，`=RIGHT(A2,2)` 返回最后 2 个，`=MID(A2,5,4)` 从第 5 个字符开始返回 4 个字符。长度不固定时，把它们与 FIND 和 LEN 搭配使用。
- FIND 和 SEARCH`=SEARCH("apple",A2)` 返回 `apple` 在 A2 中开始的位置，不区分大小写。FIND 的作用相同，但区分大小写。文本不存在时两者都返回 #VALUE!，用 ISNUMBER 就能把它变成“单元格是否包含”的判断。
- SUBSTITUTE、REPLACE`=SUBSTITUTE(A2,"-","")` 删除 A2 中的所有短横线：SUBSTITUTE 按内容匹配来替换文本。REPLACE 按位置替换：`=REPLACE(A2,1,3,"XYZ")` 覆盖前 3 个字符。
- TRIM`=TRIM(A2)` 删除 A2 文本前后的空格，并把单词之间连续的多个空格变成一个。SUBSTITUTE 可以删除所有空格，或删除 TRIM 处理不了的不间断空格。
- UPPER、LOWER、PROPER`=UPPER(A2)` 把 A2 中的所有字母变成大写，`=LOWER(A2)` 把它们全部变成小写，`=PROPER(A2)` 把每个单词的首字母变成大写。只想把整段文本的首字母大写，就组合使用 UPPER、LEFT 和 MID。
- LEN`=LEN(A2)` 返回 A2 中的字符数，空格和标点都算在内。搭配 TRIM 和 SUBSTITUTE 可以统计单词数，搭配 SUM 可以统计整个区域的字符数。
- TEXT`=TEXT(A2,"mmm d, yyyy")` 把 A2 中的日期变成 `Mar 15, 2026` 这样的文本，`=TEXT(B2,"$#,##0.00")` 把 1250.5 变成 `$1,250.50`。结果是文本，所以只用于标签，不要再拿来计算。
- 文本转换为数字`=VALUE(A2)` 把 `'120` 这样以文本形式存储的数字变成数字 120。两个负号 `=--A2` 也能做到，NUMBERVALUE 能处理用逗号作小数点的数字，“转换为数字”则可以就地修正单元格。
- 单元格内换行在单元格中输入时按 Alt+Enter 即可换行（Mac 上按 Control+Option+Return）。在公式中，`CHAR(10)` 就是换行符：`=A2&CHAR(10)&B2` 把 B2 放到第二行，打开“自动换行”后即可看到。
- 前导零Excel 会去掉前导零，因为 `00742` 就是数字 742。用 `00000` 这样的自定义数字格式、撇号（`'00742`）或文本格式保留它们，或者用 `=TEXT(A2,"00000")` 补上。
- 通配符在 Excel 的条件中，`*` 代表任意数量的字符，`?` 代表恰好一个字符：`=COUNTIF(A2:A7,"*apple*")` 统计包含 `apple` 的单元格。`~` 把通配符还原成普通字符。
日期与时间
- 计算年龄=DATEDIF(B2,TODAY(),"Y") 返回出生日期在 B2 的人的周岁年龄。还能计算某个指定日期的年龄、几岁几个月几天，以及不用 DATEDIF 的算法。
- DATEDIF=DATEDIF(A2,B2,"M") 计算 A2 中的开始日期到 B2 中的结束日期之间相差的整月数。本文讲解 Y、M、D、YM、MD 和 YD 六种单位，DATEDIF 为什么不在函数列表里，以及 #NUM! 错误。
- 日期相差天数=B2-A2 返回 A2 中的日期到 B2 中较晚日期之间相差的天数。本文讲解用 DAYS 计算天数、首尾两天都算在内，以及改为计算周数、月数、年数或工作日。
- 星期几=TEXT(A2,"dddd") 返回 A2 中日期的星期名称，例如 Monday，=WEEKDAY(A2) 则以数字形式返回。还包括简写名称、WEEKDAY 的返回类型和判断周末。
- TODAY 和 NOW=TODAY() 返回今天的日期，=NOW() 返回当前的日期和时间，两者在工作表每次重新计算时都会更新。学习计算距某个日期还剩几天，以及用 Ctrl+; 插入不会变化的日期。
- 日期加天数和月数=A2+30 返回 A2 之后 30 天的日期。要加月数用 =EDATE(A2,3)，求月末用 =EOMONTH(A2,0)，加年数则用 EDATE，每年按 12 个月计算。
- NETWORKDAYS 与 WORKDAY=NETWORKDAYS(A2,B2) 计算从 A2 到 B2 的工作日（星期一至星期五）天数，两端日期都计入。=WORKDAY(A2,10) 返回 A2 之后第 10 个工作日的日期。两个函数都可以跳过节假日列表。
- DATE、YEAR、MONTH、DAY=DATE(2026,3,15) 用年、月、日三个数返回 2026 年 3 月 15 日这个日期。YEAR、MONTH 和 DAY 把日期拆开，而 DATE 会把第 13 个月自动转入下一年。
- 时间计算=B2-A2 返回 A2 的开始时间到 B2 的结束时间之间的时长：设置为 h:mm 格式显示 8:30，乘以 24 得到 8.5 小时。还包括跨午夜的班次、超过 24 小时的合计，以及按工时算工资。
- 周数=WEEKNUM(A2) 返回 A2 中日期的周数，每周从星期日开始。=ISOWEEKNUM(A2) 返回欧洲通用的 ISO 周数，每周从星期一开始。还包括求一周的开始日期，以及根据周数求日期。
数学与统计
- ROUND=ROUND(A2,2) 把 A2 中的数字四舍五入到两位小数，=ROUND(A2,0) 四舍五入到最接近的整数。位数为负数时舍入到十位、百位和千位；MROUND 可以舍入到任意倍数。
- ROUNDUP / ROUNDDOWN=ROUNDUP(A2,0) 总是向远离零的方向舍入，所以 2.1 变成 3；=ROUNDDOWN(A2,0) 总是向零的方向舍入，所以 2.9 变成 2。CEILING 和 FLOOR 向上或向下舍入到倍数，INT 和 TRUNC 去掉小数。
- 标准差=STDEV.S(B2:B9) 计算样本的标准差，=STDEV.P(B2:B9) 计算整个总体的标准差。除非你的数据就是全部的值，否则用 STDEV.S。VAR.S 和 VAR.P 计算方差。
- RANK=RANK.EQ(B2,$B$2:$B$7) 给出 B2 在 B2:B7 各值中的名次，最大值排第 1。把 1 作为第三个参数，就从最小值开始排。并列的值名次相同；COUNTIFS 可以做组内排名。
- 随机数=RANDBETWEEN(1,100) 返回 1 到 100 之间的随机整数，=RAND() 返回大于等于 0 且小于 1 的随机小数。RANDARRAY 可以填满整个区域，INDEX 配合 RANDBETWEEN 能随机抽取一项，选择性粘贴 > 值 可以固定结果。
- MOD 和 ABS=MOD(A2,B2) 返回 A2 除以 B2 后的余数，所以 =MOD(17,5) 是 2。=ABS(A2) 返回去掉符号的数字，所以无论哪个值更大，=ABS(B2-C2) 都是两个值之差。
- PMT=PMT(B2/12,B3*12,-B1) 返回贷款额 B1、年利率 B2、期限 B3 年的每月还款额。利率除以 12，年数乘以 12，并在贷款额前加负号，就能得到正数的还款额。
- NPV 与 IRR=NPV(E2,B3:B5)+B2 按 E2 的利率对未来现金流折现，再加上 B2 的初始投资，初始投资不能放进 NPV 里折现。=IRR(B2:B5) 返回让这个 NPV 等于零的利率。XNPV 和 XIRR 支持真实日期。
- CAGR=(B2/A2)^(1/C2)-1 计算从 A2 的起始值到 B2 的结束值、历时 C2 年的复合年增长率。=RRI(C2,A2,B2) 返回同样的增长率。把单元格设置为百分比格式即可。
动态数组
- FILTER=FILTER(A2:C7,B2:B7="North") 返回 A2:C7 中地区为 North 的每一行，数据变化时结果会自动更新。本文讲解用 * 和 + 组合多个条件、if_empty、#CALC! 错误以及对结果排序。
- UNIQUE=UNIQUE(B2:B8) 按首次出现的顺序把 B2:B8 中的每个值返回一次，列表变化时结果会自动更新。学习提取唯一行、exactly_once、排序后的唯一值列表、统计不重复值的个数，以及把结果用作下拉列表的来源。
- SORT 和 SORTBY=SORT(A2:C7,3,-1) 返回按第三列从大到小排序的 A2:C7 表格，数据变化时会自动重新排序。SORTBY 可以按任意区域排序，包括多列排序和自定义顺序。
- SEQUENCE=SEQUENCE(5) 在一列中向下返回数字 1 到 5，=SEQUENCE(3,4) 填满 3 行 4 列。加上起始值和步长，就能生成任意序列，包括日期、随列表增长的行号和月历。
- TRANSPOSE=TRANSPOSE(A1:D3) 把 A1:D3 的行变成列，并与源数据保持联动。只需复制一次的话，用 选择性粘贴 > 转置。TOCOL 能把整个区域堆叠成一列。
- LET=LET(total,SUM(B2:B6),IF(total>500,total*0.9,total)) 只计算一次求和，把它命名为 total，再使用这个名称两次。LET 让长公式更短、更易读、也更快，因为每个命名的部分只计算一次。
- LAMBDA=LAMBDA(price,price*1.2)(B2) 定义一个只有一个输入 price 的小函数，并直接对 B2 调用它。把 LAMBDA 保存到名称管理器中，就能像内置函数一样使用，也可以把它传给 MAP、BYROW、SCAN 和 REDUCE。
错误与排查
- #SPILL! 错误#SPILL! 表示一个返回多个值的公式没有地方放结果：它的溢出区域中有单元格不是空的。清空挡路的单元格，结果就会出现。
- #VALUE! 错误#VALUE! 表示公式得到了类型不对的值，最常见的是需要数字的地方出现了文本：C2 中是 "n/a" 或一个空格时，=B2+C2 就会出错。SUM 会忽略文本，所以 =SUM(B2:C2) 能正常计算。
- #NAME? 错误#NAME? 表示 Excel 不认识公式中的某个词：拼错的函数名（例如 =SUMM(B2:B6)）、没加引号的文本、区域中漏了冒号、未定义的名称，或者你的 Excel 版本没有的函数。
- #REF! 错误#REF! 表示公式引用了一个已不存在的单元格，通常是因为它用到的行、列或工作表被删除了：=B2*C2 变成 =B2*#REF!。当 VLOOKUP 或 INDEX 请求的列或行超出其区域时，也会出现这个错误。
- #N/A 错误#N/A 表示查找函数没有找到要找的值。检查拼写错误、多余的空格，以及公式向下填充时跟着移动的表格区域，再用 IFNA 为确实不存在的值显示一条提示。
- #DIV/0! 错误公式除以零或空单元格时会出现 #DIV/0!，例如 C2 为空时的 =B2/C2。=IF(C2=0,"",B2/C2) 会改为显示空白单元格；对没有数字的区域求 AVERAGE 也会返回这个错误。
- 循环引用循环引用是指公式直接或通过其他公式引用了自己所在的单元格，例如在 B7 中输入 =SUM(B2:B7)。Excel 会弹出警告、显示 0，并在“公式 > 错误检查 > 循环引用”下列出该单元格。
- 公式不计算如果 Excel 显示的是公式而不是结果，说明单元格是文本格式、公式以撇号或空格开头，或者打开了“显示公式”。如果结果不更新，说明计算被设成了手动：公式 > 计算选项 > 自动。
数据工具
- 删除重复值选中数据并点击 数据 > 删除重复值，就地删除重复的行；或者用 =UNIQUE(A2:A9) 得到一份干净的副本并保留原始数据。查找、标记和统计重复项，以及按两列删除重复值。
- 高亮重复值选中单元格，选择“开始 > 条件格式 > 突出显示单元格规则 > 重复值”。如果要高亮整行、只高亮第二次出现的值或比较两列，就用 =COUNTIF($A$2:$A$9,A2)>1 这样的公式规则。
- 条件格式条件格式会在条件成立时给单元格着色。使用“开始 > 条件格式”中的预设规则，或通过“新建规则 > 使用公式”写一条像 =$C2>100 这样的规则，为整行、逾期日期和包含指定文本的单元格着色。
- 下拉列表选中单元格，打开“数据 > 数据验证”，选择“序列”，输入选项（North,South,East）或选择一个区域作为来源。然后用 UNIQUE 做成动态列表、做成随另一个列表变化的二级下拉，并查找所选项的值。
- 比较两列逐行比较两列用 =A2=B2（区分大小写用 EXACT）。要找出一列中有、另一列中没有的值，用 COUNTIF、MATCH 或 XLOOKUP，再用条件格式突出显示差异。
- 数据透视表数据透视表按某个类别对表格中的行分组，并为每组汇总一个数字，不需要公式：插入 > 数据透视表，然后把字段拖到“行”和“值”。本文介绍操作步骤、四个区域的含义，以及用公式做出同样的汇总。
