☰

如何根據兩個電子表格中的列找到重複值並檢查該行是否完全匹配

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. 新增 2 個表格到 Power Query - 在 Power Query 編輯器下 - 前往主頁 - 追加 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)

注意

  • 如果兩個工作表都有多個重複 ID（超過 2）然後，而不是計數值，最好用這個公式來找到重複項。

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

  • 並且在主表細胞中的標準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.

您可以根據需要調整公式中的儲存格參考。

相關內容