Excel在數(shù)學(xué)建模中的應(yīng)用課件

1、Excel在數(shù)學(xué)建模中的應(yīng)用1數(shù)學(xué)建模培訓(xùn)Excel的應(yīng)用技巧主講：曹清潔Excel在數(shù)學(xué)建模中的應(yīng)用2nExcel基本知識nExcel在數(shù)學(xué)建模中的高級應(yīng)用技巧 Excel在數(shù)學(xué)建模中的應(yīng)用3Excel基本知識n手動輸入數(shù)據(jù)手動輸入數(shù)據(jù) n自動輸入數(shù)據(jù)自動輸入數(shù)據(jù) n公式的使用公式的使用n函數(shù)的引用函數(shù)的引用n數(shù)據(jù)管理與分析數(shù)據(jù)管理與分析 Excel在數(shù)學(xué)建模中的應(yīng)用4手動輸入數(shù)據(jù)手動輸入數(shù)據(jù)n文本輸入文本輸入默認方式為“左對齊”.由數(shù)字組成的字符串前加一個號 .n數(shù)值輸入數(shù)值輸入默認“右對齊”方式.輸入分數(shù)時，要先輸入“0”和空格，否則作為日期型數(shù)據(jù)處理。 Excel在數(shù)學(xué)建模中的應(yīng)用5自

2、動輸入數(shù)據(jù)自動輸入數(shù)據(jù) n自動填充鼠標(biāo)拖動填充柄向下、右拖數(shù)字自動填充鼠標(biāo)拖動填充柄向下、右拖數(shù)字增量為增量為1，向上、左拖增量為，向上、左拖增量為-1 .n自定義序列自定義序列 工具選項新序列輸入序列內(nèi)容，每個序列間按回車鍵添加 選中序列區(qū)域工具選項導(dǎo)入確定 Excel在數(shù)學(xué)建模中的應(yīng)用6Excel在數(shù)學(xué)建模中的應(yīng)用7公式的使用公式的使用用公式計算用公式計算 所有的公式運算均以所有的公式運算均以“=”開頭開頭。錯誤的公式以#開頭。（1）數(shù)學(xué)運算符：）數(shù)學(xué)運算符： +、-、*、/、%（計算準(zhǔn)則：先乘方，再乘、除，后加、減）（2）文本連接符：）文本連接符：&將兩段用雙引號（英文狀態(tài)下）括

3、起的文本連接為一段連續(xù)的文本。（3）比較運算符）比較運算符：、=、=、（用于比較兩個數(shù)據(jù)的大小，比較結(jié)果是一個邏輯值，即TRUE（真）或FALSE（假）。（4）引用運算符：）引用運算符：冒號、空格、逗號 冒號：冒號：用于定義一個單元格區(qū)域，以便在公式中使用。 例：=Sum(A3：A7) A3至A7的內(nèi)容之和 空格：空格：交集運算符，表示只處理幾個單元格區(qū)域之間互相重疊部分。 例：=Sum（A3：A5 A4：A7）A4+A5的結(jié)果 逗號：逗號：并集運算符，用于連接兩個或多個單元格區(qū)域。 例：=Sum(A3:A5,A4:A7)A3、A4、A5及A4、A5、A6、A7之和(單元格有可能重復(fù))。（5）

4、運算符優(yōu)先級）運算符優(yōu)先級 （）%*、/+、-、&比較運算符。（若優(yōu)先級相同，則按從左到右的順序計算） Excel在數(shù)學(xué)建模中的應(yīng)用8函數(shù)的引用函數(shù)的引用n手工輸入：手工輸入：在編輯欄或單元格內(nèi)輸入n引用單元格地址引用單元格地址:Excel在數(shù)學(xué)建模中的應(yīng)用9n相對引用相對引用 利用單元格間的相對位置關(guān)系引用單元格內(nèi)容。公式中的相對引用隨單元格的移動而修改，但原來的位置不變。 例：打開文件“數(shù)據(jù)編輯”，在工作表“電視機的銷售統(tǒng)計表” 的G3中輸入“=E3*F3，則可在G4、G5、G6、G7、G8中填入相應(yīng)公式來計算商品的銷售金額。n絕對引用絕對引用 指引用單元格和被引用單元格位置關(guān)系是

5、固定的，公式中的引用不隨單元格地址變化而變化。 通常在地址前加“$”表示絕對引用。 例：打開文件“數(shù)據(jù)編輯”，計算其金額與利潤值。 計算金額：在G3中輸入：=E3*F3，并將之復(fù)制至G8。 計算利潤：在I3中輸入：=G3-（G3*$G$1+H3+E3），然后復(fù)制到I8n混合引用混合引用 即采用行為相對地址、列為絕對地址或列為相對地址、行為絕對地址來表示地址。 Excel在數(shù)學(xué)建模中的應(yīng)用10Excel的數(shù)據(jù)管理和分析的數(shù)據(jù)管理和分析1設(shè)計數(shù)據(jù)清單設(shè)計數(shù)據(jù)清單2記錄編輯記錄編輯3排序數(shù)據(jù)排序數(shù)據(jù)4篩選數(shù)據(jù)篩選數(shù)據(jù)5分類匯總數(shù)據(jù)分類匯總數(shù)據(jù)Excel在數(shù)學(xué)建模中的應(yīng)用111 設(shè)計數(shù)據(jù)清單設(shè)計數(shù)據(jù)清

6、單數(shù)據(jù)清單的條件工作表中只含有一張數(shù)據(jù)表數(shù)據(jù)表應(yīng)在第1行設(shè)置列標(biāo)題數(shù)據(jù)表不應(yīng)含有空行或空列Excel在數(shù)學(xué)建模中的應(yīng)用122記錄編輯記錄編輯n逐條顯示n添加記錄n刪除記錄n查詢記錄Excel在數(shù)學(xué)建模中的應(yīng)用133排序數(shù)據(jù)排序數(shù)據(jù)n排序依據(jù)：主要關(guān)鍵字第二關(guān)鍵字第三關(guān)鍵字n排序選項：自定義排序次序區(qū)分大小寫排序方向排序方法Excel在數(shù)學(xué)建模中的應(yīng)用144篩選數(shù)據(jù)篩選數(shù)據(jù)n篩選種類：自動篩選、高級篩選n自動篩選方式：前10個自定義Excel在數(shù)學(xué)建模中的應(yīng)用155分類匯總數(shù)據(jù)分類匯總數(shù)據(jù)n匯總步驟按匯總依據(jù)字段排序指定匯總依據(jù)字段設(shè)定匯總方式指定匯總字段n匯總顯示控制Excel在數(shù)學(xué)建模中的應(yīng)

7、用16數(shù)據(jù)透視表n功能按多個字段分類匯總n步驟指定數(shù)據(jù)源選定數(shù)據(jù)區(qū)域決定布局確定位置Excel在數(shù)學(xué)建模中的應(yīng)用172eExcel在數(shù)學(xué)建模中的應(yīng)用18Excel在數(shù)學(xué)建模中的高級應(yīng)用技巧uExcel的數(shù)據(jù)處理功能u用Excel繪制圖表u相關(guān)與回歸分析Excel在數(shù)學(xué)建模中的應(yīng)用19一、Excel的數(shù)據(jù)處理功能1、Excel的函數(shù) 函 數(shù)的結(jié)構(gòu)以函數(shù)名稱開始，后面是左圓括號、以逗號分隔的參數(shù)和右圓括號。 (1)函數(shù)以公式的形式出現(xiàn)，請在函數(shù)名稱前面鍵入等號（=）。 如： =sumsum(A1,A4:A10,56)Excel在數(shù)學(xué)建模中的應(yīng)用20（2）使用函數(shù)向?qū)Р迦牒瘮?shù)：按fx即可調(diào)入粘貼函數(shù)

8、對話框1101122222213153AExcel在數(shù)學(xué)建模中的應(yīng)用21例1：已知某班級學(xué)生成績，統(tǒng)計其中某科成績?yōu)?0分（含）-90分（不含）的人數(shù)。例2：計算3ln2 例3：計算 的值。 例4：求矩陣 的逆矩陣1 1 2 1 2 3 1 2 3 41*23 3 5 3 5 7 3 5 7 9 sin ,0cos ,0 xxx xyex x注：鼠標(biāo)先選中一塊4*4區(qū)，先按F2鍵，再同時按下Shift+Ctrl+Enter三個鍵，則選定區(qū)域內(nèi)出現(xiàn)逆矩陣的計算結(jié)果。Excel在數(shù)學(xué)建模中的應(yīng)用22例5：利用自定義函數(shù)進行計算（1）當(dāng)x=3,2,1,0,-1,-2,-3時，計算分段函數(shù) 的值。（2

9、）用迭代法求非線性方程x-cosx=0的數(shù)值解，迭代公式是xk=cos(xk-1)，取x0=1,試用Excel計算，要求精度達到10-12。（3）利用公式 計算 的近似值，使誤差小于10-14。Excel在數(shù)學(xué)建模中的應(yīng)用232、Excel的數(shù)據(jù)分析功能 “數(shù)據(jù)分析”的統(tǒng)計分析工具包，包含方差分析、回歸分析、協(xié)方差和相關(guān)系數(shù)、傅里葉分析等分析工具 首次使用時需要進行安裝，方法如下：（1）點擊“工具”-”加載宏”；（2）在彈出對話框中列出各種可以加載的項目，按照需要選擇“分析工具庫”、“規(guī)劃求解”等等項目，點“確定”。安裝完后，“工具”菜單中多出了“數(shù)據(jù)分析”子菜單，點擊它，彈出對話框，顯示各種

10、數(shù)據(jù)分析工具。Excel在數(shù)學(xué)建模中的應(yīng)用24Excel在數(shù)學(xué)建模中的應(yīng)用25Excel在數(shù)學(xué)建模中的應(yīng)用26數(shù)據(jù)分析功能：1、描述統(tǒng)計 主要統(tǒng)計數(shù)據(jù)的平均值、中位數(shù)、標(biāo)準(zhǔn)差、方差等等統(tǒng)計量例6：某煉鋼廠測了120種爐鋼中的Si含量，得到120個原始數(shù)據(jù)，見Excel. Excel在數(shù)學(xué)建模中的應(yīng)用27Excel在數(shù)學(xué)建模中的應(yīng)用28Excel在數(shù)學(xué)建模中的應(yīng)用292、直方圖分析工具分析工具 直方圖分析工具可完成數(shù)據(jù)的分組、頻數(shù)分布與累積頻數(shù)的計算、繪制直方圖與累積折線圖等一系列操作。以例7為操作范例，闡述直方圖分析工具的統(tǒng)計整理功能，其操作過程如下。 直方圖是一大批數(shù)據(jù)的頻率分布圖，由直方圖

11、可以觀察和分析數(shù)據(jù)的概率分布。見數(shù)據(jù)分析實例例7 ：根據(jù)抽樣調(diào)查，某月某市50戶居民購買消費品支出資料。Excel在數(shù)學(xué)建模中的應(yīng)用30Excel在數(shù)學(xué)建模中的應(yīng)用31首先，將樣本數(shù)據(jù)排成一列，最好對數(shù)據(jù)進行排序，本例中已利用排序操作排好序，為A1：A51。輸入分組標(biāo)志，本例中為B1：B10，分別是899、999、1099、1199、1299、1399、1499、1599、1699（如圖所示）22 s i nl n ( 1)y x x Excel在數(shù)學(xué)建模中的應(yīng)用32 第一步：單擊“工具”菜單，選擇“數(shù)據(jù)分析”選項；打開“數(shù)據(jù)分析”對話框，從“分析工具”列表中選擇“直方圖”選項，（如圖1所示）

12、Excel在數(shù)學(xué)建模中的應(yīng)用33第二步：打開“直方圖”對話框，確定輸入?yún)^(qū)域、接收區(qū)域和輸出區(qū)域，（如圖所示）Excel在數(shù)學(xué)建模中的應(yīng)用34n（1）“輸入?yún)^(qū)域”輸入待分析數(shù)據(jù)區(qū)域的單元格引用，若輸入?yún)^(qū)域有標(biāo)志項，則選中“標(biāo)志”復(fù)選框；否則，系統(tǒng)自動生成數(shù)據(jù)標(biāo)志?！敖邮諈^(qū)域”輸入接收區(qū)域的單元格引用，該框可為空，則系統(tǒng)自動利用輸入?yún)^(qū)域中的最小值和最大值建立平均分布的區(qū)間間隔的分組。本例中輸入?yún)^(qū)域為$A$2:$A$51，接收區(qū)域為$B$2:$B$10。n（2）在“輸出”選項中可選擇輸出去向，輸出去向類似于“抽樣”對話框的輸出去向。本例中選擇“輸出區(qū)域”為$C$1。n（3）選擇“柏拉圖”可以在輸出表

13、中同時按降序排列頻數(shù)數(shù)據(jù)；選擇“累積百分率”可在輸出表中增加一列累積百分比數(shù)值，并繪制一條百分比曲線；選擇“圖表輸出”可生成一個嵌入式直方圖。Excel在數(shù)學(xué)建模中的應(yīng)用35第三步：單擊“確定”按鈕，在輸出區(qū)域單元格可得到頻數(shù)分布，（如圖所示）Excel在數(shù)學(xué)建模中的應(yīng)用36第四步：將條形圖轉(zhuǎn)換成標(biāo)準(zhǔn)直方圖 （如圖所示） Excel在數(shù)學(xué)建模中的應(yīng)用37二、用Excel繪制圖表 圖表是數(shù)據(jù)表現(xiàn)的另一種形式。數(shù)據(jù)的圖表化就是將單元格中的數(shù)據(jù)以各種統(tǒng)計圖的形式顯示。1.圖表種類圖表種類（1）獨立的圖表：）獨立的圖表：單獨占據(jù)一個工作表，打印時也將與數(shù)據(jù)表分開打印。（2）附屬于工作表的嵌入式圖表：）

14、附屬于工作表的嵌入式圖表：它和數(shù)據(jù)源放置在同一張工作表中，同時打印。 這兩種表所依據(jù)的數(shù)據(jù)都來自工作表上選中的區(qū)域，數(shù)據(jù)變化，則圖表隨之變化。（以文件（以文件“圖表圖表”為例）為例）Excel在數(shù)學(xué)建模中的應(yīng)用382圖表術(shù)語圖表術(shù)語（1）數(shù)據(jù)系列：）數(shù)據(jù)系列：圖表中決定圖形Y軸取值的數(shù)值集合，對應(yīng)工作表中的數(shù)據(jù)行。（2）分類：）分類：圖表中決定數(shù)據(jù)系列的X軸的標(biāo)題值，對應(yīng)工作表中的數(shù)據(jù)列。（3）坐標(biāo)軸）坐標(biāo)軸：圖表的一邊。（4）圖例：）圖例：定義圖表的圖形的含義。（5）網(wǎng)格線：）網(wǎng)格線：幫助確定數(shù)據(jù)點在Y軸或X軸刻度上的確切值。 Excel在數(shù)學(xué)建模中的應(yīng)用39n3創(chuàng)建圖表創(chuàng)建圖表n（1）利用

15、圖表向?qū)Х炙膫€步驟創(chuàng)建圖表：選中區(qū)域圖表向?qū)Чぞ遪（2）按F11鍵快速創(chuàng)建圖表：選中區(qū)域F11Excel在數(shù)學(xué)建模中的應(yīng)用404編輯圖表編輯圖表（1）選中圖表元素：）選中圖表元素：單擊（2）圖表移動及改變大?。海﹫D表移動及改變大?。和蟿邮髽?biāo)（3）改變圖表圖案、顏色、設(shè)置刻度等：）改變圖表圖案、顏色、設(shè)置刻度等：雙擊圖表（4）改變圖表類型：）改變圖表類型：右鍵單擊（5）數(shù)據(jù)系列編輯）數(shù)據(jù)系列編輯添加數(shù)據(jù)系列：添加數(shù)據(jù)系列：選中區(qū)域拖曳到圖標(biāo)；選中圖表圖表添加數(shù)據(jù)刪除數(shù)據(jù)系列：刪除數(shù)據(jù)系列：選中系列Delete；選中系列編輯清除系列系列次序的調(diào)整：系列次序的調(diào)整：選中系列格式數(shù)據(jù)系列系列次序修改系

16、列顏色：修改系列顏色：選中系列雙擊（6）修改圖表中的數(shù)據(jù)）修改圖表中的數(shù)據(jù)修改圖表中的數(shù)據(jù)：修改圖表中的數(shù)據(jù)：雙擊要修改數(shù)據(jù)的單元格修改數(shù)據(jù) 修改圖表中的圖形：修改圖表中的圖形：選中系列有間斷地單擊結(jié)點拖動鼠標(biāo)（7）增加圖表標(biāo)題及數(shù)據(jù)標(biāo)志）增加圖表標(biāo)題及數(shù)據(jù)標(biāo)志雙擊某一系列 選中圖表圖表圖表選項標(biāo)題（數(shù)據(jù)標(biāo)志）Excel在數(shù)學(xué)建模中的應(yīng)用41繪圖實例： 用Excel繪制任意一元函數(shù)的圖像以 為例介紹：Excel在數(shù)學(xué)建模中的應(yīng)用421、用Excel計算描述統(tǒng)計量2、用Excel進行相關(guān)分析3、用Excel進行回歸分析三、用Excel進行相關(guān)與回歸分析Excel在數(shù)學(xué)建模中的應(yīng)用43三-1、用E

17、xcel計算描述統(tǒng)計量（1）用函數(shù)計算描述統(tǒng)計量（2）描述統(tǒng)計菜單項的使用Excel在數(shù)學(xué)建模中的應(yīng)用44n常用的描述統(tǒng)計量有眾數(shù)、中位數(shù)、算術(shù)平均數(shù)、調(diào)和平均數(shù)、幾何平均數(shù)、極差、四分位差、標(biāo)準(zhǔn)差、方差、標(biāo)準(zhǔn)差系數(shù)等。下面介紹如何用函數(shù)來計算描述統(tǒng)計量。（1）用函數(shù)計算描述統(tǒng)計量Excel在數(shù)學(xué)建模中的應(yīng)用45n用函數(shù)運算有兩種方法：一是手工輸入函數(shù)名稱及參數(shù)。這種輸入形式比較簡單、快捷。但需要非常熟悉函數(shù)名稱及其參數(shù)的輸入形式。所以，只有比較簡單的函數(shù)才用這種方法輸入；二是函數(shù)導(dǎo)入法。這是一種最為常用的辦法，它適合于所有函數(shù)的使用，而且在導(dǎo)入過程中有向?qū)崾?，因而非常方便。函?shù)的一般導(dǎo)入過

18、程為：點菜單“插入”；找“函數(shù)”，此時出現(xiàn)一個“插入函數(shù)”對話框；在對話框的“選擇類別”中確定函數(shù)的類別（如常用函數(shù)或統(tǒng)計）；在“選擇函數(shù)”內(nèi)確定欲選的函數(shù)名稱，如SUM、MODE等；點“確定”后即可出現(xiàn)該函數(shù)運算的對話框向?qū)?，再按向?qū)У奶崾就逻\行即可。Excel在數(shù)學(xué)建模中的應(yīng)用46眾數(shù) 例：為了分析小麥的分蘗情況，進行了10株小麥的調(diào)查，如下圖所示：Excel在數(shù)學(xué)建模中的應(yīng)用47眾數(shù)（Mode）n眾數(shù)（Mode）統(tǒng)計學(xué)名詞，在統(tǒng)計分布上具有明顯集中趨勢點的數(shù)值，代表數(shù)據(jù)的一般水平（眾數(shù)可以不存在或多于一個）。 修正定義：是一組數(shù)據(jù)中出現(xiàn)次數(shù)最多的數(shù)值，叫眾數(shù)，有時眾數(shù)在一組數(shù)中有好幾個

19、。用M表示。 理性理解：簡單的說，就是一組數(shù)據(jù)中占比例最多的那個數(shù)。 Excel在數(shù)學(xué)建模中的應(yīng)用48手工輸入函數(shù)名稱及參數(shù)單擊任一單元格，輸入“=MODE（B2：B11）”，回車后即可得眾數(shù)為14。Excel在數(shù)學(xué)建模中的應(yīng)用49函數(shù)導(dǎo)入法點菜單“插入”；找“函數(shù)”，此時出現(xiàn)一個“插入函數(shù)”對話框；在對話框的“選擇類別”中確定函數(shù)的類別“統(tǒng)計”；在“選擇函數(shù)”內(nèi)確定欲選的函數(shù)名稱“MODE”，Excel在數(shù)學(xué)建模中的應(yīng)用50點“確定”后即可出現(xiàn)該函數(shù)運算的對話框向?qū)?，在Number1處輸入B2：B11或選擇Excel中的B2：B11區(qū)域。按“確定”，在Excel中即得到眾數(shù)14。Excel在

20、數(shù)學(xué)建模中的應(yīng)用51中位數(shù)單擊任一空白單元格，輸入“=MEDIAN（B2:B11）”，回車后得中位數(shù)為14。Excel在數(shù)學(xué)建模中的應(yīng)用52中位數(shù)（Medians）n中位數(shù)（Medians）統(tǒng)計學(xué)名詞，是指將統(tǒng)計總體當(dāng)中的各個變量值按大小順序排列起來，形成一個數(shù)列，處于變量數(shù)列中間位置的變量值就稱為中位數(shù)，用Me表示。當(dāng)變量值的項數(shù)N為奇數(shù)時，處于中間位置的變量值即為中位數(shù)；當(dāng)N為偶數(shù)時，中位數(shù)則為處于中間位置的2個變量值的平均數(shù)。（注意：中位數(shù)和眾數(shù)不同，眾數(shù)不一定在中間） Excel在數(shù)學(xué)建模中的應(yīng)用53算術(shù)平均數(shù)單擊任一空白單元格，輸入“=AVERAGE（B2:B11）”，回車后得算術(shù)平

21、均數(shù)為14.1。Excel在數(shù)學(xué)建模中的應(yīng)用54幾何平均數(shù)幾何平均數(shù)單擊任一空白單元格，輸入“=GEOMEAN（B2:B11）”，回車后得幾何平均數(shù)為14.02。幾何平均數(shù)是是指n個觀察值連乘積的n次方根。 。其計算公式為：Excel在數(shù)學(xué)建模中的應(yīng)用55調(diào)和平均數(shù)調(diào)和平均數(shù)單擊任一空白單元格，輸入“=HARMEAN（B2:B11）”，回車后得調(diào)和平均數(shù)為1.422。公式: 調(diào)和平均數(shù)=1/(1/A+1/B+1/C+1/D+） Excel在數(shù)學(xué)建模中的應(yīng)用56截尾平均數(shù)截尾平均數(shù)n將數(shù)據(jù)按由小到大順序排列后，因數(shù)據(jù)兩端值不夠穩(wěn)定，按一定比例去掉頭尾兩端一定數(shù)量的觀察值，然后再求平均，這樣得到的

22、平均數(shù)就是截尾平均數(shù)。如果按1/10，即從10個數(shù)據(jù)中去掉最大的一個值和最小的一個值，再求平均數(shù)。n單擊任一空白單元格，輸入“=TRIMMEAN（B2:B11，1/10）”，回車后得截尾平均數(shù)為14.1Excel在數(shù)學(xué)建模中的應(yīng)用57全距全距n單擊任一空白單元格，n輸入“=MAX（B2:B11）-MIN（B2:B11）”，n回車后得全距為5。Excel在數(shù)學(xué)建模中的應(yīng)用58標(biāo)準(zhǔn)差標(biāo)準(zhǔn)差(估計樣本）估計樣本）單擊任一空白單元格，輸入“=STDEV（B2:B11）”，回車后得標(biāo)準(zhǔn)差為1.524。Excel在數(shù)學(xué)建模中的應(yīng)用59標(biāo)準(zhǔn)差計算公式Excel在數(shù)學(xué)建模中的應(yīng)用60標(biāo)準(zhǔn)差系數(shù)標(biāo)準(zhǔn)差系數(shù)單擊任

23、一空白單元格，輸入“=STDEV（B2:B11）/AVERAGE（B2:B11）”，回車后得標(biāo)準(zhǔn)差系數(shù)為01080Excel在數(shù)學(xué)建模中的應(yīng)用61標(biāo)準(zhǔn)差系數(shù)的計算公式 n式中：V為標(biāo)準(zhǔn)差系數(shù)；為標(biāo)準(zhǔn)差；x 為平均數(shù)。 Excel在數(shù)學(xué)建模中的應(yīng)用62偏度系數(shù)偏度系數(shù)單擊任一空白單元格，輸入“=SKEW（B2:B11）”，回車后得偏度系數(shù)為-0.678。汽車的扳金、省油與價格的滿意度汽車的扳金、省油與價格的滿意度(5-很滿意很滿意,1-很不滿意很不滿意)Excel在數(shù)學(xué)建模中的應(yīng)用63峰度系數(shù)峰度系數(shù)單擊任一空白單元格，輸入“=KURT（B2:B11）”，回車后得峰度系數(shù)為0.6249。扳金扳金

24、省油省油價格價格443322552211443333334433333344443333115555443333333344443333552222334433225555115555Excel在數(shù)學(xué)建模中的應(yīng)用64常用的統(tǒng)計量函數(shù)扳金扳金省油省油價格價格扳金扳金1 1省油省油-0.94-0.941 1價格價格-0.91-0.91 0.8350.8351 1由于公式執(zhí)行后顯示的是計算結(jié)果按Ctrl+、鍵(位于鍵盤左上角)可使公式在顯示公式內(nèi)容與顯示公式結(jié)果之間切換 舊車車齡與售價關(guān)系圖舊車車齡與售價關(guān)系圖y = -4.8091x + 57.8R2 = 0.98650.010.020.030.0

25、40.050.060.0024681012車齡售價Excel在數(shù)學(xué)建模中的應(yīng)用65（2）描述統(tǒng)計菜單項的使用仍使用上面的例子我們已經(jīng)把數(shù)據(jù)輸入到B2:B11單元格，然后按以下步驟操作：Excel在數(shù)學(xué)建模中的應(yīng)用66第一步：在工具菜單中選擇數(shù)據(jù)分析選項，從其對話框中選擇描述統(tǒng)計，按確定后打開描述統(tǒng)計對話框。年齡年齡每月所得每月所得15156000.06000.0202010000.010000.0252515000.015000.0303026000.026000.0353535000.035000.0404042000.042000.0454550500.050500.0505040500.

26、040500.0555537650.037650.0606030500.030500.0656525000.025000.0707015800.015800.0757510200.010200.080808000.08000.0年齡與每月所得的關(guān)系圖(多項式回年齡與每月所得的關(guān)系圖(多項式回歸)歸)y = -36.54x2 + 3463.7x -42087R2 = 0.8840.020000.040000.060000.0020406080100年齡每月所得Excel在數(shù)學(xué)建模中的應(yīng)用67第二步：在輸入?yún)^(qū)域中輸入$B$1:$B$11，在輸出區(qū)域中選擇$F$1，其他復(fù)選框可根據(jù)需要選定，選擇匯總

27、統(tǒng)計，可給出一系列描述統(tǒng)計量；選擇平均數(shù)置信度，會給出用樣本平均數(shù)估計總體平均數(shù)的置信區(qū)間；第K大值和第K小值會給出樣本中第K個大值和第K個小值。Excel在數(shù)學(xué)建模中的應(yīng)用68第三步：單擊確定，可得輸出結(jié)果。直徑直徑高度高度0.90.917171.21.225252.92.932323 335353.33.346464 458586.56.569699.69.6757512.412.4727216.216.2787827.227.28585Excel在數(shù)學(xué)建模中的應(yīng)用69上面的結(jié)果中，平均指樣本均值；標(biāo)準(zhǔn)誤差指樣本平均數(shù)的標(biāo)準(zhǔn)差；中值即中位數(shù)；模式指眾數(shù)；標(biāo)準(zhǔn)偏差指樣本標(biāo)準(zhǔn)差，自由度為n-1

28、；峰值即峰度系數(shù)；偏斜度即偏度系數(shù)；區(qū)域?qū)嶋H上是極差，或全距；可以看出與我們前面用函數(shù)計算的結(jié)果完全相同。最大值為16，最小值為11，第三個最大值為15，第三個最小值為13。Excel在數(shù)學(xué)建模中的應(yīng)用70三三-2.相關(guān)性分析相關(guān)性分析相關(guān)性分析：相關(guān)性分析： 判斷兩組數(shù)據(jù)集（可以使用不同的度量單位）之間的關(guān)系。相關(guān)系數(shù)（相關(guān)系數(shù)（R）：）： 相關(guān)性計算的返回值。用來確定兩個區(qū)域中數(shù)據(jù)的變化是否相關(guān)，以及相關(guān)的程度。是兩組數(shù)據(jù)集的協(xié)方差除以它們標(biāo)準(zhǔn)偏差的乘積。1、R0：一個集合的較大數(shù)據(jù)與另一個集合的較大數(shù)據(jù)相對應(yīng)（正相關(guān)）2、R0：一個集合的較大數(shù)據(jù)與另一個集合的較小數(shù)據(jù)相對應(yīng)（負相關(guān)）3、

29、R=0：兩個集合中的數(shù)據(jù)互不相關(guān)。|R|0.4 ：低度相關(guān)；0.4=|R|0.6:中度相關(guān)； 0.6=|R|=0.8:非常高度相關(guān)Excel在數(shù)學(xué)建模中的應(yīng)用711、CORREL函數(shù)：函數(shù)：CORRELCORREL(array1array1,array2array2)返回單元格區(qū)域 array1 和 array2 之間的相關(guān)系數(shù)。使用相關(guān)系數(shù)可以確定兩種屬性之間的關(guān)系。例如，可以檢測某地的平均溫度和空調(diào)使用情況之間的關(guān)系。Array1 第一組數(shù)值單元格區(qū)域。Array2 第二組數(shù)值單元格區(qū)域。如果數(shù)組或引用包含文本、邏輯值或空白單元格，這些數(shù)值將被忽略，但是包含零值的單元格將計算在內(nèi)。如果 a

30、rray1 和 array2 的數(shù)據(jù)點的數(shù)目不同，函數(shù) CORREL 返回錯誤值 #N/A。Excel在數(shù)學(xué)建模中的應(yīng)用72例 1、利用統(tǒng)計數(shù)據(jù)計算廣告費與銷售額之間的相關(guān)系數(shù)。相關(guān)系數(shù)相關(guān)系數(shù)CORREL(B4:B15,C4:C15)=0.92251818y = 21.512Ln(x) + 19.478R2 = 0.92570102030405060708090100051015202530高度Excel在數(shù)學(xué)建模中的應(yīng)用73例例2.2.我們收集了某廠家同一車型中舊車的車齡及其售我們收集了某廠家同一車型中舊車的車齡及其售價數(shù)據(jù)，求價格與車齡的相關(guān)系數(shù)價數(shù)據(jù)，求價格與車齡的相關(guān)系數(shù) Excel

31、在數(shù)學(xué)建模中的應(yīng)用74例 3、總平均成績、出勤率、選修學(xué)分與每周打工小時數(shù)的關(guān)系。2、使用數(shù)據(jù)分析工具求相關(guān)矩陣、使用數(shù)據(jù)分析工具求相關(guān)矩陣利用CORREL函數(shù)只能返回兩種屬性之間的相關(guān)系數(shù)，如果使用Excel提供的“數(shù)據(jù)分析”工具可以計算多組數(shù)據(jù)間的相關(guān)系數(shù)，組成相關(guān)系數(shù)表。Excel在數(shù)學(xué)建模中的應(yīng)用75例 利用“數(shù)據(jù)分析”工具計算汽車扳金、省油與價格的滿意度之間的相關(guān)系數(shù)。inicmicimETETYY)(1dbxxy21)(ln() ln() ln(21xdxby Excel在數(shù)學(xué)建模中的應(yīng)用76回歸分析回歸分析: :當(dāng)一個結(jié)果與一個或多個參數(shù)之間存在聯(lián)系時，可以進行回歸分析，通常可由

32、一個或多個自變量來預(yù)測一個變量的值?；貧w方程回歸方程: :表達參數(shù)與結(jié)果之間相互關(guān)系的數(shù)學(xué)方程式(數(shù)學(xué)模型)線性回歸線性回歸: :如果變量與結(jié)果之間具有線性關(guān)系,我們可以用線性方程式來描述它們之間的關(guān)系,這種回歸方法叫線性回歸.非線性回歸非線性回歸: :如果變量與結(jié)果之間不具有線性關(guān)系,我們必須用非線性方程式來描述它們之間的關(guān)系(如指數(shù)關(guān)系,對數(shù)關(guān)系等等),這種回歸方法叫非線性回歸.單回歸單回歸: :當(dāng)一個結(jié)果只與一個參數(shù)存在聯(lián)系時，進行的回歸分析稱為單回歸。復(fù)回歸復(fù)回歸: :當(dāng)一個結(jié)果與多個參數(shù)存在聯(lián)系時，進行的回歸分析稱為復(fù)回歸。判定系數(shù)判定系數(shù)(R2):(R2):用來確定回歸方程式的可解

33、釋性，即吻合程度。范圍在0-1之間，越接近1，解釋性越強，即吻合程度越高?；貧w方法：回歸方法：1、給圖表增加趨勢線；2、使用Excel提供的“數(shù)據(jù)分析工具”；3、利用回歸函數(shù)三三-3.-3.回歸分析回歸分析 Excel在數(shù)學(xué)建模中的應(yīng)用77例4(線性回歸)、 我們收集了某廠家同一車型中舊車的車齡及其售價數(shù)據(jù)，請使用給圖表增加趨勢線，求車齡對售價的回歸方程,并計算車齡為6.5年的舊車售價是多少.車齡車齡價格價格( (萬萬) )156.0248.5342.0437.6532.5628.7722.2818.5915.01012.56.5年的舊車車價年的舊車車價=-4.8091*6.5+57.8=26

34、.541 1、給圖表增加趨勢線進行回歸分析、給圖表增加趨勢線進行回歸分析Excel在數(shù)學(xué)建模中的應(yīng)用78Excel在數(shù)學(xué)建模中的應(yīng)用79有些數(shù)據(jù)間并不是簡單的線性關(guān)系，如果用線性模式求其回歸方程式，判有些數(shù)據(jù)間并不是簡單的線性關(guān)系，如果用線性模式求其回歸方程式，判定系數(shù)（定系數(shù)（R R2 2）很小，根本不具有任何解釋力。因此要引入非線性回歸，如多）很小，根本不具有任何解釋力。因此要引入非線性回歸，如多項式、指數(shù)、對數(shù)等回歸方法。項式、指數(shù)、對數(shù)等回歸方法。例例5(5(非線性回歸非線性回歸) )、 我們收集了年齡與月收入關(guān)系的數(shù)據(jù)，請我們收集了年齡與月收入關(guān)系的數(shù)據(jù)，請繪制該數(shù)據(jù)的散點圖，并求年

35、齡對月收入的回歸方程式。繪制該數(shù)據(jù)的散點圖，并求年齡對月收入的回歸方程式。bxceyExcel在數(shù)學(xué)建模中的應(yīng)用80例例6(6(非線性回歸非線性回歸) )、 我們收集了一個原始森林中樹的直徑我們收集了一個原始森林中樹的直徑與高度之間的關(guān)系數(shù)據(jù)與高度之間的關(guān)系數(shù)據(jù), ,請繪制該數(shù)據(jù)的散點圖，并求直請繪制該數(shù)據(jù)的散點圖，并求直徑與高度的回歸方程式徑與高度的回歸方程式( (指數(shù)回歸指數(shù)回歸) )。bxc y ) ln() ln(Excel在數(shù)學(xué)建模中的應(yīng)用81第一步：單擊“工具”菜單，選擇“數(shù)據(jù)分析”選項，出現(xiàn)“數(shù)據(jù)分析”對話框，在分析工具中選擇“回歸”。年齡年齡每月所得每月所得15156000.0

36、6000.0202010000.010000.0252515000.015000.0303026000.026000.0353535000.035000.0404042000.042000.0454550500.050500.0505040500.040500.0555537650.037650.0606030500.030500.0656525000.025000.0707015800.015800.0757510200.010200.080808000.08000.0 Excel在數(shù)學(xué)建模中的應(yīng)用82第二步：單擊“確定”按鈕，彈出“回歸”對話框，在“Y值輸入?yún)^(qū)域”輸入$B$1：$B$11；

37、在“X值輸入?yún)^(qū)域”輸入$C$1:$C$11，在“輸出選項”選擇“$E$1”，如下圖所示。)ln( xbay Excel在數(shù)學(xué)建模中的應(yīng)用83 第三步：單擊確定按鈕，得回歸分析結(jié)果如下圖所示。Excel在數(shù)學(xué)建模中的應(yīng)用84Excel回歸分析工具的輸出結(jié)果包括3個部分：n(1)(1)回歸統(tǒng)計表回歸統(tǒng)計表n回歸統(tǒng)計表包括以下幾部分內(nèi)容：nMultiple R（復(fù)相關(guān)系數(shù)R）：是R2的平方根，又稱為相關(guān)系數(shù)，用來衡量變量x和y之間相關(guān)程度的大小。本例中R為0.6313，表示二者之間的關(guān)系是正相關(guān)。nR Square（復(fù)測定系數(shù)R2）：用來說明自變量解釋因變量變差的程度，以測定因變量y的擬合效果。 E

38、xcel在數(shù)學(xué)建模中的應(yīng)用85Excel在數(shù)學(xué)建模中的應(yīng)用86n（2 2）方差分析表）方差分析表n方差分析表的主要作用是通過F檢驗來判斷回歸模型的回歸效果。表中“回歸分析”行計算的是估計值同均值之差（-）的各項指標(biāo)；“殘差”行是用于計算每個樣本觀察值與估計值之差（-）的各項指標(biāo)；“總計”行用于計算每個值同均值之差（-）的各項指標(biāo)。第二列df是自由度，第三列SS是離差的平方和，第四列MS是均方差，它是離差平方和除以自由度，第五列是F統(tǒng)計量，第六列Significance F是在顯著性水平下的F的臨界值 Excel在數(shù)學(xué)建模中的應(yīng)用87cbxaxY2基本形式基本形式JENSEN模型（水模型（水分生

39、產(chǎn)函數(shù)）分生產(chǎn)函數(shù)）兩邊同時取對數(shù)轉(zhuǎn)化為線性形式兩邊同時取對數(shù)轉(zhuǎn)化為線性形式Excel在數(shù)學(xué)建模中的應(yīng)用88月份月份廣告費廣告費(萬萬) 銷售量銷售量(萬萬)112452453000300022280280320032003317517518501850441651651650165055150150150015006619019024002400772402402800280088610610296029609919019024002400101015015016001600111114014015001500121222022023502350Excel在數(shù)學(xué)建模中的應(yīng)用89方差分析方差分

40、析dfSSMSFSignificance F回歸分析11765442 1765442 6.7260.026794856殘差102624983 262498.3總計114390425結(jié)結(jié)論論:F檢定中的顯著水準(zhǔn)檢定中的顯著水準(zhǔn)SignificanceF=0.026794856a=0.05說明說明:銷售量與廣告費存在回歸關(guān)系銷售量與廣告費存在回歸關(guān)系.Excel在數(shù)學(xué)建模中的應(yīng)用90t 檢定t 檢定Coefficients標(biāo)準(zhǔn)誤差 t Stat P-value Lower 95% Upper 95%下限 95.0%上限 95.0%Intercept 1545.831536 315.13784.90

41、5255 0.0006 843.6605796 2248.002 843.66058 2248.0025廣告費(萬) 3.14338351 1.2120872.593364 0.0268 0.442684371 5.844083 0.44268437 5.8440827Excel在數(shù)學(xué)建模中的應(yīng)用91結(jié) 結(jié)論 論:自變量廣告費的顯著水準(zhǔn)自變量廣告費的顯著水準(zhǔn)P-value=0.026795a=0.05說明：廣告費與銷售量間存在直線關(guān)系，其系數(shù)為說明：廣告費與銷售量間存在直線關(guān)系，其系數(shù)為3.14338351，正相關(guān)。，正相關(guān)?；貧w方程式為回歸方程式為:Y=3.143384X+1545.832t

42、檢定結(jié)果中，常數(shù)項為檢定結(jié)果中，常數(shù)項為1545.832， ，P-value=0.00061826a=0.05說明說明:回歸方程式的常數(shù)項不應(yīng)為回歸方程式的常數(shù)項不應(yīng)為0，不可將其省略。，不可將其省略?；拘问交拘问絻蛇吶?shù)兩邊取對數(shù)SUMMARY OUTPUT回歸統(tǒng)計Multiple R0.993241R Square0.986527Adjusted R Square0.984843標(biāo)準(zhǔn)誤差1.804792觀測值10對因變量取對因變量取對數(shù)對數(shù)對截距反對對截距反對數(shù)數(shù)Excel在數(shù)學(xué)建模中的應(yīng)用92方差分析dfSSMSFSignificance F回歸分析1 1908.007 1908.

43、007 585.76829.0595E-09殘差8 26.05818 3.257273總計9 1934.065Excel在數(shù)學(xué)建模中的應(yīng)用93Coefficients 標(biāo)準(zhǔn)誤差 t Stat P-value Lower 95%Upper 95%下限 95.0%上限 95.0%Intercept57.81.23290746.881064.74E-1154.956908660.6430954.9569160.64309車齡-4.8090909090.198701-24.20269.06E-09-5.2672966-4.35089 -5.2673-4.35089Excel在數(shù)學(xué)建模中的應(yīng)用94存款金

44、額(千萬)貸款金額(千萬)655410287423458489581736232251241071351174537基本形式基本形式對自變量取對自變量取對數(shù)對數(shù)Excel在數(shù)學(xué)建模中的應(yīng)用95回歸統(tǒng)計Multiple R0.999956R Square0.999912Adjusted R Square0.999901標(biāo)準(zhǔn)誤差0.314492觀測值10Excel在數(shù)學(xué)建模中的應(yīng)用96產(chǎn)量與需水量之間的關(guān)系：拋物線產(chǎn)量與需水量之間的關(guān)系：拋物線方差分析dfSSMSFSignificance F回歸分析1 8970.8098970.8907011.65436E-17殘差8 0.7912410.098

45、9總計98971.6把把x2,x分別看作變量按多元回歸計算即可分別看作變量按多元回歸計算即可Excel在數(shù)學(xué)建模中的應(yīng)用97Coefficients標(biāo)準(zhǔn)誤差 t StatP-value Lower 95% Upper 95%下限 95.0%上限 95.0%Intercept-3.444609390.248681 -13.85 7E-07-4.018069372 -2.87115 -4.018069 -2.8711494存款金額(千萬) 0.8903321580.002956 301.17 2E-17 0.883514957 0.897149 0.883515 0.89714936Excel在數(shù)

46、學(xué)建模中的應(yīng)用98每月總收每月總收入(萬)入(萬)不動產(chǎn)不動產(chǎn)(百萬)(百萬)動產(chǎn)動產(chǎn)(百萬)(百萬)每月房每月房貸(萬)貸(萬)撫養(yǎng)費撫養(yǎng)費(萬)(萬)信用信用指數(shù)指數(shù)6.56.512.012.03.03.02.02.02.02.082827.27.28.08.02.02.00.00.02.02.086863.83.80.00.01.01.00.00.01.01.070708.58.515.015.03.53.52.82.82.02.090904.24.20.00.00.50.50.00.01.51.575756.36.36.06.02.02.01.81.81.51.580803.03.00

47、.00.00.60.60.00.00.00.070703.23.23.03.01.01.00.00.02.02.06565圖表標(biāo)題Excel在數(shù)學(xué)建模中的應(yīng)用99加入趨勢線可以進行簡單的回歸分析，但要獲得更多的統(tǒng)計數(shù)據(jù)，可以使用數(shù)據(jù)分析工具，求簡單相關(guān)系數(shù)、判定系數(shù)、用F檢定判定變量與自變量間是否有回歸關(guān)系存在、用t檢定判定各回歸系數(shù)是否不為0、計算回歸系數(shù)的置信度、標(biāo)準(zhǔn)殘差等。例 7、根據(jù)廣告費與銷售額統(tǒng)計數(shù)據(jù)使用分析工具進行回歸分析?；貧w統(tǒng)計Multiple R0.990989406R Square0.982060003Adjusted R Square0.93721001標(biāo)準(zhǔn)誤差2.17

48、9360967觀測值8回歸分析信息說明回歸分析信息說明R=0.999099,R2=0.98206,調(diào)整后的調(diào)整后的R=0.93721說明說明:回歸方程式可解釋信用分數(shù)的差異程度相當(dāng)高回歸方程式可解釋信用分數(shù)的差異程度相當(dāng)高.Excel在數(shù)學(xué)建模中的應(yīng)用100方差分析dfSSMSFSignificance F回歸分析5520104 21.90.044248345殘差2 9.499 4.75總計7 529.5方差分析方差分析(F檢定檢定):用回歸分析檢定用回歸分析檢定,判定變量與自變量之間是否判定變量與自變量之間是否有顯著的回歸關(guān)系存在。如果顯著水準(zhǔn)有顯著的回歸關(guān)系存在。如果顯著水準(zhǔn)(Signif

49、icanceF)a值值,回回歸關(guān)系存在歸關(guān)系存在,否則不存在。否則不存在。(在這里判定系數(shù)在這里判定系數(shù)a=1-置信度置信度,在我們回歸分析中置信度取在我們回歸分析中置信度取95%,所以所以a=1-95%=0.05)Excel在數(shù)學(xué)建模中的應(yīng)用101結(jié)結(jié)論論:F檢定中的顯著水準(zhǔn)檢定中的顯著水準(zhǔn)SignificanceF=0.044248a=0.05說明說明:每月總收入、不動產(chǎn)、動產(chǎn)、每月房貸、撫養(yǎng)每月總收入、不動產(chǎn)、動產(chǎn)、每月房貸、撫養(yǎng)支出與信用分數(shù)整體間有明顯回歸關(guān)系存在。支出與信用分數(shù)整體間有明顯回歸關(guān)系存在。T檢定檢定:判斷回歸系數(shù)與常數(shù)項是否為判斷回歸系數(shù)與常數(shù)項是否為0Interce

50、pt:回歸方程中的常數(shù)項信息回歸方程中的常數(shù)項信息廣告費廣告費(萬萬):自變量自變量X的回歸系數(shù)信息的回歸系數(shù)信息Coefficients 標(biāo)準(zhǔn)誤差 t Stat P-value Lower 95% Upper 95% 下限 95.0% 上限 95.0%Intercept57.07610213 4.9504323 11.53 0.00744 35.7760961 78.376108 35.7760961 78.3761082每月總收入(萬) 5.3509127050.995484 5.375 0.03291 1.06768767 9.6341377 1.06768767 9.63413774不

51、動產(chǎn)(百萬)0.703920828 0.9303825 0.757 0.52827 -3.2991947 4.7070363 -3.2991947 4.70703632動產(chǎn)(百萬)-4.9618931 5.4451066 -0.91 0.45835 -28.390312 18.466526 -28.39031218.466526每月房貸(萬)-0.089892521 1.7158095 -0.05 0.96298 -7.4724301 7.292645 -7.4724301 7.29264503撫養(yǎng)費(萬)-2.499189095 1.7049759 -1.47 0.28034 -9.8351

52、133 4.8367351 -9.8351133 4.83673514Excel在數(shù)學(xué)建模中的應(yīng)用102車齡車齡價格價格( (萬萬) )156.0248.5342.0437.6532.5628.7722.2818.5915.01012.5例例8.8.我們收集了某廠家同一車型中舊車的車齡及其售價數(shù)我們收集了某廠家同一車型中舊車的車齡及其售價數(shù)據(jù)，請使用據(jù)，請使用數(shù)據(jù)分析工具數(shù)據(jù)分析工具，求車齡對售價的回歸方程，求車齡對售價的回歸方程結(jié)結(jié)論論:即：信用分數(shù)即：信用分數(shù)=5.350913*每月總收入每月總收入+57.0761。從回歸方程式中排除掉，最后的回歸方程式為：從回歸方程式中排除掉，最后的回歸

53、方程式為：Y=5.350913X+57.0761。說明：這些參數(shù)與信用分數(shù)間不存在顯著的直線關(guān)系，所以可將這些參數(shù)的系數(shù)說明：這些參數(shù)與信用分數(shù)間不存在顯著的直線關(guān)系，所以可將這些參數(shù)的系數(shù)其余的不動產(chǎn)、動產(chǎn)、每月房貸、撫養(yǎng)支出等四個參數(shù)的顯著水準(zhǔn)（其余的不動產(chǎn)、動產(chǎn)、每月房貸、撫養(yǎng)支出等四個參數(shù)的顯著水準(zhǔn)（P-value）均大于a=0.05說明：每月總收入與信用分數(shù)間存在直線關(guān)系，其系數(shù)為說明：每月總收入與信用分數(shù)間存在直線關(guān)系，其系數(shù)為5.350913，正相關(guān)。，正相關(guān)。其它五個自變量中，只有每月總收入的顯著水準(zhǔn)其它五個自變量中，只有每月總收入的顯著水準(zhǔn)P-value=0.032912a=

54、0.05t檢定結(jié)果中，常數(shù)項為檢定結(jié)果中，常數(shù)項為57.0761,其顯著水準(zhǔn)其顯著水準(zhǔn)P-value=0.007439a=0.05說明說明:回歸方程式的常數(shù)項不應(yīng)為回歸方程式的常數(shù)項不應(yīng)為0，不可將其省略。，不可將其省略。車齡車齡里程里程(萬萬公里公里)價格價格(萬)(萬)118.58.561.061.022212153.553.533353546.546.544434341.541.555505037.037.066585832.032.077656527.527.588767621.021.099808018.018.01010909012.012.0SUMMARY OUTPUTSUMMA

55、RY OUTPUT回歸統(tǒng)計回歸統(tǒng)計Multiple RMultiple R0.99990.9999R SquareR Square0.99990.9999Adjusted R SquareAdjusted R Square0.99980.9998標(biāo)準(zhǔn)誤差標(biāo)準(zhǔn)誤差0.20550.2055觀測值觀測值1010y=-4.8091x+57.8Excel在數(shù)學(xué)建模中的應(yīng)用103練習(xí)題練習(xí)題:以下是銀行客戶的存款金額與貸款金額對照表以下是銀行客戶的存款金額與貸款金額對照表,用數(shù)據(jù)分析用數(shù)據(jù)分析工具求存款金額對貸款金額的回歸方程工具求存款金額對貸款金額的回歸方程,并預(yù)測當(dāng)存款金額為并預(yù)測當(dāng)存款金額為80千千

56、萬時萬時,貸款金額為多少貸款金額為多少?方差分析方差分析df dfSS SSMS MSFFSignificance FSignificance F回歸分析回歸分析22 2275.72275.7 1137.851137.85 26937.3526937.352.49919E-142.49919E-14殘差 殘差77 0.29570.2957 0.042240.04224總計 總計99 22762276CoefficientsCoefficients 標(biāo)準(zhǔn)誤差標(biāo)準(zhǔn)誤差 t Statt Stat P-valueP-value Lower 95%Lower 95% Upper 95%Upper 95%

57、 下限 95.0%下限 95.0% 上限 95.0%上限 95.0%InterceptIntercept 65.9958724265.99587242 0.1862630.186263 354.315354.315 3.77E-163.77E-16 65.555430565.5554305 66.43631466.436314 65.55543165.555431 66.436314366.4363143車齡車齡-1.540712946-1.540712946 0.2072370.207237 -7.4345-7.4345 0.0001450.000145 -2.0307505-2.03075

58、05 -1.050675-1.050675 -2.030751-2.030751 -1.0506754-1.0506754里程里程-0.427767355-0.427767355 0.0238440.023844 -17.94-17.94 4.13E-074.13E-07 -0.4841495-0.4841495 -0.371385-0.371385 -0.48415-0.48415 -0.3713852-0.3713852年齡平方年齡每月所得225156,000 4002010,000 6252515,000 9003026,000 12253535,000 16004042,000 202

59、54550,500 25005040,500 30255537,650 36006030,500 42256525,000 49007015,800 56257510,200 6400808,000 年齡每月所得156,000 2010,000 2515,000 3026,000 3535,000 4042,000 4550,500 5040,500 5537,650 6030,500 6525,000 7015,800 7510,200 808,000 y=0.8903x3.4461Excel在數(shù)學(xué)建模中的應(yīng)用104通過給圖表加入趨勢線只能進行簡單的單一參數(shù)的回歸分析，通過給圖表加入趨勢線只

60、能進行簡單的單一參數(shù)的回歸分析，但在許多情況下需要使用多個自變量來預(yù)測一個變量的情況但在許多情況下需要使用多個自變量來預(yù)測一個變量的情況, ,這種回歸分析叫復(fù)回歸，復(fù)回歸必須通過數(shù)據(jù)分析工具進行。這種回歸分析叫復(fù)回歸，復(fù)回歸必須通過數(shù)據(jù)分析工具進行。復(fù)回歸復(fù)回歸Excel在數(shù)學(xué)建模中的應(yīng)用105例9、銀行為了核發(fā)信用卡，收集了申請人的每月總收入、不動產(chǎn)、動產(chǎn)、每月房貸與撫養(yǎng)支出費用等數(shù)據(jù)，并以業(yè)務(wù)主管員的經(jīng)驗，主觀地給予一個信用分數(shù)。為使評估信用分數(shù)能有一套公式，免得老是依賴主管評分，請使用復(fù)回歸求其回歸方程式?；貧w統(tǒng)計Multiple R0.94023005R Square0.88403254Adjuste