传统电子表格插入行会导致余额公式出错

我们很多人一开始都会写这样一个公式。把交易记录放进传统电子表格（比如 Excel 或 Google Sheets），每行一笔，然后维护一个滚动余额： 把公式向下复制，它就能正常工作。每个余额都是它上方那一行的余额加上本行的金额。 现在你找到一张 1 月 11 日的收据，于是在第 3 行和第 4 行之间插入一行。新行没有公式，所以你手动输入一个，或者从上面复制一个。但它下方的余额现在变成了 =C3+B5。行发生位移时，引用被自动调整以继续指向同一个单元格，而那个单元格已经不是上一行了。新交易就这样被跳过，而且不会显示任何类型的错误。从那一行开始，余额就错了。 改成删除一行，它下方的公式会变成 #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 字，完整内容请点「打开原文」。）