Excel影响力：数据分析·可视化·AI办公

本书致力于全方位提升读者的数据分析、可视化与智能办公的综合能力。

数据分析方面：前 5 章内容均属于数据分析方面的，第 1 章（工具篇）讲解目前主流的两大办公软件微

软 Excel 2019 和 WPS Office（表格） 2019 的特色功能与应用。第 2 章（效率篇）全面提高数据处理、统计等

的效率，掌握高效的数据分析操作方法。第 3 章（整理篇）主要讲解数据整理与查找定位。第 4 章（函数篇）

讲解工作中常用的函数，尤其是重要的函数嵌套。第 5 章（分析篇）讲解数据透视表和数据透视图各项综合

应用。可视化方面：第 6 章（图表篇）以图表的制作、设计和动态图表的呈现为主。如何把处理好的数据转

换成合适的图表，以及如何实现动态图表的制作？本章会给你答案。智能办公方面：第 7 章（代码篇）主要

讲解宏和 VBA 功能以及应用。第 8 章（AI 篇）主要讲借助 AI 工具提升数据分析效率。AI 工具能极大地提

高工作效率，尤其是在数据分析领域，原本很复杂的代码、很烦琐的操作，交给 AI 可以非常轻松地解决。

不管是刚刚接触数据分析的新手，还是经常进行数据分析的老手，抑或是经常使用各种数据分析工具的

职场达人，都能在本书中得到启发和收获。

“老师，Excel 太难了！功能细碎、函数繁多，记不住，而且遇到了实际问题，一脸茫然，不知道该从哪方面入手……”，以上是我在培训 Excel 软件过程中，学员反馈最多的问题。这反映出大家平时学习 Excel 的常见痛点：记不住，更用不起来！

如何才能突破呢？首先，“记不住”往往是因为要记的太多，而且没有完全理解造成的。所以对于这个问题，我的建议就是只记少量常用的功能和函数即可，Excel 的学习同样遵循经典的“二八原则”，只需要掌握小部分的功能和函数，就能解决工作中大部分的问题。这样记忆的量就变少了很多，然后重点理解常用的功能和函数，并能结合实际的实例进行综合应用演练，当下次 在工作中遇到类似的问题，就能迅速找到对应的解决方案。

以上方案有两个关键点：一是提炼工作中常用的功能与函数；二是能将这些功能、函数与实际实例相结合，融合原理讲解和实际操作，这样才能达到更理想的学习效果。为此，本书整合了132 个常用的实例，每个实例都是一个经典的问题，掌握其中的功能与函数以后，就能解决同类型的问题，而且实例是带视频讲解的。具体安排如下。

第 1 章：工具篇，讲解目前主流的两大办公软件微软 Excel 2019 和 WPS Office 2019（表格）的特色功能与应用。新版的微软 Excel 2019 中新增了很多好用的函数，如 XLOOKUP、TEXTJOIN 等，弥补了很多传统函数的短板，极大地提高了效率。国产的 WPS Office 2019（表格）也同样带来了很多的惊喜，尤其是新增了很多符合国人使用习惯的各项功能和操作，非常贴心、方便。

第 2 章：效率篇，全面提高数据处理、统计等的效率。只要掌握了高效的操作方法，就能感受到“四两拨千斤”的乐趣，以前需要手动完成的操作，都可以一键批量完成，以前看似复杂的效果，其实都有高效的“秘密通道”，开启这个通道，效能就能成倍提升！

第 3 章：整理篇，讲解数据整理与查找定位。内容包括数据规范、数据验证、条件格式、定位条件、排序和筛选等，让数据整理、统计变得更加高效。本章非常重要，是数据清洗的关键环节， 也是后续的数据分析与可视化的前提和基础。

第 4 章：函数篇，讲解工作中常用的函数，尤其是重要的函数嵌套。函数的数量非常多，不

需要都记住，只要记住常用、典型的即可，包括统计与计数类函数、逻辑判断函数、文本类函数、日期函数、查询匹配函数等。另外，对于常见的函数报错也提供相应的剖析讲解。

第 5 章：分析篇，讲解数据透视表和数据透视图各项综合应用。这是在进行数据分析过程中非常重要的工具，相对于其他版块的学习，该版块的学习性价比非常高，呈现效果更直观，值得重点学习。

第 6 章：图表篇，以图表的制作、设计和动态图表的呈现为主。在汇报中，讲究“能图不表， 能表不文”，图表传递信息的效率往往比其他形式强很多，但是如何将处理好的数据呈现为合适的图表，以及如何实现动态图表的制作？本章会给你答案。

第 7 章：代码篇，讲解宏和 VBA 功能及应用方法。宏和 VBA 对很多人而言，觉得太难了， 其实只要掌握了几个核心的步骤和方法，尤其是借助它完成工作中重复性很强的工作，你就会发 现，VBA 其实也没那么难。

第 8 章：AI 篇，讲解借助 AI（人工智能）工具提升数据分析效率。随着 AI 工具的普及，人们的工作效率大大提升，尤其在数据分析领域，原本很复杂的代码、很烦琐的操作，交给 AI 可以非常轻松地解决，包括 AI 写代码、洞察分析、AI 操作表格等，而且还可以融入 PY 脚本编辑器， 让 Python 和 AI 为数据分析赋能。

需要说明的是，本书除了第 1 章讲解的新功能需要新版软件支持以外，学习其他章节内容时使用低版本均可完成。

本书额外赠送丰富的福利资源，包括完整配套视频教程、函数词典、Excel 快捷键大全、图表高级模板、常用函数教程及本书配套练习素材。获取方式为：关注公众号“熊王”，回复关键词 “Excel 影响力”即可免费获取。

大数据时代，数据分析能力越来越重要，谁能更快洞察数据并进行综合分析，谁就能赢在起跑线上。希望本书能成为你前进方向的帮手，祝你早日成为数据分析高手！

编 者

$0/5&/54

Excel 影响力

数据分析·可视化·AI 办公

工具篇

摸透工具， 掌握软件的强大功能

1.1.1 区分微软 Excel 和金山 WPS Office（表格）/ 002

1.1.2 如何查看软件的版本信息 / 003

1.2.1 实例 01——新增函数 IFS：多条件判断 / 004

1.2.2 实例 02——新增函数 TEXTJOIN：高效文本连接 / 005

1.2.3 实例 03—— 新增函数 XLOOKUP： 超越 VLOOKUP 的强大存在 / 007

1.2.4 实例04——新增函数 UNIQUE：快速去重提取唯一值/ 009

1.2.5 实例 05——新增函数 FILTER 函数：快速查找筛选结果 / 010

1.2.6 实例 06——新增函数 TOCOL：多列转一列 / 012

1.2.7 实例 07——新增漏斗图：流程分析好帮手 / 013

1.2.8 实例 08——新增设置透视表默认布局 / 015

1.3.1 实例 09——下拉菜单模糊查找：智能匹配查找 / 016

1.3.2 实例 10——粘贴到可见单元格：筛选也能粘贴数据 / 016

目 录

1.3.3 实例 11——快速合并 / 拆分相同单元格 / 017

1.3.4 实例 12——筛选合并单元格：智能识别合并单元格并筛选 / 019

1.3.5 实例 13——智能提取：证件号智能提取生日 / 年龄 / 性别 / 020

1.3.6 实例 14——聚光灯效果：让阅读更聚焦 / 021

1.3.7 实例 15——拒绝重复项：不再担心重复录入数据 / 022

1.3.8 实例 16——仅筛选此项：高效筛选，一步到位 / 023

1.3.9 实例 17——自动计算个税：轻松搞定个税计算 / 025

1.3.10 实例 18——批量合并 / 拆分工作表：一键智能合并与拆分 / 025

效率篇

高效操作，提升数据处理效能

2.1.1 实例 19—— 一秒搞定：高效的快捷操作 / 029

2.1.2 实例 20——快速填充：智能拆分与合并 / 033

2.1.3 实例 21——冻结窗格：轻松搞定表格浏览问题 / 036

2.1.4 实例 22——对齐文字：长短不一的文字也能轻松对齐 / 038

2.1.5 实例 23——编辑区域：设置分区密码 / 039

2.1.6 实例 24——自定义格式：自动更改内容文字颜色 / 041

Excel 影响力

数据分析·可视化·AI 办公

2.1.7 实例 25——附带单位：以“万”为单位显示 / 044

2.1.8 实例 26——查找替换：一列转多列 / 045

2.1.9 实例 27——巧用数据透视表：多列转一列 / 047

2.1.10 实例 28——对照查看：单表与多表的对照查看 / 050

2.1.11 实例 29——筛选图片：让图片能随单元格一起被筛选 / 052

2.1.12 实例30——数字前加0：按指定位数填写数字，不足补0/054

2.2.1 实例 31—— OCR 识别：图片表格转可编辑表格 / 056

2.2.2 实例 32——从网页获取数据：一键获取网页数据并支持更新 / 059

2.3.1 实例 33——相同单元格：快速合并 / 拆分相同单元格 / 063

2.3.2 实例 34——合并多表数据：高效合并新妙招 / 067

整理篇

清洗数据，释放数据真正潜能

3.1.1 实例 35——分列功能：一键统一数据格式 / 070

目 录

3.1.2 实例 36——带单位计算：数据带单位也能计算 / 071

3.1.3 实例 37——提取生日：证件号提取生日并转日期格式

/ 073

3.2.1 实例 38——下拉选项：快速制作下拉选项 / 076

3.2.2 实例 39——二级下拉：制作二级下拉选项 / 077

3.2.3 实例 40——输入提醒：输入信息前实现自动提醒 / 080

3.2.4 实例 41——报错提示：设置出错后的提醒内容 / 081

3.3.1 实例 42——标记最大值：快速标记每一行最大值 / 083

3.3.2 实例 43——标记图标：按指定条件给数据单元格添加图标 / 084

3.3.3 实例 44——突出显示：按指定规则标记相应单元格 / 085

3.3.4 实例 45——数据条：缩小版的条形图 / 087

3.3.5 实例 46——自动变色：根据数据自动整行变色 / 088

3.4.1 实例 47——可见单元格：快速定位可见单元格 / 090

3.4.2 实例 48——巧用查找：根据单元格颜色求和 / 091

3.4.3 实例 49——对比差异：快速查找行 / 列内容差异 / 093

3.4.4 实例 50——两张表对比：对比不同工作表上的两个区域差异 / 094

3.4.5 实例 51——批量填充：批量修改和填充指定内容 / 095

Excel 影响力

数据分析·可视化·AI 办公

3.5.1 实例 52——多关键词排序：按指定优先级排序 / 097

3.5.2 实例 53——自定义排序：按指定顺序排序 / 098

3.5.3 实例 54——工资条模型：给每一行数据设置独立的表头 / 100

3.6.1 实例 55——筛选范围：筛选某一范围的内容 / 102

3.6.2 实例 56——按字数筛选：巧用通配符筛选特定字数内容 / 104

3.6.3 实例 57——高级筛选 1：整理不重复内容 / 105

3.6.4 实例 58——高级筛选 2：筛选复杂条件的内容 / 107

函数篇

实用函数， 实现数据分析赋能

4.1.1 实例 59——巧用 SUM：合并单元格的值批量求和 / 110

4.1.2 实例 60—— LEN+SUBSTITUTE：统计单元格中的人数 / 111

4.1.3 实例 61—— COUNTIF 函数：根据条件统计数量 / 113

目 录

4.1.4 实例 62——COUNTIF+ 数据验证：避免录入重复值 / 114

4.1.5 实例 63—— SUMIF 函数：根据条件求和 / 115

4.1.6 实例 64—— MAX 函数：合并单元格填充序号 / 118

4.1.7 实例 65—— ROW&COLUMN：自动更新行 / 列号 / 119

4.1.8 实例 66—— COUNTA+SUM：计算合并行数量 / 120

4.1.9 实例 67——跨表求和：使用 SUM 函数批量跨表求和 / 121

4.2.1 实例 68—— IF 函数：条件判断并返回特定内容 / 123

4.2.2 实例 69—— IF 函数嵌套：多条件判断返回特定内容 / 124

4.3.1 实例 70—— LEFT+LEN+RIGHT：拆分数字与汉字 / 125

4.3.2 实例 71——MID 函数：自动提取证件号中的出生日期 / 128

4.3.3 实例 72—— TEXT 函数：将内容调整为指定格式 / 129

4.3.4 实例 73—— FIND 函数：按特定内容进行分隔 / 132

4.4.1 实例 74—— DATEDIF 函数：自动计算工龄 / 134

4.4.2 实例 75—— DATE 函数：构建标准日期 / 135

4.4.3 实例 76—— EDATE 函数：日期推移计算 / 136

4.5.1 实例 77—— VLOOKUP 基础：匹配查找经典必备函数

/ 137

Excel 影响力

数据分析·可视化·AI 办公

4.5.2 实例 78—— VLOOKUP 进阶 : 搭配通配符进行匹配查找 / 139

4.5.3 实例 79——区间匹配：VLOOKUP 函数的近似匹配 / 140

4.5.4 实例 80—— INDEX+MATCH：匹配查找黄金搭档 / 141

4.5.5 实例 81——VLOOKUP 嵌套 IF：轻松完成逆序匹配 / 143

4.6.1 实例 82——常见函数报错：各种报错原因分析 / 144

4.6.2 实例 83—— IFERROR：屏蔽报错并显示指定内容 / 148

4.6.3 实例 84—— IFNA：专门解析 #N/A 错误值 / 149

分析篇

透视分析， 原来透视表 / 图如此智能

5.1.1 实例 85——区域与表格：智能表格转换与美化 / 151

5.1.2 实例 86——切片器：插入切片器实现快速筛选 / 156

5.1.3 实例 87——日程表：插入日程表并调整 / 160

5.1.4 实例 88——汇总行：根据需要调整汇总行结果 / 162

5.2.1 实例 89——数据透视表：插入方法与应用 / 163

目 录

5.2.2 实例 90——数据联动：透视表和源数据的联动 / 167

5.2.3 实例 91——筛选拆分：一表拆分为多表 / 169

5.2.4 实例 92——分组显示：将行或列进行编组 / 175

5.2.5 实例 93——条件求和：数据透视表一键搞定 / 180

5.2.6 实例 94——字段名重名：如果提示字段名已存在怎么办 / 182

5.2.7 实例 95——条件计数：数据透视表一键统计 / 182

5.2.8 实例 96——不重复个数：数据透视表轻松搞定 / 183

5.2.9 实例 97——显示最值：一键计算最大值和最小值 / 187

5.2.10 实例 98——设置单元格格式：数据透视表的单元格设置 / 188

5.2.11 实例 99——计算环比：数据透视表计算环比 / 189

5.2.12 实例 100——计算排名：数据透视表计算排名 / 191

5.2.13 实例 101——排序问题：数据透视表排序问题解决 / 192

5.2.14 实例 102——计算字段：数据透视表新增字段 / 194

5.2.15 实例 103——计算项：对字段内的内容进行计算 / 196

5.2.16 实例 104—— GETPIVOTDATA：透视表专用函数 / 199

5.2.17 实例 105——百分比：数据透视表中的各种百分比 / 201

5.2.18 实例 106——屏蔽错误：数据透视表屏蔽报错 / 205

5.3.1 实例 107——单变量求解：根据结果倒推 / 207

5.3.2 实例 108——规划求解：给出最优解答案 / 209

Excel 影响力

数据分析·可视化·AI 办公

图表篇

精彩图表， 让汇报呈现全程高能

6.1.1 实例 109——数据透视图：插入方法与应用 / 215

6.1.2 实例 110——图表元素：增删相关的元素 / 219

6.1.3 实例 111——甘特图制作：用堆积条形图制作 / 221

6.1.4 实例 112——迷你图：一键生成简约迷你图 / 225

6.1.5 实例 113——双坐标轴：柱形图和折线图的组合图表

/ 228

6.1.6 实例 114——自动标记图表特定值：图表也能自动标记

/ 230

6.2.1 实例 115——多图联动：切片器控制多张图表 / 235

6.2.2 实例 116——快速对齐：元素自动吸附对齐 / 237

6.2.3 实例 117——仪表板制作：Excel 动态仪表板制作 / 238

6.2.4 实例 118——联动呈现：Excel 图表在 PPT 中联动 / 240

目 录

代码篇

高级进阶，宏与 VBA 的更多可能

7.1.1 实例 119——录制宏：轻松应对重复性工作 / 244

7.1.2 实例 120——存储宏：宏的保存与位置 / 248

7.1.3 实例 121——编辑宏：VBA代码的力量 / 250

7.1.4 实例 122——宏安全性：调整宏安全级别 / 251

7.1.5 实例 123——使用相对引用：宏的灵活应用 / 253

7.2.1 实例 124—— 一键拆分工作表：按列拆分 / 256

7.2.2 实例 125—— 一键合并工作表：多表合一 / 260

AI 篇

人工智能，数据分析变得无所不能

8.1.1 实例 126—— AI 写代码：智能编写 VBA 代码 / 263

Excel 影响力

数据分析·可视化·AI 办公

8.1.2 实例 127—— AI 写公式：轻松搞定复杂公式 / 268

8.2.1 实例 128——分析数据：Excel 一键智能分析数据 / 271

8.2.2 实例 129——洞察分析：WPS AI 智能分析数据文档 / 273

8.2.3 实例 130—— AI 写公式：WPS AI 帮忙写公式 / 276

8.2.4 实例 131——对话操作表格：WPS AI 帮你操作 / 277

8.2.5 实例 132—— PY 脚本编辑器：AI+Python+WPS 表格 / 278