Google Sheets中按身体部位及日期间隔分组提取年份区间

现有包含身体部位与日期的数据集，需实现以下逻辑：

• 按身体部位拆分数据
• 对每个部位下的日期，按「相邻日期间隔不超过30天」的规则分组
• 每组输出对应年份区间：
  • 若组内仅含单个年份（如2021），输出格式为21/22（当前年份后两位/下一年后两位）
  • 若组内跨两个年份（如2022和2023），输出格式为22/23（两年的后两位拼接）

方案1：每个身体部位一行，汇总所有年份区间 #

适用于需要将同一部位的所有分组区间合并在一行的场景，公式如下：

=BYROW(UNIQUE(A2:A),LAMBDA(part,
  LET(
    dates,SORT(FILTER(B2:B,A2:A=part)),
    groups,SCAN(1,dates,LAMBDA(acc,current,IF(current-INDEX(dates,acc)<=30,acc,acc+1))),
    group_dates,BYROW(UNIQUE(groups),LAMBDA(g,SORT(FILTER(dates,groups=g)))),
    year_ranges,BYROW(group_dates,LAMBDA(gd,
      LET(
        min_year,YEAR(MIN(gd)),
        max_year,YEAR(MAX(gd)),
        IF(min_year=max_year,
          TEXT(min_year,"yy")&"/"&TEXT(min_year+1,"yy"),
          TEXT(min_year,"yy")&"/"&TEXT(max_year,"yy")
        )
      )
    )),
    part&": "&JOIN(", ",year_ranges)
  )
))

公式逻辑 #

1. UNIQUE(A2:A)：提取所有唯一的身体部位
2. BYROW：遍历每个部位，单独处理其对应的日期数据
3. SORT(FILTER(...))：筛选当前部位的所有日期并按时间排序
4. SCAN(...)：生成分组编号——若当前日期与分组起始日期间隔≤30天，归属同一分组；否则创建新分组
5. 对每个分组计算最小/最大年份，按规则格式化输出后，拼接成该部位的所有区间字符串

方案2：每个分组一行，单独输出部位与区间 #

适用于需要将每个分组单独成行展示的场景，公式如下：

=LET(
  data,A2:B,
  parts,INDEX(data,,1),
  dates,INDEX(data,,2),
  sorted_data,SORT(data,1,1,2,1),
  unique_parts,UNIQUE(parts),
  all_groups,REDUCE("",unique_parts,LAMBDA(acc,part,
    LET(
      part_dates,FILTER(INDEX(sorted_data,,2),INDEX(sorted_data,,1)=part),
      group_ids,SCAN(1,part_dates,LAMBDA(a,d,IF(d-INDEX(part_dates,a)<=30,a,a+1))),
      part_groups,HSTACK(REPT(part,ROWS(group_ids)),group_ids,part_dates),
      VSTACK(acc,part_groups)
    )
  )),
  grouped_data,DROP(all_groups,1),
  final,BYROW(UNIQUE(INDEX(grouped_data,,1)&"|"&INDEX(grouped_data,,2)),LAMBDA(key,
    LET(
      part,LEFT(key,FIND("|",key)-1),
      group,VALUE(RIGHT(key,LEN(key)-FIND("|",key))),
      group_dates,FILTER(INDEX(grouped_data,,3),INDEX(grouped_data,,1)=part,INDEX(grouped_data,,2)=group),
      min_year,YEAR(MIN(group_dates)),
      max_year,YEAR(MAX(group_dates)),
      year_range,IF(min_year=max_year,TEXT(min_year,"yy")&"/"&TEXT(min_year+1,"yy"),TEXT(min_year,"yy")&"/"&TEXT(max_year,"yy")),
      HSTACK(part,year_range)
    )
  )),
  final
)

使用说明 #

• 将公式粘贴到空白单元格（如D1），Google Sheets会自动扩展输出结果
• 确保A列为身体部位，B列为有效日期格式的单元格
• 公式会自动处理重复数据、排序日期并完成分组逻辑

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