谷歌表格数组公式总和列中引用自身的前一个单元格 [英] Google Sheets Array Formula Sum Previous Cell in Column Referencing Itself

问题描述

正如标题所暗示的那样；我，在我的一生中，似乎无法在不出现循环依赖错误的情况下使简单的数组公式起作用.基本公式非常简单.公式被放入正下方的单元格中，在同一列 (A) 中.=总和(A2+1).添加新行时，如果我复制并粘贴公式，则会得到一个自动修改的公式；=Sum(A3+1)，依此类推.我想自动化这个，所以当添加新行时，这个生成的数字会自行填充.由于数组公式的工作方式，这是不可能的还是我遗漏了什么?任何帮助将不胜感激.

Like the title suggests; I, for the life of me, can't seem to get a simple array formula to work without getting a circular dependency error. The basic formula is beyond simple. The formula is put into the cell directly below, in the same column (A). =Sum(A2+1). When a new row is added, if I copy and paste the formula I get an automagically modified formula; =Sum(A3+1), and so on. I want to automate this, so when new rows are added, this generated number is populated by itself. Is this just NOT possible because of the way array formula works or am I missing something? Any help would be greatly appreciated.

谢谢！

这是一个非常简单的示例表:https://docs.google.com/spreadsheets/d/1Dr9dAu2QxoS1eE-MQ20SDGdLLyJWVX4HL5-3Z42w0BA/edit?usp=sharing

Here is a very simple sample sheet: https://docs.google.com/spreadsheets/d/1Dr9dAu2QxoS1eE-MQ20SDGdLLyJWVX4HL5-3Z42w0BA/edit?usp=sharing

推荐答案

我在单元格下方的 A 列中添加了该静态编号:

I added this in column A just below your cell with the static number:

=ARRAYFORMULA(A1+ARRAYFORMULA(ROW(INDIRECT("A1"&":A"&COUNTA(B:B)-1))))

这篇关于谷歌表格数组公式总和列中引用自身的前一个单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！