Andy2483

¦^´_ 24# 198188


    ­þ¤@µ§ µo¥Í¤£¯àŪ¨úªº°ÝÃD??

Andy2483

¦^´_  Andy2483


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

Set xA = Range([1!G1], [1!A65536].End(3))
Intersect([1!M ...
198188 µoªí©ó 2024-3-5 08:58

­×§ï¬°¥Îactivesheet´N­n¦bªí "1"¤U°õ¦æ,§_«h·|§ì¤£¨ì¸ê®Æ,
¦pªG¦b¤u§@ªí¤£§ó´«¶¶§Çªº«e´£¤U¥i¥H¥Î¤u§@ªí¯Á¤Þ¸¹

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(Sheets(1).[G1], Sheets(1).[B65536].End(3)(1, 0))
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 = Sheets(1).[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
Sheets(1).[M1].Resize(UBound(Brr), 3) = Arr
End Sub

Andy2483

¦^´_ 29# 198188


    ¸ê®Æ¸Ì¥i¯à¦³¬Ý¤£¨£ªº¯S®í²Å¸¹

Andy2483

¦^´_ 31# 198188


    ¤µ¤Ñ¤~¬d¨ì2¦C»P137¦CÃöÁä¦r¬Û¦P,«á¾Ç¤£¹î,©êºp

Andy2483

¦^´_ 35# 198188
ÁÂÁ«e½ú«ü¾É,½Ð¦A¸Õ¸Õ¬Ý
Option Explicit
Sub TEST()
Dim Arr, Brr, Crr, V$, Z, Q, i&, T$, TT$, A, xA As Range, N%, C%
Set Z = CreateObject("Scripting.Dictionary")
Set xA = Range(ActiveSheet.[G1], ActiveSheet.[B65536].End(3)(1, 0))
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) = 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
         Q = Split(Z(TT) & "/", "/")
         For C = 1 To UBound(Q) - 1
            If Arr(Q(C), 1) = "" Then
               Arr(Q(C), 1) = Crr(1, 1)
               ElseIf InStr("/" & Arr(Q(C), 1) & "/", "/" & Crr(1, 1) & "/") = 0 Then
               Arr(Q(C), 1) = Arr(Q(C), 1) & "/" & Crr(1, 1)
            End If
            Arr(Q(C), N \ 3 + 2) = Crr(1, 3)
         Next
      End If
   Next
Next
ActiveSheet.[M1].Resize(UBound(Brr), 3) = Arr
End Sub

Andy2483

¦^´_ 37# 198188

Crr(1, 3)  §ï¬° Crr(i, 3). ¸Õ¸Õ¬Ý