夜雨聆风学习资料网

ARTICLE · 1030495

Excel函数速查表:110个高频函数,按场景分好类了

发布时间：2026-09-18 08:39:04 最近访问：2026-09-18 08:39:05
Excel函数速查表:110个高频函数,按场景分好类了

全文 110 个函数、8 张表，每个都能直接抄。 用法：按你的需求找行 → 抄走模板 → 只改三个地方（数据区域、条件值、容错值）。 版本标记：▲ = Excel 2021+ / WPS 13.10+　★ = 仅 Excel 365 最新版（2025–2026 新增） 无标记 = 全版本通用

开头

收藏夹里躺着 87 篇《Excel函数大全》的人，真到要用时还是打开百度。

问题不在资料不够，在于没有一份是按需求组织的。500个函数的大全不是资源，是噪音。

下面这版只做一件事：把"我要干什么"翻译成"="后面的那行字。

一、是什么：速查表不是函数清单，是翻译器

按字母排序的函数大全是字典——查得到，但用不上。按场景分类的速查表才是工具。

因为你脑子里装的从来不是函数名，是需求。你不会想"今天该用 SUMIFS 了"，你想的是"华东区上个月的销售额是多少"。

所以下面每张表的第一列全部写人话，就是为了让你能直接 Ctrl+F 搜到自己嘴里的那个词。

表 1｜查找与引用（13 个）

我要干的事
用什么
抄这个模板
按编号查一个值
XLOOKUP ▲
=XLOOKUP(A2, 表1!A:A, 表1!C:C, "查无")
老版本电脑也能开
VLOOKUP
=VLOOKUP(A2, 表1!A:C, 3, 0)
往左查、怕插列错位
INDEX
=INDEX(C:C, MATCH(A2, A:A, 0))
只取位置不取值
MATCH
=MATCH(A2, A:A, 0)
MATCH 的增强版
XMATCH ▲
=XMATCH(A2, A:A, 0, -1)
一个条件查出一堆结果
FILTER ▲
=FILTER(A:D, A:A=A2, "无匹配")
多条件筛选（且）
FILTER ▲
=FILTER(A:D, (B:B="华东")*(C:C>1000))
多条件筛选（或）
FILTER ▲
=FILTER(A:D, (B:B="华东")+(B:B="华南"))
模糊查找（通配符）
XLOOKUP ▲
=XLOOKUP("*"&A2&"*", B:B, C:C, , 2)
从下往上找最后一条
XLOOKUP ▲
=XLOOKUP(A2, B:B, C:C, , 0, -1)
横向查找
HLOOKUP
=HLOOKUP(A2, A1:H5, 3, 0)
只要某几列 / 某几行
CHOOSECOLS ▲ / CHOOSEROWS ▲
=CHOOSECOLS(A2:D100, 1, 3)
取前 N 行 / 去掉前 N 行
TAKE ▲ / DROP ▲
=TAKE(A2:D100, 5)
扩展 / 转置区域
EXPAND ▲ / TRANSPOSE ▲
=TRANSPOSE(A1:E5)

表 2｜统计与条件（20 个）

我要干的事
用什么
抄这个模板
求和
SUM
=SUM(C:C)
单条件求和
SUMIF
=SUMIF(A:A, "华东", C:C)
多条件求和
SUMIFS
=SUMIFS(C:C, A:A,"华东", B:B,">2026-09-01")
多条件数组汇总
SUMPRODUCT
=SUMPRODUCT((A:A="华东")*(C:C))
只对筛选后的行求和
SUBTOTAL
=SUBTOTAL(109, C:C)
数数字单元格
COUNT
=COUNT(C:C)
数非空单元格
COUNTA
=COUNTA(A:A)
单条件计数
COUNTIF
=COUNTIF(A:A, "华东")
多条件计数
COUNTIFS
=COUNTIFS(A:A,"华东", C:C,">1000")
平均值
AVERAGE
=AVERAGE(C:C)
单条件平均
AVERAGEIF
=AVERAGEIF(A:A, "华东", C:C)
多条件平均
AVERAGEIFS
=AVERAGEIFS(C:C, A:A, "华东")
最大值 / 最小值
MAX / MIN
=MAX(C:C)
条件最大值
MAXIFS ▲
=MAXIFS(C:C, A:A, "华东")
条件最小值
MINIFS ▲
=MINIFS(C:C, A:A, "华东")
第 N 大 / 第 N 小
LARGE / SMALL
=LARGE(C:C, 3)
中位数
MEDIAN
=MEDIAN(C:C)
排名
RANK.EQ
=RANK.EQ(C2, $C$2:$C$100)

⚠️ 最容易写反的一处：SUMIF 的求和区域在第三参数，SUMIFS 的求和区域在第一参数。写反了不报错，只给你一个错得一本正经的结果。

表 3｜文本处理（22 个）

我要干的事
用什么
抄这个模板
按分隔符拆成多列
TEXTSPLIT ★
=TEXTSPLIT(A2, "-")
取分隔符前一段
TEXTBEFORE ▲
=TEXTBEFORE(A2, "-")
取分隔符后一段
TEXTAFTER ▲
=TEXTAFTER(A2, "-")
多个单元格合并
TEXTJOIN ▲
=TEXTJOIN("-", TRUE, A2:C2)
直接连成一串
CONCAT ▲
=CONCAT(A2, B2, C2)
数字转指定格式文本
TEXT
=TEXT(A2, "0.00")
取左边 / 右边 N 个字
LEFT / RIGHT
=LEFT(A2, 3)
从中间截取
MID
=MID(A2, 4, 5)
数字符个数
LEN
=LEN(A2)
清掉多余空格
TRIM
=TRIM(A2)
清掉不可见字符
CLEAN
=CLEAN(A2)
批量替换文字
SUBSTITUTE
=SUBSTITUTE(A2, "公司", "有限公司")
按位置替换
REPLACE
=REPLACE(A2, 1, 3, "***")
找字符位置（区分大小写）
FIND
=FIND("-", A2)
找字符位置（不区分）
SEARCH
=SEARCH("华", A2)
转大写 / 小写 / 首字母大写
UPPER / LOWER / PROPER
=PROPER(A2)
文本转数字
VALUE
=VALUE(A2)
按规律提取（正则）
REGEXEXTRACT ★
=REGEXEXTRACT(A2, "[0-9]{6}")
按规律校验（正则）
REGEXTEST ★
=REGEXTEST(A2, "^\d{11}$")
按规律替换（正则）
REGEXREPLACE ★
=REGEXREPLACE(A2, "\s+", "")

表 4｜日期时间（14 个）

我要干的事
用什么
抄这个模板
今天 / 此刻
TODAY / NOW
=TODAY()
拼一个日期
DATE
=DATE(2026, 9, 17)
取年 / 月 / 日
YEAR / MONTH / DAY
=YEAR(A2)
算工龄、年龄、间隔
DATEDIF
=DATEDIF(A2, TODAY(), "Y")
几个月后的同一天
EDATE
=EDATE(A2, 3)
当月最后一天
EOMONTH
=EOMONTH(A2, 0)
N 个工作日之后
WORKDAY
=WORKDAY(A2, 10)
两个日期间的工作日数
NETWORKDAYS
=NETWORKDAYS(A2, B2)
星期几（周一=1）
WEEKDAY
=WEEKDAY(A2, 2)
第几周
WEEKNUM
=WEEKNUM(A2, 2)
文本转日期
DATEVALUE
=DATEVALUE(A2)

表 5｜逻辑与信息（10 个）

我要干的事
用什么
抄这个模板
是非判断
IF
=IF(C2>10000, "达标", "未达标")
多档判断（告别套娃）
IFS ▲
=IFS(C2>=90,"A", C2>=60,"B", TRUE,"C")
按值匹配结果
SWITCH ▲
=SWITCH(A2, 1,"新签", 2,"续费", "其他")
多个条件同时成立
AND
=AND(A2>0, B2<100)
任一条件成立
OR
=OR(A2="是", B2="是")
取反
NOT
=NOT(A2="已发")
出错时显示体面的值
IFERROR
=IFERROR(VLOOKUP(...), "未找到")
只接 #N/A 这一个错
IFNA ▲
=IFNA(XLOOKUP(...), "无记录")
是不是空单元格
ISBLANK
=ISBLANK(A2)
是不是数字
ISNUMBER
=ISNUMBER(A2)

表 6｜动态数组与新函数（13 个）

我要干的事
用什么
抄这个模板
自动排序
SORT ▲
=SORT(A2:D100, 4, -1)
按另一列排序
SORTBY ▲
=SORTBY(A2:A100, C2:C100, -1)
去重取清单
UNIQUE ▲
=UNIQUE(A2:A100)
生成序号 / 日期序列
SEQUENCE ▲
=SEQUENCE(12, 1, 1, 1)
上下拼接两张表
VSTACK ▲
=VSTACK(表1!A:D, 表2!A:D)
左右拼接两张表
HSTACK ▲
=HSTACK(A:A, C:C)
二维表压成一列 / 一行
TOCOL ▲ / TOROW ▲
=TOCOL(A1:E20)
公式版数据透视（一维）
GROUPBY ★
=GROUPBY(A2:A100, C2:C100, SUM)
公式版数据透视（二维）
PIVOTBY ★
=PIVOTBY(A2:A100, B2:B100, C2:C100, SUM)
起变量名，公式不再套娃
LET ▲
=LET(x, A2*10, x+5)
造自己的函数
LAMBDA ▲
=LAMBDA(x, x*1.06)
生成随机数 / 随机整数
RANDARRAY ▲
=RANDARRAY(5, 1, 1, 100, TRUE)

表 7｜数字与舍入（12 个）

我要干的事
用什么
抄这个模板
四舍五入
ROUND
=ROUND(A2, 2)
只进 / 只舍
ROUNDUP / ROUNDDOWN
=ROUNDUP(A2, 0)
取整 / 截断小数
INT / TRUNC
=TRUNC(A2, 1)
向上取到指定倍数
CEILING ▲
=CEILING(A2, 100)
向下取到指定倍数
FLOOR ▲
=FLOOR(A2, 100)
取余数（判奇偶/循环）
MOD
=MOD(A2, 2)
绝对值
ABS
=ABS(A2)
平方根 / 幂
SQRT / POWER
=POWER(A2, 2)
随机整数
RANDBETWEEN
=RANDBETWEEN(1, 100)

表 8｜财务常用（6 个）

我要干的事
用什么
抄这个模板
算每期还款额
PMT
=PMT(4.5%/12, 360, 1000000)
算到期本息
FV
=FV(4%/12, 60, -2000)
算现值
PV
=PV(4%/12, 60, -2000)
净现值
NPV
=NPV(8%, B2:B6)
内部收益率
IRR
=IRR(B2:B6)
直线折旧
SLN
=SLN(10000, 1000, 5)

二、为什么：你背不住函数，真不怪你

1. 人脑存需求，不存语法。 你不会背"先迈左脚共走 1320 步"，你记的是"我要买西红柿"。Excel 有 400 多个函数，分析师日常用的也就 50 个上下——没人靠背语法装得下 400 个。速查表就是"需求→语法"的翻译层，它的价值不在全，在能用需求去索引。

2. 收藏是黑洞，它奖励的是动作不是结果。 收藏那一下给你的完成感，跟真学会是一样的，成本低一百倍，所以人体这台机器必选便宜的。想让它真起作用，就别放进收藏夹——打印贴工位，或者塞进你自己的常用文档。仓库里的东西不会被用，桌面上的才会。

3. 函数是分版本的。

函数
365
2021
2019/2016
WPS
VLOOKUP / SUMIFS / INDEX
✅
✅
✅
✅
XLOOKUP / FILTER / SORT / UNIQUE
✅
✅
❌
13.10+
TEXTSPLIT / VSTACK / TOCOL
✅
❌
❌
部分
GROUPBY / PIVOTBY
✅
❌
❌
视版本
REGEX 正则三兄弟
✅ 2026 起
❌
❌
视版本

三条红线：①发给别人前先问对方什么版本，XLOOKUP 到 2016 上就是一片 #NAME?；②文件必须存 .xlsx / .xlsm，老的 .et、.xls 装不下动态数组；③WPS 13.9 以下和 Linux 版支持度不一致，重要交付先在对方机器上试一发。

4. 公式在贬值，"知道该问什么"在升值。 Copilot 和各种 AI 表格工具已经能直接写公式了，"会写嵌套公式"这个技能在快速掉价。但 AI 不知道你要什么——它不知道，它只会给你一个漂亮的错答案。反复用速查表练的其实是一件事：看到一个任务，能立刻判断它属于哪一类。这个判断力才是你指挥 AI 的资本。

三、怎么做：三步用起来

第一步：查——别翻，用 Ctrl+F。想"求和"搜求和，想"拆"搜拆，想"去重"搜去重。第一列写人话就是给你搜的。

第二步：改——只改三处，其余一个标点都别动。

  1. 数据区域
    ：A:A 换成真实列，行号写足（别只圈到 100 行）
  2. 条件值
    ："华东" 换成你的值，或引用单元格（A2，别写死）
  3. 容错值
    ：第三个参数换成你想要的提示语

补一条：要往下拖的公式，区域先按 F4 锁住（$A$2:$A$100），不然区域跟着跑，结果会诡异地越算越少。

第三步：验——Excel 算错了不会报警。 三个动作：

  • 看溢出范围
    ：FILTER、UNIQUE、SORT 的结果应该"溢"出一片。只看到一个格子有值，多半是下面被占了。
  • 看边界行
    ：手动核对最后一行，多数错误出在"区域少圈一行"。
  • 看错误值说什么
    ：#N/A 找不到；#NAME? 你的版本没这个函数；#SPILL! 溢出区被占；#VALUE! 参数类型不对。

加餐｜十个值得先练的快捷键

快捷键
干什么
Alt + =
一键自动求和
Ctrl + T
数据转"表格"，引用自动跟着长
Ctrl + Shift + L
加/去筛选
F4
锁定引用
Ctrl + ;
插入今天日期
Ctrl + 方向键
跳到数据块边界
Ctrl + Shift + 方向键
一路选中到边界
Ctrl + 1
打开单元格格式
Ctrl + PageUp/Down
切换工作表
F9
手动重算（大表卡顿时救命）

Ctrl + T 值得单说：转成"表格"后，引用它的公式会自动跟着区域长，是根治"又漏了最后几行"最省事的办法。

加餐｜让 AI 写公式的提问模板

三段式，缺一段它就给你编：

①我要做什么：按"地区"汇总"销售额" ②数据长什么样：A 列地区，C 列销售额，第 2–5000 行 ③我要什么结果：每个地区一行，放在 F2 开始

然后必须自己验。AI 最常见的翻车是"读错列"，它以为你说 B 列，其实你说 C 列，而它不会告诉你。

一句话记住：公式可以外包，判断不能外包。

往期精彩

VLOOKUP和FILTER：一个是你用了十年的旧情人，一个是让你眼前一亮的新搭档

Excel 去除重复值统计数量，一篇就够

XLOOKUP为什么完爆VLOOKUP？这三个功能太炸了

相关学习资料