实验九 Excel综合应用

实验九 Excel综合应用

一、实验目的

1． 综合使用Excel对数据表进行操作

二、实验内容

在可写硬盘D盘或E盘的根目录中新建一个以本人学号和姓名为文件名的作业文件夹（例如，081234567张三），完成以下内容：

1． 新建Excel工作簿文件，文件名：学号姓名Ex9_1.xls。按照下列要求制作Excel工作

簿：

2008年6月工资表

编号 姓名

（1） 数据计算及格式设置：

a） 将“2008年6月工资表”表格数据复制到所建工作簿文件的工作表Sheet1

中，从第一行第一列开始存放，将工作表Sheet1改名为“2008年6月工资表”。

李强 张洪 赵丽 刘兵 王敏 马琳 林强 叶宏 黄凯 杜俊 韩林 吴兵 姜吉 江维 蒋平 苗苗 贝贝 合计 部门 人事处 教务处 人事处 财务处 教务处 人事处 财务处 财务处 教务处 人事处 教务处 财务处 财务处 人事处 财务处 教务处 人事处 出生年月 基本工资 奖金 扣除 应发工资 实发工资 520 470 560 800 720 900 890 820 880 980 1000 1100 870 790 760 690 600 700 750 900 456 387 427 389 409 230 250 300 320 120 260 240 200 400 330 260 410 330 220 270 100 90 110 68 94 52 30 60 40 20 80 70 30 10 20 10 20 30 35 100 申一铭 人事处 周一兵 财务处 魏小楷 财务处 （2）

（3）

（4）

（5）

b） 在第一条记录的“编号”单元格中输入0001（数字字符串），自动填充后

续记录的编号为0002－0020。

c） 在第H列右侧新增一列，输入列标题“税款”。

d） 在所有记录的“出生日期”单元格中输入自选日期。

e） 在第一行前新增一行，将该行单元格A1:J1做合并单元格处理，在合并后

的单元格中输入标题“2008年6月工资表”。

f） 设置标题文字格式为黑体、加粗、16号、水平居中，设置表格中列标题文

字格式为楷体、加粗、12号、水平居中，列单元格以浅蓝色填充。 g） 给数据清单加线框（标题除外）：外框用较粗实线，内框用较细实线。 h） 在“2008年6月工资表”中每条记录的“应发工资”，“税款”，“实发工资”

单元格中数据按以下公式计算生成： 应发工资＝基本工资＋奖金－扣除

税款 ＝应发工资×0.03（当应发工资＜1000时）

＝应发工资×0.05（当应发工资≥1000时） （要求：税款计算使用IF函数完成） 实发工资＝应发工资－税款

i） 在“2008年6月工资表”最后一行用公式计算所有记录的“基本工资”和

“奖金”的平均值。

j） 设置“实发工资”列的数据格式为：使用货币符号￥，小数部分为0。 k） 设置“2008年6月工资表”的页面格式：页面方向为横向，左、右页边距

均为0.9。

数据排序：将“2008年6月工资表”中数据清单复制到工作表Sheet2中，在Sheet2中按主关键字为“部门”以自定义序列（财务处，人事处，教务处）排序、次关键字为“实发工资”以递减排序（提示：排序前先将“财务处，人事处，教务处”加入到自定义序列中）。将工作表Sheet2改名为“2008年6月工资排序表”。给数据清单加线框（标题除外）：外框用较粗实线，内框用较细实线。

分类汇总：将“2008年6月工资表”中数据清单复制到Sheet3中，在Sheet3中按部门以自定义序列（财务处，人事处，教务处）生成“税款”总和、“实发工资”总和的分类汇总项。（提示：先按自定义序列排序，再做分类汇总）。将工作表Sheet3改名为“2008年6月部门工资汇总表”。给数据清单加线框（标题除外）：外框用较粗实线，内框用较细实线。 数据筛选：

a） 自动筛选：插入一新工作表Sheet4，将“2008年6月工资表”数据清单

复制到Sheet4中，使用自动筛选命令生成满足“实发工资＜1000”条件的所有记录。将工作表Sheet4改名为“2008年6月工资筛选表”。给数据清单加线框（标题除外）：外框用较粗实线，内框用较细实线。

b） 高级筛选：插入一个新的工作表Sheet5，将Sheet1中工资表数据清单复

制到Sheet5中，使用高级筛选命令生成满足“奖金＞250”并且“实发工资＞1000”条件的所有记录。 创建图表：插入一新的工作表Sheet6，将“历年工资表”表格数据复制到Sheet6中，将工作表Sheet6改名为“2005-2007年工资表”。以“2005-2007年工资表”中数据作为数据源创建一个类型为“折线图”的图表，要求设定分类轴为“年份”，系列产生在“行”上。

历年工资表

姓名 李强 张洪 赵丽 刘兵 王敏 申一铭 马琳 林强 叶宏 黄凯 附件1： 姓名 考生姓名 李华 李明 李玉 李文 刘黎 刘敏 刘宁 刘波 刘璐 张华 张蒙 张继 张柏 张弛 赵庆 赵凯 赵林 赵希 赵丽 王华 王黎 王明 王思 王新 所属院系 哲学 中文 哲学 历史 历史 哲学 中文 哲学 哲学 哲学 中文 哲学 哲学 历史 哲学 历史 中文 哲学 历史 中文 中文 历史 哲学 中文 历史 英语 99 76 85 68 78 83 68 77 98 75 83 75 96 83 68 86 79 88 57 68 97 78 84 67 88 99 79 82 73 81 89 70 80 90 80 87 79 92 88 69 85 75 80 60 80 90 80 82 77 84 数学 平均成绩 2005年 2006年 2007年 800 850 720 900 600 900 950 780 890 700 900 980 880 1000 800 1200 1250 850 1090 800 1100 1250 1050 1300 950 1500 1550 1200 1600 900

2． 根据附件1中的原始内容，按照下列要求制作Excel工作簿：

（1） 请把附件1中的表格编制成一个Excel工作簿文件Ex9_2.xls。将表格中的“考

生姓名”用本人实际姓名填写。

（2） 请在“姓名”之前插入一新列，输入列标题“学号”，并请为所有学生添加学号

（第一个学生以07001开始，按顺序编号）。

（3） 在“姓名”上方插入一新行，并作合并单元格处理；在该行输入标题“学生成

绩登记表”，居中显示，字符格式不做要求。

（4） 在“平均成绩”列中，用公式计算并填入每个学生2门课程的平均成绩。

（5） 请将表格中的学生成绩记录按“平均成绩”从高到低重新排序。 （6） 根据学生所属院系进行分类汇总，计算出各院系各门课的平均成绩。 （7） *根据本表格，使用Excel里的相应功能绘制一幅图表（可参考下图），图表中

需反映出：

a） 步骤4中学生2门课程的平均成绩。

b） 步骤6中根据学生所属院系分类汇总所算出的各门课平均成绩。

考生姓名王新张柏赵庆李文王黎李玉赵希刘波张继刘黎赵林李明王明刘宁刘璐张蒙张弛王华张华李华赵凯赵丽王思刘敏1009080706050403020100历史 平均值哲学 平均值中文 平均值英语数学平均成绩

本文来源：https://www.bwwdw.com/article/6ac6.html