Excel：按列查找和排序匹配项

debugcn 发表于 Dev

伊利亚

我目前正在处理带有多个Excel文件的庞大的流行病学数据集。这些文件包含将近3万名患者的病理和临床报告。每个患者可以有几种病理和临床报告。为患者分配了唯一的ID。

我想将所有文件合并为一个，以便患者X001的ID包含所有文件的所有信息。我不能仅复制/粘贴文件，因为文件中的行数（ID）有所不同。

这是我要完成的示例。

我要合并两个列表，如下所示。

如您所见，List1和List 2的行号有所不同。另外，list1中有一些ID在list2中找不到，反之亦然。

我要合并它们，以便它们对齐和匹配，请参见下图。有人可以为此提供代码吗？我不能手动执行此操作，因为我在list1中有10万行，在list2中有30k行...要花几周的时间才能避免出错。

史蒂文·庞庞尼奥

您可以合并使用Power Query中内置的Excel合并的表，可以在“数据”选项卡下找到该表。

注意：照片是从Excel 2016拍摄的

第一步是创建查询：

在“数据”下的“获取和转换”部分中，单击-> ->，然后选择包含要合并表的适当工作簿。New QueryFrom FileFrom Workbook
选择在其中找到表的适当工作表，并确认它们正确显示
- If you notice that the table is not correct, you can make changes to it via the Edit button below.
  - For example, if you notice that your Column headers are being treated as a normal value, you can click Use First Row as Headers under the Power Query Editor Home -> Transform
  - I would also recommend changing the name of the query so it makes more sense down the line
Once you are happy with the way the query is looking, click on the Close and Load Dropdown menu under the Power Query Editor Home and select Close and Load To...
- Select Only Create Connection to add it into your Workbook Queries without duplicating the table.
Repeat the above steps for each table in which you are looking to merge.

Once you have all of your tables linked via Queries, you can now move on to merging them:

Under the same section of New Query select Combine Queries -> Merge
Select the two queries you are looking to merge in each of the respective boxes
- Confirm that they are correct via the preview window (don't worry if not all rows show)
- Rule of thumb would also be to select your largest query first, and the smaller second
Next, highlight the columns in which you are looking to merge based on. For your example it would be the ID. This is done simply by clicking on the column within the preview
Finally change the Join Kind to Full Outer and click OK

From here you should be back in the Power Query Editor

The final steps are modifying this merged query to your desired output
You should notice that there is a new column added next to your first original table with the name of the query at the top, next to the name is a button that allows you to expand out this query.
Select the appropriate columns you would like to merge into the other table and click OK
If at any point you make a mistake, you can retrace your changes under Applied Steps within the Query Settings Pane
Once you are happy with the way your newly merged query looks, go ahead and click on Close and Load
Your should now have access to your new merged query that will update based on changes made to the original connected files
如果您要从现在开始进行任何其他更改，只需单击表内的任意位置，您应该会看到“表工具”和“查询工具”选项卡都显示在顶部