在 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 陣列，再行加總。
【補充資料】
詳細函數說明請參閱微軟網站：
INDIRECT：http://office.microsoft.com/zh-tw/excel-help/HP010342609.aspx
|
INDIRECT：傳回文字串所指定的參照位址。 |
|
語法：INDIRECT(ref_text,[a1]) ref_text：單一儲存格的參照位址，其中包含A1欄名列號表示法、R1C1欄名列號表示法、定義為參照位址的名稱，或定義為字串的儲存格參照位址。 a1:指定ref_text儲存格中所包含參照位址類型的邏輯值。 |
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(星期六)。 |
SUMPRODUCT：http://office.microsoft.com/zh-tw/excel-help/HP010342935.aspx
|
SUMPRODUCT：傳回各陣列中所有對應元素乘積的總和。 |
|
語法：SUMPRODUCT(array1, [array2], [array3], ...) array1：要求對應元素乘積和的第一個陣列引數。 array2, array3,...：要求對應元素乘積和的第 2 個到第 255 個陣列引數。 註：各陣列必須有相同的維度(相同的列數，相同的欄數)。否則會傳回錯誤值#VALUE!。並且會將所有非數值資料的陣列元素當成0來處理。 |
留言列表