兩個(gè)ecel表格核對(duì)的6種方法

兩個(gè)excel表格核對(duì)的6種方法，用了三個(gè)小時(shí)才整理完成！2014-12-17蘭色幻想趙志東Excel精英培訓(xùn)excelpx-teteexce應(yīng)用分享與問(wèn)題解答，提供excel技巧、函數(shù)和VBA相關(guān)學(xué)習(xí)資料的自助查詢(xún)。每天一篇原創(chuàng)excel教程，伴你excel學(xué)習(xí)每一天!excel表格之間的核對(duì)，是每個(gè)excel用戶(hù)都要面對(duì)的工作難題，今天蘭色帶大家一起盤(pán)點(diǎn)一下表格核對(duì)的方法，一共6種，以后再也不用加班勾數(shù)據(jù)了。（蘭色用了三個(gè)小時(shí)整理出了這篇教程，估計(jì)你再也找不到這么全的兩表核對(duì)教程一定要轉(zhuǎn)發(fā)或收藏起來(lái)備用哦）一、使用合并計(jì)算核對(duì)

excel中有一個(gè)大家不常用的功能：合并計(jì)算。利用它我們可以快速對(duì)比出兩個(gè)表的差異。例：如下圖所示有兩個(gè)表格要對(duì)比，一個(gè)是庫(kù)存表，一個(gè)是財(cái)務(wù)軟件導(dǎo)出的表。要求對(duì)比這兩個(gè)表同一物品的庫(kù)存數(shù)量是否一致，顯示在sheet3表格。庫(kù)存表JJLJLLj品rq稱(chēng)庫(kù)別數(shù)量單價(jià)金額A10庫(kù)11010100：A5庫(kù)115454A3 '庫(kù)132；,6AS .命4.1A2庫(kù)1122|A6 :庫(kù)1421184:A7 '庫(kù)1-2-5L0Al庫(kù)159'45A9庫(kù)121641344喬iL庫(kù)存表｝?軟件導(dǎo)旺阪韻胡趣1軟件導(dǎo)出表:產(chǎn)品名稱(chēng)庫(kù)別數(shù)量金額A5庫(kù)115454A7 ；庫(kù)12510庫(kù)1會(huì)1冷A2庫(kù)112-2>9 ；庫(kù)1641216A6庫(kù)12134A3 :庫(kù)1§26A1 :庫(kù)17963A10庫(kù)11010100」 丨r—j 1卜小庫(kù)存表贏(yíng)件導(dǎo)出-滋冠顯融|【B「r*■ 「-rT操作方法步驟1:選取sheet3表格的Al單元格，excel2003版里，執(zhí)行數(shù)據(jù)菜單（excel2010版數(shù)據(jù)選項(xiàng)卡）-合并計(jì)算。在打開(kāi)的窗口里“函數(shù)”選“標(biāo)準(zhǔn)偏差”，如下圖所示。

步驟2:接上一步別關(guān)窗口，選取庫(kù)存表的A2:C10（第1列要包括對(duì)比的產(chǎn)品，最后一列是要對(duì)比的數(shù)量），再點(diǎn)“添加”按鈕就會(huì)把該區(qū)域添加到所有引用位置里.步驟3:同上一步再把財(cái)務(wù)軟件表的A2:C10區(qū)域添加進(jìn)來(lái)。標(biāo)簽位置：選取“最左列”，如下圖所示。引用位置（W：;A5：A6:A1首行①;A5：A6:A1首行①最左列屯廠(chǎng)I標(biāo)簽位買(mǎi)創(chuàng)縫連至鴻數(shù)據(jù)的關(guān)接電1'MU'igFiniut1U|WT卜?狐庫(kù)存4[5忑■■口占耐創(chuàng)進(jìn)行以上步驟后，點(diǎn)確定按鈕，會(huì)發(fā)現(xiàn)Sheet3中的差異表已生成，C列為0的表示無(wú)差異，非0的行即是我們要查找的異差產(chǎn)品。ABc1A10 .0..2A5哉所顧園有攀笨晝；丫1產(chǎn)出量 03譎jhrzH~njJmo4A80.70710756A20A607A70.8.9A11.414|14A9'1.4142141011|<??卜呱庫(kù)仔表/M=X14 吊/%嘰3/ |蘭色說(shuō)：如果你想生成具體的差異數(shù)量，可以把其中一個(gè)表的數(shù)字設(shè)置成負(fù)數(shù)。（添加一輔助列=c2*-l），在合并計(jì)算的函數(shù)中選取'求和”，即可。另外，此類(lèi)題目也可以用VLOOKUP函數(shù)查找另一個(gè)表中相同項(xiàng)目對(duì)應(yīng)的值，然后相減核對(duì)。

、使用選擇性粘貼核對(duì)當(dāng)兩個(gè)格式完全一樣的表格進(jìn)行核對(duì)時(shí)，可以用選擇性粘貼方法，如下圖所示，表1和表2是格式完全相同的表格，要求核對(duì)兩個(gè)表格中填的數(shù)字是否完全一致。AECDEFc項(xiàng)目1戸2月3月編軒AL1610612129A29IB.31361A37BIS320■25AiS17：2120122Q.AS418415IB7A612121911920；iA76B141IS.9AB175715‘20A&172120S91A10720.391720,2All12161221617'.3A12141.05'211016'iA13:0IS1617175All6152g15116A1517207814.9.A161619酣5密-.19gA172-19li133917IIISheets叮匸"杯「"7詁"蘭色今天就看到一同事在手工一行一行的手工對(duì)比兩個(gè)表格。蘭色馬上想到的是在一個(gè)新表中設(shè)置公式，讓兩個(gè)表的數(shù)據(jù)相減。可是同事核的表，是兩個(gè)excel文件中表格，設(shè)置公式還要修改引用方式，挺麻煩的。后來(lái)一想，用選擇性粘貼不是也可以讓兩個(gè)表格相減嗎？于是，復(fù)制表1的數(shù)據(jù)，選取表格中單元格，右鍵“選擇粘貼貼”-“減”。

羽姑板AB.僅1項(xiàng)目1月2月2ai羽姑板AB.僅1項(xiàng)目1月2月2ai1lei103A29~IS4A37B5A4■3176A5i47A61Z1.28A7689A8175la陽(yáng)1721nA1072012Allia1613：Alf.1410UA13-8IBE2粘貼?全部迦O備式迪O數(shù)值①o格式⑴O批注盟O有效性驗(yàn)證（W運(yùn)宜<■'無(wú)（Q）Ml□跳過(guò)空單元⑥粘貼鏈接L?TK所有使匸O邊框除夕O列寬迪o族式和當(dāng)Q■值和數(shù)耳所有合于o乘?O際（I）確定進(jìn)行上面操作后，差異數(shù)據(jù)即露出原形。項(xiàng)目lfi2月越B明B月Al000000.A2000:000血a0oC250A4fi0a000ASQ0Q0心0A6000000鮎o'00000At)00060A9：a0f1J0■0..0A1Q00—-加 ?ub00All00000QAl2-00Q000Al'3：Q000■0/0A1400000A150000-7J0A166Q00、''f0A17?00000?('J^卜卜11表f丄羔N靈是虻5惰克:箱訓(xùn):m氓斥貳瞅三站匸MTFR■式喘3S佔(zhàn)牯”三、使用sumproduct函數(shù)完成多條件核對(duì)一個(gè)同事遇到的多條件核對(duì)問(wèn)題，簡(jiǎn)化了一下。如下圖所示，要求核對(duì)兩表中同一產(chǎn)品同一型號(hào)的數(shù)量差異，顯示在D列。產(chǎn)品型號(hào)數(shù)量'AF8D：R4'BQ3\\ CU屯]AL6'產(chǎn)品型號(hào) ，數(shù)量弄異 11EQ7-4= 1\DR5 .-112CU3A'F6AL公式：D10二SUMPRODUCT( ($A$2:$A$6二A10)*($B$2:$B$6二BIO)*$C$2:$C$6)-C10公式簡(jiǎn)介因?yàn)榉祷氐氖菙?shù)字，所以多條件查找可以用sumproduct多條件求和來(lái)返回對(duì)應(yīng)的銷(xiāo)量。在微信平臺(tái)回復(fù)sumproduct即可查看該函數(shù)的教程。使用VL00KUP 函數(shù)核對(duì)蘭色評(píng)：本例可以用SUMIFS函數(shù)替代sumproduct函數(shù)。四、使用C0UNTIF函數(shù)核對(duì)如果有兩個(gè)表都有姓名列。怎么對(duì)比這兩個(gè)表的姓名哪些相同，哪些不同呢？其實(shí)解決這個(gè)問(wèn)題挺簡(jiǎn)單的，但還是不斷的有同學(xué)提問(wèn)，所以這里有必要再介紹一下方法。例，如下圖所示，要求對(duì)比A列和C列的姓名，在B和D列出哪些是相同的，哪些是不同的。ABDE1姓名一是否相同姓名’是否相同2張旺財(cái)相同華雨萌不同3不同張旺財(cái)相同4昊析相同暫楠不同5.馬尚飛不同斯相同,6萬(wàn)得廣相同萬(wàn)得廣相同7李玉相同區(qū)勝利不同o張傳屁不同李玉相同10分析：在excel里數(shù)據(jù)的核對(duì)一般可以用三個(gè)函數(shù)countifvlookup和match函數(shù),后兩個(gè)函數(shù)查找不到會(huì)返回錯(cuò)誤值，所以counti就成為核對(duì)的首選函數(shù)。公式：B2=IF（COUNTIF（D:D,A2）〉0,相同〃，不同〃）D2=IF（C0UNTIF（A:A,D2）〉0,〃相同〃，不同〃）公式說(shuō)明：1counti是計(jì)算一個(gè)區(qū)域內(nèi)（D:D），根據(jù)條件（等于A(yíng)2的值）計(jì)算相同內(nèi)容的個(gè)數(shù),比如A2單元格公式意思是在D列計(jì)算“張旺財(cái)”的個(gè)數(shù)。2IF是判斷條件（C0UNTIF（A:A,D2）〉0）是否成立，如果成立就是返回第1個(gè)參數(shù)的值（〃相同〃），不成立就返回第二個(gè)參數(shù)的值（〃不同〃）蘭色說(shuō)：本例是在同一個(gè)表，如果不在同一個(gè)表，只需要把引用的列換成另一個(gè)表的列即可。五、使用條件格式核對(duì)太多的同學(xué)在微信上提問(wèn)如何查找對(duì)比兩列哪些是重復(fù)的，今天蘭色介紹一種超簡(jiǎn)單的方法，不需要用任何公式函數(shù)，兩步即可完成。 操作步驟 第1步：把兩列復(fù)制到同一個(gè)工作表中ABc1A.■TVEE計(jì)VA4CSK5ECSS0V7F08「P9巳31詩(shī)垢訓(xùn)第2步：按CTRL鍵同時(shí)選取兩列區(qū)域，開(kāi)始-條件格式-突出顯示單元格規(guī)則-重復(fù)值。.01実出顯示單元朋則凹?項(xiàng)目選取規(guī)則CD融據(jù)槩働気郴式圖標(biāo)集（DEi這險(xiǎn)規(guī)則（□文旅包含（D...宜他擁EllfML,回...01実出顯示單元朋則凹?項(xiàng)目選取規(guī)則CD融據(jù)槩働気郴式圖標(biāo)集（DEi這險(xiǎn)規(guī)則（□文旅包含（D...宜他擁EllfML,回..發(fā)生日期兇”?4□餐人刪除梧式小于介于世）介于世）...邑階⑸設(shè)置后效果如下:AcD1AT2BE3VA4cs:EFCSE■■'V7F0Bp9軌邑的即是重早值E-h卅i典培訓(xùn)注：1此方法不適合excel2003版，2003版本可以用counti統(tǒng)計(jì)個(gè)數(shù)的方法查找重復(fù)。2此方法不適合同一個(gè)表中有重復(fù)項(xiàng)，可以刪除重復(fù)項(xiàng)后再兩表對(duì)比。六、使用高級(jí)篩選核對(duì)高級(jí)篩選也能核對(duì)數(shù)據(jù)？可能很多同不太相信。其實(shí)真的可以?；卮鹞⑿牌脚_(tái)一位同學(xué)的提問(wèn)：快速?gòu)囊环?00人的名單中篩選出指定30個(gè)人名。分析：excel2010版本中我們可以直接選取多個(gè)項(xiàng)目，但如果一下子給你30個(gè)姓名讓你從中挑選出來(lái)，估計(jì)要很久才能完成篩選。這時(shí)我們可以借助高級(jí)篩選來(lái)快速完成。例：如下圖所示AB兩列為姓名和銷(xiāo)量，要求，根據(jù)E列提供的姓名從A列篩選出來(lái)。操作步驟ABCDE1姓名銷(xiāo)售量姓名2asAl.S'A2,A34A3275A4J.'29 ］、ATSA'5 .53A9A6,S3,All8A7S9AIS$A853 'AL51Q'A'9 .9S....-.| |Il11A107&亠||耳質(zhì)劑II選取AB列數(shù)據(jù)區(qū)域，數(shù)據(jù)-高級(jí)篩選-打開(kāi)如下圖高級(jí)篩選窗口，并進(jìn)行如下設(shè)置。

A ECD S ;姓名！F1址名銷(xiāo)售量:2：：A106.:A1!總A3:A3 ；A ECD S ;姓名！F1址名銷(xiāo)售量:2：：A106.:A1!總A3:A3 ；A40勺?”27唁遴鍛17■■■■■■A6g91