實用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,"")

    如果不知道答案，歡迎觀看教學《卡號離奇減少表哥冤枉被罰——Excel，原來你有真假重複！ 》。

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教學

以上是實用Excel技巧分享：幾種不同情況的數據對比的詳細內容。更多資訊請關注PHP中文網其他相關文章！