☰

插入/删除行时阻止 Excel 公式更改

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2016
插入/删除行时阻止 Excel 公式更改

我正在尝试使用 13 张工作表为我的个人预算设置预算工作簿，其中 1 张用于总计，另外 12 张用于每个月。我无论如何也想不通为什么当我在其中一张月度工作表中插入一行时，我的公式会发生变化。以下是我的一个公式示例：

=SUMIF(JUN!$G$2:$G$500,"Utilities", JUN!$D$2:$D$500)

如果我在工作表顶部插入一行，它会将 2 增加到 3，从而打乱计算。有什么方法可以锁定公式，使其不发生任何变化？这真是令人沮丧。

答案1

您需要理解的是， 所指定的绝对引用的绝对性$并不是绝对的；-)

现在绕口令已经解决了，让我来解释一下。

绝对性仅适用于复制粘贴或填充公式。在绝对引用范围的上方插入行或在左侧插入列将“移动”范围的地址，以便数据范围指向保持不变。

此外，在中间范围将扩展以包含新行/列。因此，要将一行数据“添加”到范围（表格），您需要将其插入后第一个数据行。

允许添加数据行的最简单方法多于当前数据范围始终有一个标题行，并将标题行包含在实际范围内。这正是 cybernetic.nomad 在此评论。


但，还有一个问题，那就是在结尾表格。仅在最后一行数据之后的行中输入新数据是行不通的。插入行也不行前倒数第二行。

最简单的解决方案是使用特殊的“最后一行”，将该行包含在数据范围中，并始终通过插入来附加新行前那排特殊的。

我通常会降低行高并用适当的颜色填充单元格：

工作表截图

对于您的示例，完整的“最简单”公式将是：

=SUMIF(JUN!$G$1:$G$501,"Utilities",JUN!$H$1:$H$501)


实现相同目标的另一种方法是使用动态公式，该公式会自动调整表中的数据量。根据具体情况以及允许对表执行的操作，此方法有几种不同的变体。

如果通常情况下（例如您的示例），表格从工作表的顶部开始，有一个行标题，并且数据连续而没有间隙，那么一个简单的动态公式将是：

=SUMIF(INDEX(JUN!$G:$G,2):INDEX(JUN!$G:$G,COUNTA(JUN!$G:$G)),"Utilities",INDEX(JUN!$H:$H,2):INDEX(JUN!$H:$H,COUNTA(JUN!$G:$G)))

INDIRECT()这是比使用更好的解决方案

  1. 它是非易失性的，因此工作表计算速度更快，并且
  2. 如果您在表格左侧插入列，它不会中断。

动态公式技术可以进一步改进，将其用于命名公式。


当然，最好的解决办法是将表格转换为合适的表格并使用结构化引用。

答案2

所以您的意思是，如果您插入新的第 2 行（在当前第 1 行和第 2 行之间），您希望公式查看新的第 2 行？以下是几种变体：

=SUMIF(INDIRECT("JUN!$G$2:$G$500"),"Utilities", INDIRECT("JUN!$D$2:$D$500"))

将始终查看第 2 行到第 500 行，而不考虑插入（或删除）重新编号的行。这意味着，如果插入一行，原始第 500 行将重新编号为 501 行，并将超出范围。如果要查看当前第 2 行到原始第 500 行，请使用

=SUMIF(INDIRECT("JUN!$G$2"):JUN!$G$500,"Utilities", INDIRECT("JUN!$D$2"):JUN!$D$500)

如果不明显，则 INDIRECT()采用字符串（文本）参数并将其解释为地址。它允许您进行不变寻址，因为当其他地址因行/列插入/删除而调整时，字符串（看起来像地址）不会进行调整。

请注意，$地址字符串中的字符是可选的；它们没有任何效果。

答案3

设置偏移量很繁琐，但如果您一次每周或每月收集多年的数据，那么这是值得的。

计算 D 列最后一行数据的地址：
=OFFSET(D$1,1,0)-D当前最后一行+1
天当前最后一行插入时会增加，但 D$1 的偏移量不会增加。

您可以使用此公式计算平均值、范围等：
R2：=OFFSET(D$1,1,0)-D3180+1
计算 N 列中除零之外的所有值的平均值
=AVERAGEIF(OFFSET(N$1,1,0):OFFSET(N$1,R2,0),"<>0")

相关内容