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

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

2、計(jì)算量較大，尤其對(duì)多變量的規(guī)劃求解，而且在敏感性分析中要做大量的重復(fù)性工作。,一、線性規(guī)劃模型簡(jiǎn)介,Excel提供了超強(qiáng)的數(shù)學(xué)運(yùn)算、統(tǒng)計(jì)分析功能，利用它的規(guī)劃求解功能就可以快速、高效地求解線性規(guī)劃問(wèn)題。,一、線性規(guī)劃模型,數(shù)學(xué)模型：目標(biāo)函數(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為目標(biāo)函數(shù)；Xj為決策變量；aij為技術(shù)系數(shù)；bi為約束值；Cj為費(fèi)用系數(shù)；m為約束條件的個(gè)數(shù);n為變量個(gè)數(shù)。,一、線性規(guī)劃模型,單純型法原理：在找出

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

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

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為目標(biāo)單元格，輸入目標(biāo)函數(shù)；C14、C15、C16輸入約束條件，等于約束條件的左式減去右式。,合理利用線材問(wèn)題的計(jì)算公式,,輸入目標(biāo)單元格、可變單元格和約束條件,求解,在【選項(xiàng)】中選擇【線性規(guī)劃模型】一切設(shè)定好以后，單擊【求解】按鈕，規(guī)劃工具開(kāi)始運(yùn)行；運(yùn)算結(jié)果有三個(gè)選項(xiàng)：【運(yùn)算結(jié)果報(bào)告】【敏感性報(bào)告】【

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

9、到如下線性規(guī)劃模型：目標(biāo)函數(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ù)”對(duì)話框中通過(guò)點(diǎn)擊C8單元格使“目標(biāo)單元格”出現(xiàn)$C$

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

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

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

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

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

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

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

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

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