ARTICLE · 1018444

收藏备用!做了20年Excel,我把最常用的95个函数编成了速查宝典,超全面

发布时间：2026-09-16 08:19:19 最近访问：2026-09-16 08:19:18

点击👆Excel不加班，关注星标★不迷路

很久以前就有粉丝让卢子总结Excel平常用到的函数，以后遇到问题了就像查字典一样，终于狠下心来做这件事。

趁着空闲，整理了95个函数按用途排成九组，从头到尾编了 1 到 95 号。前面的 1—80 号是各版本通用的老底子，81—95 号是近年新增的新函数。不用背，把它当成一本能翻页码的字典就行。

一.求和与计数（报表的骨架）

这一类是所有汇总表的底子，用熟了能干掉一大半重复劳动。

1　SUM｜一组数求和2　SUMIF｜按一个条件汇总3　SUMIFS｜按多个条件同时汇总4　SUMPRODUCT｜单价乘数量再合计5　COUNTIF｜单条件计数6　COUNTIFS｜多条件计数7　SUBTOTAL｜分类汇总，自动跳过隐藏行8　AGGREGATE｜高级汇总，忽略错误值和隐藏行

1　=SUM(A1:A10)
2　=SUMIF(B:B,"北京",C:C)
3　=SUMIFS(D:D,B:B,"北京",C:C,">100")
4　=SUMPRODUCT(A1:A5,B1:B5)
5　=COUNTIF(B:B,"北京")
6　=COUNTIFS(B:B,"北京",C:C,">100")
7　=SUBTOTAL(9,A1:A10)
8　=AGGREGATE(9,7,A1:A10)

3 号 SUMIFS 和 6 号 COUNTIFS，是职场里性价比最高的两个函数。记住一条规律：条件区域和求和区域分开写，条件用引号包起来，大于小于写在引号里面。

举个真实的例子。销售明细表里，要算「北京地区、金额大于 100」的订单一共多少钱？以前你得先筛选、再复制、再去求和，现在一个公式。

=SUMIFS(D:D,B:B,"北京",C:C,">100")

7 号 SUBTOTAL 和 8 号 AGGREGATE 是做筛选报表的必备。你筛选之后，普通 SUM 会把隐藏行也算进去，SUBTOTAL 不会。AGGREGATE 比它更狠，连错误值都能跳过。

注意：SUBTOTAL 的第一个参数是功能号，9 代表求和、1 代表平均、2 代表计数、3 代表非空计数、4 代表最大值、5 代表最小值。别直接写成 SUM。

二.数学计算与取整（处理数据的基本功）

都是跟数值打交道的活，难点不在会不会写，而在该用哪一种取整方式。

9　ROUND｜四舍五入到指定小数位10　ROUNDUP｜始终向上取值11　ROUNDDOWN｜始终向下取值12　INT｜去掉小数，只留整数部分13　ABS｜去掉正负号，只看大小14　MOD｜求余数15　POWER｜计算次方16　SQRT｜求平方根17　PRODUCT｜多个数连续相乘18　RAND｜生成 0~1 之间的随机小数19　RANDBETWEEN｜生成指定范围内的随机整数20　PI｜直接调用 π 的值

9　=ROUND(123.456,2)　→ 123.4610　=ROUNDUP(3.1415,2)　→ 3.1511　=ROUNDDOWN(3.1415,2)　→ 3.1412　=INT(8.9)　→ 813　=ABS(-15)　→ 1514　=MOD(10,3)　→ 115　=POWER(2,3)　→ 816　=SQRT(25)　→ 517　=PRODUCT(A1:A5)18　=RAND()19　=RANDBETWEEN(1,100)20　=PI()　→ 3.141592654

这里最容易被搞混的，是 9 号 ROUND、10 号 ROUNDUP、11 号ROUNDDOWN这三个。

ROUND 是按规矩四舍五入的，3.145 保留两位得 3.15，3.144 保留两位得 3.14。ROUNDUP 是「宁可多算也不少算」，报价、预算、材料用量这种场合必须用它的。ROUNDDOWN 是「宁可少算也不多算」，做目标值、卡上限的时候用。

14 号 MOD 这个小函数，做排班和分组特别好用。比如要把一堆数据按 3 行一组分块：结果会是 1、2、3 循环，配合条件格式就能自动隔行标色。

=MOD(ROW(A3),3)+1

三.三角函数（工程场景专用）

这一类日常做报表基本碰不到，是做工程、测绘、机械、建筑的朋友才会用。看到别怕，知道有这么回事就行。

21　SIN｜正弦值22　COS｜余弦值23　TAN｜正切值24　ASIN｜已知正弦求角度25　ACOS｜已知余弦求角度26　ATAN｜已知正切求角度27　RADIANS｜角度转弧度28　DEGREES｜弧度转角度29　SINH｜双曲正弦30　COSH｜双曲余弦

21　=SIN(RADIANS(30))　→ 0.522　=COS(RADIANS(60))　→ 0.523　=TAN(RADIANS(45))　→ 124　=DEGREES(ASIN(0.5))　→ 30°25　=DEGREES(ACOS(0.5))　→ 60°26　=DEGREES(ATAN(1))　→ 45°27　=RADIANS(180)　→ 3.141628　=DEGREES(PI())　→ 18029　=SINH(1)　→ 1.175230　=COSH(0)　→ 1

四.逻辑判断（让表格会思考）

31　IF｜条件成立返回一个结果，不成立返回另一个32　IFERROR｜公式出错时返回指定内容33　AND｜所有条件都成立才为真34　OR｜任意一个条件成立就为真35　NOT｜返回相反的逻辑结果36　XOR｜只有一个条件为真时才为真

31　=IF(A1>5,"Yes","No")
32　=IFERROR(A1/B1,"")
33　=AND(A1>5,A1<10)
34　=OR(A1>5,A1<10)
35　=NOT(A1>5)
36　=XOR(A1>5,A1<10)

31 号 IF 嵌套多了就是灾难。三层以上的 IF，改成 IFS（2019 / 365 / WPS 新版支持）会清爽很多。

=IFS(A1>=90,"优秀",A1>=80,"良好",A1>=60,"及格",TRUE,"不及格")

32 号 IFERROR 是「表格祛痘膏」。做完报表满屏 #N/A、#DIV/0!，把公式整个套一层 IFERROR，瞬间干净。

=IFERROR(你的原公式, "")

五.文本处理（数据清洗的主力）

37　LEFT｜从左边取几个字38　RIGHT｜从右边取几个字39　MID｜从中间某个位置取几个字40　LEN｜统计字符长度41　TRIM｜去掉文本前后多余空格42　SUBSTITUTE｜把指定文本换成新文本43　REPLACE｜按位置替换一段内容44　FIND｜区分大小写查找字符位置45　SEARCH｜不区分大小写查找字符位置46　CONCAT｜把多个文本拼到一起47　TEXTJOIN｜批量连接，还能加分隔符48　LOWER｜英文转小写49　UPPER｜英文转大写50　PROPER｜每个单词首字母大写

37　=LEFT(A1,3)
38　=RIGHT(A1,3)
39　=MID(A1,2,3)
40　=LEN(A1)
41　=TRIM(A1)
42　=SUBSTITUTE(A1,"旧","新")
43　=REPLACE(A1,2,3,"-")
44　=FIND("-",A1)
45　=SEARCH("-",A1)
46　=CONCAT(A1,B1)
47　=TEXTJOIN("+",TRUE,A1:A3)
48　=LOWER(A1)
49　=UPPER(A1)
50　=PROPER(A1)

这一块最值得花时间，因为职场里 80% 的「脏数据」都靠它救。

37、38、39 号 LEFT / RIGHT / MID 三兄弟，专治「信息挤在一个单元格里」。比如工号 BJ2024001，前面两位是城市代码，中间年份，后面3位是编号。

=LEFT(A1,2)

=MID(A1,3,4)

=RIGHT(A1,3)

42 号 SUBSTITUTE 和 43 号 REPLACE 都是替换，区别在「你知不知道位置」。SUBSTITUTE 是按内容换：月结30天 里把「月结」干掉。

=SUBSTITUTE(A1,"月结","")

REPLACE 是按位置换：第 2 位开始换2个字符。

=REPLACE(A1,2,2,"XX")

44 号 FIND 和 45 号 SEARCH 的区别就一个字：大小写。FIND 严格区分大小写，SEARCH 无所谓。找字母就用 SEARCH，找符号或者固定格式就用 FIND。两个配合 MID 用，能实现「从某个词之后开始截取」。

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

46 号 CONCAT 和 47 号 TEXTJOIN 的差别，是能不能加分隔符。CONCAT 是硬拼，张三李四王五；TEXTJOIN 能加逗号，张三,李四,王五，而且还能选择跳过空白单元格，第二个参数 TRUE 的意思就是「忽略空单元格」。做名单汇总的时候太好用了。

=TEXTJOIN("，",TRUE,A1:A3)

注意：41 号 TRIM 只能去掉英文空格，去不掉全角空格和不换行符。从网页或者系统导出的数据，得用：

=TRIM(SUBSTITUTE(SUBSTITUTE(A1,CHAR(160)," "),CHAR(10),""))

六.查找引用（Excel 的分水岭）

会不会查函数，基本就是「会用 Excel」和「真的会用 Excel」的分界线。

51　VLOOKUP｜按列查找，返回对应值52　HLOOKUP｜按行查找，返回对应值53　LOOKUP｜在一行或一列里查找对应值54　MATCH｜返回某个值在区域里的位置55　INDEX｜按行号、列号取值56　CHOOSE｜按序号从列表里挑一个57　OFFSET｜从起点偏移后返回引用58　INDIRECT｜把文本变成真正的单元格引用59　COLUMN｜返回单元格所在列号60　ROW｜返回单元格所在行号

51　=VLOOKUP(A1,B1:C10,2,FALSE)
52　=HLOOKUP(A1,A1:F10,2,FALSE)
53　=LOOKUP(A1,A1:A10,B1:B10)
54　=MATCH(A1,A1:A10,0)
55　=INDEX(A1:C10,2,3)
56　=CHOOSE(2,"Apple","Banana","Cherry")

57　=OFFSET(A1,2,3)58　=INDIRECT("A1")59　=COLUMN(A1)60　=ROW(A1)

51 号 VLOOKUP 的语法，记住四个参数就够，最后一个参数一定写 FALSE，表示精确匹配。这是新手最容易踩的坑，不写的话默认是近似匹配，数据一乱就返回莫名其妙的结果。

=VLOOKUP(找什么, 在哪片区域找, 返回第几列, 精确还是近似)

注意：VLOOKUP 报错，排查三个方向。返回 #N/A，多半是查找值两边有看不见的空格，先用 TRIM 洗一遍；返回 #REF!，是第三个参数的列号超出了查找区域；数字查不到，是一个存成了文本一个存成了数值，用 *1 或者 VALUE 统一格式。

VLOOKUP 有个天生缺陷：只能往右看，不能往左看。要找的值在返回列的右边，它就废了。这时候用 55 号 INDEX + 54 号 MATCH，这对组合不受方向限制，还比 VLOOKUP 快，插列删列也不容易出错。

=INDEX(返回列, MATCH(找什么, 查找列, 0))

58 号 INDIRECT 是「用文本造公式」的神器。INDIRECT("A1") 和 A1 效果一样，但前者能用拼接的方式动态改变引用位置。多表合并、跨表取值特别有用，A1 里填工作表名，这个公式就自动去对应表里取 B2。

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

57 号 OFFSET 是做动态图表的核心。它能根据数据多少自动伸缩引用范围，配合下拉菜单就是动态图表。

七.日期时间（最容易算错的区域）

61　TODAY｜自动显示今天日期62　NOW｜显示当前日期和时间63　DATE｜按年月日组合成日期64　TIME｜按时分秒组合成时间65　YEAR｜提取年份66　MONTH｜提取月份67　DAY｜提取日期68　HOUR｜提取小时69　MINUTE｜提取分钟70　SECOND｜提取秒71　WEEKDAY｜返回星期几72　WEEKNUM｜返回一年中的第几周73　NETWORKDAYS｜两个日期之间的工作日数量74　WORKDAY｜从某天往后推几个工作日75　EDATE｜按月份偏移日期76　EOMONTH｜返回偏移月份的月末日期

61　=TODAY()62　=NOW()63　=DATE(2026,9,15)64　=TIME(14,30,0)65　=YEAR(A1)66　=MONTH(A1)67　=DAY(A1)68　=HOUR(A1)69　=MINUTE(A1)70　=SECOND(A1)71　=WEEKDAY(A1,2)72　=WEEKNUM(A1,2)73　=NETWORKDAYS(A1,B1,C1:C10)74　=WORKDAY(A1,5,C1:C10)75　=EDATE(A1,3)76　=EOMONTH(A1,1)

日期类最大的坑是：Excel 里日期本质是一个数字。你看到的 2026/9/15，底层其实存的是 46280，所以日期能直接相减算天数。

=B1-A1

注意：日期减日期得到的是天数，但单元格格式如果是「日期」，结果显示会变成 1900 年某个鬼日子。记得把结果单元格格式改成「常规」或「数值」。

71 号 WEEKDAY 的第二个参数一定要写 2。不写的话周一返回 2，特别反直觉。写 2 之后，周一等于 1，周日等于 7，符合我们的习惯。

=WEEKDAY(A1,2)

73 号 NETWORKDAYS 和 74 号 WORKDAY 是算项目周期、交付日期的黄金组合。

NETWORKDAYS 算「中间有多少个工作日」：=NETWORKDAYS(开始日, 结束日, 节假日列表)

WORKDAY 算「往后推 N 个工作日是哪天」：=WORKDAY(开始日, 5, 节假日列表)

这两个函数会自动跳过周末，第三个参数把法定假日列进去，它连节假日也一起跳过。做项目排期、交期承诺，比手掰日历快得多。

75 号 EDATE 和 76 号 EOMONTH 是合同、账期场景的标配。合同期 3 个月：

=EDATE(签约日,3)

只要月底：=EOMONTH(某日期,0)，第二个参数写 0 就是本月月末所以算当月最后一天，一条公式就够，不管这个月是 28 天还是 31 天，它自己会算。

=EOMONTH(TODAY(),0)

八.财务函数（做投资、算贷款用得上）

77　PMT｜算贷款每月要还多少78　NPV｜按折现率算现金流净现值79　FV｜算投资未来会变成多少钱80　PV｜把未来现金流折算到现在

77　=PMT(0.05/12,60,-10000)
78　=NPV(0.05,A1:A10)
79　=FV(0.05/12,60,-100,-1000)
80　=PV(0.05/12,60,-100)

77 号 PMT 用三个参数搞定月供：

=PMT(月利率, 总期数, 贷款金额)

年利率 5%，要除以 12 变成月利率；60 期就是 5 年；贷款 10000 元：

=PMT(0.05/12,60,-10000)

结果是 -188.71。为什么是负数？因为 Excel 把「还钱」当成现金流出。想看正数，前面加个负号就行：=-PMT(...)。

注意：贷款金额前面要加负号，或者整个结果加负号。

79 号 FV 是「定投计算器」。每月投 100，一开始先放 1000，年化 5%，投 5 年：

=FV(0.05/12,60,-100,-1000)

得出 8204.94 元。这就是复利的力量，拿来算孩子的教育金、自己的养老金特别直观。

79 号 FV 和 80 号 PV 是一对反义词。FV 是「现在的钱将来值多少」，PV 是「将来的钱现在值多少」。记住这一条就不会记混。

九.新函数专区（先看版本，老版本用不了）

上面 80 个是各版本都有的老底子，闭着眼用不会错。下面这 15 组是近几年新加的，共同特点就一条：一个公式自动溢出，连下拉填充都省了。

但先看版本。Excel 2021 及以下、老版 WPS 里没有这些，需要 Office 365 或最新版 WPS 表格。公式一敲进去就报 #NAME?，基本就是版本不够。

81｜一列变多列

按列排用 WRAPCOLS，按行排用 WRAPROWS，语法一模一样。

=WRAPCOLS(A2:A26,5)
=WRAPROWS(A2:A26,5)

82｜多行多列压成一列或一行

TOCOL 压成一列，TOROW 压成一行。第二参数写 3，可以跳过错误值和空单元格。

=TOCOL(A1:E5)=TOROW(A1:E5)=TOCOL(A1:E5,3)

83｜自动生成工作表目录

WPS 里一个函数搞定，Office 得靠复杂公式或者 VBA 才写得出来。

=SHEETSNAME(,1)

84｜正则提取，数字文字自动分离

WPS 是 REGEXP 一个函数，Office 要用三个函数拼。[0-9]+ 代表连续的数字，^ 是「非」的意思，所以 [^0-9]+ 就是数字以外的文字，也可以用[一-龟]+表示文字。

=REGEXP(A2,"[0-9]+")=REGEXP(A2,"[^0-9]+")=REGEXP(A2,"[一-龟]+")

85｜一个单元格拆成多个

分隔符不统一也能拆。多个符号写成 {" ","-"} 的形式一起丢给它，不用先做查找替换统一符号。

=TEXTSPLIT(A1," ",CHAR(10))

=TEXTSPLIT(A1,{" ","-"},CHAR(10))

86｜一键提取不重复值

在一个单元格里输入就行，回车自动往下扩展。多列一起去重也可以。

=UNIQUE(A1:A18)=UNIQUE(A1:B18)

87｜不重复计数

UNIQUE 只能去重，要数个数，外面再套一层 COUNTA。两个条件不重复计数，就把区域改成两列，最后除以列数。

=COUNTA(UNIQUE(B2:B18))
=COUNTA(UNIQUE(A2:B18))/2

88｜自动排序

比排序按钮省事的地方在于，源数据一变，结果跟着变。

=SORT(F2:G4,1,-1)=SORT(区域,对第几列排序,-1为降序,1为升序)=SORT(F2:G4,2,1)

89｜条件筛选，一拉到底

凭证自动生成、按条件取数都靠它。指定返回区域和条件，剩下的它自己填。

=FILTER(C2:G11,B2:B11=D14)
=FILTER(返回区域,条件区域=条件)

90｜查找不用再套 IFERROR

VLOOKUP、LOOKUP 找不到值会甩一个 #N/A 出来，逼得你在外面套 IFERROR。XLOOKUP 直接在第四个参数里写找不到时显示什么，还能往左查。

=XLOOKUP(E2,A:A,B:B,"")=XLOOKUP(查找值,查找区域,返回区域,错误值显示值)

91｜多个结果去重后合并进一个单元格

这一串是把前面几个函数串起来了：FILTER 先筛出符合条件的，UNIQUE 去重，TEXTJOIN 再用逗号拼成一串。以前要套好几层才能实现。

=TEXTJOIN(",",1,UNIQUE(FILTER($A$2:$A$18,$B$2:$B$18=F2)))

92｜两表标题顺序不一样也能合并

CHOOSECOLS 按列号取列，你不用管两个表的列是不是挨在一起。更省事的写法是外面套一层 MATCH，让它自动判断该取第几列。

=CHOOSECOLS(H2:L10,2)=CHOOSECOLS(区域,第几列)=CHOOSECOLS(H2:L10,2,3,1)=CHOOSECOLS(H2:L10,MATCH(A1:E1,H1:L1,0))

93｜分组统计，透视表的平替

GROUPBY 是「行区域 + 值区域 + 汇总方式」，最后一个参数写 3 表示包含标题。汇总方式换成 MAX、MIN、AVERAGE 都行。行区域可以给多列，其他组合就套 HSTACK。

=GROUPBY(A1:A72,D1:D72,SUM,3)
=GROUPBY(A1:A72,D1:D72,AVERAGE,3)
=GROUPBY(A1:B72,D1:D72,SUM,3)
=GROUPBY(HSTACK(B1:B72,A1:A72),D1:D72,SUM,3)

透视表擅长处理数字，碰上合并文本就抓瞎，新函数两样都能干。把负责人按项目合并到一个格子里，用 ARRAYTOTEXT：

=GROUPBY(A1:A7,B1:B7,ARRAYTOTEXT,3)
=UNIQUE(A1:B72)
=GROUPBY(F1:F7,G1:G7,ARRAYTOTEXT,3)

数据源有重复值的时候，先拿 UNIQUE 生成一列去重后的辅助列，GROUPBY 再引用辅助列的区域。

94｜分表录入，总表自动更新

VSTACK 的语法跟 SUM 几乎一样，会写 SUM 就会写它。最原始的写法是一个表一个表地引用，但更常用的是下面这种连着写的办法。

=VSTACK('01.现金'!A2:E11,'02.银行'!A2:E12,'03.微信'!A2:E11,'04.支付宝'!A2:E10)
=VSTACK(区域1,区域2,区域3,区域4)
=VSTACK('01.现金:04.支付宝'!A2:E12)
=VSTACK('开始表格名称:结束表格名称'!区域)

分表每天要加新数据，那就把区域写大一点，实现动态合并。代价是总表会多出一堆 0：

=VSTACK('01.现金:04.支付宝'!A2:E120)

去 0 用 FILTER，判断 E 列不等于 0 就行。不用辅助列一步到位也可以，但 这里有个特别容易写错的地方：返回区域是 A2:E120，条件区域是 E2:E120，千万别两个都写成一样的。

=FILTER(A2:E999,E2:E999<>0)
=FILTER(VSTACK('01.现金:04.支付宝'!A2:E120),VSTACK('01.现金:04.支付宝'!E2:E120)<>0)

这样在最后一格分表里敲一行新内容，总表立刻跟着变，等于自动合并，一劳永逸。

95｜行列透视

PIVOTBY 大概是参数最多的函数，一共 11 个，常用的就是前 5 个。跟 GROUPBY 比，它多了一个列区域。列区域用不上就用逗号占位。

=PIVOTBY(行区域,列区域,值区域,汇总方式,是否包含标题)
=PIVOTBY(A1:A11,,D1:D11,SUM,3)
=PIVOTBY(A1:B11,,D1:D11,SUM,3)
=PIVOTBY(A1:A11,B1:B11,D1:D11,SUM,3)
=PIVOTBY(A1:A11,B1:B11,D1:D11,SUM)

把项目放行区域、负责人放列区域、金额放值区域，出来的就是一张标准透视表。结尾那个 3 是带标题，去掉看起来更清爽。

=PIVOTBY(A1:A11&C1:C11,,B1:B11,ARRAYTOTEXT,3)=PIVOTBY(HSTACK(A1:A11,C1:C11),,B1:B11,ARRAYTOTEXT,3)=PIVOTBY(A1:A11,C1:C11,B1:B11,ARRAYTOTEXT,3)=PIVOTBY(A2:A11,C2:C11,B2:B11,ARRAYTOTEXT,0,0,,0,,,0)

最后怎么学？

第一，别按顺序学，按场景学。你这周要做什么表，就先把那一类的函数吃透。做过一遍的公式，比看十遍教程记得牢。

第二，先掌握这五个，能覆盖你 80% 的需求：

3　SUMIFS　多条件汇总51　VLOOKUP　跨表查找31 + 32　IF + IFERROR　逻辑判断和容错47　TEXTJOIN　文本拼接76　EOMONTH　日期计算

第三，公式写完一定要用 F9 验算。选中公式里的一段，按 F9 立刻看到它的计算结果，检查完按 Esc 退出。这是排查公式最有效的方法，比一行一行猜快得多。

最后多提一句：81—95 号那些新函数虽然香，别急着往公司老电脑上招呼。先确认同事能不能打开。版本不够的话，你的表发过去满屏都是 #NAME?，返工更费时间。1—80 号的老函数，照样能把活干完。

函数这东西，从来不是背出来的，是用出来的。今天翻到这儿，明天做表遇到问题回来看一眼，用不了几次就成你自己的了。

学了这些，早做完，不加班。

推荐：50个透视表教程，请收好！

上篇：投了36份简历只回1家，我用WorkBuddy的免费积分重做了一遍

作者：卢子，清华畅销书作者，《Excel效率手册早做完，不加班》系列丛书创始人，个人公众号：Excel不加班（ID：Excelbujiaban）

请把「Excel不加班」推荐给你的朋友

别忘了点赞支持卢子哦↓↓↓