2013年1月17日 星期四

Excel-計算不含六日的天數(NETWORKDAYS,WEEKDAY)

在 Excel 的工作表中,常見到要找出一段日期中不含六日的天數,該如何處理?(參考下圖)

你可以使用 NETWORKDAYS 函數很容易的求得結果,這次要用公式模擬這個函數的功能。

【公式解析】

(1) 儲存格C2:=NETWORKDAYS(A2,B2)

只要知道起始日期和終止日期,即可透過 NETWORKDAYS 函數求得不含六日的天數。

以下要來模擬 NETWORKDAYS 函數的功能:

(2) 儲存格C2:=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2)<=5))

複製儲存格C2,往下各列貼上。

ROW(INDIRECT(A2&":"&B2)):將起始和終止二個日期轉換成一段儲存格陣列。例如:2012/1/1 ~ 2012/3/1 轉換為 A40909:A40969。

WEEKDAY(ROW(INDIRECT(A2&":"&B2)),2)<=5:透過 WEEKDAY 函數以參數 2 找出所有星期一到星期五的陣列。INDIRECT 函數可以傳回文字串所指定的參照位址。

再以 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來處理。

沒有留言:

張貼留言

檢視其他文章

好康東東