總分平均分怎麼算

問題情景

如下表格：

共18個班級，七百多名學生，要求統計每個班級 總分前30名的同學各科平均成績，如下圖：

注意，並不是各科前30名，而是總分前30名。假設某同學，語文滿分，但是總分不在班級前30內，他的語文成績也不能統計在內。

公式實現

1、計算總分

這一步最容易，公式：=SUM（F2：L2），如下圖：

2、計算班級排名

在D2單元格輸入公式：=SUMPRODUCT（（$A$2：$A$710=A2）*（$E$2：$E$710>E2））+1，確定，並向下填充，得到所有同學在本班內的名次，如下圖：

該公式的結果：如果總會相同，名次則相同。

公式解釋參考：Excel | SUMPRODUCT函式實現兩種特殊但又常用的排名

3、計算總分前30名各科平均分

在P2單元格輸入公式：=AVERAGE（LARGE（（$D$2：$D$710<=30）*（$A$2：$A$710=$N3）*E$2：E$710，ROW（$A$1：$A$30））），以“Ctrl+Shift+Enter”三鍵組合結束公式輸入，得1班前30名總分平均分，公式向右、向下填充，得到每個班級 總分前30名的同學各科平均成績，如下圖：

公式解析

$D$2：$D$710<=30：

所有同學的名次與30比較，如果在30以內則返回TRUE，否則返回FALSE，本部分返回709個TURE與FALSE組成的陣列。

$A$2：$A$710=$N3：

將班級與N3班級比較，如果相同，則返回TRUE，否則返回FALSE，本部分返回709個TURE與FALSE組成的陣列。

（$D$2：$D$710<=30）*（$A$2：$A$710=$N3）*E$2：E$710：

將前兩步得到的兩個陣列與所有的總分相乘，如果滿足名次在30名以內且班級與N3相同，則返回部分，否則返回0。本部分返回值是N3班級的前30名部分與0組成的709個數值的陣列。

LARGE（（$D$2：$D$710<=30）*（$A$2：$A$710=$N3）*E$2：E$710，ROW（$A$1：$A$30））：

取上一步得到的陣列內的前30名。

=AVERAGE（LARGE（（$D$2：$D$710<=30）*（$A$2：$A$710=$N3）*E$2：E$710，ROW（$A$1：$A$30））），以“Ctrl+Shift+Enter”三鍵組合結束公式輸入，計算出N3班級的前30名平均總分。

韓老師結合多年經驗與粉絲們提出的各種企業事業單位問題集合成的一本非常實用的書，歡迎朋友們品鑑：