[µo°Ý] ¦p¦ó³]©w¦Û°Ê§ì¨ú¤À¶q³æ»ù

honeymacro

¥Ñ©ó¤u§@¤W»Ý­n,±Ä¶R®É»Ý¤ñ¹ï¤À¶q³æ»ù, ¤£ª¾¹DÀ³¸Ó«ç»ò³]©w¤~¦n

­ã´£³¡ªL

E3:
=INDEX(¸ê®Æ®w!E:E,-LOOKUP(,-MATCH(1,(¸ê®Æ®w!$D$1:$D$9>=$D3)/(¸ê®Æ®w!$A$1:$A$9=$A3)/(¸ê®Æ®w!$B$1:$B$9=$B3),{1,0})))&""

©Î:Àx¦s®æ®æ¦¡³]¦¨"#", ¥h±¼«á­±ªº&""

¸ê®Æ®w¥²¶·¥H"¼Æ¶q"»¼¼W±Æ§Ç~~

hcm19522

E3:G6{=IFERROR(LOOKUP(1,0/(¸ê®Æ®w!$A$2:$A$99=$A3)/(¸ê®Æ®w!$B$2:$B$99=$B3)/(¸ê®Æ®w!$C$2:$C$99=$C3)/(¸ê®Æ®w!$D$2:$D$9<=$D3),¸ê®Æ®w!E$2:E$99),INDEX(¸ê®Æ®w!E:E,MIN(IF((¸ê®Æ®w!$A$2:$A$99=$A3)*(¸ê®Æ®w!$B$2:$B$99=$B3)*(¸ê®Æ®w!$C$2:$C$99=$C3),ROW($2:$99)))))

honeymacro

¦^´_ 2# ­ã´£³¡ªL


    ÁÂÁÂ~~¥i¥H¦Û°Ê§ì¨ú¤À¶q³æ»ù¤F!!~¤j·PÁÂ:)

honeymacro

E3:G6{=IFERROR(LOOKUP(1,0/(¸ê®Æ®w!$A$2A$99=$A3)/(¸ê®Æ®w!$B$2B$99=$B3)/(¸ê®Æ®w!$C$2C$99=$C3)/(¸ê ...
hcm19522 µoªí©ó 2021-2-9 12:19

    ÁÂÁÂ~~³o­Ó¨ç¼Æ¤]¬Û·í¹ê¥Î ¸Ñ¨M§Ú¦n´X¶gªº§xÂZ ¤j·PÁÂ!!~ :)

honeymacro

³æ»ù§ì¤À¶q

    ±z¦n, ¥Ø«e¹J¨ì¤£¦Pexcel±Æª©ªº¤À¶q§ì¨ú, ¤£ª¾fxÀ³¦p¦ó¤U, ¯à§_½Ð¤j¤j¦A¦¸¨ó§U, ÁÂÁÂ

(¤£¯à¤W¶ÇÀÉ®×,©Ò¥H±Nexcel©ñ¦bdropbox)

Andy2483

ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾ÇÂǦ¹©«½m²ß°}¦C»P¦r¨å,VBA½m²ß¤è®×¦p¤U,½Ð¦U¦ì«e½ú«ü±Ð

°õ¦æ«e:

¤U¸ü (205.74 KB)

2023-11-29 07:16

°õ¦æµ²ªG:

¤U¸ü (261.23 KB)

2023-11-29 07:17

Option Explicit
Sub TEST()
Dim Arr, Brr, Crr, V, Z, Z1, A, i&, Q, T$, Ta$, Tb$, Tc$, Td$, Mi&, Ma&, ii&, Rp$
Set Arr = CreateObject("System.Collections.ArrayList")
Set Z = CreateObject("Scripting.Dictionary")
Set Z1 = CreateObject("Scripting.Dictionary")
Brr = Range([¸ê®Æ®w!G1], [¸ê®Æ®w!A65536].End(3))
Crr = Range([·j´M!G1], [·j´M!A65536].End(3))
Rp = Application.Rept(0, 9)
For i = 2 To UBound(Brr)
   Ta = Trim(Brr(i, 1))
   Tb = Format(Val(Brr(i, 2)), Rp)
   Tc = Trim(Brr(i, 3))
   Td = Format(Val(Brr(i, 4)), Rp)
   T = Ta & Tb & Tc & "|" & Td
   Z(T) = i: T = Ta & Tb & Tc
   Z1(T & "|Ma") = IIf(Z1(T & "|Ma") < Val(Td), Val(Td), Z1(T & "|Ma"))
   Z1(T & "|Mi") = IIf(Z1(T & "|Mi") = 0, Z1(T & "|Ma"), IIf(Z1(T & "|Mi") > Val(Td), Val(Td), Z1(T & "|Mi")))
Next
For i = 3 To UBound(Crr)
   Ta = Trim(Crr(i, 1))
   Tb = Format(Val(Crr(i, 2)), Rp)
   Tc = Trim(Crr(i, 3))
   Td = Format(Val(Crr(i, 4)), Rp)
   T = Ta & Tb & Tc & "|" & Td
   Z(T) = Z(T): Crr(i, 1) = T
Next
For Each A In Z.Keys
   If A <> vbNullString And Not Arr.contains(A) Then Arr.Add (A)
Next
Arr.Sort: Arr = Arr.toarray
For i = 0 To UBound(Arr)
   Q = Split(Arr(i), "|"): V = Val(Q(1))
   If T <> Q(0) Then T = Q(0)
   Mi = Z1(Q(0) & "|Mi"):  Ma = Z1(Q(0) & "|Ma")
   If V <= Mi Then Z(Arr(i)) = Z(T & "|" & Format(Mi, Rp)): GoTo i02
   If V >= Ma Then Z(Arr(i)) = Z(T & "|" & Format(Ma, Rp)): GoTo i02
   For ii = i + 1 To UBound(Arr)
      If Z(Arr(ii)) <> "" Then Z(Arr(i)) = Z(Arr(ii)): Exit For
   Next
i02: Next
For i = 3 To UBound(Crr)
   Crr(i - 2, 3) = Brr(Z(Crr(i, 1)), 7)
   Crr(i - 2, 2) = Brr(Z(Crr(i, 1)), 6)
   Crr(i - 2, 1) = Brr(Z(Crr(i, 1)), 5)
Next
[·j´M!I3].Resize(UBound(Crr) - 2, 3) = Crr
End Sub