Excel试题

长沙通信职业技术学院二Ｏ一Ｏ年上学期期末考试

《EXCEL 应用》 试题 （NO.1）

一、新建一工作薄，并命名为公司职工档案表表.XLS 按要求完成下表：（共36分）

表（1）

1、 “部门”、“职务”和“婚否”字段内容用下拉列表方式选择。

2、基本工资用if 函数根据职务列数据产生，计算方法如下：

3000 主任

基本工资= 2500 副主任

2000 职员

3、身份证号码为”430100”依次连接上生日的4位年、2位月、2位日。 （用公式做该题）

4、标题设为灰色底纹，隶书20号字，在表格末行增加一行，将A13-C13合并，输入“平

均工资”，并求出D 列的平均工资，在表格末行再增加一行，将A14-D14合并，输入“未婚人数”，并求出未婚人数。

5、表格的外边框设为红色的双线，内边框设为黑色单线。将表格复制到一新工作表中，命

名为“冻结窗口”，要求冻结A 、B 列和第1、2行。

6、用高级筛选选出所有姓张的并职称为“职员”员工的记录，并将结果保存在当前工作表

的下方

7、将sheet1表重命名为“职工档案表”，将“职工档案表”复制生成一新的表，并改名为

“汇总统计表”，对该表进行二级分类汇总，统计以下数据。 a.每个部门有多少人？

b.各部门的基本工资的平均值分别是多少？

8、用数据透视表统计各部门各职务人数，结果在当前工作表中显示。

9、不显示工作表的行号和列标，以及网格线。

10、将员工信息做生日提醒为绿色底纹，文字加粗显示。 二、计算：（共24分） 1、新建一名为"学生成绩表.xls ”的工作簿文件，按要求完成下列操作。

学生成绩表

学号 姓名 数学 语文 外语 200001 杨栋 89 91 90 200002 郑卫国 92 90 73 200003 张之洞 82 88 80 200004 孟宏 96 84 95 200005 赵国荣 73 68 73 200006 张玉曼 87 92 93 合计 平均分

（1）合并A9至B9单元格，并居中； （2）合并A10至B10单元格，并居中；

（3）利用函数计算学生成绩表中的"合计"的值；

（4）利用函数计算学生成绩表中的"平均分"的值（保留一位小数）；

（5）合并学生成绩表中的A1至E1单元格，居中显示并设置成黑体14； （6）添加“总分”列，计算出各学生的总分成绩。

（7）添加“排名”列，计算出各学生的排名，并按排名升序排列。

（8）将工作表导出保存在素材文件夹下，并命名为“学生成绩表。Prn ”

2、新建一名为"贷款计算.xls ”的工作簿文件，用单变量进行求解。

（1） 某公司贷款100万，想每年还款20万，在贷款年利率为5%的情况下，需要多少年

才能还清？（3分）

（2）若每年可还款20万，贷款年利率为5% ，打算还贷5年，该公司最多可贷款多少？

（3分）

3、新建一工作薄，并命名为电脑配件销售表.XLS，完成下列计算。

以下是某电脑公司电脑配件销售情况及配件的价格，请在电脑配件销售表将“价格”一栏从单价表中获得，并计算各配件的销售金额。

三、绘制图表：（共20分）

1、新建一名为"茶厂销售表.xls”的工作簿文件，按要求完成下列操作。

某茶厂2001——2003年的销售数据如下表，按要求绘制各种图表。

（1）、2001——2003年的销售簇状柱形图，并给X轴取名为时间，Y轴取名为数量,图例为

9号字，并将图例放在图表的下部。

（2）、2001——2003年各类茶叶的销售折线图，添加垂直线，添加绿茶的销售对数趋势线。

2、新建一名为"救灾捐献.xls”的工作簿文件，按要求完成下列操作。

（1）将素材文件夹中的jztj.txt文件导入在sheet1中，sheet1更名为“抗洪救灾捐献统计

表”,数据表加上内外边框，将表格格式自动套用古典2，居中对齐。（5分）

（2）计算各项捐献的总计,分别填入“总计”行的各相应列中。(结果的数字格式为会计

专用样式)

（3）绘制各部门捐款的折合人民币的三维饼图，要求有图例并显示各部门捐款总数的百

分比，图表标题为“各部门捐款总数百分比图”。嵌入在数据表格下方(存放在A8：

E18 的区域内)。

四、数据分析（共20分）

1、新建一名为"贷款分析.xls”的工作簿文件。用双变量模拟运算表解决以下问题：（10分）

假设想贷款400000万买房，要查看在不同的利率和不同的偿还年限下，每月应还的贷

款金额。初始数据如表（3）所示。

表（3）

2、新建一名为"方案分析.xls”的工作簿文件。建立一模型，该模型是假设销售不同数量的某产品，对利润产生的影响。该模型中有4个可变量：进价，加价百分比，销售数量，固定成本。利润=进货*加价百分比*销售数量-固定成本。设定三个方案，方案如下：进价，加价百分比，销售数量，固定成本

50 20% 800 2000

60 18% 700 2200

70 16% 600 1800

要求将方案建立方案摘要报告。