一种基于Excel的非手工模式会计核算系统.doc

一种基于Excel的非手工模式会计核算系统重庆邮电学院 刘曜提要：本文主要介绍了在Excel上设计一种非手工模式会计核算系统的方法，其目的在于：一是为小企业提供一个简单易用的会计核算系统，二是给出一个只需要企业提供“底层”数据即可方便地进行计算机审计的模式，三是希望有助于直观地认识计算机处理数据的程序。关键词：Excel会计核算系统 手工账簿一 问题的提出至今，在我国会计核算软件的设计中仍存在手工账簿的“阴影”。在计算机应用的初期，会计软件模拟手工设计，人们容易接纳，这有一定的道理。但是，模拟手工账簿设计的软件存在以下问题：1为了模拟手工账簿，设计显得复杂；2在会计软件中，人们仍习惯于按手工操作的凭证、账簿和报表进行审计，忽视了利用计算机提高审计效率；3受手工操作的影响，不能正确认识计算机处理数据的程序。一般来讲，会计核算需要提供以下数据：1所有会计业务发生的日期、摘要，涉及的会计科目，以及借（贷）方发生额；2任一会计科目的余额和发生额合计；3任一会计科目连续的数据变化情况（借、贷方发生额变化情况）；4会计报表数据。在手工操作下，第1项是通过填制凭证，而第2、3项是记账，第4项是编制报表完成的。但是，既然目的是提供会计核算数据，在方式上则允许不同。下面会看到在Excel上可以容易地设计一个会计核算系统，提供这些数据。这一系统完全从计算机处理数据的特点出发，抛开了“手工账簿”。二Excel非手工模式会计核算系统设计1“会计科目表”工作表设计将Excel的一张工作表命名为“会计科目表”，设计格式如图1。AB1科目编号科目名称23图1 “会计科目表”工作表格式在“会计科目表”工作表输入会计科目的编号及名称，如图2。AB1科目编号科目名称2A1001现金3A1002银行存款4A1501固定资产5A1502累计折旧6B2101短期借款7C3101实收资本8C310101实收资本A9C310102实收资本B10C3131本年利润11E5502管理费用图2 会计科目编号及名称录入示例注意图2，在每一个会计科目编号前加有一个字母，其作用是便于分级汇总。“会计科目表”工作表的作用是提供系统使用的会计科目编号及名称。2“凭证库”工作表设计将Excel的一张工作表命名为“凭证库”，设计格式如图3。ABCDEFG1借方合计贷方合计2=SUM(F5:F1000)=SUM(G5:G1000)34日期凭证号摘要科目编号会计科目借方金额贷方金额56图3 “凭证库”工作表格式在该工作表F2、G2单元格设计有计算借（贷）方金额合计数的公式SUM，以检验借、贷方的平衡。公式中的参数1000是随意确定的，可根据会计业务的多少更改，如设成50000。发生会计业务记录如图4。ABCDEFG1借方合计贷方合计219100019100034日期凭证号摘要科目编号会计科目借方金额贷方金额52004-1-11借入三个月期贷款A1002银行存款10000062004-1-11借入三个月期贷款B2101短期借款10000072004-1-52提现A1001现金500082004-1-52提现A1002银行存款500092004-1-103追加投资A1002银行存款80000102004-1-103追加投资C310101实收资本A35000112004-1-103追加投资C310102实收资本B45000122004-1-314管理部门固定资产折旧E5502管理费用3000132004-1-314管理部门固定资产折旧A1502累计折旧3000142004-1-315结转管理费用C3131本年利润3000152004-1-315结转管理费用E5502管理费用300016图4 会计业务记录示例“凭证库”工作表的作用是提供所有会计业务发生的日期，摘要，会计科目，借（贷）方金额等。3“检索标准”工作表设计将Excel的一张工作表命名为“检索标准”，设计格式如图5。A1科目编号23科目编号45科目编号6图5 “检索标准”工作表格式按“会计科目表”工作表的科目编号顺序输入科目编号，如图6。A1科目编号2A10013科目编号4A10025科目编号6A15017科目编号8A15029科目编号10B210111科目编号12C310113科目编号14C31010115科目编号16C31010217科目编号18C313119科目编号20E5502图6 建立检索标准示例“检索标准”工作表的作用是为下面介绍的“发生额合计及余额表”工作表中的数据库求和公式DSUM建立检索标准。4“发生额合计及余额表”工作表设计将Excel的一张工作表命名为“发生额合计及余额表”，表头设计如图7。ABCDEFGH1期初余额本期发生额期末余额2科目编号科目名称借或贷金额借方发生额贷方发生额借或贷金额34图7 “发生额合计及余额表”工作表表头格式该工作表中的公式设计方法如下：首先，将“会计科目表”工作表各会计科目的“科目编号”和“科目名称”复制到本工作表的“科目编号”和“科目名称”栏下，并录入（或从上期复制得到）期初余额。第二步，在“本期发生额”的“借方发生额”和“贷方发生额”栏下输入数据库求和公式DSUM，以求得各会计科目本期“借方发生额”和“贷方发生额”合计数。第三步，在“期末余额”的“金额”栏下输入余额计算公式，以确定各会计科目的期末余额。第四步，在“期末余额”的“借或贷”栏下输入条件公式IF，以确定各会计科目期末余额的方向。示例如图810。ABCD1期初余额2科目编号科目名称借或贷金额3A1001现金借4A1002银行存款借5A1501固定资产借6A1502累计折旧贷7B2101短期借款平8C3101实收资本贷9C310101实收资本A贷10C310102实收资本B贷11C3131本年利润平12E5502管理费用平13图8 “发生额合计及余额表”工作表公式设计示例EF1本期发生额2借方发生额贷方发生额3=DSUM(凭证库!A4:G1000,借方金额,检索标准!A1:A2)=DSUM(凭证库!A4:G1000,贷方金额,检索标准!A1:A2)4=DSUM(凭证库!A4:G1000,借方金额,检索标准!A3:A4)=DSUM(凭证库!A4:G1000,贷方金额,检索标准!A3:A4)5=DSUM(凭证库!A4:G1000,借方金额,检索标准!A5:A6)=DSUM(凭证库!A4:G1000,贷方金额,检索标准!A5:A6)6=DSUM(凭证库!A4:G1000,借方金额,检索标准!A7:A8)=DSUM(凭证库!A4:G1000,贷方金额,检索标准!A7:A8)7=DSUM(凭证庫!A4:G1000,借方金额,检索标准!A9:A10)=DSUM(凭证库!A4:G1000,贷方金额,检索标准!A9:A10)8=DSUM(凭证库!A4:G1000,借方金额,检索标准!A11:A12)=DSUM(凭证库!A4:G1000,贷方金额,检索标准!A11:A12)9=DSUM(凭证库!A4:G1000,借方金额,检索标准!A13:A14)=DSUM(凭证库!A4:G1000,贷方金额,检索标准!A13:A14)10=DSUM(凭证库!A4:G1000,借方金额,检索标准!A15:A16)=DSUM(凭证库!A4:G1000,贷方金额,检索标准!A15:A16)11=DSUM(凭证库!A4:G1000,借方金额,检索标准!A17:A18)=DSUM(凭证库!A4:G1000,贷方金额,检索标准!A17:A18)12=DSUM(凭证库!A4:G1000,借方金额,检索标准!A19:A20)=DSUM(凭证库!A4:G1000,贷方金额,检索标准!A19:A20)13图9 续图8GH1期末余额2借或贷金额3=IF(H3=0,平,借)=D3+E3-F34=IF(H4=0,平,借)=D4+E4-F45=IF(H5=0,平,借)=D5+E5-F56=IF(H6=0,平,贷)=D6+F6-E67=IF(H7=0,平,贷)=D7+F7-E78=IF(H8=0,平,贷)=D8+F8-E89=IF(H9=0,平,贷)=D9+F9-E910=IF(H10=0,平,贷)=D10+F10-E1011=IF(H11=0,平,贷)=D11+F11-E1112=IF(H12=0,平,借)=D12+E12-F1213图10 续图9现在以科目编号A1001对应的DSUM、IF和余额计算公式为例作进一步说明。DSUM有三个参数。第一个参数：凭证库!A4:G1000，明确求和区域为“凭证库”工作表的A4:G1000（其中1000可据需要修改）；第二个参数：借方金额 （或贷方金额），明确是对“借方金额” （或“贷方金额”）求和；第三个参数：检索标准!A1:A2，明确是对科目编号为A1001的科目求和。公式IF(H3=0,平,借)的意义为，如果H3的值（A1001期末余额的金额）为0，则为平，否则为借。该公式中借或贷参数的确定原则为，余额一般在借方的科目设为“借”，如资产类、费用类；余额一般在贷方的科目设为“贷”，如权益类、收入类。公式“=D3+E3-F3”的意义为，A1001的期初余额加上借方发生额减去贷方发生额。该公式设置的原则为，余额一般在借方的科目设为“期初余额加上借方发生额减去贷方发生额”，余额一般在贷方的科目设为“期初余额加上贷方发生额减去借方发生额”。以图4所示的会计业务，本期发生额合计和期末余额计算结果如图11（期初余额需录入或从上期的期末余额复制得到）。ABCDEFGH1期初余额本期发生额期末余额2科目编号科目名称借或贷金额借方金额贷方金额借或贷金额3A1001现金借500050000借100004A1002银行存款借470001800005000借2220005A1501固定资产借10500000借1050006A1502累计折旧贷700003000贷100007B2101短期借款平00100000贷1000008C3101实收资本贷150000080000贷2300009C310101实收资本A贷50000035000贷8500010C310102实收资本B贷100000045000贷14500011C3131本年利润平030000贷-300012E5502管理费用平030003000平013图11 本期发生额合计和期末余额计算示例图11中“本年利润”贷方金额为“3000”表示借方金额为3000。图11中“实收资本”本期发生额采用的是分级求和（凡有子细目的科目均可类似分级求和，以提供各级明细指标）。“实收资本”（C3101）有两个子细目“实收资本A”（C310101）和“实收资本B”（C310102）。从图810中可以看出，“实收资本”一级指标按C3101求和（DSUM参数“检索标准!A11:A12”为C3101所在区域），二级指标分别按C310101和C310102求和（DSUM参数“检索标准!A13:A14”、“检索标准!A15:A16”分别为C310101和C310102所在区域），其二级指标的合计数正好是一级指标数值。应当指出的是，该工作表中的公式可采用自动填充或复制方式方便地得到（参数需稍加修改）。“发生额合计及余额表”工作表的作用是提供所有会计科目的本期发生额合计，以及期初、期末余额。5会计报表工作表设计在“发生额合计及余额表”工作表提供了编制报表的发生额和余额，所以会计报表工作表只需按会计报表的式样设计，数据通过公式从“发生额合计及余额表”工作表获取即可。现以“资产负债表”工作表的设计为例。将Excel的一张工作表命名为“资产负债表”，按资产负债表的式样设计格式如图12（为简略计，图中只给出了货币资金的公式设置，其它项目公式设置可类似进行）。ABCDEF1资产负债表23资产期初数期末数负债及所有者权益期初数期末数4货币资金=VLOOKUP(A1001,发生额合计及余额表!A3:H500,4)+VLOOKUP(A1002,发生额合计及余额表!A3:H500,4)=VLOOKUP(A1001,发生额合计及余额表!A3:H500,8)+VLOOKUP(A1002,发生额合计及余额表!A3:H500,8)5合计合计图12 “资产负债表”工作表格式该工作表中的B4、C4单元格设计有查找公式VLOOKUP，以在“发生额合计及余额表”工作表获取现金和银行存款的期初（末）余额。VLOOKUP(A1001,发生额合计及余额表!A3:H500,4)有三个参数。第一个参数：A1001，明确查找科目的编号（该参数是现金科目的编号，若设为A1002则为银行存款）；第二个参数：发生额合计及余额表!A3:H500，明确查找区域为“发生额合计及余额表”工作表的A3:H500（其中500可据需要修改）；第三个参数：4，明确是查找科目编号为A1001的期初余额（若设为8，则为期末余额）。“资产负债表”等会计报表工作表的作用是提供会计报表数据。三系统使用与维护1系统使用（1）系统设计完成后，每月复制一个文件供该月会计核算使用。（2）当月的期初余额从上期的期末余额复制得到（注意粘贴时，选菜单“编辑/选择性粘贴”功能，仅粘贴数值）。（3）发生会计业务，在“凭证库”工作表录入有关数据，系统将自动产生“发生额合计及余额表”工作表和“资产负债表”等工作表数据。（4）若要查询会计业务，选择“凭证库”工作表。若要查询任一会计科目的余额和发生额，选择“发生额合计及余额表”工作表。若要查询任一会计科目连续的数据变化情况（借、贷方发生额变化情况），在“凭证库”工作表使用菜单“数据/筛选/自动筛选”功能即可实现（图13为图4所示会计业务按科目编号A1002筛选的结果）。若要查询资产负债表等会计报表，选择“资产负债表”等工作表。ABCDEFG1234日期凭证号摘要科目编号会计科目借方金额贷方金额52004-1-11借入三个月期贷款A1002银行存款10000082004-1-52提现A1002银行存款500092004-1-103追加投资A1002银行存款80000图13 查询会计科目数据连续变化情况示例2系统维护（1）系统文件可加密；（2）为防止无意中破坏系统格式设计，对表头、公式等不输入数据的单元格可进行保护；（3）系统可据需要在“会计科目表”工作表灵活增减会计科目，但应注意相应调整各工作表公式中的参数。四系统特点及适用范围1系统特点该系统从会计核算最终信息的需要出发来提供会计数据，不受手工会计模式的影响。系统设计简单，只利用了Excel的一些常用功能，不使用任何代码（当然，如果读