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

½Ð±Ð¦r¦ê¤¤ªº¸ê®Æ¦p¦ó¤ñ¹ï¦ÓÂ^¨ú¬Û¹ïÄæ¦ìªº¸ê®Æ?

½Ð±Ð¦r¦ê¤¤ªº¸ê®Æ¦p¦ó¤ñ¹ï¦ÓÂ^¨ú¬Û¹ïÄæ¦ìªº¸ê®Æ?

½Ð±Ð¦r¦ê¤¤ªº¸ê®Æ¦p¦ó¤ñ¹ï¦ÓÂ^¨ú¬Û¹ïÄæ¦ìªº¸ê®Æ?
½Ð¬ÝªþÀÉ.........ÁÂÁ«ü¾É

Book1.rar (4.71 KB)

¥Ø«e¤p¾Ç¥Í´Á«Ý¥L¤é¶i°ê¤¤

ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾ÇÂǦ¹©«½m²ß°}¦C»P¦r¨å,¾Ç²ß¤è®×¦p¤U,½Ð¦U¦ì«e½ú«ü±Ð

°õ¦æ«e:


°õ¦æµ²ªG:



Option Explicit
Sub TEST()
Dim Brr, Crr, Y, Z, i&, T1$, T2$
'¡ô«Å§iÅܼÆ
Set Y = CreateObject("Scripting.Dictionary")
'¡ô¥OYÅܼƬO ¦r¨å
Brr = Range([B10], [B65536].End(3))
'¡ô¥OBrrÅܼƬO ¤Gºû°}¦C,¥HBÄæÀx¦s®æ­È±a¤J
Crr = Range([H22], [G65536].End(xlUp))
'¡ô¥OCrrÅܼƬO ¤Gºû°}¦C,¥HG~HÄæÀx¦s®æ­È±a¤J
For i = 1 To UBound(Crr)
'¡ô³]¶¶°j°é (©î¸Ñ²Õ§O¹ïÀ³¦¨­û(GÄæ),¸Ë¤JY¦r¨å¤¤)
   T1 = Crr(i, 1): T2 = Crr(i, 2)
   '¡ô¥OÅܼƩӸ˰j°é°}¦C­È
   If (T1 = "") * (T2 = "") Then GoTo i01
   '¡ô©¿²¤ªÅ®æ
   For Each Z In Split(T1, ",")
   '¡ô³]³v¶µ°j°é!¥OZÅܼƬO (GÄæ)­È¥H","¤À³Îªº¤@ºû°}¦C¤l
      If Z = "" Then GoTo z01
      '¡ô¦pªGZÅܼƬOªÅ¦r¤¸!´N¸õ¨ìz01¼Ð¥Ü¦ì¸mÄ~Äò°õ¦æ
      Y(Z) = T2
      '¡ô¥O¥HZÅܼƷíkey,item¬OT2ÅܼÆ(²Õ¦W)
z01: Next
i01: Next
For i = 1 To UBound(Brr)
'¡ô³]¶¶°j°é (±N±ý¿ëÃѪº¦¨­û·íkey,¬dY¦r¨å±o¨ì²Õ¦W)
   Brr(i, 1) = Y(Brr(i, 1) & "")
   '¡ô¥O¬dY¦r¨å±o¨ì²Õ¦WÂл\±¼­ì¨ÓBrrªº¦¨­û
Next
[L10].Resize(UBound(Brr), 1) = Brr
'¡ô¥OBrr°}¦C­È±q[L10]¶}©l¼g¤JÀx¦s®æ¤¤
Set Y = Nothing: Erase Brr, Crr
'¡ô¥OÄÀ©ñÅܼÆ
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¤p¾Ç¥ÍµLªk¤U¸ü¾Ç²ß¡AÁÂÁÂ
  

TOP

¦^´_ 2# oobird

¥i¥H¤F......ÁÂÁª©¥D«ü¾É
¥Ø«e¤p¾Ç¥Í´Á«Ý¥L¤é¶i°ê¤¤

TOP

¥»©«³Ì«á¥Ñ oobird ©ó 2010-11-21 17:31 ½s¿è

=INDEX(H$22:H$24,MATCH(TRUE,NOT(ISERROR(FIND(","&B10&",",","&G$22:G$24&","))),0))

TOP

        ÀR«ä¦Û¦b : ºÉ¦h¤Ö¥»¥÷¡A´N±o¦h¤Ö¥»¨Æ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD