學不完．教不停．用不盡

在 Excel 中有一個資料表是由星期一至星期五組成的A,B,C,D五組數據資料，如何根據星期幾統計超過某值的個數有多少？資料會不斷的增加，而量測的某值要能變動。

儲存格B2：=A2，並設定數值格式顯示「星期X」。

由資料會不斷的增加，所以無法取得實際的位址，因此要以定義名稱的方式來取得不固定的位址。

DATA：=OFFSET(工作表1!$A$2,,,COUNTA(工作表1!$B:$B)-1,)

利用COUNTA取得B欄中有幾筆資料，COUNTA(工作表1!$B:$B)-1是因為第一列不是資料。

同理，設定以下四個名稱。

DATAA：=OFFSET(工作表1!$C$2,,,COUNTA(工作表1!$C:$C)-1,)

DATAB：=OFFSET(工作表1!$D$2,,,COUNTA(工作表1!$D:$D)-1,)

DATAC：=OFFSET(工作表1!$E$2,,,COUNTA(工作表1!$E:$E)-1,)

DATAD：=OFFSET(工作表1!$F$2,,,COUNTA(工作表1!$F:$F)-1,)

要計算各天大於等於(不小於)某值的數量，參考如下的做法：

儲存格I3：=SUMPRODUCT((WEEKDAY(DATA,2)=ROW(1:1))*(DATAA>=I$1))

WEEKDAY(DATA,2)=ROW(1:1)，要透過WEEKDAY函數取得是星期一的True/False陣列。

DATAA>=I$1，表示在A的資料中大於等於儲存格I1的數值True/False陣列。

以SUMPRODUCT函數將滿足以上兩個條件者相成，可以將True/False陣列轉為1/0陣列，其緦和即為答案。

同理設定：

儲存格J3：=SUMPRODUCT((WEEKDAY(DATA,2)=ROW(1:1))*(DATAB>=I$1))

儲存格K3：=SUMPRODUCT((WEEKDAY(DATA,2)=ROW(1:1))*(DATAC>=I$1))

儲存格L3：=SUMPRODUCT((WEEKDAY(DATA,2)=ROW(1:1))*(DATAD>=I$1))

複製儲存格I3:L3，貼至儲存格I3:L7。

如果持續輸入資料，統計表會持續更新，而更正儲存格I1時，也可因為不同條件得到不同計算結果。

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

WEEKDAY：http://office.microsoft.com/zh-tw/excel-help/HP010343015.aspx

OFFSET：http://office.microsoft.com/zh-tw/excel-help/HP010342739.aspx