实验八 Excel实验(增加后)

1、实验八 Excel函数的应用 一、实验目的 掌握Excel工作表表达式、函数的输入； 掌握Excel工作表常用函数的使用； 1 二、熟悉函数功能（一） 日期和时间函数 Date函数：返回特定日期的序列号 Day函数：将序列号转换为月份日期 Now函数：返回当前日期和时间的序列号 Time函数：返回特定时间的序列号 Today函数：返回今天日期的序列号 Weekday函数：将序列号转换为星期日期 Datedif函数：返回两个日期之间的年月日间隔数（此函数 是隐藏函数） 2 文本函数 Char函数：返回由代码数字指定的字符 Code函数：返回文本字符串中第一个字符的数字代码 Len函数：返回文本字

2、符串中的字符个数 Lower函数：将文本转换为小写 Upper函数：将文本转换为大写 Left函数：返回文本值中最左边的字符 Right函数：返回文本值中最右边的字符 Mid函数：从文本字符串中的指定位置起返回特定个数的字符 Exact函数：检查两个文本值是否相同 Trim函数：删除文本中的空格 &函数：字符连接 数学函数 ABS函数：返回数字的绝对值 Fact函数：返回数字的阶乘 Floor函数：向绝对值减小的方向舍入数字 Int函数：将数字向下舍入到最接近的整数 Mod函数：返回除法的余数 Pi函数：返回Pi的值 Rand函数：返回 0 和 1 之间的一个随机数 ROUND函数：将数字按指

3、定位数舍入 Sum函数：求参数的和 Sumif函数：按给定条件对指定单元格求和 统计函数 Average函数：计算参数的平均值 Averageif函数：计算满足条件的参数的平均值 Count函数：计算参数列表中数字的个数 Countif函数：计算区域内符合给定条件的单元格的数量 Max函数：返回参数列表中的最大值 Min函数：返回参数列表中的最小值 Rank函数：返回一列数字的数字排位 Rank.avg函数：返回一列数字的数字排位：数字的排位是其大 小与列表中其他值的比值；如果多个值具有相同的排位，则将 返回平均排位。 注意： Rank；rank.avg；rank.eq三个函数的区别 查找和引

4、用函数 Lookup函数：在向量或数组中查找值 Hlookup函数：查找数组的首行，并返回指定单元格的值 Vlookup函数：在数组第一列中查找，然后在行之间移动以返 回单元格的值 Index函数：使用索引从引用或数组中选择值 Offset函数：从给定引用中返回引用偏移量 Match函数：在引用或数组中查找值 三、实验内容与步骤（一） MS Office2010高级应用7 1、对“实验八.xlsx”中“奖金发放”完成如下操作 ： （1）用两种方式运算出年资列数据，年资列数据是指工作了 几个年头；（提示：一种可用datedif函数，一种用两个数据 相减再除以360取整） （2）根据年资计算奖金；

5、年资未满1年的员工没有奖金，满1 年以上未满3年则发放5，000元奖金，满3年以上则发给8， 000元奖金。 （3）调整D列和E列到适当的宽度 首先要进行年资的计算，请先选取D2储存格，在此要使用DATEDIF函数 来计算两个日期之间的年数、月数或天数，其格式如下： DATEIF(开始日期，结束日期，差距参数单位） 到职日 奖金基数基准日（我们以这个日期 为基准，所以使用绝对参照位址） 求算两日期差距的整年数要 使用“Y”参数 在D2单元格中输入“=DATEDIF(B2,$C$2,”Y“)”， 然后按Enter IF(判读式，条件成立时的作业，条件不成立时的作业） 当年资小于1年的条件 成立，

6、没有奖金 当年资小于3年的条件 成立，奖金为5000； 若条件不成立，就是 满3年以上，奖金为 8000 在E2单元格中输入” =IF(D21,0,IF(D23, 5000,8000)” 三、实验内容与步骤（二） MS Office2010高级应用10 2、在“实验八.xlsx”中，请你根据东方公司员工档 案表，按照如下要求完成统计和分析工作： (1)根据身份证号，请在员工档案表工作表的出生日期 列中，使用MID函数提取员工生日，单元格式类型为yyyy年 m月d日。 (2)根据身份证号，请在“员工档案表”工作表的“性别”列中， 计算出性别“男”或“女” （提示：身份证中15-17位为奇数为“

7、男”） (3)根据入职时间，请在员工档案表工作表的工龄列中 ，使用TODAY函数和INT函数计算员工的工龄，工作满一年才 计入工龄。 (1)在员工档案表工作表的G3单元格中输入=MID(F3,7,4)&年 &MID(F3,11,2)&月&MID(F3,13,2)&日，按Enter键确认，然后向下填充 公式到最后一个员工。 如下图所示： (2)在“员工档案表”工作表的C3单元格中输入=IF(MOD(MID(f3,15,3),2),男 ,女)，按Enter键确认，然后向下填充公式到最后一个员工。 如下图所示： (1)在员工档案表的J3单元格中输入=INT(TODAY()-I3)/365)，表示当前

8、 日期减去入职时间的余额除以365天后再向下取整，按Enter键确认，然后向下 填充公式到最后一个员工。 如下图所示： (2)在员工档案表的L3单元格中输入=J3*工龄工资!$B$3，按Enter键确 认，然后向下填充公式到最后一个员工。 如下图所示： MS Office2010高级应用13 (4)引用工龄工资工作表中的数据来计算员工档案表工 作表员工的工龄工资，在基础工资列中，计算每个人的基 础工资。（基础工资=基本工资+工龄工资） (5)根据员工档案表工作表中的工资数据，统计所有人的 基础工资总额，并将其填写在统计报告工作表的B2单元格 中。 (6)根据员工档案表工作表中的工资数据，统计职

9、务为项 目经理的基本工资总额，并将其填写在统计报告工作表的 B3单元格中。 (7)根据员工档案表工作表中的数据，统计东方公司本科生 平均基本工资，并将其填写在统计报告工作表的B4单元格 中。 (8)根据“员工档案表”工作表中的数据，统计东方公司最高工 资，并将其填写在统计报告工作表的B5单元格中。 MS Office2010高级应用14 (9)根据“员工档案表”工作表中的数据，统计东方公司最低 工资，并将其填写在统计报告工作表的B6单元格中。 (10)根据“员工档案表”工作表中的工资数据，用rank.avg完 成工资的排名(由大到小)。 (11)适当设置“员工档案表”中各列的列宽 (3)在统计

10、报告工作表中的B2单元格中输入=SUM(员工档案!M3:M37)， 按Enter键确认。 如下图所示： 在M3单元格中输入=K3+L3，按Enter键确认，然后向下填充公式到最后一 个员工。 (4)在统计报告工作表中的B3单元格中输入=SUMIF(员工档案!E3:E37,项 目经理,员工档案!K3:K37)，按Enter键确认。 如下图所示： (1)设置统计报告工作表中的B4单元格格式为2位小数，然后在B4单元格 中输入=AVERAGEIF(员工档案!H3:H37,本科，员工档案!K3:K37)，按Enter 键确认。 如下图所示： (2)设置统计报告工作表中的B5单元格格式为2位小数，然后在

11、B5单元格 中输入=max(统计报告!M3:M37)，按Enter键确认。 如下图所示： (3)同(2) (4)在员工档案表的N3单元格中输入=rank.avg(M3,M3:M37,0)，按Enter 键确认，然后向下填充公式到最后一个员工。 如下图所示： 三、实验内容与步骤（三） MS Office2010高级应用20 3、在“实验八.xlsx”中，请你根据“第一学期期末 成绩”工作簿文档中： (1)利用sum和average函数计算每一个学生的总分及平均成 绩；总分和平均分列数据设置保留2位小数。 (2)学号第 4、5 位代表学生所在的班级，例如： C120101代表12级1班。请通过函数提取每个学生所在的专 业并按下列对应关系填写在班级列中： 学号的4、5位 对应班级 01 1班 02 2班 03 3班 (3)根据学号，请在第一学期期末成绩工作表的姓 名列中，使用VLOOKUP函数完成姓名的自动填充。姓名和 学号的对应关系在学号对照工作表中。 (1)在K3单元格中输入=SUM(D3:J3)，按Enter键后完成总分的自动填充。 如下图所示： 在L3单元格中输入=AVERAGE(D3:J3)，按Enter键后完成平均分的自动填充。 (2)在C3单元格中输