请教：求应收账款账龄

Silencemans

求应收账款账龄
意思就是：类似应收账款的账龄，求F列的余额对应后面每月销售的总额，看是欠的哪几个月的欠款。（理想化的把他当成是按顺序还款的）
如：第一个余额16327，是最后两月的合计5524+10803，就显示“欠2024.1月-2024.2月”
谢谢

hxj119

=IF(F4>0,"欠"&TEXT(LOOKUP(1,0/(SUBTOTAL(9,OFFSET($J4,,ROW($1:$24),,12))>=F4),TRANSPOSE($K$3:$AH$3)),"yyyy.m月")&"-"&TEXT(LOOKUP(1,0/($K4:$AH4),$K$3:$AH$3),"yyyy.m月货款"),"")

我的城市你来过

=IF(F4>0,TEXT(OFFSET($K$3,,MATCH(1=0,$K4:$AJ4<>"",)-1-MAX(IF(FREQUENCY(F4,SUBTOTAL(9,OFFSET($K4,,MATCH(1=0,$K4:$AJ4<>"",)-2,,-ROW($A$1:$A$15))))=1,ROW($A$1:$A$15)))),"yyyy年mm月")&"起有应收货款","")

Silencemans

我的城市你来过 发表于 2024-3-8 10:25
=IF(F4>0,TEXT(OFFSET($K$3,,MATCH(1=0,$K4:$AJ4"",)-1-MAX(IF(FREQUENCY(F4,SUBTOTAL(9,OFFSET($K4,,MATCH ...

收到，我看下，谢谢

Silencemans

hxj119 发表于 2024-3-8 09:27
=IF(F4>0,"欠"&TEXT(LOOKUP(1,0/(SUBTOTAL(9,OFFSET($J4,,ROW($1:$24),,12))>=F4),TRANSPOSE($K$3:$AH$3)), ...

谢谢，能解释下吗