有老師想要在成績表上列出指定科目數不及格者的清單，該如何處理？
例如，假設想要在以下的成績清單中列出3科以上不及格者。
【公式設計與解析】
顧慮有些教師不是很熟悉 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. 不使用輔助欄位
【公式設計與解析】
如果你不想使用輔助欄位，則可以藉助 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-利用Google表單讓學生依評分量表實施分組互評並計算分數
Excel-依類別成績代碼轉換為分數(SUMPRODUCT)
Google Classroom-使用評分量表對學生作業評分
Excel-設計Google表單測驗多選題並在Excel中自動帶入結果得到分數
Excel-設計Google表單測驗多選題並以Excel計算分數
Excel-取用Google表單的線上測驗結果自行計算分數(SUMPRODUCT)
用Google表單來設計線上測驗卷並且評分，還可以獲得測驗結果的統計分析
實施遠距教學多元評量讓同儕互評(Google表單+Excel)
留言列表