☰

Excel 如何使用行或列索引进行数学运算来创建单元格引用？

tag-icon • tag-icon microsoft-excel worksheet-function
Excel 如何使用行或列索引进行数学运算来创建单元格引用？

我的 Excel 中有以下值：

shift   2                                   
data    131 12  55  23  45  11  23  45  119 77

我想定义一个公式，它引用n数据行左侧的第 - 个单元格，其中n由用户在单元格中给出（shift在我的情况下是）。

例如，如果 shift 为2，则输出行应为：

shift   2                                   
data    131 12  55  23  45  11  23  45  119 77

output  0   0   131 12  55  23  45  11  23  45

如果用户将移位单元格中的数字更改为3，则它会自动更改为：

shift   3                                   
data    131 12  55  23  45  11  23  45  119 77

output  0   0   0   131 12  55  23  45  11  23

是否可以？

我正在尝试用来实现这一点INDIRECT，但似乎我无法对列或行进行数学运算：在我的情况下n不是索引，而是我需要添加到索引单元格的常数。

答案1

尝试在 B4 中输入此输出公式并复制到右侧。

=IF(COLUMN()-1<=$B$1,0,INDEX($B$2:$K$2,COLUMN()-1-$B$1))

Index 以数字作为第二个参数，并将返回范围内该位置的单元格。您可以创建任何公式来返回嵌套在 Index 函数中的数字。在本例中，它是减去移位数字后的列号。

在此处输入图片描述

答案2

我会使用基于 INDEX 的公式，就像 teylyn 建议的那样，但您也可以使用 INDIRECT 轻松完成，就像您所追求的那样。我将向您展示如何出于教育目的执行此操作，但请注意 INDIRECT 有许多缺点，您可以在此处阅读：INDIRECT – Excel 中最邪恶的函数

INDIRECT 可以选择在公式中使用 R1C1 寻址，而无需更改 Excel 的默认设置，这对于处理偏移量非常有用。例如，您可以在此处阅读有关 R1C1 寻址的信息：Excel – R1C1 引用样式与 A1。

简而言之，您可以使用R来识别行信息和C列信息。如果您未指定数字，则它假定为当前行或列。如果您指定一个数字，它指的是该行或列号。如果您将数字放在方括号中，它指的是与当前行或列的偏移量。您可以在 INDIRECT 中使用可选的最后一个参数 FALSE 或 0 来打开 R1C1 格式。

在此处输入图片描述

第一个输出单元格 B3 中的公式为：

=IF(COLUMN()<$B$1+2,0, INDIRECT("R2C[-" & $B$1 & "]",0))

该列的 IF 测试可确保在第一个偏移值之前用零填充任何输出单元格。

INDIRECT 表达式构建了 R1C1 样式的地址来引用第 2 行（数据），并对 Shift 单元格 B1 中的值所在的列使用了负偏移量。

相关内容