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

[µo°Ý] excel ¼Ò½k¤ñ¹ïªº°ÝÃD

[µo°Ý] excel ¼Ò½k¤ñ¹ïªº°ÝÃD

¥»©«³Ì«á¥Ñ oshi ©ó 2015-2-4 23:09 ½s¿è

¥Ø«e¦³¤@­ÓÀÉ®×

¥Øªº:
CÄæ¦ìªº­È¡A¼Ò½k¤ñ¹ïBÄæ¦ì¡C
¦pªG§ä¨ìÃþ¦üªº´N¦bDÄæ¦ì¶ñ¤JAÄæ¦ìªº­È¡A¦pªþ¹Ï

°ÝÃD:
¥»¨Ó·Q­n¨Ï¥Îmatch("*"&C2&"*"....¥h¦¬´M

¦ý¬O·|µo¥ÍCÄæ¦ì¸Ì­±ªº¦r¦ê·|¤ñBÄæ¦ì¦h
©Ò¥H¦b§P§Oªº®É­Ô´N·|Åܦ¨ " *DVDD12_LP2_3* "


§Æ±æ¦U¦ì¤j¤j¯à°÷À°¤p§Ì¸Ñ¨M³o­Ó°ÝÃD>"<
·PÁÂ

DEAR SIR
¨Ï¥Î¦p¤U¸Õ¸Õ
=IF(SUBSTITUTE(C2,B2,"")=C2,"",A2)
ù

TOP

¦^´_ 2# rouber590324


    rouber¤j±z¦n~
­è­è¸Õ¤F¤@¤U§Aµ¹±o´£¥Üµo²{

¥L¥u·|§â²Ä¤@­Ó§ó·s¤§«á´N¨S¤ÏÀ³¤FXDD

¬Q¤Ñ¨S¦³»¡©ú²M·¡¡A·Q¨ìªº®É­Ô¤w¸g¤£¯à½s¿è¤F

Àɮ׸̭±¦³«Ü¦h(B/CÄæ)­«½Æªº¦W¦r¡A¦ýBÄ檺¦WºÙ¤@©w¦³¬Û¹ïÀ³ªºAÄæ

B/CÄæ¦ìªø«×¤£¤@¼ËQ_Q

¦Ó¦ì¸m¤]¤£¹³¬O¬Q¤Ñ´£¨Ñªº¨º»ò¾ã»ô

¥i¯à·|¬O

A1  bbbb PPPP
B2  CCC   bbbb
C3 bbbb  YYYY
                   QQQ


¤j·§¬O³o¼Ë ¡A¤£¹LÁÙ¬O«Ü·PÁÂR¤jÀ°§ÚQQ

TOP

DEAR SIR
§Ú¥u¬O¼g  D2½d¨Ò  §A­n¥Î´å¼ÐCOPY¤½¦¡¦Ü D3-D65536°Õ
D3¥H«áµL¤½¦¡·íµMµL¤ÏÀ³.

=IF(SUBSTITUTE(C2,B2,"")=C2,"",A2)
ù

TOP

¦^´_ 4# rouber590324


    Dear R¤j

§Ú¦³½Æ»s­C¨Ã¥B¤U©Ô³á~

µ²ªG´N¬OÅã¥Ü­è­è¨º¼Ë...0.0

TOP

DEAR SIR
§Ú¥u¬O¼g  D2½d¨Ò  §A­n¥Î´å¼ÐCOPY¤½¦¡¦Ü D3-D65536°Õ
D3¥H«áµL¤½¦¡·íµMµL¤ÏÀ³.

=IF(SUBST ...
rouber590324 µoªí©ó 2015-2-5 15:49



    Dear R¤j

¥t¥~¦³¤@­Ó°ÝÃD¬O~¥ÎSUBSTITUTE ¤£´N¥u¬O§âBÄ檺¦r¦ê´À´«¨ìCÄæ??

³o¼Ë¸ò§Ú·Q­n°µªº¼Ò½k¤ñ¹ï¦n¹³¤£¤Ó¤@¼ËQQ

TOP

¦^´_ 6# oshi

¥H¹ÏÀɸê®Æ¬Ý¨ÓBÄæ»PCÄæ¤ñ¹ï¡A¥i¯à¤£¥u¤@­ÓÀx¦s®æ²Å¦X
§A­n¶Ç¦^ªº¬O¨º¨Ç¸ê®Æ?
½Ð±NEXCELÀÉ®×À£ÁY«á¤W¶Ç¡A¤è«K´ú¸Õ¨Ï¥Î
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¥»©«³Ì«á¥Ñ oshi ©ó 2015-2-5 21:59 ½s¿è

¦^´_ 7# Hsieh

¼Ò½k¤ñ¹ï1.zip (18.16 KB)

¦]¬°¯A¤Îªº«O±Kªº°ÝÃD
©Ò¥H§Ú¥t¥~°µ¤F¤@¥÷excel¡A¤£¹L¤j·§ªº·§©À¬O¤@­Pªº

¦pªþÀÉ~

Step1¡G¤ñ¹ïBÄæ¦ì¸òDÄæ¦ìªº­È ¡A­Y¦³¤ñ¹ï¨ì¬Û¦P­È¡A«hÅýAÄæ¦ìªº­È¶ñ¤JEÄæ¦ì
Step2 :  ¦pªG³£¨S¦³§ä¨ìEÄæ¦ì¶ñ#not found¡A­Y¦³§ä¨ì¦ý¬O¦³¦h¦ÓµLªk¶ñ¤J­ÈªºÀx¦s®æ¶ñ#TBD
Step3 : ¼Ò½k¤ñ¹ï¬O§_¦³Ãþ¦üname ¡A·Q­n¤]ÅýEÄæ¦ì¥i¥H¶ñ¤J­È
¥Ø«e´N¬O¥d¦b²Ä¤T¨B

¥»¨Ó¬O¦³¦Ò¼{¹L¥Îvlookupªº¼Ò½k¦¬´Mªº¥\¯à¡A
¦ý¬O´N·|¹J¨ìvlookup ¹J¨ì­«½Æ­È¥u·|¶ñ²Ä¤@­Ó­È
³o¼Ë´N¤£¬O§Úªº¥Øªº¤FQ_Q


¦A³Â·ÐHsiehª©¥D¤j¤j¤F

TOP

¦^´_ 8# oshi
µL²Å¦X¸ê®Æ¶ñ¤J"#not found"
²Å¦X¦³1­Ó¥H¤W«h¶ñ¤J"#TBD"
¶È1­Ó²Å¦X«h¶ñ¤JAÄæ¹ïÀ³­È
¬O³o¼Ë¶Ü?
¸Õ¸Õ¬Ý
E2¤½¦¡
=IF(SUMPRODUCT(ISNUMBER(FIND($D2,$B$2:$B$151))*1)=0,"#not found",IF(SUMPRODUCT(ISNUMBER(FIND($D2,$B$2:$B$151))*1)>1,"#TBD",LOOKUP(2,1/ISNUMBER(FIND($B$2:$B$151,$D2)),$A$2:A$151)))
¦V¤U½Æ»s
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¼Ò½k¤ñ¹ï¡G
=CHOOSE(SUM(N(COUNTIF(B:B,"*"&D2&"*")>{0,1}))+1,"#not found",INDEX(A:A,MATCH("*"&D2&"*",B:B,)),"#TBD")

TOP

        ÀR«ä¦Û¦b : ¦¨¥\¬OÀuÂIªºµo´§¡A¥¢±Ñ¬O¯ÊÂIªº²Ö¿n¡C
ªð¦^¦Cªí ¤W¤@¥DÃD