Excel 欄位比較:尋找符合項目和差異!
本指南深入探討了在 Excel 中比較兩列的各種方法,這是許多使用者的常規任務。 無論你是 逐行比較, 逐個細胞, 反白顯示匹配項, 或者 找出差異,本教程解決了您可能遇到的各種場景。 我們針對大多數情況制定了解決方案,旨在增強您的 Excel 體驗。 注意: 您可以使用右側表格👉快速導覽至所需內容。
逐行比較兩列
下面是一個資料集(範圍 B2:C8),我需要檢查同一行中 B 列中的名稱是否與 C 列中的名稱相同。
本部分提供了兩個範例來解釋如何逐行比較兩列
範例 1:比較同一行中的儲存格
通常,如果要逐行比較兩列以進行完全匹配,則可以使用以下公式:
=B2=C2
媒體推薦 Enter 鍵並將填滿手柄向下拖曳到儲存格 D8。 如果公式傳回 TRUE,則同一行兩列的值完全相同; 如果傳回 FALSE,則值不同。
或者您可以使用 IF 函數顯示特定文字以顯示匹配或不匹配,如下所示:
=IF(B2=C2,"Match","No match")
結果可能如下圖所示:
範例 2:比較同一行中的儲存格(區分大小寫)
如果要逐行比較兩列是否區分大小寫,可以使用組合 IF 和 EXACT 函數的公式。
IF(EXACT(B2,C2), "Match", "Mismatch")
媒體推薦 Enter 鍵獲取第一個結果,然後將自動填充手柄拖到單元格D8。
在上面的公式中,您可以將文字“Match”和“Mismatch”更改為您自己的描述。
比較同一行中的多個列
有時,您可能想要比較同一行中的兩列以上,例如如下圖所示的資料集(範圍 B2:D7)。 在本節中,它列出了比較多列的不同方法。
在這裡,它分為兩部分,以提供有關比較同一行中的多個列的詳細說明。
範例1:比較多列並在同一行的所有儲存格中尋找符合項
要查找同一行中各列的完全匹配,下面的公式可以幫助您。
=IF(COUNTIF($B2:$D2, $B2)=3, "Full match", "Not")
媒體推薦 Enter 鍵以獲取第一個比較結果,然後將自動填充手柄拖到單元格E7上。
- 此公式比較列時不區分大小寫。
- 在公式中,3是列數,您可以更改它以滿足您的需要。
範例 2:比較多列並在同一行的任兩個儲存格中尋找符合項
有時,您想找出同一行中的任何兩列是否匹配,可以使用下面的IF公式。
=IF(COUNTIF($B2:$D2,$B2)>=2,"Match","No match")
媒體推薦 Enter 鍵,然後將填充手柄拖到單元格E7上。
- 此公式不支援區分大小寫。
- 式中,2表示在同一行的任兩列中尋找匹配項。 如果您要在同一行的三列中尋找符合項,請將 2 變更為 3。
逐行比較兩列或多列並突出顯示匹配或差異
如果您想要比較兩列或多列並反白顯示匹配或差異,本節將介紹兩種處理這些工作的方法。
有兩個例子來比較和突出匹配和差異
範例 1:比較兩列並反白在同一行中的所有儲存格或同一行中的任兩個儲存格中的完全符合項
若要反白所有儲存格或同一行中任兩個儲存格中的符合項,條件格式功能可以為您提供協助。
1.選擇您要使用的範圍,然後單擊 首頁 > 條件格式 > 新規則.
2。 在裡面 新格式規則 dialog
- 選擇 使用公式來確定要格式化的單元格 來自 選擇規則類型 部分
- 在中使用下面的公式 格式化此公式為真的值 文本框。
=COUNTIF($B2:$D2, $B2)=3
- 點擊 格式.
3。 在裡面 單元格格式 對話框,然後選擇一種填滿顏色或其他儲存格格式以突出行。 點選 OK > OK 關閉對話框。
現在,只有所有儲存格都符合的行才會被反白。
範例 2:比較兩列並突出顯示同一行中的差異
如果你想突出顯示同一行中的差異,即逐個比較列單元格,並根據第一列找到不同的單元格,你可以使用Excel內建功能-Go To Special。
1.選擇要突出顯示行差異的範圍,然後單擊 首頁 > 查找和選擇 > 去特別.
2.在彈出 去特別 對話框中,選擇 行差異 選項。 點擊 OK.
現在已選擇行差異。
3 現在保持選取儲存格,按一下 首頁 > 填充顏色 從下拉菜單中選擇一種顏色。
比較儲存格中兩個欄位的唯一資料和重複數據
在這部分中,資料集(範圍B2:C8)如下所示,您想要同時尋找B列和C列中的所有值,或只查找B列中的值。
本節列出了比較儲存格中兩列的 4 種不同方法,您可以根據需要選擇以下方法之一。
- 範例1:比較儲存格中的兩個欄位並在另一列中顯示比較結果
- 範例 2:比較儲存格中的兩列,並使用方便的工具選擇或反白顯示重複或唯一的數據
- 範例 3:比較儲存格中的兩個欄位並反白顯示重複或唯一數據
- 範例 4:比較儲存格中的兩個欄位並在另一列中列出完全相同的重複項
範例1:比較儲存格中的兩個欄位並在另一列中顯示比較結果
在這裡,您可以使用與 IF 和 COUNTIF 函數結合的公式來比較兩列,並找到 B 列中但 C 列中不存在的值。
=IF(COUNTIF($C$2:$C$8, $B2)=0, "No in C", "Yes in C")
媒體推薦 Enter 鍵,然後將自動填充手柄拖到單元格D8中。
- 此公式比較兩列,不區分大小寫。
- 您可以將描述“No in C”和“Yes in C”變更為其他。
範例 2:比較儲存格中的兩列,並使用方便的工具選擇或反白顯示重複或唯一的數據
有時,在比較兩列之後,您可能會對匹配項或差異進行其他操作,例如選擇,刪除,複製等。 在這種情況下,方便的工具- 選擇相同和不同的單元格 of Excel的Kutools 可以直接選擇匹配或差異以更好地進行下一個操作,也可以直接突出顯示值。
後 免費安裝Kutools for Excel點擊此處成為Trail Hunter 庫工具 > 選擇 > 選擇相同和不同的單元格。 然後在「選擇相同和不同儲存格」對話方塊中,請執行下列操作:
- 在 在中查找值 和 根據 部分, 選擇兩列 分別用來比較。
- 選擇 每一行 選項。
- 選擇 相同的值 or 不同的價值觀 如你所需。
- 指定是否為所選值著色並按一下 OK.
彈出一個對話框提醒您已找到的值的數量,點擊 OK 關閉對話框。 同時,已經選擇了值,現在您可以刪除或複製或執行其他操作。
如果勾選「填滿背景色」和「填滿字體顏色」複選框,結果將如下所示:
- 如果要比較區分大小寫,請勾選 區分大小寫 選項。
- 該工具支持比較不同工作表中的兩列。 按此處了解有關選擇相同和不同單元格的更多詳細信息.
- 如果您對這個工具有興趣, 點擊此處免費下載並試用 30 天。
範例 3:比較儲存格中的兩個欄位並反白顯示重複或唯一數據
Excel 中的條件格式功能非常強大,您可以使用它來比較儲存格中的兩列,然後根據需要突出顯示差異或符合項目。
1.選擇要比較的兩列,然後單擊 首頁 > 條件格式 > 加亮單元格規則 > 重複的值.
2. 在彈出的「重複值」對話方塊中, 選擇突出顯示格式 您需要從下拉清單中取得值。
3。 點擊 OK。 然後,突出顯示了兩列中的重複項。
範例 4:比較儲存格中的兩個欄位並在另一列中列出完全相同的重複項
如果您想在區分大小寫的情況下逐個單元格比較兩列後列出另一列中的匹配值,下面的巨集程式碼可以幫助您。
1.啟用要比較兩列的工作表,然後按 Alt + F11 鍵顯示 Microsoft Visual Basic for Applications 窗口。
2。 點擊 插入 > 模塊 ,在 Microsoft Visual Basic for Applications 窗口。
3.然後將以下代碼複製並粘貼到新的空白模塊腳本中。
VBA:比較兩列後,在next列中列出重複項
Sub ExtendOffice_FindMatches()
'UpdatebyKutools
Dim xRg, xRgC1, xRgC2, xRgF1, xRgF2 As Range
Dim xIntSR, xIntER, xIntSC, xIntEC As Integer
On Error Resume Next
SRg:
Set xRgC1 = Application.InputBox("Select first column:", "Kutools for Excel", , , , , , 8)
If xRgC1 Is Nothing Then Exit Sub
If xRgC1.Columns.Count <> 1 Then
MsgBox "Please select single column"
GoTo SRg
End If
SsRg:
Set xRgC2 = Application.InputBox("Select the second column:", "Kutools for Excel", , , , , , 8)
If xRgC2 Is Nothing Then Exit Sub
If xRgC2.Columns.Count <> 1 Then
MsgBox "Please select single column"
GoTo SsRg
End If
Set xWs = xRg.Worksheet
For Each xRgF1 In xRgC1
For Each xRgF2 In xRgC2
If xRgF1.Value = xRgF2.Value Then xRgF2.Offset(0, 1) = xRgF1.Value
Next xRgF2
Next xRgF1
End Sub
4。 按 F5 鍵運行程式碼,會一一彈出兩個對話框,供您分別選擇兩列。 然後點擊 OK > OK.
匹配項已自動列在兩列的右列。
範例1:比較兩列並提取完全匹配的數據
例如,有兩個表,現在您要比較B列和E列,然後從C列中找到相對價格,並將它們返回到F列中。
這裡介紹了一些有用的公式來解決這項工作。
在單元格F2(您要在其中放置返回值的單元格)中,使用以下公式之一:
=VLOOKUP(E2,$B$2:$C$8,2,0)
Or
=INDEX($B$2:$C$8,MATCH(E2,$B$2:$B$8,0),2)
媒體推薦 Enter 鍵,並且找到第一個值。 然後將自動填充手柄向下拖動到單元格F6,已提取所有值。
- 公式不支援區分大小寫。
- 公式中的數字 2 表示您在表數組的第二列中找到匹配的值。
- 如果公式找不到相對值,則傳回錯誤值#N/A。
如果您對公式感到困惑,可以嘗試使用方便的工具- 公式助手 of Excel的Kutools,其中包含用於解決Excel中大多數問題的多個公式。 使用它,您只需要選擇範圍,而無需記住公式的用法。 立即下載並試用!
範例2:比較兩列並拉出部分相符的數據
如果兩個比較列之間存在一些細微差異,如下圖所示,則上述方法不起作用。
在單元格F2(您要在其中放置返回值的單元格)中,使用以下公式之一:
=VLOOKUP("*"&E2&"*",$B$2:$C$8,2,0)
Or
=INDEX($B$2:$C$8,MATCH("*"&E2&"*",$B$2:$B$8,0),2)
媒體推薦 Enter 鍵,並且找到第一個值。 然後將自動填充手柄向下拖動到單元格F6,已提取所有值。
- 公式不支援區分大小寫。
- 公式中的數字 2 表示您在表數組的第二列中找到匹配的值。
- 如果公式找不到相對值,則傳回錯誤值#N/A。
- 公式中的*為通配符,用於表示任意字元或字串。
比較兩列並找出缺少的資料點
假設有兩列,列B較長,列C較短,如下圖所示。 與B列相比,如何找出C列中丟失的數據?
範例 1:比較兩列並找出缺少的資料點
如果只想在比較兩列後確定缺少哪些數據,則可以使用以下公式之一:
=ISERROR(VLOOKUP(B2,$C$2:$C$10,1,0))
Or
=NOT(ISNUMBER(MATCH(B2,$C$2:$C$10,0)))
媒體推薦 Enter 鍵,然後將自動填充手柄拖到單元格D10上。 現在,如果數據同時在B列和C列中,則公式返回FALSE,如果數據僅在B列中但在C列中未命中,則公式返回TRUE。
範例 2:找到缺少的資料點並將其列出在另一列中(使用方便的工具)
如果您想在比較兩列之後對丟失的數據進行一些後續操作,例如在另一列中列出丟失的數據或在較短的列下方補充丟失的數據,則可以嘗試使用方便的工具-選擇相同和不同的單元格 of Excel的Kutools.
安裝 Kutools for Excel 後點擊此處成為Trail Hunter 庫工具 > 選擇 > 選擇相同和不同的單元格。 然後在「選擇相同和不同儲存格」對話方塊中,執行以下操作:
- 在 尋找價值 部分中,選擇包含完整列表的較長列。
- 在 根據 部分中,選擇缺少某些數據的較短的列。
- 選擇 每一行 選項。
- 選擇 不同的價值觀 選項。 點擊 OK.
彈出對話框提醒您缺少資料的數量,點擊 OK 關閉它。 然後選擇丟失的數據。
現在你可以按 Ctrl + C 鍵複製選定的丟失數據,然後按粘貼它們 Ctrl + V 您可以根據需要在較短的列或另一個新列下面的鍵。
- 勾選 不區分大小寫 “選擇相同和不同的單元格”對話框中的“選項”將比較兩個區分大小寫的列。
- 該工具支持比較不同工作表中的兩列。 按此處了解有關選擇相同和不同單元格的更多詳細信息.
- 如果您對這個工具有興趣, 點擊此處免費下載並試用 30 天。
範例 3:比較兩列並在下面列出缺少的數據
如果要在比較兩列之後在較短的列下面列出丟失的數據,則INDEX數組公式可以為您提供幫助。
在較短列的下面單元格中,假設單元格C7,鍵入以下公式:
=INDEX($B$2:$B$10,MATCH(TRUE,ISNA(MATCH($B$2:$B$10,$C$2:C6,0)),0))
媒體推薦 Shift + Ctrl + Enter 鍵以獲取第一個丟失的數據,然後向下拖動自動填充手柄,直到它返回錯誤值#N / A。
然後,您可以刪除錯誤值,並且所有缺少的數據都已列在較短的列下面。
使用通配符比較兩列
假設這裡是B列中的數據列表,並且您要計算D列中包含“ Apple”或“ Candy”的單元格,如下所示:
要計算一個單元格是否包含一個或多個值,可以使用帶通配符的公式來解決此問題。
=SUM(COUNTIF(B2,"*" & $D$2:$D$3 & "*"))
媒體推薦 Shift + Ctrl + Enter 鍵進行第一次檢查,然後將自動填充手柄向下拖動到單元格F8。
如果要計算包含D列中值的單元格總數,請使用以下單元格F8中的公式:
- 您也可以使用公式來計算儲存格是否包含另一列中的值
此公式只需按 Enter 鍵,然後拖曳自動填滿手柄即可。=SUMPRODUCT(COUNTIF(B2,"*" &$D$2:$D$3& "*"))
- 公式中,*為通配符,表示任意字元或字串。
範例 1:比較兩列是否大於或小於
您可以使用簡單的公式快速查找每行中的日期1是否晚於日期2。
=IF(B2>C2,"Yes","No")
媒體推薦 Enter 鍵以獲取第一個比較結果,然後將自動填充手柄拖到單元格C6上以獲取所有結果。
- 在 Excel 中,日期儲存為數位系列,它們實際上是數字。 因此,您可以應用公式直接比較日期。
- 如果要比較每行中日期 1 是否早於日期 2,請將公式中的符號 > 變更為 <。
範例 2:比較兩列,如果大於或小於則格式
如果要反白日期 1 列中大於日期 2 的儲存格,可以使用 Excel 中的條件格式功能。
1.在B列(日期1)中選擇日期,然後單擊 首頁 > 條件格式 > 新規則.
2。 在裡面 新格式規則 對話框中選擇 使用公式來確定要格式化的單元格 ,在 選擇規則類型 部分,然後鍵入公式
=$B2>$C2
進入的文本框 當公式成立時,設定值的格式。
=$B2<$C2.
3。 點擊 格式 按鈕開啟「設定儲存格格式」對話框,然後根據需要選擇格式類型。 點選 OK >好。
4. 然後,Date1 列中大於 Date2 列中的儲存格已被反白顯示。
比較兩列併計算匹配或差異
下面的數據集是比較和計數匹配或差異的示例。
SUMPRODUCT公式可以快速計算兩列中的匹配項。
=SUMPRODUCT(--(ISNUMBER(MATCH(B2:B8,C2:C8,0))))
媒體推薦 Enter 獲得結果的關鍵。
有關計算匹配和差異的更多方法,請訪問此頁面: 計算Excel中兩列之間的所有匹配/重複項
比較兩個範圍
現在,您將在閱讀上述方法後知道如何比較兩列。 但是,在某些情況下,您可能需要比較兩個範圍(兩個具有多個列的序列),可以使用上述方法(公式或條件格式)逐列比較它們,但是這裡引入了一個方便的工具– Excel的Kutools 無需公式即可快速解決不同情況下的這項工作。
範例 1:按儲存格比較兩個範圍
這是需要按單元格進行比較的兩個範圍,您可以使用 選擇相同和不同的單元格 的效用 Excel的Kutools 處理它。
免費安裝 Kutools for Excel 後點擊此處成為Trail Hunter 庫工具 > 選擇 > 選擇相同和不同的單元格。 然後在彈出的「選擇相同和不同儲存格」對話方塊中,執行以下操作:
- 在 在中查找值 部分,比較兩個範圍後,選擇要查找匹配或差異的範圍。
- 在 根據 部分,選擇用於比較範圍的其他範圍。
- In 基於 部分中,選擇 單細胞.
- 在 發現 部分中,選擇要選擇或突出顯示的單元格類型。
- 在 結果處理 部分,您可以透過填滿背景顏色或字體顏色來突出顯示單元格,如果不需要突出顯示,請不要勾選複選框。 點選 OK.
彈出一個對話框,提醒已選取了多少個儲存格/行,按一下 OK 關閉它。
- 選擇並突顯獨特的價值
- 選擇並反白顯示重複值
- 如果您想按行比較兩個範圍,您也可以套用選擇相同和不同儲存格功能,但在這種情況下,請選擇 每一行 選項。
- 勾選 不區分大小寫 “選擇相同和不同的單元格”對話框中的“選項”將比較兩個區分大小寫的列。
- 該工具支持比較不同工作表中的兩列。 按此處了解有關選擇相同和不同單元格的更多詳細信息.
- 如果您對這個工具有興趣, 點擊此處免費下載並試用 30 天。
範例 2:如果資料順序相同,則比較兩個範圍
假設範圍F2:H7是一個模型,現在您要根據范圍F2:H7查找範圍B2:D7中的數據是否正確。
在這種情況下, 比較單元格 of Excel的Kutools 可以幫你。
免費安裝 Kutools for Excel 後點擊此處成為Trail Hunter 庫工具 > 比較單元格。 然後在比較儲存格對話框中,設定如下:
- 選擇兩個範圍進入 在中查找值 和 根據 盒分開。
- 選擇您要在其中反白顯示的儲存格類型 發現 部分。
- 選擇 突出顯示類型 ,在 結果處理 部分。 按一下“確定”。
彈出一個對話框,提醒已選取了多少個儲存格,按一下 OK 關閉它。 現在,已選擇並突出顯示與其他範圍內的單元格不同的單元格。
- 勾選 不區分大小寫 選項將比較兩個區分大小寫的單元格。
- 按此處了解有關比較單元格的更多詳細信息。 如果您對這個工具有興趣, 點擊此處免費下載並試用 30 天。
上面提供的資訊詳細介紹如何比較 Excel 中的列。 我希望您發現它有價值且有益。 更多可改變資料處理方式的寶貴 Excel 提示和技巧, 潛入這裡.
最佳辦公生產力工具
🤖 | Kutools 人工智慧助手:基於以下內容徹底改變數據分析: 智慧執行 | 生成代碼 | 建立自訂公式 | 分析數據並產生圖表 | 呼叫 Kutools 函數... |
熱門特色: 尋找、突出顯示或識別重複項 | 刪除空白行 | 合併列或儲存格而不遺失數據 | 沒有公式的回合 ... | |
超級查詢: 多條件VLookup | 多值VLookup | 跨多個工作表的 VLookup | 模糊查詢 .... | |
高級下拉列表: 快速建立下拉列表 | 依賴下拉列表 | 多選下拉列表 .... | |
欄目經理: 新增特定數量的列 | 移動列 | 切換隱藏列的可見性狀態 | 比較範圍和列 ... | |
特色功能: 網格焦點 | 設計圖 | 大方程式酒吧 | 工作簿和工作表管理器 | 資源庫 (自動文字) | 日期選擇器 | 合併工作表 | 加密/解密單元格 | 按清單發送電子郵件 | 超級濾鏡 | 特殊過濾器 (過濾粗體/斜體/刪除線...)... | |
前 15 個工具集: 12 文本 工具 (添加文本, 刪除字符,...) | 50+ 圖表 類型 (甘特圖,...) | 40+ 實用 公式 (根據生日計算年齡,...) | 19 插入 工具 (插入二維碼, 從路徑插入圖片,...) | 12 轉化 工具 (數字到單詞, 貨幣兌換,...) | 7 合併與拆分 工具 (高級合併行, 分裂細胞,...) | ... 和更多 |
使用 Kutools for Excel 增強您的 Excel 技能,體驗前所未有的效率。 Kutools for Excel 提供了 300 多種進階功能來提高生產力並節省時間。 點擊此處獲取您最需要的功能...
Office選項卡為Office帶來了選項卡式界面,使您的工作更加輕鬆
- 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,發布者,Access,Visio和Project。
- 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
- 將您的工作效率提高 50%,每天為您減少數百次鼠標點擊!