如图(以当日计算周和月的高低值):
本周最高:
D2 =IF(B2="","",MAX(INDIRECT("B"&ROW()-(COUNTIF(A$2:A2,"<="&A2)-COUNTIF(A$2:A2,"<="&A2-WEEKDAY(A2,2)))+1&":B"&ROW())))
本周最低:
E2 =IF(C2="","",MIN(INDIRECT("C"&ROW()-(COUNTIF(A$2:A2,"<="&A2)-COUNTIF(A$2:A2,"<="&A2-WEEKDAY(A2,2)))+1&":C"&ROW())))
本月最高:
F2 =IF(B2="","",MAX(INDIRECT("B"&ROW()-(COUNTIF(A$2:A2,"<="&A2)-COUNTIF(A$2:A2,"<="&A2-DAY(A2)))+1&":B"&ROW())))
本月最低:
G2 =IF(C2="","",MIN(INDIRECT("C"&ROW()-(COUNTIF(A$2:A2,"<="&A2)-COUNTIF(A$2:A2,"<="&A2-DAY(A2)))+1&":C"&ROW())))
公式向下拉填充即可!
注:日期A列如A2为 2010-9-20,显示为 9-20
计算结果只参照A列日期所在周和月,若周末或节假日有数据会一同计算