ikboy

¦^´_ 5# PKKO


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

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«×¶Ü?

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¤ñ¸û§Ö

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

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