Excel-找出每个月、各个星期几的最大值及最小值(MONTH,WEEKDAY,ROW)
在 Excel 的日期清单中(如下图),如何找出每个月、每个星期几的最大值和最小值?
本篇练习重点:阵列公式、名称定义、WEEKDAY 函数、MONTH 函数、ROW 函数、MAX 函数、MIN 函数、IF 函数、。
【公式设计与解析】
选取储存格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),如果是星期一者,传回对应的数值阵列。
当公式向下复制时,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 运算。即要符合二个条件者,传回其对应的数值阵列。