如何查找表格中每个ID对应的最大日期并输出动态数组结果

以下是两大主流电子表格工具的开箱即用解法，均支持源数据变动后自动更新结果：

Excel 365 / Excel 2021 及以上版本 #

使用LET+UNIQUE+MAXIFS组合实现自动溢出的动态结果，无需手动下拉填充：

基础用法（适配固定范围） #

假设ID列是A列、日期列是B列，首行是表头，数据范围为A2:B1000，任意空白单元格输入如下公式：

=LET(
id_col, A2:A1000,
date_col, B2:B1000,
// 提取非空的唯一ID列表
unique_id, UNIQUE(FILTER(id_col, id_col<>"")),
// 遍历每个ID计算对应最大日期
max_date, BYROW(unique_id, LAMBDA(x, MAXIFS(date_col, id_col, x))),
// 合并ID和最大日期列输出
HSTACK(unique_id, max_date)
)

进阶用法（适配行数动态增长） #

将源数据插入为结构化表（选中数据区域按Ctrl+T，命名为ID_Date_Table），公式可以适配自动新增的行，不用手动修改数据范围：

=LET(
id_col, ID_Date_Table[ID],
date_col, ID_Date_Table[日期],
unique_id, UNIQUE(id_col),
max_date, BYROW(unique_id, LAMBDA(x, MAXIFS(date_col, id_col, x))),
HSTACK(unique_id, max_date)
)

Google Sheets #

逻辑和Excel类似，公式写法略有区别，同样支持自动溢出：
假设ID列是A列、日期列是B列，首行是表头，任意空白单元格输入：

=LET(
id_col, FILTER(A2:A, A2:A<>""),
date_col, FILTER(B2:B, A2:A<>""),
unique_id, UNIQUE(id_col),
max_date, BYROW(unique_id, LAMBDA(x, MAX(FILTER(date_col, id_col=x)))),
{unique_id, max_date}
)

注意事项 #

• 日期列需设置为标准日期格式，文本格式的日期会导致MAX计算错误
• 如需过滤无效日期、特定条件的ID，可在FILTER函数中增加对应判断参数即可

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