在 Excel 中有一個各班成績統計表，現在要定出上標值和下標值，求得各班在這個成績範圍的人數。(參考下圖)
(一) 定義各班的名稱：
(1) 選取儲存格B1:F24。
(2) 按一下 Ctrl+Shift+F3，開啟[以選取範圍建立名稱]。
(3) 勾選「頂端列」選項，按一下[確定]按鈕。
如此，便可建立五個名稱：三年1班、三年2班、三年3班、三年4班、三年5班。
(二) 計算超過上標人數：
儲存格I4：=COUNTIF(INDIRECT(H4),">"&$I$1)
其中INDIRECT(H4)乃將儲存格內容(三年1班)轉換為位址(名稱：三年1班)
如果你不使用名稱，也可輸入以下公式：
儲存格I4：=COUNTIF(OFFSET($B$2,0,ROW(1:1)-1,23,1),">"&$I$1)
使用OFFSET($B$2,0,ROW(1:1)-1,23,1)，可以由上而下的班級名稱，抓取由左而右的班級資料。
(三) 計算低於下標人數：
儲存格J4：=COUNTIF(INDIRECT(H4),"<"&$K$1)
如果你不使用名稱，也可輸入以下公式：
儲存格J4：=COUNTIF(OFFSET($B$2,0,ROW(1:1)-1,23,1),"<"&$K$1)
(四) 計算一般人數：
儲存格K4：=COUNTIF(INDIRECT(H4),">="&$K$1)-COUNTIF(INDIRECT(H4),">"&$I$1)
或是使用SUMPRODUCT函數：
儲存格K4：=SUMPRODUCT((INDIRECT(H4)>=$K$1)*(INDIRECT(H4)<=$I$1))
其中SUMPRODUCT函數中使用「*」，可以將True/False陣列轉換為1/0陣列。
或許你也可以使用陣列公式：
儲存格K4：{=SUM(IF((INDIRECT(H4)>=$K$1)*(INDIRECT(H4)<=$I$1),1,FALSE))}
輸入完成，請輸入 Ctrl+Shift+Enter 鍵。
【延伸閱讀】
Excel-利用Google表單讓學生依評分量表實施分組互評並計算分數
Excel-依類別成績代碼轉換為分數(SUMPRODUCT)
Google Classroom-使用評分量表對學生作業評分
Excel-設計Google表單測驗多選題並在Excel中自動帶入結果得到分數
Excel-設計Google表單測驗多選題並以Excel計算分數
Excel-取用Google表單的線上測驗結果自行計算分數(SUMPRODUCT)
用Google表單來設計線上測驗卷並且評分，還可以獲得測驗結果的統計分析
實施遠距教學多元評量讓同儕互評(Google表單+Excel)
詳細函數說明請參閱微軟網站：
INDIRECT：http://office.microsoft.com/zh-tw/excel-help/HP010342609.aspx
|
INDIRECT：傳回文字串所指定的參照位址。 |
|
語法：INDIRECT(ref_text,[a1]) ref_text：單一儲存格的參照位址，其中包含A1欄名列號表示法、R1C1欄名列號表示法、定義為參照位址的名稱，或定義為字串的儲存格參照位址。 a1:指定ref_text儲存格中所包含參照位址類型的邏輯值。 |
COUNTIF：http://office.microsoft.com/zh-tw/excel-help/HP010342346.aspx
|
COUNTIF：計算範圍內符合您所指定單一條件準則的儲存格總數。 |
|
語法：COUNTIF(range, criteria) range：要列入計算的一個或多個儲存格，包括數字或包含數字的名稱、陣列或參照；空白或文字值會忽略。 criteria：定義要將哪些儲存格列入計算的準則，可以是數字、表示式、儲存格參照或文字字串。 可以在準則中使用萬用字元：問號 (?) 及星號 (*)；問號可以替代任何單一字元；星號可以替代任何一系列的字元。如果確實要尋找實際的問號或星號，請在該字元前輸入波狀符號 (~)。 |
留言列表