Excel公式垫阵列 [英] Excel formula to pad array
问题描述
例子：
说我有几个一维数组，如A，B和C：
Examples: Say I have several 1D arrays, such as A, B, and C:
A B C
--- --- ---
2 4 99
3 5 37
4 6 42
7
我想用一个公式来填充这些阵列（其被命名为范围），所以它们具有相同的行数。的（编辑，为客户提供更完整的解释：）的我也想能够通过指定的行数，以每个数组的起始位置移动。下面是一个例子输出，其中我被三个变速一两个，B和C由一个位置：
I want to use a formula to pad each of these arrays (which are named ranges) so they have the same number of rows. (Edited, to provide a more complete explanation:) I would also like to be able to shift the starting position of each array by a specified number of rows. Here is an example output, where I'm shifting A by two, B by three, and C by one position:
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（），等等）。
So the "formula" (really a combination of multiple existing standard formulas) to pad one of the arrays would effectively have four inputs: the original array name/range, the number of values to pad at the beginning of the original array, the number to pad at the end, and the padding value (i.e. 0, NA(), etc.).
初始的方法：
我第一次尝试是三个阵列段（开始填充数组，原数组和结束填充阵列）连接成一个阵列是这样的：
Initial approach: My first attempt was to concatenate the three array sections (beginning padding array, original array, and ending padding array) into one array like this:
= INDIRECT（{＆放大器; IF（_NPadBegin大于0，REPT（_PadVal＆安培;，，_NPadBegin-1）及_PadVal＆放大器;，）及_ArrayName＆放;中频（_NPadEnd大于0，与＆amp; REPT（_PadVal＆放大器;，，_NPadEnd-1）及_PadVal，）及}）
（其中有下划线的变量命名区域四个输入）
(where the variables with underscores are named ranges for the four inputs)
但是，它似乎没有可能建立一个这样的数组。我搜索过其他方法使用公式1D阵列组合，但是来到了空。
However, it doesn't seem possible to build an array like this. I've searched for other methods to combine 1D arrays using a formula, but came up empty.
另一种方法可能是创建最终所需长度的数组，最初与填充值填充它。例如，创建一个八行的0。（我可以做一个动态的公式中此步骤。）然后，你可能会覆盖在原来的数组初始化这个数组中的值，并确保原阵列转移到了A'数组正确的位置。例如，通过与A的值的4'覆盖行2 0值（我无法弄清楚如何做到这一点第二步......）
Another approach might be to create an array of the final desired length and initially populate it with the padding value. For example, create an A' array with eight rows of 0. (I can do this step in a dynamic formula.) Then you could overwrite the values in this initialized array with the original array, making sure to shift the original array to the correct location. For example, overwrite the 0 values in rows 2 through 4 of A' with the values of A. (I can't figure out how to do this second step...)
背景：
我有数据（使用动态命名的范围中产生）的多个一维数组，我想一个折线图上同时标绘。然而，阵列具有不同的长度，所以我需要垫一定数量的NA（每个阵列）在数组的开始/结束值。它们还需要排队的基础上的指数。
Background: I have multiple 1D arrays of data (generated using dynamic named ranges) that I would like to plot together on a single line chart. The arrays have differing lengths however, so I need to pad each array with a certain number of NA() values at the beginning/end of the array. They also need to be "lined up" based on an index.
这可能不会是太困难的VBA来实现这一点，但我preFER（非用户自定义），基于公式的解决方案。
It probably wouldn't be too difficult to achieve this in VBA, but I'd prefer a (non-user-defined) formula-based solution.
推荐答案
如果您有值2,3和4 A2：A4试试这个公式
If you have values 2, 3, and 4 in A2:A4 try this formula
= IF（COUNTIF（A2：A4，ROW（间接（1：与＆amp; D 2）） - 1），ROW（间接（1：与＆amp; D 2）） - 1 ，0）
，其中D 2含有（在实施例8）的值的数量。
where D2 contains the number of values (8 in your example).
这将返回这个数组
{0; 0; 2; 3; 4; 0; 0; 0}
假定号码序列开始于零 - 在式结束时，零是填充值，变更为所需
assumes that number series starts at zero - the zero at the end of the formula is the "padding value", change as required
OK按注释你可以做，这个数组公式
OK as per comments you can do that with this array formula
<$c$c>=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是一个垂直一维数组
assumes that _ArrayName is a vertical 1D array
这篇关于Excel公式垫阵列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
