第四节Excel的统计数据分析功能.docx

1、第四节Excel的统计数据分析功能嘉兴学院2017年统计学概论（专科段）实践考核培训资料Excel的统计数据分析功能一、加载Excel数据分析宏程序Excel作为Office电子表格文件处理工具，不仅具有进行相关电子表格处理的功能，而且还带有一个可以用来进行统计数据处理分析的宏程序库“分析工具库”。通常计算机安装了Office后，如果Excel电子表格“工具”项的下拉菜单中没有“数据分析”命令，Excel并不能直接用来进行统计数据的处理分析，需要通过加载宏，启动“数据分析”宏“分析工具库”系统后才能运行统计数据的处理分析工具。加载 “数据分析”宏，可点击Excel中“工具”菜单，在弹出的“加载

2、宏”对话框中选中“分析工具库”及“分析工具库-VBA函数”（如图1所示）然后点击“确定”。数据分析宏程序加载后，会在Excel的“工具”菜单里出现“数据分析”的命令选项。完成了Excel“数据分析”程序宏的加载后，点击工具菜单中的“数据分析”命令，即会弹出Excel的“数据分析”对话框（如图2所示）。在整个分析工具宏程序库中设有各种数据处理分析的工具宏程序，包括用于进行描述统计分析的描述统计和直方图等分析工具宏，也包括可以进行推断统计分析的方差分析、相关和回归分析、统计推断和检验以及时间序列指数平滑法等分析工具宏。图2“数据分析”对话框运行Excel“数据分析”宏中某一分析功能，并根据分析工具

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

4、功能函数功能AVEDEV返回一组数据与其均值的绝对偏差的平均值AVERAGE计算参数的算术平均数AVERAGEA返回所有参数的平均值BETADIST返回累积beta分布的概率密度BETAINV计算beta分布累积函数的反函数值BINOMDIST返回一元二次项分布的概率CHIDIST计算单尾chi-squared的概率值CHIINV计算单尾chi-squared分布的反函数值CHITEST计算独立检验的结果CONHDENCE返回总体平均值的置信区间CORREL计算两个数组的相关系数COUNT计算指定范围或数组里含有数字的个数COUNTA计算参数清单里含有非空白数据的个数COVAR计算协方差，即每

5、对数据点的偏差乘积的平均数CRITBINOM返回使累积二项式分布大于等于临界值的最小值DEVSQ返回数据点与各自样本均值偏差的平方和EXPONDIST返回指数分布函数FDIST计算F概率分布FINV计算F概率分布的反函数值FISHER计算数值的Fisher变换FISHERINV计算Fisher变换函数的反函数值FORECAST根据已知的x和y数组的线性回归预测x值FREQUENCY以一垂直数组计算频率的分布FTEST返回F检验结果的值GAMMADIST返回伽玛分布函数GAMMAINV计算伽玛累积分布的反函数值GAMMALN计算伽玛函数的自然对数GEOMEAN返回一正数数组或数值区域的几何平均数

6、GROWTH根据给定的数据预测指数增长值HARMEAN计算一组数据的调和平均数HYPGEOMDIST返回超几何分布INTERCEPT计算因变量和自变量的线性回归线的截距值KURT计算一组数据的峰值LARGE计算在数据组中第K大的数值LINEST返回一线性回归方程的参数LOGEST计算描述指数曲线预测公式的参数LOGINV计算x的对数正态分布累积函数的反函数值LOGNORMDIST计算x的对数正态分布的累积函数MAX返回一组参数中的最大值，忽略逻辑值及文本字符MAXA返回一组参数中的最大值，不忽略逻辑值和字符串符MEDIAN计算一组参数的中间值MIN返回一组参数中的最小值，忽略逻辑值及文本字符M

7、INA返回一组参数中的最小值，不忽略逻辑值和字符串MODE返回一组数据或数据区域中出现频率最高的数NEGBINOMDIST返回负二项式分布NORMDIST返回给定平均值和标准差的正态分布的累积函数NORMINV对于指定的均值和标准差，计算其正态分布累积函数的反函数值NORMSDIST返回标准正态分布函数值NORMSINV返回标准正态分布的区间点PEARSON计算皮尔逊（Pearson）积矩法的相关系数PERCENTILE返回数组的K百分比数值点PERCENTRANK返回特定数值在一组数中的百分比排位PERMUT计算从给定元素数目的集合中选取若干元素的排列数POISSON计算泊松概率分布PROB

8、计算落在概率中上下值之间的相对概率QUARTILE返回一组数据的四分位点RANK计算某数字在指定范围中的排序等级RSQ返回给定数据点的Pearson积矩法相关系数的平方SKEW计算一个分布的偏斜度SLOPE计算直线回归的斜率SMALL计算数据集中第K小的值STANDARDIZE计算一个标准化正态分布的概率值STDEV根据某样本估计出标准差STDEVP将参数序列视为总体本身，返回其总体标准差STEYX返回回归中每个由x预测y值的标准误差TDIST计算Student-t分布值TINV计算指定自由度和双尾概率的Student-t分布的区间点TREND返回一条线性回归拟合线的一组纵坐标值（Y值）TRI

9、MMEAN计算数组内部的平均值TTEST计算Student-t检验的概率值VAR根据抽样样本，计算方差估计值及逻辑值文字将省略VARA根据抽样样本，计算方差估计值VARP根据整个总体本身，计算方差文字及逻辑值省略不计VARPA根据整个总体，计算方差WEIBULL计算Weibull分布值ZTEST计算z检验的双尾P值在Excel运行过程中调用统计函数主要采用两种方法。第一种是在工作簿的单元格中直接输入等号及统计函数的函数名称，然后在有关的参数选项种填入正确的参数，即可得到计算结果。第二种是利用粘贴函数按钮调用，单击粘贴函数快捷按钮，或点击插入菜单中的函数（如图3所示），即会弹出一个Excel函数

10、选择表，选择其中的“统计”选项，屏幕上会弹出一个统计函数的选择表（如图3和图4所示），选定需要调用的统计函数名称，同样会弹出该函数的初始值输入对话框，在有关选项内填入确定的参数就能得到函数的计算结果。 图3函数菜单选项图4统计函数的选择表三、Excel在数据整理中的应用（一）应用Excel进行统计分组整理统计数据的重要方法是进行统计分组，显示频数分布形态。在Excel中有一个专用于统计分组的函数FREQUENCY，能进行统计分组，计算各组的频数和频率。下面以第二章中为例分别说明二者的操作方法。首先，输入待分组统计资料，如表2所示。表2某生产车间50名工人日加工零件数（单位：件 ）ABCDEFG

11、HIJ11171221241291391071171301221252108131125117122133126122118108311011812312613313412712311811241121341271231191131201231271355137114120128124115139128124121对上表资料进行分组，操作步骤如下：第一步，点击K1单元格，从“插入”菜单中选择“函数”项，或者单击工具栏按钮fx，屏幕弹出“插入函数”对话框，在“选择类别（C）”栏中选择“统计类”，如图4所示；第二步，在“选择函数（N）”列表中选择“FREQUENCY”，如图5所示；图5函数选项第三

12、步，单击“确定”按钮，弹出“函数参数”对话框，如图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编制

13、变量数列1单项数列的编制下面以表3为例，说明其操作方法。表3单项式数列日产量频数101112124132141合计10首先，在A1单元格输入“日产量”12、10、13、11、14、12、11、12、13、12，并将这10个数据输入A2A11中，然后选定数据所在的列A，单击“数据”菜单的“排序”项，打开排序对话框，单击确定，进行排序。排序之后，单击“数据”菜单中的“分类汇总”，打开“分类汇总”对话框，如图8所示，在“汇总方式”栏内选择“计数”，单击“确定”即可。 图8“分类汇总”对话框 图9输出结果汇总之后，单击左上边数字“2”，可显示单项式数列，如图9所示2组距数列的编制对于组距数列的编制，可

14、以使用频数函数Frequency进行统计。取得频数分布后，可再列表计算频数以及累计频数和频率。例如，对图7的结果，编辑整理后可得组距式数列结果，如图10所示。图10组距式数列结果 （三）应用Excel描述集中趋势描述集中趋势的统计参数有均值、众数、中位数等。在Excel中，对于未分组的资料可以用统计函数计算，对于已分组资料则根据公式计算。1算术均值（1）简单平均法例如，某生产组5名工人生产同一种产品的日产量分别为60、70、80、90、100（单位：件），则计算方法为：首先，将这5名工人的日产量输入A1A5单元格内，然后，利用AVERAGE函数计算均值，可单击任一空单元格，输入“=AVERAG

15、E（A1：A5）”，回车确定，即得到日平均产量80（件人）。（2）加权平均法利用分组数据可以直接计算加权均值。先列出计算表，然后用公式计算。首先将分组资料输入，例如A、B两列，如图11所示。图11数据输入视图计算C栏各组数值，单击C2单元格，输入“=A2*B2”，回车确定后即得出第一组数值。然后，利用填充柄功能，即鼠标指向C2单元格右下方小黑方块，当鼠标指针变为黑十字时，按下鼠标左键，并向下拖拽至C7单元格后放开，得出C栏各组数值。然后单击C8单元格并输入“=SUM（C2：C7）”计算出C栏合计数，利用填充柄功能计算出B栏合计数。最后单击任一空单元格，输入“=C8/B8”，确定后得出工人平均日

16、包装量为23.05件。（四）应用Excel描述离散趋势1标准差Excel提供了一组求标准差的函数，其中函数STDEVP是一个求标准差的函数；函数STDEVPA是求包含逻辑值和数字串数列的标准差的函数。这两个函数操作方法类似，现以图12为例说明标准差的计算方法。图12计算标准差单击任一单元格输入“=STDEVP（A2:A6）”，按“确定”即得到标准差14.14件。可以也通过函数对话框计算标准差。单击“插入”菜单中的“函数”命令，在弹出的对话框中选择“统计”类别里的STDEVP函数，打开STDEVP函数的对话框，在Numberl中输入数据所在的区域“A2：A6”，确定后即可求得标准差。2加权式标准

17、差下面以图13为例，说明其计算方法。图13加权计算标准差将资料输入A、B两列后，首先计算均值。单击C2，输入“=A2*B2”，得出24 000，利用填充柄功能，计算出C2C6，然后单击C7，输入“=SUM（C2：C6）”，计算出合计数196 000，利用填充柄功能，计算出工人数合计200，再单击任一空单元格，输入“=C5B7”，得出均值为980；接着计算离差平方及离差平方和。单击D2单元格输入“=（C2980）2”，确定后利用填充柄功能计算D数列数值；单击E2单元格，输入“=D2*B2”，计算加权离差平方，利用填充柄功能计算E列；单击E7单元格，输入“=SUM（E2：E6）”，计算离差平方和；

18、最后，单击任一空单元格，输入“=SQR（E7B7）”，即得总体标准差116.62。四、Excel在抽样估计中的应用利用Excel提供的有关统计函数，可以对总体平均数进行区间估计。设从200名工人中随机抽取20名工人调查其日产量，参阅案例1操作方法，计算出样本平均数40件和样本标准差7.5，要求在95的概率保证程度下，估计该企业工人的平均日产量。在A1、A2、A3单元格中分别输入样本容量、样本均值、样本标准差，即20、40、7.5。单击A4，计算样本平均误差，输入“=A3SQRT（A1）”。利用函数NORMINV计算平均日产量的上限和下限。下面使用函数对话框方式使用函数。单击A5单元格，选择“插

19、入”菜单中“函数”命令，打开“选择函数”对话框，在“选择类别”中选择“统计”类别，在“选择函数”栏内选择“NORMINV”函数，单击“确定”，打开NORMINV函数设置对话框，如图14所示。图14抽样估计 本例中，给定的概率保证程度为95，因此在进行NORMINV函数参数设置时，Probability项要输入“0.95（10.95）/2”即0.975；在Mean和Standard_dev项内输入样本平均数A2、样本平均误差A4，确定后计算出样本平均数的上限为43.29件。在Probability项中输入“（1095）2”即0.025，计算出样本平均数的下限为36.71件，如图15所示。图15计

20、算结果五、Excel在指数分析中的应用（一）综合法总指数本案例以表6中的数据资料为例。表6某企业产量和出厂价格产 品 名 称计量单位产 量出厂价格（元）基 期报告期基 期报告期甲产品乙产品件吨2 0004 0002 4000 5 000 5001 000 6001 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数量指

21、标综合法指数的计算数量指标综合法指数，是以价格为同度量因素（权数）编制的商品销售量指数，用来反映总产值的变动情况。在一般情况下，同度量因素（价格）要固定在基期。因此，单击任一空单元格，输入“=J6G6*100”得124（），即产量综合法指数Kq=124，它表明两种商品的总产量综合指数比基期上升24（124100），由于产量上升使总产值增加（J6G6）为120万元。2质量指标综合法指数的计算质量指标综合法指数，是以销售量为同度量因素（权数）编制的商品价格指数，用来反映价格的变动情况。在一般情况下，同度量因素（销售量）要固定在报告期。因此，单击任一空单元格，输入“=H6J6*100”，得111.9

22、，即价格综合法指数kp=111.9，它表明三种商品综合价格指数报告期比基期上升了11.9，由于价格上涨使总产值上升了（H6J6）74万元。（二）平均法总指数1算术平均法总指数的计算在一定条件下，根据基期同度量因素编制的数量指标综合法总指数可以变形为加权算术平均法总指数。一般来说，加权算术平均法总指数多用于计算数量指标综合法总指数。现以表7为例。表7产量个体指数和基期总产值产 品 名 称产量个体指数（%）基期总产值（万元）甲产品乙产品120.00125.00100400将数据输入Excel后，见图17中A、B、C列。图17数据输入方式首先，计算D列数字：单击D3单元格，输入“=B3*C3”并利用

23、填充柄功能计算D4；最后计算合计数，单击D5输入“=SUM（D3 : D4）”求出基期总销售额，并利用填充柄功能计算C5。单击任一空单元格，输入“=D5C5”，即得产量加权算术平均法总指数124。2调和平均法总指数的计算在一定条件下，根据报告期同度量因素编制的质量指标综合法总指数可以变形为加权调和平均法总指数。一般来说，加权调和平均法总指数多用于计算质量指标综合法总指数。现以表8为例。表8出厂价格个体指数和报告期总产值产 品 名 称出厂价格个体指数（%）报告期总产值（万元）甲产品乙产品120.00110.00144550将数据输入Excel后，见图18中A、B、C列。图18数据输入方式首先，计

24、算D两列数字，单击D3单元格，输入“=C3/B3*100”，然后利用填充柄功能计算D4的值；然后计算C5，输入“=SUM（C3 : C4）”，并利用填充柄功能计算D5；最后单击任一空单元格，输入“=C5/D5”，即得到调和加权平均法价格总指数111.9%。六、Excel在长期趋势分析中的应用（一）移动平均法在Excel中，移动平均法可使用AVERAGE函数，利用填充柄功能求得，如图19所示。图19移动平均法计算进行三项移动平均：可单击C3单元格，输入“=AVERACE（B2:B4）”，然后利用填充柄功能，计算C4C20单元格的值。进行五项移动平均：可单击D4单元格，输入“=AVERAGE（B2

25、:B6）”，然后利用填充柄功能，计算D5D19单元格的值。（二）最小平方法以图20说明如何运用最小平方法来建立直线趋势方程。图20最小平方法输入A、B、C后，计算D列。单击D2，输入“=B22”，并用填充柄功能，计算D3D7，再计算E列，单击E2，输入“=B2*C2”，并用填充柄功能，计算E3E7。然后计算合计，单击B8，输入“=SUM（B2：B7）”得62，再并用填充柄功能，计算C、D、E各列的合计数。 下面计算参数a、b值，先计算b，单击任一单元格，输入“=（6*E8B8*C8）/（6*D8C82）”，确定后即得b的值1.028。再计算a，单击任一单元格，输入“=B861.028*C86”

26、得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计算结果