|
插值法又稱“內(nèi)插法”，主要包括線性插 值、拋物線插值和拉格朗日插值等。其中的線 性插值法是指使用連接兩個已知量的直線，來 確定在這兩個已知量之間的一個未知量的值。相當(dāng)于已知坐標(biāo)（x0, y0）與（x1, y1），要得 到 x0 至 x1 區(qū)間內(nèi)某一位置 x 在直線上的值， 如圖 5-51 所示。Excel 中的 TREND 函數(shù)和 FORECAST 函數(shù)都可以完成簡單的線性插值計算。  圖5-51線性插值法圖示
圖 5-52 所示，是某物體在不同溫度下測 得的電阻值，需要使用插值法預(yù)測在某個指定 溫度時的電阻值。E2 單元格輸入以下公式，計算結(jié)果為 21.0562。=TREND(B2:B5,A2:A5,D2) TREND 函數(shù)的作用是根據(jù)已知 x 序列的值和 y 序列的值，構(gòu)造線性回歸直線方程，然 后根據(jù)構(gòu)造好的直線方程，計算 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ù)是一個邏輯值，如果為 TRUE 或省略，b 將按正常計算。如果為 FALSE，b 將被設(shè)為 0（零）。
圖5-52插值法計算電阻值 本例中，TREND 函數(shù)的 y 值集合為 B2:B5 單元格區(qū)域的電阻值，x 值集合為 A2:A5 單 元格區(qū)域中的溫度值，新 x 值為 D2 單元格中的溫度值。TREND 函數(shù)省略第四參數(shù)，最終以 線性插值法計算出溫度為 -194 度時對應(yīng)的電阻值。 使用以下公式也可實現(xiàn)相同的計算。=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ù)點。第二參數(shù)和第三參數(shù)分別對應(yīng)已知的 y 值和 x 值。FORECAST 函數(shù)的計算結(jié)果與 TREND 函數(shù)的結(jié)果相同。
分段插值計算 在插值計算中，取樣點越多，插值結(jié)果的誤差越小。分段線性插值是將與插值點靠近的 兩個數(shù)據(jù)點使用直線連接，然后在直線上選取對應(yīng)插值點的數(shù)。示例 5-36分段線性插值法計算熱力性質(zhì)圖5-53 所示，是某熱力公司熱力性質(zhì)對照表的部分內(nèi)容，需要根據(jù)對照表中的數(shù)據(jù)， 以分段線性插值法計算在指定壓力下各項熱力性質(zhì)的指標(biāo)。
圖5-53熱力性質(zhì)對照表 步步驟1： K3 單元格輸入以下公式，根據(jù) A3 單元格的插值點在對照表中計算出等于或靠近該 插值點的位置，如圖 5-54 所示。=MATCH(A3,A9:A179,1) 
圖5-54計算靠近該插值點的位置 MATHC 函數(shù)在 A9:A179 單元格區(qū)域中查詢 A3 單元格中壓力值所處的位置。A9:A179 單元格區(qū)域中的壓力值是升序排列，并且 MATCH 函數(shù)第三參數(shù)設(shè)置為 1。因此在查詢不到 具體的值時，將以小于查詢值的最接近值進(jìn)行匹配，并返回其相對位置。步 驟 2： B3 單元格輸入以下公式，向右復(fù)制填充到 I3 單元格，計算出各項熱力性質(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分段線性插值法計算熱力性質(zhì) 公式中用到了 OFFSET 函數(shù)，這個函數(shù)的作用是以指定的引用為參照，通過給定偏移 量得到新的引用，返回的引用可以為一個單元格或單元格區(qū)域。函數(shù)語法如下。OFFSET(reference,rows,cols,[height],[width])
第一參數(shù)偏移量的參照點，可以是一個單元格或是一個單元格區(qū)域的引用。 第二參數(shù)是相對于參照點要偏移的行數(shù)。如果偏移量的參照點是一個單元格區(qū)域時， 則以單元格區(qū)域的左上角單元格開始計算偏移量。如果參數(shù)為正數(shù)，表示從參照點開始 向下偏移。如果為負(fù)數(shù)，表示從參照點開始向上偏移。參數(shù)值為 0 或是省略參數(shù)值表示 不偏移。 第三參數(shù)是相對于參照點要偏移的列數(shù)。如果偏移量的參照點是一個單元格區(qū)域時，則 以單元格區(qū)域的左上角單元格開始計算偏移量。如果參數(shù)為正數(shù)，表示從參照點開始向右偏 移。如果為負(fù)數(shù)，表示從參照點開始向左偏移。參數(shù)值為 0 或是省略參數(shù)值表示不偏移。 第四參數(shù)用于指定新引用區(qū)域的行數(shù)，如果省略參數(shù)時，新引用區(qū)域的行數(shù)和參照點的 行數(shù)相同。 第五參數(shù)用于指定新引用區(qū)域的列數(shù)，如果省略參數(shù)時，新引用區(qū)域的列數(shù)和參照點的 列數(shù)相同。 多數(shù)情況下，OFFSET 函數(shù)的計算結(jié)果會作為其他函數(shù)的參數(shù)進(jìn)行進(jìn)一步的計算處理。如果偏移量超出了工作表邊緣，將返回錯誤值 #REF!。 通過以下兩組圖示，可以更便于理解 OFFSET 函數(shù)的偏移方式。 如圖 5-56 所示，使用以下公式返回對 D5 單元格的引用。=OFFSET(A1,4,3)圖5-56OFFSET 函數(shù)偏移圖示 1公式偏移過程：先確定以 A1 單元格作為偏移的參照點。 第二參數(shù)為 4，表示以 A1 為基點向下偏移 4 行，至 A5 單元格。 第三參數(shù)為 3，表示自 A5 單元格向右偏移 3 列，至 D5 單元格。 第四參數(shù)和第五參數(shù)省略，表示新引用范圍的行列數(shù)和參照點的行列數(shù)相同，都為 1， 即一個單元格。 如圖 5-57 所示，以下公式將返回對 D5:G8 單元格區(qū)域的引用。=OFFSET(A1,4,3,4,4)圖5-57OFFSET 函數(shù)偏移圖示 2公式偏移過程：先確定以 A1 單元格作為偏移的參照點。第二參數(shù)為 4，表示以 A1 為 基點向下偏移 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 單元格的插值點在對照表中計算出的靠 近或等于該插值點的位置。OFFSET 函數(shù)以 B$8 單元格為參照點，根據(jù) $K3 單元格中的計 算結(jié)果確定向下偏移的行數(shù)。偏移列數(shù)為 0，即不偏移，新引用的行數(shù)為 2，最終在 B 列得 到與插值點相鄰的兩個單元格的引用。 TREND 函數(shù)的第二參數(shù)為“OFFSET($A$8,$K3,0,2) ”返回的引用區(qū)域，作為已知關(guān)系 y=mx+b 中的 x 值集合部分。這部分公式的偏移過程與第一參數(shù)的偏移過程相同，以 $A$8 單元格為基點，偏移后最終得到與插值點相鄰的兩行一列的引用。也就是等于或靠近該插值 點的壓力值所在單元格及隨后與之相鄰的一個單元格。 TREND 函數(shù)的第三參數(shù)為 $A3 單元格中指定的壓力值，作為要返回對應(yīng) y 值的新 x 值。 TREND 函數(shù)以等于或靠近插值點的兩個單元格作為要計算插值的分段點，使插值計算 的結(jié)果精度更高。 當(dāng)公式向右復(fù)制時，“OFFSET(B$8,$K3,0,2)”部分分別返回不同列中的引用作為 y 值 集合，“OFFSET($A$8,$K3,0,2)”部分則始終引用 A 列中的數(shù)據(jù)作為 x 值集合，從而實現(xiàn)了 各項熱力性質(zhì)指標(biāo)的插值計算。 對照表最后一行的壓力值為 4，當(dāng) $A3 單元格中的壓力值等于該數(shù)值時，OFFSET 函數(shù) 偏移后的范圍會包含對照表下方的空白單元格，此時 TREND 函數(shù)會返回錯誤值。因此先使用 IF 函數(shù)對 $A3 單元格的壓力值進(jìn)行判斷，如果等于 4，則返回對照表中最后一行對應(yīng)的指標(biāo)。
|
