多條件內插法查詢

vackbin

請問各位大大~~
我有一份資料，需填寫"壓力"、"距離"、Type"後查出Amp值。
但，
1 .若是Tpye數值太大or太小，則帶入表單最大or最小Type對應的Amp值。
2.若是Type值在清單範圍內，但無對應值，則以內差法算出Amp值。

我會多條件帶出..
{=IFERROR(INDEX(資料,MATCH(1,(壓力範圍=K8)*(距離=K9)*(Type=N13),0),4),"確認參數")}

我也會判斷過大、過小..
{=IF(N15>MAX(IF((壓力範圍=K8)*(距離=K9),(Type))),MAX(IF((壓力範圍=K8)*(距離=K9),(Type))),IF(N15<MIN(IF((壓力範圍=K8)*(距離=K9),(Type))),MIN(IF((壓力範圍=K8)*(距離=K9),(Type))),N15))}

但是...嘗試許久還做不出上面的效果..... @@a
請大大幫幫忙啊...感謝
Test.zip (11.71 KB)

下載次數: 11

2021-2-4 10:50

准提部林

參考下:
Xl0000094.rar (11.76 KB)

下載次數: 15

2021-2-6 10:34

ML089

1 先使用 公式 - 定義名稱 (頂端)
2 直接使用內插計算式
J8 =-LOOKUP(,-TEXT(IF((壓力範圍=I$2)*(距離=I$3),(OFFSET(Amp,1,)-Amp)/(OFFSET(Type,1,)-Type)*(SMALL(QUARTILE(IF((壓力範圍=I$2)*(距離=I$3),Type),{0,4})*{1;1;0}+I8*{0;0;1},3)-Type)+Amp,""),"[<"&Amp&"] ;[>"&OFFSET(Amp,1,)&"] ;0.0"))
陣列輸入公式

ML089

精簡一下邊界計算式
J8 =-LOOKUP(,-TEXT(IF((壓力範圍=I$2)*(距離=I$3),(OFFSET(Amp,1,)-Amp)/(OFFSET(Type,1,)-Type)*(MEDIAN(QUARTILE(IF((壓力範圍=I$2)*(距離=I$3),Type),{0,4}),I8)-Type)+Amp,""),"[<"&Amp&"] ;[>"&OFFSET(Amp,1,)&"] ;0.0"))
陣列公式

要速度快還是2樓的公式好
好久沒有動腦筋，寫一個跟2樓不同計算公式給大家參考
公式 =(Amp2 - Amp1) / (Type2 - Type1) * (Type(i) - Type1) + Amp1

vackbin

哇..准提部林大大真威....
小的試著做，沒有問題!!
另外，ML089大大的公式我還在理解中...也感謝您的教學~~

ML089

已經在I4/J4 先計算出最小/最大Type，可再優化
1 使用 MATCH(V, Array, 1)函數時，Type數值太小時需要進行補底  MAX(I$4,I8)
2 使用 TREND()函數時，若是Type數值太大or太小，則帶入表單最大or最小Type對應的Amp值，此時需要修正Type不超過最大or不底於最小。

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)))
陣列輸入

ML089

已經在I4/J4 先計算出最小/最大Type，可再優化
1 使用 MATCH(V, Array, 1)函數時，Type數值太小時需要進行補底  MAX(I$4,I8)
2 使用 TREND()函數時，若是Type數值太大or太小，則帶入表單最大or最小Type對應的Amp值，此時需要修正Type不超過最大or不底於最小。

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)))
陣列輸入

Andy2483

謝謝論壇,謝謝各位前輩
後學藉此帖以 准提部林前輩範例練習VBA方案,請各位前輩指教
Xl0000094_20231122.zip (22.03 KB)

下載次數: 3

2023-11-22 09:07

執行結果:

下載 (151.4 KB)

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 範圍與距離_清單()
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