检查日期范围和否 [英] Check for day range and no
问题描述
我有以下数据表:
正如你所看到的,我有一个日期和一个没有字符串。
现在我想过滤所有日期在+20天间隔相同的日期。所以例如:
如果 12.2.2014
的值为 a
然后它得到一个 1
,如果 3.2.2014
的值一个
它也是一个 1
。相反,如果值 15.1.2014
具有值 a
,它将获得 0
因为它不在 + - 20天范围内
。如果在下面的示例中存在两行,其中 10.07.2002
和值 d
则会得到一个0,因为
20天intervall .stack.imgur.com / 4ySc6.pngalt =在这里输入图像描述>
我的实现想法是:计算每个值和日期的差异日期,如果它小于或等于20并且等于1,则给出1 else 0。
可以在excel中完成吗?
我真的很感激任何建议
PS:我希望我清楚地显示问题! >
更新
使用这个公式我得到:
= COUNTIFS($ B $ 2:$ B $ 32; B2; $ A $ 2:$ A $ 32;>&(A2-20); $ A $ 2:$ A $ 32;&(A2 + 20)) - 1
请参阅col E
这样的东西(调整 $ B $ 2:$ B $ 32
等到您的实际数据范围):
= COUNTIFS($ B $ 2:$ B $ 32,B2,
$ A $ 2:$ A $ 32,>&(A2-20),
$ A $ 2:$ A $ 32,&(A2 + 20)) - 1
-1
是为了避免计算公式的行
只给0或1:
= MIN (COUNTIFS($ B $ 2:$ B $ 32,B2,
$ A $ 2:$ A $ 32,> ;& (A2-20),
$ A $ 2:$ A $ 32,< &安培; (A2 + 20)) - 1,1)
I have the following data sheet:
As you can see I have a date and a no which is a string.
Now I want to filter out all dates which have the same number in a +-20 day intervall around the date. So for example:
If the 12.2.2014
has the value a
then it gets a 1
and if the 3.2.2014
has the value a
it gets also a 1
. In contrast if the value 15.1.2014
has the value a
it gets a 0
because it is not in the +-20 day range
. If there exists two rows like in the example below with 10.07.2002
and value d
then it gets a 0 because there is no other day in a 20 day intervall
around it.
My implementation idea is: Calculate for each value and date the difference of the dates and if it is less or equal than 20 and greater and equal to 1 then give out a 1 else a 0.
Can this be done in excel?
I really appreciate any suggestions
PS.: I hope that I showed the problem clearly!
UPDATE
Using this formula I get:
=COUNTIFS($B$2:$B$32;B2;$A$2:$A$32;">"&(A2-20);$A$2:$A$32;"<"&(A2+20))-1
see col E
.
Something like this (adjust the $B$2:$B$32
etc to your actual data range):
=COUNTIFS($B$2:$B$32,B2,
$A$2:$A$32,">" & (A2-20),
$A$2:$A$32,"<" & (A2+20))-1
-1
is to avoid counting the row the formula is on.
To give only 0 or 1:
=MIN(COUNTIFS($B$2:$B$32,B2,
$A$2:$A$32,">" & (A2-20),
$A$2:$A$32,"<" & (A2+20))-1,1)
这篇关于检查日期范围和否的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!