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

[µo°Ý] ±qÀx¦s®æ¥ªÃä¶}©l·j´M¦r¦ê

¦^´_ 9# ­ã´£³¡ªL


    ­ã´£³¡ªLª©¤j±z¦n,¦pªG­n¦A·s¼W¨ä¥L§÷½è¦bG1:G20ªº¸Ü,¨ç¼Æ·|µLªk¥¿½T±a¥X©Î§e²{ªÅ¥Õ
¦pªþÀÉ©Ò¥Ü,§Ú§Æ±æ¦bG2¤U°}¦C¤½¦¡(¨ç¼Æ),°Ñ·Ó·j´MÃöÁä¦r¦b ¤u§@ªí:§÷½è!D2:D20
§Ú§Æ±æ°Ñ·Ó¦ì¸mº¸«á¥i¥H¦A·s¼W,©Ò¥H¦ì¸m©Ô¨ìD20,§Æ±æª©¤j¯à°÷¨ó§U,ÁÂÁÂ.

§÷½è·j´M¨ç¼Æ.rar (25.64 KB)

TOP

¦^´_ 11# show780106


¢Õ¢±¡D°}¦C¤½¦¡¡G
=INDEX(§÷½è!$D:$D,RIGHT(SMALL(IF(1-ISERR(0/(FIND(§÷½è!$D$1:$D$20,"|"&$D2)>1)),FIND(§÷½è!$D$1:$D$20,$D2)*10^5+ROW($D$1:$D$20),10^9+4^8),COLUMN(A$1)),5))&""

°Ñ¦ÒªþÀÉ¡G
Xl0000191.rar (81.27 KB)

TOP

¦^´_ 11# show780106

Sub TEST()
Dim xArea As Range, Arr, Brr, N&, i&, TR, T, TT$, xClmn As Range, xR As Range
Application.ScreenUpdating = False
[D:D].Copy [G:G]
Set xArea = Range([G2], [G65536].End(xlUp))
Set xClmn = Range([§÷½è!D2], [§÷½è!D65536].End(xlUp))
For Each xR In xClmn
¡@¡@If xR <> "" Then xArea.Replace xR, "_||" & xR & "_", Lookat:=xlPart
Next
¡@
Arr = xArea.Value
ReDim Brr(1 To UBound(Arr), 1 To xClmn.Count)
For i = 1 To UBound(Arr)
¡@¡@TR = Split(Arr(i, 1), "_"):   N = 0:   TT = ""
¡@¡@For Each T In TR
¡@¡@¡@¡@If Left(T, 2) = "||" And InStr(TT, T) = 0 Then
¡@¡@¡@¡@¡@TT = TT & T:   N = N + 1:   Brr(i, N) = Mid(T, 3)
¡@¡@¡@¡@End If
¡@¡@Next
Next i
¡@
With [G2].Resize(UBound(Arr), xClmn.Count)
¡@¡@¡@.Value = Brr
¡@¡@¡@.Columns.AutoFit
End With
Beep
End Sub

°Ñ¦ÒªþÀÉ¡G
Xl0000191-v01.rar (28.58 KB)

TOP

¥»©«³Ì«á¥Ñ yen956 ©ó 2015-11-25 19:59 ½s¿è

ÁÙ¬O­ãª©¤jªº¤½¦¡§Q®`!!
¥Ñ¥ª¦Ü¥k¨Ì§Ç¥þ¨ú¥X¡G
  1. Private Sub CommandButton1_Click()
  2.     Dim I As Integer, J As Integer
  3.     Dim Col1 As Integer, Num As Integer
  4.     [B:H] = ""
  5.     For I = 1 To [A65536].End(xlUp).Row
  6.         For J = 1 To [J1].End(xlDown).Row
  7.             Num = InStr(Cells(I, 1), Cells(J, 10))
  8.             If Num > 0 Then
  9.                  Cells(J, 11) = Num
  10.             Else
  11.                  Cells(J, 11) = 10000
  12.             End If
  13.         Next
  14.         [J:K].Sort Key1:=Range("K1"), Order1:=xlAscending, Header:=xlNo
  15.         Col1 = 2
  16.         For J = 1 To [J1].End(xlDown).Row
  17.             If Cells(J, 11) < 9999 Then
  18.                 Cells(I, Col1) = Cells(J, 10)
  19.                 Col1 = Col1 + 1
  20.             End If
  21.         Next
  22.     Next
  23.     [K:K] = ""
  24. End Sub
½Æ»s¥N½X
test.gif
2015-11-25 19:56

TOP

        ÀR«ä¦Û¦b : ¡i¬°µ½Ävª§¡j¤H¥Í­n¬°µ½Ävª§¡A¤À¬í¥²ª§¡C
ªð¦^¦Cªí ¤W¤@¥DÃD