插入行时如何保持电子表格单元格引用固定？

Question

假设我有一个 Google Sheets 或 Microsoft Excel 电子表格，其中单元格 A1 中的公式为“=SUM($A$5:$A$100)”。假设单元格 A5 到 A10 中有数据，但 A11 到 A100 中（暂时）没有数据。

现在在 A5 上方插入一行。这会将第 5 行的数据向下移动，A5 现在为空白。在 A5 中输入一些内容。现在 A5 到 A11 中有数据。但 A1 中的公式已更新为“=SUM($A$6:$A$101)”，因此不包括新单元格。

使用了“绝对参考”标记$，所以我希望公式是固定的。

有没有办法强制 A1 中的公式保持不变，无论在下面插入或删除行？

理想的解决方案是适用于 Google Sheets 和 Microsoft Excel 的通用解决方案。如果解决方案各自独有，请注意。谢谢！

Answer 1

单元格 A1 中的公式的一个选项是：

=SUM(OFFSET(A1,4,0,96))

OFFSET的参数：

1. 单元格引用（将其视为锚点）
2. 单元格引用下方的行数（上方为负数）
3. 单元格引用右侧的列数（左侧为负数）
4. 参考高度
5. （未使用-参考宽度）

这在 Excel 和 Sheets 中均有效。

缺点是 OFFSET 是不稳定的，这意味着每次电子表格上的任何内容发生变化时都会重新计算。如果你有太多这样的公式，它可能会减慢速度。

Answer 2

经过一番寻找，我找到了一个适用于 Microsoft Excel 和 Google Sheets 的解决方案。

代替：

=SUM($A$5:$A$100)

使用：

=SUM(INDIRECT("A5:A"&ROW()+105))

假设公式所在的单元格是 A1。希望这对某些人有帮助。

Answer 3

注意：这在 Google 表格中不起作用。

INDIRECT 和 OFFSET 是易失性函数。这意味着每次打开的工作表中的任何单元格发生变化时，它都会重新计算。

相反，我们可以使用非易失性的 INDEX：

=SUM(INDEX(A:A,5):INDEX(A:A,100))

现在，由于 5 和 100 是“硬编码”且不与单元格绑定，因此如果插入新行，它不会改变。

Answer 4

从 A4 而不是 A5 开始公式，您将失去数据从底部掉落的功能，因为插入行后将更新底部行“A4：A101”。

标签毫无价值，因此如果您有一个 A4 尺寸的标签，它不会影响总数。