|
|
|
前些天在論壇發(fā)了一帖：《用ET表格打造更直觀的學生成績分析》(以下簡稱為《成績分析》)，http://bbs.wps.cn/thread-21963426-1-1.html。很多壇友對文中所涉及的函數(shù)非常感興趣。今天再發(fā)一帖對相關(guān)的函數(shù)作些解釋以作前文的補充。
前文中所涉及的函數(shù)主要有這么幾個：SUMIF、COUNTIF、SUMPRODUCT、VLOOKUP。這幾個函數(shù)在成績分析統(tǒng)計中經(jīng)常用得到，對于教師來說可謂是有用之極。我們且一一道來。
一、SUMIF函數(shù)
SUMIF函數(shù)的作用是根據(jù)指定條件對若干單元格、區(qū)域或引用求和。其語法為SUMIF(用于條件判斷的單元格區(qū)域，由數(shù)字、邏輯表達式等組成的判定條件，為需要求和的單元格、區(qū)域或者是引用)。以圖1所示表格為例。
圖1
我們希望在D13單元格中顯示表格中2班學生的語文成績總分。分析可以看到學生的班級在B2:B11單元格區(qū)域，語文成績則分布在D2:D11單元格區(qū)域。所以，根據(jù)SUMIF函數(shù)的語法，我們只需要在D13單元格輸入公式“=SUMIF($B$2:$B$11,"2班",D2:D11)”就可以了。其中參數(shù)"2班"為判斷條件，$B$2:$B$11為提供邏輯判斷依據(jù)的單元格區(qū)域，而D2:D11則為實際求和的單元格區(qū)域。所以，公式 “=SUMIF($B$2:$B$11,"2班",D2:D11)”可以翻譯為：在B2:B11單元格中值為“2班”的，對其對應的D列單元格數(shù)據(jù)進行求和。
在《成績分析》一文中，公式“=SUMIF($B:$B,$Q$3,D:D)”就很容易理解了：在B列中其值與Q3單元格相等的，對其對應的D列單元格進行求和。
二、COUNTIF函數(shù)
COUNIT函數(shù)的作用是計算區(qū)域中滿足給定條件的單元格的個數(shù)。語法與SUMIF函數(shù)類似：COUNTIF(為需要計算其中滿足條件的單元格數(shù)目的單元格區(qū)域，統(tǒng)計條件)。其中統(tǒng)計條件可以為數(shù)字、表格式或文本。簡單地理解就是COUNTIF(在哪里計數(shù)，根據(jù)什么計數(shù))。
仍以圖1所示表格為例。我們?nèi)绻斎牍?ldquo;=COUNTIF($B$2:$B$11,"2班")，那么自然就可以得到B2:B11單元格區(qū)域中值為"2班"的單元格數(shù)目。所以，D14單元格要統(tǒng)計2班語文平均分就簡單多了，只需要輸入公式“=D13/COUNTIF($B$2:$B$11,"2 班")”就OK了。
三、SUMPRODUCT函數(shù)
該函數(shù)可用于多條件計數(shù)，即計算符合2個及以上條件的單元格個數(shù)。其語法為SUMPRODUCT((條件1)*(條件2)* (條件…))。如圖2所示表格。
圖2
我們?nèi)绻y(tǒng)計表格中職稱為“中高”的男教師數(shù)，那么只需要在單元格中輸入公式“=SUMPRODUCT((Q2:Q11="男")*(R2:R11="中高"))”即可。相信對照表格和公式，公式的含義自然就清楚了。
圖1所示表格中“班級”在B列，語文成績在D列。假如要計算2班語文科目的及格率，那么就需要先統(tǒng)計符合兩個條件的單元格數(shù)目。條件1：B列為“2班”，條件2：D列大于或等于60分。公式“=SUMPRODUCT(($B$2:$B$11="2班")*(D2:D11>=60))”就可以滿足要求，然后再除以人數(shù)(COUNTIF($B$2:$B$11,"2班"))不就是及格率了?
成績分析統(tǒng)計中的“優(yōu)秀率”也是這樣統(tǒng)計，只是把分數(shù)從“60”換成設(shè)定的成績就行了。
四、VLOOKUP函數(shù)
VLOOKUP函數(shù)的作用是在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值，并由此返回表格或數(shù)組當前行中指定列處的數(shù)值。其語法是 VLOOKUP(查找值，數(shù)據(jù)表，列序數(shù)，匹配條件)。所謂“查找值”是指需要在數(shù)據(jù)表第一列中查找的數(shù)值，它可以是數(shù)值、引用或文字串。“數(shù)據(jù)表” 為需要在其中查找數(shù)據(jù)的數(shù)據(jù)表，可以使用對區(qū)域或區(qū)域名稱的引用。“列序數(shù)”是在數(shù)據(jù)表中待返回匹配數(shù)據(jù)所在的列序號。“匹配條件”為“FALSE”是返回精確匹配值，如果為TRUE或省略，則返回近似匹配值，也就是說，如果找不到精確匹配值，則返回小于“查找值”的最大數(shù)值。
還是舉例來說更清楚些。假設(shè)我們想知道在圖2所示表格中“教師04”的職稱是什么。那么我們就可以在單元格中輸入公式“=VLOOKUP("教師04",P2:R11,3,FALSE)”，回車就出結(jié)果了，如圖3所示。
圖3
公式的含義是在P2:R11單元格區(qū)域的首列查找值為“教師04”的單元格，并返回其所在行的第三列數(shù)據(jù)。對照表格看一下就清楚了。
在《成績分析》一文中的VLOOKUP函數(shù)中，使用了另一函數(shù)COLUMN()，它返回的是單元格所在的列數(shù)。比如公式“=COLUMN(D3)”的結(jié)果就是“4”。而不帶任何參數(shù)的“COLUMN()”返回的則是當前單元格所在的列數(shù)。
好了，《成績分析》一文中涉及的函數(shù)基本交待完了。如何有機會實踐一下，必定能體會到使用這些函數(shù)的樂趣。
|
|
發(fā)表留言請先登錄！
|