實用Excel技巧分享：幾種不同情況的資料對比
日常工作不時會需要對比資料，查詢差異，查詢重複值等。有的是對比同一工作表中的資料，有的是對比不同工作表之間的資料。這裡總結了多種不同情況的資料對比，並都給出了快速方法，讓大家能在不同情況下都能快速完成資料的對比。
第一部分：同一表內資料比較
1.嚴格比較兩列資料是否相同
所謂嚴格比較就是指資料按位元置對應比較。
1）快捷鍵對比Ctrl+
如下圖所示，選中需要對比的兩列資料A列和B列，然後按下快捷鍵Ctrl+，不同的資料B5、B9、B10、B15則會處於選中狀態。
2）定位法對比（快捷鍵F5或Ctrl+G）
以下表為例，框選A列和B列的列標題快速選擇兩列資料，然後按快捷鍵F5（或Ctrl+G）即可調出定位視窗，選擇定位條件為「行內容差異單元格」，單擊「確定」按鈕，不同的資料會處於選中狀態。
注意：
以上兩種方法可以快速比對兩列資料的差異但均不會區分字母大小寫。
3）IF函數對比
（1）不需要區分字母大小寫的if函數對比
下表A、B兩列都是數位，不存在字母，不需要區分大小寫。
可以在C2單元格輸入公式=IF(A2=B2,"相同","不相同")，輸入好之後拉動手柄向下拖動，直到本列資料截止，相同不同結果一目瞭然，如下表。
（2）區分字母大小寫的if函數對比
如遇對比資料含字母，並且需要區分大小寫，則上述公式不能準確對比。此時可將C2公式更改為=IF(EXACT(A2,B2)=TRUE,"相同","不相同")，然後下拉填充公式，最終如下圖所示。
2.找出兩列資料的重複值
1）IF + MATCH函數查詢重複值
現在要對下表找出連續兩個季度中獎的名單，又有什麼方法呢？
其實，就是要通過對比A列與B列，找出重複值。我們可以用IF+MATCH函陣列合公式，在C2單元格輸入公式=IF(ISERROR(MATCH(A2,$B$2:$B$25,0)),"",A2)，然後下拉複製公式，則可完成查詢任務。對比查詢結果見下表：
公式解析：
MATCH用於返回要查詢的資料A2在區域$B$2:$B$25中的位置。如果查到會返回一個行號（表示有重複），沒有查到則返回錯誤#N/A（表示無重複）。
公式中加入ISERROR函數，用於判斷MATCH返回的值是否是個錯誤#N/A，是錯誤#N/A則返回TRUE，不是錯誤#N/A則範圍FALSE。
最外圍的IF函數，根據ISERROR（MATCH（））是TRUE還是FALSE，返回不同值。如果是TURE（也就是沒有重複），則返回空；如果是FALSE，則返回A2。
如果我們要查詢出1季度中獎但2季度沒有中獎的名單，我們就可以將上述函數公式改成為：=IF(ISERROR(MATCH(A2,$B$2:$B$25,0)),
A2, "")。
2）IF+COUNTIF函數查詢重複值
下表A、B兩列都是客戶的姓名，需要找到兩列重複的客戶名稱，並在C列標識出來。
操作方法為在C2單元格輸入公式=IF(COUNTIF(A:A,B2)=0,"",B2)，然後下拉完成excel兩列資料對比。請看下面演示！
COUNTIF函數是對指定區域中符合指定條件的單元格計數的一個函數。
考考你：
如果上述中對比的數值超過15位，比如對比的是身份證號，上述公式是否還可以用？如果上述公式不能用了，改換成以下公式呢？
=IF(COUNTIF(A: A,B2&"*")=0," ",B2)
或者
=IF(SUMPRODUCT(1*(A:A=B2)),B2,"")
如果不知道答案，歡迎觀看教學《》。
3）IF+VLOOKUP函數查詢重複值
如下表所示，有這樣兩組員工號。不知道哪些是A、B兩組都有的。我們也可以用if+VLOOKUP函數公式來完成比對。
在C2單元格中輸入公式：=IF(ISNA(VLOOKUP(A2,$B$2:$B$25,1,))," ",A2)，然後下拉複製公式，則可找到Excel兩列資料中的重複值。
公式解析：
ISNA函數用於判斷值是否為錯誤值#N/A(即是值不存在),如果是,則返回TRUE;否則返回FALSE。
公式裡面需要在查詢區域的資料前都加上$符號，固定查詢區域。否則在下拉填充的時候，查詢區域也會跟著變化，這將會影響查詢對比的結果。
應用擴充套件：用Vlookup找不同
該公式稍作調整即可在找出不同值，或缺少值、錯誤值（非嚴格比較，不講究位置或順序）。譬如上面的B組是標準資料，要把A組中與B組不同的值找出來，公式可以寫成：
=IF(ISNA(VLOOKUP(A2,$B$2:$B$25,1,)),
A2, " ")
第二部分：跨表資料比較
1.嚴格比較兩個表的資料是否相同
當兩個格式完全一樣的表格進行對比查詢差異時，可以採用下方的方法。
1）條件格式法對比兩表差異
現以下面兩表為例，要比對出哪些數值存在差異並突出顯示出來。
首先，先選中一個表，新建規則，並選擇「使用公式確定要設定格式的單元格」，然後輸入=A9A1 , 對相對應的單元格進行判斷，判斷其是否相等。請看下面演示！
溫馨提示：
如果要清除條件格式，先選中要清除格式的單元格區域，依次執行「開始」- 「條件格式」–「 清除規則」–「 清除所選單元格的規則」（或清除整個工作表的規則）。
2）選擇性貼上法對比兩表差異（該法只適合數位的比較）
如下圖所示，兩表格式相同姓名排序相同，要求快速找出兩個表格的資料差異。
複製其中一個數值區域，然後按快捷鍵Ctrl+Alt+V選擇性貼上，設定為「減」運算，單擊「確定」後，非0部分即差異所在。請看下面演示！
此方法只適合快速定位差異資料，看一眼就算的那種，因為會破壞原資料表格。
3）IF函數對比兩表差異
如下圖所示，表a和表b是格式完全相同的表格，現在要求核對兩個表格中的數值是否完全一致，並且要能直觀顯示差異情況。
操作方法為，新建一個空白工作表，在A1單元格輸入公式=IF(表a!A1表b!A1, "表a:"& 表a!A1&" vs表b:"& 表b!A1,"")，然後在區域範圍內複製填充公式。請看下面演示！
2.按條件找出兩個表資料的差異
1）單條件找出兩個表資料的差異
譬如下面是分別由兩人彙總的成績表，表格格式一致，但姓名排序不一樣。現在需要對比兩張表，核實彙總成績是否正確。
這類資料核對屬於單條件核對。因為是不同人彙總的，所以除了按姓名核對分數外，還需要把姓名對不上的也標出來。我們採用條件格式來完成。
需要建立兩個條件格式。
第一個格式：找出姓名差異
（1）選中第2個表姓名欄資料，選擇「條件格式」中的「新建規則」，在彈出的對話方塊中選擇「使用公式確定要設定格式的單元格」，然後輸入公式=COUNTIF($A$2:$A$10,A14)=0
（2）單擊格式按鈕，選擇一種填充顏色。
確定後我們就完成第一個格式設定。
第二個格式：找出同姓名的分數差異。
（1）選中第2個表中所有分數單元格，新建規則，使用公式確定規則，輸入的公式為= =VLOOKUP($A14,$A$1:$I$10,COLUMN(B1),0)-B14
（2）單擊格式按鈕，選擇一種填充顏色。
確定後完成分數核對。總的核對結果如下：
橙色表明「劉小廣」這個姓名與另一個表對不上，可能是名字寫錯了；藍綠色表明楊文雯的語文分數、何叢良的英語分數、候嫚嫚的語文分數對不上，可能存在錯誤。
2）多條件找出兩個表資料的差異
如下圖所示，要求核對兩表中同一倉庫同一產品的數量差異，結果顯示在D列。用什麼方法可以完成呢？好頭疼呀！
在D15單元格中輸入以下公式：
=SUMPRODUCT(($A$3:$A$11=A15)*($B$3:$B$11=B15)*$C$3:$C$11)-C15
然後下拉完成該數值的對比。請看請看！！
以上就是今天的分享，一起動手練起來吧
相關學習推薦：
以上就是實用Excel技巧分享：幾種不同情況的資料對比的詳細內容，更多請關注TW511.COM其它相關文章！