Excel中如何按另一列的分组条件查询某列的下一个日期

适用场景：表格包含ID、日期两列，需为每行匹配同ID下比当前日期大的最小日期（次高日期），最高日期对应行次高日期留空，再计算两日期差值

前提约定 #

默认你的表格结构如下，可根据实际情况调整公式中的单元格范围：

• 表头在第1行，ID列为A列，日期列为B列
• 数据范围为第2行到第100行，你可根据实际数据量修改范围
• 新增「次高日期」为C列，「日期差值」为D列

方案1：Excel 2019/365/2021及以上版本 #

1. 点击C2单元格，输入公式：
   =IFERROR(MINIFS($B$2:$B$100,$A$2:$A$100,A2,$B$2:$B$100,">"&B2),"")
2. 下拉C2单元格填充所有数据行即可得到次高日期
3. 点击D2单元格，输入日期差计算公式：
   =IF(C2<>"",DATEDIF(B2,C2,"D"),"")
4. 下拉D2单元格填充所有行，公式中"D"代表按天计算差值，如需按月/年计算可替换为"M"/"Y"

方案2：Excel 2016及更早版本 #

旧版Excel不支持MINIFS函数，使用数组公式实现：

1. 点击C2单元格，输入公式：
   =IFERROR(INDEX($B$2:$B$100,MATCH(MIN(IF(($A$2:$A$100=A2)*($B$2:$B$100>B2),$B$2:$B$100,99999)),$B$2:$B$100,0)),"")
2. 按下Ctrl+Shift+Enter三键组合触发数组计算，单元格公式两侧会自动出现大括号代表数组公式生效
3. 下拉C2单元格填充所有数据行
4. 日期差值计算方式和方案1完全一致

注意事项 #

• 公式中的$A$2:$B$100范围要和你的实际数据匹配，$固定引用符号不要删除，避免下拉填充时数据范围偏移
• 日期列单元格格式必须设置为标准日期格式，否则会出现计算错误
• 如同ID下存在多个相同日期，公式会自动匹配下一个更大的唯一日期

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