如何在 Excel 中插入任意資料的中間值

Question 1

簡答

插值基於關聯 X 和 Y 值的方程式。如果您知道實際方程，則可以直接計算所需的任何中間值。如果不這樣做，則使用近似值進行內插。近似值的品質決定了中間值的準確度。如果您用有限數量的點來逼近一條曲線，則線性內插法將會很粗糙。還有其他幾種方法可以為您提供更好的結果，並且內建分析工具可以完成大部分工作。

長答案

您正在尋找自動插值中間值的“通用公式”或解決方案。您可以對幾乎任何資料使用線性插值，但如果資料點數量有限且資料形狀有明顯曲率，結果將會很粗糙。如果您想要準確性，就沒有「一刀切」的解決方案。給定資料集的最佳解決方案將取決於資料的特徵。

等式

無論您如何操作，插值都是使用定義 X 和 Y 之間關係的方程式來完成的。如果是估計，則有許多不同的方法，這些方法是由資料的性質和您需要完成的任務所驅動的。

在您的另一個問題中，您使用了基於方程式的數據Y=2^X。當你有了實際的方程式時，你可以精確地進行插值。為X或選取一個新值Y，方程式將為您提供另一個值。如果您不知道實際的方程，則需要找到一個近似的方程。我將使用這個答案來關注插值方法。這些通常使用內建分析工具來完成大部分工作。如果您需要有關使用特定工具或更自動化方法的機制的更多詳細信息，我們可以在另一個答案中對此進行擴展。

試著找出實際的方程

最好的解決方案是看看您是否可以確定實際的方程式是什麼。如果您知道產生資料的過程，那可能會告訴您方程式的性質。許多製程在受控條件下處理單一驅動變數且沒有隨機雜訊時，會遵循一條已知方程式類型的簡單曲線。因此，第一步是查看資料的形狀，看看它是否與其中一個相似。

一種簡單的方法是繪製數據圖表並添加趨勢線。 Excel 有許多常用曲線可供嘗試擬合。

讓我們用2^N您其他問題中的數據來嘗試。如果您不認識數字模式並嘗試趨勢線方法，您會看到不同形狀曲線的圖示。指數曲線具有相同的一般形狀，這將給你：

Excel 使用e而不是2作為基礎，這只是一個翻譯（e ^0.693是2）。從視覺上看，您可以看到趨勢線完全遵循數據。 R ²也告訴您這一點。 R ²是一個統計度量，衡量您用方程式解釋的資料變化量。該值1表示方程式解釋了 100% 的變異，或完美擬合。

這個問題中的例子也有某種指數形狀。如果您嘗試相同的方法，您將獲得以下結果：

所以這個數據不是指數級的。我們可以嘗試一個多項式，它描述了一些自然過程並且能夠模擬各種曲線（我稍後會詳細討論）：

作為數據背後過程的近似，它不太適合。在三階（包含 X 到 X^3 的冪的方程式）時，它具有比數據更多的主要拐點，但仍然不匹配。因此，基礎方程式看起來不像一條簡單、常見的曲線，這意味著需要對方程式進行近似。

線性插值

這是您在評論中描述的方法。它很簡單，使用簡單的公式，並且相當容易實現自動化。如果您有很多點，而且它們之間的直線足夠接近，那麼它就足夠了。在許多曲線上，某些區域的短段將接近直線。然而，這對曲線來說是一個很差的近似，並且在具有任何顯著曲率的區域中您的結果將不準確。在您的範例中，X 值 7 和 8 之間的區域將具有很大的曲率。在此區域中，直線與實際曲線相比如下所示：

您正在尋找適用於任何資料的通用解決方案。您可能會發現線性插值對於某些數據來說太粗糙。

回歸

人們在此處和其他帖子中建議將回歸作為一種方法。它可以使用趨勢線或其基礎工作表函數或分析工具來完成（我認為這可能在分析工具包中，這可能需要將該選項載入到 Excel 中，預設可能不會載入）。

迴歸嘗試將曲線擬合到數據，目的是最小化數據和曲線之間的總誤差。在正常使用中，它不是完成此任務的正確工具（它是用於擬合趨勢線的方法，您已經看到了它與您需要的相比如何）。

它適用於您的目標是對資料背後的流程進行建模的情況。假設數據不準確，迴歸顯示數據的真實情況。迴歸找到的曲線可能不會通過任何實際數據點。在您的情況下，給出的數據並假設是準確的。曲線必須經過每個點。
迴歸試圖用一個方程式擬合所有數據。如果創建資料的過程不是透過可嘗試的方程式類型來描述的，那麼它就不會有效。對於大量資料點，每個段的線性內插法可以比所有資料的迴歸曲線更好地近似。

然而，回歸不是以通常的方式使用它，而是可以「濫用」作為您想要的解決方法，而且它通常會起作用。當您嘗試對流程進行建模時，通常會重視最簡單的公式（奧卡姆剃刀）。另一方面，有了足夠複雜的方程，你就可以擬合任何東西。您始終可以畫一條穿過每個點的塗鴉。透過N點，您可以找到一個N-1將通過所有點的階多項式方程式（最壞的情況）。

我說「通常」是因為在某些情況下，這是一條非常折磨人的線路，對您的目的毫無用處。請注意，這種方法並沒有真正「建模」任何東西，因為所得方程式將預測資料範圍之外的行為。

以下是使用多項式迴歸和連續高階方程式對資料進行的分析（第一個螢幕截圖包括階數 3 - 5）：

（按一下圖像以獲得可讀尺寸。）請注意，分析工具包括您想要執行的插值類型；它產生了中間值。對於每次分析，這些a(n)值都是其找到的方程式的係數。 a(0)是常數，是 X^ ¹a(1)項的係數等。它必須是虛擬的，才能足夠接近您的目的。1

我突出顯示了差異最大的原始數據值。在此階數範圍內，每個連續階數的擬合度都會變得更好一些，但更準確地描述哪些特定點可能會改變。這是這三者的圖表：

當我們得到六階和七階多項式時，它看起來像這樣：

如果我們對 9 個值使用 8 階多項式，那將是完美的，但 7 階可能已經足夠接近了。從角度來看，請注意七階方程式的 R ²為 0.99999，但仍不完美。

使用迴歸分析工具找到適當的擬合（在本例中為七階或八階方程式），將產生您想要的中間值。但最好將結果繪製成圖表並觀察曲線以確保它不是潦草的。

樣條曲線

如果您繪製資料圖表並選擇平滑線選項，Excel 將使用樣條線來產生該選項。事實上，幾乎每個電腦圖形應用（包括字體定義）都是基於樣條線來實現平滑曲線和曲線過渡。它以繪圖員曾經用來用曲線連接任意點的彈性規則命名。

樣條線為每個部分建立曲線，一次一個部分，考慮相鄰點。曲線穿過每個點，且該點的兩側沒有突然的變化，就像用直線連接點時所得到的。

用於樣條的方程式並沒有嘗試對產生資料的過程進行建模；而是嘗試對生成資料的過程進行建模。嚴格來說是為了看起來漂亮。然而，大多數過程都遵循某種連續、平滑的曲線。當您處理單一曲線段時，產生形狀大致相似的曲線的許多不同方程式將在該段內產生非常相似的值。因此，在大多數情況下，樣條曲線會產生您想要的良好近似值（並且它自然地穿過每個點，這與回歸不同，回歸必須強制穿過每個點）。

我再說一次「大多數情況」。樣條曲線非常適合非常均勻和規則的數據，並遵循曲線的「規則」。它可以用不尋常的數據做一些意想不到的事情。例如，一個之前的 SU 問題關於 Excel 產生的資料圖表中出現的這種奇怪的負「下降」：

樣條曲線有點像果凍。想像一下一大塊果凍，你可以將特定的位置限制在你想要的地方。果凍的其餘部分會在需要的地方凸出。方程式可以定義某些類型的曲線。如果強製曲線通過特定點，也會發生同樣的情況。對於樣條線，效果僅限於奇怪的凸起或看起來不自然的曲線段；高階回歸方程式可能會走一條瘋狂的路。

這是樣條曲線表示資料曲線的方式：

如果將其與高階迴歸曲線進行比較，您會發現樣條線對局部變化的「響應性」更強。

我使用 LibreOffice Calc 進行了此分析，它有一個包含樣條線的分析插件。正如您所看到的，這也會為樣條線產生您正在尋找的插值結果。我無法立即存取 Excel 的分析工具包，因此我不知道 Excel 是否包含樣條線。如果沒有，LO Calc 將在 Windows 中運行並且是免費的。

底線

這涵蓋了可用於插入中間值的方法。不同的方法對於不同的數據可能效果更好。或者，您的要求可以是任何近似的、快速且簡單的要求。決定您需要哪種插值。如果您需要有關如何完成它的更多詳細信息，我們可以在另一個答案中解決該機制。

Answer

簡答

插值基於關聯 X 和 Y 值的方程式。如果您知道實際方程，則可以直接計算所需的任何中間值。如果不這樣做，則使用近似值進行內插。近似值的品質決定了中間值的準確度。如果您用有限數量的點來逼近一條曲線，則線性內插法將會很粗糙。還有其他幾種方法可以為您提供更好的結果，並且內建分析工具可以完成大部分工作。

長答案

您正在尋找自動插值中間值的“通用公式”或解決方案。您可以對幾乎任何資料使用線性插值，但如果資料點數量有限且資料形狀有明顯曲率，結果將會很粗糙。如果您想要準確性，就沒有「一刀切」的解決方案。給定資料集的最佳解決方案將取決於資料的特徵。

等式

無論您如何操作，插值都是使用定義 X 和 Y 之間關係的方程式來完成的。如果是估計，則有許多不同的方法，這些方法是由資料的性質和您需要完成的任務所驅動的。

在您的另一個問題中，您使用了基於方程式的數據Y=2^X。當你有了實際的方程式時，你可以精確地進行插值。為X或選取一個新值Y，方程式將為您提供另一個值。如果您不知道實際的方程，則需要找到一個近似的方程。我將使用這個答案來關注插值方法。這些通常使用內建分析工具來完成大部分工作。如果您需要有關使用特定工具或更自動化方法的機制的更多詳細信息，我們可以在另一個答案中對此進行擴展。

試著找出實際的方程

最好的解決方案是看看您是否可以確定實際的方程式是什麼。如果您知道產生資料的過程，那可能會告訴您方程式的性質。許多製程在受控條件下處理單一驅動變數且沒有隨機雜訊時，會遵循一條已知方程式類型的簡單曲線。因此，第一步是查看資料的形狀，看看它是否與其中一個相似。

一種簡單的方法是繪製數據圖表並添加趨勢線。 Excel 有許多常用曲線可供嘗試擬合。

讓我們用2^N您其他問題中的數據來嘗試。如果您不認識數字模式並嘗試趨勢線方法，您會看到不同形狀曲線的圖示。指數曲線具有相同的一般形狀，這將給你：

Excel 使用e而不是2作為基礎，這只是一個翻譯（e ^0.693是2）。從視覺上看，您可以看到趨勢線完全遵循數據。 R ²也告訴您這一點。 R ²是一個統計度量，衡量您用方程式解釋的資料變化量。該值1表示方程式解釋了 100% 的變異，或完美擬合。

這個問題中的例子也有某種指數形狀。如果您嘗試相同的方法，您將獲得以下結果：

所以這個數據不是指數級的。我們可以嘗試一個多項式，它描述了一些自然過程並且能夠模擬各種曲線（我稍後會詳細討論）：

作為數據背後過程的近似，它不太適合。在三階（包含 X 到 X^3 的冪的方程式）時，它具有比數據更多的主要拐點，但仍然不匹配。因此，基礎方程式看起來不像一條簡單、常見的曲線，這意味著需要對方程式進行近似。

線性插值

這是您在評論中描述的方法。它很簡單，使用簡單的公式，並且相當容易實現自動化。如果您有很多點，而且它們之間的直線足夠接近，那麼它就足夠了。在許多曲線上，某些區域的短段將接近直線。然而，這對曲線來說是一個很差的近似，並且在具有任何顯著曲率的區域中您的結果將不準確。在您的範例中，X 值 7 和 8 之間的區域將具有很大的曲率。在此區域中，直線與實際曲線相比如下所示：

您正在尋找適用於任何資料的通用解決方案。您可能會發現線性插值對於某些數據來說太粗糙。

回歸

人們在此處和其他帖子中建議將回歸作為一種方法。它可以使用趨勢線或其基礎工作表函數或分析工具來完成（我認為這可能在分析工具包中，這可能需要將該選項載入到 Excel 中，預設可能不會載入）。

迴歸嘗試將曲線擬合到數據，目的是最小化數據和曲線之間的總誤差。在正常使用中，它不是完成此任務的正確工具（它是用於擬合趨勢線的方法，您已經看到了它與您需要的相比如何）。

它適用於您的目標是對資料背後的流程進行建模的情況。假設數據不準確，迴歸顯示數據的真實情況。迴歸找到的曲線可能不會通過任何實際數據點。在您的情況下，給出的數據並假設是準確的。曲線必須經過每個點。
迴歸試圖用一個方程式擬合所有數據。如果創建資料的過程不是透過可嘗試的方程式類型來描述的，那麼它就不會有效。對於大量資料點，每個段的線性內插法可以比所有資料的迴歸曲線更好地近似。

然而，回歸不是以通常的方式使用它，而是可以「濫用」作為您想要的解決方法，而且它通常會起作用。當您嘗試對流程進行建模時，通常會重視最簡單的公式（奧卡姆剃刀）。另一方面，有了足夠複雜的方程，你就可以擬合任何東西。您始終可以畫一條穿過每個點的塗鴉。透過N點，您可以找到一個N-1將通過所有點的階多項式方程式（最壞的情況）。

我說「通常」是因為在某些情況下，這是一條非常折磨人的線路，對您的目的毫無用處。請注意，這種方法並沒有真正「建模」任何東西，因為所得方程式將預測資料範圍之外的行為。

以下是使用多項式迴歸和連續高階方程式對資料進行的分析（第一個螢幕截圖包括階數 3 - 5）：

（按一下圖像以獲得可讀尺寸。）請注意，分析工具包括您想要執行的插值類型；它產生了中間值。對於每次分析，這些a(n)值都是其找到的方程式的係數。 a(0)是常數，是 X^ ¹a(1)項的係數等。它必須是虛擬的，才能足夠接近您的目的。1

我突出顯示了差異最大的原始數據值。在此階數範圍內，每個連續階數的擬合度都會變得更好一些，但更準確地描述哪些特定點可能會改變。這是這三者的圖表：

當我們得到六階和七階多項式時，它看起來像這樣：

如果我們對 9 個值使用 8 階多項式，那將是完美的，但 7 階可能已經足夠接近了。從角度來看，請注意七階方程式的 R ²為 0.99999，但仍不完美。

使用迴歸分析工具找到適當的擬合（在本例中為七階或八階方程式），將產生您想要的中間值。但最好將結果繪製成圖表並觀察曲線以確保它不是潦草的。

樣條曲線

如果您繪製資料圖表並選擇平滑線選項，Excel 將使用樣條線來產生該選項。事實上，幾乎每個電腦圖形應用（包括字體定義）都是基於樣條線來實現平滑曲線和曲線過渡。它以繪圖員曾經用來用曲線連接任意點的彈性規則命名。

樣條線為每個部分建立曲線，一次一個部分，考慮相鄰點。曲線穿過每個點，且該點的兩側沒有突然的變化，就像用直線連接點時所得到的。

用於樣條的方程式並沒有嘗試對產生資料的過程進行建模；而是嘗試對生成資料的過程進行建模。嚴格來說是為了看起來漂亮。然而，大多數過程都遵循某種連續、平滑的曲線。當您處理單一曲線段時，產生形狀大致相似的曲線的許多不同方程式將在該段內產生非常相似的值。因此，在大多數情況下，樣條曲線會產生您想要的良好近似值（並且它自然地穿過每個點，這與回歸不同，回歸必須強制穿過每個點）。

我再說一次「大多數情況」。樣條曲線非常適合非常均勻和規則的數據，並遵循曲線的「規則」。它可以用不尋常的數據做一些意想不到的事情。例如，一個之前的 SU 問題關於 Excel 產生的資料圖表中出現的這種奇怪的負「下降」：

樣條曲線有點像果凍。想像一下一大塊果凍，你可以將特定的位置限制在你想要的地方。果凍的其餘部分會在需要的地方凸出。方程式可以定義某些類型的曲線。如果強製曲線通過特定點，也會發生同樣的情況。對於樣條線，效果僅限於奇怪的凸起或看起來不自然的曲線段；高階回歸方程式可能會走一條瘋狂的路。

這是樣條曲線表示資料曲線的方式：

如果將其與高階迴歸曲線進行比較，您會發現樣條線對局部變化的「響應性」更強。

我使用 LibreOffice Calc 進行了此分析，它有一個包含樣條線的分析插件。正如您所看到的，這也會為樣條線產生您正在尋找的插值結果。我無法立即存取 Excel 的分析工具包，因此我不知道 Excel 是否包含樣條線。如果沒有，LO Calc 將在 Windows 中運行並且是免費的。

底線

這涵蓋了可用於插入中間值的方法。不同的方法對於不同的數據可能效果更好。或者，您的要求可以是任何近似的、快速且簡單的要求。決定您需要哪種插值。如果您需要有關如何完成它的更多詳細信息，我們可以在另一個答案中解決該機制。

Question 2

閱讀您對問題的評論和修改，您會發現有幾件事您想做，但我之前的答案中並未真正涵蓋。這個答案將處理這些項目，並且我已經包含瞭如何完成整個插值過程的逐步演練。

數據不準確

您將產生資料的過程描述為以一定時間間隔取得讀數，數字是四捨五入的時間。方程式的好壞取決於數據。在實際分析中，您應該使用最精確的可用數字（也許您只是透過顯示舍入時間來保持範例簡單）。

但是，您顯示的數據並不完全符合您通常看到的實體過程曲線。當只有一個驅動變數且沒有雜訊時，理論曲線通常是平滑的。如果您使用非常精確的設備以預設的時間間隔觸發讀數並提供準確的測量，則您可以接受精確的結果。然而，如果您手動計時讀數並手動取得讀數，X即使讀數本身準確，這些值也可能會出現不精確的情況。X以某種方式稍微改變各個值將會引入您在資料曲線中看到的各種小不規則性（除非該範例只是您出於範例目的而編造的數字）。

如果是這種情況，您可能會受益於使用迴歸來估計最佳擬合。

使用 Y 作為 X

在您的問題中，您想要定義值Y（本範例中為 1 到 37 之間的整數值），並尋找關聯的 X 值。這在您的問題中很容易做到，Y=2^X因為這個簡單的方程式可以輕鬆反轉為X=log(Y)/log(2)，並且您可以直接計算您想要的任何值。如果方程式不簡單，通常沒有實用的方法來反轉它。我之前的回答中的“濫用”回歸方法為您提供了一個高階方程，但它是“單向”的，通常無法求解逆方程。

最簡單的方法就是逆轉X並Y從頭開始。這為您提供了一個可與您引入的整數值一起使用的方程式（分析為您提供了方程式的係數，如前面的答案所述）。

看看簡單的曲線是否有效總是沒有壞處的。這是反轉後的數據，您可以看到沒有有用的擬合：

因此，嘗試多項式擬合。然而，這就是我在之前的回答中所描述的情況。 1 到 8 的值很合適，但 9 會導致消化不良。三階多項式會帶給你衝擊：

隨著方程式階數的增加，它逐漸變得更加「有趣」。到第 7 個命令時，您將得到：

它幾乎完全穿過每個點，但 8 和 9 之間的曲線沒有用。一種解決方案是在 8 和 9 之間進行線性插值。樣條線選項提供了美觀的擬合，以及在 8 和 9 之間更有意義的曲線：

不幸的是，樣條方程式有點複雜，並且沒有提供方程式。但是，您可以對分析提供的中間值進行線性插值，這應該會使您非常接近適合合理曲線的數字。

外推法與內插法

在此範例中，您的第一個Y值為 2.9。您想要為1和產生2超出資料範圍的值。這需要外推而不是內插，這是一個非常不同的要求。

如果方程式已知，就像您的Y=2^X範例一樣，您可以計算出您想要的任何值。
如果已知產生資料的過程遵循一條簡單曲線，並且您對擬合有信心，則可以將值投影到資料範圍之外，甚至可以為值的實際範圍獲得有意義的置信區間（基於資料和資料範圍內的曲線之間有多少變化）。
如果您強制將高階方程式擬合到數據，則資料範圍之外的投影通常毫無意義。
如果您使用樣條線，則沒有投影到資料範圍之外的基礎。

無論您在資料範圍之外做出什麼預測，其效果都取決於您使用的方程，如果您沒有使用精確的方程，則距離資料越遠，它就越不準確。

查看第一張圖中的對數曲線，您可以看到它投影的值與您預期的值非常不同。

對於多項式方程，零冪係數是一個常數，這就是X的值所產生的值0。因此，這是一種查看曲線朝該方向走向的簡單方法。

請注意，在第 4 或第 5 階中，點 1 到 8 非常準確。但一旦超出該範圍，方程式的表現就會大不相同。

使用有限數據進行推斷

改進的一種方法是僅擬合該端的點，並包括遵循該端曲線形狀的盡可能多的連續點。第9點顯然已經過時了。在此之前，曲線中存在多個拐點，其中一個拐點位於點 5 或 6 附近，因此高於該點的點遵循不同的曲線。僅使用點 1 到 5，您就可以接近與三階多項式的完美擬合。此方程式將預測零點 0.12095（與上表相比），且X值為1, 0.3493。

如果您只將一條直線擬合到前五個點，會發生什麼事：

此投影的零點為 -0.5138，X對於1, -0.0071。

可能結果的範圍顯示資料範圍之外的不確定性程度。沒有正確的答案。這是你曲線「表現良好」的一端。YofX的值為9。36.7您想要轉到 37 9。在原始資料中投影一條直線將產生一個略大於9（與四階多項式相同）的值。三階多項式表示值小於9（五階和六階多項式也是如此）。七階多項式表示值遠高於9。因此，數據範圍之外的任何內容都是猜測，或者是您想要的任何內容。

把它們放在一起

那麼讓我們逐步了解一下實際的解決方案是什麼樣子的。我們假設您已經嘗試找到精確的方程式並使用趨勢線測試了常見曲線。下一步是嘗試回歸，因為這會為您提供曲線公式，並且您可以插入整數值。

我無法立即存取 Excel 2013 或分析工具包。我將使用 LibreOffice Calc 來說明這一點。它並不完全相同，但足夠接近，您應該能夠在 Excel 中遵循它。在LO Calc中，這實際上是一個需要加載的免費擴充功能。我在用著Corel PolyGUI，可以下載這裡。我對分析工具包的記憶是它不包括樣條曲線。如果情況仍然如此，並且您想在 Excel 中執行此操作，我遇到過這個免費的插件（我沒有測試過）。另一種選擇是使用 LO Calc，它將在 Windows 中運行並且免費。

在這裡，我在 A 列和 B 列中輸入了 X 和 Y 值（相反），並開啟了分析對話方塊。反白 X 值並點擊 X 按鈕載入資料範圍，並且我選擇了多項式。

在下一個選項卡上，我指定要使用0度數7（具有所有階數的七階多項式）。

為了指定輸出，我選擇 C1 並點擊“列”，它會註冊輸出所需的列。我選擇希望它輸出原始資料、計算結果，我選擇讓它在每個原始資料點之間添加三個中間點。我告訴它我想要一個新圖表上的結果圖。然後轉到計算選單並點擊計算。

就是這樣。如果您查看計算值，您可能會注意到一個問題。這將在下一步中變得明顯。

在這裡，我添加了1通過37值。此時，我們只想處理插值，因此我添加了一個公式來只計算3通過的值36。此公式僅擴展結果中列出的係數（a(n) 值）。 I2 中的公式為：

=D$4+D$5*H3+D$6*H3^2+D$7*H3^3+D$8*H3^4+D$9*H3^5+D$10*H3^6+D$11*H3^7

這只是每個係數乘以 X 值的相關冪。將其向下拖曳即可得到結果。嗯，不完全是；你必須查看它是否通過了健全性測試。我們知道8和之間存在問題9，但事實證明這只是您想要的值的一半。我們可以使用3through中的值20，但是組合來自其他方法的這麼多值是沒有意義的。因此，我們只使用樣條線來完成整個事情。

再次開啟分析對話框，並將輸入標籤上的方法變更為「樣條線」（此處未顯示）。給它一個新的輸出範圍並告訴它計算。僅此而已。

我們有新的成果可供使用。將資料範圍劃分為這麼多段可以使每個段都很短，因此線性插值應該非常好（比在原始資料上使用它要好得多）。

曲線擬合或插值的過程涉及創建數據點；使用您自己的判斷來判斷曲線「應該」（或不應該）是什麼樣子（迴歸假設原始資料也不精確）。

對這些數據進行健全性檢查表明，即使樣條曲線也會產生帶有凸起的連接曲線；一個值稍微超過9，這可能是一個偽影，而不是您正在測量的過程的反映。在這種情況下，曲線漸近於處的可能性更大，因此我隨意為高點指定了一個比目測9小一點的值。9假設並不是我的值是精確的，只是它是一種改進。對於本例，我建立了一個新列，其中包含將使用的值。

1我通過添加了一列包含您的號碼37。從前面的討論來看，我們沒有可靠的基礎來預測1和的值2，因此我將它們留空。因為37，我採用了漸近假設並做出了它9。3through的值36是透過線性插值找到的（這是一個可以適應其他數據的公式）。 Q3中的公式為：

=TREND(OFFSET($M$1,MATCH(P3,M$1:M$33)-1,2,2),OFFSET($M$1,MATCH(P3,M$1:M$33)-1,0,2),P3)

TREND 函數僅在範圍為兩點時進行內插。語法是：

TREND(Y_range, X_range, X_value)

OFFSET 函數用於每個範圍。在每種情況下，它都使用 MATCH 函數來尋找包含目標值的範圍的第一行。這些-1值是因為這些是偏移量而不是位置；第一行中的匹配是相0對於參考行的偏移量。請注意，在本例中，該Y列偏移了2，因為我新增了一個額外的列來手動調整值。 OFFSET 參數選擇包含 Y 或 X 值的列，並選擇範圍高度 2，這將為您提供低於和高於目標的值。

結果：

分析精靈完成了繁重的工作，無論您使用多項式迴歸還是樣條曲線，它只需要一個公式即可產生結果。

Answer

閱讀您對問題的評論和修改，您會發現有幾件事您想做，但我之前的答案中並未真正涵蓋。這個答案將處理這些項目，並且我已經包含瞭如何完成整個插值過程的逐步演練。

數據不準確

您將產生資料的過程描述為以一定時間間隔取得讀數，數字是四捨五入的時間。方程式的好壞取決於數據。在實際分析中，您應該使用最精確的可用數字（也許您只是透過顯示舍入時間來保持範例簡單）。

但是，您顯示的數據並不完全符合您通常看到的實體過程曲線。當只有一個驅動變數且沒有雜訊時，理論曲線通常是平滑的。如果您使用非常精確的設備以預設的時間間隔觸發讀數並提供準確的測量，則您可以接受精確的結果。然而，如果您手動計時讀數並手動取得讀數，X即使讀數本身準確，這些值也可能會出現不精確的情況。X以某種方式稍微改變各個值將會引入您在資料曲線中看到的各種小不規則性（除非該範例只是您出於範例目的而編造的數字）。

如果是這種情況，您可能會受益於使用迴歸來估計最佳擬合。

使用 Y 作為 X

在您的問題中，您想要定義值Y（本範例中為 1 到 37 之間的整數值），並尋找關聯的 X 值。這在您的問題中很容易做到，Y=2^X因為這個簡單的方程式可以輕鬆反轉為X=log(Y)/log(2)，並且您可以直接計算您想要的任何值。如果方程式不簡單，通常沒有實用的方法來反轉它。我之前的回答中的“濫用”回歸方法為您提供了一個高階方程，但它是“單向”的，通常無法求解逆方程。

最簡單的方法就是逆轉X並Y從頭開始。這為您提供了一個可與您引入的整數值一起使用的方程式（分析為您提供了方程式的係數，如前面的答案所述）。

看看簡單的曲線是否有效總是沒有壞處的。這是反轉後的數據，您可以看到沒有有用的擬合：

因此，嘗試多項式擬合。然而，這就是我在之前的回答中所描述的情況。 1 到 8 的值很合適，但 9 會導致消化不良。三階多項式會帶給你衝擊：

隨著方程式階數的增加，它逐漸變得更加「有趣」。到第 7 個命令時，您將得到：

它幾乎完全穿過每個點，但 8 和 9 之間的曲線沒有用。一種解決方案是在 8 和 9 之間進行線性插值。樣條線選項提供了美觀的擬合，以及在 8 和 9 之間更有意義的曲線：

不幸的是，樣條方程式有點複雜，並且沒有提供方程式。但是，您可以對分析提供的中間值進行線性插值，這應該會使您非常接近適合合理曲線的數字。

外推法與內插法

在此範例中，您的第一個Y值為 2.9。您想要為1和產生2超出資料範圍的值。這需要外推而不是內插，這是一個非常不同的要求。

如果方程式已知，就像您的Y=2^X範例一樣，您可以計算出您想要的任何值。
如果已知產生資料的過程遵循一條簡單曲線，並且您對擬合有信心，則可以將值投影到資料範圍之外，甚至可以為值的實際範圍獲得有意義的置信區間（基於資料和資料範圍內的曲線之間有多少變化）。
如果您強制將高階方程式擬合到數據，則資料範圍之外的投影通常毫無意義。
如果您使用樣條線，則沒有投影到資料範圍之外的基礎。

無論您在資料範圍之外做出什麼預測，其效果都取決於您使用的方程，如果您沒有使用精確的方程，則距離資料越遠，它就越不準確。

查看第一張圖中的對數曲線，您可以看到它投影的值與您預期的值非常不同。

對於多項式方程，零冪係數是一個常數，這就是X的值所產生的值0。因此，這是一種查看曲線朝該方向走向的簡單方法。

請注意，在第 4 或第 5 階中，點 1 到 8 非常準確。但一旦超出該範圍，方程式的表現就會大不相同。

使用有限數據進行推斷

改進的一種方法是僅擬合該端的點，並包括遵循該端曲線形狀的盡可能多的連續點。第9點顯然已經過時了。在此之前，曲線中存在多個拐點，其中一個拐點位於點 5 或 6 附近，因此高於該點的點遵循不同的曲線。僅使用點 1 到 5，您就可以接近與三階多項式的完美擬合。此方程式將預測零點 0.12095（與上表相比），且X值為1, 0.3493。

如果您只將一條直線擬合到前五個點，會發生什麼事：

此投影的零點為 -0.5138，X對於1, -0.0071。

可能結果的範圍顯示資料範圍之外的不確定性程度。沒有正確的答案。這是你曲線「表現良好」的一端。YofX的值為9。36.7您想要轉到 37 9。在原始資料中投影一條直線將產生一個略大於9（與四階多項式相同）的值。三階多項式表示值小於9（五階和六階多項式也是如此）。七階多項式表示值遠高於9。因此，數據範圍之外的任何內容都是猜測，或者是您想要的任何內容。

把它們放在一起

那麼讓我們逐步了解一下實際的解決方案是什麼樣子的。我們假設您已經嘗試找到精確的方程式並使用趨勢線測試了常見曲線。下一步是嘗試回歸，因為這會為您提供曲線公式，並且您可以插入整數值。

我無法立即存取 Excel 2013 或分析工具包。我將使用 LibreOffice Calc 來說明這一點。它並不完全相同，但足夠接近，您應該能夠在 Excel 中遵循它。在LO Calc中，這實際上是一個需要加載的免費擴充功能。我在用著Corel PolyGUI，可以下載這裡。我對分析工具包的記憶是它不包括樣條曲線。如果情況仍然如此，並且您想在 Excel 中執行此操作，我遇到過這個免費的插件（我沒有測試過）。另一種選擇是使用 LO Calc，它將在 Windows 中運行並且免費。

在這裡，我在 A 列和 B 列中輸入了 X 和 Y 值（相反），並開啟了分析對話方塊。反白 X 值並點擊 X 按鈕載入資料範圍，並且我選擇了多項式。

在下一個選項卡上，我指定要使用0度數7（具有所有階數的七階多項式）。

為了指定輸出，我選擇 C1 並點擊“列”，它會註冊輸出所需的列。我選擇希望它輸出原始資料、計算結果，我選擇讓它在每個原始資料點之間添加三個中間點。我告訴它我想要一個新圖表上的結果圖。然後轉到計算選單並點擊計算。

就是這樣。如果您查看計算值，您可能會注意到一個問題。這將在下一步中變得明顯。

在這裡，我添加了1通過37值。此時，我們只想處理插值，因此我添加了一個公式來只計算3通過的值36。此公式僅擴展結果中列出的係數（a(n) 值）。 I2 中的公式為：

=D$4+D$5*H3+D$6*H3^2+D$7*H3^3+D$8*H3^4+D$9*H3^5+D$10*H3^6+D$11*H3^7

這只是每個係數乘以 X 值的相關冪。將其向下拖曳即可得到結果。嗯，不完全是；你必須查看它是否通過了健全性測試。我們知道8和之間存在問題9，但事實證明這只是您想要的值的一半。我們可以使用3through中的值20，但是組合來自其他方法的這麼多值是沒有意義的。因此，我們只使用樣條線來完成整個事情。

再次開啟分析對話框，並將輸入標籤上的方法變更為「樣條線」（此處未顯示）。給它一個新的輸出範圍並告訴它計算。僅此而已。

我們有新的成果可供使用。將資料範圍劃分為這麼多段可以使每個段都很短，因此線性插值應該非常好（比在原始資料上使用它要好得多）。

曲線擬合或插值的過程涉及創建數據點；使用您自己的判斷來判斷曲線「應該」（或不應該）是什麼樣子（迴歸假設原始資料也不精確）。

對這些數據進行健全性檢查表明，即使樣條曲線也會產生帶有凸起的連接曲線；一個值稍微超過9，這可能是一個偽影，而不是您正在測量的過程的反映。在這種情況下，曲線漸近於處的可能性更大，因此我隨意為高點指定了一個比目測9小一點的值。9假設並不是我的值是精確的，只是它是一種改進。對於本例，我建立了一個新列，其中包含將使用的值。

1我通過添加了一列包含您的號碼37。從前面的討論來看，我們沒有可靠的基礎來預測1和的值2，因此我將它們留空。因為37，我採用了漸近假設並做出了它9。3through的值36是透過線性插值找到的（這是一個可以適應其他數據的公式）。 Q3中的公式為：

=TREND(OFFSET($M$1,MATCH(P3,M$1:M$33)-1,2,2),OFFSET($M$1,MATCH(P3,M$1:M$33)-1,0,2),P3)

TREND 函數僅在範圍為兩點時進行內插。語法是：

TREND(Y_range, X_range, X_value)

OFFSET 函數用於每個範圍。在每種情況下，它都使用 MATCH 函數來尋找包含目標值的範圍的第一行。這些-1值是因為這些是偏移量而不是位置；第一行中的匹配是相0對於參考行的偏移量。請注意，在本例中，該Y列偏移了2，因為我新增了一個額外的列來手動調整值。 OFFSET 參數選擇包含 Y 或 X 值的列，並選擇範圍高度 2，這將為您提供低於和高於目標的值。

結果：

分析精靈完成了繁重的工作，無論您使用多項式迴歸還是樣條曲線，它只需要一個公式即可產生結果。

如何在 Excel 中插入任意資料的中間值

我嘗試過的

答案1

簡答

長答案

等式

試著找出實際的方程

線性插值

回歸

樣條曲線

底線

答案2

數據不準確

使用 Y 作為 X

外推法與內插法

使用有限數據進行推斷

把它們放在一起

相關內容