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