1、數(shù)據(jù)分析實驗報告20 15- 20 16學(xué)年 第一學(xué)期班 級：學(xué) 號：姓 名：授課教師：況湘玲 實驗教師：況湘玲實驗一 網(wǎng)上書店數(shù)據(jù)庫的創(chuàng)建及其查詢實驗類型：驗證性實驗學(xué)時：2實驗?zāi)康模豪斫鈹?shù)據(jù)庫的概念；理解關(guān)系（二維表）的概念以及關(guān)系數(shù)據(jù)庫中數(shù)據(jù)的組織方式；了解數(shù)據(jù)庫創(chuàng)建方法。實驗步驟：步驟1 ：選擇“ bookstore數(shù)據(jù)源，進入 查詢設(shè)計窗口。步驟2:選擇查詢中需要使用的表。在添加表”對話框的 表”列表中分別選擇 書”和出版社”表，并單擊 添加”按鈕將它們添加至表窗 格。酔Microsoft Query- a文件 請京血 規(guī)豊M 唱式CO 去雖用O iSSCR ffla（W）I樹法舊I

2、財 畫 囲鬲is 冋可 因Tin it何1阿七査向來自bookitore二 已丨23 |書庫存更書容伯: 或.L5BB庫冬塑|芳呂0-0701160-7-5374Ax a Second. Latn0-OT0I66O-9-B2DD-&.1 A.bas IxpArtE91 G&-0701639-1-0122D oil a. Al ni stral a onCHTFOEatn-fi-g22ETal百屯a LagiTi Scrip11-0703400-0130Th.e Handjbook o叫*1記錄IMM選走丈坤捋數(shù)IS返回11:0ft EmcqI艮!以將SHS傳輸?shù)疥帒粜钁?yīng)用程匸11-r NUM1

3、L查詢設(shè)計4低價圖書信息查詢 步驟1 :選擇數(shù)據(jù)源并添加表。選擇“bookstore數(shù)據(jù)源，進入 查詢設(shè)計窗口。在 添加表對話框中選擇 書表，將其添加到 表窗格中。步驟2 :選擇字段。在 查詢設(shè)計”窗口的 表”窗格中，雙擊 書”表的 書名” 出版年份”和 單價”字段。步驟3:設(shè)置查詢條件，顯示查詢結(jié)果。在 條件”窗格的 條件字段”行的第一列中選擇 單價”，并在下一行中輸入“10iaflScfij tft O rwiiff 毗1“枷擊IN口1可*1麗Ll 麗鬲 阪y-r可 UTITFI 口 (R) Sn(W) W&*i(H) 畫田陽兩涮鬲陽岡可因囿rn両畫實驗1-3響當(dāng)當(dāng)”網(wǎng)上書店會員分布和圖書

4、銷售信息查詢實驗?zāi)康??掌握復(fù)雜的數(shù)據(jù)查詢方法：多表查詢、計算字段和匯總查詢 步驟1：選擇“bookstore數(shù)據(jù)源并添加 會員”表。步驟2:選擇分類字段、匯總字段和匯總方式。本查詢的分類字段是會員的城市，匯總字段是會員號，匯總方式是計數(shù)。在查詢設(shè)計會員號”字段的列標(biāo)，在編窗口的 表”窗格中，雙擊 會員”表的 城市”和 會員號”字段。然后雙擊輯列”對話框中輸入列標(biāo) 會員人數(shù)”并選擇匯總方式：計數(shù)”歹IU3 too kstoreA.i上海4L*-I-冉許器器需焉IOM.需二氏常| G 兀 3 $I !*!iA左-+w.CE3” 忻曲加件 E -垃丄步驟3 :設(shè)置查詢條件。若僅僅想了解上海和北京的

5、會員人數(shù)，可以在條件窗格中設(shè)置相應(yīng)的條件,Microsoft Query査詢來自bookstore4= 號電臨 市址U系苕甌 3E地亠If聯(lián)益St4*4.019.95007Q.80004013.015.022.9500II4.T5QO12094.017 023 m39LOOOO8005.07.029.00209.5004QI2.G3 0珂 9500叭聊QD2006.06.036.0000216.0000B0IS.Q14 03S.Z500507.50006015.01.O37.0MI592.0000n1勺n購 oraTi町口琳門門Tmu記錄巾LLku4 I廠選走“砸”菜單的“殺件”.以顯示戲雕挙

6、件。步驟3 :設(shè)置查詢條件。在條件”窗格的條件字段”行的第一列中選擇 訂購日期”并在下一行中輸入=20057-1 and =5Ln (W20DB/1/23*. *2(V庫存麵比庫曰明 訂準(zhǔn)號 訂單狀恵 訂剛曰期作看號弋I刻馳姓左.丨訂購軌里丨訂甌曰期N Shwwfcis.C. S2006-01-23 00:00.00Tvllin, Fvul弓Dd?-04.-J. 胡：血0： 口口hzi記錄 i rT雄文護棚枚磁回IhmqFt Exits”亂捋粘誦件輔到用戶端應(yīng)用程序沖INU通過匯總發(fā)現(xiàn)近兩年 shammas,Namir c.和wellin, paul的書最暢銷。實驗二企業(yè)銷售數(shù)據(jù)的分類匯總分析

7、實驗類型：驗證性 實驗學(xué)時：2實驗?zāi)康模豪斫鈹?shù)據(jù)分類匯總在企業(yè)中的作用與意義；掌握數(shù)據(jù)透視表工具的基本分類匯總功能；掌握建立分類匯總數(shù)據(jù)排行榜、生成時間序列、繪制pareto曲線圖、計算各地區(qū)客戶分布、統(tǒng)計各地區(qū)客戶的平均銷售額和大宗銷售時間序列的方法和步驟。實驗步驟：步驟I :獲取各客戶每筆銷售的銷售額、銷售產(chǎn)品的類別和時間。在一張空自的工作表中，按下Alt+d+p鍵后啟動“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А贝翱冢x擇“外部數(shù)據(jù)源”選項單擊“獲取數(shù)據(jù)”按鈕，隨后啟動了Microsoft query，選擇所建立的連接到Northwi nd.mdb數(shù)據(jù)庫的ODB(數(shù)據(jù)源-“nW,并選中“選擇數(shù)據(jù)源”窗

8、口下方“使用查詢向 導(dǎo)創(chuàng)建/編輯查詢”選項，單擊“確定”按鈕，選擇“客戶”表中的“公司名稱”、“訂單”表中的“訂購日期”以及“類別”表中“類別名稱”字段，隨后query彈出窗口顯示“查詢向?qū)o法繼續(xù)，因為該表格無法鏈接到您的查詢中。您必須在Microsoft query中的表格之間拖動字段， 人工鏈接。”這是因為“類別”表無法同“訂單”表建立聯(lián)系。單擊“確定”按鈕。要查詢銷售額，需要在 query中首先增加“訂單明細(xì)”表，利用其中的“單價”、“數(shù)量”與“折扣”字段中的數(shù)據(jù)，計算銷售額。在“數(shù)據(jù)”窗格中一個空白字段的名稱處輸人公式“訂單明細(xì)單價*數(shù)量*（1-折扣）”，按回車鍵后就可以計算出銷售額

9、。隨后，將“產(chǎn)品”表也添加到查詢中，雖然查 詢結(jié)果中并不包括任何“產(chǎn)品”表的字段，但是該表能夠建立“類別”表與“訂單明細(xì)”表之間的 聯(lián)系（“訂單明細(xì)”表指明所訂購產(chǎn)品的ID，“產(chǎn)品”表指明該產(chǎn)品屬于哪一個類別）。此時，查詢中的表都建立了正確的聯(lián)系，并在查詢結(jié)果中包括了匯總所需要的數(shù)據(jù)，從而也建立“類別”表與“訂單”表之間的聯(lián)系。將計算銷售額的字段的列標(biāo)題命名為“銷售額”。選擇query菜單中的“文件” t “將數(shù)據(jù)返回 Microsoft Office Excel ”命令，此時query已經(jīng)關(guān)閉，操作對象回到了 Excel ,單擊“下一步”按鈕，指定位置在“現(xiàn)有工作表”，單元格A3,單擊“完成

10、”按鈕。1U戶TJ翠曰目J*S-teilWrUMM* Bfra.1*fn鉀訂咽曰期TWl盤掩FlMfl罔.埒華位嵌ismF砂再巧LW!fli扌口可網(wǎng),t103 ?e-31 44 ME曰3 LP. nvsI 2口口3*0 環(huán).i口曰 amriiTttinB Mh-i-H-BWMnrarjnfi i 龍OSDPPDT 1工三丸 e卩審京Rwr i 叫=fei 3et(F WMC 3 WOJHIC VS11SflE 車iB 血 R i as O CW3 的 H、naiwi日阿i佔廖i Fi u wi |聲川jui i a爭仁整F 正悲衣11 vyti-LHd3u OD LIU1 W&-O9- 30

11、OO OO牆人孫打社iwa-1 in7 口口 口口1&9S-1 i-H OO OO i 歸譏nrb qq OO1MT-O1 口言 口口 Cl 口汕譽設(shè)lMT-tli-1 OO OO UMT-13IT - oq立丸ESI升+fr企業(yè)ma蜩皺切良仝価urFTjqnu 白白 uu1 MT-0i-O& OO OO1&3TDfi=3 口口 口忌曲血機1WT-07-07 OO OOI MT-O7-OD OO1!T DT- ES- 口口 口口ti-inn rtn1 TKir. rm E-n ci口 口口:性儘北毎曲 片濟1牛“ ”尼丄莊云齊$錄I牛W*U V MHf 匕!=卿 H M15i .03HUM步驟

12、2:匯總客戶銷售額排行榜，并排序。首先將“訂購日期”拖至行標(biāo)簽，將“銷售額”拖至數(shù)值，“類別名稱”拖至列標(biāo)簽，為了能將銷售額按照年度匯總，將光標(biāo)停留在“行標(biāo)簽”下方的任何單元格，右擊鼠標(biāo)，選擇“創(chuàng)建組（G）, ”命令，選擇組合的步長為“年”。然后將行標(biāo)簽的字段名稱 “訂購日期”改為“訂購年”，將它拉至報表篩選域， 將字段列表中的“公 司名稱”字段拖到行標(biāo)簽處，讓透視表按照行總計，從大到小排列，就得到了圖表。2匯總前三大客戶各月銷售額，并繪制圖形步驟1 :將實驗要求1所匯總的數(shù)據(jù)透視表復(fù)制到新的工作表。步驟2:利用數(shù)據(jù)透視表，匯總前三大客戶的銷售額時間序列。按照實驗要求1匯總的數(shù)據(jù)透視表，反映出

13、“高上補習(xí)班”、“正人資源”、“大鈺貿(mào)易”是公司的前三大客戶。點開“行標(biāo)簽”字段，勾選中這3個公司名稱，并拖到列標(biāo)簽。將列標(biāo)簽的字段“類別名稱”拖出數(shù)據(jù)透視表。將報表篩選中的字段“訂購年”拖到到行標(biāo)簽，將其重新組合。選擇組合量x（ 1-折扣）等字段，將所查詢數(shù)據(jù)返回Excel。的步長為“月”和“年”，把字段名稱修改為“訂購年”與“訂購月”。光標(biāo)停留在數(shù)據(jù)表中任何單元格，右擊鼠標(biāo)，選擇“數(shù)據(jù)透視表選項（0）, ”，打開后“布局和格式”選項卡中的“對于空單元格，顯示”填寫為“ 0”，此時得到的前三大客戶銷售額時間序列。O34BS-G796. BS99929月1B2. 4月O1丄月4971, O39

14、S977OBS-O 4：99Ob砂年月月冃月月月月月0J171245678-91 93157999434SS. 79994.OS796-659992OD1S2_ 4.O5275- 71496 5S275- 71496S904.91412035= 5445079973062. 34999Tioo33a11月詐月1S14b 8：3S49. 659iS4 921B它舍3舍Y3 15248. 97497O3119合9998日 0 4464 599997 15913 6754529 S2247. 09997B4S7. 7149S1172 8iS23x 45 0 550. 58796147255510 S

15、92461 1536 799994 3436 443498O13453P 67499 SS42-各合4自昌企 4707. 54：0 2944 399989 14333生 11S5 749997 9G281 12693 7499 5273lO2S2a 51498 12584.23252. 2897 15248 7497 3494.987465 2178 39999 6696. 342458 15629 49999 32043 86349 9907. S15亠._旦宇_.旦呈迪丄丁述 髙丁X學(xué)師突永五華椅盍苗通牢祥兩遠廣%Sr71%1,12-24lT.OO41.125.24%22841-1%S.f

16、i%33J3%L1%&7憫35.82%7.938.26%P.O勺缶40.60*44245.04*412.441.1%13.5*49184.1.1%Sl.1541I15.7%53.0516.9%54.9018.0Sfi.7341-1%H%58.5214%20.2i60,17吉戶百分晉尸齣察稍宮額孚 比計臣好比汁百分比選中單元格F3G92中的數(shù)據(jù)，單擊“插入”選項卡t選擇“散點圖” t選擇“帶平滑線的散點圖”， 單擊該折線圖，點擊“布局”選項卡中的“網(wǎng)格線”按鈕，點擊“主要縱網(wǎng)格線”中的“主要網(wǎng)格 線”按鈕，顯示縱向的橫坐標(biāo)網(wǎng)格線。步驟4:在曲線上添加代表 20%客戶數(shù)的垂直參考線。在單元格15:

17、17中輸入“ 20%,在單元格J5與J7中輸入“ 0”和“ 120%,在單元格J6中輸入公 式：“ =INDEX(G4:G92, MATCH(I5,F4:F92,1),1 )”，即從客戶累計百分比中，查找到 20%的客戶數(shù) 在第幾行，然后用INDEX函數(shù)查找該行對應(yīng)的銷售額累計百分比，計算結(jié)果如圖 2-15所示。選中“圖表工具”選項卡組中的“設(shè)計”選項卡，點擊“選擇數(shù)據(jù)”按鈕打開“選擇數(shù)據(jù)源”窗口，選中該窗口中的“添加”按鈕，打開“編輯數(shù)據(jù)系列”窗口，在“系列名稱”中輸入散點圖名稱為 “20% 垂直參考線”，點擊“ X軸系列值”后的拾取器，選中I5至I7的區(qū)域，點擊“ Y軸系列值”后的拾取器，

18、選中J5至J7的區(qū)域，返回并點擊“確定”按鈕，回到“選擇數(shù)據(jù)源”窗口，點擊“確定” 按鈕。即在前面所繪制的圖表上，添加了一條垂直參考線，也就是在源數(shù)據(jù)中添加了一個系列，這 個系列散點圖線的 X軸數(shù)據(jù)來自單元格I5:I7 , Y軸數(shù)據(jù)來自單元格 J5:J7，得到Pareto曲線。一誚客踴第訝旨甘比20K020常O.5S52352205$120%繪制按照訂單匯總的銷售額與銷售次數(shù)Pareto曲線步驟1 :查詢“訂購日期”、“訂單ID ”與“銷售額”等數(shù)據(jù)。在一張空自的工作表中，按下Alt+d+p鍵后啟動“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А贝翱冢x擇“外部數(shù)據(jù)源”選項單擊“獲取數(shù)據(jù)”按鈕，利用Micros

19、oft query ，從“訂單”表、“訂單明細(xì)”表中查詢“訂購日期”、“訂單ID”與“銷售額”（銷售額=訂單明細(xì).單價X數(shù)量x（ 1-折扣）等字 段，將查詢數(shù)據(jù)返回 Excel。步驟2:利用查詢的數(shù)據(jù)，制作數(shù)據(jù)透視表。從數(shù)據(jù)透視表的字段列表中，選擇“訂購日期”字段，并將其拖至行標(biāo)簽，將“銷售額”字段拖至“數(shù)值“區(qū)域。將“訂購日期”字段按年組合，拖至“報表篩選”區(qū)域，將“訂單ID ”字段拖至行標(biāo)簽，在“求和項：銷售額”欄下任意位置單擊右鍵打開并設(shè)置按銷售額降序排列，得到按照年度和訂單ID匯總的數(shù)據(jù)透視表。AaLrU1訂購曰朗23f亍標(biāo)整J求和峻：銷售薇iloseslfi3B7a 4999951Q

20、9SJ1581031103012615. 057108891133031041711188, 4310S17109S2. S449S010S971035. 2411047910495. 621054010191, 731069110164. 84105159921 2999735103729210. 96 .104249194. 5599667110328902, 5e105148623- 45H1co cnrccu q步驟3:利用數(shù)據(jù)透視表的數(shù)據(jù)，計算客戶數(shù)累計百分比與銷售額累計百分比，繪制Pareto曲線。在單元格E3G3中依次輸人說明文字：“銷售次數(shù)百分比”、“銷售次數(shù)累計百分比”、“銷

21、售額累計 百分比”。按照圖2-17所示輸人公式，并復(fù)制到下方單元格至G833,設(shè)置E、F、G三列數(shù)值的顯示形式為百分比，3位小數(shù)，單元格E4:E833中計算單次銷售占總銷售次數(shù)（即訂單數(shù)）的百分比，單元格 F4:F833中匯總累計銷售次數(shù)占總銷售次數(shù)的百分比，即到該訂單為止，已有訂單數(shù)占到總訂單數(shù)的百分比。單元格G4:G833中匯總到該訂單為止，已有訂單實現(xiàn)的銷售額占總銷售額的百分比In E4:=1/COUNT($A$4: $A$833)In F4:=SUM($E$4:E4)In G4:=SUM($B$4:B4)/SUM($B$4:$B$833)百科*匕l(fā)-l百:幻”匕蘆HL匕即LJLJZU呼

22、如O .衛(wèi):ZQ號張O.2%4.4%5.32:3ri.l S：fl脾鼻7_B73IKJINOWbJL.NO如T -斗*3 7工!&%1丄Q.JLN打陰dm%毗 1I選中單元格F3G833中的數(shù)據(jù)，繪制“帶平滑線的散點圖”，并添加縱向網(wǎng)格線，設(shè)置橫坐標(biāo)小數(shù)位數(shù)為0位步驟4:在曲線上添加代表 20喘肖售次數(shù)的垂直參考線。在單元格15:17中輸入“ 20%,在單元格J5與J7中輸入“ 0”和“ 120%,在單元格J6中輸入公式：“=INDEX(G4:G833,MATCH(I5,F4:F833,1),1)”，即從銷售次數(shù)累計百分比中，查找20%勺銷售次數(shù)在第幾行，用INDEX函數(shù)查找，該行對應(yīng)的銷售額

23、累計百分比。在前面所繪制的圖表上，添加一條垂直參考線。該參考線的X軸數(shù)據(jù)來自單元格I5:I7 , Y軸數(shù)據(jù)來自單元格 J5:J7 ,20%020%55略20%120%匯總各地區(qū)客戶分布步驟1 :查詢“公司名稱”與“地區(qū)”等數(shù)據(jù)。將Excel 一張空白工作表命名為“ 5.各地區(qū)客戶分布”，按下Alt+d+p鍵后啟動“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А贝翱冢x擇“外部數(shù)據(jù)源”選項單擊“獲取數(shù)據(jù)”按鈕，利用Microsoft query ,Excel。從“客戶”表中查詢“公司名稱”與“地區(qū)”字段，然后將所查詢的數(shù)據(jù)返回 步驟2:利用查詢的數(shù)據(jù)，制作數(shù)據(jù)透視表。從數(shù)據(jù)透視表的字段列表中，選擇“地區(qū)”字段，拖

24、至行標(biāo)簽。選擇“公司名稱”字段，拖至“數(shù)值”區(qū)域，得到按照地區(qū)匯總的客戶數(shù)的數(shù)據(jù)透視表，步驟3:利用數(shù)據(jù)透視表的數(shù)據(jù)，制作數(shù)據(jù)透視圖。光標(biāo)停留在數(shù)據(jù)透視表中，點擊“數(shù)據(jù)透視表工具”選項卡組中的“數(shù)據(jù)透視圖”命令，選擇“簇 狀柱形圖”，在當(dāng)前工作表中建立數(shù)據(jù)透視圖。匯總45繪制各地區(qū)平均銷售額及銷售額占總銷售額百分比 步驟1 :查詢“地區(qū)”與“銷售額”等數(shù)據(jù)。在Excel的空白工作表中，按下Alt+d+p鍵后啟動“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)А贝翱冢x擇“外部數(shù)據(jù)源”選項單擊“獲取數(shù)據(jù)”按鈕，利用Microsoft query，從“客戶”和“訂單明細(xì)”表中，查詢客戶的“地區(qū)”與“銷售額”（銷售額=

25、訂單明細(xì)單價X數(shù)量X（ 1-折扣）字段，將查詢數(shù)據(jù)返回Excel。查詢時應(yīng)包括“訂單”表，該表能建立“客戶”表和“訂單明細(xì)”表之間的聯(lián)系。步驟2:利用查詢的數(shù)據(jù)，制作數(shù)據(jù)透視表。從數(shù)據(jù)透視表的字段列表中，選擇“地區(qū)”字段，并將其拖至行標(biāo)簽，將“銷售額”字段拖至“數(shù)值”區(qū)域，得到按照地區(qū)匯總的銷售額的數(shù)據(jù)透視表,AEC1 :2行標(biāo)簽求和項:銷售額3S2385 493658 278399 2410734 5 6 7 8 9齧slgIr1119418913512058441112步驟3:利用數(shù)據(jù)透視表的數(shù)據(jù)，計算各地區(qū)平均銷售額與銷售額占總銷售額的百分比。在單元格D3:G3中依次輸入說明文字：“地區(qū)

26、”、“客戶數(shù)”、“平均銷售額”與“銷售額占總額百分比”。按照圖2-22所示輸入公式，設(shè)置平均銷售額列顯示方式為小數(shù)，小數(shù)位為0,百分比列顯示方式為百分比，小數(shù)位為0。In D4:=A4In E4:=5.各地區(qū)客戶分布!B4In F4:=B4/E4In G4:=B4/SUM($B$4:$B$9)DEFG3地區(qū)客戶數(shù)平均銷售額銷售額占總額百分比4東北5104774%5華北411204039%6華東161740022%7華南201205419%在單元格 a1:b21中布置好公司從1987-2006年的銷售量數(shù)據(jù)。然后，繪制公司從 1987年至在單元格 a1:b21中布置好公司從1987-2006年的

27、銷售量數(shù)據(jù)。然后，繪制公司從 1987年至8西北255971%9西南72701915%實驗小結(jié)：在實驗六中，因為需要插入數(shù)據(jù)， 但是在自己的電腦中無法顯示數(shù)據(jù)，所以只能復(fù)制指導(dǎo)書中的數(shù)據(jù)，文中已經(jīng)用紅色標(biāo)出。綜合這些實驗，給我們最大的體會就是學(xué)會了如何查詢數(shù)據(jù)。如何對一 個數(shù)據(jù)進行分析。思考題：你還能從哪些方面對客戶的銷售數(shù)據(jù)進行分析，幫助該公司促進銷售或者為客戶提供更好的服務(wù)？答：首先除了可以對客戶的銷售數(shù)據(jù) 進行匯總還可以對數(shù)據(jù)進行預(yù)測，從而得知客戶下個季度的銷售情況，從而可以進行相應(yīng)的應(yīng)對措施。同時可以按照客戶的不同級別匯總各級別客戶的總銷售額，銷售額占總銷售額的比重，為不同的客戶提供不

28、同的服務(wù)。帕累托曲線可以幫助分析投入與產(chǎn)出之間的關(guān)系，它還能幫助該公司進行哪些方面的分析？答：可以準(zhǔn)確的找出銷售量達到的層次，便于查看。實驗三餐飲公司經(jīng)營數(shù)據(jù)時間序列預(yù)測實驗類型：驗證性實驗學(xué)時：2實驗?zāi)康模豪斫庵笖?shù)平滑預(yù)測法的概念；掌握在excel中建立指數(shù)平滑預(yù)測模型的方法；掌握尋找最優(yōu)平滑常數(shù)的各種方法。實驗步驟：步驟1:確定時間序列的類型。2006年共20年的銷售量折線圖。AE-121967*.53a1T-Jl5現(xiàn)T611fl. T76.66flue9BBA.fl- 3104 11 1iwee. t12T- 11319966LSl日白白7 j.1 nZDOOT,S162001%孑172

29、0024 3ia2003札的19003加zooe忑212006步驟2 :利用“數(shù)據(jù)分析”工具中的指數(shù)平滑功能進行預(yù)測。在“文件”選項卡中選擇“選項”，將打開“Excel選項”對話框，在該對話框的左邊一欄里選擇“加載項”，右邊一欄的最下方單擊“轉(zhuǎn)到”按鈕，Excel將顯示“加載宏”對話框。在“加載宏”對話框中選擇“分析工具庫”，單擊“確定”按鈕，將會在“數(shù)據(jù)”選項卡下方出現(xiàn)“分析”組，其中包含“數(shù)據(jù)分析”選項，選中“數(shù)據(jù)分析”，在出現(xiàn)的“數(shù)據(jù)分析”對話框中選擇“指數(shù)平滑”，在“指數(shù)平滑”對話框中，在“輸入?yún)^(qū)域”輸入“ b2:b21 ”單元格，“阻尼系數(shù)”輸入“0.75 ” （注：阻尼系數(shù)=1-平

30、滑常數(shù)），在“輸出區(qū)域”輸入“c2 ”單元格，單擊“確定”按鈕。、運用指數(shù)平滑公式進行預(yù)測步驟1:在單元格fl中輸入平滑常數(shù)0.25，在單元格c2中輸入公式：“=b2”，作為第一年的預(yù)測值 （Fi）,在單元格c3中輸入指數(shù)平滑模型預(yù)測公式“ =$f$1*b2+（1-$f$1）*c2將單元格C3往下復(fù)制，便得到 2007年的指數(shù)平滑預(yù)測值7.96 。F2-吞 E=AVERAGE.CCP2;&21-C2C21J舟C尸1ton干満前缺EL 2B21 9S7& 5唱.弓口wskIn.025 |3SPSSTF41 987e 3e. a J5丄 S3UO仇7血946tJ7?. 3671ET. 211993

31、氐忑7r 069 394眞17r 441O丄 EI?EB. 1a L11丄曰夕貯喜.7TT 96121997T1ie131卿靠S仇*0147. 17r砲15OD7- S7. 49162001S. 37u BT17002協(xié)1化751S2003守6甘.141 口20037- &歇25307-理Wl 1421zaoc肌97-Z2?0O77,衛(wèi)&步驟2 :繪制指數(shù)平滑預(yù)測圖。利用單元格a2:c22中的數(shù)據(jù)繪制公司銷售量指數(shù)平滑預(yù)測圖。念司甫售教呈楷竝平樹用測三、尋找最優(yōu)的平滑常數(shù)步驟1:計算均方誤差。單元格f2中輸入公式：=average(b2:b21-c2:c21)A2)”，作為數(shù)組運算，需要同時按

32、Ctrl+Shift+E nter三個鍵作為輸入結(jié)束，計算均方誤差MSE步驟2:利用模擬運算表及查找引用函數(shù)功能，尋找最優(yōu)平滑常數(shù)。單元格e7:e24中給出不同的平滑常數(shù)(大于0小于1)，在單元格f6中輸入公式：“ =f2 ”，選定單元格 e6:f24，在“數(shù)據(jù)”菜單中選擇“模擬運算表”。ABCELF1i -i平郃r栽1257,睪qI9Q91兀3尢平滑祕.D- 3E舊艸SL 7顯S461991樂7I- 330-卵沾7汨專26L客化卽J.他4宮】曹曹*氐吞Tr 0$(L 巾01轉(zhuǎn)4& ?- 440.0.917410ISB蛍7-fil0.992S11&?札黯心恥0.8S24123 957& “a垂

33、山叫131998瞅耳化90(L 40(LUTPtii1419997 1T.flu 4508812152000T.ST. 490. 50W33200137. 570. 55山朋因172Q02&. 3T. 75a 631182003備&S. 144U9S9O192003T.S& 25o,jro0,SS47L2020057, 5-. 140,75山辟餌21sooe7,兒蜩心陽22T陽心S50.915b23LL卵0.92SS0屯-9T|0-941Q在單元格 f4 中輸入公式：“ =index(e7:e24,match(min(f7:f24),f7:f24,0)”，找到最優(yōu)平滑常數(shù)為 0.35。然后，根

34、據(jù)最優(yōu)平滑常數(shù) 0.35 （將此值代入單元格fl中），2007年的預(yù)測值為7.94 。步驟3 :利用規(guī)劃求解功能，尋找最優(yōu)平滑常數(shù)。在“文件”選項卡中選擇“選項”，將打開“ Excel選項”對話框，在該對話框的左邊一欄里選擇“加載項”，右邊一欄的最下方單擊“轉(zhuǎn)到”按鈕，Excel將顯示“加載宏”對話框。在“加載宏”對話框中選擇“規(guī)劃求解加載項”，單擊“確定”按鈕，將會在“數(shù)據(jù)”選項卡 下方的“分析”組中出現(xiàn)“規(guī)劃求解”選項，選中“規(guī)劃求解 ”。 在設(shè)置 目標(biāo)欄輸入$f$2,然后點擊“求解”按鈕。實驗3-2美食佳”公司月管理費預(yù)測實驗?zāi)康模?理解移動平均預(yù)測法的概念；?掌握在exceI中建立移動

35、平均模型的方法；?掌握尋找最優(yōu)移動平均跨度的各種方法。實驗步驟：一、運用“數(shù)據(jù)分析”工具進行移動平均預(yù)測 步驟1：確定時間序列的類型。繪制公司從 2006年1月至2007年6月共18個月的管理費用折線圖,公司莒理費用時間序列步驟2 :利用“數(shù)據(jù)分析”工具的移動平均功能進行預(yù)測。在“數(shù)據(jù)”選項卡中選擇“數(shù)據(jù)分析”，在出現(xiàn)的“數(shù)據(jù)分析”對話框中選擇“移動平均”在“移動平均”對話框中，在“輸入?yún)^(qū)域”輸入c2:c19 ”單元格，“間隔”輸入“ 3”（注:移動平均跨度為 3），在“輸出區(qū)域”輸入“d3”單元格，單擊“確定”按鈕。A序號月桁G73o1 111 z4 5 6-760-0 12 3i 1 1

36、118:2 12007-07IS. 21*0 26 20. 3 20, 20. a 20. 7 20. 1乩1 i j. -ri. 721* 3i 21-03 20L 33、運用移動平均公式進行預(yù)測步驟1 ：利用average。函數(shù)計算移動平均預(yù)測值。在單元格g1中輸入移動平均跨度3 ,在單元格d5中輸入移動平均模型預(yù)測公式：“ =average(c2:c4) ”將單元格d5往下復(fù)制，便得到 2007年7月的移動平均預(yù)測值20.3 。D5* /；-AVERAGE（C2：C4）ACDEFG1丹営謹(jǐn)需移動宇咖厘3212Q0C-QI17M5E5.41322006-0221432006-0319Tq2

37、Q06-Mi2l + 0!52006-05762006-062020.03T2006-072220+3g耳2006-08IS20+01092006-09?pn, n102006-102020.712II2D06-1I1720.013122Q06-122219.714132IK7-QI2319.7151420（17-0?1920.71F152007=03212L317is2Q07-042h0IBn2007-052219.319182D07-0S2120.320192007-0720+3步驟2 :繪制移動平均預(yù)測圖。利用單元格 c2:d20中的數(shù)據(jù)繪制公司18個月的管理費用及移動平均預(yù)測圖。會司管

38、理黃用稽動平均預(yù)測三、尋找最優(yōu)的移動平均跨度步驟1:計算均方誤差。此處用到兩個函數(shù)：sumxmy2()函數(shù)和 count()函數(shù)。sumxmy2()函數(shù)的功能是返回兩數(shù)組中對 應(yīng)數(shù)值之 差的平方和，它需要兩 個參數(shù)，一個參數(shù)是第 一個數(shù)組或數(shù) 值區(qū)域， 另一個參數(shù)是第二個數(shù)組或數(shù)值區(qū)域。count()函數(shù)的功能是計算某一范圍內(nèi)包含數(shù)值的單元格的個數(shù)。在單元格g2中輸入公式：“ =sumxmy2(c2:c19,d2:d19)/count(d2:d19)”，計算均方誤差 MSE。步驟2 :利用ofset()函數(shù)輔助進行不同移動平均跨度下的預(yù)測。借助average。函數(shù)進行的移動平均計 算僅對跨度3

39、有效，若跨度改為其 他值，則要 修改average()函數(shù)的參數(shù)范圍變化問題。average。函數(shù)的參數(shù)。為此引入ofset()函數(shù)解決offset()函數(shù)的功 能是以指定出)的范圍可個參數(shù)，第(用負(fù)數(shù)表示向左(用負(fù)數(shù)的行數(shù)；第五格或單元格區(qū)域，并可以指定返回的行數(shù)或列數(shù)。它需要五參照系的基準(zhǔn)位置；第二個參數(shù)是相對于這個基準(zhǔn)位置向上正數(shù)表示)偏移的行數(shù);第三個參數(shù)是相對于這個基準(zhǔn)位置(用正數(shù)表示)偏移的列數(shù)；第四個參數(shù)是要返回數(shù)據(jù)范圍回數(shù)據(jù)范圍的列數(shù)。事實上前三個參數(shù)指定了要返回數(shù)據(jù)范給定偏移量得到新的范圍。返回以為一個單元個參數(shù)是作為)或向下(用表示)或向右個參數(shù)是要返的范圍為參照系，通過圍

40、的起始單元格。在單元格 d5中輸入公式:”，拖動單元格d5的填充柄向上復(fù)“ =if(a5=$g$1,average(ofset(d5,-$g$1,-1,$g$1,1)制至d2，向下復(fù)制至d20，從而可在變化的移動平均跨度下計算移動平均值。將單元格 g1中的移動平均跨度改為2，不必改動 d列的公式,D20呼t|* AVERAGE (OFFSEl(D20.-LACDE 1FGI1序?qū)?21IT32213200S-031919.042006-01232D.C5SM4-051975sa2Q.$172M6-07229.09a20C6-08182L01092000-092220.01110200S-1D2

41、D20.012IL2006-111721.01312200S-1222!B-SM132007-0123142007-021922.516152007-032321.01710200T-(M1818172001-0S2219.6IS182007-06J2I20.1Q20192007-0111步驟3 :利用模擬運算表及查找引用函數(shù)功能，尋找最優(yōu)移動平均跨度。g6中給出公式：“ =g2 ”，選定單元格在單元格f7:f21給出不同的移動平均跨度，在單元格f6:g21 ,在“數(shù)據(jù)”菜單中選擇“模擬運算表”輸入引用行的覃元格墮）：輯入引用列的無格；阿山區(qū)3在單元格g4 中輸入公式：“ =index(f7:

42、f15,match(min(g7:g15),g7:g15,0)，找到最優(yōu)移動平均跨度為5。實驗思考1.可否利用規(guī)劃求解功能，尋找最優(yōu)的移動平均跨度?答：在實驗3-2中，無法利用規(guī)劃求解功能尋找最優(yōu)的移動平均刻度。因為求MSE所用的公式為=SUMXMY2(C2:C19,D2:D19)/COUNT(D2:D19)”與移動平均刻度值所在的G1單元格無直接聯(lián)系2. excel提供的移動平均趨勢線功能也可進行移動平均預(yù)測，但趨勢線方法與本實驗所介紹的方法有何不同？答：Excel提供的移動平均趨勢線方法與本實驗所介紹的方法與本實驗所介紹方法的區(qū)別在于趨勢線的作用是對已知的一堆數(shù)據(jù)作回歸分析，以找到一個可以

43、直接計算的方程式并對其他任意未經(jīng)測量的數(shù)值進行計算。趨勢線方法考慮了大量可能的結(jié)果。實驗3-3美食佳”華東分公司銷售額趨勢預(yù)測實驗?zāi)康?理解趨勢預(yù)測法的概念；?掌握在excel中建立線性趨勢預(yù)測模型的方法;?掌握尋找線性趨勢模型參數(shù)的各種方法；?掌握線性趨勢值預(yù)測的不同方法步驟1：確定時間序列的類型。公司銷售額時問序列步驟2 :添加線性趨勢線。在圖中選中數(shù)據(jù)系列，右鍵菜單中選擇“添加趨勢線”，出現(xiàn)“添加趨勢線”對話框。在“添加趨勢線”對話框的“類型”中選擇“線性”。在“添加趨 勢線”對話框的“選項”中選擇 “顯示公式r 平方值公司銷售額趙勢預(yù)測步驟3 :用趨勢線前推法大致預(yù)測線性趨勢值。選定線

44、性趨勢線，右鍵菜單中選擇“趨勢線格式”，在“趨勢線格式”對話框中選定“選項”，將趨勢預(yù)測前推1周期。步驟4 :用方程或函數(shù)準(zhǔn)確預(yù)測線性趨勢值。根據(jù)得到的線性趨勢方程公式y(tǒng)=11.473x+861.98 ，如圖3-28所示，在單元格c13中輸入公式：“ =11.473*a13+861.98”，即將x=12 （ 2007年為第12個時間序列點）代入公式，計算得到 2007 年的預(yù)測值為 999.66 。利用 forecast() 函數(shù)，在單元格c14 中輸入公式： “ =forecast(a13,c2:c12,a2:a12) ”，計算得到 2007 年的預(yù)測值為 999.65 。利用 trend(

45、) 函數(shù)，在單元格 c15 中輸入公式： “ =trend(c2:c12,a2:a12,a13) ”，計算得到 2007 年的預(yù)測值為 999.65 。步驟 5：將預(yù)測結(jié)果在圖中表示。同時選中單元格 b13 和 c13 ，并作為新數(shù)據(jù)點復(fù)制到圖形的數(shù)據(jù)線上，趨勢線前推只能在圖中看到大致預(yù)測結(jié)果 1000 左右，實驗思考1本實驗的幾張圖中， x 軸是“分類”還是“自動” ？ 本實驗(實驗 3-3)中， X 軸是自動2預(yù)測點數(shù)據(jù)如果作為新數(shù)據(jù)系列添加到圖形中，結(jié)果與圖 3-29 有何不同？ 答：實驗 3-3 中，預(yù)測點數(shù)據(jù)如果作為新數(shù)據(jù)系列添加到圖形中，預(yù)測部分的值將是一條直線。 3為什么預(yù)測值一

46、定在趨勢線的延伸線上？ 答：預(yù)測值一定在趨勢線上的原因是預(yù)測值是依據(jù)趨勢線作出來的。4若要預(yù)測公司 2008 年的全國銷售額，可以怎么做？若要預(yù)測公司 2009 年、 2010 年、甚 至更遠年份的銷售額，會有什么問題？答：若要預(yù)測 2008 年的全國銷售額， 可依據(jù) 2007年的預(yù)測值來作。 但若要預(yù)測更遠年份的銷售額， 則不能以之為基礎(chǔ)由趨勢線函數(shù)進行預(yù)測，因為彼時銷售額呈線性增長，與客觀事實不符。5除了本實驗中介紹的添加趨勢線方法可以找到線性趨勢預(yù)測模型的參數(shù)外，還可以用 哪些方法找到線性趨勢預(yù)測模型 y=a+bx 中的參數(shù) a 和 b。答：還可用回歸方法找到 Y=a+bX 中參數(shù) a,

47、b 的值。實驗 3-4 “美食佳 ”公司會員卡發(fā)行量趨勢預(yù)測實驗類型：驗證性實驗學(xué)時： 2實驗?zāi)康? ?理解非線性趨勢預(yù)測法的概念；?掌握在excel中建立非線性趨勢預(yù)測模型的方法;?掌握非線性趨勢值預(yù)測的方法。?預(yù)測公司2007年7月會員卡的發(fā)行量。實驗步驟：步驟1:確定時間序列的類型。公司會員卡發(fā)行量時間序列步驟2 :添加非線性趨勢線在圖中選中數(shù)據(jù)系列，右鍵菜單中選擇“添加趨勢線”，出現(xiàn)“添加趨勢線”對話框。在“添加趨勢線”對話框的“類型”中選擇“對數(shù)”。在“添加趨勢線”對話框的“選項”中選中“顯示公式”和“顯示 r平方值”步驟3 :趨勢線前推法大致預(yù)測非線性趨勢值。選定對數(shù)趨勢線，右鍵菜

48、單中選擇“趨勢線格式”，在“趨勢線格式”對話框中選定“選項”。將趨勢預(yù)測前推1周期。步驟4:用方程或函數(shù)準(zhǔn)確預(yù)測非線性趨勢值。根據(jù)得到的方程公式 y=7.7785In(x)+3.7651，在單元格 c16中輸入公式：“ =7.7785*1n(a16)+3.7651 ”,即將x=15(2007年7月為第15個時間序列點)代入公式，計算得到2007年7月的會員卡發(fā)行預(yù)測值為24.83萬張。步驟5:將預(yù)測結(jié)果在圖中表示。趨勢線前推只能在圖中看到大致預(yù)測結(jié)果,公司會員卡發(fā)行量m酸性趨勢預(yù)測實驗思考1請試一下，圖3-39可否考慮用xy散點圖做？ 這時用什么數(shù)據(jù)作為 x軸合適？答：可以，用序號的對數(shù)值ln

49、(x)代替x，作為x軸合適，y與x是一次線性函數(shù)關(guān)系。2 為什么預(yù)測值一定在趨勢線的延伸線上？答：預(yù)測值一定在趨勢線上的原因是預(yù)測值是依據(jù)趨勢線作出來的。3除了本實驗中介紹的添加趨勢線方法可以找到對數(shù)趨勢預(yù)測模型的參數(shù)外，是否可以用規(guī)劃求 解法找到對數(shù)趨勢預(yù)測模型y=a+bln(x)中的參數(shù)a和b ?答：還可用回歸方法找到Y(jié)=a+bX中參數(shù)a,b的值。實驗3-5美食佳”火鍋連鎖店原料年度采購成本預(yù)測實驗?zāi)康模?理解季節(jié)指數(shù)的概念；?掌握季節(jié)指數(shù)預(yù)測方法。實驗步驟： 步驟1:確定時間序列的類型。公司采購成本時間序列步驟2 :計算季節(jié)指數(shù)。一年有4個季度，所以以4為移動平均跨度，計算移動平均數(shù)，其

50、結(jié)果應(yīng)該對應(yīng)放在每4個季度的中間位置。但當(dāng)移動平均跨度為 4時，沒有中間季度位置可放， 因此只能放在第3個季度對應(yīng)的位 置處。所以從第一年開始， 4個季度的移動平均數(shù)放在單元格 d4中，即在單元格 d4中輸入公式：“ =average(c2:c5) ”。拖動單元格 d4的填充柄復(fù)制到單元格 d5:d16中，在單元格e4中輸入公式：“ =(d4+d5)/2 ”，并將它復(fù)制到單元格e5:e15內(nèi)。由此完成中心化移動平均數(shù)的計算。把中心化后的移動平均數(shù)單元格e2:e17添加到圖3-41所示的折線圖中，得到如圖3-44的結(jié)果。可見中心化的移動平均數(shù)體現(xiàn)了原材料采購成本的穩(wěn)定水平，即在一定程度上消除了原

51、材料采購成本時間序列的不規(guī)則成分。在單元格f4中輸入公式：“ =c4/e4”，并將它復(fù)制到單元格f5:f15中，由此完成季節(jié)不規(guī)則值的計算。 分別計算每一年對應(yīng)季度的不規(guī)則值的平均值，就可得到各個季度的季節(jié)指數(shù)。如在單元格i2中輸入公式：“ =average(f2,f6,f10,f14) ”，可計算得到第1季度的季節(jié)指數(shù)。公司采購成本季書揭數(shù)預(yù)劃用每一個季節(jié)指數(shù)除以未調(diào)整的季節(jié)指數(shù)之和再乘以季度指數(shù)總和4。如圖3-43中單元格i6所示,未調(diào)整前的季節(jié)指數(shù)之和為 3.9852，所以需要調(diào)整。在單元格 j2中輸入公式：“ =i2/$i$6*4 ”，往下 復(fù)制到j(luò)3:j5，得到調(diào)整后的季節(jié)指數(shù)。步驟

52、3:消除季節(jié)影響。將調(diào)整后的季節(jié)指數(shù)復(fù)制到E列，分別對應(yīng)2003-2007年的4個季度。F2農(nóng)=D2/E2ABCDEF1序號年季度采購成本調(diào)整后季 節(jié)指數(shù)消除季 節(jié)影響2120帕年詳度1竄01.210S413.2 I.322李度匸0 0. 3304621. 2433季度1 0 0. 2092719. 1544季度E1. 02. 2494422. 765200 Q 年譯度28. 01. 2108423. 1762季度90. 3304629.4E7$季度&. T0. 2092732. 0984季度7L52. 2494431.510g加05年1李度45. 0 11.2108437. 211102季度

53、12, 60. 330463S. 112113季度10. 20. 2092748. 71312q季度105. 02. 2494446. 7142006】季度阪01.2108445.4151415.20. 3304646. 016IE；?季度14.10. 2092767, Q171&4季庚114. 02. 24S4450. 7LY加笛年1季度1. 2108419182季度0. 330462Q19痔度0. 2092721204季度2. 24944在單元格F2中輸入公式：“ =D2/E2 ”，將公式復(fù)制到單元格F3:F17中，得到消除季節(jié)影響后的結(jié)果。利用單元格F2:F17中的數(shù)據(jù)繪制公司從 200

54、3年第1季度到2006年第4季度共16個季度的原材料步驟4:計算預(yù)測值。在單元格H2中輸入公式：“ =G2*E2 ”，將公式復(fù)制到單元格 H3:H21中，即在線性趨勢預(yù)測值的基礎(chǔ)上乘以調(diào)整后的季節(jié)指數(shù)得到最終的季節(jié)預(yù)測值。公司2007年1至4季度的采購成本預(yù)測值分別為73.0、20.9、13.8、154.9。根據(jù)D列的原始采購成本數(shù)據(jù)和H列的季度預(yù)測值數(shù)據(jù)，作折線實驗思考1圖3-47中的“序號”一列有什么用？答：圖3-47中 序號”一列的作用是為趨勢線公式的獲得提供依據(jù)（作為自變量X）。計算趨勢預(yù)測值時，若不用forcast（）函數(shù)，還可以有什么方法？請至少用兩種方法試試看。答：計算趨勢預(yù)測值

55、還可用移動平均預(yù)測法、指數(shù)平滑預(yù)測法、一元線性回歸分析模型等。季節(jié)指數(shù)模型是否只能用于季節(jié)數(shù)據(jù)的預(yù)測？若是年度、月度、甚至周數(shù)據(jù)，可以用季節(jié)指數(shù)模型嗎？答：季節(jié)指數(shù)模型不是只能用于季節(jié)數(shù)據(jù)的預(yù)測，年度、月度、周數(shù)據(jù)等在某些情況下均能用季節(jié)指數(shù)模型。實驗小結(jié)：在實驗中遇到的問題是，在進行指數(shù)平滑求解時，最優(yōu)的平滑指數(shù)求法很麻煩，在多次進行求解后才可以得到。思考題：為什么用模 擬運算表加查找引用函數(shù)功能，得到的最優(yōu)平滑常數(shù)（0.35 ），與用 規(guī)劃求解功能得到的結(jié)果（0.37 ）不一樣？答：用模擬運算表加查找引用函數(shù)功能，得到的最優(yōu)平滑指數(shù)(0.35 ),與規(guī)劃求解的結(jié)果(0.37 )比一樣的原因

56、是因為系統(tǒng)誤差的存在。可否調(diào)整模擬運算表的輸入數(shù)據(jù)間隔，再試一試，結(jié)果會如何？答：結(jié)果不變，因為模擬運算表只是將數(shù)據(jù)代入變量中來求得對應(yīng)的值，所得到的值與數(shù)據(jù)的間 隔無關(guān)。實驗四 住房建筑許可證數(shù)量的回歸分析實驗類型：驗證性實驗學(xué)時：2實驗?zāi)康模豪斫庖辉€性回歸分析的概念；針對不同的問題，能夠建立適當(dāng)?shù)囊辉€性回歸模型；掌握內(nèi)建函數(shù)slope()、intercept()與linest()的用法；掌握用規(guī)劃求解法、添加線性趨勢線法、回歸分析報告法確定線性回歸方程的系數(shù)；給定自變量的情況下，根據(jù)線性回歸模型預(yù)測因變量的值。實驗步驟：步驟1：確定因變量與自變量并輸入觀測值。根據(jù)實驗要求，我們確定因變

57、量為建筑許可證的頒發(fā)數(shù)量，自變量為人口密度，并將數(shù)據(jù)合理的布置在excel工作表的單元格a1:b19中AB訐可 訃的簡塩臣卑的人口27S2031&1204623G1O&705111G06打丁 00119007&44412320S680014340會1590010T5711113001龍S1Q03010013201 SO1462302210015832023000It-84 S024050IT910025300isSi 3019駟mo2700步驟2:繪制因變量與自變量關(guān)系散點圖。利用圖中的數(shù)據(jù)，以每平方公里的人口密度為x值，建筑許可證的頒發(fā)數(shù)量為y值，繪制xy散點圖。30000崖這許前證的頡貢數(shù)

58、量與驀平方公里人口密庚關(guān)果裁點圖25000# +20000 *150D0-* *100DD-:*-5000101il50006(10070008000900010000步驟3:求出回歸系數(shù)a、b的取值，計算判定系數(shù)R2，并進行預(yù)測。excel提供了幾種不同的工具，包括規(guī)劃求解工具，intercept()、slope()與linest()等內(nèi)建函數(shù)，在散點圖中添加趨勢線和趨勢線方程以及生成回歸分析報告等方法來確定回歸系數(shù)a和b。我們這里介紹利用規(guī)劃求解的方法來求解回歸系數(shù)。步驟4 :假定回歸系數(shù)的值，建立線性回歸模型。假定回歸系數(shù)的值為a=1，b=1并將之放在單元格f2:f3中。用回歸直線方 程

59、y=a+bx以及每平方公里的人口密度來計算建筑許可證的頒發(fā)數(shù)量預(yù)測值，放在單元格c2中，即在單元c2中輸入公式“=$f$2+$f$3*a2 ”，并將此公式復(fù)制到c3:c19中，得到建筑許可證的頒發(fā)數(shù)量預(yù)測值。在單元格f5中計算建筑許可證的頒發(fā)數(shù)量觀測值與預(yù)測值的均方誤差mse，即在單元格f5中輸入公式“=average(c2:c19-b2:b19)A2)” (注：其中的花括號不是直接輸入，是將所有內(nèi)容輸入完后按住ctrl+shift鍵后再按回車鍵生成的)。在“規(guī)劃求解參數(shù)”對話框中將目標(biāo)單元格設(shè)為$f$5，使其等于最小值，將可變單元格ACLiE1里的人口 至席崖軸午可 證旳務(wù)浚點講可證魏#25

60、9517S2O5952a f SE J1354&b I *4 和1432 3 a1OSTQ6237R干右O. S 365 352(51564利M 160MSEI27SS10CTT. 7&37401U 1 SQQ7417&44412920S厳韋0014340SSOl盤壬方英空旳人 口譽fitTOGO97宜401&0OOT24L建fit許可證旳頤汝勲重TOO!107571laooaT5T2117 06&1930070612810011 DO*31Q113810201301031422100$231158320230003321161胸024 OSOM&l17號丄0口ZB 300910113S*13