实验七-Excel电子表格的综合实验.doc

实验七Excel电子表格的综合实验
实验目的
1. 掌握数据列表的排序、筛选与计算。
2. 掌握数据的分类汇总。
3. 掌握页面设置与美化.
一、 实验内容
1． 新建EXCEL工作簿文件,请分别在其Sheet1和Sheet2输入以下内容（可以复制)，并以你的:学号+姓名+Excel实验七。xls保存文件。
成绩统计表
姓名
性别
高等数学
计算机
英语
总分
刘飞雷
男
69
78
77

李小利
女
78
81
78

赵季云
女
63
49
98

高红
女
69
72
69

赵波
男
63
49
87

刘红
女
45
56
85

王浩明
男
66
62
56

王明
男
66
62
56

孙名松
男
49
23
71

李刚
男
87
78
96

孙云飞
男
69
78
97

刘书红
女
88
56
85

李飞
男
69
78
90

刘雨
女
85
67
75

1。在Sheet1中对数据表中填写数据的单元格进行有效性验证,以保证用户输入数据过程中少犯错误,当有错误发生时，计算机会提醒输入者检查自己的输入.数据表中需要对各门课程成绩进行限制，要求输入的数据必须是0至100之间的整数,否则出现出错警告对话框“数据超限，请检查!”
[提示：] 执行菜单命令【数据】/【有效性】
2.在Sheet1中的使用“格式”菜单中“自动套用格式”中的“彩色2"格式，并设置该表格的外框线为双线边框，内框线为单实线，所有数据和文字居中显示，对该表按“总分"进行按降序排列。
3.将Sheet2中的数据按性别排序，先男生、后女生，性别相同的按总分降序排列。并按性别分别求出男生、女生的各科平均分（保留1位小数）。、
[提示：］1可利用菜单“数据/ 排序”，进行相应的设定.
2将光标定位在该表格中的任意单元格中，选择菜单“数据/分类汇总”，本例中分类的字段为性别，汇总的字段为三门课程的分数及总分，汇总的方式为求平均值.
4.在原有分类汇总的基础上，再汇总出男女学生的人数.按如下样张所示，分级显示及编辑汇总数据.
[提示：]1。将光标定位在该表格中的任意单元格中，选择菜单“数据/分类汇总”，进行相应的设定，如下图.2。 将生成的汇总项名称“男平均值"、“女平均值”、“男计数”,“女计数”，改为“男生平均分”、“女生平均分”、“男生人数"，“女生人数”如样张所示.
样张如下：
5。对Sheet2进行如下页面设置，并进行打印预览：
1） 纸张大小为A4，文档打印时水平居中，上、下页边距为3厘米。
2） 设置页眉“分类汇总表”、居中、粗斜体，设置页脚为当前日期，靠右安放。
3） 打印工作表的行号和页号，打印网格线。
［提示:］执行菜单命令【文件】/【页面设置】，如下图所示.
6.将作业服务器实验文件夹中的“工资表”下载到自己的存储设备中，然后将其复制到本次作业文件的Sheet3中.表中的基础数据（基本工资、加班金额、病假扣款、事假扣款）是由财务人员人工输入，其他数据可以通过如下规则编制公式或函数计算求得。
（1） 应发金额 = 基本工资 + 加班金额
（2） 失业保险金 = 基本工资 ＊ 0。01
（3） 养老保险金 = 基本工资 ＊ 0。014
（4） 应税所得额的计算。“应税所得额”就是需要纳税的那部分收入，每个人的应税所得额等于其“应发工资"减去“病假扣款”、“事假扣款”、“失业保险金”、“养老保险金”，再减去1000元后的值,如果这个值小于1000，那么应税所得额为0;如果这个值大于1000元，那么应税所得额为超出部分的数额。
公式为： =IF（G4—H4—I4-J4—K4〈1000,0，G4—H4-I4—J4—K4—1000）
（5）扣税金=应税所得额*0。1
(6） 扣款合计 = 病假扣款 + 事假扣款 + 失业保险金 + 养老保险金+扣税金
（7） 实发金额 = 应发金额 – 扣款合计
按照上述规则设置公式，请将该工资表中的所有空白单元格计算出相应数据.
7、为了使表中各行记录的显示更加容易区分,经常采用隔行显示的格式，就是为单元格隔行添加不同的底纹颜色,如下样张所示。可利用“格式”菜单中的“条件格式”来完成。
在工资表的名称框中输入“A4：O21"后按回车键，快速选中工作表中全部记录数据，执行菜单栏【格式】｜【条件格式】，在打开的对话框中，在“条件1”下拉列表中选择“公式"选项，在后面的文本框中输入公式：=MOD(ROW（），2)=0
公式的含义是：如果行号除以2没有余数，则此行为偶数行，否则为奇数行。单击“格式”按钮，选择图案颜色。单击【确定】。底纹颜色可自定.
样张如下：
8、将该上述工资表复制到sheet4中，利用自动筛选功能分析数据。所谓筛选，是指在表格中选取符合一定条件的数据.
在sheet4中筛选查找“销售部”、“事假扣款”为0，且“实发金额"在1400元以上的所有记录.执行菜单【数据】｜【筛选】｜【自动筛选】。
9. 将sheet1至sheet4分别更名为中文名字并设置工作表标签颜色，名称颜色自定。
10。为工资簿添加密码。工资表中保存了一个企业中比较重要的信息，不希望被随意查阅或修改，所以给它添加打开权限密码是很有必要的。(切记，如果忘了,你我都打不开!）请将该文件的打开权限密码设置为你的学号的后三位数字。
[提示:］ 执行菜单【工具】/【选项】/【安全性】
2