二维码 购物车
部落窝在线教育欢迎您!

别给姐说公式,用透视表一样做好账龄分析表!(上)

 

作者:老菜鸟来源:部落窝教育发布时间:2018-10-30 11:58:44点击:4262

分享到:
0
收藏    收藏人气:0人
版权说明: 原创作品,禁止转载。

编按:

前两天分享了一篇制作应收款账龄统计表的文章《一张应收款账龄统计表逼哭了多少会计人》,涉及到一个公式:

=LOOKUP(NOW()-B2,{0,31,61,91},{"欠款0-30","欠款31-60","欠款61-90","欠款90天以上"})

虽说方便有效,但对于晕函数、晕公式的朋友来说显然不是一个好的办法:

“不能总是炫公式呀——很难记的”

“不要歧视我们这些函数小白”

“怕公式,有么有更简单的?”

那该怎么办?

别忘了还有数据透视表这个神器,今天就为大家分享解决这类问题的另一个办法:一小米公式,加透视表神器。根据账期分类不同,我们将分成上下两篇介绍。上篇是各账龄区间固定为30天,下篇是各账龄区间不固定。

这正是青菜萝卜各有所爱!


 

问题回顾:如下图所示的一个数据源,要按照30天一个周期进行汇总统计。

 

 

1步:计算账期

1)首先我们还是需要先把账期计算出来,不过这次的公式非常简单:=TODAY()-B2

 

 

2)如果使用公式后显示的不是数字而是一个日期的话,选中单元格,右击选择“设置单元格格式”,把单元格格式改成常规就行。

 

 

2步:透视表操作

先看一下完整的操作步骤:

 

 

1)选择数据,插入数据透视表。

2)设置透视表字段:行字段“客户简称”,列字段“账龄”,值字段“应收账款余额”。

3)选中透视表中账期“29”的单元格,右击选择“组合”,然后设置账期区间。

上述操作中,前两小步大家都熟悉,这里只注重介绍第三小步,利用“组合”功能设置账期区间。

组合功能设置账期区间

在这个操作中,我们用到了组合功能,有些版本叫分组:

 

 

在要进行组合的字段单击右键,就会看到有“组合”这个功能,打开以后的界面也很简单:

 

 

透视表会根据该字段(也就是账期这一列)的最小值和最大值自动进行设置,步长默认10。我们需要结合自己的需要来修改这三个数字:

1)首先起始于不应该用最小值,而应该手动指定为1

2)步长也就是间隔天数,不能用10而应该是30

3)终止于里的值应该是步长的整数倍并且是最接近账期最大值的数字,比如步长为30,最大值是157,终止于就是180

 

 

设置后点击确定就完成了统计表的制作。

 

 

如果觉得分组后的标签不够直观,可以单独修改标签:

 

 

使用透视表进行分组的操作不是很难,新手练习一两次也能掌握,不过也有一点不足之处,就是分组的区间都是固定的,本例中是30天,如果要按需要指定区间的话,例如这样的效果:

 

 

每个区间都是不一样的,自动分组显示做不到,那该如何实现呢?咱们下次再说……

 

小编提议:请提供账龄自动分析素材

有么有想做一套系统的账龄分析的伙伴?账龄分析包括账龄分析表、坏账损失估计表。有的话,请提供素材,部落窝教育老菜鸟将免费为大家设计做一套自动账龄分析表格。素材请发blwjiaoyu4@itblw.com邮箱。

 

本文配套的练习课件请加入QQ群:264539405下载。

Excel高手,快速提升工作效率,部落窝教育《一周Excel直通车》视频和《Excel极速贯通班》直播课全心为你!

扫下方二维码关注公众号,可随时随地学习Excel

http://www.itblw.com/ewebeditor/uploadfile/20180914164059384001.png

相关推荐:

账龄分析表公式做法一张应收款账龄统计表逼哭了多少会计人?

LOOKUP函数常用套路LOOKUP函数用法全解(上)——LOOKUP函数的5种用法

LOOKUP函数的原理《LOOKUP函数用法全解(下)——LOOKUP函数的二分法原理

LOOKUP函数特殊应用实例《一个四舍五入的问题竟然连LOOKUP都用上了,你们也太会玩了

图片说明: 

         文中青菜萝卜各有所爱图的原图来自李家乐发表在站酷上《萝卜青菜各有所爱》。