兩個ecel表格核對的6種方法

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

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

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

、使用選擇性粘貼核對當(dāng)兩個格式完全一樣的表格進行核對時，可以用選擇性粘貼方法，如下圖所示，表1和表2是格式完全相同的表格，要求核對兩個表格中填的數(shù)字是否完全一致。AECDEFc項目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詁"蘭色今天就看到一同事在手工一行一行的手工對比兩個表格。蘭色馬上想到的是在一個新表中設(shè)置公式，讓兩個表的數(shù)據(jù)相減?？墒峭潞说谋?，是兩個excel文件中表格，設(shè)置公式還要修改引用方式，挺麻煩的。后來一想，用選擇性粘貼不是也可以讓兩個表格相減嗎？于是，復(fù)制表1的數(shù)據(jù)，選取表格中單元格，右鍵“選擇粘貼貼”-“減”。

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

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