项目七Excel的数据库操作

1、项目七 Excel的数据库操作课题Excel的数据库操作课型项目式教学执教许史萍时间2010.04.26班级0913课时数4学习目标1 .学会运用Excel处理数据，得到想要的结果2 .能灵活运用数据库操作完成特殊的任务课前准备PCM, OFFICET具，电源，相关展示内容教学步骤教学 内 容讲解示范布置任务 实施任务 评价任务小结作业布置展示课前准备的数据表操作任务任务一：排序学生根据要求进行操作，老师巡回指导任务二：自动筛选完成基础任务的同时，完成任务二进行方法归纳，小结。巩固训练。任务三：高级筛选任务四：分类汇总完成实训报告学习目标3 .学会运用Excel处理数据，得到想要的结果4 .能

2、灵活运用数据库操作完成特殊的任务学习准备图 4-52观察上图所示学生成绩表，要求：(1)如找出三班中总分的最高分；(2)找出三个班中总分在400分以上的同学；(3)根据规定，每门功课在130分以上(含130分)的可以获得三好学生称号, 请筛选出三好学生；(4)找出数学成绩最好的一个班级。学习过程1 .排序Excel 2000数据的排序功能可以使用户非常容易地对记录进行排序，用户只 要分别指定关键字及排序方法(升序或降序)，就可以完成排序操作。对图 4-52中的数据进行排序，步骤如下：(1)单击数据区任一单元格，单击“数据”菜单中的“排序”命令出现如图 4-53的排序图 4-53（2）在该对话框

3、中的“主要关键字”下拉表框中选定“总分”，系统默认排序 方向为“递增”，但在本例中要求找出最高分，所以选择“递减”前的单 选按钮，然后单击“确定”按钮，就出现如图 4-54所示的根据总分降序 排列。6 1117-02 2 3 3 2 2 t-I 1± 1-I IX 1- 1X9 5 2 2 2 74 4 4- 4 4 4-J-I 1± 1-I IX 1- 1X4 9 2 15 62 2 2 2 2 21X 1x 1± -1 lx 1程 源里霞燕娇 委彭孙吴魏2 13 12I 彳 H Sheetl ZSheet2/Shee 1:3/| <11714513039

4、26 7 co 910111214:更）支件© 浜腾® 观圉也 插入 喈式以 工且 数搪皿盘口也:帮助现围X工，外口中 12、. BF1I乒=To?郊 iJ江5 firABCDEFG 二1班级姓名语文数学英语总分21刘思瑶1301妁13541432娥12建林1301371364033 金文静 11415013S402就靖图 4-54需要注意的是，在图4-53中，有无标题行决定了第一行是否参加排序。下面我们学习有多个条件的时候如何进行排序。图表如图4-55所示。按照三个关键字对此表进行排序，其排序要求如图 4-56所示。排序结果如图4-57所示2 .

5、自动筛选使用自动筛选功能时，一次只能对工作表中的一个数据清单使用筛选命令； 对同一列数据最多可以应用两个条件。具操作步骤如下：（1）单击数据表区域中的任一单元格；（2）单击“数据”菜单，选择“筛选”命令项，选取“自动筛选”命令（如图 4-58所示）；这时在每个字段上会出现一个筛选按钮，如图 4-59所示。 单击需要筛选的数据列的筛选按钮，有选择条件“全部”、“前10个”或“自定义”等。这里我们选择“（自定义）”，出现如图4-60所示的对话框， 在里面填入相应条件即可。按图4-60填写筛选条件，按“确定”按钮，显示效果如图 4-61所示。如果是多条件自动筛选，还可以在图4-60的第二条件行中填写

6、相关条件。 筛选是一种用于查找数据清单中某些数据的快递方法，即从众多的数据中 挑选出符合某种条件的数据显示，而隐去不符合条件的其他数据（比显 示）。3 .高级筛选使用高级筛选，可以从数据库表格中筛选出符合特定条件的值。但有时限制 的条件较多，再用自动筛选就有些麻烦，这时，可以使用高级筛选数据。使 用高级筛选前，应在工作表的数据清单上方或下方先建立至少有三个空行的区域，作为设置条件的区域，且数据清单必须有列标志。如要求找出符合三好学生条件的学生，也就是要求找出每门功课都在130 分以上的学生，操作步骤如下：（ 1） 在数据列表中，与数据区隔一行建立条件区域并在条件区域中输入筛选条件。即在第一行前

7、插入三行空行，接着在C1、 D1、 E1 单元格中分别输入标志“语文”、 “数学” 、 “英语” ，然后在C2、D2、 E2 单元格中分别输入“ >=130”；（ 2） 单击“数据”菜单，选择“筛选”中的“高级筛选”命令项，屏幕出现如图 4-62 所示的对话框。将“数据区域”和“条件区域”分别设定，再按“确定”按钮，就会在远数据区域显示出符合条件的记录，如图 4-63 所示。（ 3） 如果想保留原始的数据列表，就需将符合条件的记录复制到其他位置，即在如图 4-62 所示的对话框的“方式”选项中选择“将筛选结果复制到其他位置”单选按钮, 并在”复制到”框中输入欲复制到的位置, 结果如图4-

8、64 所示 .（ 4） 特别需要指出的是, 当条件区域中条件选项不在同一行的时候, 代表这两行的条件是“或”关系，在实际运用中要注意根据题目要求灵活变通。4分类汇总Excel 2000 具有很强的汇总功能，使用分类汇总工具，可以进行分类求和、求平均值等。当然，也可以很方便地删除分类汇总的结果，恢复数据表格的原形。要进行分类汇总，首先要确定数据表格最主要的分类字段，并对数据表格按分类字段进行排序。如要求找出三个班中数学平均分最高的一个班级，则需先对如图4-52 中的数据按班级进行排序，然后单击“数据”菜单，选择“分类汇总”命令，出现如图 4-65 的对话框，系统自动设置“分类字段”为“班级”，单

9、击“汇总方式”下拉列表，选择“求平均值”，在“选定汇总项”窗口中选择“数学”复选框，最后，按“确定”按钮，得到如图 4-66 所示的分类汇总表。由此可知数学平均分最高的为1 班。 在图 4-66 中， 用户可以用鼠标选择“ 1、2、 3”来控制查看层次。只展开第一级，如图4-67 所示。只展开第二级，如图4-68 所示。特别需要注意的是，在分类汇总前一定先根据分类字段进行排序，否则将不能正常进行分类汇总，如图4-69 所示。如果用户要回到未分类汇总前的状态，只需在如图4-65 所示的对话框中单击“全部删除”按钮，图表就会回到未分类汇总的状态。综合练习请你操作1 对图4-52 中的学生进行排序，

10、总分成绩相同者按语文高低排名。根据规定，每门功课在130 分以上（含130 分）可以获得三好学生称号，请筛选出三好学生。请找出每个班中各功课的最高分。2在 Excel 中建立图4-70。要求如下：（ 1） 在 F 列加入总分列，并用函数求出每个同学的总成绩；（ 2） 新建工作表“优秀学生表”，把如图4-70 中的数据拷贝到新工作表，运用高级筛选找出符合优秀学生条件的同学（每门功课在80 分以上含（ 80 分） ） ；（ 3） 新建工作表“分类汇总表”，把如图4-70 表中的数据拷贝到此表中、计算各系计算机课程的平均分。建立图表要求如下：（ 1） 在 Excel 中建立“原始表”，输入图4-71

11、 中的数据，用“表达式”功能，分别计算每种商品的金额（数量*单价） ，要求金额的格式为“会计专用”格式（带符号¥）,并显示为绿色斜体字。（ 2） 建立新工作表“数据筛选”，将原始表中的数据拷贝到此表中，用筛选的方法（使用“高级筛选”，勿使用“自动筛选”）筛选出所有数量在 50 以上 （含50） 的商品， 筛选后的数据放在原始表第12 行的下面。（ 3） 建立新工作表“商品排序”，将原始表中的数据拷贝到此表中，用排序的方法，按“销售日期”排序，相同日期的按“金额”排序，仍相同的按“商品类别”排序，全部采用升序。（ 4） 建立新工作表“商品汇总”，将原始表中的数据拷贝到此表中，用分类汇总

12、的方法，按“销售日期”将“金额”求和汇总。（ 5） 在工作表“商品汇总”中，数据汇总后，先将明细数据隐藏，然后用汇总数据在新工作表中建立三维饼图，表示该商场不同日期所销售商品占总销售金额的百分比（不包括含数量和单价），要求显示百分比和图例，标题为“各商品销售金额比例图”。将图表所在工作白哦重新命名为“商品图表”。建立图表要求如下：（ 1） 在 Excel 中建立“原始表”，输入图4-72 中的数据，新建工作表，将原始表中数据引用到此表中，并将工作表重新命名为“序列格式表”。（ 2） 在工作表“序列格式表”中，在表格最后一行下增加一行，要求此行前 6 栏合并后居中显示“平均评分”，第 7 栏中用

13、“表达式”功能，计算完成所有学生评分的平均值。（ 3） 建立新工作表“表格排序”，将原始工作表中的数据拷贝到此表中，将所有学生记录。用排序方法，按“出生日期”进行升序排列。（ 4） 在工作表“表格排序”中，用高级筛选的方法筛选出所有 1976 年以后（含 1976 年）的女学生和1977 年以后（含1976 年）的男学生，筛选后的数据放在工作表“表格排序”从第 12行开始的空格中；（ 5） 在“原始表”中，建立三维簇状条形图，要求横轴显示分数，纵轴显示学生姓名，其他内容不在图表中出现，图表必须与图例，标题为 “学生评分图表”。建立图表要求如下：（ 1） 在 Excel 中建立“原始表”，输入

14、4-73 中的数据，计算“原始表”中第 5 列和 8 列销售额，要求用“表达式”计算，并将计算出的销售额值设为斜体，货币格式，加符号；（ 2） 在“原始表”的第 16 行计算去年、今年的销售量和销售额，要求用“求和函数”，销售数量用分位符分隔，且保留两位小数，按第3 行的格式要求表格中数据加上图案，销售金额用货币格式显示；（ 3） 新建工作表，表名“水果价格分析”，把原始表中的序号、名胜、去年单价、今年单价四列原始数据复制到此表中，在鱼侧增加一列，列名为“价格比较”，计算公式为（今年单价- 去年单价）今年单价，要求用百分比数显示，小数后保留两位小数；（4）新建工作表，表名“销售额排名”，将原始

15、表中的数据复制到此表中， 按今年销售额从高到低进行排序（相同销售额的按去年销售额从高到 低排列）；（5）新建工作表，表名“销售情况分布统计”，引用原始白哦中的名称、 去年销售额、今年销售额三列数据，作出簇状条形图，要求有图例并 添加标题“销售状况统计图” （16磅红色楷体字，并加下划线）。知识拓展1 .图4-53排序选项对话框的左下方有一个“选项”按钮。点击该按钮，我 们可以看到如图4-74所示的对话框，该对话框可以帮助我们对排序选项 做进一不控制。如图4-75所示为各地区半年销售情况表。按照月份进行排序，如果在选项中再选定按笔画排序的话，就可以看到 下面的效果图，如图4-76所示。2 .在综合练习的请你操作1中，我们如果把条件2改为“根据规定，每门 功课在1