用ET表格打造更直觀的學生成績分析之相關函數

2024-08-23 07:20:21

字體：大中小

來源：轉載

供稿：網友

　　前些天在論壇發了一帖：《用ET表格打造更直觀的學生成績分析》(以下簡稱為《成績分析》)，http://bbs.wps.cn/thread-21963426-1-1.html。很多壇友對文中所涉及的函數非常感興趣。今天再發一帖對相關的函數作些解釋以作前文的補充。

　　前文中所涉及的函數主要有這么幾個：SUMIF、COUNTIF、SUMPRODUCT、VLOOKUP。這幾個函數在成績分析統計中經常用得到，對于教師來說可謂是有用之極。我們且一一道來。

　　一、SUMIF函數

　　SUMIF函數的作用是根據指定條件對若干單元格、區域或引用求和。其語法為SUMIF(用于條件判斷的單元格區域，由數字、邏輯表達式等組成的判定條件，為需要求和的單元格、區域或者是引用)。以圖1所示表格為例。

　　圖1

　　我們希望在D13單元格中顯示表格中2班學生的語文成績總分。分析可以看到學生的班級在B2:B11單元格區域，語文成績則分布在D2:D11單元格區域。所以，根據SUMIF函數的語法，我們只需要在D13單元格輸入公式“=SUMIF($B$2:$B$11,"2班",D2:D11)”就可以了。其中參數"2班"為判斷條件，$B$2:$B$11為提供邏輯判斷依據的單元格區域，而D2:D11則為實際求和的單元格區域。所以，公式 “=SUMIF($B$2:$B$11,"2班",D2:D11)”可以翻譯為：在B2:B11單元格中值為“2班”的，對其對應的D列單元格數據進行求和。

　　在《成績分析》一文中，公式“=SUMIF($B:$B,$Q$3,D:D)”就很容易理解了：在B列中其值與Q3單元格相等的，對其對應的D列單元格進行求和。

　　二、COUNTIF函數

　　COUNIT函數的作用是計算區域中滿足給定條件的單元格的個數。語法與SUMIF函數類似：COUNTIF(為需要計算其中滿足條件的單元格數目的單元格區域，統計條件)。其中統計條件可以為數字、表格式或文本。簡單地理解就是COUNTIF(在哪里計數，根據什么計數)。

　　仍以圖1所示表格為例。我們如果輸入公式“=COUNTIF($B$2:$B$11,"2班")，那么自然就可以得到B2:B11單元格區域中值為"2班"的單元格數目。所以，D14單元格要統計2班語文平均分就簡單多了，只需要輸入公式“=D13/COUNTIF($B$2:$B$11,"2 班")”就OK了。

　　三、SUMPRODUCT函數

　　該函數可用于多條件計數，即計算符合2個及以上條件的單元格個數。其語法為SUMPRODUCT((條件1)*(條件2)* (條件…))。如圖2所示表格。

　　圖2

　　我們如果要統計表格中職稱為“中高”的男教師數，那么只需要在單元格中輸入公式“=SUMPRODUCT((Q2:Q11="男")*(R2:R11="中高"))”即可。相信對照表格和公式，公式的含義自然就清楚了。

　　圖1所示表格中“班級”在B列，語文成績在D列。假如要計算2班語文科目的及格率，那么就需要先統計符合兩個條件的單元格數目。條件1：B列為“2班”，條件2：D列大于或等于60分。公式“=SUMPRODUCT(($B$2:$B$11="2班")*(D2:D11>=60))”就可以滿足要求，然后再除以人數(COUNTIF($B$2:$B$11,"2班"))不就是及格率了?

　　成績分析統計中的“優秀率”也是這樣統計，只是把分數從“60”換成設定的成績就行了。

　　四、VLOOKUP函數

　　VLOOKUP函數的作用是在表格或數值數組的首列查找指定的數值，并由此返回表格或數組當前行中指定列處的數值。其語法是 VLOOKUP(查找值，數據表，列序數，匹配條件)。所謂“查找值”是指需要在數據表第一列中查找的數值，它可以是數值、引用或文字串。“數據表” 為需要在其中查找數據的數據表，可以使用對區域或區域名稱的引用。“列序數”是在數據表中待返回匹配數據所在的列序號。“匹配條件”為“FALSE”是返回精確匹配值，如果為TRUE或省略，則返回近似匹配值，也就是說，如果找不到精確匹配值，則返回小于“查找值”的最大數值。

　　還是舉例來說更清楚些。假設我們想知道在圖2所示表格中“教師04”的職稱是什么。那么我們就可以在單元格中輸入公式“=VLOOKUP("教師04",P2:R11,3,FALSE)”，回車就出結果了，如圖3所示。