EXCEL2003中公式與函數(shù)應(yīng)用實(shí)例

1、5.4.任務(wù)任務(wù)2 函數(shù)應(yīng)用實(shí)例函數(shù)應(yīng)用實(shí)例-2計(jì)算機(jī)應(yīng)用基礎(chǔ)計(jì)算機(jī)應(yīng)用基礎(chǔ)主講教師: 陳彥宏 E_mail: 云南省楚雄州牟定縣職業(yè)高級中學(xué)公式與函數(shù)應(yīng)用實(shí)例巧用公式與函數(shù)應(yīng)用實(shí)例巧用excel進(jìn)行比賽計(jì)分進(jìn)行比賽計(jì)分公式與函數(shù)應(yīng)用實(shí)例巧用公式與函數(shù)應(yīng)用實(shí)例巧用excel進(jìn)行比賽計(jì)分進(jìn)行比賽計(jì)分教學(xué)目標(biāo)教學(xué)目標(biāo)教學(xué)重難點(diǎn)教學(xué)重難點(diǎn)課前測評課前測評新課講授新課講授總結(jié)反思總結(jié)反思討論實(shí)作討論實(shí)作教學(xué)目標(biāo)教學(xué)目標(biāo)知識目標(biāo)1、利用MAX，MIN求最大值最小值，利用RANK函數(shù)求名次，利用IF函數(shù)判定獎(jiǎng)項(xiàng)。2、能正確運(yùn)用函數(shù)及自定義編寫公式，能正確引用單元格地址。能力目標(biāo)1讓學(xué)生繼續(xù)鞏固MAX，MI

2、N函數(shù)的用法，讓學(xué)生學(xué)會(huì)RANK，IF函數(shù)的使用。2、學(xué)會(huì)利用MAX，MIN，RANK，IF函數(shù)及自定義公式解決歌手賽快速計(jì)分問題，并制作比賽計(jì)分程序。3培養(yǎng)學(xué)生綜合運(yùn)用所學(xué)知識，解決實(shí)際問題的力。情感目標(biāo)1培養(yǎng)學(xué)生創(chuàng)造性思維和解決問題能力。2、通過成功制作此實(shí)例，讓學(xué)生在各種比賽中用于快速記分，讓學(xué)生能體會(huì)到一種成就感。返回返回教學(xué)重點(diǎn)及難點(diǎn)教學(xué)重點(diǎn)及難點(diǎn)重點(diǎn)：RANK，IF函數(shù)的使用難點(diǎn)：IF函數(shù)的使用中條件的判定，函數(shù)的格式，RANK函數(shù)中單元格范圍的選定及公式的復(fù)制。關(guān)鍵點(diǎn):計(jì)算評委有效得分時(shí)的自編公式返回返回請各位同學(xué)在請各位同學(xué)在C25、C26通過通過函數(shù)求出語文的最高分，最函數(shù)求

3、出語文的最高分，最低分，然后再通過公式復(fù)制低分，然后再通過公式復(fù)制計(jì)算出其他科目的最高分，計(jì)算出其他科目的最高分，最低分。最低分。C25：=max(c4:c24)或 =max(c:c)C26: =min(c4:c24)或 =min(c:c)最后再拖填充柄復(fù)制公最后再拖填充柄復(fù)制公式就完成了式就完成了課前測評課前測評返回返回學(xué)校舉行學(xué)校舉行“五四五四”校園歌手大賽，按組委會(huì)校園歌手大賽，按組委會(huì)要求設(shè)有要求設(shè)有7個(gè)評委進(jìn)行現(xiàn)場評分，采取個(gè)評委進(jìn)行現(xiàn)場評分，采取10分制，分制，比賽時(shí)去掉一個(gè)最高分，一個(gè)最低分，取有效比賽時(shí)去掉一個(gè)最高分，一個(gè)最低分，取有效得分的平均分作為選手最后得分，根據(jù)最后得得

4、分的平均分作為選手最后得分，根據(jù)最后得分判定名次。獎(jiǎng)項(xiàng)設(shè)置為第分判定名次。獎(jiǎng)項(xiàng)設(shè)置為第1名一等獎(jiǎng)，名一等獎(jiǎng)，23名二等獎(jiǎng)，名二等獎(jiǎng)，46名三等獎(jiǎng)，其余的為優(yōu)秀獎(jiǎng)。名三等獎(jiǎng)，其余的為優(yōu)秀獎(jiǎng)。要求在比賽時(shí)評委打出分?jǐn)?shù)后，立即公布：去要求在比賽時(shí)評委打出分?jǐn)?shù)后，立即公布：去掉一個(gè)最高分掉一個(gè)最高分XX分，去掉一個(gè)最低分分，去掉一個(gè)最低分XX分，分，X號選手最后得分為號選手最后得分為XX，比賽結(jié)束后立即公布，比賽結(jié)束后立即公布出獎(jiǎng)項(xiàng)。出獎(jiǎng)項(xiàng)。問題的導(dǎo)入問題的導(dǎo)入這是上屆歌手賽的成績這是上屆歌手賽的成績統(tǒng)計(jì)表，比賽時(shí)安排統(tǒng)計(jì)表，比賽時(shí)安排2位老師參加統(tǒng)計(jì)，最后位老師參加統(tǒng)計(jì)，最后一直到比賽結(jié)束后一直到比

5、賽結(jié)束后10多多分鐘才計(jì)算出得分，排分鐘才計(jì)算出得分，排出名次，判定出獎(jiǎng)項(xiàng)。出名次，判定出獎(jiǎng)項(xiàng)。每次當(dāng)評委打分后不能每次當(dāng)評委打分后不能立即得出該選手得分。立即得出該選手得分。師生們對計(jì)分都有意見。師生們對計(jì)分都有意見。能否通過能否通過excel改進(jìn)比改進(jìn)比賽計(jì)分，達(dá)到比賽時(shí)的賽計(jì)分，達(dá)到比賽時(shí)的預(yù)期效果呢？預(yù)期效果呢？這是一張正在運(yùn)動(dòng)的抽象的公交車圖，請你根據(jù)上圖判斷車是由A地開往B地，還是由B地開往A。AB那大家來看一個(gè)小學(xué)生能做，你卻可能不會(huì)做的問題這個(gè)問題看似很難，無從下手，其實(shí)這個(gè)問這個(gè)問題看似很難，無從下手，其實(shí)這個(gè)問題的關(guān)鍵是你有沒有仔細(xì)觀察，能否找到問題的關(guān)鍵是你有沒有仔細(xì)觀察

6、，能否找到問題切入的關(guān)鍵點(diǎn)，別急，好好想想題切入的關(guān)鍵點(diǎn)，別急，好好想想.告訴你答案：要注意車門，我們看到的告訴你答案：要注意車門，我們看到的一邊沒車門，那車門就在看不到另一邊，一邊沒車門，那車門就在看不到另一邊，公交車都是從右邊上車，因此可以斷定公交車都是從右邊上車，因此可以斷定車是由車是由BA寄語：其實(shí)遇到什么問題，關(guān)鍵其實(shí)遇到什么問題，關(guān)鍵要認(rèn)清事物的規(guī)律，善于思索，要認(rèn)清事物的規(guī)律，善于思索，找到切入點(diǎn)，一定能找到解決問找到切入點(diǎn)，一定能找到解決問題的方法。題的方法。我們今天提出來的問題，其實(shí)我們今天提出來的問題，其實(shí)通過通過excel的公式和函數(shù)可以很的公式和函數(shù)可以很輕松地解決這個(gè)

7、問題！輕松地解決這個(gè)問題！ 最高分和最低分可以用最高分和最低分可以用MAX、MIN函數(shù)函數(shù)解決。解決。 選手最后得分只需用總分減去最高分和選手最后得分只需用總分減去最高分和最低分，然后再除于有效評委數(shù)。（這最低分，然后再除于有效評委數(shù)。（這里是里是7-2=5個(gè)）個(gè)） 判定名次使用判定名次使用RANK函數(shù)函數(shù) 判定獎(jiǎng)項(xiàng)可以使用判定獎(jiǎng)項(xiàng)可以使用IF函數(shù)函數(shù)讓我們來分析一下-現(xiàn)在我們來學(xué)習(xí)RANK和IF函數(shù)的使用方法-RANK函數(shù)函數(shù)是排名函數(shù)是排名函數(shù)。RANK函數(shù)最常用的是求某一個(gè)數(shù)值在 某一區(qū)域內(nèi)的排名。看下面實(shí)例：在在K4輸入以下公式：輸入以下公式： =RANk(J4,$J$4:$J$23)

8、公式的理解是：求公式的理解是：求j4單元單元格的數(shù)值在格的數(shù)值在 j4:j23這個(gè)區(qū)這個(gè)區(qū)域的名次域的名次.注意：這里的引用區(qū)域注意：這里的引用區(qū)域$j$4:$j$23要用絕對引用，要用絕對引用，不然公式一復(fù)制引用區(qū)域不然公式一復(fù)制引用區(qū)域就不對了。就不對了。如果要整列比較名次，還如果要整列比較名次，還可以把可以把K4公式改為公式改為: =RANK(J4,J:J)IF函數(shù)的使用1IF函數(shù)的語法結(jié)構(gòu)IF函數(shù)的語法結(jié)構(gòu)：IF(條件,結(jié)果1,結(jié)果2)。2IF函數(shù)的功能對滿足條件的數(shù)據(jù)進(jìn)行處理，條件滿足則輸出結(jié)果1，不滿足則輸出結(jié)果2。函數(shù)還可以嵌套，雖然函數(shù)使用有點(diǎn)復(fù)雜，但不難理解。 下面我們用歌手

9、賽計(jì)分這個(gè)實(shí)例來理解這個(gè)函數(shù)的作用，同學(xué)們也以先模仿，再理解，再學(xué)會(huì)使用比賽獎(jiǎng)項(xiàng)設(shè)置為第比賽獎(jiǎng)項(xiàng)設(shè)置為第1名一等獎(jiǎng)，名一等獎(jiǎng)，23名二等獎(jiǎng)，名二等獎(jiǎng)，46名名三等獎(jiǎng)，其余的為三等獎(jiǎng)，其余的為優(yōu)秀獎(jiǎng)。根據(jù)名次優(yōu)秀獎(jiǎng)。根據(jù)名次來判定獎(jiǎng)項(xiàng)時(shí)來判定獎(jiǎng)項(xiàng)時(shí),我我們引入邏輯判斷們引入邏輯判斷,如果名次如果名次=1,為為“一等獎(jiǎng)一等獎(jiǎng)”，剩下，剩下的如果名次的如果名次=3,為為“二等獎(jiǎng)二等獎(jiǎng)”，再剩，再剩余的如果名次余的如果名次=6,為為“三等獎(jiǎng)三等獎(jiǎng)”，最，最后剩下的都為后剩下的都為“優(yōu)優(yōu)秀獎(jiǎng)秀獎(jiǎng)”。我們可以在O2單元格插入公式：=IF(N2=1,一等獎(jiǎng),IF(N2=3,二等,IF(N2=6,三等獎(jiǎng),優(yōu)秀

10、獎(jiǎng)) 。公式運(yùn)行后就會(huì)得到上圖所示結(jié)果注意：此公式編寫中要注意名次到獎(jiǎng)項(xiàng)的邏輯判斷，此外還要注意公式的格式，尤其是公式嵌套后使用的格式。下面我們來通過公式和函數(shù)解決歌手賽計(jì)分的問題在J4插入公式: =max(c2:I2),然后往下填充復(fù)制公式就求出所有選手最高分在k4插入公式：=min(c2:I2),然后往下填充復(fù)制公式就求出所有選手最低分在L2里輸入公式：=sum(c2:I2I2），住下填充復(fù)制公式就得出所有選手得分總分），住下填充復(fù)制公式就得出所有選手得分總分計(jì)算最后得分，在計(jì)算最后得分，在M2M2輸入公式：輸入公式：= (= (sum(c2:I2)-j2-k2)/5,或者輸入：或者輸入：

11、=(L2-j2-k2)/5,這里用總分減去最高分，最低分，這里用總分減去最高分，最低分，7個(gè)評委的得分有效的還有個(gè)評委的得分有效的還有5個(gè)，再除于個(gè)，再除于5，就是，就是每位選手最后得分，最后再往下填充復(fù)制公式就得到所有選手的最后得分每位選手最后得分，最后再往下填充復(fù)制公式就得到所有選手的最后得分求名次求名次-在在N2輸入：輸入：=RANK(M2,$M$2:$M$21),住下填充復(fù)制公式即得到每位選手名次。住下填充復(fù)制公式即得到每位選手名次。注意：這里引用區(qū)域一定要設(shè)為絕對引用。也可以在注意：這里引用區(qū)域一定要設(shè)為絕對引用。也可以在N2輸入：輸入：=RANK(M2,M:M),把它設(shè)把它設(shè)為整個(gè)

12、為整個(gè)M列比較名次，這樣的話就不必考慮是否為絕對引用了，即使選手再增加，這個(gè)公列比較名次，這樣的話就不必考慮是否為絕對引用了，即使選手再增加，這個(gè)公式仍然是正確的，只管填充復(fù)制就行式仍然是正確的，只管填充復(fù)制就行根據(jù)獎(jiǎng)項(xiàng)判定名次-在O2輸入： =IF(N2=1,一等獎(jiǎng),IF(N2=3,二等獎(jiǎng),IF(N2=6,三等獎(jiǎng),優(yōu)秀獎(jiǎng))，然后再住下填充復(fù)制公式，就能判定出每位選手按名次獲得的獎(jiǎng)項(xiàng)。特別要注意：公式中用到的“ ”號，逗號都要是英文半角下的。這就是通過公式和函數(shù)得出的結(jié)果，大家看，這就是通過公式和函數(shù)得出的結(jié)果，大家看，計(jì)算起來是不是很方便計(jì)算起來是不是很方便 前面我們是用已有的數(shù)據(jù)計(jì)算出前面

13、我們是用已有的數(shù)據(jù)計(jì)算出比賽結(jié)果，要把這個(gè)計(jì)分程序應(yīng)用于比賽結(jié)果，要把這個(gè)計(jì)分程序應(yīng)用于實(shí)際計(jì)分中，只需要把我們前面做好實(shí)際計(jì)分中，只需要把我們前面做好的這個(gè)文件中評委打分刪除，就可以的這個(gè)文件中評委打分刪除，就可以做成一個(gè)計(jì)分程序，我們看一下最后做成一個(gè)計(jì)分程序，我們看一下最后做成的效果。做成的效果。比賽計(jì)分時(shí)，只需要輸入各位評委所打出的分?jǐn)?shù)即可，比賽計(jì)分時(shí)，只需要輸入各位評委所打出的分?jǐn)?shù)即可，立即便可公布每位選手的最高分，最低分，最后得分，立即便可公布每位選手的最高分，最低分，最后得分，比賽一結(jié)束就可以公布名次，并按獎(jiǎng)項(xiàng)發(fā)獎(jiǎng)，多方便，比賽一結(jié)束就可以公布名次，并按獎(jiǎng)項(xiàng)發(fā)獎(jiǎng)，多方便，這就是這

14、就是excel的妙用。的妙用。返回返回課堂討論：課堂討論：1、如果參賽選手增加了，公式應(yīng)該如何修改？2、如果評委數(shù)變成了9個(gè)，公式應(yīng)該怎樣改變？上機(jī)操作題上機(jī)操作題 模仿我們已做好的比賽計(jì)分程序，模仿我們已做好的比賽計(jì)分程序，制作一個(gè)評委數(shù)為制作一個(gè)評委數(shù)為9個(gè)，參賽選手人數(shù)個(gè)，參賽選手人數(shù)不固定的一個(gè)比賽計(jì)分程序，設(shè)獎(jiǎng)方案不固定的一個(gè)比賽計(jì)分程序，設(shè)獎(jiǎng)方案中獎(jiǎng)項(xiàng)設(shè)置為第中獎(jiǎng)項(xiàng)設(shè)置為第12名一等獎(jiǎng)，名一等獎(jiǎng)，36名名二等獎(jiǎng)，二等獎(jiǎng)，712名三等獎(jiǎng)，其余的沒有名三等獎(jiǎng)，其余的沒有獎(jiǎng)。最后得分要求保留獎(jiǎng)。最后得分要求保留3位小數(shù)。位小數(shù)。返回返回總結(jié)反思總結(jié)反思1. RANK函數(shù)中公式中引用范圍是此公式使用函數(shù)中公式中引用范圍是此公式使用中的難點(diǎn)，對于一時(shí)難于理解的同學(xué)只要通中的難點(diǎn)，對于一時(shí)難于理解的同學(xué)只要通過上機(jī)過后出現(xiàn)名次不正確，他會(huì)自我發(fā)現(xiàn)，過上機(jī)過后出現(xiàn)名次不正確，他會(huì)自我發(fā)現(xiàn)，自我糾正引用范圍的錯(cuò)誤；自我糾正引用范圍的錯(cuò)誤；2. 在此實(shí)例的教學(xué)中，學(xué)生對于在此實(shí)例的教學(xué)中，學(xué)生對于IF函數(shù)中條件函數(shù)中條件的判定中的邏輯關(guān)系一時(shí)還難于理解，但只的判定中的邏輯關(guān)系一時(shí)還難于理解，但只要模仿著用多了一定會(huì)理解的；要模仿著用多了一定會(huì)理解的；3. 評委數(shù)和選手?jǐn)?shù)發(fā)生變化，公式如