贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

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

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

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

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

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

Excel-動態學生成績表摘要(OFFSET,MATCH,INDIRECT)

 

【公式設計與解析】

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

Excel-動態學生成績表摘要(OFFSET,MATCH,INDIRECT)

Excel-動態學生成績表摘要(OFFSET,MATCH,INDIRECT)

首先,在儲存格K1中設定資料驗證:

資料驗證準則:清單

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

Excel-動態學生成績表摘要(OFFSET,MATCH,INDIRECT)

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

Excel-動態學生成績表摘要(OFFSET,MATCH,INDIRECT)

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

儲存格G3:

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

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

Excel-動態學生成績表摘要(OFFSET,MATCH,INDIRECT)

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

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。

Excel-動態學生成績表摘要(OFFSET,MATCH,INDIRECT)

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

Excel-動態學生成績表摘要(OFFSET,MATCH,INDIRECT)

文章標籤
創作者介紹
創作者 vincent 的頭像
vincent

學不完.教不停.用不盡

vincent 發表在 痞客邦 留言(2) 人氣()


留言列表 (2)

發表留言
  • polina870523
  • 請問可以問一下如何找出資料的公式嗎?
  • erica1210
  • Hello你好! 我因為工作的關係,很多時侯都需要用到EXCEL(可是本人是白痴/0\),所以一直都有關注你的部落格,真的學習了很多。我想請教一下,我現在要計算同學參加活動的次數,橫列是活動,下面是把學生分類在參加該活動的次數,直列是3次2次跟1次,我需要計算有多少個學生參加了3次或以上活動 (學生姓名不能重覆)。 謝謝你/__\ 我有用COUNTIF,但都是算錯/_\

    感謝你!!! /_\ 我會非常感激你/_\
  • 無法理解您表格的呈現方式,如果可以,可以將表格寄vincent.teachergmail .com

    vincent 於 2018/11/23 15:39 回覆

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼