作者：蒲括邵朋编著
出版社：信息技术第二出版分社
格式: AZW3, DOCX, EPUB, MOBI, PDF, TXT
精通Excel数据统计与分析试读：
前言
1980年，著名未来学家阿尔文·托夫勒在其出版的经典著作《第三次浪潮》中预言：如果说IBM的主机拉开了信息化革命的大幕，那么“大数据”则是第三次浪潮的华彩乐章。
2011年5月，麦肯锡公司发布了《大数据：创新、竞争和生产力的下一个前沿领域》报告，陈述了“大数据”时代的到来，指出“数据已经渗透到每一个行业和业务职能领域，逐渐成为重要的生产因素”。
2012年，Twitter上每天发布超过4亿条微博，Facebook上每天更新的照片超过1000万张，Farecast公司用将近10万亿条价格记录来预测机票价格。
关于数据在我们这个时代所起的作用，其实也无需赘言。但如何把隐没在一大批看起来杂乱无章的数据里的信息、规律、关键点萃取出来，却是很多人都应该了解，甚至是应该深入研究的学问。对很多在工作中需要跟数据打交道的人，或者就是以数据统计、分析为工作的人来说，不仅仅是需要一定的统计学知识，而且还需要用统计分析工具将结果呈现出来。
毋庸置疑，对大多数人来讲，用得最多的可以用来进行数据统计与分析的工具就是Excel——这不仅仅是因为Excel相对容易获得，还因为Excel具备丰富的统计与分析功能，可以满足大多数人的大部分需求。
而这些需要用 Excel 进行数据统计与分析的人，大致可以分为这么几类：普通的办公一族，经常需要对数据进行统计与分析，而且想让自己的分析更加专业；从事数据统计与分析工作，使用专业的数据分析软件，也以 Excel 作为备用工具；统计学及相关专业的学生；想从事数据分析相关工作的人。
本书在对用Excel进行数据统计与分析的讲解上，紧扣这部分人的学习需求——先给出实例，然后讲解需要应用的分析原理，最后详细给出用Excel实现的方法与步骤。
需要说明的是，本书默认读者已经掌握以下内容：创建工作簿、插入工作表、保存文件等基本操作；在各个工作簿之间浏览；使用Windows基本功能，例如文件管理、复制和粘贴等操作。
本书的组织结构
本书分为两个部分。
第一部分：基础知识。
这一部分包括第1章和第2章。这一部分首先总体介绍了统计分析中需要用到的统计工具和统计图形等。目的是让读者对Excel在统计分析中的应用有一个基本的了解。
第二部分：统计分析。
第3～11章，分别介绍了假设检验、方差分析、回归分析、相关分析、判别分析、时间序列分析、马尔可夫链分析、聚类分析以及主成分分析等内容。对这些分析方法，本书给出了详细的例子及操作过程，以供读者参考。
章节结构
本书在介绍每种分析方法时也分为两个部分。
第一部分：分析原理。
本书在每章都会先介绍分析方法的原理，方便读者理解每步操作的实际作用，进而学会理解得到的结果的意义，以及如何对结果进行判定。
第二部分：分析实例。
为了方便读者学习如何在 Excel 中实现分析方法，每种分析方法都会分别以统计函数、趋势线和数学分析工具等不同的方式来实现。对于不同的实例，最简洁方便的分析方法不可能完全一样，所以需要在熟练使用各种实现方式之后，才能在实际中灵活使用，提高效率。
致谢
在成书过程中，需要感谢门春杰、张铮、王杉、马宏和李广鹏等参与了本书的部分编写工作，感谢杜强和王命达等参与本书的审校对并对本书策划提出了宝贵意见。同时，也向关心本书的同仁和朋友表示诚挚的感谢。
尽管本书编者尽了最大努力，仍难免有不尽如人意之处，恳请广大读者提出宝贵意见和建议。欢迎登录http://www.book95.com 与我们联系，也可以发邮件至jiahongfei@ptpress.com.cn与图书编辑联系。编者2013年夏第1章描述性统计
描述性统计（Descriptive Statistic）是通过图表或数学方法，对统计数据进行整理、分析，并对数据的分布状态、数字特征和随机变量之间的关系进行估计和描述的方法。描述性统计的任务就是描述随机变量的统计规律。
要完整地描述随机变量的统计特性需要用分布函数，但求随机变量的分布函数并不容易。实际上，对于一些问题也不需要去全面考察随机变量的变化规律，而只需知道随机变量的某些特征。
例如，研究某一地区居民的消费水平，只需知道该地区的平均消费水平即可；但如检查一批灯泡的质量时，则既需要注意灯泡的平均寿命，又需要注意灯泡寿命与平均寿命的偏离程度。尽管这些数值不能完整地描述随机变量，但能描述随机变量在某些方面的重要特征，这些数字特征在理论和实践上都具有重要的意义。
因此，在分析数据时，一般首先要对数据进行描述性统计分析，以发现其内在的规律，再选择进一步分析的方法。在描述性统计中，主要使用集中趋势、离散程度、偏度度量、峰度度量等方法来描述数据的集中性、分散性、对称性和尖端性，以归纳数据的统计特性。常用的描述统计量有众数、中位数、算术平均数、调和平均数、几何平均数、四分位差、标准差、方差、变异系数等。
集中趋势测度：算术平均值、几何平均数、调和平均数、中位数、众数。
离散程度测度：极差（全距）、标准差、方差、四分位差、变异系数。
数据分布测度：偏度、峰度。
数值统计：最小值、最大值、总和、总个数。
Excel 中用于计算描述统计量的方法有两种：函数方法和描述统计工具的方法。本章将首先介绍列联表的使用以及数据的频数分析，然后详细介绍如何使用Excel 2007中给出的统计函数来求解各种统计量和使用描述统计工具来实现对统计数据的描述性统计。1.1 列联表
列联表是观测数据按两个或更多属性（定性变量）进行交叉分类时所列出的频数表。列联表分析常用来判断同一个调查对象的两个特性之间是否存在明显相关性。例如，房地产商常常设计相关列联表问卷，调查顾客的职业类型和顾客所选房子的户型是否有明显的相关性。同样，列联表分析也可以在Excel 2007 中实现。
一个实际频数f的期望频数e，是总频数的个数n乘以该实际频数ijijf落入第i行和第j列的概率，即ij2
x统计量的计算公式为2
其自由度为（r−1）（c−1）。x独立性检验可以检验列联表中行变量与列变量之间的相关性。成根据显著性水平α和自由度（r−1）（c−1）查出临界值，若，则行变量与列变量之间是相关的；若，则行变量与列变量之间独立。2
Excel提供函数CHITEST计算x统计量的概率，提供函数CHIINV计算临界值。
函数语法：CHITEST(actual_range,expected_range)
CHITEST函数语法具有以下参数。
● actual_range为包含观察值的数据区域，将对期望值作检验。
● expected_range为包含行列汇总的乘积与总计值之比率的数据区域。
函数语法：CHIINV(probability,degrees_freedom)
CHIINV函数语法具有以下参数。2
● probability为与x分布相关的概率。
● degrees_freedom为自由度的数值。
例1.1 顾客所在地区和所选房子地板类型之间的相关性分析
下面用一个具体例子说明列联表相关性分析。表 1.1 是某装修公司的调查报告数据表，用列联表分析方法分析顾客所在地区与所选房子地板类型之间是否存在明显的相关性。表1.1 某装修公司的调查报告数据表
新建工作表“例1.1装修公司的调查报告数据.xlsx”，输入表1.1中的调查报告数据，如图1.1所示。图1.1 装修公司的调查报告数据
下面使用Excel 2007 进行相关性分析，具体操作步骤如下。
Step 01：打开“例1.1 装修公司的调查报告数据.xlsx”，如图1.2 所示，先在A8:F12 单元格范围建立期望频数表的框架。图1.2 装修公司的调查报告数据图
Step 02：单击B9 单元格，在编辑栏中输入公式“=B$6*$F3/$F$6”，然后按回车键结束；再单击B9单元格，将鼠标指针移动至B9单元格右下角，当鼠标指针变为小黑色十字形状时按下鼠标左键拖曳至E11单元格，求出B9:E11各单元格值。
Step 03：利用 Excel 的求和函数 SUM 计算行总数。单击 F9 单元格，在编辑栏中输入“=SUM(B9:E9)”，按回车键；再单击F9单元格，将鼠标指针移动至F9单元格右下角，当鼠标指针变为黑色十字形状时，按下鼠标左键拖曳至F12单元格，利用自动填充功能求出各行总数。
Step 04：计算列总数。单击B12，在编辑栏中输入“=SUM(B9:B12)”，按回车键；然后单击B12单元格，将鼠标指针移动至B12单元格右下角，当鼠标指针变为黑色十字形状时，按下鼠标左键并拖曳至F12单元格，求出各列总数，从而建立期望频数表，如图1.3所示。图1.3 期望频数
Step 05：在A14 单元格输入标志项“卡方概率值”，先点击B14 单元格，单击菜单栏【公式】/【插入函数】命令，弹出【插入函数】对话框，在【或选择类别】一项选择【统计】；在【选择函数】中选择【CHITEST】函数，如图1.4所示。图1.4 插入函数对话框
Step 06：单击【插入函数】对话框【确定】按扭，弹出【函数参数】对话框；单击【Actual_range】后的折叠按钮，选择B3:E5单元格区域；单击打开折叠按钮，返回【函数参数】对话框；单击【Expected_range】后的折叠按钮，选择B9:E11单元格区域；单击打开折叠按钮，返回【函数参数】对话框，如图 1.5 所示。最后单击【确定】按扭，即可得到卡方概率值1.30821E-07，如图1.6所示。图1.5 函数参数对话框图1.6 卡方概率值计算结果2
Step 07：求x统计量。在A15单元格输入标志项“卡方统计量”，单击B15 单元格，在编辑栏中输入公式“=SUM((B3:E5-B9:E11)^2/B9:E11)”，完成后按Ctrl+Shift+Enter组合键结束，结果如图1.7所示。图1.7 卡方统计量
Step 08：进行假设检验。在A17单元格输入标志项“置信水平”，在B17 单元格输入0.01；在 A18 单元格输入标志项“临界值”，单击 B18 单元格，在编辑栏中输入公式“=CHIINV(B17,6)”，按回车键；在A19单元格输入标志项“检验结果”，单击B19单元格，在编辑栏中输入公式“=IF(B15>B18,"拒绝两种属性不相关的假设","接受两种属性不相关的假设")”，按回车键。结果如图1.8所示。【注意】CHIINV函数的自由度=(第一类属性的分类数−1)×(第二类属性的分类数−1)，即(r−1)(c−1)=(3−1)×(4−1)=6。图1.8 列联表分析结果【结论】
以上的操作步骤即完成对整个列联表的分析，从图 1.8 所示中可以看出，B14 单元格的卡方概率值与 B15 单元格的卡方统计量是表格的两个重要计算结果。其中卡方概率值等于1.30821E−07，表明如果总体的两类属性，即所在地区和所选地板类型，是不相关的，那么得到以上观察的样本的概率是 0.000000130821，这个概率几乎等于 0，所以可以认为总体的这两个属性是显著相关的。1.2 数据频数分析
频数也称“次数”，是对总数据按某种标准进行分组，统计出各个组内含个体的个数。通过统计调查得到的数据往往是杂乱，没有规则的，因此，必须对得到的大量原始数据进行加工整理，经过数据分析得出科学结论。对于一组数据，考察不同数值出现的频数，或者是数据落入指定区域内的频数，可以了解数据的分布状况。在Excel 2007 中，数据频数分析主要通过频数分布函数与直方图分析工具等来进行。1.2.1 频数分布函数
例1.2 居民购买消费品支出频数分析
某县城统计部门抽样调查50户居民购买消费品支出，支出资料如图1.9所示（单位：元）。图1.9 居民购买消费品月支出
对其按 800～900、900～1000、1000～1100、1100～1200、1200～1300、1300～1400、1400～1500、1500～1600、1600以上分为9个组进行频数分析。
Excel 提供了一个专门用于统计分组的频数分布函数 FREQUENCY，它以一列垂直数组返回某个区域中的数据分布，描述数据分布状态。
函数语法：FREQUENCY(data_array,bins_array)
FREQUENCY函数语法具有以下参数。
● data_array 是一个数组或对一组数值的引用，要为数组或数值计算频率。如果data_array中不包含任何数值，函数FREQUENCY将返回一个零数组。
● bins_array是一个区间数组或对区间的引用，该区间用于对data_array中的数值进行分组。如果bins_array中不包含任何数值，函数FREQUENCY返回的值与data_array中的元素个数相等。【注意】在选择了用于显示返回分布结果的相邻单元格区域后，函数FREQUENCY应以数组公式的形式输入。返回数组中的元素个数比bins_array中的元素个数多1个。多出来的元素表示最高区间之上的数值个数。函数FREQUENCY将忽略空白单元格和文本。
在使用此函数时，先将样本数据排成一列。新建工作表“例 1.2 居民购买消费品支出资料.xlsx”。将图1.9所示的数据排成一列，本例中为A1:A50。
利用频数分布函数进行统计分组和计算频数，具体操作步骤如下。
Step 01：打开工作表“居民购买消费品支出资料.xlsx”。选定单元格区域，本例中选定的区域为 D1:D9，单击菜单栏【公式】/【函数库】/【插入函数】命令，弹出【插入函数】对话框，如图1.10所示。
Step 02：在【选择类别】中选择【统计】，在【选择函数】中选择【FREQUENCY】，如图1.11所示。图1.10 插入函数对话框图1.11 选择“FREQUENCY”对话框
Step 03：单击图 1.11 中【确定】按钮，弹出 FREQUENCY【函数参数】对话框；单击【Data_array】后的折叠按钮，选择 A1:A50 单元格区域；单击打开折叠按钮，返回【函数参数】对话框；在【Bins_array】栏中填写“{899;999;1099;1199;1299;1399;1499;1599;1699}”，如图1.12所示。图1.12 FREQUENCY【函数参数】对话框【注意】data_array用于计算频率的数组，或对数组单元区域的引用。本例中为A1:A50。bins_array是数据接受区间，为一组数或对数组区间的引用，设定对data_array进行频率计算的分段点。本例中为899、999、1099、1199、1299、1399、1499、1599、1699。频数分布函数要求按组距的上限分组，不接受非数值的分组标志（如“不足××”或“××以上”等）。在输入的数据两端必须加大括号，各数据之间用分号隔开。输入完成后，由于频数分布是数组操作，所以不能单击【确定】按钮。
Step 04：按“Ctrl+Shift+Enter”组合键，在最初选定单元格区域D1:D9 内得到频数分布结果，如图1.13所示。至此，频数分布函数进行统计分析的功能就全部操作完成了。图1.13 频数分布结果【结论】
通过以上分析步骤可看出，使用 Excel 2007 中提供的 FREQUENCY 函数，可以很方便地求出频数分布。从图1.13所示的结果中，可以看到居民购买消费品的花销在不同分段点间的分布状况。1.2.2 直方图分析工具
直方图是将所收集的测定值、特性值或结果值，分为几个相等的区间作为横轴，并将各区间内所测定值依据所出现的次数累积而成的面积，用“柱子”排起来的图形。因此，也叫做柱形图。
频数分布函数只能进行统计分组和频数计算，直方图分析工具可完成数据的分组、频数分布与累积频数的计算、绘制直方图与累积折线图等一系列操作。
例1.3 利用直方图分析工具进行统计分析
这里仍以例 1.2 的数据为操作范例，阐述直方图分析工具的统计整理功能，新建工作表“例1.3居民购买消费品支出数据.xlsx”。
利用直方图分析工具进行分析，具体操作步骤如下。
Step 01：在G1 单元格输入分组标志，在G2:G10 单元格区域输入899、999、1099、1199、1299、1399、1499、1599、1699，如图1.14所示。图1.14 居民购买消费品支出数据
Step 02：单击Office 按钮，然后单击【Excel选项】。弹出【Excel选项】对话框，单击【加载项】，如图1.15所示。在【管理】框中，单击【Excel加载项】，然后单击【转到】，弹出【加载宏】对话框；单击选中【分析工具库】和【分析工具库-VBA】复选框，如图1.16所示，完成后单击【确定】按钮。图1.15 Excel 选项对话框
试读结束[说明：试读内容隐藏了图片]