Power Query：在Excel中比較兩個表格

在處理來自不同來源的數據、更新數據集或執行數據審核時，經常需要在Excel中比較兩個表格。常見的目標包括識別同時存在於兩個表格中的記錄、找出差異或指定哪些條目在任一列表中缺失。例如，您可能從同事或外部系統收到新數據，並需要將其與主列表進行比較以查找更新或差異。在下面的截圖中，兩個具有相同列結構的表格包含了一些不同的和一些匹配的記錄。有效突出顯示這些差異或相似之處的能力對於數據驗證和業務決策至關重要。

本教程詳細解釋了如何使用Power Query功能比較兩個表格。此外，如果您正在尋求其他實用方法——包括公式、VBA代碼或條件格式——請參閱目錄中添加的解決方案。

在Power Query中比較兩個表格

替代解決方案

• Excel公式 - 使用公式比較兩個表格
• VBA代碼 - 使用VBA代碼比較兩個表格
• 條件格式 - 視覺化比較表格

在Power Query中比較兩個表格

Power Query是Excel中重構和分析多來源數據的有效工具。當您需要逐行比較兩個數據表時，Power Query自動化了大部分過程，減少手動比較錯誤並節省時間。請按照以下逐步指南比較兩個表格，其中包含註釋和實用提示，以確保順暢的工作流程。

從兩個表格創建查詢

1. 選擇您想要比較的第一個表格。在Excel 2019和Excel 365中，進入 數據 標籤，然後點擊 從表格/範圍。請參見下面的截圖。
提示：開始之前，確保您的表格格式化為實際的Excel表格（Ctrl+T）。這有助於Power Query準確識別數據邊界。

注意：在Excel 2016和Excel 2021中，菜單顯示為 數據 > 從表格。它們的功能是等效的。
如果您的選擇未格式化為表格，Excel可能會提示您創建一個。

2. Power Query編輯器窗口打開。在這裡，您可以根據需要查看或清理數據，但為了比較，您可以直接繼續。點擊關閉並加載 > 關閉並加載到以設置連接選項。

3. 在導入數據對話框中，選擇僅創建連接，然後點擊確定。此選項允許您僅在Power Query內部使用數據，而無需立即將其重新加載回工作表。請參見以下截圖。

4. 重複之前的步驟（1–3）為第二個表格創建連接。現在，兩個表格作為單獨的連接出現在 查詢與連接 窗格中。這為您的數據準備好了比較步驟。
提示：仔細檢查兩個表格是否具有相同的列名稱和結構，以確保下一步的準確比較。

合併查詢以比較兩個表格

創建兩個查詢後，現在合併它們以逐行比較並揭示差異或匹配。

5. 在Excel 2019和Excel 365中，選擇數據標籤，然後點擊獲取數據 > 合併查詢 > 合併。這啟動了合併過程。請參見截圖。

注意：對於Excel 2016和Excel 2021，通過數據 > 新查詢 > 合併查詢 > 合併訪問此功能——過程保持不變。

6. 在合併對話框中：

• 在兩個下拉菜單中選擇第一和第二個表格查詢。
• 選擇您要在每個表格中比較的列——按Ctrl鍵選擇多個列。通常，必須選擇所有列才能進行真正的逐行比較。
• 選擇全外連接（來自兩者的全部行）作為連接類型。此選項匹配所有行並突出顯示缺失、額外或不同的記錄。
• 點擊確定繼續。

7. 第二個表格的匹配數據出現了一個新列：

• 點擊新列標題旁的小展開按鈕（兩個箭頭）。
• 選擇展開，並選擇要在結果中包含的列（通常為所有列）。
• 按下確定插入它們。

8. 第二個表格的數據現在顯示在第一個表格的旁邊，使比較記錄變得簡單。要將合併的數據返回Excel，請轉到首頁 > 關閉並加載 > 關閉並加載。這將把並排比較添加到新的工作表中。

9. 在結果工作表中，您可以輕鬆發現匹配和不匹配：相同的行並排顯示，而差異則以空白或不同的單元格突出顯示。這種佈局使您能夠高效地查找兩個表格之間的唯一、缺失或更改的記錄。
故障排除提示：如果某些記錄沒有如預期匹配，請重新檢查連接列是否具有穩定的格式，並且源數據中不存在額外的空格或拼寫錯誤。Power Query對即使是微小的差異也非常敏感。

總之，Power Query簡化了在Excel中比較兩個表格的複雜過程。一旦您熟悉了這些步驟，就可以將這個過程擴展到更複雜的數據場景中，例如比較來自不同文件的表格或執行多字段匹配。當處理頻繁的數據更新時，將Power Query步驟保存為模板可以加速未來具有相似結構的比較。如果您在列選擇或合併時遇到困難，請仔細檢查列數據類型，並考慮提前刪除任何重複的行以避免匹配問題。

如果Power Query對於偶爾的任務來說太過高級，或者您需要直接在工作表中突出顯示差異或自動化重複比較，也可以使用以下描述的替代解決方案。

Excel公式 - 使用公式比較兩個表格

逐行比較兩個表格以檢查差異，一個強大的方法是結合使用Excel的TEXTJOIN函數與IF公式來進行此檢查。

假設您在單元格A2:C10中有Table1，在單元格F1:H10中有Table2，並且您想檢查Table1中的哪些項目不出現在Table2中。

1. 在單元格I2中輸入以下公式：

=IF(TEXTJOIN("|",,A2:C2)=TEXTJOIN("|",,F2:H2), "Match", "Mismatch")

2. 然後，將公式拖動到其他單元格以獲得結果。如果每個表格中的行完全相同，公式返回“匹配”；否則，它返回“不匹配”。

VBA代碼 - 使用宏自動化比較兩個表格

當您希望快速且直觀地逐行比較兩個表格而不會因公式弄亂工作表時，VBA方法是理想的選擇。特別適用於審核或驗證導入數據的方式，既乾淨又用戶友好。

1. 點擊開發工具 > Visual Basic以打開VBA編輯器。

2. 在編輯器中，點擊插入 > 模塊並將以下代碼粘貼到模塊窗口中：

Sub CompareSelectedTablesRowByRow()
    Dim rng1 As Range, rng2 As Range
    Dim rowCount As Long, colCount As Long
    Dim r As Long, c As Long
    Dim xTitle As String
    xTitle = "Compare Tables - KutoolsforExcel"
    On Error Resume Next
    Set rng1 = Application.InputBox("Select the first table range:", xTitle, Type:=8)
    If rng1 Is Nothing Then Exit Sub
    Set rng2 = Application.InputBox("Select the second table range:", xTitle, Type:=8)
    If rng2 Is Nothing Then Exit Sub
    On Error GoTo 0
    If rng1.Rows.Count <> rng2.Rows.Count Or rng1.Columns.Count <> rng2.Columns.Count Then
        MsgBox "Selected ranges do not have the same size.", vbExclamation, xTitle
        Exit Sub
    End If
    rng1.Interior.ColorIndex = xlNone
    rng2.Interior.ColorIndex = xlNone
    For r = 1 To rng1.Rows.Count
        For c = 1 To rng1.Columns.Count
            If rng1.Cells(r, c).Value <> rng2.Cells(r, c).Value Then
                rng1.Cells(r, c).Interior.Color = vbYellow
                rng2.Cells(r, c).Interior.Color = vbYellow
            End If
        Next c
    Next r
    MsgBox "Comparison complete. Differences are highlighted in yellow.", vbInformation, xTitle
End Sub

3. 要運行代碼，選擇VBA窗口中的運行按鈕，或按F5。選擇第一個表格範圍，然後在提示時選擇第二個表格範圍。宏將逐行檢查兩個表格中的每個單元格，如果值不同，則兩個表格中的單元格將被高亮顯示為黃色。

條件格式 - 視覺化比較表格

條件格式提供了一種視覺直觀的方法，直接在工作表內突出顯示差異或匹配。當您需要識別重複或不同的值而不改變底層數據時，這種方法非常適合，並且特別適合於並排顯示的小表格。

1. 選擇您的第一個表格範圍（例如， A1:C10).
2. 前往 首頁 > 條件格式 > 新建規則.
3. 點擊 使用公式確定要設置格式的單元格 並輸入以下公式：=A2<>F2
4. 點擊 格式，選擇填充顏色，然後點擊 確定 > 確定 應用該規則。

結果：高亮顯示的單元格表示Table1中不存在於Table2中的值。您可以根據需要重複該過程以比較Table2 vs Table1。

使用 Kutools AI 解鎖 Excel 的魔法

• 智能執行：執行單元格操作、分析數據並創建圖表——所有這些都由簡單的指令驅動。
• 自訂公式：生成量身定制的公式，簡化您的工作流程。
• VBA 編碼：輕鬆編寫和實現 VBA 代碼。
• 公式解釋：輕鬆理解複雜的公式。
• 文本翻譯：打破電子表格中的語言障礙。

通過人工智能工具增強您的 Excel 能力。立即下載，體驗前所未有的效率！