站長(zhǎng)資訊網(wǎng)
本篇文章給大家整理分享幾個(gè)財(cái)務(wù)對(duì)賬必會(huì)的函數(shù)，相信看完這篇教程，以后你再做數(shù)據(jù)核對(duì)的工作要輕松好幾倍呢！
進(jìn)行繁雜的對(duì)賬工作常常是財(cái)務(wù)人員頭疼的事情，不僅僅因?yàn)閿?shù)據(jù)量比較大，在實(shí)際對(duì)賬的過(guò)程中，可能會(huì)遇到各種各樣的情況，說(shuō)起來(lái)都是對(duì)賬，但處理的方法可能有很大的區(qū)別，因此今天為大家整理出了一些比較常遇到的問(wèn)題，都是可以運(yùn)用EXCEL瞬間完成的，一起來(lái)看看都是哪些折磨人的問(wèn)題吧。
一、最簡(jiǎn)單的對(duì)賬問(wèn)題
數(shù)據(jù)說(shuō)明：左側(cè)為系統(tǒng)訂單數(shù)據(jù)，右側(cè)是手工數(shù)據(jù)（一般為供貨商提供或者文員手工錄入登記），系統(tǒng)數(shù)據(jù)是完整的，現(xiàn)在需要核對(duì)還有哪些訂單是缺少手工數(shù)據(jù)的。
使用VLOOKUP函數(shù)查找訂單號(hào)所對(duì)應(yīng)的手工數(shù)據(jù)，按照VLOOKUP（查找值，查找范圍，查找內(nèi)容在第幾列，精確查找）這個(gè)格式代入公式，查找值是系統(tǒng)訂單號(hào)（A3），查找范圍是手工數(shù)據(jù)（E:F），訂單號(hào)在手工數(shù)據(jù)的第二列，精確查找時(shí)第四參數(shù)為0，就有了公式：=VLOOKUP(A3,E:F,2,0)
使用這個(gè)公式得到的數(shù)據(jù)中會(huì)出現(xiàn)一些#N/A，表示沒有找到對(duì)應(yīng)的數(shù)據(jù)，也就是系統(tǒng)數(shù)據(jù)中存在而手工數(shù)據(jù)中不存在的內(nèi)容，需要篩選出來(lái)查找原因。
這是最常用的一種核對(duì)數(shù)據(jù)的方法，有時(shí)候我們不僅僅要核對(duì)數(shù)據(jù)是否存在，還要核對(duì)訂單金額是否存在差異，這時(shí)候使用VLOOKUP就不方便了，需要用到另一個(gè)函數(shù)SUMIF。
思路是利用SUMIF函數(shù)按照系統(tǒng)訂單號(hào)對(duì)手工數(shù)據(jù)的訂單金額求和，再與系統(tǒng)的訂單金額相減，根據(jù)結(jié)果是否為0 差異所在，在D3單元格輸入公式：
=SUMIF（E:E,A3,F:F）-B3，雙擊填充公式，具體效果如圖所示：
SUMIF函數(shù)的格式為：SUMIF（條件區(qū)域，條件，求和區(qū)域），本例中條件區(qū)域是手工訂單號(hào)（E列），條件是系統(tǒng)訂單號(hào)（A3），求和區(qū)域是手工訂單金額（F列）。
差異為0的就是系統(tǒng)數(shù)據(jù)與手工數(shù)據(jù)吻合，差異不為零的數(shù)據(jù)中有兩種情況，一種是沒有對(duì)應(yīng)手工數(shù)據(jù)的情況，還有一種是手工數(shù)據(jù)存在但是金額不一致，這個(gè)結(jié)合之前VLOOKUP的結(jié)果就很容易看出來(lái)。
比如上圖中的C9單元格沒有出現(xiàn)#N/A錯(cuò)誤，但是D9單元格值不為零，說(shuō)明該訂單數(shù)據(jù)錄入錯(cuò)誤。
對(duì)于比較規(guī)范的數(shù)據(jù)，核對(duì)起來(lái)也很方便，通常使用VLOOKUP和SUMIF函數(shù)就能解決，但在實(shí)際工作中，會(huì)遇到一些不那么規(guī)范的數(shù)據(jù)，繼續(xù)來(lái)看。
二、略顯麻煩的對(duì)賬問(wèn)題
右側(cè)為系統(tǒng)數(shù)據(jù)，只保留了四列，實(shí)際上可能是很多列，在核對(duì)的時(shí)候可以將無(wú)用的列剔除。左側(cè)是手工登記的數(shù)據(jù)，只有三列。
對(duì)于系統(tǒng)數(shù)據(jù)沒什么好說(shuō)的，有些系統(tǒng)比較完善，導(dǎo)出的數(shù)據(jù)就比較規(guī)范，本例的系統(tǒng)數(shù)據(jù)要挑毛病的話只能說(shuō)這個(gè)費(fèi)用類型里登記的過(guò)于簡(jiǎn)單，基本沒什么有用的信息。
再看手工數(shù)據(jù)，問(wèn)題就比較明顯了，有兩個(gè)問(wèn)題：
第一、日期格式不規(guī)范，使用小數(shù)點(diǎn)作為日期中年月日分隔符估計(jì)是很多小伙伴的習(xí)慣，但是這樣的格式Excel并不會(huì)當(dāng)做日期來(lái)處理；
第二、日期列登記不完整，或許是為了偷懶，有很多空單元格，估計(jì)空的是和上面單元格的日期一致，這同樣是很多小伙伴的錄入習(xí)慣吧。
拿到這樣的數(shù)據(jù)，首先要對(duì)A列進(jìn)行處理，處理方法為：選中數(shù)據(jù)區(qū)域，按F5或者Ctrl G打開定位，定位空值后確定，輸入=，按一下方向鍵↑，按著Ctrl鍵回車完成填充；再選擇數(shù)據(jù)區(qū)域，復(fù)制粘貼為數(shù)值后，點(diǎn)擊分列，直接在第三步選擇日期格式，完成即可，具體操作看動(dòng)畫演示。
數(shù)據(jù)處理規(guī)范后，就該核對(duì)差異了，在這個(gè)例子中，需要判斷同一個(gè)日期下金額存在差異的數(shù)據(jù)是哪些，這就包含了兩個(gè)條件：日期、金額。因此考慮用SUMIFS函數(shù)，基本結(jié)構(gòu)為SUMIFS（求和范圍，條件范圍1，條件1，條件范圍2，條件2），還是以系統(tǒng)數(shù)據(jù)為基礎(chǔ)來(lái)核對(duì)手工數(shù)據(jù)，在I3單元格輸入公式為：=SUMIFS(B:B,A:A,E3,B:B,H3)-H3，雙擊填充。
差異為零的表示數(shù)據(jù)完全吻合，不為零的就需要篩選出來(lái)查找差異原因。
因?yàn)閿?shù)據(jù)不多，可以看出來(lái)有兩筆8000的是出現(xiàn)在同一個(gè)日期，我們使用SUMIFS進(jìn)行求和時(shí)，會(huì)把這兩筆進(jìn)行匯總，實(shí)際上并不是真的有差異。對(duì)于這種日期一致金額一致但是具體用途不同的，在核對(duì)時(shí)直接用公式判斷比較麻煩，可以考慮借助輔助列來(lái)進(jìn)行重復(fù)性判斷：
在手工數(shù)據(jù)后面使用公式=COUNTIFS($A$2:A3,A3,$B$2:B3,B3)，意思是對(duì)日期與金額相同的進(jìn)行計(jì)數(shù)，注意在選擇范圍的時(shí)候，對(duì)范圍的起始位置要加$進(jìn)行鎖定，這樣公式在下拉的時(shí)候范圍就會(huì)遞增，當(dāng)有重復(fù)數(shù)據(jù)出現(xiàn)時(shí)，結(jié)果也是遞增的。
同理，對(duì)系統(tǒng)數(shù)據(jù)也按照這個(gè)方法處理，公式為：=COUNTIFS($E$2:E3,E3,$H$2:H3,H3)
完成了兩個(gè)輔助列之后，核對(duì)金額的公式就變成了三個(gè)條件：
=SUMIFS(B:B,A:A,E3,B:B,H3,D:D,I3)-H3，雙擊填充可看到結(jié)果，出現(xiàn)負(fù)數(shù)就表示手工數(shù)據(jù)中沒有錄入該項(xiàng)。
今天用了兩個(gè)例子來(lái)分析數(shù)據(jù)核對(duì)的常用思路，在進(jìn)行更為復(fù)雜的核對(duì)工作時(shí)，只要掌握VLOOKUP、SUMIF、SUMIFS、COUNTIF和COUNTIFS這幾個(gè)函數(shù)，同時(shí)善于使用輔助列的話，基本都是可以很快就找到差異的。
相關(guān)學(xué)習(xí)推薦：excel教程
