2011年5月18日 星期三

Excel-統計各班某個成績範圍的人數

在 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 鍵。

詳細函數說明請參閱微軟網站:

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:定義要將哪些儲存格列入計算的準則,可以是數字、表示式、儲存格參照或文字字串。

可以在準則中使用萬用字元:問號 (?) 及星號 (*);問號可以替代任何單一字元;星號可以替代任何一系列的字元。如果確實要尋找實際的問號或星號,請在該字元前輸入波狀符號 (~)

1 則留言:

  1. 您教的Excel對我幫助太大了,教的好棒。

    回覆刪除

檢視其他文章

好康東東