第 2 章

Excel 基础——AI 时代你还需要学的那些东西

第2章：Excel 高效基础

很多人用了十年 Excel，依然每天在重复低效的操作——一个个单元格点来点去、手动复制粘贴、格式一遍遍调整。根本原因不是他们不够聪明，而是基础没打牢。本章把最关键的基础知识系统化：30个必会快捷键、数据录入规范、超级表的威力，以及引用方式的彻底搞清楚。每一节都配有 AI Prompt 案例，让 AI 帮你加速掌握。

为什么基础决定上限

一个残酷的真相

如果你问一个用了十年 Excel 的人："你觉得自己的 Excel 水平怎么样？"大多数人会说"还可以，日常工作够用"。但如果你坐在旁边观察他们操作，你几乎可以立刻找到一大堆低效行为：

每次选大范围数据，都用鼠标一点点拖——其实 Ctrl+Shift+End 一秒搞定
公式复制后引用混乱，反复手动修改——其实根本没搞清楚绝对引用和相对引用
数据表里有合并单元格，导致筛选、排序、透视表全部失效——其实合并单元格是 Excel 的头号陷阱
日期列里混着 2024/1/15、2024.1.15、2024年1月15日三种格式——后续所有日期函数都会出错
每次新增数据后，公式范围要手动扩展——其实超级表（Ctrl+T）可以自动扩展

这些问题不是 Excel 高级功能的问题，而是基础认知的缺失。基础没打牢，后面学再多的函数、数据透视表、VBA，都是在沙滩上盖楼。

基础的三个层次

Excel 的基础可以分为三个层次：

第一层：操作效率 — 快捷键、鼠标技巧、导航方式。这一层决定你"做同样的事能快多少"。

第二层：数据规范 — 录入格式、表格结构、命名规则。这一层决定你的数据能不能被后续的公式和分析正确处理。

第三层：引用机制 — 绝对引用、相对引用的理解。这一层决定你的公式能不能正确复制和扩展，是所有公式运用的基础。

本章按这三个层次逐一展开。把这三层打牢，你的 Excel 效率会有质的飞跃——不是50%的提升，而是可能节省你每天30%到40%的时间。

ℹ️ Note

AI 辅助学习的正确方式：

本章每个核心知识点都配有 AI Prompt 案例。推荐用法：先自己理解知识点，然后用 AI 做练习——把你遇到的具体问题告诉 AI，让它给你定制化的解释和例子。AI 作为随时待命的 Excel 老师，比查文档快得多。

必会快捷键30个

Excel 有几百个快捷键，但 80% 的效率提升来自其中 30 个。这里按场景分四类，每个快捷键配上键位、用途和最适合的使用场景。

导航类（7个）

Ctrl + Home
跳转到A1单元格
无论光标在哪，一键回到表格起点。查看大表格时频繁使用。


Ctrl + End
跳转到数据最后一个单元格
快速查看数据有多少行列，也用于确认数据范围。


Ctrl + Shift + End
选中从当前位置到最后有数据单元格
选大范围数据的最快方式，替代鼠标拖动。配合Ctrl+Home使用可快速全选数据。


Ctrl + ↑↓←→
跳转到当前列/行数据的边界
在连续数据区域中快速移动到最上/下/左/右有数据的单元格。


Ctrl + Shift + ↑↓←→
选中到数据边界
选中整列或整行数据的最快方式。比如选中A列所有数据：先到A1，再按Ctrl+Shift+↓。


Ctrl + G / F5
定位对话框
可以定位到空白单元格、公式单元格、最后一个单元格等，数据清洗时非常实用。


Ctrl + F / Ctrl + H
查找 / 查找并替换
批量修改内容的基础操作。Ctrl+H支持通配符，配合"*"和"?"可以做模糊替换。

选择与编辑类（9个）

Ctrl + A
全选（在超级表中只选数据区域）
在普通区域全选所有单元格；在超级表内按第一次选数据区域，再按一次选整表含标题。


Ctrl + D / Ctrl + R
向下填充 / 向右填充
复制上方/左方单元格内容到选中范围。先选中范围（含源单元格），再按快捷键。


Ctrl + Shift + L
开启/关闭筛选
快速切换自动筛选状态，不用去菜单栏找。数据分析时极高频使用。


Ctrl + T
创建超级表（Table）
本章重点！把普通数据区域转换为超级表，获得自动扩展、结构化引用、汇总行等5大优势。


Alt + Enter
单元格内换行
在同一个单元格内强制换行，用于备注字段、多行内容。注意：会导致该单元格高度增加。


Ctrl + Z / Ctrl + Y
撤销 / 重做
基础中的基础。Excel默认保存100步撤销历史。注意：关闭文件后撤销历史清空。


F2
进入单元格编辑模式
直接进入当前单元格编辑，不用双击。在公式调试时配合方向键精确定位引用区域。


Ctrl + Enter
批量填充选中区域
先选中多个单元格，输入内容后按Ctrl+Enter，所有选中单元格同时填入相同内容。


Delete / Backspace
清除内容（保留格式）/ 退格删除
Delete只删内容不删格式；若要同时删格式，右键选"清除全部"或Ctrl+Shift+Delete。

格式类（7个）

Ctrl + 1
打开单元格格式对话框
设置数字格式、对齐、边框、填充颜色的总入口。比右键菜单快很多。


Ctrl + Shift + 1
数字格式（千分位，无小数）
快速把数字格式化为1,234格式，财务报表常用。


Ctrl + Shift + 5
百分比格式
快速把小数转为百分比显示，0.15变成15%。注意：格式改变，数值不变。


Ctrl + B / I / U
加粗 / 斜体 / 下划线
基础文字格式。在整理报表标题行、突出关键数据时使用。


Alt + H + H
填充颜色（快捷菜单）
连续按Alt、H、H打开颜色选择器。标注关键行/列时使用，比鼠标点菜单快。


Ctrl + Shift + ~
清除所有格式，还原为常规
清除单元格的数字格式，还原为默认显示。处理从外部粘贴的格式混乱数据时使用。


Alt + H + V + S
选择性粘贴
只粘贴值（不粘公式）、只粘格式、转置粘贴等。复制公式结果为纯数值时必用。

公式与引用类（7个）

F4
循环切换引用类型（本章重点）
编辑公式时，选中引用后按F4循环：A1 → $A$1 → A$1 → $A1 → A1。绝对引用的核心操作。


Ctrl + `（反引号）
显示/隐藏公式
切换显示公式文本还是计算结果。检查公式时用，也可批量查看一个表的所有公式。


F9
计算所选公式部分
在编辑状态选中公式的一部分，按F9可以看到该部分的计算结果。调试复杂嵌套公式时非常有用。


Ctrl + Shift + Enter
输入数组公式（老版Excel）
Excel 2019及以前版本输入数组公式的方式。Excel 365已不需要（动态数组自动支持）。


Shift + F3
插入函数向导
打开函数搜索和参数填写界面。不记得函数参数时使用，比查帮助文档快。


Ctrl + Shift + A
显示函数参数提示
输入函数名后按此快捷键，显示参数列表提示。快速回忆函数参数顺序时使用。


Alt + =
自动求和（SUM）
选中数字区域下方或右方的空单元格，按Alt+=自动生成SUM公式。汇总行最快捷的操作。

用AI学快捷键：把你的重复操作告诉AI

学快捷键最好的方法不是死记硬背，而是把你的具体痛点告诉 AI，让它推荐最合适的快捷键。

Excel有哪些快捷键？

我每天用Excel做销售数据汇总，有几个操作我很低效：
1. 每次都要用鼠标拖动选中几百行数据
2. 经常需要把某一列的公式复制到下面所有行
3. 处理完数据后要把公式列转成纯数值再发给领导
4. 每次都要去菜单栏找"筛选"功能

请告诉我每个操作对应的最快捷键，并说明具体操作步骤。

数据录入规范——影响后续一切的基础

数据录入规范是 Excel 中最容易被忽视、但影响最大的基础。录入时不规范，后续所有的公式、筛选、透视表、图表都会出问题。下面是最常见的四个规范问题。

1. 日期格式统一——最容易踩的坑

Excel 的日期本质上是一个数字（从1900年1月1日开始的序列号），只有录入格式符合 Excel 能识别的日期格式，它才会被当成日期处理。否则，它只是一个文本字符串。

❌ 有问题的录入方式

2024.1.15（点号分隔，Excel不识别）
2024年1月15日（含中文，部分场景不识别）
1/15（没有年份，Excel可能错误猜测年份）
20240115（纯数字，Excel不会自动识别为日期）
同一列混用多种格式（最致命）

✅ 推荐的录入方式
2024/1/15 或 2024-01-15（Excel标准格式）
统一一种格式，整列保持一致
通过单元格格式（Ctrl+1）设置显示样式
从系统粘贴的日期先检查格式是否被正确识别
用TEXT函数或日期格式单元格存储，不要存文本

**如何判断日期是否被正确识别：**在Excel中，真正的日期右对齐显示，文本格式的日期左对齐显示。如果你的日期都靠左，说明被存成了文本，后续所有日期函数（DATEDIF、WEEKDAY、NETWORKDAYS等）都会出错。

2. 数字别带单位——让Excel无法计算的习惯

把"1000元"或"500个"录入单元格，Excel 会把它识别为文本，无法参与计算。这是初学者最常见的错误之一。

❌ 有问题的录入方式

金额列录入：1000元、2500元、800元
数量列录入：50个、120个、33个
面积列录入：120平方、85.5m²
结果：SUM、AVERAGE等函数返回0或错误

✅ 推荐的录入方式
只录入纯数字：1000、2500、800
单位写在列标题里：金额（元）、数量（个）
如需显示单位，用单元格格式设置（Ctrl+1 → 自定义 → 输入 0"元"）
这样数字既能计算，显示时又有单位

3. 空格问题——看不见的破坏者

从系统导出的数据、从网页复制的数据，经常在文字前后有隐藏的空格。这些空格肉眼看不出来，但会导致 VLOOKUP 匹配失败、文本比较出错。

解决方案：TRIM函数

=TRIM(A2)   — 删除A2单元格文本的首尾空格（以及内部多余空格，只保留单个空格）

数据清洗时养成习惯：新数据导入后，对文本列统一做一遍 TRIM 处理，或者用"查找替换"（Ctrl+H）把空格全部替换掉。

4. 合并单元格的危害——Excel的头号陷阱

合并单元格看起来整洁，但它是 Excel 使用中破坏性最大的操作之一。

❌ 合并单元格的后果

无法对合并区域的列进行排序
筛选功能异常（只显示合并块的第一行）
数据透视表无法正确识别合并区域
VLOOKUP等函数引用合并区域会出错
复制粘贴时经常提示"不匹配"

✅ 替代方案
视觉上的居中效果：用"跨列居中"代替合并（选中 → Ctrl+1 → 对齐 → 水平：跨列居中）
分组标识：在前面加辅助列，用分类字段代替合并
打印表头需要合并：只在最终展示用的副本上合并，原始数据表不要合并
已有合并的表格：全选 → 取消合并 → Ctrl+G定位空值 → 输入公式引用上方单元格 → Ctrl+Enter批量填充

AI案例：让AI检查你的数据规范问题

我有一张从ERP系统导出的销售数据表，包含以下列：
- 订单日期（格式混乱，有2024/1/15，有2024.01.15，有20240115）
- 金额（有些录入了"元"字，如"1580元"）
- 产品名称（从系统复制来的，可能有空格）
- 地区（有合并单元格）

我需要用这个表做VLOOKUP匹配和数据透视表分析。
请给我一个数据清洗的步骤清单，以及每步对应的Excel操作方法。

命名规范与表格结构

超级表（Ctrl+T）的威力

超级表（Excel 官方叫做"表格"，英文 Table）是 Excel 里被严重低估的功能。按 Ctrl+T 把普通数据区域转换为超级表，会获得以下五大优势：

优势一：自动扩展范围

超级表会自动识别新增数据。在超级表最后一行下方输入数据，表格范围自动扩展，相关公式、图表、透视表也会自动更新——再也不用手动调整范围。

优势二：结构化引用，公式更易读

普通区域的公式：=SUM(B2:B100)

超级表的公式：=SUM(销售表[金额])

用列名代替单元格地址，公式意图一目了然，维护时不容易出错。

优势三：自动样式和汇总行

超级表自带隔行颜色样式，改变数据量时样式自动调整。可以一键开启汇总行，每列底部自动出现求和、计数、平均值等选项。

优势四：内置筛选和排序

超级表自动在标题行加上筛选下拉箭头，不需要手动开启。

优势五：公式自动填充整列

在超级表的某一列输入公式，Excel 自动把公式填充到该列所有行——省去了手动复制公式的步骤。

❌ 普通数据区域的问题

新增数据后，公式范围不会自动更新
图表和透视表的数据源需要手动调整
公式使用B2:B100这类引用，难以理解
汇总行需要手动添加
复制公式需要手动操作

✅ 超级表的优势
自动扩展：新行自动纳入表格范围
图表和透视表自动更新数据源
结构化引用：[金额]比B2:B100更清晰
一键汇总行，自动求和、计数
输入一行公式，整列自动填充

命名规范建议

好的命名规范能让你的 Excel 文件在半年后打开还能快速理解结构：

文件命名：项目名_类型_日期.xlsx，如 销售数据_月报_202401.xlsx
**工作表命名：**用中文或清晰英文，不要用 Sheet1/Sheet2。如：原始数据、汇总分析、透视表
**超级表命名：**创建超级表后，在"表格设计"选项卡里改个有意义的名字，如 tbl销售明细、tbl产品目录
**列标题：**简洁、唯一，不要有特殊字符。超级表的列标题会直接用于结构化引用

AI案例：让AI帮你设计表格结构

我是一家小型贸易公司的财务，需要设计一个Excel表格来管理客户订单。
需要支持的分析：
1. 按月统计每个客户的销售额和订单数
2. 查看每个产品的利润率
3. 追踪应收账款（哪些订单还没付款）
4. 业务员的业绩排名

请帮我设计：
1. 需要哪几张工作表，各自的用途
2. 每张表的列名和数据类型
3. 哪些表应该用超级表（Ctrl+T）
4. 表之间怎么通过VLOOKUP或其他方式关联

格式要求：用清晰的标题和列表，方便我直接参照执行。

绝对引用与相对引用——F4的奥秘

这是 Excel 里最核心的概念之一，也是初学者出错最频繁的地方。很多人用了很多年 Excel，公式一复制就出错，根本原因就是没搞清楚这个。

四种引用方式详解

引用方式	示例	含义	复制公式时的行为
相对引用	A1	相对于当前位置的单元格	行列都随公式位置变化而变化
绝对引用（锁行锁列）	$A$1	固定到A1这个具体位置	行列都不变，永远引用A1
混合引用（锁列不锁行）	$A1	列固定为A，行随位置变化	列不变（始终是A列），行变化
混合引用（锁行不锁列）	A$1	行固定为第1行，列随位置变化	行不变（始终是第1行），列变化

**F4键的用法：**在编辑公式时，光标放在引用上（或选中引用），按 F4 键循环切换四种模式：A1 → $A$1 → A$1 → $A1 → A1。不需要手动输入 $，按几下 F4 就切换到需要的模式。

5个容易出错的场景

场景1：税率乘以金额——最常见的错误

假设税率在 D1，金额在 B 列。公式写在 C2：

❌ 错误：=B2*D1
复制到C3时，变成 =B3*D2（D1变成了D2，税率引用错误）

✅ 正确：=B2*$D$1
复制到C3时，变成 =B3*$D$1（B3随行变化，D1固定不动）

场景2：二维查表——需要混合引用

当你需要同时沿行和列查表时（如九九乘法表或价格矩阵），需要一个引用锁列不锁行、另一个锁行不锁列：

在B2输入公式，向右向下复制：
=B$1 * $A2

B$1：列随公式向右变化（B→C→D），行锁定在第1行
$A2：列锁定在A列，行随公式向下变化（2→3→4）

场景3：跨表引用固定范围

用 VLOOKUP 引用另一张表的查找范围时，必须锁定范围，否则复制公式时范围会漂移：

❌ 错误：=VLOOKUP(A2, 产品表!A:C, 2, 0)
（A:C整列引用默认是相对引用，某些情况下会出问题）

✅ 正确：=VLOOKUP(A2, 产品表!$A:$C, 2, 0)
（锁定列，确保复制后引用范围不变）

场景4：计算占比——分母必须锁定

要计算每行金额占总计的比例，总计在B10：

❌ 错误：=B2/B10
复制到B3时变成 =B3/B11（分母B10漂移成B11，报错）

✅ 正确：=B2/$B$10
复制后分母始终是B10

场景5：排名计算——自身相对，范围绝对

要对B列数据排名：=RANK(B2, $B$2:$B$100, 0)

B2：相对引用，复制时随行变化（B2、B3、B4…）
$B$2:$B$100：绝对引用，复制时排名范围不变

AI案例：让AI解释你的引用错误

我在Excel里写了一个计算销售提成的公式：
=B2*C1

B2是销售金额，C1是提成比例（放在固定单元格C1里，值是0.05）。

在C2输入这个公式，计算出来是对的。但是把C2的公式向下复制到C3、C4、C5时，结果都变成0或者报错。

请帮我：
1. 解释为什么出错
2. 告诉我正确的写法
3. 解释绝对引用和相对引用的区别，用我这个例子说明

Excel版本差异

主要版本对比

本书的案例主要基于 Excel 365（Microsoft 365 订阅版），它拥有最新的功能。如果你使用其他版本，需要了解以下差异：

版本	发布/更新	主要特点	缺少的重要功能
Excel 365	持续更新	最新功能，动态数组，XLOOKUP，Lambda等	无，功能最全
Excel 2021	2021年	含动态数组、XLOOKUP、LET函数	部分最新365专属函数
Excel 2019	2018年	含IFS、SWITCH、TEXTJOIN等	XLOOKUP、动态数组、LET
Excel 2016/2013	2015/2012年	基础函数完整	IFS、XLOOKUP、动态数组等大量现代函数
WPS表格	持续更新	兼容大多数Excel功能，有差异	Power Query差异较大，VBA部分不兼容

哪些函数只有365/2021有

以下函数是本书会用到的，但只在较新版本中可用：

XLOOKUP（第3章）：Excel 365/2021。替代方案：VLOOKUP 或 INDEX+MATCH
动态数组函数（第14章）：FILTER、SORT、UNIQUE、SEQUENCE 等，仅 Excel 365/2021
LET 函数：仅 Excel 365，用于在公式中定义变量
LAMBDA 函数：仅 Excel 365，用于自定义函数
IFS / MAXIFS / MINIFS：Excel 2019 及以上。替代方案：嵌套 IF 或数组公式

WPS与Excel的兼容性

WPS 表格与 Excel 有较高兼容性，但有几个需要注意的地方：

**Power Query：**WPS 的 Power Query 功能相比 Excel 较弱，部分操作步骤不同
**VBA：**WPS 支持 VBA，但部分对象模型有差异，Excel VBA 代码不一定在 WPS 中直接运行
**函数名：**大部分函数名相同，但少数有差异（如 WPS 有时用中文函数名）
**文件格式：**保存为 .xlsx 格式通常无问题；.xlsm（含宏）格式兼容性较好；.xls 老格式两者都支持