![在顶部添加行时如何保留公式中的单元格编号](https://linux22.com/image/1481805/%E5%9C%A8%E9%A1%B6%E9%83%A8%E6%B7%BB%E5%8A%A0%E8%A1%8C%E6%97%B6%E5%A6%82%E4%BD%95%E4%BF%9D%E7%95%99%E5%85%AC%E5%BC%8F%E4%B8%AD%E7%9A%84%E5%8D%95%E5%85%83%E6%A0%BC%E7%BC%96%E5%8F%B7.png)
我在单元格中有以下公式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))