2018年7月3日 星期二

Excel-計算二個日期區間裡每個星期幾的個數(SUMPRODUCT,WEEKDAY)

在 Excel 中,如何根據起迄日期計算二個日期區間裡每個星期幾的個數?參考下圖。
Excel-計算二個日期區間裡每個星期幾的個數(SUMPRODUCT,WEEKDAY)

【公式設計與解析】
儲存格C2:=SUMPRODUCT((WEEKDAY(ROW(INDIRECT(N($A2)&":"&
N($B2))),2)=COLUMN(A:A))*1)

複製儲存格C2,貼至儲存格C2:I17。
(1) INDIRECT(N($A2)&":"&N($B2))
N($A2):將起日的日期轉換為數字。
N($B2):將迄日的日期轉換為數字。
利用 INDIRECT 函數將 N($A2)&":"&N($B2) 轉換為儲存格範圍。
例如:100:200
(2) ROW(第(1)式)
將第一式傳回的儲存格範圍代入 ROW 函數,得到一個列的區間。
例如:ROW(100:200)
(3) WEEKDAY(第(2)式,2)
利用 WEEKDAY 函數將第(2)式傳回的儲存格範圍(數值),利用參數「2」,使其傳回值 1~7 對應至星期一~星期日。
Excel-計算二個日期區間裡每個星期幾的個數(SUMPRODUCT,WEEKDAY)
(4) 第(3)式=COLUMN(A:A)
判斷第(3)式傳回值是否和COLUMN(A:A)=1相同,傳回 TRUE /FALSE 陣列。
當公式向右複製時,COLUMN(A:A)=1→COLUMN(B:B)=2→COLUMN(C:C)→...。
(5) SUMPRODUCT(第(4)式*1)
其中的「*1」運算可以將 TRUE/FALSE 轉換為 1/0。在 SUMPRODUCT 函數將 1/0 加總,即為所求。

沒有留言:

張貼留言

檢視其他文章

好康東東