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

[µo°Ý] °Ó«~¼Æ¶q²Î­pªí

°Ó«~¼Æ¶q²Î­pªí

°ª¶¯·~ÁZ¤ÀªR1.zip (76.78 KB)





¤j¤j­Ì±z¦n:
§Ú·Q­n²Î­p¨C­Ó²Õ´ÁʶR°Ó«~ªº¼Æ¶q
¦p¹Ï1 Äæ¦ìD  «~¸¹¼Æ¶q³¡¥÷   "200-003:1"    ¡÷ 200-003¥Nªí°Ó«~§O    :1¥Nªí¼Æ¶q( :2¥Nªí¸Óµ§°Ó«~¥æ©öªº¼Æ¶q¬°2)
¹Ï2 ·|§â¹Ï1ªºÄæ¦ìD  °Ó«~¸ê®Æ§ì¨ì¹ïÀ³¤§Äæ¦ì  
¥Ø«e¸ê®Æ¬~¥X¨Ó³£¬O:1    ¥Nªí¨Cµ§¥æ©ö°Ó«~¼Æ¶q³£¬O1
§Ú·Q½Ð±Ð¤j¤j­Ì¦pªG¤§«á¹J¨ì¸ê®Æ¦³:2ªº¸Ü(¥Nªí¸Ó¥æ©ö°Ó«~¼Æ¶q¬°2)   ¨º»ò¨ç¼Æ­n«ç»ò¼g©O??
½Ð¤j¤j«ü¾É
³Â·Ð¤F..
¤£·|EXCALLªº²Â³J

TOP

[µo°Ý] °Ó«~¼Æ¶q²Î­pªí

°ª¶¯·~ÁZ¤ÀªR1.zip (76.78 KB)





¤j¤j­Ì±z¦n:
§Ú·Q­n²Î­p¨C­Ó²Õ´ÁʶR°Ó«~ªº¼Æ¶q
¦p¹Ï1 Äæ¦ìD  «~¸¹¼Æ¶q³¡¥÷   "200-003:1"    ¡÷ 200-003¥Nªí°Ó«~§O    :1¥Nªí¼Æ¶q( :2¥Nªí¸Óµ§°Ó«~¥æ©öªº¼Æ¶q¬°2)
¹Ï2 ·|§â¹Ï1ªºÄæ¦ìD  °Ó«~¸ê®Æ§ì¨ì¹ïÀ³¤§Äæ¦ì  
¥Ø«e¸ê®Æ¬~¥X¨Ó³£¬O:1    ¥Nªí¨Cµ§¥æ©ö°Ó«~¼Æ¶q³£¬O1
§Ú·Q½Ð±Ð¤j¤j­Ì¦pªG¤§«á¹J¨ì¸ê®Æ¦³:2ªº¸Ü(¥Nªí¸Ó¥æ©ö°Ó«~¼Æ¶q¬°2)   ¨º»ò¨ç¼Æ­n«ç»ò¼g©O??
½Ð¤j¤j«ü¾É
³Â·Ð¤F..
¤£·|EXCALLªº²Â³J

¦^´_ 1# prince0413


1. ¥u¬Ý¨ì¹Ï¤@ªºÀɮסAµL¹Ï¤GªºÀɮסA¬Gµ¹§Aªº¤½¦¡©È¦³»~¡AÁٽЭì½Ì¡C

2. ¦pªG§A¥u¬O­n§ì¼Æ¶qªº¤½¦¡¦p¤U¡G
¡@¡@=RIGHT(©Ò¦³­q³æ!H20)

3. ¦pªG§Aªº¡i°Ó«~§O¡j»P¡i°Ó«~¼Æ¶q¡j¬O¤À¶}ªº¡A´N¨S¦³©¹«áºûÅ@ªº°ÝÃD¡A
¡@¡@¦pªG¬O¦X¨Ö¦b¤@°_ªº¸ê®Æ¡A«Øij¤À¶}¤£¦PÄæ¦ì¤ñ¸û¦n©¹«áªº¸ê®Æ®wºûÅ@¡C
§Ú«Ü²Â,©Ò¥H§Ú«Ü¥Î¤ß
¡@§Ú¾ÇÃÀ¤£ºë,¦ý§Ú´÷¨Dª¾ÃÑ

TOP

¦^´_ 1# prince0413


¹Ï1ªºD8Àx¦s®æ¤S¸Ó¦p¦ó¸ÑŪ?
°Ó«~¥N¸¹¤S¸Ó¦p¦ó¹ïÀ³?
²Õ´§P©w­ì«h¤S¬O¥Ñ¦óÄæ¦ì§PÂ_?
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 4# Hsieh

¤j¤j±z¦n:
¹Ï1.D8Àx¦s®æ 200-003:1201-002:1999-010:1999-020:1   
¥ÎÃC¦â°Ï¤À¥Nªí¸Óµ§¥æ©ö¦³4¶µ°Ó«~:200-003    201-002   999-010   999-020   °Ó«~¼Æ¶q³£¬O¦U1

°Ó«~¥N¸¹ªí:
´`Àô/°l¥[(ÃÑÅQ): 200-003¡B660-100
´`Àô/°l¥[(¬¡¬ü):201-002¡B660-200
´`Àô/°l¥[(©ú¤O¦h):200-200¡B660-120
´`Àô/°l¥[(¯À©ú¤O¦h):660-122¡B660-122

¥ý¤j­P¦C¥X³o4¶µ°Ó«~¹ïÀ³¥N½X

¥HL15Àx¦s®æ¤½¦¡: =COUNTIFS($F$8:$F$2017,1,$D$8:$D$2017,"200-003*")+COUNTIFS($F$8:$F$2017,1,$D$8:$D$2017,"660-100*")
¦ý¦]¬°¥Ø«e¨Cµ§°Ó«~¼Æ¶q³£¬O:1(¼Æ¶q³£¬O1)
©Ò¥H¨ú¥X¨Óªº¼Æ­È³£¬O¥¿½Tªº
¥u¬O¤§«á¹J¨ì¸ê®Æ¦³:2¥H¤Wªº¸Ü¸Ó«ç»ò¿ì
¨ç¼Æ­n«ç»ò¼g¤~¦n©O?

¦A½Ð¤j¤j±Ð¾É
¤£·|EXCALLªº²Â³J

TOP

¦^´_ 3# »k¥ú°j¯T


    ¦^´_ 4# Hsieh

¤j¤j±z¦n:

¥i§_¤U¸ü§ÚªºexcelÀÉ®×
³o¼Ë¥i¯à·|¤ñ¸û²M·¡§Ú·Qªí¹Fªº·N«ä

°Ó«~¥N¸¹ªí:
´`Àô/°l¥[(ÃÑÅQ): 200-003¡B660-100
´`Àô/°l¥[(¬¡¬ü):201-002¡B660-200
´`Àô/°l¥[(©ú¤O¦h):200-200¡B660-120
´`Àô/°l¥[(¯À©ú¤O¦h):660-122¡B660-122

¥ý¤j­P¦C¥X³o4¶µ°Ó«~¹ïÀ³¥N½X

¥H¹Ï¤G L15Àx¦s®æ¤½¦¡: =COUNTIFS($F$8:$F$2017,1,$D$8:$D$2017,"200-003*")+COUNTIFS($F$8:$F$2017,1,$D$8:$D$2017,"660-100*")
¦ý¦]¬°¥Ø«e¨Cµ§°Ó«~¼Æ¶q³£¬O:1(¼Æ¶q³£¬O1)
©Ò¥H¨ú¥X¨Óªº¼Æ­È³£¬O¥¿½Tªº
¥u¬O¤§«á¹J¨ì¸ê®Æ¦³:2¥H¤Wªº¸Ü¸Ó«ç»ò¿ì
¨ç¼Æ­n«ç»ò¼g¤~¦n©O?

¦A½Ð¤j¤j±Ð¾É
¤£·|EXCALLªº²Â³J

TOP

¦^´_ 6# prince0413

200-003:2201-002:2999-010:2999-020:2

¬õ¦â¦r¬O¼Æ¶q¡A³Ì¤j­È·|¶W¹L9¶Ü?

³oºØ®æ¦¡³B²z¤W¤ñ¸û³Â·Ð¡A­pºâ¤W¤]¤ñ¸û¯Ó®É
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 6# prince0413


°ª¶¯·~ÁZ¤ÀªR_ML089.rar (109.74 KB)

L15
=SUM(COUNTIFS($G:$G,LEFT(L$8,3),$D:$D,"*"&MID($J15&"X",1,7)&":"&{1,2,3,4,5,6,7,8,9}&"*")*{1,2,3,4,5,6,7,8,9}, COUNTIFS($G:$G,LEFT(L$8,3),$D:$D,"*"&MID($J15&"X",9,7)&":"&{1,2,3,4,5,6,7,8,9}&"*")*{1,2,3,4,5,6,7,8,9})
¤½¦¡¿é¤J«á¡A¥i½Æ»s¶K¦Ü¶À¦â°Ï

¤½¦¡¥u¯à§@ 1~9 ¼Æ¶q¤Î1~2«~¸¹
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¦^´_ 6# prince0413

¸ê®Æ¥¼²Å¦X¸ê®Æ®w­ì«h¦s©ñ¡C·|¼W²K³B²z¤Wªº³Â·Ð
«Ø¥ß°Ó«~¥N¸¹¹ï·Óªí¡Aªþ¥ó¨Ï¥ÎVBA¦Û©w¸q¨ç¼Æ
°t¦X©w¸q¦WºÙ¹B¥Î¡A¶È¨Ñ°Ñ¦Ò

    ½Æ¥» °ª¶¯·~ÁZ¤ÀªR1.zip (77.58 KB)
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 9# Hsieh


    ÁÂÁ¤j¤jªº«ü¾É
¤£·|EXCALLªº²Â³J

TOP

        ÀR«ä¦Û¦b : «Ý¤H°h¤@¨B¡A·R¤H¼e¤@¤o¡A´N·|¬¡±o«Ü§Ö¼Ö¡C
ªð¦^¦Cªí ¤W¤@¥DÃD