Excel中基于日期统计ID号最大连续出现天数的问题求助

原公式无效原因 #

你之前使用的=MAX(FREQUENCY(IF(X:X=AD2;ROW(X:X));IF(X:X<>AD2;ROW(X:X);)))无法满足需求，核心问题是直接基于ID列的行号计算时，同一日期内的重复ID会被判定为多行，无法实现“每日仅算一次出现”的逻辑，导致统计的是连续行的数量而非连续日期的数量。

解决方案 #

方案1：Excel 365/2021 动态数组公式（推荐） #

无需辅助列，直接在唯一ID列（如AD2）旁的单元格输入以下公式，下拉即可批量计算：

=LET(
    id_dates, SORT(UNIQUE(FILTER($A$2:$A$18, $B$2:$B$18=AD2))),
    date_count, COUNTA(id_dates),
    IF(date_count=0, 0, IF(date_count=1, 1, MAX(SCAN(1, DROP(id_dates,1)-DROP(id_dates,-1), LAMBDA(acc,diff,IF(diff=1,acc+1,1))))))
)

公式逻辑：

1. FILTER+UNIQUE提取当前ID所有出现过的唯一日期
2. SORT确保日期按时间顺序排列
3. SCAN遍历相邻日期，判断间隔是否为1天，累计连续天数
4. MAX取累计值中的最大值，得到该ID的最大连续出现天数

方案2：旧版Excel 数组公式（无动态数组） #

需要先创建辅助列标记每日首次出现的ID：

1. 在空白列（如C2）输入=IF(COUNTIFS($A$2:A2,A2,$B$2:B2,B2)=1,1,0)，下拉填充，标记每个ID每日第一次出现的行（值为1）
2. 在唯一ID列旁输入数组公式（按Ctrl+Shift+Enter确认生效）：

=MAX(FREQUENCY(IF(($B$2:$B$18=AD2)*($C$2:$C$18=1),ROW($B$2:$B$18)),IF(($B$2:$B$18<>AD2)*($C$2:$C$18=1),ROW($B$2:$B$18))))

公式逻辑：通过辅助列筛选出每个ID的每日唯一行，再用FREQUENCY计算这些行的连续间隔，取最大值得到结果。

方案3：Power Query（适合大数据量） #

如果数据量较大，用Power Query处理更高效：

1. 选中数据区域，点击「数据」→「从表格/区域」导入Power Query编辑器
2. 选择date_of_day和ID number列，点击「转换」→「移除重复项」，得到每个ID的每日唯一记录
3. 点击「添加列」→「索引列」→「从0开始」
4. 点击「转换」→「分组依据」：分组列选ID number，新列名设为「连续天数统计」，操作选「所有行」
5. 添加自定义列，输入以下M代码：

List.Max(List.Accumulate(List.Skip([连续天数统计][date_of_day]), {1}, (state, current) => 
    let 
        prevDate = List.Last([连续天数统计][date_of_day]){List.PositionOf([连续天数统计][date_of_day], current)-1},
        newCount = if Duration.Days(current - prevDate) = 1 then List.Last(state)+1 else 1
    in 
        state & {newCount}))

6. 展开「连续天数统计」列，加载回Excel即可得到每个ID的最大连续天数

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