- ©«¤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
|
¥»©«³Ì«á¥Ñ Andy2483 ©ó 2022-12-30 15:01 ½s¿è
¦^´_ 2# Andy2483
¦^´_¦Û¤v½Æ²ß¤ß±oµù¸Ñ
Option Explicit
Sub °}¦C»P¦r¨å½m²ß_2±ø¥ó¤U°µ¸ê®Æ¾ã²z¬Û¥[_FGÄæ±Æ§Ç()
Dim Y, Z, V, Arr, i&, T(3)
'¡ô«Å§iÅܼÆ:(Y,Z,V,Arr)¬O³q¥Î«¬ÅܼÆ,i¬Oªø¾ã¼Æ,T¬O¤@ºû°}¦CT(0)~T(3)
Set Y = CreateObject("Scripting.Dictionary")
Set Z = CreateObject("Scripting.Dictionary")
Set V = CreateObject("Scripting.Dictionary")
'¡ô¥OY,Z,V¦U¬O¦r¨å
Arr = Range([C2], [A65536].End(3))
'¡ô¥OArr¬O¤Gºû°}¦C!¥H[C2]¨ìAÄæ³Ì«á¦³¤º®eÀx¦s®æ,³o½d³òÀx¦s®æÈˤJ
For i = 1 To UBound(Arr)
'¡ô³]¶¶°j°é!i±q1¨ìArr°}¦C³Ì¦C¤j¯Á¤Þ¸¹¼Æ
T(1) = DateValue(Arr(i, 1))
'¡ô¥O1¯Á¤Þ¸¹T°}¦CȬO i°j°é¦C1ÄæArr°}¦CÈÂà¤é´Á®æ¦¡
T(2) = Arr(i, 2)
'¡ô¥O2¯Á¤Þ¸¹T°}¦CȬO i°j°é¦C2ÄæArr°}¦CÈ
T(3) = Arr(i, 3)
'¡ô¥O3¯Á¤Þ¸¹T°}¦CȬO i°j°é¦C3ÄæArr°}¦CÈ
T(0) = T(1) & "|" & T(2)
'¡ô¥O0¯Á¤Þ¸¹T°}¦CȬO 1¯Á¤Þ¸¹T°}¦Cȳs±µ "|" ¦A³s±µ2¯Á¤Þ¸¹T°}¦CÈ
Y(T(0)) = Y(T(0)) + T(3)
'¡ô¥O¥H0¯Á¤Þ¸¹T°}¦CȬ°Key,item¬O¦Û¨+3¯Á¤Þ¸¹T°}¦CÈ,ˤJY¦r¨å
Z(T(0)) = T(1)
'¡ô¥O¥H0¯Á¤Þ¸¹T°}¦CȬ°Key,1¯Á¤Þ¸¹T°}¦CÈ,ˤJZ¦r¨å
V(T(0)) = T(2)
'¡ô¥O¥H0¯Á¤Þ¸¹T°}¦CȬ°Key,2¯Á¤Þ¸¹T°}¦CÈ,ˤJV¦r¨å
Next
[F:H].ClearContents
'¡ô²M°£F:HÄæÀx¦s®æ¤º®e
[F2].Resize(Z.Count, 1) = Application.Transpose(Z.ItemS)
'¡ô¥O[F2]ÂX®i¦V¤UZ¦r¨å¼Æ ¦V¥k¤£ÂX®i½d³òÀx¦s®æ¥HZ¦r¨åªºitemÂà¸m«áˤJ
[G2].Resize(V.Count, 1) = Application.Transpose(V.ItemS)
'¡ôÃþ±À
[H2].Resize(Y.Count, 1) = Application.Transpose(Y.ItemS)
'¡ôÃþ±À
With [F2].Resize(Z.Count, 3)
'¡ô¥H¤U¬O Ãö©ó[F2]ÂX®i¦V¤UZ¦r¨å¼Æ ¦V¥k3Äæ½d³òÀx¦s®æ
.Sort _
KEY1:=.Item(1), Order1:=xlAscending, _
KEY2:=.Item(2), Order2:=xlAscending, _
Header:=xlNo, Orientation:=xlTopToBottom
'¡ô¥O¥HÀx¦s®æ¶°²Ä1Äæ°µ²Ä¤@¼h°µµL¼ÐÃD¦Cªº¤W¤U¶¶±Æ§Ç,²Ä2Äæ¦P®É°µ²Ä¤G¼h¤W¤U¶¶±Æ§Ç
End With
[F1:H1] = [{"¤é´Á","²£«~","¼Æ¶q"}]
'¡ô¥OF1¨ìH1¤§¶¡ªºÀx¦s®æ¥H¦r¦ê±a¤J
Set Y = Nothing
Set Z = Nothing
Set V = Nothing
Set Arr = Nothing
Erase T
'¡ôÄÀ©ñÅܼÆ
End Sub |
|