第四节Excel的统计数据分析功能.docx
《第四节Excel的统计数据分析功能.docx》由会员分享，可在线阅读，更多相关《第四节Excel的统计数据分析功能.docx（20页珍藏版）》请在冰豆网上搜索。
第四节Excel的统计数据分析功能
嘉兴学院2017年《统计学概论》（专科段）实践考核培训资料
Excel的统计数据分析功能
一、加载Excel数据分析宏程序
Excel作为Office电子表格文件处理工具，不仅具有进行相关电子表格处理的功能，而且还带有一个可以用来进行统计数据处理分析的宏程序库——“分析工具库”。
通常计算机安装了Office后，如果Excel电子表格“工具”项的下拉菜单中没有“数据分析”命令，Excel并不能直接用来进行统计数据的处理分析，需要通过加载宏，启动“数据分析”宏“分析工具库”系统后才能运行统计数据的处理分析工具。
加载“数据分析”宏，可点击Excel中“工具”菜单，在弹出的“加载宏”对话框中选中“分析工具库”及“分析工具库-VBA函数”（如图1所示）然后点击“确定”。
数据分析宏程序加载后，会在Excel的“工具”菜单里出现“数据分析”的命令选项。
完成了Excel“数据分析”程序宏的加载后，点击工具菜单中的“数据分析”命令，即会弹出Excel的“数据分析”对话框（如图2所示）。
在整个分析工具宏程序库中设有各种数据处理分析的工具宏程序，包括用于进行描述统计分析的描述统计和直方图等分析工具宏，也包括可以进行推断统计分析的方差分析、相关和回归分析、统计推断和检验以及时间序列指数平滑法等分析工具宏。
图2 “数据分析”对话框
运行Excel“数据分析”宏中某一分析功能，并根据分析工具对数据进行分析，Excel的数据分析结果通常以统计表格或统计图的形式直观地显示出来。
二、Excel的统计函数
Excel具有大量的内置函数，例如，财务函数、日期和时间函数、数学和三角函数以及统计函数等，共有300多个内置函数。
内置函数就是预定义的内置公式，它使用参数并按照特定的顺序进行计算。
函数的参数是函数进行计算所必需的初始值。
用户把参数传递给函数，函数按特定指令对参数进行计算，把计算的结果返回给使用者。
函数的参数可以是数字、文本、逻辑值或者单元格的引用，也可以是常量公式或其他函数。
每个函数都有它需要的参数类型。
在Excel提供的函数中，有77个统计函数（见表1）。
表1 Excel中统计函数及其功能
函 数
功 能
AVEDEV
返回一组数据与其均值的绝对偏差的平均值
AVERAGE
计算参数的算术平均数
AVERAGEA
返回所有参数的平均值
BETADIST
返回累积beta分布的概率密度
BETAINV
计算beta分布累积函数的反函数值
BINOMDIST
返回一元二次项分布的概率
CHIDIST
计算单尾chi-squared的概率值
CHIINV
计算单尾chi-squared分布的反函数值
CHITEST
计算独立检验的结果
CONHDENCE
返回总体平均值的置信区间
CORREL
计算两个数组的相关系数
COUNT
计算指定范围或数组里含有数字的个数
COUNTA
计算参数清单里含有非空白数据的个数
COVAR
计算协方差，即每对数据点的偏差乘积的平均数
CRITBINOM
返回使累积二项式分布大于等于临界值的最小值
DEVSQ
返回数据点与各自样本均值偏差的平方和
EXPONDIST
返回指数分布函数
FDIST
计算F概率分布
FINV
计算F概率分布的反函数值
FISHER
计算数值的Fisher变换
FISHERINV
计算Fisher变换函数的反函数值
FORECAST
根据已知的x和y数组的线性回归预测x值
FREQUENCY
以一垂直数组计算频率的分布
FTEST
返回F检验结果的值
GAMMADIST
返回伽玛分布函数
GAMMAINV
计算伽玛累积分布的反函数值
GAMMALN
计算伽玛函数的自然对数
GEOMEAN
返回一正数数组或数值区域的几何平均数
GROWTH
根据给定的数据预测指数增长值
HARMEAN
计算一组数据的调和平均数
HYPGEOMDIST
返回超几何分布
INTERCEPT
计算因变量和自变量的线性回归线的截距值
KURT
计算一组数据的峰值
LARGE
计算在数据组中第K大的数值
LINEST
返回一线性回归方程的参数
LOGEST
计算描述指数曲线预测公式的参数
LOGINV
计算x的对数正态分布累积函数的反函数值
LOGNORMDIST
计算x的对数正态分布的累积函数
MAX
返回一组参数中的最大值，忽略逻辑值及文本字符
MAXA
返回一组参数中的最大值，不忽略逻辑值和字符串符
MEDIAN
计算一组参数的中间值
MIN
返回一组参数中的最小值，忽略逻辑值及文本字符
MINA
返回一组参数中的最小值，不忽略逻辑值和字符串
MODE
返回一组数据或数据区域中出现频率最高的数
NEGBINOMDIST
返回负二项式分布
NORMDIST
返回给定平均值和标准差的正态分布的累积函数
NORMINV
对于指定的均值和标准差，计算其正态分布累积函数的反函数值
NORMSDIST
返回标准正态分布函数值
NORMSINV
返回标准正态分布的区间点
PEARSON
计算皮尔逊（Pearson）积矩法的相关系数
PERCENTILE
返回数组的K百分比数值点
PERCENTRANK
返回特定数值在一组数中的百分比排位
PERMUT
计算从给定元素数目的集合中选取若干元素的排列数
POISSON
计算泊松概率分布
PROB
计算落在概率中上下值之间的相对概率
QUARTILE
返回一组数据的四分位点
RANK
计算某数字在指定范围中的排序等级
RSQ
返回给定数据点的Pearson积矩法相关系数的平方
SKEW
计算一个分布的偏斜度
SLOPE
计算直线回归的斜率
SMALL
计算数据集中第K小的值
STANDARDIZE
计算一个标准化正态分布的概率值
STDEV
根据某样本估计出标准差
STDEVP
将参数序列视为总体本身，返回其总体标准差
STEYX
返回回归中每个由x预测y值的标准误差
TDIST
计算Student-t分布值
TINV
计算指定自由度和双尾概率的Student-t分布的区间点
TREND
返回一条线性回归拟合线的一组纵坐标值（Y值）
TRIMMEAN
计算数组内部的平均值
TTEST
计算Student-t检验的概率值
VAR
根据抽样样本，计算方差估计值及逻辑值文字将省略
VARA
根据抽样样本，计算方差估计值
VARP
根据整个总体本身，计算方差文字及逻辑值省略不计
VARPA
根据整个总体，计算方差
WEIBULL
计算Weibull分布值
ZTEST
计算z检验的双尾P值
在Excel运行过程中调用统计函数主要采用两种方法。
第一种是在工作簿的单元格中直接输入等号及统计函数的函数名称，然后在有关的参数选项种填入正确的参数，即可得到计算结果。
第二种是利用粘贴函数按钮调用，单击粘贴函数快捷按钮，或点击插入菜单中的函数（如图3所示），即会弹出一个Excel函数选择表，选择其中的“统计”选项，屏幕上会弹出一个统计函数的选择表（如图3和图4所示），选定需要调用的统计函数名称，同样会弹出该函数的初始值输入对话框，在有关选项内填入确定的参数就能得到函数的计算结果。
图3 函数菜单选项 图4 统计函数的选择表
三、Excel在数据整理中的应用
（一）应用Excel进行统计分组
整理统计数据的重要方法是进行统计分组，显示频数分布形态。
在Excel中有一个专用于统计分组的函数FREQUENCY，能进行统计分组，计算各组的频数和频率。
下面以第二章中为例分别说明二者的操作方法。
首先，输入待分组统计资料，如表2所示。
表2 某生产车间50名工人日加工零件数（单位：
件）
A
B
C
D
E
F
G
H
I
J
1
117
122
124
129
139
107
117
130
122
125
2
108
131
125
117
122
133
126
122
118
108
3
110
118
123
126
133
134
127
123
118
112
4
112
134
127
123
119
113
120
123
127
135
5
137
114
120
128
124
115
139
128
124
121
对上表资料进行分组，操作步骤如下：
第一步，点击K1单元格，从“插入”菜单中选择“函数”项，或者单击工具栏按钮fx，屏幕弹出“插入函数”对话框，在“选择类别（C）”栏中选择“统计类”，如图4所示；
第二步，在“选择函数（N）”列表中选择“FREQUENCY”，如图5所示；
图5 函数选项
第三步，单击“确定”按钮，弹出“函数参数”对话框，如图6所示。
首先，在对话框“Data_array”栏中输入待分组计算频数分布的原数据，本例中为“A1：
J5”。
然后，在“Bins_array”栏中输入分组标志（按组距上限分组），本例可输入“{109;114;119;124;129;134}”。
在输入过程中，数据前后必须加大括号，数据间用分号分割。
输入完后，单击确定，这时在K1中显示3，由于事先确定分为6组，选定“K1：
K6”单元格作为放置计算结果的区域，按F2键，再按“Shift+Ctrl+Enter”组合健，即可显示频数分布结果，如图7所示。
图6 函数参数对话框图7 计算结果
（二）应用Excel编制变量数列
1．单项数列的编制
下面以表3为例，说明其操作方法。
表3 单项式数列
日产量
频 数
10
1
11
2
12
4
13
2
14
1
合计
10
首先，在A1单元格输入“日产量”12、10、13、11、14、12、11、12、13、12，并将这10个数据输入A2～A11中，然后选定数据所在的列A，单击“数据”菜单的“排序”项，打开排序对话框，单击确定，进行排序。
排序之后，单击“数据”菜单中的“分类汇总”，打开“分类汇总”对话框，如图8所示，在“汇总方式”栏内选择“计数”，单击“确定”即可。
图8 “分类汇总”对话框图9 输出结果
汇总之后，单击左上边数字“2”，可显示单项式数列，如图9所示
2．组距数列的编制
对于组距数列的编制，可以使用频数函数Frequency进行统计。
取得频数分布后，可再列表计算频数以及累计频数和频率。
例如，对图7的结果，编辑整理后可得组距式数列结果，如图10所示。
图10 组距式数列结果
（三）应用Excel描述集中趋势
描述集中趋势的统计参数有均值、众数、中位数等。
在Excel中，对于未分组的资料可以用统计函数计算，对于已分组资料则根据公式计算。
1．算术均值
（1）简单平均法
例如，某生产组5名工人生产同一种产品的日产量分别为60、70、80、90、100（单位：
件），则计算方法为：
首先，将这5名工人的日产量输入A1～A5单元格内，然后，利用AVERAGE函数计算均值，可单击任一空单元格，输入“=AVERAGE（A1：
A5）”，回车确定，即得到日平均产量80（件／人）。
（2）加权平均法
利用分组数据可以直接计算加权均值。
先列出计算表，然后用公式计算。
首先将分组资料输入，例如A、B两列，如图11所示。
图11 数据输入视图
计算C栏各组数值，单击C2单元格，输入“=A2*B2”，回车确定后即得出第一组数值。
然后，利用填充柄功能，即鼠标指向C2单元格右下方小黑方块，当鼠标指针变为黑十字时，按下鼠标左键，并向下拖拽至C7单元格后放开，得出C栏各组数值。
然后单击C8单元格并输入“=SUM（C2：
C7）”计算出C栏合计数，利用填充柄功能计算出B栏合计数。
最后单击任一空单元格，输入“=C8/B8”，确定后得出工人平均日包装量为23.05件。
（四）应用Excel描述离散趋势
1．标准差
Excel提供了一组求标准差的函数，其中函数STDEVP是一个求标准差的函数；函数STDEVPA是求包含逻辑值和数字串数列的标准差的函数。
这两个函数操作方法类似，现以图12为例说明标准差的计算方法。
图12 计算标准差
单击任一单元格输入“=STDEVP（A2:
A6）”，按“确定”即得到标准差14.14件。
可以也通过函数对话框计算标准差。
单击“插入”菜单中的“函数”命令，在弹出的对话框中选择“统计”类别里的STDEVP函数，打开STDEVP函数的对话框，在Numberl中输入数据所在的区域“A2：
A6”，确定后即可求得标准差。
2．加权式标准差
下面以图13为例，说明其计算方法。
图13 加权计算标准差
将资料输入A、B两列后，首先计算均值。
单击C2，输入“=A2*B2”，得出24000，利用填充柄功能，计算出C2~C6，然后单击C7，输入“=SUM（C2：
C6）”，计算出合计数196000，利用填充柄功能，计算出工人数合计200，再单击任一空单元格，输入“=C5／B7”，得出均值为980；接着计算离差平方及离差平方和。
单击D2单元格输入“=（C2－980）^2”，确定后利用填充柄功能计算D数列数值；单击E2单元格，输入“=D2*B2”，计算加权离差平方，利用填充柄功能计算E列；单击E7单元格，输入“=SUM（E2：
E6）”，计算离差平方和；最后，单击任一空单元格，输入“=SQR（E7／B7）”，即得总体标准差116.62。
四、Excel在抽样估计中的应用
利用Excel提供的有关统计函数，可以对总体平均数进行区间估计。
设从200名工人中随机抽取20名工人调查其日产量，参阅案例1操作方法，计算出样本平均数40件和样本标准差7.5，要求在95％的概率保证程度下，估计该企业工人的平均日产量。
在A1、A2、A3单元格中分别输入样本容量、样本均值、样本标准差，即20、40、7.5。
单击A4，计算样本平均误差，输入“=A3／SQRT（A1）”。
利用函数NORMINV计算平均日产量的上限和下限。
下面使用函数对话框方式使用函数。
单击A5单元格，选择“插入”菜单中“函数”命令，打开“选择函数”对话框，在“选择类别”中选择“统计”类别，在“选择函数”栏内选择“NORMINV”函数，单击“确定”，打开NORMINV函数设置对话框，如图14所示。
图14 抽样估计
本例中，给定的概率保证程度为95％，因此在进行NORMINV函数参数设置时，Probability项要输入“0.95＋（1－0.95）/2”即0.975；在Mean和Standard_dev项内输入样本平均数A2、样本平均误差A4，确定后计算出样本平均数的上限为43.29件。
在Probability项中输入“（1－0．95）／2”即0.025，计算出样本平均数的下限为36.71件，如图15所示。
图15 计算结果
五、Excel在指数分析中的应用
（一）综合法总指数
本案例以表6中的数据资料为例。
表6 某企业产量和出厂价格
产品名称
计量单位
产量
出厂价格（元）
基期
报告期
基期
报告期
甲产品
乙产品
件
吨
2000
4000
24000
5000
500
1000
600
1100
首先，将数据输入Excel后，见图16中A、B、C、D、E、F列。
图16 数据输入方式
计算G、H、I、J四列的销售额。
对于G列，单击G4单元格，输入“=C4*E4”，并利用填充柄功能计算G5；H、I、J列均可仿此计算；之后单击G6单元格，输入“=SUM（G4：
G5）”，并利用填充柄功能，计算出G、H、I、J各列的总产值。
1．数量指标综合法指数的计算
数量指标综合法指数，是以价格为同度量因素（权数）编制的商品销售量指数，用来反映总产值的变动情况。
在一般情况下，同度量因素（价格）要固定在基期。
因此，单击任一空单元格，输入“=J6／G6*100”得124（％），即产量综合法指数Kq=124％，它表明两种商品的总产量综合指数比基期上升24％（124％－100％），由于产量上升使总产值增加（J6－G6）为120万元。
2．质量指标综合法指数的计算
质量指标综合法指数，是以销售量为同度量因素（权数）编制的商品价格指数，用来反映价格的变动情况。
在一般情况下，同度量因素（销售量）要固定在报告期。
因此，单击任一空单元格，输入“=H6／J6*100”，得111.9％，即价格综合法指数kp=111.9％，它表明三种商品综合价格指数报告期比基期上升了11.9％，由于价格上涨使总产值上升了（H6－J6）74万元。
（二）平均法总指数
1．算术平均法总指数的计算
在一定条件下，根据基期同度量因素编制的数量指标综合法总指数可以变形为加权算术平均法总指数。
一般来说，加权算术平均法总指数多用于计算数量指标综合法总指数。
现以表7为例。
表7 产量个体指数和基期总产值
产品名称
产量个体指数（%）
基期总产值（万元）
甲产品
乙产品
120.00
125.00
100
400
将数据输入Excel后，见图17中A、B、C列。
图17 数据输入方式
首先，计算D列数字：
单击D3单元格，输入“=B3*C3”并利用填充柄功能计算D4；最后计算合计数，单击D5输入“=SUM（D3:
D4）”求出基期总销售额，并利用填充柄功能计算C5。
单击任一空单元格，输入“=D5／C5”，即得产量加权算术平均法总指数124％。
2．调和平均法总指数的计算
在一定条件下，根据报告期同度量因素编制的质量指标综合法总指数可以变形为加权调和平均法总指数。
一般来说，加权调和平均法总指数多用于计算质量指标综合法总指数。
现以表8为例。
表8 出厂价格个体指数和报告期总产值
产品名称
出厂价格个体指数（%）
报告期总产值（万元）
甲产品
乙产品
120.00
110.00
144
550
将数据输入Excel后，见图18中A、B、C列。
图18 数据输入方式
首先，计算D两列数字，单击D3单元格，输入“=C3/B3*100”，然后利用填充柄功能计算D4的值；然后计算C5，输入“=SUM（C3:
C4）”，并利用填充柄功能计算D5；最后单击任一空单元格，输入“=C5/D5”，即得到调和加权平均法价格总指数111.9%。
六、Excel在长期趋势分析中的应用
（一）移动平均法
在Excel中，移动平均法可使用AVERAGE函数，利用填充柄功能求得，如图19所示。
图19 移动平均法计算
进行三项移动平均：
可单击C3单元格，输入“=AVERACE（B2:
B4）”，然后利用填充柄功能，计算C4~C20单元格的值。
进行五项移动平均：
可单击D4单元格，输入“=AVERAGE（B2:
B6）”，然后利用填充柄功能，计算D5~D19单元格的值。
（二）最小平方法
以图20说明如何运用最小平方法来建立直线趋势方程。
图20 最小平方法
输入A、B、C后，计算D列。
单击D2，输入“=B2^2”，并用填充柄功能，计算D3～D7，再计算E列，单击E2，输入“=B2*C2”，并用填充柄功能，计算E3～E7。
然后计算合计，单击B8，输入“=SUM（B2：
B7）”得62，再并用填充柄功能，计算C、D、E各列的合计数。
下面计算参数a、b值，先计算b，单击任一单元格，输入“=（6*E8－B8*C8）/（6*D8－C8^2）”，确定后即得b的值1.028。
再计算a，单击任一单元格，输入“=B8／6－1.028*C8／6”得a的值6.733。
于是，建立直线趋势方程，即yt=6.733+1.028t。
将C列t值依次代入直线趋势方程yt=6.733+1.028t中，便得到y的预测值。
七、Excel在相关与回归分析中的应用
（一）相关系数的计算
利用Excel计算相关系数，可以使用CORREL函数计算。
利用图21中的资料计算x与y相关系数。
图21 数据输入
单击“插入”菜单里的“函数”命令，选择函数类别“统计”里的“CORREL函数”，打开相关系数函数CORREL对话框，如图22所示。
图22 相关分析函数对话框
在Array1、Array2里分别输入两列数据所在区域“B2:
B7”和“C2:
C7”，即得相关系数0.909。
（二）一元线性回归分析
利用“数据分析”宏中回归分析，可以直接计算y对x的回归模型。
利用图21中的资料计算y对x的回归模型。
单击“工具”菜单中的“数据分析”选项，弹出“数据分析”对话框，如图23所示。
图23 回归分析选项
选中回归选项，单击“确定”按钮。
屏幕上将弹出“回归”对话框，如图24所示。
在“Y值输入区域（Y）”中，输入“B2:
B7”，“X值输入区域（X）”中输入“C2:
C7”，输出选项如果默认，则输出结果将在新工作表中显示，单击确定按钮，EXCEL将自动生成一新工作表，表中显示回归分析结果，如图25所示。
从计算结果可见，回归模型为
。
图24 回归分析对话框
图25 计算结果
升级会员 