《社会经济调查与写作》实训指导书一(excel部分).doc

社会经济调查与写作实训指导书一(excel部分) 1.用用EXCEL搜集数据搜集数据的方法有多种，可以采用统计报表、典型调查、重点调查或抽样调查。 我国的统计调查将以抽样为主，在这里介绍一下如何用EXCEL进行抽样。 使用EXCEL进行抽样，首先要对各个总体单位进行编号，编号可以按随机原则，也可以按有关标志或无关标志，编号后，将编号输入工作表。 【例例1】假定有100个总体单位，每个总体单位给一个编号，共有从1到100个编号，输入工作表后如图1-1所示图图1-1总体各单位编号表输入各总体单位的编号后，可按以下步骤操作第一步单击工具菜单，选择数据分析选项（若无数据分析选项，可在工具菜单下选择加载宏，在弹出的对话框中选择分析工具库，便可出现数据分析选项），打开数据分析对话框，从中选择抽样，如图1-2所示图图1-2数据分析对话框第二步单击抽样选项，弹出抽样对话框。 如图1-3图图1-3抽样对话框第三步在输入区域框中输入总体单位编号所在的单元格区域，在本例是$A$1:$J$10，系统将从A列开始抽取样本，然后按顺序抽取B列至J列。 如果输入区域的第一行或第一列为标志项（横行标题或纵列标题），可单击标志复选框。 第四步在抽样方法项下，有周期和随机两种抽样模式“周期”模式即所谓的等距抽样，采用这种抽样方法，需将总体单位数除以要抽取的样本单位数，求得取样的周期间隔。 如要在100个总体单位中抽取12个，则在“间隔”框中输入8。 “随机模式”适用于纯随机抽样、分类抽样、整群抽样和阶段抽样。 采用纯随机抽样，只需在“样本数”框中输入要抽取的样本单位数即可；若采用分类抽样，必须先将总体单位按某一标志分类编号，然后在每一类中随机抽取若干单位，这种抽样方法实际是分组法与随机抽样的结合；整群抽样也要先将总体单位分类编号，然后按随机原则抽取若干类作为样本，对抽中的类的所有单位全部进行调查。 可以看出，此例的编号输入方法，只适用于等距抽样和纯随机抽样。 第四步指定输出区域，在这里输入$K$1，单击确定后，即可得到抽样结果，如图1-4所示。 图图1-4等距抽样结果2.用用EXCEL进行统计分组用EXCEL进行统计分组有两种方法，一是利用FREQUENCY函数；二是利用数据分析中的“直方图”工具，这里主要介绍后者的操作方法。 【例例2】将50名工人的月产量资料输入工作表，如图1-5所示。 图图1-5工人月产量资料然后按以下步骤操作第一步在工具菜单中单击数据分析选项，从其对话框的分析工具列表中选择直方图，打开直方图对话框，如图1-6所示。 图图1-6直方图对话框第二步在输入区域输入$A$1:$J$5，在接收区域输入$A$9:$A$15。 接收区域指的是分组标志所在的区域，假定我们把分组标志输入到A9:A15单元格，注意这里只能输入每一组的上限值，即130，160，190，220，250，280，310。 第三步选择输出选项，可选择输入区域、新工作表组或新工作薄。 我们在这里选择输入区域，可以直接选择一个区域，也可以直接输入一个单元格（代表输出区域的左上角），这里我们推荐只输入一个单元格（本例为A7），因为我们往往事先并不知道具体的输出区域有多大。 第四步选择图表输出，可以得到直方图；选择累计百分率，系统将在直方图上添加累积频率折线；选择柏拉图，可得到按降序排列的直方图。 第五步按确定按钮，可得输出结果如图1-7。 图图1-7频数分布和直方图应当注意，上图实际上是一个条形图，而不是直方图，若要把它变成直方图，可按如下操作用鼠标左键单击任一直条，然后右键单击，在弹出的快捷菜单中选取数据系列格式，弹出数据系列格式对话框，如图1-8所示。 图图1-8数据系列格式对话框在对话框中选择选项标签，把间距宽度改为0，按确定后即可得到直方图，如图1-9所示。 图图1-9调整后的直方图3.用用EXCEL作统计图EXCEL提供的统计图有多种，包括柱形图、条形图、折线图、饼图、散点图、面积图、环形图、雷达图、曲面图、气泡图、股价图、圆柱图、圆锥图等，各种图的作法大同小异。 【例例3】这里学习如何作饼图，首先把数据输入到工作表中，如图1-10所示图图1-10河北省主要市xx年人口情况按以下步骤可作出饼图第一步选中某一单元格，单击插入菜单，选择图表选项，弹出图表向导对话框，如图1-11。 图图1-11图表向导对话框第二步在图表类型中选择饼图，然后在子图表类型中选择一种类型，这里我们选用系统默认的方式。 然后单击下一步按钮，打开源数据对话框，如图1-12所示。 图图1-12源数据对话框第三步在源数据对话框中填入数据所在区域，单击完成按钮，即可得如1-13所示的饼图。 石家庄市唐山市秦皇岛市保定市邯郸市邢台市沧州市张家口市图1-13饼图实训二用用EXCEL计算描述统计量实验目的用EXCEL计算描述统计量。 EXCEL中用于计算描述统计量的方法有两种，函数方法和描述统计工具的方法。 1.用函数计算描述统计量常用的描述统计量有众数、中位数、算术平均数、调和平均数、几何平均数、极差、四分位差、标准差、方差、标准差系数等。 一般来说，在EXCEL中求这些统计量，未分组资料可用函数计算，已分组资料可用公式计算。 这里我们仅介绍如何用函数计算。 （1）众数【例4】为了解某经济学院新毕业大学生的工资情况，随机抽取30人，月工资如下156013401600141015901410161015701710155014901690138016801470153015601250156013501560151015501460155015701980161015101440用函数方法求众数，应先将30个人的工资数据输入A1A30单元格，然后单击任一空单元格，输入“=MODE（A1A30）”，回车后即可得众数为1560。 （2）中位数仍采用上面的例子，单击任一空单元格，输入“=MEDIAN（A1A30）”，回车后得中位数为1550。 （3）算术平均数单击任一单元格，输入“=AVERAGE（A1A30）”，回车后得算术平均数为1535。 （4）标准差单击任一单元格，输入“=STDEV（A1A30）”，回车后得标准差为135.0287。 2.描述统计工具量的使用仍使用上面的例子，我们已经把数据输入到A1A30单元格，然后按以下步骤操作第一步在工具菜单中选择数据分析选项，从其对话框中选择描述统计，按确定后打开描述统计对话框，如图1-14所示。 图1-14描述统计对话框第二步在输入区域中输入$A$1:$A$30，在输出区域中选择$C$1，其他复选框可根据需要选定，选择汇总统计，可给出一系列描述统计量；选择平均数置信度，会给出用样本平均数估计总体平均数的置信区间；第K大值和第K小值会给出样本中第K个大值和第K个小值。 第三步单击确定，可得输出结果，如图1-15所示。 图1-15描述统计输出结果上面的结果中，平均指样本均值；标准误差指样本平均数的标准差；中值即中位数；模式指众数；标准偏差指样本标准差，自由度为n-1；峰值即峰度系数；偏斜度即偏度系数；区域实际上是极差，或全距。 实训三用用EXCEL进行参数估计和假设检验实验目的用EXCEL进行参数估计和假设检验在EXCEL中，进行参数估计只能使用公式和函数的方法，而假设检验除以上两种方法外，还可以使用假设检验工具。 1.用用EXCEL进行区间估计【例5】某饭店在7星期内抽查49位顾客的消费额（元）如下15243826304218302526344420352426344818284619303642243245362147262831424536242827323647532224324626求在概率90%的保证下，顾客平均消费额的估计区间，如图1-16所示。 图1-16参数估计数据及结果计算方法如下第一步把数据输入到A2A50单元格。 第二步在C2中输入公式“=COUNT（A2A50）”，C3中输入“=AVERAGE（A2A50）”，在C4中输入“STDEV（A2A50）”，在C5中输入“=C4/SQRT（C2）”，在C6中输入0.90，在C7中输入“=C2-1”，在C8中输入“=TINV（1-C6，C7）”，在C9中输入“=C8*C5”，在C10中输入“=C3-C9”，在C11中输入“=C3+C9”。 在输入每一个公式回车后，便可得到上面的结果，从上面的结果我们可以知道，顾客平均消费额的置信下限为29.73536，置信上限为34.26464。 关于总体方差的估计、总体比例的估计等可按类似方法进行。 2.用用EXCEL进行假设检验用EXCEL函数和公式进行假设检验与参数估计类似，大家可以自已做一下，这里介绍一下假设检验工具的使用。 在EXCEL中，假设检验工具主要有四个，如图1-17所示。 图1-17数据分析对话框平均值的成对二样本分析实际上指的是在总体方差已知的条件下两个样本均值之差的检验，准确的说应该是Z检验，双样本等方差检验是总体方差，但假定其相等的条件下进行的t检验，双样本异方差检验指的是总体方差，但假定其不等的条件下进行的t检验，双样本平均差检验指的是配对样本的t检验。 这里介绍一下Z检验。 【例6】某厂铸造车间为提高缸体的耐磨性而试制了一种镍合金铸件以取代一种铜合金铸件，现从两种铸件中各抽一个样本进行硬度测试（表示耐磨性的一种考核指标）其结果如下合镍铸件（X）72.069.574.070.571.872合铜铸件（Y）69.870.072.068.573.070.0根据以往经验知硬度()211,N X，()222,N Y，且221=，试在05.0=水平上比较镍合金铸件硬度有无显著提高。 计算步骤如下第一步输入数据到工作表。 第二步单击工具菜单，选择数据分析选项，弹出对话框后，在其中选择双样本平均差分析，弹出对话框如图1-18所示。 图1-18双样本平均差分析对话框第三步按上图所示输入后，按确定按钮，得输出结果如图1-19。 图1-19双样本平均差分析结果在上面的结果中，我们可以根据P值进行判断，也可以根据统计量和临界值比较进行判断。 如本例采用的是单尾检验，其单尾P值为0.17，大于给定的显著性水平0.05，所以应该接受原假设，即镍合金铸件硬度没有明显提高；若用临界值判断，得出的结论是一样的，如本例Z值为0.938194，小于临界值1.644853，由于是右尾检验，所以也是接受原假设。 实训四用用EXCEL进行相关与回归分析实验目的用EXCEL进行相关与回归分析采用下面的例子进行相关和回归分析。 【例7】10个学生身高和体重的情况如下表，要求对身高和体重作相关和回归分析。 学生身高（公分）体重（公斤）12345678910171167177154169175163152172160535664495566524758501.用用EXCEL进行相关分析首先把有关数据输入EXCEL的单元格中，如图1-20所示。 用EXCEL进行相关分析有两种方法，一是利用相关系数函数，另一种是利用相关分析宏。 图1-20EXCEL数据集 （1）利用函数计算相关系数在EXCEL中，提供了两个计算两个变量之间相关系数的方法，CORREL函数和PERSON函数，这两个函数是等价的，这里介绍用CORREL函数计算相关系数第一步单击任一个空白单元格，单击插入菜单，选择函数选项，打开粘贴函数对话框，在函数分类中选择统计，在函数名中选择CORREL，单击确定后，出现CORREL对话框。 第二步在array1中输入B2B11，在array2中输入C2C11，即可在对话框下方显示出计算结果为0.896，如图1-21所示。 图1-21CORREL对话框及输入结果 （2）用相关系数宏计算相关系数第一步单击工具菜单，选择数据分析选项，在数据分析选项中选择相关系数，弹出相关系数对话框，如图1-22所示图1-22相关系数对话框第二步在输入区域输入$B$1:$C$1，分组方式选择逐列，选择标志位于第一行，在输出区域中输入$E$1，单击确定，得输出结果如图1-23所示。 图1-23相关分析输出结果在上面的输出结果中，身高和体重的自相关系数均为1，身高和体重的相关系数为0.896，和用函数计算的结果完全相同。 2.用用EXCEL进行回归分析EXCEL进行回归分析同样分函数和回归分析宏两种形式，其提供了9个函数用于建立回归模型和预测。 这9个函数分别是INTERCEPT返回线性回归模型的截距SLOPE返回线性回归模型的斜率RSQ返回线性回归模型的判定系数FORECAST返回一元线性回归模型的预测值STEYX计算估计的标准误TREND计算线性回归线的趋势值GROWTH返回指数曲线的趋势值LINEST返回线性回归模型的参数LOGEST返回指数曲线模型的参数用函数进行回归分析比较麻烦，我们这里介绍使用回归分析宏进行回归分析。 第一步单击工具菜单，选择数据分析选项，出现数据分析对话框，在分析工具中选择回归，如图1-24所示。 图1-24数据分析对话框第二步单击确定按钮，弹出回归对话框，在Y值输入区域输入$B$2$B$11，在X值输入区域输入$C$2$C$11，在输出选项选择新工作表组，如图1-25所示。 图1-25回归对话框第三步单击确定按钮，得回归分析结果如图1-26所示。 图1-26EXCEL回归分析结果在上面的输出结果中，第一部分为汇总统计，MultipleR指复相关系数，RSquare指判定系数，Adjusted指调整的判定系数，标准误差指估计的标准误，观测值指样本容量；第二部分为方差分析，df指自由度，SS指平方和，MS指均方，F指F统计量，Significance ofF指p值；第三部分包括Intercept指截距，Coefficient指系数，tstat指t统计量。 实训五用用EXCEL进行时间序列和指数分析实验目的用EXCEL进行时间序列和指数分析季节因素是时间序列非常重要的一个成分，指数分析法是研究社会经济现象数量变动情况的一种统计分析法。 在这一部分介绍如何用EXCEL进行季节指数的计算和指数分析。 1.用用EXCEL计算季节指数【例8】煤炭集团xxxx年煤炭销售量资料如下单位亿吨季度年度xxxxxxxx1季度4.785.186.466.822季度3.974.615.625.683季度5.075.696.967.384季度5.125.717.127.40试用按季平均法测定该数列的季节指数。 所计算的季节指数，如图1-27所示。 图1-27用EXCEL计算季节指数及结果计算步骤第一步计算各行平均数在F2中输入“=Average（B2:E2）”，并用鼠标拖曳将公式复制到F2F4区域。 第二步计算各列平均数有B6中输入“=Average（B2:B5）”，并用鼠标拖曳将公式复制到B6F6区域。 第三步计算季节指数在G2中输入“=F2/$G$6”，并用鼠标拖曳将公式复制到G2G6区域。 这样最后一列得到的就是按照平均法测定的季节指数。 注意在输入公式的时候，不要忘记等号，否则就不会