Excel:返回另一列中每个值的最新日期?

Excel:返回另一列中每个值的最新日期?

在 Excel 电子表格中,我为每个客户设置了一个客户 ID(A 列)和多个购买日期(B 列)。它看起来像这样:

CustomerID   Purchase Date
1               1/1/20
1               1/6/20
1               1/18/20
2               1/15/20
2               1/26/20
3               1/14/20
3               1/7/20
3               1/28/20
4               2/5/20
5               1/16/20
5               1/9/20
...

我想要做的是:找到一个公式放在 C 列中,它会告诉我购买日期是否是最大 / 最新对于每个客户 ID。我希望创建如下所示的内容:

CustomerID   Purchase Date    FORMULA
1               1/1/20           NO
1               1/6/20           NO
1               1/18/20          YES
2               1/15/20          NO
2               1/26/20          YES
3               1/14/20          NO
3               1/7/20           NO
3               1/28/20          YES
4               2/5/20           YES
5               1/16/20          YES
5               1/9/20           NO
...

最终目标是仅保留每个客户的最新记录(然后我可以简单地删除所有带有“否”的行)。有什么想法吗?

答案1

如果你有 Excel/O365 或 Excel 2019,你可以使用MAXIFS

 C2: =IF(B2=MAXIFS($B$2:$B$12,$A$2:$A$12,$A2),"YES","NO")

在此处输入图片描述

但如果你真正想要的是保留每个客户的最新记录,那么您可以尝试使用Power QueryExcel 2010+ 中提供的。

当你原来的桌子改变时,刷新查询返回当前的“最新日期”。

你需要做的就是团体通过对列Customer进行操作。MaxDate

在此处输入图片描述

结果

在此处输入图片描述

以上操作都可以通过 UI 完成,但这里是

M 代码

    Source = Excel.CurrentWorkbook(){[Name="Table7"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CustomerID", Int64.Type}, {"Purchase Date", type date}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"CustomerID"}, {{"Last Date", each List.Max([Purchase Date]), type date}})
in
    #"Grouped Rows"

答案2

可以使用数组(CSE)公式解决此问题:

在此处输入图片描述

  • 单元格中的公式C31

    {=IF(MAX(INDEX((A31=$A$31:$A$41)*$B$31:$B$41,))=B31,"Yes","No")}
    

注意:

  • 完成公式Ctrl+Shift+Enter并填充。

  • 您可以根据需要调整公式中的单元格引用。

答案3

不带 CSE 的选项:

=IF(AGGREGATE(14,6,(A2=$A$2:$A$12)*($B$2:$B$12),1)=B2,"Yes","No")

在此处输入图片描述

答案4

另一个选项比公式简单得多,即使用排序函数两次,然后删除重复项。关键是按日期排序,最新日期优先;然后按客户 ID 排序(任意顺序);然后根据客户 ID 删除重复项。

相关内容