首頁 > 互聯網 > 正文

【當前獨家】excel區間插值法公式_excel插值法公式

2022-09-22 11:27:22 來源:互聯網

插值法又稱“內插法”,主要包括線性插 值、拋物線插值和拉格朗日插值等。其中的線 性插值法是指使用連接兩個已知量的直線,來 確定在這兩個已知量之間的一個未知量的值。相當于已知坐標(x0, y0)與(x1, y1),要得 到 x0 至 x1 區間內某一位置 x 在直線上的值, 如圖 5-51 所示。

Excel 中的 TREND 函數和 FORECAST 函數都可以完成簡單的線性插值計算。

圖5-51線性插值法圖示


(資料圖片僅供參考)

簡單的插值計算

線性插值法計算電阻值

圖 5-52 所示,是某物體在不同溫度下測 得的電阻值,需要使用插值法預測在某個指定 溫度時的電阻值。

E2 單元格輸入以下公式,計算結果為 21.0562。=TREND(B2:B5,A2:A5,D2)

TREND 函數的作用是根據已知 x 序列的值和 y 序列的值,構造線性回歸直線方程,然 后根據構造好的直線方程,計算 x 值序列對應的 y 值序列。函數語法為:TREND(known_y's,[known_x's],[new_x's],[const])

第一參數指定已知關系 y=mx+b 中的 y 值集合。

第二參數指定已知關系 y=mx+b 中的 x 值集合。

第三參數指定需要函數 TREND 返回對應 y 值的新 x 值。

第四參數是一個邏輯值,如果為 TRUE 或省略,b 將按正常計算。如果為 FALSE,b 將被設為 0(零)。

圖5-52插值法計算電阻值

本例中,TREND 函數的 y 值集合為 B2:B5 單元格區域的電阻值,x 值集合為 A2:A5 單 元格區域中的溫度值,新 x 值為 D2 單元格中的溫度值。TREND 函數省略第四參數,最終以 線性插值法計算出溫度為 -194 度時對應的電阻值。

使用以下公式也可實現相同的計算。=FORECAST(D2,B2:B5,A2:A5)

FORECAST 函數的作用是根據現有的 x 值和 y 值,根據給定的 x 值通過線性回歸來預測新的 y 值。函數語法如下。FORECAST(x, known_y's, known_x's)

第一參數是需要進行預測的數據點。

第二參數和第三參數分別對應已知的 y 值和 x 值。

FORECAST 函數的計算結果與 TREND 函數的結果相同。

分段插值計算

在插值計算中,取樣點越多,插值結果的誤差越小。分段線性插值是將與插值點靠近的 兩個數據點使用直線連接,然后在直線上選取對應插值點的數。

分段線性插值法計算熱力性質

圖5-53 所示,是某熱力公司熱力性質對照表的部分內容,需要根據對照表中的數據, 以分段線性插值法計算在指定壓力下各項熱力性質的指標。

圖5-53熱力性質對照表

步驟1: K3 單元格輸入以下公式,根據 A3 單元格的插值點在對照表中計算出等于或靠近該 插值點的位置,如圖 5-54 所示。=MATCH(A3,A9:A179,1)

圖5-54計算靠近該插值點的位置

MATHC 函數在 A9:A179 單元格區域中查詢 A3 單元格中壓力值所處的位置。A9:A179 單元格區域中的壓力值是升序排列,并且 MATCH 函數第三參數設置為 1。因此在查詢不到 具體的值時,將以小于查詢值的最接近值進行匹配,并返回其相對位置。

步 驟 2: B3 單元格輸入以下公式,向右復制填充到 I3 單元格,計算出各項熱力性質指標,如 圖 5-55 所示。=IF($A3=4,B179,TREND(OFFSET(B$8,$K3,0,2),OFFSET($A$8,$K3,0,2), $A3))

圖5-55分段線性插值法計算熱力性質

公式中用到了 OFFSET 函數,這個函數的作用是以指定的引用為參照,通過給定偏移 量得到新的引用,返回的引用可以為一個單元格或單元格區域。函數語法如下。OFFSET(reference,rows,cols,[height],[width])

第一參數偏移量的參照點,可以是一個單元格或是一個單元格區域的引用。

第二參數是相對于參照點要偏移的行數。如果偏移量的參照點是一個單元格區域時, 則以單元格區域的左上角單元格開始計算偏移量。如果參數為正數,表示從參照點開始 向下偏移。如果為負數,表示從參照點開始向上偏移。參數值為 0 或是省略參數值表示 不偏移。

第三參數是相對于參照點要偏移的列數。如果偏移量的參照點是一個單元格區域時,則 以單元格區域的左上角單元格開始計算偏移量。如果參數為正數,表示從參照點開始向右偏 移。如果為負數,表示從參照點開始向左偏移。參數值為 0 或是省略參數值表示不偏移。

第四參數用于指定新引用區域的行數,如果省略參數時,新引用區域的行數和參照點的 行數相同。

第五參數用于指定新引用區域的列數,如果省略參數時,新引用區域的列數和參照點的 列數相同。

多數情況下,OFFSET 函數的計算結果會作為其他函數的參數進行進一步的計算處理。如果偏移量超出了工作表邊緣,將返回錯誤值 #REF!。

通過以下兩組圖示,可以更便于理解 OFFSET 函數的偏移方式。

如圖 5-56 所示,使用以下公式返回對 D5 單元格的引用。=OFFSET(A1,4,3)

圖5-56OFFSET 函數偏移圖示 1

公式偏移過程:先確定以 A1 單元格作為偏移的參照點。

第二參數為 4,表示以 A1 為基點向下偏移 4 行,至 A5 單元格。

第三參數為 3,表示自 A5 單元格向右偏移 3 列,至 D5 單元格。

第四參數和第五參數省略,表示新引用范圍的行列數和參照點的行列數相同,都為 1, 即一個單元格。

如圖 5-57 所示,以下公式將返回對 D5:G8 單元格區域的引用。=OFFSET(A1,4,3,4,4)

圖5-57OFFSET 函數偏移圖示 2

公式偏移過程:先確定以 A1 單元格作為偏移的參照點。第二參數為 4,表示以 A1 為 基點向下偏移 4 行,至 A5 單元格。第三參數為 3,表示自 A5 單元格向右偏移 3 列,至 D5 單元格。第四參數和第五參數都為 4,表示以 D5 單元格為左上角,最終返回 4 行 4 列的引用, 即 D5:G8 單元格區域。

本例中,TREND函數的第一參數為“OFFSET(B$8,$K3,0,2)”返回的引用區域,作為 已知關系 y=mx+b 中的 y 值集合部分。

這部分公式中的 $K3,是 MATCH 函數根據 A3 單元格的插值點在對照表中計算出的靠 近或等于該插值點的位置。OFFSET 函數以 B$8 單元格為參照點,根據 $K3 單元格中的計 算結果確定向下偏移的行數。偏移列數為 0,即不偏移,新引用的行數為 2,最終在B列得到與插值點相鄰的兩個單元格的引用。

TREND 函數的第二參數為“OFFSET($A$8,$K3,0,2) ”返回的引用區域,作為已知關系 y=mx+b 中的 x 值集合部分。這部分公式的偏移過程與第一參數的偏移過程相同,以 $A$8 單元格為基點,偏移后最終得到與插值點相鄰的兩行一列的引用。也就是等于或靠近該插值 點的壓力值所在單元格及隨后與之相鄰的一個單元格。

TREND 函數的第三參數為 $A3 單元格中指定的壓力值,作為要返回對應 y 值的新 x 值。

TREND 函數以等于或靠近插值點的兩個單元格作為要計算插值的分段點,使插值計算 的結果精度更高。

當公式向右復制時,“OFFSET(B$8,$K3,0,2)”部分分別返回不同列中的引用作為 y 值 集合,“OFFSET($A$8,$K3,0,2)”部分則始終引用 A 列中的數據作為 x 值集合,從而實現了 各項熱力性質指標的插值計算。

對照表最后一行的壓力值為 4,當 $A3 單元格中的壓力值等于該數值時,OFFSET 函數 偏移后的范圍會包含對照表下方的空白單元格,此時 TREND 函數會返回錯誤值。因此先使用 IF 函數對 $A3 單元格的壓力值進行判斷,如果等于 4,則返回對照表中最后一行對應的指標。

END

推薦閱讀北京大學出版社Excel數據處理與分析應用大全

關鍵詞:

本網站由 財經產業網 版權所有 粵ICP備18023326號-29
聯系我們:85 572 98@qq.com