如何计算一个特定时间间隔在多个时间间隔之间的次数(参见示例)
如何计算一个特定时间间隔在多个时间间隔之间的次数(参见示例)
提问于 2019-10-15 13:07:50
编辑:忘记添加当前公式(见post底部)
我有多个列将时间存储为间隔值(例如，10 6AM存储为22，6am作为06存储)-请参阅图像
我需要检查某个时间是否在所有不同的时间间隔之间。
如何编写一个Excel公式来检查一个时间是否在多个时间间隔之间(例如，下午10点到凌晨4点之间是凌晨2点30分？)
我的时间间隔存储在列中：
J5&K5 [i.e. J5 is the start (value: 02) and K5 is the end (value: 12)]
L5&M5
N5&O5
P5&Q5
R5&S5 [i.e. R5 is the start (value: 06) and K5 is the end (value: 18)]我想检查的时间是T1:AE5 5
e.g. T1 = 6:30AM
e.g. U1 = 8:30AM
e.g. AD1 = 02:30AM
e.g. AE1 = 04:30AM示例(请参阅图像)：计算AD1之间的次数：
J5&K5
L5&M5
N5&O5
P5&Q5
R5&S5 (i.e. R5 is the start and K5 is the end)在本例中，计数应该是3，并存储在AD5中(它位于J5&K5、L5&M5、N5&O5之间)
需要考虑的事情：
06)
- Sometimes
- 00:00和24:00都存储为24
- ，有时开始时间是晚上(例如22，24)，结束时间是上午(例如02，04，
)--早上(例如02或04)，结束时间是下午(例如14或16)。
AD1中的当前公式(这不能解释2.5实际上在24到06之间)
=SUMPRODUCT(($AD$1>INDEX(J4:S4,1,N(IF(1,{1,3,5,7,9}))))*($AD$1<INDEX(J4:S4,1,N(IF(1,{2,4,6,8,10})))))^注意这是另一个堆栈溢出成员提供的公式，在需求更改之前，我有一个更简单的问题要解决。
回答 2
Stack Overflow用户
回答已采纳
发布于 2019-10-15 14:37:38
打破你的公式：
=SUMPRODUCT(($AD$1>INDEX(J4:S4,1,N(IF(1,{1,3,5,7,9}))))*($AD$1<INDEX(J4:S4,1,N(IF(1,{2,4,6,8,10})))))$AD$1>INDEX(J4:S4,1,N(IF(1,{1,3,5,7,9})))检查AD1是否大于start time$AD$1<INDEX(J4:S4,1,N(IF(1,{2,4,6,8,10})))，检查AD1是否小于结束时间- ，则结果乘以。由于
True的计算值为1，而False为0，因此乘法的结果仅为满足这两种条件的1。
从逻辑上说：
- AD1大于开始时间
。
和
- AD1小于结束时间
所需的附加逻辑是计算以下实例：
- AD1大于开始时间
。
或
- AD1小于结束时间
时时
- 开始时间大于结束时间
实现这一目标的办法是：
=SUMPRODUCT((($AD$1>INDEX(J4:S4,1,N(IF(1,{1,3,5,7,9}))))+($AD$1<INDEX(J4:S4,1,N(IF(1,{2,4,6,8,10})))))*(INDEX(J4:S4,1,N(IF(1,{1,3,5,7,9})))>INDEX(J4:S4,1,N(IF(1,{2,4,6,8,10})))))$AD$1>INDEX(J4:S4,1,N(IF(1,{1,3,5,7,9})))检查AD1是否大于start time$AD$1<INDEX(J4:S4,1,N(IF(1,{2,4,6,8,10})))，检查AD1是否小于结束时间- ，这一次将它们相加在一起，(OR'd)
INDEX(J4:S4,1,N(IF(1,{1,3,5,7,9})))>INDEX(J4:S4,1,N(IF(1,{2,4,6,8,10})))检查开始时间是否大于结束时间- ，第三次检查将与先前添加的结果相乘(和d)。这只会导致只计算开始时间比结束时间(夜间)和( AD1 )在时间段内晚的情况。
因此，要计算所有想要的情况，您应该同时使用原始部分和新部分的和：
=SUMPRODUCT(($AD$1>INDEX(J4:S4,1,N(IF(1,{1,3,5,7,9}))))*($AD$1<INDEX(J4:S4,1,N(IF(1,{2,4,6,8,10})))))+SUMPRODUCT((($AD$1>INDEX(J4:S4,1,N(IF(1,{1,3,5,7,9}))))+($AD$1<INDEX(J4:S4,1,N(IF(1,{2,4,6,8,10})))))*(INDEX(J4:S4,1,N(IF(1,{1,3,5,7,9})))>INDEX(J4:S4,1,N(IF(1,{2,4,6,8,10})))))Stack Overflow用户
发布于 2019-10-15 14:35:50
好吧，这不是世界上最干净的配方，但至少有效。我确信有一种方法可以恢复这个公式，但是我对SUMPRODUCT来说是个菜鸟，我找不到一种方法。
我拿到了这个：
我在单元格A3中使用的公式是：
=SUM(IF(B7<A7;IF(Y(A1+24>=A7;A1+24<=B7+24);1;0);IF(Y(A1>=A7;A1<=B7);1;0));IF(D7<C7;IF(Y(A1+24>=C7;A1+24<=D7+24);1;0);IF(Y(A1>=C7;A1<=D7);1;0));IF(F7<E7;IF(Y(A1+24>=E7;A1+24<=F7+24);1;0);IF(Y(A1>=E7;A1<=F7);1;0));IF(H7<G7;IF(Y(A1+24>=G7;A1+24<=H7+24);1;0);IF(Y(A1>=G7;A1<=H7);1;0));IF(J7<I7;IF(Y(A1+24>=I7;A1+24<=J7+24);1;0);IF(Y(A1>=I7;A1<=J7);1;0)))这样做的目的是检查End是否小于Start。如果是这样的话，因为您的工作时间是24小时，那么24的总和为End，同时也是要检查的值。
之后，它只检查该值是否在Start和End之间。
但是的，很脏。我把公式发出去了，希望有人能继续。
但就像我说的，至少起作用了。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/58395499
复制相关文章
相似问题
腾讯云开发者
