假期之前没少听到大家给小助理抱怨,每个年底都是大型催收现场,摆在每个会计面前的第一个问题就是统计好谁欠了多少钱?欠了多久了?
但往来账款的明细这么复杂,总不能对着表格一个一个统计,经过小助理假期的总结,给大家找出了三种便捷方法,快来一起学习吧~
下图是小编公司的应收账款明细表,现在老板要求以50天为一个周期对应收账款进行分析。
第一步先求出截止今天款项的逾期天数,这里我们用比较简单的一种方法——TODAY函数,如图在D2格输入公式=TODAY()-B2
下拉填充公式,之后要记得将D列的格式调整为数值,才能正确显示天数。
做好准备工作就可以进入正题了,小助理按照公式的复杂程度,依次给大家介绍3种常见的账龄分析方法。
1、Lookup函数
利用lookup函数的查询引用功能实现多层级区间查询。在E2输入公式:
=LOOKUP(TODAY()-B2,{0,"0~50";50,"50~100";100,"100~150";150,"150~200"})
即可获得相应的账龄级别。
注:在进行区间查找的时候注意要从小到大依次排列,否则不能返回正确的结果。
下拉填充公式后,在数据表旁边插入透视表,选中数据表格,并将账龄级别、客户名称、金额拖入相应列标签、行标签、总额,就能快速得出相应账龄阶段的应收账款总额。
余下两种函数的透视表操作与上述相同。
2、Vlookup函数
利用vlookup函数模糊查找,分析各款项的账龄级别。在E2输入公式:
=VLOOKUP(TODAY()-B2,{0,"0~50";50,"50~100";100,"100~150";150,"150~200"},2,1)
即可获得相应的账龄级别。
注:vlookup函数模糊查找是指查找不到查找值,则对应到小于查找最大值,这样就可以为区间查找提供了功能。
值的
3、IF函数
通过IF函数的条件判断返回相应的账龄级别。在E2输入公式:
=IF(TODAY()-B2>=150,"150~200",IF(AND(TODAY()-B2>=100,TODAY()-B2<150),"100~150",IF(AND(TODAY()-B2>=50,TODAY()-B2<100),"50~100",IF(TODAY()-B2<50,"0~50"))))
即可获得相应的账龄级别。
有的人可能问现在的条件是从大到小排列,如果条件从小到大排列可以吗?
当然是可以的,IF函数并没有像前两种函数一样严格要求区间按照从小到大的排列,在E2输入公式:
=IF(TODAY()-B2<50,"0~50",IF(AND(TODAY()-B2>=50,TODAY()-B2<100),"50~100",IF(AND(TODAY()-B2>=100,TODAY()-B2<150),"100~150",IF(TODAY()-B2>=150,"150~200"))))
我们可以得到相同的结果。
总结:小助理最开始在整理三种方法时,整个逻辑链条来讲最好理解是IF函数,但是公式过分冗长,容易在输入时漏掉一些元素导致返回无效值。而lookup函数和vlookup函数公式则要简单很多,主要是函数本身具有了统计的功能,让公式大幅度简化。
最后再补充一个小技能,如果每次只想展示对应账龄级别的应收账款总额,该怎么实现呢?
可以通过插入切片器来实现这个功能。
来源:Excel实用技巧 ,作者Fiona
特别声明:以上内容(如有图片或视频亦包括在内)为自媒体平台“网易号”用户上传并发布,本平台仅提供信息存储服务。
Notice: The content above (including the pictures and videos if any) is uploaded and posted by a user of NetEase Hao, which is a social media platform and only provides information storage services.