arrays - Excel 公式填充数组

Question

示例:假设我有几个一维数组，例如 A、B 和 C:

 A    B    C
---  ---  ---
 2    4    99
 3    5    37
 4    6    42
      7

我想使用一个公式来填充每个数组(称为范围)，以便它们具有相同的行数。 (已编辑，以提供更完整的解释:) 我还希望能够将每个数组的起始位置移动指定的行数。下面是一个示例输出，其中我将 A 移动 2 位，B 移动 3 位，C 移动 1 个位置:

 A'   B'   C'
---  ---  ---
 0    0    0
 0    0    99
 2    0    37
 3    0    42
 4    4    0
 0    5    0
 0    6    0
 0    7    0

因此，填充其中一个数组的“公式”(实际上是多个现有标准公式的组合)实际上有四个输入:原始数组名称/范围、要在原始数组开头填充的值的数量、末尾要填充的数字以及填充值(即 0、NA() 等)。

初始方法:我的第一次尝试是将三个数组部分(开始填充数组、原始数组和结束填充数组)连接成一个数组，如下所示:

=INDIRECT("{"& IF(_NPadBegin>0, REPT(_PadVal&",", _NPadBegin-1) & _PadVal&",", "") & _ArrayName & IF(_NPadEnd>0, ", "& REPT(_PadVal&",", _NPadEnd-1) & _PadVal, "") &"}")

(其中带下划线的变量是四个输入的命名范围)

但是，似乎不可能构建这样的数组。我搜索了使用公式组合一维数组的其他方法，但一无所获。

另一种方法可能是创建一个最终所需长度的数组，并首先用填充值填充它。例如，创建一个包含 8 行 0 的 A' 数组。(我可以在动态公式中执行此步骤。)然后您可以使用原始数组覆盖此初始化数组中的值，确保将原始数组移动到正确的位置。例如，用 A 的值覆盖 A' 第 2 行到第 4 行中的 0 值。(我不知道如何执行第二步...)

背景:我有多个一维数据数组(使用动态命名范围生成)，我想将它们一起绘制在单个折线图上。然而，数组的长度不同，因此我需要在数组的开头/结尾处用一定数量的 NA() 值填充每个数组。它们还需要根据索引“排列”。

在 VBA 中实现这一点可能不会太困难，但我更喜欢基于(非用户定义的)公式的解决方案。

Answer 1

如果 A2:A4 中有值 2、3 和 4，请尝试使用此公式

=IF(COUNTIF(A2:A4,ROW(INDIRECT("1:"&D2))-1),ROW(INDIRECT("1:"&D2))-1,0)

其中 D2 包含值的数量(在您的示例中为 8)。

这将返回这个数组

{0;0;2;3;4;0;0;0}

假设数字系列从零开始 - 公式末尾的零是“填充值”，根据需要进行更改

好的，根据评论，您可以使用此数组公式来做到这一点

=IF((ROW(INDIRECT("1:"&NPadBegin+NPadEnd+ROWS(_ArrayName)))>NPadBegin)*(ROW(INDIRECT("1:"&NPadBegin+NPadEnd+ROWS(_ArrayName)))<=ROWS(_ArrayName)+NPadBegin),N(OFFSET(_ArrayName,ROW(INDIRECT("1:"&NPadBegin+NPadEnd+ROWS(_ArrayName)))-1-NPadBegin,0,1)),PadVal)

假设 _ArrayName 是一个垂直一维数组