有老師想要在成績表上列出指定科目數不及格者的清單,該如何處理?

例如,假設想要在以下的成績清單中列出3科以上不及格者。

Excel-列出成績表上N科不及格的清單

【公式設計與解析】

顧慮有些教師不是很熟悉 Excel 的公式設計,為了該其方便套用公式,所以先為儲存格範圍定義名稱。

選取儲存格A2:H32,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:座號、姓名、國文、英文、數學、自然、社會、輔助。

1. 使用輔助欄位

輔助欄位,儲存格H3:=COUNTIF(C3:G3,"<60")

複製儲存格H3,貼至儲存格H3:H19。

利用 COUNTIF 函數計算在儲存格C3:G3中小於60的個數。

列出座號,儲存格I3:

{=IFERROR(INDEX(座號,SMALL(IF(輔助>=3,ROW(座號),""),ROW(1:1))-2),"")}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。

複製儲存格I3,貼至儲存格I3:I19。

(1) IF(輔助>=3,ROW(座號),"")

在陣列公式中判斷如果輔助欄位內容大於3者,傳回符合者座號儲存格的列號陣列。

(2) SMALL(第(1)式,ROW(1:1))

利用 SMALL 函數將第(1)式傳回的陣列由小至大取出最小值。

ROW(1:1)=1,公式向下複製時,ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→…。

(3) INDEX(座號,第(2),式-2)

利用 INDEX 函數根據第(2)式傳回的座號陣列,在座號儲存格範圍中查詢對應的內容。

(4) IFERROR(第(3)式,"")

利用 IFERROR 函數將傳回的錯誤訊息以空白顯示。

相同做法,列出姓名,儲存格J3:

{=IFERROR(INDEX(姓名,SMALL(IF(輔助>=3,ROW(姓名),""),ROW(1:1))-2),"")}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。

複製儲存格J3,貼至儲存格J3:J19。

 

2. 不使用輔助欄位

Excel-列出成績表上N科不及格的清單

【公式設計與解析】

如果你不想使用輔助欄位,則可以藉助 MMULT 函數。

儲存格I3:{=IFERROR(INDEX(座號,SMALL(IF(MMULT(1*($C$3:$G$32<60),
{1;1;1;1;1})>=3,ROW(座號),""),ROW(1:1))-2),"")}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。

複製儲存格I3,貼至儲存格I3:I19。

公式:MMULT(1*($C$3:$G$32<60),{1;1;1;1;1}),可以取得和輔助欄位相同的內容。

 

同理:

儲存格J3:{=IFERROR(INDEX(姓名,SMALL(IF(MMULT(1*($C$3:$G$32<60),
{1;1;1;1;1})>=3,ROW(姓名),""),ROW(1:1))-2),"")}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。

複製儲存格J3,貼至儲存格J3:J19。

【延伸閱讀】

 Excel-和成績、分數的相關文章

 Excel-列出指定次數最佳成績的平均

 Excel-利用Google表單讓學生依評分量表實施分組互評並計算分數

 Excel-依類別成績代碼轉換為分數(SUMPRODUCT)

 Google Classroom-使用評分量表對學生作業評分

 Google表單設計測驗的單選題、複選題、多選題

 Excel-設計Google表單測驗多選題並在Excel中自動帶入結果得到分數

 Excel-設計Google表單測驗多選題並以Excel計算分數

 Excel-取用Google表單的線上測驗結果自行計算分數(SUMPRODUCT)

 用Google表單來設計線上測驗卷並且評分,還可以獲得測驗結果的統計分析

 Google Classroom 的問題和測驗作業功能

 實施遠距教學多元評量讓同儕互評(Google表單+Excel)

學不完.教不停.用不盡文章列表

arrow
arrow
    文章標籤
    Excel 不及格
    全站熱搜

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