我有一个 80 多行的 Excel 电子表格，用于计算每月的工作时间。每当我删除一行时，由于不再在这里工作，删除的行下面的所有公式都会被破坏，因为它们只会更改公式中的行号。例如，简单的公式
=SUM(HC22:HN22)
将更改为
=SUM(HC19:HN19).
仅当我删除该行时才会发生这种情况，否则公式仍然良好。
有任何想法吗？
答案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（参见命名范围也可以是相对的尽管整篇文章出色地解释了您可能需要了解的有关命名单元格和范围的所有内容）。
答案2
迟做总比不做好；我遇到过同样的问题并且是这样解决的：
我在 Excel 上创建了一个新工作表并将其用作公式的来源。它是原始工作表的副本。
之后，创建一个 vba 宏，清空此源表上的所有单元格，然后从原始表粘贴更新的值。
如果我从原始表中删除一行，它不会影响公式，因为它们使用的是“源”表。
当然，每次想要使更改生效时，您都必须运行“空白和复制/更新”宏。这会将所有值从原始工作表复制到源工作表中，并且您的公式将从那里获取这些值，而不会修改其范围。
希望它对未来的某人有所帮助。
答案3
我只是用间接函数替换了引用。=SUM(A22:A44)您可以执行 ，而不是=SUM(indirect("A22:A44"))。删除行时这不会改变。
答案4
使公式不相关的正确方法是$在要固定的尺寸前面添加一个符号。
在创建公式时，您可以按 F4，它会自动添加$符号；如果您想稍后添加，只需将其输入公式中，例如：=SUM($HC$22:$HN$22)。如果您只希望行保持不变，请使用=SUM(HC$22:HN$22)- 如果您只希望列固定，请使用=SUM($HC22:$HN22)，等等。