2014年2月13日 星期四

Excel-找出每個月每個星期幾的日數

有網友想要知道一年中每個月份的星期一、星期二、…、星期日的數量,該如何處理?

參考下圖,以2014年為例,找出每個月星期日到星期六的日數:

【輸入公式】

儲存格C2:=SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(DATE($A2,$B2,1) & ":" & DATE($A2,$B2+1,0))),1)=COLUMN(A:A)))

DATE($A2,$B2,1):取得該月的第一天。

DATE($A2,$B2+1,0):取得該月的最後一天。【注意這個技巧,求下個月的 0 號日期,即為上個月的最後一天】

INDIRECT(DATE($A2,$B2,1) & ":" & DATE($A2,$B2+1,0)):將該月頭尾日期代入 INDIRECT 函數,轉換為一個儲存格範圍。例如一月為:$41640:$41670。

ROW(INDIRECT(DATE($A2,$B2,1) & ":" & DATE($A2,$B2+1,0))):將上式代入 ROW 函數,本例可得 ROW($41640:$41670)。

接著,將上式帶入 WEEKDAY 函數來取得一個星期幾的傳回值,其中參數「1」表示星期日 = 1、星期一 = 2、…、星期六 = 7。

利用上式來判斷是否等於 COLUMN(A:A),得到一個 True/False 陣列,WEEKDAY 函數前的「--」,可以將 True/False 陣列,經由運算轉換為 1/0 陣列。

最終,透過 SUMPRODUCT 函數,計算 1 的數量,即為所求。

接著,複製儲存格C3,貼至儲存格C2:I13。

至於數字(4、5)左側的符號,是設定格式化的條件,所選取的樣式而來的:

image

沒有留言:

張貼留言

檢視其他文章

好康東東