Excel在统计中的应用与数据统计分析.pdf

Excel在统计中的应用Excel与数据统计分析一、实验说明（一）中文Excel简介Microsoft Excel是美国微软公司开发的Windows环境下的电子表 格系统，它是目前应用最为广泛的办公室表格处理软件之一。自Excel 诞生以来 Excel 历经了Excel5.0、Excel95 Excel97 和Excel2000 等 不同版本。随着版本的不断提高，Excel软件的强大的数据处理功能和 操作的简易性逐渐走入了一个新的境界，整个系统的智能化程度也不断 提高，它甚至可以在某些方面判断用户的下一步操作，使用户操作大为 简化。Excel具有强有力的数据库管理功能、丰富的宏命令和函数、强 有力的决策支持工具、图表绘制功能、宏语言功能、样式功能、对象连 接和嵌入功能、连接和合并功能，并且操作简捷，这些特性，已使Excel 成为现代办公软件重要的组成部分。由于大家对Excel的常用办公功能都比较熟悉，本实验重点介绍 Excel在统计分析中的应用。（二）实验目的与要求本实验重点介绍Excel在统计分析中的应用，包括Excel在描述统计 中的应用以及Excel在推断统计中的应用，要求学生熟练掌握运用Excel 进行统计分析的方法，并能够对分析结果进行解释。二、实验实验一 Excel在描述统计中的应用实验目的及要求要求学生掌握运用Excel进行描述统计分析、绘制各种图表和运用数 据透视表工具的技术。实验内容及步骤（一）描述统计分析例1T：表是1978-2005年我国城镇居民可支配收入数据，试求城镇 居民可支配收入时间序列的基本统计量。表1-1 1978-2005年我国城镇居民可支配收入（元）年份城镇居民可支配收入年份城镇居民可支配收入197834419922026.6197940519932577.41980477.619943496.21981500.4199542831982535.319964838.91983564.619975160.31984652.119985425.11985739.1199958541986899.62000628019871002.220016859.619881181.420027702.81989137920038472.219901510.220049421.619911700.6200510493STEP1：用鼠标点击工作表中待分析数据的任一单元格。STEP2：选择“工具”菜单的“数据分析”子菜单。STEP3：用鼠标双击数据分析工具中的“描述统计”选项。STEP4：出现“描述统计”对话框，如图1T所示。图1-1对话框内各选项的含义如下：输入区域：在此输入待分析数据区域的单元格范围。分组方式：如果需要指出输入区域中的数据是按行还是按列排列，则单 击“行”或“列”。标志位于第一行/列：如果输入区域的第一行中包含标志项（变量名），则选中“标志位于第一行”复选框；如果输入区域的第一列中包含标志 项，则选中“标志位于第一列”。复选框：如果输入区域没有标志项，则不选任何复选框，Excel将在输 出表中生成适宜的数据标志。均值置信度：若需要输出由样本均值推断总体均值的置信区间，则选中 此复选框，然后在右侧的编辑框中，输入所要使用的置信度。例如，置 信度95%可计算出的总体样本均值置信区间为10,则表示：在5%的显著 水平下总体均值的置信区间为(X-10,X+10)o第K个最大/小值：如果需要在输出表的某一行中包含每个区域 的数据的第k个最大/小值，则选中此复选框。然后在右侧的编辑框中，输入k的数值。输出区域：在此框中可填写输出结果表左上角单元格地址,用于控 制输出结果的存放位置。新工作表：单击此选项，可在当前工作簿中插入新工作表，并由新工作 表的A1单元格开始存放计算结果。如果需要给新工作表命名，则在右 侧编辑框中键入名称。新工作簿：单击此选项，可创建一新工作簿，并在新工作簿的新工作表 中存放计算结果。汇总统计：指定输出表中生成下列统计结果，则选中此复选框。STEP5：填写完“描述统计”对话框之后，按“确定”按扭即可。结果 如图2所示。Kicrosoft Excel-Bookl13回区文件 编辑 视图9 插入9 格式）工具建）数据也）窗口世）帮助国）-51 xe 匾 柒宋体 12 B/刀卷号聂壁 聿王&金1A31；一歹A I B 二|D|E|F|GK31 列12 3 4 5 6 7 8 9 o 1 2 3 4 5 6 7 83 3 3 3 3 3 3 3 4 4 4 4 4 4 4 4 4均准位数准差度度域小大和测大小信 平标中众标方峰偏区最最求观最最置差误数差3384.860714 590.1963594 1863.6#N/A3123.0255839753288.794-0.51564107 0.838973153 10149.6 343.4 1049394776.128 10493 343.41210.98288749值值数(1。度数字就绪:绘图 图2描述统计结果结果说明：描述统计工具可生成以下统计指标，按从上到下的顺序其中 包括样本的平均值、标准误差、组中值、众数、样本标准差、样本方 差、峰度值、偏度值、极差、最小值、最大值、样本总和、样本个数和一定显著水平下 总体均值的置信区间。（二）绘图操作例1-2：仍以例1T的数据为例，绘制城镇居民可支配收入时间序列的散 点图。STEP1：拖动鼠标选定数值区域A2:C12,不包括数据上面的标志项。STEP2：选择“插入”菜单的“图表”子菜单，进入图表向导，图-3所ZjN oSTEP3：选择“图表类型”为“散点图”，然后单击“下一步”。5文件 编辑9 视图9 插入（X）格式）工具复）数据也）窗口世）帮助国）喝倒：宋体 12|B 2 U I.春专方I电I事田命 a 昌 应 1978-A|B区标准类型自定义类型1年份城镇居民可支配收21978II 3433197944198047751981500619825357198356481984652919857391019868991119871002121988118113198913731419901510151991170016199220261719932577H 1994 i 收入/Sheet2Z Sheets/图表向导-4步骤之1-图表类型图图图 形形线图 柱条折饼图图图环达面匹(匡。鱼，法图 Y 自选图形）、匚1。J标4。闻昌j 3 三.不壬八就绪数字图3图表向导图STEP4：确定用于制作图表的数据区。Excel将自动把你前面所选定的 数据区的地址放入图表数据区的内，单击下一步按钮，出现图4所示 对话框。图1-4作图过程图STEP5：填写图表标题、X轴坐标名称、Y轴坐标名称，此处不用填写,单击“下一步”。STEP6：选择图表输出的位置，然后单击“完成”按扭即生成（三）数据透视表工具数据透视表是Excel中强有力的数据列表分析工具。它不仅可以用 来作单变量数据的次数分布或总和分析，还可以用来作双变量数据的交 叉频数分析、总和分析和其它统计量的分析。例3：表2列出了学生两门功课评定结果，建立学生两门功课评 定结果的交叉频数表。表2学生两门功课评定结果学号语文数学学号语文数学1001优差1006中良1002良中1007中优1003中中1008差良1004差中1009良中1005差差操作步骤：STEP1：输入数据，选中有数据的任一单元格，然后选择“数据”菜单 的“数据透视表”子菜单，进入数据透视表向导。STEP2：选择“Microsoft Excel数据清单或数据库”为数据源。图1-6 所示，单击“下一步”。C Microsoft Excel-Book1g2文件但）编辑也）视图9 插入Q）格式）工具 数据如 窗口）帮助但）-6 x:遏算宋体_ 12 B Z U善善专方|幻言|二 5 A 目111213C20A12 3456789 OOOOOOOOO WFOOOOOOOOO 1X 1X 1X 1 1X 1 1X 1X 1X语优良中差差中中差良!Sheet 1/SY敖据透视表和教据透视图向导-3步骤之1口区请指定待分析数据的数据返类型:Microsoft Office Excel数据列表或数据库i8O外部数据源d）O多重合并计算数据区域C）另一个数据透视表或数据透视图所需创建的报表类型：数据透视表（T）O数据透视图或数据透视表）3）取消 上一步旺:|下一步国）J 完成也）23458O:绘匡国）收 自选图形电）、。勺）H G c就绪图1-6 STEP3：选择待分析的数据的区域，一般情况下Excel会自动根据当前 单元格确定待分析数据区域，因此你只要直接单击“下一步”按扭即可。STEP4：确定数据透视表的结构，在此例中，要建立的是一个交叉频数 表，分别按语文和数学的成绩对学生的人数进行交叉频数分析，因此可将三个按扭“学号”、“语文”、“数学”分别拖放到表格的指定部位,并且双击“求和项：学号”，将其改为记数项，结果如图1-7所示。图7布局对话框STEP5：选择数据透视表的显示位置之后，单击“完成按扭”，可出现 如图8所示的数据透视表。图1-8结果说明：如图8的结果所示，数据透视表可以作为一个交叉频数分 析工具。完成数据透视表之后，可按需要修改数据表的显示格式。例 如，如果想要把表格中的频数替换成为百分比数。可以用鼠标右击频数 的任一单元格，选择“字段”子菜单，单击“选项”按扭，将“数据显 示方式”替换成为“占总和的百分比”，然后单击“确定”按扭即可。按同样方式，可将数据透视表修改成为其它不同样式。实验二Excel中的二项分布工具实验目的及要求掌握利用Excel的BINOMDIST的函数计算二项分布的概率以及累积 概率。实验内容及步骤例1-4：一个推销员打了六个电话，推销成功的概率是0.3,建立推 销成功次数的概率分布图表。可以按以下步骤建立推销成功次数的概率 分布图表。STEP1：如图1-9所示，先在Excel之下建立好概率分布表格的框架。图1-9STEP2：如图10所示，先在B7至F7单元格分别输入概率计算公式。AB1 C 1DEF1二项分布概率分布表2试验总次数6每次成功概率0.35概率6成功次数(k)P(Y=k)P(Y=k)P(Yk)P(Y=k)7口=BINOMDIST(A7,6,0,3,0)1|=BINOMDIST(A7,6,0,3,1)=C7-B7=1-C7=1-D7丁1921|31；4二516图1-10STEP3：公式的拷贝。选取B7至F7单元格，拖动“填充柄”至F13单元格即可完成公式的拷贝操作。结果图1-11所示。AB)CDEF1二项分布概率分布表二试验总次数6每次成功概率0.35概率6成功次数(k)P(Y=k)P(Y=k)P(Yk)P(Y=k)7口=BINOMDIST(A7,6,0.3,0)1|=BINOMDIST(A7,6,0,3,1)=C7-B7=1-C7=1-D7丁1j21 311451T6图i-nSTEP4：下面开始创建二项分布图表。选取B7至B13单元格，选取“插 入”菜单的“图表”子菜单。STEP5：选择“柱状图”，然后单击“下一步”。STEP6：单击“系列”标签，单击“分类(X)轴标志”框，并用鼠标选取 A7至A13单元格为图表X轴的轴标，然后单击“下一步”。STEP7：分别键入图表名称“二项分布图”，X轴名称“成功次数”，Y 轴名称“成功概率”，单击“完成”按扭即可生成二项分布图表。结果说明：如图1T1所示，利用Excel的BINOMDIST的函数可以计 算出二项分布的概率以及累积概率。BINOMDIST函数可以带四个参数，各参数的含义分别是：实验成功的次数，实验的总次数，每次实验中成 功的概率，是否计算累积概率。四个参数是一个逻辑值，如果为TRUE,函数BINOMDIST返回累积分 布函数，如果为FALSE,返回概率密度函数。另外，EXCEL还提供了其它分布的函数，如函数CRITBINOM；函数 HYPGEOMDIST；函数NEGBINOMDIST：函数POISSON:正态分布函数NORMDIST：函数 NORMSDIST：函数NORMSINV：t 分布函数TDIST:有兴趣的同学可以自己研究。实验三随机抽样工具实验目的及要求掌握利用Excel的BINOMDIST的函数计算二项分布的概率以及累积 概率。实验内容及步骤Excel中的Rand()函数可以返回大于等于0小于1的均匀分布随 机数，Rand()不带任何参数运行，每次计算时时都将返回一个新的数值。RAND()函数可以被用来作为不重复抽样调查的工具。例5：如图1T2所示有10个象征性的样本数据，欲从中随机抽取5个 数据可按如下步骤操作：图1-12A1No21324354657687有81091110STEP1：选择B2单元格，输入公式 JRAND0”并回车。STEP2：拖动B2单元格右下角的填充柄至B11单元格，并在B1单元格 输入标题“RANDOM”。STEP3：选取单元格B2至B11,右击选中的区域选择“复制”，再次右 击选中的区域，选择“选择性粘贴”，单击选项“数值”后，点击“确 定”按扭。STEP4：选取单元格A2至B11单元格，选择“数据”菜单项下的排序子菜单。STEP5：选取“RANDOM”为主要关键字，然后点击“确定”按扭。排序结果如图1T3所示，A2至A6单元格的样本即为随机抽取的5个样本。49 210 511 6B dom 0.2166880.2MOM 0.302M2 0.437267 0.610631 0.64232 0.656722 0.68924 0.882674 0.953918图1-13实验四由样本推断总体实验目的及要求 掌握利用Excel的几个函数，如求平均函数AVERAGE、标准差函数STDEV、T分布函数TINV等的组合使用构造一个专门用于实现样本推断总体的 Excel工作表。实验内容及步骤下面的例子先计算样本的平均数和标准差，然后在一定置信水平上 估计总体均值的区间范围。操作步骤：STEP1：构造工作表。如图14所示，首先在各个单元格输入以下的内 容，其中左边是变量名，右边是相应的计算公式。STEP2：为表格右边的公式计算结果定义左边的变量名。选定 A4:B6,A8:B8W10:B15单元格（先选择第一部分，再按住CTRL键选取 另外两个部分），选择“插入”菜单的“名称”子菜单的“指定”选项,用鼠标点击“最左列”选项，然后点击“确定”按扭即可。A|E1以样本均值推断总体均值的置信区间2 1L3样本统计量4个数=C0UNT（样本数据）5均值=AVERAGE（样本数据）6准差=STDEV 7用户输入8置信水平0.959结果10才瞬标准误一样本标血./SORT e样本个数）11自由度个数-112俏=TINV 1-J置信水平，自由度）13置信因可半径标准误14置信区间上界=样本均值置信区间半径15置信区刘F果=样本均值+置信区间半径图1-14STEP3：输入样本数据，和用户指定的置信水平0.95,如图附-13所示。STEP4：为样本数据命名。选定D1:DH单元格，选择“插入”菜单的“名 称”子菜单的“指定”选项，用鼠标点击“首行”选项，然后点击“确 定”按扭，得到图15所示的计算结果。ABCD1以样本均值推断总体均值的置信区间数据228.53样本统计量26.44个数1033.55样本均值31.434.36样本标准差_2.81424945635.97用户输入29.6S置信水平0.9531.39计算31.110才蟒标准误0.88994381830.911自932.512俏2.26215888713置信区间半径2.01319431814置信区间上界29.3868056815置信区间F界33.41319432图1T5结果说明：以上例子说明如何交叉组合使用Excel的公式和函数，以构 造出一个能实现样本推断总体有关计算的Excel工作表。实际上，在用 Excel进行数据统计处理之时，许多统计功能可以使用和上例类似的方 法，通过组合使用Excel的各类统计函数和公式加以实现的。实验五假设检验实验目的及要求掌握利用Excel的正态分布函数NORMSDIST、判断函数IF等，构造一 张能够实现在总体方差已知情况下进行总体均值假设检验的Excel工作 表。实验内容及步骤例1-6：利用Excel的正态分布函数NORMSDIST、判断函数IF等，构造 一张能够实现在总体方差已知情况下进行总体均值假设检验的Excel 工作表。操作步骤：STEP1：构造工作表。如图-16所示，首先在各个单元格输入以下的内 容，其中左边是变量名，右边是相应的计算公式。STEP2：为表格右边的公式计算结果定义左边的变量名。选定 A3:B4,A6:B8,A10:All,A13:A15 和A17:B19 单元格,选择“插入”菜 单的“名称”子菜单的“指定”选项，用鼠标点击“最左列”选项，然 后点击“确定”按扭即可。AE1X TL-kA TtA.，总体均值的吸段检验_2|3个教COUNT（样本数据）4均值=AVERAGE（样本数据）5用户输入6总体标准差7总体均值假设值8置信水平9计算结巢10才蟒标准误=总体标准差/SQRT（样本个薮）11值=（样本均值,总体均值假设值）r才蟒标准误12检检13单侧Z值=NORMSINV（1-置信水平）14结果=IF ABS 计算2值）ABS（单狈Z值）,号现Ho,接收Ho 15单侧显著水平=1-NORMSDIST ABS（计融曲）_16双侧17双侧z值=NORMSINV（1-置信水平）/2）18=IF ABS 计第值ABSC双侧Z值）月蹄Ho”,“接收Ho”）19双侧显著bk平=IF 计算Z值0,2*1-NORMSDIST C 计算2值），2+N0RMSDISTC 计算Z值）；图1-16STEP3：输入样本数据，以及总体标准差、总体均值假设、置信水平数 据。如图1-17所示。STEP4：为样本数据命名。选定C1:C11单元格，选择“插入”菜单的“名称”子菜单的“指定”选项，用鼠标点击“首行”选项，然后点击“确 定”按扭，得到如图17中所示的计算结果。AI BC1.V.休士勺伍的匿设检验|I 件一本应J居228-53样本个数1O26.44样:值31.433.55|1 用入34-3|6、体标法差C.F.C35.97在中R均值偎设值35N9.681 宿主书_O.9531.39计算结算31.11 O才由用示准汉1.75822637930.9111 位_一N.04751790932.51N单侧稔脸1 3单侧N信1.64 4:85314检脸结里15的则导者水干0.02030356216|双侧楂脸17双侧N信1.95996108218检脸结：M拒会色H。19又又1则帚督水nFO.00607125图1T7结果说明：如图17所示，该例子的检验结果不论是单侧还是双侧 均为拒绝Ho假设。所以，根据样本的计算结果，在5%的显著水平之下,拒绝总体均值为35的假设。同时由单侧显著水平的计算结果还可以看 出，在总体均值是35的假设之下，样本均值小于等于31.4的概率仅为 0.020303562o实验六双样本等均值假设检验实验目的及要求掌握利用Excel数据分析中提供双样本等均值假设检验工具进行假 设检验的方法，并能够解释实验结果。实验内容及步骤例7：双样本等均值检验是在一定置信水平之下,在两个总体方差相等 的假设之下，检验两个总体均值的差值等于指定平均差的假设是否成立 的检验。假设某工厂为了比较两种装配方法的效率，分别组织了两组员 工，每组9人，一组采用新的装配方法，另外一组采用旧的装配方法。18个员工的设备装配时间图18中表格所示。根据以下数据，是否有 理由认为新的装配方法更节约时间？A 11 BCD1组别旧方法装配时间组别法装配时间|21 11 32235313723141352295138225614123471442408135227913123210134231图1-18操作步骤：STEP1：选择“工具”菜单的“数据分析”子菜单，双击“t-检验：双 样本等方差假设”选项，则弹出图19所示对话框。图1-19STEP2：分别填写变量1的区域：$B$1:$B$1O,变量2的区域：$D$1:$D$1O,由于我们进行的是等均值的检验，填写假设平均差为0,由于数据的首行包括标志项选择标志选项，所以选择“标志”选项，再 填写显著水平。为0.05,然后点击“确定”按扭。则可以得到图1-20所 示的结果。AB1 C1卜检驶：双样本等方差假设23旧泳装配时间一新方法装配时间41|35.222222221 31.555555565方差24.4444444420.027777786值997合并方差22.236111118度设平均差09df1610t Stat1.64948461711P（T=t 单尾0.05926989912t 临界1.74588421913P（T=t 双星0.11853979914t双星临界2.1199D4821图 1-20结果分析：如图-20中所示，表中分别给出了两组装配时间的平均值、方差和样本个数。其中，合并方差是样本方差加权之后的平均值,Df是 假设检验的自由度它等于样本总个数减2,t统计量是两个样本差值减 去假设平均差之后再除于标准误差的结果，P（T=t）单尾”是单尾检 验的显著水平，”t单尾临界”是单尾检验t的临界值，“P（T=t）双 尾”是双尾检验的显著水平，气 双尾临界”是双尾检验t的临界值。由下表的结果可以看出t统计量均小于两个临界值，所以，在5%显著水 平下，不能拒绝两个总体均值相等的假设，即两种装配方法所耗时间没 有显著的不同。Excel中还提供了以下类似的假设检验的数据分析工具，它们的名称和 作用如下：“L检验：双样本异方差假设”“t-检验：成对双样本均值分析”“z-检验：双样本均值分析”实验七正态性的卡方检验实验目的及要求掌握利用Excel进行总体卡方分布检验、总体泊松分布、总体超几何分 布等其它分布的检验的方法步骤，并能够解释实验结果。实验内容及步骤卡方检验检验可以用来判断所观测的样本是否来自某一特定分布的总 体，这种检验亦称为一致性检验。例1-8：已知某样本的相关统计量和分组频数分布如图1-21所示，试用 卡方检验判断该样本是否来自一正态总体。图1-21A|BC1庄的23样本个数准差|4200 11 1641056分组下界分组上果真实频数7150158150160549160170781017018042111801112累积值200操作步骤：STEP1：创建变量名。选定A3:C4单元格,选择“插入”菜单的“名称”子菜单的“指定”选项，用鼠标点击“首行”选项，然后点击“确定”按扭即可。STEP2：计算预期正态概率值。如图1-22表中所示，在D6单元格输入标志项，在D7:D11单元格输入公式，分别计算各组的预期正态概率值,在D12计算累积概率值。_D6 湎期正态概率7=NORMDIST B7,样本均值,样本标准差,1|8 j=NQRMDIST B8,样本均值 样本标旗,1-NORMDIST（A8,样本均值 样本标准差,1 9=N0RMDIST（B9,样本均值,样本标准差,1 -NORMDI ST（A9,样本均值,样本标准差,1 10=NORMDIST（B10,样本均值 样本标准差,1-NORMDIST（A10,样床均值 样水标螺,1 11 kl-NORMDISTCAll,样本均直 样本标1睫,1 12=SUM（D7:D11 图-22STEP3：计算预期频数值。如图-23所示，在E6单元格输入标志项,在 E7:EH单元格输入公式，分别计算各组的预期频数，在E12计算累积频数值。E6 预期频数值71口7*样本个数8 1二。8*样本个数9=D9*样本个数10卜D10*样本个薮11卜D11*样本个数12 ksUM（E7:E11 图1-23STEP4：计算X2统计量。如图1-24所示，在F6单元格输入标志项，在F7:FU分别输入计算公式，分别计算X2值，在E12计算X2平方和,这项就是最后计算出的X2统计量。在E13单元格输入标志项“卡方统 计量”，为以后的引用作准备。先选中F12、F13两个单元格，选择“插 入”菜单的“名称”子菜单的“指定”选项，用鼠 标点击“尾行”选项，然后点击“确定”按扭即可。_F1234567=(C7-E7r2/E78=(C8-E8r2/E89 J=(C9-E9r2/E910=(C10-E10)*2/E1011=(C11-E11)A2/E1112 上SUM(F7:F11)13图 1-24STEP5：如图1-25所示，分别在A14至UB20单元格输入自由度、X2概率值、置信水平、临界值、X2检验结果几项的标志值及计算公式。其 中的自由度二区间分段数-正态分布参数个数T=5-2-1二2。A 1B14自由唐 115值RHIDIST（卡觥计量,自由度）1617置信水平0.0118临界值=CHIINV（置信水平自由度）19卡方检验结果=邛（卡方统计量施界他守睫总体为正太分布的假设一接受总体为正太分布的假设”）图 1-25STEP6：结果如图26所示。ABCDF|11 庄的N3个数样本标准差42001641056分组上果频数预期正态概率值7150150.0807567110.0820738150160540.2638215920.02893839160170780.3811686320.040923110170180420.2194537750.081451211180110.0547992890.00014712累积值20010.233532613卡烧卅量14自由度215卡方概率值0.8897930971617置信水平0.0118临界值9.21035103619检验结臬接受总体为正太分布的假设图 1-26结果分析：如图26所示，按照以上操作步骤可以得到表中的计算结果。按同样的方法可以作总体泊松分布、总体超几何分布等其它分布的检 验。此类统计应用也是由Excel各类公式和函数综合使用而实现的，为 了以后使用方便，和上面的一些例子一样，一般需要将整个表格的计算 框架和标志项罗列好，再保存成文件，以后只要对数据项稍作修改即可 很快得到计算结果。如果对Excel宏语言较为熟悉，还可以将它编成一 个宏语言程序，加入Excel的工具栏，这样以后使用起来更为方便。实验八列联表分析实验目的及要求列联表分析经常用来判断同一个调查的对象的两个特性之间是否存在 明显相关关系。该实验要求学生掌握利用Excel实现列联表分析的方法 步骤，并能够解释实验结果。实验内容及步骤 例1-9：如图1-27所示，表中是某装修公司的调查报告，试用列联表分 析方法分析在顾客的所在地区和所选房子的地板类型之间是否存在明 显的相关关系。ABCDI EF1列联表冽斤23真实频数地区1地区2地区3地区4教4地板72812231155钢祜地板26101633856大理石7101419507列总数105284275250图 1-27操作步骤：STEP1：建立期望频数表。如图28所示，先建立期望频数表的框架，然后在B10单元格输入公式“二B$7*$F4/$F$7”，再利用“填充柄”将 公式复制到表格的其它单元格，最后利用Excel的求和函数sum计算行和与列和。9 10 111 二木质地板 钢的地根 大理石地板 C D;!地区I 地团 地区3 地西=E$7*$FO$F$7=C$7*$“F$7=D$7*$F4/$F$7=E$7*$F$P$7=B$7*$F5/$F$7=C$7*$F5/$八 7=D$7*$F5/$F$7=E|7*|F5/1F17=C|7*$F6/$F$7=D$7*$P6/$F$7=E$7*1F6/$P$7=SU1(BIO:B12)=S0(C10:C12)=SUI(D10:D12)=SI(E10:E12)=Sin(Bl：i：E10.i=sin(Bii：Eiri=SUl5f*r.8 88 11s.Signifi耳12 回归分析 1 20621.4*20621.f：21.5T7 3.63112-0913 楼苦 18 305X09 169.61614 MJ 二_15 _16 Coefflcten号推1海荟 t-at P-valut?Lser 95%Ipper 95f 限 9员 吐属 9氏 E Intercep 0 Ml/A fN/A M/A M/A-M/A I/A Ml/A向工 Variab 1.0629，C I-n】33 1.26网图 1-41结果分析：零均值化模型的估计结果是Z=L 06284*Z(T),还原成上证指 数，最终的时间序列模型是：上证指数估计值-上证指数的平均值=1.06284(上一天上证指数-上证指数平均值)。Excel与数据统计分析统计计算与统计分析强调与计算机密切结合，Excel与数据统计分析旨在提 高学生计算机的综合运用能力，用统计方法分析问题、解决问题而编写的。根据教 材内容,也可以选择使用SPSS、QSTAT、Evievs、SAS、MINITAB等统计软件。第三章统计整理3.1计量数据的频数表与直方图例 3.1(3-1)一、指定接受区域直方图在应用此工具前，用户应先决定分布区间。否则，Excel将用一个大约等于数据 集中某数值的平方根作区间，在数据集的最大值与最小值之间用等宽间隔。如果用 户自己定义区间，可用2、5或10的倍数，这样易于分析。对于工资数据，最小值是100,最大值是298。一个紧凑的直方图可从区间100 开始，区间宽度用10,最后一区间为300结束，需要21个区间。这里所用的方法在两 端加了一个空区间，在低端是区间“1加或小于100”，高端是区间“大于300”。参考图3.3,利用下面这些步骤可得到频率分布和直方图：1.为了方便，将原始数据拷贝到新工作表“指定频数直方图”中。2.在B1单元中输入“组距”作为一标记，在B2单元中输入100,B3单元中输入 110,选取B2:B3,向下拖动所选区域右下角的+到B22单元。3.按下列步骤使用“直方图”分析工具：(1)选择叵回菜单之|数据分析|选项,在分析工具框中“直方图”。如图4所示。数据分析ax分析工具is刃刃 空.索复复 跑 一一二M h-n d7.单可无 除 分分分系差布书蛉叶程 差差差关方述数检利第 方方方相协描指IF-博每确定取消帮助QD|图3.1数据分析工具之直方图对话框1)输入输入区域：A1:A51接受区域：B1:B22(这些区间断点或界限必须按升序排列)选择标志2)输出选项输出区域：C1选定图表输出(2).单击确定Excel将计算出结果显示在输出区域中。图3.2数据分析工具之直方图对话框Excel将把频率分布和直方图放在工作表中，如图3.3所示，输出表的C和D列中 包括开始指定的界限。这些界限实际上是每一区间的上限，也就是说，界限实际上是边界。图3.3频数分布与直方图为了使图表更像传统的直方图和更易于理解，可双击图表并对它做如下修改：1.图例：因为只有一个系列的数据显示在图表中，所以不需要图例。单击图例（位 于图表右侧的“频率”）并按Delete键。2.图表区：绘图区是以X和Y轴为边界的矩形区域。通过在柱形上面单击可选取绘图 区，单击鼠标右键并选择绘图区格式，将边框改为无并将网改为无，单击所。3.条宽：在传统的直方图中，柱形是彼此相连接而不是分开的。选择某个柱形，单 击鼠标右键，选择|数据系列格式|,并单击|选择|标签,将间距宽度从150%改为0%,单击丽。4.X轴标志：选取x轴，单击鼠标右键，选择|坐标轴格司,单击|对齐|标签,将方向 从自动改为水平文本，在这种设置下，即使图表已重置尺寸，x轴标记也会变为水平 的，单击遁。最后的直方图4.6图3.4修改后的直方图直方图二、不指定接受区域直方图在进行探索性分析时，为了方便，通常不指定接受区域作直方图，步骤如下：（1）选择工阊菜单之|数据分析|选项,在分析工具框中“直方图”。如图4所示。1）输入输入区域：A1:A51接受区域：（该处为空）选择标志2）输出选项输出区域：B1选定图表输出（2）.单击福，得结果。按前面方法对直方图进行进一步修饰即得图3.5图3.5修改后的直方图3.2计数数据的透视表与条图例3.2(3-3)数据见图ABCD E12345678910工人编号1234567年龄 52 30 19 46 47 34 22技术级别63244288 9103112 3 4 5 1 1X 1X 1X 15532493434613658544474另 性文度步骤如下：(1)选择版搁菜单之|数据透视表和图表报告|选项,如图4所示。数据透视表和数据透视图向导一 3步骤之1ax三三三=-三三三三三=一=三=1清指定待分析数据的数据源类型：6那益君。荏或ceF数据潜单或数据库通i 麻苗薮花藏班r多重合并计算数据区域r另一数据透视表或数据透视图=一一 一等一所需创建的报表类型：G数据透视表数据透视图（及数据透视表）回一.选择数据源区域取消 I上一步0B 下一步 I 完成皂）|f数据透视表和数据透视图向导一 3步骤之2X谙键入或选定要建立数据透视表的数据源区域：选定区域闻:|透视表!$A$1：$E$16|司 浏览dI.|画 取消|上一步|下一步|完成一|（3）选定数据透视表位置，完成（4）将“性别”作为行字段拖至G歹U，并将“性别”作为数据拖至数据项处，得 下表结果*西为他想番m牛淳H I手段他至此处请将数据项拖至此处I数据透视表数据透视表9 也图I匿I至*11“包窗工人编号 性别 年龄 文化程度技术级别同理可得“文化程度”的透视表GHIJK计数项:性别性别 汇总文化程度 汇总男12初中8女3高中2总计15技工1文盲1小学2中专1总计15此时如点W亍图形按钮，立即得到女1口下的透视图数据透视图d)雷幽汇总I数据透视表晴将贝子段施至此处I文化程度:TMK透视图,(5)将“性别”作为行字段拖至行字段处，并将“文化程度”作为列字段拖至列字段处，将“性别”或“文化程度”作为列字段拖至数据字段处得下表结果18计数项:性别文化程度TI19性别 初中高中枝工文盲小学申吉总计20男721111221女111322总计82112115第四章总量指标和相对指标例 4.1(4-13)ABCDEF11999年2000E产量计划完成2000年实际产量2工厂实际产量计划产量实际产量%为1999年的3甲195020002200110112.824乙20202204199890.6598.915丙295030103035100.83102.88 IJ计算步骤:(1)计算各厂计划完成%E3=D3/C3*100,.(2)2000年实际产量为1999年的 F3=D3/B3*100,第五章平均指标5.1简单平均数例5.L某组有学生10人统计课考试成绩为65,82,76,80,82,86,84,88,95,98分，试求其平均指标。平均数的计算步骤如下:(1)将数据输入到A列，根据Excel提供的公式计算各种平均数AB c 1165282算术均数二AVERAGE（Al:A10）376调和均数=HARMEAN（Al:A10）480几何均数二GEOMEAN（Al:A10）582众数=MODE（A1:A10）686中位数=MEDIAN（Al:A10）7848889951098(2)用Ctrl+、可切换到下面的结果:ABCD165282算术均数84 3 176调和均数82.6014180几何均数83.11582众数82 6 186中位虹8378488899510985.2加权平均数例5.2(5-1)原始数据见下图A-D列，其中A、B列放日产量的下限和上限ABCDEFG1日产量(公斤)工人数f组中值总产量xf2L-U4月份fl5月份f2X4月份xfl5月份xf2310202010153001504203035202587550053040302535105087564050103045450135075060515552758258合计100100175295037009均数29.53710众数27.542.511中位数28.5738.00平均数的计算步骤如下：(1)计算日产量的