核對(duì)數(shù)據(jù)或者說(shuō)對(duì)比數(shù)據(jù)是Excel表妹表哥們常做的一件事。有人為此耽誤了吃飯，有人為此被領(lǐng)導(dǎo)批，有人為此被男友埋怨等等。這里總結(jié)了12種不同情況的數(shù)據(jù)對(duì)比，并都給出了快速方法，足夠應(yīng)對(duì)工作的需要了。親，如果今后再因?yàn)楹藢?duì)數(shù)據(jù)而加班，隔壁店里有豆腐！

    日常工作不時(shí)會(huì)需要對(duì)比數(shù)據(jù)，查找差異，查找重復(fù)值等。有的是對(duì)比同一工作表中的數(shù)據(jù)，有的是對(duì)比不同工作表之間的數(shù)據(jù)。希望接下來(lái)介紹的多種Excel數(shù)據(jù)對(duì)比方法，讓大家能在不同情況下都能快速完成數(shù)據(jù)的對(duì)比。

第一部分：同一表內(nèi)數(shù)據(jù)比較

1.嚴(yán)格比較兩列數(shù)據(jù)是否相同

    所謂嚴(yán)格比較就是指數(shù)據(jù)按位置對(duì)應(yīng)比較。

1）快捷鍵對(duì)比Ctrl+

    如下圖所示，選中需要對(duì)比的兩列數(shù)據(jù)A列和B列，然后按下快捷鍵Ctrl+，不同的數(shù)據(jù)B5、B9、B10、B15則會(huì)處于選中狀態(tài)。

2）定位法對(duì)比（快捷鍵F5或Ctrl+G）

    以下表為例，框選A列和B列的列標(biāo)題快速選擇兩列數(shù)據(jù)，然后按快捷鍵F5（或Ctrl+G）即可調(diào)出定位窗口，選擇定位條件為“行內(nèi)容差異單元格”，單擊“確定”按鈕，不同的數(shù)據(jù)會(huì)處于選中狀態(tài)。

注意：

    以上兩種方法可以快速比對(duì)兩列數(shù)據(jù)的差異但均不會(huì)區(qū)分字母大小寫(xiě)。

3）IF函數(shù)對(duì)比

    （1）不需要區(qū)分字母大小寫(xiě)的if函數(shù)對(duì)比

    下表A、B兩列都是數(shù)字，不存在字母，不需要區(qū)分大小寫(xiě)。

    可以在C2單元格輸入公式=IF(A2=B2,"相同","不相同")，輸入好之后拉動(dòng)手柄向下拖動(dòng)，直到本列數(shù)據(jù)截止，相同不同結(jié)果一目了然，如下表。

    （2）區(qū)分字母大小寫(xiě)的if函數(shù)對(duì)比

    如遇對(duì)比數(shù)據(jù)含字母，并且需要區(qū)分大小寫(xiě)，則上述公式不能準(zhǔn)確對(duì)比。此時(shí)可將C2公式更改為=IF(EXACT(A2,B2)=TRUE,"相同","不相同")，然后下拉填充公式，最終如下圖所示。

2.找出兩列數(shù)據(jù)的重復(fù)值

1）IF + MATCH函數(shù)查找重復(fù)值

    現(xiàn)在要對(duì)下表找出連續(xù)兩個(gè)季度中獎(jiǎng)的名單，又有什么方法呢？

    其實(shí)，就是要通過(guò)對(duì)比A列與B列，找出重復(fù)值。我們可以用IF+MATCH函數(shù)組合公式，在C2單元格輸入公式=IF(ISERROR(MATCH(A2,$B$2:$B$25,0)),"",A2)，然后下拉復(fù)制公式，則可完成查找任務(wù)。對(duì)比查找結(jié)果見(jiàn)下表：

公式解析：

    MATCH用于返回要查找的數(shù)據(jù)A2在區(qū)域$B$2:$B$25中的位置。如果查到會(huì)返回一個(gè)行號(hào)（表示有重復(fù)），沒(méi)有查到則返回錯(cuò)誤#N/A（表示無(wú)重復(fù)）。

    公式中加入ISERROR函數(shù)，用于判斷MATCH返回的值是否是個(gè)錯(cuò)誤#N/A，是錯(cuò)誤#N/A則返回TRUE，不是錯(cuò)誤#N/A則范圍FALSE。

    最外圍的IF函數(shù)，根據(jù)ISERROR（MATCH（））是TRUE還是FALSE，返回不同值。如果是TURE（也就是沒(méi)有重復(fù)），則返回空；如果是FALSE，則返回A2。   
 

    如果我們要查找出1季度中獎(jiǎng)但2季度沒(méi)有中獎(jiǎng)的名單，我們就可以將上述函數(shù)公式改成為：=IF(ISERROR(MATCH(A2,$B$2:$B$25,0)), A2, "")。

2）IF+COUNTIF函數(shù)查找重復(fù)值

    下表A、B兩列都是客戶的姓名，需要找到兩列重復(fù)的客戶名稱(chēng)，并在C列標(biāo)識(shí)出來(lái)。

    操作方法為在C2單元格輸入公式=IF(COUNTIF(A:A,B2)=0,"",B2)，然后下拉完成excel兩列數(shù)據(jù)對(duì)比。請(qǐng)看下面演示！

    COUNTIF函數(shù)是對(duì)指定區(qū)域中符合指定條件的單元格計(jì)數(shù)的一個(gè)函數(shù)。

考考你：

    如果上述中對(duì)比的數(shù)值超過(guò)15位，比如對(duì)比的是身份證號(hào)，上述公式是否還可以用？如果上述公式不能用了，改換成以下公式呢？

    =IF(COUNTIF(A: A,B2&"*")=0," ",B2)

或者

    =IF(SUMPRODUCT(1*(A:A=B2)),B2,"")

    如果不知道答案，歡迎觀看教程《卡號(hào)離奇減少表哥冤枉被罰——Excel，原來(lái)你有真假重復(fù)！》。

3）IF+VLOOKUP函數(shù)查找重復(fù)值

    如下表所示，有這樣兩組員工號(hào)。不知道哪些是A、B兩組都有的。我們也可以用if+VLOOKUP函數(shù)公式來(lái)完成比對(duì)。

    在C2單元格中輸入公式：=IF(ISNA(VLOOKUP(A2,$B$2:$B$25,1,))," ",A2)，然后下拉復(fù)制公式，則可找到Excel兩列數(shù)據(jù)中的重復(fù)值。

公式解析：

    ISNA函數(shù)用于判斷值是否為錯(cuò)誤值#N/A(即是值不存在),如果是,則返回TRUE;否則返回FALSE。

    公式里面需要在查找區(qū)域的數(shù)據(jù)前都加上$符號(hào)，固定查找區(qū)域。否則在下拉填充的時(shí)候，查找區(qū)域也會(huì)跟著變化，這將會(huì)影響查找對(duì)比的結(jié)果。

應(yīng)用擴(kuò)展：用Vlookup找不同

    該公式稍作調(diào)整即可在找出不同值，或缺少值、錯(cuò)誤值（非嚴(yán)格比較，不講究位置或順序）。譬如上面的B組是標(biāo)準(zhǔn)數(shù)據(jù)，要把A組中與B組不同的值找出來(lái)，公式可以寫(xiě)成：

    =IF(ISNA(VLOOKUP(A2,$B$2:$B$25,1,)), A2, " ")

第二部分：跨表數(shù)據(jù)比較

1.嚴(yán)格比較兩個(gè)表的數(shù)據(jù)是否相同

    當(dāng)兩個(gè)格式完全一樣的表格進(jìn)行對(duì)比查找差異時(shí)，可以采用下方的方法。

1）條件格式法對(duì)比兩表差異

    現(xiàn)以下面兩表為例，要比對(duì)出哪些數(shù)值存在差異并突出顯示出來(lái)。

    首先，先選中一個(gè)表，新建規(guī)則，并選擇“使用公式確定要設(shè)置格式的單元格”，然后輸入=A9<>A1 , 對(duì)相對(duì)應(yīng)的單元格進(jìn)行判斷，判斷其是否相等。請(qǐng)看下面演示！

溫馨提示：

    如果要清除條件格式，先選中要清除格式的單元格區(qū)域，依次執(zhí)行“開(kāi)始”- “條件格式”–“ 清除規(guī)則”–“ 清除所選單元格的規(guī)則”（或清除整個(gè)工作表的規(guī)則）。

2）選擇性粘貼法對(duì)比兩表差異（該法只適合數(shù)字的比較）

    如下圖所示，兩表格式相同姓名排序相同，要求快速找出兩個(gè)表格的數(shù)據(jù)差異。

    復(fù)制其中一個(gè)數(shù)值區(qū)域，然后按快捷鍵Ctrl+Alt+V選擇性粘貼，設(shè)置為“減”運(yùn)算，單擊“確定”后，非0部分即差異所在。請(qǐng)看下面演示！

    此方法只適合快速定位差異數(shù)據(jù)，看一眼就算的那種，因?yàn)闀?huì)破壞原數(shù)據(jù)表格。

3）IF函數(shù)對(duì)比兩表差異

    如下圖所示，表a和表b是格式完全相同的表格，現(xiàn)在要求核對(duì)兩個(gè)表格中的數(shù)值是否完全一致，并且要能直觀顯示差異情況。

    操作方法為，新建一個(gè)空白工作表，在A1單元格輸入公式=IF(表a!A1<>表b!A1, "表a:"& 表a!A1&" vs表b:"& 表b!A1,"")，然后在區(qū)域范圍內(nèi)復(fù)制填充公式。請(qǐng)看下面演示！

2.按條件找出兩個(gè)表數(shù)據(jù)的差異

1）單條件找出兩個(gè)表數(shù)據(jù)的差異

    譬如下面是分別由兩人匯總的成績(jī)表，表格格式一致，但姓名排序不一樣。現(xiàn)在需要對(duì)比兩張表，核實(shí)匯總成績(jī)是否正確。

    這類(lèi)數(shù)據(jù)核對(duì)屬于單條件核對(duì)。因?yàn)槭遣煌藚R總的，所以除了按姓名核對(duì)分?jǐn)?shù)外，還需要把姓名對(duì)不上的也標(biāo)出來(lái)。我們采用條件格式來(lái)完成。

    需要建立兩個(gè)條件格式。

    第一個(gè)格式：找出姓名差異

    （1）選中第2個(gè)表姓名欄數(shù)據(jù)，選擇“條件格式”中的“新建規(guī)則”，在彈出的對(duì)話框中選擇“使用公式確定要設(shè)置格式的單元格”，然后輸入公式=COUNTIF($A$2:$A$10,A14)=0

    （2）單擊格式按鈕，選擇一種填充顏色。

    確定后我們就完成第一個(gè)格式設(shè)定。

    第二個(gè)格式：找出同姓名的分?jǐn)?shù)差異。

    （1）選中第2個(gè)表中所有分?jǐn)?shù)單元格，新建規(guī)則，使用公式確定規(guī)則，輸入的公式為= =VLOOKUP($A14,$A$1:$I$10,COLUMN(B1),0)-B14

    （2）單擊格式按鈕，選擇一種填充顏色。

    確定后完成分?jǐn)?shù)核對(duì)?？偟暮藢?duì)結(jié)果如下：

    橙色表明“劉小廣”這個(gè)姓名與另一個(gè)表對(duì)不上，可能是名字寫(xiě)錯(cuò)了；藍(lán)綠色表明楊文雯的語(yǔ)文分?jǐn)?shù)、何叢良的英語(yǔ)分?jǐn)?shù)、候嫚嫚的語(yǔ)文分?jǐn)?shù)對(duì)不上，可能存在錯(cuò)誤。

2）多條件找出兩個(gè)表數(shù)據(jù)的差異

    如下圖所示，要求核對(duì)兩表中同一倉(cāng)庫(kù)同一產(chǎn)品的數(shù)量差異，結(jié)果顯示在D列。用什么方法可以完成呢？好頭疼呀！

    在D15單元格中輸入以下公式：

    =SUMPRODUCT(($A$3:$A$11=A15)*($B$3:$B$11=B15)*$C$3:$C$11)-C15

    然后下拉完成該數(shù)值的對(duì)比。請(qǐng)看請(qǐng)看??！

    以上就是今天的分享，一起動(dòng)手練起來(lái)吧！

本文配套的練習(xí)課件請(qǐng)加入QQ群：264539405下載。

    做Excel高手，快速提升工作效率，部落窩教育《一周Excel直通車(chē)》視頻和《Excel極速貫通班》直播課全心為你！

掃下方二維碼關(guān)注公眾號(hào)，可隨時(shí)隨地學(xué)習(xí)Excel：

相關(guān)推薦：

    查找重復(fù)數(shù)據(jù)《countif函數(shù)的使用方法以及countif函數(shù)查重復(fù)等5個(gè)案例分享》

    當(dāng)心數(shù)據(jù)的真假重復(fù)《卡號(hào)離奇減少表哥冤枉被罰——Excel，原來(lái)你有真假重復(fù)！》

    你需要一個(gè)強(qiáng)大的SUMPRODUCT函數(shù)《加了*的 SUMPRODUCT函數(shù)無(wú)所不能》

版權(quán)說(shuō)明：
    部落窩原創(chuàng)教程。主創(chuàng)作者簡(jiǎn)，編寫(xiě)了11種方法，其次是小雅，編寫(xiě)了1種方法。