1、2014年統計實驗指導書王路幫 編目 錄實驗1 數據整理與數據分析51.1 數據整理51.2 數據描述與分析10實驗2 參數估計15實驗3 回歸分析21實驗4 時間數列24實驗1 數據整理與數據分析1.1 數據整理一、實驗目的和要求：能熟練的進行統計數據的錄入、分組、匯總及各種常用統計圖表的繪制。二、實驗內容：1、數據的錄入2、數據的排序3、數據的分組4、數據透視分析5、常用統計圖表的繪制三、統計函數頻數分布函數 （FREQUENCY）的語法形式為: FREQUENCY(data_array,bins_array)其中：Data_array為用來編制頻數分布的數據，Bins_array為頻數或

2、次數的接收區間。四、實驗步驟：1、數據的錄入數據的錄入是指把調查得到的結果輸入到一張叫數據清單的EXCEL工作表中。數據清單是指包含相關數據的一系列工作表的數據行，如發貨單數據庫，或一組客戶名稱和聯系電話。數據清單可以作為數據庫使用，其中行表示記錄，列表示字段。例 某集團公司欲在某地區投資于醫療衛生事業，為了減少風險，獲得利潤，該集團企劃部門決定先了解一下市場潛力。企劃部王經理隨機訪問了該地區幾家醫院中就醫的36名患者，詢問其等候看病的時間，根據這些數據，王經理會得到什么信息呢？ 打開EXCEL工作表，在列中輸入數據的名稱，稱為變量。然后依次輸入相應調查數據。2、數據的排序打開 “數據整理.x

3、ls” 工作簿，選定“等候時間”工作表。利用鼠標選定單元格A1:B37區域在菜單中選擇“數據”中的“排序”選項，則彈出排序對話框。在排序對話框窗口中，選擇“主要關鍵字”列表中的“等候時間”作為排序關鍵字，并選擇按“遞增”排序。由于所選取數據中已經包含標題，所以在“當前數據清單”中選擇“有標題行”，然后單擊“確定”按鈕，即可得到排序的結果。在數據清單中使用分類匯總的方法如下：先選擇需要分類匯總的數據區域A1：B37，然后選擇“數據”菜單中的“分類匯總”選項，則打開“分類匯總”對話框。在“分類字段”的下拉式列表中選擇要進行分類的列標題，在“匯總方式”的下拉式列表中選擇行匯總的方式，本例中選擇按“等

4、候時間”進行分類，選擇按“計數”進行匯總，單擊“確定”按鈕，便得到分類匯總的結果。在對數據進行了分類匯總之后，可以觀察到等候時間為1分鐘的人次為1，等候時間為3分鐘的人次為2等等。盡管分類匯總可以給出各個數值所出現的次數，但從這些次數中還是無法得出其數據分布的規律性。這是因為沒有將數值相近的數據進行合并，因而不能理想地抽象概括現象的數量特征。3、數據的分組利用Excel頻數分布函數進行分組建立頻數分布的步驟為：先對數據進行排序，以了解全部數據的變動范圍；然后選擇全部數據的分組組數；再確定分組的組限，最后對各組數值所出現的頻數進行計數。打開“數據整理.xls”工作簿，選擇“等候時間”工作表。在單

5、元格C2中輸入“分組”，在單元格D2中輸入“頻數”。在C3：C8區域中依次輸入10，20，30，40，50，60，作為頻數接受區域，它們分別表明等時間在10分鐘以下的人數，等候時間在10分鐘以上，20分鐘以下的人數等。選定D3：D8區域，在“插入”菜單中單擊“函數” 選項，打開“粘貼函數”對話框在“函數分類”列表中選擇“統計”，在“函數名”列表中選擇“FREQUENCY”，單擊“確定”按鈕，Excel彈出“頻數分布”對話窗口。 在數據區域“Data_array”中輸入單元格B2：B37，在數據接受區間Bins_array 中輸入單元格C3：C8，在對話窗口中可以看到其相應的頻數是6，7，9，8

6、，5，1，0。由于頻數分布是數組操作，所以，此處不能直接單擊“確定”按鈕，而應按Ctrl +Shift組合鍵，同時敲“回車”鍵，得到頻數分布。4、數據透視分析數據透視主要是處理以文字形式表現的資料，如性別、職業、文化程度等。分為單變量表格與雙變量表格，前者稱為單向表，后者稱為交叉表。 單向表例 某移動通訊公司要制定新一年的銷售計劃，需要向市場了解移動用戶對數字移動電話類型的需求。經過問卷設計，在該地區抽取了1000個樣本進行問卷調查，獲得關于移動電話用戶消費行為的一些數據資料，試根據工作表中的數據分析該地區移動電話用戶的電話類型偏好。 打開“數據整理.xls”工作簿，選定“移動電話調查”工作表

7、。單擊“數據”菜單中的“數據透視表和圖表報告”選項，Excel彈出數據透視表向導對話框。 根據向導要求，第1步是指定“數據源類型”和確定所需創建的“報表類型”。選擇“數據清單或數據庫”作為數據源，選擇“數據透視表”作為報表類型。最后單擊“下一步”。 第2步是確定數據區域，本例中數據所覆蓋區域為A1:D1001單元格，單擊“下一步”按鈕，向導給出第3步操作對話框窗口。第3步確定數據透視表的位置。選擇現有工作表，并利用鼠標將數據透視表的位置確定在E1單元格中，如果單擊“完成”按鈕，可以結束操作。單擊“版式”，Excel彈出對話框窗口，選擇右邊的“數字移動電話類型”字段，并將它拖到左邊的“行” 區，

8、再選擇“數字移動電話類型” 字段，將其拖放在“數據”區域中，顯示為“計數項：數字移動電話類型”，以便進行數據匯總。 雙擊數據區域中的“計數項：數字移動電話類型”字段，打開“數據透視表字段”對話框，在“匯總方式”列表中選擇“計數”。如果想選擇數據顯示方式，可單擊圖右側的 “選項”按鈕，則EXCEL 彈出“數據顯示方式”。在“數據顯示方式”的下拉選項中確定“占同列數據總和的百分比”選項, 單擊“確定”按鈕，則版式調整完畢。再單擊 “確定”按鈕，回到數據透視表步驟3。 單擊數據透視表步驟3中的“完成”按鈕。交叉表例 該公司市場部經理希望根據上例中的調查結果，確認不同性別的用戶，其移動電話類型偏好是否

9、存在著不同。打開“數據整理.xls”工作簿，選定“移動電話調查”工作表。 從“數據”菜單中選擇“數據透視表和圖表報告”選項，得到“數據透視表對話框”窗口。在第1步之中選擇“數據清單或數據庫”與“數據透視表”，單擊“下一步”按鈕。 在第2步中，確定數據區域A1:D1001，單擊“下一步”按鈕在第3步中，確定數據透視表的位置，如圖所示，但將其位置調整到“H1”單元格。然后，單擊“版式”按鈕，打開“版式”對話框。 將對話框右邊的“數字移動電話類型”字段拖到左邊的“行”區作為分組變量，再將右邊的“性別” 字段拖到“列”區，作為交叉分析的變量，最后將右邊的“性別” 字段拖放到“數據”區域中，并選擇計數項

10、。如果希望結果是以百分比的形式顯示，則可雙擊數據區域中的“計數項，性別”，則會彈出“數據透視表字段”對話框。選擇“占同列數據總和的百分比”選項，來表示所出現的頻率，最后，單擊“確定”按鈕，又回到“版式”對話框。 單擊“確定”按鈕，回到第3步，要注意數據透視表的位置定在單元格H1，最后，單擊“完成”按鈕，得到交叉表。 5、常用統計圖表的繪制條形圖與柱形圖 例 薪酬是眾人所關心的主題，也更是當代經理人所關心的主題。北京、上海、深圳三個市經理人的薪酬是我國經理人“薪酬市場”的晴雨表。1999年經理人雜志所作的薪酬調查發現：所調查的經理人中，平均月收入最高的是上海經理人，為7767元；其次是深圳經理人

11、，為7602元；最后是北京經理人，為7288元。結論應當是最高為上海經理人。那么此結論是否可以用圖形來表示呢？ 打開“數據整理xls“工作簿，選擇“薪酬”工作表。在Excel的“插入”菜單中選擇 “圖表”選項，Excel會啟動圖表向導，彈出“圖表向導”對話框窗口。在 “圖表類型”列表中選擇“柱形圖”，在“子圖表類型”列表中選擇“三維簇狀柱形圖”，單擊“下一步” 按鈕，進入數據源對話框.在這步對話框中，主要是選擇數據源。單擊“數據區域”右端的“壓縮對話框”按鈕，打開工作表，在工作表上將鼠標指向單元格A1，按下鼠標左鍵，拖到B4單元格，再單擊“壓縮對話框”按鈕，回到源數據對話框。確定A1:B4單元

12、格作為此圖的數據源，同時，也應注意數據系列是以行還是以列方式排列，如果正確，此時，便有一個預覽的圖形如圖所示，如滿意可單擊“下一步”按鈕，進入“圖表選項”對話框.單擊對話框上方的“標題”頁面，在標題中輸入“三城市薪酬比較”；在“圖例”頁面中選擇底部，至于其他功能則不需改動與填加，這些過程都會在圖表預覽框中看到，如果滿意，單擊“完成”按鈕結束，得到圖形。雙擊所繪制的圖形，則會得到對話框。單擊“字體”頁面，在“字體”列表中選擇“宋體”，在“字形”列表中選擇“常規”，在“字號”列表選擇“10”。 折線圖將平面中的交點相繼聯接起來所得圖形稱為折線圖，它一般用于描述某一變量在一段時期內的變動情況。 例已

13、知1990年至1999年各月社會商品零售總額，試觀察一下社會商品零售總額的發展趨勢及其變動規律。打開“數據整理.xls”工作簿，選擇“零售額”工作表。在B列后插入一列以便輸入年份與月份。先選擇B列單元格，再打開“插入”菜單，選擇“列”項，則自動在B列后填加一列C，原來C列的內容變成了D列。 在單元格C1中輸入“時間”，在C2單元格中輸入公式“=B2&CHAR(13)&A2”，從單元格C3開始復制這個公式到C117。打開Excel“插入”菜單，選擇“插入”菜單中的“圖表”選項，Excel會啟動“圖表向導”。 在步驟1“圖表類型”中選擇“折線圖”，及 “數據點折線圖”，單擊“下一步

14、”按鈕，進入步驟2的向導窗口。 在步驟2中的數據區域中輸入C1：D117，單擊“下一步”按鈕，進入向導“步驟3”。 在步驟3中單擊圖表“標題”頁面，輸入標題“社會消費品零售額趨勢圖”；單擊“圖例”頁面，取消顯示圖例，如果通過圖表預覽，認為滿意，即可單擊“完成”按鈕。餅形圖 例 中國人如何看WTO。由中國社會調查事務所組織的最新一次問卷調查顯示：有58.4%的被調查者對中國加入WTO非常有信心；有19.3%的被調查者沒有態度；有9.2%的被調查者表示擔心；有13.1%的被調查者反對中國加入WTO。試用餅形圖描述一下中國人對加入WTO的看法。 打開“數據整理.xls”工作簿，選擇“WTO”工作表。

15、 打開Excel“插入”菜單，并單擊“插入”菜單中的“圖表”選項，Excel會啟動圖表向導。 在步驟1的“圖表類型”中選擇“餅圖”，并在“子類型圖表”中選擇“三維餅圖”，單擊“下一步”按鈕，進入向導2。 在步驟2中的數據區域中輸入A3：B6單元格，單擊“下一步”按鈕，進入向導3。在步驟3中單擊“標題”頁面，輸入標題“中國人看WTO”；單擊“圖例”頁面，取消顯示圖例；單擊“數據標志”頁面，在“數據標志”列表中選擇“同時顯示百分比及數據標志”。如果通過圖表預覽，認為滿意，即可單擊“完成”按鈕。五、作業：洛倫茨曲線與居民收入差異分析 洛倫茨曲線是本世紀初美國經濟統計學家洛倫茨根據意大利經濟學家巴雷特

16、提出的收入分配公式繪制成的描述收入和財富分配性質的曲線。曲線橫軸是累計人口百分比，縱軸是累計收入或財富的百分比。當一個國家的收入分配完全按人均分配時，同一累計百分比的人口就一定占有相同的累計收入百分比。此時該國的收入分配程度曲線就與對角線重合。如果絕大多數人口占有很少的財富和收入，而少部分人占有了絕大部分的收入，則該國的曲線就靠近下橫軸和右縱軸。一般來說，國家的收入分配不會是絕對平均的，也不會是絕對不平均的。將任一國家或地區的收入分配情況繪制成洛倫茨曲線就可以觀察分析該國家或地區收入分配的平均程度。 例 某地區1999年的人口及收入情況見圖所示，試繪制該地區的洛倫茨曲線。思考：你能否依據寧波市

17、有關的統計資料，分析寧波市不同行業收入的洛倫茲曲線？1.2 數據描述與分析一、實驗目的：能熟練的用Excel的描述統計工具進行數據的描述和分析。二、實驗主要內容：1、 集中趨勢的測定與分析2、 離中趨勢的測定與分析3、3、分布形態的測定與分析三、理論基礎均值是所有的標志值之和除以其觀察值的個數。它考慮了所有數值，因而均值的大小受總體中極端數值的影響。如果總體中有極大值出現，則會使均值偏于分布的右邊，如果總體中出現極小值，均值則會偏于分布的左邊。 眾數是總體中出現次數最多的數值，它只考慮總體中各數值出現頻數的多少，不受極端數值的影響，但當總體中出現多個眾數時，眾數便沒有意義。中位數只是考慮各單位

18、數值在總體中的順序變化，它受極端數值的影響不大。將總體分成相等的四個部分的測定數為四分位數。位于總體第25%位置的數值是第1四分位數Q1；位于總體第50%位置的數值是第2四分位數Q2；位于總體第75%位置的數值是第3四分位數Q3 。四分位距是總體中第3四分位數與第1四分數之差，四分位距可包括位于總體分布中心的50%，它能集中地反映總體的特征。四、統計函數1、均值函數（AVERAGE）均值函數是應用比較廣泛的一種平均數函數，它能夠計算一組數據的算術平均數。其語法結構為：AVERAGE(number1,number2, .)。其中：number1, number2, .為要計算平均數的 130 個

19、參數。2、中位數函數（MEDIAN）中位數函數用于描述居于數據分布中心位置的數值。其語法結構為：MEDIAN(number1,number2, .)。其中：number1, number2,. 是需要找出中位數的 1 30 個數字參數。3、眾數函數（MODE）眾數函數用于計算一組數據中出現頻率最多的數值。其語法結構為：MODE(number1,number2, .)。其中：number1, number2, .是用于眾數計算的 1 30 個參數，也可以使用單一數組（即對數組區域的引用）來代替由逗號分隔的參數。4、總體標準差函數（STDEVP）總體標準差函數用來反映相對于均值的離散程度。其語法結

20、構為：STDEVP(number1,number2,.)。其中，number1,number2,.為對應于樣本總體的 1 30 個參數。5、樣本標準差函數(STDEV)樣本標準差函數用來計算樣本中相對于均值的離散程度。其語法結構為： STDEV(number1,number2,.)其中，Number1,number2,.為對應于總體樣本的 1 30 個參數6、四分位數函數QUARTILE可以計算一組數據的四分位數。其語法結構為： QUARTILE(array,quart) 其中：(1)Array為計算四分位數的數組或數據區域。 (2)Quart決定返回第幾個四分位數。如果 quart 等于0，

21、函數 QUARTILE 返回最小值；quart 等于1，函數 QUARTILE 返回第1四分位數(第25 個百分排位)；quart 等于2，函數QUARTILE返回中位數(第 50 個百分排位)；如果 quart 等于3，函數 QUARTILE 返回第3四分位數(第 75 個百分排位)；如果 quart 等于4，函數 QUARTILE 返回最大值。7、偏度函數（SKEW）用于計算次數分布的不對稱程度。其語法結構為： SKEW(number1,number2,.) 其中：number1,number2.為需要計算偏斜度的 1 30 個參數。峰度函數（KURT）用于反映次數分布的尖銳度或平坦度。其

22、語法結構為： KURT(number1,number2, .) 其中：number1,number2, 為需要計算其峰值的 1 30 個參數。五、實驗步驟：1、集中趨勢的測定與分析（1）均值函數的使用 例 某企業的生產部門使用抽樣方法檢測一批新產品的質量，該批產品的抗拉強度見原始數據。管理人員希望知道這批產品的抗拉強度的平均水平，以決定產品質量是否合格。由此需要計算抗拉強度的均值、中位數與眾數 。操作步驟如下：打開“數據描述分析.xls”工作簿，選擇“抗拉強度”工作表。在單元格A7中輸入“均值”，在A8中輸入“中位數”，在A9中輸入“眾數”。選定單元格B7，單擊“插入”菜單，選擇“函數”選項，

23、Excel會彈出“粘貼函數”對話框窗口。在“函數分類”列表中選擇“統計”，在“函數名”列表中選均值函數“AVERAGE”。單擊“確定”，則彈出“AVERAGE”函數對話窗口。 在“Number1”區域中輸入數據B2：B6后，對話窗口底部便顯示出計算結果30。如果對話窗口中沒有計算結果，便說明計算有錯誤，需要再檢查一下。單擊“確定”按鈕，計算完成。也可以直接在單元格B7中輸入均值函數公式“=AVERAGE(B2:B6)”，然后敲回車鍵，得到同樣結果。在單元格B8中輸入公式“=MEDIAN（B2：B6）”計算中位數。在單元格B9中輸入公式“=MODE（B2：B6）”計算眾數。案例研究：網上沖浪者的

24、年齡§據報道，越來越多的人熱心于網上沖浪，因為它能夠打開空間的界限，帶給人許多新的知識。現隨機抽取一些上網人進行調查，得知其年齡數據如下：22、58、24、50、29、52、57、31、30、41、44、40、46、29、31、37、32、44、49、29。 §試根據此樣本資料對網上沖浪者的年齡水平給以描述。操作步驟如下：打開“數據描述分析.xls”工作簿，選擇“網上沖浪” 工作表在單元格A22：A25中分別輸入“均值”、“中位數”、“眾數”。在單元格B22中輸入公式“=AVERAGE（B2:B21）”，計算均值為為38.75。在單元格B23中輸入公式“=MEDIAN（B2

25、:B21）”，計算中位數為38.5。在單元格B24中輸入公式“=MODE（B2:B21）”，計算眾數為29。2、離中趨勢的測定與分析如下如所示，三個不同的曲線表示三個不同的總體，其均值相同，但離中趨勢不同。（1）用Excel函數計算標準差與樣本標準差可以利用Excel來創建公式進行標準差測定，也可以利用Excel 中的工作表函數來直接進行計算。操作步驟如下：建立如前述的“網上沖浪”工作表。單擊工具欄中的“函數”快捷按鈕，彈出對話框如前述所示的“粘貼函數”的對話框。在“函數分類”列表中選擇“統計”，在“函數名”列表中選擇總體標準差函數STDEVP。在數據區域中輸入B2：B21，則計算結果便顯示在

26、對話窗口下面，其值為10.61543687，單擊“確定”按鈕結束。樣本標準差的計算方法與其相同。（2）四分位數與四分位距將總體分成相等的四個部分的測定數為四分位數。位于總體第25%位置的數值是第1四分位數Q1；位于總體第50%位置的數值是第2四分位數Q2；位于總體第75%位置的數值是第3四分位數Q3 。四分位距是總體中第3四分位數與第1四分數之差。四分位距可包括位于總體分布中心的50%，它能集中地反映總體的特征。仍以網上沖浪者年齡為例，計算數據的最小值、第1四分位數、第2四分位數、第3四分位數和四分位距。打開“數據描述分析.xls”工作簿，選擇“網上沖浪”工作表。 在單元格A27、A28、A2

27、9、A30和A31中分別輸入“最小值”、“第1四分位數”、“第2四分位數”、“第3四分位數”、“最大值”和“四分位距”。 在B27單元格中打開“插入”菜單，單擊“函數”選項，在“函數類型”列表中選擇“統計”，在“函數名”列表中選擇四分位數函數QUARTILE，單擊“確定”按鈕，進入四分位數對話框窗口。 在四分位數函數QUARTILE的對話框中，§在Array中輸入數據區域B2:B21；§在Quart中輸入0，表示計算最小值或第0四分位數；§單擊“確定”按鈕，其值便顯示在單元格B27中。在單元格B28中輸入“=QUARTILE(B2:B21,1)”，計算第1四分位數

28、，顯示29.75。在單元格B29中輸入“=QUARTILE(B2:B21,2)”，計算第2四分位數，顯示38.5。在單元格B30中輸入“=QUARTILE(B2:B21,3)”，計算第3四分位數，顯示46.75。在單元格B31中輸入“=QUARTILE(B2:B21,4)”，計算最大值，顯示58。在單元格B32中輸入“=B30-B28”，計算四分位距，顯示值為17。 四分位距表明整個總體中位于29.75到46.75之間的數值有50%。換句話說，從29.75到46.75這個17歲范圍內可包括總體的50%。 3、分布形態的測定與分析對于一組數據，不僅要描述其集中趨勢、離中趨勢，而且也要描述其分布形

29、態。這是因為一個總體如果均值相同，標準差相同，但也可能分布形態不同。另外，分布的形態有助于識別整個總體的數量特征。總體的分布形態可以從兩個角度考慮，一是分布的對稱程度，另一個是分布的高低。前者的測定參數稱為偏度或偏斜度，后者的測定參數稱為峰度。在統計分析中，用偏度指標對其進行測定。偏度數值等于零，說明分布為對稱；偏度數值大于零，說明分布呈現右偏態；如果偏度數值小于零，說明分布呈左偏態。峰度是掌握分布形態的另一個指標，它能夠描述分布的平緩或陡峭。如果峰度數值等于0，說明分布為正態；如果峰度數值大于0，說明分布呈陡峭狀態；如果峰度值小于0，則說明分布形態趨于平緩。仍以網上沖浪者年齡為例，計算其偏態

30、與峰度。計算步驟如下：建立“網上沖浪”工作表。在A33單元格中輸入“偏態”，在B33單元格中輸入“=SKEW(B2:B21)”，其顯示值為0.27701322。在A34單元格中輸入“峰度”，在B34單元格中輸入“=KURT(B2:B21)”，其顯示值為 -1.0811953。結果如下圖所示。 從圖中偏度與峰度計算結果中可以看出，偏度0.27701322，說明其分布形態呈輕微右偏態，基本接近于對稱分布。峰度系數為-1.0811953，小于0，說明其分布形態趨勢于平坦。這主要是由于觀察數據過少，不能表現其應有的峰度。實驗2 參數估計一、實驗目的和要求：了解Excel中的各種參數估計統計函數，能夠運

31、用Excel統計函數對正態單總體參數進行區間估計。二、實驗主要內容：（1）熟悉用于參數估計的各種統計函數（2）正態單總體參數的區間估計（3）正態單總體參數的假設檢驗三、基礎理論知識1、 總體均值區間估計的基本內容2、當總體方差2已知時總體均值的區間估計對于給定的顯著性水平，可以構造均值的置信區間為：總體方差未知時總體均值的區間估計 對于給定的顯著性水平，總體均值的置信區間為： 2、必要樣本容量的計算公式 樣本量n的大小為：e為抽樣極限誤差必要樣本容量 n與總體方差、抽樣極限誤差，置信水平之間具有下述關系：在其他條件不變的情況下，總體方差越大，必要樣本容量n便越大，必要樣本容量與總體方差成正比；

32、置信水平越大，必要樣本容量便越大，二者成正方向關系；抽樣極限誤差越大，樣本容量就越小，二者成反方向關系。3、總體比例區間估計比例抽樣分布的標準差或標準誤差為： 為總體比例p為抽樣比例比例置信區間是：估計總體比例的必要樣本容量四、各種統計函數count( )COUNTIF( )AVERAGE( )STDEV( )SQRT( )TINV( )NORMSINV( )CEILING( )五、實驗步驟1、利用Excel計算總體均值置信區間例 某工廠想檢驗一批燈泡的質量，抽取10個樣本對其耐用小時進行檢測，結果如下： 1326 1336 1351 1365 1209 1343 1259 1365 1308

33、 1349 試以95%的置信度估計這批燈泡的平均耐用小時。打開“參數估計.xls“工作薄，選擇“均值”工作表。選擇單元格D1，在“插入”菜單中選擇“函數”選項，打開“粘貼函數”對話框。在“函數分類”列表中選擇“統計”，在“函數名”列表中選擇計數函數COUNT。單擊“確定”按鈕，打開計數函數對話框。在value1中輸入數據范圍。單擊A列列頭，或輸入“A:A”，這相當于選擇整個列，包括標題和所有的空單元格。單擊“確定”按鈕。單元格D1中會顯示結果為10，即A列中數據的個數。在單元格D2中輸入公式“=AVERAGE(A:A)”，計算A列的均值，顯示值為1321.1。 在單元格D3中輸入公式“=STD

34、EV(A:A)”，計算A列的標準差，顯示值為50.38397。在單元格D4中輸入公式 “=D3/SQRT(D1)”，計算標準誤差，即標準差除以樣本容量的平方根，D4中顯示。在單元格D5中輸入置信度95%。 在單元格D6中使用TINV函數計算在95%置信度和自由度下的t值。選擇單元格D6，在“插入”菜單中選擇“函數”選項，打開“粘貼函數”對話框。在“函數分類”列表中選擇“統計”，在“函數名”列表中選擇TINV函數。單擊“確定”按鈕，打開TINV函數對話框。在“Probability”中輸入“1D5”，所顯示的值是0.05；在“Deg_freedom"中輸入自由度的表達式，即“D11”，

35、所顯示值是9，單擊“確定”按鈕，單元格D6中顯示值為2.262159。在單元格D7中輸入計算抽樣極限誤差的公式，它是t值和標準誤差的乘積，公式為“=D6*D4”，顯示值為36.04255。在單元格D8和D9中輸入計算置信區間上限和下限的公式，下限為樣本均值減抽樣極限誤差，上限為樣本均值加抽樣極限誤差。其公式分別為“=D2-D7”和“=D2+D7”，顯示值為1285.057和1357.143。這樣，總體均值的95%的置信區間為：置信度越高，下限值越低，上限值越高，置信區間越寬；反之，置信度越低，置信區間越小。2、樣本容量的計算樣本量n的大小為：從上式可以看出，必要樣本容量 n與總體方差、抽樣極限

36、誤差，置信水平之間具有下述關系：在其他條件不變的情況下，總體方差越大，必要樣本容量n便越大，必要樣本容量與總體方差成正比；置信水平越大，必要樣本容量便越大，二者成正方向關系；抽樣極限誤差越大，樣本容量就越小，二者成反方向關系。例 某快餐店想在置信度為96%的條件下估計午餐時間每位顧客的平均支出，根據過去經驗，每個顧客平均支出的標準差不超過5元，要抽取多少樣本才能使其抽樣極限誤差不超過2元呢?打開“參數估計.xls”工作簿，選擇 “樣本容量”工作表；在單元格B1中輸入極限誤差2，在單元格B2中輸入置信度0.98%。96%+(1-96%)/2 在單元格B4中輸入標準差5。單元格B3中需要輸入與B2

37、中置信度相對應的Z值。使用NORNSINV函數，可以把左側概率轉換成Z值。在單元格B3中輸入公式“=NORMSINV(B2)”，計算與B2的置信度相應的左側Z值。顯示對應于96%的置信度的Z值為1.750686。在B5單元格中根據上面樣本容量的計算公式，輸入公式“=(B32*B42)/B12”，計算樣本容量，顯示值為19.15564。在B6單元格輸入“=CEILING(B5,2)”，顯示值為20。3、估計總體比例的必要樣本容量例 聯想集團希望了解購買“天禧”品牌計算機的消費者滿意比例，集團確信“天禧”品牌計算機滿意比例不會小于70%。如果集團想使抽樣極限誤差在±2%，置信度為99%，

38、則需要多大的樣本？打開“參數估計.xls”工作簿，選擇“比例樣本容量”工作表。在單元格B2中輸入P值70%，在單元格B3中輸入置信度99%，在單元格B4中輸入抽樣誤差2%。在單元格B5中輸入計算Z值的公式“=NORMSINV(B3+(1-B3)/2)”或“=ABS(NORMSINV(0.005)”，B5單元格中的計算結果顯示為2.575835。在單元格B6中輸入公式“=(B2*(1-B2)*B52)/B42”，計算必要樣本容量，結果為3483.335。在單元格B7中輸入公式“=CEILING(B6,4)” 用CELLING函數求比n大的最小整數，單元格B6為所要求的整數，4為整數的位數，顯示的

39、結果為3484。抽樣極限誤差對樣本容量有何影響呢?抽樣誤差越小估計就越精確，所以聯想集團希望它越小越好。把抽樣極限誤差由2%改為1%，樣本容量躍增為13934。可見，抽樣誤差減小一半，樣本容量增大為原來的四倍。把P值由70%改為30%。注意到這對n值沒影響把置信度由99%改為90%，這樣樣本容量減少為5682。 實驗3 回歸分析一、實驗目的與要求：掌握相關系數的求解方法，能夠熟練運用回歸分析工具進行一元和多元線性回歸分析，了解單因素方差分析工具的使用。二、實驗主要內容：（1）相關系數的計算（2）單因素方差分析（3）一元線性回歸分析三、統計函數截距函數INTERCEPT 功能：利用已知的 x 值

40、與 y 值計算回歸直線在y 軸的截距。 語法結構： INTERCEPT(known_y's,known_x's)斜率函數SLOPE 功能：返回根據 known_ys 和 known_xs 中的數據點擬合的線性回歸直線的斜率。 語法結構：SLOPE(known_y's,known_x's)測定系數函數RSQ 功能：返回根據 known_ys 和 known_xs 中數 據點計算得出的 Pearson 乘積矩相關系數的平方。 語法結構：RSQ(known_y's,known_x's)估計標準誤差函數STEYX 功能：返回通過線性回歸法計算 y 預測值

41、時所產生的標準誤差。標準誤差用來度量根據單個 x變量計算出的 y 預測值的誤差量。 語法結構：STEYX(known_y's,known_x's)四、實驗步驟1、利用圖表進行回歸分析例 近年來國家教育部決定將各高校的后勤社會化。某從事飲食業的企業家認為這是一個很好的投資機會，他得到十組高校人數與周邊飯店的季銷售額的數據資料，并想根據高校的數據決策其投資規模。 打開“飯店”工作表從“插入”菜單中選擇“圖表”選項，打開“圖表向導”對話框。在“圖表類型”列表中選擇XY散點圖，單擊“下一步”按鈕。在數據區域中輸入B2:C11，選擇“系列產生在列”，單擊“下一步”按鈕。打開“圖例”頁面，

42、取消圖例，省略標題。單擊“完成”按鈕，便得到XY散點圖。用鼠標激活散點圖，把鼠標放在任一數據點上，單擊鼠標右鍵，打開菜單，在菜單欄里選擇“填加趨勢線”選項，打開趨勢線對話框。 打開“類型”頁面，選擇“線性”選項，Excel將顯示一條擬合數據點的直線。 打開“選項”頁面，在對話框下部選擇“顯示公式”和“顯示R平方根”選項，單擊“確定”按鈕，便得到趨勢回歸圖。 2、利用工作表函數進行回歸分析 例 某企業希望確定其產品制造過程中的每月成本支出與產量之間的關系，以制定生產計劃。試根據該企業選擇歷年的產量(噸)和成本支出(千元)的樣本，計算四個回歸分析工作表函數值。 操作過程：打開“簡單線性回歸.xls

43、”工作簿，選擇“成本產量”工作表。在單元格A19、A20、A21和A22中分別輸入“截距b0”、“斜率b1”、“估計標準誤差”和“測定系數” 。在單元格B19中輸入公式：“=INTERCEPT(C2:C15,B2:B15)” ，單擊回車鍵。在單元格B20中輸入公式： “=SLOPE(C2:C15,B2:B15)”，單擊回車鍵。在單元格B21中輸入公式： “=STEYX(C2:C15,B2:B15)”，單擊回車鍵。在單元格B22中輸入公式： “=RSQ(C2:C15,B2:B15)”，單擊回車鍵。3、Excel 回歸分析工具 回歸分析工具是通過對一組觀察值使用“最小平方法”進行直線擬合，以分析一

44、個或幾個自變量對單個因變量的影響方向與影響程度的方法。它是Excel中數據分析工具的一個內容。例 某房地產經紀人從政府部門列舉的地區中隨機抽取了15戶居民作為樣本，記錄了他們的家庭住房面積及其相應的價格，他想確認一下住房面積（平方米）與價格（千元）的關系，并想據此擬合住房價格的回歸方程。 操作過程：打開“簡單線性回歸.xls”工作簿，選擇“住房”工作表。在“工具”菜單中選擇“數據分析”選項，打開“數據分析”對話框。在“分析工具”列表中選擇“回歸”選項，單擊“確定”按鈕，打開“回歸”對話框。在Y值輸入區域中輸入C1:C16。在X值輸入區域中輸入B1:B16。選擇“標志”，置信度選擇95%。在“輸

45、出選項”中選擇“輸出區域”，在其右邊的位置輸入“D1”，單擊 “確定”按鈕。寫出回歸方程。計算結果共分為三個模塊：回歸統計表 、方差分析表、回歸參數 回歸統計表包括以下幾部分內容：§ Multiple R（復相關系數R）：R2的平方根，又稱為相關系數，它用來衡量變量x和y之間相關程度的大小。§ R Square(復測定系數R2 )：用來說明用自變量解釋因變量變差的程度，以測量同因變量y的擬合效果。 Adjusted R Square (調整復測定系數R2)：僅用于多元回歸才有意義，它用于衡量加入獨立變量后模型的擬合程度。當有新的獨立變量加入后，即使這一變量同因變量之間不相關

46、，未經修正的R2也要增大，修正的R2僅用于比較含有同一個因變量的各種模型。§ 標準誤差：又稱為標準回歸誤差或叫估計標準誤差，它用來衡量擬合程度的大小，也用于計算與回歸有關的其他統計量，此值越小，說明擬合程度越好。§ 觀測值：是指用于估計回歸方程的數據的觀測值個數。方差分析表§ 方差分析表的主要作用是通過F檢驗來判斷回歸模型的回歸效果?；貧w參數表圖中，回歸參數如下：§ Intercept：截距0§ 第二、三行：0(截距) 和1(斜率)的各項指標。§ 第二列：回歸系數0(截距)和1(斜率)的值。§ 第三列：回歸系數的標準誤差&#

47、167; 第四列：根據原假設Ho：0=1=0計算的樣本 統計量t的值。§ 第五列：各個回歸系數的p值(雙側)§ 第六列：0和195%的置信區間的上下限。 實驗4 時間數列一、實驗目的和要求：能熟練運用Excel數據分析工具進行長期趨勢的預測，掌握平均發展水平和平均發展速度的計算方法，了解季節變動的分析方法。二、實驗主要內容：（1）平均發展水平和平均發展速度的計算（2）用移動平均法進行長期趨勢預測（3）用指數平滑法進行長期趨勢預測（4）季節變動的分析三、基礎理論知識1、通常把時間數列（Y）分解為以下四種變動：(1)長期趨勢變動（T）(2)季節變動（S） (3)周期波動（C）

48、(4)不規則變動（I）§乘法模型的一般形式為：Y=T×S×C×I 式中 Y、T是總量指標, S、C、I為比率。§加法模型的一般形式為：Y=T+S+C+I式中Y、T、S、C、I都是總量指標。 簡單移動平均公式式中，N 為期數；At-j+1 ：t-j+1期的實際值； Mt+1 ：t+1期的預測值。四、統計函數FORECAST預測函數 功能：根據給定的數據計算或預測未來值。 語法結構：FORECAST(x,known_y's,known_x's)TREND趨勢函數 功能：得到根據最小平方法所得的估計直線上的一組縱坐標值（y 值）。 語

49、法結構：TREND(known_y's,known_x's,new_x's,const) GROWTH增長函數 功能：根據給定的數據擬合指數曲線，并預測指數增長值。 語法結構：GROWTH(known_y's,known_x's,new_x's,const) LINEST線性擬合函數 功能：使用最小平方法對已知數據擬合一個線性模型。 語法結構：LINEST(known_y's,known_x's,const,stats)LOGEST函數 功能：用于進行指數回歸擬合曲線，并返回描述該曲線的數組。 語法結構：LOGEST(known_

50、y's,known_x's,const,stats)五、實驗步驟1、趨勢圖直接預測法例 已知1993年第1季度到1997年第四季度的某地區的季度零售額資料試對1998年的零售額進行預測。操作過程如下： (1)產生“年季”變量打開“時間數列分析與預測.XLS”工作簿，選擇“移動平均”工作表。 在C列選定任一個單元格，選擇“插入”菜單中的“列”選項，則原來C列的內容被移到D列。在C1單元格中輸入標志“年季” ，在C2單元格中輸入公式 “=B2&CHAR(13)&A2” ，再把單元格C2中的公式復制到C3:C21。(2)根據圖中資料繪制銷售額趨勢圖打開“插入”菜單中的

51、“圖表”選項，Excel彈出“圖表向導”對話框。在“圖表類型”列表中選擇“折線圖”選項；在“子圖表類型”列表中選擇“數據點折線圖”。單擊“下一步”按鈕 ，進入向導步驟2對話框。在“圖表數據源”對話框中，在數據區域輸入“C1:D21”，并單擊“下一步”按鈕。在“圖表選項”中，選擇“標題”頁面，在“圖表標題”、“分類（X）軸”、“數值（Y）軸”中分別填入“某地區銷售額趨勢圖”、“季度”和“銷售額（萬元）”。選擇“網格線”頁面，把“數值（Y）軸”下的“主要網格線”設為空。選擇“圖例”頁面，取消圖例顯示。單擊“完成”按鈕，得到趨勢圖。(3)在圖表中插入趨勢線進行預測單擊圖表以激活它，選取垂直軸，雙擊或

52、單擊鼠標右鍵并從快捷菜單中選擇“坐標軸格式”選項，Excel彈出對話框如下圖所示。打開“刻度”頁面，在“最小值”、“最大值”、“主要刻度單位”中分別輸入200、450和50；打開“字體”頁面，設置字號為“8”。單擊“確定”按鈕 。選取水平軸，雙擊或單擊鼠標右鍵并從快捷菜單中選擇“坐標軸格式”選項，Excel彈出對話框。打開“對齊”頁面，取消自動設置；打開“字體”頁面，設置字號為“8”。單擊“確定”按鈕。 選取圖中的折線，單擊鼠標右鍵并從快捷菜單中選擇“添加趨勢線”選項，打開“添加趨勢線”對話框。選擇“類型”頁面，在“趨勢預測/回歸分析類型”框中選擇“移動平均”，設置“周期”為4。單擊“確定”按

53、鈕。2、利用Excel創建公式預測 例 已知1993年第1季度到1997年第四季度的某地區的季度零售額資料，試對1998年的零售額進行預測。操作步驟如下:打開“時間數列分析與預測”工作簿，選擇“移動平均”工作表。在單元格E1中輸入“公式預測值”。在單元格E6中輸入公式“=AVERAGE(D2:D5)”，此處需要相對引用以便復制。把單元格E6中的公式復制到E7:E22各單元格中。3、回歸分析與預測 使用直線函數LINEST和趨勢函數TREND線性預測法 （1） 使用LINEST函數計算回歸統計值例 有某公司99年1至10月份的銷售量資料，試用最小平方法進行趨勢預測，并預測11、12月份的銷售量。

54、 操作步驟如下：打開“時間數列分析與預測.xls”工作簿，選擇“趨勢”工作表。在E1中輸入“回歸統計量”。選擇E2:F6，單擊“粘貼函數”，Excel彈出“粘貼函數”對話框。在“函數分類”中選擇“統計”，在“函數名”中選擇“LINEST”函數，則打開LINEST函數對話框。 在Known_ys、 Known_xs和Stats后分別輸入B2:B11、A2:A11、和1。按住Ctrl+Shift組合鍵，單擊“確定”按鈕。根據圖中的計算結果可以寫出如下估計方程： y=8.86+0.83*x 據此方程計算預測值：在D1單元格中輸入“擬合值”。在D2單元格中輸入公式“=$F$2+$E$2*A2”。將D2單元格中的公式下拉到D3：D11各單元格。作業：預測下一期。（2）使用趨勢函數TREND求預測值在單元格C1中輸入“擬合值”。選定C2：C11