E.g. I have named A10 to A20 as Age, now how do I get Age[5] which is same as A14.
我把A10命名为A20，现在我怎么才能得到年龄[5]，也就是A14。
I can write "=A14" but I did like to write "=Age$5" or something.
我可以写"=A14"但我确实喜欢写"= 5岁"之类的东西。
4 个解决方案
#1
48
You can use Excel's Index function:
您可以使用Excel的索引函数:
=INDEX(Age, 5)
#2
10
"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:
要使这种选择相对，只需使用索引公式中的行号公式和索引公式中的列号公式中的列号公式。为了更清楚地说明这一点，我们举个例子:
=INDEX(named_range,ROW(A1),COLUMN(A1))
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.
假设指定范围从A1这个公式简单的索引范围引用单元格的行和列数和自引用是相对的改变当你将细胞拖或到一边,这使得它可以创建整个阵列的细胞很容易。
#3
0
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 eg AgeIndex. The 5th element can be then found by using the array formula " = sum( Age * (1 * (AgeIndex = 5) ) ". As it's an array formula you'll need to press shift + ctrl + return to make it work and not just return.
向左侧添加一个列，以便B10到B20是您指定的范围年龄。设A10为A20，使A10 = 1, A11= 2，…A20 = 11，给范围A10到A20一个名称，如AgeIndex。然后可以使用数组公式“= sum(Age * (1 * (AgeIndex = 5))”找到第5个元素。”。因为它是一个数组公式，你需要按shift + ctrl + return来完成它，而不只是返回。
#4
0
There are a couple different ways I would do this:
我有几种不同的方法可以做到:
1) Mimic Excel Tables Using with a Named Range
1)使用指定范围模拟Excel表
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:
在您的示例中，您将范围命名为A10:A20“Age”。根据你想要在那个范围内引用一个单元格，你可以(@Alex P写)使用=索引(年龄，5)，或者如果你想引用与你的公式相同的范围“年龄”中的单元格，只需使用:
=INDEX(Age, ROW()-ROW(Age)+1)
This mimics the relative reference features built into Excel tables but is an alternative if you don't want to use a table.
这模拟了构建在Excel表中的相对引用特性，但如果您不想使用表，这是一种替代方法。
If the named range is an entire column, the formula simplifies as:
如果指定的范围是一整列，则公式化简为:
=INDEX(Age, ROW())
2) Use an Excel Table
2)使用Excel表格
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:
或者，如果您将其设置为Excel表，并输入“Age”作为Age列的标题，那么您在Age列右边的列中的公式可以使用如下公式:
=[@[Age]]
#1
48
You can use Excel's Index function:
您可以使用Excel的索引函数:
=INDEX(Age, 5)
#2
10
"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:
要使这种选择相对，只需使用索引公式中的行号公式和索引公式中的列号公式中的列号公式。为了更清楚地说明这一点，我们举个例子:
=INDEX(named_range,ROW(A1),COLUMN(A1))
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.
假设指定范围从A1这个公式简单的索引范围引用单元格的行和列数和自引用是相对的改变当你将细胞拖或到一边,这使得它可以创建整个阵列的细胞很容易。
#3
0
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 eg AgeIndex. The 5th element can be then found by using the array formula " = sum( Age * (1 * (AgeIndex = 5) ) ". As it's an array formula you'll need to press shift + ctrl + return to make it work and not just return.
向左侧添加一个列，以便B10到B20是您指定的范围年龄。设A10为A20，使A10 = 1, A11= 2，…A20 = 11，给范围A10到A20一个名称，如AgeIndex。然后可以使用数组公式“= sum(Age * (1 * (AgeIndex = 5))”找到第5个元素。”。因为它是一个数组公式，你需要按shift + ctrl + return来完成它，而不只是返回。
#4
0
There are a couple different ways I would do this:
我有几种不同的方法可以做到:
1) Mimic Excel Tables Using with a Named Range
1)使用指定范围模拟Excel表
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:
在您的示例中，您将范围命名为A10:A20“Age”。根据你想要在那个范围内引用一个单元格，你可以(@Alex P写)使用=索引(年龄，5)，或者如果你想引用与你的公式相同的范围“年龄”中的单元格，只需使用:
=INDEX(Age, ROW()-ROW(Age)+1)
This mimics the relative reference features built into Excel tables but is an alternative if you don't want to use a table.
这模拟了构建在Excel表中的相对引用特性，但如果您不想使用表，这是一种替代方法。
If the named range is an entire column, the formula simplifies as:
如果指定的范围是一整列，则公式化简为:
=INDEX(Age, ROW())
2) Use an Excel Table
2)使用Excel表格
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:
或者，如果您将其设置为Excel表，并输入“Age”作为Age列的标题，那么您在Age列右边的列中的公式可以使用如下公式:
=[@[Age]]