实验1 数据整理与数据分析
1.1 数据整理
一、实验目的和要求：
能熟练的进行统计数据的录入、分组、汇总及各种常用统计图表的绘制。
二、实验内容：
1、数据的录入
2、数据的排序
3、数据的分组
4、数据透视分析
5、常用统计图表的绘制
三、统计函数
频数分布函数 （FREQUENCY ）的语法形式为:
FREQUENCY(data_array,bins_array)
其中：Data_array为用来编制频数分布的数据，Bins_array为频数或次数的接收区间。
四、实验步骤：
1、数据的录入
数据的录入是指把调查得到的结果输入到一张叫数据清单的EXCEL 工作表中。数据清单是指包含相关数据的一系列工作表的数据行，如发货单数据库，或一组客户名称和联系电话。数据清单可以作为数据库使用，其中行表示记录，列表示字段。
例 某集团公司欲在某地区投资于医疗卫生事业，为了减少风险，获得利润，该集团企划部门决定先了解一下市场潜力。企划部王经理随机访问了该地区几家医院中就医的36名患者，询问其等候看病的时间，根据这些数据，王经理会得到什么信息呢？ 打开EXCEL 工作表，在列中输入数据的名称，称为变量。然后依次输入相应调查数据。
2、数据的排序
①打开 “数据整理.xls” 工作簿，选定“等候时间”工作表。
②利用鼠标选定单元格A1:B37区域
③在菜单中选择“数据”中的“排序”选项，则弹出排序对话框。
④在排序对话框窗口中，选择“主要关键字”列表中的“等候时间”作为排序关键字，并选择按“递增”排序。由于所选取数据中已经包含标题，所以在“当前数据清单”中选择“有标题行”，然后单击“确定”按钮，即可得到排序的结果。
在数据清单中使用分类汇总的方法如下：
先选择需要分类汇总的数据区域A1：B37，然后选择“数据”菜单中的“分类汇总”选项，则打开“分类汇总”对话框。在“分类字段”的下拉式列表中选择要进行分类的列标题，在“汇总方式”的下拉式列表中选择行汇总的方式，本例中选择按“等候时间”进行分类，
选择按“计数”进行汇总，单击“确定”按钮，便得到分类汇总的结果。在对数据进行了分类汇总之后，可以观察到等候时间为1分钟的人次为1，等候时间为3分钟的人次为2等等。
尽管分类汇总可以给出各个数值所出现的次数，但从这些次数中还是无法得出其数据分布的规律性。这是因为没有将数值相近的数据进行合并，因而不能理想地抽象概括现象的数量特征。
3、数据的分组
利用Excel 频数分布函数进行分组建立频数分布的步骤为：先对数据进行排序，以了解全部数据的变动范围；然后选择全部数据的分组组数；再确定分组的组限，最后对各组数值所出现的频数进行计数。
①打开“数据整理.xls”工作簿，选择“等候时间”工作表。
②在单元格C2中输入“分组”，在单元格D2中输入“频数”。
③在C3：C8区域中依次输入10，20，30，40，50，60，作为频数接受区域，它们分别表明等时间在10分钟以下的人数，等候时间在10分钟以上，20分钟以下的人数等。
④选定D3：D8区域，在“插入”菜单中单击“函数” 选项，打开“粘贴函数”对话框 ⑤在“函数分类”列表中选择“统计”，在“函数名”列表中选择“FREQUENCY”，单击“确定”按钮，Excel 弹出“频数分布”对话窗口。
⑥在数据区域“Data_array”中输入单元格B2：B37，在数据接受区间Bins_array 中输入单元格C3：C8，在对话窗口中可以看到其相应的频数是6，7，9，8，5，1，0。
⑦由于频数分布是数组操作，所以，此处不能直接单击“确定”按钮，而应按Ctrl +Shift组合键，同时敲“回车”键，得到频数分布。
练习1：某生活小区80户居民家庭人口数情况：
4 4 2 3 2 3 3 4 3 2 3 4 2 3 3 3 5 2 3 3
2 3 3 4 3 3 2 3 4 3 2 2 3 3 5 4 4 3 2 1
5 1 4 2 2 3 3 4 4 2 3 3 1 5 2 3 3 2 1 4
2 1 2 3 3 3 2 1 4 3 1 2 4 3 3 2 2 1 2 3
在EXCEL 中将上述资料录入，并按照家庭人口数进行频数统计。
其次，EXCEL 除能进行单变量值（单项分组）的频数统计外，还可以进行组距分组的频数统计。EXCEL 的组距分组的频数统计与单项分组的频数统计操作过程大体相同。但在分组时应注意：EXCEL 的FREQUENCY 函数进行频数统计时采用的是“上限在内”原则，为符合统计分组的“上限不在内”的原则，因此，分组时相邻组的组限不应重合。
例：某生产车间50名工人日加工零件数（单位：个）如下。（数据学生自行录入）
拟对工人生产数据进行如图1－1的C4：C9的组距分组。但是，FREQUENCY 函数在分组时，例如120这个数据会被统计在C6组内，与统计分组原则应统计在C7组内相违背，所以，分组时可按D4：D9形式进行，以符合统计分组原则。然后将各组上限列于E4：E9区域内，这时就可以按单项分组时的步骤进行操作。
图1－1
步骤如下：首先，将光标移至F4单元格，按住鼠标左键，拖曳光标覆盖F4：F9区域（如图1－2所示）。点击EXCEL 插入菜单中“函数”选项，在“统计”类函数
图1－2
中选择“FREQUENCY ”函数（如图1－3所示）。在“Data-array ”中输入原始数据阵列“B4：B53”，在“Bins-array ”中输入分组组距阵列“E4：E9”（如图1－3所示）。然后同时按“Ctrl-Shift-Enter ” 键即得如图1－4所示频数统计结果。
图1－3
图1－4
4、数据透视分析
数据透视主要是处理以文字形式表现的资料，如性别、职业、文化程度等。分为单变量表格与双变量表格，前者称为单向表，后者称为交叉表。
单向表
例 某移动通讯公司要制定新一年的销售计划，需要向市场了解移动用户对数字移动电话类型的需求。
经过问卷设计，在该地区抽取了1000个样本进行问卷调查，获得关于移动电话用户消费行为的一些数据资料，试根据工作表中的数据分析该地区移动电话用户的电话类型偏好。 ① 打开“数据整理.xls”工作簿，选定“移动电话调查”工作表。
②单击“数据”菜单中的“数据透视表和图表报告”选项，Excel 弹出数据透视表向导对话框。
② 根据向导要求，第1步是指定“数据源类型”和确定所需创建的“报表类型”。选择“数
据清单或数据库”作为数据源，选择“数据透视表”作为报表类型。最后单击“下一步”。
④第2步是确定数据区域，本例中数据所覆盖区域为A1:D1001单元格，单击“下一步”按钮，向导给出第3步操作对话框窗口。
⑤第3步确定数据透视表的位置。选择现有工作表，并利用鼠标将数据透视表的位置确定在E1单元格中，如果单击“完成”按钮，可以结束操作。
⑥单击“版式”，Excel 弹出对话框窗口，选择右边的“数字移动电话类型”字段，并将它拖到左边的“行” 区，再选择“数字移动电话类型” 字段，将其拖放在“数据”区域中，显示为“计数项：数字移动电话类型”，以便进行数据汇总。
⑦双击数据区域中的“计数项：数字移动电话类型”字段，打开“数据透视表字段”对话框，在“汇总方式”列表中选择“计数”。
⑧如果想选择数据显示方式，可单击图右侧的 “选项”按钮，则EXCEL 弹出“数据显示方式”。
在“数据显示方式”的下拉选项中确定“占同列数据总和的百分比”选项, 单击“确定”按钮，则版式调整完毕。再单击 “确定”按钮，回到数据透视表步骤3。
⑨单击数据透视表步骤3中的“完成”按钮。
交叉表
例 该公司市场部经理希望根据上例中的调查结果，确认不同性别的用户，其移动电话类型偏好是否存在着不同。
①打开“数据整理.xls”工作簿，选定“移动电话调查”工作表。
②从“数据”菜单中选择“数据透视表和图表报告”选项，得到“数据透视表对话框”窗口。在第1步之中选择“数据清单或数据库”与“数据透视表”，单击“下一步”按钮。 ③在第2步中，确定数据区域A1:D1001，单击“下一步”按钮
④在第3步中，确定数据透视表的位置，如图所示，但将其位置调整到“H1”单元格。然后，单击“版式”按钮，打开“版式”对话框。
⑤将对话框右边的“数字移动电话类型”字段拖到左边的“行”区作为分组变量，再将右边的“性别” 字段拖到“列”区，作为交叉分析的变量，最后将右边的“性别” 字段拖放到“数据”区域中，并选择计数项。如果希望结果是以百分比的形式显示，则可双击数据区域中的“计数项，性别”，则会弹出“数据透视表字段”对话框。选择“占同列数据总和的百分比”选项，来表示所出现的频率，最后，单击“确定”按钮，又回到“版式”对话框。 ⑥单击“确定”按钮，回到第3步，要注意数据透视表的位置定在单元格H1，最后，单击“完成”按钮，得到交叉表。
5、常用统计图表的绘制
条形图与柱形图
例 薪酬是众人所关心的主题，也更是当代经理人所关心的主题。北京、上海、深圳三个市经理人的薪酬是我国经理人“薪酬市场”的晴雨表。1999年《经理人》杂志所作的薪酬调查发现：所调查的经理人中，平均月收入最高的是上海经理人，为7767元；其次是深圳经理人，为7602元；最后是北京经理人，为7288元。结论应当是最高为上海经理人。那么此结论是否可以用图形来表示呢？
①打开“数据整理xls “工作簿，选择“薪酬”工作表。
②在Excel 的“插入”菜单中选择 “图表”选项，Excel 会启动图表向导，弹出“图表向导”对话框窗口。在 “图表类型”列表中选择“柱形图”，在“子图表类型”列表中选择“三维簇状柱形图”，单击“下一步” 按钮，进入数据源对话框.
③在这步对话框中，主要是选择数据源。单击“数据区域”右端的“压缩对话框”按钮，打开工作表，在工作表上将鼠标指向单元格A1，按下鼠标左键，拖到B4单元格，再单击“压缩对话框”按钮，回到源数据对话框。确定A1:B4单元格作为此图的数据源，同时，也应注意数据系列是以行还是以列方式排列，如果正确，此时，便有一个预览的图形如图所示，如满意可单击“下一步”按钮，进入“图表选项”对话框.
④单击对话框上方的“标题”页面，在标题中输入“三城市薪酬比较”；在“图例”页面中选择底部，至于其他功能则不需改动与填加，这些过程都会在图表预览框中看到，如果满意，单击“完成”按钮结束，得到图形。
⑤双击所绘制的图形，则会得到对话框。单击“字体”页面，在“字体”列表中选择“宋体”，在“字形”列表中选择“常规”，在“字号”列表选择“10”。
折线图
将平面中的交点相继联接起来所得图形称为折线图，它一般用于描述某一变量在一段时期内的变动情况。
例已知1990年至1999年各月社会商品零售总额，试观察一下社会商品零售总额的发展趋势及其变动规律。
①打开“数据整理.xls”工作簿，选择“零售额”工作表。
②在B 列后插入一列以便输入年份与月份。先选择B 列单元格，再打开“插入”菜单，选择“列”项，则自动在B 列后填加一列C ，原来C 列的内容变成了D 列。
③在单元格C1中输入“时间”，在C2单元格中输入公式“=B2&CHAR(13)&A2”，从单元格C3开始复制这个公式到C117。
④打开Excel“插入”菜单，选择“插入”菜单中的“图表”选项，Excel 会启动“图表向导”。
⑤在步骤1“图表类型”中选择“折线图”，及 “数据点折线图”，单击“下一步”按钮，进入步骤2的向导窗口。
⑥在步骤2中的数据区域中输入C1：D117，单击“下一步”按钮，进入向导“步骤3”。 ⑦在步骤3中单击图表“标题”页面，输入标题“社会消费品零售额趋势图”；单击“图例”页面，取消显示图例，如果通过图表预览，认为满意，即可单击“完成”按钮。
饼形图
例 中国人如何看WTO 。由中国社会调查事务所组织的最新一次问卷调查显示：有58.4%的被调查者对中国加入WTO 非常有信心；有19.3%的被调查者没有态度；有9.2%的被调查者表示担心；有13.1%的被调查者反对中国加入WTO 。试用饼形图描述一下中国人对加入WTO 的看法。
①打开“数据整理.xls”工作簿，选择“WTO”工作表。
②打开Excel“插入”菜单，并单击“插入”菜单中的“图表”选项，Excel 会启动图表向导。
③在步骤1的“图表类型”中选择“饼图”，并在“子类型图表”中选择“三维饼图”，单击“下一步”按钮，进入向导2。
④在步骤2中的数据区域中输入A3：B6单元格，单击“下一步”按钮，进入向导3。 ⑤在步骤3中单击“标题”页面，输入标题“中国人看WTO”；单击“图例”页面，取消显示图例；单击“数据标志”页面，在“数据标志”列表中选择“同时显示百分比及数据标志”。
⑥如果通过图表预览，认为满意，即可单击“完成”按钮。
五、作业：洛伦茨曲线与居民收入差异分析
在反贫困工作中有两项重要的统计工具：洛仑兹曲线和基尼系数，它们使用、整理大量调查数据所绘制的图形、曲线及计算结果，可以用来说明社会收入差距大小，贫富两极分化程度。这些工作可以使用Excel 来处理。根据本人近年实践，总结介绍如下。
1. 洛仑兹曲线
洛仑兹曲线研究的是国民收入在国民之间的分配问题，这是美国统计学家洛仑兹提出来的。它先将一国或一地区人口按收入由低到高排队，然后考虑收入最低的任意百分比人口所得到的收入百分比。例如最低的10%、20%、30%的人口等等所得到的收入比例分别为1.09%、
4.16%、9.21%等等，如表1所示，最后将这样得到的人口累计百分比和收入累计百分比的对应关系制成图表，即得到洛仑兹曲线，如图1-5所示。
表1
图1-5
上图即为洛伦兹曲线，其横坐标是相对人口累计百分比，纵坐标是收入累计百分比。如果收入是绝对均等的（当然这只是一种理想化的状态）， 每1%的人口都得到1%的收入，累计99%的人口就得到累计99%的收入，则收入分配是完全平等的，累计收入曲线就是上图中的对角线OL ，图中标明是“绝对均等线”。
假如收入分配绝对不均等（当然这也是一种设想的状态），几乎所有的人口均一无所有，即99%的人完全没有收入，而所有的收入都在1% 的人手中，即1%的人拥有100%的收入，累计分配曲线是由横轴和右边垂线组成的折线OAL 。图中标明是“绝对不均等线”
一般来说，一个国家、一个地区的收入分配，既不是完全不平等，也不是完全平等，而是介于两者之间，那么相应的洛仑兹曲线既不是折线OAL ，也不是对角线OL ，而是介于两者之间的就是中间那条向横轴突出的OCL 曲线。洛仑兹曲线的弯曲程度具有重要意义。一般来说它的弯曲程度反映了收入的不平等程度，弯曲程度越大，收入分配程度越不公平。
洛伦兹曲线和对角线之间的那块月牙形区域（图中斜线区域）可以看成是贫富之间的那条沟坎。这块月牙形区域面积S 大小，可以用来表征实际收入分配与理想境界的差距：这块月牙形区域面积S 越大，洛伦兹曲线弯曲度越大，月牙弯得越大，它和对角线离开得越远，说明收入差距越大，贫富两极分化越严重。反之，这块月牙形区域面积S 越小，洛伦兹曲线越平缓，月牙弯得越小，它和对角线靠得越近，说明社会收入差距越小，贫富两极分化越不明显。
用Excel 绘制洛仑兹曲线, 主要使用面积图，先绘制绝对平均区域的对角线三角形面积图，并以某显著颜色图案着色。再绘制洛仑兹曲线，选择一个前景色着色，掩盖前者的一部分以后，就可见到月牙形的曲边形，从而为基尼系数的计算做了准备。绘制洛仑兹曲线的步骤如下：
【步骤1】：选择单元格C2:C12作图表，进入【图表向导-4 步骤之 1-图表类型】，子图表类型选择“面积图”第一行第一个“面积图。显示各种树脂随时间或类别而变化的趋势线”，如图1-6：
图1-6
【步骤2】：进入“下一步”，添加标题“洛仑兹曲线”，取消图例，完成后对系列、坐标轴数据格式等格式调整后所得图形如图1-7：
图1-7
【步骤3】：取消网格线，将系列内部颜色调整为黑色斜线条，如图1-8
：
图1-8
【步骤4】：选择图表，右键选“源数据”，添加系列2，其值通过右端小按钮选取输入为“=Sheet2!$B$2:$B$12”，如图1-9：
图1-9
【步骤5】：确定后，加上X 轴、Y 轴标题，取消X 轴数据标志，调整系列2内部颜色，手工使用文本框写入O 、A 、L 、B 、C 和汉字，插入箭头，并长按Ctrl 键，将这些加入内容和原图标都选中叠加，右键选“组合”-“组合”，从而完成洛仑兹曲线图的绘制，如图1-10所示。
图1-10
根据上述说明作出如下习题：例某地区1999年的人口及收入情况见图所示，试绘制该地区的洛伦茨曲线。
户数（户）
280785 242250 167400 150000 93900 66300
月可支配收入（万元）
4000 6000 8000 10500 12500 17000
户数（户）
58350 41400 37500 19800 9450 5592
月可支配收入（万元）
22000 28000 35000 55000 85000 125000
思考：你能否依据宁波市有关的统计资料，分析宁波市不同行业收入的洛伦兹曲线？