Excel中最长的连续日期条纹 [英] Longest consecutive date streak in Excel

问题描述

假设我有电子表格:

+-------+----------+
| Date  | Duration |
+-------+----------+
| 06/30 | 12:00    |
| 07/01 | 44:30    |
| 07/03 | 12:00    |
| 07/04 | 18:00    |
| 07/05 | 10:00    |
| 07/07 | 08:00    |
| 07/09 | 99:00    |
| 07/10 | 99:00    |
+-------+----------+

如何通过Excel查找最长连续日期的长度? (在此示例中，应为3:07/03、07/04、07/05).

How could I find the length of the longest consecutive streak of dates via Excel? (in this example, it would be 3: 07/03, 07/04, 07/05).

我想使用数组公式来执行此操作，但是我不知道如何使用数组公式来计算列中的连续差.我希望避免使用帮助器列，但这不是破坏交易的事.

I'd like to do this with an array formula, but I don't know how to compute consecutive differences in a column with an array formula. I would prefer to avoid a helper column, but it's not a deal-breaker.

推荐答案

使用一个帮助器列将非常简单.

Using one helper column this will be pretty simple.

公式:

C3向下:

=IF(A3-A2=1,C2+1,0)

E2:

=MAX(C:C)+1

---

找到了一个无需助手列即可使用的数组公式:

---

Found an array formula which works without helper column:

{=MAX(FREQUENCY(IF($A$3:$A$10001-$A$2:$A$10000=1,ROW($A$2:$A$10000)),IF($A$3:$A$10001-$A$2:$A$10000<>1,ROW($A$2:$A$10000))))+1}

将对列A中从A2到A10000的日期起作用. 但是我很确定带有helper列的解决方案在性能和可读性方面会更好.

Will work for dates in column A from A2 up to A10000. But I'm pretty sure that the solution with helper column is better in terms of performance and readability.

这篇关于Excel中最长的连续日期条纹的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！