《EXCEL2003中公式与函数应用实例》由会员分享，可在线阅读，更多相关《EXCEL2003中公式与函数应用实例（26页珍藏版）》请在文档大全上搜索。
1、5.4.任务任务2 函数应用实例函数应用实例-2计算机应用基础计算机应用基础主讲教师: 陈彦宏 E_mail: 云南省楚雄州牟定县职业高级中学公式与函数应用实例巧用公式与函数应用实例巧用excel进行比赛计分进行比赛计分公式与函数应用实例巧用公式与函数应用实例巧用excel进行比赛计分进行比赛计分教学目标教学目标教学重难点教学重难点课前测评课前测评新课讲授新课讲授总结反思总结反思讨论实作讨论实作教学目标教学目标知识目标1、利用MAX，MIN求最大值最小值，利用RANK函数求名次，利用IF函数判定奖项。2、能正确运用函数及自定义编写公式，能正确引用单元格地址。能力目标1让学生继续巩固MAX，MI
2、N函数的用法，让学生学会RANK，IF函数的使用。2、学会利用MAX，MIN，RANK，IF函数及自定义公式解决歌手赛快速计分问题，并制作比赛计分程序。3培养学生综合运用所学知识，解决实际问题的力。情感目标1培养学生创造性思维和解决问题能力。2、通过成功制作此实例，让学生在各种比赛中用于快速记分，让学生能体会到一种成就感。返回返回教学重点及难点教学重点及难点重点：RANK，IF函数的使用难点：IF函数的使用中条件的判定，函数的格式，RANK函数中单元格范围的选定及公式的复制。关键点:计算评委有效得分时的自编公式返回返回请各位同学在请各位同学在C25、C26通过通过函数求出语文的最高分，最函数求
3、出语文的最高分，最低分，然后再通过公式复制低分，然后再通过公式复制计算出其他科目的最高分，计算出其他科目的最高分，最低分。最低分。C25：=max(c4:c24)或 =max(c:c)C26: =min(c4:c24)或 =min(c:c)最后再拖填充柄复制公最后再拖填充柄复制公式就完成了式就完成了课前测评课前测评返回返回学校举行学校举行“五四五四”校园歌手大赛，按组委会校园歌手大赛，按组委会要求设有要求设有7个评委进行现场评分，采取个评委进行现场评分，采取10分制，分制，比赛时去掉一个最高分，一个最低分，取有效比赛时去掉一个最高分，一个最低分，取有效得分的平均分作为选手最后得分，根据最后得得
4、分的平均分作为选手最后得分，根据最后得分判定名次。奖项设置为第分判定名次。奖项设置为第1名一等奖，名一等奖，23名二等奖，名二等奖，46名三等奖，其余的为优秀奖。名三等奖，其余的为优秀奖。要求在比赛时评委打出分数后，立即公布：去要求在比赛时评委打出分数后，立即公布：去掉一个最高分掉一个最高分XX分，去掉一个最低分分，去掉一个最低分XX分，分，X号选手最后得分为号选手最后得分为XX，比赛结束后立即公布，比赛结束后立即公布出奖项。出奖项。问题的导入问题的导入这是上届歌手赛的成绩这是上届歌手赛的成绩统计表，比赛时安排统计表，比赛时安排2位老师参加统计，最后位老师参加统计，最后一直到比赛结束后一直到比
5、赛结束后10多多分钟才计算出得分，排分钟才计算出得分，排出名次，判定出奖项。出名次，判定出奖项。每次当评委打分后不能每次当评委打分后不能立即得出该选手得分。立即得出该选手得分。师生们对计分都有意见。师生们对计分都有意见。能否通过能否通过excel改进比改进比赛计分，达到比赛时的赛计分，达到比赛时的预期效果呢？预期效果呢？这是一张正在运动的抽象的公交车图，请你根据上图判断车是由A地开往B地，还是由B地开往A。AB那大家来看一个小学生能做，你却可能不会做的问题这个问题看似很难，无从下手，其实这个问这个问题看似很难，无从下手，其实这个问题的关键是你有没有仔细观察，能否找到问题的关键是你有没有仔细观察
6、，能否找到问题切入的关键点，别急，好好想想题切入的关键点，别急，好好想想.告诉你答案：要注意车门，我们看到的告诉你答案：要注意车门，我们看到的一边没车门，那车门就在看不到另一边，一边没车门，那车门就在看不到另一边，公交车都是从右边上车，因此可以断定公交车都是从右边上车，因此可以断定车是由车是由BA寄语：其实遇到什么问题，关键其实遇到什么问题，关键要认清事物的规律，善于思索，要认清事物的规律，善于思索，找到切入点，一定能找到解决问找到切入点，一定能找到解决问题的方法。题的方法。我们今天提出来的问题，其实我们今天提出来的问题，其实通过通过excel的公式和函数可以很的公式和函数可以很轻松地解决这个
7、问题！轻松地解决这个问题！ 最高分和最低分可以用最高分和最低分可以用MAX、MIN函数函数解决。解决。 选手最后得分只需用总分减去最高分和选手最后得分只需用总分减去最高分和最低分，然后再除于有效评委数。（这最低分，然后再除于有效评委数。（这里是里是7-2=5个）个） 判定名次使用判定名次使用RANK函数函数 判定奖项可以使用判定奖项可以使用IF函数函数让我们来分析一下-现在我们来学习RANK和IF函数的使用方法-RANK函数函数是排名函数是排名函数。RANK函数最常用的是求某一个数值在 某一区域内的排名。看下面实例：在在K4输入以下公式：输入以下公式： =RANk(J4,$J$4:$J$23)
8、公式的理解是：求公式的理解是：求j4单元单元格的数值在格的数值在 j4:j23这个区这个区域的名次域的名次.注意：这里的引用区域注意：这里的引用区域$j$4:$j$23要用绝对引用，要用绝对引用，不然公式一复制引用区域不然公式一复制引用区域就不对了。就不对了。如果要整列比较名次，还如果要整列比较名次，还可以把可以把K4公式改为公式改为: =RANK(J4,J:J)IF函数的使用1IF函数的语法结构IF函数的语法结构：IF(条件,结果1,结果2)。2IF函数的功能对满足条件的数据进行处理，条件满足则输出结果1，不满足则输出结果2。函数还可以嵌套，虽然函数使用有点复杂，但不难理解。 下面我们用歌手
9、赛计分这个实例来理解这个函数的作用，同学们也以先模仿，再理解，再学会使用比赛奖项设置为第比赛奖项设置为第1名一等奖，名一等奖，23名二等奖，名二等奖，46名名三等奖，其余的为三等奖，其余的为优秀奖。根据名次优秀奖。根据名次来判定奖项时来判定奖项时,我我们引入逻辑判断们引入逻辑判断,如果名次如果名次=1,为为“一等奖一等奖”，剩下，剩下的如果名次的如果名次=3,为为“二等奖二等奖”，再剩，再剩余的如果名次余的如果名次=6,为为“三等奖三等奖”，最，最后剩下的都为后剩下的都为“优优秀奖秀奖”。我们可以在O2单元格插入公式：=IF(N2=1,一等奖,IF(N2=3,二等,IF(N2=6,三等奖,优秀
10、奖) 。公式运行后就会得到上图所示结果注意：此公式编写中要注意名次到奖项的逻辑判断，此外还要注意公式的格式，尤其是公式嵌套后使用的格式。下面我们来通过公式和函数解决歌手赛计分的问题在J4插入公式: =max(c2:I2),然后往下填充复制公式就求出所有选手最高分在k4插入公式：=min(c2:I2),然后往下填充复制公式就求出所有选手最低分在L2里输入公式：=sum(c2:I2I2），住下填充复制公式就得出所有选手得分总分），住下填充复制公式就得出所有选手得分总分计算最后得分，在计算最后得分，在M2M2输入公式：输入公式：= (= (sum(c2:I2)-j2-k2)/5,或者输入：或者输入：
11、=(L2-j2-k2)/5,这里用总分减去最高分，最低分，这里用总分减去最高分，最低分，7个评委的得分有效的还有个评委的得分有效的还有5个，再除于个，再除于5，就是，就是每位选手最后得分，最后再往下填充复制公式就得到所有选手的最后得分每位选手最后得分，最后再往下填充复制公式就得到所有选手的最后得分求名次求名次-在在N2输入：输入：=RANK(M2,$M$2:$M$21),住下填充复制公式即得到每位选手名次。住下填充复制公式即得到每位选手名次。注意：这里引用区域一定要设为绝对引用。也可以在注意：这里引用区域一定要设为绝对引用。也可以在N2输入：输入：=RANK(M2,M:M),把它设把它设为整个
12、为整个M列比较名次，这样的话就不必考虑是否为绝对引用了，即使选手再增加，这个公列比较名次，这样的话就不必考虑是否为绝对引用了，即使选手再增加，这个公式仍然是正确的，只管填充复制就行式仍然是正确的，只管填充复制就行根据奖项判定名次-在O2输入： =IF(N2=1,一等奖,IF(N2=3,二等奖,IF(N2=6,三等奖,优秀奖)，然后再住下填充复制公式，就能判定出每位选手按名次获得的奖项。特别要注意：公式中用到的“ ”号，逗号都要是英文半角下的。这就是通过公式和函数得出的结果，大家看，这就是通过公式和函数得出的结果，大家看，计算起来是不是很方便计算起来是不是很方便 前面我们是用已有的数据计算出前面
13、我们是用已有的数据计算出比赛结果，要把这个计分程序应用于比赛结果，要把这个计分程序应用于实际计分中，只需要把我们前面做好实际计分中，只需要把我们前面做好的这个文件中评委打分删除，就可以的这个文件中评委打分删除，就可以做成一个计分程序，我们看一下最后做成一个计分程序，我们看一下最后做成的效果。做成的效果。比赛计分时，只需要输入各位评委所打出的分数即可，比赛计分时，只需要输入各位评委所打出的分数即可，立即便可公布每位选手的最高分，最低分，最后得分，立即便可公布每位选手的最高分，最低分，最后得分，比赛一结束就可以公布名次，并按奖项发奖，多方便，比赛一结束就可以公布名次，并按奖项发奖，多方便，这就是这
14、就是excel的妙用。的妙用。返回返回课堂讨论：课堂讨论：1、如果参赛选手增加了，公式应该如何修改？2、如果评委数变成了9个，公式应该怎样改变？上机操作题上机操作题 模仿我们已做好的比赛计分程序，模仿我们已做好的比赛计分程序，制作一个评委数为制作一个评委数为9个，参赛选手人数个，参赛选手人数不固定的一个比赛计分程序，设奖方案不固定的一个比赛计分程序，设奖方案中奖项设置为第中奖项设置为第12名一等奖，名一等奖，36名名二等奖，二等奖，712名三等奖，其余的没有名三等奖，其余的没有奖。最后得分要求保留奖。最后得分要求保留3位小数。位小数。返回返回总结反思总结反思1. RANK函数中公式中引用范围是
15、此公式使用函数中公式中引用范围是此公式使用中的难点，对于一时难于理解的同学只要通中的难点，对于一时难于理解的同学只要通过上机过后出现名次不正确，他会自我发现，过上机过后出现名次不正确，他会自我发现，自我纠正引用范围的错误；自我纠正引用范围的错误；2. 在此实例的教学中，学生对于在此实例的教学中，学生对于IF函数中条件函数中条件的判定中的逻辑关系一时还难于理解，但只的判定中的逻辑关系一时还难于理解，但只要模仿着用多了一定会理解的；要模仿着用多了一定会理解的；3. 评委数和选手数发生变化，公式如何发生变评委数和选手数发生变化，公式如何发生变化是这个实例能否成功的关键点，学生在上化是这个实例能否成功的关键点，学生在上机时出现问题后只要教师帮其纠正其错误，机时出现问题后只要教师帮其纠正其错误，他就会明白其中的道理。他就会明白其中的道理。返回返回职高生普遍反映学习excel有一定困难，尤其是公式和函数更难，本实例通过循序渐进，学生学会了用excel对各种比赛进行快速记分，并成功地制作出比赛计分程序，让学生充分感受到学习excel的乐趣，并有一种成就感和荣誉感。
