2010年6月14日 星期一

Excel-計算每個月各星期幾的日數

在Excel中,如果想要建立一個每年每個月星期幾數量的統計表(如下圖),該如何處理呢?

image01

首先建立一個微調按鈕控制項,其設定如下:

(表單控制項的操作可參考:http://isvincent.blogspot.com/2010/05/excel_7085.html)

接著輸入公式:

儲存格B3:=DATE($B$1,A3,1)

將儲存格數值格式設定為「月/日」,複製儲存格B3到儲存格B3:B14。

 

儲存格C3:=EOMONTH(DATE($B$1,A3,1),0)

利用EOMONTH函數,求得各月的最後一天。

(相關用法請參考:http://isvincent.blogspot.com/2010/04/excel_1137.html)

將儲存格數值格式設定為「月/日」,複製儲存格C3到儲存格C3:C14。

 

儲存格D3:=SUMPRODUCT((WEEKDAY(ROW(INDIRECT($B3&":"&$C3)),2)=COLUMN(D3)-3)*1)

複製儲存格D3到儲存格D3:I14。

其原理為:

INDIRECT($B3&":"&$C3):以B3和C3儲存格內容(數值)轉換為儲存格位址。

ROW(INDIRECT($B3&":"&$C3)):儲存格位址轉為列數。

WEEKDAY(ROW(INDIRECT($B3&":"&$C3)),2):將列數代入WEEKDAY函數,求得星期幾的陣列。

WEEKDAY(ROW(INDIRECT($B3&":"&$C3)),2)=COLUMN(D3)-3):判斷其值是否為1,2,3…7。

WEEKDAY(ROW(INDIRECT($B3&":"&$C3)),2)=COLUMN(D3)-3)*1:將邏輯判斷值轉為數字(1或0)

SUMPRODUCT((WEEKDAY(ROW(INDIRECT($B3&":"&$C3)),2)=COLUMN(D3)-3)*1)

將邏輯判斷值轉為的數字加總。

 

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

語法:WEEKDAY(serial_number,[return_type])

Serial_number:必要參數。要找的日期的代表序列值。日期必須使用 DATE 函數、其他公式或函數的結果來輸入。

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

Return_type 傳回的數字 :
1或省略:1 (週日) 到 7 (週末)。與 Microsoft Excel 舊版的性質相同。
2:數字 1 (星期一) 到 7 (星期日)。
3:數字 0 (星期一) 到 6 (星期六)。

 

儲存格K3:=SUMPRODUCT((WEEKDAY(ROW(INDIRECT($B3&":"&$C3)),2)>5)*1)

複製儲存格B3到儲存格K3:K14。

如果判斷邏輯值轉換後的數字大於5,即表示為星期六(6)或星期日(7),加總的結果可得週休日數。

 

使用微調按鈕,可以立即顯示各年度的星期幾日數。

沒有留言:

張貼留言

檢視其他文章

好康東東