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

[µo°Ý] ¦C¥X§ó¦hªº¹ïÀ³¸ê®Æ

[µo°Ý] ¦C¥X§ó¦hªº¹ïÀ³¸ê®Æ

¥»©«³Ì«á¥Ñ qaqa3296 ©ó 2020-8-18 21:42 ½s¿è

­n±N"¥Ø¼Ð"ªº¸ê®Æ¨Ì§Ç¥h´M§ä"®w¦s"¤ºªº¸ê®Æ(¥H"³W®æ"¶i¦æ¼Ò½k¤ñ¹ï)¨Ã¦b"¦¨ªG"¦C¥X¥þ³¡¹ïÀ³ªº¸ê®Æ

²{¦b¬O¥Î²Â¤èªk¥Î¨ç¼Æ¥h¼g¡Aµ¥«Ý¦C¥X¸ê®Æ«á¦A¶K¨ì¦¨ªG¡C(¤H¤u¥N»ù¤Ó¤j¤F¡A¤]«D±`¦Y¨t²Î¸ê·½?¹q¸£¤ÓºC¤F)
®w¦s¤ºÁÙ¦³¦P³W®æ¤£¦P®Æ¸¹ªºÄê±b¡A®w¦s¸ê®Æ¬ù¦³10000µ§

·Pı¥i¥H¥ÎVBA¼g¡A¦ý¨S¦³ÀYºü¡A·Q°Ý¦U¦ì¹F¤H¦³¨S¦³§ó¦X¾Aªº¤è¦¡

¥Ø«e½s½X­ì«h(­^¤å¦r¥À)**-(­^¤å¦r¥À)***
§ïª©(­^¤å¦r¥À)**-(­^¤å¦r¥À)***-(­^¤å¦r¥À°Ï¤Àª©¥»)


¨ä¥L¨S¦³²Å¦X½s½X­ì«hªº¸ê®Æ§Ú¦Û¤v¤â°Ê§ä¬d¡A§Æ±æ¦U¦ì¹F¤HÀ°¦£¡A´î¤Ö¤u§@®É¶¡
ªþ¤W¤@­Ó½d¨Ò¡A²Â¤èªk¤]¦b¥Ø¼Ð¤º¡A°ª¤â½Ðª½±µ­ç°£§Y¥i¡C
ÁÂÁÂ

¸É¥R»¡©ú¡G¤£­n¥H«~¦W¬°°ò·Ç¬d¸ß¡A­«½Æ»P¦h©ó¸ê®Æ¤Ó¦h¨S¦³°Ñ¦Ò»ù­È
¨Ò¦p¡G¾TÀ½«¬¡A³o¤dÅܸU¤Æ

¦C¥X§ó¦h¸ê®Æ.zip (11.81 KB)

¥»©«³Ì«á¥Ñ n7822123 ©ó 2020-8-19 03:46 ½s¿è

¦^´_ 1# qaqa3296

¹ï©óVBA¨Ó»¡¡A³o¬O piece of cake

«ö¶À¦â¬d¸ß«ö¶s§Y¥i!

µ{¦¡¦p¤U


Sub ¼Ò½k¬d¸ß()
Dim Rg As Range, Addr0$, R1&
[K:N].ClearContents
With [®w¦s!C:C]
    Set Rg = .Find([J2] & "*", , , xlWhole)
    If Not Rg Is Nothing Then Addr0 = Rg.Address
    Do While Not Rg Is Nothing
        R1 = R1 + 1
        Rg.Resize(, 4).Offset(, -2).Copy Cells(R1, "K")
        Set Rg = .FindNext(Rg)
        If Rg.Address = Addr0 Then Exit Do
    Loop
End With
End Sub


Àɮצp¤U

¦C¥X§ó¦h¸ê®Æ.rar (17.99 KB)
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

{=INDEX(®w¦s!A:A,SMALL(IF(ISNUMBER(FIND($J$2,®w¦s!$C$2:$C$45)),ROW($2:$45),99),ROW(A1)))&""
google"EXCEL°g"  blog  ©Îgoogleºô§}:https://hcm19522.blogspot.com/

TOP

´M§ä¥Ø¼Ð¥u¦³¤@­Ó, ¥Î¿z¿ï¦A¶K§Y¥i

TOP

¬Ý¨ì¦U¦ì¤j¤jªº¦^´_¡A¤~µo²{§Ú»¡±o¤£°÷²M·¡¡A¯u¬O©êºp


1.¦p¦ó¹ï³W®æ¥u¨ú­nªºÃöÁä¦r¡A·Q·Q«Ø­Ó»²§U¬d¸ßÄæ"LEFT¨ç¼Æ"¡AÁÙ¬O¦³¿ìªk­ç°£²Ä¤Gªº"-"«á­±ªº¸ê®Æ?(·Q¤£¥X¦³§ó¦nªº¿ìªk¡A¤º®e«ÜÂø¶Ã)
2.»²§UÄ榳¨Ç¬OªÅ¥Õ¦p¦ó¦C¥X»Ý­nªº¸ê®Æ?
3.§å¦¸±N»²§UÄæ¥þ³¡³£¬d¸ß®w¦s¡AµM«á¦C¨ì"¦¨ªG"¡C
4.¹ê»Úª¬ªp¬OµLªk¥Î«~¦W¥h¿z¿ï¸ê®Æªº¡A¤j¬ù500µ§³£¬O¬Û¦P«~¦W¡A¦ý«Ü¦h³£¬O§Ú¤£»Ý­nªº¸ê®Æ


§Æ±æ³Ì«á±o¨ìµ²ªG¡C

·PÁ¦U¦ì¤j¤jÀ°¦£

¦C¥X§ó¦h¸ê®ÆV2.zip (17.94 KB)

TOP

¦^´_ 5# qaqa3296

§âÀs¤jªºµ{¦¡­×§ï¤@¤U
¨Ì»Ý¨Dª½±µ±NLeft©ñ¤Jµ{¦¡¤¤
³W®æªÅ¥Õ¥u¦n¥Î«~¸¹¬d¸ß
°õ¦æµ²ªG»P©Ò»Ý¬Û²Å

    Sub ¼Ò½k¬d¸ß()
Dim Rg As Range, Addr0$, R1&
[K:N].ClearContents
[K1:N1] = Array("«~¸¹", "«~¦W", "³W®æ", "¼Æ¶q")
R1 = 1
With [®w¦s!A:C]
   For Each a In Sheets("¥Ø¼Ð").Range([a2], [a2].End(4))
      If a.Offset(, 2) <> "" Then
         Set Rg = .Find(Left(a.Offset(, 2), 8) & "*", , , xlWhole)
      Else
         Set Rg = .Find(a, , , xlWhole)
      End If
      If Not Rg Is Nothing Then Addr0 = Rg.Address
      Do While Not Rg Is Nothing
         R1 = R1 + 1
         If Rg.Column = 3 Then
           Rg.Resize(, 4).Offset(, -2).Copy Cells(R1, "K")
         Else
           Rg.Resize(, 4).Copy Cells(R1, "K")
         End If
         Set Rg = .FindNext(Rg)
         If Rg.Address = Addr0 Then Exit Do
      Loop
   Next
End With
End Sub

TOP

§R¥h¤½¦¡,¨Ï¥ÎVBA·|§ó§Ö¡G
  1. Sub zz()
  2. Dim a, d As Object, b(), n&
  3. a = Sheets(1).Range("a2:d" & Sheets(1).[a1048576].End(3).Row)
  4. Set d = CreateObject("scripting.dictionary")
  5. With CreateObject("vbscript.regexp")
  6.     .Pattern = "-\w$"
  7.     For i = 1 To UBound(a)
  8.         If Len(a(i, 3)) = 0 Then a(i, 3) = a(i, 1)
  9.         k = a(i, 3)
  10.         a(i, 3) = .Replace(k, "")
  11.         d(a(i, 3)) = ""
  12.     Next
  13.     k = Join(d.keys, "|")
  14.     .Pattern = k
  15.     a = Sheets(2).[a1].CurrentRegion
  16.     ReDim b(1 To UBound(a), 1 To UBound(a, 2))
  17.     For i = 2 To UBound(a)
  18.         If Len(a(i, 3)) > 0 Then k = a(i, 3) Else k = a(i, 1)
  19.         If .test(k) Then
  20.              n = n + 1
  21.              For j = 1 To UBound(a, 2)
  22.                 b(n, j) = a(i, j)
  23.              Next
  24.         End If
  25.     Next
  26.     Sheets(3).[a1].CurrentRegion.Offset(1).Clear
  27.     Sheets(3).[a2].Resize(n, 4) = b
  28. End With
  29. End Sub
½Æ»s¥N½X

zz.zip (18.66 KB)

TOP

²¤Æ¤£¤F,
Sub TEST()
Dim Arr, xD, i&, j%, N&, T$, V%
Set xD = CreateObject("scripting.dictionary")
Arr = Range([¥Ø¼Ð!C1], [¥Ø¼Ð!A65536].End(xlUp))
For i = 2 To UBound(Arr)
    For j = 1 To 3 Step 2
        T = Arr(i, j):  If T <> "" Then xD(T) = 1
        If T Like "*-*-*" Then xD(Left(T, InStrRev(T, "-") - 1)) = 1
    Next j
Next i
Arr = Range([®w¦s!D1], [®w¦s!A65536].End(xlUp))
For i = 2 To UBound(Arr)
    For j = 1 To 3 Step 2
        T = Arr(i, j):  V = V + xD(T)
        If T Like "*-*-*" Then V = V + xD(Left(T, InStrRev(T, "-") - 1))
    Next j
    If V = 0 Then GoTo 101
    N = N + 1: V = 0
    For j = 1 To 4: Arr(N, j) = Arr(i, j): Next
101: Next i
[¦¨ªG!A2:A6000].ClearContents
If N > 0 Then [¦¨ªG!A2:D2].Resize(N) = Arr
End Sub


============================

TOP

¥»©«³Ì«á¥Ñ qaqa3296 ©ó 2020-8-20 23:34 ½s¿è

·PÁÂn7822123¤j¤j¦^´_

³o­Ó°ÝÃD³Ì¤jªº°ÝÃD¬O¨ú¸ê®Æªº¤è¦¡

jcchiang¤j¤jªºµ{¦¡®ÄªG¯u¦n¡A°£¤F¨S¿ìªkÀ³¥I®æ¤l¤º¦³¦h¾lªºªÅ¥Õ

ikboy¤j¤j·|¯Ê¤Ö¸ê®Æ¡A·Q½Ð°Ý§Aªºµ{¦¡«ä¸ô¬O¦p¦ó¹B§@(¨S¿ìªkÀ³¥I®æ¤l¤º¦³¦h¾lªºªÅ¥Õ)
.Pattern = "-\w$"  
k = Join(d.keys, "|")
§@¥Î¬O¤°»ò©O?

­ã´£³¡ªL¤j¤j¤]¦³¯Ê¤Ö¸ê®Æ¡A·Q½Ð°Ý§Aªºµ{¦¡«ä¸ô¬O¦p¦ó¹B§@(¥i¥HÀ³¥I®æ¤l¤º¦³¦h¾lªºªÅ¥Õ)
¬Ý°_¨Ó¬O­ç°£²Ä¤Gªº"-"«á­±ªº¸ê®Æ¡A¦ý¤£¤Ó½T©w¡A¾Ç²ß¤¤

¦^´_°a¤ªºµ¡A³£­n¦C¥X

¦pªG·Q¦b³W®æ¤º(¤£¬OªÅ¥Õ)¡A¨S¦³§ä¨ì¥ô¦ó¸ê®Æ¡A«h±N¦rÅéÅܬõ·í§@´£¿ô¸Ó¦p¦ó­×§ï?

¦C¥X§ó¦h¸ê®ÆV4.zip (25.57 KB)

TOP

¦^´_ 9# qaqa3296


   ¤°»ò¥s°µ "®æ¤l¤º¦³¦h¾lªºªÅ¥Õ"¡A½ÐÁ¿©ú¥Õ¤@ÂI
µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

        ÀR«ä¦Û¦b : «Ý¤H°h¤@¨B¡A·R¤H¼e¤@¤o¡A´N·|¬¡±o«Ü§Ö¼Ö¡C
ªð¦^¦Cªí ¤W¤@¥DÃD