我有很多如下数据，其中每一行都是一个日志条目：
A B C D E
| Date | Truck Number | Arrival time | Departure time | More columns here...
|------------+--------------+--------------+----------------|
1 | 2019-04-08 | 233 | 12:32 | 12:45 |
2 | 2019-04-08 | 245 | 12:56 | 13:03 |
3 | 2019-04-08 | 233 | 13:24 | 13:32 | <-- Searching relative to this one
4 | 2019-04-08 | 4221 | 13:40 | 13:48 |
5 | 2019-04-08 | 245 | 13:51 | 13:57 |
6 | 2019-04-08 | 233 | 14:08 | 14:23 |
我需要在每一行中包含一个计算，该计算基于该行中的卡车下次到达的时间以及上次到达的时间。我使用辅助列来存储与当前行的偏移量。
我的所有示例都与 13:24（第 3 行）到达的卡车 233 有关。它的下一次到达时间在三行之后，因此辅助列应包含3。我可以使用以下公式来实现这一点：
=IFERROR(MATCH([@[Truck Number]],$B4:$B$6,0),"")
但是，我还需要获取同一辆卡车的上一次访问的行。因此，在此示例中，我需要找到第 1 行或行偏移量 2，并将该数字放入辅助列中。
我如何向后搜索？我发现很难理解为什么简单的向后搜索竟然如此难以逆转。
到目前为止我尝试过的
根据我在网上找到的几件事，我尝试了这个：
=SMALL(IF($C$3:$C10=[@[Truck Number]],ROW($C$3:$C10),""),1)
但它返回第一的数组的元素，并且如果不知道数组的大小，似乎没有任何方法可以获取最后一个元素。
答案1
该AGGREGATE函数以及明智地创建DIV/0错误（AGGREGATE可以忽略）是你的朋友
我假设您真正想要的是上一个/下一个到达时间，而不是从当前行到该时间的偏移量。如果不是这种情况，并且您确实想要偏移量或行号，则公式变化很小，但在这种情况下，确切地知道您想要做什么会有所帮助。
上次到达：
=IFERROR(AGGREGATE(14,6,1/(([@[Truck Number]]=[Truck Number])*([@[Arrival time]]>[Arrival time]))*[Arrival time],1),"")
下一個到貨：
=IFERROR(AGGREGATE(15,6,1/(([@[Truck Number]]=[Truck Number])*([@[Arrival time]]<[Arrival time]))*[Arrival time],1),"")
使用公式评估工具来帮助理解其工作原理。
我们创建数组，并且与前一次到达时间唯一匹配的将是非错误值。这些值中最大的将是前一次。
下一个到达时间的逻辑类似，但下一个到达时间将是最小（非错误）值。
答案2
接下来看一下 AGGREGATE 函数。它将执行与上面的 ARRAY 公式相同的操作，但不是数组。AGGREGATE 采用以下一般格式
AGGREGATE(Formula Number, Option Number, range/array, parameter)
公式 14 和 15 执行 LARGE 和 SMALL 函数并在 AGGREGATE 函数中进行数组计算。因此，请勿在 AGGREGATE 函数中使用完整的列/行引用，否则可能会导致计算量过大。这反过来会使您的系统陷入困境。因此，到目前为止的公式将如下所示：
AGGREGATE(15,
您可以在输入时自己读出选项编号，我选择 6 以使其忽略对流程很重要的错误。因此公式变为：
AGGREGATE(15,6,
因此，下一部分是构建您想要的列表。首先，您将对找到您指定卡车所在的行号感兴趣。我们将通过将数组计算的行号除以 TRUE 或 FALSE 来实现此目的。这里要注意的重要一点是，当通过数学运算发送时，excel 会将 TRUE 转换为 1，将 FALSE 转换为 0。因此，数组计算将如下所示：
ROW(B1:B6)/(B1:B6=233)
or if your truck number is stored as text
ROW(B1:B6)/(B1:B6="233")
因此，每当所查看的行与 233 不匹配时，除数就会变为 FALSE。然后将行号除以 FALSE，将 FALSE 转换为 0。这意味着您会得到除以 0 的错误。因此，如果我们将其放入到目前为止的公式中，6 选项将忽略所有这些错误，只留下匹配的行号列表。所以到目前为止的公式现在看起来像：
AGGREGATE(15,6,ROW(B1:B6)/(B1:B6=233),
所以现在你只剩下参数选项了。通常我使用此过程来抓取列表中的第一个项目，因此我将其设置为 1。如果我使用它来生成列表，我会将其设置为ROW(A1)并向下复制公式，以便获得列表 1、2、3 等。就你的情况而言，你需要了解一些事情。首先，你的卡车是第几次出现。它是列表中的第一个吗？列表中的最后一个？列表中唯一的一个？COUNTIF一旦你找出你在可接受列表中的位置，你就可以使用函数获取列表中的哪辆卡车，你可以从中减去 1 以获得前一辆卡车行，你可以将 1 添加到它以获得下一辆卡车行。COUNTIF 公式将如下所示：
COUNTIF(The range to count in, what it is that is being counted)
因此，您的案例的公式将是：
COUNTIF($B$1:B1,B1)
随着该公式被拉低，请注意范围的末尾将增加，但开头将保持不变。现在可以将其放入 AGGREGATE 公式中。要获取上一辆卡车，您需要：
AGGREGATE(15,6,ROW($B$1:$B$6)/($B$1:$B$6=B1),COUNTIF($B$1:$B1,$B1)-1)
对于下一辆卡车，您将需要：
AGGREGATE(15,6,ROW($B$1:$B$6)/($B$1:$B$6=B1),COUNTIF($B$1:$B1,$B1)+1)
是的，太好了，我得到了行，但你要求行差。不过，多亏了更新，你真的想要时间！
我只需将其嵌入 INDEX 函数中，然后根据行号提取我正在寻找的信息。INDEX 的工作方式有几种。如果它是列或行中的一行，则是 1D，如果它覆盖多行和多列，则是 2D。对于 1D，您只需要说明要查看的列表。在 2D 中，您需要告诉它要查看哪一行和哪一列。所以有两个有趣的小点。如果您将 0 输入行或列引用，那么它会查看整个行或列，而不是特定的行或列。另一个小点信息是 INDEX 实际上返回的是引用范围/单元格，而不是直接返回单元格的实际内容。它将任意返回 F4，然后 F4 将提取单元格 F4 的内容。它允许您执行诸如INDEX(...):INDEX(...)索引可用于定义另一个公式的范围的开始和结束之类的操作。因此 INDEX 采用以下形式：
1D
INDEX(1D range to look in, how far into the list to look)
2D
INDEX(2D range to look in, what row to look at, what column to look at)
=INDEX($C$1:$C$6,AGGREGATE(15,6,ROW($B$1:$B$6)/($B$1:$B$6=B1),COUNTIF($B$1:$B1,$B1)-1))
下次到达时间是：
=INDEX($C$1:$C$6,AGGREGATE(15,6,ROW($B$1:$B$6)/($B$1:$B$6=B1),COUNTIF($B$1:$B1,$B1)+1))
如果是第一辆卡车/唯一一辆卡车，您可以使用 IFERROR 函数返回“”或“第一辆卡车”，如下所示：
=IFERROR(INDEX($C$1:$C$6,AGGREGATE(15,6,ROW($B$1:$B$6)/($B$1:$B$6=B1),COUNTIF($B$1:$B1,$B1)-1)),"First Truck")
如果是最后一辆卡车，您可以做同样的事情。或者，您可以像这样添加一些文字来说明“最后一辆卡车”：
=IFERROR(INDEX($C$1:$C$6,AGGREGATE(15,6,ROW($B$1:$B$6)/($B$1:$B$6=B1),COUNTIF($B$1:$B1,$B1)+1)),"Last Truck")
下面的示例包括标题行，因此参考范围与上面的文本相差 1 行。请调整参考以满足您的需求。
感谢 Ron Rosenfeld 的回答，它进行了很好的时间比较而不是计数，我注意到这种计数方法只有当卡车按时间顺序排序时才有效。