第三章 實(shí)驗(yàn)曲線的數(shù)據(jù)擬合

1、第三章 實(shí)驗(yàn)曲線的數(shù)據(jù)擬合 （一）利用（一）利用Microsoft Excel 進(jìn)行數(shù)據(jù)擬合進(jìn)行數(shù)據(jù)擬合 （二）用（二）用Excel進(jìn)行參數(shù)估計(jì)進(jìn)行參數(shù)估計(jì) （三）利用三）利用Origin進(jìn)行數(shù)據(jù)擬合進(jìn)行數(shù)據(jù)擬合 （四）利用（四）利用Origin進(jìn)行復(fù)雜曲線解譜進(jìn)行復(fù)雜曲線解譜 （五）其他數(shù)據(jù)擬合軟件（五）其他數(shù)據(jù)擬合軟件1、選中數(shù)據(jù)列、選中數(shù)據(jù)列2、繪制散點(diǎn)圖或曲線、繪制散點(diǎn)圖或曲線3、對準(zhǔn)數(shù)據(jù)點(diǎn)（或曲線）點(diǎn)擊右鍵，、對準(zhǔn)數(shù)據(jù)點(diǎn)（或曲線）點(diǎn)擊右鍵，在彈出式菜單中選中在彈出式菜單中選中“添加趨勢線添加趨勢線”類類型型4、在在“添加趨勢線添加趨勢線”對話框中選擇合對話框中選擇合適的回歸分析類型適

2、的回歸分析類型1234（一）利用Microsoft Excel 進(jìn)行數(shù)據(jù)擬合1、選中數(shù)據(jù)列、選中數(shù)據(jù)列2、繪制散點(diǎn)圖或曲線、繪制散點(diǎn)圖或曲線3、對準(zhǔn)數(shù)據(jù)點(diǎn)（或曲線）點(diǎn)擊右鍵，、對準(zhǔn)數(shù)據(jù)點(diǎn)（或曲線）點(diǎn)擊右鍵，在彈出式菜單中選中在彈出式菜單中選中“添加趨勢線添加趨勢線”所添加的三階多所添加的三階多項(xiàng)式趨勢線項(xiàng)式趨勢線對準(zhǔn)趨勢線，右擊鼠標(biāo)鍵，在彈出式對準(zhǔn)趨勢線，右擊鼠標(biāo)鍵，在彈出式菜單中選中菜單中選中“趨勢線格式（趨勢線格式（0）”，在彈出的在彈出的“趨勢線格式趨勢線格式”對話框中可對話框中可以對趨勢線類型和格式進(jìn)行修改。以對趨勢線類型和格式進(jìn)行修改。12由由3階改階改為為4階階濺射率與能量關(guān)系y

3、= -1E-06x4 + 0.0003x3 - 0.0322x2 + 1.3697x - 2.5556R2 = 0.99370510152025020406080100能量（KeV）濺射率（%）濺射率與能量關(guān)系y = 0.116x + 11.833R2 = 0.64010510152025020406080100能量（KeV）濺射率（%）濺射率與能量關(guān)系y = 5.1909Ln(x) - 1.7029R2 = 0.88410510152025020406080100能量（KeV）濺射率（%）濺射率與能量關(guān)系y = -1E-06x4 + 0.0003x3 - 0.0322x2 + 1.3697x

4、 - 2.5556R2 = 0.99370510152025020406080100能量（KeV）濺射率（%）濺射率與能量關(guān)系y = 7E-05x3 - 0.0146x2 + 0.9276x + 0.673R2 = 0.98560510152025020406080100能量（KeV）濺射率（%）線性擬合線性擬合對數(shù)擬合對數(shù)擬合3階多項(xiàng)式擬合階多項(xiàng)式擬合4階多項(xiàng)式擬合階多項(xiàng)式擬合對擬合效果好壞的評判：肉眼觀察對擬合效果好壞的評判：肉眼觀察 & 擬合擬合R2值綜值綜合判斷。一般情況下，合判斷。一般情況下， R2值越大（即越接近于值越大（即越接近于1），），擬合越精確。擬合越精確。注意：當(dāng)

5、注意：當(dāng)R2值大到一值大到一定程度后，有可能定程度后，有可能R2值較大的擬合曲線并值較大的擬合曲線并不是最佳的。例如：不是最佳的。例如：本例中本例中4階多項(xiàng)式擬合階多項(xiàng)式擬合要優(yōu)于要優(yōu)于6階擬合。階擬合。濺射率與能量關(guān)系y = 7E-05x3 - 0.0146x2 + 0.9276x + 0.673R2 = 0.98560510152025020406080100能量（KeV）濺射率（%）濺射率與能量關(guān)系y = -1E-06x4 + 0.0003x3 - 0.0322x2 + 1.3697x - 2.5556R2 = 0.99370510152025020406080100能量（KeV）濺射率

6、（%）濺射率與能量關(guān)系y = -2E-09x6 + 6E-07x5 - 7E-05x4 + 0.0047x3 - 0.1684x2 +3.3606x - 12.667R2 = 0.9970510152025020406080100能量（KeV）濺射率（%）3階多項(xiàng)式擬合階多項(xiàng)式擬合4階多項(xiàng)式擬合階多項(xiàng)式擬合6階多項(xiàng)式擬合階多項(xiàng)式擬合線性參數(shù)估計(jì)線性參數(shù)估計(jì)可以直接利用可以直接利用Excel的分析工具庫中的回歸計(jì)的分析工具庫中的回歸計(jì)算工具實(shí)現(xiàn)。對于算工具實(shí)現(xiàn)。對于非線性參數(shù)估計(jì)非線性參數(shù)估計(jì)則可以通過適當(dāng)?shù)臉?gòu)造則可以通過適當(dāng)?shù)臉?gòu)造，將問題轉(zhuǎn)化成一個(gè)使誤差最小的規(guī)劃問題進(jìn)行解決。，將問題轉(zhuǎn)化成一

7、個(gè)使誤差最小的規(guī)劃問題進(jìn)行解決。（1）線性參數(shù)估計(jì)示例）線性參數(shù)估計(jì)示例： SO2對水的溶解度實(shí)驗(yàn)式可以表示為：對水的溶解度實(shí)驗(yàn)式可以表示為：式中式中P是是SO2的分壓，的分壓，x是水溶液中是水溶液中SO2的摩爾分?jǐn)?shù)。的摩爾分?jǐn)?shù)。 實(shí)驗(yàn)實(shí)測得到的數(shù)據(jù)如下表所示，利用實(shí)驗(yàn)實(shí)測得到的數(shù)據(jù)如下表所示，利用Excel的回歸計(jì)算工的回歸計(jì)算工具求出參數(shù)具求出參數(shù)a和和b的值：的值：（二）用用Excel進(jìn)行參數(shù)估計(jì)進(jìn)行參數(shù)估計(jì)Pbapx442819.3105.73.82.20.80.310.70.50.30.20.150.10.050.02分壓p溶解度x利用公式計(jì)算功能計(jì)算出利用公式計(jì)算功能計(jì)算出第一個(gè)單

8、元格對應(yīng)的第一個(gè)單元格對應(yīng)的P的的平方根平方根通過復(fù)制公式計(jì)算出其他單通過復(fù)制公式計(jì)算出其他單元格對應(yīng)的元格對應(yīng)的P的平方根的平方根在在“工具工具”菜單中選中菜單中選中”加載宏（加載宏（I）”，然后然后在彈出的加載宏對話框中選中在彈出的加載宏對話框中選中“分析工具庫分析工具庫”，以便將相關(guān)分析工具添加到以便將相關(guān)分析工具添加到“工具工具”菜單欄。菜單欄。在在“工具工具”菜單中出菜單中出現(xiàn)了新添加的菜單項(xiàng)。現(xiàn)了新添加的菜單項(xiàng)。1、點(diǎn)擊、點(diǎn)擊“工具工具”菜單欄中菜單欄中的的“數(shù)據(jù)分析（數(shù)據(jù)分析（D）”菜單菜單項(xiàng)，然后在彈出的項(xiàng)，然后在彈出的“數(shù)據(jù)數(shù)據(jù)分析分析”對話框中選中對話框中選中“回回歸歸”；

9、2、設(shè)置、設(shè)置 “回歸回歸”對話對話框中的參數(shù)；框中的參數(shù)；按住鼠標(biāo)左鍵不放，拖拽鼠標(biāo)選中相應(yīng)的數(shù)據(jù)列，將數(shù)據(jù)按住鼠標(biāo)左鍵不放，拖拽鼠標(biāo)選中相應(yīng)的數(shù)據(jù)列，將數(shù)據(jù)表中的表中的“x=”列設(shè)為列設(shè)為Y值；將值；將“p=”和和“p1=”列設(shè)為列設(shè)為X值。值。參數(shù)：參數(shù)：a=0.01618022 b=0.04422551 往往DBS水溶液中投入活性炭，在等溫下放置達(dá)到吸附水溶液中投入活性炭，在等溫下放置達(dá)到吸附平衡，平衡，DBS的平衡濃度的平衡濃度c與投入活性炭吸附量與投入活性炭吸附量q之間的關(guān)系之間的關(guān)系如下表所示：如下表所示：請應(yīng)用非線性最小二乘法估計(jì)下式中的參數(shù)請應(yīng)用非線性最小二乘法估計(jì)下式中的參

10、數(shù)a、b和和。)1 ( /bqacc（2）非線性參數(shù)估計(jì)示例）非線性參數(shù)估計(jì)示例：平衡濃度c1.64.52 6.8 8.1611.512.718.22938.9 57.3吸附量q170.7 228.1 258 283.7 321.3 335.4 378.6 434.6 401.3 4291、輸入、輸入/導(dǎo)入數(shù)據(jù)導(dǎo)入數(shù)據(jù)2、確定其他相關(guān)單元格內(nèi)容：、確定其他相關(guān)單元格內(nèi)容：C列為模型計(jì)算值，列為模型計(jì)算值，D列為誤差列為誤差平方值，平方值，F(xiàn)1F4分別為參數(shù)分別為參數(shù)a、b、和誤差平方和和誤差平方和3、輸入相關(guān)單元格的計(jì)算公式、輸入相關(guān)單元格的計(jì)算公式（見下頁）（見下頁）1、2、3、F4單元格公

11、式： =SUM(D2:D11)D2D11單元格公式： =(C2-B2)2C2C11單元格公式：=$F$2*A2/(1+$F$1*A2$F$3)1 ( /bqacc設(shè)定規(guī)劃求解參數(shù)：設(shè)定規(guī)劃求解參數(shù)：1、將、將F4單元格設(shè)為目標(biāo)單元格；單元格設(shè)為目標(biāo)單元格；2、使目標(biāo)值求、使目標(biāo)值求“最小值最小值”；3、將將F1、F2、F3設(shè)為可變單元格設(shè)為可變單元格;4、約束條件為空；、約束條件為空；5、按、按“求解求解”開始運(yùn)算。開始運(yùn)算。擬合計(jì)算結(jié)果（3）Excel規(guī)劃求解什么是什么是 Excel Excel 規(guī)劃求解工具？規(guī)劃求解工具？ 當(dāng)您想要尋找做某件事的最佳方法時(shí)，使用的就是規(guī)劃求當(dāng)您想要尋找做某

12、件事的最佳方法時(shí)，使用的就是規(guī)劃求解。或者，更正規(guī)的說法就是，當(dāng)您想要在電子表格的某些解?；蛘?，更正規(guī)的說法就是，當(dāng)您想要在電子表格的某些單元格中得到優(yōu)化（最大化或最小化）某個(gè)目標(biāo)的值時(shí)，使單元格中得到優(yōu)化（最大化或最小化）某個(gè)目標(biāo)的值時(shí)，使用的就是規(guī)劃求解。用的就是規(guī)劃求解。優(yōu)化模型包括三部分：目標(biāo)單元格、可變單元格和約束。優(yōu)化模型包括三部分：目標(biāo)單元格、可變單元格和約束。 目標(biāo)單元格目標(biāo)單元格代表目的或目標(biāo)。例如，最大化每月利潤。代表目的或目標(biāo)。例如，最大化每月利潤。 可變單元格可變單元格是電子表格中我們可以進(jìn)行更改或調(diào)整以優(yōu)化是電子表格中我們可以進(jìn)行更改或調(diào)整以優(yōu)化目標(biāo)單元格的單元格。例

13、如，每月每種產(chǎn)品的產(chǎn)量。目標(biāo)單元格的單元格。例如，每月每種產(chǎn)品的產(chǎn)量。 約束約束是置于可變單元格中的限制條件。例如，使用的資源是置于可變單元格中的限制條件。例如，使用的資源不能超標(biāo)，并且不能生產(chǎn)過剩的產(chǎn)品。不能超標(biāo)，并且不能生產(chǎn)過剩的產(chǎn)品。Excel利潤最大化規(guī)劃求解示例假定我們在一家金屬制品公司工作，這家公司可以在他們的工廠生假定我們在一家金屬制品公司工作，這家公司可以在他們的工廠生產(chǎn)六種產(chǎn)品。生產(chǎn)每種產(chǎn)品都需要人工和原材料。產(chǎn)六種產(chǎn)品。生產(chǎn)每種產(chǎn)品都需要人工和原材料。下一頁所示圖片中數(shù)據(jù)表的第下一頁所示圖片中數(shù)據(jù)表的第 4 行（行四）顯示了生產(chǎn)一件的每種行（行四）顯示了生產(chǎn)一件的每種產(chǎn)品所

14、需的人工小時(shí)數(shù)，第產(chǎn)品所需的人工小時(shí)數(shù)，第 5 行顯示了生產(chǎn)一件的每種產(chǎn)品所需的行顯示了生產(chǎn)一件的每種產(chǎn)品所需的原材料的公斤數(shù)。例如，生產(chǎn)一件的產(chǎn)品原材料的公斤數(shù)。例如，生產(chǎn)一件的產(chǎn)品 1 需要需要 6 小時(shí)人工和小時(shí)人工和 3.2 公斤原材料。公斤原材料。第第 6 行顯示了每種產(chǎn)品單件的價(jià)格，第行顯示了每種產(chǎn)品單件的價(jià)格，第 7 行顯示了單件產(chǎn)品的成本，行顯示了單件產(chǎn)品的成本，第第 9 行顯示單件產(chǎn)品可帶來的利潤。例如，產(chǎn)品行顯示單件產(chǎn)品可帶來的利潤。例如，產(chǎn)品 2 的價(jià)格是每件的價(jià)格是每件11.00 美元，每件的單位成本是美元，每件的單位成本是 5.70 美元，每件的利潤就是美元，每件的利

15、潤就是 5.30 美元。美元。第第 8 行顯示了該月對每種產(chǎn)品的需求。例如，對產(chǎn)品行顯示了該月對每種產(chǎn)品的需求。例如，對產(chǎn)品 3 的需求為的需求為 1041件。件。該月可提供該月可提供 4500 人工工時(shí)和人工工時(shí)和 1600 公斤的原材料。公斤的原材料。該公司如何最該公司如何最大化它每月的利潤？大化它每月的利潤？源文件：源文件：利潤最大化規(guī)劃求解示例利潤最大化規(guī)劃求解示例.xls一個(gè)不可行的方案源文件：源文件：利潤最大化規(guī)劃求解示例利潤最大化規(guī)劃求解示例.xls本頁本頁中顯示的方案是不可行的，因?yàn)橐獙?shí)現(xiàn)目標(biāo)利潤中顯示的方案是不可行的，因?yàn)橐獙?shí)現(xiàn)目標(biāo)利潤（7723.00），需要的人工（），需要

16、的人工（6012.5）和原材料（）和原材料（2019.5）均超出了可用的值（可用人工均超出了可用的值（可用人工4500、可用原材料、可用原材料1600）。）。源文件：源文件：利潤最大化規(guī)劃求解示例利潤最大化規(guī)劃求解示例.xls本頁本頁中顯示的方案是可行的（但可能不是最優(yōu)的），因?yàn)橹酗@示的方案是可行的（但可能不是最優(yōu)的），因?yàn)橐獙?shí)現(xiàn)目標(biāo)利潤（要實(shí)現(xiàn)目標(biāo)利潤（7723.00），需要的人工（），需要的人工（6012.5）和）和原材料（原材料（2019.5）均未超出了可用的值（可用人工）均未超出了可用的值（可用人工4500、可用原材料可用原材料1600）。）。源文件：源文件：利潤最大化規(guī)劃求解示例利潤

17、最大化規(guī)劃求解示例.xls利用“規(guī)劃求解”工具優(yōu)化方案 約束條件：約束條件： 1）使用的人工和原材料不能超標(biāo)）使用的人工和原材料不能超標(biāo)。也就是說，單元格。也就是說，單元格 D15:D16（所所用資源）必須小于或等于單元格用資源）必須小于或等于單元格 F15:F16 中的值（可用資源）。中的值（可用資源）。 2）生產(chǎn)的產(chǎn)品不能超過需求數(shù)量）生產(chǎn)的產(chǎn)品不能超過需求數(shù)量。也就是說，單元格。也就是說，單元格 D3:I3（生產(chǎn)生產(chǎn)的每種產(chǎn)品的磅的每種產(chǎn)品的磅 3）不能生產(chǎn)任何產(chǎn)量為負(fù)的產(chǎn)品）不能生產(chǎn)任何產(chǎn)量為負(fù)的產(chǎn)品。源文件：源文件：利潤最大化規(guī)劃求解示例利潤最大化規(guī)劃求解示例.xls通過生產(chǎn)通過生產(chǎn)

18、 1800件件的產(chǎn)品的產(chǎn)品5而不生產(chǎn)任何其他產(chǎn)品，我們的金屬制而不生產(chǎn)任何其他產(chǎn)品，我們的金屬制品公司每月可獲得最高利潤品公司每月可獲得最高利潤6,840.00 美元！我們無法確定通過其美元！我們無法確定通過其他方法是否可以獲得他方法是否可以獲得6,840.00美元的最高利潤。但我們可以確定，美元的最高利潤。但我們可以確定，在我們有限的資源和需求條件下，這個(gè)月的利潤根本不可能超出在我們有限的資源和需求條件下，這個(gè)月的利潤根本不可能超出6,840.00美元。美元。“規(guī)劃求解”結(jié)果一、本例中選擇一、本例中選擇“采用線性模型采用線性模型”的原因是該問題是一種稱為線的原因是該問題是一種稱為線性模型的特

19、殊規(guī)劃求解問題?；旧?，在以下情況下，規(guī)劃求解性模型的特殊規(guī)劃求解問題?；旧?，在以下情況下，規(guī)劃求解模型都是線性模型：模型都是線性模型：（1 1）目標(biāo)單元格是通過將表單的條件（可變單元格）目標(biāo)單元格是通過將表單的條件（可變單元格）* *（約束）（約束）相加進(jìn)行計(jì)算的。相加進(jìn)行計(jì)算的。（2 2）每種約束都滿足線性模型要求。這意味著每種約束都是通過）每種約束都滿足線性模型要求。這意味著每種約束都是通過將表單的條件（可變單元格）將表單的條件（可變單元格）* *（約束）相加，然后將這些總和與（約束）相加，然后將這些總和與某個(gè)常量進(jìn)行比較來評估的。某個(gè)常量進(jìn)行比較來評估的。 如果規(guī)劃求解模型是線性的，

20、并且我們選擇如果規(guī)劃求解模型是線性的，并且我們選擇“采用線性模型采用線性模型”，則規(guī)劃求解保證可以找到規(guī)劃求解模型的最佳解決方案。如果規(guī)則規(guī)劃求解保證可以找到規(guī)劃求解模型的最佳解決方案。如果規(guī)劃求解模型不是線性的，則規(guī)劃求解可能可以找到最佳解決方案，劃求解模型不是線性的，則規(guī)劃求解可能可以找到最佳解決方案，也可能找不到。也可能找不到。 如果規(guī)劃求解模型是線性的，并且我們選擇如果規(guī)劃求解模型是線性的，并且我們選擇“采用線性模型采用線性模型”，規(guī)劃求解將使用一種非常高效的算法（單工方法）來找到該模型規(guī)劃求解將使用一種非常高效的算法（單工方法）來找到該模型的最佳解決方案。如果規(guī)劃求解模型是線性的，并

21、且我們不選擇的最佳解決方案。如果規(guī)劃求解模型是線性的，并且我們不選擇“采用線性模型采用線性模型”，規(guī)劃求解將使用一種非常低效的算法（，規(guī)劃求解將使用一種非常低效的算法（GRG2 GRG2 方法），并且可能很難找到該模型的最佳解決方案。方法），并且可能很難找到該模型的最佳解決方案。 關(guān)于關(guān)于“規(guī)劃求解規(guī)劃求解”的一些提示的一些提示二、在本例中，假定必須滿足對每種產(chǎn)品的需求，那么就必須將二、在本例中，假定必須滿足對每種產(chǎn)品的需求，那么就必須將需求約束從需求約束從 D3:I3 = D9:I9 D3:I3 = D9:I9D3:I3 = D9:I9。當(dāng)單擊當(dāng)單擊“求解求解”時(shí)，您將會(huì)看到時(shí)，您將會(huì)看到“

22、規(guī)劃求解找不到可行的解決方案規(guī)劃求解找不到可行的解決方案”消消息。該消息意味著使用我們有限的資源，無法滿足對所有產(chǎn)品的息。該消息意味著使用我們有限的資源，無法滿足對所有產(chǎn)品的需求。但實(shí)際上我們的模型并沒有錯(cuò)！規(guī)劃求解只是要告訴我們，需求。但實(shí)際上我們的模型并沒有錯(cuò)！規(guī)劃求解只是要告訴我們，如果我們想要滿足對每種產(chǎn)品的需求，我們就需要增加更多的人如果我們想要滿足對每種產(chǎn)品的需求，我們就需要增加更多的人工、更多的原材料或兩者都要增加。工、更多的原材料或兩者都要增加。 三、本例中如果我們允許對每種藥品無限制的需求，并且允許每三、本例中如果我們允許對每種藥品無限制的需求，并且允許每種藥品的產(chǎn)量為負(fù)（清

23、除種藥品的產(chǎn)量為負(fù)（清除“假定非負(fù)假定非負(fù)”復(fù)選框復(fù)選框 ），那么我們將看），那么我們將看到到 “設(shè)置目標(biāo)單元格設(shè)置目標(biāo)單元格的值未收斂的值未收斂”，這表示所用的模型有錯(cuò)，這表示所用的模型有錯(cuò)誤。誤。 四、在計(jì)算利潤時(shí)用到了四、在計(jì)算利潤時(shí)用到了SUMPRODUCT函數(shù)，公式函數(shù)，公式SUMPRODUCT(D10:I10,$D$3:$I$3)SUMPRODUCT(D10:I10,$D$3:$I$3)相當(dāng)于計(jì)算：相當(dāng)于計(jì)算： D10D10* *D3+E10D3+E10* *E3+F10E3+F10* *F3+G10F3+G10* *G3+H10G3+H10* *H3+I10H3+I10* *I3I3，這要比手動(dòng)輸入，這要比手動(dòng)輸入簡單得多！簡單得多！五、五、“$D$3”中的兩個(gè)中的兩個(gè)“$”共同表示對共同表示對D3單元格的絕對引