EXCEL与数据分析

是根据分析研究的需要，按照一定的分组标准指定多个数
据区域组别，并将数据从小分配到相应的数据区域内。

数据分组方法有单项式分组和组距式分组两种。 单项式分组是将数据中出现的每个数值作为一组，它通常 只适用于数据的变动范围较小，不同数值的个数较少，可
以一一列举的离散型数据。

组距式分组是将变量值的一个范围作为一组，它通常适用 于连续型数据和数据变动范围较大、不同数值的个数较多 的离散型数据。实际中组距式分组应用更多。
60 50
学生人数（人）
40 30 20 10 0 1 2 3 年龄（岁） 4 5
进一步编辑频数分布图，主要是要将横轴的刻度改为 实际的年龄刻度值；
8
（1）右击任一图柱→பைடு நூலகம்击“源数据” ，出现下面的复选框： （2）单击“系列”； （3）在“分类（X）轴标志” 右边的空白框中输入作为X 轴刻度的组界数值所在的单 元格地址（也可直接用鼠标 选中组界数值区域）；
三、创建数据透视图
17
一、创建数据透视表
数据透视表具有交互分析的能力，能全面、灵活地
对数据进行分析、汇总。只需要改变对应的字段位置，
即可得到多种分析结果。 在使用“数据透视表”之前，需要创建数据透视表。 具体步骤如下： 1、单击数据区域任一单元格 2、单击“数据”下拉菜单
→单击“数据透视表和数据透视图”
32
第四节
离散趋势程度的分析
一、离散趋势的测定内容 二、极差 三、标准差和方差 四、利用“数据分析”命令进行描述性分析
33
一、离散趋势的测定内容
测定集中趋势只是数据分析的一个方面，要全面地认 识现象还必须了解现象的离散趋势。因为即使现象的集中 趋势相同，其离散趋势也可能不同，致使其数量特征也不 同，因此，仅仅了解现象的集中趋势是不够的。 离散趋势主要是测定现象总体中各个体之间的数值差 异程度，表现在一个现象总体中各数值与平均数的离差程
11
组距式数组的频数分布表的制作，可通
过两种方法实现：

第一种方法：通过插入频数分布函数
（FREQUENCY)实现

第二种方法：通过“数据分析”命令中的 “直方图”工具实现
12
第一种方法：通过插入频数分布函数
（FREQUENCY)实现
采用插入FREQUENCY函数实现，应首先将各组的 组上限按升序输入工作表， FREQUENCY函数将对应 于各组组限，计算大于上一组组限、小于等于本组组限
并不是真正意义上的“直方图”，而是“柱形图”。直方 图与柱形图的重要区别是：直方图的各个条形之间没有间 隔，而柱形图有。因为直方图是根据数组结果绘制的，而 分组是连续的，因而对应的条形间是挨在一起不能分开的。
为此，必须对图形作进一步的修饰。
16
第二节
利用数据透视表进行分析
一、创建数据透视表
二、编辑数据透视表
度。离散趋势指标的大小可以说明平均数的代表性大小； 可以反映社会经济活动过程的均衡性或稳定性程度；还是 抽样推断和相关分析的重要指标。
测定离散趋势的主要方法有极差、标准差和方差。
34
二、极差
极差也称全距，是一组数据中最大值与最小值的差。 它从数据取值范围的角度描述数据的离散趋势，是测定离 散趋势的最简单的方法。
31
注：这个结果是平均复利或平均发展速度。要计算平均收益 率或平均增长率，就要通过公式计算： =几何平均数的计算结果-1
四、众数和中位数
和定性数据的计算一样，EXCEL中计算众数是通
过插入函数MODE（number1，number2，……）来实
现的；而计算中位数是通过插入函数MEDIAN （number1，number2，……）来实现的。
x x 1 x2 xn x n n
26
求非组数据的算术平均数的方法
1、使用SUM函数计算非组数据的算术平均数 2、使用AVERAGE函数计算非组数据的算术平均数
（二）组数据的算术平均数
在实际应用中，用户可能需要处理组数据。组数据 是指根据某种标准将数据划分为各种区间的数据。设有n
中的对象插入”，并选好工作表名即可。
23
三
集中趋势程度的分析
1、集中趋势的测定内容
2、算术平均数
3、几何平均数 4、众数和中位数
24
一、集中趋势的测定内容
对大量数据整理之后，大多数情况下数据都会呈现
出一种钟形分布，即各个变量值与中间位置的距离越近，
所出现的次数越多；与中间位置的距离越远，出现的次 数越少，从而形成了以中间值为中心的集中趋势。这个 集中趋势是现象共性的特征，是现象规律性的数量表现。 一般来说，在进行数据分析中，利用平均数来描述总体

22
三、创建数据透视图
数据透视图是数据透视表的图形显示效果，为了更直
观地反映数据透视表的汇总效果，可以让两者配合使用。
当用户创建好数据透视表后，右击数据透视表内任一单元 格，单击“数据透视图”按钮，系统可以直接用数据透视 表生成数据透视图，并自动添加一张工作表（以Chart1命 名）来显示数据透视图。 如果想把数据透视图和数据透视表放在一起，以对照 观察事物的特点，可以右击数据透视图的空白处，然后单 击“位置”，再在 “图表位置” 复选框中选择 “作为其
3

例：单项式分组
某班学生年龄分布表
年龄（岁） 18 19 20 21 22 合 计 人数（人） 1 12 22 13 2 50
4

例：组距式分组
某厂工人按日产量分组表
按日产量分组（件） 工人数（人）
130-140 140-150 150-160 160-170 170-180
合 计
3 9 26 8 4
3、单击“下一步”，出现如下的复选框：
18
4、设置数据透视表数据区域； 5、单击“下一步”，出现如下的对话框：
6、选择数据透视表显示位置，然后单击“完成”， 出现如下界面：
19
7、设置具体的透视表数据，按照分析目的将有关的字段 名拖动到相应的标题区域，即完成了数据透视表的创建。
20
21
二、编辑数据透视表
三 定量数据的基本特征分析
(一) 定量数据的频数分析
(二) 利用数据透视表进行分析
(三) 集中趋势程度的分析 (四) 离散趋势程度的分析
1
第一节
定量数据的频数分析
一、定量数据分组
二、单项式数组的频数分析
三、组距式数组的频数分析
2
一、定量数据分组

定量数据的频数分析往往是从数据的分组开始，数据分组
的数据频数，这里利用数组方式计算。
13
第二种方法：通过“数据分析”命令中的“直
方图”工具实现
（1）首先将各组的上限值输入的工作表中； （2）选择“工具”菜单中的“数据分析”命令，出现数 据分析窗口：
（3） 在“分析工具”选项框中选择“直方图”；
14
（4） 单击“确定”，出现“直方图”复选框：
（5） 在“直方图”复选框的“输入区域”框中输入学生 成绩数据所在单元格地址，在“接收区域”框输入分组
29
（4） 选中所有xf 相乘的数据结果单元格，单击工 具栏中的“∑”，即得到∑xf 的数据结果； （5）在其相邻单元格中输入公式“=C5/B5” →单击 “Enter”，即出现工人平均工资的数据计算结果： 2460。 算术平均数是反映现象集中趋势特征的最重要的 指标，是表明同类社会经济现象中各个个体在一定时 间、地点条件下所达到的一般水平，具有较高的代表 性。在实际中用途广泛，常可以用于比较同一时期同 类现象在不同单位、不同地区间的一般水平，也可以 作为评断事物的一种客观标准或参考。
30
三、几何平均数
几何平均数也是反映现象集中趋势的方法。在计算
平均发展速度、平均增长率、平均收益率等指标时，经
常用到几何平均数。尤其是在各种投资项目中，收益率 都是需要计算复利的，用几何平均数能很好地度量其集
中趋势。
几何平均数的计算公式为：
xg
n
x 1x 2 x n
在Excel中可以使用GEOMEAN函数计算几何平均数。
个数据，划分为若干组，每组的组中值为xi ，每组数据
的频数为fi ，其算术平均数的计算公式为：
x x f 1f1 x 2f2 x n fn x n n
使用公式计算组数据的算术平均数：
例：对某厂工人的工资整理分组后得到如下资料：
28
工资（元） 1000-2000 2000-3000 3000-4000
组限所在的单元格地址，在“输出区域”框输入生成的
频数分析表放置的单元格地址，并在下面选中“图表输 出” ；
15
（6） 单击“确定”，出现“频数分布表”和“直方图”
结果。 注意：
（1）在“频数分布表”和“直方图”结果中，“接
收”是指组限，而“频率”实际上是指“频数”。
（2）利用“直方图”工具直接作出的所谓“直方图”
分布的集中趋势，这是一组数据的典型代表值。其中最
常用的有：算术平均数、几何平均数、众数、中位数等。
25
二、算术平均数
（一）非组数据的算术平均数
在实际应用中，算术平均数是使用最广泛的中 心趋势值。根据录入的数据类型及整理的程度不同，
算术平均数可以包括非组数据的算术平均数和组数
据的算术平均数两种形式。 非组数据的算术平均数的计算方法是：将所有 单个数据相加，然后除以数据总数目。设有n个数据， 其算术平均数的计算公式为：
50
5
二、单项式数组的频数分析
频数分布表的制作与定性数据相似，可通 过两种方法实现：


第一种方法：通过分类汇总实现
第二种方法：通过插入频数分布函数 （FREQUENCY)实现
主要操作步骤：
6
（1）对“年龄”按升序排序；
（2）将各组的年龄值输入到工作表中，作为组界；
（3）单击“插入”→单击“函数”→选择“FREQUENCY”； （4）选择组界区域和数据区域的单元格地址→ 单击“确定”；
标准差，其数学公式为：
( xi x )2 σ
例如，A、B两组各有6位学生参加同一次语文测验，A组 的分数为95、85、75、65、55、45，B组的分数为73、 72、71、69、68、67。这两组的平均数都是70，但A组的 标准差为18.71分，B组的标准差为2.37分，说明A组学生 之间的差距要比B组学生之间的差距大得多。 在Excel中计算标准差是通过插入STDEVP函数；计算方

数据透视表创建完成后，还可以进行美化设计、数据设 置与更改、数据筛选等编辑操作，以满足用户多种不同 的需要。

创建数据透视表采用的是Excel提供的默认样式，如果
用户需要重新对生成的数据透视表进行美化设计，可以 “设置报告格式”直接选择“自动套用格式”。 使用过程中，还可根据需要更改数据透视表的字段项、 数据、显示效果等，以达到需要的分析效果。
（5）按下F2+Ctrl+Shift+Enter，得到频数分布表：
学生年龄频数分布表
年龄（岁） 18 学生人数（人） 2
19
20 21
21
55 17
22
合计
5
100
7
进一步可将频数分布表做成频数分布图： 选中“学生人数”下方的数据区域→单击图表向导 →选择柱形图，初步完成频数分布图：
学生年龄分布图
组中值 x 1500 2500 3500
工人数（人）f 150 220 130
合
计
—
500
要求计算工人的平均工资。
（1）在Excel中录入工人数和组中值的有关数据；
（2）在“组中值”相邻单元格中录入xf ，在其下方单元
格中输入公式“=A2*B2” →单击“Enter”，即出现第一 组xf 相乘的数据结果； （3）单击刚才的数据结果单元格右下角，并同时按住 “Ctrl”键，向下拖动鼠标，直到最后一组数据；
（4）单击“确定”，即完成 频数分布图。
（5）可进一步编辑柱形图颜 色、文字等，以美化频数分 布图。
9
学生年龄分布图
60 50 55
学生人数（人）
40 30 21 20 10 2 0 18 19 20 年龄（岁） 21 22 17
5
10
三、组距式数组的频数分析

对于连续型数据和数据变动范围较大、不同数值的
在Excel中计算极差的方法有两种： 1、首先对一组数据排序，找出其中的最大值与最小 值，然后利用公式计算极差： 极差=最大值-最小值 2、通过插入MAX函数MIN函数计算极差： 极差=MAX（number1，number2…）-
MIN（number1，number2…）
35
三、标准差和方差
标准差和方差是描述数据离散趋势的最主要、最常用 的方法。它从考察所有数据与算术平均数的总体差距的角 度测度数据的平均离散程度，进而评价平均数对数据代表 性的大小。方差是标准差的平方，实际中更多使用的是
个数较多的离散型数据，实际中要采用组距式分组，
并在此基础上进行频数分析，以揭示数值型数据的 分布特征。

这里首先要解决的问题是，数据总体可分为多少组， 每组的组距是多大，组限是多少。具体分组时先算
出极差（最大值-最小值），观察数据变动的最大范
围，然后结合数据的特征来划分，为了计算上的方 便，一般会以10及其倍数为组距。