先说一个容易被绕晕的常识:公式和函数不是一回事。你在格子里打的任何以 = 开头的东西都是公式;函数是公式里调用的、有名字的运算块。一个公式里可以塞很多函数,=IF(SUM(A1:A9)>100,"超","未超") 就是一个公式套了两个函数。
另一个常被引用的数字是「Excel 有 341 个函数」。这个数来自微软官方的「Excel 规格与限制」页,但它长期没有更新,反映的是很早以前的口径。按函数目录实际清点,现代 Excel 内置的工作表函数已经超过 500 个,再加上兼容函数、加载项函数和 Web 函数,总数还会更多。所以「500 余个」是更接近实际的描述。
01数学与三角函数
最基础的一类,但条件求和相关的几个才是日常主力。
| 函数 | 语法 | 用途 |
|---|---|---|
| SUM | SUM(数值1,[数值2],…) | 求和。对区域求和最常用,忽略文本与空单元格 |
| SUMIF | SUMIF(条件区域,条件,[求和区域]) | 单条件求和。求和区域可省略,省略时对条件区域自身求和 |
| SUMIFS | SUMIFS(求和区域,条件区域1,条件1,…) | 多条件求和。求和区域在最前面,与 SUMIF 的参数顺序相反,这是最常写错的地方 |
| SUMPRODUCT | SUMPRODUCT(数组1,[数组2],…) | 数组乘积求和。也常被用来做多条件计数,如 SUMPRODUCT((A2:A9="华东")*1) |
| ROUND | ROUND(数值,位数) | 四舍五入。位数写 0 取整,写 -1 舍入到十位 |
| ROUNDUP | ROUNDUP(数值,位数) | 向上舍入,远离零的方向 |
| ROUNDDOWN | ROUNDDOWN(数值,位数) | 向下舍入,靠近零的方向 |
| INT | INT(数值) | 向下取整到整数。负数时注意:INT(-1.5) 得 -2,不是 -1 |
| MOD | MOD(被除数,除数) | 取余数。常用于判断奇偶:MOD(A1,2)=0 为偶数 |
| ABS | ABS(数值) | 取绝对值 |
| SUBTOTAL | SUBTOTAL(功能号,引用1,…) | 对可见单元格汇总。功能号 9 为求和、1 为平均、2 为计数、3 为非空计数;加 100 则连隐藏行也算 |
| AGGREGATE | AGGREGATE(功能号,选项,引用) | 比 SUBTOTAL 更强,可忽略错误值。选项 6 表示忽略错误值 |
| POWER | POWER(底数,指数) | 乘幂,等同 ^ 运算符 |
| SQRT | SQRT(数值) | 平方根,负数会返回 #NUM! |
02统计函数
和条件求和是同一套逻辑,只是把「加总」换成「计数」「平均」「取极值」。
| 函数 | 语法 | 用途 |
|---|---|---|
| AVERAGE | AVERAGE(数值1,…) | 算术平均,忽略文本与空单元格 |
| AVERAGEIF | AVERAGEIF(条件区域,条件,[平均区域]) | 单条件平均 |
| AVERAGEIFS | AVERAGEIFS(平均区域,条件区域1,条件1,…) | 多条件平均,同样把平均区域放最前 |
| COUNT | COUNT(值1,…) | 只数数字的个数。文本型数字不算 |
| COUNTA | COUNTA(值1,…) | 数非空单元格,文本数字都算 |
| COUNTBLANK | COUNTBLANK(区域) | 数空单元格。空字符串 "" 也算空 |
| COUNTIF | COUNTIF(区域,条件) | 单条件计数。条件支持通配符 * 与 ? |
| COUNTIFS | COUNTIFS(区域1,条件1,…) | 多条件计数,各区域大小必须一致 |
| MAX | MAX(数值1,…) | 最大值。全区域无数字时返回 0 |
| MIN | MIN(数值1,…) | 最小值。同上,全无数字时返回 0 而不是空 |
| MEDIAN | MEDIAN(数值1,…) | 中位数,不受极端值影响,比平均值更能反映典型水平 |
| LARGE | LARGE(数组,k) | 第 k 大的值。LARGE(A1:A9,2) 取第二大 |
| SMALL | SMALL(数组,k) | 第 k 小的值 |
| RANK.EQ | RANK.EQ(数值,引用,[排序方式]) | 排名。排序方式 0 为降序(默认),1 为升序 |
03逻辑函数
写判断的骨架。IFS 和 SWITCH 是为了少写嵌套 IF 而生的。
| 函数 | 语法 | 用途 |
|---|---|---|
| IF | IF(条件,为真时,为假时) | 最基础的分支。第三参数省略时,条件不成立返回 FALSE |
| IFS | IFS(条件1,结果1,条件2,结果2,…) | 多分支平铺,从左到右第一个成立的条件生效,不再需要层层嵌套 |
| AND | AND(条件1,…) | 全部成立才为真。在数组公式里要改成乘法 * |
| OR | OR(条件1,…) | 任一成立即为真。数组里改用加法 + |
| NOT | NOT(条件) | 取反 |
| XOR | XOR(条件1,…) | 异或:成立的个数为奇数时返回真 |
| IFERROR | IFERROR(值,出错时) | 把任何错误换成指定值。用于清理 #N/A、#DIV/0! |
| IFNA | IFNA(值,为 #N/A 时) | 只拦 #N/A,其他错误照常抛出。排查查找失败时比 IFERROR 更精准 |
| SWITCH | SWITCH(表达式,值1,结果1,…,默认值) | 按值匹配分支,适合「等于某个值就返回某个结果」的等值判断 |
IFS 和 SWITCH 并不是 IF 的升级替代。IFS 是平铺多个条件,SWITCH 是等值匹配。要做区间判断(如分数段评等级),用 IFS 更顺;要做代码映射(如 A→甲、B→乙),用 SWITCH 更短。04查找与引用函数
用得最多、也最容易出错的一类。XLOOKUP 出现后,VLOOKUP 不再是首选,但老表格里还是到处都是。
| 函数 | 语法 | 用途 |
|---|---|---|
| XLOOKUP | XLOOKUP(查找值,查找数组,返回数组,[未找到],[匹配模式],[搜索模式]) | 首选查找函数。可向左查、可指定未找到时的返回值、默认精确匹配 |
| VLOOKUP | VLOOKUP(查找值,区域,列序号,[近似匹配]) | 只能从左往右查。第 4 参数写 FALSE 才是精确匹配,省略会走近似匹配,这是最常见的错误来源 |
| HLOOKUP | HLOOKUP(查找值,区域,行序号,[近似匹配]) | 横向查找,表头在第一行时使用 |
| INDEX | INDEX(区域,行号,[列号]) | 按位置取值。常与 MATCH 搭配做双向查找 |
| MATCH | MATCH(查找值,区域,[匹配类型]) | 返回位置序号。匹配类型 0 为精确匹配 |
| XMATCH | XMATCH(查找值,数组,[匹配模式],[搜索模式]) | MATCH 的增强版,支持反向搜索与通配符开关 |
| LOOKUP | LOOKUP(查找值,查找向量,[结果向量]) | 老函数,但现在常被用来做「最后一个非空值」等技巧 |
| CHOOSE | CHOOSE(序号,值1,…) | 按序号从列表里挑一个 |
| OFFSET | OFFSET(起点,行偏移,列偏移,[高度],[宽度]) | 生成偏移引用。易失函数,大量使用会拖慢重算 |
| INDIRECT | INDIRECT(文本引用) | 把文本变成引用。二级联动下拉的核心,也是易失函数 |
| ROW / COLUMN | ROW([引用]) | 取行号 / 列号。省略参数时取当前单元格 |
| ADDRESS | ADDRESS(行,列,[类型],[风格]) | 拼出单元格地址文本,如 $A$1 |
| HYPERLINK | HYPERLINK(链接位置,[显示文字]) | 生成可点击链接,也可指向同一工作簿内的位置 |
| FILTER | FILTER(数组,条件,[无结果时]) | 按条件筛出多行。结果溢出到下方,属动态数组 |
05文本函数
数据清洗的主战场。近两年新增的正则三件套值得单独留意。
| 函数 | 语法 | 用途 |
|---|---|---|
| LEFT / RIGHT | LEFT(文本,[个数]) | 从左侧 / 右侧截取指定长度 |
| MID | MID(文本,起始位置,个数) | 从中间截取。起始位置从 1 开始,不是 0 |
| LEN | LEN(文本) | 字符长度。一个汉字算 1 个字符,全角符号也算 1 个 |
| FIND | FIND(要找的,文本,[起始位置]) | 区分大小写,不支持通配符。找不到返回 #VALUE! |
| SEARCH | SEARCH(要找的,文本,[起始位置]) | 不区分大小写,支持通配符 |
| SUBSTITUTE | SUBSTITUTE(文本,旧,新,[第几个]) | 按内容替换。批量插换行符就是用它把分隔符换成 CHAR(10) |
| REPLACE | REPLACE(文本,起始位置,个数,新文本) | 按位置替换。脱敏手机号常用 |
| TRIM | TRIM(文本) | 删掉首尾空格,中间连续空格压成一个。不处理换行符 |
| CLEAN | CLEAN(文本) | 删掉不可打印字符,导入数据清理时配合 TRIM 用 |
| CONCAT | CONCAT(文本1,…) | 拼接,可接区域。替代已废弃的 CONCATENATE |
| TEXTJOIN | TEXTJOIN(分隔符,是否忽略空,文本1,…) | 带分隔符拼接,第二个参数 TRUE 时跳过空值 |
| TEXT | TEXT(值,格式代码) | 把数值转成指定格式的文本,如 TEXT(A1,"0000") 补足四位 |
| TEXTBEFORE | TEXTBEFORE(文本,分隔符,[第几次]) | 取分隔符之前的部分,比 LEFT+FIND 组合干净 |
| TEXTAFTER | TEXTAFTER(文本,分隔符,[第几次]) | 取分隔符之后的部分 |
| TEXTSPLIT | TEXTSPLIT(文本,列分隔符,[行分隔符]) | 按分隔符拆成多格,结果横向或纵向溢出 |
| REGEXEXTRACTNEW | REGEXEXTRACT(文本,正则,[返回模式]) | 按正则提取匹配内容,处理混合格式数据非常省事 |
REGEXTEST 判断是否匹配、REGEXEXTRACT 提取匹配部分、REGEXREPLACE 替换匹配部分。它们让很多原本需要 MID+FIND 嵌套的清洗工作变成一条公式。注意这是较新版本才有的函数,在旧版 Excel 或部分企业通道中可能不可用。06日期与时间函数
Excel 把日期存成一个序列号,1900 年 1 月 1 日是 1。理解这点,日期运算就不会神秘。
| 函数 | 语法 | 用途 |
|---|---|---|
| TODAY | TODAY() | 当前日期,无参数。每次重算都会更新 |
| NOW | NOW() | 当前日期加时间 |
| DATE | DATE(年,月,日) | 拼出日期。月或日超出范围时会自动进位 |
| YEAR / MONTH / DAY | YEAR(日期) | 取出年 / 月 / 日部分 |
| DATEDIF | DATEDIF(开始,结束,单位) | 算年龄、工龄。单位 "Y" 整年、"M" 整月、"D" 天数。函数存在但不在函数列表里,需手输 |
| EDATE | EDATE(开始日期,月数) | 前后推几个月,自动处理月末。EDATE(日期,-1) 是上月同日 |
| EOMONTH | EOMONTH(开始日期,月数) | 取某月最后一天。EOMONTH(日期,0) 是本月末 |
| WORKDAY | WORKDAY(开始日期,工作日数,[假期]) | 跳过周末与假期的 N 个工作日后是哪天 |
| NETWORKDAYS | NETWORKDAYS(开始,结束,[假期]) | 两个日期之间的工作日天数 |
| WEEKNUM | WEEKNUM(日期,[返回类型]) | 该日期是当年第几周。返回类型 2 表示周一为一周起点 |
07财务函数
做贷款、折旧、投资测算时用。参数正负号有约定:现金流出为负,流入为正。
| 函数 | 语法 | 用途 |
|---|---|---|
| PMT | PMT(利率,期数,现值,[终值],[付款类型]) | 等额本息每期还款额。算支出时结果为负 |
| FV | FV(利率,期数,每期付款,[现值],[类型]) | 未来值,用于测算定投积累 |
| PV | PV(利率,期数,每期付款,[终值],[类型]) | 现值,用于判断一笔未来收益现在值多少 |
| RATE | RATE(期数,每期付款,现值,[终值],[类型],[猜测]) | 反推利率。常需给一个猜测值帮助收敛 |
| NPER | NPER(利率,每期付款,现值,…) | 反推期数,比如还清贷款要多少个月 |
| NPV | NPV(折现率,现金流1,…) | 净现值。注意它假设现金流发生在期末 |
| IRR | IRR(现金流数组,[猜测]) | 内部收益率,要求现金流按期间等间隔排列 |
| XIRR | XIRR(现金流,日期流,[猜测]) | 不定期现金流的内部收益率,实际项目测算更常用 |
| SLN | SLN(原值,残值,年限) | 直线法折旧,每期金额相同 |
| DDB | DDB(原值,残值,年限,期间,[系数]) | 双倍余额递减折旧,前期折旧多 |
08动态数组函数
这类函数一个公式就能输出一片结果,结果自动「溢出」到相邻单元格。它们改变了 Excel 的建表方式,但也带来一个新问题:溢出区域被挡住时会返回 #SPILL!。
| 函数 | 语法 | 用途 |
|---|---|---|
| UNIQUE | UNIQUE(数组,[按列],[仅出现一次]) | 去重取唯一值。做名单、做分类清单的第一选择 |
| SORT | SORT(数组,[排序列],[排序方向],[按行]) | 排序,方向 1 升序 -1 降序 |
| SORTBY | SORTBY(数组,排序依据1,[方向1],…) | 按另一列的顺序排,可多级 |
| FILTER | FILTER(数组,条件,[无结果]) | 按条件筛行,第三参数给「无结果」时的提示文字 |
| SEQUENCE | SEQUENCE(行数,[列数],[起始],[步长]) | 生成数字序列,做编号、做日历很方便 |
| RANDARRAY | RANDARRAY([行],[列],[最小],[最大],[整数]) | 生成随机数组,做抽样或测试数据 |
| VSTACK | VSTACK(数组1,…) | 纵向堆叠多个区域,合并多表最省事 |
| HSTACK | HSTACK(数组1,…) | 横向拼接多个区域 |
| TOROW / TOCOL | TOCOL(数组,[忽略项],[扫描方式]) | 把多行多列压成一行 / 一列 |
| WRAPROWS | WRAPROWS(向量,每行个数) | 把一列按固定个数折成多行,排座位表常用 |
| TAKE / DROP | TAKE(数组,行数,[列数]) | 取前 N 行 / 丢掉前 N 行 |
| CHOOSECOLS | CHOOSECOLS(数组,列序号,…) | 按序号挑列,重新排列列顺序 |
| GROUPBYNEW | GROUPBY(行字段,值,…) | 用公式做分组汇总,相当于公式版数据透视表 |
| PIVOTBYNEW | PIVOTBY(行字段,列字段,值,…) | 公式版交叉表,行列双向分组 |
09信息函数与其他
数量不多,但排查问题时离不开。判断单元格「是什么」,比判断「等于什么」更有用。
| 函数 | 语法 | 用途 |
|---|---|---|
| ISBLANK | ISBLANK(值) | 判断是否真空。含 "" 的单元格返回 FALSE,这点常被误解 |
| ISNUMBER | ISNUMBER(值) | 判断是否为数字。查出「看起来是数字其实是文本」的关键 |
| ISTEXT | ISTEXT(值) | 判断是否为文本 |
| ISERROR | ISERROR(值) | 判断是否为任意错误值 |
| ISNA | ISNA(值) | 只判断 #N/A,查找失败时用得上 |
| TYPE | TYPE(值) | 返回类型代码:1 数字、2 文本、4 逻辑值、16 错误值、64 数组 |
| CELL | CELL(信息类型,[引用]) | 取单元格格式信息,如 "format" 取数字格式代码 |
| CONVERT | CONVERT(数值,原单位,目标单位) | 单位换算,如把英里转公里 |
怎么快速找到一个函数
三个实用路径,比背函数名有效得多:
一是输入前几个字母看提示。在格子里打 =VL,下方会列出所有以 VL 开头的函数,方向键选中按 Tab 补全,此时还会显示参数提示。
二是用「插入函数」对话框。点编辑栏左侧的 fx,可以按类别浏览,也能看到每个参数的文字说明。找不常用的函数时这个最快。
三是搜官方函数列表。微软支持站点有按字母排序的完整函数清单,含每个函数的详细语法和示例。第三方函数目录站通常按使用频率排序,更适合「不知道用哪个」的阶段。
下一步
会写单个函数之后,真正拉开差距的是嵌套与排错。为什么 #VALUE! 出现、为什么 VLOOKUP 返回了错的行、为什么数组公式结果只有一个值——这些在 Excel 公式 页逐条拆过。需要把函数用在具体场景里,可以看 下拉选项(数据验证 + INDIRECT 联动)和 表格内换行(SUBSTITUTE + CHAR 批量处理)。