返回列表 上一主題 發帖

多條件內插法查詢

多條件內插法查詢

請問各位大大~~
我有一份資料,需填寫"壓力"、"距離"、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)
Jack

參考下:
Xl0000094.rar (11.76 KB)

TOP

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"))
陣列輸入公式
{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

本帖最後由 ML089 於 2021-2-8 11:04 編輯

精簡一下邊界計算式
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
{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

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

TOP

已經在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)))
陣列輸入
{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

已經在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)))
陣列輸入
{...} 表示需要用 CTRL+SHIFT+ENTER 三鍵輸入公式

TOP

        靜思自在 : 時時好心就是時時好日。
返回列表 上一主題