深入浅出Excel

目　录

前言

你的大脑开始关注Excel。我们从这里开始学习，但此时你的大脑可能正在扯你的后腿。你的大脑可能会想“最好把大脑留给更为重要的问题，比如，应该避开哪些野生动物，裸体单板滑雪是否是个很疯狂的举动？”所以，如何才能让你的大脑觉得，你以后要靠电子表格来谋生呢？

谁适合阅读本书？　xxvi

我们知道你在想什么　xxvii

元认知：关于思考的思考　xxix

这样做就能让你的大脑动起来　xxxi

自述文件　xxxii

技术评审团队　xxxiv

致谢　xxxv

1. 公式初探　Excel真正的力量

我们都使用Excel来制作电子表格。

在制作电子表格方面，Excel贡献很大。但真正的Excel高手是那些已经熟练掌握了公式技巧的人。熟练操作数据是指能执行你所需要的计算以及使用做这些计算的公式，并将你的数据变得有用和清晰。如果你掌握了这些公式技巧，那么你真的可以让数据为你唱歌。

你能快乐地度过假期的最后一晚吗？　2

这是你们的预算和实际花销　3

用Excel来做记录是非常棒的　4

使用公式来处理你的数据　5

即使你的数据有所变化，“引用”也可以使你的公式正常工作　11

仔细检查你的公式　14

使用单元格区域引用一列单元格　15

使用SUM将单元格区域内的值相加　15

当你复制/粘贴一个公式时，引用也会改变　21

Excel公式让你更深入地掌控你的数据　26

每个人都还剩下充足的现金，可以在纽约享受一顿美味的晚餐了！　27

2. 视觉设计　美化电子表格

大部分人通常使用Excel来进行页面布局。

很多熟悉Excel的编程高手都惊讶于人们“只”使用该软件的网格来显示信息。但Excel，特别是最新的版本，其作为页面布局工具非常得心应手。使用一些重要的，并且不太明显的Excel工具来做一些复杂的视图是非常方便的。

CRMFreak需要向分析师提交财务报表　30

美元符号是单元格格式的一部分　35

如何为你的数据选择格式　36

老板批准了！　39

设计原则：简洁　40

设计高手们的争论……　41

使用字体来标明什么是最重要的　42

单元格样式使得重复的要素保持一致的格式　46

选择要使用的单元格样式后，继续使用主题来改变电子表格外观　47

他很满意，但还有一些其他问题……　50

为类似的一些组使用接近和对齐　53

你的表格引起了轰动！　57

3. 引用　指引正确的方向

一个公式应该和它的引用一样准确。

无论你的公式多么有创意多么精彩，假如它没有指向正确的数据，那它将没有任何意义。为短的单个公式写引用是很容易的，但一旦这些公式变长并且需要被复制，引用出错的概率将会大大增加。在本章中，你将学习绝对引用和相对引用，以及Excel先进的结构化引用特性，以确保无论你的引用多大多复杂，你的公式都可以保持紧凑和准确。

MIN返回一系列数字中最小的数字　64

运行你的公式并使用你的鼠标让Excel来自动填充这些列　67

Excel使用一个更复杂的引用计算出了正确的答案　68

使用绝对引用来避免复制/粘贴时的变化　73

绝对引用给了你很多选择　75

命名区域简化了你的公式　76

Excel的表格(Table)功能让你的引用变得快速简单　82

结构化引用是绝对引用的一种不同形式　83

你对利润率的预测被证明是准确的　88

4. 不同准度查看数据　排序、缩放和筛选

你的数据看上去很有诱惑力。

只有你懂得数据的含义，你才能发现它们的魅力。在这章中，你将会忘记格式和函数，而只是关注你将如何改变对数据的看法。当你处理你的数据时，寻找需要解决的问题，排序、放大和筛选工具将为你提供许多惊喜的功能来帮助你了解数据的实际含义。

竞选顾问需要帮助——整理他们的筹款数据库　90

寻找大笔金额捐款人的姓名　91

排序可以改变数据的顺序　92

排序为你展示了不同角度的大数据集　95

利用放大功能可以看到更多的数据　103

你的客户对此印象深刻！　106

筛选功能可以隐藏那些你不想看到的数据　107

使用筛选下拉框来选择如何筛选你的数据　108

竞选团队提出了一个意料之外的要求……　109

竞选团队对你的工作非常满意　112

不断有捐款资金注入　115

5. 数据类型　让Excel正确地表示数值

Excel并不是总能表现出它所想的。

有时，Excel向你展现了一个数字，而实际上Excel却认为其是文本。或者它向你展现了一些文本，而实际上它却视其为数字。Excel甚至会向你展示一些既不是数字也不是文本的数据！本章你将学习到，无论数据看起来是什么格式，你都必须按照Excel的方式来看数据。这方面的知识不仅能帮助你更好地掌控你的数据（少发生一点“#$%!是怎么了？”的情况），而且可以帮助你了解公式真正的含义。

你的医生朋友在最后一刻毁掉了他的数据　118

不知为何，你的平均值公式的分母为0　121

Excel中的数据既可以是数字也可以是文本　122

医生以前也遇到过类似的问题　125

你需要一个Excel将你的文本认为是数字的函数　126

研究生也会做一些统计……但是出现了一个问题　132

错误是一种特殊的数据类型　135

现在你的名字被当成科学家的名字印在了书上　140

6. 日期和时间　保持准时

Excel里的日期和时间很难表示。

除非你了解Excel的内部是如何表示它们的。我们都会涉及到这些类型数字的计算，这一章会教你如何计算两个日期间的天、月、年，甚至秒的方法。简单来说，日期和时间的确是一种特别的数据类型和格式。一旦你掌握了基本理论，你就可以使用Excel完美地来做时间调度了。

你有时间来参加训练吗？为了参加马萨诸塞州的马拉松比赛　142

VALUE()返回了一个数字，该数字是日期，却保存为文本格式　146

Excel将日期认成了整数　147

将这两个日期相减，得出的是这两个日期间相隔的天数　148

当日期相减时，注意观察你的格式　152

看上去你在第一个一万公里长跑比赛前没有足够的时间完成训练　153

教练有一个更好的办法　154

DATEDIF()用来计算两个日期间的间隔时间　156

教练很高兴你能加入她的课程　161

Excel将时间用0至1之间的十进制小数来表示　162

教练为你制定了一个Excel挑战课程　165

你已经具有马萨诸塞州马拉松赛的资格了　167

7. 发掘函数　独立探索Excel的特性

Excel有很多函数，绝对超出了你的需要。

经过多年的发展和版本升级，Excel已经积累了大量函数，对于使用这些函数的人来说，这些函数是不可或缺的。Excel是否有足够多的函数绝对不是问题，问题是你不知道那些对你非常有用的函数。我们所说的是哪些函数呢？当然只有你自己知道。下面，你将可以学到一些如何寻找所需公式的技巧，以帮助你高效地工作。

你应该租一个额外的停车位吗？　170

你需要一个计划来了解更多函数　173

Excel帮助页面中有很多提示和技巧　174

这是会展中心下个月售票的数据库。　178

函数参考解析　183

Dataville会展中心的COO出现了……　185

函数是按照数据类型和规程组织的　186

你的电子表格显示了每个日期的总售票数量　192

免费的包厢票！　195

8. 公式审核　让公式运行变得直观

Excel的公式有时候非常复杂。

一针见血，不是吗？如果你所要做的只是简单的计算，那么一张纸、一支笔和一个计算器就足够了。然而，这些复杂的公式有时候会很麻烦——特别是其他人编写的公式，如果你不了解编写者的想法就无法理解这些公式。本章中，你将学习使用Excel中一个简单但是很有用的图形化的功能，叫做公式审核，它能生动地展示你电子表格模型中的数据流。

你应该买房还是租房呢？　198

用净现值将未来的成本折算成今天的价值　202

经纪人给你做了一个电子表格　205

Excel中的模型可以很复杂　206

公式审核显示了公式中参数的位置　208

Excel的贷款函数采用相同的基本元素　212

经纪人电子表格中的PMT公式可以计算出你每月的付款金额　213

公式必须正确，而且假设必须合理　218

经纪人在衡量……　222

房产绝对是个好投资！　225

9. 图表　将数据做成图表

谁想一直盯着枯燥的数字？

通常，一张整洁的图表是更吸引人的数据展示方法。有时候，你的数据太多了，以至于没有图表根本无法将它们全部看清楚。Excel有庞大的图表工具，只要你知道鼠标该往哪儿点，它就能释放魔力，生动而流畅地用图表来展示你的数据吧。

Head First投资公司需要其投资报告的图表　228

使用插入(Insert)选项卡创建图表　231

使用设计(Design)和布局(Layout)选项卡来重做你的图表　232

你的饼图没得到公司图形艺术家的认可　236

你的时间不多了……　247

你的报表非常成功……　249

10. 假设分析　其他可能性

事情可能会向不同的方向发展。

有很多定量因素可能会影响你的业务发展、经济周转、日程安排等。Excel可以出色地帮助你将设想变成模型，并评估这些因素的变化将如何影响你最关心的变量。本章你将学习3 个主要的功能——方案、单变量求解和规划求解，它们的目的是让你对“假设”问题的分析易如反掌。

你的朋友贝蒂是否应该打广告呢？　252

贝蒂对不同的广告配置已经做了最好和最坏的预测　255

方案帮助你了解同一模型中不同输入数据所得的结果　258

方案显示变化的元素在不同配置情况下的结果　259

贝蒂想知道自己的盈亏平衡点在哪里　261

通过尝试不同的候选值，使用单变量求解出所需的数值　262

贝蒂想让你的模型更精细　266

规划求解可以处理更加复杂的优化问题　267

对你的规划求解模型进行一次检查　272

使用规划求解计算你的预测　276

贝蒂的最佳方案通过了……　277

11. 文本函数　文字也可以作为数据

Excel热爱数字，但它同时也能处理你的文本。

Excel有很多种函数帮助你处理文本数据。这些函数可以用于很多种场合，但是有一种场合却不适用，那就是人们不得不处理乱糟糟的数据时。很多情况下，你接收到的数据并不是你想要的格式——例如，来自陌生数据库的数据。文本函数擅长从杂乱无章的数据中提取要点，这样你便可加以分析使用。接下来你会得到更多信息……

你的客户数据库崩溃了！　280

这是数据　281

分列(Text to Columns)采用分隔符拆分数据　282

分列并非所向无敌　285

Excel有一整套函数用于处理文本　286

LEFT和RIGHT是提取文本的基本函数　289

你需要改变公式第二个参数的值　291

客户数据缺失，业务开始受到影响　293

电子表格变得越来越庞大！　297

FIND返回一个数字，该数字确定文本的位置　298

分列只看公式，不看结果　302

选择性粘贴(Paste Special)为你提供多种粘贴选择　302

看起来时间不多了……　305

数据危机解除！　308

12. 数据透视表　强大的分组能力

数据透视表是Excel最为强大的功能之一。

数据透视表是什么？我们为什么要关注它？对Excel新手而言，数据透视表也可能是Excel中最让人头疼的功能之一。但是数据透视表的目的非常简单：将数据快速分组，这样你就能对其进行分析了。正如下文中你即将了解到的，利用数据透视表对数据进行分组和汇总要比单纯使用公式进行分组快多了。当你阅读完本章，你就能以前所未有的速度对数据进行切分了。

Head First 汽车周刊(HFAW)需要对年度汽车资讯进行分析　310

你不得不进行大量的重复操作　313

数据透视表无疑是汇总数据最强大的工具　314

构建数据透视表结构其实就是预览你的字段的走向　316

使用数据透视表汇总数据要比公式更快　320

你的编辑真是太有才了！　322

你就要完成杂志的数据表格了　326

你的数据透视表非常受欢迎！　330

13. 布尔表达式　TRUE和FALSE

Excel中有一种看起来似乎极其简单的数据类型。

它们就是布尔表达式，即简单的TRUE和FALSE 。你可能觉得它们太简单太原始了，在复杂数据分析中没什么用，但事实却并非如此。在这一章中，你将把布尔表达式插入到逻辑公式中来完成各种任务，从整理数据到生成全新的数据点。

在Dataville湖捕鱼的渔夫都遵守规定吗？　332

你手上有每条船的允许捕捞量　333

布尔表达式返回TRUE或FALSE的结果　334

IF根据布尔表达式的条件给出结果　334

你的IF公式需要结合整个命名系统　336

统计一下每种类型的船各有多少只　343

COUNTIFS和COUNTIF差不多，但是前者要强大得多　346

遇到复杂情况的时候，将公式分别应用到各列　350

鱼类申张了正义！　356

14. 数据分割　切分

创造性地运用你的工具。

在前面的13章中，你已经掌握了许多Excel的知识，现在你已经知道(或者知道如何找到)可以解决数据问题的大多数工具。但是，如果你的问题不适合使用这些工具呢？如果你所需的数据并不在一个地方，或者你的数据被分成了不同类别，且不适用你的分析目标？在最后一章，你将使用查找函数和一些已知的数据分割工具，创造性地使用Excel的工具。

你现在为一个监察机构工作，他们需要记录预算情况　358

这是他们想要的图表　359

这是按郡划分的联邦政府支出数据　360

有时候你得到的数据不够用　363

区域的问题更加难以解决　365

这是一个查找关键字　366

VLOOKUP 将交叉引用两个数据源　367

创建数据分段，将正确的数据加入你的分析　374

地缘政治协会想要再做一点细微的调整　376

你让地缘政治协会能够追踪到钱的流向……　380

离开小镇……　381

和你在Dataville的时光太美好了！　381

附录i. 其他　最热门的十大功能(我们没有涵盖到)

你已经学了很多内容了。

但是Excel是一款复杂的程序，还有许多内容没学呢！在附录里，我们将讨论本书由于篇幅限制而没有涵盖的10项内容，但是这10项内容应该是你接下来学习的重点。

1. 数据分析　384

2. 格式刷(Format Painter)　385

3. 数据分析工具库　386

4. 数组公式　386

5. 图形和SmartArt　387

6. 控制再计算和优化性能　388

7. 连接到网络　389

8. 使用外部数据源　389

9. 协作　　390

10. 应用程序可视化基础(VBA)　390

附录ii. 在Excel中安装规划求解　规划求解

默认状态下，有些好用的功能并没有直接安装在Excel中。

没错，为了运行第10章中的优化程序，你需要激活规划求解(Solver)，它是Excel 默认自带的加载项，但你必须先激活它。

在Excel中安装规划求解　392

Head First系列图书每一本都非常畅销，它的版式（以及封面）很特别，可令人阅读起来轻松愉快，不像常见的大部头科技书那样：规规矩矩的版式，字里行间透着呆板、学究与教化。Head First系列图书的编写方式采用引导式教学，它不直接告诉你该怎么做，而是利用故事当作引子，带领读者思考并想办法解决问题。解决问题的过程中又会产生一些新的问题，再继续思考、继续解决问题，这样可以加深体会。作者还利用歪歪斜斜的手写字体增加“现场感”。书中精心设计许多轻松搞笑的对白，让学习过程不会太枯燥。《深入浅出Excel》属于HeadFirst系列图书中的一员。全书旨在以全新的体验方式，帮助读者将Excel技能从入门级提高到专家级。通过本书的学习，读者将掌握对多个工作表中的数据进行计算、使用数据透视表进行解释性分析以及用单变量求解来优化结果、用精妙的可视化数据来展示结论等技巧。　　《深入浅出Excel》面向各层次的Excel使用人员，尤其适合初学者阅读使用。【作者简介】20世纪80年代，当Michael Milton的朋友们在使用BASIC编程，以及玩《情圣拉瑞（Leisure Suit Larry）》的游戏时，他正在使用SuperCalc来制作图表。他的工作主要是帮助人们在正确的场合来显示正确的表格，他希望读者在阅读了本书以后，也能够掌握这样的技巧。