运用EXCEL实现银行对账自动化.doc

运用运用运用运用EXCELEXCELEXCELEXCEL实现银行对账自动化实现银行对账自动化实现银行对账自动化实现银行对账自动化 许国 吴光校 银行对账是指每月单位的银行存款日记账和银行对账单核对,勾销已达账,生成银行存款余额调节表的过程，这是保证资金安全的最重要手段。一般财务系统也带有银行对账模块，但在实际操作中存在这样那样的问题，要么银行对账单的数据文件格式与财务系统不符, 要么财务系统的对账模块不能正常使用，这样只能用手工核对，但这要花较长时间，而且容易出错，一旦出错，核查起来更麻烦。本人充分运用Excel的VBA功能实现了银行对账自动化。 一、收集对账数据 1、从账务系统中导出银行存款明细账的数据，并保存为EXCEL文档，然后把相应的内容复制到图1中对应的栏目内。 2、银行对账单数据文件一般为DBF格式，也有的为EXCEL文档，如果为DBF文件，就需要转化为EXCEL文档，然后把对应的内容复制到图1中对应的栏目内。处理完后的EXCEL文档如图1，并把“sheet1”工作表重命名为“对账数据”。 图1 二、自动核对对账数据 1、在“对账数据”工作表中选择工具 宏Visual Basic编辑器菜单，进入VBA编辑窗口，执行插入模块命令，插入模块1，然后在编辑窗口中输入如下代码。 Sub zdhd() Dim Irow As Integer, i As Integer, j As Integer Irow = a1.CurrentRegion.Rows.Count 取得行数 For i = 3 To Irow 数据从第3行开始 For j = 3 To Irow If Cells(i, C) = Cells(j, K) Then 如果C列i行等于K列j行 If Cells(i, D) = Cells(j, L) Then 并且D列i行等于L列j行 If Cells(i, C) Then 并且C列i行不等于空 Cells(j, L) = 则L列j行的值为 Cells(i, D) = 则D列i行的值为 Exit For 如果找到该单元格,则退出查找 lls(i, D) = Then 鈥欏鏋淒鍒梚琛岀瓑浜庣 End If:End If:End If:Next j:Next i For i = 3 To Irow 注释与上相似 For j = 3 To Irow If Cells(i, E) = Cells(j, I) Then If Cells(i, F) = Cells(j, J) Then If Cells(i, E) Then Cells(j, J) = :Cells(i, F) = Exit For：End If：End If：End If：Next j：Next i End Sub 注：单引号后的文字为代码注释，可以不输入，另外为节省篇幅,在一行中使用了冒号隔开多条指令(下同)。 2、源代码编辑无误后保存并退出，然后选择视图 工具栏 窗体命令，出现窗体控件，单击按钮，光标变成小十字形，然后将小十字形移到适当位置，按下左键拖动鼠标，直到出现的方框大小合适后，释放鼠标左键，形成一个按钮，点击按钮输入“自动核对”文字，然后在此按钮上单击右键，选择指定宏命令，弹出“宏”对话框，选定宏“zdhd”，单击对话框右上方的确定按钮，完成后按“自动核对”按钮即可勾对已达账，如图2。 图2 三、生成银行存款调节表 1、把“sheet2”工作表重命名为“银行调节表”，设置好1至9行的内容,然后按前面的方法插入一个模块2，并在编辑窗口中输入如下代码。 Sub lhtjb() Dim Irow As Integer, i As Integer Irow = Sheet1.a1.CurrentRegion.Rows.Count Sheets(对账数据).Activate 激活对账数据工作表 For i = 3 To Irow 数据从第3行开始 If Cells(i, D) = Then 如果D列i行等于空 ow If Cells(i, C) = Cells(j, K) Then 雚MJP End If:End If:End If:Next j:Next i For i = 3 To Irow 注释与上相似 For j = 3 To Irow If Cells(i, E) = Cells(j, I) Then If Cells(i, F) = Cells(j, J) Then If Cells(i, E) Then Cells(j, J) = :Cells(i, F) = Exit For：End If：End If：End If：Next j：Next i End Sub 注：单引号后的文字为代码注释，可以不输入，另外为节省篇幅,在一行中使用了冒号隔开多条指令(下同)。 2、源代码编辑无误后保存并退出，然后选择视图 工具栏 窗体命令，出现窗体控件，单击按钮，光标变成小十字形，然后将小十字形移到适当位置，按下左键拖动鼠标，直到出现的方框大小合适后，释放鼠标左键，形成一个按钮，点击按钮输入“自动核对”文字，然后在此按钮上单击右键，选择指定宏命令，弹出“宏”对话框，选定宏“zdhd”，单击对话框右上方的确定按钮，完成后按“自动核对”按钮即可勾对已达账，如图2。 图2 三、生成银行存款调节表 1、把“sheet2”工作表重命名为“银行调节表”，设置好1至9行的内容,然后按前面的方法插入一个模块2，并在编辑窗口中输入如下代码。 Sub lhtjb() Dim Irow As Integer, i As Integer Irow = Sheet1.a1.CurrentRegion.Rows.Count Sheets(对账数据).Activate 激活对账数据工作表 For i = 3 To Irow 数据从第3行开始 If Cells(i, D) = Then 如果D列i行等于空 lls(i, D) = Then 如果D列i行等于空 If Cells(i, C) Then 并且C列i行不等于空 Range(Cells(i, A), Cells(i, C).Select 则选中A列i行至C列i行单元格 Selection.Copy _然后复制到银行调节表中相应的单元格中 Sheets(银行调节表).Cells(i + 7, A) End If:End If:Next i For i = 3 To Irow 注释与上相似 If Cells(i, F) = Then:If Cells(i, E) Then Range(Cells(i, 1), Cells(i, 3).Select Selection.Copy Sheet2.Cells(i + 7, A) Cells(i, E).Select Selection.Copy Sheet2.Cells(i + 7, D) End If：End If：Next i For i = 3 To Irow 注释与上相似 If Cells(i, J) = Then:If Cells(i, I) Then Range(Cells(i, H), Cells(i, I).Select Selection.Copy Sheet2.Cells(i + 7, E) End If：End If：Next i For i = 2 To Irow 注释与上相似 If Sheet1.Cells(i, J) = Then If Sheet1.Cells(i, K) Then Cells(i, H).Select Selection.Copy Sheet2.Cells(i + 7, E) Cells(i, K).Select ,然后按前面的方法插入一个模块2，并在编辑窗口中雚M擯If Cells(i, C) Then 并且C列i行不等于空 Range(Cells(i, A), Cells(i, C).Select 则选中A列i行至C列i行单元格 Selection.Copy _然后复制到银行调节表中相应的单元格中 Sheets(银行调节表).Cells(i + 7, A) End If:End If:Next i For i = 3 To Irow 注释与上相似 If Cells(i, F) = Then:If Cells(i, E) Then Range(Cells(i, 1), Cells(i, 3).Select Selection.Copy Sheet2.Cells(i + 7, A) Cells(i, E).Select Selection.Copy Sheet2.Cells(i + 7, D) End If：End If：Next i For i = 3 To Irow 注释与上相似 If Cells(i, J) = Then:If Cells(i, I) Then Range(Cells(i, H), Cells(i, I).Select Selection.Copy Sheet2.Cells(i + 7, E) End If：End If：Next i For i = 2 To Irow 注释与上相似 If Sheet1.Cells(i, J) = Then If Sheet1.Cells(i, K) Then Cells(i, H).Select Selection.Copy Sheet2.Cells(i + 7, E) Cells(i, K).Select lls(i + 7, E) Cells(i, K).Select Selection.Copy Sheet2.Cells(i + 7, G) End If：End If：Next i Sheets(银行调节表).Activate 激活银行调节表工作表 End Sub 2、源代码编辑无误后保存并退出，再按上面的方法创建一个窗体按钮，并输入“生成调节表”文字，然后把“lhtjb”宏指定给此按钮，完成后按“生成调节表”按钮即可生成如图3所示的银行存款余额调节表。 图3 四、整理银行存款余额调节表 1、仍按前面的方法插入一个模块3，然后在编辑窗口中输入如下代码。 Sub lhtzbzl() Dim i As Integer,Irow As Integer Irow = Sheet1.a1.CurrentRegion.Rows.Count 取得行数 Sheets(银行调节表).Activate 激活银行调节表工作表 For i = 10 To Irow 数据从第10行开始 If Cells(i, A) = Then 如果A列i行等于空 则选中A列i行至D列i行单元格 Sheet2.Range(Cells(i, A), Cells(i, D).Select Selection.Delete Shift:=xlUp 向上删除选中单元格 End If：Next i：For i = 10 To Irow 同上 If Cells(i, E) = Then Sheet2.Range(Cells(i, E), Cells(i, G).Select Selection.Delete Shift:=xlUp End If：Next i：Cells(10, H).Activate End Sub 诳? Range(Cells(i, A), Cells(i, C).Select 濋q耡P 2、源代码编辑无误后保存并退出，再按上面的方法创建一个窗体按钮，并输入“整理调节表”文