Excel表格中數(shù)據(jù)比對和查找的幾種技巧分析

1、Excel表格中數(shù)據(jù)比對和查找的幾種技巧經(jīng)常被人問到怎么對兩份 Excel數(shù)據(jù)進(jìn)行比對，提問的往往都很籠 統(tǒng)；在工作中，有時(shí)候會需要對兩份內(nèi)容相近的數(shù)據(jù)記錄清單進(jìn)行比 對，需求不同，比對的的目標(biāo)和要求也會有所不同。下面Office辦公助手（）的小編根據(jù)幾個(gè)常見的應(yīng)用環(huán)境介紹 一下Excel表格中數(shù)據(jù)比對和查找的技巧。應(yīng)用案例一：比對取出兩表的交集（相同部分）Sheetl中包含了一份 數(shù)據(jù)清單A,sheet2中包含了一份數(shù)據(jù)清單B, 要取得兩份清單共有的數(shù)據(jù)記錄（交集），也就是要找到兩份清單中的 相同部分。方法1:高級篩選高級篩選是處理重復(fù)數(shù)據(jù)的利器。選中第一份數(shù)據(jù)清單所在的數(shù)據(jù)區(qū)域，在功能區(qū)上

2、依次單擊【數(shù)據(jù)】【高級】（2003版本中菜單操作為【數(shù)據(jù)】一一【篩選】一一【高級篩選】），出現(xiàn)【高級篩選】對話框在對話框中，篩選【方式】可以根據(jù)需求選取，例如這里選擇將篩選結(jié)果復(fù)制到其他位置”；【列表區(qū)域】就是之前所選中的第一份 數(shù)據(jù)清單A所在的單元格區(qū)域；【條件區(qū)域】則選取另外那份清單B 所在的單元格區(qū)域。如下圖所示：Hi留必二Sheets, Qwww,pffiezhiqHpu.<jpm5P力，釜|加工m劭AB11:M2731甜45669 ；34?703 iQBEJC769汩5510磋,n11整5012第曰5213* * Jl.r!單11 生“為北財(cái)事際”：?原中*町A Ub48曼5S

3、7638TO點(diǎn)擊【確定】按鈕后，就可以直接得到兩份清單的交集部分，效果 如下圖。其中兩個(gè)清單中雖然都有【西瓜】和【菠蘿】，但是由于數(shù) 量不一致，所以沒有作為相同記錄被提取出來。這個(gè)操作的原理，就是利用了高級篩選功能對于匹配指定條件的記 錄進(jìn)行篩選的功能，把兩張表中的任意一張作為條件區(qū)域， 在另外一 張表中就能篩選出與之相匹配的記錄，忽略掉其他不相關(guān)的記錄。需要注意的是，使用高級篩選的時(shí)候務(wù)必注意兩個(gè)清單的標(biāo)題行要 保持一致（高級篩選中作為條件區(qū)域的前提），并且在選取【列表區(qū)域】 和【條件區(qū)域】的時(shí)候都要把標(biāo)題行的范圍包含在其中。方法2:公式法使用公式進(jìn)行比對的方法有很多，如果是單列數(shù)據(jù)對比比較

4、常用的 函數(shù)是COUNTIF函數(shù)，如果是多列數(shù)據(jù)記錄對比，SUMPRODUCT 函數(shù)比較勝任。在其中一張清單的旁邊輸入公式：=SUMPRODUCT(A2&B2=Sheet2!A$2:A$13&Sheet2!B$2:B$13)*1并向下復(fù)制填充。其中的 Sheet2!A$1:A$13和Sheet2舊$2:B$13是另一張清單中的兩列數(shù)據(jù)區(qū)域，需要根據(jù)實(shí)際情況修改。公式結(jié)果等 于1的記錄就是兩個(gè)清單的交集部分，如下圖所示：cj Ljjr .'手均O.S4MS4M5A" 口 ！tl Sheet?打,www.officezh4J應(yīng)用案例二：取出兩表的差異記錄要在某一張

5、表里取出與另一張表的差異記錄，就是未在另外那張清 單里面出現(xiàn)的部分，其原理和操作都和上面第一種場景的差不多， 所 不同的只是篩選后所選取的集合正好互補(bǔ)。方法1 ：高級篩選先將兩個(gè)清單的標(biāo)題行更改使之保持一致， 然后選中第一份數(shù)據(jù)清 單所在的數(shù)據(jù)區(qū)域，在功能區(qū)上依次單擊【數(shù)據(jù)】 【高級】，出 現(xiàn)【高級篩選】對話框。在對話框中，篩選方式選擇 在原有區(qū)域顯 示篩選結(jié)果”；【列表區(qū)域】和【條件區(qū)域】的選取和前面場景 1完全相同，如下圖所示:Offi"辦公助手VwWfofficezhu th ou. com說/唱*> t醴*'后及克行柝斗一«A二舟備 M寸亞 H 下也

6、W4 5 口 是 lotrs 二 一Q- ,+| 1st 品后生1 HH45««55兼哼76 西方50n2535m46通渭席好70提子騁英型7276里45A點(diǎn)擊【確定】完成篩選，將篩選出來的記錄全部選中按 Del鍵刪除（或做標(biāo)記），然后點(diǎn)擊【清除】按鈕（2003版本中為【全部顯示】按鈕）就可以恢復(fù)篩選前的狀態(tài)得到最終的結(jié)果，如下圖所示：1，白電 E局金= 總%廢期衣 電政求行忻*Mkrowk Em*'公靈 的盥 豐魔 通開宸二M小 血加注 告t 命中I磬 斛曲， 口.S9必取金,咽”卡取宿號打加匚£G卻KOE QU lOMfe方法2:公式法使用公式的話，方

7、法和場景1完全相同，只是最后需要提取的是公 式結(jié)果等于0的記錄。應(yīng)用案例三：取出關(guān)鍵字相同但數(shù)據(jù)有差異的記錄前面的兩份清單中，【西瓜】和【菠蘿】的貨品名稱雖然一致，但 在兩張表上的數(shù)量卻不相同，在一些數(shù)據(jù)核對的場景下，就需要把這 樣的記錄提取出來。方法1:高級篩選高級篩選當(dāng)中可以使用特殊的公式，使得高級篩選的功能更加強(qiáng) 大。第一張清單所在的sheet里面，把D1單元格留空，在D2單元格內(nèi) 輸入公式:=VLOOKUP(A2 , Sheet2!$A$2:$B$13, 2, 0)<>B2然后在功能區(qū)上依次單擊 【數(shù)據(jù)】一一【高級】，出現(xiàn)【高級篩選】對話框。在對話框中，篩選方式選擇 在原有

8、區(qū)域顯示篩選結(jié)果”；【列 表區(qū)域】選取第一張清單中的完整數(shù)據(jù)區(qū)域，【條件區(qū)域】則選取剛剛特別設(shè)計(jì)過的D1:D2單元格區(qū)域，如下圖所示：Offi"辦公助手WWbfficezhu shou. com二j三半Micr Mah. EffclBL學(xué).EI*旗率E示A = v LOO<UPA2rSheet2! 5A父切$】，2朋通 2小開立5KteJC卡盟里曾一七二才僑 *AW棒不玄意的正錄w窗asM工專45碗S9麗76l50n25西E48由3g賽琳70由于舒7?76*U在*丈一饅HE茁 :制曲事置立喇幗55亨津口臧,：$4： UJ】二工:?注口：博 u Sinti SB： III： +.

9、點(diǎn)擊【確定】按鈕以后，就可以得到篩選結(jié)果，就是第一張中貨品 名稱與第二張表相同但數(shù)量卻不一致的記錄清單，如下圖所示:同樣的，照此方法在第二張清單當(dāng)中操作，也可以在第二張清單中找到其中與第一張清單數(shù)據(jù)有差異的記錄。這個(gè)方法是利用了高級篩選中可以通過自定義公式來添加篩選條件的功能，有關(guān)高級篩選中使用公式作為條件區(qū)域的用法，可參考本站發(fā)布的；另外一篇教程：Excel中數(shù)據(jù)庫函數(shù)和高級篩選條件區(qū)域設(shè)置方法詳解方法2:公式法使用公式還是可以利用前面用到的 SUMPRODUCT函數(shù)，在其中一張清單的旁邊輸入公式：=SUMPRODUCT(A2=Sheet2!A$2:A$13)*(B2<>Sheet2!B$2:B$13)并向下復(fù)制填充。公式中的包含了兩個(gè)條件，第一個(gè)條件是A列數(shù)據(jù)相同，第二個(gè)