hcm19522

B2 ¥k©Ô ¤U©Ô{=IFERROR(INDEX(¸ê®Æ®w!$E:$E,SMALL(IF((OFFSET($A1,MATCH(1=1,$A2:$A999<>"",),)="³W®æ"&¸ê®Æ®w!$D$2:$D$999)*(¸ê®Æ®w!$C$2:$C$999=$B$1),ROW($2:$999)),(ROW()-LOOKUP(1,0/($A$1:$A1<>""),ROW($1:1)))*10-10+COLUMN(A1))),"")

B2½Æ»s¨ì¦U¼Æ¶q ¤U¤@®æ ¥k©Ô ¤U©Ô

hcm19522

¦^´_ 3# urlsyu

¼Æ²Õ¤½¦¡ :½Æ»s¤½¦¡ ¤£§t "=" ,¶K¤W«á¥[ "="   ;³Ì«e "{" ¥Nªí shift+ctrl+enter ¤TÁä¤@°_«ö©Ò²£¥Í ,«Dµ{¦¡¸Ì­±

hcm19522

¦^´_ 5# urlsyu


    https://blog.xuite.net/hcm19522/twblog/588890415

hcm19522

¦^´_ 7# urlsyu


    ®w¦s C3:I4=SUMIFS(¸ê®Æ®w!$E:$E,¸ê®Æ®w!$C:$C,$B3,¸ê®Æ®w!$D:$D,C$2)-SUMIFS(¥X³f!$E:$E,¥X³f!$C:$C,$B3,¥X³f!$D:$D,C$2)

hcm19522

¦^´_ 9# urlsyu


®w¦sA3 ¤U©Ô{=IFERROR("A000"&SMALL(IF(SUMIF(¸ê®Æ®w!B:B,"A000"&ROW($1:$9)-1,¸ê®Æ®w!$D:$D)-SUMIF(¥X³f!$C:$C,"A000"&ROW($1:$9)-1,¥X³f!$E:$E),ROW($1:$9)-1),ROW(A1)),"")

®w¦sB3 ¥k©Ô ¤U©Ô=IF($A3="","",SUMIFS(¸ê®Æ®w!$D:$D,¸ê®Æ®w!$B:$B,$A3,¸ê®Æ®w!$C:$C,B$2)-SUMIFS(¥X³f!$E:$E,¥X³f!$C:$C,$A3,¥X³f!$D:$D,B$2))

hcm19522

¦^´_ 11# urlsyu

=IFERROR("A"&TEXT(SMALL(IF(SUMIF(¸ê®Æ®w!B:B,"A"&TEXT(ROW($1:$99)-1,"0000"),¸ê®Æ®w!$D:$D)-SUMIF(¥X³f!$C:$C,"A"&TEXT(ROW($1:$99)-1,"0000"),¥X³f!$E:$E),ROW($1:$99)-1),ROW(A1)),"0000"),"")

hcm19522

(»²§U ¨ú§å¸¹¤£­«½Æ) ¸ê®Æ®wF1 ¤U©Ô{=INDEX(B:B,MIN(IF((COUNTIF(F$1:F1,B$2:B$999)=0)*(B$2:B$999<>""),ROW($2:$999),4^8)))&""

®w¦sA3 ¤U©Ô {=IFERROR(INDEX(¸ê®Æ®w!F:F,SMALL(IF(SUMIF(¸ê®Æ®w!B:B,¸ê®Æ®w!F$2:F$99,¸ê®Æ®w!$D:$D)-SUMIF(¥X³f!$C:$C,¸ê®Æ®w!F$2:F$99,¥X³f!$E:$E),ROW($2:$99)),ROW(A1))),"")