2011年5月2日 星期一

Excel-依星期中各天統計不固定數量的資料

在 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

WEEKDAY:傳回符合日期的星期。給定的日預設為介於1(星期日)7(星期六) 之間的整數。

語法:WEEKDAY(serial_number,[return_type])

Serial_number:要找的日期的代表序列值。

Return_type:決定傳回值類型的數字。

 

RETURN_TYPE

傳回的數字

1 或省略

數字 1 (星期日) 7 (星期六)

2

數字 1 (星期一) 7 (星期日)

3

數字 0 (星期一) 6 (星期六)

11

數字 1 (星期一) 7 (星期日)

12

數字 1 (星期二) 7 (星期一)

13

數字 1 (星期三) 7 (星期二)

14

數字 1 (星期四) 7 (星期三)

15

數字 1 (星期五) 7 (星期四)

16

數字 1 (星期六) 7 (星期五)

17

數字 1 (星期日) 7 (星期六)

 

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

 

傳回根據所指定的儲存格位址、列距及欄距而算出的參照位址。

語法:OFFSET(reference, rows, cols, [height], [width])

Reference:用以計算位移的起始參照位址。

Rows:左上角儲存格要往上或往下參照的列數。

Cols:結果的左上角儲存格要往左或往右參照的欄數。

Height:所傳回參照位址的高度 (以列數為單位)

Width:所傳回參照位址的寬度 (以欄數為單位)

 

沒有留言:

張貼留言

好康東東