王智偉

摘要：EXCEL軟件是辦公自動化軟件之一，它的主要功能是對表格進行分析、計算、處理，并且可以將分析計算結(jié)果用各種圖表形式展現(xiàn)出來。公式和函數(shù)是實現(xiàn)表格計算功能的強大武器，是學習《EXCEL電子表格制作》的重點和難點，該文就圍繞成績表中遇到的常見計算問題所涉及的EXCEL函數(shù)進行闡述。

關(guān)鍵詞：函數(shù)；IF；AND；RANK；COUNTIF；SUMIF；AVERAGEIF

中圖分類號：TP393 文獻標識碼：A 文章編號：1009-3044（2018）24-0249-03

《EXCEL電子表格制作》是中等職業(yè)學校計算機專業(yè)和非計算機專業(yè)學生都要學習和掌握的一門基礎(chǔ)學科，其中公式和函數(shù)一章是《EXCEL電子表格制作》一書的重點和難點章節(jié)，也是歷年來河南省素質(zhì)能力大賽EXCEL數(shù)據(jù)處理考試的重要知識點之一，很多學生在學習函數(shù)的過程中感到難懂難記，該文就以成績表處理中遇到的常見問題為例，來講一下中職生在學習《EXCEL電子表格制作》這門課中應(yīng)掌握的一些函數(shù)。

1 問題引入

如圖1所示是一個學生成績表，表中所對應(yīng)的空白單元格是需要使用公式和函數(shù)來計算的部分。從圖中我們可以看出，成績表的計算可以分為橫向計算和縱向計算兩部分。

2處理過程

2.1 橫向計算

對成績表的橫向計算通常包括：計算每個學生的總分、平均分，根據(jù)總分或平均分對學生求等級、求名次、求獎學金，根據(jù)各科成績判斷有無掛科以及掛科的科目等。

1）橫向求總分、平均分

橫向計算每個學生的總分、平均分分別使用求和函數(shù)SUM和求平均值函數(shù)AVERAGE，這兩個函數(shù)幾乎在所有的《計算機基礎(chǔ)》教材或《EXCEL電子表格制作》教材中都有，使用也很簡單。就本表格而言，求總分只需要在F3單元格中輸入公式“=SUM（C3：E3）”后按回車鍵，或者單擊F3單元格后，選擇“公式”選項卡下面“函數(shù)庫”組中的“自動求和”按鈕下拉箭頭，選擇“求和”命令即可在F3單元格中自動出現(xiàn)公式“=SUM（C3：E3）”。這時只需要按回車鍵或單擊編輯欄中的對號按鈕就可以算出第一個學生的總分。使用同樣的方法在G3單元格中輸入公式“=AVERAGE（C3：E3）”后按回車鍵即可求出第一個學生的平均分。要求其他學生的總分和平均分只需要選中F3和G3單元格，拖動右下角的填充柄就可以完成。

2）根據(jù)總分求等級、獎學金

根據(jù)總分或平均分對學生求等級，以總分為例。成績等級計算：要求總分大于等于260的為優(yōu)秀，大于等于240的為良好。大于等于180的是中等，低于180的為差。這個問題需要用到IF函數(shù)的嵌套來完成。我們先來看看IF函數(shù)的格式及功能。

單擊“公式”選項卡下的插入函數(shù)按鈕，打開“插入函數(shù)”對話框，在“搜索函數(shù)”文本框中輸入“IF”，單擊“轉(zhuǎn)到”按鈕，在“選擇函數(shù)”列表框中單擊“IF”，在列表框的下方就會出現(xiàn)“IF”函數(shù)的格式及功能說明，如圖2所示。

單擊“確定”按鈕，出現(xiàn)“函數(shù)參數(shù)”對話框，如圖3所示。從IF函數(shù)的參數(shù)對話框中我們可以看出，它的功能是判斷是否滿足某個條件，如果滿足返回一個值，如果不滿足則返回另一個值。它里面包含三個參數(shù)：Logical_test、Value_if_true、Value_if_false，單擊每個參數(shù)后面的文本框，下方會出現(xiàn)關(guān)于該參數(shù)的詳細說明。從函數(shù)參數(shù)對話框中我們可以看出參數(shù)Logical_test是表示判定條件的，是任何可被計算為true或false的數(shù)值表達式；參數(shù)Value_if_true是Logical_test為true時的返回值，如果忽略，則返回true，IF函數(shù)最多可嵌套7層；Value_if_false是Logical_test為false時的返回值，如果忽略，則返回false。從參數(shù)說明中我們可以得知，在參數(shù)Value_if_true和Value_if_false中是分別可以再進行IF嵌套使用的。

針對本題目的要求，把總分共分為了4個分數(shù)段，我們可以使用3個IF語句的嵌套來完成。在H3單元格中輸入公式：=IF（F3>=260，"優(yōu)秀"，IF（F3>=240，"良好"，IF（F3>=180，"中等"，"差"）））或=IF（F3<180，"差"，IF（F3<240，"中等"，IF（F3<260，"良好"，"優(yōu)秀"）））。做這類題目時可以遵從兩個原則：一是條件的設(shè)置，可以從高分向低分排列，也可以從低分向高分排列，但要注意等號的使用位置，公式中所有的標點符號必須是英文半角狀態(tài)，分數(shù)段也必須是連續(xù)的；二是條件的數(shù)量與分數(shù)段的個數(shù)有關(guān)，一般來說IF條件嵌套的個數(shù)比實際的條件個數(shù)少1個。

根據(jù)總分求獎學金的方法類似。比如總分大于等于260的，獎學金為300元，大于等于240的，獎學金為100元，其余的沒有獎學金。我們可以在J3單元格中輸入公式：=IF（F3>=260，"300元"，IF（F3>=240，"100元"，""））或=IF（F3<240，""，IF（F3<260，"100元"，"300元"））。

3）根據(jù)各科成績求是否有掛科和掛科科目

求有無掛科問題需要使用IF函數(shù)和AND函數(shù)嵌套來完成。使用AND函數(shù)可以一次判定多個條件是否都滿足，如果都滿足，說明無掛科，否則說明有掛科。我們可以在K3單元格中輸入公式：=IF（AND（C3>=60，D3>=60，E3>=60），"無"，"有"）。

求掛科科目問題需要使用多個IF函數(shù)及IF函數(shù)和AND函數(shù)運算結(jié)果聯(lián)接在一起來完成。我們可以在L3單元格中輸入公式：=IF（C3<60，"語文"，""）&IF;（D3<60，"數(shù)學"，""）&IF;（E3<60，"英語"，""）&IF;（AND（C3>=60，D3>=60，E3>=60），"無"，""）。

4）根據(jù)總分求名次

求名次問題需要使用RANK函數(shù)來實現(xiàn)。我們在“插入函數(shù)”對話框“搜索函數(shù)”文本框中輸入RANK，單擊“轉(zhuǎn)到”按鈕，再單擊“確定”按鈕，進入RANK函數(shù)參數(shù)對話框，如圖4所示。

從這個對話框中我們可以看出RANK函數(shù)的功能是返回某數(shù)字在一列數(shù)字中相對于其他數(shù)值的大小排名。它包含三個參數(shù)：Number、Ref、Order，單擊每個參數(shù)后的文本框，下方會出現(xiàn)關(guān)于這個參數(shù)的詳細說明。從參數(shù)說明中我們可以得知，參數(shù)Number是要查找排名的數(shù)字，可以是具體的數(shù)字或單元格引用，參數(shù)Ref是一組數(shù)或?qū)σ粋€數(shù)據(jù)列表的引用，非數(shù)字值將被忽略。參數(shù)Order是在列表中排名的數(shù)字，如果為0或忽略，降序；非0值，升序。針對本題目，我們可以在I3單元格中輸入公式：=RANK（F3，F(xiàn)3：F12，0），求出第一個學生在全班的排名，但是如果要求其他學生的排名，使用這個公式就不能使用拖動填充柄的方法準確地求出，這里面牽涉到單元格引用的問題，在EXCEL中單元格的引用有絕對引用、相對引用、混合引用。在這里主要用到絕對引用和相對引用，使用絕對引用時如果復(fù)制公式，單元格的名稱不會發(fā)生變化；如果使用相對引用，則復(fù)制公式時，單元格的名稱會隨著目標單元格的不同而發(fā)生變化。在這個公式中，第一個參數(shù)是要排名的學生，是需要不斷變化的，所以必須使用相對引用，第二個參數(shù)是要排名的范圍，這個是不能發(fā)生變化的，每個學生都要在這個范圍內(nèi)排名，所以必須使用絕對引用。我們可以這樣對參數(shù)進行修改：=RANK（F3，$F$3：$F$12，0），這樣再用拖動填充柄的方法求其他學生的名次就不會出錯了。

2.2 縱向計算

1）求單科總分、平均分、最高分、最低分

縱向求單科總分、平均分、最高分、最低分分別使用求和函數(shù)SUM、求平均值函數(shù)AVERAGE、求最大值函數(shù)MAX、求最小值函數(shù)MIN，這幾個函數(shù)的使用非常簡單，在這里我只給出公式，不再詳述。

求語文單科總分，在C13單元格中輸入公式：=SUM（C3：C12）。求語文單科平均分，在C14單元格中輸入公式：=AVERAGE（C3：C12）。求語文單科最高分，在C15單元格中輸入公式：=MAX（C3：C12）。求語文單科最低分，在C16單元格中輸入公式：=MIN（C3：C12）。

求其他科目的總分、平均分、最高分、最低分只需要選中C3：C16單元格，拖動右下角的填充柄就可完成。

2）求單科及格率、優(yōu)秀率、優(yōu)秀人數(shù)及各分數(shù)段人數(shù)

處理這類問題就要用到統(tǒng)計類函數(shù)COUNT和COUNTIF。要求及格率，需要先求出及格人數(shù)和總?cè)藬?shù)。求及格人數(shù)使用條件統(tǒng)計函數(shù)COUNTIF，求總?cè)藬?shù)使用統(tǒng)計函數(shù)COUNT。及格率公式是及格人數(shù)除以總?cè)藬?shù)，把求得的結(jié)果轉(zhuǎn)換為百分比樣式。針對本題目，求語文科目的及格率，需要在C17單元格中輸入公式：=COUNTIF（C3：C12，">=60"）/COUNT（C3：C12）。

同樣要求優(yōu)秀率，需要先求出優(yōu)秀人數(shù)和總?cè)藬?shù)，優(yōu)秀人數(shù)也是使用條件統(tǒng)計函數(shù)COUNTIF，在C19單元格中輸入公式：=COUNTIF（C3：C12，">=80"）。優(yōu)秀率公式是優(yōu)秀人數(shù)除以總?cè)藬?shù)，把求得的結(jié)果轉(zhuǎn)換為百分比樣式。在C18單元格中輸入公式：=COUNTIF（C3：C12，">=80"）/COUNT（C3：C12）。

60分以下的人數(shù)，在C20單元格輸入公式：=COUNTIF（C3：C12，"<60"）。

要求60～70分數(shù)段的人數(shù)需要先求出大于等于60分的人數(shù)和大于等于70分的人數(shù)，二者相減即可。在C21單元格中輸入公式：=COUNTIF（C3：C12，">=60"）-COUNTIF（C3：C12，">=70"）。用同樣的方法可以求其他分數(shù)段的人數(shù)，大家可以嘗試著做一下。求其他科目的及格率、優(yōu)秀率及各分數(shù)段的人數(shù)，都可以使用拖動填充柄的方法直接求出。

3）按性別求總分、平均分

求男生語文單科總分和平均分，需要使用條件求和函數(shù)SUMIF，條件求平均值函數(shù)AVERAGEIF。我們先來看一下SUMIF函數(shù)的格式及功能說明，在“插入函數(shù)”對話框的“搜索函數(shù)”文本框中輸入SUMIF，單擊“轉(zhuǎn)到”按鈕和“確定”按鈕，打開如圖5所示的對話框。

從圖中我們可以看出，SUMIF函數(shù)的主要功能是對滿足條件的單元格求和，它包含三個參數(shù)：Range、Criteria、Sum_range。單擊每個參數(shù)后的文本框，下方會出現(xiàn)關(guān)于這個參數(shù)的詳細說明。從參數(shù)說明中我們可以得知，參數(shù)Range是要進行計算的單元格區(qū)域即條件區(qū)域；參數(shù)Criteria是以數(shù)字、表達式或文本形式定義的條件；參數(shù)Sum_range是用于求和的實際單元格即求和區(qū)域。針對本題目，條件區(qū)域為性別列，即B3：B12；條件是性別為男的任意單元格；求和區(qū)域為語文列，即C3：C12。如果要使用填充柄向右復(fù)制公式的話，則前兩個參數(shù)是不能改變的，需要使用絕對引用，第三個參數(shù)需要變化，需要使用相對引用。我們可以在C25單元格中輸入公式：=SUMIF（$B$3：$B$12，$B$3，C3：C12）。

條件求平均值函數(shù)AVERAGEIF的格式及參數(shù)個數(shù)與SUMIF函數(shù)都是一樣的，只是功能不一樣，AVERAGEIF函數(shù)查找給定條件單元格的平均值。前兩個參數(shù)Range和Criteria的含義與SUMIF函數(shù)是一樣的，第三個參數(shù)Average_range是用于求平均值的實際單元格區(qū)域即求平均區(qū)域。針對本題目，要求男生的語文平均分，需要在C26單元格輸入公式：=AVERAGEIF（$B$3：$B$12，$B$3，C3：C12）。

男生的語文總分、平均分求出后，女生的語文總分、平均分也可以很容易的求出，只需要將公式中的第二個參數(shù)改為性別是女生的單元格絕對引用即可，大家可以嘗試著去做。其余未計算的相應(yīng)單元格數(shù)據(jù)都可以使用拖動填充柄的方式計算出來。

3 問題總結(jié)

綜上所述，要處理成績的常見計算問題，我們需要掌握下面這些函數(shù)：SUM、AVERAGE、MAX、MIN、COUNT、IF、AND、RANK、COUNTIF、SUMIF、AVERAGEIF。前五個函數(shù)是常用函數(shù)，在所有的計算機應(yīng)用基礎(chǔ)教材及EXCEL電子表格制作教材中都有，使用方法也很簡單。后幾個函數(shù)是需要重點學習的。學習一個新的函數(shù)，需要了解這個函數(shù)的四個方面：函數(shù)名、函數(shù)的功能、函數(shù)中所包含的參數(shù)個數(shù)及各個參數(shù)所表示的含義。如果后兩項記不住的話也沒關(guān)系，只要記住函數(shù)名和函數(shù)的功能就可以了，遇到問題知道用哪個函數(shù)就行，其他的記不住可以使用插入函數(shù)對話框幫你解決。最后要強調(diào)的是，不管用到哪個函數(shù)，一定得記住函數(shù)中涉及的所有符號一定是英文半角狀態(tài)下的標點符號，否則就會不斷地出現(xiàn)錯誤提示信息。

參考文獻：

[1]王小林，郭燕.EXCEL 2010電子表格制作案例教程》[M].北京：北京金企鵝文化發(fā)展中心策劃主編，航空工業(yè)出版社，全國計算機應(yīng)用技術(shù)證書考試（NIT）推薦教材，2017.7.

[2]劉可，李顯進.EXCEL 2010從入門到精通[M].北京：清華大學出版社，2014.

【通聯(lián)編輯：王力】