現在正值期末，教師通常會做一些課程回饋的問卷調查，因為多個班級共用一個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)式傳回相同班級的數量，即可求得平均數。
留言列表