Excel索引匹配查找日期，返回错误值的时间间隔之间的时间

Question

我有一张工作表，其中包含2个单独的数据文件，并加载到2张工作表中。 “EventTime”工作表包含发生特定事件的日期和事件时间。

“间隔”工作表包含日期（独立于“事件时间”中的日期），时间开始（这是单个时间间隔的开始）和时间结束（时间开始+ 30分钟）。我将时间开始和时间汇总到TIME INTERVAL列中。

我的目标是确定“EventTime”中的Event Time值是否落入“EventTime”和“Interval”中相同日期的“Interval”中的任何特定TIME INTERVAL之间。该时间启动值不能在“EVENTTIME”

我已经尝试了一些指数匹配功能小于活动时间：

=INDEX(Interval!$D$2:$D$5322,MATCH(1,IF(EventTime!B2>=LEFT(Interval!$D$2:$D$5322,5)+0,IF(EventTime!B2<RIGHT(Interval!$D$2:$D$5322,5)+0,1),0),0)) 

此事件的时间映射到时间间隔有相当大的侵害;它会查找满足条件的第一个时间间隔，而不考虑日期。所以会发生什么看起来像时间匹配罚款，但如果你看看

16:28的价值下16:14 - 16:44但这违反了时间开始在“间隔”的要求，不能小于Time值。

的正确值应该16:28映射到是16:28 - 16:58

另一种方法我都试过是：

=INDEX(Interval!$D$2:$D$5322,MATCH(EventTime!B2,LEFT(Interval!$D$2:$D$5322,5)+0)) 

这似乎是看事件时间的分钟部分并将其与第一个以分钟为单位的第一时间间隔进行比较，这个时间到底是怎么回事，如何在几分钟内找出分钟已经让我难倒了，但它似乎在以后的时间段内返回正确的值，这很奇怪。

方法3：

=INDEX(Interval!$D$2:$D$5322,MATCH(1,(EventTime!A2=Interval!$A$2:$A$5322)*(EventTime!B2<=LEFT(Interval!$D$2:$D$5322,5)+0)*(EventTime!B2>RIGHT(Interval!$D$2:$D$5322,5)+0))) 

我在这里使用日期作为一项要求，但现在这只是比赛只有1个时间间隔。

方法4：

=INDEX(Interval!$D$2:$D$5322,MATCH(1,IF(A2=Interval!$A$2:$A$5322,IF(EventTime!B2>=LEFT(Interval!$D$2:$D$5322,5)+0,IF(EventTime!B2<RIGHT(Interval!$D$2:$D$5322,5)+0,1),0),0))) 

到第一方法类似我试图但增加了日期的要求，给出相同的1个时间间隔输出。

所有这些公式都是用Control + Shift + Enter输入的。

我一直坚持这几天，它开始让我疯了，我认为我走在正确的轨道上，但我错过了一些真正简单的事情。

该文件在这里

http://www.filedropper.com/example_1

Answer 1

我已成功地解决了这个代码的问题，就基本得到第一个匹配的间隔时间（使用ROW（$ Y $ 1）根据日期和城市特定事件的时间（这里包括城市代码不是一样我只用日期和时间要求）。

=IFERROR(INDEX(Interval!$D$2:$D$5322,SMALL(IF((EventTime!A2=Interval!$A$2:$A$5322)*(EventTime!B2>=LEFT(Interval!$D$2:$D$5322,5)+0)*(EventTime!B2<RIGHT(Interval!$D$2:$D$5322,5)+0),ROW(Interval!$D$2:$D$5322)-MIN(ROW(Interval!$D$2:$D$5322))+1,""),ROW($Y$1)))," ") 

当有2周事件时间非常接近对方，但被映射到分离这适用于我的主要问题除了时间间隔（轶事例子，事件时间14:14和14:43， 14:14将映射到14:14 - 14:44，14:43将映射到14:30 - 15:00）。这很好，因为这仍然符合我们的目的，但我可能会使用一些VBA或R来缩短间隔映射的事件时间。

Answer 2

所以上传，嗯。这很复杂，但我有一个解决方案，你可能会效仿 - 然后用它来进一步解决你的问题。

我已经做了一件事，这可能会使解决方案不适用于您的情况。我已经把日期和时间放在一起到一个数字。根据我的理解，你可以做到这一点，然后创建字符串，但我不知道你在用什么，所以我可能是错的。另外，假设间隔是固定的（在30分钟）

这是我的公式（记住，我已经添加了日期的时间）

=SUMPRODUCT(MAX((Interval!B:B)*(Interval!B:B-EventTime!B3-1/48<0))) 

这将返回“最后“适合事件的间隔的可行日期和时间。不要被“-1/48”丢掉 - 这是在excel时间写半个小时最简单的方法。这实际上是最晚的间隔时间，比事件晚了半小时。

这产生

Target event Interval start 
01.11.2015 23:34 01.11.2015 23:23 
01.11.2015 22:50 01.11.2015 22:41 
01.11.2015 22:48 01.11.2015 22:41 
01.11.2015 22:11 01.11.2015 22:00 
01.11.2015 22:05 01.11.2015 22:00 
01.11.2015 22:07 01.11.2015 22:00 
01.11.2015 21:28 01.11.2015 21:28 
01.11.2015 21:23 01.11.2015 21:13