小編有話說：對賬幾乎是每個財務(wù)都會做的工作，若不會用幾個excel函數(shù)，單靠肉眼去核對，那肯定是加班沒商量了。今天小編給大家推送的教程，就是解決數(shù)據(jù)核對的問題，相信看完這篇教程，以后你再做數(shù)據(jù)核對的工作要輕松好幾倍呢！

進(jìn)行繁雜的對賬工作常常是財務(wù)人員頭疼的事情，不僅僅因為數(shù)據(jù)量比較大，在實際對賬的過程中，可能會遇到各種各樣的情況，說起來都是對賬，但處理的方法可能有很大的區(qū)別，因此今天為大家整理出了一些比較常遇到的問題，都是可以運(yùn)用EXCEL瞬間完成的，一起來看看都是哪些折磨人的問題吧。

一、最簡單的對賬問題

數(shù)據(jù)說明：左側(cè)為系統(tǒng)訂單數(shù)據(jù)，右側(cè)是手工數(shù)據(jù)（一般為供貨商提供或者文員手工錄入登記），系統(tǒng)數(shù)據(jù)是完整的，現(xiàn)在需要核對還有哪些訂單是缺少手工數(shù)據(jù)的。

使用VLOOKUP函數(shù)查找訂單號所對應(yīng)的手工數(shù)據(jù)，按照VLOOKUP（查找值，查找范圍，查找內(nèi)容在第幾列，精確查找）這個格式代入公式，查找值是系統(tǒng)訂單號（A3），查找范圍是手工數(shù)據(jù)（E:F），訂單號在手工數(shù)據(jù)的第二列，精確查找時第四參數(shù)為0，就有了公式：=VLOOKUP(A3,E:F,2,0)

使用這個公式得到的數(shù)據(jù)中會出現(xiàn)一些#N/A，表示沒有找到對應(yīng)的數(shù)據(jù)，也就是系統(tǒng)數(shù)據(jù)中存在而手工數(shù)據(jù)中不存在的內(nèi)容，需要篩選出來查找原因。

這是最常用的一種核對數(shù)據(jù)的方法，有時候我們不僅僅要核對數(shù)據(jù)是否存在，還要核對訂單金額是否存在差異，這時候使用VLOOKUP就不方便了，需要用到另一個函數(shù)SUMIF。

思路是利用SUMIF函數(shù)按照系統(tǒng)訂單號對手工數(shù)據(jù)的訂單金額求和，再與系統(tǒng)的訂單金額相減，根據(jù)結(jié)果是否為0 差異所在，在D3單元格輸入公式：

=SUMIF（E:E,A3,F:F）-B3，雙擊填充公式，具體效果如圖所示：

SUMIF函數(shù)的格式為：SUMIF（條件區(qū)域，條件，求和區(qū)域），本例中條件區(qū)域是手工訂單號（E列），條件是系統(tǒng)訂單號（A3），求和區(qū)域是手工訂單金額（F列）。

差異為0的就是系統(tǒng)數(shù)據(jù)與手工數(shù)據(jù)吻合，差異不為零的數(shù)據(jù)中有兩種情況，一種是沒有對應(yīng)手工數(shù)據(jù)的情況，還有一種是手工數(shù)據(jù)存在但是金額不一致，這個結(jié)合之前VLOOKUP的結(jié)果就很容易看出來。

比如上圖中的C9單元格沒有出現(xiàn)#N/A錯誤，但是D9單元格值不為零，說明該訂單數(shù)據(jù)錄入錯誤。

對于比較規(guī)范的數(shù)據(jù)，核對起來也很方便，通常使用VLOOKUP和SUMIF函數(shù)就能解決，但在實際工作中，會遇到一些不那么規(guī)范的數(shù)據(jù)，繼續(xù)來看。

二、略顯麻煩的對賬問題

右側(cè)為系統(tǒng)數(shù)據(jù)，只保留了四列，實際上可能是很多列，在核對的時候可以將無用的列剔除。左側(cè)是手工登記的數(shù)據(jù)，只有三列。

對于系統(tǒng)數(shù)據(jù)沒什么好說的，有些系統(tǒng)比較完善，導(dǎo)出的數(shù)據(jù)就比較規(guī)范，本例的系統(tǒng)數(shù)據(jù)要挑毛病的話只能說這個費(fèi)用類型里登記的過于簡單，基本沒什么有用的信息。

再看手工數(shù)據(jù)，問題就比較明顯了，有兩個問題：

第一、日期格式不規(guī)范，使用小數(shù)點(diǎn)作為日期中年月日分隔符估計是很多小伙伴的習(xí)慣，但是這樣的格式Excel并不會當(dāng)做日期來處理；

第二、日期列登記不完整，或許是為了偷懶，有很多空單元格，估計空的是和上面單元格的日期一致，這同樣是很多小伙伴的錄入習(xí)慣吧。

拿到這樣的數(shù)據(jù)，首先要對A列進(jìn)行處理，處理方法為：選中數(shù)據(jù)區(qū)域，按F5或者Ctrl G打開定位，定位空值后確定，輸入=，按一下方向鍵↑，按著Ctrl鍵回車完成填充；再選擇數(shù)據(jù)區(qū)域，復(fù)制粘貼為數(shù)值后，點(diǎn)擊分列，直接在第三步選擇日期格式，完成即可，具體操作看動畫演示。

數(shù)據(jù)處理規(guī)范后，就該核對差異了，在這個例子中，需要判斷同一個日期下金額存在差異的數(shù)據(jù)是哪些，這就包含了兩個條件：日期、金額。因此考慮用SUMIFS函數(shù)，基本結(jié)構(gòu)為SUMIFS（求和范圍，條件范圍1，條件1，條件范圍2，條件2），還是以系統(tǒng)數(shù)據(jù)為基礎(chǔ)來核對手工數(shù)據(jù)，在I3單元格輸入公式為：=SUMIFS(B:B,A:A,E3,B:B,H3)-H3，雙擊填充。

差異為零的表示數(shù)據(jù)完全吻合，不為零的就需要篩選出來查找差異原因。

因為數(shù)據(jù)不多，可以看出來有兩筆8000的是出現(xiàn)在同一個日期，我們使用SUMIFS進(jìn)行求和時，會把這兩筆進(jìn)行匯總，實際上并不是真的有差異。對于這種日期一致金額一致但是具體用途不同的，在核對時直接用公式判斷比較麻煩，可以考慮借助輔助列來進(jìn)行重復(fù)性判斷：

在手工數(shù)據(jù)后面使用公式=COUNTIFS($A$2:A3,A3,$B$2:B3,B3)，意思是對日期與金額相同的進(jìn)行計數(shù)，注意在選擇范圍的時候，對范圍的起始位置要加$進(jìn)行鎖定，這樣公式在下拉的時候范圍就會遞增，當(dāng)有重復(fù)數(shù)據(jù)出現(xiàn)時，結(jié)果也是遞增的。

同理，對系統(tǒng)數(shù)據(jù)也按照這個方法處理，公式為：=COUNTIFS($E$2:E3,E3,$H$2:H3,H3)

完成了兩個輔助列之后，核對金額的公式就變成了三個條件：

=SUMIFS(B:B,A:A,E3,B:B,H3,D:D,I3)-H3，雙擊填充可看到結(jié)果，出現(xiàn)負(fù)數(shù)就表示手工數(shù)據(jù)中沒有錄入該項。

今天用了兩個例子來分析數(shù)據(jù)核對的常用思路，在進(jìn)行更為復(fù)雜的核對工作時，只要掌握VLOOKUP、SUMIF、SUMIFS、COUNTIF和COUNTIFS這幾個函數(shù)，同時善于使用輔助列的話，基本都是可以很快就找到差異的。

不知道今天的內(nèi)容對你幫助有多大，歡迎留言告訴我們，如果還有不能自己解決的對賬問題，也可以在微信群或QQ群里交流。

本文配套的練習(xí)課件請加入QQ群：264539405下載。

如果您因工作所需使用到Excel，不妨關(guān)注部落窩教育的《一周Excel直通車》視頻課或《Excel極速貫通班》直播課系統(tǒng)學(xué)習(xí)。

如果您想要隨時隨地學(xué)習(xí)excel，掃下方二維碼，可關(guān)注公眾號，每日為您推送優(yōu)質(zhì)excel教程：

 相關(guān)推薦：