Excel 2016数据处理与分析实战秘籍 作者：张运明

Excel 2016数据处理与分析实战秘籍 出版社：清华大学出版社

Excel 2016数据处理与分析实战秘籍 内容简介

《Excel 2016数据处理与分析实战秘籍》围绕Excel数据处理和分析的六项关键技术，聚焦于实战技巧，专注于讲透讲全，使读者醍醐灌顶，豁然开朗，了然于胸。在详解、细讲、深研的基础上，辐射了自定义格式、表格、函数、名称、去重、分列、宏、VBA等功能，使《Excel 2016数据处理与分析实战秘籍》成为Excel数据处理和分析六大基本功能的工具书，更成为拓展、延伸学习Excel的立交桥。《Excel 2016数据处理与分析实战秘籍》展示的数据分析技巧精彩纷呈，读者可从书中的实战案例中快速掌握知识点，并应用到实际工作中，显著提高工作效率。

《Excel 2016数据处理与分析实战秘籍》内容丰富实用，操作详细具体，既适合Excel 2016初、中级读者阅读，也适合公司或企业中从事文秘、管理、市场、营销、财务等工作的办公人员学习，同时还可作为高等院校和社会培训机构的相关教材。

Excel 2016数据处理与分析实战秘籍 目录

前言

第1章 数据填充

1.1 鼠标拖放或双击以填充数据

1.1.1 启用填充柄和单元格拖放功能

1.1.2 填充柄使用之三部曲

1.1.3 使用左键拖放填充

1.1.4 使用右键拖放填充

1.1.5 使用左键双击填充

1.2 自定义序列填充

1.2.1 添加自定义序列“星期”

1.2.2 导入自定义序列“学历”

1.2.3 根据标题自动填充“个人信息”

1.2.4 通过“序列”对话框填充序列

1.3 使用填充命令填充相同内容

1.4 使用公式填充序号、编号

1.4.1 填充无空行数据的连续编号

1.4.2 填充有空行数据的连续编号

1.4.3 规避特殊数字填充编号

1.4.4 填充连续的英文字母

1.5 快速填充

1.5.1 启用记忆式键入和快速填充功能

1.5.2 记忆式键入现有条目

1.5.3 预览快速填充的长度数字

1.5.4 使用命令快速填充宽度数字

1.5.5 快速提取带度量单位的高度数字

1.5.6 从数字中提取数字或插入字符

1.5.7 合并多列内容并添加符号

1.5.8 将姓名分成姓和名两列

1.5.9 排序后多次快速填充

1.5.10 调整字符串的前后顺序

1.5.11 复制相同的内容

1.5.12 灵活转换大小写

1.6 定位填充

1.6.1 填充空白单元格

1.6.2 填充上行内容

第2章 数据验证

2.1 “数据验证”对话框介绍

2.1.1 打开“数据验证”对话框

2.1.2 “设置”选项卡简介

2.1.3 “输入信息”-选项卡简介

2.1.4 “出错警告”选项卡简介

2.1.5 “输入法模式”-选项卡简介

2.2 为非序列设置数据验证

2.2.1 为数值型数据设置数据验证

2.2.2 使用公式自定义数据验证

2.3 为一个序列设置数据验证

2.3.1 直接输入一个序列设置数据验证

2.3.2 引用区域设置有序列的数据验证

2.3.3 使用名称设置有序列的数据验证

2.3.4 设置可以增缩序列的数据验证

2.3.5 设置可模糊输入序列的数据验证

2.4 二级序列实例：从组别中选择人员

2.4.1 建立表头

2.4.2 完善数据表

2.4.3 完善辅助列

2.4.4 设置数据验证

2.4.5 试用数据验证

2.5 三级序列实例：填写籍贯

2.5.1 获取行政区划数据

2.5.2 整理行政区划数据

2.5.3 制作三级序列

2.6 可拓展的三级序列：填写籍贯

2.6.1 建立辅助表

2.6.2 定义名称

2.6.3 建立三级序列

2.7 可缩减的五级序列：填写地址

2.7.1 获取行政区划及代码

2.7.2 获取五级行政区划

2.7.3 制作五级序列

2.7.4 五级序列的缩减

2.8 数据验证的其他问题

2.8.1 数据验证的查找

2.8.2 数据验证的更改

2.8.3 数据验证的清除

2.8.4 数据验证区域的扩展

2.8.5 圈释无效数据

2.8.6 数据验证无效的原因排查

2.8.7 数据验证命令不可用的原因排查

第3章 数据排序

3.1 调用排序命令

3.1.1 在“数据”选项卡直接调用

3.1.2 在“开始”选项卡直接调用

3.1.3 启用“筛选”功能间接调用

3.1.4 创建表格间接调用

3.1.5 创建数据透视表间接调用

3.1.6 通过右键快捷菜单直接调用

3.2 快速地对数据排序

3.2.1 快速地对语文成绩排序

3.2.2 快速地对出生日期排序

3.2.3 快速地对姓名排序

3.3 指定条件对数据排序

3.3.1 按总分和语文成绩排序

3.3.2 按数学成绩的字体颜色排序

3.3.3 按语文成绩的单元格颜色排序

3.3.4 按总分的单元格图标排序

3.3.5 按姓名的笔画数排序

3.3.6 对字母区分大小写排序

3.3.7 对无标题行的数据表排序

3.3.8 对数据表按行排序

3.4 自定义列表排序

3.4.1 创建自定义列表“高中低”

3.4.2 按自定义列表对身高排序

3.5 排序规则和问题

3.5.1 排序规则列表

3.5.2 对混合型数字排序

3.5.3 处理文本中的空格

3.5.4 重新应用排序

3.5.5 排序的常见问题

3.6 排序功能的拓展应用

3.6.1 选定区域排序

3.6.2 序号不参与排序

3.6.3 返回排序前的状态

3.6.4 按内容长短排序

3.6.5 按字母和数字排序

3.6.6 字母区分大小写排序

3.6.7 对数据随机排序

3.6.8 对数据隔行排序

3.6.9 隔行填充列标题

3.6.10 对合并单元格排序

3.6.11 多列分别排序

3.6.12 清除多处空行

3.6.13 VBA指定规则排序

3.7 在数据透视表中排序

3.7.1 创建数据透视表

3.7.2 按班级总分项排序

3.7.3 按班级和个人总分排序

3.7.4 按总分和数学成绩排序

3.7.5 对局部数据排序

3.7.6 按汉字笔画排序

3.7.7 按自定义列表排序

3.7.8 对行排序

3.7.9 手动排序

第4章 数据筛选

4.1 灵活自如的自动筛选

4.1.1 启用自动筛选的方法

4.1.2 在筛选状态下排序

4.1.3 按颜色（图标）筛选

4.1.4 文本按条件筛选

4.1.5 数字按条件筛选

4.1.6 日期按条件筛选

4.1.7 奇妙的关键字筛选

4.1.8 通过唯一值列表筛选

4.1.9 多列多次自动筛选

4.1.10 通过右键快捷菜单筛选

4.1.11 固化自动筛选结果

4.1.12 重新应用自动筛选

4.1.13 自动筛选的退出和清除

4.2 功能强大的高级筛选

4.2.1 高级筛选对话框和条件区域

4.2.2 使用单条件高级筛选

4.2.3 使用多条件“并”的高级筛选

4.2.4 使用多条件“或”的高级筛选

4.2.5 使用多条件“并”“或”结合的高级筛选

4.2.6 使用公式模糊筛选

4.2.7 使用公式精确筛选

4.2.8 使用逻辑条件值筛选

4.2.9 使用通配符实现模糊筛选

4.2.10 撤销或清除高级筛选结果

4.2.11 重组数据表字段或减少字段

4.2.12 巧妙筛选不重复记录

4.2.13 按特定条目筛选（多对多筛选）

4.2.14 对两组数据进行比对式筛选

4.2.15 使用名称进行高级筛选

4.2.16 自动实时刷新筛选结果

4.3 使用SUBTOTAL函数处理筛选结果

4.3.1 自动筛选后得到连续编号

4.3.2 统计筛选后的不重复人数和总人数

4.3.3 返回筛选后第一人和最大年龄值

4.4 在数据透视表中筛选

4.4.1 建立数据透视表

4.4.2 通过标签筛选某人之后的人员

4.4.3 通过值筛选大于或等于60岁的人员

4.4.4 通过搜索框筛选包含某字的人员

4.4.5 通过唯一值列表筛选

4.4.6 对同一字段多次筛选

4.4.7 数据透视表的刷新

4.4.8 数据透视表中的自动筛选

4.4.9 锦上添花的切片器筛选

4.5 使用记录单查询

4.5.1 添加“记录单”按钮

4.5.2 使用单个条件查询

4.5.3 使用运算符查询

4.5.4 使用通配符查询

4.5.5 使用多条件组合查询

4.6 在外部数据库中筛选

4.6.1 准备Excel数据库

4.6.2 安装ODBC驱动程序

4.6.3 Microsoft Query查询向导

4.6.4 Microsoft Query窗口查询

4.6.5 Microsoft Query参数查询

4.6.6 编辑查询和修改参数

第5章 条件格式

5.1 新建条件格式的两种方式

5.1.1 通过快速格式化新建条件格式

5.1.2 通过高级格式化新建条件格式

5.2 基于各自值设置所有单元格的格式

5.2.1 设置色阶样式的条件格式

5.2.2 设置图标样式的条件格式

5.2.3 设置数据条样式的条件格式

5.3 只为包含以下内容的单元格设置格式

5.3.1 为单元格值设置单元格格式

5.3.2 为特定文本设置单元格格式

5.3.3 为发生日期设置单元格格式

5.3.4 为错误值设置单元格格式

5.4 仅对排名靠前或靠后的数值设置格式

5.4.1 通过高级格式化标识末端数据

5.4.2 通过快速格式化标识末端数据

5.5 仅对高于或低于平均值的数值设置格式

5.5.1 通过高级格式化标识靠边数据

5.5.2 通过快速格式化标识靠边数据

5.6 仅对唯一值或重复值设置格式

5.6.1 通过高级格式化标识重复值

5.6.2 通过快速格式化标识重复值

5.7 使用公式确定要设置格式的单元格

5.7.1 公式中的单元格引用

5.7.2 为数据设置自动框线

5.7.3 判断所录数据的正确性

5.7.4 比较两列数据的异同

5.7.5 提醒生日等重要日期

5.7.6 按月份汇总加班时数

5.7.7 多种方法定位查找到的记录

5.7.8 标识隔行或隔项着色的记录

5.7.9 标识距离矩阵的对称性

5.7.10 标识宣传任务的完成情况

5.7.11 标识号码的历史中奖情况

5.7.12 科学校验身份证号码

5.7.13 标识不同类别的学生

5.7.14 标识双重重复项记录

5.8 使用条件格式制作图表图像

5.8.1 制作项目进度图

5.8.2 制作保质期横条图

5.8.3 制作数据环比图

5.8.4 制作多色方块图

5.8.5 制作旋风图

5.8.6 构造地板砖和人物头像

5.9 使用“快速分析”以应用条件格式

5.10 条件格式的复制、查找和清除

5.10.1 条件格式的复制

5.10.2 条件格式的查找

5.10.3 条件格式的清除

5.11 条件格式的管理

5.11.1 新建条件格式规则

5.11.2 编辑条件格式规则

5.11.3 删除条件格式规则

5.11.4 调整规则的优先级

第6章 数据透视表

6.1 创建数据透视表

6.1.1 创建简单的数据透视表

6.1.2 创建多条件汇总的数据透视表

6.1.3 对二维表创建数据透视表

6.2 数据透视表的设置和布局

6.2.1 显示/隐藏数据透视表字段列表

6.2.2 升序排列数据透视表字段

6.2.3 调整数据透视表字段布局

6.2.4 在区域节调整字段区域

6.2.5 隐藏字段标题和筛选箭头

6.2.6 选择数据透视表的区域

6.2.7 改变字段项的位置

6.2.8 更改分类汇总布局

6.2.9 更改总计行布局

6.2.10 更改报表布局

6.2.11 对字段进行分组

6.2.12 巧获多行多列的唯一值

6.3 数据透视表计算

6.3.1 值汇总方式

6.3.2 值显示方式

6.3.3 计算字段和计算项

6.3.4 多重合并计算

6.3.5 数据透视表函数

6.4 数据透视表对象的操作

6.4.1 数据透视表的重命名

6.4.2 数据透视表的移动

6.4.3 数据透视表的复制

6.4.4 数据透视表的删除

参考文献

Excel 2016数据处理与分析实战秘籍 精彩文摘

第1章 数据填充

录入数据是运用Excel处理和分析数据必不可少的一个基础环节。数据若非杂乱无章，而是有章可循，如果还是手工输入，不仅麻烦，而且容易出错，这时候使用数据填充功能，就会是一件让人倍感愉快而轻松的事情。

数据填充是Excel中快速、规范录入数据的神奇功能，可以批量填写数据，瞬间即可填写连续的、有规律的数据或重复的数据，其智能化设计把人们从大量枯燥乏味的数据录入中解脱出来，极大地节约了时间，提高了数据处理的效率。

本章介绍的填充技巧包括鼠标拖放或双击填充、自定义序列填充、使用填充命令填充、用公式填充序号和编号、快速填充、定位填充等内容。

为透彻介绍Excel的填充功能，新建一个Excel文件，将该工作表命名为“第1章　数据填充.xlsx”，随着内容的介绍，将增加一些工作表。

1.1　鼠标拖放或双击以填充数据

使用鼠标拖放或双击填充是Excel最基本的填充方式，它让用户无须记忆一些复杂的规则，有利于提高数据录入效率。