站長資訊網(wǎng)
本篇文章給大家整理分享幾個財務(wù)對賬必會的函數(shù)，相信看完這篇教程，以后你再做數(shù)據(jù)核對的工作要輕松好幾倍呢！
進行繁雜的對賬工作常常是財務(wù)人員頭疼的事情，不僅僅因為數(shù)據(jù)量比較大，在實際對賬的過程中，可能會遇到各種各樣的情況，說起來都是對賬，但處理的方法可能有很大的區(qū)別，因此今天為大家整理出了一些比較常遇到的問題，都是可以運用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)比較完善，導出的數(shù)據(jù)就比較規(guī)范，本例的系統(tǒng)數(shù)據(jù)要挑毛病的話只能說這個費用類型里登記的過于簡單，基本沒什么有用的信息。
再看手工數(shù)據(jù)，問題就比較明顯了，有兩個問題：
第一、日期格式不規(guī)范，使用小數(shù)點作為日期中年月日分隔符估計是很多小伙伴的習慣，但是這樣的格式Excel并不會當做日期來處理；
第二、日期列登記不完整，或許是為了偷懶，有很多空單元格，估計空的是和上面單元格的日期一致，這同樣是很多小伙伴的錄入習慣吧。
拿到這樣的數(shù)據(jù)，首先要對A列進行處理，處理方法為：選中數(shù)據(jù)區(qū)域，按F5或者Ctrl G打開定位，定位空值后確定，輸入=，按一下方向鍵↑，按著Ctrl鍵回車完成填充；再選擇數(shù)據(jù)區(qū)域，復制粘貼為數(shù)值后，點擊分列，直接在第三步選擇日期格式，完成即可，具體操作看動畫演示。
數(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進行求和時，會把這兩筆進行匯總，實際上并不是真的有差異。對于這種日期一致金額一致但是具體用途不同的，在核對時直接用公式判斷比較麻煩，可以考慮借助輔助列來進行重復性判斷：
在手工數(shù)據(jù)后面使用公式=COUNTIFS($A$2:A3,A3,$B$2:B3,B3)，意思是對日期與金額相同的進行計數(shù)，注意在選擇范圍的時候，對范圍的起始位置要加$進行鎖定，這樣公式在下拉的時候范圍就會遞增，當有重復數(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)負數(shù)就表示手工數(shù)據(jù)中沒有錄入該項。
今天用了兩個例子來分析數(shù)據(jù)核對的常用思路，在進行更為復雜的核對工作時，只要掌握VLOOKUP、SUMIF、SUMIFS、COUNTIF和COUNTIFS這幾個函數(shù)，同時善于使用輔助列的話，基本都是可以很快就找到差異的。
相關(guān)學習推薦：excel教程
