excel 比對兩排資料差異：詳細圖文教學與常見問題解析

在日常工作中，經常需要比對兩排（或兩欄、兩個表格）的資料，找出它們之間的差異，例如：驗證兩個清單是否一致、找出新增或刪除的項目、或是比對兩個版本的資料集。Excel 提供了多種強大且便捷的功能來實現這個需求。本文將詳細介紹幾種常見且實用的方法，並解答您可能遇到的問題。

方法一：使用「條件化格式」找出差異

這是最直觀也最常用的一種方法，透過設定條件，讓 Excel 自動標記出兩排資料中不同的項目。

步驟一：準備兩排要比對的資料

假設我們有兩排資料，分別在 A 欄和 B 欄。為了方便比對，建議將兩排資料對齊，即相同位置的資料代表要比較的對象。如果兩排資料長度不同，也可以先進行一些處理（例如填充空值），以便後續操作。

範例：

A 欄：蘋果, 香蕉, 橙子, 葡萄, 芒果

B 欄：蘋果, 菠蘿, 橙子, 葡萄, 奇異果

步驟二：選取要套用條件化格式的範圍

選取您想要標記差異的資料範圍。通常我們會選取包含兩排資料的整個範圍，例如 A1:B5。

步驟三：新增「條件化格式」規則

1. 點擊 Excel 功能區的「常用」選項卡。
2. 在「樣式」群組中，找到並點擊「條件化格式」。
3. 選擇「新增規則」。

步驟四：設定規則

1. 在「新增規則」對話框中，選擇「使用公式來決定要格式化哪些儲存格」。
2. 在「為符合此公式的值設定格式」欄位中，輸入以下公式：

=A1<>B1

說明：

• 這個公式的意思是：如果 A1 儲存格的值不等於 B1 儲存格的值，則此條件為 TRUE。
• 當公式結果為 TRUE 時，Excel 便會套用您設定的格式。
• 請注意，公式中的 A1 和 B1 是您選取範圍的第一個儲存格，Excel 會根據您選取的範圍自動調整公式，比對相對應的儲存格。
3. 點擊「格式」按鈕。
4. 在「儲存格格式」對話框中，選擇您想要用來標記差異的格式，例如，在「填滿」選項卡中選擇一個醒目的顏色（如黃色）。
5. 點擊「確定」關閉「儲存格格式」對話框。
6. 點擊「確定」關閉「新增規則」對話框。

現在，Excel 會自動將兩排資料中不相同的儲存格標記出來。在這個例子中，B2（菠蘿）和 B5（奇異果）會被標記出來，因為它們與 A2（香蕉）和 A5（芒果）不同。

進階技巧：只標記一個範圍的差異

如果您只想標記 A 欄中有，但 B 欄中沒有的項目，或者反之，可以調整公式：

• 標記 A 欄中與 B 欄不同的項目：=COUNTIF($B$1:$B$5,A1)=0 (假設 B 欄為 $B$1:$B$5，且 A1 是您選取範圍的第一個儲存格)
• 標記 B 欄中與 A 欄不同的項目：=COUNTIF($A$1:$A$5,B1)=0 (假設 A 欄為 $A$1:$A$5，且 B1 是您選取範圍的第一個儲存格)

這種方法適用於比對兩個清單，找出其中一個清單獨有的項目。

方法二：使用「比較並合併工作表」

如果您需要更詳細地比對兩個表格，找出新增、刪除或修改的資料，可以使用 Excel 的「比較並合併工作表」功能（需要安裝「Office 增益集」中的「Spreadsheet Compare」）。這個方法通常用於比對兩個版本的 Excel 文件。

步驟一：安裝「Spreadsheet Compare」

如果您的 Excel 沒有此功能，可以透過「檔案」->「選項」->「增益集」->「管理：Excel 增益集」->「前往」，然後勾選「Spreadsheet Compare」進行安裝。

步驟二：開啟「Spreadsheet Compare」

在「Spreadsheet Compare」工具中，您需要指定兩個要比較的工作簿。

步驟三：設定比較選項

您可以選擇要比較哪些工作表，以及如何處理儲存格的差異（例如：僅顯示差異、以顏色標示差異等）。

步驟四：執行比較

點擊「比較」，Excel 會生成一個包含所有差異的報告，詳細列出哪些儲存格的內容發生了變化。

優點：

• 非常適合比對兩個版本的完整 Excel 文件。
• 可以詳細追蹤新增、刪除和修改的項目。

缺點：

• 需要額外安裝增益集。
• 對於僅僅是兩排簡單資料的快速比對，可能稍顯複雜。

方法三：使用公式 `IF` 和 `EXACT` 函數

如果您想要在一個新的欄位中明確顯示差異的結果，可以使用 `IF` 和 `EXACT` 函數的組合。

步驟一：準備資料

同樣假設資料在 A 欄和 B 欄。

步驟二：在新欄位輸入公式

在 C 欄（或其他新欄位）的第一個儲存格（例如 C1）輸入以下公式：

=IF(EXACT(A1,B1),"相同","不同")

公式說明：

• EXACT(A1,B1)：這個函數用來比較兩個文字字串是否完全相同（區分大小寫）。如果完全相同，則返回 TRUE，否則返回 FALSE。
• IF(EXACT(A1,B1),"相同","不同")：如果 `EXACT(A1,B1)` 的結果為 TRUE，則顯示「相同」，否則顯示「不同」。

步驟三：填充公式

將 C1 儲存格的公式向下拖曳填充，即可看到每一行資料的比較結果。

優點：

• 結果直觀，直接顯示「相同」或「不同」。
• 可以自定義顯示的文字。

缺點：

• 僅能判斷是否相同，無法直接標記出具體的差異內容。
• 如果需要比對的項目非常多，可能需要額外的處理來定位具體差異。

方法四：使用 Power Query (Get & Transform)

對於更複雜的資料比對和合併場景，Power Query 是一個非常強大的工具。它可以讓您從多種來源載入資料，進行轉換，然後合併比對。

基本步驟：

1. 將兩排資料分別載入為 Power Query。
2. 使用「合併查詢」功能，選擇兩個資料表，並指定比對的欄位。
3. 選擇合併的類型（例如：左合併、右合併、完整外部合併），這將決定如何處理兩個資料表中不存在的項目。
4. 在合併後的結果中，您可以進一步篩選和查看差異。

優點：

• 功能強大，適用於各種複雜的資料處理和合併需求。
• 步驟可自動化，方便重複執行。

缺點：

• 學習曲線相對較高。
• 對於簡單的比對，可能顯得過於複雜。

結論

選擇哪種方法取決於您的具體需求和資料的複雜程度。對於日常的簡單比對，條件化格式是最快捷的方式。如果您需要更詳細的追蹤和報告，可以考慮「Spreadsheet Compare」。而對於更複雜的資料整合和分析，Power Query 則能提供無與倫比的靈活性。

常見問題 (FAQ)

Q1：為何使用條件化格式比對時，有些儲存格沒有被標記出來？

A：有幾種可能的原因：

• 公式錯誤：請仔細檢查您輸入的公式是否正確，特別是儲存格的引用和範圍。例如，公式 `=A1<>B1` 需要您選取的範圍是從 A1 開始，並且 A1 和 B1 是您要比較的對應儲存格。
• 範圍選擇錯誤：確保您選取的條件化格式範圍包含了您要比對的兩排資料。
• 儲存格值相同：如果儲存格的值實際上是相同的（包括空格、大小寫），則不會被標記。請注意 `EXACT` 函數是區分大小寫的，而 `A1<>B1` 則不區分大小寫，但會考慮空格。
• 其他規則的影響：如果您的工作表中已經存在其他條件化格式規則，可能會影響到新規則的套用。您可以透過「條件化格式」->「管理規則」來查看和調整規則的優先順序。

Q2：如何比對兩排資料，並找出其中一個清單獨有的項目？

A：您可以使用條件化格式結合 `COUNTIF` 函數來實現。假設您要比對 A 欄（清單一）和 B 欄（清單二），並找出 A 欄中獨有的項目，您可以：

1. 選取 A 欄的資料範圍。
2. 新增條件化格式規則，選擇「使用公式來決定要格式化哪些儲存格」。
3. 輸入公式：=COUNTIF($B$1:$B$10,A1)=0 （假設 B 欄的範圍是 B1:B10，A1 是選取範圍的第一個儲存格）。
4. 設定格式，例如黃色填滿。

這個公式的意思是，如果 A1 這個值在 B1:B10 的範圍內出現的次數為 0（即不存在），則標記 A1。您可以根據需要調整範圍和公式。

Q3：如何比對兩排資料，並同時找出新增和刪除的項目？

A：對於同時找出新增和刪除的項目，最清晰的方法是使用 Power Query 的「合併查詢」功能，並選擇「完整外部合併」。這會將兩個資料集的所有列都包含在結果中，並標記出僅存在於其中一個資料集中的項目。另外，您也可以使用「Spreadsheet Compare」工具，它會生成詳細報告，清楚列出新增、刪除和修改的項目。

Q4：使用 `IF` 和 `EXACT` 函數時，為何有些看似不同的內容卻顯示「相同」？

A：這是因為 `EXACT` 函數在比較時是區分大小寫的。例如，"Apple" 和 "apple" 在 `EXACT` 函數中會被視為不同。如果您希望不區分大小寫進行比較，可以使用 `IF(UPPER(A1)=UPPER(B1),"相同","不同")` 或 `IF(LOWER(A1)=LOWER(B1),"相同","不同")` 的公式，將兩個儲存格的內容都轉換為大寫或小寫後再進行比較。

Q5：如何在 Excel 中快速比對大量資料，並匯出差異列表？

A：對於大量資料的比對，推薦使用 Power Query。您可以將兩個資料集都載入 Power Query，然後進行「合併查詢」，選擇「完整外部合併」。這樣會生成一個包含所有數據的表格，您可以在該表格中篩選出僅存在於左側表格（代表原始資料）或右側表格（代表新資料）的記錄，然後將篩選後的結果複製到新的工作表中，形成差異列表。這種方法效率高且可重複操作。