ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

½Ð±Ð¿z¿ï«á¼Æ­È¥[Á`

½Ð±Ð¿z¿ï«á¼Æ­È¥[Á`

¥»©«³Ì«á¥Ñ jasonwilliams ©ó 2011-2-2 22:32 ½s¿è

½Ð°Ý§Ú·Q ¿z¿ï¥X C Äæ¦ì¬Û¦Pªº¸ê®Æ¡]¸ê®Æ¤j¬ù¦³300¦hµ§¡^
¦A§â  D Äæ¦ìªº¼Æ­È¥[Á`
¤§«á¡ACÄæ¦ì¨ä¾l¬Û¦P¸ê®Æ§R°£
¥u¯d¤@µ§¿z¿ï«áªº¸ê®Æ  

­ì©l
     A   B   C   D .  .   .  
1¡@a  x   w ¡@¢´
2   b  x   w ¡@¢³
3¡@c  x    b¡@ ¢´
4¡@d  x   e     6
5¡@e  x   b     7
.
.

µ²ªG
     A   B   C   D .  .   .  
1¡@a  x   w ¡@9
2   c  x    b¡@ 12
3¡@d  x   e     6
4¡@
5¡@
.
.
½Ð°Ý¥ÎVBA ¸Ó«ç»ò¼g¡H

¯¬·s¦~§Ö¼Ö
50 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

¦^´_ 1# jasonwilliams
  1. Sub Ex()
  2. Set d = CreateObject("Scripting.Dictionary")
  3. For Each a In Range([C1], [C65536].End(xlUp))
  4.    If d.exists(a.Value) = False Then
  5.       d(a.Value) = Array(a.Offset(, -2).Value, a.Offset(, -1).Value, a.Value, a.Offset(, 1).Value)
  6.       Else
  7.       ar = d(a.Value)
  8.       ar(3) = ar(3) + a.Offset(, 1).Value
  9.       d(a.Value) = ar
  10.     End If
  11. Next
  12. Columns("A:D") = ""
  13. [A1].Resize(d.Count, 4) = Application.Transpose(Application.Transpose(d.items))
  14. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¥»©«³Ì«á¥Ñ jasonwilliams ©ó 2011-2-3 17:34 ½s¿è

ª©¥D±z¦n
µ{¦¡½X¥i¥H¨Ï¥Î ·PÁÂ
¡]ÁöµM§Ú¬Ý¤£¤ÓÀ´¡A§Ú·|ºÉ¤O¥h¾Ç²ß¡^:D

¥t¥~§Ú¥Î¤F¤@­Ó «ü¥O«ö¶s·Q°µ¤@Áä²M°£excel ­¶­±¤º®e
§Ú¥Î«Ü²Âªº¤è¦¡°µ¥X¨Óªº
Private Sub CommandButton2_Click()
    Cells.Select
    Selection.Clear
    Range("A1").Select
End Sub
§Ú·QÀ³¸ÓÁÙ¦³§ó¦nªº¤èªk¤~¬O¡H
50 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

¦^´_ 3# jasonwilliams
  1. Private Sub CommandButton2_Click()
  2. '³o¸Ì´¡¤J§A¿ý»sªº°Ï¬q
  3. Set d = CreateObject("Scripting.Dictionary")
  4. For Each a In Range([C1], [C65536].End(xlUp))
  5.    If d.exists(a.Value) = False Then
  6.       d(a.Value) = Array(a.Offset(, -2).Value, a.Offset(, -1).Value, a.Value, a.Offset(, 1).Value)
  7.       Else
  8.       ar = d(a.Value)
  9.       ar(3) = ar(3) + a.Offset(, 1).Value
  10.       d(a.Value) = ar
  11.     End If
  12. Next
  13. Columns("A:D") = ""
  14. [A1].Resize(d.Count, 4) = Application.Transpose(Application.Transpose(d.items))
  15. End Sub
½Æ»s¥N½X
²M°£³¡¥÷
Cells.Clear
§Y¥i
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¥»©«³Ì«á¥Ñ jasonwilliams ©ó 2011-2-3 20:09 ½s¿è

¤£¦n·N«ä
­è­è¸Õ¤F«Ü¤[
Åý§Ú¸Õ¥X¨Ó¤F

½s¿è§¹¥»¤å
¤~¬Ý¨ìª©¥D¤w¦^¤å


¥t¥~¡G
¦]¬°­ì©l¸ê®Æ¤¤§Ú¥u­n¯S©w´XÄæ¸ê®Æ ¨Ò¦p¡G DÄæ ¨ì EÄæ + ³æ¿W GÄæ
§Úªº°µªk¬O copy ¨ì Sheet2 ¤§«á
¦A§âsheet1 ¨ä¾l¸ê®Æ§R°£
sheet2 ½Æ»s¨ì sheet1
¦A°µ¿z¿ï¥[Á`
¤£ª¾¹D¦³µL¤èªk¥i¥H¤£¥Î¨ì sheet2 ´N¥i¹F¦¨¡H


§Ú¦A¬ã¨sµ{¦¡½X
¤£À´ªº¦a¤è ½Ð±z¦h¦h«ü±Ð
ÁÂÁÂ
50 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

¤£¦n·N«ä
¤p§Ì¤S¦³·sªº°ÝÃD
¦]¬°¤£·Q®ö¶Oª©¥»
§Ú´Npo¦b³o¤F

½Ð°Ý§Ú·Q°µ¤@­Ó «ü¥O«ö¶s
«ö¤U¥h·|¶K¤W½Æ»sªº¸ê®Æ

Range("A1").PasteSpecial
§Ú¥Î¤W­±ªº»yªk
¨Ï¥ÎªÌ¦pªG¦³½Æ»s¸ê®Æªº¸Ü
¥i¥H¥¿±`¶K¤W

¦pªG¨S½Æ»s¸ê®Æªº¸Ü
·|Åã¥Ü " Class Range ªº PasteSpecial ¤èªk¥¢±Ñ "

½Ð°Ý¬O§Ú¥Î¿ù»yªk
ÁÙ¬O¸Ó«ç»ò¼g¤~¯à§PÂ_¬O§_¦³½Æ»s¸ê®Æ

ÁÂÁÂ
50 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

¥»©«³Ì«á¥Ñ jasonwilliams ©ó 2011-2-8 22:56 ½s¿è

¦^´_ 2# Hsieh [code]

  Set D = CreateObject("Scripting.Dictionary")
            For Each a In Range([c1], [c65536].End(xlUp))
            If D.exists(a.Value) = False Then
            D(a.Value) = Array(a.Offset(, -2).Value, a.Offset(, -1).Value, a.Value, a.Offset(, 1).Value)
            Else
            ar = D(a.Value)
            ar(3) = ar(3) + a.Offset(, 1).Value
            D(a.Value) = ar
            End If
            Next

            Columns("A:D") = ""
            [A1].Resize(D.Count, 4) = Application.Transpose(Application.Transpose(D.items))

           

¤£¦n·N«ä  
§Ú²{¦b·Q¿z¿ï B Äæ¦ìªº¸ê®Æ
§Ú§ó§ï ¬õ¦â³¡¥÷ C §ï¦¨ B
«o¥X²{ ÂŦ⨺¦æ¿ù»~

½Ð°Ý§Ú¬O¤Ö§ó§ï­þÃä¤F¡H
¤pªº¤£¤~¡A¬Ý¤£¤ÓÀ´µ{¦¡½X
½Ðª©¥D¦A¦¸¬Û±Ï

ÁÂÁÂ
50 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X

TOP

¦^´_ 7# jasonwilliams


    ½Ð°Ñ¾\VBA»¡©úOFFSET
·í¦bBÄæ§@Åܼưj°é®Éa.offset(,-2)´N·|²£¥Í¿ù»~
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

        ÀR«ä¦Û¦b : ¤@­Ó¯Ê¤fªºªM¤l¡A¦pªG´«¤@­Ó¨¤«×¬Ý¥¦¡A¥¦¤´µM¬O¶êªº¡C
ªð¦^¦Cªí ¤W¤@¥DÃD