☰

在顶部添加行时如何保留公式中的单元格编号

tag-icon • tag-icon microsoft-excel microsoft-excel-2013
在顶部添加行时如何保留公式中的单元格编号

我在单元格中有以下公式M8：

{=SUM((MOD(ROW(M$16:$M977)-ROW($M$16),4)=0)*(M$16:$M977))}

使用此公式，从 M16 开始，每四行获取 M 列中每周的总小时数，例如 m16+m20+m24+m28 等。我不断在顶部添加新数据，这样当我打开电子表格时，我将在顶部获得最新的信息。当我为新数据添加四行新行时，公式如下所示：

{=SUM((MOD(ROW(M$20:$M977)-ROW($M$20),4)=0)*(M$20:$M977))}

我如何让它总是从 M16 开始添加，因为那是我新一周的新的总小时数。

答案1

将 M16 的值放在文件底部或您不会移动的单元格中！事实上，每次添加一行时，对单元格的每个引用都会自动被修改！

答案2

首先，您应该将其更改为SUMPRODUCT公式。它不需要您将其作为数组公式输入，而且 - 恕我直言 - 它更容易使用。

=SUMPRODUCT(M16:M977*(MOD(ROW(M16:M977)-ROW(M16),4)=0))

为了解决插入行时 Excel 的行为问题，我建议将数据转换为表格。（插入功能区 > 表格）这样可以非常轻松地引用“此区域中的所有数据”，并且每当表格变大或变小时，它都会按您想要的方式更新。在这种情况下，公式看起来会像这样：

=SUMPRODUCT(Table1[Hours]*(MOD(ROW(Table1[Hours])-ROW(Table1[[#Headers],[Hours]])-1,4)=0))

如果由于某种原因您无法将其转换为表格，我建议您创建一个命名范围并引用它。命名范围的公式如下：

=OFFSET(Sheet3!$M$1,15,0,MATCH(9E+99,Sheet3!$M:$M)-15)

...引用它的公式是：

=SUMPRODUCT(rngHours*(MOD(ROW(rngHours)-ROW(OFFSET(rngHours,0,0,1,1)),4)=0))

...rngHours您为命名范围指定的名称在哪里。

如果你真的不想使用命名范围，你可以将整个内容压缩成一个大公式，但它会变得混乱，尽管功能：

=SUMPRODUCT(OFFSET(Sheet3!$M$1,15,0,MATCH(9E+99,Sheet3!$M:$M)-15)*(MOD(ROW(OFFSET(Sheet3!$M$1,15,0,MATCH(9E+99,Sheet3!$M:$M)-15))-ROW(OFFSET(Sheet3!$M$1,15,0)),4)=0))

相关内容