EXCEL运用.DOC

Excel统计分析功能概述
第1章

第1章 Excel统计分析功能概述
1.1 Excel 2003新功能
1．列表功能
在 Microsoft Office Excel 2003 中，可以在工作表中创建列表以将相关数据分组并对其进行操作。Excel 2003既可以对现有数据创建列表，也可从空白区域创建列表。在指定一个范围作为列表后，可以很容易地管理和分析独立于该列表外部其他数据的数据。此外，可以通过与 Microsoft Windows SharePoint Services 集成来与其他人共享包含在列表内的 信息。
默认情况下，列表中每一列都已在标题行中启用了“自动筛选”，该功能可以快速筛选或排序数据。与Microsoft Windows SharePoint Services集成Excel列表可以通过与 Microsoft Windows SharePoint Services的无缝集成来对包含在列表内的信息进行协作。可以通过发布列表来在 SharePoint网站上基于Excel列表创建SharePoint列表。如果选择将列表链接到SharePoint网站，则在同步该列表时，在Excel中对列表所做的任何更改都将反映在SharePoint网站上。还可以使用Excel编辑现有的Microsoft Windows SharePoint Services 列表。
2．改进的统计函数
在Excel 2003中，下列统计函数（包括四舍五入结果）的外观以及精确性都已增强：
BINOMDIST、CHIINV、CONFIDENCE、CRITBINOM、DSTDEV、DSTDEVP、DVAR、DVARP、FINV、FORECAST、GAMMAINV、GROWTH、HYPGEOMDIST、INTERCEPT、LINEST、LOGEST、LOGINV、LOGNORMDIST、NEGBINOMDIST、NORMDIST、NORMINV、NORMSDIST、NORMSINV、PEARSON、POISSON、RAND、RSQ、SLOPE、STDEV、STDEVA、STDEVP、STDEVPA、STEYX、TINV、TREND、VAR、VARA、VARP、VARPA、ZTEST
上述函数的计算结果可能与Excel以前版本中的结果有所不同。
3．XML支持
Excel 2003中行业标准的XML支持简化在个人计算机和后端系统之间访问和捕获信息、解除信息锁定以及允许跨企业和在业务合作伙伴之间创建集成的企业解决方案的过程。
使用Excel中的XML支持，数据可以使用以企业为中心的XML语汇展示给外部进程。
通过XML可采用以前无法实现或很难实现的方式组织并处理工作簿和数据。通过使用XML架构，现在可以从普通的商业文档中识别和提取特定的业务数据片段。可以将自定义XML架构附加到任何工作簿。然后，使用“XML源”任务窗口将单元格映射到该架构的元素。一旦将XML元素映射到工作表，便可以无缝地将XML数据导入映射的单元格，并可从中导出。
4．智能文档
智能文档是可以对其进行编程以通过动态地响应操作的上下文来扩展工作簿功能的 文档。
有多种类型的工作簿（如窗体和模板）作为智能文档进行工作效果很好，智能文档对于作为进程的一部分的工作簿效果尤其好。例如，有些公司可能具有用于填充雇员年度支出表的进程，并且可能已经使用 Microsoft Office Excel 2003 模板来完成此任务。如果该模板为智能文档，就可以连接到自动填写一些必需信息（如用户姓名、雇员编号、经理姓名等）的数据库。在完成填写费用报表后，智能文档可以显示一个按钮，单击该按钮可以将智能文档发送到进程中的下一步骤。由于智能文档知道公司经理是谁，因此可以自动发送给此人。而且，不论谁使用智能文档，智能文档都知道自己在费用审阅进程中的位置以及下一步需要执行什么操作。
智能文档可以重复使用现有内容。例如，会计师在创建记账声明时可以使用现有的样板文件。
智能文档可使共享信息变得更为轻松。智能文档可与各种数据库进行交互并使用 BizTalk 跟踪工作流。智能文档甚至可以与其他 Microsoft Office 应用程序进行交互。例如，用户可以使用智能文档通过 Microsoft Outlook 发送电子邮件。所有这一切都可直接在Excel中完成。
5．文档工作区
使用“文档工作区”可简化通过Microsoft Office Excel 2003与其他人实时地共同创作、编辑和审阅文档的过程。文档工作区网站是 Microsoft Windows SharePoint Services 网站，可集中一个或多个文档。不管是通过直接处理文档工作区副本，还是通过处理自己的副本，人们都可以很容易地协同处理文档，可以使用已保存到文档工作区网站上副本的更改，定期更新自己的副本。
通常，使用电子邮件将文档作为共享附件发送时，会创建文档工作区。共享附件的发件人将成为该文档工作区的管理员，而所有的收件人都会成为文档工作区的成员，他们会被授予参与该网站相关讨论的权限。另一个创建“文档工作区”的常见方法是使用 Microsoft Office 2003 程序中“工具”菜单的“共享工作区”任务窗口。
在使用 Word、Excel、PowerPoint 或 Visio 打开“文档工作区”所基于文档的本地副本后，该 Office 程序将定期从“文档工作区”获取更新，并使其可供用户使用。如果对工作区副本的更改与用户对自己副本所做的更改有冲突，可以选择保留哪个副本。当完成编辑的副本时，可将更改保存到文档工作区，在那里，其他成员可获取这些更改并将其合并到他们的文档副本中。
6．信息权限管理
现在，敏感信息仅可以通过限制对存储信息的网络或计算机的访问来进行控制。但是，一旦赋予了用户访问权限，就会对如何处理内容或将内容发送给谁没有任何限制。这种内容分发很容易使敏感信息扩散到从未打算让其接收该信息的人员。Microsoft Office 2003 提供了一种称为“信息权限管理”（IRM）的新功能，可以帮助防止敏感信息扩散到错误的人员手中。
作者使用“权限”对话框（“文件”|“权限”|“不能分发”或“常用”工具栏上的“权限”）来赋予用户“读取”和“更改”访问权限，以及设置内容的到期日期。只需单击“权限”子菜单上的“无限制的访问”或再次单击“常用”工具栏上的“权限”，作者还可以从文档、工作簿或演示文稿中删除受限权限。
另外，公司的管理员可以在“权限”子菜单上创建在 Microsoft Office Word 2003、Microsoft Office Excel 2003 和 Microsoft Office PowerPoint 2003 中可用的权限策略，并定义谁可以访问信息及用户对文档、工作簿或演示文稿具有什么级别的编辑和管理功能。
接收具有受限权限内容的用户只需像处理不具有受限权限的内容一样来打开文档、工作簿或演示文稿。如果用户的计算机上没有安装 Office 2003 或更高版本，则可下载允许其查看此内容的程序。
7．并排比较工作簿
使用同一个工作簿查看多个用户所做的更改可能有些困难，但现在出现了一种比较工作簿的新方法，即并排比较工作簿。使用“窗口”菜单中的“并排比较”命令可以更方便地查看两个工作簿之间的差异，而无须将所有更改都合并到一个工作簿中，且可以同时滚动浏览两个工作簿以辨别这两个工作簿之间的差异。
8．Office 中的其他新增功能
（1）Office 的新外观
Microsoft Office 2003 具有开放而充满活力的新外观。此外，还为用户提供了新的和经过改进的任务窗口。新任务窗口包括“开始工作”、“帮助”、“搜索结果”、“共享工作区”、“文档更新”和“信息检索”。
（2）支持手写设备
在Tablet PC 上，可像使用笔和纸一样，使用手写直接向 Office 文档中快速输入。此外，现在还可以水平地显示任务窗口，以帮助用户在 Tablet PC 上按所希望的方式进行 工作。
（3）“信息检索”任务窗口
在Internet 连接状态下，新的“信息检索”任务窗口提供了各种各样的参考资料和丰富的信息资源，可以使用百科全书、Web 搜索或通过访问第三方内容来对各主题执行信息检索。
（4）Microsoft Office Online
在工作时Microsoft Office Online可以充分利用该网站内容提供帮助。可直接从 Web 浏览器内访问 Microsoft Office Online，也可使用 Office 程序内各种任务窗口和菜单中提供的链接访问文章、提示、剪贴画、模板、联机培训、下载和服务，从而提高使用 Office 程序的效率。
1.2 统计分析功能简介
1.2.1 统计方法概述
概括而言，所谓的统计方法是指用以收集数据、分析数据和由数据得出结论的一系列方法。统计方法通常可分为两类：描述统计方法和推断统计方法。
1．描述统计方法
描述统计方法是指通过图表的方式对数据进行处理显示，进而对数据进行定量的综合概括的统计方法。
例如表1.1列示了10家公司首席执行官（CEO）的酬金。描述统计方法可以对表1.1的数据进行处理，给出如表1.1和图1-2-1所示的综合信息。表示为图表后的数据更加容易理解。从图1-2-1可以很容易地看出大多数公司CEO的酬金在1 000~3 500千美元之间。图1-2-1中的数据表明，CEO的酬金在1 000千美元以下的公司占10%，CEO的酬金在1 000~3 500千美元之间的公司占80%，CEO的酬金在3 500千美元以上的占10%。
表 1.1
公 司 代 码
1
2
3
4
5
6
7
8
9
10
酬金/千美元
8 925
2 437
1 410
696
1 847
1 490
3 414
3 344
1 490
2 861

除了用图表方法对数据进行分析处理外，还可以应用描述统计方法进行数据汇总计算。如可以计算出10家公司CEO的平均酬金为2 801.4千美元，中位数为2 142千美元。这些数据一方面说明CEO酬金的平均水平是2 801.4千美元，但是中位数又说明有一半公司CEO的酬金低于2 142千美元，有一半公司CEO的酬金高于2 142千美元，另一方面也说明CEO酬金之间差异是比较大的。

图 1-2-1
2．推断统计方法
推断统计方法是指根据样本数据去推断总体数量测度的方法。
例如，假设某公司为了了解所生产的某种易耗产品的使用寿命，从生产的某批产品中随机抽取50件产品进行测试，其结果（小时数）如下：
62，65，68，71，80，88，102，86，79，73，77，63，88，81，74，71，62，90
79，83，93，62，92，58，84，77，70，78，66，63，75，71，59，71，105，89
86，66，68，77，60，87，75，51，89，69，107，93，84，80
利用Excel统计分析工具，可计算出50件产品的平均使用寿命为76.94小时，抽样极限误差为3.48。根据统计推断方法，由此可以推断出整批产品的使用寿命在73.46~80.42小时之间，而且此结论的可靠程度为95%。
本书将主要介绍最基本的统计方法以及相应的Excel应用，具体内容如图1-2-2所示。

图 1-2-2
1.2.2 Excel统计分析功能
在当今众多的电子表格软件中，微软公司的Excel以其强大的功能、先进的技术、良好的可操作性和简单易用，赢得了全世界的一一致认可。
英文Excel的中文含义是“胜过，优于”。Excel自1985年问世，就因其独特的功能特性被公认为功能最完整、技术最先进和使用最简单的电子表格软件。十几年来，随着计算机领域新技术和新思维的不断出新和计算机硬件技术的迅猛发展，微软公司对其王牌产品Excel倾注了极大的热情，并投入了巨大的开发力量，几乎每隔几年就对Excel进行改进升级，每一次升级都融入了许多独具匠心的新技术和新方法，使得Excel功能更加完善，用户更加得心应手。
Excel是一个快速制表、将数据图表化以及进行数据分析和管理的工具软件包。Excel可以管理、组织纷繁复杂的数据，并对数据进行分析处理，最后以图表、统计图形的形式给出分析结果。尤其重要的是，Excel 2003提供了超强的统计分析程序，范围涵盖了最基本的统计分析。
Excel 2003以分析工具库和统计函数的形式来提供统计分析功能。
下面分别就Excel 2003中的分析工具和统计函数的统计分析功能加以简要介绍。更详细的操作介绍可参见本书附录。
1．分析工具的统计分析功能
Excel 软件中提供了15个数据分析工具，称为“分析工具库”。在进行统计分析时使用分析工具可节省步骤。只需为每一个分析工具提供必要的数据和参数，分析工具就会使用适宜的统计函数，在输出表格中显示相应的结果。其中，有些工具在生成输出表格时还能同时生成图表。
（1）统计绘图、制表
利用Excel分析工具库中的“直方图”分析工具，可以进行频数分布处理和绘制直 方图。
（2）描述统计量计算
利用Excel分析工具库中的“描述统计”分析工具，可以计算常用的集中趋势测度、离散程度测度、数据分布测度及其他基本统计量。
l 集中趋势测度：平均值、中位数、众数。
l 离散程度测度：极差（全距）、标准误差（相对于平均值）、标准偏差、方差。
l 数据分布测度：峰值、偏斜度。
l 数值统计：最小值、最大值、总和、总个数。
利用“排位与百分比排位”分析工具，可以产生一个数据列表，在其中罗列给定数据各个数值的大小次序排位和相应的百分比排位，用来分析数据中各数值间的相互位置关系。
（3）参数估计
利用“描述统计”分析工具，可以计算正态分布下方差未知的样本均值极限误差，从而实现单一总体均值的区间估计。
（4）假设检验
利用F-检验分析工具、t-检验分析工具、z-检验分析工具，可以进行总体均值、方差的假设检验。其中：
① 两个总体均值检验：
l 利用“z-检验：双样本平均差检验”分析工具，可以在两总体方差已知时，进行两总体均值的假设检验。

l 利用“t-检验：双样本等方差假设”分析工具，可以在两正态总体方差未知但相等时，进行两总体均值的假设检验。
l 利用“t-检验：双样本异方差假设”分析工具，可以在两正态总体方差未知且不相等时，进行两总体均值的假设检验。
l 利用“t-检验：成对双样本均值分析”分析工具，可以进行均值的成对检验。
② 两个总体方差检验：
利用“F-检验：双样本方差分析”分析工具，可进行两个总体的方差检验。
（5）方差分析
利用方差分析工具，可进行单因素和双因素的方差分析。
① 单因素方差分析：
利用“单因素方差分析”分析工具，可以对两个以上总体均值的显著性差异进行检验。
② 双因素方差分析：
l 利用“无重复双因素分析”分析工具，可以对两个因素各自对实验结果影响的显著性进行检验。
l 利用“可重复双因素分析”分析工具，可以对两个因素各自对实验结果及两因素交互作用对实验结果影响的显著性进行检验。
（6）相关、回归分析
利用“相关系数”分析工具和“协方差”分析工具，可以对两个及两个以上变量间的相关关系进行分析计算。
利用“回归分析”分析工具，可以建立简单线性回归和多元线性回归模型，并可对模型的有效性进行检验分析。
（7）时间序列分析
利用“指数平滑”分析工具，可对时间序列基于前期预测值导出相应的新预测值，进行趋势分析。
利用“移动平均”分析工具，可对时间序列数据进行移动平均处理，进行数据的趋势分析。
（8）抽样
利用“随机数发生器”分析工具，可以按照用户选定的分布类型，在工作表的特定区域中生成一系列独立随机数。
利用“抽样分析”分析工具，可以以输入区域为总体构造总体的一个样本。当总体太大而不能进行处理或绘制时，可以选用具有代表性的样本。如果确认输入区域中的数据是周期性的，还可以对一个周期中特定时间段中的数值进行采样。例如，如果输入区域包含季度销售量数据，以4为周期进行。
（9）数据变换
利用“傅里叶分析”分析工具，可以对数据进行快速傅里叶变换（FFT）和逆变换，变换后的数据用于相关系数检验和分析。
2．统计函数的统计分析功能
Excel中提供了78个统计函数用于统计分析。这些统计函数的统计分析功能包括：
（1）频数分布处理
频数分布处理：FREQUENCY
（2）描述统计量计算
① 集中趋势计算
算术平均数：AVERAGE、AVERAGEA
几何平均数：GEOMEAN
调和平均数：HARMEAN
中位数：MEDIAN
众数：MODE
四分位数：QUARTILE
K百分比数值点：PERCENTILE
内部平均值：TRIMMEAN
② 离散程度计算
平均差：AVEDEV
样本标准差：STDEVA、STDEV
总体的标准偏差：STDEVP、STDEVPA
样本方差：VAR、VARA
总体方差：VARP、VARPA
样本偏差平方和：DEVSQ
③ 数据分布形状测度计算
偏斜度：SKEW
峰度：KURT
标准化值z：STANDARDIZE
④ 数值计算
计数：COUNT、COUNTA
极值：MAX、MAXA、MIN、MINA、LARGE、SMALL
排序：RANK、PERCENTRANK
（3）概率计算
① 离散分布概率计算
排列：PERMUT
概率之和：PROB
二项分布：BINOMDIST、CRITBINOM、NEGBINOMDIS
超几何分布：HYPGEOMDIST
泊松分布：POISSON
② 连续变量概率计算
正态分布：NORMDIST、NORMINV
标准正态分布： NORMSDIST、NORMSINV
对数正态分布：LOGINV、LOGNORMDIST
卡方分布：CHIDIST、CHIINV
t分布：TDIST、TINV
F分布：FDIST、FINV
β概率分布：BETADIST、BETAINV
指数分布：EXPONDIST
韦伯分布：WEIBULL
Г分布：GAMMADIST、GAMMAINV、 GAMMALN、GAMMALN
（4）参数估计
均值极限误差计算：CONFIDENCE
（5）假设检验
方差假设检验：FTEST
均值假设检验：TTEST、ZTEST
（6）卡方检验
拟合优度和独立性检验：CHITEST
（7）相关、回归分析
相关分析：COVAR、CORREL、PEARSON、FISHER、FISHERINV
线性回归分析：FORECAST、RSQ、LINEST、INTERCEPT、SLOPE、STEYX、TREND
曲线回归：LOGEST、GROWTH

9