google-sheets - 基于最新匹配条件的查找值

Question

下面是我拥有的一个表的示例，我想要做的是根据最后一次出现(不包括今天的日期)获取特定条件的值列中的值。

所以在下面的示例中，我想找到最后一次出现“A”的值，即 12。

我认为这可以使用索引匹配来完成，但我无法理解它。

例如

Todays Date: 15/12/2013---------------------------------||Date        | Criteria | Value|--------------------------------||12/11/2013  | A        | 3      ||16/11/2013  | B        | 6      ||27/11/2013  | C        | 7      ||3/12/2013   | A        | 12     ||5/12/2013   | B        | 8      ||15/12/2013  | A        |        |----------------------------------

编辑:我还想补充一点，这个公式将在与上表不同的工作表中。公式中的工作表引用也需要是动态的，它会从另一个单元格中提取工作表名称。

Answer 1

我会使用这个公式:

=index(C:C,max(arrayformula(匹配(过滤器(A:A,B:B="A",C:C<>""),A:A,0))),1)

此公式假定您的数据位于 A、B、C 列中，并且对于“条件”列中的每个“A”值，日期都是不同的。 (如果不是这样，那么这个公式就行不通了，见下文。

让我们从外面看里面的公式:

1. filter(A:A,B:B="A",C:C<>"") - 这将导致条件列中有“A”的日期，并且“值”列不为空。
2. arrayformula(match(filter(A:A,B:B="A",C:C<>""),A:A,0)) - 在这一步我们基本上找到这些日期所在的行号。匹配函数将搜索日期(在步骤 1 中计算)。需要数组公式，因为会有更多结果。
3. ma​​x(arrayformula(match(filter(A:A,B:B="A",C:C<>""),A:A,0))) - 这将查找最大行数(Criteria 列中包含“A”的最大行数)
4. index(C:C,max(arrayformula(match(filter(A:A,B:B="A",C:C<>""),A:A,0))), 1) - 最后，我们使用 INDEX 函数导航到具有最大行号的值。

现在，如果你想让这个公式在另一张纸上起作用，你应该写，而不是例如:

=index(C:C,... => =index(Data!C:C,...

假设您的数据在您的数据工作表中。

如果你想让这张表是动态的，那就有点棘手了。假设您从 G1 单元格中获取工作表名称的值。然后你应该写:

=index(indirect(concatenate(G1,"!C:C")),...

这并不是那么漂亮，因为当它出现在那个长公式中时(如前所述)，你应该在每个场合都这样做。相反，您可以做一些前期工作。

让我们将其写入 H1 单元格: =concatenate(G1,"!C:C") - 如果 G1 单元格中的工作表名称为“Data”，则 H1 单元格应包含:Data!C:C，同样，您可以添加到

H2 单元格:=concatenate(G1,"!A:A"),H3 单元格:=concatenate(G1,"!B:B")

现在你可以写了(我认为这就是你问题的最终答案):

=index(indirect(H1),max(arrayformula(match(filter(indirect(H2),indirect(H3)="A",indirect(H1)<>""),indirect(H2) ,0))),1) - 其中 H1、H2、H3 将引用您的数据表的列。

希望对你有帮助。