統計學原理實驗指導Excel在統計學中的應用中北大學經濟與管理學院實驗一 EXCEL的基本統計功能 Microsoft EXCEL是一個設計精良、功能齊全的辦公軟件。它除了具有我們常用的辦公功能，如通過電子表格的形式對數字數據進行組織和計算；將數字數據轉化為可視化的圖表和數據庫管理功能外。它還是一個十分強大而且非常易用于使用數據統計和預測工具。EXCEL的統計功能分為基本統計和預測兩部分。我們將按照統計順序逐一介紹。這一節將結合實例，重點介紹EXCEL2000的基本統計功能。EXCEL2000提供的基本統計主要包括描述性統計、頻數統計、等級和百分數等方法。這些統計方法主要利用EXCEL統計函數或數據分析中的描述統計過程來實現。一、數據的篩選與排序圖1是一份學生成績登記表，我們就以此為例，看看excel是如何篩選出我們需要的數據。 一、自動篩選 目的：將語文成績“大于等于90分”的學生和語文成績“小于60分”的學生篩選出來。 實現過程如下。 1、選中數據表中任意一個單元格，執行“數據篩選自動篩選”命令，此時，每一項列標題右側均出現一個下拉按鈕（參見圖1）。2、單擊“語文”右側的下拉按鈕，在隨后彈出的快捷菜單中（參見圖1），選“自定義”選項，打開“自定義自動篩選方式”對話框（如圖2）。 3、單擊左上方框右側的下拉按鈕，在隨后出現的對話框中，選“大于或等于”選項，然后在右上方框中輸入數值“90”；選中“或”選項，再單擊左下方框右側的下拉按鈕，在隨后出現的快捷菜單中，選“小于”選項，然后在右下方框中輸入數值“60”（參見圖2）。 4、設置好以后，單擊“確定”按鈕，符合條件的數據即刻被篩選出來（篩選的結果如圖3所示）。 提示： 在上述篩選結果的基礎上，對“數學”列繼續進行篩選操作，將篩選條件設置為“大于或等于”“90”，就可將“語文”和“數學”成績都“大于或等于90”分的學生篩選出來。 二、高級篩選 目的：將“語文”或“數學”成績“大于或等于”“90”分的學生篩選出來。 實現過程如下。 1、先執行“數據篩選自動篩選”命令，撤銷“自動篩選”功能。 如果前面沒有進行“自動篩選”操作，則此步可以省略。 2、分別選中D66、D67、E66、E68單元格，輸入內容：語文、=90、數學、=90（如圖4）。 3、選中成績表中任意單元格，執行“數據篩選高級篩選”命令，打開“高級篩選”對話框（如圖5）。 4、選中“將篩選結果復制到其他位置”選項。 提示： 也可以不選擇此選項，但為了保留原有數據，建議最好將篩選結果復制保存到其他位置上。 5、單擊“條件區域”右側的紅色按鈕，此時，對話框縮小成一個浮動狀態條（如圖6），用鼠標選中D66至E68條件區域，然后按浮動狀態條右側的紅色按鈕，返回“高級篩選”對話框。 6、單擊“復制到”右側的紅色按鈕，此時，對話框縮小成一個浮動狀態條（如圖7），用鼠標選中保存篩選結果的第1個單元格（如A69），然后按浮動狀態條右側的紅色按鈕，返回“高級篩選”對話框。 提示：“數據區域”系統通常按默認區域自動添加，如果需要更改，仿照上面操作就行了。 7、全部設置完成后，單擊“確定”按鈕，符合條件的數據就被篩選出來了。 提示： 如果把“條件區域”改成如圖8所示的樣式（即，條件數值位于同一行中），然后執行上述篩選制度操作，則只將“語文”和“數學”成績都“大于或等于”“90”分的學生篩選了出來。 三、排序通過排序，可以根據某特定列的內容來重排數據清單中的行。例如，假設一包含銷售數據的數據清單有銷售員、單位數量和訂單日期等列。我們可以用“排序”命令按銷售員重排數據清單、從高到低組織單位數量或從早到晚排好訂單日期。當選擇“排序”命令后，Microsoft Excel 會利用該列和我們指定的排序次序，或利用自定義排序次序來重新排列行、列或單個的單元格。除非另有指定，否則Microsoft Excel 會根據我們選擇的“主要關鍵字”列的內容以升序順序（最低到最高）對行作排序。如果我們按一列以上作排序，主要列中有完全相同項的行會根據我們指定的第二列作排序。第二列中有完全相同項的行會根據我們指定的第三列作排序。提示：排序操作不僅僅適用于數據庫，對于我們在工作表中的任意選定范圍，我們都可以執行排序操作。12.3.1 按列排序按照某一選定列排序的操作步驟如下： (1) 執行“數據”菜單上的“排序”命令，出現一個如圖12-6所示的對話框。 (3) 在“主要關鍵字”列表框中，選定重排數據清單的主要列，在本例中我們選定“地區”。選定“遞增”或“遞減”選項按鈕以指定該列值的排序次序，在本例中選定“遞增”。若要由一列以上來作排序，在“次要關鍵字”和“第三關鍵字”框中，選定您想用作排序的附加列。對于每一列再選定“遞增”或“遞減”選項按鈕。如果在數據清單中的第一行包含列標記，在“當前數據清單”框中選定“有標題行”選項按鈕，以使該行排除在排序之外，或選定“沒有標題行”使該行也被排序。小技巧：按行排列數據相信大家對Excel的排序功能都不陌生，通常數據只是按列排列。有時我們需要按行排列數據，這時只需從“數據”菜單中選擇“排序”，在排序對話框中左下角選中“選項”按鈕，在彈出的對話框中選擇“按行排序”，并設置相應關鍵字即可，而且還可以更改排序的方法是按拼音還是按筆畫排序或是選擇自定義序列中的排序方法。二、統計圖表（一）利用 Excel 對原始數據進行分組并得到頻數分布和直方圖 例3-1：某車間工人進行技能測試成績分別為下表： （單位：分）57894984868775737268758297816781547987957671609065767270868589896457838178877261要注意的是： Excel 在統計各組頻數時，是按“各組上限在本組內”的原則處理的（如本例中， 59 分及 59 分以下都歸入第一組，其余數據中 69 分以及 69 分以下都歸入第二組，這樣得到的頻數才相當于統計習慣上所指的“ 60 以下”、“ 60 一 70 ”的頻數，依此類推）。具體步驟如下：第一步，選擇“工具”菜單欄中的“數據分析”第二步，在“數據分析”菜單選擇“直方圖”命令。第三步， 在“直方圖”對話框 “輸入區域”數值框中輸入原始數據所在區域（例中為 Al : J4 ，這里的單元格引用使用絕對引用或相對引用均可。相對引用：當把公式復制到其它單元格中時，行或列的引用會改變。所謂行或列的引用會改變，即指代表行的數字和代表列的字母會根據實際的偏移量相應改變。 絕對引用：當把公式復制到其它單元格中時，行和列的引用不會改變。 混合引用：行或列中有一個是相對引用，另一個是絕對引用。），在 “接收區域”數值框中輸入指定的分組上限值所在區域（本例為 A8 : A12 。如果將數據名稱所在單元格也包括在輸入和接受區域中，就應2選中標志復選框。（本例未包括，此處不需要選中單元格。在“輸出區域”數值框中制定頻數分布輸出區域的起點單元格（本例中為 C7 ) ；選中“圖表輸出”（否則只輸出頻數分布而不顯示直方圖）復選框，如圖 3 一 7 所示；單擊“確定”按鈕即可得到次數分布表和直方圖，如圖3-8所示。 在頻數分布表基礎上，也可以進一步計算出各組頻率（比重）及向上累計頻數和頻率等。如本例中，可先在單元格 D13 中單擊“自動求和”按鈕或輸入公式“ = SUM ( DS : D12 ) ”得到工人總數，然后在單元格 E7 中輸入“比重（ % ) ，在單元格 E8 中輸入公式“ = D8 / $ D $ 13 * 100 ，按 Enter鍵后利用鼠標拖動的方法將單元格 E8復制到單元格 E9 至 E13 中，即可得到各組比重。在單元格 F7 中輸入“向上累計人數”，在單元格 F8中輸入公式“ = D8 ，在單元格 F9 中輸入公式“ = D9 + F8 ，按 Enter 鍵后將單元格 F9 復制到單元格 F10 至 F12 中，即可得到各組向上累計人數。在單元格 G7 中輸入“向上累計頻率夕，在單元格 G8 中輸入公式“ = F8 / $ DS13 * 100 ，按 Enter鍵后將單元格 G9 復制到單元格 G12 至 G12 中，可得到各組的向上累計頻率。此外，直方圖中的曲線代表各組累積的百分比。（二)、利用 Excel 繪制其他統計圖在統計整理中常用的統計圖有柱形圖或條形圖、折線圖、餅圖、雷達圖等。利用 Excel 的圖表向導可以很方便地繪制所需統計圖。限于篇幅，下面僅說明折線圖和餅狀圖的繪制方法。1、 利用 Excel 繪制折線圖以例 3 一 3 中我國 1991 一 2005 年國內生產總值的數據來說明折線圖（如圖 3 一 4 所示）的制作過程。首先輸入數據。在工作表的 Al 中輸入“年份”，在 Bl 中輸入“國內生產總值”，在 A2 一 A16 中分別輸入 1991 一 2005 （也可只輸入前兩個數，再用自動填充功能產生年份序列），在 B2 一 B16 中分別輸入各年國內生產總值的數值。例 3 一 3 我國 1991 一 2005 年國內生產總值表年份國內生產總值年份國內生產總值19912178119998967719922692320009921519933533420011096551994481982002120333199560794200313582319967117720041598781997789732005183085199884402然后選擇“插入”一“圖表”命令（或單擊工具欄中的“圖表向導”按鈕），在“標準類型”中選擇“折線圖”（再選擇其中一種子圖表類型，本例中選擇帶點的折線圖）命令。 單擊“下一步”按鈕，在“數據區域”欄中輸入縱坐標數據所在單元格區域（本例中輸入“ Bl : B16 ，也可以單擊輸入欄尾端的小圖表，再用鼠標選定該數據區域。這里把數據名稱所在單元格包括在內，可自動生成名為“國內生產總值”的圖表標題和圖例，若指定數據區域為 B2 : B16 ，則自動生成的圖例名為“系列 1 ) ；選擇“系列”選項卡，在“分類（ X ）軸標志”欄中輸入年份數據的區域“ AZ : A16 （若不指定此選項，橫坐標采用自然數自動編號）。單擊“下一步”按鈕，在“標題”、“數據標識”、“網格線”等選項卡中按需要作出相應選擇（也可以忽略這些選擇，直接單擊“完成”按鈕）。單擊“下一步”按鈕指定圖表的輸出位置（默認位置是當前工作表，所以通常也可以忽略該項選擇），最后單擊“完成”按鈕即可。2、 利用 Excel 繪制餅狀圖餅狀圖也簡稱餅圖，可以根據各組比重來繪制，也可以直接根據各組總量指標來繪制（ Excel 會自動計算出比重來繪圖，并在圖中顯示各組比重）。下面以表 3 一 6 中 2006 年 1 一 3 季度我國國內生產總值的按三次產業劃分的數據來說明餅狀圖的制作。首先輸入數據。在工作表的 A2 一 A4 中分別輸入“第一產業”、“第二產業”和“第三產業”字樣，在 Bl 中輸入“絕對額”字樣，在 B2 一 A4 中分別輸入相應的指標數值。然后選擇“插入” ? “圖表”命令，在“標準類型”中選擇“餅圖”（再任選一個子圖表類型）命令；單擊“下一步”按鈕，在“數據區域”欄中輸入數值所在單元格區域（本例中輸入 BZ : B4 ，也可以用鼠標選定該數據區域） ; 選擇“系列”選項卡，在“分類（ X ）軸標志”欄中輸入各組名稱所在區域，以便在餅圖中顯示各組（類別）名稱，本例中輸入“ AZ : A4 ”。若不指定此選項，則各組名稱采用自然數自動編號代替。單擊“下一步”按鈕，可在“數據標志”選項卡中選擇希望在餅圖中顯示的內容。本例選擇了“類別名稱”、“值”和“百分比”，若“圖例”選項卡中選擇了顯示圖例，就不必選“類別名稱”。單擊“完成”按鈕即可得出所需的餅圖，如圖 3 一 6 所示。22實驗二 集中指標以下利用Excel提供的函數，計算算術平均數、幾何、調和、中位數、眾數等集中指標。學會使用Excel計算各種數字特征，要求獨立完成相關作業。一、由未分組數據計算分布特征的有關指標（一）用EXCEL統計函數進行特征值計算 EXCEL描述性統計函數主要包括一般統計函數，集中趨勢函數和變異統計函數：如圖35所示，單元格區域B4:B53是50名工人某日加工零件個數資料（例32）。C4:C19是一些描述性統計量的說明。D4:D19是一般統計結果。其做法有如下兩種。1在單元格D3中輸入公式“=COUNT （B4:B53）”并回車，得到B4:B53區域中非空數值型數據的個數統計；在單元格D4中輸入公式“=SUM（B3:B53）”并回車，得到50名工人日加工零件數的總和；同樣，在D5:D15單元格中中分別輸入MAX、MIN、AVERAGE、MEDIAN、GEOMEAN、HARMEAN、AVEDEV、STDEV、VAR、KURT和SKEW 函數，分別得到50個數據中的最大值、最小值、平均值、中位數、幾何平均數和調和平均數及變異統計的平均差、標準差、方差峰度和偏度。圖35 統計函數2首先在EXCEL2000的系統工具欄中選擇“插入”中的函數，其次在函數對話框中選擇所計算的函數，然后根據函數向導提示一步步的完成。（二）宏程序進行特征值計算除了利用上述統計函數完成統計數據分析外，EXCEL還在數據分析宏程序中提供了一個描述性統計過程。對于例32，我們也可以利用這個“描述性統計”宏過程來計算，其方法更為簡單。我們點擊圖中的“描述性統計”過程，“描述性統計”過程的菜單如圖36A，36B，36C 所示。我們在“輸入區域”中輸入數據所在單元格區域“B3:B53”，選擇輸出“匯總統計”和“平均數值信度”，在“K個最大值”和“K個最小值”選擇中，選擇系統默認值“1”，表示選擇輸出第1個最大值和第1個最小值。輸入“輸出區域”為E1單元格，然后按“確定”，即得到圖所示特征值計算結果，該結果與圖中利用統計函數計算的結果是一致的。圖36A 數據分析宏程序 圖36B 描述性統計過程對話框圖3-6C描述性統計結果二、由分組數據計算算術平均數指標對于分組數據的有關指標，只能用 Excel 的公式與復制功能來實現。下面就以圖 5 一 4 的數據來說明有關指標的具體計算操作。成績（分）學生（人數）f 組中值xxf60 分以下65533060-702265143070-802675195080-902185178590-100495380合計79-5875算術平均數74.36708861 ( l ）輸入數據，如圖 5 一 4 的 A 、 B 、 C 列所示。 ( 2 ）算術平均數的計算：在單元格 DZ 中輸入公式“ = C2* B2 ，按 Enter 鍵后將單元格 D2 的公式向下復制到 D6 ，在單元格 D7 中輸入公式“ = SUM ( D 2 : D6 ) （或單擊自動求和圖標即可），按 Enter 鍵后計算算術平均數所需的分子的數值（本例中為 5875 ) 就顯示在單元格 D7 中。在單元格 A9 中輸入“算術平均數”，在單元格 B9 中輸入公式 = D7 /B 7 ，按 Enier 鍵后單元格 B9 中顯示的數值（ 74 . 36709 ）就是所求的算術平均數。對于眾數、中位數等統計指標的計算，同樣只需要將相應的計算公式在 Exeel 中實現即可。補存：分組數據中位數的計算例：某地區農民家庭按人均收入分組的分組數據資料如下：收入（元）頻率（%）01001002002003003004004005005006006007002.313.719.715.215.12014計算家庭人均收入的中位數，均值，標準差。2、操作步驟（如圖2-1所示）（1）繪制計算表框架，且輸入分組數據：分組，頻率，組限。（2）用各種常用公式或函數在計算表中計算其他各欄。公式如下：累積頻率：（略）；組中值：(D4D10+E4E10)/2組距：= E4E10 - D4D10xifi：=F4F10*B4B10xi2fi：=POWER（F4F10，2）*B4B103.計算中位數，均值，標準差。公式如下：中位數：D7+(B11/2-C6)/B7*G7均值：SUM（H4H10）標準差：SQRT（I11-POWER（H11,2）圖2-1 分組資料數字特征計算描述統計結果中： “中值”即“中位數”；“模式”即“眾數”；“區域”即“極差”；“樣本方差”即；“標準偏差”即；“峰值”，EXCEL工作表函數為KURT，其計算公式為：“偏斜度”，EXCEL工作表函數為SKEW，其計算公式為：“置信度”，其計算公式為：圖2-4 “描述統計”計算結果實驗三 變異指標分布與形狀變異指標和集中指標是描述統計的基本指標。例一：仍用上面例子說明變異指標的計算。接上面：以圖 5 一 4 的數據來說明有關變異指標的計算操作。成績（分）學生（人數）f 組中值xxf60 分以下65533060-702265143070-802675195080-902185178590-100495380合計79-5875算術平均數74.36708861 ( 1 ）平均差的計算：在單元格 El 中輸入“加權的離差絕對值”，在單元格 E2 中輸入公式“ = AB S ( C2 一 B $ 9 ) * B2 ，按 Enter 鍵后將單元格 E2 Enter 鍵后在計算平均差所需的分子的數值 ( 644 . 557 ）就顯示在單元格 E7 中。在單元格 A10 中輸入“平均差”，在單元格 B10 中輸入公式“ = E7 / B7 ，按 Enter 鍵后單元格 B10 中顯示的數值（ 8 . 1 58 949 ）就是所求的平均差。 ( 2 ）方差的計算：在單元格 Fl 中輸入“加權的離差平方”，在單元格 F2中輸入公式“=（ c2 一 B $ 9 ）2 * B2 ，按 Enter 鍵后將單元格 F2 的公式向下復制到 F6 ，在單元格 F7 , 中輸入公式“ = SuM ( F2 : F6 ) ，按 Enter 鍵后在計算方差所需的分子的數值（ 8 268 . 3544 ) 顯示在單元格 F7 中。在單元格 All 中輸入“方差”，在單元格 Bll 中輸入公式“ = F7 /B 7 , 按 Enter 鍵即可在單元格 B11 中得到的所求方差的數值（本例中為 1 04 . 6627 ）。 ( 3 ）標準差的計算：由于標準差就等于方差的平方根，所以在單元格 A12 中輸入“標準差”，在單元格 B12 中輸入公式“ = Bll 0 . 5 ”或“ = SQRT ( Bll ) ，按 Enter 鍵即可在單元格 B12 中得到的所求標準差的數值（本例中為 10 . 23048 ）。 例2為了了解小學生每天花在看電視的時間大概有多久，某老師抽樣了20位小學四年級的學生，請他們記錄每天看電視的時間。得到如圖3-3所示的數據（單位：分鐘）。請問（1）這七天的平均數、區域、四分位數、平均絕對離差、標準差、方差各為多少？（2）從這七天的平均數和標準差，你得到什么結論？（3）平均來說（不分假日和非假日），每位學生花多少時間看電視？作法1如圖3-3所示，將所有的數據置于“電視”的工作表內。2Excel提供了函數AVERAGE以計算平均數；MAX計算最大值，MIN計算最小值，兩者相減為區域；QUARTILE計算四分位數；STDEV計算樣本標準差；VAR計算樣本方差。計算第一位學生的各種指標，該學生的數據放于B2:H2中，在適當地址如I2中鍵入“=AVERAGE（B2:H2）”，即可得到第一位學生收視時間的平均數。在J2中鍵入“=MAX（B2:H2）-MIN(B2:H2)”，即可得到該學生的區域。在K2中鍵入“=QUARTI