- ©«¤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
|
ÁÂÁ¦U¦ì«e½ú´£¨Ñ³o»ò¦hª¾ÃѦb½×¾Â¤W
¤µ¤Ñ«á¾Ç½m²ß¨ìnª`·N°õ¦æ®Ä¯à!
¤ß±oµù¸Ñ¦p¤U!½Ð¦U¦ì«e½ú«ü¥¿¨Ã«ü¾É!ÁÂÁÂ!
Option Explicit
Sub TEST_2()
Dim i&, x&, Y, Z, Arr, Brr, Crr, T
'¡ô«Å§iÅܼÆ
T = Timer
Set Y = CreateObject("Scripting.Dictionary")
Set Z = CreateObject("Scripting.Dictionary")
'¡ô¥OY,Z¦U¬O¦r¨å
For x = 1 To 2
'¡ô³]¥~¶¶°j°é§â¨âªí¸ê®Æ ¥ÎZ¦r¨å¾ã²z ¬°¤£«½Æ¨Ã¦U±NZ¦r¨åÂà¸m¬°°}¦C
',¦A¸Ë¤J¦r¨å¦¨¬°Y(3), Y(4)
Y(x) = Sheets(x).Range("A1:A" & Sheets(x).[A65536].End(3).Row)
Y(x) = Application.Transpose(Y(x))
'¡ôºÉ¶q¤£¥ÎÂà¸mªº¤è¦¡³B²z¸ê®Æ!¤@¨â¦¸ÁÙ¦n!¦h¦¸¯Ó®É!
Crr = Y(x)
'¡ô»Ýn¥ÎCrr±N¦r¨å¸Ìªº°}¦C²±¸Ë¥X¨Ó°õ¦æ¤ñ¸û§Ö
For i = 1 To UBound(Crr)
Z(Crr(i)) = ""
Next
Y(x + 2) = Application.Transpose(Z.KEYS)
'¡ôºÉ¶q¤£¥ÎÂà¸mªº¤è¦¡³B²z¸ê®Æ!¤@¨â¦¸ÁÙ¦n!¦h¦¸¯Ó®É!
Z.RemoveAll
Next
For x = 3 To 4
'¡ô³]¥~¶¶°j°é§â¨â°}¦C¸ê®Æ¤ÀÃþ¨Ã²Õ¦¨¦r¦ê
Crr = Y(x)
'¡ô»Ýn¥ÎCrr±N¦r¨å¸Ìªº°}¦C²±¸Ë¥X¨Ó°õ¦æ¤ñ¸û§Ö
For i = 2 To UBound(Crr)
If Z(Crr(i, 1)) = "" Then
Arr = Arr & Crr(i, 1) & "|"
Z(Crr(i, 1)) = Z(Crr(i, 1)) + 1
Else
Brr = Brr & Crr(i, 1) & "|"
Arr = Replace(Arr, Crr(i, 1) & "|", "")
End If
Next
Next
Brr = Application.Transpose(Split(Brr, "|"))
Arr = Application.Transpose(Split(Arr, "|"))
'¡ô±NArr,Brr¦r¦ê ¥Î"|" ²Å¸¹©î¸Ñ¬°¤@ºû°}¦C,¨ÃÂà¸m¬°µ²ªG
'¦]¬°Arr,Brr«Å§i¨S¦³«ü©w¬O¤°»òÃþ«¬¸ê®Æ!©Ò¥H¥i¥HÅÜ´«Ãþ«¬!
With Sheets(3)
.[I1].Resize(, 2) = Array("¬Û¦P", "¤£¦P")
.[I2].Resize(UBound(Brr), 1) = Brr
.[J2].Resize(UBound(Arr), 1) = Arr
End With
Set Y = Nothing
Set Z = Nothing
Set Arr = Nothing
Set Brr = Nothing
Set Crr = Nothing
MsgBox Timer - T & "’"
End Sub |
|