題 如何在Excel中插入任意數據的中間值


我有像這個例子的數據表,在這種情況下A1:B9的九個條目:

A    B
--   ---
1    2.9
2    5.06
3    7
4    8.84
5    10.87
6    13.24
7    16.22
8    20.25
9    36.7

以上表示B中的非線性增加物理變量的九次測量,例如電壓,並且A恰好表示測量完成的九個圓分鐘中的每一個。

我想創建第二個表,列E和F,行數量是B列中最高值的“下一個整數”。在這種情況下,B9 = 36.7,因此它將有37行。列F1:F37將包含整數1到37,列E必須具有對應於F的數值,與列A到B之間的關係相同。換句話說,插入對應於列F值的列E值。

例如,A3 = 3且B3 = 7。在這種情況下,F7 = 7且E7 = 3,因為B已經包含整數7並且在A列中具有匹配值。但是,F8 = 8,這是B列中未包含的中間值。因此E8將介於3之間4,基於原始數據,必須進行插值。

這個想法是在繪製圖形時,A1:B9將具有與E1:F37相同的形狀。在這個例子中,我將數據表擴展為在原始測量過程中發生的37個整數結果,並且將看到這些值將發生的時間(在E列中,帶小數位)。

我試過的

在嘗試自己解決這個問題時,我能夠找到一個耗時的公式(請注意,在我的嘗試中,我的E和F列與我上面描述的相反)。

  1. 我創建了一個包含B列元素之間差異的列(K)。 K5 = B5-B4。這是每X增量的Y位移。
  2. E列將包含從1開始的連續整數(37),作為B中最大元素的下一個整數值。在這種情況下,B9包含36.7,所以37。
  3. 在F1:F37我輸入以下公式。

Cell F1包含:

=IF(E1>$B$9,$A$9+(E1-$B$9)/$K$9,IF(E1>$B$8,$A$8+(E1-$B$8)
    /$K$9,IF(E1>$B$7,$A$7+(‌​E1-$B$7)/$K$8,IF(E1>$B$6,$A$6+(E1-$B$6)
    /$K$7,IF(E1>$B$5,$A$5+(E1-$B$5)/$K$6,IF(E1‌​>$B$4,$A$4+
    (E1-$B$4)/$K$5,IF(E1>$B$3,$A$3+(E1-$B$3)/$K$4,IF(E1>$B$2,$A$2+
    (E1-$B$2‌​)/$K$3,IF(E1>$B$1,$A$1+(E1-$B$1)/$K$2,E1/$K$1)))))))))  

它工作得很好。但它不是一個自動化的公式;必須輸入與A + B(X + Y)列中的元素一樣多的“IF”。我使用來自A1:B9和E1:F37的線測試散點圖(對於正確的X / Y序列反向),它們生成完全相同的曲線形狀,因此它起作用。

但它不是一個有效的解決方案,因為它需要為每個數據集進行繁瑣的自定義手動過程。我正在尋找一種方法來以更自動化的方式實現這一點,其中包含內置於Excel中的功能,或者至少使用公式的更通用的方法。


4
2017-10-11 00:32


起源


這與您後續問題的重複。為了插值,您需要決定值之間的關係。在你的這個問題中,4階多項式擬合(或過擬合)數據;在隨後的問題中,值之間存在指數關係。一旦你決定了這種關係,那就是應用適當的公式。對於這組數據,您將在1..9行中插入一列等間距值,從1..9開始,然後使用變量 TREND 函數來計算Y值 - Ron Rosenfeld
對於您的兩個問題,所需的活動是REGRESSION,請更改問題的措辭。沒有通用的內置解決方案,但您可以使用正確的關鍵字在網絡上找到有用的插件 - Máté Juhász
@MátéJuhász:沒有X值,它也不是回歸。 :-)但回歸實際上與這裡的要求有點不同。回歸是基於所有數據的近似值,可能實際上不會通過任何數據點。 OP想要的是實際點之間的插值。他沒有指定它的性質,因此線性插值符合要求,但Gary的學生在另一個問題上的答案使用實際的指數曲線。 - fixer1234
紳士,我很遺憾在選擇2 ^ n指數數據時產生第二個問題。大多數答案都是在2 ^ n焦點上。它不應該。這就是為什麼我要回到這裡,原始數據:九個元素,X = 1到9,Y = 2.9,5.06,7,8.84,10.87,13.24,16.22,20.25,36。最高的Y元素是36,我想將X擴展為36個元素,顯然X1 = 1,X12 = 7,X36 = 36,但我需要填充Y相對於X的空白。 - Wagner Lip
我有三個問題,第一個;我的母語不是英語,第二;我沒有強大的數學背景,第三;似乎我的Excel表練習沒有解決方案。我沒有放棄這些問題,我只是按照建議編輯第一篇文章。此外,我可能無法完全使用此論壇。謝謝。 - Wagner Lip


答案:


簡答

插值基於與X和Y值相關的等式。如果您知道實際的等式,則可以直接計算所需的任何中間值。如果不這樣做,則使用近似值進行插值。近似值的質量決定了中間值的精確度。如果您使用有限數量的點逼近曲線,則線性插值將是粗略的。還有其他幾種可以提供更好結果的方法,以及可以完成大部分工作的內置分析工具。

答案很長

您正在尋找一種自動插入中間值的“通用公式”或解決方案。您可以對幾乎任何數據使用線性插值,但如果數據的數據點數量有限並且數據形狀有明顯的曲率,則結果將是粗略的。如果你想要準確的話,沒有“一刀切”的解決方案。給定數據集的最佳解決方案取決於數據的特徵。

方程式

無論你怎麼做,插值都是用定義X和Y之間關係的方程來完成的。方程式可以是實際的也可以是估算。如果是估算值,則有許多不同的方法由數據的性質和您需要完成的內容驅動。

在您的另一個問題中,您使用了基於等式的數據 Y=2^X。如果您有實際的等式,則可以精確插值。為兩者選擇一個新值 X 要么 Y 並且等式給出了另一個值。如果你不知道實際的等式,你需要找到一個近似它的。我將使用這個答案專注於插值方法。這些通常使用內置的分析工具來完成大部分工作。如果您需要有關使用特定工具或更自動化方法的機制的更多詳細信息,我們可以在另一個答案中進行擴展。

試著找出實際的方程式

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

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

trend menu

讓我們試試吧 2^N 您的其他問題的數據。如果您無法識別數字模式並嘗試使用趨勢線方法,則會看到不同形狀曲線的圖標。指數曲線是相同的一般形狀,這將給你這樣:

2^N 

Excel使用 e 而不是 2 作為基礎,這只是一個翻譯(例如0.693 是 2)。在視覺上,您可以看到趨勢線正好跟隨數據。 R2 也告訴你。 [R2 是您用等式計算的數據變化量的統計量度。價值 1 意味著該等式佔變異的100%,或完美契合。

這個問題中的例子也有一種指數形狀。如果您嘗試相同的方法,您會得到以下結果:

exp fit

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

poly 3

作為數據背後的過程的近似,它不是很合適。在第三階(包含X到X ^ 3的冪的等式),它具有比數據更多的主要拐點並且仍然不匹配。因此,基本方程看起來不像一條簡單的常見曲線,這意味著方程式需要近似。

線性插值

這是您在評論中描述的方法。它很簡單,使用簡單的公式,並且相當容易自動化。如果你有很多分數,它們之間的直線足夠接近就足夠了。在許多曲線上,某些區域的短段將接近直線。但是,對於曲線來說,這是一個很差的近似值,並且在具有任何明顯曲率的區域中,您的結果將是不准確的。在您的示例中,X值為7到8之間的區域會有很多曲率。在此區域中,與實際曲線相比的直線如下所示:

straight vs curve

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

回歸

人們建議回歸作為一種方法,在這里和其他帖子。它可以使用趨勢線或它們的底層工作表函數或分析工具來完成(我認為可能在Analysis Toolkit中,可能需要將該選項加載到Excel中,默認情況下可能不會加載)。

回歸試圖使曲線適合您的數據,目的是最小化數據和曲線之間的總誤差。在它的正常使用中,它不是這項任務的正確工具(它是用於擬合趨勢線的方法,你看到了與你需要的相比)。

  • 它適用於您的目標是為數據背後的流程建模的情況。假設數據不准確,回歸表明它應該是什麼。通過回歸找到的曲線可能不會通過任何實際數據點。在您的情況下,數據被給出並假設是準確的。曲線必須通過每個點。

  • 回歸試圖將單個方程擬合到所有數據。如果創建數據的過程不是由可用的方程類型描述,那麼它將無效。對於大量數據點,每個段的線性插值可以比所有數據的回歸曲線更好地近似。

然而,不是以通常的方式使用它,回歸可以被“濫用”作為你想要的解決方法,它通常會起作用。當您嘗試對流程建模時,通常會對最簡單的公式進行評估(Occam的剃刀)。另一方面,通過足夠複雜的等式,您可以適應任何事物。你總是可以畫一個會遍布每一點的塗鴉。同 N 點,你可以找到一個 N-1 有序多項式方程將通過所有點(最壞情況)。

我說“通常”,因為在某些情況下,這是一條非常折磨的線,對你的目的來說是無用的。並且請注意,這種方法並不真正“模擬”任何東西,因為結果方程式可以預測數據范圍之外的行為。

這裡是使用多項式回歸和連續高階方程對數據進行分析(第一個截圖包括訂單3 - 5):

poly 3-5

(單擊圖像可讀大小。)請注意,分析工具包括您想要進行的插值;它生成了中間值。對於每個分析, a(n) 值是它找到的方程的係數。 a(0) 是一個常數, a(1) 是X ^ 1項的係數等。它顯示了R2 適合的價值。它需要虛擬 1 足夠接近你的目的。

我已經突出顯示了最大差異的原始數據值。在這個訂單範圍內,每個連續訂單的擬合變得更好,但更準確地描述哪個特定點可以改變。這是這三個圖表:

poly 3-5 chart

當我們得到第6和第7階多項式時,它看起來像這樣:

poly 6-7

poly 6-7 chart

如果我們為你的9個值找到8階多項式,那就完美了,但是7階可能已經足夠接近了。從透視角度來看,注意7階方程有一個R.2 .99999仍然不完美。

使用回歸分析工具找到足夠的擬合(在本例中為7階或8階方程),將生成所需的中間值。但是,繪製結果並註視曲線以確保它不是塗鴉是一個好主意。

樣條曲線

如果您繪製數據圖表並選擇平滑線條的選項,Excel用於生成樣條線的是樣條曲線。事實上,幾乎所有計算機圖形應用程序(包括字體定義)都基於樣條曲線,用於平滑曲線和曲線過渡。它的命名是繪圖員曾經用於將任意點與曲線連接起來的靈活規則。

樣條曲線為每個部分創建曲線,一次一個部分,考慮相鄰點。曲線穿過每個點,並且在點的兩側沒有突然變化,例如在用直線連接點時得到的變化。

用於樣條曲線的方程式不會嘗試對產生數據的過程進行建模;它看起來很漂亮。但是,大多數過程都遵循某種連續,平滑的曲線。當您處理單個曲線段時,產生大致相似形狀的曲線的許多不同方程將在該段內產生非常相似的值。所以在大多數情況下,樣條曲線會為你想要的東西產生一個很好的近似值(它自然會經過每個點,不像回歸,必須強制通過每個點)。

我再次說“大多數情況”。樣條曲線非常適用於非常均勻和規則的數據,並遵循曲線的“規則”。它可以用不尋常的數據做一些意想不到的事情例如,a 以前的SU問題 是關於這個奇怪的負面“dip”在圖表Excel中產生的數據:

dip

樣條有點像Jello。想像一下Jello的一大塊,你可以限制你想要的特定位置。其餘的Jello將在它需要的地方膨脹。方程式可以定義某些類型的曲線。如果強製曲線通過特定點,則會發生同樣的事情。對於樣條曲線,效果僅限於奇怪的凸起或不自然的曲線段;高階回歸方程可以遵循一條狂野的路徑。

這是樣條曲線表示數據曲線的方式:

spline

spline chart

如果將其與高階回歸曲線進行比較,則樣條曲線對局部變化更具“響應性”。

我使用LibreOffice Calc進行了這個分析,它有一個包含樣條曲線的分析插件。如您所見,這也為樣條線生成了您正在尋找的插值結果。我沒有準備好訪問Excel的Analysis Toolkit,因此我不知道Excel是否包含樣條曲線。如果沒有,LO Calc將在Windows中運行,它是免費的。

底線

這包括可用於插入中間值的方法。可能不同的方法可以更好地處理不同的數據。或者,您的要求可以是近似,快速和簡單的。確定您需要什麼樣的插值。如果您需要有關如何完成它的更多細節,我們可以在另一個答案中解決這些問題。


7
2017-10-17 18:06



親愛的Fixer1234,我甚至無法開始感謝你。我得到的遠遠超出了我的預期。現在我有很長時間處理和玩耍的材料,學習和提高。你在很多方面都是一位完美的老師。我們需要更多像你這樣的人。感謝您的奉獻精神,時間和知識。瓦格納。 - Wagner Lip
@WagnerLip,不客氣。樂意效勞。在某些時候,也許你會為別人做這件事。 :-) - fixer1234
Fixer1234,再次感謝你。自2002年以來,我是一個非常活躍的大型電子論壇參與者,盡可能地提供幫助。幫助人們尋找知識是非常有益的。事實上,這個Excel問題是開發一種方法來幫助論壇中的人們在電子設備中產生更好的旋鈕尺度(角度和標記)。 - Wagner Lip


閱讀你對這個問題的評論和修訂,你想做的一些事情在我之前的回答中並沒有真正涵蓋。這個答案將處理這些項目,並且我已經逐步介紹瞭如何完成整個插值過程。

不准確的數據

您將生成數據的過程描述為以一定時間間隔讀取數據,並且數字是捨入時間。方程式與數據一樣好。在您的實際分析中,您應該使用最精確的數字(也許您只是通過顯示舍入時間來保持您的示例簡單)。

但是,您顯示的數據並不完全符合您通常在物理過程中看到的那種曲線。當只有一個驅動變量且沒有噪聲時,理論曲線通常是平滑的。如果您使用非常精確的設備來觸發預設間隔的讀數並提供準確的測量,您可以接受精確的結果。但是,如果您手動計時讀數並手動讀取,則 X 即使讀數本身是準確的,值也可能是不精確的。轉移個人 X 一點點或另一個值會引入您在數據曲線中看到的各種小的不規則性(除非示例只是為了示例而編寫的數字)。

如果是這種情況,您可能會從使用回歸估計最佳擬合中受益。

使用Y作為X.

在您的問題中,您想要為其定義值 Y (在此示例中,整數值從1到37),並找到關聯的X值。這很容易做到 Y=2^X 問題因為這個簡單的方程很容易被逆轉 X=log(Y)/log(2),您可以直接計算出您想要的任何值。如果方程式不簡單,通常沒有實際的方法來反轉它。我之前的答案中的“濫用”回歸方法為您提供了一個高階方程,但它是“單向”,通常不能解決反向方程。

最簡單的方法就是逆轉 X 和 Y 從頭開始。這為您提供了一個可以與您引入的整數值一起使用的公式(分析為您提供了上一個答案中描述的公式的係數)。

看一條簡單的曲線是否有效就絕對不會受到傷害。這是反向數據,您可以看到沒有合適的數據:

log

所以,嘗試多項式擬合。但是,這是我在上一個答案中描述的情況。從1到8的值很合適,但9給它消化不良。三階多項式給出了一個凸點:

poly3

隨著等式的階數增加,它逐漸變得更“有趣”。按照第7順序,你得到這個:

poly7

它幾乎完全通過每個點,但8到9之間的曲線沒有用。一種解決方案是在8到9之間進行線性插值。但是,在這種情況下,您可以通過合併上端的樣條來獲得更好的值。樣條曲線選項提供了良好的外觀,以及在8到9之間更有意義的曲線:

splines

不幸的是,樣條方程有點卷積並且沒有提供方程。但是,您可以對分析提供的中間值進行線性插值,這樣可以使您非常接近符合合理曲線的數字。

外推與插值

在這個例子中,你的第一個 Y 值是2.9。您想要為其生成值 1 和 2,超出數據范圍。這需要外推而不是插值,這是一個非常不同的要求。

  • 如果已知等式,就像你的 Y=2^X 例如,您可以計算任何您想要的值。

  • 如果已知生成數據的過程遵循簡單的曲線,並且您對擬合有信心,則可以將值投影到數據范圍之外,甚至可以獲得值實際可能的範圍的有意義置信區間(基於數據與數據范圍內的曲線之間有多大的變化。

  • 如果您將高階方程強制擬合到數據中,則數據范圍之外的投影通常是沒有意義的。

  • 如果使用樣條曲線,則無法在數據范圍外進行投影。

無論您在數據范圍之外進行的任何投影都與您使用的等式一樣好,如果您沒有使用精確的等式,您從數據中獲得的距離越遠,它就越不准確。

查看第一個圖形中的對數曲線,您可以看到它將投影與您期望的值非常不同的值。

對於多項式方程,零功率係數是一個常數,這就是為一個常數產生的值。 X 的價值 0。因此,這是一種簡單的方法來查看曲線在該方向上的位置。

zero vals

請注意,按照第4或第5順序,第1點到第8點非常準確。但是一旦超出範圍,方程式的表現就會大不相同。

使用有限數據進行外推

改善事物的一種方法是僅適合該端的點,並包括與該端曲線形狀一樣多的連續點。第9點顯然已經出局了。在此之前曲線中存在幾個變形,一個圍繞點5或6,因此高於該曲線的點遵循不同的曲線。僅使用1到5點,就可以得到與3階多項式完美匹配的結果。該等式將投影0.12095的零點(與上表相比),以及 X 的價值 10.3493

如果您只是在前五個點符合直線,會發生什麼:

straight

這預示著零點-0.5138和一個 X 的 1-0.0071

這一系列可能的結果表明您的數據范圍之外的不確定性水平。沒有正確的答案。這是你曲線的“表現良好”的結尾。該 Y 的價值 X 的 9 是 36.7。你想去37.樣條表明曲線是漸近的 9。在原始數據中投射一條直線會產生一個比一點多的值 9 (與4階多項式相同)。三階多項式表示小於的值 9 (第5和第6個訂單也是如此)。 7階多項式表示基本上高於的值 9。所以數據范圍之外的任何東西都是猜測,或者你想要的任何東西。

把它們放在一起

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

我沒有準備好訪問Excel 2013或Analysis Toolkit。我將使用LibreOffice Calc來說明這一點。它不完全相同,但它足夠接近你應該能夠在Excel中關注它。在LO Calc中,這實際上是一個需要加載的免費擴展。我正在使用 CorelPolyGUI,可以下載 這裡。我對Analysis Toolkit的回憶是它沒有包含樣條線。如果仍然如此,你想在Excel中這樣做,我遇到了 這個免費的插件 (我沒有測試過)。另一種方法是使用LO Calc,它將在Windows中運行並且是免費的。

step 1

在這裡,我在A列和B列中輸入了X和Y值(反轉),並打開了分析對話框。突出顯示X值並單擊X按鈕會加載數據范圍,我選擇了多項式。

step 2

在下一個選項卡上,我指定要使用 0 至 7 度(具有所有順序的7階多項式)。

step 3

要指定輸出,我選擇C1並單擊Columns,它會註冊輸出所需的列。我選擇我希望它輸出原始數據,計算結果,並且我選擇讓它在每個原始數據點之間添加三個中間點。我告訴它我需要一張新圖表上的結果圖表。然後轉到計算菜單並單擊計算。

step 4

它就是。如果查看計算值,您可能會發現問題。它將在下一步中變得明顯。

step 5

在這裡,我添加了 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,但結果是你想要的值的一半。我們可以使用來自的值 3 通過 20但是將另一種方法中的許多值組合起來是沒有意義的。所以,讓我們只使用樣條線來完成整個事情。

step 6

再次打開分析對話框,並在輸入選項卡上將方法更改為“樣條線”(此處未顯示)。給它一個新的輸出範圍並告訴它計算。這就是全部。

step 7

我們有新的結果可供使用。將數據范圍劃分為這麼多段會使每個段都縮短,因此線性插值應該非常好(比在原始數據上使用它更好)。

step 8

曲線擬合或插值的過程涉及創建數據點;使用你自己的判斷曲線“應該”(或不應該),看起來像(回歸假設即使原始數據也是不精確的)。

給這些數據一個健全性檢查表明,即使樣條曲線也會形成一個凸起的連接曲線;一個值稍微超過 9,這可能是一件神器而不是你正在測量的過程的反映。在這種情況下,曲線漸近於 9 更有可能,所以我任意地給高點分配一個小於頭髮的值 9 通過觀察它。假設不是我的價值是精確的,只是它是一種改進。在此圖示中,我創建了一個包含將使用的值的新列。

我添加了一個包含您的數字的列 1 通過 37。從前面的討論中,我們沒有可靠的基礎來預測價值 1 和 2所以我把它們留空了。對於 37,我採用了漸近假設並成功了 9。的值 3 通過 36 通過線性插值找到(它是一個可以適應其他數據的公式)。第三季度的公式是:

=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,這將為您提供目標下方和上方的值。

結果:

result

分析嚮導執行繁重的工作,無論您使用的是多項式回歸還是樣條,它只需要一個公式來生成結果。


3
2017-10-18 21:42