一、将日期统一到各月末 B8:=EOMONTH(D5,-1) L8:=EOMONTH(I8,0) 函数说明 1、eomonth 函数表达式=eomonth(start_date,months) 用途:计算本日期指定间隔月份的月末日期,比如参数用0就是当月末,用-1是上月末。
二、找出结算方式对应账期天数 M8=VLOOKUP(K8,$B$36: $D$54,3,0) 1、vlookup 函数表达式:=vlookup(lookup_value,table_array,col_index_num,range_lookup) 用途:从指定范围里,如果该范围第一列存在要找的值,再从该范围指定列取值。 根据结算方式,找出账期
三、计算距离到期的天数 N8=$G$5-(L8+M8) 账龄计算日-(结算日+账期天数)=应收账款距到期天数
四、计算超期分段 O8=LOOKUP(N8,$B$58: $B$65,$C$58: $C$65) 1、lookup 函数表达式=lookup(lookup_value,lookup_vector,result_vector) 用途:在某列里找近似值,返回结果列对应的值,比如大于0且不到90天的,返回0对应的“超期3个月以内”
五、计算普通账龄 P8=IFERROR(VLOOKUP(L8,$B$8: $D$32,3,0),"24个月以上") 1、iferror 函数表达式=iferror(value,value_if_error) 用途:如果公式计算值没问题显示计算结果,否则显示指定结果。 2、vlookup 嵌套公式说明 按照月份找出当前记录所属的普通账龄账期,如果找不到则计入“24个月以上”。
六、生成普通账龄表 T8:=(SUMIF($F$8: $F$33,$R8,$J$8: $J$33))/10000 函数说明 1、sumif 函数表达式=sumif(range,criteria,sum_range) 用途:将同一个客户代码的应收账款进行汇总。 U8:=(SUMPRODUCT(($P$8: $P$33=U$7)*($F$8: $F$33=$R8)*$J$8: $J$33))/10000 函数说明 2、sumproduct 函数表达式=sumproduct(array1,array2,array3,...) 用途:将同时满足多个条件的数据进行求和:客户代码相符、普通账龄为“一个月内”的应收金额。 将U8的公式填充到V8:AC8区域
七、超期账龄表 T16:=(SUMIF($F$8: $F$33,$R16,$J$8: $J$33))/10000 函数说明 1、sumif 函数表达式=sumif(range,criteria,sum_range) 用途:将同一个客户代码的应收账款进行汇总。 U16:=(SUMPRODUCT(($O$8: $O$33=U$15)*($F$8: $F$33=$R16)*$J$8: $J$33))/10000 函数说明 2、sumproduct 函数表达式=sumproduct(array1,array2,array3,...) 用途:将同时满足多个条件的数据进行求和:客户代码相符、超期账龄为“账期内”的应收金额。
V16=T16-U16 用途:超期金额=全部应收账款-账期内应收款
W16=(SUMPRODUCT(($O$8: $O$33=W$15)*($F$8: $F$33=$R16)*$J$8: $J$33))/10000 用途:将同时满足多个条件的数据进行求和:客户代码相符、超期账龄为“超期3个月内”的应收金额。 |