ªð¦^¦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

¦^´_ 1# asus103
  1. Option Explicit
  2. Sub Ex()
  3.     Dim D(1) As Object, F As Range, MyClass$, F_Address$, Rng As Range, C, R, D_Key$, ARng As Range
  4.     Set D(0) = CreateObject("SCRIPTING.DICTIONARY")
  5.     Set D(1) = CreateObject("SCRIPTING.DICTIONARY")
  6.     With Sheets("Sheet1")
  7.         Set F = .Range("B:B").Find(what:="¾Ç¡@¸¹", After:=.[b1], Lookat:=xlWhole)
  8.         If Not F Is Nothing Then
  9.             F_Address = F.Address
  10.             Do
  11.                 Set Rng = .Range(F, F.End(xlToRight).End(xlDown))
  12.                 MyClass = F.Offset(-2)
  13.                 For Each C In Rng.Columns(1).Cells
  14.                     If IsNumeric(C) Then
  15.                         D_Key = C & "," & C(1, 2) & "," & MyClass & "," & C(1, 3)
  16.                         D(0)(D_Key) = ""
  17.                         For R = 4 To Rng.Columns.Count
  18.                             If Rng(1, R) <> "" Then D(1)(D_Key & Rng(1, R)) = .Cells(C.Row, Rng(1, R).Column)
  19.                         Next
  20.                     End If
  21.                 Next
  22.                 Set F = .Range("B:B").FindNext(F)
  23.             Loop While F_Address <> F.Address
  24.             With Sheets("Sheet4")
  25.                 .UsedRange.Offset(1).Clear
  26.                 For Each R In D(0).KEYS
  27.                     Set ARng = .Range("A" & Rows.Count).End(xlUp).Offset(1)
  28.                     ARng.Resize(, 4) = Split(R, ",")
  29.                     For C = 5 To .[A1].End(xlToRight).Column
  30.                         If .Cells(1, C) <> "" Then ARng(1, C) = D(1)(R & .Cells(1, C))
  31.                     Next
  32.                 Next
  33.             End With
  34.         End If
  35.     End With
  36. End Sub
½Æ»s¥N½X

TOP

GBKEE¡G
«D±`·PÁ±z!!!!
§Ú²{¦b°¨¤W³B²z¡A¥ý·PÁ±zªº¶O¤ß
ASUS

TOP

¦^´_ 2# GBKEE
¦A¤@¦¸·PÁ±z
²{¦b°ò¥»¸ê®Æ¶i¨Ó¤F
¥u¬O¦Û¤vµ{«×ÁÙ¤£°÷¡A©|µLªk§¹¥þ¬ÝÀ´¡A§Ú·|¦A¥[ªo¬ÝÀ´¥¦

¨º¦¨ÁZ³¡¤À§Ú­Y¬O¥Îvlookup³B²z·|¹J¨ì¨â­Ó°ÝÃD
1.­ì¸ê®Æ¨Ã«D§¹¾ã°Ï¶ô¡A¤¤¶¡¦³³\¦hªÅ¥Õ
2.¦U¯Z¤Î¬ì¥Ø¦ì¸m¨Ã¤£¬Û¦P
½Ð°Ý¦³¤èªk§â¦¨ÁZÂಾ¹L¨Ó¶Ü?
ÁÂÁÂ
ASUS

TOP

¦^´_ 4# asus103
ªþÀɤW¨Ó¬Ý¬Ý

TOP

¦^´_ 5# GBKEE


GBKEE±z¦n
­ì¨ÓªºÀɮפ¤¡A§Y¦³¦¨ÁZªº³¡¤À
¥u¬O¦U¯Z(¦p5¯Z¥H«á)¿ï­×¬ì¥Ø¨Ã¤£¬Û¦P¡A
©Ò¥H§ÚµLªk¦Û°Ê§P§O¨ú¥X

¥t¡A§ÚªºÅv­­µLªk¬Ý¨ì§Ú¦Û¤vªºªþÀÉ
­YÀɮצ³°ÝÃD·Ð½Ð¦A§iª¾
ÁÂÁÂ!!!
ASUS

TOP

¦^´_ 6# asus103
¦p¹Ï ¥u­n¸ê®ÆªºBÄæ ¤º [¯Z¯Å] ¦b [¾Ç¸¹] ªº¤W2¦C  µ{¦¡À³¥iÀ³¥Iªº

TOP

¦^´_ 3# 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¯Å") = 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) = 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 12:06 ½s¿è

°J¤ß·PÁ¨â¦ìª©¥Dªº¹©¤O¨ó§U
§Ú­ì¥H¬°®£©È»Ý­n«Üªøªºµ{¦¡½X¤~¯à¸Ñ¨Mªº
¨â¦ìª©¥D¤ÆÁc¬°Â²ªº¥\¤O¯u¥O§Ú¨ØªA¡A¦Ó¥B¬O¦b³o»òµuªº®É¶¡¤º
·P¿Eªü!!
§Ú¥ý¨Ï¥Î¤F¡A¤§«á§Ú·|§V¤O¬ÝÀ´¨Ã¾Ç²ßªº

©|¦³2­Ó¤p°ÝÃD½Ð±Ð¡G
1.­Y¬O§â¯Z¯ÅÄæ§ï¬°¯Â¼Æ¦r¡A¬O§_¬O¤â°Ê§Y¥i
2.­Y·Q¦b¥¼¿ï­×ªº¦ì¸m¤W¶ñ¤W"-1"¡A¨ºÀ³¸Ó¦b­þ¤@¦æ¥[¤W¨Ç¬Æ»ò?
ÁÂÁÂ
ASUS

TOP

¦^´_ 8# Hsieh
Hsieh¤j¤j±z¦n
°J¤ß·PÁ±zªºÀ°§U

©|¦³2­Ó¤p°ÝÃD½Ð±Ð¡G
1.­Y¬O§â¯Z¯ÅÄæ§ï¬°¯Â¼Æ¦r¡A¬O§_¬O¤â°Ê§Y¥i
2.­Y·Q¦b¥¼¿ï­×ªº¦ì¸m¤W¶ñ¤W"-1"¡A¨ºÀ³¸Ó¦b­þ¤@¦æ¥[¤W¨Ç¬Æ»ò?
ÁÂÁÂ
ASUS

TOP

        ÀR«ä¦Û¦b : ¡i¬O§_µo´§¤F¨}¯à¡H¡j¤H¶¡¹Ø©R¦]¬°µu¼È¡A¤~§óÅã±o¬Ã¶Q¡CÃø±o¨Ó¤@½ë¤H¶¡¡AÀ³°Ý¬O§_¬°¤H¶¡µo´§¤F¦Û¤vªº¨}¯à¡A¦Ó¤£­n¤@¨ý¨Dªø¹Ø¡C
ªð¦^¦Cªí ¤W¤@¥DÃD