主页 / computer / 问题 / 1721166
Accepted
AlainD
Asked: 2022-05-16 12:17:16 +0800 CST

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

  • 772

假设我有一个 Google 表格或 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 表格和 Microsoft Excel 的通用解决方案。如果解决方案对每个解决方案都是独一无二的，请注意这一点。谢谢！

microsoft-excel spreadsheet

5 个回答

  1. Best Answer

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

    代替：

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

    利用：

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

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

    • 1

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

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

    偏移量参数：

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

    这适用于 Excel 和表格。

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

    • 1

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

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

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

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

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

    • 1

  4. 试试这个公式：

    =SUM(INDIRECT("A5"):INDIRECT("A100"))

    在此处输入图像描述

    • 0

  5. 从 A4 而不是 A5 开始公式，您将松动数据从底部掉落的特征，因为插入将在插入行后更新底行“A4：A101”。

    标签一文不值，所以如果您在 A4 中有标签，它不会破坏总和。

    • -1

相关问题