实验07 Excel常用函数
一、实验内容:
1.常用统计函数和数学函数
本题使用的统计函数: Average、Max、Min、Count、Countif、Rank 本题使用的数学函数: Sum、Round 打开文件夹中的工作簿“E21.XLS”，作如下操作：
(1) 用公式计算最后得分，最后得分为去掉最高分和最低分求平均，评委允许缺席，最后得分用Round函数保留一位小数 (2) 用Rank函数计算名次
(3) 用Average计算各评委的平均分
(4) 用Countif函数和Count函数计算优秀率(>=90)和不及格率(<60)
(5) 条件格式设置：对C3:I13区域设置条件格式，>=90分以上的分数用蓝色显示，<60分以下的用红色显示
2、常用逻辑函数
本题使用的逻辑函数: If、And、Or 打开文件夹中的工作簿“E22.XLS”，在sheet1工作表中作如下操作： (1) 使用逻辑函数计算总评，总评的计算规则:
(a)机试成绩>=85 且 笔试成绩>=85, 则总评为“优秀” (b)机试成绩<60 或 笔试成绩<60 , 则总评为“不合格” (c) 其它情况 总评为“合格” (2) 将表中不及格的分数用红色表示。
(3) 将表中的“不合格”用红色、斜体、加粗显示；
(4) 保存修改。
3、常用时间和文本函数
常用日期时间函数: Today、Date、Datedif 本题使用的文本函数: Mid
打开文件夹中的工作簿“E23.XLS”，作如下操作： (1) 将工作表sheet3更名为“常用名称”，并按下图所示在该工作表内输入内容
(2) 定义名称: 部门、性别
(3) 切换到sheet1, 为部门和性别设置有效性，按下图所示数据用组合框输入部门和性别 (4) 身份证号码中包含出生日期信息，故出生日期用公式计算： =DATE(MID(F2,7,4),MID(F2,11,2),MID(F2,13,2)) (5) 用公式计算年龄：(Today()-E2)/365.25 (6) 保存修改
4、查找引用函数
本题使用的查找引用函数: VLookUp
打开[Excel实验二]文件夹中的工作簿“E24.XLS”，使用Rank函数计算名次，并使用VLookUp函数查奖金。
二、实验结果提交
将按要求编辑好的“E21.XLS”、“E22.XLS” 、“E23.XLS”和“E24.XLS”4个文件上传到你自己的文件夹内。
如打不开个人网络存储空间，请登录到ftp://202.193.94.88（用户名：JLHSTD ，密码：12345），将文件上传到你自己的文件夹内。
三、选作题
1、逻辑函数应用
打开文件夹中的工作簿E25.xls，作如下操作： (1) 按右图所示编辑工作表
(2) 用公式计算是“闰年”还是“平年” =IF(OR(AND(MOD(A2,4)=0, MOD(A2,100)<>0),
MOD(A2,400)=0),\闰年\平年\
(3) 工作簿以“E25.xls”为文件名保存。
2、财务函数应用
某客户欲购买一套价值50万元的公寓，首付20万元，余下金额向银行申请商业贷款30万元，贷款年限10年，年利率为5%，试制作按月还款表及查询月还款数据的模型。打开文件夹中的工作簿E26.xls，应用财务函数完成月还额计算款模型。
3. 查找引用和数据库函数
打开文件夹中的工作簿 “E27.xls”，使用VLookUp函数从其它工作表内查得奖金、住房补贴、请假扣款等内容，使用数据库函数计算各部门实发金额的总计。
4、数据库函数应用
打开文件夹中的工作簿 “E28.xls”，使用数据库函数完成表格的计算。