Google Sheets日期季节年份计算公式适配（第二部分）

需求背景 #

现有包含身体部位与日期的数据集，原Google Sheets公式具备以下功能：

• 按身体部位分组，要求组内后续日期间隔不超过30天
• 输出组内覆盖的年份：仅覆盖1年则输出对应年份，跨2年则输出年份区间

原公式如下：

=let(Σ;{1;scan(1;B3:index(B:B;match(;0/(B:B<>"")));lambda(a;c;if(isbetween(days(c;offset(c;-1;));0;30)*(offset(c;;-1)=offset(c;-1;-1));a;a+1)))};
map(Σ;lambda(Γ;let(Λ;text(minifs(B2:index(B:B;match(;0/(B:B<>"")));Σ;Γ);"y");Γ;text(maxifs(B2:index(B:B;match(;0/(B:B<>"")));Σ;Γ);"y");
if(Λ=Γ;Λ;Λ&"/"&Λ+1)))))

需适配公式满足以下两个条件：

1. 身体部位后缀. Conservative不影响分组（例如Mid与Mid. Conservative属于同一组）
2. 数组中的身体部位组可无序排列

适配后的公式 #

=let(
  数据区域;A2:index(B:B;match(;0/(B:B<>"")));
  清理后部位;bycol(index(数据区域;;1);lambda(x;trim(regexreplace(x;"\. Conservative$";""))));
  排序后数据;sort({清理后部位;index(数据区域;;2)};;2;1);
  分组标识;{1;scan(1;index(排序后数据;;2);lambda(a;c;
    let(prev_row;offset(c;-1;0;1;2);
        prev_part;index(prev_row;;1);curr_part;index(offset(c;0;-1);;;1);
        if(isbetween(days(c;index(prev_row;;2));0;30)*(prev_part=curr_part);a;a+1)
    )
  ))};
  map(分组标识;lambda(Γ;
    let(
      组最小年;text(minifs(index(排序后数据;;2);分组标识;Γ);"yyyy");
      组最大年;text(maxifs(index(排序后数据;;2);分组标识;Γ);"yyyy");
      if(组最小年=组最大年;组最小年;组最小年&"/"&组最大年)
    )
  ))
)

关键改动说明 #

• 统一分组依据：用regexreplace(x;"\. Conservative$";"")批量移除所有部位名称末尾的. Conservative后缀，让后缀不同但核心名称一致的部位归为同一组
• 兼容无序数据：先通过sort函数按日期对数据排序，确保分组时能正确判断同部位记录的日期间隔，不受原始数据顺序影响
• 优化分组逻辑：在scan函数中直接引用排序后的部位和日期，避免原公式中offset在无序数据下的判断误差，确保同组条件的准确性

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