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

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

6.1學(xué)生成績處理

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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é)生成績處理

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

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

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

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