如何引用 Excel 中命名区域内的单元格？

Question

例如，我有一个名为A10-A20的命名区域，名称为Age，我如何获取与A14相同的Age[5]。

我可以编写"=A14"，但我想编写类似于"=Age$5"的代码或类似的内容。

- Champ

Answer 1

你可以使用Excel中的Index函数：

=INDEX(Age, 5)

- Alex P

---

您知道是否有办法与相对选择一起使用，以便该公式可以在同一列中“向下拖动”/应用于多个单元格吗？ - leifericf

如何使用索引来访问年龄子集，例如5-7岁的年龄段？ - DavveK

我找到了答案：INDEX（NamedRange，5）：INDEX（NamedRange，7）） - DavveK

Answer 2

你知道是否有一种方法可以使此公式与相对选择配合使用，以便可以在同一列中“向下拖动”/应用于多个单元格吗？

为了使这样的选择变得相对，请在INDEX公式中使用ROW公式作为行号，并使用COLUMN公式作为INDEX公式中的列号。下面是一个例子，以使其更清晰：

=INDEX(named_range,ROW(A1),COLUMN(A1))

假设命名范围从A1开始，此公式仅通过引用单元格的行和列号来索引该范围。由于该引用是相对的，因此当您向下或向侧面拖动单元格时，它会发生变化，这使得轻松创建整个单元格数组成为可能。

- Dawid Stróżak

---

如果我需要知道命名范围的起始单元格，这不会破坏命名范围的整个概念吗？ - Dana

这取决于你想做什么，大多数情况下命名范围将从顶部开始。我已经有一段时间没有在Excel中做任何事情了，但我记得过去在创建电子表格模型时，我能够创建动态命名范围，其位置根据筛选器而异，所以我猜你会使用某些动态内容来返回引用，而不是硬编码的A1行或列。由于我现在正在使用Linux，无法测试此功能。 - Dawid Stróżak

@Dana，我不认为是这样的；row(A1)和column(A1)只是产生坐标(1,1)，你用它来索引你命名范围中的第一个单元格。如果命名范围不从A1开始，它也会起同样的作用。 - AnjoMan

@AnjoMan，你说得对，我测试过了！这是启用人们相对于命名范围索引引用的好方法。Dawid Stróżak，大多数情况下，当我使用命名范围时，我将它们放在任何想要访问值或一系列值的地方，不想一直搜索。尤其是在编写VBA时，我经常这样做。我的大多数命名范围都在工作表的任何位置，甚至不接近顶部，更不用说大多数在顶部了。 - Cyberchipz

@Cyberchipz - 与其对这个答案进行大刀阔斧的编辑，不如写下你自己的答案，"在Dawid的回答基础上进一步展开..." - Mogsdad

@Mogsdad 谢谢你的建议，但如果您愿意，我还是可以接受这个答案的。他和其他答案都是正确的。我最喜欢第一个答案，因为它很简单，并且作为 Excel 的经验用户，我能够立即扩展它。实际上，这可能是完全不同的问题，但它作为第一个问题的扩展与之相关，我认为那些寻求更多信息的人可以从中受益。我认为真正的答案应该包含两个部分。;-) 我认为从两个答案中我可以得出一个真正的答案。笑 - Cyberchipz

Answer 3

我会有几种不同的方法来实现这个：

1) 使用命名区间模仿Excel表格

在您的示例中，您将范围 A10:A20 命名为“Age”。根据您希望如何引用该范围中的单元格，您可以使用（如@Alex P所写）=INDEX(Age, 5) 或者如果您想引用与公式在同一行上的“Age”范围中的单元格，只需使用：

=INDEX(Age, ROW()-ROW(Age)+1)

这类似于Excel表中内置的相对参照功能，但如果你不想使用表格，则可以选择这种方式。

如果命名范围是整个列，则公式简化为：

=INDEX(Age, ROW())

2) 使用Excel表格

或者，如果您将其设置为Excel表格，并将“Age”作为Age列的标题，则右侧列中的公式可以使用以下公式：

=[@[Age]]

- ChrisB

---

"..然后在年龄列右侧的列中使用您的公式..."，除非我误解了您的答案，我认为问题是在询问是否有一种方法可以引用命名列中的单元格。 - Gabriel Fair

Answer 4

我一直想在一个所有行都相同且通常引用同一行中其他单元格的表格中使用类似这样的东西，但随着公式变得复杂，对其他列的引用变得难以阅读。

我尝试了其他答案中给出的技巧，例如将A列命名为“销售”，可以将其称为INDEX(Sales;row())，但我觉得有点太长了。

然而，在这种特殊情况下，我发现仅使用Sales也同样有效 - Excel（这里是2010）会自动获取相应的行。

它似乎也适用于其他范围; 例如，假设我在A2：A11中有值，我将其命名为Sales，我可以在B2：11中只使用=Sales*0.21，它将使用相同的行值，输出十个不同的结果。

---

我还在此页面上发现了一个好技巧：命名范围也可以是相对的。回到您最初的问题，如果您的值“年龄”在A列中，并且假设您在同一行的公式中使用该值，则可以将Age定义为$A2而不是$A$2，以便在例如B5或C5中使用时，它实际上将引用$A5。（名称管理器始终显示相对于当前选择的单元格的引用）

- Joubarc

Answer 5

在左侧添加一列，使B10到B20成为您的命名范围“Age”。

将A10到A20设置为A10 = 1，A11 = 2，...，A20 = 11，并给范围A10到A20命名，例如 AgeIndex 。

然后可以使用数组公式找到第5个元素：

=sum( Age * (1 * (AgeIndex = 5) )

作为一个数组公式，您需要按下Ctrl + Shift + Return才能使其生效，而不仅仅是回车。这样做，公式将被转换为一个数组公式：

{=sum( Age * (1 * (AgeIndex = 5) )}

- Drew

Answer 6

从EJ_PAYDATES_2021范围中读取特定日期（索引在最后一个“1”旁边）。

=INDEX(PayDates.xlsx!EJ_PAYDATES_2021,1,1)  // Jan
=INDEX(PayDates.xlsx!EJ_PAYDATES_2021,2,1)  // Feb
=INDEX(PayDates.xlsx!EJ_PAYDATES_2021,3,1)  // Mar

这允许从另一个电子表格文件中读取范围[0]等的特定元素。目标文件无需打开。在上面的示例中，范围命名为EJ_PAYDATES_2021，其中包含该范围内每个月的一个元素。

我花了一些时间来解析它，但它有效，并且是上面提出的问题的答案。

- Steve Walton