我可以将多维数组合并为Google表格中的一维数组吗? [英] Can I merge a multi-dimensional array into a single dimensional array in Google Sheets?

问题描述

我希望将几列合并为一列，我不在乎顺序.

I'm looking to combine several columns into one, I do not care about order.

例如，我有一列包含标签集合:

For example, I have a column with a collection of tags:

|   || A                      |
| = || ====================== |
| 1 || Tags List              |
| 2 || Loon, Diver, Gaviform  |
| 3 || Shoveler, Anseriformes |
| 4 || Roc                    | 

如果我在 B2 中使用公式 = ARRAYFORMULA(SPLIT(A2:A))，我将得到以下输出:

If I use the formula =ARRAYFORMULA(SPLIT(A2:A)) in B2, I would get the following output:

|   || B        | C            | D         |
| = || ======== | ============ | ========= |
| 1 ||          |              |           |
| 2 || Loon     | Diver        | Gaviform  |
| 3 || Shoveler | Anseriformes |           |
| 4 || Roc      |              |           |

相反，我想将其收集到单个列中，例如:

Instead, I'd like to collect that into a single column like:

|   || B            |    |   || B            |
| = || ============ |    | = || ============ |
| 1 ||              |    | 1 ||              |
| 2 || Loon         |    | 2 || Loon         |
| 3 || Diver        | OR | 3 || Shoveler     |
| 4 || Gaviform     |    | 4 || Roc          |
| 5 || Shoveler     |    | 5 || Diver        |
| 6 || Anseriformes |    | 6 || Anseriformes |
| 7 || Roc          |    | 7 || Gaviform     |

有没有一种方法可以使用单个公式来执行此操作，以便我可以执行 = OTHER_FORMULA_OR_FORMULAS(ARRAYFORMULA(SPLIT(A2:A)))，假设标签列表可以是任意长度?

Is there a way to do this with a single formula such that I could do =OTHER_FORMULA_OR_FORMULAS(ARRAYFORMULA(SPLIT(A2:A))), given that the tag list might be any length?

我知道可以使用数组构造函数，例如 {A2:A; B2:B，C2:C} 来合并列，但是鉴于我可能会有无数的标签和许多类似的列来执行此操作，寻找一个适合所有公式的尺寸.

I know it's possible to use an array constructor, e.g. {A2:A;B2:B,C2:C} to combine the columns, but given that I might have an untold number of tabs and a number of similar columns to do this on, I'm looking for a one size fits all formula.

推荐答案

使用:

=TRANSPOSE(SPLIT(TEXTJOIN(", ", 1, A2:A), ", "))

或:

=ARRAYFORMULA(TRIM(TRANSPOSE(SPLIT(TEXTJOIN(",", 1, A2:A), ","))))

这篇关于我可以将多维数组合并为Google表格中的一维数组吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！