传统电子表格插入行会算错余额，有人提议加入窗口函数

我们许多人早期都写过这样的公式。把交易记录放进传统电子表格（比如 Excel 或 Google Sheets）里，每行一笔，并维护一个滚动余额： 把公式往下复制，就能用。每个余额等于它上面一行的余额加上本行的金额。

现在你找到一张 1 月 11 日的收据，要在第 3 行和第 4 行之间插入一行。新行没有公式，于是你手动输入一个，或者从上面复制一个。但它下面的余额现在变成了 =C3+B5。行位置移动时，引用被调整成仍然指向同一个单元格，而那个单元格已经不是上一行了。新交易就这样被跳过，且不显示任何类型的错误。从那一行开始，余额就是错的。

反过来，删除一行，它下面的公式会变成 #REF!，之后的所有余额也都会变成 #REF!。

这些都不是 bug。对传统电子表格来说，“上一笔交易”就意味着“上面那个单元格”。只要没人重新排列任何东西，两者才一致。

如果你知道窍门，也有办法。Vertex42 建议用 OFFSET() 或相对命名区域，以能在编辑后仍然存活的方式指向“上面那个单元格”。Exceljet 建议在 Excel 365 中用单个 SCAN() 公式处理整列。

那些有数据库基础的类电子表格工具呢？在 Airtable 和 Baserow 中，公式只能看到它所在的那条记录，NocoDB 和 Notion 数据库也是如此。在它们的论坛上询问如何在 Airtable 或 Baserow 中做滚动总计的人，会被指向脚本，或者被指向手动把每条记录链接到前一条记录。

所以这些工具通过根本没有“上一行”来回避这个问题。但需求是存在的：滚动余额、患者上次就诊以来的天数、每次发货后剩余的库存。

SQL 有一个答案：窗口函数。有用于上一行和下一行的 LAG 和 LEAD，或者用 SUM(amount) OVER (PARTITION BY account ORDER BY date) 做滚动总计。它们也能用，但它们是给编写查询的开发者用的高级工具，不是给在表格里工作的人用的。

我们所说的“上一行”，真正意思是“按日期排序的上一笔交易”。那我们就精确地这样说。在 Grist 中，滚动余额或任何累计总计看起来像这样： prev_balance = PREVIOUS(rec, order_by="Date").Running_Balance or 0 prev_balance + $Amount PREVIOUS(rec, order_by="Date") 按‘Date’对表排序，找到当前记录（rec），并返回前一条记录。它是一整条记录（不只是某个单元格），所以 .Running_Balance 取到该记录的余额。第一笔交易没有前一条，因此它的余额为空，or 0 处理了这种情况。

排序在公式里，不在视图里。在任何位置插入一笔交易，它都会按日期落到正确位置。删除一笔，余额会在它周围合拢。按‘Amount’排序或筛选表格，每条记录仍然保留其按日期计算的余额，因为那正是我们所要求的。

现在，一个信用卡账户可能有多个持卡人，每个人可能想要自己的余额。你可能会想把数据按人拆成每张表。请不要那样做。保留一张表，并明确指定： prev_balance = PREVIOUS(rec, group_by="Card_Member", order_by="Date").Balance or 0 prev_balance + $Amount PREVIOUS() 现在会在同一持卡人的交易中按日期找到前一笔交易。

而且由于顺序存在于公式中（order_by），没有什么能阻止你并排拥有多个滚动总计：一个针对整个账户，一个针对每个持卡人，一个每月重新开始。每一列保持自己的计数。添加一个链接到持卡人并按日期排序的视图，你就可以选择一位持卡人并查看他们的交易，其余额逐行累加。

PREVIOUS() 有两个兄弟。NEXT() 看向另一边，RANK() 给出每条记录在顺序中的位置，所以 RANK(rec, order_by="-Amount") <= 10 标记出十笔最大的交易。帮助文章更详细地介绍了这三者。

一旦查找知道了顺序，“前一个”就只是你可以向它提出的一个问题。

假设人们的时薪随时间变化，而你保留了历史：Bertha 从 2019 年 8 月 30 日起每小时赚 110 美元，从 2020 年 3 月 24 日起每小时赚 125 美元。2020 年 2 月的一条工资记录需要该日期生效的时薪，也就是开始日期在该日期当天或之前的最新时薪： rates = Rates.lookupRecords(Person=$Person, order_by="Rate_Start") rates.find.le($Date).Hourly_Rate lookupRecords() 获取 Bertha 的时薪记录，按开始日期排序。find.le() 找到小于或等于（也就是 le）工资日期的最后一条。

这一点与日期无关。佣金层级也是同样的道理：销售额达到 5,000 美元赚 7%，达到 10,000 美元赚 10%，以此类推。 rates = Commission_Rates.lookupRecords(order_by="Sales_Target") rates.find.le($Sales_Amount).Commission_Rate Excel 和 Google Sheets 用户把这称为近似匹配：VLOOKUP() 的最后一个参数为 TRUE。微软自己的文档说，第一列随后需要排序；如果没有排序，“返回值可能不是你预期的结果。”没有错误，只是不同的答案。在 Grist 中，顺序是公式的一部分：它自己排序层级，无论你选择如何在屏幕上呈现它们。

同样的两行代码回答了一整类日常的最近匹配（有时称为“近似匹配”）问题： 而对于最简单的问题（一组中的第一条或最后一条记录），你甚至不需要 find()。下面是一位客户最近的订单： Orders.lookupOne(Customer=$id, order_by="-Date") 负号表示降序，所以第一个匹配就是最新的。

Grist 公式是 Python，所以你一直可以手动写所有这些：获取记录，循环遍历它们，选出正确的那条。我们应该检查一下那要付出什么代价。

通过扫描完成的滚动总计，SUM(r.Amount for r in Transactions.all if r.Date <= $Date)，会为每条记录查看整张表。有 1,000 笔交易时，那是一百万步。有 20,000 笔时，那是四亿步。

（原文较长，此处译出前 6000 字，完整内容请点「打开原文」。）