跟着勇哥学做财务报表(3)

勇哥说Excel2021-01-08 13:11:54

跟勇哥学Excel,Smart Working,Life Happy

跟着勇哥学做财务报表已经发布了两期,粉丝反馈这种形式比较好。结合实例讲技巧这样便于大家理解技巧,有粉丝反馈财务报表很实用。近期计划开展一期财务报表学习训练营,有兴趣的可以添加勇哥微信603076787了解详细。带你进入Excel的海洋,通过训练提升您的Excel使用技巧。从此不再惧怕Excel数据处理,从此不再受Excel的折磨。


言归正传,开始我们今天的财务报表学习。


  今天我们一起结合根据凭证录入数据 -->指定月份的通用记账凭证,学习Excel的一些实用技巧

凭证录入

通用凭证


上图GIF是最终的结果呈现,通过这部分内容您将学习到如下的技巧


目录

⊙  数据调节框制作

⊙  数据辅助技巧

⊙  动态记账凭证制作

Text函数

offset函数

多条件查询

⊙  index函数

⊙  iferror

⊙  vlookup函数

⊙  index函数


01

数据调节框制作

从上面的GIF演示图,我们发现点击向上向下的按钮,竟然可以调节数据范围,这样不但可以方便用户操作,而且这种操作给人很炫的感觉。这样的功能,不需要你会VBA。只要动动手,也可以非常方便地做出来。下面就跟着GIF一起来学些,怎么快速做数据调节框吧。

处理思路

1.在开发工具->添加微调框. 

2.设置相应的控制属性,步长是指每次点击变化的数据,最大、最小值用来设置数据范围,当前值是指初始值。单元格链接是指数据显示在哪个单元格


当然Excel中除了微调框之外,下拉选择框使用的也特别多 下拉框的实现方法

02

数据辅助技巧

凭证录入数据生成通用凭证,整个过程主要是根据用户的输入。依次查询出对应月份,对应凭证序号的数据。整个操作过程主要是查询功能,但是要想通过一个公式搞定是非常麻烦的,不但公式复杂问题难以定位。而且也容易出错,所以平时在设计表的时候。尽可能地将问题分步化,简单化。在使用Excel的时候,我们要学会分步,迂回一个复杂的问题分解为数个简单的问题。这样问题就可以迎刃而解。希望这个理念可以帮助到大家。Excel是用来解决问题的,不是用来烧脑的。一定要记住这一点,好啦闲话少叙。我们一起来看看,记账凭证制作过程中我们只要使用到了哪些技巧。



03

动态记账凭证制作

通用凭证的设计思路

1.通用记账凭证 sheet页中设计多个凭证单

       2.每个凭证单序号对应一个凭证表,每个凭证表定义6条记录

 3.根据用户选择的年度,月份以及当前的凭证序号。找到对应的数据行

 4.根据对应的数据行从凭证录入表中获取摘要,总账科目,明细科目,借方金额,贷方进入,日期等信息填写到记账凭证表中。

5.格式化金额数据(借助text函数将金额数据转化为财务上通用的格式)


下面一步步来解析,整个过程中用到的函数。


根据用户选择的份以及对应的凭证序号找到对应的行号

因为需要和月份、凭证序号对比。为了公式简洁,需要创建对应的名称


月份 =OFFSET(凭证录入!$A$4,1,,COUNTA(凭证录入!$A:$A)-2)

这里借助offset函数以及counta函数找到对应的月份区域,offset的技巧参考跟着勇哥做财务报表(2) 


counta是为了计算出记账凭证表一共有多少条记录。counta是为了计算出对应区域有多少个非空单元格。这里之所以减2是因为凭证录入表中A1 以及A3是空的。大家可以考虑下为什么说A3是空的呢?可以在留言部分回复


凭证序号 =OFFSET(记账月份,,MATCH("凭证序号",凭证录入!$3:$3,)-1)

凭证需要依旧是使用offset函数实现,这里借助之前建立的记账月份。在使用match函数找到对应的列进行偏移。偏移到凭证序号对应的数据区域。

注意:这里为什么不直接使用C列呢?主要是担心用户把凭证序号放到别的列。所以借助match函数找到对应的列。这里一定要注意,列名中一定要包含凭证序号哦。


记账行号

=SMALL(IF((记账月份=通用记账凭证!$AF$1)*(凭证序号=凭证编号)*((借方金额+贷方金额)<>""),ROW(记账月份)),ROW(通用记账凭证!$1:$6))

这里借助数组函数,* 表示 记账月份,凭证序号 借方金额 贷方金额这三种是需要同时满足。外面嵌套small函数是为了找到第一次出现的位置. 形成满足条件的数据行号数组。


更多的多条件查询参见 多条件查询的那些套路


根据当前凭证的需要 找到对应对应记录在 凭证录入表中对应的行号

=IFERROR(INDEX(记账行号,MOD(ROW()-4,12)),"")

注意

这里-4 以及mod 12的作用

因为在凭证记账表中前四行没有实质数据因此-4 。mod 12 是因为每笔记录会有对应的入和出,通用凭证表支持6对记录。所以要mod12

iferror是为了将错误的值显示为空。这样结果显示更符合人性


对应的行号找到了,接下来问题就简单了。根据行号找对应的内容。


日期

=DATE(AD$1,AF$1,INDEX(凭证录入!B:B,AD5))

传入年月日拼装出对应的日期,其中日是根据在凭证录入表中的行号查询得到的


摘要 

=IFERROR(INDEX(凭证录入!H:H,$AD5),"") 

因为摘要放在凭证录入表的H列,因此使用Index就可以找到对应的信息


科目代码

=IFERROR(INDEX(凭证录入!I:I,$AD5),"")  

一定要隐藏,财务可不想看到这个编号。这是系统定义的,对于财务而言这个编号只会让她的脑袋瓜变大,其他作用不大。


总账科目

借助vlookup 根据科目代码 从会计科目表 中查找到对应的内容

=IFERROR(VLOOKUP($B5,会计科目表!$A:$C,2,),"")  漫谈逻辑判断函数

注意

我们在通用记账凭证表中并没有看到B列是因为被隐藏起来了。

隐藏小技巧:选中B列的任一单元格 按ctrl + 0 就可以快速将本列隐藏。


明细科目

=IFERROR(VLOOKUP($B5,会计科目表!$A:$C,3,),"")

实现机制与总账科目的方法一致。


财务与钱打交道最多。最关键就是找出对应的金额了

借方金额

=IFERROR(INDEX(凭证录入!K:K,$AD5),0)  这里介绍了index一对一查询 您还可以看看 一对多查询


金额

=IFERROR(INDEX(凭证录入!K:K,$AD5),0)


附单据数的计算方法

="附单据数︵"&INDEX(凭证录入!$D:$D,$AD5)&"︶张"

依旧是基于行号计算出对应单据数量, & 符号要来实现文本的拼装 

参考 文本处理,其实很简单 学习更多的函数


凭证编号生成

="第 "&TEXT($AF$1,"00")&TEXT($AC2,"0000 号")

TEXT($AF$1,"00") 用来及算出对应的月份,1月->01 二月->02 依次类推。这种借助Text函数进行格式化保证了数据为数的一致性


TEXT($AC2,"0000 号") 表示序列号 同理 借助的也是Text格式化函数,有了Text函数我们的数据格式会更加相同,处理文本的时候经常要用到这个函数。Text函数的原理就是占位符,定义统一的位数。不满则用0替换

大家有没有发现:这里为了得到摘要,科目代码,借方金额,贷方金额都是使用的index函数。并且参数都不一样。有没有好的方法将公式改为同一个?感兴趣的亲,可以考虑下,在文章底部留言。改变之后 这几个数据都可以用同一个公式去处理,是不是很赞。


借方金额,贷方金额得到之后接下里就是要将其转化为财务要的格式。勇哥给他取了个好听的名字-美颜,超级变变变

这里主要是借助text ,right ,left column 等相关函数


左侧是千分符表示法,右侧是财务的表示方法

=LEFT(RIGHT(TEXT($E5/1%," ¥0; ¥-0;"),18-COLUMN()))

$E5/1% 把金额中的小数点去除


TEXT($E5/1%," ¥0; ¥-0;")借助Text函数确定是显示¥还是数值


RIGHT(TEXT($E5/1%," ¥0; ¥-0;"),18-COLUMN()) 去取出右边N位


感兴趣的亲可以考虑下 为什么这里是18-Column()


外部再嵌套left用来取出最左边的一位。这样千分符数据就可以转化为财务的格式了。是不是特别方便呢?


通过这篇文章的学习,你了解到了如何从凭证录入表快速生成通用凭证。不但速度快,而且还是动态的,工作效率杠杠的。并且你也学习到名称,函数(offset ,small ,vlookup ,iferror ,index, text ,left, right, mod ,column)的组合使用,辅助列,微调节框的使用方法。



推荐阅读

(点击蓝字可直接跳转)

跟着勇哥学做财务报表(1)

跟着勇哥做财务报表(2)