将多列并合并为一列

Union of multiple columns as one column

提问人：Stan 提问时间：7/29/2015 最后编辑：pnutsStan 更新时间：11/18/2023 访问量：10942

问：

您能否建议为有或没有 VBA 的 12 个单独列（位于工作簿中的 12 个不同的 Excel 工作表中）创建联合列的最佳方法是什么？

有很好的手册如何在没有 VBA（使用 MATCH 功能）的情况下为两列执行此操作，但是我不确定如何处理多列的情况。

VBA Excel 联合 Unpivot

评论

0赞 nekomatic 7/29/2015
您能否举例说明源数据以及您希望的结果是什么？
0赞 Stan 7/29/2015
我有 12 张纸，每张纸都包含一列中的项目编号 ID。我想要一个单独的工作表，它将创建一个列，其中包含来自单独工作表的所有单个项目 ID 列的并集（可能按升序排列）。谢谢！
0赞 7/29/2015
Union 方法不会聚合来自不同工作表的不同范围。您需要遍历工作表并将列数据收集到一个集中位置，然后进行重复数据删除和排序。
0赞 nekomatic 7/29/2015
我认为您的选择是 a） 编写一个 VBA 宏，将适当数量的单元格从每个源工作表复制并粘贴到目标工作表上的单个列，然后对该列中的重复项进行排序和删除，或者 b） 将每个源范围定义为 Excel 表格，然后通过“获取外部数据”命令使用 MS Query，并使用 手写 SQL 查询， 并返回所需的数据。SELECT DISTINCTUNIONORDER BY

答：

1赞 pnuts 7/29/2015 #1

我认为可以通过数据透视表的多个合并范围来实现。需要列的标签和每张表的多列（可以克隆现有列）。应自动对列表中的重复项进行排序和删除（如果已克隆）。

1赞 jblood94 4/28/2018 #2

编辑：

我假设您的 ID 都是数字的（否则，如果没有 VBA，如果不是不可能的话，排序将非常棘手）。您可以修改以下数组公式以满足您的需求（选择一个具有足够行的区域来保存完整的 ID 堆栈，输入公式，然后使用 ++ 提交公式）：ctrlshiftenter

=SMALL(IFERROR(CHOOSE(COLUMN(INDIRECT("C1:C12",FALSE)),Sheet1!A1:A73,Sheet2!A1:A70,Sheet3!A1:A79,Sheet4!A1:A58,Sheet5!A1:A51,Sheet6!A1:A94,Sheet7!A1:A50,Sheet8!A1:A89,Sheet9!A1:A75,Sheet10!A1:A89,Sheet11!A1:A70,Sheet12!A1:A94),FALSE),ROW(INDIRECT("1:"&COUNT(Sheet1!A1:A73,Sheet2!A1:A70,Sheet3!A1:A79,Sheet4!A1:A58,Sheet5!A1:A51,Sheet6!A1:A94,Sheet7!A1:A50,Sheet8!A1:A89,Sheet9!A1:A75,Sheet10!A1:A89,Sheet11!A1:A70,Sheet12!A1:A94))))

我将使用一个较小的版本（2 列）来解释它是如何工作的：

=SMALL(IFERROR(CHOOSE(COLUMN(A1:B1),A1:A73,C1:C70),FALSE),ROW(1:143))

首先，返回一个介于 1 和 2 之间的整数水平数组。将其传递给具有两个单列范围的 CHOOSE 函数会从两个 and 创建一个 73 x 2 的数组（而不是创建交错数组，第二列的最后三个值将填充 ）。COLUMN(A1:B1)A1:A73C1:C70#NA

将结果包装起来，将三个值转换为（否则，将返回错误）。IFERROR#NAFALSESMALL

接下来，返回一个介于 1 和 143 之间的整数垂直数组。将 73 x 2 数组和 1 到 143 之间的整数数组传递给将返回一个 143 x 1 的排序值数组（垂直）（这三个值将被忽略）。ROW(1:143)SMALLFALSE

注意：以这种方式使用使公式稳定，即使删除了行/列;但是，它也会使公式不稳定，这将导致每次工作簿发生更改时都要重新计算公式，这可能会大大减慢速度。另一个选项是（例如，，它可能会受到行/列删除的影响，但不是可变的。INDIRECTINDIRECTINDEXROW(A1:INDEX(A:A,COUNT(...)))

评论

0赞 Maziar Rezaei 8/9/2021
这部分似乎不起作用：“将其传递给具有两个单列范围的 CHOOSE 函数会创建一个 73 x 2 数组”。似乎并非如此，它正在创建一个包含两个元素的水平数组，该数组仅对应于 73x2 数组的第一行。
0赞 jblood94 8/9/2021
@MaziarRezaei 你是对的。答案中有几个错误。我修复了错误并稍微简化了公式。
0赞 Maziar Rezaei 8/10/2021
多谢！我确实解决了 Column（xxx） 调用的问题，但使用的是谷歌表格，我只是在 Excel 中尝试过。这似乎是 Google 表格中的一个错误。在 Excel 中，它按预期工作。
0赞 Maziar Rezaei 8/10/2021
在 Google 表格中，创建 73x2 数组的公式的等效方法是使用大括号语法。这里是：={A1：A73，C1：C70}
0赞 Oink 1/18/2022 #3

if you don't mind a bit of manual effort, this works for numeric and non numeric IDs:

  1. Stack columns on top of each other manually using Ctrl-C + Ctrl-V
  2. Go to Data tab --> Filter --> Advanced Filter --> tick unique records only --> choose your copy to location

This simple two step process would then give you unique union of two columns. Obviously the higher the number of columns, the more the utility of a VBA approach.

评论

0赞 Darren Bartrup-Cook 1/18/2022
Gotta watch out for those 7 year old questions. :) Hopefully Stan has sorted the problem by now - hasn't been seen on the site for the last six years.

上一个：是否有正则表达式可以将 10 位数字（通常是非连续的）放入命名的捕获组中？

下一个：将多列并合并为一列