第3章excel在數(shù)學(xué)規(guī)劃和統(tǒng)計中的應(yīng)用

1、第3章 Excel 在數(shù)學(xué)分析中的應(yīng)用,本章內(nèi)容：第一節(jié) Excel在線性規(guī)劃中的應(yīng)用第二節(jié) Excel在統(tǒng)計分析中的應(yīng)用,第1節(jié) Excel 在線性規(guī)劃中的應(yīng)用,本章重點：學(xué)習Excel 規(guī)劃求解方法掌握Excel數(shù)學(xué)分析工具,一、線性規(guī)劃模型簡介,線性規(guī)劃模型應(yīng)用程度名列前矛 。美國《財富》雜志對全美前500家大公司的調(diào)查表明，有85%的公司頻繁地使用線性規(guī)劃，并取得了顯著的經(jīng)濟效益。線性規(guī)劃可以用單純形法筆算求解，但

2、計算量較大，尤其對多變量的規(guī)劃求解，而且在敏感性分析中要做大量的重復(fù)性工作。,一、線性規(guī)劃模型簡介,Excel提供了超強的數(shù)學(xué)運算、統(tǒng)計分析功能，利用它的規(guī)劃求解功能就可以快速、高效地求解線性規(guī)劃問題。,一、線性規(guī)劃模型,數(shù)學(xué)模型：目標函數(shù)：Max/Min S=C1X1+C2X2+....+CnXn約束條件：    a11 X1 +a12 X2+...+a1n Xn≥(＝,≤)b1 

3、60;  a21 X1 +a22 X2+...+a2n Xn≥(＝,≤)b2    am1 X1 +am2 X2+...+amn Xn≥(＝,≤)bm  X1 , X2... Xn≥0,一、線性規(guī)劃模型,其中：S為目標函數(shù)；Xj為決策變量；aij為技術(shù)系數(shù)；bi為約束值；Cj為費用系數(shù)；m為約束條件的個數(shù);n為變量個數(shù)。,一、線性規(guī)劃模型,單純型法原理：在找出

4、一個基可行解后，判斷它是否為最優(yōu)解，如果不是，則另外換一個基可行解，直到得到問題的解答。整個計算過程實際上一個在基可行解上的迭代過程。由于基可行解是有限的，如果有最優(yōu)解，則經(jīng)過有限步迭代可以達到。,二、 Excel求解線性規(guī)劃問題,步驟：1、加載【規(guī)劃求解】宏2、定義規(guī)劃求解參數(shù) 【目標單元格】 【等于】 【可變單元格】 【約束】 【選項】3、求解,

5、【目標單元格】是指選定計算問題的目標并含有計算公式的單元格。【等于】按問題的目標進行求解?！究勺儐卧瘛渴侵笡Q策變量（不含公式）所在的單元格，可以有多個單元格或區(qū)域【約束】增加、修改、刪除各個約束條件。【選項】決定采用線性規(guī)劃還是非線性規(guī)劃。,案例研究,合理利用線材問題：現(xiàn)要做100套鋼架，每套用長度分別為2.9m、 2.1m、 1.5m的圓鋼各一根，已知原料長7.4m，問如何下料，使所用的原材料最??？為了節(jié)約原材料，需要進

6、行套裁，有如下5套裁剪方案：,套裁方案,1. 建立線性規(guī)劃模型,設(shè)按方案1～5下料的原材料根數(shù)分別為x1,x2,x3,x4,x5, 根據(jù)上表，列出數(shù)學(xué)模型: Min z = 0x1+0.1x2+0.2x3+0.3x4+0.8x5 且滿足： x1+2x2+x4＝100 2x3+2x4+x5＝100 3x1+x2+2x3+x5＝100 x1,x2,x3,x4,x5>＝0,在Excel中輸入

7、數(shù)據(jù),其中：,B3:G6為原始數(shù)據(jù)區(qū)；B12:F12為方案1～5的決策變量（x1,x2,x3,x4,x5），即可變單元格；C8為目標單元格，輸入目標函數(shù)；C14、C15、C16輸入約束條件，等于約束條件的左式減去右式。,合理利用線材問題的計算公式,,輸入目標單元格、可變單元格和約束條件,求解,在【選項】中選擇【線性規(guī)劃模型】一切設(shè)定好以后，單擊【求解】按鈕，規(guī)劃工具開始運行；運算結(jié)果有三個選項：【運算結(jié)果報告】【敏感性報告】【

8、極限值報告】，分別生成三個報告。（見圖1）,課堂作業(yè),某藥廠生產(chǎn)A、B、C三種藥物，可供選擇的原料有甲、乙、丙、丁四種，成本分別是每公斤5元、6元、7元、8元。每公斤不同原料所能提供的各種藥物如表一所示。藥廠要求每天生產(chǎn)A藥品恰好100克、B藥品至少530克、C藥品不超過160克。要求選配各種原料的數(shù)量，即滿足生產(chǎn)的需要，又使總成本最少。,原料需求表,（1）建立線性規(guī)劃模型：,設(shè)X1、X2、X3、X4分別表示甲、乙、丙、丁原料的用量，得

9、到如下線性規(guī)劃模型：目標函數(shù)： Min Z＝5X1＋6X2＋7X3＋8X4約束條件： X1+ X2+ X3+ X4=100　　 5X1+4X2+6X3+5X4≥530　 2X1+ X2+ X3+2X4≤160　　 X1, X2，X4，X3≥0,在“規(guī)劃求解參數(shù)”對話框中通過點擊C8單元格使“目標單元格”出現(xiàn)$C$

10、8的絕對引址，并根據(jù)本題題意在其后的小框框內(nèi)選擇“最小值”。在“可變單元格”中通過從表格中選擇D3:G3區(qū)域，使之在文本框內(nèi)出現(xiàn)$D$3:$G$3。在“約束條件”處按“增加”，然后在出現(xiàn)的“增加約束”對話框中的“單元格引用位置”處通過點擊C5單元格使之出現(xiàn)$C$5,在后面的框框內(nèi)選“＝”，“約束值”編輯為$B$5。,,第二、三、四個約束條件分別編輯為“$C$6≥$B$6”,“$C$7≤$B$7”,“$D$3:$G$3≥0”. 按“

11、確定”退出。按“求解”按鈕，在彈出的“規(guī)劃求解結(jié)果”對話框內(nèi)可根據(jù)需要生成運算結(jié)果、敏感性分析和限制范圍的報告，然后按“確定”對模型進行求解。,,（6）如發(fā)現(xiàn)數(shù)字解為小數(shù)，可按需要該為用整數(shù)表示，方法如下：　　 ① 按住Ctrl鍵，分別選定需改為用整數(shù)表示的單元格D3、E3、F3、G3、C8。　　 ② 選取“格式”、“單元格… …”、“數(shù)字”、“科學(xué)計數(shù)”。　　 ③ 在“小數(shù)位數(shù)”中選定“0”格式。按“確定”退出。,第二節(jié) E

12、xcel在統(tǒng)計分析中運用,,一、相關(guān)分析,相關(guān)分析是一種描述變量之間的相關(guān)程度的分析方法。在相關(guān)分析中，所有變量都是隨機變量，它們之間并不存在被解釋變量和解釋變量之間的關(guān)系。相關(guān)分析有簡單相關(guān)和多元相關(guān)、線性相關(guān)和非線性相關(guān)之間的關(guān)系、正相關(guān)和負相關(guān)之分。,簡單相關(guān)分析,總體相關(guān)系數(shù)Cov(x,y)表示隨機變量x與y的協(xié)方差Var(x)表示隨機變量x的方差Var(y)表示隨機變量y的方差,總體相關(guān)系數(shù)滿足如下性質(zhì)：,|

13、ρxy|≤1|ρxy|=1的充要條件是X和Y依概率線性相關(guān)?？傮w相關(guān)系數(shù)ρxy一般是得不到的，我們只能根據(jù)樣本觀測值估計。,樣本的相關(guān)系數(shù),設(shè)隨機變量x,y的一組樣本為(xi, yi)，i=1,2,…，n，則定義x的樣本方差為S2x，y的樣本方差為S2y，x和y的樣本協(xié)方差為Sxy，則：,VARP,返回樣本總體的方差。語法：VARP(number1,number2)說明：,COVER,返回協(xié)方差，即每對數(shù)據(jù)點的偏差乘積的平均數(shù)

14、，利用協(xié)方差可以決定兩個數(shù)據(jù)集之間的關(guān)系。語法：COVER(array1,array2) array1:第一個單元格區(qū)域 array2:第二個單元格區(qū)域公式：,CORREL,相關(guān)系數(shù)函數(shù),例題1,某機構(gòu)調(diào)查10個公司的年齡和年銷售額（萬元）的統(tǒng)計數(shù)據(jù)。,計算結(jié)果,多元相關(guān),對于若干變量之間的關(guān)聯(lián)程度進行估計。,,多元相關(guān)系數(shù)是度量一個變量與其他所有變量相關(guān)程度的數(shù)量指標。記為R。R定義為最小二乘估計值?與變量Y的觀測值的簡

15、單相關(guān)系數(shù)。,多元相關(guān)系數(shù)R,式中：,,式中：R表示變量Y與變量x2,x3,…,xk的相關(guān)程度用多重相關(guān)系數(shù),案例,二. 回歸分析,,回歸分析是通過一定的數(shù)學(xué)表達式描述變量之間的數(shù)量變化關(guān)系，并進行預(yù)測?；貧w分析分為：一元線性回歸分析、多元線性回歸分析、非線性回歸分析、曲線估計、時間序列的曲線估計等模型。,回歸分析和相關(guān)分析都是研究變量間關(guān)系的統(tǒng)計學(xué)課題。但兩者有所側(cè)重：在回歸分析中，變量Y成為因變量，自變量x可以是隨機變量；而

16、在相關(guān)分析中，x,y都是隨機變量；回歸分析通過一定的數(shù)學(xué)表達式描述變量之間的數(shù)量變化關(guān)系，并進行預(yù)測。,一元線性回歸分析,最簡單的情形是一個自變量和一個因變量，且它們大體上有線性關(guān)系，這叫一元線性回歸。,計算公式,其中：X是自變量；y是因變量；β0是回歸常數(shù)；β1是回歸系數(shù)；ε是隨機擾動項,最小二乘法,基本思想是使殘差e的平方： e2=∑( y-?)2 ＝最小,例題1數(shù)據(jù)分析中的回歸分析,例題2： 旅行成本法(TCM),森林生

17、態(tài)系統(tǒng)價值評價在全球范圍內(nèi)成為環(huán)境經(jīng)濟學(xué)和生態(tài)經(jīng)濟學(xué)的研究熱點和焦點之一.條件價值法和旅行成本法是評價公共物品尤其是環(huán)境物品價值的兩種標準方法。,旅行成本法(TCM),基本思路是將去某一景點的游客的旅行費用當作“門票”一樣對待。游客對同一景點有相同的需求曲線，旅行成本增加，旅游次數(shù)減少。旅行成本法的目的是建立旅行次數(shù)(出游率)與門票費用的對應(yīng)關(guān)系(即需求曲線)。從而計算消費者剩余，以對景點進行評價。,案例,,,,千人訪客率=-10

18、*門票+70依據(jù)此公式，求出不同地點在不同門票(旅行成本)下的旅行者人數(shù)。然后對每一個門票的旅行人數(shù)加總。求不同門票下的總收入加總的總收入即為此旅游景點的總價值(消費者剩余),作業(yè)1,一貿(mào)易公司專門經(jīng)營某種雜糧的批發(fā)業(yè)務(wù)。公司現(xiàn)有庫容5000擔的倉庫。1月1日，公司擁有庫存1000擔雜糧，并有資金20000元。估計第一季度雜糧價格如下表所示。,各月份的進貨單價及出貨單價,,如買進的雜糧當月到貨，但需到下月才能賣出，且規(guī)定“貨到付