F3=LOOKUP(9E+307,D:D)
G5=IF(F5="","",$B$3-E5)公式向下复制
以下两个公式都是数组公式,公式输完后,光标放在公式编辑栏同时按下CTRL+SHIFT+回车键,使数组公式生效。
F5=IF(F5="","",IF(ISERROR(INDEX(A:A,LARGE(($B$5:$B$1003<>"")*ROW($B$5:$B$1003),ROW(F1)))),$A$5,INDEX(A:A,LARGE(($B$5:$B$1003<>"")*ROW($B$5:$B$1003),ROW(F1)))))
以下公式向下复制
E5=IF(F5="","",IF(ISERROR(INDEX(A:A,LARGE(($B$5:$B$1003<>"")*ROW($B$5:$B$1003),ROW(F1)))),$A$5,INDEX(A:A,LARGE(($B$5:$B$1003<>"")*ROW($B$5:$B$1003),ROW(F1)))))
F6=IF(AND(F5<>"",(ROW()-ROW($F$5))=COUNT($B$5:$B$1001)),$F$3-SUM(F$5:F5),IF($F$3=SUM(F$5:F5),"",IF($F$3-SUM(F$5:F5)<=INDEX(B:B,LARGE(($B$5:$B$1003<>"")*ROW($B$5:$B$1003),ROW(F2))),$F$3-SUM(F$5:F5),INDEX(B:B,LARGE(($B$5:$B$1003<>"")*ROW($B$5:$B$1003),ROW(F2))))))
F3=LOOKUP(9E+307,D:D)
G5=IF(F5="","",$B$3-E5)公式向下复制
F5=IF(SUM($C$5:$C$1003)=0,$F$3,IF(F3>INDEX(C:C,LARGE(($C$5:$C$1003<>"")*ROW($C$5:$C$1003),ROW(F1))),INDEX(C:C,LARGE(($C$5:$C$1003<>"")*ROW($C$5:$C$1003),ROW(F1))),F3))
以下公式向下复制
E5=IF(F5="","",IF(ISERROR(INDEX(A:A,LARGE(($C$5:$C$1003<>"")*ROW($C$5:$C$1003),ROW(F1)))),$A$5,INDEX(A:A,LARGE(($C$5:$C$1003<>"")*ROW($C$5:$C$1003),ROW(F1)))))
F6=IF(AND(F5<>"",(ROW()-ROW($F$5))=COUNT($C$5:$C$1001)),$F$3-SUM(F$5:F5),IF($F$3=SUM(F$5:F5),"",IF($F$3-SUM(F$5:F5)<=INDEX(C:C,LARGE(($C$5:$C$1003<>"")*ROW($C$5:$C$1003),ROW(F2))),$F$3-SUM(F$5:F5),INDEX(C:C,LARGE(($C$5:$C$1003<>"")*ROW($C$5:$C$1003),ROW(F2))))))