巧用Ｅｘｃｅｌ自動查找未達賬項并編制銀行存款余額調(diào)節(jié)表

2007-12-31 00:00:00肖彥鐘燕

中國管理信息化 2007年11期

[摘要] 為保證銀行存款的安全完整，必須定期對銀行存款進行清查，將銀行對賬單與企業(yè)銀行存款日記賬進行核對#65377;本文試圖通過Excel來自動完成對賬及銀行存款余額調(diào)節(jié)表的編制，給出了設(shè)計思路和具體步驟#65377;

[關(guān)鍵詞] 未達賬項;銀行存款余額調(diào)節(jié)表;Excel

[中圖分類號]F232[文獻標識碼]A[文章編號]1673-0194(2007)11-0016-03

銀行存款是企事業(yè)單位流動性最強的資產(chǎn)，為保證銀行存款的安全完整，必須定期對銀行存款進行清查，銀行存款的清查主要手段是通過銀行對賬單與企業(yè)銀行存款日記賬的核對，并編制銀行存款余額調(diào)節(jié)表，核對雙方的余額及賬目實現(xiàn)的#65377;而實際工作中，大多采用人工核對方法，不僅耗時，而且容易出錯#65377;也有部分單位購買的財務(wù)軟件中附帶此項功能或制作了相應(yīng)的軟件通過計算機完成該項工作，但都會產(chǎn)生較大的成本#65377;本文試圖通過常用的Office組件Excel來自動完成對賬及銀行存款余額調(diào)節(jié)表的編制#65377;Excel是財務(wù)人員常用的軟件，使用靈活方便，可以根據(jù)財務(wù)工作中出現(xiàn)的各種情況進行調(diào)整使用#65377;利用Excel自動查找未達賬項并編制銀行存款余額調(diào)節(jié)表步驟如下:

一#65380;設(shè)計思路

如圖1所示，在一個Excel工作簿中設(shè)置3個工作表，分別命名為“原始數(shù)據(jù)區(qū)”#65380;“未達賬項區(qū)”#65380;“余額調(diào)節(jié)表”#65377;①查找并標記未達賬項，將一定會計期間的企業(yè)銀行存款日記賬#65380;銀行對賬單數(shù)據(jù)按預(yù)定的格式導入“原始數(shù)據(jù)區(qū)”，通過預(yù)先輸入的公式，能夠自動將未達賬項直接標記出來;②單獨列示未達賬項，使未達賬項一目了然，即從“原始數(shù)據(jù)區(qū)”中將標記的未達賬項過入“未達賬項區(qū)”;③根據(jù)未達賬項自動編制銀行存款余額調(diào)節(jié)表，即根據(jù)“未達賬項區(qū)”的數(shù)據(jù)，自動編制“余額調(diào)節(jié)表”#65377;

以上3個步驟實際上是同時實現(xiàn)的，只要在“原始數(shù)據(jù)區(qū)”輸入相應(yīng)數(shù)據(jù)，不需進行其他的操作，“余額調(diào)節(jié)表”會根據(jù)我們預(yù)先輸入的公式直接編制出來#65377;

二#65380;具體步驟

1. “原始數(shù)據(jù)區(qū)”的設(shè)計

按照圖2所示，設(shè)計“原始數(shù)據(jù)區(qū)”的格式，圖中的灰色區(qū)域是將來的原始數(shù)據(jù)輸入?yún)^(qū)#65377;

B8，D8，G8，I8四個單元格是用來計算發(fā)生額合計數(shù)的，公式分別為:

B8單元格輸入“=”合計:“SUM(B9:B100)“元””，其中B100可根據(jù)數(shù)據(jù)行數(shù)的多少進行調(diào)整#65377;

D8，G8，I8單元格只需在上述公式中將B改為對應(yīng)字母即可，也可直接將B8單元格復制到上述3個單元格#65377;

“原始數(shù)據(jù)區(qū)”設(shè)計的關(guān)鍵在于如何將未達賬項查找并標記出來#65377;對賬時，我們是將B列的數(shù)據(jù)與I列的數(shù)據(jù)進行核對，能對上的，在該數(shù)據(jù)前的對賬欄內(nèi)打“√”，未對上的打“×”#65377;用同樣的方法核對D列和G列的數(shù)據(jù)#65377;以B列與I列的數(shù)據(jù)核對為例，在A列對賬欄中輸入公式，A9單元格中輸入“=IF(B9=”“，”√”，IF(ISNA(VLOOKUP(B9，$I￥9:$I$100，1，F(xiàn)ALSE))，“×”，“√”))”，將該公式復制到A10至A100單元格#65377;C9單元格中輸入“=IF(D9=“”，“√”，IF(ISNA(VLOOKUP(D9，$G$9:$G$100，1，F(xiàn)ALSE))，“×”，“√”))”，F(xiàn)9單元格中輸入“=IF(G9=“”，“√”，IF(ISNA(VLOOKUP(G9，$D$9:$D$100，1，F(xiàn)ALSE))，“×”，“√”))”，H9單元格中輸入“=IF(I9=“”，“√”，IF(ISNA(VLOOKUP(I9，$B$9:$B$100，1，F(xiàn)ALSE))，“×”，“√”))”#65377;

公式解釋:

以A9單元格的公式為例，“=IF(B9=“”，“√”，IF(ISNA(VLOOKUP(B9，$I$9:$I$100，1，F(xiàn)ALSE))，“×”，“√”))”，公式對應(yīng)內(nèi)容如下①VLOOKUP(B9，$I$9:$I$100，1，F(xiàn)ALSE)在I列相應(yīng)區(qū)域內(nèi)查找B9單元格的數(shù)據(jù)，能找到則顯示該數(shù)字;不能找到，則會出現(xiàn)出錯信息“#N/A”#65377;② ISNA(VLOOKUP(B9，$I$9:$I$100，1，F(xiàn)ALSE))能消除該錯誤信息，若不能找到則返回TRUE;能找到則返回FALSE#65377;③ IF(ISNA(VLOOKUP(B9，$I$9:$I$100，1，F(xiàn)ALSE))，“×”，“√”)若能找到，則顯示“√”;若不能找到或無數(shù)據(jù)，則顯示“×”#65377;④為了將無數(shù)據(jù)和不能找到的相區(qū)別，使無數(shù)據(jù)時也顯示為“√”，又增加了一層IF函數(shù):IF(B9=“”，“√”，IF(ISNA(VLOOKUP(B9，$I$9:$I$100，1，F(xiàn)ALSE))，“×”，“√”))#65377;

2. “未達賬項區(qū)”的設(shè)計

按照圖3所示，設(shè)計“未達賬項區(qū)”的格式#65377;本工作表的主要功能是在“原始數(shù)據(jù)區(qū)”中，將前面標記為“×”的數(shù)據(jù)(即沒有對上的未達賬項)填入本表#65377;

A4#65380;B4#65380;C4#65380;D4均為該列合計數(shù)，A4單元格輸入“=”合計:“SUM(A5:A100)“元””，并將該公式復制到B4#65380;C4#65380;D4#65377;

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ù)組公式則只能返回第一個值#65377;

數(shù)組公式:數(shù)組公式對一組或多組值執(zhí)行多重計算，并返回一個或多個結(jié)果#65377;數(shù)組公式括于大括號 { } 中#65377;按 Ctrl+Shift+Enter時Microsoft Excel 自動在大括號 { } 之間插入公式#65377;數(shù)組公式也可以向下拖曳進行復制#65377;

公式對應(yīng)內(nèi)容如下:① ROW(原始數(shù)據(jù)區(qū)!$A$9:$B$101)，返回原始數(shù)據(jù)區(qū)中相應(yīng)單元格的行號#65377;② 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)#65377;③SMALL(IF(原始數(shù)據(jù)區(qū)!$A$9:$B$101=“×”，ROW(原始數(shù)據(jù)區(qū)!$A$9:$B$101))，ROW(1∶1)):用Small把符合條件的行號按照從小到大的順序列出來#65377;Small是用來列示數(shù)據(jù)記錄中第K個最小值的函數(shù)，而ROW(1∶1)=1，所列示的就是符合條件的行號的第一個最小值#65377;ROW(1∶1)的特點是隨著公式的向下拖曳，每向下一行ROW(n:n)會增加一個數(shù)變?yōu)镽OW(n+1∶n+1)#65377;當A5單元格公式向下拖曳時，ROW(1∶1)會變?yōu)镽OW(2∶2)=2，即返回第二個最小值，第三行依此類推#65377;④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)容列示出來#65377;行號為步驟二的結(jié)果-8，這是由于我們在設(shè)計原始數(shù)據(jù)區(qū)時，對賬單和日記賬的輸入是從第9行開始的#65377;列號為2，是原始數(shù)據(jù)區(qū)!$A￥9:$B$101的第二列#65377;⑤SUM(IF(原始數(shù)據(jù)區(qū)!$A$9:$B$101=“×”，1)):統(tǒng)計原始數(shù)據(jù)區(qū)!$A$9:$B$101中等于“×”的個數(shù)，然后與ROW(1∶1)進行對比，當統(tǒng)計的個數(shù)小于ROW(1∶1)時，公式返回空白值#65377;這里是為了屏蔽錯誤值#65377;⑥=IF(SUM(IF(原始數(shù)據(jù)區(qū)!$A$9:$B$101=“×”，1))

3. “余額調(diào)節(jié)表”的設(shè)計