Excel在统计分析中的应用

发布时间：2024-11-02

Excel在统计分析中的应用

嘉兴学院2008年《统计学概论》课程实践考核

Excel在统计分析中的应用

一、加载Excel数据分析宏程序

Excel作为Office电子表格文件处理工具，不仅具有进行相关电子表格处理的功能，而且还带有一个可以用来进行统计数据处理分析的宏程序库——“分析工具库”。通常计算机安装了Office后，如果Excel电子表格“工具”项的下拉菜单中没有“数据分析”命令，Excel并不能直接用来进行统计数据的处理分析，需要通过加载宏，启动“数据分析”宏“分析工具库”系统后才能运行统计数据的处理分析工具。

加载 “数据分析”宏，可点击Excel中“工

具”菜单，在弹出的“加载宏”对话框中选中

“分析工具库”及“分析工具库-VBA函数”（如

图1所示）然后点击“确定”。数据分析宏程序

加载后，会在Excel的“工具”菜单里出现“数

据分析”的命令选项。

完成了Excel“数据分析”程序宏的加载后，

点击工具菜单中的“数据分析”命令，即会弹

出Excel的“数据分析”对话框（如图2所示）。

在整个分析工具宏程序库中设有各种数据处理

分析的工具宏程序，包括用于进行描述统计分析的描述统计和直方图等分析工具宏，也包括图1 加载宏对话框

可以进行推断统计分析的方差分析、相关和回

归分析、统计推断和检验以及时间序列指数平滑法等分析工具宏。

Excel在统计分析中的应用

图2 “数据分析”对话框

运行Excel“数据分析”宏中某一分析功能，并根据分析工具对数据进行分析，Excel的数据分析结果通常以统计表格或统计图的形式直观地显示出来。

二、Excel的统计函数

Excel具有大量的内置函数，例如，财务函数、日期和时间函数、数学和三角函数以及统计函数等，共有300多个内置函数。内置函数就是预定义的内置公式，它使用参数并按照特定的顺序进行计算。函数的参数是函数进行计算所必需的初始值。用户把参数传递给函数，函数按特定指令对参数进行计算，把计算的结果返回给使用者。函数的参数可以是数字、文本、逻辑值或者单元格的引用，也可以是常量公式或其他函数。每个函数都有它需要的参数类型。在Excel提供的函数中，有77个统计函数（见表1）。

表1 Excel中统计函数及其功能函数

AVEDEV

AVERAGE

AVERAGEA

BETADIST

BETAINV

BINOMDIST

CHIDIST

CHIINV

CHITEST

CONHDENCE

CORREL

COUNT

COUNTA

COVAR

CRITBINOM

DEVSQ 计算参数的算术平均数返回所有参数的平均值返回累积beta分布的概率密度计算beta分布累积函数的反函数值返回一元二次项分布的概率计算单尾chi-squared的概率值计算单尾chi-squared分布的反函数值计算独立检验的结果返回总体平均值的置信区间计算两个数组的相关系数计算指定范围或数组里含有数字的个数计算参数清单里含有非空白数据的个数计算协方差，即每对数据点的偏差乘积的平均数返回使累积二项式分布大于等于临界值的最小值返回数据点与各自样本均值偏差的平方和功能返回一组数据与其均值的绝对偏差的平均值

Excel在统计分析中的应用

续表

函数

EXPONDIST

FDIST

FINV

FISHER

FISHERINV

FORECAST

FREQUENCY

FTEST

GAMMADIST

GAMMAINV

GAMMALN

GEOMEAN

GROWTH

HARMEAN

HYPGEOMDIST

INTERCEPT

KURT

LARGE

LINEST

LOGEST

LOGINV

LOGNORMDIST

MAX

MAXA

MEDIAN

MIN

MINA

MODE

NEGBINOMDIST

NORMDIST

NORMINV

NORMSDIST

NORMSINV

PEARSON

PERCENTILE

PERCENTRANK

PERMUT

POISSON

PROB

QUARTILE

RANK

RSQ

SKEW

SLOPE

SMALL

STANDARDIZE 返回指数分布函数计算F概率分布计算F概率分布的反函数值计算数值的Fisher变换计算Fisher变换函数的反函数值根据已知的x和y数组的线性回归预测x值以一垂直数组计算频率的分布返回F检验结果的值返回伽玛分布函数计算伽玛累积分布的反函数值计算伽玛函数的自然对数返回一正数数组或数值区域的几何平均数根据给定的数据预测指数增长值计算一组数据的调和平均数返回超几何分布计算因变量和自变量的线性回归线的截距值计算一组数据的峰值计算在数据组中第K大的数值返回一线性回归方程的参数计算描述指数曲线预测公式的参数计算x的对数正态分布累积函数的反函数值计算x的对数正态分布的累积函数返回一组参数中的最大值，忽略逻辑值及文本字符返回一组参数中的最大值，不忽略逻辑值和字符串符计算一组参数的中间值返回一组参数中的最小值，忽略逻辑值及文本字符返回一组参数中的最小值，不忽略逻辑值和字符串返回一组数据或数据区域中出现频率最高的数返回负二项式分布返回给定平均值和标准差的正态分布的累积函数对于指定的均值和标准差，计算其正态分布累积函数的反函数值返回标准正态分布函数值返回标准正态分布的区间点计算皮尔逊（Pearson）积矩法的相关系数返回数组的K百分比数值点返回特定数值在一组数中的百分比排位计算从给定元素数目的集合中选取若干元素的排列数计算泊松概率分布计算落在概率中上下值之间的相对概率返回一组数据的四分位点计算某数字在指定范围中的排序等级返回给定数据点的Pearson积矩法相关系数的平方计算一个分布的偏斜度计算直线回归的斜率计算数据集中第K小的值计算一个标准化正态分布的概率值功能

Excel在统计分析中的应用

续表

函数

STDEV

STDEVP

STEYX

TDIST

TINV

TREND

TRIMMEAN

TTEST

VAR

VARA

VARP

VARPA

WEIBULL

ZTEST 功能根据某样本估计出标准差

将参数序列视为总体本身，返回其总体标准差返回回归中每个由x预测y值的标准误差计算Student-t分布值计算指定自由度和双尾概率的Student-t分布的区间点返回一条线性回归拟合线的一组纵坐标值（Y值）计算数组内部的平均值计算Student-t检验的概率值根据抽样样本，计算方差估计值及逻辑值文字将省略根据抽样样本，计算方差估计值根据整个总体本身，计算方差文字及逻辑值省略不计根据整个总体，计算方差计算Weibull分布值计算z检验的双尾P值

在Excel运行过程中调用统计函数主要采用两种方法。第一种是在工作簿的单元格中直接输入等号及统计函数的函数名称，然后在有关的参数选项种填入正确的参数，即可得到计算结果。第二种是利用粘贴函数按钮调用，单击粘贴函数快捷按钮，或点击插入菜单中的函数（如图3所示），即会弹出一个Excel函数选择表，选择其中的“统计”选项，屏幕上会弹出一个统计函数的选择表（如图3和图4所示），选定需要调用的统计函数名称，同样会弹出该函数的初始值输入对话框，在有关选项内填入确定的参数就能得到函数的计算结果。

图3 函数菜单选项图4 统计函数的选择表

三、Excel在数据整理中的应用

（一）应用Excel进行统计分组

整理统计数据的重要方法是进行统计分组，显示频数分布形态。在Excel中

Excel在统计分析中的应用

有一个专用于统计分组的函数FREQUENCY，能进行统计分组，计算各组的频数和频率。下面以第二章中为例分别说明二者的操作方法。

首先，输入待分组统计资料，如表2所示。

表2 某生产车间50名工人日加工零件数单位：件

J 对上表资料进行分组，操作步骤如下：

第一步，点击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所示。

Excel在统计分析中的应用

图6 函数参数对话框

图7 计算结果

（二）应用Excel编制变量数列

1．单项数列的编制

下面以表3为例，说明其操作方法。

表3 单项式数列日产量频数

合计 10

首先，在A1单元格输入“日产量”12、10、13、11、14、12、11、12、13、12，并将这10个数据输入A2～A11中，然后选定数据所在的列A，单击“数据”菜单的“排序”项，打开排序对话框，单击确定，进行排序。排序之后，单击“数据”菜单中的“分类汇总”，打开“分类汇总”对话框，如图8所示，在“汇总方式”栏内选择“计数”，单击“确定”即可。

Excel在统计分析中的应用

图8 “分类汇总”对话框图9 输出结果

汇总之后，单击左上边数字“2”，可显示单项式数列，如图9所示

2．组距数列的编制

对于组距数列的编制，可以使用频数函数Frequency进行统计。取得频数分布后，可再列表计算频数以及累计频数和频率。例如，对图7的结果，编辑整理后可得组距式数列结果，如图10所示。

图10 组距式数列结果

（三）应用Excel描述集中趋势

描述集中趋势的统计参数有均值、众数、中位数等。在Excel中，对于未分组的资料可以用统计函数计算，对于已分组资料则根据公式计算。

1．算术均值

（1）简单平均法

例如，某生产组5名工人生产同一种产品的日产量分别为60、70、80、90、100（单位：件），则计算方法为：

Excel在统计分析中的应用

首先，将这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件。

Excel在统计分析中的应用

可以也通过函数对话框计算标准差。单击“插入”菜单中的“函数”命令，在弹出的对话框中选择“统计”类别里的STDEVP函数，打开STDEVP函数的对话框，在Numberl中输入数据所在的区域“A2：A6”，确定后即可求得标准差。

2．加权式标准差

下面以图13为例，说明其计算方法。

图13 加权计算标准差

将资料输入A、B两列后，首先计算均值。单击C2，输入“=A2*B2”，得出24 000，利用填充柄功能，计算出C2~C6，然后单击C7，输入“=SUM（C2：C6）”，计算出合计数196 000，利用填充柄功能，计算出工人数合计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所示。

Excel在统计分析中的应用

图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中的数据资料为例。

Excel在统计分析中的应用

表6 某企业产量和出厂价格产品名称

甲产品

乙产品计量单位件吨产量基期 2 000 4 000 报告期 2 4000 5 000 出厂价格（元）

基期 500 1 000 报告期 600 1 100

首先，将数据输入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为例。

Excel在统计分析中的应用

表7 产量个体指数和基期总产值

将数据输入Excel后，见图17中A、B、C列。

图17 数据输入方式

首先，计算D列数字：单击D3单元格，输入“=B3*C3”并利用填充柄功能计算D4；最后计算合计数，单击D5输入“=SUM（D3 : D4）”求出基期总销售额，并利用填充柄功能计算C5。单击任一空单元格，输入“=D5／C5”，即得产量加权算术平均法总指数124％。

2．调和平均法总指数的计算

在一定条件下，根据报告期同度量因素编制的质量指标综合法总指数可以变形为加权调和平均法总指数。一般来说，加权调和平均法总指数多用于计算质量指标综合法总指数。现以表8

为例。

表8 出厂价格个体指数和报告期总产值