1、1实验五 统计数据管理一、实验目的：掌握用 EXCEL 进行数据的排序与筛选，数据的分类汇总，数据透视分析。二、实验环境：使用的软件为 EXCEL2003。硬件为 PC 机，每人一台。三、实验内容与实验步骤实验内容、原理分析及具体实验步骤见讲义。四、实验结果总结对实验结果进行分析，完成思考题目，总结实验的心得体会，并提出实验的改进意见。数据：表 1 2004 级商管 1 班第 2 学期成绩统计表学号 姓名 外语 数学 计算机 总分 名次030001 A1 90 87 93 270030002 A2 76 89 88 253030003 A3 65 98 78 241030004 A4 67 8
2、4 56 207030005 A5 34 56 78 168030006 A6 67 46 89 202030007 A7 78 55 77 210030008 A8 87 78 89 254030009 A9 76 45 78 199一、数据排序1、排名用函数语法：RANK（number ，ref ， order） 。参数：number 是需要计算其排位的一个数字；ref 是包含一组数字的数组或引用；order 是用来说明排序方式的数字（若 order 为零或省略，则以降序方式给出结果，反之按升序） 。在“名次”列的“G3”单元格输入 “=RANK(F3,$F$3:$F$11)”，然后按下回
3、车键，第一位学生的名次也就出来了。自动填充 G4 到 G11 的名次。 结果：1 3 4 6 9 7 5 2 8要注意：相同数值用 RANK 函数计算得到的名次相同，会导致后续数字的序数空缺。2、利用格式工具栏排序某班成绩表为例：首先在需要排序的数据列中（如外语） ，单击任一单元格单击“常用”工具栏中的“升序”按钮。3、利用菜单排序1）一列排序选中需要排序的数据列后，执行“数据/排序”命令，弹出“排序”对话框，在对话框中选择要排序的方式（总分递增，也可以按名次，姓名，学号等排序） ，单击确定即可。2)在多列上排序以成绩为例，以总分递增，姓名递增排，可以在主要关键字选“总分” “递增” ，在次要
4、关键字选“姓名” “递增” ，确定。3） 按行排序22000 2001 2002 2003收入 52000 59000 64000 75000支出 31000 40000 38000 45000利润 21000 19000 26000 30000选好数据区域，从“数据”菜单中选“排序” ，在“排序”对话框中单击“选项”后，弹出“排序选项”对话框， “方向”中选“按行排序” ，确定后返回“排序”对话框，在“主要关键字”右侧选“递减” ，按确定后得到结果如下。2003 2002 2000 2001收入 75000 64000 52000 59000支出 45000 38000 31000 4000
5、0利润 30000 26000 21000 19000二、数据筛选筛选是查找和处理数据清单中数据子集的快捷方法。筛选只是暂时隐藏不必显示的行。1、 进行自动筛选（1）如果要显示所有行，单击“全部”；（2）显示在由数据项或百分比指定的上限或下限范围内的的所有数据行，单击“前10 个”。（3）使用当前列中的两个条件值，或者其他比较运算符，单击“自定义”。 （4）当前筛选的数据列中含有空白单元格时，显示此列中含有空白单元格的数据行，单击“空白”（把某学生成绩删除，单击数据列上端的下拉箭头，则有空白、非空白选项）；显示此列中含有数值的数据行，单击“非空白”。以学生成绩为例，对成绩进行数据筛选：单击成绩
6、表中数据区域内的任意单元格执行“数据/筛选/ 自动筛选”命令在每个字段的右边出现一个下拉箭头按钮，单击“成绩 1”右边的下拉箭头想进行单一筛选，在列表中选择一个数值，如选择 67，筛选结果显示出来，可以发现使用了自动筛选的字段其右边的下拉箭头变成蓝色，而行号也显示为蓝色。进行自定义筛选在列表中选择“自定义” ，如筛选外语 90 以上的学生：单击“自定义”后，在对话框中对筛选条件进行设置，如“大于等于” “90”结果为 1。还可以进行外语前 10%的筛选。2、取消自动筛选1）要取消对某一列进行的筛选，请单击该列首单元格右端的下拉箭头，再单击“全部”。2）要撤消数据清单中筛选箭头，请在“数据”菜单
7、中指向“筛选”再单击“ 自动筛选”命令。3、进行高级筛选（1）某一列或另一列上具有单个条件要找到满足一列条件或另一列条件的数据，在条件区域中为不同行输入条件。例如，将外语成绩“大于或等于 80”分，或总分大于 250 分的学生筛选出来。外语 总分=8025031）分别选中 b13、b14、c13、c15 单元格，输入内容：外语、=80、总分、2502)选中成绩表中任意单元格，执行“数据筛选高级筛选 ”,打开“高级筛选”对话框3)选中“ 将筛选结果复制到其他位置”选项。提示：也可以不选择此选项，但为了保留原有数据，建议最好将筛选结果复制保存到其他位置上。4）单击“ 数据区域”右侧的压缩对话框，用
8、鼠标选中 A2 至 C15 条件区域，然后按压缩对话框，返回“ 高级筛选” 对话框。4)单击“ 条件区域”右侧的压缩对话框，用鼠标选中 B13 至 F11 条件区域，然后按压缩对话框，返回“ 高级筛选” 对话框。5)单击“ 复制到”右侧的压缩对话框，用鼠标选中保存筛选结果的第 1 个单元格(如 A20)，然后按压缩对话框，返回“高级筛选”对话框。确定即可以筛选出符合条件的6)全部设置完成后，单击“确定”按钮，符合条件的数据就被筛选出来了。姓名 班级 外语 数学 计算机 总分 名次A1 一班 90 87 93 270 1A2 二班 76 89 88 253 3A8 二班 87 78 89 254
9、 2（2）某一列或另一列上具有单个条件要在两列或多列中查找满足单个条件的数据，请在条件区域的同一行中输入所有条件。把“条件区域”改成以下的样式（即条件数值位于同一行中） ，执行上述筛选制度操作，则只将外语“大于或等于 80”分的学生和“总分”大于 250 分学生筛选了出来。外语 总分=80 250筛选结果如下：姓名 班级 外语 数学 计算机 总分 名次A1 一班 90 87 93 270 1A8 二班 87 78 89 254 2三、数据的分类汇总1、创建分类汇总任务：把成绩按班级汇总。第一步：以分类字段班级为主关键字对要汇总的所有数据进行排序（一班在前，二班在后，则选递减排序） 。姓名 班级
10、 外语 数学 计算机 总分 名次A1 一班 90 87 93 270 1A3 一班 65 98 78 241 4A6 一班 67 46 89 202 7A9 一班 76 45 78 199 8A2 二班 76 89 88 253 3A4 二班 67 84 56 207 6A5 二班 34 56 78 168 9A7 二班 78 55 77 210 5A8 二班 87 78 89 254 2第二步：选定数据范围内的任一单元格“数据”菜单分类汇总在对话框中：分类字段选“班级” 、汇总方式选“平均值” 、选定汇总项如“外语、数4学、计算机” ，选“替换当前分类汇总” 、 “汇总结果显示在数据下方”
11、（如果每组数据分页，则一班、二班中间加分页符，显示、打印都分页）确定。姓名 班级 外语 数学 计算机 总分 名次A1 一班 90 87 93 270 1A3 一班 65 98 78 241 4A6 一班 67 46 89 202 7A9 一班 76 45 78 199 8一班 平均值 74.5 69 84.5A2 二班 76 89 88 253 3A4 二班 67 84 56 207 6A5 二班 34 56 78 168 9A7 二班 78 55 77 210 5A8 二班 87 78 89 254 2二班 平均值 68.4 72.4 77.6总计平均值 71.111111 70.88888
12、9 80.6666672、分级显示在上图汇总结果中，工作表左上方是分级显示的级别符号如显示 1、2、3 级 ，如果要分级包括某个级别，单击该级别的数字。分级显示级别符号下方有显示明细数据符号“+”，单击可以在数据清单中显示出数据清单中的明细数据，分级显示级别符号下方有隐藏明细数据符号“-” ，单击可以在数据清单中隐藏数据清单中的明细数据。本题 1 级：“总计平均值” ，2 级：一班 平均值、二班 平均值、总计平均值，3 级：显示全部结果。3、分类汇总的删除在分类汇总数据清单中选择一个单元格执行“数据/分类汇总”命令，弹出“分类汇总”对话框在“分类汇总”对话框中单击“全部删除”按钮，可删除分类汇
13、总的结果。四、关于合并计算数据姓名 数学 姓名 计算机 姓名 外语A1 87 A2 93 A1 90A2 98 A3 78 A3 65A3 46 A6 89 A6 67A4 45 A7 78 A9 76A5 89 A1 88 A2 76A6 84 A4 56 A4 67A9 56 A5 78 A5 34A7 55 A9 77 A7 78A8 78 A8 89 A8 87合并后：数学 计算机 外语A1 87 88 90A2 98 93 76A3 46 78 65A4 45 56 67第三页第一页 第二页5A5 89 78 34A6 84 89 67A9 56 77 76A7 55 78 78A8
14、 78 89 87(1)每张工作表都只包含两列：“姓名”和科目名称，如图 1 中的“计算机”工作表。(2）姓名要统一格式，特别对两个字的姓名，姓名中间要么都不用空格对齐，要么都用空格对齐，否则格式不同会生成单独的行，影响合并的结果。(3）Sheet4 作为“汇总”工作表，用来存放各科成绩，是一个空白表。合并计算的操作(1)在“汇总”工作表中，单击合并计算数据目标区域左上角的单元格 A1。 (2）在主菜单选择“数据” ，单击 “合并计算”命令，打开“合并计算”对话框” ， “函数”下拉列表框中，单击用来对数据进行合并计算的汇总函数“求和” 。(3）单击“引用位置”输入框后面的 “压缩对话框”图标
15、，单击 Sheet1 工作表，用鼠标选定“姓名”和“计算机”两列数据单击“压缩对话框”返回“合并计算”对话框。(4)单击添加 按钮，将选择的源数据添加到“所有引用位置”列表框中。(5）对“外语” 、 “数学”等工作表的数据重复执行上述操作，将它们所在的单元地址全部添加到“所有引用位置”列表框中。(6）在“标志位置”选项框中，勾选指示标志在源区域中位置的复选框：“首行” 、“最左列” ，按确定按钮返回到“汇总”工作表。会马上发现，各科成绩都跑到 “汇总”工作表中了，并且按第一次选定工作表的姓名顺序排列。四、单变量求解单变量求解是在已知一个公式的最终结果，求公式中某一个变量的值。设在一次公务员考试
16、中，共有四门考试成绩，成绩是加权的：法律知识占 20%，专业知识占 30%，英语占 20%，面试成绩占 30%。若一个公务员前三门成绩为法律 a2 80，专业知识 b2 90，英语 c2 85，要求总成绩 80 分才能录取，面试成绩应达到多少才能被录取？在总成绩单元格（E2）中输入计算公式：“=A2*20%+B2*30%+C2*20%+D2*30%”执行“工具/单变量求解” 命令，弹出“单变量求解”对话框在对话框中的“目标单元格”引用单元格 E2，在“目标值”文本框中输入 80，在“可变单元格”中引用单元格D2单击“确定”按钮，弹出 “单变量求解状态”对话框，单击“确定”按钮得 66.67分。
17、*打印表格时要打行号和列标：“文件”页面设置工作表打印“行号列标”五、数据透视分析透视分析是通过使用数据透视表，对数据库或数据清单中的数据进行多角度分析查看。数据透视表是对大量数据快速汇总和建立交叉列表的交互式表格。借助数据透视表的强、强大数据分析功能，可以使数据分析工作简单化。工人编号 性别 年龄 文化程度 技术级别1 男 52 文盲 62 男 30 初中 33 男 19 初中 24 男 46 高中 45 女 47 小学 466 男 34 小学 27 男 22 初中 88 男 31 高中 59 男 55 初中 810 男 32 初中 511 男 49 中专 412 女 34 初中 413 男
18、 34 初中 414 男 61 技工 715 男 36 初中 4步骤如下：（1）选择“数据”菜单中“数据透视表和数据透视图”选项。(2)选择数据源区域。（3）选定数据透视表位置，完成。7（4）将“性别”作为行字段拖至 G 列，并将“性别”作为数据拖至数据项处，得下表结果。计数项:性别性别 汇总男 13女 2总计 15同理可得“文化程度”的透视表。计数项:文化程度文化程度 汇总初中 8高中 2技工 1文盲 1小学 2中专 1总计 15此时如点击图形按钮，立即得到如下的透视图。汇 总0123456789初 中 高 中 技 工 文 盲 小 学 中 专汇 总8（5）将“性别”作为行字段拖至行字段处，并
19、将“文化程度”作为列字段拖至列字段处， “性别”或“文化程度”作为列字段拖至数据字段处得下表结果计数项：文化程度 文化程度性别 初中 高中 技工 文盲 小学 中专 总计男 7 2 1 1 1 1 13女 1 1 2总计 8 2 1 1 2 1 15（6）向数据表中添加字段段。如添加工资。步骤与前面相同，在步骤 3 对话框，把“酬金”纽拖到“数据框”中，确定后结果如下。文化程度性别 数据 初中 高中 技工 文盲 小学 中专 总计男 计数项:文化程度 7 2 1 1 1 1 13求和项:酬金 6158 1689 909 564 890 1180 11390女 计数项:文化程度 1 1 2求和项:酬
20、金 1076 468 1544计数项:文化程度 的求和 8 2 1 1 2 1 15求和项:酬金 的求和 7234 1689 909 564 1358 1180 12934综合练习：某班 20 名学生，考试三门课，创建一个成绩表，输入数据，学号、性别、姓名、高数、外语、政治、总分、平均分、名次。（1）运用公式和函数算出每位同学的总分数及平均分。（2）求每科成绩的最高分和最低分、总分、平均分。（3）大于等于 90 分用红色，小于 60 分用黑色。（4）按平均分（或总分）排序（5）最高分加以重视。 （绘图工具栏/星与旗帜爆炸形）（6）计算总成绩标准差、众数、中位数（7）计算各等级的人数：（8）成绩筛选，显示 7080 分之间的成绩。（9）每个人成绩画柱形图，每个人平均分画折线图、散点图，每科按优、良、中、及格、不及格画图，三科优秀率、不及格率画图，三科中每科平均数、众数、中位数画图。
