EXCEL数据管理与数据透视

1）畅销书作者、微软MVP李锐的倾注心血之作，融合23年实战与16年教学经验，带你突破职场办公效率瓶颈
2）一次性掌握数据工具、函数公式、数据透视技术，为决策提供有力支持，为基于AI的数据管理与应用打下坚实基础
3）买书即送高清录播课、随书案例文件、课程相关视频与公式集等，并提供专家答疑

本书由畅销书作者、微软MVP李锐撰写，融合其23年实战与16年教学经验；旨在通过实际案例与实用技巧，帮助读者一次性掌握Excel数据工具、函数公式和数据透视技术，从而高效处理海量数据，优化工作流程，突破职场效率瓶颈，为未来基于AI的数据管理与应用奠定基础。 本书共16章，分为三部分。第一部分为数据工具的核心应用（第1~5章）：第1章概述企业面临的低效困境，并给出高效办公的解决方案；第2~5章详细解析删除重复值、数据验证、快速填充与条件格式等核心功能，帮助读者构建规范的数据源，掌握数据工具的核心应用技术。第二部分为基于函数公式的数据管理（第6~10章）：结合实战案例，系统讲解逻辑判断、文本处理、日期计算、查找引用与统计计算函数，以实现数据的自动化复杂计算功能。第三部分为数据透视与数据分析（第11~16章）：深入探讨数据透视表与透视图的应用，涵盖数据透视表的创建、布局变换、多维度分析（排序、筛选、统计计算）及透视图展示，助力读者从海量数据中提炼关键信息，实现数据的洞察与分析。

李锐，石家庄锐明科技有限公司CEO、微软MVP、Office办公类畅销书作者、51CTO金牌讲师，拥有10余年管理与数据分析经验，15年Office培训经验。办公领域的知名专家（微博40万粉丝、抖音12万粉丝），已发布多套办公相关的视频课程，深受广大用户喜爱。

前言

第一部分 数据工具的核心应用

第1章 如何利用Excel高效办公2

1.1 企业的低效困境与高效办公解决方案2

1.1.1 企业的低效困境2

1.1.2 高效办公解决方案2

1.2 Excel数据工具的作用与优势3

1.2.1 一键删除重复数据3

1.2.2 一键标注重复数据4

1.2.3 禁止输入重复数据4

1.3 Excel函数公式的作用与优势5

1.3.1 封装复杂的计算过程5

1.3.2 按条件合并同类项5

1.3.3 从多张报表中查询数据6

1.3.4 从多张工作表中查询数据6

1.4 Excel数据透视表的作用与优势8

1.4.1 海量数据快速汇总8

1.4.2 报表布局灵活变换8

1.4.3 动态交互统计计算9

第2章 删除重复值10

2.1 按单条件删除重复值11

2.2 按多条件删除重复值12

2.3 从下向上删除重复值13

第3章 数据验证16

3.1 让报表智能提示输入信息17

3.2 禁止在报表内输入不规范数据19

3.3 禁止在表格中输入重复值21

3.4 创建下拉菜单22

3.5 创建二级下拉菜单27

3.6 批量圈释无效数据34

第4章 快速填充36

4.1 快速提取出生日期37

4.2 快速拆分数据40

4.3 快速合并多列数据41

4.4 快速提取地址信息42

4.5 分段显示数据43

4.6 对数据进行加密显示44

第5章 条件格式48

5.1 突出显示重复数据50

5.2 突出显示高于平均值的数据52

5.3 利用数据条对报表进行可视化显示54

5.4 利用色阶对报表进行可视化显示56

5.5 利用图标集对报表进行可视化显示58

5.6 自助对整行目标数据标记颜色61

第二部分 基于函数公式的数据管理

第6章 逻辑判断类数据管理66

6.1 按条件返回结果66

6.2 按多级条件进行嵌套计算69

6.3 多条件同时满足的判断70

6.3.1 且关系多条件判断：使用IF+AND函数70

6.3.2 且关系多条件判断：使用IF函数+乘号（*）72

6.4 多条件任意满足的判断72

6.4.1 或关系多条件判断：使用IF+OR函数73

6.4.2 或关系多条件判断：使用IF函数+加号（+）74

6.5 复杂多条件的判断75

6.5.1 复杂多条件判断的思路解析75

6.5.2 方案1：使用IF+AND+OR函数75

6.5.3 方案2：使用IF函数+乘号（*）+加号（+）76

6.6 判断数据是否为数值格式77

6.7 规避公式返回的错误值78

6.8 使用IFS函数进行多条件判断79

6.9 灵活匹配数据80

6.10 简化公式结构83

6.11 自定义封装数据86

第7章 文本处理类数据管理89

7.1 按要求提取数据89

7.1.1 常用的文本提取函数89

7.1.2 文本提取函数示例91

7.2 按要求合并数据93

7.3 按要求定位数据94

7.3.1 FIND函数和SEARCH函数的用法94

7.3.2 文本定位函数示例95

7.4 按要求替换数据96

7.4.1 SUBSTITUTE函数的用法96

7.4.2 REPLACE函数的用法97

7.4.3 SUBSTITUTE函数和REPLACE函数的区别98

7.4.4 REPLACEB函数的用法98

7.5 按要求转换数据98

7.5.1 TEXT函数的用法98

7.5.2 TEXT函数示例99

7.6 按区域合并数据101

7.7 加分隔符合并数据102

7.7.1 TEXTJOIN函数的用法102

7.7.2 示例1：合并单元格区域数据103

7.7.3 示例2：合并函数返回的内存数组103

7.8 按分隔符拆分数据104

7.8.1 TEXTSPLIT函数的用法104

7.8.2 将数据拆分到多列和多行105

第8章 日期时间类数据管理106

8.1 提取年、月、日数据107

8.2 提取小时、分钟、秒数据108

8.3 合并日期和时间数据109

8.3.1 DATE函数的用法109

8.3.2 TIME函数的用法110

8.4 按要求计算星期相关数据111

8.4.1 WEEKDAY函数的用法111

8.4.2 WEEKNUM函数的用法112

8.5 按要求推算日期113

8.5.1 EDATE函数的用法113

8.5.2 EOMONTH函数的用法114

8.6 按要求计算工作日115

8.6.1 WORKDAY.INTL函数的用法115

8.6.2 NETWORKDAYS.INTL函数的用法117

8.6.3 示例：复杂排班下的工作日计算117

8.7 按要求统计日期间隔119

8.7.1 DATEDIF函数的用法119

8.7.2 示例：根据入职日期精确统计工龄120

第9章 查找引用类数据管理122

9.1 查找数据122

9.1.1 VLOOKUP函数的用法122

9.1.2 示例1：按照员工编号查询业绩124

9.1.3 示例2：按照姓名查询所有科目成绩124

9.1.4 注意事项125

9.2 按位置引用数据126

9.2.1 INDEX函数的用法126

9.2.2 示例1：从列/行数据中按行号/列号引用数据127

9.2.3 示例2：从多行多列区域中引用数据127

9.2.4 示例3：从多个不连续区域中引用数据127

9.3 按条件定位数据位置128

9.3.1 MATCH函数的用法128

9.3.2 示例1：精确查找数据129

9.3.3 示例2：在升序区域中进行近似查找129

9.3.4 示例3：在降序区域中进行近似查找129

9.4 组合查找数据130

9.4.1 INDEX+MATCH函数组合查找的原理130

9.4.2 示例1：在列/行区域中按条件查找数据130

9.4.3 示例2：在多行多列区域中按条件查找数据131

9.4.4 示例3：在多个不连续区域中按条件查找数据131

9.5 偏移引用数据133

9.5.1 OFFSET函数的用法133

9.5.2 示例1：偏移引用单个/多个数据134

9.5.3 示例2：偏移引用区域数据135

9.6 跨表引用数据135

9.6.1 INDIRECT函数的用法135

9.6.2 示例1：按A1引用样式引用数据136

9.6.3 示例2：按R1C1引用样式引用数据136

9.6.4 示例3：动态引用数据137

9.6.5 示例4：跨表引用数据138

9.7 灵活查找数据140

9.7.1 XLOOKUP函数的用法140

9.7.2 示例1：纵向/横向查找数据140

9.7.3 示例2：容错查找数据141

9.7.4 示例3：查找多列数据141

9.7.5 示例4：从下向上查找数据142

9.8 按条件筛选数据142

9.8.1 FILTER函数的用法142

9.8.2 示例1：按单条件筛选数据143

9.8.3 示例2：按多条件筛选数据143

9.9 提取不重复数据144

9.9.1 UNIQUE函数的用法144

9.9.2 示例1：纵向/横向提取不重复数据145

9.9.3 示例2：提取只出现过一次的数据145

9.10 按要求排列数据145

9.10.1 SORTBY函数的用法145

9.10.2 示例1：升序/降序排列表格146

9.10.3 示例2：按多条件排列表格146

第10章 统计计算类数据管理148

10.1 按要求进行舍入计算148

10.1.1 常用的Excel舍入函数148

10.1.2 示例1：将利润值保留两位小数150

10.1.3 示例2：计算计费小时数150

10.1.4 示例3：将结算金额舍入到百位150

10.2 按要求统计数据150

10.2.1 常用的Excel统计函数150

10.2.2 平均值和中值152

10.2.3 示例：计算多重统计值152

10.3 按条件求和统计153

10.3.1 SUMIFS函数的用法153

10.3.2 示例1：按单条件进行求和154

10.3.3 示例2：按多条件进行求和154

10.4 统计计算155

10.4.1 SUMPRODUCT函数的用法155

10.4.2 示例1：简化计算过程155

10.4.3 示例2：按条件进行计数156

10.4.4 示例3：按条件进行求和156

10.4.5 示例4：按权重进行加权计算157

10.4.6 注意事项158

10.5 按条件进行计数统计158

10.5.1 COUNTIFS函数的用法158

10.5.2 示例1：按单条件进行计数统计159

10.5.3 示例2：按多条件进行计数统计159

10.5.4 示例3：按关键词进行计数统计159

10.6 分段统计160

10.6.1 FREQUENCY函数的用法160

10.6.2 优势与局限162

10.7 排除隐藏行统计162

10.7.1 SUBTOTAL函数的用法162

10.7.2 示例1：排除筛选隐藏行后进行统计163

10.7.3 示例2：排除手动隐藏行后进行统计164

10.8 忽略错误值后进行统计165

10.8.1 AGGREGATE函数的用法165

10.8.2 示例1：忽略错误值后进行统计167

10.8.3 示例2：忽略隐藏行和错误值后进行统计167

10.8.4 扩展说明167

10.9 按条件计算极值168

10.9.1 MAXIFS和MINIFS函数的用法168

10.9.2 示例：按单/多条件筛选值168

第三部分 数据透视与数据分析

第11章 数据透视表的创建方法172

11.1 数据透视表的数据管理规范172

11.1.1 对数据源的规范性要求172

11.1.2 示例1：补全列标题不完整的表格173

11.1.3 示例2：批量删除表格中的多余空行174

11.1.4 示例3：将文本数字批量转换为规范数值175

11.1.5 示例4：对不规范日期数据进行批量转换178

11.1.6 示例5：清除合并单元格并智能填充178

11.2 数据透视表的3种常用创建方法183

11.2.1 插入默认的数据透视表183

11.2.2 插入推荐的数据透视表185

11.2.3 插入数据透视图和数据透视表185

11.3 通过多重合并计算数据区域创建数据透视表186

11.4 利用超级表创建动态数据透视表189

11.4.1 将普通表格转换为超级表189

11.4.2 使用超级表创建动态数据透视表190

11.4.3 检查动态数据透视表是否支持增加行/列192

11.4.4 刷新数据透视表的两种方式194

11.5 通过定义名称创建动态数据透视表195

11.5.1 通过定义名称动态引用数据源区域195

11.5.2 将名称作为数据透视表的数据源196

第12章 数据透视表的布局变换198

12.1 行列布局变换198

12.1.1 示例1：按区域分类汇总销售额198

12.1.2 示例2：按渠道分类汇总销售额199

12.1.3 示例3：按区域和渠道分类汇总销售额199

12.1.4 示例4：按区域和渠道分级汇总销售额201

12.2 页筛选布局变换202

12.2.1 单字段页筛选布局变换202

12.2.2 多字段页筛选布局变换203

12.3 报表布局的整体变换204

12.3.1 以压缩形式显示205

12.3.2 以大纲形式显示206

12.3.3 以表格形式显示206

12.3.4 设置是否重复项目标签207

12.4 分类汇总设置209

12.4.1 设置是否显示分类汇总行209

12.4.2 设置分类汇总行的显示位置211

12.5 数据透视表的空行设置212

12.5.1 在每个项目后插入空行212

12.5.2 删除每个项目后的空行212

12.6 数据透视表的移动、复制和全选213

12.6.1 移动数据透视表213

12.6.2 复制数据透视表214

12.6.3 全选数据透视表215

第13章 数据透视表的数据排序216

13.1 手动排序216

13.2 自动排序216

13.3 自定义个性化排序218

13.4 扩展说明221

第14章 数据透视表的数据筛选222

14.1 字段筛选222

14.2 标签筛选222

14.3 模糊筛选223

第15章 数据透视表的统计计算226

15.1 设置值汇总依据226

15.2 设置值显示方式229

15.3 计算同比增长230

15.4 计算环比增长233

第16章 数据透视图237

16.1 数据透视图的作用与优势237

16.2 由表格直接创建数据透视图238

16.3 由数据透视表创建数据透视图239

16.4 使用切片器灵活筛选数据透视图242

16.5 使用切片器的注意事项244

16.6 获取更多学习资料的方法246