Excel第五章描述统计.docx

Excel第五章描述统计.docx

《Excel第五章描述统计.docx》由会员分享，可在线阅读，更多相关《Excel第五章描述统计.docx（28页珍藏版）》请在冰豆网上搜索。

Excel第五章描述统计

第五章描述统计

描述统计（descriptivestatistics）是指用统计指标、统计表、统计图等方法，

对资料的数量特征或分布规律进行测量或描述。

本章主要讲述如何应用Excel进行描述统计的方法与技巧，侧重于对资料的分布规律的描述和统计指标的计算。

关于如何用统计表或统计图进行描述统计，第三、四章已做介绍，这里不再累述。

第一节频数分布表和直方图

频数分布表（frequencydistributiontable）是指对于数据量比较大的资料，通过设置带有特定数值间隔的区域，并统计所有观测值在每个间隔内出现的次数（即频数）的一种表格。

通过频数分布表可以揭示资料的分布规律，主要目的在于根据其分布特点来选择合适的统计分析方法。

直方图（histogram）是频数分布表的直观表达形式，通过把频数的大小用长方形直条的高低或长短来显现频数的分布规律。

一、频数分布表的制作

利用Excel的“工具”菜单中的“数据分析…”命令里的“直方图”分析工具制作频数分布表。

（一）方法

1．在工作表内的某行或某列（常取列）输入资料的原始数据。

每个单元格

只存放一个数据；所有的数据存放在一列或一行中；可以通过键盘输入数据，也可以利用Excel的数据转换功能从外部数据文件（如dBase文件）中快捷生成。

关于Excel的数据转换功能参见有关章节。

2．利用“直方图”分析工具制作频数分布表。

单击“工具”菜单→“数据分析…”命令→“数据分析”对话框，在“分析工具”中→单击“直方图”

→“直方图”对话框，在“输入区域”中输入原始数据所在区域的单元格引用；“接收区域”可以不添，也可以根据数据的变异范围自己预先定义一个数值间隔区域，此处则输入该区域的单元格引用；对于“标志”复选框，如果输入区域的第一行或第一列中包含标志项，请选中此复选框；如果输入区域没有标志项，请清除此该复选框，Excel将在输出表中生成适宜的数据标志；对于“输出选项”，可以单击“输出区域”单选框，并在编辑框中输入对输出表左上角单元格的引用。

如果输出表将覆盖已有的数据，Excel会自动确定输出区域的大小并显示信息。

也可以单击“新工作表组”单选框，可在当前工作簿中插入新工作表，并由新工作表的A1单元格开始粘贴计算结果。

如果需要给新工作表命名，请在右侧的编辑框中键入名称。

也可以单击“新工作簿”单选框，可创建一新工作簿，并在新工作簿的新工作表中粘贴计算结果。

选中“柏拉图”复选框，可以在输出表中同时按降序排列频率数据。

如果此复选框被清除，Excel将只按升序来排列数据，即省略输出表中最右边的三列数据。

选中“累积百分率”复选框，可以在输出表中添加一列累积百分比数值，并同时在直方图表中添加累积百分比折线。

如果清除此选项，则会省略累积百分比。

选中“图表输出”复选框，可以在输出表中同时生成一个嵌入式直方图表。

如果只输出频数分布表，则上述三个复选框都可不必选。

如果要生成直方图，则必须选中“图表输出”复选框。

（二）操作步骤

下面以例5.1为例具体演示其操作步骤。

例5.11985年测得南方某城市120名12~15月龄女童头围（cm）数据如

下，请制作频数分布表。

47.244.845.443.547.844.145.644.844.144.9

43.944.645.843.943.943.945.344.543.145.5

46.646.844.447.043.543.746.145.444.846.5

44.144.144.544.846.243.346.444.546.344.7

43.141.944.445.845.444.346.043.746.643.0

45.444.843.345.143.844.843.845.945.047.4

44.447.043.644.545.143.845.542.645.946.0

45.445.343.544.245.645.846.743.643.845.9

46.743.644.144.745.946.144.444.846.343.6

44.846.844.143.244.343.741.643.844.746.6

45.245.844.845.242.944.445.343.642.245.0

46.246.046.044.546.546.343.744.542.443.9

1．在工作表的A列输入原始数据。

在A2输入“头围（cm）”，作为此组数据的标志；A3输入“47.2”，A4输

入“43.9”，A5输入“46.6”，…，A120输入“46.6”，A121输入“45.0”，A122

输入“43.9”。

见图5-1。

图5-1原始数据的输入（部分）

2．利用“直方图”分析工具制作频数分布表。

⑴单击“工具”菜单→单击“数据分析…”命令（见图5-2）→“数据分

析”对话框。

见图5-3。

图5-2Excel的“工具”菜单图5-3Excel的“数据分析”对话框

注意：

如果打开“工具”菜单，没有如图5-2中所示的“数据分析…”命

令，可先单击此菜单中的“加载宏…”命令→“加载宏”对话框，在“当前加载宏”中→选中“分析工具库”和“分析工具库–VBA函数”两项复选框，见图5-4。

单击[确定]按钮，再打开“工具”菜单，就会出现如图5-2中所示的“数据分析…”命令。

⑵在“分析工具”中→单击“直方图”→单击[确定]按钮（见图5-3）→

“直方图”对话框。

见图5-5。

图5-4Excel的“加载宏”对话框

图5-5Excel的“直方图”对话框

⑶在“输入区域”的编辑框中输入“a2:

a122”，“接收区域”可以不添，选中“标志”复选框，单击“输出区域”单选框，并在编辑框中输入“b2”，见图5-6。

单击[确定]按钮则在当前工作表中生成一个由Excel定义的一组平滑分布

的接收区间的频数分布表。

见图5-7。

图5-6“直方图”对话框中的参数输入及选项

图5-7最后生成的频数分布表

上述的操作步骤是在“接收区域”不添的情况下生成的频数分布表，其特点是接收区域由Excel自动生成。

如果想让Excel按你指定的接收区域生成频数分布表，就需要在调用“直方图”分析工具之前先定义一个接收区域，然后在“直方图”对话框里的“接收区域”的编辑框中输入该区域的单元格引用，其它步骤同前，就可生成一个按你指定的接收区域生成的频数分布表。

对于例5.1，可以先在D2:

D14的区域定义如图5-8所示的接收区域，包括标志值“头围（cm）”，然后在“直方图”对话框里的“接收区域”的编辑框中输入此区域的单元格引用“d2:

d14”，在“输出区域”单选框的编辑框中输入“e2”，见图5-9。

最后生成的频数分布表见图5-10。

图5-8用户定义的接收区域

图5-9用户定义的接收区域的单元格引用

图5-10按用户定义的接收区域生成的频数分布表

用户可以通过比较两种不同方法生成的频数分布表的异同（见图5-10），从而根据自身的需要来选择合适的方法。

二、直方图的绘制

利用Excel的“工具”菜单中的“数据分析…”命令里的“直方图”分析工具绘制直方图。

（一）方法

与前述频数分布表的制作方法几乎完全相同，唯一不同的地方是：

在“直

方图”的对话框中必须选中“图表输出”复选框，才可生成一个嵌入式直方图。

（二）操作步骤

下面以例5.2为例具体演示其操作步骤。

例5.2利用例5.1的数据绘制直方图。

操作步骤与频数分布表的制作步骤的1.、2.⑴、2.⑵相同；

⑶在“输入区域”的编辑框中输入“a2:

a122”，“接收区域”可以不添，选中“标志”复选框，单击“输出区域”单选框，并在编辑框中输入“b2”，选中“图表输出”复选框，见图5-11。

单击[确定]按钮则在当前工作表中生成一个由Excel定义的一组平滑分布的接收区间的频数分布表的同时，在其左侧又生成了一个嵌入式直方图。

见图5-12。

图5-11选中“图表输出”复选框的“直方图”对话框

图5-12Excel生成的带有嵌入式直方图的频数分布表

一般情况下，由Excel初始生成的嵌入式图表的大小比例都失调（见图5-12），需要用户自己调整图表的大小比例。

方法为：

单击图表（移动鼠标指针，当出现“图表区域”时单击即可选中整个图表），然后拖动图表的选定柄（见图5-13）来调整图表的大小到合适的比例。

对于上例生成的直方图，可以拖动图表下边界的中间选定柄的指针（见图5-13）到第“18”行边界，经此调整后的直方图见图5-14。

图5-13Excel工作表中的嵌入式图表被选中时的界面

图5-14对例5.2的嵌入式直方图大小调整后的样式

如果用户想得到一个更加符合专业要求的图表式样，需利用图表的各种编

辑功能调整图表的某些组成部分（如绘图区、坐标轴等等），关于此方面的介绍请参见本书第四章相关内容，也可参见Excel的图表编辑功能的联机帮助信息或相关书籍。

对于例5.2中直方图进行调整，使之更符合统计专业要求的式样

图5-15对例5.2的嵌入式直方图全面调整后的统计专业样式

见图5-15。

第二节平均数和百分位数的计算

广义的平均数是一个指标体系，常用的包括算术均数（简称均数）、几何均

数、中位数，都是用来描述数值资料的平均水平或集中位置，不同的资料类型选用不同的指标。

百分位数是一个位置指标，以Px表示，是一组排序后的数据的百等份分割值，最常用的P50即为中位数，多个百分位数结合应用时，可描述资料的分布规律。

有关的知识请参见统计学的相关书籍。

在Excel中，可以通过调用统计函数来计算上述所有指标，也可通过调用统计分析模块里的分析工具计算上述绝大部分指标，还可以利用Excel状态行的快速计算功能计算上述部分指标。

一、均数

㈠调用统计函数AVERAGE（）计算均数

对于工作表中已存在的数据，可在非数据区的任意单元格内输入统计函数AVERAGE（）的引用来计算均数；对于现场数据，首先在工作表的某列或某行输入全部原始数据，然后再在非数据区的任意单元格内输入统计函数AVERAGE（）的引用来计算均数；对于有些由其它软件生成的数据形式（如dBase数据库等），可先将其转换成Excel文件，再利用统计函数AVERAGE（）的引用来计算均数。

统计函数AVERAGE（）的语法为：

★AVERAGE（number1,number2,…）

Number1,number2,…要计算均值的1～30个参数。

其中参数可以是数字，或者是涉及数字的名称、数组或引用。

如果数组或单元格引用参数中有文字、逻辑值或空单元格，则忽略其值。

但是，如果单元格包含零值则计算在内。

。

实际应用中，常只用一个number参数，并且此参数为要计算的数据区域的引用。

下面以例5.3为例具体演示其操作步骤。

例5.310名7岁男童的体重（kg）分别为：

18.6，21.5，24.8，18.4，22.2，

23.7，19.5，20.4，21.8，26.1。

求体重的均数。

1．在工作表的A列输入原始数据。

在A2输入“体重（kg）”，作为此组数据的标志；A3输入“18.6”，A4输

入“21.5”，A5输入“24.8”，…，A10输入“20.4”，A11输入“21.8”，A12

输入“26.1”。

见图5-16。

图5-16原始数据的输入

2．在工作表的非数据区的任意单元格内输入统计函数AVERAGE（）的引用

来计算均数。

在A13输入“=average（a3:

a12