Google 表格公式 - 时间错位
本文介绍一个情景以及解决方案
情景
假设有这么一个情景，
| A | B | C | |
|---|---|---|---|
| 1 | 开始时间 | 结束时间 | 事件 |
| 2 | 07:00 | 08:00 | |
| 3 | 08:00 | 09:00 | |
| 4 | 09:00 | 10:00 | |
| 5 | 10:00 | 11:00 |
我们要记录一下每天的日志，从几点到几点做了什么事。但是我们每次填写记录的时候，总是要先把上一个时间段的结束时间填到新行的开始时间上，再写上当前时间段的结束时间，比较繁琐。其实当前时间段的开始时间与上一时间段的结束时间总是一致的，也就是说，A3 等于 B2，A4 等于 B3，以此类推。
所以每次我们新增时间段的时候，只有 B 列的值是有意义的，A 列的值可以通过 B 列的值计算出来，并不需要我们自己填。
那么怎么计算呢？
思路
我们还是考虑映射关系，对于 A2:A 和 B2:B 两个范围，用索引来表示值之间的映射的话，就是这样：
- 1 -> NA
- 2 -> 1
- 3 -> 2
- 4 -> 3
- 5 -> 4
- ……
解释来说，就是 A2 没有映射，A3（索引 2）映射 B2（索引 1）的值，A4（索引 3）映射 B3（索引 2）的值，以此类推。
此映射及其简单，就是 IF(a=1, "", a-1)。
所以思路就是，先生成一个从 1 开始，长度为 A2:A 范围长度，间隔为 1 的索引序列，对该序列进行映射，然后用映射后的索引定位到 B2:B 范围的值，就完成了。
实现
先将样表修改如下，D2 处的公式为 =ROWS(A2:A)，用于计算 A2:A 的行数，也就是序列的长度，
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 开始时间 | 结束时间 | 事件 | |
| 2 | 07:00 | 6 | ||
| 3 | 08:00 | |||
| 4 | 09:00 | |||
| 5 | 10:00 | |||
| 6 | 11:00 | |||
| 7 |
在 A2 处输入公式 =SEQUENCE(D2, 1, 1, 1)，
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 开始时间 | 结束时间 | 事件 | |
| 2 | 1 | 07:00 | 6 | |
| 3 | 2 | 08:00 | ||
| 4 | 3 | 09:00 | ||
| 5 | 4 | 10:00 | ||
| 6 | 5 | 11:00 | ||
| 7 | 6 |
然后使用映射，将 A2 公式改为 =MAP(SEQUENCE(D2, 1, 1, 1), LAMBDA(a, IF(a=1, "", a-1)))，
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 开始时间 | 结束时间 | 事件 | |
| 2 | 07:00 | 6 | ||
| 3 | 1 | 08:00 | ||
| 4 | 2 | 09:00 | ||
| 5 | 3 | 10:00 | ||
| 6 | 4 | 11:00 | ||
| 7 | 5 |
使用新索引定位 B2:B 的值，将 A2 的公式改为
=MAP(SEQUENCE(D2, 1, 1, 1), LAMBDA(a, IF(a=1, "", INDEX(B2:B, a-1, 1))))，
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 开始时间 | 结束时间 | 事件 | |
| 2 | 07:00 | 6 | ||
| 3 | 07:00 | 08:00 | ||
| 4 | 08:00 | 09:00 | ||
| 5 | 09:00 | 10:00 | ||
| 6 | 10:00 | 11:00 | ||
| 7 | 11:00 |
基本算完成了！但是还有一点小瑕疵就是 A 列最后留了一个小尾巴。其实这个也好解决，我们只需要判断一下 B 列每一行的值是不是空，如果是空，就不进行映射了。
将 A2 的公式改为
=MAP(SEQUENCE(D2,1,1,1), B2:B, LAMBDA(a,b, IF(a=1,"", IF(b="","", INDEX(B2:B,a-1,1)))))，
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 开始时间 | 结束时间 | 事件 | |
| 2 | 07:00 | 6 | ||
| 3 | 07:00 | 08:00 | ||
| 4 | 08:00 | 09:00 | ||
| 5 | 09:00 | 10:00 | ||
| 6 | 10:00 | 11:00 | ||
| 7 |
最后将第 2 行隐藏即可，因为该行只是一个辅助行。
反转时间
在这里其实 D2 处的公式显得有些多余，完全可以将它合并到 A2 的公式中。但是在另一种情况下，预先计算行数就很有必要了。这种情况就是时间段反转。
因为记录日志是一个会频繁增加新行的操作，如果日志多了，我们需要不断地滚动鼠标滑轮才能找到最后一条日志（也就是上一条日志），而最开始的一些日志我们基本也不会去看它们了，它们却一直待在表格的最开头，这很没必要。
所以如果在记录日志时是从下往上增加新行记录的，旧的日志会逐渐往表格后排，而我们每次都在表格开头填加新记录，不需要滚动鼠标滑轮。这很方便，但是自动填写上一时间段的结束时间的公式就会发生变化了。
不过变化也不大，就是把映射公式中的 a-1 改为 a+1，同时 IF 不再判断 a=1 而是判断 a=ROWS(A2:A)。因为这个判断在 IF 中，也就是说每次映射都会判断一遍，也都会计算一遍 ROWS(A2:A) 的值，这样比较浪费资源，因为这个行数总是固定的。于是我们就可以将它预先计算出来，并存在某处，然后在公式里直接引用值就可以了，不需要每次映射都计算一遍。
将 A2 的公式改为
=MAP(SEQUENCE(D2,1,1,1), B2:B, LAMBDA(a,b, IF(a=D2,"",IF(b="","",INDEX(B2:B,a+1,1)))))，
| A | B | C | D | |
|---|---|---|---|---|
| 1 | 开始时间 | 结束时间 | 事件 | |
| 2 | 6 | |||
| 3 | 09:00 | 10:00 | ||
| 4 | 08:00 | 09:00 | ||
| 5 | 07:00 | 08:00 | ||
| 6 | 06:00 | 07:00 | ||
| 7 | 06:00 |
乌拉！