Excel函數學習之財務對帳必會的幾個函數（分享）

這篇文章跟大家整理分享幾個財務對帳必會的函數，相信看完這篇教程，以後你再做數據核對的工作要輕鬆好幾倍呢！

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

一、最簡單的對帳問題

#資料說明：左邊為系統訂單數據，右邊是手動資料（一般為供貨商提供或文員手動輸​​入登記），系統資料是完整的，現在需要核對還有哪些訂單是缺少手動資料的。

使用VLOOKUP函數查找訂單號碼所對應的手動數據，按照VLOOKUP（查找值，查找範圍，查找內容在第幾列，精確查找）這個格式代入公式，查找值是系統訂單號（A3 ），查找範圍是手動資料（E:F），訂單號碼在手動資料的第二列，精確查找時第四參數為0，就有了公式：=VLOOKUP(A3,E:F,2 ,0)

使用這個公式得到的數據中會出現一些#N/A，表示沒有找到對應的數據，也就是系統數據中存在而手動資料中不存在的內容，需要篩選出來找出原因。

這是最常用的核對資料的方法，有時候我們不只檢查資料是否存在，還要檢查訂單金額是否有差異，這時候使用VLOOKUP就不方便了，需要用到另一個函數SUMIF。

想法是利用SUMIF函數依照系統訂單號碼對手動資料的訂單金額求和，再與系統的訂單金額相減，根據結果是否為0 差異所在，在D3儲存格輸入公式：

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

SUMIF函數的格式為：SUMIF（條件區域，條件，求和區域），本例中條件區域是手工訂單號碼（E列），條件是系統訂單號碼（A3），求和區域是手工訂單金額（F列）。

差異為0的就是系統資料與手工資料吻合，差異不為零的資料中有兩種情況，一種是沒有對應手工資料的情況，還有一種是手工資料存在但是金額不一致，這個結合之前VLOOKUP的結果就很容易看出來。

例如上圖的C9儲存格沒有出現#N/A錯誤，但D9儲存格值不為零，表示該訂單資料輸入錯誤。

對於比較規範的數據，核對起來也很方便，通常使用VLOOKUP和SUMIF函數就能解決，但在實際工作中，會遇到一些不那麼規範的數據，繼續來看。

二、略顯麻煩的對帳問題

#右邊為系統數據，只保留了四列，其實可能是很多列，在核對的時候可以將無用的列剔除。左側是手工登記的數據，只有三列。

對於系統資料沒什麼好說的，有些系統比較完善，導出的資料就比較規範，本例的系統資料要挑毛病的話只能說這個費用類型裡登記的過於簡單，基本沒什麼有用的資訊.

再看手工數據，問題就比較明顯了，有兩個問題：

第一、日期格式不規範，使用小數點作為日期中年月日分隔符估計是很多小夥伴的習慣，但是這樣的格式Excel並不會當做​​日期來處理；

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

拿到這樣的數據，首先要對A列進行處理，處理方法為：選取資料區域，按F5或Ctrl G開啟定位，定位空值後確定，輸入=，按一下方向鍵↑ ，按Ctrl鍵回車完成填滿；再選擇資料區域，複製貼上為數值後，點選分列，直接在第三步驟選擇日期格式，完成即可，具體操作看動畫示範。

#

資料處理規範後，就該核對差異了，在這個例子中，需要判斷同一個日期下金額存在差異的資料是哪些，這就包含了兩個條件：日期、金額。因此考慮用SUMIFS函數，基本結構為SUMIFS（求和範圍，條件範圍1，條件1，條件範圍2，條件2），還是以系統資料為基礎來核對手動數據，在I3單元格輸入公式為：= SUMIFS(B:B,A:A,E3,B:B,H3)-H3，雙擊填滿。

差異為零的表示資料完全吻合，不為零的就需要篩選出來找出差異原因。

因為資料不多，可以看出來有兩筆8000的是出現在同一個日期，我們使用SUMIFS進行求和時，會把這兩筆進行匯總，實際上並不是真的有差異。對於這種日期一致金額一致但是具體用途不同的，在核對時直接用公式判斷比較麻煩，可以考慮借助輔助列來進行重複性判斷：

在手工資料後面使用公式=COUNTIFS($A$2:A3,A3,$B$2:B3,B3)，意思是對日期與金額相同的進行計數，注意在選擇範圍的時候，對範圍的起始位置要加$進行鎖定，這樣公式在下拉的時候範圍就會遞增，當有重複資料出現時，結果也是遞增的。

同理，對系統資料也依照這個方法處理，公式為：=COUNTIFS($E$2:E3,E3,$H$2:H3,H3)

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

#=SUMIFS(B:B,A:A,E3,B:B ,H3,D:D,I3)-H3，雙擊填滿可看到結果，出現負數就表示手動資料中沒有輸入該項。

今天用了兩個例子來分析資料核對的常用思路，在進行更為複雜的核對工作時，只要掌握VLOOKUP、SUMIF、SUMIFS、COUNTIF和COUNTIFS這幾個函數，同時善於使用輔助列的話，基本上都是可以很快就找到差異的。

相關學習推薦：excel教學

以上是Excel函數學習之財務對帳必會的幾個函數（分享）的詳細內容。更多資訊請關注PHP中文網其他相關文章！