巧用Excel自动查找未达账项并编制银行存款余额调节表

现金余额调节表格式银行存款余额调节表

单位名称:

年   月   日

账号:

企业账面余额:              银行对账单余额:

加:企业未收款              加:银行未收账款

减:企业未付款              减:银行未付账款

调整后余额:                调整后余额:

现金余额调节表

截至日期:

账面现金 库存现金

余额: 余额:

加:现金已收,帐未收 加:帐已收,现金未收

减:现金已付,帐未付 减;帐已付,现金未付

合计 合计

巧用Ｅｘｃｅｌ自动查找未达账项并编制银行存款余额调节表

来源：本站 作者：编辑08 时间：2008-07-22 点击： 39

[摘 要] 为保证银行存款的安全完整,必须定期对银行存款进行清查,将银行对账单与企业银行存款日记账进行核对?本文试图通过Excel来自动完成对账及银行存款余额调节表的编制,给出了设计思路和具体步骤? 　　[关键词] 未达账项;银行存款余额调节表;Excel 　　[中图分类号]F232[文献标识码]A[文章编号]1673-0194(2007)11-0016-03 　　 　　银行存款是企事业单位流动性最强的资产,为保证银行存款的安全完整,必须定期对银行存款进行清查,银行存款的清查主要手段是通过银行对账单与企业银行存款日记账的核对,并编制银行存款余额调节表,核对双方的余额及账目实现的?而实际工作中,大多采用人工核对方法,不仅耗时,而且容易出错?也有部分单位购买的财务软件中附带此项功能或制作了相应的软件通过计算机完成该项工作,但都会产生较大的成本?本文试图通过常用的Office组件Excel来自动完成对账及银行存款余额调节表的编制?Excel是财务人员常用的软件,使用灵活方便,可以根据财务工作中出现的各种情况进行调整使用?利用Excel自动查找未达账项并编制银行存款余额调节表步骤如下: 　　 　　一?设计思路 　　 　　如图1所示,在一个Excel工作簿中设置3个工作表,分别命名为“原始数据区”?“未达账项区”?“余额调节表”?①查找并标记未达账项,将一定会计期间的企业银行存款日记账?银行对账单数据按预定的格式导入“原始数据区”,通过预先输入的公式,能够自动将未达账项直接标记出来;②单独列示未达账项,使未达账项一目了然,即从“原始数据区”中将标记的未达账项过入“未达账项区”;③根据未达账项自动编制银行存款余额调节表,即根据“未达账项区”的数据,自动编制“余额调节表”? 　　以上3个步骤实际上是同时实现的,只要在“原始数据区”输入相应数据,不需进行其他的操作,“余额调节表”会根据我们预先输入的公式直接编制出来? 　　 　　二?具体步骤 　　 　　1. “原始数据区”的设计 　　 　　按照图2所示,设计“原始数据区”的格式,图中的灰色区域是将来的原始数据输入区? 　　B8,D8,G8,I8四个单元格是用来计算发生额合计数的,公式分别为: 　　B8单元格输入“=”合计:“&SUM(B9:B100)&“元””,其中B100可根据数据行数的多少进行调整? 　　D8,G8,I8单元格只需在上述公式中将B改为对应字母即可,也可直接将B8单元格复制到上述3个单元格? 　　“原始数据区”设计的关键在于如何将未达账项查找并标记出来?对账时,我们是将B列的数据与I列的数据进行核对,能对上的,在该数据前的对账栏内打“√”,未对上的打“×”?用同样的方法核对D列和G列的数据?以B列与I列的数据核对为例,在A列对账栏中输入公式,A9单元格中输入“=IF(B9=”“,”√”,IF(ISNA(VLOOKUP(B9,$I￥9:$I$100,1,FALSE)),“×”,“√”))”,将该公式复制到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)),“×”,“√”))”,公式对应内容如下①VLOOKUP(B9,$I$9:$I$100,1,FALSE)在I列相应区域内查找B9单元格的数据,能找到则显示该数字;不能找到,则会出现出错信息“#N/A”?② ISNA(VLOOKUP(B9,$I$9:$I$100,1,FALSE))能消除该错误信息,若不能找到则返回TRUE;能找到则返回FALSE?③ IF(ISNA(VLOOKUP(B9,$I$9:$I$100,1,FALSE)),“×”,“√”)若能找到,则显示“√”;若不能找到或无数据,则显示“×”?④为了将无数据和不能找到的相区别,使无数据时也显示为“√”,又增加了一层IF函数:IF(B9=“”,“√”,IF(ISNA(VLOOKUP(B9,$I$9:$I$100,1,FALSE)),“×”,“√”))? 　　2. “未达账项区”的设计 　　 　　按照图3所示,设计“未达账项区”的格式?本工作表的主要功能是在“原始数据区”中,将前面标记为“×”的数据(即没有对上的未达账项)填入本表? 　　A4?B4?C4?D4均为该列合计数,A4单元格输入“=”合计:“&SUM(A5:A100)&“元””,并将该公式复制到B4?C4?D4? 　　A5单元格输入“{=IF(SUM(IF(原始数据区!$A$9:$B$101=“×”,1))　　B5单元格输入“{=IF(SUM(IF(原始数据区!$C$9:$D$100=“×”,1))　　C5单元格输入“{=IF(SUM(IF(原始数据区!$F$9:$G$100=“×”,1))　　D5单元格输入“{=IF(SUM(IF(原始数据区!$H$9:$I$100=“×”,1))　　公式解释: 　　以A5单元格的公式为例,“{=IF(SUM(IF(原始数据区!$A$9:$B$101=“×”,1))　　这里要用到数组公式,因为符合条件的数据不止一个,若不用数组公式则只能返回第一个值? 　　数组公式:数组公式对一组或多组值执行多重计算,并返回一个或多个结果?数组公式括于大括号 { } 中?按 Ctrl Shift Enter时Microsoft Excel 自动在大括号 { } 之间插入公式?数组公式也可以向下拖曳进行复制? 　　 　　[摘 要] 为保证银行存款的安全完整,必须定期对银行存款进行清查,将银行对账单与企业银行存款日记账进行核对?本文试图通过Excel来自动完成对账及银行存款余额调节表的编制,给出了设计思路和具体步骤? 　　[关键词] 未达账项;银行存款余额调节表;Excel 　　[中图分类号]F232[文献标识码]A[文章编号]1673-0194(2007)11-0016-03 　　 　　银行存款是企事业单位流动性最强的资产,为保证银行存款的安全完整,必须定期对银行存款进行清查,银行存款的清查主要手段是通过银行对账单与企业银行存款日记账的核对,并编制银行存款余额调节表,核对双方的余额及账目实现的?而实际工作中,大多采用人工核对方法,不仅耗时,而且容易出错?也有部分单位购买的财务软件中附带此项功能或制作了相应的软件通过计算机完成该项工作,但都会产生较大的成本?本文试图通过常用的Office组件Excel来自动完成对账及银行存款余额调节表的编制?Excel是财务人员常用的软件,使用灵活方便,可以根据财务工作中出现的各种情况进行调整使用?利用Excel自动查找未达账项并编制银行存款余额调节表步骤如下: 　　 　　一?设计思路 　　 　　如图1所示,在一个Excel工作簿中设置3个工作表,分别命名为“原始数据区”?“未达账项区”?“余额调节表”?①查找并标记未达账项,将一定会计期间的企业银行存款日记账?银行对账单数据按预定的格式导入“原始数据区”,通过预先输入的公式,能够自动将未达账项直接标记出来;②单独列示未达账项,使未达账项一目了然,即从“原始数据区”中将标记的未达账项过入“未达账项区”;③根据未达账项自动编制银行存款余额调节表,即根据“未达账项区”的数据,自动编制“余额调节表”? 　　以上3个步骤实际上是同时实现的,只要在“原始数据区”输入相应数据,不需进行其他的操作,“余额调节表”会根据我们预先输入的公式直接编制出来? 　　 　　二?具体步骤 　　 　　1. “原始数据区”的设计 　　 　　按照图2所示,设计“原始数据区”的格式,图中的灰色区域是将来的原始数据输入区? 　　B8,D8,G8,I8四个单元格是用来计算发生额合计数的,公式分别为: 　　B8单元格输入“=”合计:“&SUM(B9:B100)&“元””,其中B100可根据数据行数的多少进行调整? 　　D8,G8,I8单元格只需在上述公式中将B改为对应字母即可,也可直接将B8单元格复制到上述3个单元格? 　　“原始数据区”设计的关键在于如何将未达账项查找并标记出来?对账时,我们是将B列的数据与I列的数据进行核对,能对上的,在该数据前的对账栏内打“√”,未对上的打“×”?用同样的方法核对D列和G列的数据?以B列与I列的数据核对为例,在A列对账栏中输入公式,A9单元格中输入“=IF(B9=”“,”√”,IF(ISNA(VLOOKUP(B9,$I￥9:$I$100,1,FALSE)),“×”,“√”))”,将该公式复制到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)),“×”,“√”))”,公式对应内容如下①VLOOKUP(B9,$I$9:$I$100,1,FALSE)在I列相应区域内查找B9单元格的数据,能找到则显示该数字;不能找到,则会出现出错信息“#N/A”?② ISNA(VLOOKUP(B9,$I$9:$I$100,1,FALSE))能消除该错误信息,若不能找到则返回TRUE;能找到则返回FALSE?③ IF(ISNA(VLOOKUP(B9,$I$9:$I$100,1,FALSE)),“×”,“√”)若能找到,则显示“√”;若不能找到或无数据,则显示“×”?④为了将无数据和不能找到的相区别,使无数据时也显示为“√”,又增加了一层IF函数:IF(B9=“”,“√”,IF(ISNA(VLOOKUP(B9,$I$9:$I$100,1,FALSE)),“×”,“√”))? 　　2. “未达账项区”的设计 　　 　　按照图3所示,设计“未达账项区”的格式?本工作表的主要功能是在“原始数据区”中,将前面标记为“×”的数据(即没有对上的未达账项)填入本表? 　　A4?B4?C4?D4均为该列合计数,A4单元格输入“=”合计:“&SUM(A5:A100)&“元””,并将该公式复制到B4?C4?D4? 　　A5单元格输入“{=IF(SUM(IF(原始数据区!$A$9:$B$101=“×”,1))　　B5单元格输入“{=IF(SUM(IF(原始数据区!$C$9:$D$100=“×”,1))　　C5单元格输入“{=IF(SUM(IF(原始数据区!$F$9:$G$100=“×”,1))　　D5单元格输入“{=IF(SUM(IF(原始数据区!$H$9:$I$100=“×”,1))　　公式解释: 　　以A5单元格的公式为例,“{=IF(SUM(IF(原始数据区!$A$9:$B$101=“×”,1))　　这里要用到数组公式,因为符合条件的数据不止一个,若不用数组公式则只能返回第一个值? 　　数组公式:数组公式对一组或多组值执行多重计算,并返回一个或多个结果?数组公式括于大括号 { } 中?按 Ctrl Shift Enter时Microsoft Excel 自动在大括号 { } 之间插入公式?数组公式也可以向下拖曳进行复制? 　　 　　公式对应内容如下:① ROW(原始数据区!$A$9:$B$101),返回原始数据区中相应单元格的行号?② IF(原始数据区!$A$9:$B$101=“×”,ROW(原始数据区!$A$9:$B$101)) 如果原始数据区!$A$9:$B$101中有等于“×”的记录则返回其对应的行号ROW(原始数据区!$A$9:$B$101)?③SMALL(IF(原始数据区!$A$9:$B$101=“×”,ROW(原始数据区!$A$9:$B$101)),ROW(1∶1)):用Small把符合条件的行号按照从小到大的顺序列出来?Small是用来列示数据记录中第K个最小值的函数,而ROW(1∶1)=1,所列示的就是符合条件的行号的第一个最小值?ROW(1∶1)的特点是随着公式的向下拖曳,每向下一行ROW(n:n)会增加一个数变为ROW(n 1∶n 1)?当A5单元格公式向下拖曳时,ROW(1∶1)会变为ROW(2∶2)=2,即返回第二个最小值,第三行依此类推?④INDEX(原始数据区!$A$9:$B$101,SMALL(IF(原始数据区!$A$9:$B$101=“×”,ROW(原始数据区!$A$9:$B$101)),ROW(1∶1))-8,2):用INDEX把符合条件的指定单元格的内容列示出来?行号为步骤二的结果-8,这是由于我们在设计原始数据区时,对账单和日记账的输入是从第9行开始的?列号为2,是原始数据区!$A￥9:$B$101的第二列?⑤SUM(IF(原始数据区!$A$9:$B$101=“×”,1)):统计原始数据区!$A$9:$B$101中等于“×”的个数,然后与ROW(1∶1)进行对比,当统计的个数小于ROW(1∶1)时,公式返回空白值?这里是为了屏蔽错误值?⑥=IF(SUM(IF(原始数据区!$A$9:$B$101=“×”,1))　　3. “余额调节表”的设计 　　 　　按照图4所示,设计“余额调节表”的格式?本工作表的主要功能是将前两张工作表中的数据填入本表? 　　B4?D4单元格可直接从“原始数据区”取数,B5?B6?D5?D6单元格将“未达账项区”的数据进行汇总? 　　B4单元格输入“=原始数据区!B6” 　　B5单元格输入“=SUM(未达账项区!C5:C100)” 　　B6单元格输入“=SUM(未达账项区!D5:D100)” 　　B7单元格输入“=B4 B5-B6” 　　D4单元格输入“=原始数据区!G6” 　　D5单元格输入“=SUM(未达账项区!B5:B100)” 　　D6单元格输入“=SUM(未达账项区!A5:A100)” 　　D7单元格输入“=D4 D5-D6” 　　 　　三?使用中的注意事项 　　 　　1. 期初未达账项问题 　　如果存在期初未达账项,只需将其也输入“原始数据区”即可?如果要与本期的数据相区别,可在表格中再加上一栏时间栏? 　　2. 只核对数字可能出现的问题及解决办法 　　由于核对时是以金额为依据的,当存在相同金额时核对可能出错,此时可以结合支票号码手动进行核对? 　　 巧用Ｅｘｃｅｌ自动查找未达账项并编制银行存款余额调节表 肖　彦　钟　燕