在很多企业,应收账款要按指定的账期显示,类似“0-30天,31-60天,……”这种样式。在Excel应收账款分析表中,需要根据应收账款的账龄天数显示不同的账期区间 。 例如,公司的账期根据时间段划分为五大类别,如下表。 传统办法是用IF函数嵌套函数判断,如果账期类别多,会比较繁琐还容易出错。 这里介绍账期匹配的三个函数。至关重要!你会了吗? 01、VLOOKUP函数 平时用VLOOKUP函数做查询,更多的是用到它精确查找的功能,最后一个参数为0,如果找不到就会报错。由于账期匹配是区间段查询,所以用到VLOOKUP函数的模糊查找功能,最后一个参数为1。 首先需要在G2:H6区域建立一个规则表。 在E2单元格中输入公式: =VLOOKUP(D2,$G$2:$H$6,2,1) 通过VLOOKUP函数的模糊查找,来判断各账目的账期。 (本例中的账龄计算日期为2019年1月26日) 建立规则表的好处是查看清晰,也容易修改。有时候不方便在旁边建立规则表,就需要将规则表嵌入VLOOKUP函数当中。 在单元格E2中输入公式: =VLOOKUP(D2,{0,"0-30天";31,"31-60天";61,"61-90天";91,"91-120天";121,">120天"},2,1) 初看公式有点复杂,里面还有逗号(,)、分号(;)、引号(“ ”)和大括号({ })等多种符号。这里营长推荐一个快捷键【F9】,可以快速将函数中引用地址快速转换成引用内容。在函数中,选择“$G$2:$H$6”,按下【F9】即可。有的电脑键盘需要按【Fn+F9】。这样,匹配条件和对应的账期类别就在函数中体现,不会引用其他的区域。缺点是看起来复杂,容易出错。 02、LOOKUP函数 LOOKUP函数也可以做模糊查询,函数结构如下: · LOOKUP(查找值,查找区域,返回区域) 单元格E2使用规则表的公式: =LOOKUP(D2,$G$2:$G$6,$H$2:$H$6) 在单元格E2中不使用规则表,输入公式: =LOOKUP(D2,{0;31;61;91;121},{"0-30天";"31-60天";"61-90天";"91-120天";">120天"}) 03、IFS函数 其实在Office 2019或Office 365版本中,Excel软件新增了IFS函数,用于多条件判断。因为如果判断条件区间多一点,IF语句就需要不断地嵌套,变得越来越复杂,到最后面的括号数量多,很容易弄错。 先看下IFS函数的语法结构: · IFS(条件1,结果1,条件2,结果2……) 只要按照“条件,结果”的格式,不断地添加参数就可以了,最多支持127对条件和结果。 在单元格E2中输入公式: =IFS(D2>120,">120天",D2>90,"91-120天",D2>60,"61-90天",D2>30,"31-60天",D2>0,"0-30天") 最好所有的条件能够覆盖实际的数据范围,如果不确定,可以将最后一个条件用“TRUE”来代替,表示前面所有条件都不满足的情况,否则公式返回结果会出现错误。 |
|