計(jì)算機(jī)應(yīng)用基礎(chǔ)課件第6章Excel綜合應(yīng)用課件

第6章Excel綜合應(yīng)用《計(jì)算機(jī)基礎(chǔ)》課程組2013年9月

6.1學(xué)生成績(jī)處理

6.1.1知識(shí)點(diǎn)解析排位函數(shù)RANK.EQRANK.EQ函數(shù)的功能是返回一個(gè)數(shù)字在數(shù)據(jù)區(qū)域中的排位。其排位大小與數(shù)據(jù)區(qū)域中的其他值相關(guān)。如果多個(gè)值具有相同的排位，則返回該組數(shù)值的最高排位。要計(jì)算B3單元格在B2:B10單元格區(qū)域中排在第幾位，其對(duì)應(yīng)的公式為“=RANKD.EQ(B3,B3:B10,0)”。6.1學(xué)生成績(jī)處理

6.1.1知識(shí)點(diǎn)解析排位函數(shù)RANK.EQRANK.EQ函數(shù)的語(yǔ)法格式為RANK.EQ(Number,Ref,Order)，三個(gè)參數(shù)的含義分別是：Number：需要找到排位位置的數(shù)字或單元格。Ref：需要參與排位的數(shù)據(jù)區(qū)域，Ref中的非數(shù)值型值將被忽略。Order：如果order為0或省略，是按降序排位，否則為升序排位。函數(shù)RANK.EQ對(duì)重復(fù)數(shù)的排位相同。但重復(fù)數(shù)的存在將影響后續(xù)數(shù)值的排位。例如B3和B6單元格的數(shù)值一樣，它們對(duì)應(yīng)的排位都為4，B9單元格的排位則為6（沒(méi)有排位為5的數(shù)值）。6.1學(xué)生成績(jī)處理

6.1.1知識(shí)點(diǎn)解析絕對(duì)引用在計(jì)算出3單元格的排名后，通過(guò)公式復(fù)制計(jì)算其他人的排名時(shí)會(huì)發(fā)現(xiàn)，B6單元格的數(shù)值與B3單元格相等，而其排名卻是2。查看C6單元格的公式，其公式為“=RANK.EQ(B6,B6:B13,0)”。公式顯示的是“B6單元格在B6:B13單元格區(qū)域中排在第幾位”，而實(shí)際上是要計(jì)算“B6單元格在B2:B10單元格區(qū)域中排在第幾位”。之所以出現(xiàn)錯(cuò)誤，是因?yàn)樵谶M(jìn)行公式復(fù)制時(shí)，公式中的單元格引用是相對(duì)引用。6.1學(xué)生成績(jī)處理

6.1.1知識(shí)點(diǎn)解析絕對(duì)引用如果希望公式中引用的單元格或單元格區(qū)域不隨公式位置的變化而變化，可以使用絕對(duì)引用。絕對(duì)引用是指當(dāng)把公式復(fù)制到其它單元格時(shí)，公式中引用的單元格或單元格區(qū)域中行和列的引用不會(huì)改變。絕對(duì)引用的單元格名稱中其行和列之前均會(huì)加上$以示區(qū)分，如$A$4表示絕對(duì)引用A4單元格。因此，在C3單元格的編輯框中，選擇“B3:B13”，按下F4鍵，實(shí)現(xiàn)相對(duì)引用轉(zhuǎn)換到絕對(duì)引用，“B3:B13”變化為“$B$3:$B$10”，然后進(jìn)行公式復(fù)制時(shí)，就能得到正確的結(jié)果了。6.1學(xué)生成績(jī)處理

6.1.1知識(shí)點(diǎn)解析多條件統(tǒng)計(jì)函數(shù)COUNTIFS如何統(tǒng)計(jì)游戲積分15000～20000（含15000，不含20000）的人數(shù)？可以轉(zhuǎn)換為：統(tǒng)計(jì)B3:B10單元格區(qū)域中數(shù)值在15000～20000（含15000，不含20000）的單元格個(gè)數(shù)可以采用COUNTIFS函數(shù)來(lái)實(shí)現(xiàn)。6.1學(xué)生成績(jī)處理

6.1.1知識(shí)點(diǎn)解析多條件統(tǒng)計(jì)函數(shù)COUNTIFS如何統(tǒng)計(jì)游戲積分15000～20000（含15000，不含20000）的人數(shù)？可以轉(zhuǎn)換為：統(tǒng)計(jì)B3:B10單元格區(qū)域中數(shù)值在15000～20000（含15000，不含20000）的單元格個(gè)數(shù)可以采用COUNTIFS函數(shù)來(lái)實(shí)現(xiàn)。6.1學(xué)生成績(jī)處理

6.1.1知識(shí)點(diǎn)解析多條件統(tǒng)計(jì)函數(shù)COUNTIFSCOUNTIFS函數(shù)語(yǔ)法格式為COUNTIFS(criteria_range1,criteria1,[criteria_range2,criteria2]…)其參數(shù)的含義分別是：criteria_range1：為第一個(gè)需要計(jì)算其中滿足某個(gè)條件的單元格數(shù)目的單元格區(qū)域（簡(jiǎn)稱條件區(qū)域）。criteria1：為第一個(gè)區(qū)域中將被計(jì)算在內(nèi)的條件（簡(jiǎn)稱條件），其形式可以為數(shù)字、表達(dá)式或文本。criteria_range2為第二個(gè)條件區(qū)域。

criteria2為第二個(gè)條件，依次類推。6.1學(xué)生成績(jī)處理

6.1.1知識(shí)點(diǎn)解析多條件統(tǒng)計(jì)函數(shù)COUNTIFS對(duì)應(yīng)的公式為“=COUNTIFS(B3:B10,">=15000",B3:B10,"<20000")”6.1學(xué)生成績(jī)處理

6.1.1知識(shí)點(diǎn)解析復(fù)雜條件格式與混合引用將排行榜（在C列）前3名的整行數(shù)據(jù)（從A列C列）都用“灰色背景”標(biāo)識(shí)出來(lái)6.1學(xué)生成績(jī)處理

6.1.1知識(shí)點(diǎn)解析復(fù)雜條件格式與混合引用將排行榜（在C列）前3名的整行數(shù)據(jù)（從A列C列）都用“灰色背景”標(biāo)識(shí)出來(lái)需要對(duì)A3:C10單元格區(qū)域運(yùn)用復(fù)雜條件格式6.1學(xué)生成績(jī)處理

6.1.1知識(shí)點(diǎn)解析復(fù)雜條件格式與混合引用第3行數(shù)據(jù)（A3:C3）的所有單元格對(duì)應(yīng)的條件格式可以描述為：如果第3行中的第C列單元格中的數(shù)據(jù)<=3（C3<=3），那么對(duì)該單元格應(yīng)用“灰色背景”格式。第4行數(shù)據(jù)（A4:C4）的所有單元格對(duì)應(yīng)的條件格式可以描述為：如果第4行中的第C列單元格中的數(shù)據(jù)<=3（C4<=3），那么對(duì)該單元格應(yīng)用“灰色背景”格式。從以上分析可以知道，對(duì)第3到第10行（A3:C10）的所有單元格對(duì)應(yīng)的條件格式的公式為“=$C3<=3”。在該條件表達(dá)式中，地址“$C3”為混合引用單元格地址。如在用條件“=$C3<=3”對(duì)A3:C10的單元格進(jìn)行條件格式判斷時(shí)，A3、B3、C3（第3行）單元格的格式判斷條件為“C3單元格的內(nèi)容是否<=3”，在A8、B8、C8單元格（第8行）單元格的格式判斷條件為“C8單元格的內(nèi)容是否<=3”。6.2學(xué)生成績(jī)處理

6.2.1任務(wù)實(shí)現(xiàn)（1）填寫考勤記錄、平時(shí)成績(jī)、期中成績(jī)及期末成績(jī)。（2）根據(jù)考勤成績(jī)計(jì)算辦法（考勤以100分計(jì)，遲到1次扣5分，請(qǐng)假1次扣10分，曠課1次扣20分），計(jì)算考勤成績(jī)。（3）根據(jù)總評(píng)成績(jī)計(jì)算辦法（總評(píng)成績(jī)=考勤成績(jī)×10%+平時(shí)成績(jī)×20%+期中成績(jī)×20%+期末成績(jī)×50%，成績(jī)四舍五入，曠考計(jì)為曠考），計(jì)算總評(píng)成績(jī)。（4）將總評(píng)成績(jī)轉(zhuǎn)換為總評(píng)等級(jí)：總評(píng)成績(jī)>=90分為A；>=80分為B；>=70分為C；>=60分為D；60分以下為E，曠考為F。（5）統(tǒng)計(jì)每個(gè)分?jǐn)?shù)段的人數(shù)。6.2學(xué)生成績(jī)處理

6.2.1任務(wù)實(shí)現(xiàn)請(qǐng)參照書上步驟，自行完成上、下頁(yè)邊距為3cm，左、右頁(yè)邊距為2cm。紙張采用B5紙橫向打印，打印的表格在頁(yè)面中采用水平居中?？傇u(píng)成績(jī)分段人數(shù)統(tǒng)計(jì)信息不打印。如果成績(jī)單需要分多頁(yè)打印，則每頁(yè)需要打印標(biāo)題（第1行到第4行）信息。6.3學(xué)生成績(jī)處理

6.3.1總結(jié)與提高1.IF函數(shù)嵌套如果O5單元格的值為64公式“=IF(O5<60,”E“,IF(O5<70,”D“,IF(O5<80,”C“,IF(O5<90,”B“,”A“))))”的執(zhí)行結(jié)果為多少？公式“=IF(O5<60,"E",IF(60<=O5<70,"D",IF(70<=O5<80,"C",IF(80<=O5<90,"B","A"))))”的執(zhí)行結(jié)果為多少？AD6.3學(xué)生成績(jī)處理

6.3.3總結(jié)與提高1.IF函數(shù)嵌套為防止出現(xiàn)條件交叉包含，在我們進(jìn)行多條件嵌套時(shí)：（1）條件判斷要么從最大到最小，要么從最小到最大，不要出現(xiàn)大小交叉情況。（2）如果條件判斷為從大到小，通常使用的比較運(yùn)算符為大于>或大于等于>=。（2）如果條件判斷為從小到大，通常使用的比較運(yùn)算符為小于<或小于等于<=。6.3學(xué)生成績(jī)處理

6.3.1總結(jié)與提高2.用IF函數(shù)防止異常結(jié)果出現(xiàn)如果某個(gè)同學(xué)遲到了10次，曠課了3次，請(qǐng)問(wèn)他的考勤成績(jī)是多少？與現(xiàn)實(shí)相符嗎？怎么解決判斷“考勤成績(jī)”是不是小于0，若是，“考勤成績(jī)”計(jì)為0分，否則計(jì)為計(jì)算出來(lái)的成績(jī)IF(100-COUNTIF(C5:J5,"遲到")*5-COUNTIF(C5:J5,"請(qǐng)假")*10-COUNTIF(C5:J5,"曠課")*20>=0,100-COUNTIF(C5:J5,"遲到")*5-COUNTIF(C5:J5,"請(qǐng)假")*10-COUNTIF(C5:J5,"曠課")*20,0)6.3學(xué)生成績(jī)處理

6.3.1總結(jié)與提高3.排位函數(shù)RANK除了可以使用RANK.EQ函數(shù)來(lái)排名外，也可以使用RANK函數(shù)。RANK函數(shù)返回一個(gè)數(shù)字在數(shù)字列表中的排位。RANK函數(shù)是在Excel2007版本及之前的版本中提供的函數(shù)。在Excel2010版本中，提供了RANK.EQ等新函數(shù)來(lái)替代它，而Excel2010版本中仍提供RANK函數(shù)是為了保持與Excel早期版本的兼容性。6.3學(xué)生成績(jī)處理

6.3.1總結(jié)與提高4.COUNTIFS函數(shù)統(tǒng)計(jì)“全勤，并且總評(píng)成績(jī)?yōu)?0~89的學(xué)生數(shù)”？統(tǒng)計(jì)“考勤成績(jī)=100，并且總評(píng)成績(jī)?yōu)?0~89的學(xué)生數(shù)”（1）O5:O32單元格區(qū)域中單元格的值>=80。（2）O5:O32單元格區(qū)域中單元格的值<90。（3）K5:K32單元格區(qū)域中單元格的值=100因此，其對(duì)應(yīng)的表達(dá)式為“=COUNTIFS(O5:O32,">=80",