ªð¦^¦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)

¦^´_ 1# qaqa3296


    ÁÂÁ«e½úµoªí¦¹¥DÃD»P½d¨Ò
¤£½×¬O§_²Å¦X»Ý¨D! «á¾Ç¦b¦¹©«¾Ç¨ì«Ü¦hª¾ÃÑ!
«á¾Çªº°}¦C»P¦r¨å½m²ß¤ß±oµù¸Ñ¦p¤U:
Option Explicit
Sub TEST_1()
Dim Brr, Arr, c&, R&, V, Y, Z
Dim K$, P$, Q, S
'¡ô«Å§iÅܼÆ
S = Timer
Sheets(3).[M2:P60000].ClearContents
Set Y = CreateObject("Scripting.Dictionary")
Set Z = CreateObject("Scripting.Dictionary")
Set V = CreateObject("Scripting.Dictionary")
'¡ô¥OY,Z,V¦U¬O¦r¨å
Arr = Sheets(1).Range("A1:C" & Sheets(1).[A65536].End(3).Row)
'¡ô¥Ø¼Ðªí °}¦C½d³ò
For R = 1 To UBound(Arr)
'¡ô¥~¶¶°j°é§â ¥Ø¼Ðªí ³W®æ©î¸Ñ,­«²Õ¬°¼Ò½k¤ñ¹ïÃöÁä¦r¨Ã­Ë¤JV¦r¨å
   For c = 1 To UBound(Arr, 2)
   '¡ô¤º¶¶°j°é¥h°£ªÅ¥Õ¦r¤¸
      Arr(R, c) = Replace(Arr(R, c), " ", "")
   Next
   P = Arr(R, 3)
   If P Like "*-*-*" Then
      P = Split(P, "-")(0) & "-" & Split(P, "-")(1)
      ElseIf P = "" Then
      '¡ô¦pªG³W®æÄæ¬OªÅ®æ ´N¥HAÄæ®æ»PBÄæ®æ²Õ¬°¼Ò½k¤ñ¹ïÃöÁä¦r
         P = Arr(R, 1) & Arr(R, 2)
   End If
   V(P) = 1
   '¡ô­Ë¤JV¦r¨å
   P = ""
Next
Brr = Sheets(2).Range("D1:A" & Sheets(2).[A65536].End(3).Row)
'¡ô®w¦sªí °}¦C½d³ò
For R = 1 To UBound(Brr)
'¡ô¥~¶¶°j°é§â ®w¦sªí ³W®æ©î¸Ñ,­«²Õ¦A¥[¤J²Å¸¹ "|" »P¦C¼Æ
',¬°¼Ò½k¤ñ¹ïÃöÁä¦r¨Ã­Ë¤JZ¦r¨å

   For c = 1 To UBound(Brr, 2)
   '¡ô¤º¶¶°j°é¥h°£ªÅ¥Õ¦r¤¸
      Brr(R, c) = Replace(Brr(R, c), " ", "")
      P = P & Brr(R, c) & "|"
     '¡ô§â¨C¦C4Ä檺¸ê®Æ¥Î "|" ¦ê°_¨Ó
   Next
   K = Brr(R, 3)
   If K Like "*-*-*" Then
      K = Split(K, "-")(0) & "-" & Split(K, "-")(1)
      ElseIf K = "" Then
         K = Brr(R, 1) & Brr(R, 2)
   End If
   Z(K & "|" & R) = P  '@@
   '¡ô­«²Õ¦A¥[¤J²Å¸¹ "|" »P¦C¼Æ
   P = ""
Next
For Each Q In Z.KEYS
   If V(Split(Q, "|")(0)) = 1 Then
   '¡ô¥Î "|" ©î¸ÑZ¦r¨å¸Ìªºkey,¦r¦ê¦bV¦r¨å§ä¨ì,¥Nªí²Å¦X´£¨ú±ø¥ó
      Y(Q) = Split(Z(Q), "|")
      '¡ô¥ÎY¦r¨å¸Ë ²Å¦X±ø¥ó ªºZ¦r¨åitem¸ê®Æ  @@³B
   End If
Next
Arr = Application.Transpose(Application.Transpose(Y.items))
'¡ô±NY¦r¨åªº items Âà¸m¨â¦¸ ´N¬Oµ²ªG¸ê®Æ
Sheets(3).[M1].Resize(Y.Count, 4) = Arr
MsgBox Timer - S & "’"
End Sub

TOP

¦^´_ 63# °a¤ªºµ

´ú¸Õ¤@¤U
¬d¸ß33­Ó¸ê®Æ¡A¸ê®Æ®w¼Æ7000

°}¦C½m²ß¥[Function½m²ß_1=>11¬í
°}¦C¥[Function¥[¦r¨å½m²ß=>4¬í

¸ê®Æ¦C¥X¬Û¦P

TOP

¥»©«³Ì«á¥Ñ °a¤ªºµ ©ó 2020-9-12 17:39 ½s¿è

¦^´_ 62# ­ã´£³¡ªL
ÁÂÁ·Ǥjªº«ü¾É  ¦ý³o¤è¦¡  ¥u¬O¦b¦r¨å¸Ì§R°£­«½Æ¦Ó¤w  ¤£¹LÀ³¸Ó·|¤ñ¸û§Ö¤@ÂIÂI  ¦r¨å§PÂ_­«½Æ°}¦C¤£´£¨úªº¤è¦¡ÁÙ¦b§V¤O...
  1. Public Sub °}¦C¥[Function¥[¦r¨å½m²ß()
  2. Application.ScreenUpdating = False
  3. If [¦¨ªG!A1] <> "" Then [¦¨ªG!A1].CurrentRegion.Clear
  4. Crr = [¥Ø¼Ð!A1].CurrentRegion
  5. Brr = [®w¦s!A1].CurrentRegion
  6. ReDim Drr(1 To UBound(Brr, 1), 1 To UBound(Brr, 2))
  7. Set xD = CreateObject("Scripting.Dictionary")
  8.     For i = 1 To UBound(Crr)
  9.         A3 = ¤À³Î¤å¦r(Trim(Crr(i, 3)))
  10.         A1 = Trim(Crr(i, 1))
  11.         For N = 1 To UBound(Brr)
  12.             B3 = ¤À³Î¤å¦r(Trim(Brr(N, 3)))
  13.             B1 = Trim(Brr(N, 1))
  14.             If A1 Like B1 Or A3 Like B3 And A3 <> "" Then
  15.                xD(Brr(N, 1)) = Brr(N, 1)
  16.             End If
  17.         Next N
  18.     Next i
  19.     For E = 1 To UBound(Brr)
  20.         If Brr(E, 1) = xD(Brr(E, 1)) Then
  21.             G = G + 1
  22.             For F = 1 To UBound(Brr, 2)
  23.                 Drr(G, F) = Brr(E, F)
  24.             Next F
  25.         End If
  26.     Next E
  27. Erase Brr, Crr
  28. [¦¨ªG!A1].Resize(G, UBound(Drr, 2)) = ""
  29. [¦¨ªG!A1].Resize(G, UBound(Drr, 2)) = Drr
  30. Erase Drr
  31. Sheets(3).Activate
  32. Cells(1, 1).Select
  33. Application.ScreenUpdating = False
  34. End Sub
  35. '====================================================================
  36. Public Function ¤À³Î¤å¦r(A3)
  37.     Drx = Array("-", ".")
  38.     A7 = "": A8 = ""
  39.     For A9 = LBound(Drx) To UBound(Drx)
  40.         For A0 = 1 To Len(A3)
  41.             If InStr(Mid(Right(A3, A0), 1, 1), Drx(A9)) Then
  42.                 A8 = Mid(Right(A3, A0), 1, A0)
  43.                 A7 = Mid(A3, 1, Len(A3) - Len(A8))
  44.             Exit For
  45.             End If
  46.         Next A0
  47.     Next A9
  48.     If A7 = "" Then A7 = A3
  49.     If A7 = "" Then Exit Function
  50.     If Left(A7, 4) Like "####" Then
  51.        X = Mid(A7, 1, 4)
  52.     ElseIf Left(A7, 5) Like "####[A-Z]" Then
  53.        X = Mid(A7, 1, 5)
  54.     ElseIf Left(A7, 5) Like "[A-Z]####" Then
  55.        X = Mid(A7, 1, 5)
  56.     ElseIf Left(A7, 8) Like "???-????" Then
  57.        X = Mid(A7, 1, 8)
  58.     End If
  59.     If X = "" Then X = A3
  60.     ¤À³Î¤å¦r = X
  61. End Function
½Æ»s¥N½X

TOP

¦^´_ 61# °a¤ªºµ


½×¾Â¦³«Ü¦h¨Ò¤l¥i°Ñ¦Ò~~
©Î¨ì³o¦h¾Ç²ß, ¦³²{¦¨¸û·sªº©«¤l·í°Ñ¦Ò:
http://club.excelhome.net/forum-2-1.html

TOP

¥»©«³Ì«á¥Ñ °a¤ªºµ ©ó 2020-9-11 10:58 ½s¿è

¦^´_ 60# ­ã´£³¡ªL
½Ð°Ý ·Ç´£¤j¤j ¯à¤£¯à¼g¦¨µ{¦¡ ³o¼Ë·|¸û¦n²z¸Ñ ¦ì¸mªº¹B§@¤è¦¡  ¦pªG¥i¥Hªº¸Ü ^^"  ¦]¬° ©ñ¨ì¦r¨å¸Ì ªº¸ê®Æ¦ì¸m  
¤£¤Ó©ú¥Õ¬O¦p¦ó«ü©w³æ¤@¸ê®Æ¨ú¥X¨Ó

TOP

¦^´_ 59# °a¤ªºµ


dic--keys, items
keys--¥u·|¯d¿W¤@µL¤Gªº"¯Á¤Þ­È", ©Ò¥H¤£·|­«ÂÐ
items--¥i¥H®e¯Ç¥ô¦ó«¬ºA¤º®e, ÅܤƸû¦h, ¦ý¥i±q²³æªºµÛ¤â

¨ä¹ê«Ü¦n²z¸Ñ, ¦h´X¦¸½m²ß§Y¥i,
³æÄæ¸ê®Æ:
1) ¨ú±oaÄ椺®eªº°ß¤@­È
2) ­pºâaÄæ¦U°ß¤@­Èªº¥X²{¦¸¼Æ
¨âÄæ¸ê®Æ:
1) ­pºâaÄæ¦U°ß¤@­È¦bbÄ檺¦X­p¼Æ
2) ­pºâaÄæ¦U°ß¤@­È,¥BbÄæ²Å¦X¬Y¤@±ø¥óªº¦¸¼Æ

TOP

¥»©«³Ì«á¥Ñ °a¤ªºµ ©ó 2020-9-9 17:42 ½s¿è

¦^´_ 58# qaqa3296
ÁÂÁ§A À°§Ú´ú¸Õ  ¨ä¹ê ¨ºÁÙ¬O¦³°ÝÃD  ´N¬O·|­«½Æ§ì ²Ä3Ädªº¸ê®Æ
¦]¬° ¦³®É­Ô²Ä3Äd ¬OªÅ¥Õªº ¦³®É­Ô¤£¬O ©Ò¥H·Q¤F³Ì²Âªº¤è¦¡ ´N¬O§R°£­«½Æ ¦ý³o¤£¬O¤@­Ó¦n¤è¦¡
¤£ª¾¹D¸Ó¦p¦ó§PÂ_.. ¤£¹LÁÙ¬OÁÂÁ§AÄ@·Nªá®É¶¡À°§Ú´ú¸Õ ÁÙ¦³·Ç¤j­Ìªº³W«h ¥i¥H¸Ñ¨M³o­Ó½ÆÂøªº®æ¦¡°ÝÃD ..
¨ä¹ê¦r¨åªº¨ä¤¤¤@­Ó¯SÂI ¬O¥i¥H§R°£­«½Æ  ¦ý§Ú¤£·|¥Î ¦]¬°¨º¤£¬O¨º»òª½Æ[ ¤£¤Ó¦n²z¸Ñ
·Ç¤jªº¦r¨å¹B¥Î§Ú¤]¬Ý¤£©ú¥Õ ¥u¬O ²q´ú À³¸Ó¬O §â 1,3 Ä檺¸ê®Æ ©ñ¨ì¦r¨å¸Ì §Q¥ÎÅܼƸò²Å¸¹¬ö¿ý¦r¨å¦ì¸m
¦b§ì¥X¨Ó¤ñ¹ï ¦ý¬O ¦p¦ó§PÂ_§Ú´N¤£¤Ó©ú¥Õ¤F...

TOP

¦^´_ 57# °a¤ªºµ

§A§ï¥Î°}¦C¼g¤F

¸ê®Æ§e²{²Å¦X³W«h¡A¥i¥H±µ¨ü

¨Sµo²{¤°»ò°ÝÃD

TOP

¦^´_ 55# qaqa3296
­è¤~µo²{ §PÂ_¦³¿ù»~ §ï¤F¤@¤U ...¦³ªÅÀ°§Ú´ú¸Õ¤@¤U ¬Ý¬ÝÁÙ¦³¨S¦³¿ù»~ªº¦a¤è  ÁÂÁ§A

javascript:;

µ²ªG0907_8.rar (46.66 KB)

TOP

        ÀR«ä¦Û¦b : ºÉ¦h¤Ö¥»¥÷¡A´N±o¦h¤Ö¥»¨Æ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD