wps表格圖文說明教程：用ET表格巧妙處理多條件下的成績統(tǒng)計(jì)

　　在ET表格中，對(duì)于班級(jí)成績冊的各科求總分、平均分以及排名次等，都可算是輕松的任務(wù)。但是，對(duì)于多年級(jí)多班級(jí)混合編排的班級(jí)，如果想再類似的操作，那又該如何進(jìn)行呢?

　　圖1

　　圖1所示為不同學(xué)校不同專業(yè)混編的示意成績冊。我們需要計(jì)算各學(xué)校各專業(yè)各科目的總分和平均分，并對(duì)各學(xué)生在各自學(xué)校各自專業(yè)內(nèi)排定名次。這項(xiàng)看起來艱巨復(fù)雜的任務(wù)，在ET表格中正確使用SUMPRODUCT函數(shù)，那么完成起來其實(shí)也并不算怎么困難。咱們只需如此操作即可：

　　一、多條件求和

　　比如我們需要計(jì)算工業(yè)職專機(jī)電專業(yè)所有學(xué)生的語文成績總分。分析表格可以看到：學(xué)校名稱在B2:B16單元格區(qū)域，專業(yè)名稱在C2:C16區(qū)域，語文成績則分布在D2:D16區(qū)域。在合適的單元格輸入公式“=SUMPRODUCT((B2:B16="工業(yè)職專")*(C2:C16="機(jī)電"),D2:D16)”，回車后可以得到結(jié)果“228”了。看出來了吧?公式中的前兩個(gè)小括號(hào)內(nèi)就是需要滿足的條件，而“D2:D16”就表示需要求和的區(qū)域。如果有更多的條件，那就再在前面加小括號(hào)就可以了。要注意的是小括號(hào)中間用“*”連接。

　　至于我們希望的各學(xué)校各專業(yè)各科目的總分，那就得設(shè)計(jì)如圖2所示表格來解決了。

　　圖2

　　先建好空白表格，如圖2所示錄入學(xué)校名稱和專業(yè)名稱。在N2單元格輸入公式 “=SUMPRODUCT(($B$2:$B$16=$L3)*($C$2:$C$16=$M3),D$2:D$16)”，選中該單元格的填充句柄，向右和向下拖動(dòng)復(fù)制公式至整個(gè)表格，那么各學(xué)校各專業(yè)各學(xué)科的總分就有了。不太難吧?

　　由于各學(xué)校的專業(yè)設(shè)置并不相同，所以某些的某些專業(yè)的各科總分計(jì)算為“0”。如果想追求盡善盡美，那么我們可以用IF函數(shù)，當(dāng)結(jié)果為“0”時(shí)不顯示任何內(nèi)容，這樣可以使結(jié)果顯示盡量美觀一些。公式為“=IF(SUMPRODUCT(($B$2:$B$16=$L3)* ($C$2:$C$16=$M3),D$2:D$16)=0,"",SUMPRODUCT(($B$2:$B$16=$L3)* ($C$2:$C$16=$M3),D$2:D$16))”。

　　二、多條件求平均分

　　求和與求平均分只有一步之遙，那就是需要統(tǒng)計(jì)出各學(xué)校每個(gè)專業(yè)的人數(shù)。有了它，那么平均分就簡單多了。這同樣可以使用SUMPRODUCT函數(shù)來完成。

　　圖3

　　在如圖3所示的人數(shù)統(tǒng)計(jì)表的N18單元格輸入公式“=SUMPRODUCT(($B$2:$B$16=$L18)*($C$2:$C$16=$M18)*(D$2:D$16<>""))”，然后向右向下復(fù)制公式就可以了。

　　求平均分即拿總分除以人數(shù)。因此，如果人數(shù)為“0”，那么求平均分時(shí)就會(huì)出現(xiàn)問題。因此，在求平均分時(shí)，同樣需要用IF函數(shù)做一個(gè)判斷。借用前面的兩個(gè)表格，那么工業(yè)職專機(jī)電專業(yè)的語文平均分公式可寫為“=IF(ISERROR(N3/N18),"",N3/N18)”，其余的復(fù)制公式即可，如圖4所示。

　　圖4

　　公式中的ISERROR(N3/N18)作用判斷“N3/N18”結(jié)果是否會(huì)出現(xiàn)錯(cuò)誤。

　　如果直接不借助人數(shù)統(tǒng)計(jì)表格直接求平均分，那么該公式可寫為“=IF(SUMPRODUCT(($B$2:$B$16=$L3)* ($C$2:$C$16=$M3)*(D$2:D$16<>""))=0,"",SUMPRODUCT(($B$2:$B$16=$L3)* ($C$2:$C$16=$M3),D$2:D$16)/SUMPRODUCT(($B$2:$B$16=$L3)*($C$2:$C$16=$M3)* (D$2:D$16<>"")))”。

　　三、多條件下的排名次

　　在不破壞原來數(shù)據(jù)表排序的情況下，為每位學(xué)生排定在本校本專業(yè)內(nèi)的名次，看起來很難，但是有了SUMPRODUCT就不一樣了。試想一下，借助于SUMPRODUCT函數(shù)，我們可以輕松統(tǒng)計(jì)出符合多個(gè)條件的人數(shù)。那么工業(yè)職專機(jī)電專業(yè)總分為“616”的同學(xué)的名次，不就是學(xué)校為“工業(yè)職專”、專業(yè)為“機(jī)電”、總分“>616”的人數(shù)再加上1嘛!

　　有了這個(gè)思路，公式就好寫了吧? J2單元格公式為“=SUMPRODUCT(($B$2:$B$16=B2)*($C$2:$C$16=C2)*($I$2:$I$16>I2))+1”，寫完后，向下復(fù)制公式就行了。結(jié)果如圖5所示。

　　圖5