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这是五个单元格。是否可以让excel通过将单元格值(例如B1)设置为5来确定这一点。
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
有人可以帮助我吗?
推荐答案
假设您在单元格A1,A2,A3中拥有数字1,2,3,4,5,6 ,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的索引(如果在B1中输入3,则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).
直接将 A3转换为索引。
INDIRECT convert "A3" to a index.
请参见以下链接将单元格中的值用作公式中的单元格引用?
这篇关于Excel根据单元格值定义范围的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!