Grist 联合创始人 Dmitry Sagalovskiy 发布技术博客,探讨了传统电子表格在处理累积余额等跨行公式时因插入或删除行而导致引用错误的局限性 1。文章指出,在 Excel 等传统软件中,插入新行会导致下方公式引用错位且无报错,而删除行则引发 #REF! 错误 1。为解决这一问题,Grist 通过 PREVIOUS()、NEXT() 和 RANK() 等基于记录顺序而非物理位置的函数,实现了更稳健的“窗口函数”式计算 1。
该机制支持根据指定字段排序获取前一条记录,例如使用 PREVIOUS(rec, order_by="Date") 以避免对物理行位置的依赖 1。此外,Grist 还支持分组计算,示例代码为 prev_balance = PREVIOUS(rec, group_by="Card_Member", order_by="Date").Running_Balance or 0,用于处理特定会员在时间序列上的余额变化 1。针对需要近似匹配的场景(如查找生效日期内的最新费率),用户可利用有序查找功能,通过 lookupRecords() 配合 find.le() 实现类似 SQL 窗口函数的操作 1。性能测试显示,在 20,000 条交易记录的数据集上,Grist 的 PREVIOUS() 仅需数秒完成全列计算,而手动扫描循环耗时超过五分钟 1。
Grist co-founder Dmitry Sagalovskiy published a technical article discussing the limitations of traditional spreadsheets, such as Excel, in handling cross-row formulas for calculations like running balances. 1 In conventional spreadsheet environments, inserting new rows causes downstream formula references to shift silently without error, while deleting rows often triggers #REF! errors. 1 To address these issues, Grist implements "window function" capabilities using tools like PREVIOUS(), NEXT(), and RANK() that rely on record order rather than physical row position. 1
The platform uses functions such as PREVIOUS(rec, order_by="Date") to retrieve the preceding record based on sorting by a specified field, instead of relying on static grid location. 1 This approach supports grouped calculations, exemplified by the formula prev_balance = PREVIOUS(rec, group_by="Card_Member", order_by="Date").Running_Balance or 0. 1 Additionally, Grist facilitates scenarios involving effective dates and rates by combining lookupRecords() with find.le() operators to perform approximate matching, similar to SQL window functions. 1 Performance tests cited in the article indicate that calculating values across a column of 20,000 transaction records using Grist’s PREVIOUS() function takes only seconds, whereas equivalent manual scanning loops require more than five minutes. 1
评论
还没有评论,欢迎留下第一条。