更新时间:2023-01-30 08:40:27
这样的东西(调整 $ 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)