将多列堆叠到一个? [英] Stacking multiple columns on to one?
问题描述
我正在使用Google SpreadSheet,并试图让多张表单包含单词列表。在最后的工作表中,我想创建一个总结列表,这是列中所有值的组合。我得到它使用= CONCATENATE()排序工作,但它把它变成一个字符串。任何方式保持列列表?
这是一个列的例子:
Sheet1
- 苹果
- 橙色
- 香蕉
工作表2
更新回答
>是更好的解决方案。它已被张贴在下面,但我在这里复制它,所以它在最高的答案:
= unique({A:A;注意:这将在某些场景中包含一个空白单元格(例如,B:B})
就好像第一个列表的末尾有一个)
如果你不关心排序和空白单元格,简单的<$ c
$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $> A; B:B}))
否则 filter() code $ c> = filter(unique({A:A; B:B})$ {code $>可以删除空格,如下所示:
),NOT(ISBLANK(unique({A:A; B:B}))))
以下是旧的 弃用的 答案
我相信这是做错的方法,因为这似乎是一个非常简单而常见的任务,我觉得我必须错过一些东西,因为它不应该需要这样一个过度的解决方案。
但是,这是有效的:
= UNIQUE(TRANSPOSE(;,A:A,B: B),;)))
如果您的数据包含任何';'自然需要更改分隔符。
I am using Google SpreadSheet, and I'm trying to have multiple sheets containg a list of words. On the final sheet, I would like to create a summative list, which is a combination of all the values in the column. I got it sort working using =CONCATENATE() , but it turned it into a string. Any way to keep it as a column list?
Here is an example as columns:
Sheet1
- apple
- orange
- banana
Sheet2
- pineapple
- strawberry
- peach
FinalSheet
- apple
- orange
- banana
- pineapple
- strawberry
- peach
Updated Answer
I was right there is a much better solution. It's been posted below but I'm copying it here so it's in the top answer:
=unique({A:A;B:B})
Caveat: This will include one blank cell in certain scenarios (such as if there's one at the end of the first list).
If you're not concerned with ordering and a tailing blank cell a simple sort()
will clean things up:
=sort(unique({A:A;B:B}))
Otherwise a filter()
can remove the blanks like so:
=filter(unique({A:A;B:B}),NOT(ISBLANK(unique({A:A;B:B}))))
The following is the old deprecated answer
I'm confident that this is "The Wrong Way To Do It", as this seems such an absurdly simple and common task that I feel I must be missing something as it should not require such an overwrought solution.
But this works:
=UNIQUE(TRANSPOSE(SPLIT(JOIN(";",A:A,B:B),";")))
If your data contains any ';' characters you'll naturally need to change the delimiter.
这篇关于将多列堆叠到一个?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!