如何引用 Excel 命名范围内的单元格？
例如，我有一个命名范围 A10—A20 作为 Age；如何获得与 A14 相同的 Age[5] ？
我可以写“=A14”，但我确实喜欢写“=Age$5”或类似的内容。
For example, I have a named range A10—A20 as Age; how do I get Age[5] which is same as A14.
I can write "=A14" but I did like to write "=Age$5" or something similar.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
您可以使用 Excel 的
Index函数：You can use Excel's
Indexfunction:“您是否知道是否有一种方法可以通过相对选择来实现此操作，以便可以将公式“向下拖动”/应用于同一列中的多个单元格？”
要使此类选择相对，只需使用 ROW 公式作为 INDEX 公式中的行号，使用 COLUMN 公式作为 INDEX 公式中的列号。
为了使这一点更清楚，这里有一个例子：
假设命名范围从 A1 开始，这个公式只是按引用单元格的行号和列号索引该范围，并且由于该引用是相对的，当您向下或向一侧拖动单元格时，它会发生变化，这使得轻松创建整个单元阵列成为可能。
"Do you know if there's a way to make this work with relative selections, so that the formula can be "dragged down"/applied across several cells in the same column?"
To make such selection relative simply use ROW formula for a row number in INDEX formula and COLUMN formula for column number in INDEX formula.
To make this clearer here is the example:
Assuming the named range starts at A1 this formula simply indexes that range by row and column number of referenced cell and since that reference is relative it changes when you drag the the cell down or to the side, which makes it possible to create whole array of cells easily.
我可以通过几种不同的方法来执行此操作：
1) 使用命名范围模仿 Excel 表格
在您的示例中，您将范围
A10:A20命名为“Age”。根据您想要引用该范围内的单元格的方式，您可以（如 @Alex P 所写）使用=INDEX(Age, 5)或者如果您想引用范围“Age”中的单元格它与您的公式位于同一行，只需使用：这模仿了 Excel 表格中内置的相对参考功能，但如果您不想使用表格，则可以选择它。
如果命名范围是整个列，则公式简化为：
2) 使用 Excel 表格
或者，如果您将其设置为 Excel 表格并键入“年龄”作为“年龄”列的标题，那么 Age 列右侧的列中的公式可以使用如下公式：
There are a couple different ways I would do this:
1) Mimic Excel Tables Using with a Named Range
In your example, you named the range
A10:A20"Age". Depending on how you wanted to reference a cell in that range you could either (as @Alex P wrote) use=INDEX(Age, 5)or if you want to reference a cell in range "Age" that is on the same row as your formula, just use:This mimics the relative reference features built into Excel tables but is an alternative if you don't want to use a table.
If the named range is an entire column, the formula simplifies as:
2) Use an Excel Table
Alternatively if you set this up as an Excel table and type "Age" as the header title of the Age column, then your formula in columns to the right of the Age column can use a formula like this:
我一直愿意在工作表中使用类似的东西，其中所有行都是相同的，并且通常引用同一行中的其他单元格 - 但随着公式变得复杂，对其他列的引用变得难以阅读。
我尝试了其他答案中给出的技巧，例如，A 列名为“Sales”，我可以将其称为
INDEX(Sales;row())但我发现它对于我来说有点太长了口味。然而，在这种特殊情况下，我发现单独使用
Sales也同样有效 - Excel（此处为 2010）只会自动获取相应的行。它似乎也适用于其他范围；例如，假设我在
A2:A11中有值，我将其命名为Sales，我可以在B2 中使用并且它将使用相同的行值，给出十个不同的结果。=Sales*0.21:11我还在此页面上发现了一个不错的技巧：命名范围也可以是相对的。回到原来的问题，如果您的值“Age”位于 A 列中，并假设您在同一行的公式中使用该值，则可以将 Age 定义为 $A2 而不是 $A$2，这样在使用时例如，在 B5 或 C5 中，它实际上指的是 $A5。 （名称管理器始终显示相对于当前所选单元格的引用）
I've been willing to use something like this in a sheet where all lines are identical and usually refer to other cells in the same line - but as the formulas get complex, the references to other columns get hard to read.
I tried the trick given in other answers, with for example column A named as "Sales" I can refers to it as
INDEX(Sales;row())but I found it a bit too long for my tastes.However, in this particular case, I found that using
Salesalone works just as well - Excel (2010 here) just gets the corresponding row automatically.It appears to work with other ranges too; for example let's say I have values in
A2:A11which I nameSales, I can just use=Sales*0.21inB2:11and it will use the same row value, giving out ten different results.I also found a nice trick on this page: named ranges can also be relative. Going back to your original question, if your value "Age" is in column A and assuming you're using that value in formulas in the same line, you can define Age as being $A2 instead of $A$2, so that when used in B5 or C5 for example, it will actually refer to $A5. (The Name Manager always show the reference relative to the cell currently selected)
在左侧添加一列，以便 B10 到 B20 是您的命名范围年龄。
设置 A10 到 A20，使得 A10 = 1，A11= 2，...A20 = 11，并为范围 A10 到 A20 指定一个名称，例如
AgeIndex。然后可以使用数组公式找到第 5 个元素：
由于它是数组公式，因此您需要按 Ctrl + Shift + Return 才能使其工作，而不仅仅是返回。这样做，公式将变成数组公式：
Add a column to the left so that B10 to B20 is your named range Age.
Set A10 to A20 so that A10 = 1, A11= 2,... A20 = 11 and give the range A10 to A20 a name e.g.
AgeIndex.The 5th element can be then found by using an array formula:
As it's an array formula you'll need to press Ctrl + Shift + Return to make it work and not just return. Doing that, the formula will be turned into an array formula:
从范围 EJ_PAYDATES_2021 读取特定日期（索引位于最后一个“1”旁边）
这允许从另一个电子表格文件读取范围 [0] 等的特定元素。目标文件不需要打开。上例中的范围名为 EJ_PAYDATES_2021，该范围内包含每个月的一个元素。
我花了一段时间来解析它，但它有效，并且是上面提出的问题的答案。
To read a particular date from range EJ_PAYDATES_2021 (index is next to the last "1")
This allows reading a particular element of a range [0] etc from another spreadsheet file. Target file need not be open. Range in the above example is named EJ_PAYDATES_2021, with one element for each month contained within that range.
Took me a while to parse this out, but it works, and is the answer to the question asked above.