Excel财务综合应用之一:小型账务系统(第二部分 基础设置表)

第二部分 基础设置

科目

在手工账的年代,会计科目是要自己用笔写出来的。如今,电算化的时代,预制好科目,在填制凭证的时候直接调用即可,非常方便。但是,我们是用EXCEL表格来做账,最原始、最简单的也是人工录入科目,只不过是把用笔写换成用电脑打字,这无疑是一大进步。即便是这样的原始状态下,也能通过EXCEL的计算汇总功能,方便地进行科目汇总,方便编制财务报表。当然,EXCEL的功能远超这种需求。

需求

一方面要提高输入效率,一方面要保证输入的准确性、统一性。

设计思路

利用EXCEL的数据验证功能,限定输入内容,也是就说,我们可以预先设定好科目表,在填制凭证的时候,只能从科目表中存在的科目中选择。
简单地,我们可以把整个科目表作为一个列表,在输入的时候选择。
设置“基础设置”表,把科目预先编辑好,格式如下:

科目更新在B列、C列进行插入、修改操作,A列是一个公式,把B列和C列用下划线”_"连接起来,作为录入、筛选的关键字。

基础设置表.png

定义名称:科目编码=OFFSET(科目!$A$1,1,0,COUNTA(科目!$A:$A)-1,1)
然后在明细账表中D列科目&编码字段设置数据验证

数据验证1.png

点击出错警告标签,去掉输入无效数据时显示出错警告前的勾选。
数据验证2.png

最后,我们就可以点击明细账表中D列的下拉箭头来输入科目了,又快又准又方便!
明细账中录入科目.png

当然,如果科目数量不多,这样设置是没有问题的,但如果科目有几百条甚至更多,这样筛选起来就不是很方便了。
有没有更好的办法呢?当然有啦,下面就听我详细道来。
功能描述
明细账表中D列,输入科目&编码字段的一部分内容,连续的关键字,注意一定是“连续”的字段。然后点击下拉箭头,即可找到包含该关键字的所有科目,点击输入。
具体设置
基础设置表D列,字段设置为科目筛选,并定义名称:科目筛选=OFFSET(科目!$D$2,,,COUNTIF(科目编码,""&CELL("contents")&"")),这里利用CELL函数、COUNTIF函数和通配符,计算出科目编码中包含当着单元格内容的单元格个数,作为OFFSET函数的一个参数,得到一个区域引用。
在D列从D2开始输入数组公式{=INDEX(A:A,SMALL(IF(ISNUMBER(FIND(CELL("contents"),科目编码)),ROW(科目编码),4^8),ROW(A1)))&""},填充到科目表最后一行。公式含义与功能:

  1. 利用CELL("contents")函数取得当前输入内容
  2. 利用FIND函数在基础设置表A列查找当前输入内容,返回数字或错误值
  3. 利用ISNUMBER函数判断查找结果,返回TRUE或FALSE
  4. 利用IF函数进行判断,TRUE则返回对应科目编码的行号,FALSE则返回一个极大值(大于科目表的行数即可,这里取4^8)
  5. 利用SMALL函数比较IF函数返回值,以公式所在行号为参数,取得第N小值(行号)
  6. 利用INDEX函数,取得第N行所在科目。
    这样,科目表设置完成。

科目方向

明细账表中的科目余额,没有定义借贷方向,统一以借方减贷方为余额,为了方便利用数据透视表汇总科目余额,编制会计报表,这里设置科目方向为1和-1,借方余额的科目方向为1、贷方余额的科目方向为-1.
这里科目名称使用一级编码和一级科目名称联合起来表示,其实也可以在每一个明细科目中设置科目方向,效果一样,这里简化处理。

凭证号

预先录入凭证号列表,在明细账中的凭证号列设置数据验证,输入凭证号的时候可以选择录入。
基础设置暂时就这么多。日后有需求可能会作一些调整。

最后编辑于
©著作权归作者所有,转载或内容合作请联系作者
平台声明:文章内容(如有图片或视频亦包括在内)由作者上传并发布,文章内容仅代表作者本人观点,简书系信息发布平台,仅提供信息存储服务。

推荐阅读更多精彩内容