Excel - 求和到列表末尾

Question

我在 Excel 2003 中有一个类似于数据库的表，其中一个标题行包含第 6 行中的自动筛选器，以及从第 7 行到第 160 行的数据。第 1 - 5 行是工作表标题、列组标题和我无法摆脱的指示。

我还在该区域中显示数字列的 SUM(X6:X160) 和 SUBTOTAL(109,X6:X160)。我不想将其显示在表格底部，因为一些用户经常向该表格添加行，并且他们定期破坏公式。

问题:每当用户将数据添加到表末尾时，就需要更新 SUM 和 SUBTOTAL 公式以扩大范围，不用说，大多数人都忘记了这一点。

我自己可以扩展公式以覆盖直到第 500 行的所有行并隐藏剩余的行，因此用户需要在到达表的可见末尾时“插入”行 - 这反过来会更新公式，但我不认为这是一种非常安全的方法......了解我的用户。

问题:有没有办法从 X6“到 X 列的末尾，无论它在哪里”创建 SUM 和 SUBTOTAL？

结论

我使用了 SUM(OFFSET(....))，从标题中的新字段中获取“高度”参数，该字段通过 =COUNTA($A:$A 显示“记录数” )-1 (-1 表示也被计数的列标题文本)，因为从业务上下文中可以清楚地看出，对于任何有效的数据记录，A 列中必须存在键值，并且不允许有空白行(至少我可以对用户进行这么多培训)-加上用户的好处，不仅可以看到 SUM，而且现在还可以看到标题卡住 Pane 中的记录计数。

Answer 1

使用 Offset() 和 Count() 似乎是最流行的，而且我确信是最有效的。

=SUM(OFFSET($X$6,0,0,COUNT($X$6:$X$1000)))

就我个人而言，我倾向于经常使用Indirect()。它可能会运行得更慢，但它可以工作。它帮助我看到正在创建的范围。但要小心，由于部分范围保存在文本中，因此当您移动公式时它不会更新。这可能会让你绊倒。无论如何，你都在这里。

=SUM(INDIRECT("$X$6:$X$" & COUNT($X$6:$X$1000)))

您可以使用 iDevlop 指出的动态范围，或将 Offset() 或 Indirect() 放入 Sum() 中，例如我刚刚做到了。

无论哪种方式，你都要小心，因为Indirect()和Offset()是Volatile Functions 。通常您要避免这种情况。

我还发现了this guy ，他使用 Index() 和 Match()，它们不是 volatile 函数。

编辑:

想想(因为我刚刚在我的电子表格上做了)..

如果您知道您的数据有一个合理的限制，例如 1000，您可以只使用 =SUM($X$6:$X$1000) 它会跳过空格，即使对于小计方法也是如此1 或 101(平均)。