[摘 要] 為保證銀行存款的安全完整,必須定期對銀行存款進行清查,將銀行對賬單與企業(yè)銀行存款日記賬進行核對?本文試圖通過Excel來自動完成對賬及銀行存款余額調(diào)節(jié)表的編制,給出了設(shè)計思路和具體步驟? 　　[關(guān)鍵詞] 未達賬項;銀行存款余額調(diào)節(jié)表;Excel 　　[中圖分類號]F232[文獻標(biāo)識碼]A[文章編號]1673-0194(2007)11-0016-03 　　 　　銀行存款是企事業(yè)單位流動性最強的資產(chǎn),為保證銀行存款的安全完整,必須定期對銀行存款進行清查,銀行存款的清查主要手段是通過銀行對賬單與企業(yè)銀行存款日記賬的核對,并編制銀行存款余額調(diào)節(jié)表,核對雙方的余額及賬目實現(xiàn)的?而實際工作中,大多采用人工核對方法,不僅耗時,而且容易出錯?也有部分單位購買的財務(wù)軟件中附帶此項功能或制作了相應(yīng)的軟件通過計算機完成該項工作,但都會產(chǎn)生較大的成本?本文試圖通過常用的Office組件Excel來自動完成對賬及銀行存款余額調(diào)節(jié)表的編制?Excel是財務(wù)人員常用的軟件,使用靈活方便,可以根據(jù)財務(wù)工作中出現(xiàn)的各種情況進行調(diào)整使用?利用Excel自動查找未達賬項并編制銀行存款余額調(diào)節(jié)表步驟如下: 　　 　　一?設(shè)計思路 　　 　　如圖1所示,在一個Excel工作簿中設(shè)置3個工作表,分別命名為“原始數(shù)據(jù)區(qū)”?“未達賬項區(qū)”?“余額調(diào)節(jié)表”?①查找并標(biāo)記未達賬項,將一定會計期間的企業(yè)銀行存款日記賬?銀行對賬單數(shù)據(jù)按預(yù)定的格式導(dǎo)入“原始數(shù)據(jù)區(qū)”,通過預(yù)先輸入的公式,能夠自動將未達賬項直接標(biāo)記出來;②單獨列示未達賬項,使未達賬項一目了然,即從“原始數(shù)據(jù)區(qū)”中將標(biāo)記的未達賬項過入“未達賬項區(qū)”;③根據(jù)未達賬項自動編制銀行存款余額調(diào)節(jié)表,即根據(jù)“未達賬項區(qū)”的數(shù)據(jù),自動編制“余額調(diào)節(jié)表”? 　　以上3個步驟實際上是同時實現(xiàn)的,只要在“原始數(shù)據(jù)區(qū)”輸入相應(yīng)數(shù)據(jù),不需進行其他的操作,“余額調(diào)節(jié)表”會根據(jù)我們預(yù)先輸入的公式直接編制出來? 　　 　　二?具體步驟 　　 　　1. “原始數(shù)據(jù)區(qū)”的設(shè)計 　　 　　按照圖2所示,設(shè)計“原始數(shù)據(jù)區(qū)”的格式,圖中的灰色區(qū)域是將來的原始數(shù)據(jù)輸入?yún)^(qū)? 　　B8,D8,G8,I8四個單元格是用來計算發(fā)生額合計數(shù)的,公式分別為: 　　B8單元格輸入“=”合計:“&SUM(B9:B100)&“元””,其中B100可根據(jù)數(shù)據(jù)行數(shù)的多少進行調(diào)整? 　　D8,G8,I8單元格只需在上述公式中將B改為對應(yīng)字母即可,也可直接將B8單元格復(fù)制到上述3個單元格? 　　“原始數(shù)據(jù)區(qū)”設(shè)計的關(guān)鍵在于如何將未達賬項查找并標(biāo)記出來?對賬時,我們是將B列的數(shù)據(jù)與I列的數(shù)據(jù)進行核對,能對上的,在該數(shù)據(jù)前的對賬欄內(nèi)打“√”,未對上的打“×”?用同樣的方法核對D列和G列的數(shù)據(jù)?以B列與I列的數(shù)據(jù)核對為例,在A列對賬欄中輸入公式,A9單元格中輸入“=IF(B9=”“,”√”,IF(ISNA(VLOOKUP(B9,$I￥9:$I$100,1,FALSE)),“×”,“√”))”,將該公式復(fù)制到A10至A100單元格?C9單元格中輸入“=IF(D9=“”,“√”,IF(ISNA(VLOOKUP(D9,$G$9:$G$100,1,FALSE)),“×”,“√”))”,F9單元格中輸入“=IF(G9=“”,“√”,IF(ISNA(VLOOKUP(G9,$D$9:$D$100,1,FALSE)),“×”,“√”))”,H9單元格中輸入“=IF(I9=“”,“√”,IF(ISNA(VLOOKUP(I9,$B$9:$B$100,1,FALSE)),“×”,“√”))”? 　　公式解釋: 　　以A9單元格的公式為例,“=IF(B9=“”,“√”,IF(ISNA(VLOOKUP(B9,$I$9:$I$100,1,FALSE)),“×”,“√”))”,公式對應(yīng)內(nèi)容如下①VLOOKUP(B9,$I$9:$I$100,1,FALSE)在I列相應(yīng)區(qū)域內(nèi)查找B9單元格的數(shù)據(jù),能找到則顯示該數(shù)字;不能找到,則會出現(xiàn)出錯信息“#N/A”?② ISNA(VLOOKUP(B9,$I$9:$I$100,1,FALSE))能消除該錯誤信息,若不能找到則返回TRUE;能找到則返回FALSE?③ IF(ISNA(VLOOKUP(B9,$I$9:$I$100,1,FALSE)),“×”,“√”)若能找到,則顯示“√”;若不能找到或無數(shù)據(jù),則顯示“×”?④為了將無數(shù)據(jù)和不能找到的相區(qū)別,使無數(shù)據(jù)時也顯示為“√”,又增加了一層IF函數(shù):IF(B9=“”,“√”,IF(ISNA(VLOOKUP(B9,$I$9:$I$100,1,FALSE)),“×”,“√”))? 　　2. “未達賬項區(qū)”的設(shè)計 　　 　　按照圖3所示,設(shè)計“未達賬項區(qū)”的格式?本工作表的主要功能是在“原始數(shù)據(jù)區(qū)”中,將前面標(biāo)記為“×”的數(shù)據(jù)(即沒有對上的未達賬項)填入本表? 　　A4?B4?C4?D4均為該列合計數(shù),A4單元格輸入“=”合計:“&SUM(A5:A100)&“元””,并將該公式復(fù)制到B4?C4?D4? 　　A5單元格輸入“{=IF(SUM(IF(原始數(shù)據(jù)區(qū)!$A$9:$B$101=“×”,1))　　B5單元格輸入“{=IF(SUM(IF(原始數(shù)據(jù)區(qū)!$C$9:$D$100=“×”,1))　　C5單元格輸入“{=IF(SUM(IF(原始數(shù)據(jù)區(qū)!$F$9:$G$100=“×”,1))　　D5單元格輸入“{=IF(SUM(IF(原始數(shù)據(jù)區(qū)!$H$9:$I$100=“×”,1))　　公式解釋: 　　以A5單元格的公式為例,“{=IF(SUM(IF(原始數(shù)據(jù)區(qū)!$A$9:$B$101=“×”,1))　　這里要用到數(shù)組公式,因為符合條件的數(shù)據(jù)不止一個,若不用數(shù)組公式則只能返回第一個值? 　　數(shù)組公式:數(shù)組公式對一組或多組值執(zhí)行多重計算,并返回一個或多個結(jié)果?數(shù)組公式括于大括號 { } 中?按 Ctrl Shift Enter時Microsoft Excel 自動在大括號 { } 之間插入公式?數(shù)組公式也可以向下拖曳進行復(fù)制? 　　 　　[摘 要] 為保證銀行存款的安全完整,必須定期對銀行存款進行清查,將銀行對賬單與企業(yè)銀行存款日記賬進行核對?本文試圖通過Excel來自動完成對賬及銀行存款余額調(diào)節(jié)表的編制,給出了設(shè)計思路和具體步驟? 　　[關(guān)鍵詞] 未達賬項;銀行存款余額調(diào)節(jié)表;Excel 　　[中圖分類號]F232[文獻標(biāo)識碼]A[文章編號]1673-0194(2007)11-0016-03 　　 　　銀行存款是企事業(yè)單位流動性最強的資產(chǎn),為保證銀行存款的安全完整,必須定期對銀行存款進行清查,銀行存款的清查主要手段是通過銀行對賬單與企業(yè)銀行存款日記賬的核對,并編制銀行存款余額調(diào)節(jié)表,核對雙方的余額及賬目實現(xiàn)的?而實際工作中,大多采用人工核對方法,不僅耗時,而且容易出錯?也有部分單位購買的財務(wù)軟件中附帶此項功能或制作了相應(yīng)的軟件通過計算機完成該項工作,但都會產(chǎn)生較大的成本?本文試圖通過常用的Office組件Excel來自動完成對賬及銀行存款余額調(diào)節(jié)表的編制?Excel是財務(wù)人員常用的軟件,使用靈活方便,可以根據(jù)財務(wù)工作中出現(xiàn)的各種情況進行調(diào)整使用?利用Excel自動查找未達賬項并編制銀行存款余額調(diào)節(jié)表步驟如下: 　　 　　一?設(shè)計思路 　　 　　如圖1所示,在一個Excel工作簿中設(shè)置3個工作表,分別命名為“原始數(shù)據(jù)區(qū)”?“未達賬項區(qū)”?“余額調(diào)節(jié)表”?①查找并標(biāo)記未達賬項,將一定會計期間的企業(yè)銀行存款日記賬?銀行對賬單數(shù)據(jù)按預(yù)定的格式導(dǎo)入“原始數(shù)據(jù)區(qū)”,通過預(yù)先輸入的公式,能夠自動將未達賬項直接標(biāo)記出來;②單獨列示未達賬項,使未達賬項一目了然,即從“原始數(shù)據(jù)區(qū)”中將標(biāo)記的未達賬項過入“未達賬項區(qū)”;③根據(jù)未達賬項自動編制銀行存款余額調(diào)節(jié)表,即根據(jù)“未達賬項區(qū)”的數(shù)據(jù),自動編制“余額調(diào)節(jié)表”? 　　以上3個步驟實際上是同時實現(xiàn)的,只要在“原始數(shù)據(jù)區(qū)”輸入相應(yīng)數(shù)據(jù),不需進行其他的操作,“余額調(diào)節(jié)表”會根據(jù)我們預(yù)先輸入的公式直接編制出來? 　　 　　二?具體步驟 　　 　　1. “原始數(shù)據(jù)區(qū)”的設(shè)計 　　 　　按照圖2所示,設(shè)計“原始數(shù)據(jù)區(qū)”的格式,圖中的灰色區(qū)域是將來的原始數(shù)據(jù)輸入?yún)^(qū)? 　　B8,D8,G8,I8四個單元格是用來計算發(fā)生額合計數(shù)的,公式分別為: 　　B8單元格輸入“=”合計:“&SUM(B9:B100)&“元””,其中B100可根據(jù)數(shù)據(jù)行數(shù)的多少進行調(diào)整? 　　D8,G8,I8單元格只需在上述公式中將B改為對應(yīng)字母即可,也可直接將B8單元格復(fù)制到上述3個單元格? 　　“原始數(shù)據(jù)區(qū)”設(shè)計的關(guān)鍵在于如何將未達賬項查找并標(biāo)記出來?對賬時,我們是將B列的數(shù)據(jù)與I列的數(shù)據(jù)進行核對,能對上的,在該數(shù)據(jù)前的對賬欄內(nèi)打“√”,未對上的打“×”?用同樣的方法核對D列和G列的數(shù)據(jù)?以B列與I列的數(shù)據(jù)核對為例,在A列對賬欄中輸入公式,A9單元格中輸入“=IF(B9=”“,”√”,IF(ISNA(VLOOKUP(B9,$I￥9:$I$100,1,FALSE)),“×”,“√”))”,將該公式復(fù)制到A10至A100單元格?C9單元格中輸入“=IF(D9=“”,“√”,IF(ISNA(VLOOKUP(D9,$G$9:$G$100,1,FALSE)),“×”,“√”))”,F9單元格中輸入“=IF(G9=“”,“√”,IF(ISNA(VLOOKUP(G9,$D$9:$D$100,1,FALSE)),“×”,“√”))”,H9單元格中輸入“=IF(I9=“”,“√”,IF(ISNA(VLOOKUP(I9,$B$9:$B$100,1,FALSE)),“×”,“√”))”? 　　公式解釋: 　　以A9單元格的公式為例,“=IF(B9=“”,“√”,IF(ISNA(VLOOKUP(B9,$I$9:$I$100,1,FALSE)),“×”,“√”))”,公式對應(yīng)內(nèi)容如下①VLOOKUP(B9,$I$9:$I$100,1,FALSE)在I列相應(yīng)區(qū)域內(nèi)查找B9單元格的數(shù)據(jù),能找到則顯示該數(shù)字;不能找到,則會出現(xiàn)出錯信息“#N/A”?② ISNA(VLOOKUP(B9,$I$9:$I$100,1,FALSE))能消除該錯誤信息,若不能找到則返回TRUE;能找到則返回FALSE?③ IF(ISNA(VLOOKUP(B9,$I$9:$I$100,1,FALSE)),“×”,“√”)若能找到,則顯示“√”;若不能找到或無數(shù)據(jù),則顯示“×”?④為了將無數(shù)據(jù)和不能找到的相區(qū)別,使無數(shù)據(jù)時也顯示為“√”,又增加了一層IF函數(shù):IF(B9=“”,“√”,IF(ISNA(VLOOKUP(B9,$I$9:$I$100,1,FALSE)),“×”,“√”))? 　　2. “未達賬項區(qū)”的設(shè)計 　　 　　按照圖3所示,設(shè)計“未達賬項區(qū)”的格式?本工作表的主要功能是在“原始數(shù)據(jù)區(qū)”中,將前面標(biāo)記為“×”的數(shù)據(jù)(即沒有對上的未達賬項)填入本表? 　　A4?B4?C4?D4均為該列合計數(shù),A4單元格輸入“=”合計:“&SUM(A5:A100)&“元””,并將該公式復(fù)制到B4?C4?D4? 　　A5單元格輸入“{=IF(SUM(IF(原始數(shù)據(jù)區(qū)!$A$9:$B$101=“×”,1))　　B5單元格輸入“{=IF(SUM(IF(原始數(shù)據(jù)區(qū)!$C$9:$D$100=“×”,1))　　C5單元格輸入“{=IF(SUM(IF(原始數(shù)據(jù)區(qū)!$F$9:$G$100=“×”,1))　　D5單元格輸入“{=IF(SUM(IF(原始數(shù)據(jù)區(qū)!$H$9:$I$100=“×”,1))　　公式解釋: 　　以A5單元格的公式為例,“{=IF(SUM(IF(原始數(shù)據(jù)區(qū)!$A$9:$B$101=“×”,1))　　這里要用到數(shù)組公式,因為符合條件的數(shù)據(jù)不止一個,若不用數(shù)組公式則只能返回第一個值? 　　數(shù)組公式:數(shù)組公式對一組或多組值執(zhí)行多重計算,并返回一個或多個結(jié)果?數(shù)組公式括于大括號 { } 中?按 Ctrl Shift Enter時Microsoft Excel 自動在大括號 { } 之間插入公式?數(shù)組公式也可以向下拖曳進行復(fù)制? 　　 　　公式對應(yīng)內(nèi)容如下:① ROW(原始數(shù)據(jù)區(qū)!$A$9:$B$101),返回原始數(shù)據(jù)區(qū)中相應(yīng)單元格的行號?② IF(原始數(shù)據(jù)區(qū)!$A$9:$B$101=“×”,ROW(原始數(shù)據(jù)區(qū)!$A$9:$B$101)) 如果原始數(shù)據(jù)區(qū)!$A$9:$B$101中有等于“×”的記錄則返回其對應(yīng)的行號ROW(原始數(shù)據(jù)區(qū)!$A$9:$B$101)?③SMALL(IF(原始數(shù)據(jù)區(qū)!$A$9:$B$101=“×”,ROW(原始數(shù)據(jù)區(qū)!$A$9:$B$101)),ROW(1∶1)):用Small把符合條件的行號按照從小到大的順序列出來?Small是用來列示數(shù)據(jù)記錄中第K個最小值的函數(shù),而ROW(1∶1)=1,所列示的就是符合條件的行號的第一個最小值?ROW(1∶1)的特點是隨著公式的向下拖曳,每向下一行ROW(n:n)會增加一個數(shù)變?yōu)镽OW(n 1∶n 1)?當(dāng)A5單元格公式向下拖曳時,ROW(1∶1)會變?yōu)镽OW(2∶2)=2,即返回第二個最小值,第三行依此類推?④INDEX(原始數(shù)據(jù)區(qū)!$A$9:$B$101,SMALL(IF(原始數(shù)據(jù)區(qū)!$A$9:$B$101=“×”,ROW(原始數(shù)據(jù)區(qū)!$A$9:$B$101)),ROW(1∶1))-8,2):用INDEX把符合條件的指定單元格的內(nèi)容列示出來?行號為步驟二的結(jié)果-8,這是由于我們在設(shè)計原始數(shù)據(jù)區(qū)時,對賬單和日記賬的輸入是從第9行開始的?列號為2,是原始數(shù)據(jù)區(qū)!$A￥9:$B$101的第二列?⑤SUM(IF(原始數(shù)據(jù)區(qū)!$A$9:$B$101=“×”,1)):統(tǒng)計原始數(shù)據(jù)區(qū)!$A$9:$B$101中等于“×”的個數(shù),然后與ROW(1∶1)進行對比,當(dāng)統(tǒng)計的個數(shù)小于ROW(1∶1)時,公式返回空白值?這里是為了屏蔽錯誤值?⑥=IF(SUM(IF(原始數(shù)據(jù)區(qū)!$A$9:$B$101=“×”,1))　　3. “余額調(diào)節(jié)表”的設(shè)計 　　 　　按照圖4所示,設(shè)計“余額調(diào)節(jié)表”的格式?本工作表的主要功能是將前兩張工作表中的數(shù)據(jù)填入本表? 　　B4?D4單元格可直接從“原始數(shù)據(jù)區(qū)”取數(shù),B5?B6?D5?D6單元格將“未達賬項區(qū)”的數(shù)據(jù)進行匯總? 　　B4單元格輸入“=原始數(shù)據(jù)區(qū)!B6” 　　B5單元格輸入“=SUM(未達賬項區(qū)!C5:C100)” 　　B6單元格輸入“=SUM(未達賬項區(qū)!D5:D100)” 　　B7單元格輸入“=B4 B5-B6” 　　D4單元格輸入“=原始數(shù)據(jù)區(qū)!G6” 　　D5單元格輸入“=SUM(未達賬項區(qū)!B5:B100)” 　　D6單元格輸入“=SUM(未達賬項區(qū)!A5:A100)” 　　D7單元格輸入“=D4 D5-D6” 　　 　　三?使用中的注意事項 　　 　　1. 期初未達賬項問題 　　如果存在期初未達賬項,只需將其也輸入“原始數(shù)據(jù)區(qū)”即可?如果要與本期的數(shù)據(jù)相區(qū)別,可在表格中再加上一欄時間欄? 　　2. 只核對數(shù)字可能出現(xiàn)的問題及解決辦法 　　由于核對時是以金額為依據(jù)的,當(dāng)存在相同金額時核對可能出錯,此時可以結(jié)合支票號碼手動進行核對? 　　 巧用Ｅｘｃｅｌ自動查找未達賬項并編制銀行存款余額調(diào)節(jié)表 肖　彥　鐘　燕