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

1图1加载宏对话框嘉兴学院2013年《统计学概论》（专科段）实践考核培训资料Excel的统计数据分析功能一、加载Excel数据分析宏程序Excel作为Office电子表格文件处理工具，不仅具有进行相关电子表格处理的功能，而且还带有一个可以用来进行统计数据处理分析的宏程序库——“分析工具库”。通常计算机安装了Office后，如果Excel电子表格“工具”项的下拉菜单中没有“数据分析”命令，Excel并不能直接用来进行统计数据的处理分析，需要通过加载宏，启动“数据分析”宏“分析工具库”系统后才能运行统计数据的处理分析工具。加载“数据分析”宏，可点击Excel中“工具”菜单，在弹出的“加载宏”对话框中选中“分析工具库”及“分析工具库-VBA函数”（如图1所示）然后点击“确定”。数据分析宏程序加载后，会在Excel的“工具”菜单里出现“数据分析”的命令选项。完成了Excel“数据分析”程序宏的加载后，点击工具菜单中的“数据分析”命令，即会弹出Excel的“数据分析”对话框（如图2所示）。在整个分析工具宏程序库中设有各种数据处理分析的工具宏程序，包括用于进行描述统计分析的描述统计和直方图等分析工具宏，也包括可以进行推断统计分析的方差分析、相关和回归分析、统计推断和检验以及时间序列指数平滑法等分析工具宏。图2“数据分析”对话框运行Excel“数据分析”宏中某一分析功能，并根据分析工具对数据进行分析，Excel的数据分析结果通常以统计表格或统计图的形式直观地显示出来。二、Excel的统计函数Excel具有大量的内置函数，例如，财务函数、日期和时间函数、数学和三角函数以及统计函数等，共有300多个内置函数。内置函数就是预定义的内置公式，它使用参数并按照特定的顺序进行计算。函数的参数是函数进行计算所必需的初始值。用户把参数传递给函数，函数按特定指令对参数进行计算，把计算的结果返回给使用者。函数的参数可以是数字、文本、逻辑值或者单元格的引用，也可以是常量公式或其他函数。每个函数都有它需要的参数类型。在Excel提供的函数中，有77个统计函数（见表1）。表1Excel中统计函数及其功能函数功能AVEDEV返回一组数据与其均值的绝对偏差的平均值AVERAGE计算参数的算术平均数AVERAGEA返回所有参数的平均值BETADIST返回累积beta分布的概率密度BETAINV计算beta分布累积函数的反函数值2函数功能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返回标准正态分布的区间点3函数功能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统计函数的选择表4三、Excel在数据整理中的应用（一）应用Excel进行统计分组整理统计数据的重要方法是进行统计分组，显示频数分布形态。在Excel中有一个专用于统计分组的函数FREQUENCY，能进行统计分组，计算各组的频数和频率。下面以第二章中为例分别说明二者的操作方法。首先，输入待分组统计资料，如表2所示。表2某生产车间50名工人日加工零件数（单位：件）ABCDEFGHIJ11171221241291391071171301221252108131125117122133126122118108311011812312613313412712311811241121341271231191131201231271355137114120128124115139128124121对上表资料进行分组，操作步骤如下：第一步，点击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计算结果5（二）应用Excel编制变量数列1．单项数列的编制下面以表3为例，说明其操作方法。表3单项式数列日产量频数101112124132141合计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）简单平均法6例如，某生产组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为例，说明其计算方法。7图13加权计算标准差将资料输入A、B两列后，首先计算均值。单击C2，输入“=A2*B2”，得出24000，利用填充柄功能，计算出C2~C6，然后单击C7，输入“=SUM（C2：C6）”，计算出合计数196000，利用填充柄功能，计算出工人数合计200，再单击任一空单元格，输入“=C5／B7”，得出均值为980；接着计算离差平方及离差平方和