2012年10月27日 星期六

Excel-計算各月不含星期六日的天數

在 Excel 中,如果想要計算各月不含星期六和星期日的天數,該如何處理?(參考下圖)

【公式解析】

儲存格C3:=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT("A" & DATE(A3,B3,1) & ":" & "A" & DATE(A3,B3+1,1)-1)),2)<6))

複製儲存格C3,貼至儲存格C3:C14。

DATE(A3,B3,1):找出每月第1天所代表的數值

DATE(A3,B3+1,1)-1:找出每月最後1天所代表的數值

ROW(INDIRECT("A" & DATE(A3,B3,1) & ":" & "A" & DATE(A3,B3+1,1)-1)):將上述二個日期轉換成一段儲存格陣列。例如:2012年8月轉換為 A40909:A40939。

接著透過 WEEKDAY 函數以參數 2,找出所有星期一到星期五的陣列。

最後以 SUMPRODUCT 函數計算星期一到星期五的陣列數,其中「--」是要將 True/False 陣列透過 -- 運算,轉換為 1/0 陣列,才能加總。

 

【補充資料】

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

INDIRECThttp://office.microsoft.com/zh-tw/excel-help/HP010342609.aspx

INDIRECT:傳回文字串所指定的參照位址。

語法:INDIRECT(ref_text,[a1])

ref_text:單一儲存格的參照位址,其中包含A1欄名列號表示法、R1C1欄名列號表示法、定義為參照位址的名稱,或定義為字串的儲存格參照位址。

a1:指定ref_text儲存格中所包含參照位址類型的邏輯值。

 

WEEKDAYhttp://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(星期六)

 

SUMPRODUCThttp://office.microsoft.com/zh-tw/excel-help/HP010342935.aspx

SUMPRODUCT:傳回各陣列中所有對應元素乘積的總和。

語法:SUMPRODUCT(array1, [array2], [array3], ...)

array1:要求對應元素乘積和的第一個陣列引數。

array2, array3,...:要求對應元素乘積和的第 2 個到第 255 個陣列引數。

註:各陣列必須有相同的維度(相同的列數,相同的欄數)。否則會傳回錯誤值#VALUE!。並且會將所有非數值資料的陣列元素當成0來處理。

 

沒有留言:

張貼留言

檢視其他文章

好康東東