高效随身查 Excel 2021必学的高效办公应用技巧（视频教学版）

第 1 章 数据输入与批量输入 1 
1.1 工作表的编辑1 
技巧 1 快速移动或复制工作表  1 
技巧 2 一次性复制多个工作表  3 
技巧 3 复制使用其他工作簿中的工作表  3 
技巧 4 设置工作表标签的颜色  4 
1.2 数据的输入 5 
技巧 5 输入分数  5 
技巧 6 输入特殊符号  6 
技巧 7 输入身份证号码或长编码  7 
技巧 8 输入以 0 开头的数据 8 
技巧 9 在会计报表中显示大写人民币值  10 
技巧 10 数据随单元格列宽自动换行 11 
技巧 11 在任意需要的位置上强制换行11 
技巧 12 建立批注  12 
技巧 13 运用“墨迹公式”手写公式 13 
技巧 14 让输入的数据自动添加小数位  15 
1.3 数据的批量输入16 
技巧 15 快速填充递增序号  16 
技巧 16 在连续单元格中输入相同日期  18 
技巧 17 一次性输入工作日日期  18 
技巧 18 快速填充大批量序号  19 
技巧 19 填充间隔指定天数的日期 20 
技巧 20 一次性输入所有周日日期 22 
技巧 21 批量填充工作日 23 
技巧 22 自定义填充序列 25 
技巧 23 一次性在不连续单元格中输入相同数据 27 
技巧 24 让空白单元格自动填充上面的数据  27 
技巧 25 快捷填充海量相同数据  29 
技巧 26 忽略非空单元格，批量输入数据  29 
技巧 27 将当前内容快速填充到其他多个工作表中  30 
1.4 导入使用外部数据 .32 
技巧 28 导入文本文件数据 . 32 
技巧 29 导入网页中的有用数据  34 
第 2 章 Excel 数据编辑 .36 
2.1 常用的数据编辑技巧 36 
技巧 1 跨列居中显示表格的标题 . 36 
技巧 2 为表格标题添加会计用下画线  37 
技巧 3 数据竖向显示的效果 38 
技巧 4 合并居中与跨列居中的区别 . 39 
技巧 5 设计单元格框线 . 40 
技巧 6 只应用部分框线装饰表格 . 41 
技巧 7 快速设置数据为货币样式 . 42 
技巧 8 将现有单元格的格式添加为样式 42 
技巧 9 用格式刷快速引用表格格式 . 44 
技巧 10 一次性清除单元格所有的格式  45 
2.2 快速选定与查找替换 46 
技巧 11 选取多个不连续的单元格区域 . 46 
技巧 12 快速定位超大单元格区域 46 
技巧 13 一次性选中所有空值单元格 47 
技巧 14 一次性定位可见单元格  48 
技巧 15 查看公式引用的所有单元格 50 
技巧 16 一次性选中表格中相同格式的单元格 50 
技巧 17 快速定位合并的单元格  52 
技巧 18 数据替换的同时，自动设置格式  53 
技巧 19 只在指定区域中进行替换 54 
技巧 20 完全匹配的查找 55 
技巧 21 快速找到除某一数据之外的数据  55 
2.3 选择性粘贴的妙用 .56 
技巧 22 将公式计算结果转换为数值 56 
技巧 23 粘贴数据时匹配目标区域格式 . 57 
技巧 24 复制数据表到其他位置时保持原列宽 . 58 
技巧 25 选择性粘贴实现同增或同减某一数值 60 
技巧 26 将进货单价统一上调 10% . 61 
技巧 27 同增或同减数据时忽略空单元格  62 
技巧 28 让粘贴数据随源数据自动更新 . 63 
技巧 29 将表格转换为图片 . 64 
技巧 30 将 Excel 表格插入到 Word 文档中 . 64 
技巧 31 链接 Excel 与 Word 中的表格 65 
2.4 提升数据输入的有效性66
技巧 32 限制输入金额不超过预算值 66 
技巧 33 限制单元格只能输入日期 67 
技巧 34 选中单元格时给出智能的输入提示  68 
技巧 35 建立可选择输入的序列  69 
技巧 36 避免输入重复值 71 
技巧 37 限制输入数据的长度  72 
技巧 38 禁止输入文本值 73 
技巧 39 限制输入空格 74 
技巧 40 禁止出库数量大于库存数量 75 
技巧 41 圈释无效数据 76 
技巧 42 复制设置的数据验证  77 
技巧 43 删除设置的数据验证  78 
第 3 章 自定义单元格格式，输入特殊数据 .79 
技巧 1 快速输入大量负数 79 
技巧 2 批量添加数据单位  80 
技巧 3 当数据有部分重复时的快捷输入 81 
技巧 4 根据单元格内容，显示不同的字体颜色 . 82 
技巧 5 当成绩低于 60 分时，显示“取消资格”文字  83 
技巧 6 根据数值的正负，自动添加不同的前后缀  84 
技巧 7 约定数据宽度不足时用零补齐  85 
技巧 8 输入 1 时显示“√”，输入 0 时显示“×” 86 
技巧 9 在数字前统一添加文字前缀 . 87 
技巧 10 正确显示超过 24 小时的时间 88 
技巧 11 显示 mm.dd(yyyy) 格式的日期 . 89 
技巧 12 考勤表中的“d 日”格式  90 
技巧 13 隐藏单元格中所有或部分数据 . 91 
技巧 14 自定义格式的数据转换为实际数据  92 
第 4 章 数据的编排与整理94 
4.1 行列的批量处理94 
技巧 1 一次性插入多行、多列 94 
技巧 2 一次性隔行插入空行 95 
技巧 3 隔 4 行（任意指定）插入空行  97 
技巧 4 一次性隔行调整行高 99 
4.2 表格结构的整理 101 
技巧 5 快速互换两列数据 . 101 
技巧 6 删除 A 列为空的所有行 102 
技巧 7 删除整行为空的所有行  103
技巧 8 复制合并的单元格且让数据连续显示 105 
技巧 9 取消单元格合并后，一次性填充空白单元格  106 
技巧 10 删除重复数据  108 
技巧 11 提取不重复的数据 110 
技巧 12 合并多个单元格的数据且自动分行显示 .111 
技巧 13 一个单元格中的数据拆分为多列112 
4.3 不规则数据的整理114 
技巧 14 不规范数值导致的无法计算问题114 
技巧 15 不规范日期导致的无法计算问题115 
技巧 16 一次性删除文本中的所有空格 118 
技巧 17 一列多属性数据的分列整理  120 
技巧 19 结全 Word 批量去除单元格中的字母 . 122 
技巧 20 解决合并单元格不能筛选的问题 . 123 
技巧 21 解决合并单元格不能排序的问题 . 126 
技巧 22 解决合并单元格中公式不能复制的问题  130 
第 5 章 数据的分析与分类汇总132 
5.1 按条件自动设置特殊格式132 
技巧 1 库存量过低时自动预警  132 
技巧 2 突出显示排名前几位的数据 134 
技巧 3 标识出只值班一次的员工 135 
技巧 4 突出显示本月的退货数据 137 
技巧 5 排除某文本后的特殊标记 138 
技巧 6 用数据条直观比较数据  140 
技巧 7 为不同库存量亮起“三色灯” . 140 
技巧 8 用条件格式给优秀成绩标记小红旗 143 
技巧 9 制作数据比较的旋风图 . 144 
技巧 10 高亮显示将要进行值班的日期 146 
技巧 11 自动标识出周末  147 
技巧 12 比较两个单元格的采购价格是否相同 149 
技巧 13 用条件格式突出显示每行的分和分  150 
技巧 14 突出显示“缺考”或未填写数据的单元格 151 
技巧 15 使用条件格式突出显示整行  152 
技巧 16 特殊显示加班时长长的员工  153 
技巧 17 快速引用已设置的条件格式  155 
5.2  数据的排序筛选156 
技巧 18 双关键字排序  156 
技巧 19 设置按行排序  157 
技巧 20 对同时包含字母和数字的文本进行排序  158
技巧 21 自定义排序规则  159 
技巧 22 筛选出大于指定值的数据  161 
技巧 23 筛选出成绩表中大于平均分的记录 . 161 
技巧 24 筛选出成绩表中排名前 5 位的记录 . 162 
技巧 25 筛选出各科成绩都大于等于 90 分的记录  163 
技巧 26 筛选出至少一科成绩大于等于 90 分的记录  164 
技巧 27 高级筛选条件区域中使用通配符 . 165 
技巧 28 筛选出不重复的记录 . 166 
技巧 29 筛选出指定时间区域的记录  167 
技巧 30 模糊筛选出同一类型的数据  169 
技巧 31 使用搜索筛选器筛选数据  170 
技巧 32 使用搜索筛选器从筛选结果中二次排除  171 
技巧 33 取消数据筛选  172 
5.3 数据的分类汇总 172 
技巧 34 创建多种统计的分类汇总  172 
技巧 35 创建多级分类汇总 174 
技巧 36 只显示分类汇总的结果 . 175 
技巧 37 复制使用分类汇总的统计结果 176 
技巧 38 取消分类汇总  177 
第 6 章 数据的透视统计与分析178 
6.1 数据透视表的创建178 
技巧 1 快速创建数据透视表  178 
技巧 2 调整行列字段，获取不同的统计结果 180 
技巧 3 用表格中部分数据建立数据透视表  181 
技巧 4 重新更改数据透视表的数据源  182 
技巧 5 创建能随数据源自动更新的数据透视表 183 
技巧 6 添加切片器，实现筛选统计 184 
技巧 7 查看某一汇总项的明细数据 186 
技巧 8 数据源变动时刷新数据透视表 . 187 
6.2 数据透视表的外观调整 188 
技巧 9 在统计表的各分级之间添加空白行 188 
技巧 10 设置数据透视表以表格形式显示 . 189 
技巧 11 套用数据透视表样式，一键美化数据透视表 . 190 
技巧 12 将数据透视表转换为普通报表 191 
6.3 数据透视表的汇总方式与值的显示方式192 
技巧 13 更改数据透视表默认的汇总方式 . 192 
技巧 14 同时使用多种汇总方式 
技巧 15 更改统计项为占总和的百分比 196 
技巧 16 显示父行汇总的百分比 . 197 
技巧 17 显示行汇总的百分比 . 198 
6.4 统计结果分组与自定义公式 .199 
技巧 18 统计成绩表中指定分数区间的人数 . 199 
技巧 19 按小时统计点击量 200 
技巧 20 日期数据的分组  201 
技巧 21 建立季度统计报表  202 
技巧 22 统计同一地域补贴金额的合计值 . 204 
技巧 23 对考核成绩按分数段给予等级 206 
技巧 24 自定义公式求解各销售员奖金 208 
技巧 25 自定义公式根据销售额确定次年底薪  210 
技巧 26 自定义公式计算商品销售的毛利211 
6.5 统计结果的图表展示 .212 
技巧 27 创建数据透视图直观反应数据 212 
技巧 28 为数据透视图添加数据标签  213 
技巧 29 套用样式快速美化数据透视图 214 
技巧 30 在数据透视图中筛选查看部分数据 . 216 
第 7 章 图表编辑 216 
7.1 新建图表 218 
技巧 1 表达成分关系的图表  218 
技巧 2 创建直方图显示数据统计结果 . 218 
技巧 3 创建瀑布图展示数据累计情况 . 220 
技巧 4 创建旭日图显示二级分类 222 
技巧 5 选择不连续数据源建立图表 223 
技巧 6 用好推荐的图表 225 
技巧 7 快速创建复合型图表  226 
技巧 8 在原图上更改图表的数据源 227 
技巧 9 在原图上更改图表的类型 229 
技巧 10 用迷你图直观比较一行或一列数据 . 230 
技巧 11 按实际需要更改系列的名称  231 
7.2 图表优化编辑 .232 
技巧 12 重设刻度的值、小值  232 
技巧 13 将垂直轴显示在图表正中位置 234 
技巧 14 快速添加数据标签 235 
技巧 15 为饼图添加类别名称与百分比数据标签  236 
技巧 16 在图表中显示数据与小数据 . 237
技巧 17 调整图表中各分类间的默认距离 . 238 
技巧 18 图表存在负值系列时，要将图表的数据标签移到图外 239 
技巧 19 将饼图中特定扇面分离出来以突出显示  240 
技巧 20 绘制平均线 . 241 
7.3 图表美化及输出 243 
技巧 21 套用样式极速美化 243 
技巧 22 只保留必要元素  244 
技巧 23 为图表对象填色  245 
技巧 24 为图表对象添加边框线条  246 
技巧 25 图表中添加说明及标释文字  248 
技巧 26 将建立的图表转换为图片  249 
技巧 27 保护图表不被随意更改 . 251 
技巧 29 将 Excel 图表应用于 PowerPoint 演示文稿  253 
第 8 章 数据的计算与统计函数256 
8.1 数据求和及按条件求和运算 . 256 
技巧 1 用“自动求和”按钮快速求和 . 256 
技巧 2 根据销售数量与单价计算总销售额  257 
技巧 3 按经办人计算销售金额  258 
技巧 4 统计各部门工资总额  259 
技巧 5 计算本月下旬的销售额总计值 . 260 
技巧 6  应用通配符对某一类数据求和  261 
技巧 7 只计算某两种产品的合计金额 . 261 
技巧 8 统计指定仓库指定商品的总销售件数 262 
技巧 9 多条件统计某一类数据总和 263 
技巧 10 按月汇总出库数量 264 
技巧 11 统计单日销售金额合计值，并返回值 265 
技巧 12 统计总金额时去除某个部门  266 
技巧 13 统计非工作日的总销售金额  267 
技巧 14 对每日出库量累计求和 . 269 
8.2 数据求平均值及按条件求平均值运算 . 270 
技巧 15 计算全年月平均支出金额  270 
技巧 16 计算各店平均利润（排除新店） . 271 
技巧 17 统计各班级平均分数 . 271 
技巧 18 在成绩表中忽略 0 值求平均分 273 
技巧 19 提取部分关键字作为条件求平均值 . 274 
技巧 20 统计各班级千米跑平均速度（按性别统计） . 274 
技巧 21 统计指定店面所有男装品牌的平均利润  276 
技巧 22 计算平均分时将“缺考”成绩也计算在内 277
技巧 23 计算成绩表中前 5 名的平均分 278 
技巧 24 计算指定车间、指定性别员工的平均工资（双条件） 279 
技巧 25 统计各科目成绩的平均分  280 
技巧 26 求几何平均值，比较两组数据的稳定性  281 
8.3 统计符合条件的数据条目数 . 282 
技巧 27 根据签到表统计到会人数  282 
技巧 28 统计所有课程的报名人数  283 
技巧 29 统计某课程的报名人数 . 283 
技巧 30 统计各级学历的人数 . 284 
技巧 31 统计工资大于指定金额的人数 285 
技巧 32 从成绩表中统计及格人数与不及格人数  286 
技巧 33 统计出成绩大于平均分数的学生人数  287 
技巧 34 统计各店面男装的销售记录条数（双条件） . 287 
技巧 35 从学生档案表中统计指定日期区间中指定性别的人数 288 
技巧 36 统计指定部门获取奖金的人数 289 
技巧 37 统计指定车间、指定性别员工的人数  290 
技巧 38 统计指定班级分数大于指定值的人数  291 
技巧 39 统计指定性别测试合格的人数 292 
技巧 40 统计其中一科得满分的人数  293 
技巧 41 统计连续 3 次考试都进入前 10 名的人数  294 
8.4 其他数据的计算与统计 295 
技巧 42 统计企业女性员工的年龄 295 
技巧 43 统计指定产品的报价  296 
技巧 44 统计上半月单笔销售金额 296 
技巧 45 分别统计各班级名成绩  297 
技巧 46 统计排名前 3 位的销售金额 298 
技巧 47 统计各科目成绩中的分  299 
技巧 48 统计指定车间、指定性别员工的工资 300 
技巧 49 为学生考试成绩排名次 . 301 
技巧 50 对销售员的销售额排名次  302 
技巧 51 返回被投诉次数多的客服编号 . 303 
技巧 52 统计各房价区间的频数 . 304 
第 9 章 数据的逻辑判断与查找函数 .306 
9.1 逻辑判断函数 .306 
技巧 1 判断销售额是否达标  306 
技巧 2 根据不同返利比计算返利金额 . 307 
技巧 3 按双条件判断是否为员工发放奖金  308 
技巧 4 判断员工是否达到退休年龄 309
技巧 5 只为满足条件的产品提价 310 
技巧 6 根据双条件筛选出符合发放赠品条件的消费者  312 
技巧 7 多层条件判断函数 IFS . 313 
技巧 8 分男女性别判断跑步成绩是否合格  314 
9.2 按条件查找数据 315 
技巧 9 根据产品编号查询库存数量 315 
技巧 10 按姓名查询学生的各科目成绩 317 
技巧 11 从产品备案表中查询各产品单价 319 
技巧 12 建立员工档案信息查询表  320 
技巧 13 根据多条件计算员工年终奖 322 
技巧 14 将多张工作表中的数据合并到一张工作表中 . 323 
技巧 15 查找并返回符合条件的多条记录 . 325 
技巧 16  查找指定月份、指定专柜的销售金额 328 
技巧 17 查找成绩的学生姓名  330 
技巧 18 查询总金额的销售员  330 
技巧 19 查找短跑成绩表中用时短的次数编号  331 
第 10 章 日期数据的处理函数 .333 
技巧 1 建立倒计时牌 333 
技巧 2 计算展品陈列天数  333 
技巧 3  判断借出的图书是否到期  334 
技巧 4 计算员工在职天数 . 335 
技巧 5 计算临时工的工作天数  336 
技巧 6 计算员工年龄 337 
技巧 7 计算员工工龄 339 
技巧 8 根据员工工龄自动追加工龄工资  339 
技巧 9 根据休假天数自动显示出休假结束日期 ( 排除周末和周日 ) . 341 
技巧 10  返回值班日期对应的星期数 341 
技巧 11 判断加班日期是平时还是双休日  342 
技巧 12 计算平时与双休日的加班工资 343 
技巧 13 计算两个日期间的工作日  344 
技巧 14 计算优惠券有效的截止日期 345 
技巧 15 在考勤表中根据当前月份自动建立日期序列 . 346 
技巧 16 在考勤表中根据日期自动返回对应的星期数 . 348 
技巧 17 计算年假占全年天数的百分比 349 
技巧 18 判断应收账款是否到期 . 350 
技巧 19 计算总借款天数  351 
技巧 20 计算还款剩余天数 352 
技巧 21 根据账龄计算应收账款的到期日期 . 352
技巧 22 利用 DAYS360 函数判断借款是否逾期  353 
技巧 23 分别统计 12 个月以内账款与超过 12 个月账款总金额 . 354 
技巧 24 计算固定资产已使用的时间  356 
技巧 25 将来访时间界定为整点时间区域 . 357 
技巧 26 自行车 5 公里比赛用时统计（分钟数） 358 
技巧 27 计算机器运行的秒数 . 358 
第 11 章 多表数据合并计算361 
技巧 1 多表汇总求和运算（按位置） . 361 
技巧 2 多表汇总求和运算（按类别） . 363 
技巧 3 多表汇总求平均值计算  365 
技巧 4 多表汇总计数运算 . 366 
技巧 5 销售件数匹配单价表中的单价，计算总销售额  369 
技巧 6 合并计算生成二维汇总表 370 
技巧 7  巧用合并计算，分析各班成绩的稳定程度  372 
技巧 8 巧用合并计算，统计各商品的售价 373 
技巧 9 巧用合并计算，进行分类汇总 . 375 
技巧 10 巧用合并计算，统计重复次数 376 
技巧 11 巧用合并计算，半多表整理为单表 . 377 
第 12 章 表格安全与打印379 
12.1 工作表安全设置技巧379 
技巧 1 加密工作簿  379 
技巧 2 隐藏含有重要数据的工作表 380 
技巧 3 快速保护重要的工作表  381 
技巧 4 设置只允许用户进行部分操作 . 382 
技巧 5 保护公式不被修改 . 383 
技巧 6 指定工作表中的可编辑区域 . 385 
技巧 7 阻止文档中的外部内容 387 
技巧 8 宏安全性设置 388 
技巧 9 禁用文档中的 ActiveX 控件 389 
12.2 工作表页面设置 .390 
技巧 10 手动直观调整页边距 . 390 
技巧 11 让表格显示在纸张的正中间  391 
技巧 12 调整分页的位置  392 
技巧 13 在任意需要的位置上分页  393 
技巧 14 为打印的表格添加页眉页脚  395 
技巧 15 在页眉中使用图片并调整  396 
12.3 工作表打印设置 .398
技巧 16 打印指定的页  398 
技巧 17 打印工作表中某一特定区域  399 
技巧 18 一次性建立多个打印区域  400 
技巧 19 一次性打张工作表 . 401 
技巧 20 将数据缩印在一页纸内打印  401 
技巧 21 打印超宽工作表  402 
技巧 22 将表格标题打印到每一页中  403 
技巧 23 将行号、列号一起打印 . 404 
技巧 24 不打印单元格中的颜色和底纹效果 . 405 
技巧 25 分类汇总结果并分页打印  406 
技巧 26 数据透视表的结果分页打印  407 
技巧 27 巧妙为工作表添加打印背景  408 
附录 A Excel 问题集  411 
1. 选项设置问题 411 
问题 1 鼠标指针指向任意功能按钮时都看不到屏幕提示 411 
问题 2 打开工作薄发现工作表的行号、列标都没有了  412 
问题 3 想填充数据或复制公式，但找不到填充柄 . 413 
问题 4 工作簿中的垂直滚动条和水平滚动条不见了  413 
问题 5 滑动鼠标中键向下查看数据时，工作表中的内容随之缩放  414 
问题 6 工作表中显示计算公式，但并没有计算结果  415 
问题 7 更改数据源的值，但公式的计算结果并不自动更新 . 415 
问题 8 添加“自动筛选”后，日期值却不能自动分组筛选 416 
2. 函数应用问题 418 
问题 9 两个日期相减时不能得到差值天数，却返回一个日期值. 418 
问题 10 公式引用单元格明明显示的是数据，计算结果却为 0 419 
问题 11 数字与“空”单元格相加，结果报错 . 420 
问题 12 LOOKUP 查找找不到正确结果 . 420 
问题 13 VLOOKUP 查找对象明明是存在的却查找不到 . 421 
问题 14 VLOOKUP 查找时，查找内容与查找区域首列内容不能精确匹配， 
如何实现查找  422 
问题 15 日期数据与其他数据合并时，不能显示为原样日期， 
只显示一串序列号 . 423 
问题 16 设置按条件求和（按条件计数等）函数时，如何处理条件判断问题.. 424 
问题 17 公式返回“#DIV/0!”错误值  425 
问题 18 公式返回“#N>A”错误值  426 
问题 19 公式返回“#NAME?”错误值 . 426 
问题 20 公式返回“#NUM!”错误值 . 429 
问题 21 公式返回“#VALUE!”错误值 429
问题 22 公式返回“#REF!”错误值  430 
3. 数据透视表问题431 
问题 23 创建数据透视表时，弹出“数据透视表字段名无效”提示框 431 
问题 24 创建的数据透视表使用“计数”而不使用“求和”  431 
问题 25 透视表的列宽无法保留 . 432 
问题 26 字段分组时弹出提示，无法进行分组  433 
问题 27 透视表中将同一个数据项显示两次 . 434 
问题 28 删除了数据项，但其字段仍然显示在筛选区域中  435 
问题 29 刷新数据透视表时，统计的数据消失了  436 
问题 30 在数据透视表中添加计算项时，为什么“计算项”为灰色不可用 436 
问题 31 更新数据源后数据透视表不能相应更新  437 
4. 图表问题438 
问题 32 创建复合饼图时，为何无法实现细分要求 438 
问题 33 数据源中的日期不连续，创建的图表是断断续续的  439 
问题 34 条形图垂直轴标签显示月份数时从大到小显示，不符合常规  440 
问题 35 更改了数据源，为何图表中的数据标签不能同步更改 . 441 
5. 其他问题442 
问题 36 输入一串长数字，按“Enter”键后如何不显示为科学计数方式 . 442 
问题 37 填充序号时不能自动递增  443 
问题 38 填充时间时不能按分钟数（秒数）递增 444 
问题 39 向单元格中输入数据时总弹出对话框  445 
问题 40 对于文本型数字，为其应用“数值”格式后还是无法显示为数值， 
无法参与统计和计算） 445 
问题 41 完成合并计算后，原表数据更新了，但结果不能同步更新 . 446