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

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

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

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

±z¦n¡G
§Ú¬OVBAªì¾ÇªÌ¡A²{¦b»Ý­n§â¤@¥÷Âø¶Ãªº­ì©l³øªí¡A¾ã²z¦¨©Ò»Ý«¬¦¡ªº¤u§@ªí(¦pªþ¥ó)
½Ð°Ý°£¤F¤gªk·Ò¿û(¥X¿ù²v¸û°ª)¥H¥~¡A¯à§_¦³¦Û°Ê³B²zªº¤è¦¡¡A¤]·í§@§Ú¾Ç²ß¤Wªº¤@­Ó½d¥»¡C
§Ú·Q³o¬O¤@­Ó¯E¤jªº¤uµ{¡C
·PÁ±zªþµù¡G
1.¦U¯Z¥i¯à·|¤Á¦¨¨â³¡¤À
2.¦U¯Z¿ï­×¬ì¥Ø¤]¤£¬Û¦P
3.¦U¯Z¤H¼Æ¥i¯à·|§ïÅÜ
4.©Ò»Ý±Æ¦C¤è¦¡¦pSHEET4

score.rar (77.52 KB)

ASUS

ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾ÇÂǦ¹©«½m²ß°}¦C»P¦r¨å,¾Ç²ßªº¤è®×¦p¤U,½Ð¦U¦ì«e½ú«ü±Ð
°õ¦æµ²ªG:


Option Explicit
Sub TEST()
Dim Brr, Crr, Z, i&, j%, C%, N&, X%, T$, ¯Z¯Å$
¤u§@ªí2.UsedRange.ClearContents
Set Z = CreateObject("Scripting.Dictionary")
Brr = Range(¤u§@ªí1.[N1], ¤u§@ªí1.[B65536].End(3)(1, 0))
ReDim Crr(UBound(Brr), 1 To 20)
For i = 1 To UBound(Brr)
   Z(Application.Text(i, "[DBNum1]")) = i
   If Brr(i, 2) = "¾Ç¡@¸¹" Then
      ¯Z¯Å = Z(Mid(Brr(i - 2, 2), 2, 1)) & Mid(Brr(i - 2, 2), 4, 2)
      For j = 5 To UBound(Brr, 2)
         T = Brr(i, j): If T = "" Then C = j - 1: Exit For
         If Not Z.Exists(T) Then X = X + 1: Z(T) = X + 4: Crr(0, X + 4) = T
         Z(j) = Z(T)
      Next
   End If
   If Brr(i, 2) Like "######" Then
      N = N + 1: For j = 5 To 20: Crr(N, j) = -1: Next
      Crr(N, 1) = Brr(i, 2): Crr(N, 2) = Brr(i, 3): Crr(N, 3) = ¯Z¯Å: Crr(N, 4) = Brr(i, 4)
      For j = 5 To C: Crr(N, Z(j)) = Brr(i, j): Next
   End If
Next
With ¤u§@ªí2.[A1].Resize(N + 1, X + 4): .NumberFormat = "@": .Value = Crr: End With
¤u§@ªí2.[A1].Resize(, 4) = [{"¾Ç¡@¸¹","©m¦W","¯Z¯Å","®y¸¹"}]
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

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

¦^´_ 20# Hsieh
Hsieh¤j¤j¡G
·PÁ±z
¤§«e§Ú»~¥H¬°d1(C) = ""¤¤C¬Ocontent
§Ú·Q¥¦À³¸Ó¬Oindex§a¡H
¦pªG¬O³o¼Ë¡A§ÚÀ³¸Ó¬Ý±oÀ´¤F

°²¦p§Ú»¡ªº¨S¿ù¡A±z´N¤£¥²®ö¶O±zÄ_¶Qªº®É¶¡¦^ÂÐ
­Y§Úªº·Qªk¦³¿ù¡A¦b³Â·Ð±z§iª¾

¦ýÄ@¦³¤@¤Ñ§Ú¤]¯à¼g¥X³o¼Ë¦³³Ð·Nªºµ{¦¡
µL­­ªº·P¿E
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

¥»©«³Ì«á¥Ñ 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

¦^´_ 17# asus103

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

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

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

TOP

§ï³o¦æ
d(A & C) = IIf(s > 2, "", "'") & A.Offset(, s).Text
¾Ç®üµ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

        ÀR«ä¦Û¦b : ·O´d¨S¦³¼Ä¤H¡A´¼¼z¤£°_·Ð´o¡C
ªð¦^¦Cªí ¤W¤@¥DÃD