将多列堆叠成一列? [英] Stacking multiple columns on to one?

问题描述

我正在使用 Google 电子表格，并且我正在尝试使用包含单词列表的多张工作表.在最后一张纸上，我想创建一个汇总列表，它是列中所有值的组合.我使用 =CONCATENATE() 让它工作，但它把它变成了一个字符串.有什么办法可以将其保留为列列表?

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?

这是一个列示例:

工作表 1

• 苹果
• 橙色
• 香蕉

Sheet2

• 菠萝
• 草莓
• 桃子

最终表格

• 苹果
• 橙色
• 香蕉
• 菠萝
• 草莓
• 桃子

推荐答案

更新答案

我就在那里是一个更好的解决方案.它已发布在下面，但我将其复制到此处，因此它位于最佳答案中:

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})

警告:在某些情况下，这将包括一个空白单元格(例如，如果第一个列表的末尾有一个).

如果您不关心排序和尾随空白单元格，一个简单的 sort() 将清理一切:

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}))

否则 filter() 可以像这样删除空格:

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.

但这有效:

=UNIQUE(TRANSPOSE(SPLIT(JOIN(";",A:A,B:B),";")))

如果您的数据包含任何;"自然需要更改分隔符的字符.

If your data contains any ';' characters you'll naturally need to change the delimiter.

这篇关于将多列堆叠成一列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！