[µo°Ý] ¦p¦ó¨Ï¥Î°}¦C+¦r¨åª«¥ó¤ñ¹ï¸ê®Æªº®É­Ô¥ÎLIKE»yªk?

PKKO

¤£¦n·N«ä,³Â·Ð¦U¦ì¤j¤j¤F,§Úı±oTEST_1ªºµ{¦¡½X°õ¦æ³t«×¸û§Ö,¦ý§Ú¥u·|¥ÎTEST_2ªº¤è¦¡,¦³¤j¤j¥i¥H±Ð¾Ç¤@¤U¶Ü?

1. Sub TEST_1()
   
2. '³o¬O­ì¥»ªºµ{¦¡½X
   
3. [B:B].Clear: [J1] = ""
   
4. xRow = 10
   
5. Arr = [A1].Resize(xRow)
   
6. Brr = [C1].Resize(xRow)
   
7. Set xD = CreateObject("Scripting.Dictionary")
   
8. For i = 1 To UBound(Brr)
   
9.     xD(Brr(i, 1)) = xD(Brr(i, 1)) + 1
   
10. Next
    
11. For i = 1 To UBound(Arr)
    
12.     Arr(i, 1) = xD(Arr(i, 1))
    
13. Next
    
14. [B1].Resize(xRow) = Arr
    
15. End Sub
    
16. 
    
17. 
    
18. Sub TEST_2()
    
19. '§Ú·Q±NTEST_1¼Ò²Õªºµ{¦¡½X§ï¦¨¥ÎLIKEªº¤è¦¡,¤£¾å±o¸Ó«ç»ò°µ???
    
20. 
    
21. 
    
22. '§Ú¥u·|¤U¦C³oºØ¤è¦¡
    
23. xRow = 10
    
24. Arr = [A1].Resize(xRow)
    
25. Brr = [C1].Resize(xRow)
    
26. Dim AA
    
27. ReDim AA(1 To 10, 1 To 1)
    
28. For i = 1 To UBound(Arr)
    
29.     M = 0
    
30.     For J = 1 To UBound(Brr)
    
31.         If Brr(J, 1) Like "*" & Arr(i, 1) & "*" Then
    
32.             M = M + 1
    
33.             AA(i, 1) = M
    
34.         End If
    
35.     Next
    
36. Next
    
37. [B1].Resize(xRow) = AA
    
38. End Sub

½Æ»s¥N½X

Andy2483

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


    ÁÂÁ½׾Â,ÁÂÁ«e½ú
«á¾ÇÂǦ¹©«¾Ç²ß«e½úªº¤è®×,«á¾Ç¾Ç²ß¤ß±oµù¸Ñ¦p¤U,½Ð«e½ú¦A«ü¾É

°õ¦æµ²ªG:

¤U¸ü (67.59 KB)

2023-5-9 14:04

Public Const R = 1000
Sub ¨ú¼Æ3()
Dim i&, j&, Arr, Brr, Drr, T, SS, xD, xD1, TT$, M%, N%, Y$
'¡ô«Å§iÅܼÆ
T = Timer
[B:B].ClearContents: [H3:H4] = ""
'¡ô²M°£µ²ªGÄæ¸ê®Æ
Arr = [A1].Resize(R):  Brr = [B1].Resize(R):  Drr = [D1].Resize(R)
'¡ô¥O(Arr,Brr,Drr)¦U¬O¤Gºû°}¦C±a¤JÀx¦s®æ­È
Set xD = CreateObject("Scripting.Dictionary")
Set xD1 = CreateObject("Scripting.Dictionary")
'¡ô¥O(xD,xD1)¦U¬O¦r¨å
For j = 1 To UBound(Drr)
'¡ô³]¶¶°j°é
    TT = Drr(j, 1)
    '¡ô¥OTTÅܼƬO °j°éDrr°}¦C­È
    M = Len(TT)
    '¡ô¥OMÅܼƬO TTÅܼƪº¦r¼Æ
    N = 1
    '¡ô¥ONÅܼƬO 1
    xD1.RemoveAll
    '¡ô¥O²MªÅ xD1¦r¨å
    Do
    '¡ô³]±ø¥ó°j°é
      For i = 1 To M
      '¡ô³]¶¶°j°é
          Y = Mid(TT, N, i)
          '¡ô¥OY¬O TTÅܼƱq ²Ä1¦r¶}©l¨ú¥k°¼ªº¦r
          If xD1(Y) = "" Then xD(Y) = xD(Y) + 1
          '¡ô¦pªGxD1¦r¨å¸Ì¨S¦³YÅܼƳokey,´N¥OxD¦r¨å¯Ç¤J,item²Ö¥[1
          xD1(Y) = 1
          '¡ô¥OYÅܼƯǤJxD1¦r¨å¸Ì,item¬O 1,ÅýxD1¦r¨å¦b¤U¦¸²MªÅ«eÂo­«½Æ
      Next i
      N = N + 1: M = M - 1
      '¡ôN.MÅܼưµÅܤÆ,¥H¥þ³¡¤å¦r¦ê²Õ¦X³v¦¸Â^¨ú
    Loop Until M = 0
    '¡ô°õ¦æ¨ì ³Ì«á¤@­Ó¦r¤¸
Next j
For i = 1 To UBound(Arr)
'¡ô³]¶¶°j°é
    Y = Arr(i, 1): j = xD(Y)
    '¡ô¥HArr°}¦C­È¬dxD¦r¨å¦^¶Çitem­È(¸Ókey¥X²{¹L´X¦¸)
    Brr(i, 1) = j
    '¡ô¼g¤JBrr°}¦C
    SS = SS + j
    '¡ô²Ö­p¦¸¼Æ
Next i
[B1].Resize(R) = Brr:  [H3] = Timer - T: [H4] = SS
End Sub

PKKO

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


    ­ì¨Ó¦p¦¹,·PÁ¤j¤j!

±z´£¨Ñªºµ{¦¡½X,§Ú³£¦³¥Î¨ì®@!!

¦ý¼¶¼gªº®É­ÔÁ`¬O­n«ä¦Ò¤@¤U,ÁÙµLªk«Ü¶¶ºZ,©Ò¥H³£·|¦^¨Ó°½¬Ý¤j¤j±z¼gªºµ{¦¡½X^_^

­ã´£³¡ªL

¦^´_ 11# PKKO


Filter»P¤@¯ë¨ç¼Æ¬Û¦P¡A¹J¤j°j°éÁÙ¬O­n¯Ó¨Ç®É¶¡¡G
Sub Test_Filter()
Dim i&, Arr(3000), Brr, T
For i = 0 To 3000:  Arr(i) = i + 1: Next i
T = Timer
¡@
For i = 1 To 5000:  Brr = Filter(Arr, i): Next i
MsgBox Timer - T
End Sub
¡@
¦ý¤ñ°_Âù°j°é¤w§Ö«Ü¦h¡A¬O¤£¿ùªº¤èªk¡ã¡ã
¡@
­Y­n¤ñ¹ï¤è¦¡¬°¡G
"A12*"¡@¡@"B315*"¡@¡e¶}ÀY¬Û¦P¡f¡@©Î¡@"*256"¡@"*21"¡@¡eµ²§À¬Û¦P¡f¡AÁÙ¬O¥Î Like¡A
Instr«h¥i§PÂ_ÃöÁä¦r¦b¤å¦r¦ê¤¤ªº¦ì¸m¡A¤]¬O±`¥Î»yªk¡ã¡ã

µ{¦¡¨Ì¸ê®Æ¦h¹è¡D«¬ºA¤Îµ²ºc¡A¨Ï¥Î¾A©y§Y¥i¡AµL¦nÃa¤§¤À¡ã¡ã
¡@

PKKO

¦^´_ 9# stillfish00


    ·PÁÂstillfish00¤j¤j,¤@¸ô¨«¨Ó«ü¾É³\¦hµ{¦¡½X

³o¦¸±z¤SÀ°¤F§Ú¤j¦£¤F,¤@ª½¦b«ä¦Ò«ç¼Ë¥i¥HÅýµ{¦¡¶]±o§ó§Ö

±zªºµ{¦¡½X³t«×§Ö¥B¼¶¼g§Ö³t,·P®¦®@~

PKKO

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


    ·PÁ­㴣³¡ªL¤j¤jªº¦^ÂÐ,Åý§Ú¾Ç²ß¤F±zªº«ä¦ÒÅÞ¿è¤è¦¡,¥H¤Î¤£¦Pªºµ{¦¡¼¶¼g¤è¦¡!

ikboy

¦^´_ 9# stillfish00


    stillfish00¤j¤j, ³o©Û°ª

stillfish00

¦^´_ 1# PKKO
¤£¥Î Like , ¥Î Filter

1. Sub Test()
   
2.     Dim Arr, Brr, arFilter
   
3.     Dim dArr As Object
   
4.     Dim xRow : xRow = 10
   
5.    
   
6.     Arr = [A1].Resize(xRow)
   
7.     Brr = Application.Transpose([C1].Resize(xRow).Value)
   
8.    
   
9.     Set dArr = CreateObject("Scripting.Dictionary")
   
10.    
    
11.     For Each x In Arr
    
12.         If Not dArr.exists(x) Then
    
13.             arFilter = Filter(Brr, x)   'lbound index = 0
    
14.             dArr.Add x, UBound(arFilter) - LBound(arFilter) + 1 'count
    
15.         End If
    
16.     Next
    
17.     For i = 1 To UBound(Arr)
    
18.         Arr(i, 1) = dArr(Arr(i, 1))
    
19.     Next
    
20.     [B1].Resize(UBound(Arr)) = Arr
    
21. End Sub

½Æ»s¥N½X

ikboy

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


    ¦hÁ­ã¤j, ³oÅý§Ú¦^¬Ý¨ì§Úªºµ{§Ç¤¤¦³º|¬},§Ú³Q¹w³]µª®×»¤¥h½Õ¥Î¤F¦r¨å,¦ý·íC¦C¦³­«ÂЮɫK·|ºâº|,ÁÙ¬OGBKEEª©¤j©MPKKO¥ý¶iªº¤èªk·Ç½T¡C

­ã´£³¡ªL

¡e¼Ò½k¤ñ¹ï¡f¡A¦]¦³§PÂ_¦¡¡A¤j·§¤@¯ë³£¨Ï¥Î¡eÂù¼h°j°é¡f¡A20000*20000=400000000¡A§Ö¤£¤F¡I
­n¤£¡A´N­n¥ý±N³Q¤ñ¹ï¤å¦r¡e©î¤À¡f¦A¯Ç¤J¡e¦r¨åÀÉ¡f¡A¨Ò¦p¡G1234 ¥ý©î¦¨ 1,2,3,4,12,23,34,123,234,1234¡A
¥H¢·­Ó¦r¤¸¨Ó»¡¡A¨C­Ó½s¸¹­n©î¦¨¢²¢µ­Ó¡G=(¦r¤¸¼Æ+1)/2*¦r¤¸¼Æ¡A
¬G²Ä¤@­Ó°j°é¬°¡G20000*36=720000¡A¦A¥[²Ä¤G°j°é20000¡A¦@¶] 740000¡A
¦ý³o¤S²æÂ÷¥¿³W¸Ñªk¡A°Ñ¦Ò§Y¥i¡G¡]ªþÀɦ³¤TºØ¼Ò¦¡¡A¥i¤ñ¸û¬Ý¬Ý¡^
¡@
µ{¦¡½X¡G

1. Sub ¨ú¼Æ3()
   
2. Dim i&, j&, Arr, Brr, Drr, T, SS, xD, xD1, TT$, M%, N%, Y$
   
3. T = Timer
   
4. [B:B].ClearContents: [H3:H4] = ""
   
5. Arr = [A1].Resize(R):  Brr = [B1].Resize(R):  Drr = [D1].Resize(R)
   
6. Set xD = CreateObject("Scripting.Dictionary")
   
7. Set xD1 = CreateObject("Scripting.Dictionary")
   
8. For j = 1 To UBound(Drr)
   
9. ¡@¡@TT = Drr(j, 1)
   
10. ¡@¡@M = Len(TT)
    
11. ¡@¡@N = 1
    
12. ¡@¡@xD1.RemoveAll
    
13. ¡@¡@Do
    
14. ¡@¡@¡@For i = 1 To M
    
15. ¡@¡@¡@¡@¡@Y = Mid(TT, N, i)
    
16. ¡@¡@¡@¡@¡@If xD1(Y) = "" Then xD(Y) = xD(Y) + 1
    
17. ¡@¡@¡@¡@¡@xD1(Y) = 1
    
18. ¡@¡@¡@Next i
    
19. ¡@¡@¡@N = N + 1: M = M - 1
    
20. ¡@¡@Loop Until M = 0
    
21. Next j
    
22. ¡@
    
23. For i = 1 To UBound(Arr)
    
24. ¡@¡@Y = Arr(i, 1): j = xD(Y)
    
25. ¡@¡@Brr(i, 1) = j
    
26. ¡@¡@SS = SS + j
    
27. Next i
    
28. ¡@
    
29. [B1].Resize(R) = Brr:  [H3] = Timer - T: [H4] = SS
    
30. End Sub

½Æ»s¥N½X

¡@
ªþÀɤU¸ü¡G
¼Ò½k¤ñ¹ï¨ú¼Æv01.rar (151.53 KB)

¤U¸ü¦¸¼Æ: 33

2015-9-16 22:59

¡@