7楼未考虑2月份的跨月情况,比如B3=2006-3-1,返回5——已经超过A7的持续天数。 如果D2:G2升序排列,7楼可以免去许多Max和Min,直接用D2、G2。下面给个公式不用排序,而且没有2月份跨月问题的数组公式: =TEXT(LOOKUP(B3,SMALL(DATE(YEAR(B3),MONTH(B3)-{1;0},D2:G2),ROW($1:$8)))+A7-B3,"0;""N"";""N""")简化: =TEXT(LOOKUP(B3,SMALL(DATE(YEAR(B3),MONTH(B3)-{1;0},D2:G2),ROW($1:$8)))+A7-B3,"[>0]0;""N""") 公式长度计92字符。 思路:用Month(B3)-{1;0}得到上个月和本月的月份,则Date得到8个数,即上月的四个日期和本月的四个日期。 用Small(Date,Row(1:8))将日期排序,再用Lookup查找得到小于等于B3的日期——即最合适的起始日期。剩下的+A7-B3就简单了。假如B3=2006-11-10,则找到的Date就是2006-11-6,由6日加上A7的4天再减去B3,得到0,同样,B3如果是2006-11-11,则得到-1,因此只要是找到的最合适日期与输入日期的差额超过了连续天数,得到负数或0,所以用Text将其显示为"N"。也可以不用Text,而是设置单元格格式的方法,公式更短些,得到的是数值。 事实上,多数人很容易被这么一个观念所误导,即——上个月最多就用一个日期,即当B3的日期小于D2的时候用上个月的G2。我把上个月的D2:F2三个日期也算上,虽然他们用不上。 注意点:Month-{1;0},因为D2:G2是水平数组,所以月份要用垂直数组,得到2行4列的8个日期。 2006-12-27补充: =TEXT(LOOKUP(B3,SMALL(DATE(YEAR(B3),MONTH(B3)-{1;0},IF(D2:G2>DAY(DATE(YEAR(B3),MONTH(B3),0)),9^9,D2:G2)),ROW($1:$8)))+A7-B3,"[>0]0;""N""") 即:D2:G2的起始日期如果不是上个月的日期(其中Date(YEAR(B3),MONTH(B3),0)返回B3日期的上个月末的日期,如果有加载分析工具库,可以用EOMONTH(B3,-1)代替),则返回一个大的数字目前日期是5位数,用9^9已经很大了,如果要写也可以用9E+307(即遥远的未来的某天),再对日期排序。 这样就可以将2006-2-30默认为是2006-3-2、4-31默认为5-1等这种情况避免掉。 再给一个类似的应用实例: http://club.excelhome.net/dispbbs.asp?boardid=3&replyid=634978&id=207180&page=1&skin=0&Star=2第14楼 一起收录为blog的:
[此贴子已经被作者于2006-12-27 15:43:57编辑过] |