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

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

¥»©«³Ì«á¥Ñ n7822123 ©ó 2020-8-21 22:54 ½s¿è

¦^´_ 15# qaqa3296


§Aªº"¼Ò½k¤ñ¹ï" ³W«h¯u½ÆÂø¡A6~8½X½ü¬y¥Î?

¤°»ò®É­Ô¥Î6½X¼Ò½k¤ñ¹ï¡A¤°»ò®É­Ô¥Î8½X¼Ò½k¤ñ¹ï? ¦³¨S¦³7½X¤ñ¹ï?

¼gµ{¦¡Á¿¨s "ÅÞ¿è"¡A§AÅÞ¿èµ¹ªº¤£²M¤£·¡¡A§O¤H¥u¯à½M¤lºN¶H!

¦p­ã¤j©Ò»¡¡A§AÀ³¸Ó§â"©Ò¦³" ³W®æªº®æ¦¡³q³q¦C¥X¨Ó¡A¨Ã¥ÎÃC¦â°Ï¤À¡A­n¥H­þ¨Ç¦r¤¸¨Ó"¼Ò½k¤ñ¹ï"

¤£µM¥H§Aªº"¤å¦r±Ô­z" ÅÞ¿è¡A·|¨S§¹¨S¤F ! ¤U­±³o¥y¸Ü¡A»¡¹ê¸Ü¡A§Ú´N¯uªº¬Ý¤£À´!!!

¤T­Ó¦r¸`"-"¥|­Ó¦r¸`°µ¬°±ø¥ó®ÄªG¡AÀ³¸Ó¥i¥H±o¨ì§ó²z·Qªº¸ê®Æ

Á|¨Ò¨Ó»¡¡A¤U­±¬õ¦â³¡¤À¬O­n¼Ò½k¤ñ¹ïªº³¡¤À¡A¬õ¦â³¡¤À¤@¼Ë«hµø¬°¤@¼Ë

§ÚÀH«K¦C2ºØ¡A½Ð§A§â"©Ò¦³"®æ¦¡¦C¥X¡A¦p¤U°Ñ¦Ò

A02-A001-E

99-A001-A

µ{¦¡¬O¨Ì»Ý¨D¼gªº¡A»Ý¨Dªí¹F¤£²M·¡
©ÎªÌ¨S¦³¤W¶Çªþ¥ó¡A·R²ö¯à§U

TOP

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

¦^´_n7822123
¦pjcchiang©Ò»¡¬O¨â°¼¦r¦êªÅ®æ

·PÁÂjcchiang¸É¥R·sªº»yªk

§Ú¨ú±o¸ê®Æ®É¡A³W®æÄæ³Q·í§@³Æµù¡A¥´¤F¤@°ï¤º®e¡A§R±¼¨º¨Ç°T®§¡A«h¥X²{¨â°¼¦r¦êªÅ®æ³Ì¬°±`¨£¡A­ì¥»·Q»¡¬O¤£¬O­n¥ý¥ÎVLOOKUP¥h§ó·s³W®æªº¤º®e¡AÅý¸ê®Æ¤£·|¦]¤H¤u¥´±o¿ù»~¦h¤FªÅ¥Õµ¥µ¥..

¬Ý¨Ó¤]­n§ó·s¤@¤U«ä¦Ò¼Ò¦¡¤F


¦^´_­ã´£³¡ªL
§Ú¤@¶}©l¨S¦³ª`·N¨ìÁÙ¦³¨ä¥L³W®æ¡A¦p­ã´£³¡ªL²q´úªº¤@¼Ë

­è­è¥ÎLEN¨ç¼ÆÀˬd¤@¤U

¤T­Ó¦r¸`"-"¥|­Ó¦r¸`°µ¬°±ø¥ó®ÄªG¡AÀ³¸Ó¥i¥H±o¨ì§ó²z·Qªº¸ê®Æ ¡A¦A¨Ó¤~¬O¨ú8½X¡A8½X¥H¤Uªº±N·|¦C¥X¤j¶q¦h¾l¸ê®Æ¡A©Ò¥H¤£»Ý¦A«Ø¥ß·sªº³W«h¡A´N§ïµ{¦¡6~8½X½ü¬y¥Î§Y¥i

·PÁ¦U¦ì¤j¤jªºÀ°¦£¡A¦³¾Ç¨ì·sªº¨Æª«¡Aı±o¶}¤ß

TOP

¥»©«³Ì«á¥Ñ °a¤ªºµ ©ó 2020-8-21 18:16 ½s¿è

¦^´_ 9# qaqa3296
§Úªº¼gªk¤ñ¸û²³æ ¦ý¬O·|¤ñ¸ûºC §A¬Ý¬Ý¬O¤£¬O§A­nªº  ¦pªG¬O¤å¦r §A´N¥´¤å¦r ¦C¦p ·Ù¨® ¥Î²Ä¤G­Ó ¦pªG¬O ­ì¥»ªº ªÅ®æ §A´N¥Î²Ä¤@­Ó

javascript:;

¦C¥X§ó¦h¸ê®Æ.rar (20.27 KB)

TOP

¦^´_ 9# qaqa3296


¤£¤F¸Ñ¤Ö¸ê®Æ¬O¦ó·N???
®Ú¾Ú´y­z, ±N³W®æ 231-215-??? ²Ä¤T¦r¸`¥h°£, °µ¬°¥t¤@¤ñ¹ï±ø¥ó,
¤å¦r«e«á­Y§t¦³ªÅ¥Õ¦r¤¸, ®M¤@­ÓTRIM(ARR(i,3)) §Y¥i~~
100-1001.3.V1 ³o­Ó¬O§_ÁÙ­n¨ú¥X 100-1001 °µ¬°±ø¥ó,
¦h¦C¥X´X­Ó¤£¦P«¬¦¡ªº³W®æ¤å¦r, ¤Î¨äºI¨ú³W«h~~~

©ÎªÌ, ³W®æªº«e8½X¬°©T©w¦@¥Î, ¤§«áªº¬°ÅܤƧÎ, ³£µø¬°¬Û¦P???

TOP

¦^´_ 9# qaqa3296
  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 = "[-\.\s]+"
  7.     .Global = True
  8.     For i = 1 To UBound(a)
  9.         If Len(a(i, 3)) = 0 Then a(i, 3) = Trim(a(i, 1))
  10.         a(i, 3) = Trim(a(i, 3))
  11.         k = Split(.Replace(a(i, 3), "|"), "|")
  12.         If UBound(k) > 0 Then k = k(0) & "-" & k(1) Else k = a(i, 3)
  13.         d(k) = ""
  14.     Next
  15.     k = Join(d.keys, "|")
  16.     .Pattern = k
  17.     a = Sheets(2).[a1].CurrentRegion
  18.     b = a: n = 1
  19.     For i = 2 To UBound(a)
  20.         If Len(a(i, 3)) > 0 Then k = a(i, 3) Else k = a(i, 1)
  21.         If .TEST(k) Then
  22.              n = n + 1
  23.              For j = 1 To UBound(a, 2)
  24.                 b(n, j) = a(i, j)
  25.              Next
  26.         End If
  27.     Next
  28.     Workbooks.Add 1
  29.     [a1].Resize(n, 4) = b
  30. End With
  31. End Sub
½Æ»s¥N½X

TOP

¥»©«³Ì«á¥Ñ jcchiang ©ó 2020-8-21 07:59 ½s¿è

¦^´_ 9# qaqa3296

¦pªG¬O¦r¦ê¥ª¥kªºªÅ®æ,¥i¨Ï¥ÎTrim¥h°£
Trim¡G§R°£¦r¦ê¥ª¡B¥k¨âºÝªÅ¥Õ
Set Rg = .Find(Left(Trim(a.Offset(, 2)), 8) & "*", , , xlWhole)

TOP

¦^´_ 9# qaqa3296


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

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

²¤Æ¤£¤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

§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

        ÀR«ä¦Û¦b : ¯àµ½¥Î®É¶¡ªº¤H¡A¥²¯à´x´¤¦Û¤v§V¤Oªº¤è¦V¡C
ªð¦^¦Cªí ¤W¤@¥DÃD