统计学实验实习指导书.docx

10 Excel 在统计学中的应用10.1 用Excel 搜集与整理数据10.1.1 用 Excel 搜集数据统计数据的收集是统计工作过程的根底性环节，方法有多种多样，其中以抽样调查最有 代表性。在抽样调查中，为保证抽样的随机性，需要取得随机数字，所以我们在这里介绍一 下如何用Excel 生成随机数字并进展抽样的方法。需要提示的是，在使用 Excel 进展实习前， 电脑中的 Excel 需要完全安装，所以局部同学电脑中的office 软件需要重安装，否则实习无法正常进展。本书中例题全部用Excel2022 完成。使用 Excel 进展抽样，首先要对各个总体单位进展编号，编号可以按随机原则，也可以按有关标志或无关标志，具体可参见本书有关抽样的章节，编号后，将编号输入工作表。【例 10-1】我们假定统计总体有 200 个总体单位，总体单位的编号从 1 到 200，输入工作表后如图 10-1 所示：图 10-1 总体各单位编号表各总体单位的编号输入完成后，可按以下步骤进展操作：第一步：选择数据分析选项假设你使用的是Excel2022，单击工具菜单，假设很多据分析选项，可在工具菜单下选择加载宏，在弹出的对话框中选择分析工具库，便可消灭数据分析选项；假设你使用的是Excel2022，点击左上角Office 标志图标，Excel 选项，加载项， 在下面的治理下拉列表中选择“Excel 加载项”，转到，勾选“分析工具库”，确定。，翻开数据分析对话框，从中选择抽样。如图10-2 所示。图 10-2 数据分析对话框其次步：单击抽样选项，确定后弹出抽样对话框。如图10-3：图 10-3 抽样对话框第三步：在输入区域框中输入总体单位编号所在的单元格区域，在本例是$A$1:$J$20， 系统将从A 列开头抽取样本，然后按挨次抽取 B 列至J 列。假设输入区域的第一行或第一列为标志项横行标题或纵列标题，可单击标志复选框。第四步：选择“随机模式”，样本数为 10。在抽样方法项下，有周期和随机两种抽样模式。 “周期”模式即所谓的等距抽样或机械抽样，承受这种抽样方法，需将总体单位数除以要抽取的样本单位数，求得取样的周期间隔。如我们要在 200 个总体单位中抽取 10 个， 则在“间隔”框中输入 20；假设在 200 个总体单位中抽取 24 个，则在“间隔”框中输入 8假设不能整除，则取整数。 “随机模式”适用于纯随机抽样、分类抽样、整群抽样和阶段抽样。承受纯随机抽样，只需在“样本数”框中输入要抽取的样本单位数即可；假设承受分类抽样，必需先将总体单位按某一标志分类编号，然后在每一类中随机抽取假设干单位，这种抽样方法实际是分组法与随 机抽样的结合；整群抽样也要先将总体单位分类编号，然后按随机原则抽取假设干类作为样本， 对抽中的类的全部单位全部进展调查。由于本例中没有分类或分群的标志，所以无法进展分 类或整群抽样，只适用于等距抽样和纯随机抽样。本例中选择“随机模式”，即选择了纯随机抽样方法进展抽样。第四步：指定输出区域，在这里我们输入$A$22：$A$31，单击确定后，即可得到抽样结果，如图 10-4：图 10-4 等距抽样结果10.1.2 用 Excel 进展统计分组用 Excel 进展统计分组有两种方法，一是利用FREQUENCY 函数；二是利用数据分析中的“直方图”工具。我们介绍一下后者的操作方法。【例 10-2】某生产车间 50 名工人日加工零件数单位：个资料输入工作表，如图 10-5所示：图 10-5 工人月产量资料然后按以下步骤操作。第一步：分组标志输入。分组前要先输入分组标志，选定一个区域直方图菜单中称这 个区域为接收区域，输入每一组的上限值，即在$A$7:$A$13 位置输入 110，115，120，125， 130，135，140。其次步：单击数据分析选项，从其对话框的分析工具列表中选择直方图，翻开直方图对话框。如图 10-6 所示：图 10-6 直方图对话框第三步：在输入区域输入$A$1:$J$5，输入区域指等分析数据的单元格区域。在接收区 域输入$A$7:$A$13。接收区域指的是分组标志所在的区域，即第一步输入的分组标志上限值。第四步：选择输出选项，可选择输入区域、工作表组或工作薄。我们在这里选择输入区域，可以直接选择一个区域，也可以直接输入一个单元格代表输出区域的左上角，这里我们推举只输入一个单元格本例为 A16，由于我们往往事先并不知道具体的输出区域有多大。第五步：选择图表输出，可以得到直方图；选择累计百分率，系统将在直方图上添加累 积频率折线；选择柏拉图，可得到按降序排列的直方图本例中，由于没有选择柏拉图，所以不是降序排列。第五步：按确定按钮，可得输出结果如图10-7：图 10-7 频数分布和直方图应当留意，上图实际上是一个条形图，而不是直方图，假设要把它变成直方图，可按如下操作：用鼠标左键单击直条，然后右键单击，在弹出的快捷菜单中选取“设置数据系列格式”，弹出数据系列格式对话框，如图 10-8 所示：图 10-8 数据系列格式对话框在对话框中将分类间距设置成 0%，按关闭后即可得到直方图，如图10-9 所示：图 10-9 调整后的直方图10.1.3 用 Excel 作统计图Excel 供给的统计图有多种，包括柱形图、条形图、折线图、饼图、散点图、面积图、环形图、雷达图、曲面图、气泡图、股价图、圆柱图、圆锥图等，各种图的作法大同小异。【例 10-3】我们这里承受浙江省2022 年各市GDP 资料，看一下如何作饼图。首先把数据输入到工作表中，如图 10-10 所示：图 10-10 浙江省 2022 年各市 GDP按以下步骤可作出饼图：第一步：选中某一单元格，单击插入菜单，Excel2022 菜单显示如图 10-11：图 10-11 插入图表菜单1其次步：在图表类型中选择饼图，再选择二维饼图，如图10-12 所示：图 10-12 插入图表菜单2第三步：单击选择数据菜单，在图表数据输入区域D中输入$A$1:$B$12 选择在源数据对话框中填入数据所在区域，如图10-13：图 10-13 源数据对话框第四步：单击确定后得到如下饼图，如图10-14 所示：图 10-14 饼图10.1.4 用 Excel 作散点图散点图是观看两个变量之间关系程度最为直观的工具之一，利用EXCEL 的图表向导， 可以格外便利的创立并且改进一个散点图，也可以在一个图表中同时显示两个以上变量之间的散点图。【例 10-4】我们这里承受我国 1981 到 2022 年的人均 GDP 和人均居民消费资料，看一下如何作散点图。首先把数据输入到工作表中，如图10-15 所示：图 10-15 我国 1981 到 2022 年的人均 GDP 和人均居民消费资料具体步骤如上例子相像，选择插入菜单，选择散点图，得散点图如图10-16：图 10-16 散点图10.2 用EXCEL 计算描述统计量EXCEL 中用于计算描述统计量的方法有两种，函数方法和描述统计工具的方法。10.2.1 用函数计算描述统计量常用的描述统计量有众数、中位数、算术平均数、调和平均数、几何平均数、极差、 四分位差、标准差、方差、标准差系数等。一般来说，在Excel 中求这些统计量，未分组资料可用函数计算，已分组资料可用公式计算。这里我们仅介绍如何用函数计算。【例 10-5】：某生产车间 50 名工人日加工零件数单位：个输入表格后如图 10-17：图 10-17 工人日加工零件数依据上面表格中的数据，利用函数求一些统计指标。(1) 算术平均数单击任一单元格，输入“ =AVERAGE(A1:J5) ”，回车后得算术平均数为122.74 。(2) 众数单击任一空单元格，输入“=MODE(A1:J5) ”，回车后即可得众数为123(3) 中位数仍承受上面的例子，单击任一空单元格，输入“=MEDIAN A1 ： J5 ”，回车后得中位数为 123 。(4) 标准差单击任一单元格，输入“ =STDEV A1 ： J5 ”，回车后得标准差为7.74 。10.2.2 描述统计工具量的使用仍使用上面的例子，我们已经把数据输入到A1 ：A30 单元格，然后按以下步骤操作：第一步：数据菜单中选择数据分析，从其对话框中选择描述统计，按确定后翻开描述统计对话框，如图10-18 所示：图 10-18 描述统计对话框其次步：在输入区域中输入$A$1:$J$5，在输出区域中选择$B$14，其他复选框可依据需要选定，选择汇总统计，可给出一系列描述统计量；选择平均数置信度，会给出用样本平均 数估量总体平均数的置信区间；第 K 大值和第K 小值会给出样本中第K 个大值和第K 个小值。分组方式选择“逐行”，则每行分别进展统计，这里有5 行，则有 5 组统计结果。第三步：单击确定，可得输出结果，如图10-19 所示：图 10-19 描述统计输出结果10.3 用EXCEL 进展时间序列分析10.3.1 测定增长量和平均增长量【例 10-6】：依据我国1998-2022 年国内生产总值，计算逐期增长量、累计增长量和平均增长量。原始数据如图 10-20，计算结果如图 10-21。图 10-20 我国 1998-2022 年国内生产总值计算步骤如下：第一步：计算逐期增长量：在 C3 中输入公式：=B3-B2，并用鼠标拖曳将公式复制到 C3： C11 区域。其次步：计算累计增长量：在 D3 中输入公式：=B3-$B$2，并用鼠标拖曳公式复制到 D3： D11 区域。第三步：计算平均增长量水平法：在 C13 中输入公式：=B11-B2/9，按回车键，即可得到平均增长量。图 10-21 用 EXCEL 计算增长量和平均增长量资料及结果10.3.2 测定进展速度和平均进展速度【例 10-7】：以我国1998-2022 年国内生产总值为例，说明如何计算定基进展速度、环比进展速度和平均进展速度。如图 10-22：图 10-22 用 EXCEL 计算进展速度和平均进展速度资料及结果第一步：计算定基进展速度：在C3 中输入公式：=B3/$B$2，并用鼠标拖曳将公式复制到 C3：C11 区域。其次步：计算环比进展速度：在D3 中输入公式：=B3/B2，并用鼠标拖曳将公式复制到D3：D11 区域。第三步：计算平均进展速度水平法：选中 D13 单元格，单击插入菜单，选择函数选项，消灭插入函数对话框后，选择 GEOMEAN返回几何平均值函数，在数值区域中输入 D3： D11 即可。10.3.3 计算长期趋势【例 10-8】：我们用我国 19882022 年国内生产总值资料来说明如何用移动平均法计算长期趋势。如图 10-23：图 10-23 用 EXCEL 计算长期趋势资料及结果第一步：计算三项移动平均：在C3 中输入“=B2+B3+B4/3”，并用鼠标拖曳将公式复制到C4：C20 区域。其次步：计算四项移动平均：在D4 中输入“=SUMB2：B5/4”，并用鼠标拖曳将公式复制到D5：D20 区域。第三步：计算二项移正平均数：在E4 中输入“=D4+D5/2”，并用公式拖曳将公式复制到 E5：E19 区域。10.3.4 计算季节变动【例 10-9】：利用某种商品四年分季度的销售额资料，说明如何用移动平均趋势剔除法测定季节变动。如图 10-24：图 10-24 用 EXCEL 计算季节变动资料第一步：按图上的格式在A 列输入年份，在B 列输入季别，在C 列输入销售额。其次步：计算四项移动平均：在D3 中输入“=SUMC2：C4/4”，并用鼠标拖曳将公式复制到D3：D15 区域。第三步：计算趋势值即二项移动平均T：在 E4 中输入“=D3+D4/2”，并用鼠标拖曳将公式复制到E4：E15 区域。第四步：剔除长期趋势，即计算Y/T：在F4 中输入“=C4/E4”，并用鼠标拖曳将公式复制到 F4：F15 区域。第五步：重排列F4：F15 区域中的数字，使同季的数字位于一列，共排成四列。见图 3-6。第六步：计算各年同季平均数：在 B25 单元格中输入公式：=average(B22:B24)；在 C25中输入公式=average(C22:C24)；在 D25 中输入公式=average(D21:23)；在 E25 中输入公式=average(E21:E23)。第七步：计算调整系数：在B27 中输入公式：=4/sum(B25:E25)第八步：计算季节比率：在 B26 中输入公式：=B25*$B$27，并用鼠标拖曳将公式复制到单元格区域B26：E26，就可以得到季节比率的值，具体结果见图10-25：图 10-25 用 EXCEL 计算季节变动结果10.4 用EXCEL 进展指数分析10.4.1 用 Excel 计算总指数【例 10-10】：设某粮油连锁店 1998 年和 1999 年三种商品的零售价格和销售量资料如图 10-26。试分别以基期销售量和零售价格为权数，计算三种商品的价格综合指数和销售量综合指数。图 10-26 用 EXCEL 计算总指数资料及结果计算步骤：第一步：计算各个p0q0：在 G3 中输入“=C3*E3”，并用鼠标拖曳将公式复制到 G3：G5区域。其次步：计算各个p0*q1：有H3 中输入“=D3*E3”，并用鼠标拖曳将公式复制到H3：H5区域。第三步：计算各个p1*q1：有I3 中输入“=D3*F3”，并用鼠标拖曳将公式复制到I3：I5区域。第四步：计算 p0q0 和 p0q1：选定G3：G5 区域，单击工具栏上的“ ”按钮，在G6 消灭该列的求和值。选定H3：H5 区域，单击工具栏上的“ ”按钮，在H6 消灭该列的求和值。选定HI：I5 区域，单击工具栏上的“ ”按钮，在I6 消灭该列的求和值。第五步：计算生产量综合指数Kq= p0q1/ p0q0：在B8 中输入“=H6/G6”便可得到生产量综合指数。第六步：计算价格综合指数Kp= p1q1/ p0q1：在B9 中输入“=I6/H6”便可得到价格综合指数。留意：在输入公式的时候，不要遗忘等号，否则就不会消灭数值。10.4.2 用 Excel 计算平均指数现以生产量平均指数为例，说明加权算术平均法的计算方法。【例 10-11】：仍以上例资料，计算生产量平均指数和价格平均指数。如图10-27。图 10-27 用 EXCEL 计算平均指数资料及结果计算步骤：第一步：计算个体指数 k=q1/q0：在I3 中输入“=D3/C3”。并用鼠标拖曳将公式复制到 I3：I5 区域。其次步：计算k*p0q0 并求和。在K3 中输入“=I3*G3”并用鼠标拖曳将公式复制到K3： K5 区域。选定K3：K5 区域，单击工具栏上的：“ ”按钮，在K6 列消灭该列的求和值。第三步：计算生产量平均指数：在B7 中输入“=K6/G5”即得到所求的值。价格平均指数计算步骤略。10.5 用EXCEL 进展相关与回归分析【例 10-12】：中国居民人均消费支出与人均 GDP元/人如表 10-1 所示： 表 10-1 中国居民人均消费支出与人均GDP计量单位：元年份人均居民消费人均 GDPCONSPGDPP1978395.8675.11979437716.91980464.1763.71981501.9792.41982533.5851.11983572.8931.41984635.61059.219857161185.21986746.51269.61987788.31393.61988836.415271989779.71565.91990797.11602.31991861.41727.21992966.61949.819931048.62187.919941108.72436.119951213.12663.719961322.82889.119971380.93111.919981460.63323.119991564.43529.320221690.83789.710.5.1 用 EXCEL 进展相关分析首先把有关数据输入 EXCEL 的单元格中。用 EXCEL 进展相关分析有两种方法，一是利用相关系数函数，另一种是利用相关分析宏。（1） 利用函数计算相关系数在 EXCEL 中，供给了两个计算两个变量之间相关系数的方法，CORREL 函数和 PERSON函数，这两个函数是等价的，这里我们介绍用CORREL 函数计算相关系数：第一步：单击任一个空白单元格，单击插入菜单，选择函数选项，翻开粘贴函数对话 框，在函数分类中选择统计，在函数名中选择CORREL，单击确定后，消灭CORREL 对话框。图 10-27 EXCEL 数据集其次步：在array1 中输入B4：B26，在array2 中输入C4：C26，即可在对话框下方显示出计算结果为 0.996。如图 10-28 和图 10-29 所示：图 10-28CORREL 对话框及输入结果图 10-29 EXCEL 数据集（2） 用相关系数宏计算相关系数第一步：单击数据菜单，单击数据分析按钮，在数据分析选项中选择相关系数，弹出相关系数对话框，如图 10-30 和图 10-31 所示：图 10-30EXCEL 数据集图 10-31 相关系数对话框其次步：在输入区域输入$B$3:$C$26，分组方式选择逐列，选择标志位于第一行，在输出区域中输入$E$5，单击确定，得输出结果如图10-32：图 10-32 相关分析输出结果在上面的输出结果中，身高和体重的自相关系数均为1，身高和体重的相关系数为0.996，和用函数计算的结果完全一样。10.5.2 用 EXCEL 进展回归分析EXCEL 进展回归分析同样分函数和回归分析宏两种形式，其供给了 9 个函数用于建立回归模型和推测。这 9 个函数分别是：INTERCEPT 返回线性回归模型的截距SLOPE 返回线性回归模型的斜率RSQ 返回线性回归模型的判定系数FORECAST 返回一元线性回归模型的推测值STEYX 计算估量的标准误TREND 计算线性回归线的趋势值GROWTH 返回指数曲线的趋势值LINEST 返回线性回归模型的参数LOGEST 返回指数曲线模型的参数用函数进展回归分析比较麻烦，我们这里介绍使用回归分析宏进展回归分析。第一步：单击工具菜单，选择数据分析选项，消灭数据分析对话框，在分析工具中选择回归，如图 10-33：图 10-33 数据分析对话框其次步：单击确定按钮，弹出回归对话框，在Y 值输入区域输入$B$2：$B$11，在X 值输入区域输入$C$2：$C$11，在输出选项选择工作表组，如图10-34 所示：图 10-34 回归对话框第四步：单击确定按钮，得回归分析结果如图10-35 所示图 10-35 EXCEL 回归分析结果在上面的输出结果中，第一局部为汇总统计，MultipleR 指复相关系数，R Square 指判定系数，Adjusted 指调整的判定系数，标准误差指估量的标准误，观测值指样本容量；其次局部为方差分析，df 指自由度，SS 指平方和，MS 指均方，F 指 F 统计量，Significance of F 指 p 值；第三局部包括：Intercept 指截距，Coefficient 指系数，t stat 指 t 统计量。