谷歌追加US preadsheet阵列 [英] Appending google spreadsheet arrays

问题描述

当我在谷歌小号preadsheets追加数组，所有生成的元素都不会呈现细胞。例如，如果我输入公式：

When I append arrays in google spreadsheets, all of the resulting elements are not rendered in cells. For example, if I enter the formula:

={{1,2,3}, {4,5,6}}

以s preadsheet细胞呈现的值1,4,5,6。为什么任何想法正在发生的事情，或替代方案？我更大的问题是积累从不同的表行到另一个工作表 - 我能做到这一点通过

the values rendered in spreadsheet cells are 1,4,5,6. Any ideas about why this is happening, or alternatives? My broader problem is to accumulate rows from separate sheets into another sheet - I can do that via

={ImportRange(...), ImportRange(...)}

但同样的问题是显而易见的（缺少来自第一阵列的第二个元素及以后）。

but the same problem is apparent (missing the second element and beyond from the first array).

推荐答案

修改（2014年10月2日）

我只是在这发生了，当别人upvoted。下面的信息是在表的最新版本过时的 - 你可以现在（已经能够几个月）串联嵌入式阵列内的阵列。我下面提供的所有示例将工作，包括我说，不应该工作。

I just happened upon this when someone upvoted. The information below is obsolete in the newest version of Sheets - you can now (have been able to for a few months) concatenate arrays inside embedded arrays. All the examples that I provided below will work, including the one I said "shouldn't work".

在谷歌表嵌入式阵列

值的阵列可以通过使用嵌入式阵列的单个函数来填充。嵌入式阵列中的每个元件（这可能是猜想的点，它是或多或少只是我的意见）重新presents将在在片材的连续细胞的填充值。分号是行分隔符;逗号（或在使用逗号的小数点分隔符的语言环境反斜杠）是列分隔符。因此，这将成功创建两排，三列阵列（以下所有例子都假定一个语言环境支持逗号列分隔符）：

An array of values may be populated by a single function using an embedded array. Each element in the embedded array (and this may be point of conjecture; it is more or less just my opinion) represents the value that will be populated in contiguous cells in the sheet. Semi-colons are row delimiters; commas (or backslashes in locales that use a comma for a decimal separator) are column delimiters. So this will successfully create a two-row, three-column array (all of the following examples assume a locale supporting comma column delimiters):

= {1,2,3; 4,5,6}

嵌入式嵌入式阵列内的数组

在嵌入式阵列重新$ P $每个元素psents在s preadsheet一个单元，我认为这是合理的假设，一个人应该能够以填充细胞与其他嵌入式阵列，的只要它不覆盖外嵌入式阵列的其他元素。所以IMO这样的事情应该（见第3点）是成功的：

As each element in an embedded array represents a cell in the spreadsheet, I think it is reasonable to assume that one should be able to populate a cell with another embedded array, as long as it does not overwrite other elements in the outer embedded array. So IMO something like this should (see point 3) be successful:

= {{1; 2; 3}，{4，5，6}}

然而这样的事情不应该工作（再IMO），如在第一嵌入式阵列的第二和第三元件将被覆盖第二埋入阵列

However something like this shouldn't work (again IMO), as the second and third elements of the first embedded array would be "overwriting" the second embedded array:

= {{1,2,3}，{4,5,6}}

有与第一嵌入式阵列嵌入数组中相关联的错误

由于+贾森指出，像 = {{1; 2; 3}，{4，5，6}，{7; 8; 9}} 不起作用，所述第一嵌入式阵列只填充一种元素（但每隔一列正确填充）。它同样有趣的是，一个元件被自动转换为文本串。这是（不幸）一个在谷歌表长期的错误。当您尝试调用一个阵列上的SPLIT（）函数（数组中的每个元素，除了第一个成功拆分）发生同样的事情。

As +Jason pointed out, something like ={{1;2;3},{4;5;6},{7;8;9}} doesn't work in that the first embedded array only populates one element (but every other column is populated correctly). It is also interesting that that one element is auto-converted to a text string. This is (unfortunately) a long standing bug in Google Sheets. The same thing occurs when you attempt to invoke the SPLIT() function on an array (every element in the array is split successfully except for the first one).

我不认为嵌入式阵列内的嵌入式阵列将与您更广泛的问题，帮助反正

嵌入式阵列不能真正用于到另一端附加一个阵列反正（由于覆盖的效果），并有可以直接做到这一点没有原生的功能。您可以通过脚本库（信贷+亚哈）获得VMERGE功能将工作开箱：

Embedded arrays can't really be used to append one array on to end of another anyway (due to the "overwriting" effect), and there is no native function that can do it directly. The VMERGE function which you can obtain via the Script gallery (credit to +ahab) will work out of the box:

= VMERGE（IMPORTRANGE（...）; IMPORTRANGE（...）; ...）

，也可以使用本机的功能做一些字符串操作来实现这一目标。例如，对于一维数组：

or you can use native functions to do some string manipulation to achieve this. For example, for one-dimensional arrays:

<$c$c>=ArrayFormula(TRANSPOSE(SPLIT(CONCATENATE(ImportRange(\"key1\";\"A1:A10\")&CHAR(9);ImportRange(\"key2\";\"A1:A10\")&CHAR(9));CHAR(9))))

但是，以及作为笨重，不是很可读，这种类型的公式可以是非常昂贵的性能，明智的大型数据集（我倾向于建议在preference的VMERGE自定义的功能选项）。

but as well as being clunky and not very readable, this type of formula can be very expensive performance-wise for large data sets (I would tend to recommend the VMERGE custom function option in preference).

这篇关于谷歌追加US preadsheet阵列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！