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

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

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

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

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




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

¦^´_ 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
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

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

TOP

¥»©«³Ì«á¥Ñ ­ã´£³¡ªL ©ó 2020-4-28 10:54 ½s¿è

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

TOP

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

TOP

        ÀR«ä¦Û¦b : ¤H¨ÆªºÁ}Ãø»PµZ¿i¡A´N¬O¤@ºØ¦ÒÅç¡C
ªð¦^¦Cªí ¤W¤@¥DÃD