Excel：如何通过列标题在公式中引用整个列？

Question

我想写一个类似于=SUM(tab2!A:A)的公式，但是想要使用列标题A，例如“count”。我该如何修改才能更接近：=SUM(tab2!"count")？

我这样做的原因是因为我从tab2中的另一个来源复制和粘贴了一个电子表格，指向“count”的列可能在不同的列中。当我粘贴新电子表格时，我希望公式通过标题名称自动找到要汇总的列并给出正确的计算结果。

我一定错过了什么，因为这似乎是一个非常基本的问题，但我找不到答案...

感谢您的帮助！

- ru111

Answer 1

我喜欢@Doug提出的命名区域的想法，但如果问题在于您正在转储数据[而且您事先不知道哪一列将在哪里]并且每次都需要重命名范围，则有其他选项 - 我建议使用OFFSET。 OFFSET允许您动态创建一个范围，从特定点开始向下/上/右/左移动多少行和列，取决于您确定的数量。

在您的情况下，您需要将其与搜索列的方法相结合，以查看哪个列显示“Count”。我假设您的列标题始终在第1行中。 如果它们不是[或者如果它们不总是在您事先知道的第2行或任何一行] ... 您可以解决这个问题，但我建议您尝试使数据更加统一，而不是创建不必要的Excel解决方案。

总的来说，您的公式应如下所示：

=SUM(OFFSET(A1,,MATCH("Count",1:1,0)-1,ROWS(A:A),1))

这段代码首先确定第一行中单词“Count”所在的列。然后从该数字中减去1，现在就等于距离A列右侧的列数。它使用offset函数为所有行创建对该列的引用，并将这些行求和。

- Grade 'Eh' Bacon

Answer 2

请查看名称管理器以命名范围 :)

- Doug Coats

---

我从tab2中创建了名称，并成功使用列标题得到了一个公式，但是当我用新的电子表格替换tab2并且列的顺序不同，公式无法找到对应于标题的新列（公式使用旧列，该列现在具有不同的标题）。我错过了什么？ - ru111

虽然现在可能还为时过早，但您能否提供一个示例呢？这可能是因为命名范围与范围静态关联。例如，在原始工作簿中的“this.Range”与（“C：C”）相关联，而在每个工作簿中可能不相同。 - Doug Coats

假设我有两列，"age"和"count"。我选中这两列并点击"Create from Selection"，它允许我计算'=SUM(count)'，对应的是第二行。到目前为止一切顺利。现在，如果我有新的数据，其中列的顺序是错误的（"count"，"age"），并将其粘贴进去，'=SUM(count)'仍然会计算第二列（现在是"age"），尽管列名已经改变。我希望这个公式能够找到与特定名称"count"对应的列，无论列在哪里。 - ru111

现在你很可能需要在vba中完成这个任务。这篇文章可能会有所帮助，然后你可以添加一个工作表函数来将总和放置在所需位置。https://dev59.com/w2kw5IYBdhLWcg3wJXIh - Doug Coats

Answer 3

你没有说明是否考虑宏解决方案。如果是这样，这可能会起作用。 如果你要粘贴的工作表是Sheet2，而你想要结果的工作表是Sheet1，那么如果将此宏放置在Sheet1的Worksheet_Activate事件中，当你在Sheet2中粘贴数据后立即单击Sheet1选项卡，就会给你结果:

Private Sub Worksheet_Activate()
Dim r As Range
Dim rCol As Range
Dim rFound As Range
Dim ws As Worksheet
Dim lTotal As Long

Set ws = Sheet2
Set r = ws.Cells
Set rFound = r.Find("count")
If Not rFound Is Nothing Then
    Set rCol = rFound.EntireColumn
    lTotal = Application.WorksheetFunction.Sum(rCol)
End If

Cells(1, 1) = lTotal

End Sub

它假设在Sheet2中只有一个单元格包含单词“count”。

- graeme_a_henderson