ARRAYFORMULA() 不适用于 SPLIT() [英] ARRAYFORMULA() does not work with SPLIT()

问题描述

当我使用 =arrayformula(split(input!G2:G, ",")) 时，为什么拆分公式不会扩展到整个列?

Why doesn't the split formula get expanded over the entire column when I use =arrayformula(split(input!G2:G, ",")) ?

我只得到 input!G2 单元格的结果，而不是 G 列中的其余单元格.其他公式如 =arrayformula(find(",", input!G2:G)) 似乎可以正常运行并且可以毫无问题地扩展.

I get result only for the input!G2 cell, but not the rest in the G column. Other formulas like =arrayformula(find(",", input!G2:G)) seem to function and get expanded without problems.

推荐答案

应用于垂直数组的 SPLIT 现在可以工作了.(2017 年 1 月 4 日)

=ArrayFormula(SPLIT(input!G2:G,","))

或用于错误屏蔽

=ArrayFormula(IFERROR(SPLIT(input!G2:G,",")))

注意:结果数组的宽度与 SPLITted 元素的最大数量一样.

Note: the resultant array is as wide as the maximum number of SPLITted elements.

(答案的其余部分不再适用，但保留用于...历史目的?)

这将被视为一个非常糟糕的答案，但是:事实并非如此.

This will be regarded as a really bad answer, but: it just doesn't.

我怀疑这可能是因为它可能会产生一个参差不齐的数组(每行中有不同数量的元素)，这可能被认为是有问题的.但是，array SPLIT"曾经在以前版本的 Sheets 中工作"(尽管有一个错误，它不能在数组的第一个元素上正常工作).

I suspect it might be because it potentially will produce a jagged array (different number of elements in each row), which may be considered problematic. However, an "array SPLIT" used to "work" in the previous version of Sheets (albeit with a bug where it didn't work properly on the first element of the array).

SPLIT 不仅仅是一个不能在数组上迭代的函数(例如 INDIRECT、INDEX、SUMIFS).

SPLIT is not alone as a function that can't be iterated over an array (eg INDIRECT, INDEX, SUMIFS).

解决方法(2017 年 1 月 4 日编辑):

A workaround (edited Jan 4 2017):

=ArrayFormula(REGEXREPLACE(input!G2:G&REPT(",",6),REPT("([^,]*),",6)&",*","$"&COLUMN(OFFSET(A1,,,,1,6))))

OFFSET(A1,,,1,6) 和 REPT 函数中的 6 决定了 SPLIT 中元素的最大数量.您可以注入一个表达式来计算数据列中的最大元素数，但性能会受到更大的影响.

The 6 in the OFFSET(A1,,,1,6) and the REPT functions determines the maximum number of elements in the SPLIT. You could inject an expression that would calculate the maximum number of elements from the column of data, but then performance would suffer even more.

另外，这里需要注意的是，它仅支持按单个字符(在本例中为逗号)进行拆分.

Also, a caveat here is that it will only support splitting by a single character (in this case a comma).

或者，您可以查看 Google Apps 脚本自定义函数.

Alternatively, you could look at a Google Apps Script custom function.

以前的解决方法不再有效，因为 REGEXEXTRACT 似乎不再支持第二个参数的数组 - 无论如何，它在 2017 年 1 月仍然如此.

This previous workaround no longer works, as REGEXEXTRACT no longer appears to support an array for the second argument - as it stands in January 2017, anyway.

=ArrayFormula(IFERROR(REGEXEXTRACT(","&input!G2:G,"^"&REPT(",+[^,]+",COLUMN(OFFSET(A1,,,1,6))-1)&",+([^,]+)")))

这篇关于ARRAYFORMULA() 不适用于 SPLIT()的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！