- ©«¤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-11-28
|
¦^´_ 6# Andy2483
¥H¤U¬O½Æ²ß¤ß±oµù¸Ñ
Option Explicit
Sub ¸ü¤J_1()
Dim Arr, Brr(1 To 14), Z, A, T$, i&, j%, q%
'¡ô«Å§iÅܼÆ:(Arr,Z,A)¬O³q¥Î«¬ÅܼÆ,T¬O¦r¦êÅܼÆ,(i)¬Oªø¾ã¼Æ
'(j,q)¬Oµu¾ã¼Æ,Brr¬O¤@ºûªÅ°}¦C,¯Á¤Þ¸¹1~14
Sheets("¶×Á`").UsedRange.Offset(4).ClearContents
'¡ô¥O"¶×Á`"¤u§@ªí¤¤²[»\¤w¨Ï¥ÎÀx¦s®æªº³Ì¤p¤è¥¿°Ï°ìÀx¦s®æ½d³ò,
'¦¹½d³ò¦V¤U°¾²¾4¦Cªº·s½d³ò²M°£¤º®e
Set Z = CreateObject("Scripting.Dictionary")
'¡ô¥OZ³o³q¥Î«¬ÅܼƬO ¦r¨å
For q = 1 To Sheets.Count
'¡ô³]¶¶°j°é!¥Oq³oµu¾ã¼Æ±q1 ¨ì³o¬¡¶Ã¯ªº¤u§@ªí¼Æ¶q¼Æ
If Trim(Sheets(q).[A5]) = "" Then GoTo q01
'¡ô¦pªGq°j°é¼Æ¤u§@ªíªº[A5]Àx¦s®æÈ¥h°£ÀY§ÀªÅ¦r¤¸«áªº·s¦r¦ê¬OªÅ¦r¤¸,
'¦pªG¬OªÅ¦r¤¸´N¸õ¨ì¼Ð¥Ü q01¦ì¸mÄ~Äò°õ¦æ
Arr = Range(Sheets(q).[n1], Sheets(q).[a65536].End(3))
'¡ô¥OArr³o³q¥Î«¬ÅܼƬO¤Gºû°}¦C,¥Hq°j°é¯Á¤Þ¸¹¤u§@ªíªº[N1]¨ìAÄæ³Ì«á¦³¤º®eÀx¦s®æ,
'¥O³oÀx¦s®æ½d³òȱa¤JArr°}¦C¤¤
For i = 5 To UBound(Arr)
'¡ô³]¶¶°j°é!¥OiÅܼƱq1¨ì Arr°}¦CÁa¦V³Ì¤j¯Á¤Þ¦C¸¹
T = Trim(Arr(i, 1)) & "|" & Trim(Arr(i, 2))
'¡ô¥OT³o¦r¦êÅܼƬO ¥H"|"²Å¸¹³s±µi¦C1/2ÄæArr°}¦CÈ¥h°£ÀY§ÀªÅ¥Õ¦r¤¸ªº²Õ¦X·s¦r¦ê
A = Z(T)
'¡ô¥OA³o³q¥Î«¬ÅܼƬO ¥HTÅܼƬdZ¦r¨å±a¥X¨Óªºitem
If Not IsArray(A) Then
'¡ô¦pªGAÅܼƤ£¬O°}¦C??
A = Brr
'¡ô¥OAÅܼƬO ¦PBrr°}¦C¤j¤pªº¤@ºûªÅ°}¦C
A(1) = Trim(Arr(i, 1)): A(2) = Trim(Arr(i, 2))
'¡ô¥OA°}¦C1¯Á¤Þ¸¹°}¦CȬO i°j°é¦C1ÄæArr°}¦CÈ,¥h°£ÀY§ÀªÅ¥Õ¦r¤¸ªº·s¦r¦ê
'¡ô¥OA°}¦C2¯Á¤Þ¸¹°}¦CȬO i°j°é¦C2ÄæArr°}¦CÈ,¥h°£ÀY§ÀªÅ¥Õ¦r¤¸ªº·s¦r¦ê
End If
For j = 3 To UBound(Arr, 2)
'¡ô³]¶¶°j°é!¥OjÅܼƱq3¨ì Arr°}¦C¾î¦V³Ì¤j¯Á¤ÞÄ渹¼Æ
A(j) = A(j) + Val(Arr(i, j))
'¡ô¥OA°}¦Cj°j°é¼Æ¯Á¤Þ¸¹°}¦CȬO ²Ö¥[i°j°é¦Cj°j°éÄæArr°}¦CÈÂà¤Æ¦¨ªº¼ÆÈ
Next
Z(T) = A
'¡ô¥OTÅܼÆkey¥H ·sªºA°}¦C©ñ¦^Z¦r¨å¤¤
Next
q01: Next
'------------------------------
With Sheets("¶×Á`").[A5].Resize(Z.Count, 14)
'¡ô¥H¤U¬OÃö©ó"¶×Á`"¤u§@ªíªº[A5]ÂX®i¦V¤UZ¦r¨åkey¼Æ¦C,ÂX®i¦V¥k14Äæ½d³òÀx¦s®æ,
'Ãö©ó¦¹½d³òÀx¦s®æµ{§Ç
.Value = Application.Transpose(Application.Transpose(Z.Items))
'¡ô¥O¸Ó½d³òÀx¦s®æÈ¥HZ¦r¨åitemÂà¸m¨â¦¸ªº°}¦Cȱa¤J
.Sort KEY1:=.Item(1), Order1:=1, KEY2:=.Item(2), Order1:=1, Header:=2
'¡ô¥O¸Ó½d³òÀx¦s®æ°µ2¼h¦¸µL¼ÐÃD¦Cªº¥¿±Æ§Ç,²Ä1¼h¬O²Ä1Äæ,²Ä2¼h¬O²Ä2Äæ
.Columns(7) = "=rank(F5," & .Columns(6).Address(1, 1) & ")"
'¡ô¥O²Ä7ÄæȬO ²Ä6Ä檺±Æ¦W¤½¦¡
.Columns(14) = "=rank(M5," & .Columns(13).Address(1, 1) & ")"
'¡ô¥O²Ä14ÄæȬO ²Ä13Ä檺±Æ¦W¤½¦¡
End With
End Sub |
|