1、用 Excel 作统计分析案例讲解说明：Excel拥有很强大的统计分析功能，大量的统计分析运用 EXCEL就可以完成。在使用统计分析功能之前，要求按照下列步骤操作：1、安装 Excel，选择自定义安装，加载“宏命令”，其他按照需要进行安装；2、完成自定义安装，进入 EXCEL界面之后，继续下面操作，EXCEL 工具 加载宏 分析工具库 确定3、完成上述步骤后，在 Excel“工具”菜单下面会出现“数据分析”子菜单，所有统计分析功能均在该子菜单下面。一、 如何运用 Excel 作统计图（直方图）下列数据是 80个工人的月收入（单位：元），绘制直方图，观察月收入的分布状况。1027 1302 15
2、73 1658 1705 1794 1920 20401050 1356 1578 1665 1712 1800 1934 20701109 1435 1579 1670 1730 1830 1940 20761154 1452 1600 1670 1750 1854 1958 21401200 1452 1630 1678 1753 1860 1965 21431235 1543 1630 1679 1758 1870 1973 22701237 1543 1645 1690 1779 1870 1987 23001260 1546 1649 1690 1780 1870 1998 2354
3、1280 1560 1652 1699 1784 1890 2000 24781298 1570 1652 1700 1793 1893 2038 25951、数据处理：注意：对任意数据绘制直方图时，首先必须进行简单的数据处理，即对数据进行排序，分组，找分界点。具体表现为：（1）组数的确定， ， 为数据总量（80 个） ，可计算)lg(32.1Nm拟分为 8 组（2）组距的确定， ， 为数据最大值08/)(MIAXRdMINAXR,与最小值（3）结合组数和组距以及原始数据确定各组的起始点，即为第一组 1200 以下，第二组 12001400，依次类推，第八组为 2400 以上。（4）在 Exc
4、el 空白区域输入各组分界点，依次为，1199.99，1399.99，1599.99，1799.99，1999.99，2199.99，2399.99，即区域B2:B8。2、操作步骤（1） 、输入原始数据（区域 A1:A80，B2:B8） ，并对该数据排序。操作方法：EXCEL数据排序。（2）绘直方图操作过程： 第一步：选择“工具”“数据分析” ，见下图：第二步：选择“数据分析”“直方图” ，见下图第三步：在“直方图”字对话框依次执行下列操作：在“输入区域”用鼠标点击 A1:A80 得到如图所示结果；在“分组区域”用鼠标点击 B2:B8 得到如图所示结果；在“输出选项”子菜单下面，选择“图表输出
5、”可得到直方图，选择“新工作簿”表示将频数分布表存放在新的工作表中，也可选择“输出区域”存放在你认为合适的地方3、操作结果完成上述操作后，EXCEL 会相应输出下列操作结果。（1）表格：根据上述操作可得到下列表格，左图为 Excel 原始输出，右图为对应的含义解释。（2）直方图：（调整后如下图 ）直 方 图0510152025301199.991399.991599.991799.991999.992199.992399.99其 他接 收频率0.00%20.00%40.00%60.00%80.00%100.00%120.00%频 率累 积 %二、 如何运用 Excel 计算描述统计量：包括平均
6、数、众数、中位数、方差、偏度、峰度等。下面是某保险公司 160 名员工的某月保险销售额， （单位：千元）要求计算相关描述统计量，了解该公司员工的保险销售额的情况8.4 14.26 15.76 16.98 17.39 17.61 24.2 26.518.81 14.52 15.77 17.07 17.41 17.61 24.26 26.79.24 14.61 15.82 17.14 17.43 17.62 24.8 26.749.64 14.78 15.84 17.16 17.43 17.66 25.05 26.9311.05 14.87 15.86 17.16 17.48 17.7 25.29
7、 27.511.43 14.92 15.88 17.25 17.48 17.75 25.4 27.7612.37 15.08 15.88 17.25 17.52 17.75 25.53 31.1612.74 15.13 15.99 17.25 17.57 17.79 25.64 31.3412.96 15.15 16.16 17.31 17.57 17.84 25.83 32.4接收 频率1199.99 41399.99 81599.99 111799.99 281999.99 172199.99 72399.99 3其他 2按月收入分组 频率1200以下 412001400 81400160
8、0 1116001800 2818002000 1720002200 722002400 32400以上 213.22 15.27 16.22 17.34 17.57 17.9 26.07 32.5513.25 15.34 17.93 18.42 18.96 21.09 21.53 22.6513.33 15.37 17.93 18.51 19.05 21.16 21.64 22.8413.51 15.42 17.96 18.55 19.25 21.16 21.66 23.1613.61 15.43 17.97 18.63 19.27 21.19 21.72 23.2713.61 15.46
9、18.02 18.64 19.35 21.25 21.81 23.413.68 15.48 18.22 18.64 19.45 21.31 21.88 23.4513.8 15.56 18.24 18.69 19.46 21.34 21.92 23.513.85 15.64 18.28 18.73 19.55 21.4 22.13 23.6513.97 15.65 18.29 18.78 19.91 21.42 22.28 23.7614.16 15.71 18.33 18.94 21.03 21.53 22.38 23.941、 操作步骤：第一步：输入原始数据（A1：A160） ，并进入描述
10、统计菜单，操作步骤如下：Excel工具数据分析 描述统计 第二步：在“描述统计”子菜单下面作如下选择：“输入区域”：选择 A1：A160，表示对该数据作描述统计分析。“输入区域”：选择“汇总统计” ，表示输出各种描述统计量，包括平均数、 众数、中位数、方差、 偏度、峰度等各个描述统计量。选择“新工作簿” ，表示将描述统计分析结果存放在新的工作表格中。2、操作结果： 完成上述操作步骤后，Excel 相应输出下列分析结果统计量 计算结果平均 18.77194标准误差 0.354332中位数 17.915众数 17.25标准差 4.48198方差 20.08814峰度 0.678236偏度 0.60
11、8222区域 24.15最小值 8.4最大值 32.55求和 3003.51观测数 160三、如何运用 Excel 作区间估计现有一大批糖果，先从重随机地取 16袋，称得重量（以克计）如下：506 508 499 503 504 510 497 512 514 505 493 496， 设袋装糖果重量近似服从正态分布，试求总体均值 的置信度为 0.95的置信区间。1、操作目标：要进行区间估计，需构造形如下图的工作表：其中，左边 A5：A16 是变量名，右边 B5：B16 是变量相应的计算公式，输入样本数据 F2：F17。A列“样本统计量”包含三个，依次为样本个数、样本均值、样本标准差，B 列给
12、出计算三个样本统计量的计算公式（或称函数）；A列“用户输入”，输入进行区间估计的置信水平，通常为 0.95；A列“计算结果”，下面包含进行区间估计所需的六个量： 抽样标准误差、自由度、t 值、置信区间半径、置信区间下限、置信区间上限。B 列为各自对应计算公式注：若事先构造好下表，那么任意数据进行区间，只要输入数据，直接可以得到区间估计的结果。2、操作步骤：第一步：在 Excel 表格中输入上图中 A4：A16 和 F1：F17 的内容。第二步：名称定义。（1） 、进行变量定义，将 A 列的变量名称定义为 B 列各公式计算结果的变量名。 方法：选定 A5：B7 和 A9：B9，A11：B16（先
13、用鼠标选择第一部分，再按住 CTRL 键选择另外两个部分） ，然后选择“插入”“名称”“指定”“最左列”“确定” 。如下图：（2） 、为样本数据进行名称定义，即将 F2：F17 的数据定义为 “样本数据” 。方法：“插入”“名称”“指定 ”“首行”“确定”第三步：进行区间估计。依次在 B 列输入 A 列对应位置变量的计算公式，回车可得到各个变量值。1、计算“样本个数” ，在 B5 内编辑计算公式：“=count(F2：F16)” （点中F2：F16） ，回车即可得到样本个数计算值 16。2、计算“样本均值” ，在 B6 内编辑计算公式：“=average(F2：F16) ”（点中 F2：F16
14、） ，回车即可得到样本均值计算值 503.75。3、计算“样本标准差” ，在 B7 内编辑计算公式：“=stdev(F2：F16) ”（点中 F2：F16） ，回车即可得到样本标准差 6.202。4、 “置信水平” ，由用户自行输入，一般为 0.955、 “抽样标准误差” ，在 B11 内编辑计算公式：“=样本标准差/sqrt(样本个数)”（方法：输入等于号，点中 B7,输入“/”和函数“sqrt”，点中 B5，回车即可得到抽样标准差 1.55。6、 “自由度” ，在 B12 内编辑计算公式：“=样本个数-1”,具体公式编辑方法同（5） ，回车可得自由度值为 15。 （或者不需要公式，直接填入
15、自由度值）7、 “t 值” ，在 B13 内编辑计算公式：“=tinv(1-B9,B12) ”,具体公式编辑方法同（5） ，回车 t 值为 2.1314.8、 “置信区间半径 ”，在 B14 内编辑计算公式：“=B13*B11 ”，具体公式编辑方法同（5） ，回车，可得值为 3.3049、 “置信区间上限” ，在 B15 内编辑计算公式：“=B6+B14 ”，具体公式编辑方法同（5） ，回车，可得值为 507.054910、 “置信区间下限” ，在 B15 内编辑计算公式：“=B6-B14 ”，具体公式编辑方法同（5） ，回车值为 500.4451经过上述步骤可得区间估计的结果，上述过程看似复
16、杂，实际上只要构造好一个区间估计的固定工作表，对任意数据进行区间估计，只要代入数据即可。五、如何运用 Excel 作假设检验某车间用一台包装机包装葡萄糖，包得的袋装葡萄糖重量是一个随机变量，它服从正态分布。当机器正常时，其均值为 0.5 公斤，标准差为 0.015 公斤。某日开工后为检验包装机是否正常，随机的抽取所包装的糖 9 袋，称得净重为0.497 0.506 0.518 0.524 0.498 0.511 0.520 0.515 0.512 （公斤）问：机器是否正常？(总体标准差稳定）1、操作目标：构造形如下图的工作表：要进行假设检验，需构造形如下图的工作表：其中，左边 A5：A16 是
17、变量名，右边 B5：B16 是变量相应的计算公式，输入样本数据 E2：E10A 列“样本统计量”包含两个，依次为样本个数、样本均值， B 列给出计算个样本统计量的计算公式（或称函数） ；A 列“用户输入” ，输入进行假设检验的置信水平，通常为 0.95，总体标准差和总体均值假设值（通常为已知条件） ；A 列“计算结果” ，下面包含进行区间估计所需的两个量： 抽样标准误差、计算 Z 值后面依次为单侧检验和双侧检验，由你自行选择。注：若事先构造好下表，那么任意数据进行假设检验，只要输入数据，直接可以得到假设检验的结果。2、操作方法：第一步：在 Excel 表格中输入上图中 A2：A19 和 E1：
18、E10 的内容第二步：名称定义。 （方法基本上相同于区间估计）（1）进行变量定义，将 A 列的名称定义为 B 列各个公式计算结果的变量名。选定 A3：B4 和 A6：B8，A10：B11，A13：B15，A17：B19（先用鼠标选择第一部分，再按住 CTRL 键选择另外两个部分） ，然后选择“插入”名称指定最左列确定。（2）为样本数据定义。如图“插入”“名称”“指定 ”“首行” ，此操作是将 E2：E10 的数据名称定义为“样本数据”第三步：计算依次在 B 列输入计算公式，形如下图，依次可得到各个函数值，（1） 计算“样本个数” ，在 B3 内编辑计算公式：“=count(E2：E10) ”（
19、点中 E2：E10） ，回车即可得到样本个数计算值 9。（2）计算样本均值，在 B4 内编辑计算公式：“=average(E2：E10) ”（点中E2：E10） ，回车即可得到样本均值计算值 0.511（3） 、输入假设检验所需要的参数，包括置信水平 0.95，总体标准差 0.015，总体均值假设值 0.5 （在进行假设检验时，这些参数值一般已知）（4） 、 “抽样标准误差” ，在 B10 内编辑公式， “=B7/B3”，回车可得 0.002（5） “计算 Z 值” ，在 B11 内编辑公式， “=NORMSINV（1-B6） ”，回车可得-1.64（6）进行双侧检验， “双侧 Z 值” ，在
20、 B16 内编辑公式， “=NORMSINV(1-B6)”“检验结果” ，在 B18 内编辑公式， “=IF（ABS（B11）ABS(B17),”拒绝 H0” , 接受 H0”）形如下图，输入该公式后，回车即为检验结果六、如何运用 Excel 作方差分析随机选取 26 位被访者，了解他们对安乐死的赞同程度。然后按不同血型进行分组，资料如下表。在显著性水平为 0.05 时进行方差分析，判断不同血型的人对安乐死的态度是否有显著差异性？现假设已把数据输入到 Excel 表格中，区域 A2:D11O A AB AB80 80 73 9278 77 80 8694 69 92 7073 84 76 85
21、79 52 6886 4091 73758164 （1） 操作步骤：第一步：选择“工具”下拉菜单，然后选择“数据分析”选项 。第二步：在分析工具中选择“单因素方差分析” 。如下图：第三步：当出现对话框后 ，在“输入区域”方框内键入 A2：D11（直接让鼠标选中所要分析的数据即可） ；在 方框内输入 0.05；在“输出选项”中选择输出区域为（可以任意指定空白区域） ，若选择“新工作表组” ，方差分析结果将在另一表格中呈现；最后选择“确定” 。（2） 输出结果：完成上述操作步骤后，EXCEL 输出下列分析结果：表一：样本数据描述统计组 计数 求和 平均 方差列 1 10 801 80.1 76.5
22、4444列 2 7 475 67.85714 257.8095列 3 5 389 77.8 82.2列 4 4 333 83.25 87.58333表二：方差分析结果差异源 SS df MS F P-value F crit组间 840.0775 3 280.0258 2.178953 0.119232 3.049124组内 2827.307 22 128.514总计 3667.385 25从上表中可以看出，04912.3),(178953.205.FF所以说明不同血型的人对安乐死的态度没有显著差异。七、如何运用 Excel 作相关分析下表为年龄（X）与脂肪百分比（Y）的数据，试计算两者的相关
23、系数。年龄 23 23 27 27 39 41 45 49脂肪的百分比 9.5 27.9 7.8 17.8 31.4 25.9 27.4 25.2(1)、操作步骤： 第一步：选择“工具”“数据分析”“相关系数”“确定”第二步：在“相关系数”子对话框中，作如下选择：“输入区域”：选中原数据即可，如图所示。“输出选项”：若选择“输出区域”（任意选定空白区域即可，则相关系数计算结果显示在该区域。若选则“新工作表”，则相关系数显示在该工作表中。最后选择“确定”进行相关分析。（2）结果：完成上述步骤后，显示结果如下：列 1 列 2列 1 1列 2 0.792086 1表示年龄与脂肪百分比相关系数为 0.
24、792八、如何运用 Excel 作回归分析下面是某市人均国民收入与消费金额的数据，试对两个变量作回归分析。年份 人均国民收入 人均消费金额1981 393.8 2491982 419.14 2671983 460.86 2891984 544.11 3291985 668.29 4061986 737.73 4511987 859.97 5131988 1068.8 6431989 1169.2 6901990 1250.7 7131991 1429.5 8031992 1725.9 9471993 2099.5 1148（1）操作步骤：第一步：在新工作簿中输入数据如下图所示。第二步：“工具”
25、“数据分析” “回归”选项，单击“确定”按钮，进入“回归”对话框。第三步：在“回归”字对话框作如下操作：在“Y 值输入区域”中输入 C2:C14（点中该区域即可），它代表人均消费金额的数据范围，表示因变量。在“X 值输入区域”中输入 B2:B14（点中该区域即可），它代表人均国民收入的数据范围，表示自变量。“置信度”，通常为 0.95在“输出选项”，进行适当选择，表示选择将回归结果存放的具体位置；在“残差”部分表示回归残差的计算结果（2） 、操作结果：根据上述步骤，最后可得回归结果如下：表一中：给出回归的判决系数（拟合优度）并给出回归标准误差表二中： ，表明本次回归有效05.F表三中：给出回归方程中的截距 54和自变量的回归系数 0.53。 各自的t统计量和 P值表明均通过了显著性检验，所以回归方程中截距和自变量都是有统计学意义的。
