用Excel作统计分析课件

实训五实训五用用Excel作统计分析作统计分析如何运用如何运用Excel作统计图（直方图）作统计图（直方图）下列数据是80个工人的月收入（单位：元），绘制直方图，观察月收入的分布状况。10271302157316581705179419202040105013561578166517121800193420701109143515791670173018301940207611541452160016701750185419582140120014521630167817531860196521431235154316301679175818701973227012371543164516901779187019872300126015461649169017801870199823541280156016521699178418902000247812981570165217001793189320382595如何运用Excel作统计图（直方图）下列数据是80个工人1、数据处理、数据处理 注意：对任意数据绘制直方图时，首先必须进行简单的数据处理，即对数据进行排序，分组，找分界点。具体表现为：（1）组数的确定，为数据总量（80个），可计算拟分为8组（2）组距的确定，为数据最大值与最小值（3）结合组数和组距以及原始数据确定各组的起始点，即为第一组1200以下，第二组12001400，依次类推，第八组为2400以上。（4）在Excel空白区域输入各组分界点，依次为，1199.99，1399.99，1599.99，1799.99，1999.99，2199.99，2399.99，即区域B2:B8。1、数据处理注意：对任意数据绘制直方图时，首先必须进行简单2、操作步骤、操作步骤（1）、输入原始数据（区域A1:A80，B2:B8），并对该数据排序。操作方法：EXCEL数据排序。2、操作步骤（1）、输入原始数据（区域A1:A80，B2:B（2）绘直方图操作过程：第一步：选择第一步：选择“工具工具”“数据分析数据分析”，见下图：，见下图：（2）绘直方图操作过程：第一步：选择“工具”“数据分析第二步：选择第二步：选择“数据分析数据分析”“直方图直方图”，见下图，见下图第二步：选择“数据分析”“直方图”，见下图第三步：在第三步：在“直方图直方图”字对话框依次执行下列操作：字对话框依次执行下列操作：在“输入区域”用鼠标点击A1:A80得到如图所示结果；在“分组区域”用鼠标点击B2:B8得到如图所示结果；在“输出选项”子菜单下面，选择“图表输出”可得到直方图，选择“新工作簿”表示将频数分布表存放在新的工作表中，也可选择“输出区域”存放在你认为合适的地方第三步：在“直方图”字对话框依次执行下列操作：在“输入区域”3、操作结果、操作结果完成上述操作后，EXCEL会相应输出下列操作结果。（1）表格：）表格：根据上述操作可得到下列表格，左图为Excel原始输出，右图为对应的含义解释。接收频率1199.9941399.9981599.99111799.99281999.99172199.9972399.993其他2按月收入分组频率1200以下41200140081400160011160018002818002000172000220072200240032400以上2 3、操作结果完成上述操作后，EXCEL会相应输出下列操作结果(2）直方图：（）直方图：（调整后如下图）(2）直方图：（调整后如下图）二、如何运用二、如何运用Excel计算描述统计量：计算描述统计量：包括平均数、众数、中位数、方差、偏度、峰度等包括平均数、众数、中位数、方差、偏度、峰度等 下面是某保险公司160名员工的某月保险销售额，（单位：千元）要求计算相关描述统计量，了解该公司员工的保险销售额的情况8.414.2615.7616.9817.3917.6124.226.518.8114.5215.7717.0717.4117.6124.2626.79.2414.6115.8217.1417.4317.6224.826.749.6414.7815.8417.1617.4317.6625.0526.9311.0514.8715.8617.1617.4817.725.2927.511.4314.9215.8817.2517.4817.7525.427.7612.3715.0815.8817.2517.5217.7525.5331.1612.7415.1315.9917.2517.5717.7925.6431.3412.9615.1516.1617.3117.5717.8425.8332.413.2215.2716.2217.3417.5717.926.0732.5513.2515.3417.9318.4218.9621.0921.5322.6513.3315.3717.9318.5119.0521.1621.6422.8413.5115.4217.9618.5519.2521.1621.6623.1613.6115.4317.9718.6319.2721.1921.7223.2713.6115.4618.0218.6419.3521.2521.8123.413.6815.4818.2218.6419.4521.3121.8823.4513.815.5618.2418.6919.4621.3421.9223.513.8515.6418.2818.7319.5521.422.1323.6513.9715.6518.2918.7819.9121.4222.2823.7614.1615.7118.3318.9421.0321.5322.3823.94二、如何运用Excel计算描述统计量：包括平均数、众数、中位1、操作步骤操作步骤 第一步：输入原始数据（A1：A160），并进入描述统计菜单，操作步骤如下：Excel工具数据分析描述统计 第二步：在“描述统计”子菜单下面作如下选择：“输入区域”：选择A1：A160，表示对该数据作描述统计分析。“输入区域”：选择“汇总统计”，表示输出各种描述统计量，包括平均数、众数、中位数、方差、偏度、峰度等各个描述统计量。选择“新工作簿”，表示将描述统计分析结果存放在新的工作表格中。1、操作步骤第一步：输入原始数据（A1：A160），并进2、操作结果、操作结果 完成上述操作步骤后，Excel相应输出下列分析结果 统计量计算结果平均18.77194标准误差0.354332中位数17.915众数17.25标准差4.48198方差20.08814峰度0.678236偏度0.608222区域24.15最小值8.4最大值32.55求和3003.51观测数1602、操作结果完成上述操作步骤后，Excel相应输出下列分三、如何运用三、如何运用Excel作区间估计作区间估计 现有一大批糖果，先从重随机地取16袋，称得重量（以克计）如下：506 508 499 503 504 510 497 512 514 505 493496，设袋装糖果重量近似服从正态分布，试求总体均值的置信度为0.95的置信区间 三、如何运用Excel作区间估计现有一大批糖果，先从重随机1、操作目标、操作目标 要进行区间估计，需构造形如下图的工作表：其中，左边A5：A16是变量名，右边B5：B16是变量相应的计算公式，输入样本数据F2：F17。A列“样本统计量”包含三个，依次为样本个数、样本均值、样本标准差，B列给出计算三个样本统计量的计算公式（或称函数）；A列“用户输入”，输入进行区间估计的置信水平，通常为0.95；A列“计算结果”，下面包含进行区间估计所需的六个量：抽样标准误差、自由度、t值、置信区间半径、置信区间下限、置信区间上限。B列为各自对应计算公式注：若事先构造好下表，那么任意数据进行区间，只要输入数据，直接可以得到区间估计的结果。1、操作目标要进行区间估计，需构造形如下图的工作表：其中，2、操作步骤、操作步骤 第一步：在Excel表格中输入上图中A4：A16和F1：F17的内容。第二步：名称定义。（1）、进行变量定义，将A列的变量名称定义为B列各公式计算结果的变量名。方法：选定A5：B7和A9：B9，A11：B16（先用鼠标选择第一部分，再按住CTRL键选择另外两个部分），然后选择“插入”“名称”“指定”“最左列”“确定”。如下图：2、操作步骤第一步：在Excel表格中输入上图中A4：A1（2）、为样本数据进行名称定义，即将F2：F17的数据定义为“样本数据”。方法：“插入”“名称”“指定”“首行”“确定”（2）、为样本数据进行名称定义，即将F2：F17的数据定义为第三步：进行区间估计。依次在B列输入A列对应位置变量的计算公式，回车可得到各个变量值。1、计算“样本个数”，在B5内编辑计算公式：“=count(F2：F16)”（点中F2：F16），回车即可得到样本个数计算值16。第三步：进行区间估计。依次在B列输入A列对应位置变量的计算公2、计算“样本均值”，在B6内编辑计算公式：“=average(F2：F16)”（点中F2：F16），回车即可得到样本均值计算值503.75 2、计算“样本均值”，在B6内编辑计算公式：“=averag3、计算“样本标准差”，在B7内编辑计算公式：“=stdev(F2：F16)”（点中F2：F16），回车即可得到样本标准差6.202。3、计算“样本标准差”，在B7内编辑计算公式：“=stdev4、“置信水平”，由用户自行输入，一般为0.9 5、“抽样标准误差”，在B11内编辑计算公式：“=样本标准差/sqrt(样本个数)”（方法：输入等于号，点中B7,输入“/”和函数“sqrt”，点中B5，回车即可得到抽样标准差1.55。4、“置信水平”，由用户自行输入，一般为0.95、“抽样6、“自由度”，在B12内编辑计算公式：“=样本个数-1”,具体公式编辑方法同（5），回车可得自由度值为15。（或者不需要公式，直接填入自由度值）6、“自由度”，在B12内编辑计算公式：“=样本个数-1”,7、“t值”，在B13内编辑计算公式：“=tinv(1-B9,B12)”,具体公式编辑方法同（5），回车t值为2.1314.7、“t值”，在B13内编辑计算公式：“=tinv(1-B9 8、“置信区间半径”，在B14内编辑计算公式：“=B13*B11”，具体公式编辑方法同（5），回车，可得值为3.3048、“置信区间半径”，在B14内编辑计算公式：“=B139、“置信区间上限”，在B15内编辑计算公式：“=B6+B14”，具体公式编辑方法同（5），回车，可得值为507.05499、“置信区间上限”，在B15内编辑计算公式：“=B6+B110、“置信区间下限”，在B15内编辑计算公式：“=B6-B14”，具体公式编辑方法同（5），回车值为500.4451 经过上述步骤可得区间估计的结果，上述过程看似复杂，实际上只要构造好一个区间估计的固定工作表，对任意数据进行区间估计，只要代入数据即可。10、“置信区间下限”，在B15内编辑计算公式：“=B6-B五、如何运用五、如何运用Excel作假设检验作假设检验某车间用一台包装机包装葡萄糖，包得的袋装葡萄糖重量是一个随机变量，它服从正态分布。当机器正常时，其均值为0.5公斤，标准差为0.015公斤。某日开工后为检验包装机是否正常，随机的抽取所包装的糖9袋，称得净重为 0.497 0.506 0.518 0.524 0.498 0.511 0.520 0.515 0.512 （公斤）问：机器是否正常？(总体标准差稳定）五、如何运用Excel作假设检验某车间用一台包装机包装葡萄糖1、操作目标：构造形如下图的工作表：、操作目标：构造形如下图的工作表：要进行假设检验，需构造形如下图的工作表：其中，左边A5：A16是变量名，右边B5：B16是变量相应的计算公式，输入样本数据E2：E10A列“样本统计量”包含两个，依次为样本个数、样本均值，B列给出计算个样本统计量的计算公式（或称函数）；A列“用户输入”，输入进行假设检验的置信水平，通常为0.95，总体标准差和总体均值假设值（通常为已知条件）；A列“计算结果”，下面包含进行区间估计所需的两个量：抽样标准误差、计算Z值后面依次为单侧检验和双侧检验，由你自行选择。注：若事先构造好下表，那么任意数据进行假设检验，只要输入数据，直接可以得到假设检验的结果。1、操作目标：构造形如下图的工作表：要进行假设检验，需构造形2、操作方法：、操作方法：第一步：在Excel表格中输入上图中A2：A19和E1：E10的内容第二步：名称定义。（方法基本上相同于区间估计）（1）进行变量定义，将A列的名称定义为B列各个公式计算结果的变量名。选定A3：B4和A6：B8，A10：B11，A13：B15，A17：B19（先用鼠标选择第一部分，再按住CTRL键选择另外两个部分），然后选择“插入”名称指定最左列确定。2、操作方法：第一步：在Excel表格中输入上图中A2：A1（2）为样本数据定义。如图“插入”“名称”“指定”“首行”，此操作是将E2：E10的数据名称定义为“样本数据”（2）为样本数据定义。如图“插入”“名称”“指定”第三步：计算依次在B列输入计算公式，形如下图，依次可得到各个函数值，(1)、计算“样本个数”，在B3内编辑计算公式：“=count(E2：E10)”（点中E2：E10），回车即可得到样本个数计算值9。第三步：计算依次在B列输入计算公式，形如下图，依次可得到各个（2）计算样本均值，在B4内编辑计算公式：“=average(E2：E10)”（点中E2：E10），回车即可得到样本均值计算值0.511（2）计算样本均值，在B4内编辑计算公式：“=average（3）、输入假设检验所需要的参数，包括置信水平0.95，总体标准差0.015，总体均值假设值0.5 （在进行假设检验时，这些参数值一般已知）（3）、输入假设检验所需要的参数，包括置信水平0.95，总体（4）、“抽样标准误差”，在B10内编辑公式，“=B7/B3”，回车可得0.002（4）、“抽样标准误差”，在B10内编辑公式，“=B7/B3（5）“计算Z值”，在B11内编辑公式，“=NORMSINV（1-B6）”，回车可得-1.64（5）“计算Z值”，在B11内编辑公式，“=NORMSINV（6）进行双侧检验，“双侧Z值”，在B16内编辑公式，“=NORMSINV(1-B6)”（6）进行双侧检验，“双侧Z值”，在B16内编辑公式，“=N“检验结果”，在B18内编辑公式，“=IF（ABS（B11）ABS(B17),”拒绝H0”,接受H0”）形如下图，输入该公式后，回车即为检验结果“检验结果”，在B18内编辑公式，“=IF（ABS（B11）六、如何运用六、如何运用Excel作方差分析作方差分析随机选取26位被访者，了解他们对安乐死的赞同程度。然后按不同血型进行分组，资料如下表。在显著性水平为0.05时进行方差分析，判断不同血型的人对安乐死的态度是否有显著差异性？现假设已把数据输入到Excel表格中，区域A2:D11OAABAB8080739278778086946992707384768579526886409173758164六、如何运用Excel作方差分析随机选取26位被访者，了解他（1）操作步骤操作步骤：第一步：选择“工具”下拉菜单，然后选择“数据分析”选项。第二步：在分析工具中选择“单因素方差分析”。如下图：（1）操作步骤：第一步：选择“工具”下拉菜单，然后选择第三步：当出现对话框后，在“输入区域”方框内键入A2：D11（直接让鼠标选中所要分析的数据即可）；在方框内输入0.05；在“输出选项”中选择输出区域为（可以任意指定空白区域），若选择“新工作表组”，方差分析结果将在另一表格中呈现；最后选择“确定”。第三步：当出现对话框后，在“输入区域”方框内键入A2：D1（2）输出结果：）输出结果：完成上述操作步骤后，EXCEL输出下列分析结果：表一：样本数据描述统计组计数求和平均方差列 11080180.176.54444列 2747567.85714257.8095列 3538977.882.2列 4433383.2587.58333 表二：方差分析结果差异源SSdfMSFP-valueF crit组间840.07753280.02582.1789530.1192323.049124组内2827.30722128.514总计3667.38525从上表中可以看出，所以说明不同血型的人对安乐死的态度没有显著差异。（2）输出结果：完成上述操作步骤后，EXCEL输出下列分析七、如何运用七、如何运用Excel作相关分析作相关分析下表为年龄（X）与脂肪百分比（Y）的数据，试计算两者的相关系数。年龄2323272739414549脂肪的百分比9.527.97.817.831.425.927.425.2七、如何运用Excel作相关分析下表为年龄（X）与脂肪百分比(1)、操作步骤：、操作步骤：第一步：选择“工具”“数据分析”“相关系数”“确定”(1)、操作步骤：第一步：选择“工具”“数据分析”第二步：在“相关系数”子对话框中，作如下选择：“输入区域”：选中原数据即可，如图所示。“输出选项”：若选择“输出区域”（任意选定空白区域即可，则相关系数计算结果显示在该区域。若选则“新工作表”，则相关系数显示在该工作表中。最后选择“确定”进行相关分析。第二步：在“相关系数”子对话框中，作如下选择：“输入区域”（2）结果：）结果：完成上述步骤后，显示结果如下：表示年龄与脂肪百分比相关系数为0.792（2）结果：完成上述步骤八、如何运用八、如何运用Excel作回归分析作回归分析下面是某市人均国民收入与消费金额的数据，试对两个变量作回归分析。年份人均国民收入人均消费金额1981393.82491982419.142671983460.862891984544.113291985668.294061986737.734511987859.9751319881068.864319891169.269019901250.771319911429.580319921725.994719932099.51148八、如何运用Excel作回归分析下面是某市人均国民收入与消费（1）操作步骤：）操作步骤：第一步：在新工作簿中输入数据如下图所示。（1）操作步骤：第二步：“工具”“数据分析”“回归”选项，单击“确定”按钮，进入“回归”对话框。第二步：“工具”“数据分析”“回归”选项，单击“第三步：在“回归”字对话框作如下操作：在“Y值输入区域”中输入C2:C14（点中该区域即可），它代表人均消费金额的数据范围，表示因变量。在“X值输入区域”中输入B2:B14（点中该区域即可），它代表人均国民收入的数据范围，表示自变量。“置信度”，通常为0.95在“输出选项”，进行适当选择，表示选择将回归结果存放的具体位置；在“残差”部分表示回归残差的计算结果第三步：在“回归”字对话框作如下操作：在“Y值输入（2）、操作结果：）、操作结果：根据上述步骤，最后可得回归结果如下：表一中：给出回归的判决系数（拟合优度）并给出回归标准误差 表二中，表明本次回归有效 表三中：给出回归方程中的截距54和自变量的回归系数0.53。各自的t统 计量和P值表明均通过了显著性检验，所以回归方程中截距和自变量都是有统计学意义的（2）、操作结果：根据上述步骤，最后可得回归结果如下：