实训十九Excel公式和函数的使用
实训目的
1.掌握公式的输入和使用。
2.掌握常用函数使用。
实训内容及步骤
一、公式的使用
一）、打开实训19文件夹下的工作簿“工资表.xls”，进行如下操作：
1、计算每个人的应发工资；
2、求出每个人的扣款：当“应发工资”小于800元时，“扣款”值为0，当“应发
工资”超过800元时，“扣款”值为（应发工资-800）*5%。
3、求出每个人的实发工资；
实训步骤：
1、打开实训19文件夹下的工作簿“工资表.xls”，在F2单元格输入公式“=D2+E2”，
然后利用自动填充的方法将公式复制到F3-F11单元格。
2、在G2单元格输入公式“=IF（F2<800，0，（F2-800）*5%）”，然后利用自动填
充的方法将公式复制到G3-G11单元格。
3、在H2单元格输入公式“=F2-G2”，然后利用自动填充的方法将公式复制到
H3-H11单元格。
二、函数的使用
一）、打开实训19文件夹下的工作簿“成绩表.xls”，进行如下操作：
1、选择工作表“第一学期成绩原表”；
2、求出每个同学的总分，平均分和名次；
3、求出每门课的总分、平均分。
4、求出每门课和总评中的>=90分人数、80-89分的人数、70-79分的人数、60-69
分的人数、不及格人数。
5、求出每个学生的等级，如果总评分数“>=90分”则为“优秀”，分数在“80-89
分”之间为“良”，分数在“70-79分”为“中”，分数在“60-69分”为“及格”，
分数“<60分”为“差”。
6、选择工作表“成绩表”；求出工作表“成绩表”中的平时平均和总评成绩，其中
总评=平时平均*20%+期中成绩*30%+期末成绩*50%，而且总评要求小数部分
四舍五入。
实训步骤：
1、打开实训19文件夹下的工作簿“成绩表.xls”，单击工作表“第一学期成绩原表”
的标签，
2、在G3单元格输入“=SUM(C3:F3)”，然后利用自动填充的方法将公式复制到单
元格G4-G34；在H3单元格输入“=A VERAGE(C3:F3)”，然后利用自动填充的
方法将公式复制到单元格H4-H34；在I3单元格输入“=RANK(H3,H$3:H$34)”，然后利用自动填充的方法将公式复制到单元格I4-I34。
3、在C40单元格输入“=SUM(C3:C34)”，然后利用自动填充的方法将公式复制到
单元格D40-F40；在C41单元格输入“=A VERAGE(C3:C34)”，然后利用自动填充的方法将公式复制到单元格D41-F41。
4、在C35单元格输入“=COUNTIF(C2:C46,">=90")”；在C36单元格输入
“=COUNTIF(C2:C46,">=80")-COUNTIF(C2:C46,">=90")”；在C37单元格输入“=COUNTIF(C2:C46,">=70")-COUNTIF(C2:C46,">=80")”；在C38单元格输入“=COUNTIF(C2:C46,">=60")-COUNTIF(C2:C46,">=70")”；在C39单元格输入“=COUNTIF(C2:C46,"<60")”；其余各科成绩用自动填充的方法复制公式。5、在J3单元格输入“=IF(H3>=90,"优秀",IF(H3>=80,"良",IF(H3>=70,"中
",IF(H3>=60,"及格","差"))))，然后利用自动填充的方法将公式复制到单元格J4-J34。
6、选择工作表“成绩表”在G2单元格输入“=A VERAGE(C2:F2)”，然后利用自
动填充的方法将公式复制到单元格G3-G11；在J2单元格输入“=ROUND(G2*0.2+H2*0.3+I2*0.5,0)”，然后利用自动填充的方法将公式复制到单元格J3-J11。
二）、打开实训19文件夹下的工作簿“工资表.xls”，进行如下操作：
1、计算“工资表”中“教务科”的“基本工资”总和；
2、统计“工资表”中“财务科”的人数与“基本工资”等于1000元的人数。
3、计算“工资表”中科室为“人事科”的同志的各项工资数据的和。
4、分别计算补助工资为100元的那些人员各项工资数据的和。
统计员常用的excel公式5、分别计算补助工资100元以上的那些人员各项工资数据的和。
6、分别计算所有姓“石”的人的各项工资数据和。
实训步骤：
1、打开实训19文件夹下的工作簿“工资表.xls”，在D13单元格输入
“=SUMIF(B2:B11,B4,D2:D11)”；
2、在D14单元格输入“=COUNTIF(B2:B11,B10)”；在D15单元格输入
“=COUNTIF(D2:D11,1000)”；
3、在D16单元格输入“=SUMIF($B2:$B11,$B2,D2:D11)”，然后利用自动填充的
方法将公式复制到单元格E16-H16。
4、在D17单元格输入“=SUMIF($E2:$E11,100,D2:D11)”，然后利用自动填充的方
法将公式复制到单元格E17-H17。
5、在D18单元格输入“=SUMIF($E2:$E11,">100",D2:D11)”，然后利用自动填充
的方法将公式复制到单元格E18-H18。
6、在D19单元格输入“=SUMIF($C2:$C11,"石*",D2:D11)”，然后利用自动填充的
方法将公式复制到单元格E19-H19。
三、自主操作练习题
打开实训19文件夹下的工作簿“公式与函数的操作题.xls”，进行如下操作：
1、使用“合格情况表”工作表中的数据，计算“合格率”，结果分别放在相应的单元格中。
2、使用“设备情况表”工作表中的数据，计算每种设备的“进货总额”与“折损总额”，结果分别放在相应的单元格中。
3、使用“销售情况表”工作表中的数据，计算“销售总额”，结果分别放在相应的单元格中。
4、使用“考试情况表”工作表中的数据，计算“总分”和“平均分”，结果分别放在相应的单元格中。
５、使用“价格日报表”工作表中的数据，计算“最大值”、“最小值”和“平均分”，结果分别放在相应的单元格中。
６、使用“考试成绩表”工作表中的数据，计算“总人数”和“平均成绩”，结果分别放在相应的单元格中。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。