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

[µo°Ý] ®Ö¹ï¨â­ÓÀx¦s®æªº¸ê®Æ

MID($A5,FIND(",",$A5)+2,10)>>¨ú±o100PCS
FIND(MID($A5,FIND(",",$A5)+2,10),$B$1:$B$6)
>>¦b$B$1:$B$6¤¤¨ú±o¦³"100PCS"ªº©Ò¦b¦ì¸m,¥i¯à¬O{#VALUE!;#VALUE!;22;#VALUE!;#VALUE!;#VALUE!}

= ISNUMBER(FIND(MID($A5,FIND(",",$A5)+2,10),$B$1:$B$6))
={FALSE;FALSE;TRUE;FALSE;FALSE;FALSE}

IF(ISNUMBER(FIND(MID($A2,FIND(",",$A2)+2,10),$B$1:$B$6)),ROW($1:$6)
={FALSE;FALSE;3;FALSE;FALSE;FALSE}  >>²Ä3­Ó¦ì¸m¬°¯u

{="B"&MIN(IF(ISNUMBER(FIND(MID($A2,FIND(",",$A2)+2,10),$B$1:$B$6)),ROW($1:$6)))}
»PB²Õ¦X«á§Y¬°B3³o­Ó¦ì¸m¦³"100PCS"³o­Ó¬Û¦Pªº

¬°¤°»ò­n¥[MIN¬O¦]¬°¥i¯à¦³«Ü¦h¦ì¸m¦³¬Û¦Pªº"100PCS"¥u¨ú³Ì¥ý¥X²{ªº¦ì¸m
ª¾¤§¬°ª¾¤§¡A¤£ª¾¬°¤£ª¾¡A¸Û¹ê¤]¡I

TOP

Mr Gong... §A¦n^^
¤W¦¸½Ð±Ð§Aªºexcel , ¦b¨Ï¥Î¤W¥X¤FÂI¤p°ÝÃD,
·Q¦A½Ð±Ð§A, ¦p¹Ï¤¤:
¦b§Úªº¸ê®Æ¤¤·|¥X¨â­Ó", "¸¹ªº
¦b¨º­Óexcel¤½¦¡¤º¦h¤Öª¾¹D¬O¾a½T»{","¤§«áªº¸ê®Æ,
¦pªG§Ú·Q¦P®ÉÀˬd¨â­Ó","«áªº¸ê®Æ¬O§_¬Û¦P,
À³¸Ó­n«ç¼Ë?
¦A¦¸³Â·Ð§A¤F........!

¥¼©R¦W-4.jpg (45.67 KB)

¥¼©R¦W-4.jpg

TOP

¥»©«³Ì«á¥Ñ Hsieh ©ó 2011-3-8 18:55 ½s¿è

¦^´_ 12# seci
©w¸q¦WºÙ
y=MAX(IF(MID(Sheet1!$A2,ROW(INDIRECT("A1:A"&LEN(Sheet1!$A2))),1)=",",ROW(INDIRECT("A1:A"&LEN(Sheet1!$A2))),""))

C2°}¦C¤½¦¡
{="B"&MAX(IF(ISNUMBER(FIND(MID(A2,y+1,255),$B$2:$B$6)),ROW($A$2:$A$6),""))}
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

Mr Hsieh, ¦hÁ§Aªº¸Ñµª^^

TOP

Mr Hsieh,
¦p¹Ï¤¤:
§Ú¸Õ¹L±N","«áªº¨â²Õ¸ê®Ætest¹L,
µoı¥ú¬O¤@²Õ¬Û¦P¤]·|Åã¥Üµ²ªG,
¦³¨S¦³¤èªk­×§ï¦¨,
¤@©w­n","«á¨â²Õ¸ê®Æ¤@¼Ë¤~Åã¥Üµ²ªG©O~!
¸U¤ÀÁÂÁÂ!

¥¼©R¦W-2.jpg (48.92 KB)

¥¼©R¦W-2.jpg

TOP

¦^´_ 15# seci
¨º§Aªº¸ê®Æªº¤ñ¹ï³W«h¡A¨ì©³¬O³Ì«á¤@²Õ³r¸¹¤À¹jÁÙ¬O²Ä¤@²Õ¥H«áªº©Ò¦³¦r¦ê¤ñ¹ï
½Ð§â§Aªº¸ê®Æ§@¦¨EXCELÀɮ׫á¡AÀ£ÁY¤W¶Ç¡A¸ÑÄÀ²M·¡§Aªº¤ñ¹ï³W«h
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

Mr. Hsieh
ªþ¥ó¬O§Ú¹ê»Ú¤u§@­n¤ñ¹ïªº¸ê®Æ,
¦p¹Ï¥Ü :
§Ú­n¤ñ¹ï¨C®æ¤º³Ì«á¨â²Õ¸ê®Æ, ¸ê®Æ»Ý­n§¹¥þ¤@¼Ë.

Thank you very much~!

¥¼©R.jpg (56.04 KB)

¥¼©R.jpg

DATA1.rar (1.88 KB)

TOP

§A­nªº¬O²Ä¤G­Ó³r¸¹¬°¤ÀÂ_ÂI,¤£¬O³Ì«á¤@­Ó,§ï¤@¤UÁª©¥Dªº¤½¦¡
Y=LARGE(IF(MID(Sheet1!$A2,ROW(INDIRECT("A1:A"&LEN(Sheet1!$A2))),1)=",",ROW(INDIRECT("A1:A"&LEN(Sheet1!$A2))),""),2)
´N¬O¤F

TOP

¦^´_ 17# seci

©w¸q¦WºÙx
=LEN(Sheet1!$A2)-LEN(SUBSTITUTE(Sheet1!$A2,",",""))-2
C2°}¦C¤½¦¡
{="B"&MIN(IF(ISNUMBER(FIND(MID(A2,FIND(",",SUBSTITUTE(A2,",","",x))+3,255),$B$2:$B$4)),ROW($A$2:$A$4),""))}
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

Thanks a lot !

TOP

        ÀR«ä¦Û¦b : ºw¤ô¦¨ªe¡C²É¦Ì¦¨ÅÚ¡A¤Å»´¤vÆF¡A¤Å¥Hµ½¤p¦Ó¤£¬°¡C
ªð¦^¦Cªí ¤W¤@¥DÃD