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

[µo°Ý] Â^¨ú³øªí¤¤©Ò»Ý¸ê®Æ

¦^´_ 10# asus103
µ{¦¡´£¨úªº¾Ç¸¹¤w¸g¬O¼Æ¦r¤F
  1. Sub Ex()
  2. Dim A As Range, Ar(), C, d As Object, d1 As Object, d2 As Object, r&, MyClass$, Ky, s%, i%
  3. Set d = CreateObject("Scripting.Dictionary")
  4. Set d1 = CreateObject("Scripting.Dictionary")
  5. Set d2 = CreateObject("Scripting.Dictionary")
  6. With Sheets("Sheet1")
  7.   For Each A In .Range(.[B1], .Cells(.Cells.Rows.Count, 2).End(xlUp))
  8.      If A Like "*¯Z" Then MyClass = A.Value
  9.      If Replace(A.Value, "¡@", "") = "¾Ç¸¹" Then Ar = .Range(A, A.End(xlToRight)).Value
  10.      If Val(A.Value) <> 0 And InStr(A, "-") = 0 Then
  11.        s = 0
  12.        For Each C In Ar
  13.         If C <> "" Then d1(C) = ""
  14.          If C = "©m¦W" Then d1("¯Z¯Å") = "": d(A & "¯Z¯Å") = MyClass
  15.          d2(A.Value) = ""
  16.          d(A & C) = A.Offset(, s).Value
  17.          s = s + 1
  18.        Next
  19.     End If
  20.   Next
  21. End With
  22. With Sheets("Sheet4")
  23. .Cells = ""
  24. r = 2
  25. .[A1].Resize(, d1.Count) = d1.KEYS
  26. For Each Ky In d2.KEYS
  27.    For i = 1 To d1.Count
  28.       .Cells(r, i) = IIf(d(Ky & .Cells(1, i)) = "", -1, d(Ky & .Cells(1, i)))
  29.    Next
  30. r = r + 1
  31. Next
  32. End With
  33. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¥»©«³Ì«á¥Ñ asus103 ©ó 2010-12-30 14:27 ½s¿è

¦^´_ 11# Hsieh
¹ï¤£°_
¬O§Ú¨S¦³»¡²M·¡
¬O¯Z¯Å¦WºÙ¡A¦p¤T¦~¤G¯Z§ï¬°302....

ÁÙ¦³¥t¥~¤@­Ó¤p°ÝÃD
­ì©l¸ê®Æ¤¤­Y¬O"©m  ¦W"¤£¬O"©m¦W"(¦pB7¤¤¬O"©m  ¦W"®É¡A(¤¤¶¡¦³ªÅ¥Õ))
®ö¶O±z³\¦hÄ_¶Qªº®É¶¡¡A
¥u¯à¦b¤@¦¸¸ò±z»¡·PÁÂ
ÁÂÁ±z
ASUS

TOP

¦^´_ 12# asus103
  1. Sub Ex()
  2. Dim A As Range, Ar(), C, d As Object, d1 As Object, d2 As Object, r&, MyClass$, Ky, s%, i%
  3. Set d = CreateObject("Scripting.Dictionary")
  4. Set d1 = CreateObject("Scripting.Dictionary")
  5. Set d2 = CreateObject("Scripting.Dictionary")
  6. With Sheets("Sheet1")
  7.   For Each A In .Range(.[B1], .Cells(.Cells.Rows.Count, 2).End(xlUp))
  8.      If A Like "*¯Z" Then MyClass = A.Value
  9.      If Replace(A.Value, "¡@", "") = "¾Ç¸¹" Then Ar = .Range(A, A.End(xlToRight)).Value
  10.      If Val(A.Value) <> 0 And InStr(A, "-") = 0 Then
  11.        s = 0
  12.        For Each C In Ar
  13.         If C <> "" Then d1(C) = ""
  14.          If C = "©m¦W" Then d1("¯Z¯Å") = "": d(A & "¯Z¯Å") = Replace(Replace(Replace(Replace(Replace(Replace(MyClass, "°ª", ""), "¦~", ""), "¯Z", ""), "¤T", 3), "¤G", 2), "¤@", 1)
  15.          d2(A.Value) = ""
  16.          d(A & C) = IIf(s > 5, "", "'") & A.Offset(, s).Text
  17.          s = s + 1
  18.        Next
  19.     End If
  20.   Next
  21. End With
  22. With Sheets("Sheet4")
  23. .Cells = ""
  24. r = 2
  25. .[A1].Resize(, d1.Count) = d1.KEYS
  26. For Each Ky In d2.KEYS
  27.    For i = 1 To d1.Count
  28.       .Cells(r, i) = IIf(d(Ky & .Cells(1, i)) = "", -1, d(Ky & .Cells(1, i)))
  29.    Next
  30. r = r + 1
  31. Next
  32. End With
  33. End Sub
½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 13# Hsieh

¥þ¥Ø¼Ð¹F¦¨!!!!
«D±`¦a·PÁ±z

ªþµù:
¦³¤@­Ó¦nª±ªº²{¶H§Ú·|¥h¬ã¨s
¿é¥Xªº¦¨ÁZ¤¤¡A«e¤T­Ó¬O¤å¦r®æ¦¡¡A¤§«á¬O¼Æ¦r®æ¦¡

¦A¦¸·P¿E
±q³o¤§¶¡¡A§Ú¤]¾Ç¨ì¦n¦h¡A§ó©ú¥Õ"¤£¨¬ªº§ó¦h"
ASUS

TOP

§ï³o¦æ
d(A & C) = IIf(s > 2, "", "'") & A.Offset(, s).Text
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¦^´_ 15# Hsieh
§¹¥þ§¹¬üª¬ºA!!!!!!±z¯u¬O¯«¤H°Ú!!!!
§Ú·|¥H¦¹¬°¥Ø¼Ð§V¤Oªº!!
¦A¤@¦¸·PÁÂ!!!
ASUS

TOP

¦^´_ 13# Hsieh

Hsieh¤j¤j±z¦n¡G
·PÁ±zªº¼ö¤ß¨ó§U¡A¤u§@¤Wªº°ÝÃD¤w¸gOK
¦ý°ò©ó·Q­n§âVBA¾Ç¦nªº¤ß±¡¡AÃö©ó³o­Óµ{¦¡ªº¥©§®¤§³B¡A¬O§_¥i¥HÄ~Äò½Ð±Ð±z
³o´X¤Ñ§Ú¥ý¬Ý¤F¤@¥»VBA¤Jªùªº®Ñ
¤]¸Ô²Ó¬Ý¤F±zµoªíªº¤@¨Ç¤å³¹¡A¦p"Dictionaryª«¥óªº»{ÃÑ»PÀ³¥Î"¡B"VBAªº¼g§@§Þ¥©»P¼W¶i®Ä¯à"¡B"¾Ç²ß Excel VBAªº¤@¨Ç°ò¥»·§©À"µ¥¡AÀò¯q¨}¦h
¦ý¦¹µ{¦¡¤¤ªº¤@¨Çµ{¦¡½X§ÚÁÙ¬O¬Ý¤£À´
¦p¡G
    For Each C In Ar
        If C <> "" Then d1(C) = ""
         If C = "©m¡@¦W" Then d1("¯Z¯Å") = "": d(A & "¯Z¯Å") = Replace(Replace(Replace(Replace(Replace(Replace(MyClass, "°ª", ""), "¦~", ""), "¯Z", ""), "¤T", 3), "¤G", 2), "¤@", 1)
         d2(A.Value) = ""
         d(A & C) = IIf(s > 20, "", "'") & A.Offset(, s).Text
         '§ïÅÜ s ¥i§ïÅܦ¨ÁZ¥H¤å¦r©Î¼Æ¦r®æ¦¡
         s = s + 1
       Next
³o¬q¤¤¬O¦p¦ó±N¸ê®Æ«ü©w¶i¤Jd¡Bd1¡Bd2³o¤T­ÓDictionaryª«¥óªº©O?
°õ¦æ§¹d1(C) = ""«á¡Ad1ªºÅܼƤº®e¤¤³ºµM´N¥X²{count=1¡BItem1="¾Ç¸¹"¡H¯u¬O¤Ó¯«©_¤F
"d1("¯Z¯Å") = "": d(A & "¯Z¯Å") ="ªº»yªk¬O¬Æ»ò¡H§Ú·Q¥L¬O¬°¤F§â¯Z¯Å³o­ÓITEM«ü©w¶id1ªº²Ä¤T¶µ¡A¦ý¥L¬O«ç»ò¿ì¨ìªº©O¡H

¤£¦n·N«ä¡A¤p§Ì·M¬N©|¦³³\¦h¤£©ú¤§³B¡A
´Á«Ý±zªº¬Û§U¤§«á¡AÄ~Äò±´¯ÁVBAªº¥@¬É
ASUS

TOP

¦^´_ 17# asus103

http://forum.twbts.com/thread-20-1-1.html
¥ý¹ï¸Óª«¥óªºÄݩʲz¸Ñ«á¦AÄ~Äò°Q½×
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

¥»©«³Ì«á¥Ñ asus103 ©ó 2011-1-3 14:14 ½s¿è

¦^´_ 18# Hsieh
±z¦n¡G
³o½g¤º®e¡A§Ú¦³¦L¤U¨Ó¡AŪ¤F¦n´X¦¸¡A¥i¯à¬O§Ú¨S¦³´x´¤¨ì­«ÂI
§â¸ê®Æ¥[¤J Dictionaryª«¥óªº»yªk¡Gdictionary_object.Add index, content
¥i¬O±z¦bµ{¦¡¤¤¥Îªº¬O¡yd1(C) = ""¡zOR¡yd2(A.Value) = ""¡zµ²ªG¦b"°Ï°ìÅܼÆ"¤¤³ºµM´N¥X²{count=1¡BItem1="¾Ç¸¹".....
§ÚµLªk±Àª¾¨äcontent¬O¦p¦ó¨ú±o(index§Ú²q¥i¯à¬O¶¶§Ç»¼¥[¬O¶Ü?)
¦pªG§Ú²q·Qd1(C) = ""³o­Óµ{¦¡½Xªº·N¸q¬O
­YC¤£¦bd1¤¤¡A¨º»ò´N¨Ì§Ç¥[¤Jd1(index¡Bcount³£¥[1)³o¼Ë¹ï¶Ü?
¨º¨ä»yªk¬°¦ó?
¥t¥~
If C = "©m¦W" Then d1("¯Z¯Å") = "": d(A & "¯Z¯Å") = MyClass
¤¤: ªº·N¸q¬O«ü¦bthenªº½d³ò¤§¤¤¡A¨Ã¥B¥i¥H¬Ù²¤ENDIF¬O¶Ü?¦Ó¤£¥u¬O³æ¯Âªº¤À¦æ²Å¸¹
°ÝÃD«ÜÄø¡A¦ý
©|½Ð½ç±Ð
ASUS

TOP

¦^´_ 19# asus103


    dictionaryª«¥óÆ[©À¥i¥Î®e¾¹¨Ó°µ¤ñ³ë
³o­Ó®e¾¹¥L¬OÀHµÛ¸ê®Æ¦h¹è¦Ó¸òµÛ²£¥ÍÀx¦ìªº
³o­ÓÀx¦ì¥i¥H¥Ñ¨Ï¥ÎªÌ¦Û¦æ½s¸¹¨Óªí¥Ü
³o­Ó½s¸¹´N¬O¤Þ¼Æ¤¤ªº(index)key¡AÃöÁä¦r
¦Ü©ó³o­ÓÀx¦ì­n©ñ¸mªºªF¦è´N¬O(content)item¡A¤º®e
°Ñ¦ÒVBA»¡©ú
Dictionary ª«¥ó
               
´y­z

ª«¥ó¡A¥Î©óÀx¦s¸ê®ÆÃöÁä¦r©M¶µ¥Ø¹ï¡C

»yªk

Scripting.Dictionary

½Ðª`·N

Dictionary ¥i¥H¬O¥ô¦ó«¬¦¡ªº¸ê®Æªº¶µ¥Ø³QÀx¦s¦b°}¦C¤¤¡C¨C­Ó¶µ¥Ø³£»P¤@­Ó°ß¤@ªºÃöÁä¦r¬ÛÃö¡C¸ÓÃöÁä¦r¥Î¨Ó¨ú¥X³æ­Ó¶µ¥Ø¡A³q±`¬O¾ã¼Æ©Î¦r¦ê¡A¥i¥H¬O°£°}¦C¥~ªº¥ô¦ó«¬ºA¡C
¥Ñ¥H¤W»¡©ú¥iª¾¡A
Dictionary ´N¬O¤@­Ó®e¾¹ª«¥ó¡C
¦A¬Ý¥L¦³­þ¨Ç¤èªk?
add   ¥[¤J¶µ¥Ø
exists   Àˬd¶µ¥Ø¬O§_¦s¦b
items    ¶Ç¦^©Ò¦³¶µ¥Ø°}¦C
keys     ¶Ç¦^©Ò¦³¶µ¥Ø¤§ÃöÁä¦r°}¦C
remove   ²¾°£¶µ¥Ø
removeall   ²¾°£©Ò¦³¶µ¥Ø

VBA»¡©ú¤¤¹ïADD¤èªk¦³´£¨ì
Add ¤èªk (¥Ø¿ý)   

´y­z

¥[¤J¤@¹ï¬Û¹ïÀ³ªºÃöÁä¦r©M¶µ¥Ø¨ì Dictionary ª«¥ó¡C

»yªk

object.Add key, item

Add¤èªkªº»yªk¦³¦p¤U´X­Ó³æ¤¸¡G

³æ¤¸ ´y­z
Object ¥²­n¤Þ¼Æ¡C¤@­Ó Dictionary ª«¥óªº¦W¦r¡C
Key ¥²­n¤Þ¼Æ¡C»P©Ò¥[¤Jªº¶µ¥Ø¬ÛÃöªºÃöÁä¦r¡C
Item ¥²­n¤Þ¼Æ¡C»P©Ò¥[¤JªºÃöÁä¦r¬ÛÃöªº¶µ¥Ø¡C

½Ðª`·N

¦pªG¸ÓÃöÁä¦r¤w¸g¦s¦b¡A«h²£¥Í¤@­Ó¿ù»~¡C
©Ò¥H¡A·í¨Ï¥ÎADD¤èªk®É­Y¥¼¥ý¹ïÃöÁä¦r¬O§_¦s¦b°µÀˬd¡A­Y¹J¨ì¤w­«½Æ®É´N·|²£¥Í¿ù»~¡C
©Ò¥H§Ú¨Ï¥Îd(key)=item³o¼Ëªº»yªk¥iÁקK³o¼Ëªº³Â·Ð¡C
©Ò¥H·í¸ê®Æ¤¤¡A¦P¼ËÃöÁä¦r©Ò°Ñ·Ó¨ìªº¤º®e­Y¤£¬Û¦P®É¡A¦¹ÃöÁä¦rÀx¦ìªº¤º®e±N·|³Q³Ì«á«ü©wªº­È¨ú¥N¡C
©Ò¥H¡Adictionaryª«¥óªºcountÄݩʳ̲³æªº»¡ªk´N¬O¡Adictionaryª«¥óªºÃöÁä¦r¼Æ¶q¡C

½Ðª`·N¸Ó³sµ²¤å³¹¤¤»yªkªº»¡©ú
dictionary_object.Add index, content
index´N¬O¯Á¤Þ­Èkey
content´N¬O¤º®eitem
d1(C) = ""³o´N¬O«ü©wÃöÁä¦rCªº¨º­ÓÀx¦ì¯dªÅ«O¦s
©Ò¥H§Aªº¸ÑÄÀ¨S¿ù¡A­Yc¤£¦s¦bd1.COUNT´N·|¥[1
¾Ç®üµL²P_¤£®¢¤U°Ý

TOP

        ÀR«ä¦Û¦b : ¯¸¦b¥b¸ô¡A¤ñ¨«¨ì¥Ø¼Ð§ó¨¯­W¡C
ªð¦^¦Cªí ¤W¤@¥DÃD