在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部分.过滤数据

1. FILTER(any_columns，ROW(A2:C)< = MAX(IF(LEN(A2:C)，ROW(A2:C)* COLUMN(A2:C)^ 0,0))).这是我限制数据范围的方法.
2. 该范围是开放的，表示它从第二行( A2 )开始，在任何行结束.
3. 我想在此步骤中获得有限的数组，以减少公式应做的工作.这是通过条件 if 完成的.
4. ROW(A2:C)必须小于或等于数据的最大行. MAX(IF(LEN(A2:C)，some_rows)给出最大行数.
5. If(len .. 部分检查单元格内部是否有一些文本.
6. 注意 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 的情况下可以正常工作，因此它是一个过度杀伤力.

1. 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.
2. The range is open, means it starts from the second row (A2) and ends in any row.
3. 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.
4. 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.
5. If(len.. part checks if a cell has some text inside it.
6. 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, because filter formula takes only one row into its condition. That is why I multiply by COLUMN(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部分.加入文字

1. query 公式具有3个参数:data，query_text和number_of_header_rows.

1. 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部分.替换和修剪

1. 函数 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屋！