Excel - 满足条件时获取最后一个值

Question

截屏

您好，我正在尝试获取满足以下条件时的最后一个值。

1. 客户 ID = 客户 ID
2. 日期 1 < 日期 2

返回的值是我想要的。

我尝试过这个公式，但它似乎不起作用......

=IF(AND(E2=$A$2:$A$13,B2:B13<F2),$C$2:$C$13)

谁能帮我？

Answer 1

您可以使用以下命令获取每个客户的最新日期：

=MAX(FILTER($B$2:$B$13,($B$2:$B$13<$F2)*($A$2:$A$13=$E2)))

因此，该客户该日期的最新价值如下：

=SUMPRODUCT(INT($A$2:$A$13=$E2),INT($B$2:$B$13=MAX(FILTER($B$2:$B$13,($B$2:$B$13<$F2)*($A$2:$A$13=$E2)))),$C$2:$C$13)

Answer 2

在 中G2，公式抄下来：

=AGGREGATE(14,6,C$2:C$13/(A$2:A$13=E2)/(B$2:B$13<F2),1)

Answer 3

除非使用数组公式，否则您不能在此类函数中使用单元格范围。如果您有带有 MAXIFS() 函数的新版 Excel，以下是另一种解决方案：

=VLOOKUP(MAXIFS($B$2:$B$13;$B$2:$B$13;"<"&F2;$A$2:$A$13;E2);$B$2:$C$13;2;0)

然后将其拖至第 3 行和第 4 行。

解释：

MAXIFS($B$2:$B$13;$B$2:$B$13;"<"&F2;$A$2:$A$13;E2)

=> 这将查找最后一个 date1 < date2 的优质客户 ID。

=VLOOKUP(MAXIFS(...);$B$2:$C$13;2;0)

=> 这将查找与 MAXIFS() 返回值中找到的日期相对应的值。但如果 2 个客户的日期相同，就会出现问题。

如果两个客户的日期相同，那么我们可以使用 SUMIFS() 而不是 VLOOKUP()：

=SUMIFS($C$2:$C$13;$B$2:$B$13;MAXIFS($B$2:$B$13;$B$2:$B$13;"<"&F2;$A$2:$A$13;E2);$A$2:$A$13;E2)

=> 使用 SUMIFS() 函数，我们可以验证获取良好日期和良好客户的值。