跟着勇哥学做财务报表第二期

本篇文章您将看到以下内容

目录

⊙银行存款余额调节表

⊙会计科目表

⊙凭证录入

1

银银行存款余额调节表

银行存款余额调节表可做为银行存款科目的附列资料保存。该表主要目的是在于核对企业账目与银行账目的差异，也用于检查企业与银行账目的差错。调节后的余额是该企业对账日银行实际可用的存款数额。

银行存款余额调节表，是在银行对账单余额与企业账面余额的基础上，各自加上对方已收、本单位未收账项数额，减去对方已付、本单位未付账项数额，以调整双方余额使其一致的一种调节方法。

银行存款余额调节表是一种对账记录的工具，并不是凭证;如果余额相等，则一般没错;否则可能存在未达款项，或者记录错误。

设计注意点

需要区分清楚银行->企业 & 企业->银行

区分清楚哪些是增项 哪些是减项 帮助新人了解业务

金额需要加上千分符，更符合财务的阅读习惯

千分符设置

2

会计科目表

这张表体现了字段设计原子化，尽量采用纵表的设计理念.这部分我们将学习到如下内容

数据有效性

条件格式

单元格格式

科目级别自动判断

账户科目拼装技巧

动态数据区域求和

数据有效性

细心的朋友可以看到当我们点击余额方向的时候，自动会有提示信息出来。这个主要是使用了数据有效性，下面将演示该技巧的使用方法。

条件格式

大家可以发现，余额方向为借的数据 其中的A:G列的字体都是显示为红色，这主要是使用条件格式设置的。

注意：因为是A：G列都要设置为红色，所以在设置公式的时候D列一定要做绝对引用，这样保证A到G列都是和相应行D列的内容相比。

单元格格式

仔细观察这张财务报表，你会发现。数值为0的单元格竟然被显示为了-。这样的好处是让财务人员不至于看到满眼的0.如何去实现这个呢？一种简单的方法就是用if公式去判断，第二种方式是借助单元格设置。

借助下面这段代码就可以将0值设置为-

_ * #,##0.00_ ;_ * -#,##0.00_ ;_ * "-"??_ ;_ @_

科目级别自动判断

因为财务的科目是分等级的，从这张表我们可以看出根据科目代码的长度对应相应的科目等级 4位->1 6位->2 8位->3

有了上述规则。我们可以快速判断出科目级别，而不需要自己去手动填写。手动填写不但会出错，而且效率也低下。

方法1：if(len(A5) = 4,1,(if(len(A5)=6,2,8))

方法2：int((len(A5) - 4) /2 ) + 1

方法一 使用if嵌套 这种方法比较传统

方法二 采用数学方法，这种方法比较讨巧

账户科目拼装技巧

从报表中我们可以看出，H列是根据B，C列的数据拼装起来。但是对于1级科目C列的内容为空。这种情况就得特殊处理

=IF(C5="",B5,CONCATENATE(B5,"_",C5))

CONCATENATE 函数是将相应的内容串起来

动态数据区域求和

我们都知道，模块的好处是 数据发生变化后。原先写的公式不需要更新，可以自动算出结果。要想达到这个目的求和的数据区域就得做成动态的。

以本报表为例，如何实现动态数据求和呢？

实现思路如下

1.使用offset函数找到对应的条件区域、求和区域

2.在此基础上使用sum函数 =SUMIF(OFFSET($D5,,,COUNTA($D:$D)-1),"=1",OFFSET(F$5,,,COUNTA($D:$D)-1))

COUNTA($D:$D) 表示从D5开始有多少个非空行，也就是我们的条件区域的高度。

这里为了公式简洁，可以将 条件区域和求和区域定义为名称

offset函数

OFFSET($D5,,,COUNTA($D:$D)-1)

以D5单元格为原点，行列不偏移。目标区域是1列N行的区域

N行是根据COUNTA($D:$D)-1 计算而来

offset函数逻辑

注意

上图行列偏移都是正数，表示向下 向右 偏移。当然偏移也支持负数，则表示向上、向左偏移

3

凭证录入

下表是凭证录入表

在本部分，你将学习到如下内容

1.利用数据有效性，实现下拉列表

2.反向数据查询

3.对筛选后的数据进行统计

利用数据有效性，实现下拉列表

在本例中，凭证种类和账户名称都是采用下拉列表来实现。这种实现方法首先可以简化用户的操作。其次可以提升数据的准确性，下面将介绍下拉列表的实现方式。

方法1序列指定数据区域 实现下拉列表

注意数据与数据之间一定要用英文, 隔开

方法2 序列+名称实现下拉列表

分为两步，第一步根据科目表的数据自动生成对应的账户信息。这样就起到了模板的作用。第二步 在序列部分像引用公式一样 引用名称。

=OFFSET(会计科目表!$H$4,1,,COUNTA(会计科目表!$A:$A)-1) 这个公式就是根据数据的范围 自动生成一个科目区域，实现了动态。

逆向数据查询

大家可以看到，我们需要根据用户选择的账户名称从科目表中找出对应的编号，逆向数据查询的技巧很多，本例将给大家介绍如何使用index实现反向查询。

=INDEX(科目代码,MATCH($J5,账户查询,))

先使用MATCH($J5,账户查询,)找到对应的账户名称出现在哪一行这里match的第三个参数使用了默认参数表示是精确匹配

在使用index函数在科目代码中找对应行的数据，这里第三个参数为空。因为科目代码是一列N行，自动会找对应行的数据。如果科目代码为1行N列 则会找对应列的数据。

对筛选后的数据进行统计

在数据核对的时候，我们经常需要根据不同的筛选条件进行数据比较。有种方法是使用多条件统计，比如比较平均值就得使用Averageifs 比较合计值就得使用sumifs，比较出现数量就得使用countifs.其实在excel中，有个万能函数subtotal

根据参数的不同，可以计算相应的统计结果

参数说明如下

因为我们按照不同的筛选条件对账，因此可以使用subtotal，这样可以不用记住那么多函数，只需要查询下每个参数对应的含义即可。方便又快捷