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

[µo°Ý] Àˬd­«½Æ©Ê½èªº¸ê®Æ

[µo°Ý] Àˬd­«½Æ©Ê½èªº¸ê®Æ

Àˬd­«½Æ©Ê½èªº¸ê®Æ
¤j¤j: ¥i§_À°¦£¸Ñ´b¤@¤U,VBA ¼gªk,
1. ±NSheet1 ªº¸ê®Æ,½Æ»s¨ìSheet2 ªº A1 ¦ì¸m¶}©l,¨Ì§Ç¼g¤J.
2. ­«½Æ©Êªº¸ê®Æ,¤£­n¦A­«½Æ½Æ»s¨ìSheet2
3. ¤ñ¸û¤£¥i­«½ÆÄæ¦ì:©m¦W,¦a°Ï,©Ê§O,±B«Ã
¥H¤W·P®¦ ~

À˵ø­«½Æ¸ê®Æ.rar (1.62 KB)

¦^´_ 1# jackyliu
¸Õ¸Õ¬Ý
  1. Option Explicit
  2. Sub Ex()
  3.     Dim D As Object, E, I As Integer
  4.     Set D = CreateObject("Scripting.dictionary")             '¦r¨åª«¥ó
  5.     For Each E In Sheet1.Range("a1").CurrentRegion.Rows      'ª«¥ó: A1©Ò©µ¦ù½d³òªº¦C
  6.          D(E.Cells(1, 1) & E.Cells(1, 2) & E.Cells(1, 3) & E.Cells(1, 5)) = E.Value
  7.     Next
  8.     With Sheet2
  9.         .Cells.Clear
  10.         I = 1
  11.         For Each E In D.KEYS
  12.             .Cells(I, "A").Resize(1, UBound(D(E), 2)) = D(E)   'Ū¨ú¦r¨åª«¥óªºITEM(°}¦C)
  13.         I = I + 1
  14.         Next
  15.     End With
  16. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

µ{¦¡ª½¨ì³o¦æ´N¥X²{¿ù»~¤F,·|¬O«Å§iªº°ÝÃDÁÙ¬O§ÚExecl¸ê®Æ¦³¼Æ¦r,­^¤å,¤¤¤å ªºÃö«Y¶Ü?

.Cells(I, "A").Resize(1, UBound(D(E), 2)) = D(E)   'Ū¨ú¦r¨åª«¥óªºITEM(°}¦C)

°õ¦æ¶¥¬q¿ù»~'13'
«¬ºA¤£²Å

TOP

¦^´_ 3# jackyliu
µ¹ªºµ{¦¡½X¬O¥H Sheet1A1©Ò©µ¦ù½d³òªº¦C ¬° Sheet1 ªº¸ê®Æ
For Each E In Sheet1.Range("a1").CurrentRegion.Rows      'ª«¥ó: A1©Ò©µ¦ù½d³òªº¦C
§A´ú¸ÕÀɮפ¤ Sheet1 ªº¸ê®Æ ¦b­þ¸Ì?? ¶·ªþ¤W§A´ú¸ÕÀɤ~¤F¸Ñ
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

·PÁªO¤j¥Î¤ß....
    For Each E In Sheet1.Range("a1").CurrentRegion.Rows      'ª«¥ó: A1©Ò©µ¦ù½d³òªº¦C
         D(E.Cells(1, 1) & E.Cells(1, 2) & E.Cells(1, 3) & E.Cells(1, 5)) = E.Value
    Next
¥H¤Wµ{¦¡»¡©ú: ·j´M¾ã¼ÆÅܼÆE ½d³ò¬OSheet1ªºA1¦Cªº½d³ò,±N·j´M¨ìªº²Ä1,2,3,5Ä檺E­Èµ¹ÅܼÆD,«ùÄò·j´M¦æ¦C¸ê®Æ,ª½¨ì¨S¦³¸ê®Æ¬°¤î.

    With Sheet2
        .Cells.Clear
        I = 1
        For Each E In D.KEYS
            .Cells(I, "A").Resize(1, UBound(D(E), 2)) = D(E)   'Ū¨ú¦r¨åª«¥óªºITEM(°}¦C)
        I = I + 1
        Next
    End With
¥H¤Wµ{¦¡»¡©ú: Sheet2©Ò¦³¸ê®Æ²M°£,±q°}¦CÅܼÆD¸Ì·j´M°}¦CÅܼÆEªº­È,±N¨C¤@¦æ¦Cªº­È,¨Ì§Ç¼g¤JSheet2

±qcode ²z¸Ñªº¬Ýªk»¡©ú¦p¤W, ¤£ª¾¹D¸ÑÄÀ¦³»~¶Ü? ½Ð±Ð¾É!
¥i§_À°¦£±Ð¾Éµ{¦¡¦p¦ó§P©w¤w¦s¦b,¦³­«½Æ¤º®e,¤£°õ¦æ¼g¤JSheet2¸Ì,¦n¶Ü?

¨Ãªþ¤W¦³¿ù»~ªºÀÉ®×,¦pªþ¥ó!

À˵ø­«½Æ¸ê®Æ-1.rar (7.58 KB)

À˵ø­«½Æ¸ê®Æ-1

TOP

¦^´_ 5# jackyliu
¥i¯à¬Oª©¥»ªº®t²§2000ª©¨S¥Î¹L,¤£ª¾¿ù»~¦b­þ¸Ì,2003°õ¦æ¨S¦³¿ù»~ªº
½Ð§ä¥X¿ù»~ÂI¦p¹Ï





¦ó§P©w¤w¦s¦b,¦³­«½Æ¤º®e,¤£°õ¦æ¼g¤JSheet2¸Ì
µ{¦¡½X¦p¤U
  1. Option Explicit
  2. Sub Ex()
  3.     Dim D1 As Object, D2 As Object, E, I As Integer, S As String
  4.     Set D1 = CreateObject("Scripting.dictionary")                   '¦r¨åª«¥ó
  5.     Set D2 = CreateObject("Scripting.dictionary")                   '¦r¨åª«¥ó
  6.     For Each E In Sheet1.Range("a1").CurrentRegion.Rows             'ª«¥ó: A1©Ò©µ¦ù½d³òªº¦C
  7.          S = E.Cells(1, 1) & E.Cells(1, 2) & E.Cells(1, 3) & E.Cells(1, 5)  'S=>key­È
  8.          D1(S) = E.Value                                            '¦³¬Û¦Pªºkey­È-> E½d³òªº­È(°}¦C) Ū¤J¨ìItem
  9.          D2(S) = D2(S) + 1                                          '¦³¬Û¦Pªºkey­È-> item +1
  10.     Next
  11.     With Sheet2
  12.         .Cells.Clear
  13.         I = 1
  14.         For Each E In D2.KEYS
  15.             If D2(E) = 1 Then                                       'D2¨S­«ÂЪºkey­È -> item= 1
  16.                 .Cells(I, "A").Resize(1, UBound(D1(E), 2)) = D1(E)  'Ū¨úD1¬Û¦Pªºkey­È: ¾É¤JD1ªºItem(°}¦C)
  17.                 I = I + 1
  18.             End If
  19.         Next
  20.     End With
  21. End Sub
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¦^´_ 4# GBKEE
  1.         For Each E In D.KEYS
  2.             .Cells(I, "A").Resize(1, UBound(D(E), 2)) = D(E)
½Æ»s¥N½X
§Ú°»´úªºµ²ªG¡G(Office 2010)
·í E ¶Ç¤J­È¬° "µL" ®É¡A¥¦´N·|²£¥Í "«¬ºA¤£²Å" ªº°T®§¡C
§Ú¥Î Debug ¼Ò¦¡³v¤@°õ¦æ¡AÆ[¹î¬ÛÃöÅܼƪº¦s¤J­È¤§ÅܤơA
  1.         Dk(E.Cells(1, 1) & E.Cells(1, 2) & E.Cells(1, 3) & E.Cells(1, 5)) = E.Value
½Æ»s¥N½X
·íµo²{ E.Value ²ö¦W¬° "µL' ®É¡A«Kª¾¤j¶Õ¤w¥h¡C¦ý«D¨C¦¸¦p¦¹¡A¥u­n¬O¦³¦¹²{¶H
±µ¤U¨Óªº°õ¦æ³£¬O  "«¬ºA¤£²Å"¡C ¦¹®É¡A¦A±Nµ{¦¡½X­«·sÀx¦s¡B³ÜªM¯ù¦A­«·s°õ¦æ¡A
¥¦µÎºZ¤F«K¤S¥¿±`°õ¦æ¤F¡C
³o·¥¥i¯à¬O Office ¼ç¦bªº Bug¡A ¥ç§Y E ­Èªº«¬ºA§P©w¤§°ÝÃD¡A¨Ï±o¥N¤J­È·|µLºÝ
Åܬ° "µL"¡C
P.S.  §Ú¦b ThisWorkbook¡B¥H¤Î Module1 ¤À§O°õ¦æ³£´¿µo¥Í¦¹²§ª¬¡C

TOP

¦^´_ 7# c_c_lai
·Ð½Ð¦b¥[¤W.Value´ú¸Õ¬Ý¬Ý
  1.         For Each E In D.KEYS
  2.             .Cells(I, "A").Resize(1, UBound(D(E), 2)).Value = D(E)
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

¥»©«³Ì«á¥Ñ c_c_lai ©ó 2013-7-24 10:11 ½s¿è

¦^´_ 8# GBKEE
µ´¹ï¤£¬O .Value ªº¦]¯À¡A
.Cells(cts, "A").Resize(1, UBound(Dk(E), 2)) = Dk(E) »P
.Cells(cts, "A").Resize(1, UBound(Dk(E), 2)).Value = Dk(E)
¨ä¹ê³£¬O¦Pµ¥·N¸q¡A¦]¬°¦b¤@¶i¤J For Each E In D.KEYS «á§Y°»´ú¨ì
­È¬° "µL" ªº±¡ªp  (¦¹²{¶H¤~¥O¤H¦Ê«ä¤£¸Ñ)¡C
¥u­n¨S²§ª¬µo¥Í¡A±µ¤U¦h¦¸°õ¦æ³£¬O OK¡A
¤Ï¤§¡B«h§¡¬O "«¬ºA¤£²Å"¡C²z½×¤W¬O¤£¸Ó¦³ªº¿ù»~¡A§Ú¥ç¸ÕµÛ
±N E ©ú½T«Å§i¬° Variant¡A ¥u­nµo¥Í²§ª¬¯«¥P¤]µL¸Ñ¡A
¥u¯àµ¥¥¦®ð®ø¤F¡I

P. S. ¶¶«K½Ð±Ð¡A³Ìªñºô­¶¬O¤£¬O³Q§ó§ï¨ì¤F¡A
         §Úªº¥k¤W¨¤­Ó¤H¸ê°T¥uÅã¥Ü
[module]ÃäÄæ¼Ò¶ô_§Úªº§U¤â[/module]

TOP

¦^´_ 9# c_c_lai
¨¯­W¤F,½Ð´«­ÓÅܼƦWºÙ¸Õ¸Õ¬Ý
  1. For Each K In D.KEYS
  2.             .Cells(I, "A").Resize(1, UBound(D(K), 2)) = D(K)   
½Æ»s¥N½X
·P®¦ªº¤ß......(¦b³Â»¶®a±Ú°Q½×°Ï.¥Î¤ß¾Ç²ß·|¦³¶i¨Bªº)
¦ý¸ê·½µL­­,«á´©¦³­­,  ¤@¤Ñ1¤¸ªºÃÙ§U,¤H¤H¦³¯à¤O.

TOP

        ÀR«ä¦Û¦b : ¤ß¤¤±`¦sµ½¸Ñ¡B¥]®e¡B·P«ä¡Bª¾¨¬¡B±¤ºÖ¡C
ªð¦^¦Cªí ¤W¤@¥DÃD