从特定行开始汇总一列

Question

看起来很简单，但我找不到一种方法在Excel公式中定义一个范围，使其覆盖到列的末尾。

比如说，我可以使用这个方程 SUM(C:C) 来求和列C中的所有数字。然而，由于页面顶部有标题和列头，我想从第6行开始我的范围。我以为SUM(C6:C)可以实现，但在Excel中它并不起作用。

这是必要的，这样我的总和就始终正确，无论将来我在文档中添加多少行数据。

谢谢。

- jmbouffard

---

如果C6上方都是文本，那么文本将不会与该列的其他部分相加，因为值需要是数字。 - user2140261

问题在于我无法保证上面的内容会是什么，因为这个文件可能会被不同的人使用，而且随着时间的推移，我自己也可能会忘记不能在上面放置数字。 - jmbouffard

Answer 1

=Sum(C:C)-Sum(C1:C5)

将所有内容加起来，然后减去不需要的单元格中的值之和，无需使用易挥发函数Offset、Indirect或Array。

如果您不喜欢这种方法，也可以尝试以下方法:

=SUM($C$6:INDEX($C:$C,MATCH(9.99999999999999E+307,$C:$C))

以上公式仅在C6至C:C中找到数字匹配项时进行求和。这也是非挥发性的，但我认为更昂贵和粗糙。无论如何，我添加了它。

如果您想要像CountA函数一样使用文本，在列中使用最后一个文本值，您可以使用以下方法。

=COUNTIF(C6:INDEX($C:$C,MATCH(REPT("Z",255),$C:$C)),"T")

您还可以使用其他组合，例如：

=Sum($C$6:$C$65536) 

或者

=CountIF($C$6:$C$65536,"T") 

以上内容适用于Excel 2003及以下版本。

=Sum($C$6:$C$1048576) 

或者

=CountIF($C$6:$C$1048576,"T")

这两种方法都适用于Excel 2007及以上版本。

以上所有函数都会忽略最后一个值下面的所有空白值。

- user2140261

---

这个方法可以用于求和，但我不确定其他使用范围的函数是否适用。例如，我需要计算包含文本的列的行数，使用=COUNTIF(C:C,"T")。但是考虑到你删除第一行中的COUNTIF(C1:C5,"T")的方法也可以解决问题，但我觉得这不是一个干净的做法。 - jmbouffard

@jmbouffard 这就是为什么我既回答了你的原始问题，又提供了第二个选项。据我所知，这是唯一的非易失性和非数组方式来获取最后一个单元格。 - user2140261

感谢您提出第二个选项，但这只会从第一个单元格到最后一个包含数字的单元格获取范围。因此，在文本数据集中，它与我先前评论中的示例不太有用。 - jmbouffard

但是我同意我的问题不够具体，所以我会等一段时间看看是否有其他提议，并在否则接受你的答案。 - jmbouffard

我已经更新了我的回答，如果您需要更多信息或帮助，请提出一个更具体的新问题，以便明确您真正寻找的内容。 - user2140261

第一个公式=Sum(C:C)-Sum(C1:C5)会创建一个循环引用，无法正常工作。将此公式放在单元格C6中，当您想要计算单元格C6的内容时，它可以正常工作。更改减法部分也是一样的。这个想法不能被使用。 - Chris Rogers

Answer 2

你们似乎都喜欢复杂化。只需点击列（选择整个列），按住CTRL并点击要排除的单元格（在你的情况下为C1到C5）。现在你已经选择了整个C列（右侧到工作表末尾）而不包括起始单元格。现在你需要做的是右键单击并为你的选择定义名称（例如asdf）。在公式中使用SUM（asdf）。现在你完成了，祝你好运。

总是找到最简单的方法；）

- Bogdan Horascu

---

你甚至可以在同一列的顶部插入SUM而不会出现循环错误。 - Bogdan Horascu

如果你想在回答中添加内容，你应该编辑它。在SO上，通过编辑来改进问题和答案是受到鼓励的。 - Mr. T

“新名称”对话框显示命名范围实际上将是=Sheet1!$C$6:$C$1048576。因此，使用了最大行数的常量。 - czerny

那样做不行。那会生成=SUM(C:C,C1:C5)。 - Chris Rogers

Answer 3

这 对我来说似乎是最简单的方法（但不是最健壮的）。只需计算从第6行到Excel指定的允许的最大行数的总和。根据这个 网站，目前的最大值为1048576，所以以下内容适用于您：

=sum(c6:c1048576)

对于更强大的解决方案，请参见其他答案。

- kc2001

---

这是最有效的方法，适用于Excel 2007及更高版本。Excel 2003需要使用=SUM(C6:C655366)，因为可能的最大行数较低。 - Chris Rogers

@ChrisRogers 你可能是指 =SUM(C6:65536)？ - Edvaaart

Answer 4

如果您不介意使用OFFSET()函数，它是一种易变的函数，每当单元格更改时都会重新计算，那么这是一个既动态又可重复使用的好解决方案：

=OFFSET($COL:$COL, ROW(), 1, 1048576 - ROW(), 1)

其中$COL是您要操作的列的字母，ROW()是动态选择与包含此公式的单元格相同的行的行函数。您还可以使用静态数字($ROW)替换ROW()函数。

=OFFSET($COL:$COL, $ROW, 1, 1048576 - $ROW, 1)

你可以通过在公式选项卡的“定义名称”菜单中定义一个名为“maxRows”的命名常量来进一步简化公式。

=OFFSET($COL:$COL, $ROW, 1, maxRows - $ROW, 1)

一个快速的例子：要从C6加到列C的末尾，你可以这样做：

=SUM(OFFSET(C:C, 6, 1, maxRows - 6, 1))

或者 =SUM(OFFSET(C:C, ROW(), 1, maxRows - ROW(),1))

- sqlsword

Answer 5

我用类似这样的方法解决了问题（在Excel 2013中引用第8行到最后一列的C和D列）：

=SUMIFS(INDIRECT(ADDRESS(ROW(D$8), COLUMN())&":"&ADDRESS(ROWS($C:$C), COLUMN())),INDIRECT("C$8:C"&ROWS($C:$C)),$C$2)

- Leonidas