logo标签列表

如何在Excel公式中使用命名列

excelrangeformulanamed
8
我知道如何在Excel中创建命名区域。
我有一个电子表格，它包含各种参数的不同列，并且最后一个单元格中有一个公式。这在每一行中都会重复出现，每一行都有不同的数据集，并且公式被更新以引用正确的行索引。
但是，公式看起来像（三行的价值）：
=G2*(10*D2 + 20*E2 + 5*F2)
=G3*(10*D3 + 20*E3 + 5*F3)
=G4*(10*D4 + 20*E4 + 5*F4)

我想使用命名区域，但我找不到像这样做的方法：
=Count * (10*var1 + 20*var2 + 5*var3)

其中count、var1、var2和var3会自动更新为当前行的特定列。我可以为每个单元格创建一个命名范围，但这并没有什么帮助。我可以给列命名范围，但是我找不到在公式中添加偏移量的方法。

此外，整个目的是为了可读性，所以如果最终变成一些复杂的公式函数调用，那可能并没有太大帮助。

9个回答
7

简单易懂，至少对于使用Excel 2010的用户而言：

  1. 给你的列命名：选择整列，输入名称
  2. 在公式中使用列名称；Excel将把引用的列与当前行组合起来访问单个单元格。

以Alex P的示例为例：

  1. 通过点击包含“D”的列标题选择列D，将名称“input1”输入名称字段，然后按下Enter。
  2. 对于列E到F，分别使用“input2”和“input3”进行重复操作。
  3. 不要定义其他名称来定义名称“input1”[...]，如上例所示！
  4. 使用上面示例中所给出的公式

注意：

使用这种方式命名列时，您无法访问除公式所在行之外的任何其他行！

至少我不知道有可能表达类似于<ColName>(row+1)...

5
我建议创建一个表格。选择您的范围A1:H4，然后转到表格工具部件>新建>带标题插入表格（在Mac上）。这将把A2:H4标记为表格主体，将A1:H4标记为表头。
从中，您可以获得：
  • 您放入表头列的任何内容都将自动定义此列的名称，例如Count、Radius、Density、Height
  • 然后，您可以使用=[@Count]*(10*[@Radius]+20*[@Density]+5*[@Height])编写公式
  • 当您更改单元格H2中的公式时，Excel会自动将此公式“复制”到列H中的所有单元格。因此，公式中不会再有意外的不一致。
  • 当您需要添加另一行时，只需单击最后一个单元格（在我们的示例中为H4）并按Tab键。Excel会添加另一行，并确保将您的公式“复制”到新行中。
  • 如果您需要合计行，请使用表格小部件中的合计行复选框添加它。Excel会自动添加一行合计。如果单击合计行中的任何单元格，则可以使用“▼▲”按钮更改“总计公式”，例如计算列的平均值而不是总和。
  • 如果您有一个很长的表格并向下滚动，以便标头不再可见，Excel会自动显示列标题而不是列名称（例如Count而不是G）。
我真的推荐视频You Suck at Excel with Joel Spolsky，其中说明了所有这些内容。
5
我还没有完全审查之前的回答，但我认为这更接近@Jason Coyne原帖所寻找的内容。因此，我希望能得到很多赞。;-)
如果您在Excel中"格式化为表格"，则可以让您的公式引用表格和列名。这里有一篇题为使用结构化引用与Excel表格的文章详细介绍了这一点。
顺便说一句，看起来自Excel 2007以来就已经提供了这个功能。
这是一个示例的屏幕截图：

enter image description here

你应该能够在E2中看到公式=[@Count] * (10*[@Var1] + 20*[@Var2] + 5*[@Var3])，这与@jason-coyne想要输入的内容非常接近。
我不喜欢强制选择样式（如果您找不到喜欢的样式，则需要定义新样式）。好消息是，您可以任意重新格式化单元格而不破坏“表格性”。
它坚持打开自动筛选。但是，关闭自动筛选很容易（请参见“表格工具设计”菜单下的“筛选按钮”复选框）。
它还坚持在标题行中有非空唯一值（这有点说得通）。如果您删除标题单元格或插入列，则Excel会为您发明一个新的唯一名称并将其填充。天哪！
如果您不想让某列具有标题，可以输入撇号（'）后跟一个或多个空格。请记住，标题值必须是唯一的，因此如果要有多列没有标题，请继续添加空格。

如果您想下载屏幕截图中的示例工作簿，这里是链接：https://filebin.ca/3vfaSDn4NLEA/SampleWorkbook.xlsx

避免包含外部链接，只需在此处直接包含数据集。 - Adriano Martins
@adriano-martins，我不知道如何直接将数据集添加到答案中。您能否添加一条评论或链接来解释如何操作？顺便说一下，我真的需要包含一个XLSX文件，因为实现OP所要求的关键是格式化表格。由于我还不确定直接附加的“数据集”是什么意思，所以我猜它不包括单元格的格式。期待学习如何直接将数据集添加到答案中。 - Mark Moore
列名在当前行中正确地使用，但如果我想引用上一行，则它会引用D1、D2而不是列名。有什么办法吗？ - user1396423
4
假设我在第二行到第四行的D列到F列中有以下数字设置：
    D    E    F    G
2   10   15   20
3   1    2    3
4   20   30   40

现在假设我想让D列的值称为input1，E列称为input2，F列称为input3:

在插入 > 名称 > 定义...中：

input1 RefersTo =OFFSET(Sheet1!$D$2,0,0,COUNT(Sheet1!$D:$D),1)
input2 RefersTo =OFFSET(Sheet1!$E$2,0,0,COUNT(Sheet1!$E:$E),1)
input3 RefersTo =OFFSET(Sheet1!$F$2,0,0,COUNT(Sheet1!$F:$F),1)

现在，如果我在G列中按以下公式编写，应该会得到正确的答案：
G2 =(10*input1+20*input2+30*input3) // 1000
G3 =(10*input1+20*input2+30*input3) // 140
G5 =(10*input1+20*input2+30*input3) // 2000
9
三十年的发展过程中，人们会认为微软应该想出一种更直观的方式来解决这个问题。 - Jeff Axelrod
30+4之后，我找不到如何应用这个解决方法...我该如何在Excel 2010中实现它？ - Dror
2

在参考Alex P的答案之后，我有补充：

不建议使用

=OFFSET(Sheet1!$D$2,0,0,COUNT(Sheet1!$D:$D),1)
作为input1的公式，建议使用
=Sheet1!$D$2:INDEX(Sheet1!$D:$D,COUNT(Sheet1"$D:$D))

这样可以得到相同的结果，但是它是非易失性的，即仅在前置单元格更改时重新计算。 在更大的模型中，这要好得多！

1
如果您正在使用VBA，则可以选择整个列并将其命名为，例如MyCol，在名称框（左上输入框）中。然后在您的代码中，您可以使用以下代码引用该列中的单元格MyCol（第12行）：
Cells(12, Range("MyCol").Column)
0

你可以尝试使用row()函数。它会返回当前所在的行数。因此，根据电子表格的布局，你可以像这样使用它：

=offset(NamedColumn1, row()-1)

-1是因为你正在指定从第一行向下移动多少行，而如果你在第一行，你想要的是0。
0

使用 Excel 的 命名引用 功能。

给单元格或单元格区域命名的方法：

  1. 选择该单元格或单元格区域
  2. 在名称框中输入其名称（名称框位于公式小部件左侧，具有单元格名称）

不能使用与单元格名称冲突的名称，例如 k0。

命名单元格可以在公式中使用。例如：

=pi*radius*radius
链接已损坏（“抱歉，您要查找的页面无法找到。”）。 - Peter Mortensen
-1

我想提议一种轻微变化的单元格引用由Dror制作。 这也可以工作：

Range("MyCol").Rows(12)
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接