Excel表格中數據比對和查找的幾種技巧

2021-02-11 和老菜鳥一起學Excel

想每天都能收這樣的文章嗎？微信掃描「二維碼」一鍵關注「老菜鳥的巢」

經常被人問到怎麼對兩份Excel數據進行比對，提問的往往都很籠統；在工作中，有時候會需要對兩份內容相近的數據記錄清單進行比對，需求不同，比對的的目標和要求也會有所不同。根據幾個常見的應用環境介紹一下Excel表格中數據比對和查找的技巧。

應用案例一：比對取出兩表的交集(相同部分)

Sheet1中包含了一份數據清單A，sheet2中包含了一份數據清單B，要取得兩份清單共有的數據記錄(交集)，也就是要找到兩份清單中的相同部分。


方法1：高級篩選

高級篩選是處理重複數據的利器。

選中第一份數據清單所在的數據區域，在功能區上依次單擊【數據】——【高級】(2003版本中菜單操作為【數據】——【篩選】——【高級篩選】)，出現【高級篩選】對話框。

在對話框中，篩選【方式】可以根據需求選取，例如這裡選擇「將篩選結果複製到其他位置」；【列表區域】就是之前所選中的第一份數據清單A所在的單元格區域；【條件區域】則選取另外那份清單B所在的單元格區域。如下圖所示：

點擊【確定】按鈕後，就可以直接得到兩份清單的交集部分，效果如下圖。其中兩個清單中雖然都有【西瓜】和【菠蘿】，但是由於數量不一致，所以沒有作為相同記錄被提取出來。

這個操作的原理，就是利用了高級篩選功能對於匹配指定條件的記錄進行篩選的功能，把兩張表中的任意一張作為條件區域，在另外一張表中就能篩選出與之相匹配的記錄，忽略掉其他不相關的記錄。

需要注意的是，使用高級篩選的時候務必注意兩個清單的標題行要保持一致(高級篩選中作為條件區域的前提)，並且在選取【列表區域】和【條件區域】的時候都要把標題行的範圍包含在其中。



方法2：公式法

使用公式進行比對的方法有很多，如果是單列數據對比比較常用的函數是COUNTIF函數，如果是多列數據記錄對比，SUMPRODUCT函數比較勝任。

在其中一張清單的旁邊輸入公式：

=SUMPRODUCT((A2&B2=Sheet2!A$2:A$13&Sheet2!B$2:B$13)*1)

並向下複製填充。其中的Sheet2!A$1:A$13和Sheet2!B$2:B$13是另一張清單中的兩列數據區域，需要根據實際情況修改。公式結果等於1的記錄就是兩個清單的交集部分，如下圖所示：



應用案例二：取出兩表的差異記錄

要在某一張表裡取出與另一張表的差異記錄，就是未在另外那張清單裡面出現的部分，其原理和操作都和上面第一種場景的差不多，所不同的只是篩選後所選取的集合正好互補。

方法1：高級篩選

先將兩個清單的標題行更改使之保持一致，然後選中第一份數據清單所在的數據區域，在功能區上依次單擊【數據】——【高級】，出現【高級篩選】對話框。在對話框中，篩選方式選擇「在原有區域顯示篩選結果」；【列表區域】和【條件區域】的選取和前面場景1完全相同，如下圖所示：

點擊【確定】完成篩選，將篩選出來的記錄全部選中按【Del】鍵刪除(或做標記)，然後點擊【清除】按鈕(2003版本中為【全部顯示】按鈕)就可以恢復篩選前的狀態得到最終的結果，如下圖所示：


方法2：公式法

使用公式的話，方法和場景1完全相同，只是最後需要提取的是公式結果等於0的記錄。



應用案例三：取出關鍵字相同但數據有差異的記錄

前面的兩份清單中，【西瓜】和【菠蘿】的貨品名稱雖然一致，但在兩張表上的數量卻不相同，在一些數據核對的場景下，就需要把這樣的記錄提取出來。

方法1：高級篩選

高級篩選當中可以使用特殊的公式，使得高級篩選的功能更加強大。

第一張清單所在的sheet裡面，把D1單元格留空，在D2單元格內輸入公式：

=VLOOKUP(A2，Sheet2!$A$2:$B$13，2，0)<>B2

然後在功能區上依次單擊【數據】——【高級】，出現【高級篩選】對話框。在對話框中，篩選方式選擇「在原有區域顯示篩選結果」；【列表區域】選取第一張清單中的完整數據區域，【條件區域】則選取剛剛特別設計過的D1:D2單元格區域，如下圖所示：

點擊【確定】按鈕以後，就可以得到篩選結果，就是第一張中貨品名稱與第二張表相同但數量卻不一致的記錄清單，如下圖所示：

同樣的，照此方法在第二張清單當中操作，也可以在第二張清單中找到其中與第一張清單數據有差異的記錄。

這個方法是利用了高級篩選中可以通過自定義公式來添加篩選條件的功能。


方法2：公式法

使用公式還是可以利用前面用到的SUMPRODUCT函數，在其中一張清單的旁邊輸入公式：

=SUMPRODUCT((A2=Sheet2!A$2:A$13)*(B2<>Sheet2!B$2:B$13))

並向下複製填充。公式中的包含了兩個條件，第一個條件是A列數據相同，第二個條件是B列數據不相同。公式結果等於1的記錄就是兩個清單中數據有差異的記錄，如下圖所示。這個例子中也可以使用更為人熟知的VLOOKUP函數來進行匹配查詢，但是VLOOKUP只適合單列數據的匹配，如果目標清單中包含了更多欄位數據的差異對比，還是SUMPRODUCT函數的擴展性更強一些。

成者高速智能成冊掃描儀十大顛覆性功能：曲面展平、淨化底色、糾偏裁邊、不變形、高速掃描、精度高、安全保密、立體實物掃描、識別34種語言、一鍵OCR轉換word。諮詢電話/微信：18893103211

極米無屏電視十大顛覆性功能：電視、電腦、2D轉3D、3D大片、手機電腦同屏，遊戲、學習、音樂、投影。諮詢電話/微信：18893103211

相關焦點

  • 巧用EXCEL中的VBA和函數結合整理比對多種分散數據
    （excel表格）是非常零散的數據，而且其中部分單位數據欄位名不在同一列，欄位名也不盡相同，如有的欄位名為「身份證號」，有的欄位名為「身份證號碼」，有的在第2列，有的在第3列，要是用SQL整理分析數據，必須要對這些零散的excel表格進行數據整理，工作量非常大，耗時費力，效率低下，由於審計組的審計目的只是要比對需要核實的人員信息是否在這些零散的excel表格中出現，如何用最少的時間，最小的工作量完成此項任務就擺在了審計組面前
  • 讓Excel表格中空白地方數據變成0?這個技巧超實用!
    其中在線上辦公中用得最多的軟體就是excel表格了，無論是數據匯報、成績統計，還是信息公布，excel都在校園教學辦公中扮演著重要的角色，所以掌握excel的製作技巧對於老師們來說也是一項亟待提升的技能.
  • excel查找數據就是如此簡單,vlookup函數的模糊查找
    我們在實際工作中，我們經常使用excel表格處理數據，處理數據的方法有很多種，查找數據應該是我們在日常工作中使用頻率比較高的操作，這次我們還是分享查找數據的小技巧，這次是對數據進行模糊查找，我們使用vlookup函數對數據進行模糊查找，下面我們就以實例結合視頻的形式將詳細的操作步驟展示出來
  • Excel中的數據匹配和查找
    在Excel中，提供了多個可以用來進行數據匹配和查找的函數：HLOOKUP、INDEX、LOOKUP、MATCH和VLOOKUP等。
  • excel表格中的index函數在處理數據時的用法
    index函數在excel中被歸類為「查找與引用」函數。該類函數中，有我們熟悉的縱向查找函數vlookup函數，其格式是：「=VLOOKUP（查找的數值，查找的數據表區域，返回目標數值在數據表區域中的列序號，匹配條件）」，該函數返回的是數據表中的數值。
  • Excel vlookup篩選兩列的重複項與查找兩個表格相同數據
    Vlookup函數可用於多種情況查找，篩選重複數據就是其中之一，它既可篩選兩列重複的數據又可查找兩個表格相同的數據。篩選兩列重複數據時，不僅僅是返回一項重複數據，是把所有重複的都標示出來；查找兩表格相同數據時，兩個表格既可以位於同一Excel文檔，又可分別位於兩個Excel文檔，並且也可以標示出所有重複的數據；當查找兩個位於不同Excel文檔中的表格相同數據時，查找範圍需要寫文檔名稱和工作簿名稱，這樣Excel才能找到查找區域。
  • excel數據查找技巧:按時間段進行區域查找數據
    如何根據日期和名稱等多個條件查詢相應時間範圍或者時間段所對應的產品價格、數量呢？品名、編號等都需要精確查找，但不需要對日期進行精確查找，而是查找最接近或等於查找日期的某個時間段。趕緊看看下面的文章吧！學習更多技巧，請收藏關注部落窩教育excel圖文教程。
  • Excel表格中怎麼查找重複項?
    面對大量數據的Excel表格，怎麼查找其中重複的數據呢？一個個Ctrl+F查找太麻煩了。
  • Excel怎麼查找重複數據
    在繪製excel表格後梳理數據時，有時候需要查看重複的對象。那麼，大夥知道excel表格怎麼查找重複的數據嗎？若不確定的話，來看看小編給出的介紹吧。1、在電腦上雙擊打開一個需要查找重複數據的excel表格。
  • Excel表格中最經典的36個小技巧,圖解
    技巧20：同時修改多個工作表技巧21：恢復未保存文件技巧22、給excel文件添加打開密碼技巧23、快速關閉所有excel文件技巧24、製作下拉菜單技巧25、二級聯動下拉技巧27、刪除空白行技巧28、表格只能填寫不能修改技巧29、文字跨列居中顯示技巧30、批註添加圖片技巧31、批量隱藏和顯示批註技巧32、
  • Excel表格中最經典的36個小技巧,全在這兒了
    技巧20：同時修改多個工作表技巧21：恢復未保存文件技巧22、給excel文件添加打開密碼技巧23、快速關閉所有excel文件技巧24、製作下拉菜單技巧25、二級聯動下拉技巧27、刪除空白行技巧28、表格只能填寫不能修改技巧29、文字跨列居中顯示技巧30、批註添加圖片技巧31、批量隱藏和顯示批註技巧32、
  • 如何在excel中篩選直接從別的表格獲取數據
    如何在excel中篩選直接從別的表格獲取數據我想要先說的是：我認真製作的教程喜歡得到的大家的喜歡和支持，如果你們想要源文件我可以提供給你們，例子都是我平時點滴時間製作出來，很不容用，大家多多評論支持下。不勝感激！
  • Excel表格中最經典的36個技巧,全在這兒了.(上)
    、表格只能填寫不能修改技巧29、文字跨列居中顯示技巧30、批註添加圖片技巧31、批量隱藏和顯示批註技巧36、合併單元格篩選技巧1、單元格內強制換行在單元格中某個字符後按alt+回車鍵，即可強制把光標換到下一行中。
  • 職場人員常用的excel操作技巧,查找與替換的使用
    我們在實際工作中，當我們需要處理和分析大量數據的時候，我們通常選擇使用excel表格來處理這些數據，excel裡有很多特別實用的功能，今天我們要分享的這個功能對於大部分職場人員來說經常會使用到，這個excel功能就是查找與替換的操作技巧，當我們的表格裡有很多數據的時候，我們需要查找到我們需要的數據
  • 給表格點顏色瞧瞧：Excel中按顏色查找技巧
    今天一同事傳來一張Excel表格，讓幫忙核對紅色標註的數據，找開表格後看到同事說的紅色數據是字體顏色，而不是填充顏色（如下圖），表格有800多行，字體還很小，紅色和黑色之間，一會眼睛就花了，好在小編有神奇的Excel技巧，今天就和大家一起分享如何快速查找帶顏色的文字。
  • 如何查找兩個excel表格的重複值?職場推薦使用Sumproduct函數
    課程信息卡課程：《Excel天天訓練營》2.0圖文版章節：第2章-精通函數內容：查找重複值（Sumproduct）在職場工作中，如果遇到兩個excel表格需要比對，查找重複值的話，最好用的就是sumproduct函數了。
  • 手把手教你如何用程序思維快速比對excel數據
    excel使我們日常生活中常見的數據表，各種財務統計、數據報表等都離不開excel表格。而今，對excel的數據比對也越來越多。單純的人工比對不僅費時費力，而且還容易出錯，真正的費力不討好，小面小編就以程式設計師的思維聊聊怎麼使用Excel自帶的VB編寫程序快速的比對excel數據，學會了以後能夠提高自己的工作效率，再也不用加班處理數據了（這裡以目前比較常用的Excel 2007做示範，其他版本的Excel類似）。1.
  • 你要掌握這個excel技巧!
    但是在現實的工作中，有些人經常習慣於在一個單元格內進行數字記錄，這種表格要是對起帳來，十分麻煩，因為只是在一個單元格內顯示一個結果，明細數據要點擊單元格在編輯欄才能看到。要想和左邊的數據進行核對，就要先把數據整理成數據列表的形式。如何操作，分為兩步：先複製數據出來，粘貼在任意單元格內。
  • Excel教程：excel查找合併單元格操作技巧
    Excel教程查找合併單元格是一項比較實用的excel操作技巧。比如一個excel工作表，有很多合併單元格，如何一次性選中所有合併單元格，然後取消合併單元格操作。您會嗎？　　excel查找合併單元格操作是這樣的：先任意合併兩個單元格，編輯菜單-查找（或直接按ctrl+f），在查找對話框中點「選項」，然後從單元格中選取格式，然後點全部查找就會找到所有合併過的單元格，如果要全選，可以按快捷鍵ctrl+a。 這樣就可以一次性選中所有的excel表格裡面的合併單元格，然後點擊取消合併單元格即可。
  • 用Access進行簡單數據比對
    同為Office套件中一部分的Access，雖然有著同樣強大的功能，但使用的人卻相對少些，不像Word和Excel那樣廣泛。 Access 資料庫管理系統是MicrosoftOffice 套件的重要組成部分，不僅是一個資料庫，而且具有強大的數據管理功能，它可以方便地利用各種數據源，生成窗體（表單），查詢，報表和應用程式等。