☰

Excel 自定义表格数组模式和增量

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2010
Excel 自定义表格数组模式和增量

我很难找到一个解决方案，让我可以复制相同的公式，但将表格数组的行数以 15 为增量增加。在我的第一次尝试中，我只是尝试手动创建三个适当的公式，然后全手操作，希望它能够捕捉到模式，但我没有运气。

我正在寻找这样的输出：

=MAX(VLOOKUP($A2,Sheet2!$A2:$B17,2,0),VLOOKUP($A2,Sheet2!$E2:$F17,2,0),VLOOKUP($A2,Sheet2!$I2:$J17,2,0))
=MAX(VLOOKUP($A2,Sheet2!$A17:$B32,2,0),VLOOKUP($A2,Sheet2!$E17:$F32,2,0),VLOOKUP($A2,Sheet2!$I17:$J32,2,0))
=MAX(VLOOKUP($A2,Sheet2!$A32:$B47,2,0),VLOOKUP($A2,Sheet2!$E32:$F47,2,0),VLOOKUP($A2,Sheet2!$I32:$J47,2,0))

我很业余，所以欢迎任何建议。提前谢谢您。

答案1

OFFSET将是大多数人倾向于使用这个选项的函数。然而，它是一个易失性函数。易失性函数并不是世界末日。它只是意味着只要工作表上的单元格发生变化，它们就会重新计算，即使该单元格与易失性公式无关。常规公式只有在影响它们的某些东西发生变化时才会重新计算。因此，使用易失性函数可能会导致大量多余的计算。特别是如果它是复制下来并在许多单元格中重复使用的东西。

使用常规公式（非易失性） OFFSET可以实现相同的结果。通常假设返回给定行（1D 范围）和列（2D 范围）的值。实际返回的是单元格地址，然后提取该地址的值。使用此地址返回，您可以使用一个函数定义范围的起点，然后使用另一个函数定义范围的终点。用 : 分隔这两个函数，现在您有一个范围了！INDEXINDEXINDEXINDEXINDEXINDEX

现在让我们看看确定行模式的数学原理。公式 1 从第 2 行开始，公式 2 从第 17 行开始，公式 3 从第 32 行开始……等等。因此，这里的模式本质上是 (公式 #-1)*15+2。所以现在我们只需要开发一个计数器，当公式向下复制时，计数器加一。您可以用 1、2、3 等填充一列，或者您可以直接使用ROW(A1)。第一次使用它时，它将返回 1。当它向下复制时，它将返回 2，然后是 3，等等。完美的小计数器。（注意：Column(A1)可用于水平计数）。

如前所述，INDEX其形式为：

INDEX(SELECT RANGE, ROW in SELECT RANGE, COLUMN in SELECT RANGE)

几点说明：

  • 行号和列号与所选范围有关，且与工作表不同，除非所选范围对于二维范围从 A1 开始，或者对于一维范围从第 1 行或第 A 列开始。

  • 一维选定区域不需要列号，只需要行号。如果选定区域是水平的，行号实际上就是列号。

  • 如果输入的行号或列号为 0，则 INDEX 将其视为返回所选范围的输入行或列。

让我们回到公式的构建。让我们先找到范围的起点。因此，在这种情况下，我们要告诉索引查找 A2、A17、A32 等

=INDEX($A:$A,(ROW(A1)-1)*15+2)

要找到范围 B17、B32、B47 的终点，公式如下所示：

=INDEX($B:$B,ROW(A1)*15+2)

现在将两者结合起来定义您的范围，公式如下：

=INDEX($A:$A,(ROW(A1)-1)*15+2):INDEX($B:$B,ROW(A1)*15+2)

现在，该公式本身看起来并不多，因为您不能在单个单元格中输入超过一个单元格的值。但是，它可以用于您的查找公式。因此，将范围方程代入原始公式中，您将得到：

=MAX(VLOOKUP($A2,INDEX(SHEET2!A:A,(ROW(A1)-1)*15+2):INDEX(SHEET2!B:B,ROW(A1)*15+2),2,0),VLOOKUP($A2,INDEX(SHEET2!E:E,(ROW(A1)-1)*15+2):INDEX(SHEET2!F:F,ROW(A1)*15+2),2,0),VLOOKUP($A2,INDEX(SHEET2!I:I,(ROW(A1)-1)*15+2):INDEX(SHEET2!J:J,ROW(A1)*15+2),2,0))

相关内容