第1部分 数据采集与整理 1
1.1数据录入与基本设置 1
1.1.1使用特殊符号自定义单元格格式 1
1.1.2设置数字以小数点对齐 4
1.1.3单元格数据换行 6
1.1.4设置不能隔行或隔列填写数据 7
1.1.5设置倾斜的列标签 8
1.1.6给单元格数据加滚动条显示 10
1.1.7冻结窗格，轻松查看行、列数据 12
1.1.8轻松绘制单斜线、双斜线表头 14
1.1.9如何让数字以“万”为计数单位来显示 15
1.1.10设置仅能修改部分单元格数据 17
1.1.11隐藏工作表 20
1.2合并单元格 21
1.2.1批量合并单元格 21
1.2.2批量拆分合并单元格 24
1.2.3给合并单元格填充序号 25
1.2.4合并单元格计算 26
1.2.5合并单元格筛选 27
1.2.6合并单元格数据查询 29
1.3数据规范 31
1.3.1利用数据验证（数据有效性）规范数据输入 31
1.3.2设置只能输入规范的日期 36
1.3.3巧用数据验证规范时间格式 37
1.3.4数据输入不规范，部分数据带数量单位，此时怎么计算平均值 39
1.3.5一键添加“能计算”的数量单位 41
1.3.6使用多级联动菜单规范数据输入 42
1.3.7处理不能计算的“数值” 45
1.3.8规范全角、半角数据 47
1.3.9数字与文本分离的方法 48
1.3.10LOOKUP＋FIND函数组合规范标准名称 52
1.3.11给同一单元格的姓名和电话号码中间加分隔符号 54
1.4行列设置 55
1.4.1快速删除空白行 55
1.4.2插入行或删除行后，都可自动填写序号 56
1.4.3数据转置与跳过单元格复制 57
1.4.4最快捷的一列转多列方式 61
1.4.5聚光灯效果（阅读模式）改变当前行和列的颜色 63
1.4.6将同部门员工姓名合并到同一单元格 64
1.4.7将同一单元格的同部门员工姓名分行显示 67
1.5数据维度转换 71
1.5.1使用函数建立目录 71
1.5.2不使用函数建立目录 73
1.5.3链接到另一张表的4种方法 76
1.5.4单击订单名称，即可跳到订单详情工作表 79
1.6数据格式转换 80
1.6.1数值取整的9种方法 80
1.6.2数值的特殊舍入方式 82
1.6.3NUMBERSTRING函数和TEXT函数 84
1.6.4怎么计算长短不一的文本算式结果 86
1.6.5阿拉伯数字（小写）转为中文数字（大写）来表示人民币的金额 88
1.7数据筛选 90
1.7.1数据筛选基础 90
1.7.2高级筛选 93
1.7.3筛选符合条件的两种方法 95
1.7.4不用公式的跨表查询 97
1.7.5CELL＋SUMIF函数组合使隐藏列不参与汇总 98
1.7.6序号经过筛选后仍然不乱 99
1.8排序和排名 101
1.8.1多个排序条件的排序 101
1.8.2数据按行排序 102
1.8.3数据按自定义序列排序 104
1.8.4剔除0值排名次，升序、降序由你定 105
1.8.5只给有销量的产品添加序号 108
1.8.6RANK.EQ函数引用合并区域，实现多列数据排名 109
1.8.7SUMPRODUCT函数实现中式排名 110
1.8.8SUMPRODUCT函数实现分组排名 111
1.8.9利用公式自动生成字母序列 112
1.8.10数据透视表实现排名 114
1.9数据去重复 118
1.9.1删除重复项 118
1.9.2计算报名人数 121
1.9.3利用COUNT＋MATCH函数组合统计两列重复值 122
1.9.4利用EXACT函数设置条件格式，标记两组不同的数据 123
1.10多个工作簿、工作表合并、汇总与拆分 125
1.10.1利用数据查询功能实现多个工作表合并 125
1.10.2利用Power Query编辑器实现多个工作簿合并与刷新 129
1.10.3多个工作表数据汇总 140
1.10.4对于结构一致的多个工作表，合并计算是最好的汇总方法 142
1.10.5多个工作表，不用合并，直接查询 143
1.11图片处理 149
1.11.1批量导入文件名 149
1.11.2工作表中批量插入照片 152
1.11.3在批注中插入图片 153
1.11.4图片放在文件夹里，Excel也能查看 156
1.11.5一次为上千幅图片重命名 157
1.11.6利用Excel照相机自动匹配图片 159
第2部分 数据统计分析 163
2.1数据查询和匹配 163
2.1.1查找得票最多的姓名 163
2.1.2查找开奖号码对应的数字 164
2.1.3数值重复，如何提取前3名销量 165
2.1.4提取订货量对应的订货型号 167
2.1.5去掉最后一个特殊符号及其以后的内容 168
2.1.6两列商品型号排序不一，对应数量如何相减 170
2.1.7利用VLOOKUP函数实现多表数据合并查询 171
2.1.8利用VLOOKUP＋MATCH函数组合可以轻松查找数据 172
2.1.9满足条件的数据自动“跑”到其他工作表中 173
2.1.10INDEX＋MATCH函数组合应用——查找业绩前几名的员工姓名 175
2.1.11INDEX＋MATCH函数组合应用——提取行与列交叉单元格的数值 177
2.1.12INDEX＋MATCH函数组合应用——提取整行和整列数据 178
2.1.13利用OFFSET函数在大量数据中查找指定数据 179
2.1.14利用OFFSET函数在动态单元格区域中查找指定数据 180
2.1.15跨表查询指定顾客的购买记录 181
2.1.16在大量信息中，快速查找哪些员工信息是不完整的 183
2.1.17利用OFFSET函数提取销量最大的整列信息 184
2.1.18利用OFFSET函数提取销量前3名的整列信息 185
2.1.19提取销量最大的月份 186
2.1.20提取销量前3名的月份 187
2.1.21如何给相同姓名添加相同编号 188
2.1.22INDEX＋SMALL函数组合完成一对多查找 190
2.1.23IFERROR函数修正VLOOKUP函数返回错误值 192
2.1.24如何查找主单号对应的子单号，且主单号与子单号同行显示 193
2.1.25利用公式从地址中提取省级行政区 195
2.1.26利用VLOOKUP＋IF函数组合、VLOOKUP＋CHOOSE函数组合实现逆向查询 196
2.1.27IF、VLOOKUP、LOOKUP、CHOOSE、INDEX函数都可完成等级评定 198
2.1.28利用VLOOKUP函数查询同部门多个员工信息 200
2.1.29利用VLOOKUP函数查询一种产品多次的进货量 203
2.1.30多条件查询的函数 204
2.2数据统计 206
2.2.1根据等级计算总成绩 206
2.2.2依据评分标准，折算男女同学的体育分数 207
2.2.3统计除请假外参与考核的部门人数 209
2.2.4根据规定好的占比划分成绩等级 210
2.2.5利用公式填写金额收据 211
2.2.6正值、负值分别求和，盈亏情况一目了然 212
2.2.7算算每户有几口人 213
2.2.8依据收费标准，计算不同地区、不同重量的快递费用 214
2.2.9多人分组完成多个项目，统计每个人参与了哪些项目 215
2.2.10同一单元格中有多个姓名，统计总人数 217
2.2.11员工姓名和业绩在同一单元格中，统计业绩最大值 218
2.2.12巧用ROW函数统计前N名数据 219
2.2.13排除重复项统计每月缺勤人数 220
2.2.14巧用动态区域统计累计情况 222
2.2.15根据销售额分段提成标准计算累进提成 225
2.2.16统计代码含有指定数字的商品销售总量 227
2.2.17利用LEN＋SUBSTITUTE函数组合计算员工参与项目数 229
2.2.18按不同字体或背景颜色统计数值 230
2.3日期、时间范围统计 233
2.3.1制作按年、月自动变化的考勤表表头 233
2.3.2设置考勤表周六、周日列自动变色，且自动统计工作日与非工作日加班时长 234
2.3.3制作日期竖排的考勤表表头 236
2.3.4日期与时间分离的3种方法 238
2.3.5利用SUM＋OFFSET函数组合查询产品指定月的销量合计 240
2.3.6利用LOOKUP＋DATEDIF函数组合计算账龄 241
2.3.7根据出生日期制作员工生日提醒 242
2.3.8 2018年是平年还是闰年 243
2.3.9判断会员是否可以升级到VIP 244
2.3.10利用公式计算早班、中班、夜班工作时长 245
2.3.11计算加班时长与补助工资 246
2.3.12SUMIFS函数按月统计产品销量 248
2.3.13计算指定年与月的销售总额 250
2.3.14根据入职时间计算带薪年假天数 251
2.4数据透视表 253
2.4.1数据透视表的8个典型应用 253
2.4.2巧用数据透视表批量合并单元格 258
2.4.3利用OFFSET函数定义名称，实现数据透视表的动态更新 263
2.4.4利用数据透视表对数据进行分段统计 266
2.4.5巧用数据透视表快速拆分工作表 270
2.5数据专业分析 274
2.5.1新个税计算的3个公式 274
2.5.2如何分析共享单车各站点的借车与还车高峰时段 276
2.5.3中了五千万大奖，Excel来帮忙规化 278
2.5.4根据身份证号码统计易出现的错误 281
2.5.5利用INDEX＋RANDBETWEEN函数组合和RAND函数实现随机分组 283
2.5.6TREND函数预测交易额 284
第3部分 数据可视化 286
3.1条件展现 286
3.1.1灵活使用公式，设置条件格式 286
3.1.2自定义奇、偶行颜色 289
3.1.3数据增减，颜色始终追随汇总行 290
3.1.4自动填充单元格颜色，提示身份证号码位数出错 291
3.1.5完全相同的行填充相同颜色 292
3.1.6突出显示两个工作表中完全相同的行 294
3.1.7设置缴费的行为绿色 296
3.1.8高亮显示需要查看的商品 297
3.1.9用不同颜色突出显示前3名的数据 299
3.1.10根据货号设置间隔色 301
3.1.11在所有员工的工资表中，仅能查看本人信息 303
3.1.12商品到期的文字提醒不醒目，可加个图标 306
3.1.13实现智能添加单元格边框 309
3.1.14突出显示多次考核业绩不合格的员工姓名 311
3.1.15标记两组中不同的数据 313
3.1.16计算合同到期天数，并设置“交通三色灯”提醒 314
3.2典型图表及应用 317
3.2.1产品销量对比图：6个月用6行显示 317
3.2.2数量对比图，加一条线突显效果 320
3.2.3甘特图——项目进度清晰可见 321
3.2.4旭日图——体现数据层次及占比的最好图表 327
3.2.5带数值或极值的图表 329
3.2.6利用模板快速制作N个数据分析图表 333
3.2.7如何制作表达数据简洁醒目的迷你图 336
3.2.8如何做出双纵坐标轴图表 338
3.2.9图表更新的3个方法 339
3.2.10图表的翻转 341
3.3动态图表 343
3.3.1快速制作动态图表 343
3.3.2制作带控件的动态图表 346
3.3.3制作“双列”数据动态图表 349
3.3.4制作带滚动条的动态图表 353
3.3.5双控件柱形图的制作 357
3.3.6双控件动态复合饼图的制作 361
3.3.7制作突出显示某系列数据的动态图表 367
3.4打印输出 373
3.4.1多页打印，并设置每页都有标题 373
3.4.2设置按类别分页打印 375
3.4.3设置工作表的完整打印 377
3.4.4设置打印工作表时添加水印 379
第4部分 函数与公式 382
4.1公式综述 382
4.1.1必须知道的公式基础知识 382
4.1.2公式中常出现的错误代码及修正方法 388
4.1.3使用“追踪错误”对公式进行检查 389
4.1.4将公式保护起来 391
4.1.5数组公式——基础知识 393
4.1.6数组公式——应用初步 396
4.1.7数组公式——典型应用 398
4.2统计函数 401
4.2.1利用MODE.MULT函数统计出现最多的数字（一） 401
4.2.2利用MODE.MULT函数统计出现最多的数字（二） 403
4.2.3利用COUNTIF函数给众多班级中相同班级的学生编号 404
4.2.4 5个常用的“IFS”结尾的多条件统计函数 405
4.2.5利用SUM＋COUNTIF函数组合统计不重复值的个数 409
4.2.6利用FREQUENCY函数分段计数 411
4.2.7利用COUNTIFS函数统计满足多个条件的单元格数量 412
4.2.8利用TRIMMEAN函数计算去掉最高分和最低分后的平均分 413
4.3文本函数 414
4.3.1MID＋FIND函数组合提取括号内数据 414
4.3.2TEXT——超级好用的文本函数 416
4.3.3＆——文本连接符的使用 423
4.3.4CONCATENATE文本连接函数 426
4.3.5CONCAT文本连接函数 428
4.3.6TEXTJOIN文本连接函数 430
4.3.7CLEAN函数清除非打印字符 431
4.3.8SUBSTITUTE文本替换函数的使用 432
4.3.9REPLACE函数——隐藏身份证号码的部分数字 435
4.4时间与日期函数 436
4.4.1根据身份证号码计算退休日期 436
4.4.2根据续费月数计算到期日 439
4.4.3利用DATEDIF函数计算精确到年、月、天的账龄 440
4.4.4利用WORKDAY函数计算几个工作日之后的日期 441
4.4.5利用EOMONTH函数取某月的最后一天 443
4.4.6利用NETWORKDAYS.INTL函数计算工作日 444
4.5数学函数 446
4.5.1SUMIF函数应用——单条件、多条件、模糊条件求和 446
4.5.2SUMIF函数应用——非空值条件、排除错误值、日期区间求和 447
4.5.3SUMIF函数应用——隔列求和、查找与引用 449
4.5.4SUMIFS函数多字段、多条件求和 450
4.5.5SUMPRODUCT函数用法解析 452
4.5.6SUMPRODUCT函数的注意事项 458
4.5.7利用SUBTOTAL函数实现忽略隐藏行统计 459
4.5.8AGGREGATE函数——忽略错误值计算的万能函数 460
4.5.9ROUND函数对数据四舍五入 462
4.5.10QUOTIENT函数与TRUNC函数——截去小数，保留整数 464
4.6查找与引用函数 465
4.6.1VLOOKUP函数应用之基础——基本查找 465
4.6.2VLOOKUP函数应用之小成——多行多列查找 467
4.6.3VLOOKUP函数应用之提升——区间查找、等级评定、模糊查找 468
4.6.4VLOOKUP函数应用之进阶——多条件查找、逆向查找 470
4.6.5VLOOKUP函数应用之高级篇——一对多查找 472
4.6.6HLOOKUP函数实现行查找 474
4.6.7LOOKUP函数查询的10种方法 476
4.6.8LOOKUP函数典型应用——根据抽样标准计算抽样数量 480
4.6.9LOOKUP函数典型应用——合并单元格的拆分与查找 482
4.6.10MATCH函数——查找数据所在的位置 484
4.6.11INDEX函数——查找某行、某列的值 485
4.6.12查找“行列交叉单元格”数据的4个函数 486
4.6.13OFFSET偏移函数应用 487
4.6.14INDIRECT函数汇总各仓库的合计到销售总表 491
4.6.15CHOOSE函数的用法集锦 496
4.6.16空格——交叉运算符 499
4.7信息与逻辑函数 502
4.7.1IF函数——最常用的逻辑函数 502
4.7.2IF、OR、AND等逻辑函数的使用——以闰年为例 504
附录A Excel与Word“双剑合璧”，提取无规律分布的字母、汉字、数字 506
附录B 组合键大全 508