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 列中，我有值:

In column K I have values:

[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屋！