fxExcel 使用指南
首页 / 函数公式大全
FUNCTION REFERENCE

Excel 函数公式大全:按类别速查 500 余个函数

不是把所有函数列一遍,而是把真正会用到的按用途分组,给语法、给人话解释、给易错点。要查某个具体函数怎么拼,这页够用;要理解公式为什么报错,去 公式页。

ENGINE LIMITS

函数嵌套层数
64
单函数参数上限
255
公式长度
8,192 字符
内部字节
16,384 B

先说一个容易被绕晕的常识:公式和函数不是一回事。你在格子里打的任何以 = 开头的东西都是公式;函数是公式里调用的、有名字的运算块。一个公式里可以塞很多函数,=IF(SUM(A1:A9)>100,"超","未超") 就是一个公式套了两个函数。

另一个常被引用的数字是「Excel 有 341 个函数」。这个数来自微软官方的「Excel 规格与限制」页,但它长期没有更新,反映的是很早以前的口径。按函数目录实际清点,现代 Excel 内置的工作表函数已经超过 500 个,再加上兼容函数、加载项函数和 Web 函数,总数还会更多。所以「500 余个」是更接近实际的描述。

怎么用这张表:每一行给的是「函数名 / 语法 / 说明」。语法里的方括号表示可选参数,实际写公式时不要输入方括号。所有函数名不区分大小写,但习惯上写成大写更好读。

01数学与三角函数

最基础的一类,但条件求和相关的几个才是日常主力。

函数语法用途
SUMSUM(数值1,[数值2],…)求和。对区域求和最常用,忽略文本与空单元格
SUMIFSUMIF(条件区域,条件,[求和区域])单条件求和。求和区域可省略,省略时对条件区域自身求和
SUMIFSSUMIFS(求和区域,条件区域1,条件1,…)多条件求和。求和区域在最前面,与 SUMIF 的参数顺序相反,这是最常写错的地方
SUMPRODUCTSUMPRODUCT(数组1,[数组2],…)数组乘积求和。也常被用来做多条件计数,如 SUMPRODUCT((A2:A9="华东")*1)
ROUNDROUND(数值,位数)四舍五入。位数写 0 取整,写 -1 舍入到十位
ROUNDUPROUNDUP(数值,位数)向上舍入,远离零的方向
ROUNDDOWNROUNDDOWN(数值,位数)向下舍入,靠近零的方向
INTINT(数值)向下取整到整数。负数时注意:INT(-1.5) 得 -2,不是 -1
MODMOD(被除数,除数)取余数。常用于判断奇偶:MOD(A1,2)=0 为偶数
ABSABS(数值)取绝对值
SUBTOTALSUBTOTAL(功能号,引用1,…)对可见单元格汇总。功能号 9 为求和、1 为平均、2 为计数、3 为非空计数;加 100 则连隐藏行也算
AGGREGATEAGGREGATE(功能号,选项,引用)比 SUBTOTAL 更强,可忽略错误值。选项 6 表示忽略错误值
POWERPOWER(底数,指数)乘幂,等同 ^ 运算符
SQRTSQRT(数值)平方根,负数会返回 #NUM!

02统计函数

和条件求和是同一套逻辑,只是把「加总」换成「计数」「平均」「取极值」。

函数语法用途
AVERAGEAVERAGE(数值1,…)算术平均,忽略文本与空单元格
AVERAGEIFAVERAGEIF(条件区域,条件,[平均区域])单条件平均
AVERAGEIFSAVERAGEIFS(平均区域,条件区域1,条件1,…)多条件平均,同样把平均区域放最前
COUNTCOUNT(值1,…)只数数字的个数。文本型数字不算
COUNTACOUNTA(值1,…)数非空单元格,文本数字都算
COUNTBLANKCOUNTBLANK(区域)数空单元格。空字符串 "" 也算空
COUNTIFCOUNTIF(区域,条件)单条件计数。条件支持通配符 * 与 ?
COUNTIFSCOUNTIFS(区域1,条件1,…)多条件计数,各区域大小必须一致
MAXMAX(数值1,…)最大值。全区域无数字时返回 0
MINMIN(数值1,…)最小值。同上,全无数字时返回 0 而不是空
MEDIANMEDIAN(数值1,…)中位数,不受极端值影响,比平均值更能反映典型水平
LARGELARGE(数组,k)第 k 大的值。LARGE(A1:A9,2) 取第二大
SMALLSMALL(数组,k)第 k 小的值
RANK.EQRANK.EQ(数值,引用,[排序方式])排名。排序方式 0 为降序(默认),1 为升序

03逻辑函数

写判断的骨架。IFS 和 SWITCH 是为了少写嵌套 IF 而生的。

函数语法用途
IFIF(条件,为真时,为假时)最基础的分支。第三参数省略时,条件不成立返回 FALSE
IFSIFS(条件1,结果1,条件2,结果2,…)多分支平铺,从左到右第一个成立的条件生效,不再需要层层嵌套
ANDAND(条件1,…)全部成立才为真。在数组公式里要改成乘法 *
OROR(条件1,…)任一成立即为真。数组里改用加法 +
NOTNOT(条件)取反
XORXOR(条件1,…)异或:成立的个数为奇数时返回真
IFERRORIFERROR(值,出错时)把任何错误换成指定值。用于清理 #N/A、#DIV/0!
IFNAIFNA(值,为 #N/A 时)只拦 #N/A,其他错误照常抛出。排查查找失败时比 IFERROR 更精准
SWITCHSWITCH(表达式,值1,结果1,…,默认值)按值匹配分支,适合「等于某个值就返回某个结果」的等值判断
一个常见误解:IFS 和 SWITCH 并不是 IF 的升级替代。IFS 是平铺多个条件,SWITCH 是等值匹配。要做区间判断(如分数段评等级),用 IFS 更顺;要做代码映射(如 A→甲、B→乙),用 SWITCH 更短。

04查找与引用函数

用得最多、也最容易出错的一类。XLOOKUP 出现后,VLOOKUP 不再是首选,但老表格里还是到处都是。

函数语法用途
XLOOKUPXLOOKUP(查找值,查找数组,返回数组,[未找到],[匹配模式],[搜索模式])首选查找函数。可向左查、可指定未找到时的返回值、默认精确匹配
VLOOKUPVLOOKUP(查找值,区域,列序号,[近似匹配])只能从左往右查。第 4 参数写 FALSE 才是精确匹配,省略会走近似匹配,这是最常见的错误来源
HLOOKUPHLOOKUP(查找值,区域,行序号,[近似匹配])横向查找,表头在第一行时使用
INDEXINDEX(区域,行号,[列号])按位置取值。常与 MATCH 搭配做双向查找
MATCHMATCH(查找值,区域,[匹配类型])返回位置序号。匹配类型 0 为精确匹配
XMATCHXMATCH(查找值,数组,[匹配模式],[搜索模式])MATCH 的增强版,支持反向搜索与通配符开关
LOOKUPLOOKUP(查找值,查找向量,[结果向量])老函数,但现在常被用来做「最后一个非空值」等技巧
CHOOSECHOOSE(序号,值1,…)按序号从列表里挑一个
OFFSETOFFSET(起点,行偏移,列偏移,[高度],[宽度])生成偏移引用。易失函数,大量使用会拖慢重算
INDIRECTINDIRECT(文本引用)把文本变成引用。二级联动下拉的核心,也是易失函数
ROW / COLUMNROW([引用])取行号 / 列号。省略参数时取当前单元格
ADDRESSADDRESS(行,列,[类型],[风格])拼出单元格地址文本,如 $A$1
HYPERLINKHYPERLINK(链接位置,[显示文字])生成可点击链接,也可指向同一工作簿内的位置
FILTERFILTER(数组,条件,[无结果时])按条件筛出多行。结果溢出到下方,属动态数组

05文本函数

数据清洗的主战场。近两年新增的正则三件套值得单独留意。

函数语法用途
LEFT / RIGHTLEFT(文本,[个数])从左侧 / 右侧截取指定长度
MIDMID(文本,起始位置,个数)从中间截取。起始位置从 1 开始,不是 0
LENLEN(文本)字符长度。一个汉字算 1 个字符,全角符号也算 1 个
FINDFIND(要找的,文本,[起始位置])区分大小写,不支持通配符。找不到返回 #VALUE!
SEARCHSEARCH(要找的,文本,[起始位置])不区分大小写,支持通配符
SUBSTITUTESUBSTITUTE(文本,旧,新,[第几个])按内容替换。批量插换行符就是用它把分隔符换成 CHAR(10)
REPLACEREPLACE(文本,起始位置,个数,新文本)按位置替换。脱敏手机号常用
TRIMTRIM(文本)删掉首尾空格,中间连续空格压成一个。不处理换行符
CLEANCLEAN(文本)删掉不可打印字符,导入数据清理时配合 TRIM 用
CONCATCONCAT(文本1,…)拼接,可接区域。替代已废弃的 CONCATENATE
TEXTJOINTEXTJOIN(分隔符,是否忽略空,文本1,…)带分隔符拼接,第二个参数 TRUE 时跳过空值
TEXTTEXT(值,格式代码)把数值转成指定格式的文本,如 TEXT(A1,"0000") 补足四位
TEXTBEFORETEXTBEFORE(文本,分隔符,[第几次])取分隔符之前的部分,比 LEFT+FIND 组合干净
TEXTAFTERTEXTAFTER(文本,分隔符,[第几次])取分隔符之后的部分
TEXTSPLITTEXTSPLIT(文本,列分隔符,[行分隔符])按分隔符拆成多格,结果横向或纵向溢出
REGEXEXTRACTNEWREGEXEXTRACT(文本,正则,[返回模式])按正则提取匹配内容,处理混合格式数据非常省事
正则三件套:REGEXTEST 判断是否匹配、REGEXEXTRACT 提取匹配部分、REGEXREPLACE 替换匹配部分。它们让很多原本需要 MID+FIND 嵌套的清洗工作变成一条公式。注意这是较新版本才有的函数,在旧版 Excel 或部分企业通道中可能不可用。

06日期与时间函数

Excel 把日期存成一个序列号,1900 年 1 月 1 日是 1。理解这点,日期运算就不会神秘。

函数语法用途
TODAYTODAY()当前日期,无参数。每次重算都会更新
NOWNOW()当前日期加时间
DATEDATE(年,月,日)拼出日期。月或日超出范围时会自动进位
YEAR / MONTH / DAYYEAR(日期)取出年 / 月 / 日部分
DATEDIFDATEDIF(开始,结束,单位)算年龄、工龄。单位 "Y" 整年、"M" 整月、"D" 天数。函数存在但不在函数列表里,需手输
EDATEEDATE(开始日期,月数)前后推几个月,自动处理月末。EDATE(日期,-1) 是上月同日
EOMONTHEOMONTH(开始日期,月数)取某月最后一天。EOMONTH(日期,0) 是本月末
WORKDAYWORKDAY(开始日期,工作日数,[假期])跳过周末与假期的 N 个工作日后是哪天
NETWORKDAYSNETWORKDAYS(开始,结束,[假期])两个日期之间的工作日天数
WEEKNUMWEEKNUM(日期,[返回类型])该日期是当年第几周。返回类型 2 表示周一为一周起点

07财务函数

做贷款、折旧、投资测算时用。参数正负号有约定:现金流出为负,流入为正。

函数语法用途
PMTPMT(利率,期数,现值,[终值],[付款类型])等额本息每期还款额。算支出时结果为负
FVFV(利率,期数,每期付款,[现值],[类型])未来值,用于测算定投积累
PVPV(利率,期数,每期付款,[终值],[类型])现值,用于判断一笔未来收益现在值多少
RATERATE(期数,每期付款,现值,[终值],[类型],[猜测])反推利率。常需给一个猜测值帮助收敛
NPERNPER(利率,每期付款,现值,…)反推期数,比如还清贷款要多少个月
NPVNPV(折现率,现金流1,…)净现值。注意它假设现金流发生在期末
IRRIRR(现金流数组,[猜测])内部收益率,要求现金流按期间等间隔排列
XIRRXIRR(现金流,日期流,[猜测])不定期现金流的内部收益率,实际项目测算更常用
SLNSLN(原值,残值,年限)直线法折旧,每期金额相同
DDBDDB(原值,残值,年限,期间,[系数])双倍余额递减折旧,前期折旧多

08动态数组函数

这类函数一个公式就能输出一片结果,结果自动「溢出」到相邻单元格。它们改变了 Excel 的建表方式,但也带来一个新问题:溢出区域被挡住时会返回 #SPILL!。

函数语法用途
UNIQUEUNIQUE(数组,[按列],[仅出现一次])去重取唯一值。做名单、做分类清单的第一选择
SORTSORT(数组,[排序列],[排序方向],[按行])排序,方向 1 升序 -1 降序
SORTBYSORTBY(数组,排序依据1,[方向1],…)按另一列的顺序排,可多级
FILTERFILTER(数组,条件,[无结果])按条件筛行,第三参数给「无结果」时的提示文字
SEQUENCESEQUENCE(行数,[列数],[起始],[步长])生成数字序列,做编号、做日历很方便
RANDARRAYRANDARRAY([行],[列],[最小],[最大],[整数])生成随机数组,做抽样或测试数据
VSTACKVSTACK(数组1,…)纵向堆叠多个区域,合并多表最省事
HSTACKHSTACK(数组1,…)横向拼接多个区域
TOROW / TOCOLTOCOL(数组,[忽略项],[扫描方式])把多行多列压成一行 / 一列
WRAPROWSWRAPROWS(向量,每行个数)把一列按固定个数折成多行,排座位表常用
TAKE / DROPTAKE(数组,行数,[列数])取前 N 行 / 丢掉前 N 行
CHOOSECOLSCHOOSECOLS(数组,列序号,…)按序号挑列,重新排列列顺序
GROUPBYNEWGROUPBY(行字段,值,…)用公式做分组汇总,相当于公式版数据透视表
PIVOTBYNEWPIVOTBY(行字段,列字段,值,…)公式版交叉表,行列双向分组
把两张表纵向合并并去重 =UNIQUE(VSTACK(一月!A2:C99,二月!A2:C99)) 按销售额取前 10 名 =TAKE(SORT(A2:C99,3,-1),10) 筛出华东区且金额大于 5000 =FILTER(A2:E99,(B2:B99="华东")*(E2:E99>5000),"无符合记录")

09信息函数与其他

数量不多,但排查问题时离不开。判断单元格「是什么」,比判断「等于什么」更有用。

函数语法用途
ISBLANKISBLANK(值)判断是否真空。含 "" 的单元格返回 FALSE,这点常被误解
ISNUMBERISNUMBER(值)判断是否为数字。查出「看起来是数字其实是文本」的关键
ISTEXTISTEXT(值)判断是否为文本
ISERRORISERROR(值)判断是否为任意错误值
ISNAISNA(值)只判断 #N/A,查找失败时用得上
TYPETYPE(值)返回类型代码:1 数字、2 文本、4 逻辑值、16 错误值、64 数组
CELLCELL(信息类型,[引用])取单元格格式信息,如 "format" 取数字格式代码
CONVERTCONVERT(数值,原单位,目标单位)单位换算,如把英里转公里

怎么快速找到一个函数

三个实用路径,比背函数名有效得多:

一是输入前几个字母看提示。在格子里打 =VL,下方会列出所有以 VL 开头的函数,方向键选中按 Tab 补全,此时还会显示参数提示。

二是用「插入函数」对话框。点编辑栏左侧的 fx,可以按类别浏览,也能看到每个参数的文字说明。找不常用的函数时这个最快。

三是搜官方函数列表。微软支持站点有按字母排序的完整函数清单,含每个函数的详细语法和示例。第三方函数目录站通常按使用频率排序,更适合「不知道用哪个」的阶段。

下一步

会写单个函数之后,真正拉开差距的是嵌套与排错。为什么 #VALUE! 出现、为什么 VLOOKUP 返回了错的行、为什么数组公式结果只有一个值——这些在 Excel 公式 页逐条拆过。需要把函数用在具体场景里,可以看 下拉选项(数据验证 + INDIRECT 联动)和 表格内换行(SUBSTITUTE + CHAR 批量处理)。