将窗口函数添加到电子表格

运行总计、累积余额和最接近的匹配：行之间保持正确的公式。这是我们许多人早期写下的一个公式。将交易放入传统电子表格（如 Excel 或 Google Sheets）中，每行一个，并保持运行平衡：复制公式，它就起作用了。每个余额是其上方的余额加上其所在行的金额。现在，您找到 1 月 11 日的收据，并在第 3 行和第 4 行之间为其插入一行。新行没有公式，因此您可以输入一个公式，或者从上面复制它。但它下面的余额现在显示 =C3+B5 。当行移动时，引用被调整为保持指向同一单元格，并且该单元格不再是上面的行。新事务将被跳过，且不会显示任何类型的错误。从这一行开始，平衡就错了。相反，删除一行，下面的公式就变成#REF！ ，其后的每个余额也是如此。这些都不是错误。对于传统的电子表格，“上一笔交易”意味着“上面的单元格”。只有在没有人重新安排任何事情的情况下，两人才会达成一致。当然有更好的方法吗？如果你知道其中的窍门的话，是有的。 Vertex42 建议 OFFSET() 或相对命名范围以在编辑后仍然存在的方式指向“上方的单元格”。 Exceljet 建议对 Excel 365 中的整个列使用单个 SCAN() 公式。具有数据库基础的类似电子表格的工具怎么样？在 Airtable 和 Baserow 中，公式只能看到它所在的记录，NocoDB 和 Notion 数据库也是如此。人们在论坛上询问如何在 Airtable 或 Baserow 中进行运行总计时，会得到脚本的帮助，或者手动将每条记录链接到前一条记录。因此，这些工具通过根本没有“上面的行”来避免这个问题。但需求是存在的：实时余额、自患者上次就诊以来的天数、每次发货后剩余的库存。 SQL有一个答案：窗口函数。上一行和下一行有 LAG 和 LEAD，或者运行总计有 SUM(amount) OVER (PARTITION BY account ORDER BY date)。它们也可以工作，但它们是为开发人员编写查询的高级工具，而不是为在表中工作的人员提供的。实际更好的方法：PREVIOUS() 运行总计，还是飞行总计？金夏洛克摄。我们所说的“前一行”的真正含义是“按日期列出的上一笔交易”。那么我们就这么说吧。在 Grist 中，运行余额或任何累积总计如下所示： prev_balance = PREVIOUS(rec, order_by="Date").Running_Balance 或 0 prev_balance + $Amount PREVIOUS(rec, order_by="Date") 按“日期”对表进行排序，查找当前记录 (rec)，并返回上一条记录。这是一个完整的记录（不仅仅是一个单元格），因此 .Running_Balance 获取该记录的余额。第一个交易没有前一个交易，所以它的余额是空的，或者 0 来处理这个问题。顺序在公式中，而不是在视图中。在任何地方插入一笔交易，它就会在日期前落实到位。删除一个，它周围的余额就会关闭。按“金额”对表格进行排序或对其进行过滤，每条记录仍按日期保留其余额，因为这就是我们所要求的。现在，一个信用卡账户可能有多个持卡人，每个持卡人可能都想要自己的余额。您可能想将数据拆分到每个人的表中。请不要这样做。保留一张表，并具体说明： prev_balance = PREVIOUS(rec, group_by="Card_Member", order_by="Date").Balance 或 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%，依此类推。费率 = Commission_Rates.lookupRecords(order_b