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

[µo°Ý] ¦³±ø¥óªº²Î­p

¤j¤j¥t¥~¦pªG§Ú·Q­n¦b¸ê®Æ¦³Åܧó©Î¼W¥[®É¦Û°Ê§ó·sµ²ªG©O?

TOP

¦^´_ 11# gctsai
¨Ï¥Î¤u§@ªíªº¹w³]¨Æ¥óWorksheet_Change,³o¬O§Aªþ¥óSheets("¨Ó·½")ªºµ{¦¡½X.
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2.     Ex
  3. End Sub
  4. Private Sub Ex()
  5.     Dim D As Object, Rng As Range
  6.     Set D = CreateObject("SCRIPTING.DICTIONARY") '³]¥ß¦r¨åª«¥ó
  7.     Set Rng = Sheets("¨Ó·½").[A2]                                       '³]¥ßÀx¦s®æª«¥ó
  8.     With Sheets("²Î­p")
  9.         Do While Rng <> ""        'Rngªº­È¬°ªÅ¥Õ®É¤£°õ¦æ Doªº°j°é
  10.             If Rng = .[A2] Then D(Rng.Offset(, 1).Value) = D(Rng.Offset(, 1).Value) + 1
  11.             '        .[A2] ->Sheets("²Î­p")[A2]      '¦r¨åª«¥ó(KEY)=ITEM + 1
  12.             Set Rng = Rng.Offset(1)  'Rng¤U²¾¤@¦C¦ì
  13.         Loop
  14.         With .[B2:C2]
  15.             .Cells(1).Resize(D.Count) = Application.Transpose(D.KEYS)
  16.             .Cells(2).Resize(D.Count) = Application.Transpose(D.ITEMS)
  17.             .Resize(D.Count, 2).Sort Key1:=.Cells(1), Order1:=xlAscending, Header:=xlNo
  18.         End With
  19.     End With
  20.     Set D = Nothing
  21.     Set Rng = Nothing
  22. End Sub
½Æ»s¥N½X

TOP

¤j¤j§Ú¦b"¨Ó·½"¼W¥[¤F¤@¦C¸ê®Æ¦p¤U
  A                B                   C
¤T¬v        D25-MOTO        NOKIA
¥i¬O"²Î­p"¤]¨S¦³¦Û°Ê¦A­pºâ¤@¦¸,¦Ó¬O­n¦b°õ¦æ¤@¦¸¥¨¶°«á¤~·|±q·s­pºâ

TOP

§ï¥ÎWorksheet_Calculate¨Æ¥ó¸Õ¸Õ

TOP

¦^´_ 13# gctsai

§Ú·Q§A¬Oµ{¦¡½X©ñ¿ù¼Ò²Õ§a!
ª`·NGBKEE´£¥Ü¸Óµ{¦¡½X¥²¶·¸m©ñ¦b¨Ó·½¤u§@ªí¼Ò²Õ¤º
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

ÁÂÁ¤j¤jªº«ü¾É,¤w¸g¥i¥H¥Î¤F,­ì¨Ó¬O¼Ò²Õªº°ÝÃD
¨º¦pªG­n²Î­pªºÄæ¦ì¤£¦b®ÇÃä©O
If Rng = "¤T¬v" Then D(Rng.Offset(, 3).Value) = D(Rng.Offset(, 3).Value) + 1
°£¤F¤W­z¤§¥~¬O§_¥i¥H¦³¥t¥~ªº¤è¦¡¦]¬°¦³®É­Ô­n²Î­pªºÄæ¦ì·|Â÷ªº«Ü»·¬Æ¦Ü·|¦b±ø¥óªº«e­±
text.rar (13.72 KB)

TOP

¦^´_ 16# gctsai
¨º¦pªG­n²Î­pªºÄæ¦ì¤£¦b®ÇÃä©O
¨º§A­n¸ò¹q¸£»¡ªü ¦p¹Ï

   
  1. Private Sub Ex()
  2.     Dim D As Object, Rng As Range, f As Variant
  3.     Set D = CreateObject("SCRIPTING.DICTIONARY") '³]¥ß¦r¨åª«¥ó
  4.     Set Rng = Sheets("¨Ó·½").[a2]    '³]¥ßÀx¦s®æª«¥ó
  5.     With Sheets("²Î­p")
  6.          f = Application.Match(.[b1].Text, Sheets("¨Ó·½").Rows(1), 0) 'f: ¦b¨Ó·½¤¤´M§ä²Î­pªºÄæ¦ì
  7.          If IsError(f) Then MsgBox "²Î­pªºÄæ¦ì¤£¦s¦b!!!": Exit Sub
  8.         Do While Rng <> ""        'Rngªº­È¬°ªÅ¥Õ®É¤£°õ¦æ Doªº°j°é
  9.             If Rng = .Range("A2") Then D(Rng.Offset(, f - 1).Value) = D(Rng.Offset(, f - 1).Value) + 1
  10.             '        .[A2] ->Sheets("²Î­p")[A2]      '¦r¨åª«¥ó(KEY)=ITEM + 1
  11.             Set Rng = Rng.Offset(1)  'Rng¤U²¾¤@¦C¦ì
  12.         Loop
  13.         With .[B2:C2]
  14.             .Resize(.CurrentRegion.Rows.Count, 2) = ""
  15.             .Cells(1).Resize(D.Count) = Application.Transpose(D.KEYS)
  16.             .Cells(2).Resize(D.Count) = Application.Transpose(D.ITEMS)
  17.             .Resize(D.Count, 2).Sort Key1:=.Cells(1), Order1:=xlAscending, Header:=xlNo
  18.         End With
  19.     End With
  20.     Set D = Nothing
  21.     Set Rng = Nothing
  22. End Sub
½Æ»s¥N½X

TOP

ÁÂÁ¤j¤j,§Ú¤w¸g¦³¸ò¹q¸£»¡¤F,¥¦»¡ok!
¦ý¬O°ÝÃDÄ_Ä_¤S¦³¤@­Ó°ÝÃD,´N¬O:
¦pªG¦³§OªºÀɮפ]¬O­n¥Î³o­Óvba,¥i¬O§Ú¦b¥¨¶°¤º§ä¤£¨ì

TOP

±`¥Î¨ìªº¸Ü¥i§â¥¨¶°¦s©ñ¦b­Ó¤H¥¨¶°¬¡­¶Ã¯
¤]¥i°µ¦¨¼W¯q¶°¸ü¤J

TOP

±`¥Î¨ìªº¸Ü¥i§â¥¨¶°¦s©ñ¦b­Ó¤H¥¨¶°¬¡­¶Ã¯
¤]¥i°µ¦¨¼W¯q¶°¸ü¤J
oobird µoªí©ó 2011-7-30 23:30



½Ð°Ý¤j¤j¥¨¶°¬¡­¶Ã¯©Î¼W¯q¶°­n¦p¦ó°µ©O?

TOP

        ÀR«ä¦Û¦b : ¤f»¡¤@¥y¦n¸Ü¡A¦p¤f¥X½¬ªá¡F¤f»¡¤@¥yÃa¸Ü¦p¤f¦R¬r³D¡C
ªð¦^¦Cªí ¤W¤@¥DÃD