删除行时公式会发生变化

Question 1

我怀疑存在以下问题。假设第 22 行中的公式包含对第 21 行（或包含第 21 行的范围）单元格的引用。如果删除第 21 行，这将导致 #REF! 错误（或将包含的范围缩小 1 行）。我猜你想要的是让公式更新为始终引用其上方的行（而不是硬编码为行号）或让范围高度保持不变（即始终对上方五行求和）。

为此，您可以使用 OFFSET 函数。假设您在 A22 中输入：

=OFFSET(A22,-1,0)

这将查找 A22 上方一行，并返回单元格 A21 中的值。但是，如果您删除第 21 行，旧的第 22 行将成为新的第 21 行，并自动更新其自己的公式，=OFFSET(A21,-1,0)现在将返回 A20 中的值。

OFFSET 还可以返回一个范围（即，其上方的五个单元格，在一列中），如下所示：

=SUM(OFFSET(A22,-5,0,5,1))

警告： OFFSET 是一个易失性函数，会显著降低大型工作簿的速度。在这种情况下，您可以使用“相对命名范围”，例如名为“CellAbove”。在此处了解如何定义这样的名称：https://exceljet.net/named-ranges（参见命名范围也可以是相对的尽管整篇文章出色地解释了您可能需要了解的有关命名单元格和范围的所有内容）。

Answer

我怀疑存在以下问题。假设第 22 行中的公式包含对第 21 行（或包含第 21 行的范围）单元格的引用。如果删除第 21 行，这将导致 #REF! 错误（或将包含的范围缩小 1 行）。我猜你想要的是让公式更新为始终引用其上方的行（而不是硬编码为行号）或让范围高度保持不变（即始终对上方五行求和）。

为此，您可以使用 OFFSET 函数。假设您在 A22 中输入：

=OFFSET(A22,-1,0)

这将查找 A22 上方一行，并返回单元格 A21 中的值。但是，如果您删除第 21 行，旧的第 22 行将成为新的第 21 行，并自动更新其自己的公式，=OFFSET(A21,-1,0)现在将返回 A20 中的值。

OFFSET 还可以返回一个范围（即，其上方的五个单元格，在一列中），如下所示：

=SUM(OFFSET(A22,-5,0,5,1))

警告： OFFSET 是一个易失性函数，会显著降低大型工作簿的速度。在这种情况下，您可以使用“相对命名范围”，例如名为“CellAbove”。在此处了解如何定义这样的名称：https://exceljet.net/named-ranges（参见命名范围也可以是相对的尽管整篇文章出色地解释了您可能需要了解的有关命名单元格和范围的所有内容）。

Question 2

迟做总比不做好；我遇到过同样的问题并且是这样解决的：

我在 Excel 上创建了一个新工作表并将其用作公式的来源。它是原始工作表的副本。

之后，创建一个 vba 宏，清空此源表上的所有单元格，然后从原始表粘贴更新的值。

如果我从原始表中删除一行，它不会影响公式，因为它们使用的是“源”表。

当然，每次想要使更改生效时，您都必须运行“空白和复制/更新”宏。这会将所有值从原始工作表复制到源工作表中，并且您的公式将从那里获取这些值，而不会修改其范围。

希望它对未来的某人有所帮助。

Answer