[µ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

GBKEE

¦^´_ 1# PKKO
¸Õ¸Õ¬Ý

1. Option Explicit
   
2. Sub TEST_2()
   
3. Dim xRow As Integer, Arr(), Brr(), Ar()
   
4. xRow = 10
   
5. Arr = [A1].Resize(xRow).Value
   
6. Brr = [C1].Resize(xRow).Value
   
7. ReDim Ar(1 To UBound(Arr), 1 To UBound(Arr, 2)) '³]¸m¤@­Ó»PArr¦P¼Ë¤j¤p°}¦C(ªÅªº)
   
8. ReDim AA(1 To 10, 1 To 1)
   
9. For i = 1 To UBound(Arr)
   
10.     For J = 1 To UBound(Brr)
    
11.         If Brr(J, 1) Like "*" & Arr(i, 1) & "*" Then
    
12.         'Like ¹Bºâ¤l¥Î¨Ó¤ñ¸û¨â­Ó¦r¦ê¦³¬Û¦Pªº¦r
    
13.         'If Brr(J, 1) = Arr(i, 1) Then   '¨âªÌ¬Û¦P
    
14.             Ar(i, 1) = Ar(i, 1) + 1
    
15.         End If
    
16.     Next
    
17. Next
    
18. [B1].Resize(xRow) = Ar
    
19. End Sub

½Æ»s¥N½X

ikboy

¦^´_ 1# PKKO


Sub TEST_1()

[B:B].Clear: [J1] = ""
xRow = 10
arr = [A1].Resize(xRow)
Brr = [C1].Resize(xRow)
Set xd = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(Brr)
    xd(Brr(i, 1)) = 0
Next
k = xd.keys
t = xd.items
For i = 1 To UBound(arr)
    For j = 0 To UBound(k)
        If InStr(k(j), arr(i, 1)) Then t(i - 1) = t(i - 1) + 1
    Next
Next
[B1].Resize(xRow) = Application.Transpose(t)
End Sub

PKKO

¦^´_ 2# GBKEE


    ·PÁ¶Wª©¤j¤jªº¦^ÂÐ,¦ý±zªº¤è¦¡¦ü¥G»P§ÚªºTEST_2¼Ò²Õµ{¦¡½X¬Û¦P,§Ú·Q­n¾Ç²ßªº³¡¤À¬O³z¹L°}¦C+¦r¨åª«¥óªº¤è¦¡

­ì¦]¬O³t«×¤W¦ü¥G¤ñ¸û§Ö

PKKO

¦^´_  PKKO


Sub TEST_1()

.Clear: [J1] = ""
xRow = 10
arr = [A1].Resize(xRow)
Brr = [C1].R ...
ikboy µoªí©ó 2015-9-16 09:42

·PÁÂikboy¤j¤jªº¦^ÂÐ,±zªºINSTR¤è¦¡¤p§Ì¾Ç²ß¤F

¦ý·Q½Ð±Ð¤@¤U

±zªº¤è¦¡Áö¦³¥Î¨ì¦r¨å+°}¦C

¦ý±z¸ò§Úªº¤@¼Ë³£¬O¶]¤FÂù­«°j°é,°²³]2¸U*2¸U´N·|¤Ó¤[¤F

§Ú¹ê»Ú±N±zªºµ{¦¡½X»P§Úªº´ú¸Õ,¶]¤@¸U¦¸°j°éµo²{

±zªºµ{¦¡½X³t«×»P§Úªº¬Û®t¤£¨ì10 %

·Q½Ð°Ý¦³¶]³æ°j°éªº¤è¦¡(Ãþ¦ü§ÚªºTEST_1ªºµ{¦¡½X)

¥i¥H¥[§Ö¤ñ¹ï³t«×¶Ü?

ikboy

¦^´_ 5# PKKO


    ³æ°j¤èªk¥Î¦b²Ö¥[§a¡A¦ý±z¥Øªº¬O sumif, count, °µÂù°j¹³¬O¥²»Ý¡A­n¦A§Öªº¸Ü­n½Ð¨ä¥L¤j¤j«ü±Ð¤F¡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

¡@

ikboy

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


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

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

¦^´_ 9# stillfish00


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