[µo°Ý] ¦hÀx¦s®æ´M§ä¯S©w±ø¥ó

free72921

½Ð°Ý¦U¦ì«e½ú¦p¦ó¦b¤u§@­¶¤¤¿é¤J¡yª««~¡z«á¦^¶Ç¤ÀÃþªí¤¤ªº¤ÀÃþ¶µ¥Ø¡A

·PÁ¦U¦ì«e½ú¡C

¤U¸ü (51.82 KB)
¤ÀÃþ¶µ¥Ø

2020-4-26 19:02

¤U¸ü (52.15 KB)
¦^¶Ç¤ÀÃþ

2020-4-26 19:02

¦hÀx¦s®æ´M§ä¯S©w±ø¥ó.zip (6.9 KB)

¤U¸ü¦¸¼Æ: 20

2020-4-26 19:05

GBKEE

¦^´_ 1# free72921
¸Õ¸Õ¬Ý

1. Option Explicit
   
2. Sub Ex()
   
3.     Dim AR As Variant, i As Integer, E As Variant
   
4.     With Sheets("¤ÀÃþªí")
   
5.         i = 2  '²Ä¤G¦C¶}©l
   
6.         Do While .Cells(i, "A") <> ""   '°õ¦æ¤@ª½¨ì¨S¸ê®Æ
   
7.             AR = AR & IIf(AR <> "", vbLf, "") & .Cells(i, "A") & "," & .Cells(i, "B") '  ±µ¦X  ¤ÀÃþ"," ª««~
   
8.             '**Ū¨úª««~¸ê®Æ   vbLf  Àé¦C     '**ARªº«¬ºA¬°¦Û¦ê
   
9.             i = i + 1   '¤U¤@¦C
   
10.         Loop
    
11.         If AR <> "" Then AR = Split(AR, vbLf)    '±N«¬ºA¬°¦Û¦ê Âର°}¦C
    
12.     End With
    
13.     With Sheets("¤u§@­¶")
    
14.         i = 2
    
15.         Do While .Cells(i, "A") <> ""
    
16.              For Each E In AR            '°}¦C ªº¨C¤@¤¸¯À
    
17.                 If InStr(E, .Cells(i, "A")) Then    'InStr ¤ñ¹ï¤¸¯À¤¤¬O§_¦³«ü©wªºª««~
    
18.                     .Cells(i, "B") = Mid(E, 1, InStr(E, ",") - 1)  '¼g¤J¤ÀÃþ
    
19.                     Exit For
    
20.                 End If
    
21.             Next
    
22.             i = i + 1
    
23.         Loop
    
24.     End With
    
25. End Sub

½Æ»s¥N½X

­ã´£³¡ªL

=LOOKUP(,-FIND(","&A2&",",","&¤ÀÃþªí!B$1:B$199&","),¤ÀÃþªí!A:A)&""

­ã´£³¡ªL

Sub TEST()
Dim Arr, A, xD, i&
Set xD = CreateObject("Scripting.Dictionary")
Arr = Range([¤ÀÃþªí!A1], [¤ÀÃþªí!B65536].End(xlUp))
For i = 2 To UBound(Arr)
    For Each A In Split(Arr(i, 2), ","): xD(A & "") = Arr(i, 1): Next
Next i
With Range([¤u§@­¶!B2], [¤u§@­¶!A65536].End(xlUp))
     Arr = .Cells.Value
     For i = 1 To UBound(Arr): Arr(i, 1) = xD(Arr(i, 1) & ""): Next
     .Columns(2).Value = Arr
End With
End Sub

free72921

«D±`·PÁÂ2¦ìª©¥D«e½ú¡A¨ü±Ð¤F¡C
ÁÂÁÂ