Excel 速查表
最后更新
公式基础
每个公式都以等号开头。Excel 会计算公式，并在单元格中显示结果。
| 操作 | 语法 |
|---|---|
| 开始写公式 | 先输入 =，再写表达式，例如 =2+2 |
| 引用另一个单元格 | =A1 |
| 算术运算 | + - * /，乘方用 ^ |
| 控制运算顺序 | =(A1+A2)*B1 |
| 连接文本 | =A1&" "&B1 或 =CONCAT(A1," ",B1) |
| 比较运算符 | = <> > < >= <= |
| 计算某个值的百分比 | =A1*15% |
| 给公式加注释 | =SUM(A1:A9)+N("monthly total") |
| 显示公式而不是结果 | Ctrl + `（切换） |
| 把公式变成结果值 | 复制，然后选择性粘贴（Paste Special）→ 值（Values） |
单元格引用与区域
$ 会锁定行或列，复制公式时它就不会跟着移动。这是 Excel 里最值得弄懂的一件事。
| 引用 | 含义 |
|---|---|
A1 | 相对引用：向任何方向复制都会移动 |
$A$1 | 绝对引用：永远不会移动 |
$A1 | 锁定列，行会移动 |
A$1 | 锁定行，列会移动 |
A1:A10 | 同一列中向下的 10 个单元格 |
A1:C10 | 一个矩形区域 |
A:A | 整个 A 列 |
1:1 | 整个第 1 行 |
Sheet2!A1 | 另一个工作表中的单元格 |
'My Sheet'!A1 | 名称中带空格的另一个工作表 |
[Book2.xlsx]Sheet1!A1 | 另一个工作簿中的单元格 |
编辑时切换 $ | F4（Windows），Cmd + T（Mac） |
数学与汇总函数
日常求和统计用的函数。它们都可以接收一个区域、一组单元格，或两者混用。
| 函数 | 作用 |
|---|---|
=SUM(B2:B20) | 把区域中的所有数字相加 |
=AVERAGE(B2:B20) | 数字的平均值 |
=MEDIAN(B2:B20) | 中位数 |
=MIN(B2:B20) / =MAX(B2:B20) | 最小值 / 最大值 |
=PRODUCT(B2:B5) | 把所有值相乘 |
=SUMPRODUCT(B2:B20,C2:C20) | 两两相乘后求和，用于加权合计 |
=ABS(B2) | 绝对值 |
=POWER(B2,3) | B2 的立方（等同于 =B2^3） |
=SQRT(B2) | 平方根 |
=MOD(B2,2) | 余数：偶数的结果为 =0 |
=SUBTOTAL(109,B2:B20) | 只对可见行求和（忽略被筛选掉的行） |
=RAND() / =RANDBETWEEN(1,100) | 随机小数 / 随机整数 |
逻辑函数
IF 是最常用的主力函数。IFS 和 IFERROR 能让长公式保持易读。
| 函数 | 作用 |
|---|---|
=IF(B2>1000,"Over","OK") | 一个条件，两种结果 |
=IF(B2>1000,"Over",IF(B2>500,"Watch","OK")) | 嵌套 IF，得到三种或更多结果 |
=IFS(B2>1000,"Over",B2>500,"Watch",TRUE,"OK") | 代替嵌套 IF 的扁平写法 |
=AND(B2>0,C2>0) | 所有条件都成立时才为 TRUE |
=OR(B2>0,C2>0) | 任一条件成立即为 TRUE |
=NOT(B2>0) | 把 TRUE/FALSE 取反 |
=IFERROR(A2/B2,0) | 用备用值替换错误 |
=IFNA(VLOOKUP(...),"Not found") | 只捕获 #N/A |
=ISBLANK(B2) | 单元格为空时为 TRUE |
=ISNUMBER(B2) / =ISTEXT(B2) | 类型检查，适合校验导入的数据 |
=SWITCH(B2,1,"Low",2,"Mid",3,"High","Other") | 把一个值与一组情况逐一匹配 |
计数与条件汇总
*IF 和 *IFS 系列函数用来回答符合某条规则的行“有多少个”和“总共多少”。
| 函数 | 作用 |
|---|---|
=COUNT(B2:B20) | 统计包含数字的单元格 |
=COUNTA(B2:B20) | 统计任意类型的非空单元格 |
=COUNTBLANK(B2:B20) | 统计空单元格 |
=COUNTIF(B2:B20,">100") | 统计满足一个条件的行 |
=COUNTIF(B2:B20,"*north*") | 通配符：* 代表任意多个字符，? 代表一个字符 |
=COUNTIFS(B2:B20,">100",C2:C20,"Paid") | 统计同时满足多个条件的行 |
=SUMIF(C2:C20,"Paid",B2:B20) | 在 C 列匹配时对 B 列求和 |
=SUMIFS(B2:B20,C2:C20,"Paid",D2:D20,"EU") | 按多个条件求和 |
=AVERAGEIF(C2:C20,"Paid",B2:B20) | 条件平均值 |
=MAXIFS(B2:B20,C2:C20,"Paid") | 匹配行中的最大值 |
=COUNTIF($A$2:A2,A2)>1 | 沿着列往下标记重复项 |
=SUMPRODUCT((C2:C20="Paid")*(B2:B20)) | 不用 SUMIFS 的条件求和 |
查找与引用函数
从另一张表里取出一个值。XLOOKUP 是 VLOOKUP 的现代替代品；INDEX/MATCH 在所有 Excel 版本中都能用。
| 函数 | 作用 |
|---|---|
=VLOOKUP(A2,$F$2:$H$50,3,FALSE) | 在第一列中查找 A2，返回第 3 列的值。FALSE 表示精确匹配 |
=XLOOKUP(A2,$F$2:$F$50,$H$2:$H$50,"Not found") | 查找区域和返回区域分开指定，可以向左查找 |
=INDEX($H$2:$H$50,MATCH(A2,$F$2:$F$50,0)) | 经典写法，哪里都能用 |
=MATCH(A2,$F$2:$F$50,0) | A2 在区域中的位置 |
=HLOOKUP(A2,$F$1:$Z$4,3,FALSE) | 和 VLOOKUP 一样，但按行查找 |
=INDEX(B2:D20,2,3) | 区域中第 2 行第 3 列的单元格 |
=XLOOKUP(A2,F:F,H:H,,-1) | 近似匹配：取下一个较小的项（用于分档、区间查找） |
=OFFSET(A1,2,1) | A1 向下 2 格、向右 1 格的单元格 |
=INDIRECT("Sheet"&B1&"!A1") | 用文本拼出一个引用 |
=CHOOSE(B2,"Low","Mid","High") | 从列表中取第 N 项 |
=UNIQUE(A2:A100) | 区域中不重复的值（溢出到相邻单元格） |
=FILTER(A2:C100,C2:C100="Paid") | 满足条件的行（溢出到相邻单元格） |
文本函数
大多数真实表格一开始都是乱糟糟的文本。这些就是清理工具。
| 函数 | 作用 |
|---|---|
=LEN(A2) | 字符数 |
=LEFT(A2,3) / =RIGHT(A2,3) | 前 3 个 / 后 3 个字符 |
=MID(A2,4,5) | 从第 4 个字符开始取 5 个字符 |
=TRIM(A2) | 删除开头、结尾和重复的空格 |
=CLEAN(A2) | 去掉导入数据中不可打印的字符 |
=UPPER(A2) / =LOWER(A2) / =PROPER(A2) | 转换大小写 |
=SUBSTITUTE(A2,"-","") | 替换子串的每一处出现 |
=REPLACE(A2,1,3,"NEW") | 按位置替换，而不是按内容替换 |
=FIND("@",A2) / =SEARCH("@",A2) | 子串的位置（FIND 区分大小写） |
=TEXTSPLIT(A2,",") | 按分隔符把文本拆分到多个单元格 |
=TEXTJOIN(", ",TRUE,A2:A9) | 用分隔符连接一个区域，跳过空值 |
=TEXT(A2,"0.00") | 按指定格式把数字转成文本 |
=VALUE(A2) | 把数字字符串转成真正的数字 |
=EXACT(A2,B2) | 区分大小写的比较 |
日期与时间函数
Excel 把日期存储为数字，所以两个日期相减就能得到天数。
| 函数 | 作用 |
|---|---|
=TODAY() / =NOW() | 今天的日期 / 当前日期和时间 |
=YEAR(A2), =MONTH(A2), =DAY(A2) | 从日期中取出其中一部分 |
=DATE(2026,8,6) | 用年、月、日组成日期 |
=B2-A2 | 两个日期之间相差的天数 |
=DATEDIF(A2,B2,"m") | 两个日期之间的整月数（"y"、"m"、"d"） |
=EDATE(A2,3) | 三个月后的同一天 |
=EOMONTH(A2,0) | A2 所在月份的最后一天 |
=WEEKDAY(A2,2) | 星期几，参数为 2 时 1 = 星期一 |
=NETWORKDAYS(A2,B2) | 两个日期之间的工作日天数 |
=WORKDAY(A2,10) | A2 之后第 10 个工作日的日期 |
=TEXT(A2,"yyyy-mm-dd") | 把日期格式化为文本 |
=HOUR(A2), =MINUTE(A2) | 时间的各个部分 |
舍入与数字函数
为了显示而舍入，用单元格格式；为了计算而舍入，用函数。
| 函数 | 作用 |
|---|---|
=ROUND(A2,2) | 四舍五入到 2 位小数 |
=ROUNDUP(A2,0) / =ROUNDDOWN(A2,0) | 总是向上 / 总是向下舍入 |
=MROUND(A2,5) | 舍入到最接近的 5 的倍数 |
=CEILING(A2,1) / =FLOOR(A2,1) | 向上 / 向下舍入到指定倍数 |
=INT(A2) | 去掉小数部分 |
=TRUNC(A2,1) | 截断小数，不做舍入 |
=RANK(B2,$B$2:$B$20) | 某个值在区域中的排名 |
=PERCENTILE(B2:B20,0.9) | 第 90 百分位数 |
=STDEV.S(B2:B20) | 样本标准差 |
=CORREL(B2:B20,C2:C20) | 两列之间的相关系数 |
错误代码及其含义
每种错误都指向一个具体的问题，看懂它们能省去大量瞎猜。
| 错误 | 原因 | 常见解决方法 |
|---|---|---|
#DIV/0! | 除以零或除以空单元格 | 用 IFERROR 包起来，或用 IF(B2=0,...) 做判断 |
#N/A | 查找没有找到结果 | 检查多余的空格（TRIM）以及数据类型是否一致 |
#VALUE! | 参数类型不对，比如需要数字的地方给了文本 | 检查被引用的单元格；试试 VALUE() |
#REF! | 公式指向了已删除的单元格 | 重新建立引用 |
#NAME? | 函数名拼错，或文本没有加引号 | 修正拼写；给文本加上引号 |
#NUM! | 数值结果超出 Excel 能表示的范围 | 检查是否有不可能的参数，例如 SQRT(-1) |
#NULL! | 两个区域没有交集 | 检查参数之间是否漏了逗号 |
#SPILL! | 动态数组没有空间展开 | 清空下方或右侧的单元格 |
#### | 不是错误，只是列太窄 | 把列调宽 |
| 循环引用 | 公式包含了它自己所在的单元格 | 去掉对自身的引用 |
排序、筛选和数据工具
让一堆数据从密密麻麻的格子变成真正看得懂的信息。
| 任务 | 方法 |
|---|---|
| 对区域排序 | Data → Sort，或按 Alt + A 再按 S |
| 添加筛选下拉按钮 | Ctrl + Shift + L |
| 套用表格格式 | Ctrl + T：自带命名区域和自动扩展的公式 |
| 删除重复项 | Data → Remove Duplicates |
| 把一列拆分成多列 | Data → Text to Columns |
| 快速填充（按规律填充） | Ctrl + E |
| 冻结标题行 | View → Freeze Panes → Freeze Top Row |
| 条件格式 | Home → Conditional Formatting：按规则给单元格上色 |
| 数据验证（下拉列表） | Data → Data Validation → List |
| 给区域命名 | 选中区域，然后在名称框中输入名称 |
| 追踪公式的输入来源 | Formulas → Trace Precedents |
| 单变量求解（反推输入值） | Data → What-If Analysis → Goal Seek |
五步做出数据透视表
汇总几千行数据最快的方法。
| 步骤 | 操作 |
|---|---|
| 1. 整理源数据 | 只有一行标题，没有空行或合并单元格 |
| 2. 插入 | 选中数据 → Insert → PivotTable |
| 3. 行 | 把要分组的字段拖到 Rows（行） |
| 4. 值 | 把要汇总的数字拖到 Values（值） |
| 5. 汇总 | 点击值字段 → Summarize Values By → Sum / Count / Average |
| 增加第二个维度 | 把字段拖到 Columns（列） |
| 筛选整张表 | 把字段拖到 Filters（筛选），或添加切片器（Slicer） |
| 显示百分比 | 值字段 → Show Values As → % of Grand Total |
| 数据变化后刷新 | Alt + F5 |
| 在公式中读取透视表的某个单元格 | =GETPIVOTDATA("Sales",$A$3,"Region","EU") |
快捷键：必备
最省时间的十几个快捷键。
| 操作 | Windows | Mac |
|---|---|---|
| 编辑当前单元格 | F2 | Ctrl + U |
| 确认输入并留在当前单元格 | Ctrl + Enter | Ctrl + Enter |
| 在单元格内换行 | Alt + Enter | Ctrl + Option + Enter |
| 自动求和 | Alt + = | Cmd + Shift + T |
切换引用中的 $ | F4 | Cmd + T |
| 用上方单元格向下填充 | Ctrl + D | Cmd + D |
| 向右填充 | Ctrl + R | Cmd + R |
| 选择性粘贴 | Ctrl + Alt + V | Cmd + Ctrl + V |
| 插入今天的日期 | Ctrl + ; | Cmd + ; |
| 重复上一步操作 | F4 | Cmd + Y |
| 撤销 / 恢复 | Ctrl + Z / Ctrl + Y | Cmd + Z / Cmd + Shift + Z |
| 显示公式 | Ctrl + ` | Ctrl + ` |
快捷键：导航与选择
不碰鼠标也能在大表格里快速移动。
| 操作 | Windows | Mac |
|---|---|---|
| 跳到数据边缘 | Ctrl + 方向键 | Cmd + 方向键 |
| 选择到数据边缘 | Ctrl + Shift + 方向键 | Cmd + Shift + 方向键 |
| 选中整列 / 整行 | Ctrl + Space / Shift + Space | Ctrl + Space / Shift + Space |
| 选中当前区域 | Ctrl + A | Cmd + A |
| 跳到 A1 单元格 | Ctrl + Home | Fn + Ctrl + Left |
| 跳到指定单元格 | Ctrl + G | Ctrl + G |
| 下一个 / 上一个工作表 | Ctrl + PgDn / PgUp | Option + Right / Left |
| 插入行或列 | Ctrl + Shift + + | Cmd + Shift + + |
| 删除行或列 | Ctrl + - | Cmd + - |
| 隐藏列 / 行 | Ctrl + 0 / Ctrl + 9 | Cmd + 0 / Cmd + 9 |
| 查找 / 替换 | Ctrl + F / Ctrl + H | Cmd + F / Ctrl + H |
| 只选中可见单元格 | Alt + ; | Cmd + Shift + Z |
快捷键：格式设置
数字格式的快捷键最值得记住，因为几乎天天都会用到。
| 操作 | Windows | Mac |
|---|---|---|
| 设置单元格格式对话框 | Ctrl + 1 | Cmd + 1 |
| 加粗 / 倾斜 / 下划线 | Ctrl + B / I / U | Cmd + B / I / U |
| 货币格式 | Ctrl + Shift + $ | Ctrl + Shift + $ |
| 百分比格式 | Ctrl + Shift + % | Ctrl + Shift + % |
| 保留 2 位小数的数字格式 | Ctrl + Shift + ! | Ctrl + Shift + ! |
| 日期格式 | Ctrl + Shift + # | Ctrl + Shift + # |
| 常规格式（清除格式） | Ctrl + Shift + ~ | Ctrl + Shift + ~ |
| 外边框 | Ctrl + Shift + & | Cmd + Option + 0 |
| 删除边框 | Ctrl + Shift + _ | Cmd + Option + - |
| 复制格式（格式刷） | Ctrl + Shift + C，然后 Ctrl + Shift + V | Cmd + Shift + C，然后 Cmd + Shift + V |
最常用的 Excel 公式、函数和快捷键，全都在这一页。这份 Excel 速查表收录了实际做表时真正会用到的内容：写公式、绝对引用与相对引用、IF 和计数函数、VLOOKUP 与 XLOOKUP、清理文本、处理日期、每种错误代码的含义，以及值得记住的快捷键。
这里的内容在 Windows 版和 Mac 版 Excel 中都能使用，几乎所有内容也能原样用在 Google Sheets 和 LibreOffice Calc 里。函数名使用英文，因为 Excel 内部存储的就是英文名，不过非英文版的 Excel 可能会显示翻译后的名称。
Excel 速查表常见问题
这份 Excel 速查表免费吗？
免费。整个页面都可以免费使用，无需注册，也不用下载任何东西。表格里的任何公式都可以直接复制。
最重要的 Excel 公式有哪些？
如果只学十个：SUM、AVERAGE、IF、COUNTIF、SUMIF、XLOOKUP（或 VLOOKUP）、INDEX 搭配 MATCH、TRIM、TEXT 和 IFERROR。它们合起来就能搞定求和、条件判断、从另一张表取值、清理杂乱的文本，以及防止错误把报表弄坏。
Excel 公式里的 $ 是什么意思？
它会锁定引用的一部分，复制公式时这部分不会移动。
$A$1 永远指向 A1；$A1 固定 A 列，但行可以变；A$1 固定第 1 行，但列可以变。编辑引用时按 F4（Mac 上按 Cmd + T），就能在四种组合之间循环切换。该用 VLOOKUP 还是 XLOOKUP？
如果你的 Excel 支持 XLOOKUP（Microsoft 365 和 Excel 2021 及以后版本），就用它。它把查找列和返回列作为两个独立参数，所以可以向左查找，别人插入一列时也不会出错，还自带“找不到时”的参数。VLOOKUP 仍然值得学，因为你接手的每个旧工作簿里都有它。INDEX/MATCH 则是所有 Excel 版本都能用的写法。
这些公式能在 Google Sheets 里用吗？
几乎都能原样使用：公式、单元格引用、IF、COUNTIF/SUMIF、文本和日期函数、VLOOKUP、INDEX/MATCH、UNIQUE 和 FILTER。快捷键差别更大一些，还有少数函数只有 Excel 才有（比如部分较新的动态数组函数），所以遇到不常见的函数，先确认一下再用。
为什么我的 Excel 里函数名看起来不一样？
Excel 会把函数名翻译成界面语言，所以德文版显示 SUMME 而不是 SUM，法文版显示 SOMME。文件本身存储的是英文名，因此文档、这份速查表和共享的工作簿都使用英文写法。在你自己的 Excel 里输入翻译后的名称，意思完全一样。
怎样让 #N/A 这类错误不出现在报表里？
用
IFERROR 把公式包起来，例如 =IFERROR(VLOOKUP(A2,F:H,3,FALSE),"Not found")。如果你只想捕获查找失败，同时仍能看到 #VALUE! 这样的真正问题，就改用 IFNA。把所有错误都隐藏起来，会让出错的公式变得无从察觉。在哪里可以练习这些公式？
Coddy 免费的交互式 Excel 课程会在浏览器中运行一个真正的电子表格：每节课给你一小份数据和一个任务，并检查你输入的公式。无需安装任何东西，学完还能获得免费证书。
相关资源
更多速查表
