163条Excel技巧实战筛选：快捷键、数据清洗与公式图表

简介：《Excel使用技巧大全（超全）》是一份面向办公人员、学生及数据分析入门者的速查型文档，聚焦日常表格处理中效率低、操作路径不熟等问题。文档以163种技巧为主线，按基本方法、数据处理、单元格与工作表操作、工作簿管理、数据查找、文件管理、帮助与快捷方式等模块编排，内容涉及快捷键组合、区域选取、格式调整、复制移动、多表编辑等高频场景，目录层级清晰，便于按问题定位查阅。压缩包内共1个doc文件，约303KB，体积轻巧，可直接在本地阅读或打印备查。目前已有509人学习下载，适合希望系统梳理Excel操作、补齐快捷键与实用技巧短板的初、中级用户，也可作为办公培训的辅助材料。

1. 一份 163 条的 Excel 技巧文档，值不值得放进工具箱

上个月帮一个做运营的朋友处理月度报表，她翻着一份《Excel 使用技巧大全(超全).doc》问我：第 47 条写着“奇特的 F4 键”，到底在什么场景下按？这份 163 种技巧的合集把基本方法、数据输入与编辑、图形图表、函数公式、数据分析管理、设置技巧六块全收了，条目从 1 编到 163，当字典查很顺手，从头读到第 30 条基本就困了。它解决的不是“会不会 Excel”，而是“同一个动作你按了几下”——同样填 800 行相同内容，有人拖了五分钟填充柄，有人按一次 Ctrl+Enter。适合每天要出报表、被临时需求追着跑的人，也适合带新人时想找一份现成清单的人；不适合指望它讲 Power Query 或动态数组的读者，那部分它确实没写。

2. 163 条怎么拆：六块目录结构与优先练的 40 条

2.1 先看清目录：编号是检索入口，不是学习顺序

把文档目录从头翻一遍，会发现它是按“操作对象”分块的，不是按工作场景分块。基本方法 1–44 条讲选单元格、选工作表、开工作簿；数据输入与编辑 45–104 条是全篇最厚的一块，六成内容都在这里；图形图表 105–132 条、函数公式 133–144 条、数据分析与管理 145–157 条、设置技巧 158–163 条依次收尾。六块加起来正好 163 条，没有交叉编号，这一点比很多拼凑出来的合集干净。

这张表的用法很直接：遇事先按模块缩小范围，再去翻编号。比如“图表里隐藏的行没画出来”，直接跳到 111 条附近；“单元格里数字怎么变文本了”，去 50、52 条附近看。文档标题写得比较笼统，真正能当索引用的是编号，记住区间比记住标题快。

2.2 三条筛选标准：频率、不可替代性、学习成本

163 条全练是不现实的，也没必要。我一般按三个问题过一遍：这个动作我一周做几次？有没有菜单能替代？三十秒内能不能记住？三个都过得去的才留下。

频率高、没有菜单替代、三十秒能学会的，是必须条件化的那批：Shift 拖放改次序、Ctrl+Enter 批量填充、F4 切引用、Ctrl+` 看公式。频率高但菜单能替代的，比如“给单元格重新命名”，知道有这回事就行，真要用时点名称框。频率低、学习成本高的，比如自定义图表类型（118、119 条）、在图表里拖动数据点反改数值（121 条），先记个位置，别提前学。

还有一类要特别对待：文档里标着“技巧大赛获奖作品”的条目，比如 13、34、53、80、81、83、95、150、155 条。这类标注能当质量提示看，但标准是当年的，能不能用还得自己在当前版本上试。我踩过一次坑：某条获奖技巧在旧版本里是优雅解法，放到今天的版本里已经有原生功能，硬套反而是绕路。

2.3 我实际留下的 40 条长什么样

下面这张表是从 163 条里筛出来的，覆盖录入、清洗、计算、呈现四个环节，编号和文档一一对应，可以直接照着目录翻。

这张表不是让你背，而是让你在文档目录里有个锚点。真正要练的是其中前十行，剩下的遇到再查。

2.4 文档没覆盖、但今天必须自己补的四件事

这份合集的年代感很明显：描述多是“单击插入→名称→定义”这种菜单路径，快捷键部分和当前版本有出入；没有超级表、没有动态数组、没有 Power Query。用的时候要自己补四块。

第一块是 Ctrl+T 超级表，把区域转成表之后，公式自动扩展、筛选自带标题、结构化引用不易跑偏，比手动选区域稳。第二块是 XLOOKUP 和 IFERROR 的组合，替代 VLOOKUP 加列序号的写法，少一个数错列的机会。第三块是 Power Query 做逆透视和合并多表，文档里 104 条那类“把外部数据引入表格”的活，今天用 PQ 更可控。第四块是版本差异记录，凡是文档里的快捷键，第一次用之前先在一个空表上试一遍，能用再写进自己的笔记。这四块补上，163 条才接得上现在的工作流。

3. 录入与清洗：把 45 分钟的手工活压成四步

3.1 第一步：把重复值一次性填满

最耗时的从来不是难操作，是重复操作。800 行里填同一个部门名，拖填充柄要几十秒，还容易拖过头。选中目标区域，输入内容，按 Ctrl+Enter，一次填满整片区域，这是文档 89 条“同时在多个单元格中输入相同内容”的核心动作。

操作顺序（不是公式，按顺序执行）： 1. 选中 B2:B801 2. 直接键入 "华东区" 3. 按 Ctrl+Enter 4. 结果：B2 到 B801 全部为 "华东区"，活动单元格仍停在 B2

这段的关键参数只有一个：选区必须先确定，再键入，最后按 Ctrl+Enter。顺序反了就会变成只填一个单元格。批量铺公式同理，文档 88 条讲的就是这个：选中整片区域，写好第一条公式，Ctrl+Enter 一次性落下去，引用会按相对规则自动偏移。要注意的是，用这种方式铺公式时，如果你的公式里已经写死了绝对引用，落下去之后每一行都指向同一个单元格，结果全一样，这时候要回去检查 F4 有没有按多一次。

还有一个省事的做法是用名称框快速定位选区。名称框在编辑栏左侧，默认显示当前单元格地址，直接输入B2:B801回车就能选中整片区域，比拖滚动条准。文档 41 条“每次选定同一单元格”讲的是同一类思路，给常用位置起个名，下次点名称框下拉就能跳回去。

3.2 第二步：文本型数字的集中清理

从系统导出的数据，最常见的翻车是求和对不上。单元格里看着是 1234.56，SUM 出来是 0，那是因为它是文本。文档 50 条给了思路，实操上有三条路，按数据量选。

第一条，数据→分列，选中整列，下一步到底，列格式选常规，文本会被强制转成数值。这条路最稳，一次处理一整列，不受单元格里有没有前后空格影响。第二条，选择性粘贴乘 1：在任意空单元格输入 1，复制它，选中目标列，选择性粘贴里勾“乘”，确定。这条路适合零散几列，缺点是会把空单元格变成 0。第三条，公式清洗。

=TRIM(CLEAN(A2))*1

TRIM 去首尾空格和多余空格，CLEAN 去不可见的控制字符，乘 1 把文本强制转数值。这一条要放在辅助列里，转完再选择性粘贴回原列，不然原公式会自引用。参数上要注意：TRIM 只处理 ASCII 空格，全角空格和换行符要靠 CLEAN 加替换处理；如果数据里有中文单位（比如“1,234 元”），乘 1 会得到错误值，得先用 SUBSTITUTE 去掉逗号和单位。

做完之后核对一眼：状态栏选中整列看求和，和源系统对得上才算过。文档 57 条“将单元格区域从公式转换成数值”在这里配合用，转完数值再把辅助列删掉。

3.3 第三步：下拉列表与自定义序列，把键入变成点选

录入环节最大的不确定性是人。同一个人名，今天写“张伟”，明天写“张 伟”，后面所有透视和匹配全废。文档 85 条“用下拉列表快速输入数据”就是治这个的。

这里最容易漏的是第 4 步。默认是“停止”以外什么都不做，别人粘贴一段自由文本进去照样能过，下拉就白设了。另外来源区域建议用绝对引用，比如$K$2:$K$8，不然复制到别处就失效。

自定义序列解决的是排序问题。部门按“总部、华东、华南、华北”这种业务顺序排，Excel 默认按拼音排，怎么排都不对。路径是文件→选项→高级→编辑自定义列表，把顺序录进去，之后数据→排序→次序里就能选“自定义序列”。文档 95 条讲的就是这个，参数上要注意序列一旦录进去，只对当前用户生效，换台机器要重新录，团队里最好把这份顺序写在共享文档里。

3.4 第四步：冻结窗格与列宽，让三千行也能看

数据一旦过千行，翻页找不到表头是常态。文档 97 条“固定显示某列”讲的是这个需求，实操上分两种情况。

只想要表头不动，视图→冻结窗格→冻结首行，一步到位。想要前两行加第一列都不动，得先选中一个单元格再冻结：要冻结前两行和前两列，就点 C3，然后视图→冻结窗格→冻结拆分窗格。这个“先选单元格”的规则很多人不知道，直接点“冻结首行”还以为自己操作错了。判断标准很简单：冻结的分界线永远在你选中单元格的左边和上边。

列宽也有个顺手动作：选中要统一的多列，双击任意一列列标的右边界，所有选中列会一起自适应；只想给一列调宽又不想看到一串####，双击就行，不用估数字。如果是从别的表复制过来的列宽，用选择性粘贴里的“列宽”那一项，比手动拉准。这几步做完，一张日常表就进入了可操作状态，后面才是计算和呈现的事。

4. 公式与图表：清 0、加权、次坐标轴三个高频套路

4.1 IF 清 0 与“不显示零值”，两种做法边界不同

报表里一排零不好看，这是文档 133 条要解决的问题。做法有两个，选错了会埋雷。

第一种是选项法：文件→选项→高级→此工作表的显示选项→取消勾选“在具有零值的单元格中显示零值”。这个做法只影响显示，单元格里还是 0，参与计算完全正常，适合报表定稿阶段临时用。缺点是它是工作簿级设置，别人打开看到的不一样，容易产生“你改我数据了”的误会。

第二种是公式法：

=IF(A2=0,"",A2)

逻辑很直白：等于 0 就返回空文本，否则返回原值。参数上要注意，返回的""是文本，如果后面还有 SUM、AVERAGE 引用这一列，文本会被忽略，结果可能没问题；但如果这一列还要参与乘法或者图表，空文本会让图表出现断点或者让计算报错。所以我的习惯是：显示层用选项法或条件格式，计算层保留原始数值，不要让 IF 把数值改成文本。

如果一定要用公式，又不想变成文本，可以用自定义格式：

#,##0;-#,##0;

三段格式依次是正数、负数、零值，第三段留空，零值就显示成空白，值本身还是数字。这条比 IF 干净，公式里不用多套一层，筛选和求和都不受影响。

4.2 加权平均与分段求和：别拿 AVERAGE 硬算

文档 137 条讲加权平均，这一条在实际业务里被低估了。平均单价、平均得分这类指标，直接用 AVERAGE 求的是简单平均，每个样本权重一样，结果会偏。正确写法是 SUMPRODUCT 除以权重和。

=SUMPRODUCT(B2:B11,C2:C11)/SUM(C2:C11)

B 列是数值，C 列是权重。SUMPRODUCT 先把两个区域对应位置相乘再求和，等于把所有数值按权重加权，最后除以权重总和归一化。参数上有两个点要盯：两个区域的行数必须一致，不一致会返回错误值；区域里不能有文本，有文本会被当 0 处理，看起来算出来了其实是错的，最好先用 COUNT 核对两列非空数量。

分段求和是文档 135、136 条的内容。相邻区域求和直接框选，Alt+= 自动求和最快；不相邻区域求和，先写=SUM(，然后按住 Ctrl 逐个点选区域，逗号分隔，最后回车。这里有个容易忽略的点：Ctrl 点选时要看清选区高亮，点错一个区域不会报错，只会静默算错，所以求和完最好和源数据的总计对一次。

4.3 F4 与 Ctrl+`：引用四态和公式审查

文档 47 条把 F4 叫“奇特”，其实它有两个身份。不在编辑状态下按 F4，是重复上一步操作，连续插行、连续调格式都靠它；在编辑公式时按 F4，是在四种引用状态之间循环。

拖公式跑偏，九成是这一步没按到位。判断方法很简单：把公式往右拖一格再往下拖一格，如果结果全一样，说明锁过头了；如果结果错位，说明该锁的没锁。

接手别人的表，第一件事是 Ctrl+（反引号，在数字 1 左边），把所有公式摊开显示，再按一次恢复。文档 143 条讲的就是这个。摊开后重点看三处：有没有跨工作簿的外部引用、有没有整列引用（A:A` 这种，几万行会明显拖慢计算）、有没有被隐藏的辅助列。这三样任一条命中，表就不是“打开即用”的状态，得先清理再动。

4.4 次坐标轴与隐藏数据：图表两个最容易漏的开关

文档 117 条讲不同度量用不同坐标轴。金额和百分比放一张图，一个量级几万，一个 0 到 1，不放次坐标轴的话，百分比那条线会贴在地平线上。操作是选中要换轴的数据系列，右键设置数据系列格式，坐标轴选项里选“次坐标轴”。参数上要注意，次坐标轴的最大最小值默认自动，一旦两条线的交叉点看着别扭，手动设一下次轴范围，比反复改图表类型快。

文档 111 条讲图表显示隐藏数据，这个坑很隐蔽。表里手动隐藏了几行，图表跟着少了几段，看起来像公式错了。原因是图表默认不绘制隐藏行列的数据。修法是：右键图表→选择数据→左下角“隐藏的单元格和空单元格”→勾选“显示隐藏行列中的数据”。这个开关是图表级的，每张图都要单独设，不会全局生效。

复合图表（116 条）和自定义图表类型（118 条）属于加分项。前者是把柱形和折线放一张图上，后者是把调好的样式存成模板，周报固定用一套。这两条学不学不影响出活，但如果每周都要出一模一样的图，存一次模板能省不少重复劳动。

5. 排查：五类翻车现场和它们的成因

5.1 照着文档按快捷键没反应

现象：按文档里写的组合键，屏幕毫无变化，或者弹出一个完全不相干的对话框。

原因：这份文档成文较早，部分快捷键描述与当前版本行为不一致，还有些条目的描述其实是菜单操作，被转述成了快捷键。比如“快速选中全部工作表”，正确做法是右键工作表标签选“选定全部工作表”，或者按住 Ctrl 逐个点标签；Ctrl+Shift+A 在编辑公式时是参数提示，不是选工作表。再比如“彻底清除单元格内容”那条，Delete 只清内容，格式和批注都留着。

解决：凡是文档里给的快捷键，第一次用之前先在空白表上试一遍，确认行为再写进自己的清单。切换工作表用 Ctrl+PageUp / Ctrl+PageDown，这个在当前版本稳定可用。文档里描述为“单击某菜单→某命令”的条目，优先按菜单路径走，别硬套快捷键。

5.2 拖完单元格，原数据被顶掉

现象：把一个单元格拖到另一个已有数据的位置，松手后弹出提示，确认之后目标区域原来的内容被覆盖，撤销也只能回退一步。

原因：拖放的三种修饰键行为完全不同，很多人只知道其中一种。直接拖是移动并覆盖，按住 Ctrl 拖是复制并覆盖，按住 Shift 拖才是插入式移动，也就是把目标位置往后挤，不覆盖任何数据。文档第 7 条讲的“快速修改单元格式次序”用的就是 Shift 拖。

解决：调整顺序一律用 Shift 拖，看到鼠标指针变成带横杠的插入标记再松手。拖之前先把目标区域看一遍，尤其注意有没有合并单元格，拖进合并区域的行为很不直观。真覆盖了，Ctrl+Z 能救一次，但如果中间又做了别的操作，撤销链容易断，这时候只能靠版本历史或者提前备份。

5.3 Delete 清完，格式和批注还在

现象：选中一片区域按 Delete，内容没了，但底色、边框、数据验证、批注全在，粘到别处还是带着一堆格式。

原因：Delete 键的语义是清除内容，不是清除单元格。格式、批注、条件格式、数据验证属于另外几层属性，文档第 8 条专门区分了这一点。

解决：要清干净，用开始选项卡里的“清除”→“全部”，四层属性一起清。只想清格式不动内容，选“清除格式”；只想清批注，选“清除批注”。整行整列不要了，用右键删除行/列，而不是清空内容，否则会留下一堆空行影响后续的 COUNTA 和数据透视。清完之后建议随手 Ctrl+S，这类操作没有后悔药。

5.4 SUM 出来是 0 或者少一截

现象：一列数字肉眼看着都在，求和结果是 0，或者比手工加的总数少。

原因：文本型数字是第一嫌疑，常见于系统导出、网页复制、从别的表粘过来的数据。第二嫌疑是数字里混了不可见字符，比如前导空格、不换行空格、制表符。第三嫌疑是筛选状态下求和，SUBTOTAL 和 SUM 的行为不一样，SUM 会把隐藏行也算进去，SUBTOTAL(9, 区域) 才只算可见行。

解决：先看状态栏，选中整列如果状态栏显示“计数”而不是“求和”，基本可以确认是文本。按 3.2 节的三条路处理，分列最稳。清完再核对一次总数。如果表是筛选状态，先确认你用的是 SUM 还是 SUBTOTAL，这个差别不报错，只会静默给出不同结果。

5.5 图表里少了几行数据

现象：表里明明有 12 个月，图表只画出 9 个月，剩下三个月的数据在表里看得见。

原因：那三行被手动隐藏了，图表默认不绘制隐藏行列的数据，而且这个设置在每张图上独立，不会跟着工作簿走。另一种情况是数据区域引用没覆盖到新增行，图表还是旧的引用范围。

解决：右键图表→选择数据→隐藏的单元格和空单元格→勾选“显示隐藏行列中的数据”。如果是引用范围问题，在选择数据里把区域重新拉到最新一行，或者干脆把源数据先转成超级表，图表引用会跟着表自动扩展。改完记得看一眼图例，多出来的系列名对不对。

6. 把 163 条压成一张卡片：我的三步验证法

163 条全记住是不可能的，也没有必要。我的做法是压成三张卡，每张不超过 15 项，放在手边随时查。

卡片不是抄文档，是抄自己用过的。每一条后面我都写一行小字：在哪个项目里用过、当时踩了什么坑。没有这行字的条目，过两个月就变成陌生名词。

验证方法就三步，任何一条新技巧到手都走一遍。第一步，造一个最小样例，三行两列，把技巧跑通，确认行为符合预期，而不是照着描述想当然。第二步，记下版本和结果，同一个文件在不同版本上可能表现不同，我一般写“在某版本上按此步骤可用”，不写版本号本身，因为机器会换。第三步，等一周，看这一周里有没有主动用过它。用过就留在卡上，没用过就划掉，别留着当心理安慰。

血泪经验来自一次对账：文档里一条“清除零值”的技巧，我当时直接用了 IF 返回空文本，结果那一列被后面的图表引用，折线图断成两截，查了半天以为是数据缺失。从那以后我每拿到一份技巧清单，都先把最想用的三条在自己的真实表上跑一遍，跑不通的直接划掉，不再相信“看起来应该能用”。希望帮到你。

本文还有配套的精品资源，点击获取