在 Excel 的日期清單中(如下圖),如何找出每個月、每個星期幾的最大值和最小值?

本篇練習重點:陣列公式、名稱定義、WEEKDAY 函數、MONTH 函數、ROW 函數、MAX 函數、MIN 函數、IF 函數、。

image

 

【公式設計與解析】

選取儲存格A1:C50,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期、星期、數值。

 

1. 每個星期幾的最大值

 

儲存格F2:{=MAX(IF(WEEKDAY(日期,2)=ROW(1:1),數值,""))}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。

(以下各個公式都是陣列公式)

複製公式儲存格F2,貼至儲存格F2:F8。

在陣列公式中,利用 WEEKDAY 函數利用參數「2」(值回值1~7對應星期一~星期日),判斷是否為星期一(ROW(1:1)=1),如果是星期一者,傳回對應的數值陣列。

image

當公式向下複製時,ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→...。

根據傳回對應的數值陣列,利用 MAX 函數取得其中的最大值。

2. 每個星期幾的最小值

儲存格G2:{=MIN(IF(WEEKDAY(日期,2)=ROW(1:1),數值,""))}

複製公式儲存格G2,貼至儲存格G2:G8。

根據傳回對應的數值陣列,利用 MIN 函數取得其中的最小值。

3. 每個月的最大值

儲存格F11:{=MAX(IF(MONTH(日期)=ROW(1:1),數值,""))}

複製公式儲存格F11,貼至儲存格F11:F16。

在陣列公式中,利用 MONTH 函數判斷是否為一月(ROW(1:1)=1),如果是一月,傳回對應的數值陣列。

4. 每個月的最小值

儲存格G11:{=MIN(IF(MONTH(日期)=ROW(1:1),數值,""))}

複製公式儲存格G11,貼至儲存格G11:G16。

5. 每個月各個星期幾的最大值

儲存格G19:{=MAX(IF((WEEKDAY(日期,2)=ROW(1:1))*(MONTH(日期)=
COLUMN(A:A)),數值,""))}

複製公式儲存格G19,貼至儲存格G19:K25。

其中「WEEKDAY(日期,2)=ROW(1:1))*(MONTH(日期)=COLUMN(A:A))」的「*」,乃執行邏輯 AND 運算。即要符合二個條件者,傳回其對應的數值陣列。

    文章標籤

    Excel MONTH WEEKDAY ROW

    全站熱搜

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