问题描述
我希望将几列合并为一列，我不在乎顺序.
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.
推荐答案
use:
=TRANSPOSE(SPLIT(TEXTJOIN(", ", 1, A2:A), ", "))
或:
=ARRAYFORMULA(TRIM(TRANSPOSE(SPLIT(TEXTJOIN(",", 1, A2:A), ","))))
这篇关于我可以在 Google Sheets 中将多维数组合并为一维数组吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！