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

插值法又稱“內插法”，主要包括線性插 值、拋物線插值和拉格朗日插值等。其中的線 性插值法是指使用連接兩個已知量的直線，來 確定在這兩個已知量之間的一個未知量的值。相當于已知坐標（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數據處理與分析應用大全

關鍵詞：