Excel从已计算行号的单元格中选择一个值 [英] Excel select a value from a cell having row number calculated

问题描述

在A列中，我计算出的数字如下:

In column A i have numbers calculated like so:

[A]
[1]
[2]
[3]
[4]
[1]
[2]
[3]
...

在K列中，我有值:

[row] [K]
[ 1 ] [7]
[ 2 ] [3]
[ 3 ] [6]
[ 4 ] [9]

鉴于行号是存储在A列中的数字，我如何从K列中提取值?
我尝试像这样使用Address()和Cell():

How could I pull values from column K given that row number is the number stored in column A?
I have tried using Address() and Cell() like so:

=Cell("contents",ADDRESS(A5,11))

ADDRESS应该返回对给定单元格提供的行号和列号的引用(可以从其他单元格获取)，CELL应该获得引用并返回有关所引用单元格的详细信息(此处为内容"，因此取值).如果我这样使用它:

ADDRESS supposed to return a reference to given cell provided row and column numbers (that can be taken from other cells), CELL supposed to take reference and return detail about the cell referenced (here "content" and thus value). If I use it like so:

=Cell("contents", K4)

当我使用ADDRESS时，它会给我'9':

it gives me '9', when I use ADDRESS:

=ADDRESS(A4,11)

它给了我$ K $ 4.但是，当我一起使用它们时，我会得到一个错误.

it gives me $K$4. But when I use them together I get an error.

推荐答案

您可以使用INDIRECT函数.这需要一个字符串并将其转换为范围

You could use the INDIRECT function. This takes a string and converts it into a range

更多信息此处

=INDIRECT("K"&A2)

---

但是最好使用INDEX，因为它的挥发性较小.

---

But it's preferable to use INDEX as it is less volatile.

=INDEX(K:K,A2)

这将返回表或范围内的值或对值的引用

This returns a value or the reference to a value from within a table or range

更多信息此处

将任一函数放入单元格B2中并填充.

Put either function into cell B2 and fill down.

这篇关于Excel从已计算行号的单元格中选择一个值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！