经济与法译丛--基于EXCEL的数据分析建模和模拟

为什么需要写这本书？
电子表格是从20世纪80年代开始广泛使用的，由此引发了一场教学革命。以前只能用大型计算机或者神秘软件解决的问题，现在我们都可以用台式计算机进行大规模的计算解决，而且以前只能在大型计算机所在的工作室中进行，但现在可以在教室里的个人电脑中的电子表格解决问题。电子表格不仅改变教学的内容，而且学生可以根据兴趣主动探索使用新电子表格工具。在我的教学生涯中，我也不知道收到了多少位欣喜若狂的同学的电话、邮件或来访，告诉我他们刚刚用电子表格构建的各种模型。
我已经教授了近25年的电子表格分析建模相关课程。一直以来，我见证并参与了电子表格的演化发展过程，在这个过程中，我总结了如下事实：
（1）每年来自学生、工作者以及新手对于Excel分析和建模技巧的需求越来越多。
（2）Excel已经发展成为集工具、函数和功能的一体化工具，包括本书使用的最近的Excel版本——Excel 2007。
（3）很高兴地看到Excel用户能创建新的应用和工具处理更加复杂的问题。
（4）那些在电子表格革命之前的学生经常会发现，自己对商科专业的大多数本科生通常学到的知识感到茫然。
上述的每条都是写这本书的原因。第一条表明对Excel所能提供的功能需求是不可预见的。实际上，因为每个经济组织要保持经济的持续生产力，一个熟练应用电子表格的个人在组织中应该会很受欢迎。最起码拥有了这些技能，你可以自己完成，避免由于等待专家的解决方案而延迟进程。这在以前的信息技术部门（IT）是经常发生的，你需要IT组给你提供解决方案和工具，但是只能等待，而现在你完全可以自己解决问题。
第二条和第三条意思是说，当你将聪明、精力充沛、强大的工具以及良好的学习环境结合在一起时，能够产生很好的效果。从我的教学生涯以及咨询过程中能够体会到这点，诀窍是要提供一个教学工具，使得分析变得容易，我希望本书就是这种教学工具。我相信有三个因素可以促进Excel学习——挑选比较有兴趣的教学例子、有条理地引导学生进行推理分析以及全面解释相关的Excel工具达到分析的目的。
最后一点观察是我想针对那些电子表格革命之前接受教育的学生，为他们写本书，给他们正确指引。几年前，我在辛辛那提机场书店中遇见以前的一个MBA学员，他告诉我说，他想在市面上找一本基于Excel建模和数据分析的书籍——“我已经从塔克商学院毕业20年了，现在公司来了一个实习生，他用Excel能很容易做很多事情，我想知道他是怎么做到的？”本书中会介绍数据分析中常用的工具，如图表法、统计推断建模、模拟和优化工具，并利用Excel这些工具解决问题。因此读完本书以后，大多数读者能够成功地自学掌握电子表格分析技术。我们经常会听见学生提出如下问题：“用Excel能做这件事吗？”“我不知道Excel还有那种功能！”等，因此本书除了是一个指引器之外，学生读完后可以知道是否能用Excel解决他面临的问题。
谁会从此书中收益？
本书适合于这样的学生和实际操作者，他们寻找一本单纯介绍Excel的基础书，能够找到三方面的内容——数据分析、商业建模以及模拟。我已经将此书作为本科、行政管理硕士和行政管理教育项目的学习材料。对于大多数学员来讲，这本书可作为半个或者一个学期的学习教材。书中的教学例子有很多用处，在教学的过程中，本书的内容可以作为课堂教学的一种补充，或者是从业人员学习电子表格的入门书，也可以供自学者使用。最后，我相信本书对于将要从业的工作者，就像我在辛辛那提遇到以前的学生一样，可以通过自学本书重新理解数据分析、建模和模拟的概念和原理。
本书的特点
书中选取的教学例子，都是本人多年在从事教学或者咨询过程中遇到的实际案例。有的虽然简单，但是切中问题的关键；有些比较复杂，需要花力气认真阅读，但请不要因为例子复杂而影响你学习的信心。从这些例子中可以学到很多东西，不仅是分析技术，还有解决复杂问题的方法。本书的一些例子，就像现实世界中经常遇到的那样，杂乱无章，需要做出合理的假设，如果要得到问题的解，要简化并做出一些让步。我希望对读者介绍的分析方法和分析技术一样有价值。在解决统计例子中，我花了很多精力给出Excel截屏，希望能帮助读者扎实理解书中的例子。
但是，我要强烈提醒你一件事——本书不是一本Excel操作手册。Excel操作手册集中在介绍Excel工具上，没有分析——它假设你自己会分析。市面上有许多优秀的Excel操作手册和网站（如mrexcel.com），你可以找到特定Excel问题的详细帮助信息。我是想写一本关于数据分析的书，通过学习这本书，分析师可以轻松地处理Excel，这点请你要牢记。因此，总的来说，分析问题是本书的焦点，Excel只是一个很好的工具，要通过它来分析问题。
致谢
在这里我要感谢斯普林格出版社编辑给予的非常有价值的支持，他们是：尼尔斯·彼得·托马斯博士、爱丽丝·布兰克女士和乌里克·斯特里克女士。感谢伊丽莎白·鲍曼女士多年来出色的编辑工作。也要感谢这些年来我教过的无数的学生，特别是比尔·杰伦，他是一个Excel网站的管理人员，他让我成为Excel信徒。最后我要感谢我的家人和朋友，在他们默默地支持下，我才能完成本书，他们分别是——特拉、罗伯特、布兰迪、马克、莉莉和斯科特。

作者格雷罗是弗吉尼亚州威廉斯堡威廉大学商学院的教授，主要教学科研方向是决策、统计、运营管理以及商业定量方法等方面，曾任教于达特茅斯的阿莫斯塔商学院以及圣母大学商学院等。由于他能将复杂的决策问题讲得深入浅出，深受学生欢迎。
他在华盛顿大学获得电气工程专业的硕士学位和运营系统管理专业的博士学位，在得克萨斯大学获取MBA。他在运营管理、产品设计以及规划方面出版了多部著作。
在进入高校研究之前，他曾经在陶氏化学公司以及洛克导弹和空间公司担任过工程师。他在多处任兼职或者合作课题，有政府部门、跨国公司以及美国各种大型或者小型的制造业企业等。
人们常常会发现他坐在风平浪静的海滩上，研究着一本极具挑战性的Excel手册，身旁放着一杯卡普兰迪葡萄酒。

本书介绍了基于Excel的统计数据分析建模和模拟。本书涵盖了统计学中的基本理论和方法，如区间估计、假设检验、方差分析、回归分析和蒙特卡洛模拟等，同时介绍了线性规划、方案管理器和单变量求解等Excel工具的应用。本书最大的特色是每章都围绕一个实际问题，给出分析解决问题的全部过程以及Excel截屏，有利于读者学习Excel数据分析的方法，提高Excel数据处理能力。本书可作为MBA统计学课程的教材，也作为经济管理类本科生和研究生学习统计学和数据处理的参考资料。

第1章　Excel电子表格建模简介 / 001
1.1　引言 / 002
1.2　MBA学生能做什么？ / 002
1.3　为什么要对问题建模？ / 004
1.4　为什么要用Excel建模进行决策？ / 004
1.5　电子表格风水/电子表格工程 / 006
1.6　一个电子表格改进的例子 / 008
1.6.1　朱莉亚的商业问题——一个非常不确定性结果问题 / 008
1.6.2　拉姆的评论 / 011
1.6.3　朱莉亚修改后的工作簿 / 012
1.7　总结 / 016

第2章　定量数据的展示 / 019
2.1　引言 / 020
2.2　数据的分类 / 020
2.3　数据上下文和情景 / 021
对准备数据提出一些建议 / 024
2.4　图表的种类 / 026
2.4.1　功能区和Excel菜单系统 / 027
2.4.2　一些常用的图 / 028
2.4.3　制作表的步骤 / 031
2.5　图表法分析和展示数据的例子 / 035
2.5.1　例子——泰娜的大学第二学期的预算 / 036
2.5.2　收集数据 / 038
2.5.3　整理数据 / 039
2.5.4　分析数据 / 040
2.5.5　展示数据 / 047
2.6　一些用图示法展示数据的最终实践建议 / 048
2.7　总结 / 050

第3章　定量数据的分析 / 055
3.1　引言 / 056
3.2　什么是数据分析？ / 056
3.3　数据分析工具 / 057
3.4　两个数据集的分析 / 060
3.4.1　时间序列数据——图示分析 / 062
3.4.2　横截面数据——图示分析 / 065
3.4.3　时间序列数据分析——描述统计 / 067
3.4.4　横截面数据分析——描述统计 / 069
3.5　时间序列数据分析——预测/分析数据相关性的工具 / 072
3.5.1　图示分析法 / 073
3.5.2　线性回归 / 076
3.5.3　协方差和相关系数 / 080
3.5.4　其他预测方模型 / 082
3.5.5　一些发现 / 083
3.6　横截面数据分析——预测/分析数据相关性的工具 / 083
一些发现 / 089
3.7　总结 / 090

第4章　定性数据的展示 / 095
4.1　引言——什么是定性数据？ / 096
4.2　有效展示定性数据的基本要领 / 096
数据展示和准备计划 / 097
4.3　数据录入和操作 / 099
4.3.1　数据录入工具和精确性 / 100
4.3.2　数据转换成Excel格式 / 103
4.3.3　用逻辑IF函数进行数据转换 / 105
4.3.4　非Excel格式数据的转换 / 108
4.4　应用排序、筛选以及高级筛选进行数据查询 / 110
4.4.1　数据排序 / 111
4.4.2　数据筛选 / 112
4.4.3　筛选 / 113
4.4.4　高级筛选 / 118
4.5　例子 / 121
4.6　总结 / 126

第5章　定性数据的分析 / 131
5.1　引言 / 132
5.2　定性数据分析的基本内容 / 133
处理数据误差 / 134
5.3　数据透视图和数据透视表 / 138
5.3.1　一个例子 / 138
5.3.2　数据透视表 / 140
5.3.3　数据透视图 / 149
5.4　TiendaMía.com例子——问题1 / 152
5.5　TiendaMía.com例子——问题2 / 154
5.6　总结 / 159

第6章　数据分析中的统计推断 / 163
6.1　引言 / 164
6.2　根据数据选择统计方法 / 165
6.3　χ2——卡方检验分类数据的独立性 / 165
假设检验——原假设与备择假设 / 166
6.4　分类与定距数据的z-检验和t-检验 / 169
6.5　一个例子 / 169
6.5.1　z-检验：双样本均值检验 / 172
6.5.2　SC州非囚犯与SC州受EB培训囚犯分数有无差别？ / 173
6.5.3　t-检验：双样本异方差 / 175
6.5.4　TX州囚犯成绩是否高于TX州非囚犯成绩？ / 175
6.5.5　不论州别，囚犯成绩是否高于非囚犯？ / 176
6.5.6　SC州与TX州囚犯在特殊培训前成绩是否有差异？ / 178
6.5.7　EB培训项目是否提高了囚犯成绩？ / 179
6.5.8　如果观察值均值不同，但没有看到分数的一致变化，怎么办？ / 180
6.5.9　总结评论 / 182
6.6　方差分析 / 183
6.6.1　方差分析：单因素例子 / 183
6.6.2　冷藏箱月盗损平均数不同，三个港口盗损平均数是否有差异？ / 185
6.7　试验设计 / 186
6.7.1　完全随机组试验设计举例 / 188
6.7.2　析因实验设计例子 / 192
6.8　总结 / 193

第7章　数据建模和模拟：第一部分 / 201
7.1　引言 / 202
什么是模型？ / 203
7.2　如何对模型进行分类？ / 204
7.3　确定性建模例子 / 206
事件的基本分析 / 207
7.4　理解模型的重要组成部分 / 210
7.4.1　模型准备和设计阶段 / 211
7.4.2　建模阶段 / 211
7.4.3　天气与相关参与者数的确定 / 214
7.4.4　参与者玩游戏的概率 / 216
7.4.5　艾菲尔牧师的假设分析问题 / 216
7.4.6　OLPS活动建模总结 / 217
7.5　基于Excel的建模 / 218
7.5.1　基本模型 / 219
7.5.2　敏感性分析 / 221
7.5.3　表单控件箱中的控件 / 227
7.5.4　选择按钮 / 228
7.5.5　滚动条 / 230
7.6　总结 / 232

第8章　数据建模和模拟：第二部分 / 235
8.1　引言 / 236
8.2　模型模拟的种类和不确定性 / 237
将不确定过程加到模型中 / 238
8.3　蒙特卡洛抽样法 / 238
8.3.1　实施蒙特卡洛模拟的步骤 / 239
8.3.2　几种概率分布简介 / 244
8.3.3　利用泊松分布对到达人数建模 / 247
8.3.4　列查询和水平查询函数 / 249
8.4　一个财务例子——利润表 / 251
8.5　一个营运例子——汽车屋公司 / 254
8.5.1　汽车屋公司建模问题情况 / 258
8.5.2　建立中枢工作表 / 259
8.5.3　构建计算工作表 / 261
8.5.4　泊松到达计量方法的变动——出于建模精度的考量 / 262
8.5.5　足够的样本容量 / 264
8.5.6　建立数据收集工作表 / 265
8.5.7　结论 / 269
8.6　总结 / 272

第9章　规划求解、方案管理与单变量求解工具 / 277
9.1　引言 / 278
9.2　规划求解——约束优化 / 279
9.3　例子——约克河考古预算问题 / 280
9.3.1　公式化 / 282
9.3.2　公式化YRA预算问题的线性规划问题 / 283
9.3.3　准备规划求解工作表 / 283
9.3.4　利用规划求解工具求解 / 287
9.3.5　规划求解报告 / 288
9.3.6　YRA预算的一些问题 / 292
9.4　方案管理器 / 297
9.4.1　例1——计算抵押贷款利率 / 297
9.4.2　例2——利润分析表 / 300
9.5　单变量求解 / 301
9.5.1　例1——利用PMT单元格函数进行单变量求解 / 302
9.5.2　例2——利用CUMIPMT单元格函数进行单变量求解 / 303
9.6　总结 / 305