Excel 根据单元格值定义范围 [英] Excel Define a range based on a cell value

问题描述

是否可以根据单元格中给定的值定义范围.

Is it possible to define a range based on a value given in a cell.

所以，例如:我的选择是 A1:A5 那是五个单元格.是否可以通过将单元格值(如 B1)设置为 5 来让 excel 确定这一点.

So, for example: My selection is A1:A5 That are five cells. is it possible to let excel determine this by setting a cell value (like B1) to 5.

它的目的是通过一个单元格值的一次更改轻松更改许多范围.因此，如果我将单元格值 (B1) 更改为 6.范围将自动更改为 A1:A6

It for the purpose of easily changes a lot of ranges with one change in a cell value. So if I would change the cell value (B1) to 6. The range would automatically change to A1:A6

更具体地说，我想把它倒过来.

Even more specific, I would like to do it reversed.

最后一个例子:选择应该是 A6:A10(这是五个单元格).在 B1 中，我的值为 5.如果我将 B1 的值更改为 6.范围应更改为 A5:A10

Final example: Selection should be A6:A10 (this are the five cells). In B1 I have the value 5. If I change the value of B1 to 6. The range should change to A5:A10

谁能帮帮我???

推荐答案

假设你有数字 1,2,3,4,5,6，分别在单元格 A1,A2,A3,A4,A5,A6 中.在单元格 A7 中，我们计算 A1:Ax 的总和.x 在单元格 B1 中指定(在这种情况下，x 可以是 1 到 6 之间的任何数字).在单元格 A7 中，您可以编写以下公式:

Say you have number 1,2,3,4,5,6, in cell A1,A2,A3,A4,A5,A6 respectively. in cell A7 we calculate the sum of A1:Ax. x is specified in cell B1 (in this case, x can be any number from 1 to 6). in cell A7, you can write the following formular:

=SUM(A1:INDIRECT(CONCATENATE("A",B1)))

CONCATENATE 将为您提供单元格 Ax 的索引(如果您将 3 放入 B1，CONCATENATE("A",B1)) 将提供 A3).

CONCATENATE will give you the index of the cell Ax(if you put 3 in B1, CONCATENATE("A",B1)) gives A3).

INDIRECT 将A3"转换为索引.

INDIRECT convert "A3" to a index.

查看此链接 使用单元格中的值作为公式中的单元格引用?

这篇关于Excel 根据单元格值定义范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！