英文:
How to check the range returned by Excel OFFSET function?
问题
如果我在调试期间想要检查返回的范围，例如D6:F10，是否有一种明确显示它的方法？
E_1x和S_1x是上述公式中单个单元格的用户定义名称。
我发现了一些间接的方法来检查它，但是有一种直接显示"D6:F10"（在上面的示例中）的方法会更加实用。
谢谢关注。
英文:
I have the following excel function:
OFFSET(Foglio1!C1;S_1x-1;0;E_1x-S_1x+1;1)
If I want to check the returned range during debugging, say e.g. D6:F10, is there a way to explicitly show it?
E_1x and S_1x are user defined names of single cells in the formula above.
I have discovered some indirect ways to check it, but having a method that directly shows me "D6:F10" (in the example above) would be much more practical.
Thanks for the attention.
答案1
得分: 1
你可以使用以下公式在一个单元格中显示OFFSET()公式的范围：
=LET(
初始单元格,A1,
行偏移,3,列偏移,4,
高度,5,宽度,8,
范围,ADDRESS(ROW(初始单元格)+行偏移,COLUMN(初始单元格)+列偏移)&":"&ADDRESS(ROW(初始单元格)+行偏移+高度,COLUMN(初始单元格)+列偏移+宽度),
范围
)
使用你的起始范围 "Foglio1!C1" 和变量名 "S_1x" 和 "E_1x"，公式变为：
=LET(
初始单元格,Foglio1!C1,
行偏移,S_1x-1,列偏移,0,
高度,E_1x-S_1x+1,宽度,1,
范围,ADDRESS(ROW(初始单元格)+行偏移,COLUMN(初始单元格)+列偏移)&":"&ADDRESS(ROW(初始单元格)+行偏移+高度,COLUMN(初始单元格)+列偏移+宽度),
范围
)
注意1：显然，我们在公式中使用不同的标点符号。确保在分隔时使用分号; 而不是逗号,。
注意2：为了使此公式起作用，初始范围必须是单个单元格，变量/参数不能是错误值。
编辑1：
在不使用LET()函数的情况下，公式可以写成以下形式，使用你的起始范围 "Foglio1!C1" 和变量名 "S_1x" 和 "E_1x"：
=ADDRESS(ROW(Foglio1!C1)+(S_1x-1),COLUMN(Foglio1!C1)+(0))&":"&ADDRESS(ROW(Foglio1!C1)+(S_1x-1)+(E_1x-S_1x+1),COLUMN(Foglio1!C1)+(0)+(1))
这将显示一个带有绝对引用的范围，即用 $ 符号括起范围的字母和数字，例如 $A$2:$M$9。
将上述公式与SUBSTITUTE()函数结合使用，将 $ 符号替换为空字符串值 ("") 以去除它。
英文:
You can use the following formula to show you the range of that OFFSET() formula in a cell:
=LET(
initial,A1,
row_shift,3,col_shift,4,
height,5,width,8,
range,ADDRESS(ROW(initial)+row_shift,COLUMN(initial)+col_shift)&":"&ADDRESS(ROW(initial)+row_shift+height,COLUMN(initial)+col_shift+width),
range
)
using your starting range "Foglio1!C1" and variable names "S_1x" and "E_1x" that becomes:
=LET(
initial,Foglio1!C1,
row_shift,S_1x-1,col_shift,0,
height,E_1x-S_1x+1,width,1,
range,ADDRESS(ROW(initial)+row_shift,COLUMN(initial)+col_shift)&":"&ADDRESS(ROW(initial)+row_shift+height,COLUMN(initial)+col_shift+width),
range
)
Note 1: Apparently we use different punctuation for formulas. Make sure to use semicolon ; instead of comma , for separation.
Note 2: For this to work, the initial range has to be a single cell and the variables/parameters cannot be error values.
> Edit 1:
Without the use of the LET() function the formula can be written as follows, using your starting range "Foglio1!C1" and variable names "S_1x" and "E_1x":
=ADDRESS(ROW(Foglio1!C1)+(S_1x-1),COLUMN(Foglio1!C1)+(0))&":"&ADDRESS(ROW(Foglio1!C1)+(S_1x-1)+(E_1x-S_1x+1),COLUMN(Foglio1!C1)+(0)+(1))
This will show a range with absolute references, i.e. encapsulating the range letters and numbers with $, e.g. $A$2:$M$9.
Combine the above formula with the SUBSTITUTE() function to get rid of the $ symbol by replacing it with an empty string value ("")
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论