在 Excel 中查找差異 - 解釋 5 種簡單技術

在 Microsoft Excel 中處理大型數據集時，不可避免地會遇到數據差異。識別和修復這些不一致對於確保准確的報告和分析至關重要。

好消息？Excel附帶了多種工具和技術來幫助您查找和解決這些問題。在本文中，您將了解一些最有效的方法。這包括過濾、條件格式和使用高級公式。

通過將這些技術融入您的日常工作流程，您將更有效地管理和分析您的數據。最終為您的項目做出更好的決策並改善結果。

目錄

• 了解 Excel 中的差異
• 在 Excel 中查找差異的 5 種方法
• #1 – 在 Excel 中通過人工審核發現差異
• #2 – 在 Excel 中使用過濾器來檢測和修復不一致的數據
• 3# – 突出顯示條件格式的差異
• #4 – 使用高級 Excel 函數查找差異
  • IF 和 IS 函數
  • VLOOKUP、HLOOKUP 和 XLOOKUP
  • 匹配
• #5 – 使用 Excel 插件進行差異檢測
• 您如何創建數據驗證規則以防止出現差異？
• 我們的最終決定

了解 Excel 中的差異

行和列中的差異可能是由於人為輸入錯誤、重複條目或數據格式不規則造成的。

看看下面的數據。您會注意到Walter White和Wayne Cena的性別拼寫錯誤 – Mael 而不是 Male。實際上，在這種情況下很容易注意到並修復錯誤。但對於包含數千行和列的電子表格而言，情況並非如此。

要查找和管理這些差異，您可以使用以下Excel功能：

• 過濾器：將過濾器應用於特定列以查看唯一值并快速發現不一致的數據。
• 條件格式：使用格式規則突出顯示滿足特定條件的單元格或列之間不匹配的數據。
• 高級 Excel 函數：利用 MATCH 和VLOOKUP等函數比較數據集並識別它們之間的差異。

通過了解如何在 Excel 中使用這些功能，您可以有效地發現和糾正數據集中的差異。

在 Excel 中查找差異的 5 種方法

我們主要有 5 種方法來解決 Excel 中的差異：

1. 人工審核
2. 過濾
3. 條件格式
4. 高級 Excel 函數
5. Excel 加載項

讓我們逐一瀏覽，以便您找出最適合您的方法。

#1 – 在 Excel 中通過人工審核發現差異

使用Excel時，人工審核可能是識別數據差異的有用方法。此過程需要仔細檢查數據集以發現​​任何不一致或錯誤。

您可以按照以下步驟執行人工審核：

• 首先要清楚地了解您正在使用的數據集。讓自己熟悉行、列和格式。
• 滾動數據並密切注意每個單元格中顯示的信息。您可以使用帶有填充顏色的單元格突出顯示、文本格式或條件格式等工具來使數據更易於閱讀。
• 將一行與另一行或一列與另一列進行比較。查找可能導致 Excel 工作表不准確的不匹配信息、重複條目等。
• 當您發現差異時，創建一個列表或使用評論來記錄它們。這些可以作為後期改正問題的重要參考。

在 Excel 電子表格中手動檢查數據是否存在差異時，耐心是關鍵。這個過程很耗時。但這是識別潛在錯誤的良好起點。

#2 – 在 Excel 中使用過濾器來檢測和修復不一致的數據

檢測不一致數據的最簡單方法之一是使用 Excel 的過濾器。

使用 Filter，我們將在以下數據集中的Gender列中隔離不正確的數據：

1.在主頁選項卡中選擇排序和篩選。

2. 選擇過濾器——確保您的光標在數據范圍內。

3. 單擊數據不一致的列上的下拉列表箭頭。

4. 現在，取消選擇所有正確匹配的數據。在我們的例子中，它是Male。然後，單擊確定以保存更改。

5. 您的 Excel 工作表現在將只顯示具有不正確值的行。要修復它們，請單擊“主頁”選項卡中的“查找並選擇”。

6.出現下拉列表時單擊替換。

7. 在Find what中輸入錯誤的值，在Replace with中輸入正確的值。然後，單擊“全部替換”。

8. 將出現一個確認更改的對話框。單擊“確定”繼續。

3# – 突出顯示條件格式的差異

識別列和行差異的另一種方法是使用條件格式。

您可以訪問諸如突出顯示單元格、頂部/底部、數據欄、色階和熨斗組等規則。此外，您可以創建新規則並管理或清除以前的規則。

我們將使用示例表格向您展示條件格式的強大功能：

1. 選擇您的表格或首選行和列。我們選擇 A 列（公司）和 B 列（國家）。然後，在主頁選項卡上選擇條件格式。

2. 選擇規則類型，然後選擇規則。我們正在選擇突出顯示單元格規則和重複值......

3。 現在，我們必須選擇是否突出顯示重複值或唯一值以及填充顏色和字體以格式化單元格。完成後，單擊確定。

4. 所有重複的值現在都用淺紅色填充深紅色文本突出顯示。

#4 – 使用高級 Excel 函數查找差異

IF 和 IS 函數

要比較單元格並識別差異，您可以使用 IF 和 IS 函數。

例如，我們使用公式=IF(A2=B2,"MATCH","NOT MATCHED")比較單元格 A2 和 B2。我們已經拖動了公式以將其應用於其他單元格。因此，相同的單元格返回MATCH否則NOT MATCHED。

您可以將 IF 與 IS 函數（如 ISNUMBER 或 ISTEXT）結合使用以檢查特定數據類型。例如，如果 B2 是數字，公式=IF(ISNUMBER(B2), "Number", "NaN")將返回c 列中的數字，否則返回NaN。

VLOOKUP、HLOOKUP 和 XLOOKUP

VLOOKUP（垂直）和 HLOOKUP（水平）函數在比較兩個表的不同列或行中的值時很有用。為此，請使用以下公式：=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) 或=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])。

將lookup_value替換為您要在另一個表中查找的值，將table_array替換為第二個表中的單元格區域，將col_index_num或row_index_num替換為您要從中返回值的列或行的索引號，並將[range_lookup]替換為FALSE表示完全匹配，TRUE表示近似匹配。

在下面的教程中，我們在 E 列上使用 VLOOKUP 函數來比較 A 列和 D 列中的員工姓名，如果找到匹配項，則從 B 列中提取他們的薪水。

XLOOKUP 是 VLOOKUP 和 HLOOKUP 的更新版本，適用於 Excel 2021 和 Excel 365。XLOOKUP 公式為：=XLOOKUP(lookup_value, lookup_array, return_array,[if_not_found])。

將lookup_value替換為您要在另一個表中查找的值，將lookup_array替換為要在第二個表中搜索的單元格範圍，將return_array 替換為您要從中返回值的數組，如果不匹配則將[if_nout_found]替換為文本值值被發現。

下面是具有相同表格的 XLOOKUP 快速示例：

匹配

MATCH 函數也可用於比較兩個列表的差異。

MATCH 函數=MATCH(lookup_value, lookup_array, [match_type])搜索範圍內的指定項目並返回該項目在該範圍內的相對位置。

在這裡，我們使用 MATCH 查找西瓜在數組A2:A8中的位置。我們將match_type用作0來查找與lookup_value完全相等的第一個值。

#5 – 使用 Excel 插件進行差異檢測

Excel 提供了各種加載項和內置工具，可幫助您更有效地檢測和分析差異。在本節中，我們將討論其中一些有用的插件。

首先，考慮使用Spreadsheet Inquire加載項。這可以幫助您比較兩個工作簿並逐個單元突出顯示差異。要啟用電子表格查詢加載項：

1. 單擊文件選項卡。

2. 選擇選項，然後單擊加載項類別。

3. 在“管理”框中，選擇“COM 加載項”並單擊“前往”。

4. 選中電子表格查詢框並單擊確定。

5. 啟用後，導航至“查詢”選項卡以使用“比較文件”命令。

另一個用於差異檢測的有用工具是分析工具庫。此插件提供高級統計功能和數據分析工具，在分析差異時非常有用。

要啟用 Analysis ToolPak，請按照與啟用 Spreadsheet Inquire 加載項相同的步驟操作。但是在“管理”框中選擇“Excel 加載項”並選中“分析工具庫”框。

總之，Excel 提供了各種加載項來幫助您檢測和處理數據中的差異。您可以通過熟悉這些工具和公式來提高數據準確性。

您如何創建數據驗證規則以防止出現差異？

要在 Excel 中創建有助於防止差異的數據驗證規則，請執行以下步驟：

1. 選擇要限制的單元格。這可以是單個單元格、一系列單元格或整個列。

2. 轉到工具欄中的“數據”選項卡，然後單擊“數據驗證”按鈕（由兩個水平框、一個綠色複選標記和一個紅色交叉圓圈標記）。

3. 在“數據驗證”窗口中，確保您位於“設置”選項卡上。在這裡，您將有機會根據您的需要定義您的驗證標準。

Excel 中提供了多種類型的驗證條件，其中一些包括：

• 整數 - 只允許指定範圍內的整數。
• Decimal – 只允許指定範圍內的十進制數。
• 列表——將輸入限制為可接受值的預定義列表。
• 日期 – 需要特定日期範圍內的日期。
• 時間——只接受特定時間範圍內的時間。
• 文本長度——​​對輸入文本的長度施加限制。
• 自定義 – 使您能夠使用 Excel 公式創建自定義驗證規則。

選擇適當的驗證標准後，根據需要指定參數。例如，如果您選擇整數，則必須使用數據范圍驗證器設置最小值和最大值，例如介於、等於、小於等。

除了驗證標準之外，您還可以設置自定義錯誤消息和輸入提示，以幫助用戶理解並遵守您的驗證規則。為此，請切換到“輸入消息”和“錯誤警報”選項卡以輸入所需的消息。

通過實施數據驗證規則，您可以顯著降低數據不一致的可能性並提高 Excel 工作表的整體準確性。

我們的最終決定

在本文中，您了解了查找和解決 Excel 數據差異的各種方法。讓我們回顧一下這些關鍵策略：

• 使用列中的過濾器下拉列表來識別唯一值和不一致的數據。
• 使用一組規則模板在選定單元格中使用條件格式來突出顯示差異。
• 應用MATCH、VLOOKUP、XLOOKUP等高級 Excel 函數來交叉檢查兩個列表或表格中的數據。
• 使用Excel 加載項檢測差異。
• 設置數據驗證規則以防止數據錯誤

在您的 Excel 技能集中使用這些技術，您的數據將更加準確和可信。

請記住定期練習這些方法，以更有效地檢測電子表格中的差異。

報名參加我們的，不斷提高您的技能，並繼續探索新的特性和功能，以更加精通 Excel。