从事财务工作的伙伴对于账龄统计表一定不陌生,就是根据时间段汇总每个客户的应收账款金额,然后交给相关业务部门去催款,说起来很容易,做起来就未必容易了。通常财务系统导出的数据并不能直接满足需要,还需要使用Excel来二次加工,这个过程所耗费的时间就因人而异了。
好了,废话不多说,来看下一位学员提供的数据源(数据为模拟值,仅供参考):
数据源中并没有账龄这个列,所以我们首先需要自行添加账龄:
账龄的计算非常简单,使用公式=TODAY()-E2就可以计算出来,这个公式的意思就是开始日期距离今天的天数,使用TODAY()函数的作用就是每次查看时账龄会根据查看日期自动更新。
完成了账龄的添加,接下来就需要使用透视表来进行处理,大致分为以下几步:
1
、创建基础透视表
操作要点:
把开始日期放在行字段的时候,新版本的Excel会自动产生季度和年两个汇总方式,我们只保留年。
再把客户名称放在年的下方,将账龄放在列字段,未收回金额放在值区域即可。
具体摆放位置按照实际需要即可,没有强行规定。
2
、按账龄进行分组设置
基础透视表显然不能满足我们的需求,还需要在此基础上对账龄进行分组设置,关于分组的区间设置,同样是按需要设置的,正常情况下是以30天为一个区间进行设置,但是本例比较特殊,要求是360天为一个区间进行设置,具体操作看动画演示:
操作要点:
起始于手动输入1,终止于使用默认值,步长输入360。
设置完成后效果为:
注意到第一个区间和最后一个区间,明显是有问题的,我们可以在数据源中看到:
数据源包含了总计行,同时有两行没有填写开始日期,解决方法是修改数据源的范围,同时完善数据源的信息。
使用更改数据源功能:
将数据范围改到21行截止:
完善数据源后对透视表进行刷新,结果如图:
此时效果已经接近实际需求了,最后一步就是对透视表的美化。
3
、对透视表进行排版
至此,一个简单的账龄汇总表就完成了。
为了比较客观的体现出制作过程,对于数据源未做修改,在通过透视表发现问题时,才进行了修正。目的只有一个,让大家了解制作的思路和原理,这样才能将这个技能应用到具体工作中,学以致用才不枉费我们付出的每一分钟。
联系客服