wps表格教程：用ET表格巧妙解決多條件下的成績統計

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

　　圖1

　　圖1所示為不同學校不同專業混編的示意成績冊。我們需要計算各學校各專業各科目的總分和平均分，並對各學生在各自學校各自專業內排定名次。這項看起來艱巨復雜的任務，在ET表格中正確使用SUMPRODUCT函數，那麼完成起來其實也並不算怎麼困難。咱們只需如此操作即可：

　　一、多條件求和

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

　　至於我們希望的各學校各專業各科目的總分，那就得設計如圖2所示表格來解決了。

　　圖2

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

　　由於各學校的專業設置並不相同，所以某些的某些專業的各科總分計算為“0”。如果想追求盡善盡美，那麼我們可以用IF函數，當結果為“0”時不顯示任何內容，這樣可以使結果顯示盡量美觀一些。公式為“=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函數來完成。

　　圖3

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

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

　　圖4

　　公式中的ISERROR(N3/N18)作用判斷“N3/N18”結果是否會出現錯誤。

　　如果直接不借助人數統計表格直接求平均分，那麼該公式可寫為“=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<>"")))”。

　　三、多條件下的排名次

　　在不破壞原來數據表排序的情況下，為每位學生排定在本校本專業內的名次，看起來很難，但是有了SUMPRODUCT就不一樣了。試想一下，借助於SUMPRODUCT函數，我們可以輕松統計出符合多個條件的人數。那麼工業職專機電專業總分為“616”的同學的名次，不就是學校為“工業職專”、專業為“機電”、總分“>616”的人數再加上1嘛!

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

　　圖5