提问人：Artem 提问时间：9/22/2023 最后编辑：Artem 更新时间：9/25/2023 访问量：116
INDEX 和 MATCH 在 Google 表格中返回不正确的值
INDEX and MATCH return incorrect values in Google Sheets
问：
有一个表格，其中每一行都是来自 CRM 系统的事件。A 列包含 ID，B 列包含事件的日期和时间。根据 ID，我需要计算下一个事件和上一个事件之间的持续时间。不操作表。
| AMOCRM ID 引线 = 8952 | 变更日期 | 小时差 |
|---|---|---|
| 2105687 | 2020-08-24 14:54:10 | |
| 2104881 | 2020-08-24 14:54:11 | |
| 2105687 | 2020-08-24 14:54:26 | 0:00:00 |
| 2105687 | 2020-08-24 14:54:28 | 0:00:00 |
| 2106409 | 2020-08-24 15:04:18 | |
| 2104881 | 2020-08-24 15:07:29 | 0:13:18 |
| 2117085 | 2020-08-24 17:07:51 | |
| 2117085 | 2020-08-24 17:07:53 | 0:00:00 |
| 2166941 | 2020-08-25 9:04:36 | |
| 2104881 | 2020-08-25 9:08:13 | 18:00:44 |
| 2104881 | 2020-08-25 9:08:14 | 18:00:45 |
| 2105687 | 2020-08-25 9:08:17 | 18:13:49 |
| 2106409 | 2020-08-25 9:09:45 | 18:02:16 |
| 2106409 | 2020-08-25 9:09:48 | 18:02:19 |
| 2117085 | 2020-08-25 9:10:17 | 0:00:00 |
| 2117085 | 2020-08-25 9:10:20 | 16:02:27 |
| 2170075 | 2020-08-25 9:49:11 | |
| 2106409 | 2020-08-25 18:32:54 | 9:23:06 |
| 2515935 | 2020-08-25 18:57:26 | |
| 2515935 | 2020-08-26 10:28:07 | 0:00:00 |
| 2560737 | 2020-08-26 15:27:34 | |
| 2117085 | 2020-08-27 12:25:40 | 51:15:20 |
| 2104881 | 2020-08-27 12:37:04 | 69:29:35 |
| 2117085 | 2020-08-27 15:24:54 | 54:14:34 |
| 2104881 | 2020-08-27 15:35:33 | 72:41:22 |
| 2515935 | 2020-08-31 14:54:42 | 0:00:00 |
| 2105687 | 2020-08-31 17:25:34 | 170:31:06 |
我在下面尝试了这个组合，以及其他一些具有相同逻辑的组合
=IF(COUNTIF(A$2:A2, A2)>1, B2-INDEX(B$2:B2, MATCH(A2, A$2:A2, 1)), "")
但在某些情况下，它无法正常工作。 下面是筛选后的示例：
第 3、7 和 11 行工作。但在 12（应该有 00：00：01）和 24（应该有 51：28：50）中，它不是从前一个事件中减去值，而是从 7 行中减去值。在第 26 行（应该有 02：58：29）中，它从第 3 行中减去。
答：
0赞
rockinfreakshow
9/25/2023
#1
您可以尝试：
=map(A2:A,B2:B,lambda(Σ,Λ,if(or(Σ="",Λ=""),,iferror(let(Γ,counta(A2:Σ)-1, Ξ,xlookup(Σ,offset(Σ,-Γ,,Γ),offset(Λ,-Γ,,Γ),,,-1),
if(len(Ξ),Λ-Ξ,))))))
- 将“
小时差”列的格式设置为“持续时间” - 这用作 ArrayFormula;因此，请确保输出列没有任何现有数据/公式，以便formula_output自由流动而不会引发错误
评论
0赞
Artem
9/25/2023
非常感谢您的回答。我不知道为什么，但它返回空单元格。我检查了一切。
0赞
Artem
9/25/2023
哇！它有效！我刚刚在 28 行上用这样的小样本测试了它。问题是我的数据集超过 40000 行，并且出现错误“尝试计算此公式时已达到计算限制”。
0赞
rockinfreakshow
9/25/2023
然后尝试拖动式公式（而不是数组公式）。这进入Cell_C2（如上面的屏幕截图所示）=iferror(let(Γ,counta(A$2:A2)-1, Ξ,xlookup(A2,offset(A2,-Γ,,Γ),offset(B2,-Γ,,Γ),,,-1), if(len(Ξ),B2-Ξ,)))
0赞
Artem
9/26/2023
非常感谢您的帮助！非常感谢您的帮助。展望未来，我计划进一步了解该过程的运作方式。
0赞
Artem
9/26/2023
让我再问一个问题。我调整了您的公式以返回当前事件和下一个事件之间的差值，如下所示： ，但又出了点问题。=IFERROR(LET(Γ,COUNTA(A$2:A2), Ξ,XLOOKUP(A2,OFFSET(A2,1,,Γ),OFFSET(B2,1,,Γ),,,-1), IF(LEN(Ξ),Ξ-B2,)))
评论
请勿发布代码、数据、错误消息等图像 - 将文本复制或键入到问题中。请阅读 如何问我问一个好问题？.有几种方法可以将数据包含在 Markdown 表中，包括 www.tablesgenerator.com 的免费在线服务