日期在多个条件之间的行(仅适用于Excel) [英] Rows with dates between multiple conditions (excel only)
问题描述
我有列A
:
str 11/9/2007
end 10/10/2008
str 11/14/2008
end 5/29/2009
str 11/27/2009
end 2/5/2010
str 10/8/2010
end 4/8/2011
str 4/29/2011
end 5/20/2011
str 7/8/2011
end 10/21/2011
和 B列
:
9/6/2007
9/7/2007
9/10/2007
9/11/2007
9/12/2007
9/13/2007
9/14/2007
9/17/2007
9/18/2007
9/19/2007
9/20/2007
9/21/2007
9/24/2007
9/25/2007
9/26/2007
9/27/2007
9/28/2007
10/1/2007
10/2/2007
10/3/2007
10/4/2007
10/5/2007
10/8/2007
10/9/2007
10/10/2007
10/11/2007
10/12/2007
10/15/2007
10/16/2007
我想在 B列旁边的列中插入
如果 1
列B
日期在开始
和中的任何一个结束
范围在列A
中。因此,预期的输出类似于:
I'd like to insert 1
in the column next to Column B
if Column B
date is in any of the start
and end
range in Column A
. So the expected output is something like:
0
0
0
1
1
1
1
1
1
1
1
1
1
1
有什么提示吗?
推荐答案
以s(i)和e(i)表示您的开始和结束日期(i = 1,2,3,...,N)和ta目标日期。
Let s(i) and e(i) denote your start and end dates (i=1,2,3,...,N) and t a target date.
您需要确定是否存在i的值,使得s(i)< = t< = e(i)。
You need to find if there exists a value of i such that s(i)<=t<=e(i).
您可以使用 MATCH
函数执行此操作。
You can use the MATCH
function to do this.
s(i )和e(i)(i = 1,2,3,...,N)应该分别按升序排列(由评论者@pnuts建议)。让开始
和结束
分别表示包含这些开始日期和结束日期列的范围。假设 Target
表示目标日期t。
The s(i) and e(i) (i=1,2,3,...,N) should each be arranged (as suggested by commenter @pnuts) in a column in ascending order. Let Start
and End
respectively denote the ranges containing these columns of start and end dates. Let Target
denote a target date, t.
= MATCH(Target,Start, 1)
返回开始
内的日期的索引,该日期是在目标日期或之前的最新日期。因此,它返回索引的候选值,即s(i)<= t。剩下的就是测试该候选值是否为e(i)> = t,即
=MATCH(Target,Start,1)
returns the index of the date within Start
which is the latest date which is on or before the target date. So it returns a candidate value for the index, i such that s(i)<=t. All that remains is to test whether e(i)>=t for this candidate value, ie
= INDEX(End,MATCH(Target,开始,1))> =目标
通常,这将提供 TRUE
如果存在i的值,则s(i)<= t <== e(i),而 FALSE
否则。当目标日期t出现在第一个开始日期s(1)之前时,会发生这种情况。在这种情况下,将传递错误值#N / A
。您可以使用 IFERROR
函数捕获此错误,以提供 FALSE
来代替,然后可以转换布尔值使用常规
-
和 FALSE
设为0和1的结果>(双重否定)操作。
Generally, this will deliver TRUE
if there exists a value of i such that s(i)<=t<=e(i) and FALSE
otherwise. The exception to this occurs when the target date t occurs before the first start date s(1). In this case the error value #N/A
is delivered. You can trap this with the IFERROR
function to deliver FALSE
instead and you can convert the booleans TRUE
and FALSE
to the 0 and 1 result you are looking for using the usual --
(double negation) operation.
重要的是,开始日期和结束日期应按升序排列(最早)。如果不是这样,该解决方案将无法正常工作。
It is important that the start and end dates are arranged in ascending (earliest first) order. If not this solution will not work.
这篇关于日期在多个条件之间的行(仅适用于Excel)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!