[µo°Ý]±N¨â°}¦C¨Ì¶¶§Ç¦X¨Ö°ÝÃD
©«¤l 77 ¥DÃD 11 ºëµØ 0 ¿n¤À 118 ÂI¦W 0 §@·~¨t²Î winXP ³nÅ骩¥» office2007 ¾\ŪÅv 20 ©Ê§O ¨k µù¥U®É¶¡ 2010-12-24 ³Ì«áµn¿ý 2011-3-2
[µo°Ý]±N¨â°}¦C¨Ì¶¶§Ç¦X¨Ö°ÝÃD
¦U¦ì¤j¤j±z̦n
¦pªG·Q±N¨â°}¦C¨Ì¥Ñ¤p¦Ó¤j¶¶§Ç¦X¨Ö°ÝÃD
¦p¡G
A 8 4 6 B 7 5 3 9 C 3 4 5 6 7 8 9
3 4 5 4 2 1 3 1 4 2 5 4 3 3
±NA¡AB¨â°}¦C¦X¨Ö¦¨C°}¦C¡A¨Ã±N¨ä¾l¬Û¹ïÀ³¸ê®Æ§Û¿ý¡A
À³¸Ó¦p¦ó¸Ñ¨M
·PÁ¦U¦ìªº¨ó§U
ASUS
©«¤l 1446 ¥DÃD 40 ºëµØ 0 ¿n¤À 1470 ÂI¦W 0 §@·~¨t²Î Windows 7 ³nÅ骩¥» Excel 2010 & 2016 ¾\ŪÅv 50 ©Ê§O ¨k ¨Ó¦Û ¥xÆW µù¥U®É¶¡ 2020-7-15 ³Ì«áµn¿ý 2024-10-21
¦^´_ 21# Hsieh
ÁÂÁ«e½ú,ÁÂÁ½׾Â
«á¾ÇÂǦ¹©«½m²ß°}¦C¦b¦r¨å¸Ì§]¦R¤§¶¡½s¿è°}¦CÈ»P Àx¦s®æ¦P¦r¤¸¼Æ¾î¦V±Æ§Ç,½m²ß¤è®×¦p¤U
½Ð«e½úÌ«ü±Ð
¸ê®Æªí(¤u§@ªí1):
¸ê®Æªí(¤u§@ªí2):
µ²ªGªí(¤u§@ªí3):
Option Explicit
Sub TEST_2()
Application.ScreenUpdating = False
Dim Y, N&, i&, j&, A, C%
Set Y = CreateObject("Scripting.Dictionary")
Sheets(3).UsedRange.Clear
For i = 1 To 2
Y("ªí" & i) = Sheets(i).Range("A1").CurrentRegion: A = Y("ªí" & i)
Y(i & "/R") = UBound(A): Y(i & "/C") = UBound(A, 2)
For j = 1 To Y(i & "/C")
N = N + 1: A(1, j) = Format(A(1, j), "000") & "|" & Format(N, "000")
Next
Y("ªí" & i) = A
Next
Sheets(3).[A1].Resize(Y("1/R"), Y("1/C")) = Y("ªí1")
Sheets(3).[A1].Item(1, Y("1/C") + 1).Resize(Y("2/R"), Y("2/C")) = Y("ªí2")
With Sheets(3).UsedRange
.Sort Key1:=.Item(1), Order1:=xlAscending, Header:=xlNo, _
Orientation:=xlLeftToRight
Intersect([1:1], .Cells).Replace "|*", "", Lookat:=xlPart
End With
Set Y = Nothing
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y
©«¤l 77 ¥DÃD 11 ºëµØ 0 ¿n¤À 118 ÂI¦W 0 §@·~¨t²Î winXP ³nÅ骩¥» office2007 ¾\ŪÅv 20 ©Ê§O ¨k µù¥U®É¶¡ 2010-12-24 ³Ì«áµn¿ý 2011-3-2
·PÁÂHsieh¡BFAlonso¨â¦ì¤j¤j
§Ú±q³oÓ°Q½×ªº¹Lµ{¤¤¦¬Ã¬«Ü¦h
·|¦Aªá®É¶¡¦n¦n§l¦¬¨ä¤¤ºëµØ
¦Ó¥Bµ{¦¡½Xȱo¨åÂáA¥H«á¥²µM©|¦³¾÷·|¥Î¨ì
·PÁ¦A¤T
ASUS
©«¤l 4901 ¥DÃD 44 ºëµØ 24 ¿n¤À 4916 ÂI¦W 255 §@·~¨t²Î Windows 7 ³nÅ骩¥» Office 20xx ¾\ŪÅv 150 ©Ê§O ¨k ¨Ó¦Û ¥x¥_ µù¥U®É¶¡ 2010-4-30 ³Ì«áµn¿ý 2024-11-25
¦^´_ 16# asus103 Sub Dic_Sort()
Dim C()
Set d = CreateObject("Scripting.Dictionary") '
Set d1 = CreateObject("Scripting.Dictionary") '¦P¯Á¤Þ¶µ¥Ø¼Æ¶qp¼Æ¾¹
'(ª`·N)¤u§@ªí1¸ò¤u§@ªí2ªº¸ê®Æ¦C¼Æn¬Û¦P
a = Sheets(1).Range("A1").CurrentRegion: B = Sheets(2).Range("A1").CurrentRegion '¼g¤JA¡BB°}¦C¤º®e
For Each y In Array(a, B) '¥H°j°é¶¶§ÇŪ¤JA¡BB°}¦C¨ì¦r¨åª«¥ó
For i = LBound(y, 2) To UBound(y, 2)
d(y(1, i) + d1(y(1, i)) * 0.1) = Application.Transpose(Application.Index(y, , i)) '¦]¬°¯Á¤Þȳ£¬O¾ã¼Æ¡A©Ò¥H¯Á¤ÞÈ¥[p¼Æªº0.1¿·í¦¨·s¯Á¤ÞÈ¡AÁקK»P¨ä¥L¯Á¤ÞÈ«½Æ¡A¹ïÀ³2¦CªºÈ
d1(y(1, i)) = d1(y(1, i)) + 1 '¦P¯Á¤ÞÈp¼Æ
Next
Next
Do Until d.Count = 0 '¶i¦æ°j°é¡Aª½¨ì¦r¨å¤º®e¼Æ¶q¬°0¸õ¥X°j°é
ky = Application.Small(d.keys, 1) '±o¨ì¯Á¤ÞÈ°}¦C¤¤³Ì¤pÈ
'ky = Application.Min(d.keys) '±o¨ì¯Á¤ÞÈ°}¦C¤¤³Ì¤pÈ¡A¥ç¥i¨Ï¥ÎMIN¨ç¼Æ
ReDim Preserve C(s)
C(s) = d(ky) '±N³Ì¤pȪº¤º®e¦s¤J°}¦C
s = s + 1
d.Remove ky '²¾°£¦r¨å¤¤³Ì¤pȪº¶µ¥Ø¡A¦¹®É¦r¨å¤º®e¼Æ¶q·|´î¤Ö1Ó
Loop
Sheets(3).[A1].Resize(UBound(a, 1), s) = Application.Transpose(C) '쥻C°}¦C¥iµø¬°s¦C(°}¦CAªº¦C¼Æ)Äæ¡A©Ò¥HÂà¸m«á¦¨¬°(°}¦CAªº¦C¼Æ)¦CsÄæ¡A¼g¤J¤u§@ªí
Set d = Nothing 'ÄÀ©ñª«¥ó
Set d1 = Nothing 'ÄÀ©ñª«¥ó
End Sub ½Æ»s¥N½X
Array_Sort.zip (11.64 KB)
¾Ç®üµL²P_¤£®¢¤U°Ý
©«¤l 135 ¥DÃD 19 ºëµØ 0 ¿n¤À 165 ÂI¦W 0 §@·~¨t²Î XP ³nÅ骩¥» 2003 ¾\ŪÅv 20 ©Ê§O ¨k ¨Ó¦Û »´ä µù¥U®É¶¡ 2010-12-9 ³Ì«áµn¿ý 2012-8-27
Sub merge_rank2()
Dim myobject As Object, myobject2 As Object
Dim myrange As Range
Dim i As Integer, j As Integer, k As Integer, myrow As Integer
Dim mykey
Set myobject = CreateObject("scripting.dictionary")
Set myobject2 = CreateObject("scripting.dictionary")
myrow = Sheet4.Range("A65536").End(xlUp).Row
With Sheet4
For Each myrange In .Range(.[a1], .[a1].End(xlToRight))
myobject(myrange.Value) = myobject(myrange.Value) + 1
For j = 2 To myrow
myobject2(myrange.Value & "," & myobject(myrange.Value) & "," & j) = myrange.Offset(j - 1).Value
Next
Next
End With
With Sheet5
.Activate
.Range("a1").Activate
For i = 1 To myobject.Count
For j = 1 To myobject(Application.Small(myobject.keys, i))
ActiveCell.Value = Application.Small(myobject.keys, i)
For k = 2 To myrow
ActiveCell.Offset(k - 1, 0).Value = myobject2(Application.Small(myobject.keys, i) & "," & j & "," & k)
Next
ActiveCell.Offset(0, 1).Select
Next
Next
End With
Set myobject = Nothing
Set myobject2 = Nothing
End Sub ½Æ»s¥N½X ³oÓ¬OÀu¤Æµ{¦¡,²Ä¤@¦æ«ÂФ]¥i¨Ï¥Î
§Ú¨ì¦¹¬°¤î¤F.....
80 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X
©«¤l 135 ¥DÃD 19 ºëµØ 0 ¿n¤À 165 ÂI¦W 0 §@·~¨t²Î XP ³nÅ骩¥» 2003 ¾\ŪÅv 20 ©Ê§O ¨k ¨Ó¦Û »´ä µù¥U®É¶¡ 2010-12-9 ³Ì«áµn¿ý 2012-8-27
¥»©«³Ì«á¥Ñ FAlonso ©ó 2011-1-15 14:13 ½s¿è
¬Ý²Ä20¶,¨ºÓ¬O³Ì²×µ{¦¡
80 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X
©«¤l 4901 ¥DÃD 44 ºëµØ 24 ¿n¤À 4916 ÂI¦W 255 §@·~¨t²Î Windows 7 ³nÅ骩¥» Office 20xx ¾\ŪÅv 150 ©Ê§O ¨k ¨Ó¦Û ¥x¥_ µù¥U®É¶¡ 2010-4-30 ³Ì«áµn¿ý 2024-11-25
¥»©«³Ì«á¥Ñ Hsieh ©ó 2011-1-15 18:23 ½s¿è
¦^´_ 17# FAlonso
§@ªk«ä¦ÒÅÞ¿è¤@¼Ë¡A±Æ§Ç¬O¥ÎSMALL¨ç¼Æ¡A¥i±NKEY¤@¤@²¾°£¡A¨ú³Ì¤pÈ¡A©Ò¥HY¥ÎMIN¤]¬O¦P¼Ë®ÄªGSub Dic_Sort()
Dim C()
Set d = CreateObject("Scripting.Dictionary") '
Set d1 = CreateObject("Scripting.Dictionary") '¦P¯Á¤Þ¶µ¥Ø¼Æ¶qp¼Æ¾¹
A = Sheets(1).[A1:J2]: B = Sheets(2).[A1:J2] '¼g¤JA¡BB°}¦C¤º®e
For Each y In Array(A, B) '¥H°j°é¶¶§ÇŪ¤JA¡BB°}¦C¨ì¦r¨åª«¥ó
For i = LBound(y, 2) To UBound(y, 2)
d(y(1, i) + d1(y(1, i)) * 0.1) = Array(y(1, i), y(2, i)) '¦]¬°¯Á¤Þȳ£¬O¾ã¼Æ¡A©Ò¥H¯Á¤ÞÈ¥[p¼Æªº0.1¿·í¦¨·s¯Á¤ÞÈ¡AÁקK»P¨ä¥L¯Á¤ÞÈ«½Æ¡A¹ïÀ³2¦CªºÈ
d1(y(1, i)) = d1(y(1, i)) + 1 '¦P¯Á¤ÞÈp¼Æ
Next
Next
Do Until d.Count = 0 '¶i¦æ°j°é¡Aª½¨ì¦r¨å¤º®e¼Æ¶q¬°0¸õ¥X°j°é
ky = Application.Small(d.keys, 1) '±o¨ì¯Á¤ÞÈ°}¦C¤¤³Ì¤pÈ
'ky = Application.Min(d.keys) '±o¨ì¯Á¤ÞÈ°}¦C¤¤³Ì¤pÈ¡A¥ç¥i¨Ï¥ÎMIN¨ç¼Æ
ReDim Preserve C(s)
C(s) = d(ky) '±N³Ì¤pȪº¤º®e¦s¤J°}¦C
s = s + 1
d.Remove ky '²¾°£¦r¨å¤¤³Ì¤pȪº¶µ¥Ø¡A¦¹®É¦r¨å¤º®e¼Æ¶q·|´î¤Ö1Ó
Loop
Sheets(3).[A1].Resize(2, s) = Application.Transpose(C) '쥻C°}¦C¥iµø¬°s¦C2Äæ¡A©Ò¥HÂà¸m«á¦¨¬°2¦CsÄæ¡A¼g¤J¤u§@ªí
Set d = Nothing 'ÄÀ©ñª«¥ó
Set d1 = Nothing 'ÄÀ©ñª«¥ó
End Sub ½Æ»s¥N½X
¾Ç®üµL²P_¤£®¢¤U°Ý
©«¤l 135 ¥DÃD 19 ºëµØ 0 ¿n¤À 165 ÂI¦W 0 §@·~¨t²Î XP ³nÅ骩¥» 2003 ¾\ŪÅv 20 ©Ê§O ¨k ¨Ó¦Û »´ä µù¥U®É¶¡ 2010-12-9 ³Ì«áµn¿ý 2012-8-27
[ª©¥DºÞ²z¯d¨¥] Hsieh(2011-1-15 11:59): ¤w¶}©ñ¤U¸ü¡Aµ{¦¡½X¤]Åã¥Ü¤F
°Ñ¦Ò¬Ý¬Ý
¦hÁ°ѻP°Q½×
¦^´_ 14# Hsieh Sub merge_rank()
Dim myobject As Object, myobject2 As Object
Dim myrange As Range
Dim i As Integer, j As Integer
Set myobject = CreateObject("scripting.dictionary")
Set myobject2 = CreateObject("scripting.dictionary")
With Sheet4
For Each myrange In .Range(.[a1], .[a1].End(xlToRight))
myobject(myrange.Value) = myobject(myrange.Value) + 1 'myrange.value¬°²Ä1 row¬Y¼Æ¦r,myobject§@¬°p¼Æ¾¹
myobject2(myrange.Value & "," & myobject(myrange.Value)) = myrange.Offset(1).Value myobject2¿é¤J²Ä2 rowªº¼Æ¦r(index¬°myrange.Value & "," & myobject(myrange.Value,§Y¼Æ¦r¤Î¨ä¥X²{¦¸¼Æ)
Next
End With
With Sheet5
.Activate
.Range("a1").Activate
For i = 1 To myobject.Count '¥ý¼Æ¦³¦h¤Öµ§¤£¦Pªº¸ê®Æ
For j = 1 To myobject(Application.Small(myobject.keys, i)) '¥ý±Æ¦C,¦A§ä¥X¨ä¥X²{¦¸¼Æ
ActiveCell.Value = Application.Small(myobject.keys, i)
ActiveCell.Offset(1, 0).Value = myobject2(Application.Small(myobject.keys, i) & "," & j)
ActiveCell.Offset(0, 1).Select
Next
Next
End With
Set myobject = Nothing
Set myobject2 = Nothing
End Sub ½Æ»s¥N½X
80 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X
©«¤l 77 ¥DÃD 11 ºëµØ 0 ¿n¤À 118 ÂI¦W 0 §@·~¨t²Î winXP ³nÅ骩¥» office2007 ¾\ŪÅv 20 ©Ê§O ¨k µù¥U®É¶¡ 2010-12-24 ³Ì«áµn¿ý 2011-3-2
¥»©«³Ì«á¥Ñ asus103 ©ó 2011-1-15 03:15 ½s¿è
¦^´_ 13# FAlonso
·PÁ±zFAlonso¤j¤j
±zªºIDEAªº½T«Ü§®¡A²`·P¨ØªA
§Ú·|®Ú¾Ú³oÓ·Qªk¦A¨Ó¬Ý¬Ý§Úªºµ{¦¡ªº§ï¶iªÅ¶¡
§Úªº°}¦C¤¤²Ä1ºûªº½T¬O°ß¤@
¦ý¬O¨C¤@Ó¸ê®Æ¤§¤U«o¤£¥u1Ó
§Y
A 8 4 6 B 7 5 3 9 C 3 4 5 6 7 8 9
3 4 5 4 2 1 3 1 4 2 5 4 3 3
1 2 3 5 4 3 2 . . . . . . . . . ..
«D±`·PÁ±zªº¨ó§U
ASUS
©«¤l 135 ¥DÃD 19 ºëµØ 0 ¿n¤À 165 ÂI¦W 0 §@·~¨t²Î XP ³nÅ骩¥» 2003 ¾\ŪÅv 20 ©Ê§O ¨k ¨Ó¦Û »´ä µù¥U®É¶¡ 2010-12-9 ³Ì«áµn¿ý 2012-8-27
[ª©¥DºÞ²z¯d¨¥] Hsieh(2011-1-14 20:32): ¥ý´£¥X§Aªº¸Ñ¨M¤è¦¡¡A¦A¸Ñ¶}Åv
80 ¦r¸`¥H¤º
¤£¤ä«ù¦Û©w¸q Discuz! ¥N½X