(輔助) 由下往上同名称累加 I2:I17=SUMIF(F2:F17,F2,G2:G17)
用輔助D2:D5{=SUM(TEXT(IF(B2>I$2:I$17,,B2-I$2:I$17)+G$2:G$17,"0;!0")*(F$2:F$17=A2)*H$2:H$17)
無輔助 D2:D5{=SUM(TEXT(IF(B2>SUMIF(OFFSET(F$1,ROW($1:$16),,16),F$2:F$17,OFFSET(G$1,ROW($1:$16),,16)),,B2-SUMIF(OFFSET(F$1,ROW($1:$16),,16),F$2:F$17,OFFSET(G$1,ROW($1:$16),,16)))+G$2:G$17,"0;!0")*(F$2:F$17=A2)*H$2:H$17)
無輔助 另解{=SUM(IFERROR(TEXT(SUMIF(OFFSET(F$2,,,ROW($1:$16)),A2,G$2:G$17)-SUMIF(F$2:F$17,A2,G$2:G$17)+B2,"[<0]\0;[<"&G$2:G$17&"]0;")*H$2:H$17,G$2:G$17*H$2:H$17)*(F$2:F$17=A2))
留言
張貼留言