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

巧用Exce1自动查找未达账项并编制银行存款余额调节表银行存款是企事业单位流动性最强的资产,为保证银行存款的安全完整,必须定期对银行存款进行清查,银行存款的清查主要手段是通过银行对账单与企业银行存款日记账的核对,并编制银行存款余额调节表,核对双方的余额及账目实现的｡而实际工作中,大多采用人工核对方法,不仅耗时,而且容易出错｡也有部分单位购买的财务软件中附带此项功能或制作了相应的软件通过计算机完成该项工作,但都会产生较大的成本｡本文试图通过常用的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列的数据进行核对,能对上的,在该数据前的对账栏内打,未对上的打“X”｡用同样的方法核对D列和G列的数据｡以B列与I列的数据核对为例,在A列对账栏中输入公式,A9单¥9:$I$100,1,FALSE)),“X"，“，”))”，将该公式复制到A10至A100单元格｡C9单元格中输入=IF(D9=,，IF(ISNA(VLOOKUP(D9,$G$9:$G$100,1,FALSE)), “X”)) ” ,F9 单元格中输入“=IF(G9=,，IF(ISNA(VLOOKUP(G9,$D$9:$D$100,1,FALSE)),“X”，“一))”，H9单元格中输入“=IF(I9=,，IF(ISNA(VLOOKUP(I9,$B$9:$B$100,1,FALSE)),“X”,))”｡公式解释:以A9单元格的公式为例，”=IF(B9=,V,IF(ISNA(VLOOKUP(B9,$I$9:$I$100,1,FALSE)),“X"，“，”))”，公式对应内容如下①VLOOKUP(B9,$I$9:$I$100,1,FALSE)在I列相应区域内查找B9单元格的数据,能找到则显示该数字;不能找到,则会出现出错信息“#N/A”｡②ISNA(VLOOKUP(B9,$I$9:$I$100,1,FALSE))能消除该错误信息,若不能找到则返回TRUE能找到则返回FALSE｡DIF(ISNA(VLOOKUP(B9,$I$9:$I$100,1,FALSE)),“X”,“，”)若能找到,则显示“，”；若不能找到或无数据,则显示“X”｡④为了将无数据和不能找到的相区别，使无数据时也显示为",又增加了一层IF函数:IF(B9= , ,IF(ISNA(VLOO KUP(B9,$I$9:$I$100,1,FALSE)), “X” ))｡2. “未达账项区”的设计按照图3所示,设计“未达账项区”的格式｡本工作表的主要功能是在“原始数据区”中，将前面标记为“x”的数据(即没有对上的未达账项)填入本表｡A4､B4､C4､D4均为该歹U合计数,A4单元格输入“=”合计："&SUM(A5:A100)&“元'”，并将该公式复制到B4､C4､D4｡A5单元格输入“{=IF(SUM(IF(原始数据区!$A$9:$B$101="X"，1))公式对应内容如下：①ROW原始数据区！$A$9:$B$101),返回原始数据区中相应单元格的行号｡②IF(原始数据区!$A$9:$B$101="乂”,ROW^始数据区！$A$9:$B$101))如果原始数据区！$A$9:$B$101中有等于“X”的记录则返回其对应的行号ROW/((始数据区！$A$9:$B$101)｡③SMALL(IF(原始数据区！$A$9:$B$101="X"，ROW原始数据区!$A$9:$B$101)),ROW(1：1)):用Small把符合条件的行号按照从小到大的顺序列出来｡Small是用来列示数据记录中第K个最小值的函数,而ROW(11)=1,所列示的就是符合条件的行号的第一个最小值｡ROW(11)的特点是随着公式的向下拖曳,每向下一行ROW(n:n没增加一个数变为ROW(n+1n+1)｡当A5单元格公式向下拖曳时，ROW(1：1)会变为ROW(22)=2,即返回第二个最小值,第三行依此类推｡④INDEX原始数据区！$A$9:$B$101,SMALL(IF(原始数据区!$A$9:$B$101="X"，ROW(K始数据区!$A$9:$B$101)),ROW(1：1))-8,2):用INDEX把符合条件的指定单元格的内容列示出来｡行号为步骤二的结果-8,这是由于我们在设计原始数据区时,对账单和日记账的输入是从第9行开始的｡列号为2,是原始数据区！$A¥9:$B$101的第二歹U｡⑤SUM(IF(原始数据区！$A$9:$B$101="X",1)):统计原始数据区！$A$9:$B$101中等于“X”的个数，然后与ROW(11)进行^^匕，当统计的个数小于ROW(11)时，公式返回空白值｡这里是为了屏蔽错误值｡⑥=IF(SUM(IF(原始数据区！$A$9:$B$101="X",1))。