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

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

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

¥»©«³Ì«á¥Ñ PKKO ©ó 2015-9-16 08:20 ½s¿è

¤£¦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. Sub TEST_2()
  17. '§Ú·Q±NTEST_1¼Ò²Õªºµ{¦¡½X§ï¦¨¥ÎLIKEªº¤è¦¡,¤£¾å±o¸Ó«ç»ò°µ???


  18. '§Ú¥u·|¤U¦C³oºØ¤è¦¡
  19. xRow = 10
  20. Arr = [A1].Resize(xRow)
  21. Brr = [C1].Resize(xRow)
  22. Dim AA
  23. ReDim AA(1 To 10, 1 To 1)
  24. For i = 1 To UBound(Arr)
  25.     M = 0
  26.     For J = 1 To UBound(Brr)
  27.         If Brr(J, 1) Like "*" & Arr(i, 1) & "*" Then
  28.             M = M + 1
  29.             AA(i, 1) = M
  30.         End If
  31.     Next
  32. Next
  33. [B1].Resize(xRow) = AA
  34. End Sub
½Æ»s¥N½X

TEST.rar (13.04 KB)

PKKO

¦^´_ 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
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 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

TOP

¦^´_ 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

TOP

¦^´_  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«×¶Ü?
PKKO

TOP

¦^´_ 5# PKKO


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

TOP

¡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)
¡@

TOP

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


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

TOP

¦^´_ 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
ªí¹F¤£²M¡BÃD·N¤£©ú½T¡B¨SªþÀɮ׮榡¡B¨S¦³°Q½×°ÝÃDªººA«×~~~~~~¥H¤W·R²ö¯à§U¡C

TOP

¦^´_ 9# stillfish00


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

TOP

        ÀR«ä¦Û¦b : ­n§åµû§O¤H®É¡A¥ý·Q·Q¦Û¤v¬O§_§¹¬üµL¯Ê¡C
ªð¦^¦Cªí ¤W¤@¥DÃD