实验八EXCEL实验

一、实验目的。

1．熟识数据清单的特点和数据清单的建立与维护的基本方法；

2．掌握数据清单记录的排序操作；

3．掌握数据清单记录的自动筛选操作；

4．掌握数据清单记录的高级筛选操作；

5．掌握数据清单记录的分类汇总操作。

二、实验内容。

将**的文件“实验八 excel实验解压到“本地磁盘(e:)”中，并将解压生成的文件夹以自己的学号姓名实验八重命名（如张三同学，学号为200940304101，则建立的文件夹名称为“200940304101张三实验八”），然后完成以下操作：

1．打开e:\学号姓名实验八文件夹中的工作簿，按顺序完成如下操作：

1）排序。在“排序1”工作表中，按班级“b”在前“a”在后排序，将排序结果复制到以单元格d1为左上角的区域。然后原区域再按班级“a”在前“b”在后排序。

在“排序2”工作表中，学历按“博士”、“硕士”、“学士”的顺序排序；学历相同按年龄从大到小排序。

在“排序3”工作表中，班级按“机械”、“电子”、“新闻”、“财会”的顺序排序。（提示：利用自定义序列）

在“排序4”工作表中进行排序，排序要求是：性别“女”在前“男”在后；性别相同按学历升序排序；学历相同则按基本工资降序排序，基本工资相同则按年龄从大到小排序。

2）自动筛选。

从“自动筛选1”工作表的数据清单a1：f51中筛选出总分为前5名的记录（允许多于5条记录），将结果复制到以单元格a55为左上角的区域中（包括标题行）。再单击“总分”所在列的按钮选择“全部”，显示全部记录；

从“自动筛选1”工作表的数据清单a1：f51中筛选出三门功课成绩都在80分及以上的所有记录。

从“自动筛选2”工作表的数据清单a1：g51中筛选出非博士、非教授的女教师记录。

从“自动筛选3”工作表的数据清单a1：g51中筛选出80年代参加工作且基本工资不足1200元的记录。

2．打开e:\学号姓名实验八文件夹中的工作簿，按顺序完成如下操作：

1）高级筛选。

从“高级筛选1”工作表的数据清单a1：f51中筛选出有不及格情况的记录，条件区域放在以单元格h1为左上角的区域，将结果复制到以单元格m1为左上角的区域。

从“高级筛选1”工作表的数据清单a1：f51中筛选出学号后两位为02或05的记录，条件区域放在以单元格h20为左上角的区域，将结果复制到以单元格m20为左上角的区域。

从“高级筛选2”工作表的数据清单a1：g51中筛选出男博士和男硕士的记录，条件区域放在以单元格j1为左上角的区域，将结果复制到以单元格m1为左上角的区域。

从“高级筛选2”工作表的数据清单a1：g51中筛选出1980-1-1之前参加工作且基本工资不足1500元和1990-1-1及以后参加工作且工资超过1100元的记录，条件区域放在以单元格j30为左上角的区域，将结果复制到以单元格m30为左上角的区域。

2）数据库统计函数。

根据“数据库函数”工作表中的数据清单区域a1：f51的数据，在区域i1：i10的各单元格中使用数据库统计函数计算出所要求的值。

3．打开e:\学号姓名实验八文件夹中的工作簿，按顺序完成如下操作：

1）分类汇总。

对工作表sheet1的数据清单区域a1：e51按学历分类，求各类人员基本工资的最大值。

对工作表sheet2的数据清单区域a1：e51，按班级分类求出各班级的英语、计算机及总分的平均值。

对工作表sheet3的数据清单区域a1：e51，按图a-5所示进行分类汇总，先按性别和职称为主次关键字排序，然后分别以性别为分类字段求人数，再按职称为分类字段求各类人员的平均基本工资。

图a-5 分类汇总后参考效果图。

2）数据透视表。

对工作表sheet4的数据清单区域a1：e51，建立一个数据透视表，按学历统计最低基本工资，并将其显示在新建的工作表“学历最低基本工资”中（完成结果如下图）。

对工作表sheet4的数据清单区域a1：e51，在sheet4工作表中，以h4单元格为起始单元格建立一个数据透视表，统计出各类职称中各种学历男、女教师的最高基本工资。要求在数据透视表中，按学历分页，按职称分行，按性别分列。

3）（选做）模拟运算表。

打开sheet5工作表，假设其他费用占毛利润的45%，净利润等于毛利润减去其他费用，那么在条件不改变时，毛利润分别为10000，20000，30000，40000，50000，60000……时，使用模拟运算表在b3：b12区域计算净利润的变化情况。

打开sheet6工作表，假设公司的他费用占毛利润的45%，净利润等于毛利润减去其他费用以及所缴税。那么毛利润分别为10000，20000，30000，40000，50000，60000……，税率是毛利润的1%，3%，5%，7%，9%……时，使用模拟运算表在c3：h12区域计算净利润的变化情况。

下课前将实验结果---即e盘根目录下以自己“学号姓名实验八”命名的文件夹用winrar软件压缩，文件名为“学号姓名实验八。rar”（如张三同学，学号为200940304101，则文件名为“200940304101张三实验八。rar”）上传到老师的ftp空间中所在班级的文件夹中。

具体操作参考：本科教材p224 专科教材p213

用户名：pyr

密码：pyr