[µo°Ý] ½Ð°Ýcountifs¦h±ø¥ó,¦p¦ó¥ÎVBA©O?·|¶]¨ìÀú±¼

starry1314

¨ç¼Æ:=COUNTIFS($A$3:$A$16,"<>*T*",$A$3:$A$16,"<>*Q*",$A$3:$A$16,"<>*I*",$A$3:$A$16,"<>*P*",$A$3:$A$16,"<>*L*",$B$3:$B$16,F2,$C$3:$C$16,"=2")
¦h­««ü©w±ø¥ó ²Î­p­Ó¼Æ.rar (10.26 KB)

¤U¸ü¦¸¼Æ: 8

2015-8-7 23:54

¤U¸ü (21.9 KB)

2015-8-7 23:55

Scott090

¦^´_ 1# starry1314


    ¸ê®Æ¦h¡A¸Õ¥Î³o­Ó

1. Option Base 1
   
2. Option Explicit
   
3. 
   
4. Sub µæ¦â­pºâ()
   
5.     Dim i&, j%, k%
   
6.     Dim aa  '°Ï°ì¡Bµæ¦â¡B¼Æ¶q ¸ê®Æ
   
7.     Dim bb  ''µæ¦â
   
8.     Dim cc$  '°Ï¤À±ø¥ó
   
9.     Dim dd  '·j´Mµ²ªG
   
10.         
    
11.     Sheets("¤u§@ªí1").Activate
    
12.     aa = Range("A3:C" & [A2].End(xlDown).Row)
    
13.     bb = Range("F2:F" & [F2].End(xlDown).Row)
    
14.     Range("G2:G" & UBound(bb) + 1).Clear    '²Ä2¦C¶}©lµæ¦â°Ï¤À
    
15.    
    
16.     ReDim dd(UBound(bb), 1)
    
17.     cc = "TQILP"
    
18.    
    
19.     For i = 1 To UBound(aa)
    
20.         If InStr(cc, Mid(aa(i, 1), 2, 1)) = 0 And aa(i, 3) = 2 Then
    
21.             For j = 1 To UBound(bb)
    
22.                 If aa(i, 2) = bb(j, 1) Then dd(j, 1) = dd(j, 1) + 1: Exit For
    
23.             Next
    
24.         End If
    
25.     Next i
    
26.     Cells(2, "G").Resize(UBound(dd)) = dd
    
27.    
    
28. End Sub

½Æ»s¥N½X