- ©«¤l
- 1447
- ¥DÃD
- 40
- ºëµØ
- 0
- ¿n¤À
- 1471
- ÂI¦W
- 0
- §@·~¨t²Î
- Windows 7
- ³nÅ骩¥»
- Excel 2010 & 2016
- ¾\ŪÅv
- 50
- ©Ê§O
- ¨k
- ¨Ó¦Û
- ¥xÆW
- µù¥U®É¶¡
- 2020-7-15
- ³Ì«áµn¿ý
- 2024-12-5
|
ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾ÇÂǦ¹©«½m²ß°}¦C»P¦r¨å,¾Ç²ß¤è®×¦p¤U,½Ð¦U¦ì«e½ú«ü±Ð
°õ¦æ«e:
¿é¤J 2021/11/21 «á«ö¶s°õ¦æµ²ªG:
Option Explicit
Sub TEST()
Dim Brr, Z, V1&, V2&, V3, i&, j%, R&, N&, T$
'¡ô«Å§iÅܼÆ
Intersect(ActiveSheet.UsedRange, [U2:Z65536]).ClearContents
If IsDate([S2]) = False Then MsgBox "[S2]»Ý¿é¤J¤é´Á": Exit Sub
'¡ô¦pªG[S2]Àx¦s®æȤ£¬O¤é´Á? True´N¸õ¥X´£¥Ü,µ²§ôµ{¦¡°õ¦æ
Set Z = CreateObject("Scripting.Dictionary")
'¡ô¥OZÅܼƬO ¦r¨å
Brr = Range([F1], [A65536].End(xlUp))
'¡ô¥OBrrÅܼƬO ¤Gºû°}¦C,¥HA~FÄæÀx¦s®æȱa¤J°}¦C¤¤
For i = 2 To UBound(Brr)
'¡ô³]¶¶°j°é
T = Brr(i, 1) & "|" & Brr(i, 3) & "|" & Brr(i, 4)
'¡ô¥OTÅܼƬO ²Ä1.3.4Äæ°j°é°}¦CȪº²Õ¦X¦r¦ê,¤¤¶¡¥H"|"¶¡¹j
If InStr(T, [S2] & "|") = 0 Then GoTo i01
'¡ô¦pªGT²Õ¦X¦r¦ê¤£²Å¦X±ø¥ó? True´N¸õ¨ì¼Ð¥Üi01¦ì¸mÄ~Äò°õ¦æ
If Z(Brr(i, 4)) = "" Then V1 = V1 + 1: Z(Brr(i, 4)) = 1
'¡ô¦pªG°Ó«~¦WºÙ¤£¦bZ¦r¨å¸Ì? True´N¥OV1ÅܼƲ֥[1«á,
'±N °Ó«~¦WºÙkey¹ïÀ³item§ï¬°1
V2 = V2 + Brr(i, 5): V3 = V3 + Brr(i, 6)
'¡ô¥OV2ÅܼƲ֥[ ¼Æ¶q,'¡ô¥OV3ÅܼƲ֥[ ®ø¶Oª÷ÃB
N = Z(T)
'¡ô¥ONÅܼƬO ¥HTÅܼƬdZ¦r¨å¦^¶ÇitemÈ
If N = 0 Then
'¡ô¦pªGNÅܼƬO 0
R = R + 1: For j = 1 To 6: Brr(R, j) = Brr(i, j): Next
'¡ô¥ORÅܼƲ֥[1,³]¶¶°j°é±N°j°é¦C°}¦CÈÁèì«ü©wªº RÅܼƦC
Z(T) = R: GoTo i01
'¡ô¥OTÅܼÆkey¹ïÀ³item§ï¬°RÅܼÆ,¥O¸õ¨ì¼Ð¥Üi01¦ì¸mÄ~Äò°õ¦æ
End If
Brr(N, 5) = Brr(N, 5) + Brr(i, 5): Brr(N, 6) = Brr(N, 6) + Brr(i, 6)
'¡ô¥O²Ä¤G¦¸¥H¤W¥X²{ªºTÅܼÆ,¨ä¼Æ¶q»P®ø¶Oª÷ÃB²Ö¥[
i01: Next
If R > 0 Then R = R + 1 Else: MsgBox "µL²Å¦X±ø¥ó¸ê®Æ": Exit Sub
For j = 1 To 3: Brr(R, j) = "": Next
Brr(R, 4) = V1: Brr(R, 5) = V2: Brr(R, 6) = V3
'¡ô¥O¥[Á`È©ñ¦b°}¦C¸Ì
[U2].Resize(R, 6) = Brr
'¡ô¥O±q[U2]ÂX®i½d³òÀx¦s®æÈ¥HBrr°}¦Cȼg¤J,¶W¹L½d³òªº°}¦CÈ©¿²¤
Set Z = Nothing: Erase Brr
'¡ô¥OÄÀ©ñÅܼÆ
End Sub |
|