【3】Excel統(tǒng)計函數(shù)count countif
Excel快速計算、統(tǒng)計混合編班考試成績表
為了保證學(xué)生成績錄入和處理的公平性，學(xué)校采用混合編班統(tǒng)一錄入的方式。也就是說，把同年級多個班的學(xué)生成績統(tǒng)一錄入到一個Excel工作表中，而且使同班的學(xué)生不相鄰。這種情況下，想在不破壞原始工作表的情況下統(tǒng)計各班學(xué)生各科的總分、平均分及各科的分數(shù)段分布，咱們還得好好動一下腦子。
先看一下原始成績表，如圖1所示。
第一列為學(xué)生的考號，其中考號的第5位和第6位數(shù)字是班級代碼。學(xué)生各科的成績分別在C、D、E、F列。而我們需要統(tǒng)計的是各學(xué)科各班的總分、平均分及各分數(shù)段人數(shù)等項指標，如圖2所示。
一、總分的統(tǒng)計
在不破壞原工作表順序的情況下，總分的統(tǒng)計我們可以使用SUM函數(shù)及數(shù)組公式來完成。
首先點擊L3單元格計算01班語文科的總成績。
輸入“=SUM(IF(MID($A$2:$A$150,5,2)= $K3,$C$2:$C$150))”，然后按下“Ctrl+Shift+Enter”快捷鍵，可以看到公式的最外層添加了數(shù)組公式標記(一對大括號)，公式變身成為“{=SUM(IF(MID($A$2:$A$150,5,2)= $K3,$C$2:$C$150))}”。切記這對大括號是不可以手工輸入的。
按下快捷鍵后，L3單元格立刻顯示出了01班語文科的總分?？彀?還有更快的呢。單擊此單元格，向下拖動其填充句柄至L10單元格復(fù)制此公式，立刻各班的語文科總分就都有了。
方便吧?簡單介紹一下公式中用到的相關(guān)函數(shù)，那咱們就可以舉一反三了。
MID($A$2:$A$150,5,2)= $K3：K3單元格是班級代碼。而MID函數(shù)可以截取字符串中的指定字符，如MID(A2,5,2)的含義是從A2單元格字符串的第5個字符開始截取2個字符組成新的字符串。至于公式中所寫的的MID($A$2:$A$150,5,2)就可以利用數(shù)組公式對A2至A150單元格中的字符串逐一截取了。
而IF函數(shù)當然是用作判斷了。IF(MID($A$2:$A$150,5,2)= $K3是判斷截取的字符串是否與在K3單元格中的字符串相同。
整個公式的意思是：逐一判斷A2至A150單元格中第5、6字符串是否與K3單元格的字符串相同，如果相同，那么就把同一行中C列的單元格數(shù)據(jù)進行累加。
二、平均分的統(tǒng)計
明白了各班總分是怎么計算出來的，那么平均分的計算就易如反掌了。仍然點擊L3單元格，拖動其填充句柄向右至M3單元格，松開鼠標后在M3單元格同樣可以得到01班語文科的總分。不要緊，在公式編輯欄中把公式中的“SUM”函數(shù)更改為“AVERAGE”函數(shù)，再按下“Ctrl+Shift+Enter”快捷鍵。好了，現(xiàn)在看到結(jié)果了吧?
選中M3單元格，向下拖動填充句柄至M10單元格復(fù)制此公式。行了，各班的語文平均分就都有了。
三、分數(shù)段統(tǒng)計
在混合編班而又不破壞原表順序的情況下，我們就不能使用常用的COUNTIF函數(shù)或FREQUENCY函數(shù)來統(tǒng)計各分數(shù)段人數(shù)。我們可以使用SUMPRODUCT函數(shù)來完成這個任務(wù)。
點擊N3單元格統(tǒng)計01班語文成績在90分以上的人數(shù)。在此單元格輸入公式“=SUMPRODUCT((MID($A$2:$A$150,5,2)=$K3)*($C$2:$C$150>=90))”，然后按下回車鍵。對，是回車鍵，而不是前面用的“Ctrl+Shift+Enter”快捷鍵。按下回車鍵后自然可以看到統(tǒng)計結(jié)果了。
這個公式的意思是：統(tǒng)計考號的5、6兩位字符串與K3單元格字符串相同且C2:C150單元格數(shù)據(jù)大于90的單元格個數(shù)。“*”符號前后的括號內(nèi)就是我們設(shè)置的條件。如果要設(shè)置的條件更多，那就再添加“*”，并在其后添加括號，在括號內(nèi)添加條件就是了。
如法炮制，在O3單元格輸入如下公式“=SUMPRODUCT((MID($A$2:$A$150,5,2)=$K3)*($C$2:$C$150>=80)*($C$2:$C$150<90))”，回車后就可以得到80-89分這一分數(shù)段的人數(shù)了。
選中O3單元格，向右拖動其填充句柄復(fù)制公式至S3單元格。并根據(jù)要統(tǒng)計的不同的分數(shù)段，修改各單元格的公式。如S3單元格的公式應(yīng)修改為“=SUMPRODUCT((MID($A$2:$A$150,5,2)=$K3)*($C$2:$C$150<50))”。
好了，現(xiàn)在01班的各分數(shù)段已經(jīng)統(tǒng)計出來了。選中N3:S3單元格區(qū)域，向下拖動填充句柄至S10單元格復(fù)制相應(yīng)公式。OK，各班分數(shù)段統(tǒng)計完畢。最終效果如圖3所示。
四、其它各科的成績統(tǒng)計
以數(shù)學(xué)科統(tǒng)計為例。細細想來，數(shù)學(xué)科的統(tǒng)計與語文科成績唯一的不同就是語文成績在C列而數(shù)學(xué)成績在D列。所以，我們只要復(fù)制語文統(tǒng)計表然后把其中的“$C$2:$C$150”替換為“$D$2:$D$150”就可以了。
先選中K1:S10單元格區(qū)域，按下“Ctrl+C”快捷鍵復(fù)制，再單擊K12單元格，然后按下“Ctrl+V”快捷鍵進行粘貼。把K12單元格的“語文”修改為“數(shù)學(xué)”?，F(xiàn)在選中新復(fù)制過來的K12:S21單元格區(qū)域。然后按下“Ctrl+F”快捷鍵，打開“查找和替換”對話框。然后點擊“替換”選項卡，在“查找內(nèi)容”輸入框中輸入要查找的內(nèi)容“$C$2:$C$150”，再在“替換為”輸入框中輸入要替換的內(nèi)容“$D$2:$D$150”。點擊下方的“全部替換”按鈕。OK，數(shù)學(xué)成績的統(tǒng)計就算是大功告成了。如圖4所示。
【2】count函數(shù)統(tǒng)計函數(shù)COUNT：
count函數(shù)統(tǒng)計函數(shù)COUNT
[js102.21]1、COUNT 統(tǒng)計總數(shù)
[bs11p173]連續(xù)3天MA多頭排列選股公式語句
HF:=MA5>MA30 and MA10>MA30 and MA30>MA60
count(HF,3)=3
COUNT 統(tǒng)計，統(tǒng)計滿足條件的周期數(shù)。
用法：COUNT(X，N) 統(tǒng)計N周期中滿足X條件的周期數(shù)，若N=0則從第一個有效值開始。
例如：COUNT(CLOSE>OPEN，20) 表示統(tǒng)計20周期內(nèi)收陽的周期數(shù);
[js102.24]例六 COUNT（X，N）表示統(tǒng)計在N天內(nèi)滿足條件X的有幾天;
多頭排列-良好的上升趨勢-5/10/30日均線依次從上而下的排列，這種情況維持一段時間，
假設(shè)我們這里定為4天以上：
AA：=MA（CLOSE，5）；
BB：=MA（CLOSE，10）；
CC：=MA（CLOSE，30）；
T1：=AA＞BB AND BB＞CC;
COUNT（T1，4 ）=4
聯(lián)系客服
