½Ð°Ý¦r¦ê­n¦p¦ó²Î­p¦¸¼Æ¤£­«´_?

oiggu

½Ð°Ý¦r¦ê­n¦p¦ó²Î­p¦¸¼Æ¤£­«´_

kimbal

¦bd3 ©ñ¤W³o­Ó¤½¦¡:

1. =SUMPRODUCT((MID($A$1:$A$30,2,4)=D$2&"")*(LEFT($A$1:$A$30,1)=$C3)*($A$1:$A$30<>"")/COUNTIF($A$1:$A$30,$A$1:$A$30))

½Æ»s¥N½X

µM«á§Û¨ì¨ä¥L®æ

Hsieh

¦^´_ 1# oiggu
¿ï¨úd3Àx¦s®æ,¥[¤J©w¸q¦WºÙx
¤½¦¡=OFFSET(Sheet1!$A:$A,,,COUNTA(Sheet1!$A:$A),)

¤U¸ü (11.34 KB)

2010-7-24 17:02

d3°}¦C¤½¦¡
{=IF(ISNUMBER(MATCH($C3&D$2,LEFT(x,5),0)),SUM(1/COUNTIF(OFFSET($A$1,MATCH($C3&D$2,LEFT(x,5),0)-1,,COUNTIF(x,"="&$C3&D$2&"*")),OFFSET($A$1,MATCH($C3&D$2,LEFT(x,5),0)-1,,COUNTIF(x,"="&$C3&D$2&"*")))),0)}
¦V¤U¦V¥k½Æ»s

gong

d3=SUMPRODUCT((LEFT($A$1:$A$30,5)=$C3&D$2)*1/COUNTIF($A$1:$A$30,$A$1:$A$30))

oiggu

ÁÂÁ¦U¦ì:
¸g´ú¸Õ³£¥i¥H¨Ï¥Î¡A¦ý¹J¨ì¤@­Ó°ÝÃD¸ê®Æ¦³5000µ§·|¼vÅT°õ¦æ³t«×·|«D±`摱
½Ð°Ý¬O§_¥i§ïvba ¦A¶×¤J®æ¤l¥[§Ö°õ¦æ³t«×¡C

Hsieh

¦^´_ 5# oiggu

1. Sub MyCount()
   
2. Set d = CreateObject("Scripting.Dictionary")
   
3. With Sheet1
   
4. For Each a In .Range(.[A1], .[A65536].End(xlUp))
   
5.    If d(Left(a, 5)) = "" Then
   
6.       d(Left(a, 5)) = a
   
7.       ElseIf InStr(d(Left(a, 5)), a) = 0 Then
   
8.       d(Left(a, 5)) = d(Left(a, 5)) & "," & a
   
9.     End If
   
10. Next
    
11. For Each a In .[C3:C5]
    
12.    For Each b In .[D2:F2]
    
13.       .Cells(a.Row, b.Column) = UBound(Split(d(a & b), ",")) + 1
    
14.     Next
    
15. Next
    
16. End With
    
17. End Sub

½Æ»s¥N½X

oiggu

ÁÂÁ¶W¯Åª©¥D¡A¤Ó¯«¤F¡C
¤w¸g¥i¥H²Î­p¦¸¼Æ
½Ð°Ý­n¦p¦ó¥ÎVBA§âª÷ÃB²Î­p¥X¨Ó
©ñ¤J®æ¦¡¤º¡B¦r¦ê¶}ÀY+¦~¤ë²Î­p

Hsieh

¦^´_ 7# oiggu

1. Sub MyCount()
   
2. 
   
3. Set d = CreateObject("Scripting.Dictionary")
   
4. Set d1 = CreateObject("Scripting.Dictionary")
   
5. 
   
6. With Sheet1
   
7. 
   
8. For Each a In .Range(.[A1], .[A65536].End(xlUp))
   
9. d1(Left(a, 5)) = d1(Left(a, 5)) + Application.Sum(a.Offset(, 1).Resize(, 3))
   
10.    If d(Left(a, 5)) = "" Then
    
11. 
    
12.       d(Left(a, 5)) = a
    
13. 
    
14.       ElseIf InStr(d(Left(a, 5)), a) = 0 Then
    
15. 
    
16.       d(Left(a, 5)) = d(Left(a, 5)) & "," & a
    
17. 
    
18.     End If
    
19. 
    
20. Next
    
21. 
    
22. For Each a In .[F5:F7]
    
23. 
    
24.    For Each b In .[G3:L3]
    
25.       
    
26.      If (b.Column Mod 2) = 1 Then Cells(a.Row, b.Column) = UBound(Split(d(a & b), ",")) + 1
    
27.      If (b.Column Mod 2) = 0 Then Cells(a.Row, b.Column) = d1(a & b)
    
28. 
    
29.     Next
    
30. 
    
31. Next
    
32. 
    
33. End With
    
34. 
    
35. End Sub

½Æ»s¥N½X

oiggu

¦^´_ 8# Hsieh


·PÁ¶W¯Åª©¥D
§A¼gªº vba ²¼ä¦³¤O¡A¤w¸g¥i¥H²Î­p¦¸¼Æ¨Ï¥Î¥¿±`¡A
ÁٽбФ@­Ó°ÝÃD¡A¦p¦ó§â¦P¦WºÙªº¼Æ¦rÁ`­p°_¨Ó¡B
¹ï·Ó §÷®Æ¶µ¥Ø¤ÀÃþ­pºâ¡A¦A¥Î VBA ¶×¤J®æ¦¡­n¦p¦ó§@?

oiggu

¦^´_ 9# oiggu

Sub sum()
For k = 2 To 10
Set d = CreateObject("Scripting.Dictionary")
With Sheets("date")
For Each a In Sheets("date").Range(.[a3], .[a65536].End(xlUp))
   d(a) = d(a) + a.Offset(, k - 1)
Next
For Each a In Sheets("a").Range([a3], [a65536].End(xlUp))
      Sheets("a").Cells(a.Row, k) = d(a)
    Next
End With
Next
End Sub

·PÁªO¥D
¤£¾å±o¨ºùØ¥X¿ù¡B ´ú¸Õ¥X¨Óªº­È=0 ¤£¥¿½T
½Ð°Ý Sheets("a").Cells(a.Row, k) = d(a) ¿ù¦b¨ºùØ?