實訓一利用Excel處理成績表【實訓目的】Excel表格的建立掌握Excel常用公式和函數【實訓內容】1、單元格引用相對引用:在復制公式時,按一定的規律復制,相對于公式復制前的地址而言,橫向或縱向移動一定的單元格。絕對引用:在行號和列標前加$,在復制公式時,整個地址不變。混合引用：在行號或列標前加$,在復制公式時,行號或列標不變。2、公式運算符：+，-，*，/，^，%，=，<>，<，<=，>，>=，&文本連結公式：以等號開頭，包含一系列數值、函數、運算符、單元格引用使用：選擇單元格，從編輯欄輸入公式，√表示確認,×表示取消函數格式：函數名(參數序列)，參數大多以,分隔,也可無參數求和函數sum（）：返回某一單元格區域中所有數字之和；條件求和函數sumif（條件區域，條件，單元格區域）：根據指定條件對若干單元格求和；求平均值函數average（）：返回某一單元格區域中所有數字的平均值；條件求平均值函數averageif(條件區域，條件，單元格區域)：根據指定條件對若干單元格求平均數；（5）求最大值、最小值函數max()、min()（6）計數函數count(單元格列表）：計算區域內數值型數據的個數；countif(單元格列表，條件)：統計區域內滿足條件的單元格的數目；（7）排名函數rank(排名的數字，排名的數字所在的區域，排序方式）排序方式有兩種：若是忽略或者為0，則為降序；若是非零值則是升序。二、實訓案例下表是20個同學的三門課程大學語文、高等數學B，大學英語的成績，試求每門課程的最高分、最低分、平均分、男生人數、女生人數、男生各科平均分、女生各科平均分、男生英語總分、每個同學的總分以及排名。成績表學號性別大學語文高等數學B大學英語1男8687782女8087733男8887854女8286845女8582856男9092867男7584808女8793869男80918910女77897911男86948312男83887613男91858014女76867415女74828016男87827117男78968118男80898219女81957620男879581Excel演算步驟1.每門課程的最高分、最低分和平均分第一步：在C23欄輸入公式“=MAX(C3:C22)”,得大學語文的最高分為91，將鼠標置于C23欄右下角，按住“+”型符號往右拖（Excel的句柄填充功能)，直至E23欄，如圖1-1所示。圖1-1各科最高分圖1-2各科最低分第二步：在C24欄輸入公式“=Min(C3:C22)”,得大學語文的最低分為74，將鼠標置于C24欄右下角，按住“+”型符號往右拖（Excel的句柄填充功能)，直至E24欄，如圖1-2所示。第三步：在C25欄輸入公式“=AVERAGE(C3:C22)”,得大學語文的平均分為82.65，將鼠標置于C25欄右下角，按住“+”型符號往右拖，直至E25欄，如圖1-3所示。圖1-3各科平均分圖1-4男生、女生人數2.男生人數、女生人數第三步：在C26欄輸入公式“=COUNTIF(B3:B22,"男")”,得男生人數為12，在C27欄輸入公式“=COUNTIF(B3:B22,"女")”,得女生人數為8，如圖1-4所示。3.男生各科平均分、女生各科平均分、男生英語總分第四步：在C28欄輸入公式“=AVERAGEIF($B$3:$B$22,"男",C3:C22)”,得男生大學語文平均分為84.25，將鼠標置于C28欄右下角，按住“+”型符號往右拖，直至E28欄，如圖1-5所示。圖1-5男生各科平均分、女生各科平均分圖1-6男生英語總分第五步：同理，在C29欄輸入公式“=AVERAGEIF($B$3:$B$22,"女",C3:C22)”,得女生大學語文平均分為80.25，將鼠標置于C29欄右下角，按住“+”型符號往右拖，直至E29欄，如圖1-5所示。第六步：在C31欄輸入公式“=SUMIF(B3:B22,"男",E3:E22)”,得男生英語總分為972，如圖1-6所示。每個同學的總分以及排名第七步：在F3欄輸入公式“=SUM(C3:E3)”,得學號1同學的總分為251，將鼠標置于F3欄右下角，按住“+”型符號往下拖，直至F22欄，如圖1-7所示。第八步：在G3欄輸入公式“=RANK(F3,$F$3:$F$22,0)”,得學號1同學的總分為12，將鼠標置于G3欄右下角，按住“+”型符號往下拖，直至G22欄，如圖1-8所示圖1-7每個學生的總分圖1-8每個學生的排名【實訓練習】實訓1：完成Excel文檔“實訓一學生成績表”中的任務。實訓2：在Excel文檔“實訓一學生成績表”sheet2中快速輸入以下數據，并將此工作簿命名為“快速輸入”數學實驗報告實驗序號：日期：_____年_____月_____日班級姓名學號實驗名稱問題背景簡述：實驗目的：實驗原理與使用軟件實驗過程記錄（含：基本步驟、程序的文件名及異常情況記錄等）：實驗結果報告及實驗總結：教師評語：實訓二利用EXCEL計算增長率【實訓目的】1.掌握增長率和復合增長率的計算會利用EXCEL繪制柱形圖、折線圖和等相關圖形。【實訓內容】一、基本概念概念1：增長率是指事物增長的數量與原來的數量的百分比值。假設一項投資基期的本金為，末期的價值為，則該項投資的價值增長量為,于是,該項投資的增長率為.概念2：復合增長率是反映事物從某一年的數量開始，按照某個固定指數經過若干年增長后達到預期數量的百分比值。已知一項投資的基期本金為,投資期數為期,末期價值為,則由可得Excel中復合增長率r可表示為或二、實訓案例1、根據國際貨幣基金組織WEO數據，試比較中國與美國、日本、俄羅斯、英國、法國、德國、印度、韓國等國2008年相對于1990、2000年經濟增長率和年平均增長率，以及2005年到2008年之間每年的經濟增長率和年均增長率。表2-1：中、美、日、俄、英、法、德、印、韓GDP數據一覽表國家1990年2000年2005年2006年2007年2008年中國1866899215183218211924257306300670美國5803199515126384133989140777144414日本437022850298995017345507364751580675075666俄羅斯73056216254269035331114416680英國5703976512541132581398914461法國103371443017241180811895319480德國127492062522422232512428224958印度54914207591345855396628455380525253韓國1866909603236086524099087438975013010239377資料來源：國際貨幣基金組織WEO數據庫，單位：億本幣（均為2008年流通貨幣），見國家統計局網站《統計數據——國際數據》Excel演算步驟1.9國18年、8年的GDP增長率計算與比較第一步:在H5欄輸入公式“=(G5/B5-1)*100%”,得出中國2008年相對于1990年的GDP增長率為1510.6%,如圖2-1所示.圖2-1完成中國18年GDP增長率計算第二步:將鼠標置于H5欄右下角,按住“+”型符號往下拖(即使用Excel的句柄填充功能),直至H13欄,出現美國、日本、俄羅斯、英國、法國、德國、印度、韓國2008年相對于1990年的經濟增長率,如圖2-2所示.圖2-2完成各國18年GDP增長率計算第三步:類似于18年GDP增長率計算方法,可求各國2008年相對于2000年的GDP增長率,如圖2-3所示.圖2-3完成各國8年GDP增長率計算第四步:選定各國8年和18年GDP增長率(包括9國國名),雙擊圖表窗口出現圖表導向,選擇柱形圖,順序點擊至“完成”,做適當修飾后(使圖更清晰),即完成9國8年和18年GDP增長率的比較柱形圖,如圖2-4所示.可以看出,中國18年GDP增長率最高,俄羅斯8年GDP增長率最高,印度和韓國排在中、俄之后和西方發達國家之前.圖2-4各國GDP增長率比較圖2.2005-2008年的GDP年度增長率計算與比較第一步:在J5欄輸入公式“=(E5/D5-1)*100%”,選定J5欄,按住“+”型符號,然后利用Excel的句柄填充功能,先從J5拖至L5,然后選定J5至L5欄,按住“+”型符號再拖至L13,得到各國2006-2008年每年相對于上一年的GDP增長率結果,如圖2-5所示.圖2-5完成各國GDP年度增長率計算第二步:選定中國、美國、日本、俄羅斯、英國、法國、德國、印度、韓國9國2006-2008年每年GDP增長率(包括9國國名),雙擊圖表窗口出現圖表導向,選擇柱形圖,順序點擊至“完成”,即完成9國2006-2008年每年GDP增長率的比較柱形圖,如圖2-6所示.可以看出,這三年俄羅斯每年GDP增長率最高,中國第二,印度第三.圖2-6各國GDP年度增長率比較圖3.各國18年、8年的GDP復合增長率計算與比較第一步:建立計算公式.第二步:計算各國18年GDP年均增長率.在M5欄輸入公式“=POWER(G5/B5，1/18)-1”,計算出中國2008年相對于1990年18年的GDP復合增長率為16.7%,選定M5欄拖至M13欄,得到美國、日本、俄羅斯、英國、法國、德國、印度、韓國的1990-2008年的GDP復合增長率,如圖2-7所示.圖2-7完成各國18年GDP復合增長率計算第三步:仿第二步,計算8年GDP復合增長率,結果如圖2-8所示.圖2-8完成各國8年GDP復合增長率計算第四步:作圖比較9國1990-2008年和2000-2008年的GDP復合增長率.如圖2-9所示,從圖上比較可以看出,1990年以來,中國經濟增長最快,印度緊追其后;2000年以來,俄羅斯經濟增長最快,中國緊追其后.但2000年以來,除法國、俄羅斯GDP增長加快之外,其他國家均有所放慢.圖2-9各國8年、18年GDP復合增長率比較圖二、高技術產業銷售收入增長分析問題2表2-2給出了我國高技術產業2002-2006年的銷售收入數據,試通過年度增長率和復合增長率分析比較各產業的增長情況.表2-2我國高技術產業2002-2006年的銷售收入數據單位:億元產業2002年2003年2004年2005年2006年醫藥制造業2279.982750.7330334019.834718.82航空航天器制造業499.9547.2498.4781.37798.88電子及通信設備制造業7658.679927.1413819.116646.2521068.86電子計算機及辦公設備制造業3441.676305.979192.710722.1512634.18醫療設備及儀器儀表制造業734.04880.4813031752.182363.82合計14614.2620411.5227846.233921.7841584.56第一步:借助Excel軟件，根據公式分別計算每一個產業2003-2006年每年相對于上一年的銷售收入年度增長率，根據公式分別計算每一個產業2002-2006年的銷售收入復合增長率，如表2-3所示.可以看出，電子計算機及辦公設備制造業的銷售收入復合增長率為0.3842，高于其他四個產業，該產業2003年銷售收入增長率為0.8322，呈現出爆發式增長的勢頭，但是該產業的銷售收入增長速度從2004年開始下降，2005年、2006年該產業的銷售收入增長率均保持在0.17左右；航空航天器制造業2004年銷售收入增長率為-0.0892，呈現出負增長，2003年、2006年該產業的銷售收入增長率均小于0.1，而2005年該產業的銷售收入出現大幅增長，增長率高達0.5678.表2-3我國高技術產業2003-2006年年度增長率和復合增長率產業年度增長率復合增長率2003年2004年2005年2006年醫藥制造業0.20650.10260.32540.17390.1994航空航天器制造業0.0946-0.08920.56780.02240.1243電子及通信設備制造業0.29620.39210.20460.26570.2879電子計算機及辦公設備制造業0.83220.45780.16640.17830.3842醫療設備及儀器儀表制造業0.19950.47990.34470.34910.3396第二步:作出增長率趨勢比較圖,如圖2-10所示.可以看出，電子計算機及辦公設備制造業的銷售收入增長率從2003年的0.8322急劇下降到2005年的0.1664，從2005年開始增長率趨于平穩；航空航天器制造業的銷售收入增長率波動較大，而電子及通信設備制造業、醫療設備及儀器儀表制造業的銷售收入增長率波動比較平穩.圖2-10我國高技術產業2002-2006年年度增長率趨勢比較三、中美兩國GDP差距發展趨勢分析根據表2-4提供的中國和美國1978-2010年GDP數據,分析兩國之間GDP差距的發展趨勢,并采用2010年相對于1978年的復合增長率討論未來差距的發展趨勢.表2-4中國和美國1978-2010年GDP數據單位:億美元年份美國中國年份美國中國1978年229472164.6080471995年739777279.8107081979年256332612.6045641996年781698561.0538321980年278953034.4459931997年830439525.1477511981年312842868.9736641998年8747010194.745371982年325502812.1499751999年9268010833.184531983年353673017.5608282000年9817011983.887131984年393323105.6010762001年10128013248.182091985年422033069.7991152002年10470014538.202631986年446282975.7313052003年10961016409.664961987年473953239.8171982004年11686019315.971591988年510384041.590492005年12422022343.531021989年548444513.2272992006年133989.326801.438021990年580313902.9481042007542621991年599594091.9594212008年143690.843025.812611992年633774880.9825962009年141190.549963.812661993年665746132.2281222010年146241.859847.067671994年707225592.052261第一步:借助Excel軟件,根據公式計算中國和美國在1978-2010期間的GDP復合增長率分別為11.1%、6%，說明兩國的經濟在這32年時間里整體上都呈現出增長.然后根據公式分別求得中國和美國1979-2010年GDP的年度增長率，如圖2-11所示.圖2-11美國、中國1979-2010年年度增長率第二步:計算中國和美國GDP差距走勢,如圖2-12所示，對應的趨勢圖如圖2-13所示.圖2-12中國和美國1978-2010年GDP及其差距數據圖2-13中國和美國1978-2010年GDP及其差距走勢可以看出，1978年美國的GDP是中國的10.6倍，在1978-1994年美國GDP每年都能保持0.05左右的增長速度，而中國在此期間GDP增長不穩定，經常出現負增長，所以中美兩國GDP差距越來越大.1995年以后，雖然中國GDP呈現增長趨勢，但由于美國本身的GDP基數大，1995-2006年中美兩國GDP差距仍較大.2007-2010年美國的經濟呈現出經濟衰退的趨勢，而中國GDP仍能保持較高的增長率，因而中美兩國GDP差距從2007年開始縮小.第三步:根據Excel提供的趨勢分析工具,可得中國和美國2010年以后GDP及其差距走勢預測趨勢圖,如圖2-14所示.可以看出,大約到2025年,中國的GDP會趕超美國.圖2-14中國和美國2010年以后GDP及其差距趨勢預測圖【實訓練習】表2-5是我國2009年至2018年10年間的國民總收入等6個指標的相關數據，請根據數據分析各項指標的變化情況,并用圖表表示.表2-52009年至2018年國民總收入等6項指標數據指標2009年2010年2011年2012年2013年國民總收入(億元)347934.9410354.1483392.8537329588141.2國內生產總值(億元)348517.7412119.3487940.2538580592963.2第一產業增加值(億元)33583.838430.844781.449084.553028.1第二產業增加值(億元)160171.7191629.8227038.8244643.3261956.1第三產業增加值(億元)154762.2182058.6216120244852.2277979.1人均國內生產總值(元)2618030808363023987443684指標2014年2015年2016年2017年2018年國民總收入(億元)642097.6683390.5737074818461896915國內生產總值(億元)641280.6685992.9740060.8820754.3900309第一產業增加值(億元)55626.357774.660139.262099.564734第二產業增加值(億元)277571.8282040.3296547.7332742.7366001第三產業增加值(億元)308082.5346178383373.9425912.1469575人均國內生產總值(元)4700550028536805920164644任務1:繪制我國國民總收入等6個指標的直方圖；任務2:計算我國2018年相對于2009年各指標的增長率;任務3:計算我國2015年到2018年之間每年的增長率;任務4:計算我國2018年相對于2009年各指標的復合增長率;任務5:比較我國2015年到2018年之間每年的增長率和復合增長率的差異;任務6:選擇合適的圖表表示上述計算結果;任務7:根據你們的數據分析總結國民總收入等6個指標的變化情況,撰寫實訓報告.數學實驗報告實驗序號：日期：_____年_____月_____日班級姓名學號實驗名稱問題背景簡述：實驗目的：實驗原理與使用軟件實驗過程記錄（含：基本步驟、程序的文件名及異常情況記錄等）：實驗結果報告及實驗總結：教師評語：實訓三利用EXCEL求解投入產出問題【實訓目的】掌握利用EXCEL進行矩陣的線性運算，矩陣的乘法，逆矩陣以及矩陣的轉置；掌握利用EXCEL求解線性方程組。【實訓內容】1.mmult(array1,array2)函數：返回兩個矩陣array1,array2的乘積，結果矩陣的行數與array1的行數相同，矩陣的列數與array2的列數相同。注意：array1的列數必須與array2的行數相同，而且兩個數組中都只能包含數值。按Ctrl+Shift+Enter組合鍵得到兩個矩陣的乘積。2.minverse函數：返回矩陣的逆距陣。語法:minverse(array)Array必需行數和列數相等的數值矩陣。按Ctrl+Shift+Enter組合鍵得到所求矩陣的逆矩陣。3.transpose(array)函數：返回矩陣的轉置距陣。按Ctrl+Shift+Enter組合鍵得到矩陣的轉置距陣.4.線性方程組的有關概念線性方程組就是指n元一次方程組,設含有n個未知數m個方程的線性方程組為(6.1)記,,，則方程組(6.1)可表示為.其中,稱為方程組(6.1)的系數矩陣,B稱為右端常數矩陣,X稱為未知數矩陣.如果矩陣A可逆,則用左乘上式兩端,得到方程組的解為.二、實訓操作實訓1設矩陣，，求（1）；（2）；（3）（4）;（5）;（6）;解決方案:第一步:在EXCEL中輸入矩陣A,B，如圖所示：第二步：在A9欄輸入“=A3+E3”，然后句柄填充，可得A+B第三步：在E9欄輸入“=A3-E3”，然后句柄填充，可得A-B第四步：在A15欄輸入“=4*A3”，然后句柄填充，可得4A第五步：選中E15：G17單元格，在E15單元格中輸入“=MMULT(A3:C5,E3:G5)”，同時按下CTRL+SHIFT+ENTER鍵，可得A*B第六步：選中A21：C23單元格，在A21單元格中輸入“=MINVERSE(A3:C5)”，同時按下CTRL+SHIFT+ENTER鍵，可得A的逆矩陣第七步：選中A25：C27單元格，在A25單元格中輸入“=TRANSPOSE(A3:C5)”，同時按下CTRL+SHIFT+ENTER鍵，可得A的轉置矩陣實訓2解線性方程組解決方案:第一步:在EXCEL中輸入系數矩陣A和常數矩陣B，如圖所示：第二步：選中A8：C10單元格，在A8單元格中輸入“=MINVERSE(A2:C4)”，同時按下CTRL+SHIFT+ENTER鍵，可得系數矩陣A的逆矩陣第三步：選中B13：B15單元格，在B13單元格中輸入“=MMULT(A8:C10,E2:E4)”，同時按下CTRL+SHIFT+ENTER鍵，可得。【實訓練習】1、設矩陣，，求（1）；（2）；（3）（4）;（5）2、3、設，求4、利用逆矩陣解下列方程組：5、設A=11111-11-11,B=數學實驗報告實驗序號：日期：_____年月日班級姓名學號實驗名稱問題背景簡述：實驗目的：實驗原理與使用軟件實驗過程記錄（含：基本步驟、程序的文件名及異常情況記錄等）：實驗結果報告及實驗總結：教師評語：實訓四利用EXCEL求解投入產出問題【實訓目的】掌握利用EXCEL求直接消耗系數；掌握利用EXCEL求解消耗平衡(投入產出)方程組。【實訓內容】一、投入產出的平衡關系①從縱向看，中間投入+最初投入=總投入。②從橫向看，中間使用+最終需求=總產出。③每一個部門的總投入等于該部門的總產出。概念1：計算每個部門總產出1元價值的產品時，將相應各部門向該部門的直接輸出所占的比例稱為直接消耗系數，直接消耗系數是常數。二、如果消耗平衡方程組可以表示為因為,所以(I－A)X=Y.如果矩陣I－A可逆,則在方程(I－A)X=Y兩邊同時左乘矩陣(I－A)-1,可得解的矩陣表示為X=(I－A)-1Y其中,(I－A)-1為矩陣I－A的逆矩陣,稱為里昂惕夫逆矩陣.【實訓操作】一、利用Excel求直接消耗系數矩陣典型問題1利用Excel求解本章第一節表6-1的直接消耗系數矩陣表6-1投入產出表單位:萬億元產出投入中間使用最終需求總產出工業農業服務業中間投入工業農業服務業1.80.612415610最初投入(增值)933總投入15610解決方案:第一步:在H4欄輸入“=C4/C$8”,得出直接消耗系數a11,即單位價值工業部門產品直接消耗0.2單位的工業部門自身產品.第二步:在H5欄輸入“=D4/$D$8”,得出直接消耗系數a12,即單位價值工業部門產品直接消耗0.1單位的農業部門產品.第三步:在H6欄輸入“=E4/$E$8”,得出直接消耗系數a13,即單位價值工業部門產品直接消耗0.1單位的服務業部門產品，結果如圖6-1所示.圖6-1直接消耗系數矩陣A二、利用Excel解線性方程組典型問題2利用Excel求解投入產出方程組(6.1).0.2x1解決方案:第一步:在工作表的E2至G4區域建立一個單位矩陣I,在I2至I4區域依次輸入33,8,16.第二步:計算I－A.在A6欄輸入“=E2－B2”,利用拖曳的方法將A6欄公式復制到A6至C8的區域,結果如圖6-2所示.第三步:計算(I－A)-1.選中E6至G8區域,輸入公式“=MINVERSE(A6∶C8)”,按下【Ctrl】+【Shift】+【Enter】組合鍵,結果如圖6-3所示.圖6-2方程組6.2)的系數矩陣圖6-3里昂惕夫逆矩陣(I-A)-1第四步:利用公式X=(I-A)-1Y求方程組(6.2)的解.選中I6至I8區域,輸入公式“=MMULT(E6∶G8,I2∶I4)”,按下【Ctrl】+【Shift】+【Enter】組合鍵,得方程組的解為x1=50,x2=30,x3=40,如圖6-4所示.圖6-4線性方程組(6.2)的解三、煤電系統的投入產出模型典型問題3某地區的煤礦、電廠和鐵路三個企業是一個相互關聯的經濟系統,三者之間的直接消耗系數如表6-4所示.假設三個企業除了用于滿足系統內部需求外,還需要滿足外部的訂單需求,已知現階段的外部需求分別為60萬元、25萬元和18萬元.問:現階段各企業的總產出為多少?因為未來一個階段是需求旺季,估計外部需求將會分別增加15萬元、5萬元和7萬元,各企業又該如何安排生產?表6-4直接消耗系數表單位:萬元產出投入中間使用外部訂單需求煤礦電廠鐵路中間投入煤礦電廠鐵路00.150.300.450.050.050.400.150.05602518解決方案:設x1,x2和x3分別表示3個企業現階段的總產出,記，，則即利用Excel求解上述方程組,得，即三個企業現階段的總產出分別為106.16萬元、51.58萬元和54.87萬元.具體結果如圖6-5所示.圖6-5現階段三個企業總產出如果外部需求分別增加15萬元、5萬元和7萬元,記ΔX=Δx1Δx則相應地有A·ΔX+ΔY=ΔX或(I-A)·ΔX=ΔY利用Excel求解該方程組,得Δx1=27.10,Δx2=12.16,Δx3=16.57.所以在未來的一個階段三個企業的總產出應分別增加27.10萬元、12.16萬元和16.57萬元,即三個企業未來一個階段的總產出分別為132.26萬元、63.74萬元和71.44萬元.具體結果如圖6-7所示.圖6-6未來一個階段三個企業產出增加量【實訓練習】實訓1設某廠生產甲、乙、丙3種產品，其中第一季度和第二季度的產量用矩陣表示，其單位成本和銷售單價用矩陣表示,且甲乙丙單位成本銷售單價請使用Excel的MMULT函數求兩個季度的成本總額和銷售總額.實訓2已知，使用Excel的MINVERSE函數求矩陣A的逆矩陣.實訓3已知矩陣方程，請使用Excel求解該矩陣方程.實訓4表6-7是某經濟體系中A、B兩個部門的投入產出資料,如果最終需求變成對A為210百萬元,對B為147百萬元,那么這個經濟體系中各部門的總產出是多少?表6-7投入產出表單位:百萬元產出投入使用方最終需求總產出AB生產方AB120604040140100300200實訓5對由M(制造)、L(勞動)和A(農業)三個部門組成的經濟體系進行調查,它們的產品投入產出分析資料如表6-8所示.表6-8投入產出表單位:億元產出投入買方最終需求總產出MLA賣方MLA12030667.5112.59201030392.5297.5155600450200當對M、L和A的最終需求變為550億元、330億元和67億元時,列出相應的表格,建立方程組,然后求出M、L和A新的總產出是多少.數學實驗報告實驗序號：日期：_____年_____月_____日班級姓名學號實驗名稱問題背景簡述：實驗目的：實驗原理與使用軟件實驗過程記錄（含：基本步驟、程序的文件名及異常情況記錄等）：實驗結果報告及實驗總結：教師評語：實訓五利用EXCEL求解線性規劃問題注意：如果在【數據】菜單中沒有見到【規劃求解】命令，則要單擊【文件】【選項】【加載項】【轉到】命令，在彈出的對話框中的列表框中，選定【規劃求解加載項】選項前的復選框．【實訓目的】掌握使用EXCEL自定義函數進行數據處理掌握線性規劃問題模型建立掌握EXCEL求解線性規劃問題【實訓內容】一、線性規劃問題的三要素．1.有一組決策變量；2.有一個線性目標函數;3.有一組線性約束條件。線性規劃問題的數學模型可表示如下線性規劃問題的求解有很多方法，也有很多工具，比如常用的Matlab、Lingo、Excel等.Excel具有強大的規劃求解功能，可以解決最多有200個變量，100個外在約束和400個簡單約束（決策變量整數約束的上下邊界）的線性規劃與非線性規劃問題，本節主要講解如何使用Excel求解線性規劃問題.使用Excel求解線性規劃規劃求解加載宏是一組命令構成的一個子程序，其功能是可以求出線性和非線性數學規劃問題的最優解和最優值.使用規劃求解加載宏求解數學規劃的步驟：第一步：在Excel工作表中輸入目標函數的系數、決策變量、約束條件的系數、約束條件左端的值和約束條件右端的值（每一個單元格輸入一個數據）；第二步：選定一個單元格存儲目標函數，用定義公式的方式在這個目標單元格內定義目標函數；第三步：選定與決策變量個數相同的單元格（稱為可變單元格），用以存儲決策變量；再選擇與約束條件個數相同的單元格，用定義公式的方式在每一個單元格內計算出相應的約束函數（稱為約束函數單元格）；第四步：點擊規劃求解按鈕，打開規劃求解參數設定對話框，添加約束條件，完成規劃模型的設定.【實訓案例】案例1求解線性規劃問題：Excel演算步驟第一步：啟動Excel，在工作表中的A1，A2，A3，A10，E3，F3單元格中分別輸入文字“目標函數系數”，“決策變量”，“約束條件”，“目標函數值”，“約束條件左端的值”，“約束條件右端的值”；在B1，C1，D1單元格中輸入目標函數的系數1，-2，1，在B4，C4，D4單元格中輸入第一個約束條件的系數1，1，1；同理，在相應單元格中輸入其他約束條件的系數與約束條件右端的值，如下圖4-1所示：圖4-1數據輸入第二步：計算約束條件左端的值和目標函數值．因為約束條件左端的值等于約束條件的系數乘以相應的決策變量，所以在E4單元格中輸入公式“=B4*B2+C4*C2+D4*D2”，在E5單元格中輸入公式“=B5*B2+C5*C2+D5*D2”，依次類推在E9單元格中輸入公式“=B9*B2+C9*C2+D9*D2”；目標函數的值等于目標函數系數乘以決策變量，從而在D10單元格中輸入公式“=B1*B2+C1*C2+D1*D2”，如圖4-2所示．圖4-2計算約束條件左端的值和目標函數值事實上，在計算約束條件左端值時，只需要在E4單元格中輸入公式“=B4*$B$2+C4*$C$2+D4*$D$2”，然后單擊E4單元格，將鼠標至于E4單元格右下角，當光標變為小黑十字時拖曳至E9單元格即可．第三步：單擊【工具】菜單中的【規劃求解】命令，在彈出的規劃求解對話框中輸入各項參數．（1）設置目標單元格和可變單元格在“規劃求解參數”對話框中選中“最大值”前的單選按鈕，設置目標單元格為“$D$10”，可變單元格為“$B$2:$D$2”，如圖4-3所示．圖4-3“規劃求解參數”對話框（2）添加約束條件單擊【規劃求解參數】對話框中的【添加】按鈕，打開【添加約束】對話框，單擊單元格引用位置文本框，然后選定工作表中的E4單元格，則在文本框中顯示“$E$4”，選擇“<=”約束條件；單擊約束值文本框，然后選定工作表中的F4單元格，如圖4-4所示．圖4-4“添加約束”對話框圖中所示約束條件表示，依此類推，把所有約束條件都添加到【規劃求解參數】對話框的【約束】列表框中．第四步：在“選擇求解方法中”點擊“單純線性規劃”，單擊【求解】按鈕，彈出圖4-5所示的【規劃求解結果】對話框，選中【保存規劃求解結果】單選按鈕．圖4-5“規劃求解結果”對話框第五步：在【規劃求解結果】對話框中，單擊【確定】按鈕，工作表中就顯示出規劃求解的結果，如圖4-6所示．圖4-6結果顯示如果要生成運算結果報告，可在【規劃求解】對話框中選擇【報告】列表框中的【運算結果報告】．單擊【確定】按鈕，則產生如圖4-7所示的運算結果報告表，在該表中對約束條件和結果作出了詳細的說明．圖4-7運算結果報告表從圖4-6或圖4-7可以很容易看出，當變量時，目標函數的最大值為．案例2某奶制品加工廠用牛奶生產兩種奶制品，1桶牛奶可以在設備甲上用12小時加工成3公斤，或者在設備乙上用8小時加工成4公斤．根據市場需求，生產的全部能售出，且每公斤獲利24元，每公斤獲利16元．現在加工廠每天能得到50桶牛奶的供應，每天正式工人總的勞動時間為480小時，并且設備甲每天至多能加工100公斤，設備乙的加工能力沒有限制．請為該廠制定一個生產計劃，使得工廠每天獲利最大．解決方案:1.模型建立第一步：根據實際問題，設置決策變量．設每天用桶牛奶生產，用桶牛奶生產.第二步：確定目標函數．設每天獲利元．桶牛奶可生產3公斤，獲利，桶牛奶可生產4公斤，獲利，故.第三步：分析各種資源限制，列出約束條件．原料供應：生產的原料(牛奶)總量不超過每天的供應，即桶；勞動時間：生產的總加工時間不超過總的勞動時間，即；設備能力：的產量不得超過設備甲每天的加工能力，即；非負約束：均不能為負值，即.第四步：綜合目標函數和各個約束條件，寫出整個線性規劃模型如下：2.Excel求解第一步：在工作表中的A14，A15，A16，E15，E16，A22單元格中分別輸入“目標函數系數”，“決策變量”，“約束條件系數”，“約束條件左端的值”，“約束條件右端的值”,“目標函數值”；在B14，C14單元格中輸入目標函數的系數72，64，在B16，C16單元格中輸入第一個約束條件的系數1，1；同理，在相應單元格中輸入其他約束條件的系數與約束條件右端的值，如下圖4-8所示：圖4-8數據輸入第二步：計算約束條件左端的值和目標函數值．在E16單元格中輸入公式“=B16*$B$15+C16*$C$15”，并使用句柄填充拖曳至E20單元格.在B22單元格中輸入公式“=B14*B15+C14*C15”，如圖4-9所示．圖4-9計算約束條件左端的值和目標函數值第三步：單擊【數據】菜單中的【規劃求解】命令，在彈出的規劃求解對話框中輸入各項參數．設置目標單元格和可變單元格在“規劃求解參數”對話框中選中“最大值”前的單選按鈕，設置目標單元格為“$B$22”，可變單元格為“$B$15:$C$15”，如圖4-10所示．圖4-10“規劃求解參數”對話框添加約束條件單擊【添加】按鈕，打開【添加約束】對話框，單擊單元格引用位置文本框，然后選定工作表中的E16至E18單元格，則在文本框中顯示“$E$16:$E$18”，選擇“<=”約束條件；單擊約束值文本框，然后選定工作表中的F16至F18單元格，如圖4-11所示．圖4-11“添加約束”對話框依此類推，把非負約束條件添加到約束列表框中．第四步：在【規劃求解結果】對話框中，單擊【確定】按鈕，工作表中就顯示出規劃求解的結果，如圖4-12所示．圖4-12“規劃求解結果”對話框從圖4-12可以很容易看出，當變量時，目標函數的最大值為．【實訓練習】實訓1某單位一次性購買了10本圖書，書名、數量、單價數據見表7-11：表7-11圖書單價及數量表書名單價數量書名單價數量大學語文18.320政治經濟學2813高等數學3420文學5.512化工與化學23.58自然地理197大學英語2015計算機3840寫作1830英語寫作13.524任務1：請利用EXCEL自定義函數計算出每本書的購買總價.任務2：請利用EXCEL自定義函數計算出購買平均價格、平均數量.實訓2某工廠用A、B兩種配件生產甲、乙兩種產品，每生產1件甲產品使用4個A配件耗時1h，每生產1件乙產品使用4個B配件耗時2h.該廠每天最多可從配件廠獲得16個A配件和12個B配件，按每天工作8h計算，若生產1件甲產品獲利2萬元，生產1件乙產品獲利3萬元，問工廠應如何安排生產，以使得總利潤最大？任務1：建立使總利潤最大的線性規劃模型.任務2：使用EXCEL求解該線性規劃模型.實訓3某農場每天需要使用一種特殊飼料800千克，這種特殊飼料由玉米和大豆粉配制而成，營養要求是至少含有30%的蛋白質和至多5%的纖維，具體成份見表7-12.表7-12飼料每千克飼料中含有成份總量（千克）費用（元/千克）蛋白質纖維玉米0.090.020.3大豆粉0.60.060.9請問農場應該如何配制這種特殊飼料，使得每天的飼料成本最小？任務1：建立使總成本最小的線性規劃模型.任務2：使用EXCEL求解該線性規劃模型.數學實驗報告實驗序號：日期：_____年_____月_____日班級姓名學號實驗名稱實驗目的：實驗原理與使用軟件實驗過程記錄（含：基本步驟、程序的文件名及異常情況記錄等）：實驗結果報告及實驗總結：教師評語：實訓六利用EXCEL進行統計分析注意：如果在【數據】菜單中沒有見到【數據分析】命令，則要單擊【文件】【選項】【加載項】【轉到】命令，在彈出的對話框中的列表框中，選定【分析工具庫】選項前的復選框．【實訓目的】掌握利用EXCEL求基本統計量；掌握頻數分布直方圖和頻率分布直方圖的做法【實訓內容】一、基本統計量的計算通過調查或觀察，采集到樣本以后，常用一些統計量描述這些數據的分布狀態，并通過這種認識，對數據的總體特征進行總結和歸納。數據的分布狀態常通過數據的進行描寫。描述性統計分析，主要包括集中趨勢和離散趨勢分析，其主要統計量如下：（1）描述集中趨勢的統計◆算術平均值：樣本數據的總和除以樣本數據的個數即是算術平均值。EXCEL調用格式：average(X)，其中X表示一組數據.◆中位數：首先將樣本數據(假設有n個數)按升序或降序排列，如果n為奇數，則數列中間的數值為中位數；如果n為偶數，則中位數為其中兩數值的均值。EXCEL調用格式：median(X)，其中X表示一組數據.◆眾數：樣本數據中出現頻數(次數)最多的那個數稱為眾數。眾數是一組數據分布的峰值，是一種位置的代表，當數據的分布具有明顯的集中趨勢時，尤其對于偏態分布，眾數的代表性比均值好。EXCEL調用格式：mode(X)，其中X表示一組數據.（2）描述離散趨勢的統計量平均指標是一個代表性數值，它反映總體各單位某一數量標志的一般水平，而把總體各單位之間的差異抽象化了。要全面反映一個總體的特征，還必須測定總體各單位之間差異程度。◆極差：樣本數據中最大值與最小值的差值。作為樣本觀測數據離散程度大小的一個簡單度量。EXCEL調用格式：max(X)-min(X)，其中X表示一組數據.◆方差:每個樣本值與全體樣本值的平均數之差的平方和除以數據個數減一.當數據分布比較分散（即數據在平均數附近波動較大）時，各個數據與平均數的差的平方和較大，方差就較大；當數據分布比較集中時，各個數據與平均數的差的平方和較小。因此方差越大，數據的波動越大；方差越小，數據的波動就越小。EXCEL調用格式：var(X)，其中X表示一組數據.◆標準差：樣本方差的算術平方根叫做樣本標準差。樣本標準差越大，樣本數據的波動就越大。EXCEL調用格式：stdev(X)，其中X表示一組數據.（3）

頻數分布直方圖和頻率分布直方圖直方圖是最常用的圖示法，又可以分為頻數分布直方圖和頻率分布直方圖兩種。繪制頻數分布和頻率分布直方圖一般分為以下七個步驟:第一步確定全部數據所在的范圍，找出數據的最大值和最小值；第二步確定組距和組數(一般建議確定5~20組，當樣本容量n較大時可分10~20組，樣本容量較小時可分5~10組)；第三步數據分組；注意：統計落入各組的數據個數，若數據正好落在界線上，則規定該數據歸到下一組。第四步求出各組的頻數（落入各組的數據個數）第五步求出各組的頻率(各組頻數除以數據總個數)第六步列頻數分布和頻率分布表；第七步繪制頻數分布直方圖（縱坐標是頻數/組距）和頻率分布直方圖（縱坐標是頻率/組距）。二、實訓操作實訓1表6-1是1980年-1998年某市城鎮居民年人均可支配收入的數據。表6-1人均可支配收入與人均消費支出數據(單位：元)年份城鎮居民年人均可支配收入年份城鎮居民年人均可支配收入1980526.921990884.211981532.721991903.661982566.811992984.091983591.1819931035.261984699.9619941200.91985744.0619951289.771986851.219961432.931987884.2119971538.971988847.2619981663.631989820.99試求城鎮居民人均可支配收入的平均數、中位數、眾數、極差、方差、標準差、峰度和偏度。操作步驟EXCEL求解步驟：第一步：打開待分析數據的EXCEL表，單擊【數據】菜單下【數據分析】選項，進入“數據分析”對話框，鼠標雙擊“數據分析”中的【描述統計】選項，出現“描述統計”對話框，如圖6-1所示。圖6-1“描述統計”對話框第二步：在“輸入區域”輸入“$B$2:$B$20”，在“輸出選項”中選擇“輸出區域”選項，并輸入“$H$2”(不是唯一的，也可以是其它單元格地址)，同時選中“匯總統計”和“平均數置信度”復選框，如圖6-2所示。圖6-2“描述統計”參數設計第三步：設計完“描述統計”參數后，按【確定】按扭即可，如圖6-3所示。圖6-3“描述統計”計算結果描述統計工具可生成以下統計指標，按從上到下的順序其中包括樣本的平均值、標準誤差、中位數、眾數、樣本標準差、樣本方差、峰度值、偏度值、極差、最小值、最大值、樣本總和、樣本個數和一定顯著水平下總體均值的置信區間。實訓2、繪制員工年薪工資頻率分布直方圖某公司為了了解該公司市場部員工工資的情況，隨機調查了50名市場部的員工的年薪工資，具體的數據如表6-2所示(單位：千元).表6-250名市場部的員工的年薪工資1459514811213212714313413613714016211817014415584102154142145127148165138134165123124124173113104141142138160157138131116178123141138114135151138157試列出這50個數據的頻數直方圖和頻率直方圖.解決方案：第一步：首先確定全部數據所在的范圍，找出數據的最大值和最小值.在B7欄輸入函數“=MAX(A1：J5)”,在B8欄輸入函數“=MIN(A1：J5)”,得到最大值是178，最小值是84.再取兩個特殊值與，使略小于最小值84，略大于178，不妨取，，這樣所有的數據都落入區間內.第二步：確定組數和組距：一般建議確定5~20組，當樣本容量較大時可分10~20組，樣本容量較小時可分5~10組，本題有50個數據，我們作等距分組，分為5組，每組的組距為.第三步：數據分組：在D7-D11欄中分別輸入數組[80,100],[100,120],[120,140],[140,160],[160,180].第四步：列出頻數：統計落入各組限的數據個數，若數據正好落在界線上，則規定該數據歸到下一組，如120就歸到[120,140]組內.現對落在各小組內的數據進行統計，在F8欄中輸入函數“=COUNTIF(A1:J5,＂<100＂)－COUNTIF(A1:J5,＂<80＂)”,得到組限[80,100]中的數據個數是2,依次類推,所得的頻數分布表如圖6-4.第五步：求頻率,即各組頻數除以數據總個數,.第六步：列出頻率分布：在G8欄中輸入函數“=F8/50”得到組限[80,100]中的數據頻率為0.04,將鼠標置于G8欄右下角，按住“＋”型符號往下拖,直至G12欄，所得的頻率分布表如圖6-4所示.圖6-4頻數、頻率分布表第七步：繪制頻數分布直方圖和頻率分布直方圖.選定E8:E12的“頻數/組距”的數據，點擊【插入】菜單下【柱形圖】，然后【選擇數據】修改【水平（分類）軸標簽】，然后點擊【插入】菜單下【文本框】添加豎直軸“頻數/組距”，可得到頻數分布條形圖，類似可得到頻率分布條形圖如圖6-5所示。在頻數分布條形圖中點擊【設置數據系列格式】，把分類間距調到0%，并設置邊框顏色，即可得到頻數直方圖，類似可得到頻率分布直方圖，如圖6-6所示。圖6-5頻數分布直方圖圖6-6頻率分布直方圖【實訓練習】一名射擊運動員在在十米氣步槍決賽中的成績(環數)為10.6,10.6,9.3,10.7,10.4,10.4,10.8,9.2,10.4,10.0.試以這些數據作為一個樣本，使用Excel計算平均值、眾數、中位數、極差、方差和標準差.(10)2.某地舉行了一次語文、數學、外語三科競賽，表6-3是某校的競賽成績：運用所學的知識，將表格填充完整，并畫出頻數直方圖和頻率直方圖.(40)表6-3三門學科競賽總成績統計表分數段頻數頻率280~300分0.1260~279分7240~259分10220~239分9200~219分8180~199分70~179分合計503.某班級50名同學的經濟數學成績如表6-4所示（：分）表6-4經濟數學成績表55767269667257867192828082100697364745868667755677675755365795067757573956669796470787267725888706173（1）試求該班經濟數學成績的平均數、中位數、眾數、極差、標準差和偏度；(10)（2）畫出頻數直方圖和頻率直方圖；(30)（3）及格率是多少（分數≥60分）?優秀率是多少（分數≥90分）？(10)實驗報告實驗序號：06日期：_____年_____月_____日班級姓名學號實驗名稱問題背景簡述：實驗目的：實驗原理與使用軟件實驗過程記錄（含：基本步驟、程序的文件名及異常情況記錄等）：實驗結果報告及實驗總結：教師評語：實訓七利用EXCEL進行統計分析注意：如果在【數據】菜單中沒有見到【數據分析】命令，則要單擊【文件】【選項】【加載項】【轉到】命令，在彈出的對話框中的列表框中，選定【分析工具庫】選項前的復選框．【實訓目的】掌握利用EXCEL進行一元線性回歸分析；掌握利用EXCEL進行簡單的時間序列分析。【實訓內容】一、線性回歸分析步驟：1.確定自變量、因變量；2.繪制散點圖或求相關系數（用CORREL函數或數據分析中的“相關系數”）3.應用數據分析工具進行回歸分析或應用趨勢線進行回歸分析4.利用回歸分析進行預測。設因變量為，其預測值為，則的預測區間為（置信度為95%）其中二、時間序列趨勢分析1.簡單平均法根據過去已有的t期觀察值，通過簡單平均來預測下一期數值的一種預測方法，稱為簡單平均法.設時間序列已有的期觀察值為，則第期的預測值為,簡單平均法計算簡單，只適合對波動不大的客觀現象使用.2.移動平均法通過對時間序列逐期遞推移動求得平均數作為趨勢值或預測值的一種預測方法，稱為移動平均法(movingaverage)，簡單移動平均是將最近的期數據加以平均，作為下一期的預測值.第期的簡單移動平均預測值為3.指數平滑法指數平滑法(exponentialsmarthing)是對過去的觀察值加權平均進行預測的一種方法，該方法使得第期的預測值等于第期的實際觀察值與第期預測值的加權平均值.其預測模型為，為第期的實際觀察值，為第期的預測值，為平滑系數，為阻尼系數.一般來說,如果數據波動較大,值應取大一些,可以增加近期數據對預測結果的影響；如果數據波動平穩,值應取小一些.【實訓操作】實訓1已有研究表明,一種股票的收益率同市場(market)的收益率相關.表7-1中給出了2004年6月的22個交易日中長江電力(代碼600900)的收益率和整個市場的收益率數據,試根據數據得到長江電力收益率關于市場收益率的線性方程,并給出線性擬合圖.表7-12004年6月的22個交易日中長江電力的收益率與市場的收益率數據日期market600900日期market600900200406010.0171490.018994200406160.0048210.0069220040602-0.0062-0.0121820040617-0.02063-0.0183320040603-0.01647-0.0067320040618-0.00931-0.02917200406040.00009-0.00113200406210.008833-0.001220040607-0.01474-0.01017200406220.0108410.02286420040608-0.01237-0.0114220040623-0.008960.00235320040609-0.023170.00230920040624-0.00772-0.0023520040610-0.00185-0.0080720040625-0.02208-0.02235200406110.0022380.00464620040628-0.016770.00601720040614-0.02503-0.01619200406290.0126020.021531200406150.0182780.01880120040630-0.00972-0.00703解決方案:根據表7-1所提供的數據,按照統計及回歸分析的有關理論知識,建立長江電力收益率受市場收益率影響的線性回歸方程.可通過利用Excel提供的數據分析工具或趨勢線得到回歸方程.1.應用數據分析工具進行回歸分析第一步:新建工作表,輸入表頭“應用回歸分析工具進行回歸分析”,輸入表7-1中的22個交易日的日期及收益率數據.第二步:單擊【數據】→【數據分析】,在出現的【數據分析】對話框中選擇“回歸”,如圖7-1所示,單擊【確定】.圖7-1【數據分析】對話框第三步:在出現的【回歸】對話框中,單擊“Y值輸入區域”后的折疊按鈕,選擇C3∶C25單元格;單擊“X值輸入區域”后的折疊按鈕,選擇B3∶B25單元格.選中“標志”復選框、“新工作表組”單選框和“線性擬合圖”復選框,如圖7-2所示,點擊【確定】.圖7-2【回歸】對話框第四步:得到回歸分析結果的匯總輸出(SUMMARYOUTPUT),如圖7-3所示.圖7-3回歸分析結果匯總輸出結果分析：結果可以分為四個部分第一部分是回歸統計的結果，包括相關系數、決定系數、調整之后的相關系數、回歸標準誤差以及樣本觀測值個數。第二部分是方差分析的結果，包括可解釋的自由度、離差、殘差和它們總離差的以及由此計算出的F統計量和相應的顯著水平。第三部分是回歸方程的截距和斜率的估計值以及它們的估計標準誤差、t統計量大小雙邊拖尾概率值、以及估計值的上下界。從圖7-3的回歸匯總輸出可以看出,對應的回歸方程為長江電力收益率=0.0024+0.7923×市場收益率..第四部分是樣本散點圖，其中藍色的點是樣本的真實散點圖，紅色的點是根據回歸方程進行樣本歷史模擬的散點。如果覺得散點圖不夠清晰可以用鼠標拖動圖形的邊界達到控制圖形大小的目的。第五步:得到回歸分析結果中的線性擬合圖(LineFitPlot),如圖7-4所示.圖7-4線性擬合圖2.應用趨勢線進行回歸分析第一步：新建工作表，輸入表頭“應用散點圖和趨勢線進行回歸分析”，輸入表7-1中22個交易日的日期及收益率數據；第二步:選擇B4∶C25單元格數據，單擊【插入】→【散點圖】.第三步:在得到的散點圖中選中網格線，右鍵選擇“刪除”即可刪除網格線.第三步:點擊散點圖，單擊【布局】→【坐標軸標題】→【主要橫坐標標題】→【坐標軸下方標題】，在出現的文本框中輸入“市場收益率”.然后點擊【坐標軸標題】→【主要縱坐標標題】→【豎排標題】，在出現的文本框中輸入“長江電力收益率”.生成的散點圖如圖7-5所示.圖7-5長江電力收益率同市場收益率散點圖第四步:右擊散點圖中的藍色散點,選擇“添加趨勢線”,在“趨勢預測/回歸分析類型”選項區域中單擊“線性”選項圖標.選中【顯示公式】復選框,單擊【關閉】按鈕.最終回歸分析結果如圖7-5所示.圖7-5回歸分析函數輸出圖從圖7-5中可以看出,長江電力收益率同市場收益率的回歸方程為:y=0.7923x+0.0024.其中,x表示市場收益率,y表示長江電力收益率.實訓2表7-2給出了我國1990-2004年人均國內生產總值(GDP)和居民消費價格指數的時間序列，要求用3期移動平均預測2005年的居民消費價格指數以及平滑系數為0.7預測2005年的居民消費價格指數).表7-2人均國內生產總值(GDP)和居民消費價格指數的時間序列年份人均GDP(元)居民消費價格指數(%)(上年=100)年份人均GDP(元)居民消費價格指數(%)(上年=100)19901634103.11998630899.219911879103.41999655198.619922287106.420007086100.419932939114.720017651100.719943923124.12002821499.219954854117.120039111101.219965576108.3200410561103.919976054102.8注：資料來源—國家統計局網站1.問題分析:為判斷這兩個數列的變化形態及隨時間的變化趨勢，下面給出了2個序列的圖形.從圖7-6及圖7-7可以看出，人均GDP序列呈現一定的線性趨勢，居民消費價格指數序列圖則沒有任何趨勢，呈現出一定的隨機波動.通過圖形的觀察和分析有助于作進一步的描述，并為選擇預測模型提供基本依據.圖7-6人均GDP序列圖圖7-7居民消費價格指數序列圖2.解決方案:=1\*GB2⑴用移動平均法預測居民消費價格指數第一步：單擊【數據】→【數據分析】，在出現的“數據分析”對話框中選擇“移動平均”，單擊【確定】按鈕.這時彈出移動平均對話框，如圖7-8.圖7-8移動平均對話框第二步:在“輸入區域”框中指定統計數據所在區域為B1∶B16選中“標志位于第一行”復選框,在“間隔”框內輸入移動平均的項數為3,在“輸出區域”框中指定輸出數據所在區域為C2∶C16,選中“圖表輸出”復選框,如圖7-8所示.第三步：單擊【確定】，得計算結果及實際值與移動平均值的曲線圖如圖7-9所示.圖7-9移動平均的計算結果及實際值與移動平均值的曲線圖從圖7-9可以看出，2005年居民消費價格指數的預測值為101.4333.=2\*GB2⑵用指數平滑法預測居民消費價格指數第一步：單擊【數據】→【數據分析】，在出現的“數據分析”對話框中選擇“指數平滑”，單擊【確定】按鈕.這時出現指數平滑對話框，如圖7-10.圖7-10指數平滑對話框第二步：在輸入框中指定輸入參數，如圖7-10所示.第三步：單擊【確定】，得一次指數平滑值，如圖7-11所示圖7-11指數平滑結果輸出圖從圖7-11可以看出，2005年居民消費價格指數的預測值為102.9455=3\*GB2⑶利用線性趨勢方程來預測人均GDP第一步：單擊【數據】→【數據分析】，在出現的“數據分析”對話框中選擇“回歸”，單擊【確定】按鈕.這時出現回歸對話框，如圖7-12所示.圖7-12回歸工具對話框第二步：在輸入及輸出區域填入相應的值，如圖7-12所示；得到的輸出結果如表7-3、7-4和圖7-13所示.表7-3人均GDP線性趨勢預測結果年份t人均GDP(元)預測人均GDP(元)殘差標準殘差1990年116341442.39191.610.511991年218792042.32-163.32-0.431992年322872642.24-355.24-0.941993年429393242.17-303.17-0.801994年539233842.0980.910.211995年648544442.02411.981.091996年755765041.94534.061.411997年860545641.87412.131.091998年963086241.7966.210.181999年1065516841.72-290.72-0.772000年1170867441.64-355.64-0.942001年1276518041.57-390.57-1.032002年1382148641.49-427.49-1.132003年1491119241.42-130.42-0.35200434719.661.90表7-4人均GDP線性回歸計算結果Coefficients標準誤差tStatP-valueLower95%Upper95%下限95.0%上限95.0%Intercept842.47213.043.950.00382.221302.71382.221302.71t599.9323.4325.600.00549.31650.54549.31650.54圖7-13人均GDP線性擬合圖所以，線性趨勢回歸方程為：，將代入線性趨勢回歸方程可得2005年人均GDP的預測值，即（元）..【實訓練習】1．一家電氣銷售公司的管理人員認為，每月的銷售額是廣告費用的函數，并想通過廣告費用對月銷售額做出估計。表7-4是近8個月的銷售額與廣告費用數據：表7-4近8個月的銷售額與廣告費用數據電視廣告費用/萬元報紙廣告費用/萬元月銷售收入y/萬元51.596229041.5952.52.59233.3953.52.3942.54.29432.594任務1：請利用Excel求月銷售收入的眾數、中位數、平均數.任務2：請利用Excel求電視廣告費用的極差.任務3：請利用Excel求電視廣告費用、報紙廣告費用和月銷售收入的方差、標準差.任務4：請利用3期移動平均法預測下個月的月銷售收入為多少萬元？任務5：采用指數平滑法，分別用阻尼系數α=0.3和α=0.5預測各月的月銷售收入，分析預測誤差，說明用哪一個平滑系數預測更合適？任務6：請利用Excel繪制繪制月銷售收入關于電視廣告費用的散點圖任務7:請分別利用Excel數據分析工具箱中的相關系數功能和CORREL函數計算月銷售收入與電視廣告費用的相關系數.任務8:請利用Excel進行回歸分析，建立月銷售額關于電視廣告費的回歸方程，并預測電視廣告費用為3.6萬元的月銷售收入。任務9:建立月銷售額關于電視廣告費用和報紙廣告費用的回歸方程。并預測電視廣告費用和報紙廣告費用分別為3.6萬元和4.5萬元時，估計月銷售額。任務10：撰寫實訓1~實訓9的實訓報告.數學實驗報告實驗序號：07日期：_____年_____月_____日班級姓名學號實驗名稱問題背景簡述：實驗目的：實驗原理與使用軟件實驗過程記錄（含：基本步驟、程序的文件名及異常情況記錄等）：實驗結果報告及實驗總結：教師評語：實訓八使用Excel討論概率計算問題【實訓目的】熟悉超幾何分布的概率計算；掌握使用Excel計算二項分布、正態分布的概率以及累積概率.【實訓內容】1.超幾何分布HYPGEOMDIST函數：給定樣本容量、樣本總體容量和樣本總體中成功的次數，HYPGEOMDIST函數返回樣本取得給定成功次數的概率.其語法為：HYPGEOMDIST(sample_s,number_sample,population_s,number_population)，其中sample_s為樣本中成功的次數，number_sample為樣本容量，population_s為樣本總體中成功的次數，number_population為樣本總體的容量.2.二項分布BINOMDIST函數：返回二項式分布的概率值.其語法為：BINOMDIST(number_s,trials,probability_s,cumulative)其中為number_s為試驗成功的次數，trials為獨立試驗的次數，probability_s為每次試臉中成功的概率，cumulative取0或1..3.正態分布NORMDIST函數：返回正態分布的概率值.其語法為：NORMDIST(x，mean，standard_dev,cumulative）其中x為需要計算其分布的數值，mean為正態分布的算術平均值，standard_dev為正態分布的標準偏差，cumulative取0或1注意：cumulative取0返回概率密度函數，cumulative取1返回累積分布函數若，則X對應的概率問題一般可借助EXCEL的NORM.DIST函數求解，基本使用格式為：；；4.NORMINV函數：返回指定平均值和標準差的正態累積分布的反函數.其語法為:NORMINV（probability，mean，standard_dev）其中probability為正態分布的概率值，mean為正態分布的算術平均值，standard_dev為正態分布的標準偏差.【實訓操作】一、彩票中獎概率問題典型問題1某地發行福利彩票，每張彩票的號碼是7個數字的無序數組，開獎時，用一個搖獎機，里面裝有分別寫上01,02,…，35的35個小球.充分攪拌這些小球一分鐘，從出口處掉出一個小球，記下小球上的數字.搖出的小球不放回搖獎機中，重復剛才的做法，一直到產生一個7個數字的無序數組，記作a，設有一、二、三等獎.規定：彩票號碼與a完全一樣時，得一等獎；彩票號碼與a有6個數字一樣時，得二等獎；有5個數字一樣時，得三等獎.試問：買一張彩票，中一、二、三等獎的概率各是多少?1.問題分析根據題意，將問題轉化為一個袋子中有35個彩球，其中紅球7個，白球28個，每次隨機的取出一只，第一次取到的球不放回袋中，第二次從剩余的球中再取一球，共取7次，求取到7球中全是紅球、有6個紅球和有5個紅球的概率.經過轉換，問題變為無放回的隨機抽樣(超幾何分布)，根據其概率分布（詳見本章第三節）即可計算出相應的概率值.2.解決方案利用Excel中的超幾何分布函數(HYPGEOMDIST函數)可計算出相應參數下超幾何分布的概率，具體求解步驟如下.第一步：新建一個工作表，輸入表頭“應用超幾何分布函數HYPGEOMDIST求概率”.第二步：分別單擊C2、E2、C3和E3單元格，輸入已知參數:N=35，M=7，n=7，x=8.第三步：運用HYPGEOMDIST求7個球中全為紅球的概率，在B5單元格輸入“=HYPGEOMDIST(E3，C3，E2，C2)”，結果如圖8-1所示.圖8-1應用超幾何分布求概率利用相同的原理可求得x=6及x=5的概率值.第一步：新建Excel工作表，輸入“超幾何分布函數概率分布圖”.第二步：分別單擊C2、E2和C3單元格，輸入己知參數N=35，M=7，n=8.第三步：設定樣本中中獎的號碼個數x序列.在B6—B13單元格輸入x為0，1，…，7的取值.第四步：求不同的x對應的概率.單擊C6單元格，輸入"=HYPGEOMDIST（B6，$C$3,$E$2，$C$2）”，再次單擊C6單元格，將鼠標至于C6單元格右下角，當光標變為小黑十字時拖曳