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

Excel 中的 TREND 函數(shù)和 FORECAST 函數(shù)都可以完成簡單的線性插值計(jì)算。

圖5-51線性插值法圖示

(資料圖片僅供參考)

簡單的插值計(jì)算

線性插值法計(jì)算電阻值

圖 5-52 所示，是某物體在不同溫度下測 得的電阻值，需要使用插值法預(yù)測在某個(gè)指定 溫度時(shí)的電阻值。

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

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

第一參數(shù)指定已知關(guān)系 y=mx+b 中的 y 值集合。

第二參數(shù)指定已知關(guān)系 y=mx+b 中的 x 值集合。

第三參數(shù)指定需要函數(shù) TREND 返回對應(yīng) y 值的新 x 值。

第四參數(shù)是一個(gè)邏輯值，如果為 TRUE 或省略，b 將按正常計(jì)算。如果為 FALSE，b 將被設(shè)為 0（零）。

圖5-52插值法計(jì)算電阻值

本例中，TREND 函數(shù)的 y 值集合為 B2:B5 單元格區(qū)域的電阻值，x 值集合為 A2:A5 單 元格區(qū)域中的溫度值，新 x 值為 D2 單元格中的溫度值。TREND 函數(shù)省略第四參數(shù)，最終以 線性插值法計(jì)算出溫度為 -194 度時(shí)對應(yīng)的電阻值。

使用以下公式也可實(shí)現(xiàn)相同的計(jì)算。=FORECAST(D2,B2:B5,A2:A5)

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

第一參數(shù)是需要進(jìn)行預(yù)測的數(shù)據(jù)點(diǎn)。

第二參數(shù)和第三參數(shù)分別對應(yīng)已知的 y 值和 x 值。

FORECAST 函數(shù)的計(jì)算結(jié)果與 TREND 函數(shù)的結(jié)果相同。

分段插值計(jì)算

在插值計(jì)算中，取樣點(diǎn)越多，插值結(jié)果的誤差越小。分段線性插值是將與插值點(diǎn)靠近的 兩個(gè)數(shù)據(jù)點(diǎn)使用直線連接，然后在直線上選取對應(yīng)插值點(diǎn)的數(shù)。

分段線性插值法計(jì)算熱力性質(zhì)

圖5-53 所示，是某熱力公司熱力性質(zhì)對照表的部分內(nèi)容，需要根據(jù)對照表中的數(shù)據(jù)， 以分段線性插值法計(jì)算在指定壓力下各項(xiàng)熱力性質(zhì)的指標(biāo)。

圖5-53熱力性質(zhì)對照表

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

圖5-54計(jì)算靠近該插值點(diǎn)的位置

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

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

圖5-55分段線性插值法計(jì)算熱力性質(zhì)

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

第一參數(shù)偏移量的參照點(diǎn)，可以是一個(gè)單元格或是一個(gè)單元格區(qū)域的引用。

第二參數(shù)是相對于參照點(diǎn)要偏移的行數(shù)。如果偏移量的參照點(diǎn)是一個(gè)單元格區(qū)域時(shí)， 則以單元格區(qū)域的左上角單元格開始計(jì)算偏移量。如果參數(shù)為正數(shù)，表示從參照點(diǎn)開始 向下偏移。如果為負(fù)數(shù)，表示從參照點(diǎn)開始向上偏移。參數(shù)值為 0 或是省略參數(shù)值表示 不偏移。

第三參數(shù)是相對于參照點(diǎn)要偏移的列數(shù)。如果偏移量的參照點(diǎn)是一個(gè)單元格區(qū)域時(shí)，則 以單元格區(qū)域的左上角單元格開始計(jì)算偏移量。如果參數(shù)為正數(shù)，表示從參照點(diǎn)開始向右偏 移。如果為負(fù)數(shù)，表示從參照點(diǎn)開始向左偏移。參數(shù)值為 0 或是省略參數(shù)值表示不偏移。

第四參數(shù)用于指定新引用區(qū)域的行數(shù)，如果省略參數(shù)時(shí)，新引用區(qū)域的行數(shù)和參照點(diǎn)的 行數(shù)相同。

第五參數(shù)用于指定新引用區(qū)域的列數(shù)，如果省略參數(shù)時(shí)，新引用區(qū)域的列數(shù)和參照點(diǎn)的 列數(shù)相同。

多數(shù)情況下，OFFSET 函數(shù)的計(jì)算結(jié)果會作為其他函數(shù)的參數(shù)進(jìn)行進(jìn)一步的計(jì)算處理。如果偏移量超出了工作表邊緣，將返回錯(cuò)誤值 #REF!。

通過以下兩組圖示，可以更便于理解 OFFSET 函數(shù)的偏移方式。

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

圖5-56OFFSET 函數(shù)偏移圖示 1

公式偏移過程：先確定以 A1 單元格作為偏移的參照點(diǎn)。

第二參數(shù)為 4，表示以 A1 為基點(diǎn)向下偏移 4 行，至 A5 單元格。

第三參數(shù)為 3，表示自 A5 單元格向右偏移 3 列，至 D5 單元格。

第四參數(shù)和第五參數(shù)省略，表示新引用范圍的行列數(shù)和參照點(diǎn)的行列數(shù)相同，都為 1， 即一個(gè)單元格。

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

圖5-57OFFSET 函數(shù)偏移圖示 2

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

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

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

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

TREND 函數(shù)的第三參數(shù)為 $A3 單元格中指定的壓力值，作為要返回對應(yīng) y 值的新 x 值。

TREND 函數(shù)以等于或靠近插值點(diǎn)的兩個(gè)單元格作為要計(jì)算插值的分段點(diǎn)，使插值計(jì)算 的結(jié)果精度更高。

當(dāng)公式向右復(fù)制時(shí)，“OFFSET(B$8,$K3,0,2)”部分分別返回不同列中的引用作為 y 值 集合，“OFFSET($A$8,$K3,0,2)”部分則始終引用 A 列中的數(shù)據(jù)作為 x 值集合，從而實(shí)現(xiàn)了 各項(xiàng)熱力性質(zhì)指標(biāo)的插值計(jì)算。

對照表最后一行的壓力值為 4，當(dāng) $A3 單元格中的壓力值等于該數(shù)值時(shí)，OFFSET 函數(shù) 偏移后的范圍會包含對照表下方的空白單元格，此時(shí) TREND 函數(shù)會返回錯(cuò)誤值。因此先使用 IF 函數(shù)對 $A3 單元格的壓力值進(jìn)行判斷，如果等于 4，則返回對照表中最后一行對應(yīng)的指標(biāo)。

END

推薦閱讀北京大學(xué)出版社Excel數(shù)據(jù)處理與分析應(yīng)用大全

關(guān)鍵詞：