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

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

2、學(xué)運(yùn)算、統(tǒng)計(jì)分析功能，利用它的規(guī)劃求解功能就可以快速、高效地求解線性規(guī)劃問題。一、線性規(guī)劃模型n 數(shù)學(xué)模型：目標(biāo)函數(shù)：Max/Min S=C1X1+C2X2+.+CnXn約束條件： a11 X1 +a12 X2+.+a1n Xn(,)b1 a21 X1 +a22 X2+.+a2n Xn(,)b2 am1 X1 +am2 X2+.+amn Xn(,)bm X1 , X2. Xn0一、線性規(guī)劃模型其中：nS為目標(biāo)函數(shù)；nXj為決策變量；naij為技術(shù)系數(shù)；nbi為約束值；nCj為費(fèi)用系數(shù)；nm為約束條件的個(gè)數(shù);nn為變量個(gè)數(shù)。一、線性規(guī)劃模型n單純型法原理：在找出一個(gè)基可行解后，判斷它是否為最優(yōu)解

3、，如果不是，則另外換一個(gè)基可行解，直到得到問題的解答。整個(gè)計(jì)算過程實(shí)際上一個(gè)在基可行解上的迭代過程。n由于基可行解是有限的，如果有最優(yōu)解，則經(jīng)過有限步迭代可以達(dá)到。二、 Excel求解線性規(guī)劃問題步驟：步驟：1、加載【規(guī)劃求解】宏2、定義規(guī)劃求解參數(shù) 【目標(biāo)單元格】 【等于】 【可變單元格】 【約束】 【選項(xiàng)】3、求解 【目標(biāo)單元格】是指選定計(jì)算問題的目標(biāo)并含有計(jì)算公式的單元格?！镜扔凇堪磫栴}的目標(biāo)進(jìn)行求解。【可變單元格】是指決策變量（不含公式）所在的單元格，可以有多個(gè)單元格或區(qū)域【約束】增加、修改、刪除各個(gè)約束條件?！具x項(xiàng)】決定采用線性規(guī)劃還是非線性規(guī)劃。案例研究案例研究合理利用線材問題：n

4、現(xiàn)要做100套鋼架，每套用長度分別為2.9m、 2.1m、 1.5m的圓鋼各一根，已知原料長7.4m，問如何下料，使所用的原材料最?。縩為了節(jié)約原材料，需要進(jìn)行套裁，有如下5套裁剪方案：套裁方案方案1方案2方案3方案4方案5下料 2.9m12010根數(shù) 2.1m002211.5m31203合計(jì)7.47.37.27.16.6料頭(m)00.10.30.30.81. 建立線性規(guī)劃模型n設(shè)按方案15下料的原材料根數(shù)分別為x1,x2,x3,x4,x5, 根據(jù)上表，列出數(shù)學(xué)模型: Min z = 0 x1+0.1x2+0.2x3+0.3x4+0.8x5 且滿足： x1+2x2+x4100 2x3+2x4

5、+x5100 3x1+x2+2x3+x5100 x1,x2,x3,x4,x50在Excel中輸入數(shù)據(jù)其中：nB3:G6為原始數(shù)據(jù)區(qū)；nB12:F12為方案15的決策變量（x1,x2,x3,x4,x5），即可變單元格；nC8為目標(biāo)單元格，輸入目標(biāo)函數(shù)；nC14、C15、C16輸入約束條件，等于約束條件的左式減去右式。合理利用線材問題的計(jì)算公式單元公式說明C8=B6*B12+C6*C12+D6*D12+E6*E12+F6*F12料頭C14 =G3-B3*B12-C3*C12-D3*D12-E3*E12-F3*F122.9m圓鋼C15 =G4-B4*B12-C4*C12-D4*D12-E4*E12-

6、F4*F122.1m圓鋼C16 =G5-B5*B12-C5*C12-D5*D12-E5*E12-F5*F121.5m圓鋼輸入目標(biāo)單元格、可變單元格和約束條件求解n在【選項(xiàng)】中選擇【線性規(guī)劃模型】n一切設(shè)定好以后，單擊【求解】按鈕，規(guī)劃工具開始運(yùn)行；n運(yùn)算結(jié)果有三個(gè)選項(xiàng)：【運(yùn)算結(jié)果報(bào)告】【敏感性報(bào)告】【極限值報(bào)告】，分別生成三個(gè)報(bào)告。（見圖1）課堂作業(yè)n某藥廠生產(chǎn)A、B、C三種藥物，可供選擇的原料有甲、乙、丙、丁四種，成本分別是每公斤5元、6元、7元、8元。每公斤不同原料所能提供的各種藥物如表一所示。藥廠要求每天生產(chǎn)A藥品恰好100克、B藥品至少530克、C藥品不超過160克。要求選配各種原料的

7、數(shù)量，即滿足生產(chǎn)的需要，又使總成本最少。原料需求表 原料藥物甲5乙6丙 7丁8A1111B5465C2112（1）建立建立線性規(guī)劃模型：設(shè)X1、X2、X3、X4分別表示甲、乙、丙、丁原料的用量，得到如下線性規(guī)劃模型：目標(biāo)函數(shù)： Min Z5X16X27X38X4約束條件： X1+ X2+ X3+ X4=100 5X1+4X2+6X3+5X4530 2X1+ X2+ X3+2X4160 X1, X2，X4，X30n在“規(guī)劃求解參數(shù)”對話框中通過點(diǎn)擊C8單元格使“目標(biāo)單元格”出現(xiàn)$C$8的絕對引址，并根據(jù)本題題意在其后的小框框內(nèi)選擇“最小值”。n在“可變單元格”中通過從表格中選擇D3:G3區(qū)域，使

8、之在文本框內(nèi)出現(xiàn)$D$3:$G$3。n在“約束條件”處按“增加”，然后在出現(xiàn)的“增加約束”對話框中的“單元格引用位置”處通過點(diǎn)擊C5單元格使之出現(xiàn)$C$5,在后面的框框內(nèi)選“”，“約束值”編輯為$B$5。n第二、三、四個(gè)約束條件分別編輯為“$C$6$B$6”,“$C$7$B$7”,“$D$3:$G$30”. n按“確定”退出。按“求解”按鈕，在彈出的“規(guī)劃求解結(jié)果”對話框內(nèi)可根據(jù)需要生成運(yùn)算結(jié)果、敏感性分析和限制范圍的報(bào)告，然后按“確定”對模型進(jìn)行求解。n（6）如發(fā)現(xiàn)數(shù)字解為小數(shù)，可按需要該為用整數(shù)表示，方法如下： 按住Ctrl鍵，分別選定需改為用整數(shù)表示的單元格D3、E3、F3、G3、C8。

9、 選取“格式”、“單元格 ”、“數(shù)字”、“科學(xué)計(jì)數(shù)”。 在“小數(shù)位數(shù)”中選定“0”格式。按“確定”退出。第二節(jié) Excel在統(tǒng)計(jì)分析中運(yùn)用一、相關(guān)分析n相關(guān)分析是一種描述變量之間的相關(guān)程度的分析方法。n在相關(guān)分析中，所有變量都是隨機(jī)變量，它們之間并不存在被解釋變量和解釋變量之間的關(guān)系。n相關(guān)分析有簡單相關(guān)和多元相關(guān)、線性相關(guān)和非線性相關(guān)之間的關(guān)系、正相關(guān)和負(fù)相關(guān)之分。簡單相關(guān)分析n總體相關(guān)系數(shù)nCov(x,y)表示隨機(jī)變量x與y的協(xié)方差nVar(x)表示隨機(jī)變量x的方差nVar(y)表示隨機(jī)變量y的方差)()(),(yVarxVaryxCovxy總體相關(guān)系數(shù)滿足如下性質(zhì)：n|xy|1n|xy|

10、=1的充要條件是X和Y依概率線性相關(guān)。總體相關(guān)系數(shù)xy一般是得不到的，我們只能根據(jù)樣本觀測值估計(jì)。樣本的相關(guān)系數(shù)n設(shè)隨機(jī)變量x,y的一組樣本為(xi, yi)，i=1,2,，n，則定義x的樣本方差為S2x，y的樣本方差為S2y，x和y的樣本協(xié)方差為Sxy，則：1)(22nxxSix1)(22nyySiy1)(2nyyxxSiixyVARP返回樣本總體的方差。語法：VARP(number1,number2)說明：222)(nxxnVARPCOVER返回協(xié)方差，即每對數(shù)據(jù)點(diǎn)的偏差乘積的平均數(shù)，利用協(xié)方差可以決定兩個(gè)數(shù)據(jù)集之間的關(guān)系。語法：COVER(array1,array2) array1:第一

11、個(gè)單元格區(qū)域 array2:第二個(gè)單元格區(qū)域公式：)( )(1),(1yjnjhjuyuxnYXCovCORREL相關(guān)系數(shù)函數(shù)yxyxyxCov),(,例題1n某機(jī)構(gòu)調(diào)查10個(gè)公司的年齡和年銷售額（萬元）的統(tǒng)計(jì)數(shù)據(jù)。公司年齡X銷售額Y13252106035254635512606156579608220995510750計(jì)算結(jié)果列 1列 2列 11列 20.9228081多元相關(guān)n對于若干變量之間的關(guān)聯(lián)程度進(jìn)行估計(jì)。iikkiiiuXXXY.33221n多元相關(guān)系數(shù)是度量一個(gè)變量與其他所有變量相關(guān)程度的數(shù)量指標(biāo)。記為R。nR定義為最小二乘估計(jì)值與變量Y的觀測值的簡單相關(guān)系數(shù)。多元相關(guān)系數(shù)R22

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

13、量變化關(guān)系，并進(jìn)行預(yù)測。一元線性回歸分析n最簡單的情形是一個(gè)自變量和一個(gè)因變量，且它們大體上有線性關(guān)系，這叫一元線性回歸。計(jì)算公式xy10其中：X是自變量；y是因變量；0是回歸常數(shù)；1是回歸系數(shù)；是隨機(jī)擾動(dòng)項(xiàng)最小二乘法基本思想是使殘差e的平方： e2=( y-)2 最小21)()(xxyyxxxy10年份糧食產(chǎn)量(萬噸)化肥(萬噸)播種面積(公頃)19882097.5291.9913943419892134.5333.6906369419902148.7386.2884238719912314.5502902339219922217.1541.5890401619932904.0610.893

14、0769219942893.5661.5901401919952710.5662.7903500019962545.7677.5937642719972948.4637.4936000019982663.0724.59057823例題1數(shù)據(jù)分析中的回歸分析例題2： 旅行成本法(TCM)n森林生態(tài)系統(tǒng)價(jià)值評價(jià)在全球范圍內(nèi)成為環(huán)境經(jīng)濟(jì)學(xué)和生態(tài)經(jīng)濟(jì)學(xué)的研究熱點(diǎn)和焦點(diǎn)之一.n條件價(jià)值法和旅行成本法是評價(jià)公共物品尤其是環(huán)境物品價(jià)值的兩種標(biāo)準(zhǔn)方法。旅行成本法(TCM)n基本思路是將去某一景點(diǎn)的游客的旅行費(fèi)用當(dāng)作“門票”一樣對待。n游客對同一景點(diǎn)有相同的需求曲線，旅行成本增加，旅游次數(shù)減少。n旅行成本法的目

15、的是建立旅行次數(shù)(出游率)與門票費(fèi)用的對應(yīng)關(guān)系(即需求曲線)。從而計(jì)算消費(fèi)者剩余，以對景點(diǎn)進(jìn)行評價(jià)。案例訪問人數(shù)訪問人數(shù)訪客出發(fā)地訪客出發(fā)地旅行里程旅行里程2501504002100450031501600420030005250出發(fā)地出發(fā)地旅行成本旅行成本(元元)訪問人數(shù)訪問人數(shù) 出發(fā)地總出發(fā)地總?cè)丝谌丝诿壳嗽L每千人訪問率問率122505,000502340010,00040344500150,0003045160080,00020563000300,00010n千人訪客率=-10*門票+70n依據(jù)此公式，求出不同地點(diǎn)在不同門票(旅行成本)下的旅行者人數(shù)。n然后對每一個(gè)門票的旅行人數(shù)加總。n求不同門票下的總收入n加總的總收入即為此旅游景點(diǎn)的總價(jià)值(消費(fèi)者剩余)新增收新增收費(fèi)值費(fèi)值出發(fā)地出發(fā)地1的游客的游客數(shù)數(shù)出發(fā)地出發(fā)地2的游客的游客數(shù)數(shù)出發(fā)地出發(fā)地3的游客的游客數(shù)數(shù)出發(fā)地出發(fā)地4的游客的游客數(shù)數(shù)出發(fā)地出發(fā)地5的游客的游客數(shù)數(shù)游客總游客總數(shù)數(shù)總收入總收入030050060002400600015，200 012504004500160030009，750 975022003003000 800 04，300 860031502001500 01，850 555041