½Ð±Ð¦p¦ó..¨ú¦P©m¦W©M¥[Á`¦¨¥æª÷...·PÁÂ

alims

.................¸ê®Æ-------
¤é´Á        ©m¦W        ¦¨¥æª÷
2¤ë1¤é        aaa               1000
2¤ë2¤é        bbb        2000
2¤ë3¤é        aaa                3000
2¤ë4¤é        ccc               4000
2¤ë5¤é        aaa               5000
2¤ë7¤é        aaa               1000
2¤ë8¤é        bbb        2000
2¤ë9¤é        aaa               3000
2¤ë10¤éccc                4000
2¤ë11¤éaaa         5000
               
---        Åܦ¨------       
        ©m¦W        ¦¨¥æª÷
        aaa         18000
        bbb        4000
        ccc                8000
¤]´N¬O ¦P©m¦W§@¥[Á`

register313

¦^´_ 1# alims

¦r¨å¼Ð·Ç¥Îªk

1. Sub XX()
   
2. Dim Ar(), d As Object
   
3. Set d = CreateObject("scripting.dictionary")
   
4. Ar = Range("A1:C" & [C2].End(xlDown).Row)
   
5. For i = 1 To UBound(Ar)
   
6.   If Not d.exists(Ar(i, 2)) Then d.Add Ar(i, 2), Ar(i, 3) Else d(Ar(i, 2)) = d(Ar(i, 2)) + Ar(i, 3)
   
7. Next i
   
8. [E:F] = ""
   
9. [E1].Resize(d.Count, 1) = Application.Transpose(d.keys)
   
10. [F1].Resize(d.Count, 1) = Application.Transpose(d.items)
    
11. End Sub

½Æ»s¥N½X

alims

¦^´_ 2# register313


    ¦n¼F®`!!!  ÁÂÁ¤F

GBKEE

¦^´_ 1# alims

1. Option Explicit
   
2. Sub Ex()
   
3.     Dim Rng As Range
   
4.     Set Rng = Range("A1").CurrentRegion         '³]¸m¸ê®Æ°Ï
   
5.     Rng.Columns(2).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range("IV1:IV2"), CopyToRange:=Range("F1"), Unique:=True
   
6.                                                 '¶i¶¥¿z¿ï ¥X¤£­«´_ªº©m¦W¨ì FÄæ
   
7.     Range("G1") = "¦¨¥æª÷"
   
8.     With Range(Range("G2"), Range("F1").End(xlDown).Offset(, 1))
   
9.         .FormulaR1C1 = "=SUMIF(" & Rng.Columns(2).Address(, , 0) & ", RC[-1] ," & Rng.Columns(3).Address(, , 0) & ")"
   
10.                     '³]¤U¤½¦¡
    
11.         .Value = .Value   '¤½¦¡Âà´«¬°­È ¤]¥i¥H¤£Âà´«
    
12.     End With
    
13. End Sub

½Æ»s¥N½X

Hsieh

¦^´_ 3# alims
¦X¨Ö·Jºâ±Mªù¸Ñ¨M³oÃþ°ÝÃD

register313

¦^´_ 3# alims

¤p­p(©m¦WÄæ¦ì­n¥ý±Æ§Ç)

¤U¸ü (171.32 KB)

2012-6-21 17:27

alims

¦^´_ 6# register313

¥Î¹L¤F ÁÂÁÂ

lasum

·PÁ¦U¦ì´£¨Ñªº¸ê®Æ¡K´ú¸Õ³£¥i¥H°õ¦æ¡K
¦ý¬O¥X²{¤@­Ó°ÝÃD¡K
°õ¦æregister313¤jªºµ{¦¡®É¡K­n¨D©w¸q i ¡K¥i¯à¬O§Úªºª©¥»¤ñ¸û§C§a¡I

mistery

§Q¥Î SUM¨ç¼Æ+¯x°} ´N¥i¥H¤F~
= { sum ((©m¦WÄæ¦ì¡G©m¦WÄæ¦ì = "¬Y¤H") * (¦¨¥æª÷Äæ¦ì¡G¦¨¥æª÷Äæ¦ì))  }

GBKEE

¦^´_ 9# mistery
¤£¥Î°}¦C,ÁÙ¦³¤]¥i¥H
=SUMIF($B$2:$B$11,E2,$C$2:$C$11)
=SUMPRODUCT(($B$2:$B$11=E2)*($C$2:$C$11))