[µo°Ý] ½Ð°Ý²Î­p¯S©w±ø¥ó¤£­«½Æ¤§¼Æ¶q¦p¦ó§ï¥HVBA°õ¦æ

starry1314

=IFERROR(SUMPRODUCT(1/COUNTIF(rng¦­,rng¦­)*(MMULT(ISNUMBER(FIND(MID(A$3,{1,2},1),rng¦­))*1,{1;1})=2))-A6,"")
¦¹¦ê¨ç¼Æ ¬°²Î­p CÄ椺¥X²{¯S©w¤@¼Ë±ø¥ó¤§¤£­«½Æ¼Æ¶q  

=IFERROR(SUMPRODUCT(1/COUNTIF(rng¦­,rng¦­)*(MMULT(ISNUMBER(FIND(MID(B$3,{1,2},1),rng¦­))*1,{1;1})=2)),"")
¦¹¦ê¨ç¼Æ ¬°²Î­p CÄ椺¥X²{¯S©w¨â¼Ë±ø¥ó¤§¤£­«½Æ¼Æ¶q  

rng¦­=¸ê®Æ½d³ò(©w¸q¦WºÙ)

1.¥D­n¬O²Î­pC¦C¤£­«½Æ¼Æ¶q«á,¦A±q¸Ì­±§PÂ_¦p[A3]ªº­È¬°H  ¥u­pºâ½s¸¹¤º¦³¥X²{H¤§¼Æ¶q «á¦A¦©°£[A6]ªº¼Æ¶q
2.¥D­n¬O²Î­pC¦C¤£­«½Æ¼Æ¶q«á,¦A±q¸Ì­±§PÂ_¦p[A3]ªº­È¬°H  ¥u­pºâ½s¸¹¤º¦P®É¦³¥X²{H©MV¤§¼Æ¶q

¦]¸ê®Æ²³¦h·Q½Ð°Ý¦p¦ó§ï¥H°}¦C³B²z§¹«á¦A¿é¥X©Ò­n¼Æ¶q

¤U¸ü (27.01 KB)

2015-11-4 17:39

¤U¸ü (31.95 KB)

2015-11-4 17:39

VBA²Î­p¯S©w±ø¥ó¤£­«½Æ¼Æ¶q.rar (7.91 KB)

¤U¸ü¦¸¼Æ: 14

2015-11-4 17:41

starry1314

¦p¦ó§ó§ï¬°¤£­«½Æ¦¸¼Æ©O

1. Sub ²Î­p¯S©w±ø¥ó­Ó¼Æ()
   
2. Dim arr, n&, i&, j&, hh$
   
3. arr = Sheets("¤u§@ªí1").Range("C2:C4000").Value
   
4. For i = 1 To 3999
   
5.        For j = 1 To Len(arr(i, 1))
   
6.               hh = Mid(arr(i, 1), j, 1)
   
7.               If hh Like "[H]"  Then n = n + 1
   
8.        Next
   
9. Next
   
10. MsgBox n
    
11. End Sub

½Æ»s¥N½X

¥t­n¦p¦ó¥[¤J¦P®É¦³¥X²{H©MVªº¼Æ¶q

  If hh Like "[H]" And hh Like "[V]" Then n = n + 1  
²Î­p¥X¨Ó¬O0

starry1314

½Ð°Ý¦p¦ó§ï¬°²Î­p¤£­«½Æ­Ó¼Æ©O

1. Sub ²Î­p±ø¥ó­«½Æ­Ó¼Æ()
   
2. Dim arr, n&, i&, j&, hh$
   
3. arr = Sheets("¤u§@ªí1").Range("C2:C4000").Value
   
4. For i = 1 To 3999
   
5.        For j = 1 To Len(arr(i, 1))
   
6.               hh = Mid(arr(i, 1), j, 12)
   
7.               If hh Like "*H*" And hh Like "*V*" Then n = n + 1
   
8.        Next
   
9. Next
   
10. MsgBox n
    
11. End Sub

½Æ»s¥N½X

­ã´£³¡ªL

Sub TEST()
Dim A, xD, T$(1), N&(1)
Set xD = CreateObject("Scripting.Dictionary")
T(0) = Mid([¤u§@ªí2!b3], 1, 1)
T(1) = Mid([¤u§@ªí2!b3], 2, 1)
For Each A In Range([¤u§@ªí1!c2], [¤u§@ªí1!c1].Cells(Rows.Count, 1).End(xlUp)).Value
¡@¡@If A = "" Or xD(A) = 1 Then GoTo 101
¡@¡@If InStr(A, T(0)) Then
¡@¡@¡@¡@If InStr(A, T(1)) Then N(1) = N(1) + 1 Else N(0) = N(0) + 1
¡@¡@End If
¡@¡@xD(A) = 1
101: Next
[¤u§@ªí2!A5] = N(0)
[¤u§@ªí2!A6] = N(1)
End Sub

hcm19522

A5{=COUNT(1/(FIND(A3,¤u§@ªí1!C2:C24)*ISERR(FIND("V",¤u§@ªí1!C2:C24))*(COUNTIF(OFFSET(¤u§@ªí1!C2,,,ROW(C1:C23)),¤u§@ªí1!C2:C24)=1)))
A6{=COUNT(1/(COUNTIF(OFFSET(¤u§@ªí1!C2,,,ROW(C1:C23)),¤u§@ªí1!C2:C24)=1)*FIND(A3,¤u§@ªí1!C2:C24)*FIND("V",¤u§@ªí1!C2:C24))

starry1314

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

ÁÂÁ¸ѨMÃøÃD¡I¡I·Q¯}ÀY³£·d¤£©w

starry1314

¦^´_ 5# hcm19522


    ÁÂÁ¡ã¦]¥u·Q¥Î¢ä¢Ð¢Ï
§Ú´£¨Ñªº¨ç¼Æ­ì¥»¤w¥i¥X²{§Ú­nªº®ÄªG¡ã¦ý¸ê®Æ¤Ó¦h¡@­pºâ¤Ó½wºC¤F

starry1314

Sub TEST()
Dim A, xD, T$(1), N&(1)
Set xD = CreateObject("Scripting.Dictionary")
T(0) = Mid([¤u§@ªí2!b3], 1, 1)
T(1) = Mid([¤u§@ªí2!b3], 2, 1)
For Each A In Range([¤u§@ªí1!c2], [¤u§@ªí1!c1].Cells(Rows.Count, 1).End(xlUp)).Value
¡@¡@If A = "" Or xD(A) = 1 Then GoTo 101
¡@¡@If InStr(A, T(0)) Then
¡@¡@¡@¡@If InStr(A, T(1)) Then N(1) = N(1) + 1 Else N(0) = N(0) + 1
¡@¡@End If
¡@¡@xD(A) = 1
101: Next
[¤u§@ªí2!A5] = N(0)
[¤u§@ªí2!A6] = N(1)
End Sub
­ã´£³¡ªL µoªí©ó 2015-11-4 18:52

ª©¤j±z¦n ¹J¨ì­Ó°ÝÃD....¦pªG§ì¥XT(0)±MÄݳæ¦ìªº½s¸¹¥i¥H­pºâ¥¿½T,
¦ý¦]¥u¦³6­Ó³æ¦ì»Ý³æ¿W­pºâ±MÄݪº¼Æ¶q,¥i§_¥u­pºâ±N6­Ó³æ¦ì¥H¥~ªº½s¸¹¼Æ¶q

­ã´£³¡ªL

¦^´_ 8# starry1314


½Ð´£¥X½d¨Ò, ¨Ã¼ÒÀÀµ²ªG¤Î»¡©ú³W«h~~

starry1314

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

¤wªþ¤W~ÁÂÁÂ
    VBA²Î­p¯S©w±ø¥ó¤£­«½Æ¼Æ¶q.rar (15.21 KB)

¤U¸ü¦¸¼Æ: 13

2015-11-10 10:07

¤U¸ü (32.3 KB)

2015-11-10 10:09