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è)高級(jí)中學(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)課前測(cè)評(píng)課前測(cè)評(píng)新課講授新課講授總結(jié)反思總結(jié)反思討論實(shí)作討論實(shí)作教學(xué)目標(biāo)教學(xué)目標(biāo)知識(shí)目標(biāo)1、利用MAX，MIN求最大值最小值，利用RANK函數(shù)求名次，利用IF函數(shù)判定獎(jiǎng)項(xiàng)。2、能正確運(yùn)用函數(shù)及自定義編寫(xiě)公式，能正確引用單元格地址。能力目標(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ì)分問(wèn)題，并制作比賽計(jì)分程序。3培養(yǎng)學(xué)生綜合運(yùn)用所學(xué)知識(shí)，解決實(shí)際問(wèn)題的力。情感目標(biāo)1培養(yǎng)學(xué)生創(chuàng)造性思維和解決問(wèn)題能力。2、通過(guò)成功制作此實(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ì)算評(píng)委有效得分時(shí)的自編公式返回返回請(qǐng)各位同學(xué)在請(qǐng)各位同學(xué)在C25、C26通過(guò)通過(guò)函數(shù)求出語(yǔ)文的最高分，最函數(shù)求

3、出語(yǔ)文的最高分，最低分，然后再通過(guò)公式復(fù)制低分，然后再通過(guò)公式復(fù)制計(jì)算出其他科目的最高分，計(jì)算出其他科目的最高分，最低分。最低分。C25：=max(c4:c24)或 =max(c:c)C26: =min(c4:c24)或 =min(c:c)最后再拖填充柄復(fù)制公最后再拖填充柄復(fù)制公式就完成了式就完成了課前測(cè)評(píng)課前測(cè)評(píng)返回返回學(xué)校舉行學(xué)校舉行“五四五四”校園歌手大賽，按組委會(huì)校園歌手大賽，按組委會(huì)要求設(shè)有要求設(shè)有7個(gè)評(píng)委進(jìn)行現(xiàn)場(chǎng)評(píng)分，采取個(gè)評(píng)委進(jìn)行現(xiàn)場(chǎng)評(píng)分，采取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í)評(píng)委打出分?jǐn)?shù)后，立即公布：去要求在比賽時(shí)評(píng)委打出分?jǐn)?shù)后，立即公布：去掉一個(gè)最高分掉一個(gè)最高分XX分，去掉一個(gè)最低分分，去掉一個(gè)最低分XX分，分，X號(hào)選手最后得分為號(hào)選手最后得分為XX，比賽結(jié)束后立即公布，比賽結(jié)束后立即公布出獎(jiǎng)項(xiàng)。出獎(jiǎng)項(xiàng)。問(wèn)題的導(dǎo)入問(wèn)題的導(dǎo)入這是上屆歌手賽的成績(jī)這是上屆歌手賽的成績(jī)統(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)評(píng)委打分后不能每次當(dāng)評(píng)委打分后不能立即得出該選手得分。立即得出該選手得分。師生們對(duì)計(jì)分都有意見(jiàn)。師生們對(duì)計(jì)分都有意見(jiàn)。能否通過(guò)能否通過(guò)excel改進(jìn)比改進(jìn)比賽計(jì)分，達(dá)到比賽時(shí)的賽計(jì)分，達(dá)到比賽時(shí)的預(yù)期效果呢？預(yù)期效果呢？這是一張正在運(yùn)動(dòng)的抽象的公交車(chē)圖，請(qǐng)你根據(jù)上圖判斷車(chē)是由A地開(kāi)往B地，還是由B地開(kāi)往A。AB那大家來(lái)看一個(gè)小學(xué)生能做，你卻可能不會(huì)做的問(wèn)題這個(gè)問(wèn)題看似很難，無(wú)從下手，其實(shí)這個(gè)問(wèn)這個(gè)問(wèn)題看似很難，無(wú)從下手，其實(shí)這個(gè)問(wèn)題的關(guān)鍵是你有沒(méi)有仔細(xì)觀察，能否找到問(wèn)題的關(guān)鍵是你有沒(méi)有仔細(xì)觀察

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

7、問(wèn)題！輕松地解決這個(gè)問(wèn)題！ 最高分和最低分可以用最高分和最低分可以用MAX、MIN函數(shù)函數(shù)解決。解決。 選手最后得分只需用總分減去最高分和選手最后得分只需用總分減去最高分和最低分，然后再除于有效評(píng)委數(shù)。（這最低分，然后再除于有效評(píng)委數(shù)。（這里是里是7-2=5個(gè)）個(gè)） 判定名次使用判定名次使用RANK函數(shù)函數(shù) 判定獎(jiǎng)項(xiàng)可以使用判定獎(jiǎng)項(xiàng)可以使用IF函數(shù)函數(shù)讓我們來(lái)分析一下-現(xiàn)在我們來(lái)學(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要用絕對(duì)引用，要用絕對(duì)引用，不然公式一復(fù)制引用區(qū)域不然公式一復(fù)制引用區(qū)域就不對(duì)了。就不對(duì)了。如果要整列比較名次，還如果要整列比較名次，還可以把可以把K4公式改為公式改為: =RANK(J4,J:J)IF函數(shù)的使用1IF函數(shù)的語(yǔ)法結(jié)構(gòu)IF函數(shù)的語(yǔ)法結(jié)構(gòu)：IF(條件,結(jié)果1,結(jié)果2)。2IF函數(shù)的功能對(duì)滿(mǎn)足條件的數(shù)據(jù)進(jìn)行處理，條件滿(mǎn)足則輸出結(jié)果1，不滿(mǎn)足則輸出結(jié)果2。函數(shù)還可以嵌套，雖然函數(shù)使用有點(diǎn)復(fù)雜，但不難理解。 下面我們用歌手

9、賽計(jì)分這個(gè)實(shí)例來(lái)理解這個(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ù)名次來(lái)判定獎(jiǎng)項(xiàng)時(shí)來(lái)判定獎(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é)果注意：此公式編寫(xiě)中要注意名次到獎(jiǎng)項(xiàng)的邏輯判斷，此外還要注意公式的格式，尤其是公式嵌套后使用的格式。下面我們來(lái)通過(guò)公式和函數(shù)解決歌手賽計(jì)分的問(wèn)題在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è)評(píng)委的得分有效的還有個(gè)評(píng)委的得分有效的還有5個(gè)，再除于個(gè)，再除于5，就是，就是每位選手最后得分，最后再往下填充復(fù)制公式就得到所有選手的最后得分每位選手最后得分，最后再往下填充復(fù)制公式就得到所有選手的最后得分求名次求名次-在在N2輸入：輸入：=RANK(M2,$M$2:$M$21),住下填充復(fù)制公式即得到每位選手名次。住下填充復(fù)制公式即得到每位選手名次。注意：這里引用區(qū)域一定要設(shè)為絕對(duì)引用。也可以在注意：這里引用區(qū)域一定要設(shè)為絕對(duì)引用。也可以在N2輸入：輸入：=RANK(M2,M:M),把它設(shè)把它設(shè)為整個(gè)

12、為整個(gè)M列比較名次，這樣的話(huà)就不必考慮是否為絕對(duì)引用了，即使選手再增加，這個(gè)公列比較名次，這樣的話(huà)就不必考慮是否為絕對(duì)引用了，即使選手再增加，這個(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)。特別要注意：公式中用到的“ ”號(hào)，逗號(hào)都要是英文半角下的。這就是通過(guò)公式和函數(shù)得出的結(jié)果，大家看，這就是通過(guò)公式和函數(shù)得出的結(jié)果，大家看，計(jì)算起來(lái)是不是很方便計(jì)算起來(lái)是不是很方便 前面我們是用已有的數(shù)據(jù)計(jì)算出前面

13、我們是用已有的數(shù)據(jù)計(jì)算出比賽結(jié)果，要把這個(gè)計(jì)分程序應(yīng)用于比賽結(jié)果，要把這個(gè)計(jì)分程序應(yīng)用于實(shí)際計(jì)分中，只需要把我們前面做好實(shí)際計(jì)分中，只需要把我們前面做好的這個(gè)文件中評(píng)委打分刪除，就可以的這個(gè)文件中評(píng)委打分刪除，就可以做成一個(gè)計(jì)分程序，我們看一下最后做成一個(gè)計(jì)分程序，我們看一下最后做成的效果。做成的效果。比賽計(jì)分時(shí)，只需要輸入各位評(píng)委所打出的分?jǐn)?shù)即可，比賽計(jì)分時(shí)，只需要輸入各位評(píng)委所打出的分?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、如果評(píng)委數(shù)變成了9個(gè)，公式應(yīng)該怎樣改變？上機(jī)操作題上機(jī)操作題 模仿我們已做好的比賽計(jì)分程序，模仿我們已做好的比賽計(jì)分程序，制作一個(gè)評(píng)委數(shù)為制作一個(gè)評(píng)委數(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)，其余的沒(méi)有名三等獎(jiǎng)，其余的沒(méi)有獎(jiǎng)。最后得分要求保留獎(jiǎng)。最后得分要求保留3位小數(shù)。位小數(shù)。返回返回總結(jié)反思總結(jié)反思1. RANK函數(shù)中公式中引用范圍是此公式使用函數(shù)中公式中引用范圍是此公式使用中的難點(diǎn)，對(duì)于一時(shí)難于理解的同學(xué)只要通中的難點(diǎn)，對(duì)于一時(shí)難于理解的同學(xué)只要通過(guò)上機(jī)過(guò)后出現(xiàn)名次不正確，他會(huì)自我發(fā)現(xiàn)，過(guò)上機(jī)過(guò)后出現(xiàn)名次不正確，他會(huì)自我發(fā)現(xiàn)，自我糾正引用范圍的錯(cuò)誤；自我糾正引用范圍的錯(cuò)誤；2. 在此實(shí)例的教學(xué)中，學(xué)生對(duì)于在此實(shí)例的教學(xué)中，學(xué)生對(duì)于IF函數(shù)中條件函數(shù)中條件的判定中的邏輯關(guān)系一時(shí)還難于理解，但只的判定中的邏輯關(guān)系一時(shí)還難于理解，但只要模仿著用多了一定會(huì)理解的；要模仿著用多了一定會(huì)理解的；3. 評(píng)委數(shù)和選手?jǐn)?shù)發(fā)生變化，公式如