在ArrayFormula中合并文本 [英] Combine Text in ArrayFormula
问题描述
我有一张使用Google表格的表格.它具有三列,这些列将始终具有空值或该列的特定值.每行将有一个,两个或三个值.它永远不会在一行上具有三个null值.在第四列中,我想要一个ArrayFormula,它将组合这些值并在多个值之间用逗号分隔.
I have a table using Google Sheets. It has three columns that will always have a null value or a specific value for that column. Each line will have one, two, or three values; it will never have three null values on one line. In the fourth column, I want an ArrayFormula that will combine those values and separate the values with a comma if there is more than one.
这是我要完成的事情的照片.
Here is a photo of what I am trying to accomplish.
到目前为止,我已经尝试了好几个主意,这个公式是到目前为止我得到的最接近的公式,但是仍然不能正常工作.我认为在连接之前将每一列视为一个数组,而不是逐行执行功能.我使用LEN函数而不是A2 ="或ISBLANK(A2),因为列A-C也是ArrayFormulas.我意识到这可能不是最有效的公式,但我认为它涵盖了所有可能性.我当然也欢迎其他想法.
I've tried several ideas so far and this formula is the closest I've gotten so far but it's still not quite working correctly; I think it is treating each column as an array before joining rather than doing the function line by line. I'm using the LEN function rather than A2="" or ISBLANK(A2) because columns A-C are ArrayFormulas as well. I realize this probably isn't the most efficient formula to use but I think it covers every possibility. I'm definitely open to other ideas as well.
={"Focus";
ArayFormula(
IFS(
$A$2:$A="", "",
(LEN(A2:A)>0 & LEN(B2:B)>0 & LEN(C2:C)>0), TEXTJOIN(", ", TRUE, A2:A, B2:B, C2:C),
(LEN(A2:A)>0 & LEN(B2:B)>0 & LEN(C2:C)=0), TEXTJOIN(", ", TRUE, A2:A, B2:B),
(LEN(A2:A)>0 & LEN(B2:B)=0 & LEN(C2:C)>0), TEXTJOIN(", ", TRUE, A2:A, C2:C),
(LEN(A2:A)=0 & LEN(B2:B)>0 & LEN(C2:C)>0), TEXTJOIN(", ", TRUE, B2:B, C2:C),
(LEN(A2:A)>0 & LEN(B2:B)=0 & LEN(C2:C)=0), A2:A,
(LEN(A2:A)=0 & LEN(B2:B)>0 & LEN(C2:C)=0), B2:B,
(LEN(A2:A)=0 & LEN(B2:B)=0 & LEN(C2:C)>0), C2:C
)
)
}
是否可以使用Google表格实现这一目标?
Is it possible to achieve this with Google Sheets?
推荐答案
请尝试:
= ARRAYFORMULA(SUBSTITUTE(TRIM(TRANSPOSE(QUERY(TRANSPOSE(FILTER(A2:C,ROW(A2:C)))* COLUMN(A2:C)^ 0,0)))),, 2 ^ 99))),",,"))
注意:
- 如果某些名称中包含空格,该公式将无法正常工作:例如阿斯顿·马丁"(Aston Martin)
- 因此,如果您有空格,请尝试以下操作:
= ARRAYFORMULA(SUBSTITUTE(代替(TRIM(TRANSPOSE(QUERY(TRANSPOSE(FILTER(SUBSTITUTE(A2:C,'',char(9)),ROW(A2:C))< = MAX(IF(LEN(A2:C),ROW(A2:C)* COLUMN(A2:C)^ 0,0)))),, 2 ^ 99))),",,"),CHAR(9),"))
编辑
忽略较短的变体(不带 * COLUMN(A2:C)^ 0
)将起作用:
Noticed the shorter variant (without *COLUMN(A2:C)^0
) will work:
= ARRAYFORMULA(SUBSTITUTE(替代(TRIM(TRANSPOSE(QUERY(TRANSPOSE(FILTER(SUBSTITUTE(A2:C,",char(9)),ROW(A2:C)< =:C),0)))),, 2 ^ 99))),",,"),CHAR(9),"))
注意:
- 我使用了一个古老的技巧来将字符串与
array-formula
连接起来.请参见示例文件
- I used an old trick to join strings with an
array-formula
. See sample file
如果您想了解任何分层公式,最好的方法是将其分成几部分:
If you like to understand any tiered formula, the best way is to split it by parts:
第1部分.过滤数据
-
FILTER(any_columns,ROW(A2:C)< = MAX(IF(LEN(A2:C),ROW(A2:C)* COLUMN(A2:C)^ 0,0)))
.这是我限制数据范围的方法. - 该范围是开放的,表示它从第二行(
A2
)开始,在任何行结束. - 我想在此步骤中获得有限的数组,以减少公式应做的工作.这是通过条件
if
完成的. -
ROW(A2:C)
必须小于或等于数据的最大行.MAX(IF(LEN(A2:C),some_rows)
给出最大行数. -
If(len ..
部分检查单元格内部是否有一些文本. - 注意
some_rows
部分:MAX(IF(LEN(A2:C),ROW(A2:C)* COLUMN(A2:C)^ 0,0))),, 2 ^ 99)))
.ROW(A2:C)
必须乘以列,因为filter
公式只需要一行进入其状态.这就是为什么我乘以COLUMN(A2:C)^ 0
是具有1s的列.编辑.现在注意到了,该公式在没有* COLUMN(A2:C)^ 0
的情况下可以正常工作,因此它是一个过度杀伤力.
FILTER(any_columns,ROW(A2:C)<=MAX(IF(LEN(A2:C),ROW(A2:C)*COLUMN(A2:C)^0,0)))
. this is my way to limit the data range.- The range is open, means it starts from the second row (
A2
) and ends in any row. - I want to get the limited array in this step to reduce work that the formula should do. This is done with a condition,
if
. ROW(A2:C)
must be less or equal to the max row of data.MAX(IF(LEN(A2:C), some_rows)
gives the max row.If(len..
part checks if a cell has some text inside it.- Note
some_rows
part:MAX(IF(LEN(A2:C),ROW(A2:C)*COLUMN(A2:C)^0,0)))),,2^99)))
.ROW(A2:C)
must be multiplied by columns, becausefilter
formula takes only one row into its condition. That is why I multiply byCOLUMN(A2:C)^0
which is columns with 1s. Edit. Now noticed, that the formula works fine without*COLUMN(A2:C)^0
, so it's an overkill.
第2部分.加入文字
-
query
公式具有3个参数:data,query_text和number_of_header_rows.
query
formula has 3 arguments: data, query_text, and a number_of_header_rows.
数据
是使用过滤器制作的.
data
is made with a filter.
query_text
为空,这使我们等效于选择全部(选择*"
).
query_text
is empty, which gives us equivalent to select all
("select *"
).
并且标头的行数是很大的数字( 2 ^ 99
).这是一个技巧:当 query
的标头多于一行时,它将与他们连接在一起.
And the number of rows of a header is some big number (2^99
).
This is a trick: when a query
has more headers then one row,
it will join them with space.
建立联合后, transpose
函数会将结果转换回列.
After a union is made, transpose
function will convert the result back to the column.
第3部分.替换和修剪
-
函数
trim
删除多余的空格.
然后我们用定界符替换空格:,"
.这就是为什么如果字符串中包含空格,则需要修改公式.正确的结果:福特,阿斯顿·马丁".错误的:福特,阿斯顿,马丁".但如果我们以前用一些字符替换空格( char(9)
是Tab),那么我们就不会在此步骤中替换它.
Then we replace spaces with the delimiter: ", "
. That is why the
formula needs to be modified if spaces are in strings. Correct
result: "Ford, Aston Martin". Incorrect: "Ford, Aston, Martin". But
if we previously replace spaces with some char (char(9)
is Tab),
then we do not replace it in this step.
这篇关于在ArrayFormula中合并文本的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!