正版新书 Microsoft Excel 365学习手册(第11版) [美]迈克尔·亚历山大 清华大学出版社 表处理软

本书内容

● 了解和探索Excel 365的新功能

● 创建工作表和工作簿，以及导入数据

● 编写公式和自定义函数来执行计算

● 了解Power Pivot和Power Query

● 使用图表和示意图来可视化数据

● 使用模拟分析、规划求解、分析工具库等分析数据

● 使用Visual Basic for Applications和用户窗体实现Excel 365的自动化

Michael Alexander是Slalom Consulting的高级顾问，具有超过15年的数据和报表管理经验。他迄今已撰写了十几本使用Microsoft Excel进行高级商业分析的图书；凭借对Excel社区做出的突出贡献，他获得了Microsoft Excel MVP称号。

Dick Kusleika连续12年蝉联Microsoft Excel MVP称号，使用Microsoft Office已逾20年。Dick撰写了多本关于Excel和Access的图书。

无论你想了解Excel 365的哪种用法，本书都将倾囊相授！

Microsoft Excel 365功能卓越，全球无数企业和个人用户使用Excel 365来收集、理解和解读数据。Excel办公系列书籍在广大读者中引起热烈反响，一直畅销不衰。本书对上一版做了全面细致的更新，娓娓道来，指引你创建和使用电子表格，编写公式和自定义函数，可视化数据，以及基于组织的数据创建报表和预测。本书既适合初级用户，也适合高级用户，将帮助你解锁数据的价值和潜力。

目    录

第 I部分  Excel基础知识

第1章  Excel简介3

1.1  了解Excel的用途3

1.2  了解工作簿和工作表4

1.3  在工作表中导航6

1.3.1  用键盘导航6

1.3.2  用鼠标导航7

1.4  使用功能区8

1.4.1  功能区选项卡8

1.4.2  上下文选项卡9

1.4.3  功能区中的命令类型10

1.4.4  用键盘访问功能区11

1.5  使用快捷菜单13

1.6  自定义快速访问工具栏13

1.7  使用对话框15

1.7.1  导航对话框15

1.7.2  使用选项卡式对话框16

1.8  使用任务窗格16

1.9  创建第一个Excel工作簿17

1.9.1  开始创建工作表17

1.9.2  填充月份名称18

1.9.3  输入销售数据18

1.9.4  设置数字的格式19

1.9.5  让工作表看上去更有吸引力19

1.9.6  对值求和20

1.9.7  创建图表20

1.9.8  打印工作表21

1.9.9  保存工作簿21

第2章  输入和编辑工作表数据23

2.1  了解数据类型23

2.1.1  数值23

2.1.2  文本输入24

2.1.3  公式24

2.1.4  错误值25

2.2  在工作表中输入文本和值25

2.2.1  输入数值25

2.2.2  输入文本25

2.2.3  使用输入模式26

2.3  在工作表中输入日期和时间26

2.3.1  输入日期值27

2.3.2  输入时间值27

2.4  修改单元格内容27

2.4.1  删除单元格内容28

2.4.2  替换单元格内容28

2.4.3  编辑单元格内容28

2.4.4  学习一些实用的数据输入方法29

2.5  应用数字格式35

2.5.1  使用自动数字格式35

2.5.2  通过功能区设置数字格式36

2.5.3  使用快捷键设置数字格式36

2.5.4  使用“设置单元格格式”对话框设置数字格式37

2.5.5  添加自定义数字格式39

2.6  在平板电脑上使用Excel39

2.6.1  探索Excel的平板电脑界面39

2.6.2  在平板电脑上输入公式40

2.6.3  “绘图”功能区简介41

第3章  基本工作表操作43

3.1  学习Excel工作表基本原理43

3.1.1  使用Excel窗口43

3.1.2  激活工作表45

3.1.3  向工作簿添加新工作表46

3.1.4  删除不再需要的工作表46

3.1.5  更改工作表名称47

3.1.6  更改工作表选项卡颜色47

3.1.7  重新排列工作表47

3.1.8  隐藏和取消隐藏工作表48

3.2  控制工作表视图49

3.2.1  放大或缩小视图以便更好地查看工作表49

3.2.2  在多个窗口中查看工作表50

3.2.3  并排比较工作表51

3.2.4  将工作表窗口拆分成窗格51

3.2.5  通过冻结窗格来保持显示标题52

3.2.6  使用监视窗口监视单元格53

3.3  使用行和列53

3.3.1  选择行和列54

3.3.2  插入行和列54

3.3.3  删除行和列55

3.3.4  更改列宽和行高55

3.3.5  隐藏行和列56

第4章  使用Excel区域和表格59

4.1  单元格和区域简介59

4.1.1  选择区域60

4.1.2  选择完整的行和列60

4.1.3  选择非连续的区域61

4.1.4  选择多工作表区域61

4.1.5  选择特殊类型的单元格63

4.1.6  通过搜索选择单元格65

4.2  复制或移动区域67

4.2.1  使用功能区中的命令进行复制67

4.2.2  使用快捷菜单命令进行复制68

4.2.3  使用快捷键进行复制69

4.2.4  使用拖放方法进行复制或移动70

4.2.5  复制到相邻的单元格70

4.2.6  向其他工作表复制区域71

4.2.7  使用Office剪贴板进行粘贴71

4.2.8  使用特殊方法进行粘贴72

4.2.9  使用“选择性粘贴”对话框74

4.3  对区域使用名称75

4.3.1  在工作簿中创建区域名称76

4.3.2  管理名称78

4.4  向单元格添加批注79

4.4.1  显示批注80

4.4.2  回复批注81

4.4.3  编辑批注和回复81

4.4.4  删除批注和回复81

4.4.5  关闭批注会话81

4.5  添加单元格注释82

4.5.1  显示注释83

4.5.2  设置注释格式83

4.5.3  编辑注释84

4.5.4  删除注释85

4.6  使用表格85

4.6.1  理解表格的结构85

4.6.2  创建表格86

4.6.3  向表格添加数据87

4.6.4  排序和筛选表格数据87

4.6.5  更改表格外观92

第5章  设置工作表格式95

5.1  了解格式设置工具95

5.1.1  使用“开始”选项卡中的格式设置工具96

5.1.2  使用浮动工具栏96

5.1.3  使用“设置单元格格式”对话框97

5.2  设置工作表格式97

5.2.1  使用字体来设置工作表的格式97

5.2.2  更改文本对齐方式99

5.2.3  使用颜色和阴影102

5.2.4  添加边框和线条103

5.3  使用条件格式105

5.3.1  指定条件格式105

5.3.2  使用图形条件格式105

5.3.3  创建基于公式的规则109

5.3.4  条件格式公式示例110

5.3.5  使用条件格式113

5.4  使用命名样式方便地设置格式114

5.4.1  应用样式114

5.4.2  修改现有样式115

5.4.3  创建新样式116

5.4.4  从其他工作簿合并样式116

5.4.5  使用模板控制样式116

5.5  了解文档主题117

5.5.1  应用主题118

5.5.2  自定义主题119

第6章  了解Excel文件和模板121

6.1  创建新工作簿121

6.2  打开现有工作簿122

6.2.1  筛选文件名123

6.2.2  选择文件显示首选项124

6.3  保存工作簿125

6.4  使用自动恢复126

6.4.1  恢复当前工作簿的版本127

6.4.2  恢复未保存的工作127

6.4.3  配置自动恢复127

6.5  使用密码保护工作簿127

6.6  组织文件128

6.7  其他工作簿信息选项128

6.7.1  “保护工作簿”选项128

6.7.2  “检查问题”选项129

6.7.3  “版本历史”选项129

6.7.4  “管理工作簿”选项129

6.7.5  “浏览器视图”选项129

6.7.6  “兼容模式”部分129

6.8  关闭工作簿130

6.9  保护工作的安全130

6.10  使用模板131

6.10.1  探索Excel模板131

6.10.2  使用默认模板133

6.10.3  使用自定义工作簿模板134

第7章  打印工作成果137

7.1  基本打印功能137

7.2  更改页面视图138

7.2.1  “普通”视图138

7.2.2  “页面布局”视图139

7.2.3  “分页预览”视图140

7.3  调整常用页面设置141

7.3.1  选择打印机142

7.3.2  指定要打印的内容142

7.3.3  更改页面方向143

7.3.4  指定纸张大小143

7.3.5  打印多份报表143

7.3.6  调整页边距144

7.3.7  了解分页符145

7.3.8  打印行和列标题145

7.3.9  对打印输出进行缩放设置146

7.3.10  打印单元格网格线147

7.3.11  打印行和列标题147

7.3.12  使用背景图像147

7.4  为报表添加页眉或页脚148

7.4.1  选择预定义的页眉或页脚149

7.4.2  了解页眉和页脚元素代码149

7.4.3  其他页眉和页脚选项150

7.5  其他与打印相关的主题151

7.5.1  在工作表之间复制页面设置151

7.5.2  禁止打印特定的单元格151

7.5.3  禁止打印对象152

7.5.4  为工作表创建自定义视图152

7.5.5  创建PDF文件153

第8章  自定义Excel用户界面155

8.1  自定义快速访问工具栏155

8.1.1  快速访问工具栏简介155

8.1.2  向快速访问工具栏添加新命令156

8.1.3  其他快速访问工具栏操作158

8.2  自定义功能区159

8.2.1  自定义功能区的目的159

8.2.2  可以自定义的项159

8.2.3  如何自定义功能区160

8.2.4  重置功能区161

第 II部分  使用公式和函数

第9章  公式和函数简介165

9.1  了解公式的基础知识165

9.1.1  在公式中使用运算符166

9.1.2  了解公式中的运算符优先级167

9.1.3  在公式中使用函数169

9.2  在工作表中输入公式171

9.2.1  通过指向输入公式172

9.2.2  将区域名称粘贴到公式中173

9.2.3  向公式中插入函数173

9.2.4  函数输入提示174

9.3  编辑公式175

9.4  在公式中使用单元格引用176

9.4.1  使用相对、绝对和混合引用176

9.4.2  更改引用类型178

9.4.3  引用工作表外部的单元格178

9.5  公式变量简介179

9.5.1  理解LET函数180

9.5.2  公式变量的应用180

9.6  在表格中使用公式181

9.6.1  汇总表格中的数据181

9.6.2  在表格中使用公式183

9.6.3  引用表格中的数据184

9.7  更正常见的公式错误184

9.7.1  处理循环引用185

9.7.2  指定在什么时候计算公式186

9.8  使用高级命名方法187

9.8.1  为常量使用名称187

9.8.2  为公式使用名称188

9.8.3  使用区域交集188

9.8.4  对现有引用应用名称189

9.9  使用公式190

9.9.1  不使用硬编码数值190

9.9.2  将编辑栏用作计算器190

9.9.3  精确复制公式190

9.9.4  将公式转换为数值191

第10章  理解和使用数组公式193

10.1  理解遗留数组公式194

10.1.1  遗留数组公式的示例194

10.1.2  编辑遗留的数组公式194

10.2  动态数组简介195

10.2.1  理解溢出区域197

10.2.2  引用溢出区域198

10.3  探索动态数组函数199

10.3.1  SORT函数200

10.3.2  SORTBY函数201

10.3.3  UNIQUE函数201

10.3.4  RANDARRAY函数202

10.3.5  SEQUENCE函数202

10.3.6  FILTER函数204

10.3.7  XLOOKUP函数206

第11章  使用公式执行常用数学运算209

11.1  计算百分比209

11.1.1  计算目标的百分比209

11.1.2  计算百分比变化210

11.1.3  计算带负值的百分比变化211

11.1.4  计算百分比分布212

11.1.5  计算累积总计212

11.1.6  使用百分比增加或减小值213

11.1.7  处理除零错误214

11.2  数字舍入215

11.2.1  使用公式舍入数字215

11.2.2  舍入到最接近的分215

11.2.3  舍入到有效位216

11.3  统计区域中的值217

11.4  使用Excel的转换函数218

第12章  使用公式处理文本219

12.1  使用文本219

12.2  使用文本函数220

12.2.1  连接文本字符串220

12.2.2  将文本设为句子形式221

12.2.3  删除文本字符串中的空格222

12.2.4  从文本字符串中提取部分字符串223

12.2.5  在文本字符串中查找特定字符223

12.2.6  找到字符的第二个实例224

12.2.7  替换文本字符串225

12.2.8  统计单元格中的特定字符226

12.2.9  在公式中添加换行226

12.2.10  清理文本字段中的奇怪字符227

12.2.11  在数字中填充0228

12.2.12  设置文本字符串中数字的格式228

12.2.13  使用DOLLAR函数229

第13章  使用公式处理日期和时间231

13.1  Excel如何处理日期和时间231

13.1.1  了解日期序号231

13.1.2  输入日期232

13.1.3  了解时间序号233

13.1.4  输入时间234

13.1.5  设置日期和时间格式234

13.1.6  日期问题235

13.2  使用Excel的日期和时间函数236

13.2.1  获取当前日期和时间236

13.2.2  计算年龄237

13.2.3  计算两个日期之间的天数238

13.2.4  计算两个日期之间的工作日天数238

13.2.5  排除假日，生成营业日列表239

13.2.6  提取日期的一部分240

13.2.7  计算两个日期之间的年数和月份数241

13.2.8  将日期转换为儒略日期格式242

13.2.9  计算一年已完成天数的百分比和剩余天数的百分比243

13.2.10  返回给定月份的最后一个日期243

13.2.11  计算日期的日历季度244

13.2.12  计算日期的财季245

13.2.13  从日期返回财务月246

13.2.14  计算一个月中第N个工作日的日期246

13.2.15  计算每个月最后一个工作日的日期247

13.2.16  提取时间的一部分248

13.2.17  计算流逝的时间248

13.2.18  舍入时间值249

13.2.19  将用小数表达的小时、分钟或秒钟转换为时间249

13.2.20  向时间增加小时、分钟或秒钟250

第14章  使用公式进行条件分析251

14.1  了解条件分析251

14.1.1  检查是否满足简单条件251

14.1.2  检查多个条件252

14.1.3  验证条件数据253

14.1.4  检查是否同时满足条件1和条件2254

14.1.5  检查是否满足条件1或条件2255

14.2  执行条件计算256

14.2.1  对满足特定条件的所有值求和256

14.2.2  对满足两个或更多个条件的所有值求和258

14.2.3  对给定日期范围内的值求和259

14.2.4  统计满足特定条件的值260

14.2.5  统计满足两个或更多个条件的值261

14.2.6  获取满足特定条件的所有数字的平均值262

14.2.7  获取满足两个或更多个条件的所有数字的平均值262

第15章  使用公式进行匹配和查找265

15.1  查找公式简介265

15.2  使用Excel的查找函数266

15.2.1  基于左侧查找列精确查找值266

15.2.2  基于任意列查找精确值268

15.2.3  横向查找值269

15.2.4  隐藏查找函数返回的错误269

15.2.5  在区间值列表中找到最接近匹配270

15.2.6  从多个表格中查找值272

15.2.7  基于双向矩阵查找值274

15.2.8  基于多个条件查找值275

15.2.9  找出列中的最后一个值276

第16章  在表格和条件格式中使用公式279

16.1  突出显示满足特定条件的单元格279

16.2  突出显示列表1中存在但列表2中不存在的值281

16.3  突出显示既在列表1中存在又在列表2中存在的值282

16.4  基于日期突出显示283

16.4.1  突出显示两个日期之间的日期284

16.4.2  基于到期日突出显示日期285

第17章  避免工作表出错287

17.1  发现并更正公式错误287

17.1.1  括号不匹配288

17.1.2  单元格中显示一组井号(#)289

17.1.3  空白单元格不为空289

17.1.4  多余的空格字符289

17.1.5  返回错误的公式290

17.1.6  运算符优先级问题293

17.1.7  未计算的公式293

17.1.8  小数位精度的问题293

17.1.9  “虚链接”错误294

17.2  使用Excel中的审核工具294

17.2.1  找出特殊类型的单元格295

17.2.2  查看公式295

17.2.3  追踪单元格关系295

17.2.4  追踪错误值296

17.2.5  修复循环引用错误297

17.2.6  使用后台错误检查功能297

17.2.7  使用公式求值298

17.3  查找和替换298

17.3.1  查找信息299

17.3.2  替换信息299

17.3.3  查找格式300

17.3.4  工作表拼写检查300

17.4  使用自动更正301

第 III部分  创建图表和其他可视化

第18章  开始创建Excel图表305

18.1  图表的概念305

18.1.1  了解Excel的图表处理方式306

18.1.2  嵌入式图表307

18.1.3  图表工作表307

18.1.4  图表的组成部分308

18.1.5  图表的限制310

18.2  创建图表的基本步骤310

18.2.1  创建图表310

18.2.2  切换行和列的方向311

18.2.3  更改图表类型311

18.2.4  应用图表布局312

18.2.5  应用图表样式313

18.2.6  添加和删除图表元素313

18.2.7  设置图表元素的格式313

18.3修改和自定义图表314

18.3.1  移动图表和调整图表大小315

18.3.2  将嵌入式图表转换为图表工作表315

18.3.3  复制图表315

18.3.4  删除图表316

18.3.5  添加图表元素316

18.3.6  移动和删除图表元素316

18.3.7  设置图表元素的格式316

18.3.8  复制图表的格式317

18.3.9  重命名图表317

18.3.10  打印图表318

18.4  了解图表类型318

18.4.1  选择图表类型318

18.4.2  柱形图320

18.4.3  条形图321

18.4.4  折线图322

18.4.5  饼图323

18.4.6  XY(散点图)324

18.4.7  面积图325

18.4.8  雷达图325

18.4.9  曲面图326

18.4.10  气泡图326

18.4.11  股价图327

18.5  Excel的较新图表类型327

18.5.1  直方图328

18.5.2  排列图328

18.5.3  瀑布图329

18.5.4  箱形图329

18.5.5  旭日图330

18.5.6  树状图330

18.5.7  漏斗图330

18.5.8  地图330

第19章  使用高级图表技术333

19.1  选择图表元素333

19.1.1  使用鼠标进行选择334

19.1.2  使用键盘进行选择334

19.1.3  使用“图表元素”控件进行选择335

19.2  用于修改图表元素的用户界面选项335

19.2.1  使用“设置格式”任务窗格335

19.2.2  使用图表自定义按钮336

19.2.3  使用功能区337

19.2.4  使用浮动工具栏337

19.3  修改图表区337

19.4  修改绘图区338

19.5  处理图表中的标题339

19.6  处理图例339

19.7  处理网格线341

19.8  修改坐标轴341

19.8.1  更改数值坐标轴341

19.8.2  更改类别坐标轴345

19.9  处理数据系列347

19.9.1  删除或隐藏数据系列347

19.9.2  为图表添加新数据系列347

19.9.3  更改数据系列所使用的数据348

19.9.4  在图表中显示数据标签350

19.9.5  处理丢失的数据351

19.9.6  添加误差线352

19.9.7  添加趋势线353

19.9.8  创建组合图353

19.9.9  显示数据表355

19.10  创建图表模板355

第20章  创建迷你图357

20.1  迷你图类型357

20.2  创建迷你图358

20.3  自定义迷你图360

20.3.1  调整迷你图单元格的大小360

20.3.2  处理隐藏或丢失的数据360

20.3.3  更改迷你图类型361

20.3.4  更改迷你图的颜色和线宽361

20.3.5  突出显示某些数据点361

20.3.6  调整迷你图坐标轴刻度362

20.3.7  伪造参考线363

20.4  指定日期坐标轴364

20.5  自动更新迷你图365

20.6  显示动态区域的迷你图365

第21章  使用自定义数字格式和形状实现可视化367

21.1  使用数字格式进行可视化367

21.1.1  设置基本数字格式367

21.1.2  创造性设置自定义数字格式369

21.1.3  使用符号增强报表374

21.2  将形状和图标用作视觉元素377

21.2.1  插入形状377

21.2.2  插入SVG图标图形378

21.2.3  插入3D模型378

21.2.4  设置形状和图标的格式379

21.2.5  使用形状增强Excel报表380

21.2.6  创建动态标签382

21.2.7  创建链接的图片382

21.3  使用SmartArt和艺术字384

21.3.1  SmartArt基础384

21.3.2  艺术字基础385

21.4  使用其他图形类型385

21.4.1  图形文件简介385

21.4.2  插入屏幕快照386

21.4.3  显示工作表背景图像386

21.5  使用公式编辑器386

第 IV部分  管理和分析数据

第22章  导入和清理数据391

22.1  导入数据391

22.1.1  从文件导入391

22.1.2  对比导入与打开操作393

22.1.3  导入文本文件394

22.1.4  复制和粘贴数据396

22.2  清理数据396

22.2.1  删除重复的行396

22.2.2  识别重复的行397

22.2.3  拆分文本398

22.2.4  更改文本的大小写401

22.2.5  删除多余空格402

22.2.6  删除奇怪字符402

22.2.7  转换值403

22.2.8  对值进行分类403

22.2.9  连接列404

22.2.10  重新排列各列405

22.2.11  随机排列行405

22.2.12  从URL中提取文件名405

22.2.13  匹配列表中的文本406

22.2.14  将纵向数据更改为横向数据407

22.2.15  填补已导入报表中的空白408

22.2.16  拼写检查409

22.2.17  替换或删除单元格中的文本410

22.2.18  将文本添加到单元格410

22.2.19  解决结尾负号问题411

22.2.20  数据清理检查表411

22.3  导出数据411

22.3.1  导出到文本文件411

22.3.2  导出到其他文件格式412

第23章  使用数据验证413

23.1  数据验证简介413

23.2  指定验证条件414

23.3  能够应用的验证条件类型415

23.4  创建下拉列表416

23.5  对数据验证规则使用公式417

23.6  了解单元格引用417

23.7  数据验证公式示例418

23.7.1  只接受文本419

23.7.2  接受比前一个单元格更大的值419

23.7.3  只接受非重复的输入项419

23.7.4  接受以特定字符开头的文本419

23.7.5  只接受一周中的特定日期420

23.7.6  只接受总和不超过特定值的数值420

23.7.7  创建从属列表420

23.8  使用数据验证但不限制输入421

23.8.1  显示输入信息421

23.8.2  提供建议项421

第24章  创建和使用工作表分级显示423

24.1  工作表分级显示简介423

24.2  创建分级显示426

24.2.1  准备数据426

24.2.2  自动创建分级显示427

24.2.3  手工创建分级显示427

24.3  使用分级显示429

24.3.1  显示级别429

24.3.2  向分级显示中添加数据429

24.3.3  删除分级显示429

24.3.4  调整分级显示符号429

24.3.5  隐藏分级显示符号430

第25章  链接和合并计算工作表431

25.1  链接工作簿431

25.2  创建外部引用公式432

25.2.1  了解链接公式的语法432

25.2.2  通过指向创建链接公式433

25.2.3  粘贴链接433

25.3  使用外部引用公式433

25.3.1  创建指向未保存的工作簿的链接433

25.3.2  打开一个包含外部引用公式的工作簿434

25.3.3  更改启动提示435

25.3.4  更新链接435

25.3.5  更改链接源435

25.3.6  断开链接436

25.4  避免外部引用公式中的潜在问题436

25.4.1  重命名或移动源工作簿436

25.4.2  使用“另存为”命令436

25.4.3  修改源工作簿437

25.4.4  使用中间链接437

25.5  合并计算工作表438

25.5.1  通过公式合并计算工作表439

25.5.2  使用“选择性粘贴”功能合并计算工作表439

25.5.3  使用“合并计算”对话框来合并计算工作表440

25.5.4  工作簿合并计算示例441

25.5.5  刷新合并计算443

25.5.6  有关合并计算的更多信息443

第26章  数据透视表简介445

26.1  数据透视表简介445

26.1.1  数据透视表示例445

26.1.2  适用于数据透视表的数据447

26.2  自动创建数据透视表450

26.3  手动创建数据透视表451

26.3.1  指定数据451

26.3.2  指定数据透视表的放置位置451

26.3.3  指定数据透视表布局452

26.3.4  设置数据透视表的格式454

26.3.5  修改数据透视表455

26.4  更多数据透视表示例457

26.4.1  每个分行每天新增的存款总额是多少457

26.4.2  一周中哪一天的存款金额最多458

26.4.3  每个分行的每种账户类型的开户数是多少458

26.4.4  开户时存取的金额是多少458

26.4.5  柜员最常开的账户类型是什么459

26.4.6  哪个分行的柜员为新客户开的支票账户最多460

26.5  学习更多知识460

第27章  使用数据透视表分析数据461

27.1  使用非数值数据461

27.2  对数据透视表中的项进行分组463

27.2.1  手动分组示例463

27.2.2  自动分组示例464

27.3  使用数据透视表创建频率分布467

27.4  创建计算字段或计算项469

27.4.1  创建计算字段470

27.4.2  插入计算项471

27.5  使用切片器筛选数据透视表474

27.6  用日程表筛选数据透视表475

27.7  引用数据透视表中的单元格476

27.8  创建数据透视图477

27.8.1  数据透视图示例478

27.8.2  关于数据透视图的更多介绍480

27.9  使用数据模型481

第28章  执行电子表格模拟分析487

28.1  模拟分析示例487

28.2  模拟分析的类型488

28.2.1  执行手动模拟分析489

28.2.2  创建模拟运算表489

28.2.3  使用方案管理器494

28.3借助人工智能分析数据499

28.3.1  使用Excel的建议499

28.3.2  查询分析的数据500

第29章  使用单变量求解和规划求解来分析数据503

29.1  反向执行模拟分析503

29.2  单一单元格单变量求解504

29.2.1  单变量求解示例504

29.2.2  有关单变量求解的更多信息505

29.3  规划求解简介506

29.3.1  适合通过规划求解来解决的问题506

29.3.2  一个简单的规划求解示例507

29.3.3  探索规划求解的选项510

29.4  规划求解示例511

29.4.1  求解联立线性方程511

29.4.2  最小化运输成本512

29.4.3  分配资源514

29.4.4  优化投资组合515

第30章  使用分析工具库分析数据519

30.1  分析工具库概述519

30.2  安装分析工具库加载项520

30.3  使用分析工具520

30.4  分析工具库工具简介521

30.4.1  方差分析工具521

30.4.2  相关系数工具521

30.4.3  协方差工具522

30.4.4  描述统计工具522

30.4.5  指数平滑工具523

30.4.6  F-检验(双样本方差检验)工具523

30.4.7  傅里叶分析工具523

30.4.8  直方图工具523

30.4.9  移动平均工具524

30.4.10  随机数发生器工具525

30.4.11  排位与百分比排位工具526

30.4.12  回归工具526

30.4.13  抽样工具527

30.4.14  t-检验工具527

30.4.15  z-检验工具(双样本平均差检验)527

第31章  保护工作成果529

31.1  保护类型529

31.2  保护工作表529

31.2.1  取消锁定单元格530

31.2.2  工作表保护选项531

31.2.3  分配用户权限532

31.3  保护工作簿532

31.3.1  需要密码才能打开工作簿533

31.3.2  保护工作簿的结构533

31.4  VBA工程保护534

31.5  相关主题535

31.5.1  将工作表保存为PDF文件535

31.5.2  将工作簿标记为最终版本535

31.5.3  检查工作簿536

31.5.4  使用数字签名536

第 V部分

了解Power Pivot和Power Query

第32章  Power Pivot简介541

32.1  了解Power Pivot的内部数据模型541

32.1.1  Power Pivot功能区541

32.1.2  将Excel表格链接到Power Pivot542

32.2  从其他数据源载入数据548

32.2.1  从关系数据库载入数据548

32.2.2  从平面文件载入数据550

32.2.3  刷新和管理外部数据连接554

第33章  直接操作内部数据模型557

33.1  直接为内部数据模型提供数据557

33.2  管理内部数据模型中的关系561

33.3  管理查询和连接562

第34章  向Power Pivot添加公式563

34.1  使用计算列增强Power Pivot数据563

34.1.1  创建你的第一个计算列563

34.1.2  设置计算列的格式565

34.1.3  在其他计算中引用计算列566

34.1.4  向最终用户隐藏计算列566

34.2  使用DAX创建计算列567

34.2.1  找出可安全用于计算列的DAX函数567

34.2.2  创建DAX驱动的计算列568

34.2.3  引用其他表的字段570

34.2.4  嵌套函数572

34.3  了解计算度量值572

34.4  使用Cube函数来解放数据574

第35章  Power Query简介577

35.1  Power Query的基础知识577

35.1.1  了解查询步骤582

35.1.2  查看高级查询编辑器583

35.1.3  刷新Power Query数据583

35.1.4  管理现有查询584

35.1.5  了解列级操作585

35.1.6  了解表级操作586

35.2  从外部源获取数据587

35.2.1  从文件导入数据588

35.2.2  从数据库系统导入数据590

35.3  从其他数据系统获取数据591

35.4  管理数据源设置591

35.5使用Power Query进行数据分析593

35.5.1数据分析选项593

35.5.2数据分析的快速操作594

第36章  使用Power Query转换数据595

36.1  执行常见的转换任务595

36.1.1  删除重复记录595

36.1.2  填充空字段597

36.1.3  填充空字符串598

36.1.4  连接列598

36.1.5  改变大小写599

36.1.6  查找和替换文本600

36.1.7  修整和清除文本600

36.1.8  提取左侧、右侧和中间的值602

36.1.9  提取首字符和结尾字符603

36.1.10  提取中间字符603

36.1.11  使用字符标记拆分列604

36.1.12  逆透视列606

36.1.13  逆透视其他列606

36.1.14  透视列607

36.2  创建自定义列608

36.2.1  使用自定义列进行连接609

36.2.2  了解数据类型转换610

36.2.3  使用函数扩展自定义列的功能611

36.2.4  向自定义列添加条件逻辑612

36.3  分组和聚合数据613

36.4  使用自定义数据类型615

第37章  使查询协同工作619

37.1  重用查询步骤619

37.2  了解追加功能622

37.2.1  创建必要的基础查询623

37.2.2  追加数据623

37.3  了解合并功能625

37.3.1  了解Power Query连接625

37.3.2  合并查询626

37.3.3  了解模糊匹配628

第38章  提高Power Query的生产率631

38.1  关于如何提高Power Query生产率的一些提示631

38.1.1  快速获取关于查询的信息631

38.1.2  将查询组织成组631

38.1.3  更快速地选择查询中的列632

38.1.4  重命名查询步骤632

38.1.5  快速创建引用表632

38.1.6  复制查询来节省时间633

38.1.7  查看查询依赖项633

38.1.8  设置默认加载行为633

38.1.9  防止数据类型自动更改633

38.2  避免Power Query的性能问题635

38.2.1  使用视图而不是表635

38.2.2  让后台数据库服务器完成一些计算635

38.2.3  升级到64位Excel635

38.2.4  通过禁用隐私设置来提高性能636

38.2.5  禁用关系检测636

第 VI部分

Excel自动化

第39章  VBA简介641

39.1  VBA宏简介641

39.2  显示“开发工具”选项卡642

39.3  宏安全性简介642

39.4  保存含有宏的工作簿643

39.5  两种类型的VBA宏644

39.5.1  VBA子过程644

39.5.2  VBA函数645

39.6  创建VBA宏646

39.6.1  录制VBA宏646

39.6.2  关于录制VBA宏的更多信息652

39.6.3  编写VBA代码654

39.7  学习更多知识662

第40章  创建自定义工作表函数663

40.1  VBA函数概述663

40.2  查看一个简单示例664

40.2.1  创建自定义函数664

40.2.2  在工作表中使用函数664

40.2.3  分析自定义函数665

40.3  函数过程简介666

40.4  执行函数过程667

40.4.1  从过程中调用自定义函数667

40.4.2  在工作表公式中使用自定义函数667

40.5  使用函数过程参数668

40.5.1  创建无参数的函数668

40.5.2  创建有一个参数的函数668

40.5.3  创建另一个有一个参数的函数669

40.5.4  创建有两个参数的函数670

40.5.5  创建有一个区域参数的函数670

40.5.6  创建一个简单但有用的函数671

40.6  调试自定义函数671