在 ArrayFormula 中组合文本 [英] Combine Text in ArrayFormula

问题描述

我有一张使用 Google 表格的表格.它具有三列，这些列始终具有空值或该列的特定值.每行将有一个、两个或三个值；它永远不会在一行上有三个空值.在第四列中，我想要一个 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(TRANSPOSE(FILTER(A2:C,ROW(A2:C)<=MAX(IF(LEN(A2:C),ROW(A2:C))*COLUMN(A2:C)^0,0)))),,2^99)))," ",", "))

注意事项:

• 如果某些名称内部有空格，则公式将无法正常工作:例如阿斯顿马丁"
• 所以如果你有空格，请试试这个:

<代码>=数组公式(替换(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:

<代码>=数组公式(替换(SUBSTITUTE(TRIM(TRANSPOSE(QUERY(TRANSPOSE(FILTER(SUBSTITUTE(A2:C," ",char(9)),ROW(A2:C)<=MAX(IF(LEN(A2:C),ROW(A2):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 是用过滤器制作的.

query_text 为空，这相当于全选("select *").

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屋！