D2=B2/SUMPRODUCT((LOOKUP(ROW($2:9),ROW($2:9)/(A$2:A9<>""),A$2:A9)=LOOKUP("嫻",A$2:A2))*B$2:B9)*1/LOOKUP(9,1/C$2:C2)
另解D2=B2/SUMPRODUCT((SUBTOTAL(3,OFFSET(A$2,,,ROW($1:8)))=COUNTIF(A$2:A2,"<>"))*B$2:B9)*1/LOOKUP(9,1/C$2:C2)
另解=IF(A2="",D1/B1*B2,C2/LOOKUP(1,SUBTOTAL({3,9},OFFSET(A2,,{0,1},ROW($1:9))))*B2)
另解 {=1/LOOKUP(9,1/C$2:C2)*B2/SUM((LOOKUP("嫻",A$1:A2)=LOOKUP(ROW($2:9),ROW($2:9)/(A$2:A9>0),A$2:A9))*B$2:B10)
另解 {=LOOKUP(1,0/C$2:C2,C$2:C2)/SUM((LOOKUP(ROW($2:9),IF(A$2:A9<>"",ROW($2:9)),A$2:A9)=LOOKUP("座",$A$2:A2))*B$2:B9)*B2
留言
張貼留言