如何在Excel中查找各25值区间的最长连续数据计数

我有一组为期一年、每10分钟采集一次的数据集，数值范围在200-1000之间，每条记录都带有对应的日期时间。现在需要把数据按25为间隔划分区间（比如200-225、225-250……975-1000），找出每个区间内数据连续出现的最长计数（比如连续26小时对应156次）。

我已经能用公式=COUNTIFS(Table3[U2 Max 10m],">200",Table3[U2 Max 10m],"<225")统计各区间的数据总数，但没法实现最长连续时长的计数查询。

示例数据 #

预期结果 #

需要填充如下表格，返回每个区间的最长连续数据计数，比如225-250区间最长连续出现6次，就返回该最大值：

方法一：辅助列+数组公式（适合小批量数据） #

1. 标记区间归属：
   在数据表格新增一列（比如C列），命名为「区间标记」，输入公式：

   =FLOOR.MATH(B2,25)&"-"&FLOOR.MATH(B2,25)+25
   

   下拉填充后，会自动把数值映射到对应的区间（比如241对应「225-250」）。

2. 计算连续次数：
   再新增一列（D列），命名为「连续计数」，在D2输入公式：

   =IF(C2=C1,D1+1,1)
   

   下拉填充后，同一区间连续出现时计数累加，切换区间时重置为1。

3. 提取最大连续计数：
   在结果表格的对应单元格，输入数组公式（Excel 365/2021直接回车，旧版本按Ctrl+Shift+Enter确认）：

   =MAX(IF(Table3[区间标记]="225-250",Table3[连续计数],0))
   

   替换公式中的区间文本，即可得到对应区间的最长连续次数。

方法二：Power Query批量处理（适合一年大数据量） #

1. 选中数据表格，点击「数据」选项卡→「从表格/区域」导入Power Query编辑器。
2. 添加自定义列「区间」，公式：

   = Number.Floor([U1 Avg 10m],25) & "-" & Number.Floor([U1 Avg 10m],25)+25
   

3. 添加索引列（「添加列」→「索引列」→「从0开始」）。
4. 高级分组：
   • 点击「转换」→「分组依据」，选择「高级」，分组列选「区间」；
   • 新列名设为「连续段」，操作选「添加自定义」，公式：

     = List.Accumulate(List.Skip([索引]), {{[索引]{0}, 1}}, (state, current) => 
         if current - List.Last(state){0} = 1 then 
             List.RemoveLastN(state,1) & {{current, List.Last(state){1}+1}} 
         else 
             state & {{current, 1}})
     

5. 提取最大值：添加自定义列「最长连续计数」，公式：

   = List.Max(List.Transform([连续段], each _{1}))
   

6. 删除多余列，点击「关闭并上载」，即可得到所有区间的最长连续计数结果。

内容的提问来源于stack exchange，提问作者Jim Smith