ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[µo°Ý] ½Ð°Ý¦p¦ó¦Û°Ê¦C¥X¸ê®Æ®w¸ê®Æ

[µo°Ý] ½Ð°Ý¦p¦ó¦Û°Ê¦C¥X¸ê®Æ®w¸ê®Æ

¥»©«³Ì«á¥Ñ urlsyu ©ó 2020-2-10 14:00 ½s¿è

½Ð°Ý¤j¤j¡A°²³]§Ú¦³¸ê®Æ®w¦p¤U
·Q­n±N¨äÂà¸m¦¨³øªí¤º®æ¦¡«áA4¦L¥X¡A¤è«K²{³õ¤H­û®Ö¹ï¶i¥X³f
¨Ò¦p§Ú·Q½Õ¥X¬Y§å¸¹ªº³W®æ0~6

³o»Ý­n¦p¦ó»s§@¡HÁÂÁÂ

¬¡­¶Ã¯1.zip (24.06 KB)

¥»©«³Ì«á¥Ñ hcm19522 ©ó 2020-2-10 16:14 ½s¿è

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©Ô
ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

¤j¤j§A¦n¡A§Ú¶K¤W¥H«á¬OªÅ¥ÕÅã¥Ü
§Ú¤§«eª¦¤å¤]¬Oª¾¹D¥ÎOFFSET»yªk
¦ý¥d¦b¦p¦ó§PÂ_¸ê®Æ®w§å¸¹¸ò³W®æ
½Ð°Ý§A´£¨Ñ§PÂ_³W®æ¨Ì¾Ú¬O¦b¨º?

¤j·PÁÂ

TOP

¦^´_ 3# urlsyu

¼Æ²Õ¤½¦¡ :½Æ»s¤½¦¡ ¤£§t "=" ,¶K¤W«á¥[ "="   ;³Ì«e "{" ¥Nªí shift+ctrl+enter ¤TÁä¤@°_«ö©Ò²£¥Í ,«Dµ{¦¡¸Ì­±
ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

¥»©«³Ì«á¥Ñ urlsyu ©ó 2020-2-10 22:19 ½s¿è

¦^´_ 4# hcm19522
¤j¤j§A¦n
ª¦¤åª¦ªºÀY´ß·w·w
¦¨¥\¤F

¤j¤j¤j·P®¦:L

TOP

ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

¦^´_ 6# hcm19522

½Ð°Ý¤j¤j
¥[¤J¥X³f¸ê®Æ«á¡A·Q»s§@®w¦s¸ê®Æ¡A­n¦p¦ó§PÂ_³W®æÄæ¡H

·P®¦~
¬¡­¶Ã¯1.zip (32.91 KB)

TOP

¦^´_ 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)
ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

¦^´_ 8# hcm19522

¦A¦¸·PÁ¤j¤j
³Ì«á·Q±N®w¦s¥u¦C¥X©|¦³®w¦s§å¸¹¦pÀɮשҦC¡A¬ã¨s¦n´X¤Ñ¼g¤£¥X¨Ó:dizzy:
¬¡­¶Ã¯1.zip (33.69 KB)

·P®¦·P®¦

TOP

¦^´_ 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))
ÀH·NºÛ "EXCEL°g"  blog  ©Îhttps://hcm19522.blogspot.com/ EXCEL¨ç¼Æ

TOP

        ÀR«ä¦Û¦b : §Ú­Ì­n°µ¦nªÀ·|ªºÀô«O¡A¤]­n°µ¦n¤º¤ßªºÀô«O¡C
ªð¦^¦Cªí ¤W¤@¥DÃD