《Excel实用商业分析》

内容简介

《Excel实用商业分析》以实用为目的的同时考虑商业分析知识的系统结构，详细介绍了商业分析的内容、概念、方法及应用。通过大量例子，《Excel实用商业分析》重点讨论了以Excel作为工具，如何用这些概念和方法来解决企业管理及其他各种活动中的实际问题，特别是如何用Excel对这些问题构建不同的分析模型，从而对问题进行分析并提供解决方案。《Excel实用商业分析》由4篇19章组成。为方便读者复习巩固，各章末尾的小结对该章讨论的重要概念、公式和方法进行了总结。

作者简介

陈星玉，商业管理硕士(MBA)、博士。长期从事与商业分析和财务管理有关的工作，就职于多家国际上市公司，曾任与财务管理、计划和分析等有关的各种高级管理职位，现为某上市公司首席财务官。作者在中国大陆、台湾和香港地区以及美国和德国出版译著和专著若干部，研究论文多篇，其最近的一本著作是《财务分析》(2015，电子工业出版社)。作者曾获得美国注册会计师证书(CPA)、美国注册管理会计师证书(CMA)及美国注册财务管理经理证书(CFM)。

目录

目录

第1篇描述统计
第1章引言2
1．1定量分析与定性分析2
1．1．1定量分析2
1．1．2定性分析3
1．1．3定量分析与定性分析之比较3
1．2定量分析的步骤4
1．3描述统计与推断统计6
1．4变量8
1．5Excel在定量分析中的作用8
1．5．1Excel的简单历史8
1．5．2Excel与定量分析9
1．6本章小结9
第2章描述统计：数据整理及表示10
2．1数据的类型10
2．2定性数据：单变量11
2．2．1频度表11
2．2．2柱形图(直方图)15
2．2．3饼图15
2．2．4伯拉图16
2．2．5折线图和散点图21
2．3定性数据：双变量22
2．3．1交叉表22
2．3．2多重柱形图(簇状柱形图)23
2．3．3堆积柱形图23
2．4定量数据：间断变量24
2．4．1频度表24
2．4．2柱形图25
2．4．3饼图26
2．5定量数据：连续变量28
2．5．1频度表28
2．5．2柱形图30
2．5．3折线图31
2．6本章小结32
第3章描述统计：数据的统计度量33
3．1数据集中趋势的度量33
3．1．1均值33
3．1．2中位数35
3．1．3众数36
3．1．4平均数、中位数、众数之
比较39
3．2数据分散度的度量40
3．2．1极差40
3．2．2平均绝对偏差41
3．2．3方差41
3．2．4标准差43
3．2．5相对分散度――变异系数44
3．2．6异众比率45
3．3百分位数47
3．4数据分布形态的度量：偏态
与峰态48
3．4．1偏态及偏态系数48
3．4．2峰态及峰态系数49
3．5标准差的应用51
3．5．1标准化值51
3．5．2数据分布与标准差关系的
经验法则51
3．6时间序列数据53
3．7Excel【描述统计】工具的应用55
3．8数据透视表57
3．8．1创建数据透视表57
3．8．2数据透视表中计算方法的
改变59
3．8．3数据透视表中群组的建立
及取消60
3．8．4数据透视图63
3．8．5【筛选器】：切片器与
日程表68
3．8．6下查功能71
3．8．7将数据透视表做成不同形式
的表格71
3．9本章小结72
第4章概率基础73
4．1基本概念73
4．2概率的设定法则74
4．2．1随机试验的种类74
4．2．2客观概率法则75
4．2．3主观判断概率法则
(thesubjectiveapproach)76
4．3概率的运算法则77
4．3．1补集事件法则77
4．3．2加法法则77
4．3．3乘法法则80
4．4本章小结82
第5章离散型随机变量：二项分布及
泊松分布84
5．1离散型随机变量84
5．1．1离散型与连续型随机变量84
5．1．2离散型随机变量的概率
分布84
5．1．3离散型变量概率分布特点86
5．1．4离散型随机变量的期望值
及方差87
5．2二项分布88
5．2．1二项分布的定义及条件88
5．2．2二项分布的均值与方差89
5．2．3二项分布的Excel计算89
5．3泊松分布94
5．3．1泊松分布的概念94
5．3．2泊松分布的应用及其Excel
计算95
5．3．3二项分布与泊松分布的关系98
5．4本章小结99
第6章连续随机变量：正态分布100
6．1连续随机变量及其概率分布100
6．1．1连续随机变量100
6．1．2连续随机变量的概率分布100
6．2正态分布101
6．2．1正态分布的概念101
6．2．2正态分布曲线的Excel
作法103
6．2．3标准正态分布及正态分布
的标准化105
6．2．4标准正态分布概率表106
6．2．5正态分布的Excel计算和
应用109
6．3指数分布118
6．4本章小结122
第2篇推断统计
第7章抽样及抽样分布124
7．1抽样方法124
7．1．1简单随机抽样125
7．1．2系统抽样法126
7．1．3分层抽样法127
7．1．4分群抽样法131
7．2抽样分布132
7．2．1平均值的抽样分布：正态
分布总体132
7．2．2平均值的抽样分布：非正态
分布总体132
7．2．3抽样分布：有限总体且抽样
无替代133
7．3抽样分布：应用134
7．4本章小结140
第8章估计142
8．1基本概念142
8．2学生t-分布143
8．2．1学生t-分布的概念144
8．2．2Excel中有关t-分布公式
的应用145
8．3总体平均值置信区间的估计150
8．3．1总体平均值置信区间的
估计(1)：总体为正态分布，
总体标准差δ未知150
8．3．2总体平均值置信区间的
估计(2)：总体为正态分布，
总体标准差δ已知154
8．3．3总体平均值置信区间的
估计(3)：总体分布未知157
8．4总体比例值p的置信区间
估计159
8．5两总体均值差的置信区间162
8．5．1两总体均值差的置信区间
估计(1)：成对样本162
8．5．2两总体均值差的置信区间
估计(2)：独立样本164
8．6两总体比例差的置信区间
估计167
8．7样本大小的选取169
8．7．1样本大小的选取(1)：总体
均值170
8．7．2样本大小的选取(2)：总体
均值差171
8．7．3样本大小的选取(3)：总体
比例值p172
8．7．4样本大小的选取(4)：总体
比例值p差173
8．8本章小结175
第9章假设检验(1)：单样本178
9．1基本概念178
9．2假设检验(1)：总体均值
检验――总体标准差已知
(Z检验)181
9．2．1平均值双边检验：δ已知182
9．2．2平均值单边检验：δ已知183
9．3P值186
9．3．1P值的概念186
9．3．2P值的计算187
9．4假设检验(2)：均值检验――
总体标准差未知(t-检验)191
9．4．1均值检验：右侧单边，总体
δ未知192
9．4．2均值检验：左侧单边，总体
δ未知194
9．4．3均值检验：双边，总体δ
未知195
9．5假设检验：样本比率与总体
比率197
9．6本章小结198
第10章假设检验(2)：二总体比较200
10．1成对双样本平均值的比较201
10．2独立双样本平均值的比较(1)：
二总体方差相等204
10．3独立双样本平均值的比较(2)：
二总体方差未知且不等207
10．4独立双样本平均值的比较(3)：
Z检验210
10．5二总体比率的比较213
10．6二样本方差的比较215
10．7本章小结218
第11章方差分析219
11．1基本概念219
11．1．1完全随机化设计220
11．1．2区集随机化设计221
11．2单因子方差分析221
11．3随机区集实验226
11．4双因子方差分析230
11．5本章小结235
第12章卡方检验236
12．1卡方分布及检验的基本概念236
12．1．1卡方分布的概念236
12．1．2卡方检验的一般步骤237
12．1．3适合卡方检验的条件238
12．2适合度检验239
12．2．1适合度检验的一般步骤239
12．2．2适合度检验：二项分布243
12．2．3适合度检验：泊松分布246
12．2．4适合度检验：正态分布248
12．3同质性检验251
12．4独立性检验254
12．5总体方差的区间估计与假设
检验257
12．5．1总体方差的区间估计257
12．5．2总体方差的假设检验259
12．6本章小结261
第3篇回归与预测
第13章回归及相关分析263
13．1简单线性回归与最小二乘法
准则263
13．2回归方程置信区间估计269
13．2．1估计标准误差269
13．2．2估计值y的置信区间
估计271
13．3相关分析275
13．3．1相关系数275
13．3．2决定系数275
13．3．3相关系数与决定系数的
比较278
13．4有关回归方程的假设检验及
置信区间278
13．4．1相关系数的检验278
13．4．2斜率的检验279
13．4．3斜率的置信区间估计279
13．5方差分析：检验线性关系是
否存在279
13．6增灵：一个有关回归分析的
例子280
13．7本章小结285
第14章多元回归及相关分析287
14．1多元回归方程287
14．2多元回归的置信区间289
14．2．1对y平均值置信区间的
估计289
14．2．2对单个y置信区间的
估计289
14．3多元回归的相关分析291
14．4多元回归方程的显著性
检验292
14．4．1回归方程显著性检验292
14．4．2偏回归系数显著性检验293
14．4．3偏回归系数的置信区间
估计293
14．5本章小结296
第15章预测与时间序列数据297
15．1预测及其种类297
15．1．1预测的种类297
15．1．2预测的方法298
15．1．3预测的步骤299
15．2预测的准确性299
15．2．1平均绝对误差299
15．2．2平均绝对百分比误差299
15．2．3均方误差300
15．2．4均方根误差300
15．3时间序列数据303
15．4基于时间序列数据的预测305
15．4．1移动平均305
15．4．2指数平滑法308
15．4．3趋势311
15．4．4季节指数313
15．5本章小结322
第4篇建模、优化与风险分析
第16章Excel模型324
16．1构建Excel模型的基本步骤324
16．1．1模型的设计325
16．1．2建模326
16．1．3模型检测327
16．1．4什么是好模型331
16．2基准情形分析331
16．3假设分析：模拟运算表332
16．3．1单元格命名333
16．3．2模拟运算表――单变量335
16．3．3模拟运算表――双变量336
16．3．4假设分析：方案管理器338
16．4平衡点分析：单变量求解341
16．5本章小结345
第17章最优方案：线性规划346
17．1最优方案：概念及一般步骤346
17．1．1优化模型的要素346
17．1．2最优化步骤347
17．2线性规划348
17．2．1优化问题的种类348
17．2．2线性关系的特点348
17．2．3单纯形法349
17．2．4线性规划的不同问题
种类349
17．3分配模型350
17．4覆盖模型359
17．4．1采购问题359
17．4．2排班问题362
17．5配料模型364
17．6网络模型366
17．6．1简单运输计划367
17．6．2转运问题370
17．6．3转运问题：有损耗374
17．7二进制模型376
17．7．1工作分配问题
(Assigment)377
17．7．2项目取舍问题379
17．7．3设施地点选择问题381
17．8本章小结383
第18章寻找最优方案：非线性模型及
演化模型385
18．1非线性模型385
18．2演化模型388
18．2．1产量计划389
18．2．2成员分组393
18．2．3最短旅行路线396
18．3本章小结398
第19章风险分析及模拟400
19．1决策风险400
19．2风险分析的步骤400
19．3模拟：概念401
19．4Excel随机数函数及随机数
的产生402
19．4．1RAND()及RANDBETWEEN
(a，b)基本用法402
19．4．2随机数发生器405
19．5模拟：销售量预测407
19．6模拟：订货量408
19．7本章小结412

前言/序言

前言

现代职场最基本的要求将是学习了数学和统计学……

V.Mayer-Schonberger＆K.Cukier

商业分析与决策

我们正在进入或已经生活在大数据时代。所有企业每天都要收集及处理大量数据。据麦肯锡(McKinsey＆Company)(2015)估计,早在2009年，美国1000人以上的公司平均每个公司已累计了200TB的数据。在中国，有人估计到2020年，平均每个人每年产生的数据可达4.1GB。如何使用这些数据已经成为许多企业在激烈的市场竞争中能否制胜的关键因素。数据本身只是一些未经处理的(原始)数字。这些未经处理的数字并不能告诉我们什么，其本身并不能产生价值，亦不能为决策者提供参考。只有经过整理、分析，我们才能找出这些数据所隐藏的趋势、各有关变量间的关系及其他信息，从而为管理人员做出正确的决策及把握商机提供准确而及时的参考和支持。这就是商业分析(BusinessAnalytics，BA)的工作。由于数据重要性的日益凸显，所有管理者都要用到定量分析(quantitativeanalysis)的方法。很多企业对管理人员的基本要求之一是具备一定的商业分析能力，并能将分析结果运用到决策中以解决工作中的各种问题。

商业分析就是对企业的大量数据进行分析，找出与企业商业活动有关的各种趋势(trend)、模式(pattern)和各因素间的相互关系等，使这些数据成为信息(information)，成为知识，为企业决策提供咨询和支持。商业分析试图回答的问题包括一件事情(一种结果，等等)为什么会发生；在相似的条件下这种事情还会再发生吗；如果我们改变其中的一个条件，事情会有什么变化；等等。商业分析用到的主要是统计及其他定量分析方法。商业分析基本上由3部分组成：数据处理及分析(主要是统计)、决策及与决策有关的不确定性分析。

关于《Excel实用商业分析》

１．目的及特点

商业分析中的数据分析基本是商业统计的内容，而决策、不确定性分析及建模则属于管理科学(managementscience)的范畴。从企业管理人员或商业分析师的角度来说，二者实际上是密不可分的。因为《Excel实用商业分析》的读者对象主要是企业管理人员和商业分析师及与大数据分析有关的人员，我们将二者放在一起讨论。作者相信，这种安排更有利于读者对内容的学习及掌握。《Excel实用商业分析》的目的是系统地介绍商业分析，使读者阅读完《Excel实用商业分析》后对商业分析的内容、方法及应用有一个比较好的理解，并能将这些知识及方法运用到工作中，解决工作中遇到的问题。《Excel实用商业分析》的一大特点是运用大量商业方面的例子对各种概念及其应用进行讨论，并介绍如何用Excel来建立模型以分析和解决各种问题。作者希望，读者在读了《Excel实用商业分析》后能够将《Excel实用商业分析》讨论的概念、方法及各种Excel知识和技巧处理用于解决工作或研究中遇到的有关数据处理、分析、决策、风险分析等方面的实际问题。

2．对读者背景知识的要求

数据分析大部分内容都与统计有关，但《Excel实用商业分析》并不假设读者在阅读《Excel实用商业分析》之前有很多统计学的基础。《Excel实用商业分析》将系统介绍数据分析中要用到的统计学中有关的基本概念及方法，但《Excel实用商业分析》不是一本统计学教程，故不会对统计理论做太多讨论。统计学的概念及方法只是我们解决问题的工具。《Excel实用商业分析》的重点将放在如何运用这些工具来解决工作中的实际问题。只要对这些概念及方法有正确的理解，我们就能够正确运用这些概念及方法来解决工作中的各种问题，而没有必要对其中的理论基础做过多讨论。

微软的Excel@几乎已成为所有企业用于数据处理分析的最重要的工具。《Excel实用商业分析》主要目的之一是介绍如何用Excel解决各种商业分析方面的问题。《Excel实用商业分析》只假定读者具备Excel的入门知识，如Excel的建档及其他各种基本知识等。如果读者从未使用过Excel，建议读者读一读Excel的入门书，再用一用Excel，以对其基本用法有所了解。《Excel实用商业分析》会对用到的Excel公式、函数等进行详细讨论。

3．《Excel实用商业分析》内容

《Excel实用商业分析》分4篇19章。第1篇和第2篇属于数据分析，第3篇为回归与预测，第4篇为建模、优化与风险分析。

商业统计可分为两大部分：描述统计和推断统计。《Excel实用商业分析》第1篇是描述统计，包括6章(第1章～第6章)，将系统介绍统计学的基本概念及各种统计分布类型，主要包括描述、总结和表达不同类型数据的概念及方法。我们将首先讨论定量分析的特点及步骤(第1章)，数据的整理及表示(第2章)，然后是数据的分散和集中程度及表示方法(第3章)。概率的概念是研究各种随机变量的基础，我们用一章(第4章)的篇幅介绍了概率的基本概念及计算。第5章和第6章则是离散型与连续型随机变量的常见分布。

第2篇是推断统计方面的内容，共有6章(第7章～第12章)，包括抽样及各种估计和检验的方法及其应用。第7章抽样及抽样分布讨论了各种不同的抽样方法。这些方法都是为了使抽到的样本是随机的(或尽可能是随机的)，样本特征尽可能反映总体特征。第8章是根据样本统计值估计总体统计值。由于假设检验在数据分析中的重要性，本篇用了4章的篇幅(第9章～第12章)讨论各种假设检验及其应用。

第3篇是回归与预测，由3章(第13章～第15章)组成。回归要回答的是不同变量之间关系的问题。第13章侧重于简单线性回归，第14章则讨论了多元回归的问题；第15章的内容包括时间序列数据的特点及各种预测方法。

《Excel实用商业分析》最后一篇即第4篇分为4章(第16章～第19章)。第16章包括Excel建模的方法和技巧，以及不同类型的Excel模型。第17章和第18章则对如何寻找最优方案进行了讨论。第19章的内容涵盖了风险分析的概念及方法，特别是如何用模拟的方法对风险进行分析。

4．《Excel实用商业分析》的术语及符号

(1)《Excel实用商业分析》术语

《Excel实用商业分析》术语的中文主要根据下列著作确定。

有关统计的术语：

戴久永.统计概念与方法.1998.三民书局，647页.

吴柏林.现代统计学.2013.五南图书出版公司，468页.

有关Excel的术语：

MicrosoftExcel帮助.https://support.office.com/zh-cn/excel

苏蕊,等.Excel表格、公式、函数与图表完全学习手册.2013.电子工业出版社，337页.

(2)有关符号

{}:表示随书所附Excel文件的文件名。

例如，{C03F3.32}表示Excel的文件名为C03，工作表的名字为F3.32，即第3章(C03)图3.32(F3.32)的数据、计算、模型或图。有时一个工作表会有数个图，这时会在F后面将该工作表的图列出，如{C02F2.8-2.18}表示该工作表的图或数据包括图2.8～图2.18。

书中有时会介绍一些Excel应用的小窍门(tips)，若其中有数据引用，表示方法与上面相似，但将F改成了T。如{C02T2.1}表示Excel的文件名为C02，工作表的名字为T2.1，即第2章(C02)Excel小窍门2.1(T2.1)的数据、计算或模型。与小窍门有关的工作表(T)位于该章Excel文件的最右(后)方。

【】：表示Excel中的各种选项和功能。

例如，【数据分析】表示Excel中的“数据分析”选项。

→：Excel操作过程中从一步(上一步)到另(下)一步。

例如，单击【数据】→【规划求解】，表示单击Excel的【数据】选项，然后单击【规划求解】。

5．致谢

在《Excel实用商业分析》写作过程中，作者与同事就有关数据分析及Excel应用的讨论让作者受益匪浅。好友何秀娟女士、张绍辉博士及王薇博士的建议使《Excel实用商业分析》增色不少。作者感谢同事和好友的支持。

《Excel实用商业分析》承石会敏和徐萍女士悉心编辑，特此感谢。

《Excel实用商业分析》由澳门基金会资助出版。

陈星玉(echen05@yahoo.com)

2017年1