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

¦h±ø¥ó¤º´¡ªk¬d¸ß

¦h±ø¥ó¤º´¡ªk¬d¸ß

½Ð°Ý¦U¦ì¤j¤j~~
§Ú¦³¤@¥÷¸ê®Æ¡A»Ý¶ñ¼g"À£¤O"¡B"¶ZÂ÷"¡BType"«á¬d¥XAmp­È¡C
¦ý¡A
1 .­Y¬OTpye¼Æ­È¤Ó¤jor¤Ó¤p¡A«h±a¤Jªí³æ³Ì¤jor³Ì¤pType¹ïÀ³ªºAmp­È¡C
2.­Y¬OType­È¦b²M³æ½d³ò¤º¡A¦ýµL¹ïÀ³­È¡A«h¥H¤º®tªkºâ¥XAmp­È¡C

§Ú·|¦h±ø¥ó±a¥X..
{=IFERROR(INDEX(¸ê®Æ,MATCH(1,(À£¤O½d³ò=K8)*(¶ZÂ÷=K9)*(Type=N13),0),4),"½T»{°Ñ¼Æ")}

§Ú¤]·|§PÂ_¹L¤j¡B¹L¤p..
{=IF(N15>MAX(IF((À£¤O½d³ò=K8)*(¶ZÂ÷=K9),(Type))),MAX(IF((À£¤O½d³ò=K8)*(¶ZÂ÷=K9),(Type))),IF(N15<MIN(IF((À£¤O½d³ò=K8)*(¶ZÂ÷=K9),(Type))),MIN(IF((À£¤O½d³ò=K8)*(¶ZÂ÷=K9),(Type))),N15))}

¦ý¬O...¹Á¸Õ³\¤[ÁÙ°µ¤£¥X¤W­±ªº®ÄªG..... @@a
½Ð¤j¤jÀ°À°¦£°Ú...·PÁÂ
Test.zip (11.71 KB)
Jack

ÁÂÁ½׾Â,ÁÂÁ¦U¦ì«e½ú
«á¾ÇÂǦ¹©«¥H ­ã´£³¡ªL«e½ú½d¨Ò½m²ßVBA¤è®×,½Ð¦U¦ì«e½ú«ü±Ð
Xl0000094_20231122.zip (22.03 KB)

°õ¦æµ²ªG:
20231122_1.jpg
2023-11-22 09:07


Option Explicit
Function F20231122_1(ByVal Va$)
Application.Volatile
Evaluate "Ex()"
F20231122_1 = Va
End Function
Sub Ex()
Dim Arr, Brr, Crr, Z, A, i&, Mi#, Ma#, ii&, E#, V#
Set Arr = CreateObject("System.Collections.ArrayList")
Set Z = CreateObject("Scripting.Dictionary")
Brr = Range([F6], [C65536].End(3))
For i = 1 To UBound(Brr)
   If Brr(i, 1) <> [I2] Or Brr(i, 2) <> [I3] Then GoTo i01 Else V = Brr(i, 3)
   Z(V) = Val(Brr(i, 4))
   Ma = IIf(Ma < V, V, Ma)
   Mi = IIf(Mi = 0, Ma, IIf(Mi > V, V, Mi))
i01: Next
Brr = Range([I8], [I65536].End(3))
For i = 1 To UBound(Brr): Z(Val(Brr(i, 1))) = Z(Val(Brr(i, 1))): 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)
   V = Arr(i)
   If V <= Mi Then Z(V) = Z(Mi): GoTo i02
   If V >= Ma Then Z(V) = Z(Ma): GoTo i02
   If Z(V) <> "" Then E = V
   If Z(V) = "" Then
      For ii = i + 1 To UBound(Arr)
         A = Arr(ii)
         If Z(A) <> "" Then
            Z(V) = Z(E) + (Z(A) - Z(E)) * ((V - E) / (A - E)): Exit For
         End If
      Next
   End If
i02: Next
For i = 1 To UBound(Brr): Brr(i, 1) = Z(Brr(i, 1)): Next
[K8].Resize(UBound(Brr)) = Brr: [I5] = Mi: [J5] = Ma
End Sub
Sub ½d³ò»P¶ZÂ÷_²M³æ()
Dim Arr, Brr, Crr, Z, A, i&, Mi#, Ma#, ii&, E#, V#
Set Z = CreateObject("Scripting.Dictionary")
Brr = Range([D6], [C65536].End(3))
For i = 1 To UBound(Brr)
   Z(Brr(i, 1)) = Brr(i, 2)
Next
With [I2].Validation
   .Delete
   .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=Join(Z.Keys, ",")
End With
With [I3].Validation
   .Delete
   .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
        xlBetween, Formula1:=Join(Z.Items, ",")
End With
End Sub
¥Î¦æ°Ê¸Ë¸mÂsÄý½×¾Â¾Ç²ß«Ü¤è«K,ÁÂÁ½׾¸gÀç¹Î¶¤
½Ð¤j®a¤@°_¤W½×¾Â¨Ó¥æ¬y

TOP

¤w¸g¦bI4/J4 ¥ý­pºâ¥X³Ì¤p/³Ì¤jType¡A¥i¦AÀu¤Æ
1 ¨Ï¥Î MATCH(V, Array, 1)¨ç¼Æ®É¡AType¼Æ­È¤Ó¤p®É»Ý­n¶i¦æ¸É©³  MAX(I$4,I8)
2 ¨Ï¥Î TREND()¨ç¼Æ®É¡A­Y¬OType¼Æ­È¤Ó¤jor¤Ó¤p¡A«h±a¤Jªí³æ³Ì¤jor³Ì¤pType¹ïÀ³ªºAmp­È¡A¦¹®É»Ý­n­×¥¿Type¤£¶W¹L³Ì¤jor¤£©³©ó³Ì¤p¡C

I8 =IF(I8=0,"",TREND(OFFSET(F$5,MATCH(MAX(I$4,I8),E$6:E$111/(C$6:C$111=I$2)/(D$6:D$111=I$3)),,2),OFFSET(E$5,MATCH(MAX(I$4,I8),E$6:E$111/(C$6:C$111=I$2)/(D$6:D$111=I$3)),,2),MEDIAN(I$4,I8,J$4)))
°}¦C¿é¤J
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

¤w¸g¦bI4/J4 ¥ý­pºâ¥X³Ì¤p/³Ì¤jType¡A¥i¦AÀu¤Æ
1 ¨Ï¥Î MATCH(V, Array, 1)¨ç¼Æ®É¡AType¼Æ­È¤Ó¤p®É»Ý­n¶i¦æ¸É©³  MAX(I$4,I8)
2 ¨Ï¥Î TREND()¨ç¼Æ®É¡A­Y¬OType¼Æ­È¤Ó¤jor¤Ó¤p¡A«h±a¤Jªí³æ³Ì¤jor³Ì¤pType¹ïÀ³ªºAmp­È¡A¦¹®É»Ý­n­×¥¿Type¤£¶W¹L³Ì¤jor¤£©³©ó³Ì¤p¡C

I8 =IF(I8=0,"",TREND(OFFSET(F$5,MATCH(MAX(I$4,I8),E$6:E$111/(C$6:C$111=I$2)/(D$6$111=I$3)),,2),OFFSET(E$5,MATCH(MAX(I$4,I8),E$6:E$111/(C$6:C$111=I$2)/(D$6$111=I$3)),,2),MEDIAN(I$4,I8,J$4)))
°}¦C¿é¤J
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

«z..­ã´£³¡ªL¤j¤j¯u«Â....
¤pªº¸ÕµÛ°µ¡A¨S¦³°ÝÃD!!
¥t¥~¡AML089¤j¤jªº¤½¦¡§ÚÁÙ¦b²z¸Ñ¤¤...¤]·PÁ±zªº±Ð¾Ç~~
Jack

TOP

¥»©«³Ì«á¥Ñ ML089 ©ó 2021-2-8 11:04 ½s¿è

ºë²¤@¤UÃä¬É­pºâ¦¡
J8 =-LOOKUP(,-TEXT(IF((À£¤O½d³ò=I$2)*(¶ZÂ÷=I$3),(OFFSET(Amp,1,)-Amp)/(OFFSET(Type,1,)-Type)*(MEDIAN(QUARTILE(IF((À£¤O½d³ò=I$2)*(¶ZÂ÷=I$3),Type),{0,4}),I8)-Type)+Amp,""),"[<"&Amp&"] ;[>"&OFFSET(Amp,1,)&"] ;0.0"))
°}¦C¤½¦¡

­n³t«×§ÖÁÙ¬O2¼Óªº¤½¦¡¦n
¦n¤[¨S¦³°Ê¸£µ¬¡A¼g¤@­Ó¸ò2¼Ó¤£¦P­pºâ¤½¦¡µ¹¤j®a°Ñ¦Ò
¤½¦¡ =(Amp2 - Amp1) / (Type2 - Type1) * (Type(i) - Type1) + Amp1
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

1 ¥ý¨Ï¥Î ¤½¦¡ - ©w¸q¦WºÙ (³»ºÝ)
2 ª½±µ¨Ï¥Î¤º´¡­pºâ¦¡
J8 =-LOOKUP(,-TEXT(IF((À£¤O½d³ò=I$2)*(¶ZÂ÷=I$3),(OFFSET(Amp,1,)-Amp)/(OFFSET(Type,1,)-Type)*(SMALL(QUARTILE(IF((À£¤O½d³ò=I$2)*(¶ZÂ÷=I$3),Type),{0,4})*{1;1;0}+I8*{0;0;1},3)-Type)+Amp,""),"[<"&Amp&"] ;[>"&OFFSET(Amp,1,)&"] ;0.0"))
°}¦C¿é¤J¤½¦¡
{...} ªí¥Ü»Ý­n¥Î CTRL+SHIFT+ENTER ¤TÁä¿é¤J¤½¦¡

TOP

°Ñ¦Ò¤U:
Xl0000094.rar (11.76 KB)

TOP

        ÀR«ä¦Û¦b : °µ¸Ó°µªº¨Æ¬O´¼¼z¡A°µ¤£¸Ó°µªº¨Æ¬O·Mè¡C
ªð¦^¦Cªí ¤W¤@¥DÃD