拖动公式并更改列引用而不是行

Question

我在 Excel 的 E5 行有这个公式：

=SUM(Banco!H$5;Banco!H$6;Banco!H$8;Banco!H$9;Banco!H$10;Banco!H$11)

我希望它可以在我向下拖动时更改列引用而不是行引用（基本上像我在横向拖动一样）...例如：

=SUM(Banco!I$5;Banco!I$6;Banco!I$8;Banco!I$9;Banco!I$10;Banco!I$11)
=SUM(Banco!J$5;Banco!J$6;Banco!J$8;Banco!J$9;Banco!J$10;Banco!J$11)
=SUM(Banco!K$5;Banco!K$6;Banco!K$8;Banco!K$9;Banco!K$10;Banco!K$11)

有线索吗？

非常感谢！

- Marcelo

Answer 1

使用偏移函数。

例如 - 假设你在第1行有一个公式，并且你想要引用 Banco!H5，你可以这样做:

=OFFSET(Banco!$G$5,0,ROW())

现在，当您向下拖动时，它将按您向下移动的行数进行偏移。

因此，您的新公式将如下所示：

=SUM(OFFSET(Banco!$G$5,0,ROW()),OFFSET(Banco!$G$6,0,ROW()),OFFSET(Banco!$G$8,0,ROW()),OFFSET(Banco!$G$9,0,ROW()),OFFSET(Banco!$G$10,0,ROW()),OFFSET(Banco!$G$11,0,ROW()))

再次说明，这假设你将该公式粘贴在第一行（我知道它看起来不太好看，但由于你指定了特定的单元格，因此必须单独指定每个单元格）

希望这样说得清楚

- John Bustos

---

我的公式与我在问题中发布的完全相同，所以是一个SUM函数。我需要做的就是拖动该公式，每一行都必须给我右侧列的值，而不是下面的行（即，我需要得到H$5、I$5、J$5，而不是H$5、H$6、H$7）。这可能吗？ - Marcelo

我给你的公式会完全实现这个功能，所以...从第一行开始尝试吧... - John Bustos

好的，好的。但是如果我在E5单元格上使用它（我真正想要使用它的地方），它就不起作用。为什么呢？ - Marcelo

你需要更新row()部分...只需将每个row()替换为row()-4，因为现在你从第5行开始，而不是第一行... - John Bustos

如果您使用ROW()函数，如果删除行，则可能会出现问题（公式结果将更改）。使用ROWS可能更加健壮，例如，如果您想要E5中的第一个结果，请尝试使用=OFFSET(Banco!$H$5,0,ROWS(E$5:E5)-1).....此外，您可以通过对整个范围求和并减去第7行来简化较长的版本，例如=SUM(OFFSET(Banco!$H$5:$H$11,0,ROWS(E$5:E5)-1))-OFFSET(Banco!$H$7,0,ROWS(E$5:E5)-1)。 - barry houdini

Answer 2

使用OFFSET和COUNTA函数的组合。每次向下移动一行时，OFFSET函数将给出COUNTA列的偏移量。使counta函数计算拖动整个函数到其上方的行数（也就是每次将函数拖动到额外的行时，它将增加1）。

- Alexander Khvatov