Hsieh

¦^´_ 1# basarasy

1. Sub Ex()
   
2. Dim A As Range
   
3. Set d = CreateObject("Scripting.Dictionary")
   
4. Set d1 = CreateObject("Scripting.Dictionary")
   
5. For Each A In Range([A1], [A1].End(xlDown))
   
6. mystr = Join(Application.Transpose(Application.Transpose(A.Resize(, 4))), ",")
   
7. d(mystr) = d(mystr) + A.Offset(, 4).Value
   
8. d1(mystr) = Array(A.Value, A.Offset(, 1).Value, A.Offset(, 2).Value, A.Offset(, 3).Value, d(mystr))
   
9. Next
   
10. [H:L] = ""
    
11. [H1].Resize(d1.Count, 5) = Application.Transpose(Application.Transpose(d1.items))
    
12. End Sub

½Æ»s¥N½X

¦pªG§âªí®æ¥[¤WÄæ¦WºÙ¡A´N¥i¨Ï¥Î¼Ï¯Ã¤ÀªR

Hsieh

¦^´_ 3# basarasy
³o¬O«Üª½Æ[ªº¥N½X

1. Sub Ex()
   
2. Dim A As Range  '«Å§iÅܼƬ°Àx¦s®æ«¬ºA
   
3. Set d = CreateObject("Scripting.Dictionary")  '³]¸m¦r¨åª«¥ó
   
4. Set d1 = CreateObject("Scripting.Dictionary")  '³]¸m¦r¨åª«¥ó
   
5. For Each A In Range([A1], [A1].End(xlDown))  '¦bAÄæ§@°j°é¨ú±o¦ì¸m
   
6. mystr = Join(Application.Transpose(Application.Transpose(A.Resize(, 4))), ",")  'Åܼƫü©w¬°AÄæ¦V¥kÂX®i¦¨4Äæ¤j¤pªº½d³ò¥H³r¸¹³sµ²ªº¦r¦ê
   
7. d(mystr) = d(mystr) + A.Offset(, 4).Value  '­pºâ¥Hmystr¬°ÃöÁä¦rªº²Ö¥[
   
8. d1(mystr) = Array(A.Value, A.Offset(, 1).Value, A.Offset(, 2).Value, A.Offset(, 3).Value, d(mystr))  '¥Hmystr¬°ÃöÁä¦r¥[¤J¶µ¥Ø¡A¦¹¶µ¥Ø¬°¤@°}¦C¡A°}¦C³Ì«á¤@­Ó­È¬°²Ö¥[­È
   
9. Next
   
10. [H:L] = ""  '²MªÅ¥Ø¼Ð°Ï
    
11. [H1].Resize(d1.Count, 5) = Application.Transpose(Application.Transpose(d1.items))  '±N¦r¨åª«¥ó¤º®e¥H2¦¸Âà¸m(¦]¬°­nÂন¼Ð·Çªº¤Gºû°}¦C)¼g¤J¥Ø¼Ð°Ï
    
12. End Sub

½Æ»s¥N½X

Hsieh

¦^´_ 6# FAlonso

http://forum.twbts.com/thread-20-1-1.html
http://forum.twbts.com/viewthread.php?tid=2287&extra=pageD1&page=2
³o¸Ì¦³ªì¨Bªº»¡©ú¡A­n¤F¸Ñ¨äÄݩʤΤèªk½Ð°Ñ¾\VBA»¡©úÀÉ

Hsieh

¦^´_ 6# basarasy


    ÄÀ©ñª«¥ó¬O¦n²ßºD
¨ä¹êª«¥ó·|ÀHµÛµ{§Çµ²§ô¦Ó¦Û°ÊÄÀ©ñ

Hsieh

§¹¥þ¬Ý¤£À´©pÅÞ¿è
½Ð±N¶·¨D±Ô­z²M·¡¡A¹ê¦bµLªk±q§A¿ù»~ªº¥N½X¡A§ä¥X§AªºÅÞ¿è

Hsieh

¦^´_ 10# FAlonso

1. Sub Ex()
   
2. On Error Resume Next
   
3. Dim Mystr(), MyCnt()
   
4. i = 1
   
5. Do Until Cells(i, 1) = ""
   
6.    If IsError(Application.Match(Cells(i, 1) & "," & Cells(i, 2) & "," & Cells(i, 3) & "," & Cells(i, 4), Mystr, 0)) Then
   
7.       ReDim Preserve Mystr(s)
   
8.       ReDim Preserve MyCnt(s)
   
9.       Mystr(s) = Cells(i, 1) & "," & Cells(i, 2) & "," & Cells(i, 3) & "," & Cells(i, 4)
   
10.       MyCnt(s) = Cells(i, 5)
    
11.       s = s + 1
    
12.       Else
    
13.       k = Application.Match(Cells(i, 1) & "," & Cells(i, 2) & "," & Cells(i, 3) & "," & Cells(i, 4), Mystr, 0)
    
14.       MyCnt(k) = MyCnt(k - 1) + Cells(i, 5)
    
15.     End If
    
16.     i = i + 1
    
17. Loop
    
18. For i = 0 To s - 1
    
19.    Cells(i + 1, 8).Resize(, 4) = Split(Mystr(i), ",")
    
20.    Cells(i + 1, 12) = MyCnt(i)
    
21. Next
    
22. End Sub

½Æ»s¥N½X

Hsieh

¦^´_ 18# FAlonso
Àx¦s®æ«ü©w¥X¨Óªº½d³ò
¤£½×¬O1¦C©Î¦h¦C
excel§¡±Nµø¬°¤Gºû°}¦C
¸g¹Ltranspose¨ç¼ÆÂà¸m«á¤~·|±o¨ì¥¿½Tªººû¼Æ
©Ò¥H·í§Aªº­ì°}¦C¬O¥H¤@ºû°}¦CÀx¦s¦h­Ó°}¦C¦¨¬°¦hºû°}¦C®É
¦p
Dim Ay()
For i = 0 To 10
   ReDim Preserve Ay(i)
   Ay(i) = Array(i * 1, i * 2, i * 3, i * 4, i * 5)
Next
[A1].Resize(i, 5) = Application.Transpose(Application.Transpose(Ay))
Ay°}¦C¤¸¯À¬O°}¦C
­n¥H¤@¦¸¼g¤J´N¥²¶·¨Ï¥Î2¦¸transpose

Hsieh

¦^´_ 20# FAlonso
­º¥ý§Ú¥²¶·¥ý¨ØªAªº¨Dª¾ºA«×
°O±o·íªì­è±µÄ²°}¦C»Pdictionaryª«¥ó®É¡A¤]¦³µÛ¸ò§A¬Û¦PªººÃ´b
¦ý§Ú´N¬O¤£¨D¬Æ¸Ñªº­Ó©Ê¡A´N§âTRANSPOSE¨ç¼Æ§@¬°³B²z°}¦C°ÝÃDªº¤èªk¦Ó¤w
¤µ¤Ñ¹J¨ì±z´£¥XºÃ°Ý¡A¨º´NÅý§Ú­Ì¤@°_¨Ó±´°Q¥Lªº¶ø¯µ§a!
¥H¤U¬O§Ú­Ó¤H¹ï°}¦C»Pdictionaryªº²z¸Ñ¤è¦¡
´£¨Ñ±z°µ°Ñ¦Ò
­Y¦³¤£¬O¤§³B¡AÁٽЦU¦ì«e½ú«ü¥¿

1.Range("A1:A4")ºâ¤£ºâ¬O¤@­Ó°}¦C?
¬O°}¦C¡A¦Ó¥B¬O¤Gºû°}¦C
¥H¤U¦Cµ{¦¡½X´ú¸Õ¸Ó°}¦Cª¬ºA

1. Sub Ex()
   
2. Dim j%, i%, Ar As Variant
   
3. On Error Resume Next
   
4. Ar = [A1:A4]
   
5. Do Until j > 2  'j¬°­pºâÂà¸m¦¸¼ÆªºÅܼÆ
   
6. MsgBox IIf(IsArray(Ar), "ar¬O°}¦C", "ar¤£¬O°}¦C") '¬O§_¬°°}¦C
   
7. i = 1
   
8. Do Until Err.Number > 0
   
9. i = i + 1
   
10. k = UBound(Ar, i)
    
11. Loop
    
12. MsgBox "¦¹°}¦C¬°" & i - 1 & "ºû°}¦C" & Chr(10) & "³QÂà¸m" & j & "¦¸" '²Ä¤@ºû¤W­­(¦C¼Æ)
    
13. Err.Clear
    
14. Ar = Application.Transpose(Ar)
    
15. j = j + 1
    
16. Loop
    
17. End Sub

½Æ»s¥N½X

2.¬°¤°»ò¥´ª½ªº®æ¤l,¤@¦¸transpose´N¥i¥H,¥´¾îªº­n¨â¦¸?
¨º¬O¦]¬°joinªº¤Þ¼Æ¥²¶·¬O¤@ºû°}¦C

3.d(mystr) = d(mystr) + A.Offset(, 4).Value³o¥y¬O«ç¸ÑÄÀ,¬°¤°»ò¥i¥H§@²Ö¥[ªº§@¥Î?
¦]¬°dictionaryª«¥ó¡A§A¥i¥Hª½±µµø¬°°}¦C¡A·í¤¤ªºmystr´N¬O¯Á¤Þ­È(¤@¯ë°}¦C·|¬O¼Æ­È,¦b¦¹¬O°£¤F°}¦C«¬ºA¥H¥~ªº¥ô¦ó¸ê®Æ«¬ºA)¡A¥Nªí¦¹ª«¨£¬Y­Ó¤¸¯Àªº«ü©w¯Á¤Þ
d(mystr)´N¬Od³o­Óª«¥óªºmystr¦ì¸mªº¨º­Ó¤¸¯À
d(mystr) = d(mystr) + A.Offset(, 4).Value  
ÅܼÆ(·s­È)=ÅܼÆ(­ì­È)+±`¼Æ
´N¦n¤ñ¤@­ÓÅܼƤF¡A©Ò¥H¦ÛµM´N¯à­pºâ²Ö¥[­È

Hsieh

Dictionaryª«¥ó·í¥[¤JKEYªº¦P®É§Y²£¥Í¹ïÀ³¸ÓKEYªº­È¬°Emptyªºªì©l­È
¾ã­Óª«¥ó·í¥iµø¬°¤Gºû°}¦C
¨ä²Ä¤@ºû«h¬O¸Óª«¥óªº¼Æ¶q
¨ä²Ä¤Gºû«h¬O¸Óª«¥óªºkeys»Pitems
¸Ó°}¦C¤j¤pªí¥Ü¦p¤U:
ThisArray(d.count,2)
¦ý§AµLªk¥HINDEX«ü©w¸Ó°}¦C
¦]¬°³o¥u¬O±N¨äµø¬°¤Gºû°}¦C
¦ý¹ê»Ú¤W¥L¬O2­ÓÁa¦Cªº°}¦C¦X°_¨Ó
©Ò¥H¤@¯ëÀ³¥Î¤W·|¥H¨äKeyª½±µ«ü©w¨ì¹ïÀ³ªºItem

Hsieh

¦^´_ 25# linshin1999
dim d as object
³o¬O¥¿½T«Å§i¡Adictionary¥L¬O­Óª«¥ó©Ò¥H«Å§i¬°ª«¥ó¨S¿ù
dictionary³o­Óª«¥ó¤£·|¦]¬°¥Lªºkey»Pitem²£¥Í¸ê®Æ«¬ºAÅܤÆ
§A¥i±N¦¹ª«¥ó¬Ý¦¨¤@­Ó®e¾¹¡A¦¹®e¾¹ªº¤º®eª«¨Ã¤£·|§ïÅܦ¹®e¾¹¥»¨­ªº©Ê½è

©Ò¥HVBA»¡©ú¤¤´£¨ì
dictionaryª«¥ó¡A¨äkey¦¡°£¤F°}¦C¥H¥~ªº¥ô¦ó¸ê®Æ«¬ºA¡A¦Óitem«h¥i¬°¥ô¦ó§ÎºAªº¸ê®Æ¡C
¤@­ÓÃöÁä¦r¥i¹ïÀ³¥ô¦óªº¸ê®Æ¡A³o¬O¦¹ª«¥óªº¯S©Ê¡C