☰

如何根据两个电子表格中的列查找重复值并检查行是否完全匹配

tag-icon • tag-icon microsoft-excel
如何根据两个电子表格中的列查找重复值并检查行是否完全匹配

我有两个 Excel 电子表格：Header2018 和 Header2019

它们都包含相同的列标题，但记录条目不同。其中一些条目是相同的，我想找出哪些是相同的。

我想将所有重复记录（基于列：asset_id）合并到一个新的电子表格中，然后检查其余列之间是否存在差异。

2018 年标题示例：

asset_id    Name    Country

2          Trent      CAN
3          Alex       CAN
4          Derek      CAN
5          Louis      CAN
6          Teresa     CAN

标题 2019：

asset_id    Name    Country
4          Derek      CAN
5          Louis      USA
1          Kate       CAN
7          Pat        CAN
8          Steven     CAN
9          Tom        CAN

结果表：

asset_id    Name    Country
4          Derek      CAN
5          Louis      CAN
4          Derek      CAN
5          Louis      USA

根据结果​​表，我希望能够找到 asset_id 相同但另一列不同的记录。

提前致谢！

答案1

您可以使用 Power Query 获取结果。

  1. 在 Power Query 编辑器下向 Power Query 添加 2 个表 - 转到主页 - 附加 2 个查询： 在此处输入图片描述

  2. 选择 asset_id 列-转到转换-选择分组依据-选择高级-添加聚合-为新列键入名称并选择所有行： 在此处输入图片描述

  3. 展开表格并选择名称和国家 - 过滤计数列 >2，删除计数列 - 关闭并加载： 在此处输入图片描述

答案2

我想建议方法，需要辅助列和数组公式来从两个工作表中提取公共行：

第 1 页：

在此处输入图片描述

  • A2单元格中的公式辅助值：

=COUNTIF(Sheet2!$B$2:$B$7, B2)

第 2 页：

在此处输入图片描述

  • 单元格中的公式A2：

=COUNTIF(Sheet1!$B$2:$B$6, B2)

注意：

  • 如果两个 Sheet 都有多个重复 ID（超过 2）然后，而不是计数，最好用这个公式来查找重复项。

    =IF(ISERROR(MATCH(B2,Sheet2!B$2:B$7,0)),"","Duplicate")

  • 并且在主表Cell 中的标准E59是复制代替1。

主表：

在此处输入图片描述

  • 单元格中的数组公式G60：

    {=IFERROR(INDEX(Sheet1!$B$2:$D$6, SMALL(IF(COUNTIF($E$59, Sheet1!$A$2:$A$6), ROW(Sheet1!$B$2:$D$6)-MIN(ROW(Sheet1!$B$2:$D$6))+1), ROW(A1)), COLUMN(A1)),IFERROR(INDEX(Sheet2!$B$2:$D$7, SMALL(IF(COUNTIF($E$59, Sheet2!$A$2:$A$7), ROW(Sheet2!$B$2:$D$7)-MIN(ROW(Sheet2!$B$2:$D$7))+1), ABS(ROW(A1)-2)), COLUMN(A1)),""))}

  • 完成公式Ctrl+Shift+Enter，先向右然后向下填充。

  • 在数组公式中ABS(ROW(A1)-2))用于调整行号为。Sheet 2​1

您可以根据需要调整公式中的单元格引用。

相关内容