简介《Excel使用技巧大全超全》是一份面向办公人员、学生及数据分析入门者的速查型文档聚焦日常表格处理中效率低、操作路径不熟等问题。文档以163种技巧为主线按基本方法、数据处理、单元格与工作表操作、工作簿管理、数据查找、文件管理、帮助与快捷方式等模块编排内容涉及快捷键组合、区域选取、格式调整、复制移动、多表编辑等高频场景目录层级清晰便于按问题定位查阅。压缩包内共1个doc文件约303KB体积轻巧可直接在本地阅读或打印备查。目前已有509人学习下载适合希望系统梳理Excel操作、补齐快捷键与实用技巧短板的初、中级用户也可作为办公培训的辅助材料。1. 一份 163 条的 Excel 技巧文档值不值得放进工具箱上个月帮一个做运营的朋友处理月度报表她翻着一份《Excel 使用技巧大全(超全).doc》问我第 47 条写着“奇特的 F4 键”到底在什么场景下按这份 163 种技巧的合集把基本方法、数据输入与编辑、图形图表、函数公式、数据分析管理、设置技巧六块全收了条目从 1 编到 163当字典查很顺手从头读到第 30 条基本就困了。它解决的不是“会不会 Excel”而是“同一个动作你按了几下”——同样填 800 行相同内容有人拖了五分钟填充柄有人按一次 CtrlEnter。适合每天要出报表、被临时需求追着跑的人也适合带新人时想找一份现成清单的人不适合指望它讲 Power Query 或动态数组的读者那部分它确实没写。2. 163 条怎么拆六块目录结构与优先练的 40 条2.1 先看清目录编号是检索入口不是学习顺序把文档目录从头翻一遍会发现它是按“操作对象”分块的不是按工作场景分块。基本方法 1–44 条讲选单元格、选工作表、开工作簿数据输入与编辑 45–104 条是全篇最厚的一块六成内容都在这里图形图表 105–132 条、函数公式 133–144 条、数据分析与管理 145–157 条、设置技巧 158–163 条依次收尾。六块加起来正好 163 条没有交叉编号这一点比很多拼凑出来的合集干净。模块编号区间条数我的使用频率基本方法1–4444每天数据输入与编辑45–10460每天图形图表105–13228每周函数与公式133–14412每天数据分析与管理145–15713每周设置技巧158–1636一次性配置这张表的用法很直接遇事先按模块缩小范围再去翻编号。比如“图表里隐藏的行没画出来”直接跳到 111 条附近“单元格里数字怎么变文本了”去 50、52 条附近看。文档标题写得比较笼统真正能当索引用的是编号记住区间比记住标题快。2.2 三条筛选标准频率、不可替代性、学习成本163 条全练是不现实的也没必要。我一般按三个问题过一遍这个动作我一周做几次有没有菜单能替代三十秒内能不能记住三个都过得去的才留下。频率高、没有菜单替代、三十秒能学会的是必须条件化的那批Shift 拖放改次序、CtrlEnter 批量填充、F4 切引用、Ctrl 看公式。频率高但菜单能替代的比如“给单元格重新命名”知道有这回事就行真要用时点名称框。频率低、学习成本高的比如自定义图表类型118、119 条、在图表里拖动数据点反改数值121 条先记个位置别提前学。还有一类要特别对待文档里标着“技巧大赛获奖作品”的条目比如 13、34、53、80、81、83、95、150、155 条。这类标注能当质量提示看但标准是当年的能不能用还得自己在当前版本上试。我踩过一次坑某条获奖技巧在旧版本里是优雅解法放到今天的版本里已经有原生功能硬套反而是绕路。2.3 我实际留下的 40 条长什么样下面这张表是从 163 条里筛出来的覆盖录入、清洗、计算、呈现四个环节编号和文档一一对应可以直接照着目录翻。编号技巧要点我用它的场景1右键工作表标签选定全部工作表一次改十几张月表的表头5CtrlShift* 选中当前区域只给数据区加边框不碰空行7Shift 拖放改单元格次序调整列顺序不插删列8编辑→清除→全部清空格式和批注不只清内容20双击单元格边框跳到区域边缘三千行数据最后一行在哪22Ctrl 点选不连续单元格单独给几个异常值标色25删除单元格而非清空删完不留空格26 / 38快速删空行导入数据后的收尾41名称框定位回同一单元格大表来回跳45一个单元格塞多个值临时记备注47F4 重复上一步连续插行、连续调格式49AltEnter 单元格内换行地址、备注折行50巧变文本为数字导出数据求和为 052数字设为文本格式工号、身份证前导零57公式区域转数值交表前断掉外部引用58快速输入有序文本序号、月份62快速输入相同文本补空缺的分类字段63给数字加单位自定义格式 0元85下拉列表录入状态字段统一口径88 / 89多单元格同公式、同内容批量铺公式90快速输入日期时间时间戳94快速复制单元格内容只复制值不复制格式95自定义序列排序按部门、按等级排97固定显示某列左右翻页不丢姓名列99自动填充复制公式和格式向下刷公式102计算年龄DATEDIF 类场景104外部数据引入对账前的数据落表111图表显示隐藏数据折叠行后图缺段114图表增加数据系列月度加一条对比线117不同度量用次坐标轴金额和占比放一张图118自定义图表类型固定周报图表样式125一键建默认图表快速出草图133IF 清 0报表不显示零值134 / 138批量求和、自动求和多段小计142F4 切绝对相对引用拖公式不跑偏143Ctrl 查看所有公式接手别人的表154自动筛选前 10快速看头部数据这张表不是让你背而是让你在文档目录里有个锚点。真正要练的是其中前十行剩下的遇到再查。2.4 文档没覆盖、但今天必须自己补的四件事这份合集的年代感很明显描述多是“单击插入→名称→定义”这种菜单路径快捷键部分和当前版本有出入没有超级表、没有动态数组、没有 Power Query。用的时候要自己补四块。第一块是 CtrlT 超级表把区域转成表之后公式自动扩展、筛选自带标题、结构化引用不易跑偏比手动选区域稳。第二块是 XLOOKUP 和 IFERROR 的组合替代 VLOOKUP 加列序号的写法少一个数错列的机会。第三块是 Power Query 做逆透视和合并多表文档里 104 条那类“把外部数据引入表格”的活今天用 PQ 更可控。第四块是版本差异记录凡是文档里的快捷键第一次用之前先在一个空表上试一遍能用再写进自己的笔记。这四块补上163 条才接得上现在的工作流。3. 录入与清洗把 45 分钟的手工活压成四步3.1 第一步把重复值一次性填满最耗时的从来不是难操作是重复操作。800 行里填同一个部门名拖填充柄要几十秒还容易拖过头。选中目标区域输入内容按 CtrlEnter一次填满整片区域这是文档 89 条“同时在多个单元格中输入相同内容”的核心动作。操作顺序不是公式按顺序执行 1. 选中 B2:B801 2. 直接键入 华东区 3. 按 CtrlEnter 4. 结果B2 到 B801 全部为 华东区活动单元格仍停在 B2这段的关键参数只有一个选区必须先确定再键入最后按 CtrlEnter。顺序反了就会变成只填一个单元格。批量铺公式同理文档 88 条讲的就是这个选中整片区域写好第一条公式CtrlEnter 一次性落下去引用会按相对规则自动偏移。要注意的是用这种方式铺公式时如果你的公式里已经写死了绝对引用落下去之后每一行都指向同一个单元格结果全一样这时候要回去检查 F4 有没有按多一次。还有一个省事的做法是用名称框快速定位选区。名称框在编辑栏左侧默认显示当前单元格地址直接输入B2:B801回车就能选中整片区域比拖滚动条准。文档 41 条“每次选定同一单元格”讲的是同一类思路给常用位置起个名下次点名称框下拉就能跳回去。3.2 第二步文本型数字的集中清理从系统导出的数据最常见的翻车是求和对不上。单元格里看着是 1234.56SUM 出来是 0那是因为它是文本。文档 50 条给了思路实操上有三条路按数据量选。第一条数据→分列选中整列下一步到底列格式选常规文本会被强制转成数值。这条路最稳一次处理一整列不受单元格里有没有前后空格影响。第二条选择性粘贴乘 1在任意空单元格输入 1复制它选中目标列选择性粘贴里勾“乘”确定。这条路适合零散几列缺点是会把空单元格变成 0。第三条公式清洗。TRIM(CLEAN(A2))*1TRIM 去首尾空格和多余空格CLEAN 去不可见的控制字符乘 1 把文本强制转数值。这一条要放在辅助列里转完再选择性粘贴回原列不然原公式会自引用。参数上要注意TRIM 只处理 ASCII 空格全角空格和换行符要靠 CLEAN 加替换处理如果数据里有中文单位比如“1,234 元”乘 1 会得到错误值得先用 SUBSTITUTE 去掉逗号和单位。做完之后核对一眼状态栏选中整列看求和和源系统对得上才算过。文档 57 条“将单元格区域从公式转换成数值”在这里配合用转完数值再把辅助列删掉。3.3 第三步下拉列表与自定义序列把键入变成点选录入环节最大的不确定性是人。同一个人名今天写“张伟”明天写“张 伟”后面所有透视和匹配全废。文档 85 条“用下拉列表快速输入数据”就是治这个的。步骤操作位置参数设置1选中目标列建议整列选留出新增行2数据→数据验证允许选“序列”3来源选一个维护好的选项列或直接填待处理,处理中,已完成4出错警告勾选“输入无效数据时显示出错警告”样式选“停止”5输入信息写上“只选不填”减少自由发挥这里最容易漏的是第 4 步。默认是“停止”以外什么都不做别人粘贴一段自由文本进去照样能过下拉就白设了。另外来源区域建议用绝对引用比如$K$2:$K$8不然复制到别处就失效。自定义序列解决的是排序问题。部门按“总部、华东、华南、华北”这种业务顺序排Excel 默认按拼音排怎么排都不对。路径是文件→选项→高级→编辑自定义列表把顺序录进去之后数据→排序→次序里就能选“自定义序列”。文档 95 条讲的就是这个参数上要注意序列一旦录进去只对当前用户生效换台机器要重新录团队里最好把这份顺序写在共享文档里。3.4 第四步冻结窗格与列宽让三千行也能看数据一旦过千行翻页找不到表头是常态。文档 97 条“固定显示某列”讲的是这个需求实操上分两种情况。只想要表头不动视图→冻结窗格→冻结首行一步到位。想要前两行加第一列都不动得先选中一个单元格再冻结要冻结前两行和前两列就点 C3然后视图→冻结窗格→冻结拆分窗格。这个“先选单元格”的规则很多人不知道直接点“冻结首行”还以为自己操作错了。判断标准很简单冻结的分界线永远在你选中单元格的左边和上边。列宽也有个顺手动作选中要统一的多列双击任意一列列标的右边界所有选中列会一起自适应只想给一列调宽又不想看到一串####双击就行不用估数字。如果是从别的表复制过来的列宽用选择性粘贴里的“列宽”那一项比手动拉准。这几步做完一张日常表就进入了可操作状态后面才是计算和呈现的事。4. 公式与图表清 0、加权、次坐标轴三个高频套路4.1 IF 清 0 与“不显示零值”两种做法边界不同报表里一排零不好看这是文档 133 条要解决的问题。做法有两个选错了会埋雷。第一种是选项法文件→选项→高级→此工作表的显示选项→取消勾选“在具有零值的单元格中显示零值”。这个做法只影响显示单元格里还是 0参与计算完全正常适合报表定稿阶段临时用。缺点是它是工作簿级设置别人打开看到的不一样容易产生“你改我数据了”的误会。第二种是公式法IF(A20,,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是在四种引用状态之间循环。按 F4 次数引用形式含义典型场景1 次$A$1行列都锁固定系数单元格2 次A$1锁行不锁列按行取表头3 次$A1锁列不锁行按列取参数4 次A1都不锁常规相对引用拖公式跑偏九成是这一步没按到位。判断方法很简单把公式往右拖一格再往下拖一格如果结果全一样说明锁过头了如果结果错位说明该锁的没锁。接手别人的表第一件事是 Ctrl反引号在数字 1 左边把所有公式摊开显示再按一次恢复。文档 143 条讲的就是这个。摊开后重点看三处有没有跨工作簿的外部引用、有没有整列引用A:A 这种几万行会明显拖慢计算、有没有被隐藏的辅助列。这三样任一条命中表就不是“打开即用”的状态得先清理再动。4.4 次坐标轴与隐藏数据图表两个最容易漏的开关文档 117 条讲不同度量用不同坐标轴。金额和百分比放一张图一个量级几万一个 0 到 1不放次坐标轴的话百分比那条线会贴在地平线上。操作是选中要换轴的数据系列右键设置数据系列格式坐标轴选项里选“次坐标轴”。参数上要注意次坐标轴的最大最小值默认自动一旦两条线的交叉点看着别扭手动设一下次轴范围比反复改图表类型快。文档 111 条讲图表显示隐藏数据这个坑很隐蔽。表里手动隐藏了几行图表跟着少了几段看起来像公式错了。原因是图表默认不绘制隐藏行列的数据。修法是右键图表→选择数据→左下角“隐藏的单元格和空单元格”→勾选“显示隐藏行列中的数据”。这个开关是图表级的每张图都要单独设不会全局生效。复合图表116 条和自定义图表类型118 条属于加分项。前者是把柱形和折线放一张图上后者是把调好的样式存成模板周报固定用一套。这两条学不学不影响出活但如果每周都要出一模一样的图存一次模板能省不少重复劳动。5. 排查五类翻车现场和它们的成因5.1 照着文档按快捷键没反应现象按文档里写的组合键屏幕毫无变化或者弹出一个完全不相干的对话框。原因这份文档成文较早部分快捷键描述与当前版本行为不一致还有些条目的描述其实是菜单操作被转述成了快捷键。比如“快速选中全部工作表”正确做法是右键工作表标签选“选定全部工作表”或者按住 Ctrl 逐个点标签CtrlShiftA 在编辑公式时是参数提示不是选工作表。再比如“彻底清除单元格内容”那条Delete 只清内容格式和批注都留着。解决凡是文档里给的快捷键第一次用之前先在空白表上试一遍确认行为再写进自己的清单。切换工作表用 CtrlPageUp / CtrlPageDown这个在当前版本稳定可用。文档里描述为“单击某菜单→某命令”的条目优先按菜单路径走别硬套快捷键。5.2 拖完单元格原数据被顶掉现象把一个单元格拖到另一个已有数据的位置松手后弹出提示确认之后目标区域原来的内容被覆盖撤销也只能回退一步。原因拖放的三种修饰键行为完全不同很多人只知道其中一种。直接拖是移动并覆盖按住 Ctrl 拖是复制并覆盖按住 Shift 拖才是插入式移动也就是把目标位置往后挤不覆盖任何数据。文档第 7 条讲的“快速修改单元格式次序”用的就是 Shift 拖。解决调整顺序一律用 Shift 拖看到鼠标指针变成带横杠的插入标记再松手。拖之前先把目标区域看一遍尤其注意有没有合并单元格拖进合并区域的行为很不直观。真覆盖了CtrlZ 能救一次但如果中间又做了别的操作撤销链容易断这时候只能靠版本历史或者提前备份。5.3 Delete 清完格式和批注还在现象选中一片区域按 Delete内容没了但底色、边框、数据验证、批注全在粘到别处还是带着一堆格式。原因Delete 键的语义是清除内容不是清除单元格。格式、批注、条件格式、数据验证属于另外几层属性文档第 8 条专门区分了这一点。解决要清干净用开始选项卡里的“清除”→“全部”四层属性一起清。只想清格式不动内容选“清除格式”只想清批注选“清除批注”。整行整列不要了用右键删除行/列而不是清空内容否则会留下一堆空行影响后续的 COUNTA 和数据透视。清完之后建议随手 CtrlS这类操作没有后悔药。5.4 SUM 出来是 0 或者少一截现象一列数字肉眼看着都在求和结果是 0或者比手工加的总数少。原因文本型数字是第一嫌疑常见于系统导出、网页复制、从别的表粘过来的数据。第二嫌疑是数字里混了不可见字符比如前导空格、不换行空格、制表符。第三嫌疑是筛选状态下求和SUBTOTAL 和 SUM 的行为不一样SUM 会把隐藏行也算进去SUBTOTAL(9, 区域) 才只算可见行。解决先看状态栏选中整列如果状态栏显示“计数”而不是“求和”基本可以确认是文本。按 3.2 节的三条路处理分列最稳。清完再核对一次总数。如果表是筛选状态先确认你用的是 SUM 还是 SUBTOTAL这个差别不报错只会静默给出不同结果。5.5 图表里少了几行数据现象表里明明有 12 个月图表只画出 9 个月剩下三个月的数据在表里看得见。原因那三行被手动隐藏了图表默认不绘制隐藏行列的数据而且这个设置在每张图上独立不会跟着工作簿走。另一种情况是数据区域引用没覆盖到新增行图表还是旧的引用范围。解决右键图表→选择数据→隐藏的单元格和空单元格→勾选“显示隐藏行列中的数据”。如果是引用范围问题在选择数据里把区域重新拉到最新一行或者干脆把源数据先转成超级表图表引用会跟着表自动扩展。改完记得看一眼图例多出来的系列名对不对。6. 把 163 条压成一张卡片我的三步验证法163 条全记住是不可能的也没有必要。我的做法是压成三张卡每张不超过 15 项放在手边随时查。卡片内容维护频率快捷键卡CtrlEnter、F4、Ctrl1、AltEnter、Ctrl、CtrlPageDown、Shift拖放、CtrlShift*每季度清一次没用的公式卡IF 清 0、SUMPRODUCT 加权、TRIMCLEAN 清洗、DATEDIF 算年龄、XLOOKUPIFERROR、SUBTOTAL 可见行求和遇到新场景再加一条检查清单交表前过五项外部引用是否断开、文本型数字是否清完、隐藏行列是否处理、筛选状态是否恢复、打印区域是否设置每次交表前过一遍卡片不是抄文档是抄自己用过的。每一条后面我都写一行小字在哪个项目里用过、当时踩了什么坑。没有这行字的条目过两个月就变成陌生名词。验证方法就三步任何一条新技巧到手都走一遍。第一步造一个最小样例三行两列把技巧跑通确认行为符合预期而不是照着描述想当然。第二步记下版本和结果同一个文件在不同版本上可能表现不同我一般写“在某版本上按此步骤可用”不写版本号本身因为机器会换。第三步等一周看这一周里有没有主动用过它。用过就留在卡上没用过就划掉别留着当心理安慰。血泪经验来自一次对账文档里一条“清除零值”的技巧我当时直接用了 IF 返回空文本结果那一列被后面的图表引用折线图断成两截查了半天以为是数据缺失。从那以后我每拿到一份技巧清单都先把最想用的三条在自己的真实表上跑一遍跑不通的直接划掉不再相信“看起来应该能用”。希望帮到你。本文还有配套的精品资源点击获取