[求助]求倒数第n天

LangQueS

这是实用文件的局部，参见附件，求倒数第n天，谢谢。
0Z5LMzfL.rar (2.42 KB, 下载次数: 176)

2006-12-26 10:11 上传

点击文件名下载附件
[求助]求倒数第n天

[此贴子已经被作者于2006-12-26 10:11:08编辑过]

sahelo

	开始日期	截止日期	开始日1	开始日2	开始日3	开始日4
品名1			10	8	15	25
	2006-11-2	2
展示类别
展位统计
连展天数
9
传送任务
A

颜色字体之间的关系！能否详细叙述一下！？

mallen

抱歉，完全不知所云……

爱莫能助，请再详细明确的说明好吗？

^_^

LangQueS

QUOTE:

以下是引用sahelo在2006-12-26 9:28:05的发言：

	开始日期	截止日期	开始日1	开始日2	开始日3	开始日4
品名1			10	8	15	25
	2006-11-2	2
展示类别
展位统计
连展天数
9
传送任务
A

颜色字体之间的关系！能否详细叙述一下！？

开始日是每次活动的开始日期，例如开始日为10，说明在每个月的10日开始活动（活动穿插时取倒数日期的最大值）。

连展天数，是每次活动的持续天数。例如，10月25日开展，到下月2日已经结束，即"N"；11月25日开展，到下月2日是倒数第一天。

我现在用的公式是C3=IF(ISERROR(MATCH(DAY($B3),$D$2:$G$2,0)),"N",$A$7)，活动跨月会出问题。

[此贴子已经被作者于2006-12-26 9:45:18编辑过]

sahelo

看上面描述 B3单元格不是开始日期？！那是。。。

IF(ISERROR(MATCH(DAY($B3),$D$2:$G$2,0)),"N",$A$7)

返回的只有2个数据 要么N 要么A7 ？！

说实话，还是没看懂！

LangQueS

QUOTE:

以下是引用sahelo在2006-12-26 9:56:20的发言：

看上面描述 B3单元格不是开始日期？！那是。。。

IF(ISERROR(MATCH(DAY($B3),$D$2:$G$2,0)),"N",$A$7)

返回的只有2个数据 要么N 要么A7 ？！

说实话，还是没看懂！

那么不考虑活动的穿插，1楼的附件已经重新上传，又加了一些说明，看看是否说清楚了。谢谢！

fjlhgs

公式长了点。

C3=IF($B$3<MIN(DATE(YEAR($B$3),MONTH($B$3),$D$2:$G$2)),IF($B$3-DATE(YEAR($B$3),MONTH($B$3)-1,MAX($D$2:$G$2))+1>$A$7,"N",$A$7-$B$3+DATE(YEAR($B$3),MONTH($B$3)-1,MAX($D$2:$G$2))),IF($B$3-LOOKUP($B$3,DATE(YEAR($B$3),MONTH($B$3),$D$2:$G$2))+1>$A$7,"N",$A$7-$B$3+LOOKUP($B$3,DATE(YEAR($B$3),MONTH($B$3),$D$2:$G$2))))

数组公式。

LangQueS

好了，谢谢fjlhgs！

[此贴子已经被作者于2006-12-26 11:53:51编辑过]

gouweicao78

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""")简化：

QUOTE:

=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个日期。

QUOTE:

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编辑过]

LangQueS

gouweicao78先生的公式非常好，谢谢！谢谢！