在 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 加總,即為所求。

arrow
arrow
    文章標籤
    Excel SUMPRODUCT WEEKDAY
    全站熱搜

    vincent 發表在 痞客邦 留言(1) 人氣()