學不完．教不停．用不盡

現在正值期末，教師通常會做一些課程回饋的問卷調查，因為多個班級共用一個Google表單，所以只會得到一個試算表結果，如何將數據轉換成各個班級的各個題目平均分數？

如下圖，很多學生填答，在此只留下班級資料(刪附學生個人資料)，想要以此得到一個班級和題目的對照平均分數。

【公式設計與解析】

這個工作看起來很複雜，只要用對方法，公式處理起來還算簡單。

在此，將題目轉換成甲,乙,丙,丁,戊,己庚,辛,壬,癸，班級為201,202,203,204,205,206。

首先，選取所有的資料來源，按 Ctrl+Shift+F3 鍵，勾選：頂端列。

定義名稱：班級,甲,乙,丙,丁,戊,己庚,辛,壬,癸。

接著，輸入公式，儲存格N2：

=SUMPRODUCT(INDIRECT($M2)*(班級=N$1))/SUMPRODUCT((班級=N$1)*1)

然後，複製儲存格N2，貼至儲存格N2:S11。

其中公式：

(1) INDIRECT($M2)

因為題目已經轉換為：甲,乙,丙,丁,戊,己庚,辛,壬,癸，

而甲,乙,丙,丁,戊,己庚,辛,壬,癸也已經定義了各個題目數據的儲存格範圍。

透過 INDIRECT 函數就可以將儲存格M2內容的字串轉換為儲存格範圍。

(2) SUMPRODUCT(INDIRECT($M2)*(班級=N$1))

在 SUMPRODUCT 函數中設定條件：班級=N$1，要找出符合班級名稱和儲存格N1相同者，再與第(1)式的傳回值相乘。

結果就會取得和儲存格N1相同的班級數據的總和。

(3) SUMPRODUCT((班級=N$1)*1)

用以取得和儲存格N1相同的班級數量。因為條件式會傳回 TRUE／FALSE 陣列，在經過「*1」運算後，會得到 1／0 的陣列。加總 1／0 之後就可得合於條件的數量。

(4) 第(2)式/第(3)式

用以求得平均數。將第(2)式所傳回相同的班級數據的總和，再除以第(3)式傳回相同班級的數量，即可求得平均數。