实验七Excel电子表格的综合实验

1、实验七 Excel电子表格的综合实验实验目的掌握数据列表的排序、筛选与计算。掌握数据的分类汇总。掌握页面设置与美化。实验内容新建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 孙名松男4923 71李刚男87 78 96 孙云飞男69 78 97 刘书红女88 56

2、85 李飞男69 78 90 刘雨女85 67 75 1.在Sheet1中对数据表中填写数据的单元格进行有效性验证，以保证用户输入数据过程中少犯错误，当有错误发生时，计算机会提醒输入者检查自己的输入。数据表中需要对各门课程成绩进行限制，要求输入的数据必须是0至100之间的整数，否则出现出错警告对话框“数据超限，请检查！” 提示: 执行菜单命令【数据】/【有效性】2.在Sheet1中的使用“格式”菜单中“自动套用格式”中的“彩色2”格式，并设置该表格的外框线为双线边框，内框线为单实线，所有数据和文字居中显示，对该表按“总分”进行按降序排列。3.将Sheet2中的数据按性别排序，先男生、后女生，性

3、别相同的按总分降序排列。并按性别分别求出男生、女生的各科平均分（保留1位小数）。、提示: 1可利用菜单“数据/ 排序”，进行相应的设定。 2将光标定位在该表格中的任意单元格中，选择菜单“数据/分类汇总”，本例中分类的字段为性别，汇总的字段为三门课程的分数及总分，汇总的方式为求平均值。 4.在原有分类汇总的基础上，再汇总出男女学生的人数。按如下样张所示，分级显示及编辑汇总数据。提示: 1.将光标定位在该表格中的任意单元格中，选择菜单“数据/分类汇总”，进行相应的设定，如下图。2. 将生成的汇总项名称“男平均值”、“女平均值”、“男计数”，“女计数”，改为“男生平均分”、“女生平均分”、“男生人数

4、”，“女生人数”如样张所示。样张如下：5.对Sheet2进行如下页面设置，并进行打印预览：纸张大小为A4，文档打印时水平居中，上、下页边距为3厘米。设置页眉“分类汇总表”、居中、粗斜体，设置页脚为当前日期，靠右安放。打印工作表的行号和页号，打印网格线。提示: 执行菜单命令【文件】/【页面设置】，如下图所示。6. 将作业服务器实验文件夹中的“工资表”下载到自己的存储设备中，然后将其复制到本次作业文件的Sheet3中。表中的基础数据（基本工资、加班金额、病假扣款、事假扣款）是由财务人员人工输入，其他数据可以通过如下规则编制公式或函数计算求得。 （1） 应发金额 = 基本工资 + 加班金额 （2）

5、失业保险金 = 基本工资 * 0.01 （3） 养老保险金 = 基本工资 * 0.014 （4） 应税所得额的计算。“应税所得额”就是需要纳税的那部分收入，每个人的应税所得额等于其“应发工资”减去“病假扣款”、“事假扣款”、“失业保险金”、“养老保险金”，再减去1000元后的值，如果这个值小于1000，那么应税所得额为0；如果这个值大于1000元，那么应税所得额为超出部分的数额。公式为： =IF(G4-H4-I4-J4-K41000,0,G4-H4-I4-J4-K4-1000)（5） 扣税金=应税所得额*0.1（6） 扣款合计 = 病假扣款 + 事假扣款 + 失业保险金 + 养老保险金+扣税金

6、 （7） 实发金额 = 应发金额 扣款合计按照上述规则设置公式，请将该工资表中的所有空白单元格计算出相应数据。7、为了使表中各行记录的显示更加容易区分，经常采用隔行显示的格式，就是为单元格隔行添加不同的底纹颜色，如下样张所示。可利用“格式”菜单中的“条件格式”来完成。在工资表的名称框中输入“A4:O21”后按回车键，快速选中工作表中全部记录数据，执行菜单栏【格式】|【条件格式】，在打开的对话框中，在“条件1”下拉列表中选择“公式”选项，在后面的文本框中输入公式：=MOD(ROW(),2)=0公式的含义是：如果行号除以2没有余数，则此行为偶数行，否则为奇数行。单击“格式”按钮，选择图案颜色。单击【确定】。底纹颜色可自定。样张如下：8、将该上述工资表复制到sheet4中，利用自动筛选功能分析数据。所谓筛选，是指在表格中选取符合一定条件的数据。在sheet4中筛选查找“销售部”、“事假扣款”为0，且“实发金额”在1400元以上的所有记录。执行菜单【数据】|【筛选】|【自动筛选】。9. 将sheet1至 sheet4分别更名为中文名字并设置工作表标签颜色，名称颜色自定。10.为工资簿添加密码。工资表中保存了一个企业中