用Excel轻松搞定应收账款账龄分段分析 / 开普饭

近推送的五篇文章：

《偷懒的技术》读者粉丝福利

6月3日之前

在当当网满100减50的活动基础上，

《偷懒的技术》联合【华章管理】

为大家申请到当当图书200减30优惠码

只要消费当当网自营图书超过200立减30

结算时输入优惠码：QG6ZUH

立享折上折！

数量有限，先到先得哦~

· 正 · 文 · 来 · 啦 ·

需求

财务工作中需要对应收账款进行账龄分析，比如下图中逾期应收账款管理表，是从应收账款或合同管理软件中导出，已知其应该收款的日期，现需计算其逾期天数、并将逾期天数进行分组，划分为：

3个月以内（0-92天）
3-6个月（92-183天）
6-12个月（184-365天）
1-2年（366-730天）
2年以上（大于730天）

本文将介绍如何使用透视表和公式对逾期天数进行分组。

准备工作：计算逾期天数

步骤一：设置自定义格式

为了方便计算，在A3单元格输入基准日期”2019-3-31“，并设置自定义格式为：

"基准日："yyyy-m-d

也就是说，A3单元格实际内容为日期”2019-3-31“，通过自定义格式将期显示为：

”基准日：2019-3-31“。

这样做的好处：

A3单元格是标准日期，可以直接加减计算来计算逾期天数。
不必为了日期能加减，将A3单元格分拆为二个单元格（A列分拆为二列）

步骤二：计算逾期天数

D5单元格输入公式：

=$A$3-C5

知识点：
日期本质上就是数字，可直接加减（前提：一定要使用标准的日期才能加减，而不是使用2019.1.28这种错误的日期格式）
关于日期本质上是数字的知识介绍及应用，可参见Excel偷懒的技术公众号之前的文章
日期①：吾本佳人，奈何以他人面目示人多年

一、使用透视表来分组

我们使用透视表的组合，手动将天数组合成需要的分段区间。

详见操作Gif动图

分组后如下图：

然后再添加客户字段，调整一下布局：

二、使用函数公式对账龄进行分段

1、使用IF函数

对新手来讲，用IF函数来判断逾期天数是处于哪个区间，是最容易理解的了。只是在判断的时候要注意判断的逻辑顺序，不能乱。

从小到大进行判断的公式：

=IF(D5<=92,"3个月内",IF(D5<=183,"3-6个月",IF(D5<=365,"6-12个月",IF(D5<=730,"1-2年","2年以上"))))

从大到小进行判断的公式：

=IF(D5>730,"2年以上",IF(D5>365,"1-2年",IF(D5>183,"6-12个月",IF(D5>92,"3-6个月","3个月内"))))

大家在编写多层嵌套公式时，可能不知道如何编写，我们可以一层层编写，然后再组合在一起，比如从小到大的判断公式：

先判断3个月以内：

=IF(D5<=92,"3个月内","继续判断A")

然后判断是否在3-6个月

继续判断A=IF(D5<=183,"3-6个月","继续判断B")

同理，继续判断是否在6-12个月、"1-2年"、"2年以上"

继续判断B=IF(D5<=365,"6-12个月",”继续判断C")

继续判断C=IF(D5<=730,"1-2年","2年以上")

然后将上述公式组合在一起即可。

2、使用VLOOKUP函数或LOOKUP函数

VLOOKUP有二种查找模式：精确匹配、近似匹配。我们平时使用是精确匹配模式。但近似匹配模式也是很有用处的，它的特点是先查找指定的值，如果找不到，就返回比它小的最接近的值所对应的结果。这种模式一般用于计算个税、计算提成。本安全划分应收账款的区间也可用这种模式。

近似匹配模式下，在找不到要查找的值时，就返回比它小的最接近的值。那么，我们只要正确的设置区间就可将逾期天数按我们的要求分段。

比如我们将区间值设为0、93、184、366、731，它们分别对应3个月.......2年以上，如下图中的L4:M9单元格所示。

然后在E5编制公式

=VLOOKUP(D5,$L$5:$M$9,2,1)

注：VLOOKUP的第四参数为1或true，表示近似匹配。

然后下拉填充。

公式解释：

E5单元格用VLOOKUP的近似匹配模式在L5:M9的首列查找62，L列没有62，那么，公式就会返回比62小的最接近它的值0，所对应的值”3个月“，

同理，E6单元格用VLOOKUP的近似匹配模式在L5:M9的首列查找102，L列没有102，那么，公式就会返回比102小的最接近它的值93，所对应的值”3-6个月“。

注意：

使用VLOOKUP时，查找区域必须按首列升序排列（L5:L9就是升序排列的），否则，查找结果可能会出错。

用Excel轻松搞定应收账款账龄分段分析

相关推荐