2011年11月27日 星期日

Excel-計算一段時間範圍內每個星期幾的天數(SUMPRODUCT、陣列)

在 Excel 的工作表中,如果指定二個起迄日期,要求出這兩個日期之間,每個星期幾的天數有幾天,該如何處理(參考下圖)?以下要分別以陣列公式和 SUMPRODUCT 函數來計算。

 

【方法一:使用陣列公式】

儲存格D2:{=SUM(IF(WEEKDAY(ROW(INDIRECT($A$2 & ":" & $A$5)),2)=ROW(1:1),1,0))}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵。

複製儲存格D2,貼至儲存格D2:D8。

本公式要利用陣列來運算,所以先取得兩個日期間的日期數字(每個日期都有一個數字代表)的陣列。

INDIRECT($A$2 & ":" & $A$5):將兩個日期數字轉換成 XXX:XXX 形式。

ROW(INDIRECT($A$2 & ":" & $A$5)):利用 ROW 函數取得兩個日期間的所有數字(所有日期)。

利用 WEEKDAY 函數判斷是否為 1 (是否等於ROW(1:1)),如果成立則給予 1,否則給予 0。

加總這些數字,即可得有幾個星期一的日數。

將儲存格D2住下複製時,ROW(1:1)=1 → ROW(2:2)=2→ … → ROW(7:7)=7,可求得每個星期幾的天數。

 

【方法二:使用SUMPRODUCT 函數】

儲存格D2:=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT($A$2 & ":" & $A$5)),2)=ROW(1:1)))

複製儲存格D2,貼至儲存格D2:D8。

SUMPRODUCT 函數中使用「--」,是為了將 True/False 陣列轉換為 1/0 陣列,才能以數值計算。

原理和「方法一」一樣,因為 SUMPRODUCT 函數本來就是以陣列形式來運算。

沒有留言:

張貼留言

好康東東