假设我有一个 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 的通用解决方案。如果解决方案各自独有，请注意。谢谢！
答案1
单元格 A1 中的公式的一个选项是：
=SUM(OFFSET(A1,4,0,96))
OFFSET的参数：
- 单元格引用（将其视为锚点）
- 单元格引用下方的行数（上方为负数）
- 单元格引用右侧的列数（左侧为负数）
- 参考高度
- （未使用-参考宽度）
这在 Excel 和 Sheets 中均有效。
缺点是 OFFSET 是不稳定的，这意味着每次电子表格上的任何内容发生变化时都会重新计算。如果你有太多这样的公式，它可能会减慢速度。
答案2
经过一番寻找，我找到了一个适用于 Microsoft Excel 和 Google Sheets 的解决方案。
代替：
=SUM($A$5:$A$100)
使用：
=SUM(INDIRECT("A5:A"&ROW()+105))
假设公式所在的单元格是 A1。希望这对某些人有帮助。
答案3
注意：这在 Google 表格中不起作用。
INDIRECT 和 OFFSET 是易失性函数。这意味着每次打开的工作表中的任何单元格发生变化时，它都会重新计算。
相反，我们可以使用非易失性的 INDEX：
=SUM(INDEX(A:A,5):INDEX(A:A,100))
现在，由于 5 和 100 是“硬编码”且不与单元格绑定，因此如果插入新行，它不会改变。
答案4
从 A4 而不是 A5 开始公式，您将失去数据从底部掉落的功能，因为插入行后将更新底部行“A4：A101”。
标签毫无价值，因此如果您有一个 A4 尺寸的标签，它不会影响总数。