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

¢ÐÄæ­n¨ú¥X²Å¦X¢ÒÄ椺ªºÃöÁä¦r

¢ÐÄæ­n¨ú¥X²Å¦X¢ÒÄ椺ªºÃöÁä¦r

¢ÐÄæ­n¨ú¥X²Å¦X¢ÒÄ椺ªºÃöÁä¦r¡A½Ð°Ý¦p¦ó³]©w¤½¦¡¡AÁÂÁ¡C

¢ÐÄæ­n¨ú¥X²Å¦X¢ÒÄ椺ªºÃöÁä¦r.zip (22 Bytes)

joyce

¦^´_ 1# leiru


ªþ¥ó¸ê®Æ§¨¬OªÅªº¡A½Ð¦A½T»{¡AÁÂÁÂ

TOP

¦^´_ 2# samwang


    ÁÂÁ§iª¾,ªþ¤WÀÉ®×

¢ÐÄæ­n¨ú¥X²Å¦X¢ÒÄ椺ªºÃöÁä¦r.zip (6.7 KB)

joyce

TOP

¦^´_ 3# leiru

Sub test()
Dim Arr, xD, Reg, xE, T$, T1$, i%, j%, a
Set xD = CreateObject("Scripting.Dictionary")
Set Reg = CreateObject("VBScript.RegExp")
Arr = Range([d1], [d65536].End(3))
For i = 2 To UBound(Arr): T = Arr(i, 1): xD(T) = "": Next
Arr = Range([b1], [a65536].End(3))
For i = 2 To UBound(Arr)
    T = Arr(i, 1)
    With Reg
        .Global = True
        .IgnoreCase = True
        .Pattern = "[\u4e00-\u9fa5()]+"
        Set xE = .Execute(T)
        a = Split(Trim(.Replace(T, "")), "¡B")
        For j = 0 To UBound(a)
            If Left(a(j), 2) <> "AA" Then
                pos = InStr(1, a(j), "AA")
                T = Mid(a(j), pos, Len(a(j)))
            Else
                T = a(j)
            End If
            If xD.Exists(T) Then: T1$ = T1$ & "¡B" & T
        Next
        Arr(i, 2) = Mid(T1, 2): T1 = ""
    End With
Next
[a1].Resize(UBound(Arr), 2) = Arr
End Sub

TOP

google"EXCEL°g"  blog  ©Îgoogleºô§}:https://hcm19522.blogspot.com/

TOP

¦^´_ 5# hcm19522

½Ð°Ý§Ú·Ó¤½¦¡³]©w,mark½d³òB2:E7,
¦bB2Àx¦s®æ¿é¤J=IFERROR(IF(COLUMN(A1)=1,"","¡B")&INDEX($D:$D,SMALL(IF(ISNUMBER(0/(FIND($D$10:$D$18,"@"&$A2)-1)),ROW($10:$18)),COLUMN(A1)))&C2,"")
«öctrl+shift+enter

¦ý¨SÅã¥Ü¥X§Æ±æµ²ªG

¢ÐÄæ­n¨ú¥X²Å¦X¢ÒÄ椺ªºÃöÁä¦r.zip (8.07 KB)

joyce

TOP

¥»©«³Ì«á¥Ñ hcm19522 ©ó 2021-11-9 15:53 ½s¿è

¦^´_ 6# leiru


   ¤w¦¨°Ï°ì°}¦C  B2:B7¥ý¤@°_§R B2­«·s ¦b¥k©Ô ¤U©Ô
  A5 ¦h­Ó 3
google"EXCEL°g"  blog  ©Îgoogleºô§}:https://hcm19522.blogspot.com/

TOP

¦pªGexcelª©¥»¨S¦³TEXTJOIN¨ç¼Æ, ¥Îvba¼g­ÓÃþ¦üªº¦Û­q¨ç¼Æ:
Xl0000808.rar (11.15 KB)

¤½¦¡//°}¦C
=TEXTJOIN("¡B",1,IF(1-ISERR(0/(FIND(D$2:D$16,"_"&A2)>1)),D$2:D$16,""))

TOP

ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾ÇÂǦ¹©«½m²ßVBA,¾Ç²ß¤è®×¦p¤U,½Ð¦U¦ì«e½ú«ü±Ð
°õ¦æ«e:


°õ¦æµ²ªG:


Option Explicit
Sub TEST()
Dim Brr, Crr, i&, x%, T$, T1$
Brr = Range([D2], [D65536].End(3))
Crr = Range([A2], [A65536].End(3))
For i = 1 To UBound(Crr)
   T = Crr(i, 1)
   Crr(i, 1) = ""
   For x = 1 To UBound(Brr)
      T1 = Trim(Brr(x, 1)): If T1 = "" Then GoTo x01
      If InStr(T, T1) Then
         Crr(i, 1) = IIf(Crr(i, 1) = "", T1, Crr(i, 1) & "¡B" & T1)
      End If
x01: Next
Next
[C2].Resize(UBound(Crr), 1) = Crr
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

        ÀR«ä¦Û¦b : ¸Ü¦h¤£¦p¸Ü¤Ö¡A¸Ü¤Ö¤£¦p¸Ü¦n¡C
ªð¦^¦Cªí ¤W¤@¥DÃD