- ©«¤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
|
¦^´_ 1# dragonbx
ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾ÇÂǦ¹¥DÃD½m²ß°}¦C»P¦r¨å,½Ð¦U¦ì«e½ú¦A«ü¾É
20230315_2.zip (12.35 KB)
°õ¦æ«e:
°õ¦æµ²ªG:
Option Explicit
Sub Test()
Dim Brr, Crr, Y, i&, R&, M&, T2%, C%, T1$
'¡ô«Å§iÅܼÆ:(Brr,Crr,Y)¬O³q¥Î«¬ÅܼÆ,(i,R,M)¬Oªø¾ã¼ÆÅܼÆ,
'(T2,C)¬Oµu¾ã¼ÆÅܼÆ,T1¬O¦r¦êÅܼÆ
Set Y = CreateObject("Scripting.Dictionary")
'¡ô¥OY³o³q¥Î«¬ÅܼƬO ¦r¨å
Set Brr = Sheets(1).UsedRange: Brr.Offset(0, 4).ClearContents
'¡ô¥OBrr³o³q¥Î«¬ÅܼƬO¯Á¤Þ¸¹1¤u§@ªí¦³¨Ï¥ÎªºÀx¦s®æ,
'¥OBrrÅܼƦV¥k°¾²¾4Ä檺Àx¦s®æ¤º®e²M°£
Brr = Brr: For i = 2 To UBound(Brr): Y(Val(Brr(i, 1))) = "": Next
'¡ô¥OBrr¬O¤Gºû°}¦C,¥HBrrÅܼÆȱa¤J
'³]¶¶°j°é!i±q2¨ì Brr°}¦CÁa¦V³Ì¤j¯Á¤Þ¦C¸¹
'¥Oi°j°é¦C²Ä1ÄæBrr°}¦CȸgÂà¤Æ¬°¼ÆÈ·íkey,item¬OªÅ¦r¤¸,¯Ç¤JY¦r¨å
ReDim Crr(1 To UBound(Brr), 1 To Y.Count + 1): Y.RemoveAll
'¡ô«Å§iCrr¬O¤Gºû°}¦C,Áa¦V½d³ò(1¨ìBrr°}¦CÁa¦V³Ì¤j¯Á¤Þ¦C¸¹)¦C,
'¾î¦V½d³ò(1¨ìY¦r¨åkey¼Æ¶q+1)Äæ
'²MªÅY¦r¨å
For i = 2 To UBound(Brr)
'¡ô³]¶¶°j°é!i±q2¨ì Brr°}¦CÁa¦V³Ì¤j¯Á¤Þ¸¹
T1 = Val(Brr(i, 1)): T2 = Brr(i, 2)
'¡ô¥OT1³o¦r¦êÅܼƬO i°j°é¦C²Ä1ÄæBrr°}¦CȸgÂà¤Æ¬°¼ÆȪº·s¦r¦ê
If Not Y.Exists(T1 & "/C") Then
'¡ô¦pªGT1ÅܼƳs±µ"/C"¦¨ªº¦r¦ê¦bY¦r¨å¸Ì¨S¦³¦¹key?
C = C + 1: Y(T1 & "/C") = C: Crr(1, C + 1) = T1
'¡ô¥OC³oµu¾ã¼ÆÅܼƲ֥[1
'¥OT1ÅܼƳs±µ"/C"¦¨ªº¦r¦ê·íkey,item¬O CÅܼÆ,¯Ç¤JY¦r¨å¸Ì
'¥O1¦C²Ä(CÅܼÆ+1)ÄæCrr°}¦CȬO T1ÅܼÆ
End If
R = Y(T1 & "/R"): R = R + 1: Y(T1 & "/R") = R
'¡ô¥OR³oªø¾ã¼ÆÅܼƬO (T1ÅܼƳs±µ"/R"¦¨ªº¦r¦ê¬dY¦r¨å¦^¶ÇitemÈ):
'¥ORÅܼƲ֥[1:¥OT1ÅܼƳs±µ"/R"¦¨ªº¦r¦ê·íkey,item¬ORÅܼÆ,©ñ¦^Y¦r¨å
If R > M Then M = R: Crr(M + 1, 1) = Brr(1, 2) & M
'¡ô¦pªGRÅܼƤj©ó ³oMªø¾ã¼ÆÅܼÆ!´N¥OMÅܼƬORÅܼÆÈ:
'¥O(MÅܼÆ+1)¦C²Ä1ÄæCrr°}¦CȬO 1¦C²Ä2ÄæBrr°}¦Cȳs±µMÅܼƪº·s¦r¦ê
Crr(Y(T1 & "/R") + 1, Y(T1 & "/C") + 1) = T2
'¡ô¥O(Y(T1 & "/R") + 1)¦C²Ä(Y(T1 & "/C") + 1)ÄæCrr°}¦CȬO T2ÅܼÆ
'(Y(T1 & "/R") + 1)¦C: T1ÅܼƳs±µ"/R"¦¨ªº¦r¦ê¬dY¦r¨åªºitemÈ+1 ¦C
'(Y(T1 & "/C") + 1)¦C: T1ÅܼƳs±µ"/C"¦¨ªº¦r¦ê¬dY¦r¨åªºitemÈ+1 Äæ
Next
Crr(1, 1) = Brr(1, 1)
'¡ô¥O1¦C²Ä1ÄæCrr°}¦CȬO 1¦C²Ä1ÄæBrr°}¦CÈ
[E1].Resize(M + 1, C + 1) = Crr
'¡ô¥O[E1]ÂX®i¦V¤U(MÅܼÆ+1)¦C,¦V¥kÂX®i(CÅܼÆ+1)Äæ,
'³oÂX®i½d³òÀx¦s®æÈ¥HCrr°}¦Cȱa¤J
Set Y = Nothing: Erase Brr, Crr
'¡ô¥OÄÀ©ñÅܼÆ
End Sub |
|