學不完．教不停．用不盡

有老師問到在 Excel 中關於成績統計分析的問題，成績表如下圖左：

1. 由成績表中經由選取不同科目，可以顯示不同統計表。

2. 統計表中列出不重覆的成績。

3. 依個別不同成績列出該成績的人數。

4. 依不同成績由小至大列出座號。

 

【公式設計與解析】

如果要能動態選取科目而顯示不同的統計表，需要藉助下拉式選單和儲存格範圍的名稱定義。

首先，在儲存格K1中設定資料驗證：

資料驗證準則：清單

來源：=$C$2:$E$2

接著，選取儲存格A2:E12，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：座號、姓名、國文、數學、英文。

本例使用輔助欄位減少公式複雜度。新增國文、數學、英文三個輔助欄位（參考下圖）。

儲存格G3：

=IFERROR(OFFSET(C$3,IF(COUNTIF(C$3:C3,C3)>1,"",ROW(C3))-3,0),"")

複製儲存格G3，貼至儲存格G3:I12。

接著，輸入統計區的公式。

1. 列出不重覆的成績

儲存格K3：=IFERROR(LARGE(OFFSET($G$3,0,MATCH($K$1,$C$2:$E$2,0)-1,
COUNTA(座號),1),ROW(1:1)),"")

複製儲存格K3，貼至儲存格K3:K12。

 

2. 統計各個成績的人數

儲存格L3：=IF(K3<>"",COUNTIF(INDIRECT($K$1),K3),"")

複製儲存格L3，貼至儲存格L3:L12。

 

3. 由小至大列出各個成績的座號

儲存格M3：{=IFERROR(SMALL(IF(INDIRECT($K$1)=$K3,ROW(座號),""),
COLUMN(A:A))-2,"")}

複製儲存格M3，貼至儲存格M3:R12。

使用時將輔助欄位加以隱藏，只要選取科目即可動態顯示不同的統計表。