用Excel作统计分析图形案例讲解

用Excel作统计分析案例讲解说明：Excel拥有很强大的统计分析功能，大量的统计分析运用EXCEL就可以完成在使用统计分析功能之前，要求按照下列步骤操作：1、安装Excel，选择自定义安装，加载“宏命令”，其他按照需要进行安装；2、完成自定义安装，进入EXCEL界面之后，继续下面操作，EXCEL工具加载宏分析工具库确定3、完成上述步骤后，在Excel“工具”菜单下面会出现“数据分析”子菜单，所有统计分析功能均在该子菜单下面。如何运用Excel作统计图（直方图）下列数据是80个工人的月收入（单位：元），绘制直方图，观察月收入的分布状况。102713021573165817051794192020401050135615781665171218001934207011091435157916701730183019402076115414521600167017501854195821401200145216301678175318601965214312351543163016791758187019732270123715431645169017791870198723001260154616491690178018701998235412801560165216991784189020002478129815701652170017931893203825951、数据处理：注意：对任意数据绘制直方图时，首先必须进行简单的数据处理，即对数据进行排序，分组，找分界点。具体表现为：（1）组数的确定，m13.322lg（N），N为数据总量（80个），可计算拟分为8组（2）组距的确定，d（RMAXRMIN）/8200，RMAX,RMIN为数据最大值与最小值（3）结合组数和组距以及原始数据确定各组的起始点，即为第一组1200以下，第二组1200~1400，依次类推，第八组为2400以上。（4）在Excel空白区域输入各组分界点，依次为，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得到如图所示结果；在“输出选项”子菜单下面，选择“图表输出”可得到直方图，选择“新工作簿”表示将频数分布表存放在新的工作表中，也可选择“输出区域”存放在你认为合适的地方3、操作结果完成上述操作后，EXCEL会相应输出下列操作结果。接收频率1199.9941399.9981599.99111799.99281999.99172199.9972399.993其他2按月收入分组频率1200以下41200~140081400~1600111600~1800281800~2000172000~220072200~240032400以上2（1）表格：根据上述操作可得到下列表格，左图为Excel原始输出，右图为对应的含义解释。接收二、如何运用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直方图120.00%100.00%80.00%60.00%40.00%20.00%0.00%频率累积%1、操作步骤：第一步：输入原始数据（A1：A160），并进入描述统计菜单，操作步骤如下：Excel——工具——数据分析——描述统计第二步：在“描述统计”子菜单下面作如下选择：“输入区域”：选择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观测数160三、如何运用Excel作区间估计现有一大批糖果，先从重随机地取16袋，称得重量（以克计）如下：506508499503504510497512514505493496，设袋装糖果重量近似服从正态分布，试求总体均值的置信度为0.95的置信区间。1、操作目标：要进行区间估计，需构造形如下图的工作表：其中，左边A5：A16是变量名，右边B5：B16是变量相应的计算公式，输入样本数据F2：F17。A列“样本统计量”包含三个，依次为样本个数、样本均值、样本标准差，B列给出计算三个样本统计量的计算公式（或称函数）；A列“用户输入”，输入进行区间估计的置信水平，通常为0.95；A列“计算结果”，下面包含进行区间估计所需的六个量：抽样标准误差、自由度、t值、置信区间半径、置信区间下限、置信区间上限。B列为各自对应计算公式注：若事先构造好下表，那么任意数据进行区间，只要输入数据，直接可以得到区间估计的结果。2、操作步骤：第一步：在Excel表格中输入上图中A4：A16和F1：F17的内容。第二步：名称定义。（1）、进行变量定义，将A列的变量名称定义为B列各公式计算结果的变量名。方法：选定A5：B7和A9：B9，A11：B16（先用鼠标选择第一部分，再按住CTRL键选择另外两个部分），然后选择“插入”——“名称”——“指定”——“最左列”——“确定”。如下图：2）、为样本数据进行名称定义，即将F2：F17的数据定义为“样本数据”方法：“插入”——“名称”——“指定”——“首行”——“确定”第三步：进行区间估计。依次在B列输入A列对应位置变量的计算公式，回车可得到各个变量值。1、计算“样本个数”，在B5内编辑计算公式：“=count（F2：F16）”（点中F2：F16），回车即可得到样本个数计算值16。2、计算“样本均值”，在B6内编辑计算公式：“=average（F2：F16）”（点中F2：F16），回车即可得到样本均值计算值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。（或者不需要公式，直接填入自由度值）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经过上述步骤可得区间估计的结果，上述过程看似复杂，实际上只要构造好一个区间估计的固定工作表，对任意数据进行区间估计，只要代入数据即可。五、如何运用Excel作假设检验某车间用一台包装机包装葡萄糖，包得的袋装葡萄糖重量是一个随机变量，它服从正态分布。当机器正常时，其均值为0.5公斤，标准差为0.015公斤。某日开工后为检验包装机是否正常，随机的抽取所包装的糖9袋，称得净重为0.4970.5060.5180.5240.4980.5110.5200.5150.512（公斤）问：机器是否正常？（总体标准差稳定）1、操作目标：构造形如下图的工作表：要进行假设检验，需构造形如下图的工作表：其中，左边A5：A16是变量名，右边B5：B16是变量相应的计算公式，输入样本数据E2：E10A列“样本统计量”包含两个，依次为样本个数、样本均值，B列给出计算个样本统计量的计算公式（或称函数）；A列“用户输入”，输入进行假设检验的置信水平，通常为0.95，总体标准差和总体均值假设值（通常为已知条件）；A列“计算结果”，下面包含进行区间估计所需的两个量：抽样标准误差、计算Z值后面依次为单侧检验和双侧检验，由你自行选择。注：若事先构造好下表，那么任意数据进行假设检验，只要输入数据，直接可以得到假设检验的结果。2、操作方法：第一步：在Excel表格中输入上图中A2：A19和E1：E10的内容第二步：名称定义。（方法基本上相同于区间估计）（1）进行变量定义，将A列的名称定义为B列各个公式计算结果的变量名。选定A3：B4和A6：B8，A10：B11，A13：B15，A17：B19（先用鼠标选择第一部分，再按住CTRL键选择另外两个部分），然后选择“插入”——名称——指定——最左列——确定。（2）为样本数据定义。如图“插入”——“名称”——“指定”——“首行”，此操作是将E2：E10的数据名称定义为“样本数据”第三步：计算依次在B列输入计算公式，形如下图，依次可得到各个函数值，1）计算“样本个数”，在B3内编辑计算公式：“=count（E2：E10）”（点中E2：E10），回车即可得到样本个数计算值9。（2）计算样本均值，在B4内编辑计算公式：“=average（E2：E10）”（点中E2：E10），回车即可得到样本均值计算值0.511（3）、输入假设检验所需要的参数，包括置信水平0.95，总