ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

¸óÄæ¹ï¤ñ

¦^´_ 18# 198188

Option Explicit
Sub TEST()
Dim Arr, Brr, Crr, V$, Z, i&, T$, TT$, A, xA As Range, N%
Set Z = CreateObject("Scripting.Dictionary")
Set xA = Range(ActiveSheet.[G1], ActiveSheet.[A65536].End(3))
xA.Offset(1, 12).Resize(, 3).ClearContents:  Brr = xA
For i = 2 To UBound(Brr)
   T = Format(Trim(Brr(i, 2)), "0000000"): V = Format(Val(Brr(i, 7)), "0000000"): TT = T & "/" & V
   Z(TT) = i
Next
Arr = ActiveSheet.[M1].Resize(UBound(Brr), 3)
A = Array(Range([KH!C1], [KH!A65536].End(3)), Range([KH!J1], [KH!H65536].End(3)), Range([KP!C1], [KP!A65536].End(3)), Range([KP!J1], [KP!H65536].End(3)))
For Each Crr In A
   Crr = Crr: N = N + 1
   For i = 3 To UBound(Crr)
      T = Format(Trim(Crr(i, 1)), "0000000"): V = Format(Val(Crr(i, 2)), "0000000"): TT = T & "/" & V
      If Z.Exists(TT) Then
         If Arr(Z(TT), 1) = "" Then
            Arr(Z(TT), 1) = Crr(1, 1)
            ElseIf InStr("/" & Arr(Z(TT), 1) & "/", "/" & Crr(1, 1) & "/") = 0 Then
            Arr(Z(TT), 1) = Arr(Z(TT), 1) & "/" & Crr(1, 1)
         End If
         Arr(Z(TT), N \ 3 + 2) = Crr(1, 3)
      End If
   Next
Next
ActiveSheet.[M1].Resize(UBound(Brr), 3) = Arr
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 17# Andy2483

­è­è§Ú´ú¸Õ¤F´X¦¸¡A¤£ª¾¹D¬O«ç麽¦^¨Æ¡A¦³ªº¼Æ¾ÚŪ¤£¥X¨Ó¡C¥i§_À°§Ú¬Ý¬Ýªþ¥ó¡A¬O§_®æ¦¡¸òVBA ¦³½Ä¬ð¡H

¹ï¤ñ.rar (12.43 KB)

TOP

¦^´_ 14# shuo1125

ÁÂÁ«ü±Ð,³o¤èªk¨S¥Î¹L,¤S¾Ç¨ì¤@©Û,¤§«e³£¥u·|¥Îintersect
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 15# Andy2483


    ©ú¥Õ¡AÁÂÁÂ

TOP

¦^´_ 13# 198188

1.Set xA = Range([1!G1], [1!A65536].End(3))  §ï¬°
Set xA = Range(ActiveSheet.[G1], ActiveSheet.[A65536].End(3))

2.¥N½X¸Ì­±¦³[1!   ªº³£­n§ï
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 12# Andy2483
·PÁÂAndy¤j¼ö±¡¥B±M·~~
¤]´£¨Ñ­ÓxA.Offset(1, 12).Resize(, 3).ClearContents

TOP

¦^´_ 12# Andy2483


    ¥i¥H¤F¡AÁÂÁ¡I

Set xA = Range([1!G1], [1!A65536].End(3))
Intersect([1!M:O], xA.Offset(1, 12)).ClearContents: Brr = xA
Arr = [1!M1].Resize(UBound(Brr), 3)
[1!M1].Resize(UBound(Brr), 3) = Arr
¥t¥~¦pªG§Ú°õ¦æªº¤u§@ªí¤£©T©w¦WºÙ¡A«ç¼Ë­×§ï¬°¥Îactivesheet?

TOP

¦^´_ 11# 198188

Intersect([1!M:O], xA.Offset(1, 12)).ClearContents
'¡ô¥OxAÀx¦s®æ°¾²¾¤U1¦C¥k12Äæ½d³ò»P M:OÄæ¥æ¶°ªº°Ï°ìÀx¦s®æ¤º®e²M°£
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¦^´_ 10# Andy2483


xA.Offset(1, 12).ClearContents:  Brr = xA
'¡ô¥OxAÀx¦s®æ°¾²¾¤U1¦C¥k12Ä檺°Ï°ìÀx¦s®æ¤º®e²M°£,¥OBrrÅܼƬO¥HxAÀx¦s®æ­È±a¤Jªº¤Gºû°}¦C

³o¥y²M°£内®e¡A¥¦¬O±q²Ä13Äæ(ÄæM) ¨ì ²Ä19Äæ (ÄæS) ªº内®e²M°£¡A§Ú»Ý­nªº¥u¬O ²Ä13ÄæÄæM - ²Ä15ÄæÄæO, §Ú»Ý­n«ç¼Ë­×¥¿¡A¤~¯àÅý¥L¤£§âÄæP - ÄæSªº¸ê®Æ²M°£¡H

TOP

¥»©«³Ì«á¥Ñ Andy2483 ©ó 2024-3-5 08:41 ½s¿è

ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾ÇÂǦ¹©«½Æ²ß¤F¤@¤U,¤è®×µù¸Ñ¦p¤U,½Ð¦U¦ì«e½ú«ü±Ð

Option Explicit
Sub TEST()
Dim Arr, Brr, Crr, V$, Z, i&, T$, TT$, A, xA As Range, N%
'¡ô«Å§iÅܼÆ:&¬Oªø¾ã¼Æ,%¬Oµu¾ã¼Æ,$¬O¦r¦êÅܼÆ
Set Z = CreateObject("Scripting.Dictionary")
'¡ô¥OZÅܼƬO ¦r¨å
Set xA = Range([1!G1], [1!A65536].End(3))
'¡ô¥OxAÅܼƬO°Ï°ìÀx¦s®æ(¤u§@ªí "1"¸ÌªºG1Àx¦s®æ¨ì AÄæ³Ì«á¦³¤º®eÀx¦s®æ)
xA.Offset(1, 12).ClearContents:  Brr = xA
'¡ô¥OxAÀx¦s®æ°¾²¾¤U1¦C¥k12Ä檺°Ï°ìÀx¦s®æ¤º®e²M°£,¥OBrrÅܼƬO¥HxAÀx¦s®æ­È±a¤Jªº¤Gºû°}¦C
For i = 2 To UBound(Brr)
'¡ô³]¶¶°j°é!¥Oi±q2 ¨ìBrr°}¦CÁa¦V³Ì¤j¯Á¤Þ¦C¸¹
   T = Format(Trim(Brr(i, 2)), "0000000"): V = Format(Val(Brr(i, 7)), "0000000"): TT = T & "/" & V
   '¡ô¥OTÅܼƬOi°j°é¦C2ÄæBrr°}¦C­È¥h°£ÀY§ÀªÅ¥Õ¦r¤¸«á,Âà¤Æ¬°7½X¼Æ­Èªº¦r¦ê,¦pªG¤£¨¬7½X«h¥ª°¼¥H0 ¸É¨¬
   '¡ô¥OVÅܼƬOi°j°é¦C7ÄæBrr°}¦C­ÈÂà¤Æ¬°¼Æ­È«á,¦AÂà¤Æ¬°7½X¼Æ­Èªº¦r¦ê,¦pªG¤£¨¬7½X«h¥ª°¼¥H0 ¸É¨¬
   '¡ô¥OTTÅܼƥH"/"²Å¸¹³s±µT»PVÅܼƩҲզ¨ªº·s¦r¦ê

   Z(TT) = i
   '¡ô¥O¥HTTÅܼƬ°key,item¬OiÅܼÆ(¦C¸¹)¯Ç¤JZ¦r¨å¤¤
Next
Arr = [1!M1].Resize(UBound(Brr), 3)
'¡ô¥OArrÅܼƬO ¤u§@ªí "1"ªºM1Àx¦s®æÂX®i¦V¤U/¦V¥k°Ï°ìÀx¦s®æ­È±a¤Jªº¤Gºû°}¦C
A = Array(Range([KH!C1], [KH!A65536].End(3)), Range([KH!J1], [KH!H65536].End(3)), Range([KP!C1], [KP!A65536].End(3)), Range([KP!K1], [KP!I65536].End(3)))
'¡ô¥OAÅܼƬO 4­Ó°Ï°ìÀx¦s®æªº¤@ºû°}¦C
For Each Crr In A
'¡ô³]³v¶µ°j°é!¥OCrrÅܼƬO A°}¦C¸Ìªº°}¦C­È(°Ï°ìÀx¦s®æ)
   Crr = Crr: N = N + 1
   '¡ô¥OCrrÅܼÆÂàÅܬ° ¥HCrr°Ï°ìÀx¦s®æ­È±a¤Jªº¤Gºû°}¦C (PS:CrrÅܼƫŧi¬O³q¥Î«¬ÅܼÆ,©Ò¥H¥i¥H¥ÑÀx¦s®æÅܬ°¤Gºû°}¦C)
   For i = 3 To UBound(Crr)
   '¡ô³]¶¶°j°é!¥Oi±q3¨ìCrr°}¦CÁa¦V³Ì¤j¯Á¤Þ¦C¸¹
      T = Format(Trim(Crr(i, 1)), "0000000"): V = Format(Val(Crr(i, 2)), "0000000"): TT = T & "/" & V
      '¡ô¦¹¼Ðªº¬°Crr°}¦C,T.V.TTÅܼƷN¸q¦P¤W
      If Z.Exists(TT) Then
      '¡ô¦pªGZ¦r¨å¸Ì¦³ TTÅܼƪºkey?
         If Arr(Z(TT), 1) = "" Then
         '¡ô¤S¦pªGArr°}¦C¸Ì(Z¦r¨å©Ò°O¾Ðªº¦C¸¹1Äæ)°}¦C­È¬OªÅ¦r¤¸?
            Arr(Z(TT), 1) = Crr(1, 1)
            '¡ô¥O¸Ó°}¦C­È¥H 1¦C1ÄæCrr°}¦C­È¼g¤J
            ElseIf InStr("/" & Arr(Z(TT), 1) & "/", "/" & Crr(1, 1) & "/") = 0 Then
            '¡ô§_«h¦pªG¸Ó°}¦C­È¨S¦³ 1¦C1ÄæCrr°}¦C­È
            Arr(Z(TT), 1) = Arr(Z(TT), 1) & "/" & Crr(1, 1)
            '¡ô¥O¸Ó°}¦C­È¬O¥H ¦Û¨­¦r¦ê³s±µ 1¦C1ÄæCrr°}¦C­È©Ò²Õ¦¨ªº·s¦r¦ê
         End If
         Arr(Z(TT), N \ 3 + 2) = Crr(1, 3)
         '¡ô¥O¦bArr°}¦C¸Ì¼g¤J 1¦C3ÄæCrr°}¦C­È
      End If
   Next
Next
[1!M1].Resize(UBound(Brr), 3) = Arr
'¡ô¥O¤u§@ªí "1"ªºM1Àx¦s®æÂX®iªº½d³ò¸Ì¼g¤JArr°}¦C­È
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

        ÀR«ä¦Û¦b : ¡i°±º¢¤£«e¡A²×µL©Ò±o¡j¤H³£°g©ó´M§ä©_ÂÝ¡A¦]¦Ó°±º¢¤£«e¡FÁa¨Ï®É¶¡¦A¦h¡B¸ô¦Aªø¡A¤]¤FµL¥Î³B¡A²×µL©Ò±o¡C
ªð¦^¦Cªí ¤W¤@¥DÃD