高效随身查——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