Excel的統(tǒng)計功能及其應用技巧

Excel的統(tǒng)計功能及其應用技巧由“開始”菜單啟動桌面快捷方式啟動“MicrosoftOffice”快捷方式啟動Excel程序的啟動工作簿是計算和儲存數(shù)據(jù)的文件

3個工作表工作表可以對數(shù)據(jù)進行組織和分析表格

256列和65536行組成工作簿和工作表引用就是標識工作表上的單元格或單元格區(qū)域。通過引用，可以在公式中使用工作表不同部分的數(shù)據(jù)，或者在多個公式中使用同一單元格的數(shù)值。單元格引用：B10，A10

區(qū)域引用：C5：C15，A5：B10單元格和區(qū)域引用相對引用：A6＝3×B5；絕對引用：A6＝3×$B$5行絕對引用：A6＝3×B$5列絕對引用：A6＝3×$B5絕對引用與相對引用1、單因素方差分析2、二因素交叉無重復的方差分析3、二因素交叉有重復的方差分析4、描述統(tǒng)計分析5、二樣本方差的F檢驗6、編制頻數(shù)分布表及繪制直方圖7、排位與百分位比排位8、二樣本均數(shù)差數(shù)的u檢驗9、配對資料的t檢驗10、等方差非配對資料的t檢驗11、異方差非配對資料的t檢驗12、多元回歸和直線回歸分析13、計算多個變量二二之間的相關系數(shù)及協(xié)方差14、進行隨機和順序抽樣分析工具庫提供的統(tǒng)計分析方法由“工具”菜單打開“加載宏”命令分析工具庫的安裝數(shù)據(jù)分析工具外觀1、AVERAGE 計算算術平均值2、BINOMDIST 計算二項式分布的概率值3、CHIDIST 計算特定χ2分布的單尾概率值4、CHIINV 計算一定單尾概率值時的χ2臨界值5、CHITEST 計算獨立性檢驗的χ2值6、CONFIDENCE 計算總體平均值的置信區(qū)間7、CORREL 計算兩組數(shù)據(jù)的相關系數(shù)8、COVAR 計算兩組數(shù)據(jù)的協(xié)方差9、FDIST 計算特定F分布的單尾概率值10、FINV 計算一定概率時的臨界F值11、FTEST 計算二個樣本方差之比F值的概率12、GEOMEAN 計算幾何平均數(shù)13、HARMEAN 計算調和平均數(shù)14、INTERCEPT 計算直線回歸的截距15、MAX 計算最大值16、MEDIAN 計算一組給定數(shù)字的中位數(shù)EXCEL電子表格提供的粘帖函數(shù)（一）17、MIN 計算最小值18、MODE 計算一組數(shù)據(jù)的眾數(shù)19、NORMDIST 計算正態(tài)分布的累積函數(shù)20、NORMINV 計算正態(tài)分布累積函數(shù)的逆函數(shù)21、NORMSDIST 計算標準正態(tài)分布的累積函數(shù)22、NORMSINV 計算標準正態(tài)分布累積函數(shù)的逆函數(shù)23、POISSON 計算泊松分布的概率24、SLOPE 計算給定數(shù)據(jù)的直線回歸系數(shù)25、STDEV 計算樣本標準差26、STDEVP 計算樣本總體的標準差27、TDIST 計算學生氏-t分布的概率值28、TINV 計算特定概率時學生氏-t分布的臨界t值29、TTEST 計算t檢驗時的學生氏-t檢驗相關的概率30、VAR 計算樣本的方差31、VARP 計算樣本總體的方差32、ZTEST 計算u-檢驗的雙尾概率值EXCEL電子表格提供的粘帖函數(shù)（二）#####錯誤原因：公式產(chǎn)生的結果太長，單元格容納不下。解決辦法：適當增加列的寬度。#NIV/0！錯誤原因：除數(shù)為零。在公式中，除數(shù)使用了空白單元格或包含零值的單元格引用。解決辦法：修改單元格引用，或在用作除數(shù)的單元格中輸入不為零的值。#N/A錯誤原因：表示在函數(shù)和公式中沒有可用的數(shù)值可以引用。解決辦法：檢查公式中引用的元格的數(shù)據(jù)，并輸入正確數(shù)據(jù)。使用公式和函數(shù)時出現(xiàn)的常見錯誤信息（一）#NAME？錯誤原因：刪除了公式中使用名稱或使用了不存在的名稱以及拼寫錯誤。解決辦法：確認使用的名稱確實存在。#NULL！錯誤原因：使用了不正確的區(qū)域運算或不正確的單元格引用。解決辦法：如果要引用2個不相交的區(qū)域，請使用聯(lián)合運算符（逗號）。例如，=SUM（B2：B5，D2：D5）表示對這2個不相交區(qū)域的引用。#NUM！錯誤原因：在需要數(shù)字參數(shù)的函數(shù)中使用了不能接受的參數(shù)或公式產(chǎn)生的數(shù)字太大或太小，EXCEL不能表示。如在計算臨界t值或F值時，輸入的概率值大于1或小于0。解決辦法：檢查數(shù)字是否超出限定區(qū)域，函數(shù)內的參數(shù)是否正確。使用公式和函數(shù)時出現(xiàn)的常見錯誤信息（二）#REF！錯誤原因：刪除了由其它公式引用的單元格或將移動單元格粘帖到由其它引用的單元格中。解決辦法：檢查引用單元格是否被刪除，或者啟動相應的應用程序。#VALUE！錯誤原因：需要數(shù)字或邏輯值時輸入了文本。解決辦法：確認公式或函數(shù)所需的運算符或參數(shù)正確，并且公式引用的單元格中包含有效的數(shù)值。使用公式和函數(shù)時出現(xiàn)的常見錯誤信息（三）二項分布平均數(shù)、方差和標準差二項分布的概率函數(shù)為二項分布概率的計算實例已知某種豬病的死亡率為30%，現(xiàn)有10頭病豬，如不給予治療，問死亡4頭及死亡4頭和4頭以下的概率為多少？死亡4頭的概率計算公式為：死亡4頭和4頭以下概率的計算公式為：用粘帖函數(shù)BINOMDIST計算死亡4頭的概率計算本計算在編緝欄中為BINOMDIST（4，10，0.30，F(xiàn)ALSE）

死亡4頭和4頭以下概率的計算本計算在編緝欄中為BINOMDIST（4，10，0.30，TRUE）

普哇松分布

普哇松分布的概率函數(shù)

為常數(shù)，它等于平均數(shù)等于方差

==2

普哇松分布概率的計算實例

已知某一地區(qū)，出現(xiàn)怪胎的事件服從普哇松分布P（2），請計算該地區(qū)出現(xiàn)3次怪胎的概率，及出現(xiàn)3次和3次以下怪胎的概率為多少？

出現(xiàn)3次怪胎概率的公式為：出現(xiàn)3次和3次以下怪胎的概率計算公式為：用POISSON粘帖函數(shù)計算出現(xiàn)3次怪胎概率的計算本計算在編緝欄中顯示POISSON(3，2，F(xiàn)ALSE)出現(xiàn)3次和3次以下怪胎的概率計算本計算編緝欄中顯示POISSON(3，2，TRUE)

正態(tài)分布

用符號N（，2）

表示正態(tài)分布的概率函數(shù)為：NORMDIST粘帖函數(shù)：計算累積函數(shù)NORMINV粘帖函數(shù)：計算逆函數(shù)已知某品種成年豬體重的總體平均數(shù)=100kg，總體標準差=20kg。試計算成年豬體重在70kg以下的概率。計算公式如下：

用正態(tài)分布粘帖函數(shù)的計算用NORMDIST粘帖函數(shù)計算概率

本計算在編緝欄中的形式為NORMDIST(70，100，20，TRUE)用NORMDIST粘帖函數(shù)計算函數(shù)值

本計算在編緝欄中的形式為NORMDIST(70，100，20，F(xiàn)ALSE)已知某品種成年豬體重的總體平均數(shù)=100kg，總體標準差=20kg。如果我們希望淘汰30%體重最輕的豬，問體重在多少kg以下的豬應給予淘汰。計算公式如下：

用NORMINV粘貼函數(shù)計算

本計算在編緝欄中的形式為NORMINV(0.30，100，20)標準正態(tài)分布

用符號N（0，1）

表示標準正態(tài)分布的概率函數(shù)為：標準化的公式為：利用標準正態(tài)分布函數(shù)計算概率

NORMSDIST粘貼函數(shù)：計算累積函數(shù)NORMSINV粘貼函數(shù)：計算逆函數(shù)已知某品種成年豬體重的總體平均數(shù)=100kg，總體標準差=20kg。試計算成年豬體重在70kg以下的概率。首先對70kg進行標準化，得u值等于-1.5，計算公式如下：

用NORMSDIST粘帖函數(shù)計算概率

本例在編緝欄中的形式是NORMSDIST(-1.5)

當給定一尾概率值時，求其臨界值就得利用NORMSINV函數(shù)。假定現(xiàn)要計算當一尾概率為0.025時，計算公式如下：用NORMSINV粘貼函數(shù)計算

本例在編緝欄中的形式是NORMSINV(0.025)學生氏t分布

t分布的概率密度函數(shù)為

學生氏t分布的計算實例

TDIST粘貼函數(shù)：單尾或雙尾概率

TINV粘貼函數(shù)：臨界t值（二尾概率）用TDIST粘貼函數(shù)計算二尾概率

本計算在編緝欄中輸入TDIST（2，60，2）例如現(xiàn)要計算自由度等于60，t值與平均數(shù)相差2以上的2尾概率

用TINV粘貼函數(shù)計算臨界值

請計算自由度為10且二尾概率為0.05時的臨界t值

本計算在編緝欄中為TINV（0.05，10）卡方分布

卡方分布概率密度函數(shù)為：

卡方分布的計算實例

CHIDIST粘貼函數(shù)：計算單尾概率

CHIINV粘貼函數(shù)：計算臨界值

CHIDIST函數(shù)積分公式為：

CHIINV函數(shù)積分公式為：

用CHIDIST粘貼函數(shù)計算概率

請計算自由度等于1及卡方值等于3.84時的一尾概率

本計算在編緝欄中為CHIDIST（3.84，1）用CHIINV粘貼函數(shù)計算臨界值

請計算自由度為10及一尾概率為0.01時的2臨界值

本計算在編緝欄中為CHIINV（0.01，10）F分布

F分布的概率密度函數(shù)為：F>0F分布的計算實例

FDIST粘貼函數(shù)：計算單尾概率FINV粘貼函數(shù)：計算臨界值

FDIST函數(shù)積分公式為：

FINV函數(shù)積分公式為：用函數(shù)FDIST計算一尾概率

請計算第一自由度等于2、第二自由度等于4及F值等于18時的一尾概率

本計算在編緝欄中為FDIST（18，2，4）用函數(shù)FINV計算臨界F值

請計算df1=3，df2=10及一尾概率為0.05時的臨界F值

本計算在編緝欄中為FINV（0.05，3，10）EXCEL電子表格的模擬運算功能模擬運算表是工作表中的一個單元格區(qū)域，它可以顯示公式中某些值的變化對計算結果的影響。模擬運算表為同時求解某一運算中所有可能的變化值的組合提供了捷徑，并且還可以將所有不同的計算結果同時顯示在工作表中，便于查找和比較。模擬運算表有兩種類型：單變量模擬運算表雙變量模擬運算表

利用模擬運算計算臨界t值表

單變量模擬計算結果利用模擬運算計算臨界F值表

雙變量模擬計算結果次數(shù)分布表

直方圖對話框輸入格式圖示

接收頻率累積%接收頻率累積%15.942.00%71.93517.50%23.965.00%79.92831.50%31.999.50%63.92644.50%39.91014.50%87.92155.00%47.91321.00%55.91763.50%55.91729.50%95.91671.50%63.92642.50%47.91378.00%71.93560.00%39.91083.00%79.92874.00%31.9987.50%87.92184.50%103.9891.50%95.91692.50%23.9694.50%103.9896.50%15.9496.50%111.9498.50%111.9498.50%119.93100.00%119.93100.00%其他0100.00%其他0100.00%次數(shù)分布表、柏拉圖表及其累積頻率

次數(shù)分布圖（直方圖）

離散型數(shù)據(jù)

白黑花白黑花白黑白白白花白白花白白花白黑白白黑白白黑白白黑花白白花白白白白黑白白黑花白黑花白黑花白白白白白白白黑花白黑花白黑花白黑花白黑花白黑花雜一代白毛黑斑豬自交后的毛色分離情況

將白、黑和花分別轉換成1、2和3，然后利用直方圖工具求出頻數(shù)分布表，再將1、2和3恢復成白、黑和花就得到了這三種毛色豬的次數(shù)分布表，其結果為白、黑和花三種毛色豬的次數(shù)分別為39、17和16頭

描述統(tǒng)計分析描述統(tǒng)計分析的基本統(tǒng)計量（一）算術平均數(shù)中位數(shù)樣本方差描述統(tǒng)計分析的基本統(tǒng)計量（二）樣本標準差標準誤偏斜度偏斜度=0，分布對稱，偏斜度為正值，分布正偏，即眾數(shù)位于算術平均數(shù)的左側，偏斜度為負值，分布負偏，即眾數(shù)位于算術平均數(shù)的右側描述統(tǒng)計分析的基本統(tǒng)計量（三）峰值（峭度）置信半徑峰值=0，正態(tài)分布，峰值為正值，曲線過于陡峭，峰值為負值，曲線過于平坦描述統(tǒng)計分析對話框200頭大白母豬仔豬一月窩重的描述統(tǒng)計分析結果樣本均數(shù)與總體均數(shù)差異顯著性檢驗t檢驗無效假設為Ho：o備擇假設為HA：o計算公式如下：根據(jù)以上公式可導出以下結論：由此可知，當樣本平均數(shù)落在已知的總體均數(shù)置信概率為（1-）的置信區(qū)間以外時，就表明在顯著水平時差異顯著已知約克夏母豬體重的總體平均數(shù)o=130kg，現(xiàn)隨機抽測10頭母豬的體重，數(shù)據(jù)如下：121、127、103、132、157、133、130、139、140、136（kg），試檢驗該樣本是否來自總體均數(shù)為130kg的總體。顯著性檢驗計算結果均數(shù)差異的顯著性檢驗等方差假設時的成組資料t檢驗異方差假設時的成組資料t檢驗成對資料的t檢驗等方差假設時的成組資料t檢驗檢驗的基本公式現(xiàn)隨機抽測8頭大白豬與8頭哈白豬經(jīng)產(chǎn)母豬產(chǎn)仔數(shù)資料如下（單位：頭）：異方差假設時的成組資料t檢驗檢驗的基本公式雙樣本等方差假設t檢驗結果現(xiàn)隨機抽測8頭大白豬與8頭哈白豬經(jīng)產(chǎn)母豬產(chǎn)仔數(shù)資料如下（單位：頭）：雙樣本異方差假設t檢驗結果成對資料的t檢驗檢驗的基本公式現(xiàn)用國產(chǎn)與進口的膘厚測定儀，對14頭肥豬進行了測定（單位：mm），數(shù)據(jù)如下：試檢驗兩種儀器測定的結果有無顯著差異？成對資料的t檢驗結果方差分析單因素方差分析二因素交叉無重復方差分析二因素交叉有重復方差分析單因素方差分析具有k組每組n個觀察值的數(shù)據(jù)模式SST=SSA+SSedfT=dfA+dfe單因素方差分析實例現(xiàn)有5個不同品種的若干頭母豬的窩產(chǎn)仔數(shù)資料見下表，試檢驗不同品種母豬平均窩產(chǎn)仔數(shù)的差異顯著性。五個不同品種母豬產(chǎn)仔數(shù)的方差分析表基本公式單因素方差分析表單因素方差分析示意圖二因素交叉無重復方差分析二因素交叉無重復觀察值的資料模式SST=SSA+SSB+SSedfT=dfA+dfB+dfe平方和及自由度的簡易計算公式如下：二因素交叉無重復觀察值的方差分析表二因素交叉無重復觀察值的方差分析實例無重復雙因素方差分析品種與飼料對豬增重的方差分析表二因素交叉有重復方差分析二因素交叉有重復觀察值的資料模式平方和及自由度的簡易計算公式如下：二因素交叉無重復觀察值的方差分析表二因素交叉有重復觀察值的方差分析實例為了考察飼料中鈣和磷的含量對幼豬生長發(fā)育的影響，將鈣（A）和磷（B）在飼料中的含量各分為4個水平進行試驗，每個水平組合3頭豬，經(jīng)2個月飼養(yǎng)試驗，得幼豬增重結果列于下表。試分析鈣和磷及它們之間的交互作用對幼豬生長發(fā)育的影響。不同鈣磷用量的試驗豬增重結果（單位：kg）可重復雙因素分析示意圖不同鈣磷用量試驗豬增重結果的方差分析回歸分析基本公式直線回歸方程的顯著性檢驗回歸方程顯著性檢驗的方差分析表回歸系數(shù)和回歸截距的顯著性檢驗95%回歸系數(shù)的置信區(qū)間為：95%回歸截距的置信區(qū)間為：現(xiàn)有10只綿羊的胸圍（x，cm）和體重（y，kg）的數(shù)據(jù)下表，請進行回歸分析。直線回歸計算實例復相關分析（回歸統(tǒng)計）表上式中當方程中有截距時i取值為1，否則為0回歸方程的方差分析回歸系數(shù)和回歸截距的顯著性檢驗表多元回歸分析多元回歸分析的矩陣表示多元回歸方程的顯著性檢驗多元回歸方程顯著性檢驗的方差分析表偏回歸系數(shù)和回歸常數(shù)的顯著性檢驗偏回歸系數(shù)及常數(shù)項的95%置信區(qū)間為多元回歸計算實例現(xiàn)有某豬場50頭肥豬5項胴體性狀資料的數(shù)據(jù)列于下表，其中瘦肉量依變量y，眼肌面積為x1、腿肉量為x2、腰肉量為x3、椎骨數(shù)為x4，擬配合估計瘦肉量的最優(yōu)回歸方程。多元回歸分析對話框示意圖復相關分析（回歸統(tǒng)計）表多元回歸方程的方差分析偏回歸系數(shù)和回歸常數(shù)的顯著性檢驗表相關系數(shù)與協(xié)方差的計算簡單相關系數(shù)的基本公式

簡單相關系數(shù)的計算實例

用多元回歸的例題簡單相關系數(shù)計算結果

瘦肉量(kg)眼肌面積(cm2)腿肉量(kg)腰肉量(kg)椎骨數(shù)(個）瘦肉量(kg)1

眼肌面積(cm2)0.5123221

腿肉量(kg)0.8746340.4576281

腰肉量(kg)0.6969870.3117540.5285191

椎骨數(shù)(個）0.1806670.451630.087310.0107831協(xié)方差的計算

協(xié)方差的基本公式

總體協(xié)方差

樣本協(xié)方差

粘帖函數(shù)（COVAR函數(shù)）：總體協(xié)方差協(xié)方差分析工具法：樣本協(xié)方差分析

協(xié)方差的計算實例

用多元回歸的例題協(xié)方差計算結果

瘦肉量(kg)眼肌面積(cm2)腿肉量(kg)腰肉量(kg)椎骨數(shù)(個）瘦肉量(kg)1.35267984

眼肌面積(cm2)2.3229806515.1988131

腿肉量(kg)0.435685310.764129180.18344184

腰肉量(kg)0.188994040.283362610.052775920.05435657

椎骨數(shù)(個）0.204693881.715204080.036428570.002448980.94897959偏相關系數(shù)的計算

任意級偏相關系數(shù)的基本公式如下：

上式計算所得的偏相關系數(shù)是（n-2）級的，即n個變量中有（n-2）個保持不變，其中Cij是簡單相關系數(shù)矩陣的逆矩陣中第i行第j列的元素，Cii和Cjj逆矩陣主對角線上第i和第j個元素。偏相關系數(shù)下標中圓點前的下標表示相關的2個變量，圓點后的下標表示保持不變的變量。

偏相關系數(shù)的計算實例

多元回歸分析的例題用粘帖函數(shù)MINVERSE計算出相關系數(shù)矩陣的逆矩陣

簡單相關系數(shù)矩陣的逆矩陣

瘦肉量(kg)眼肌面積(cm2)腿肉量(kg)腰肉量(kg)椎骨數(shù)(個）瘦肉量(kg)7.0275-0.4698-4.6751-2.2740-0.6248眼肌面積(cm2)-0.46981.6711-0.2760-0.0407-0.6453腿肉量(kg)-4.6751-0.27604.72190.84300.5479腰肉量(kg)-2.2740-0.04070.84302.14850.3325椎骨數(shù)(個）-0.6248-0.64530.54790.33251.3529協(xié)方差矩陣的逆矩陣

瘦肉量(kg)眼肌面積(cm2)腿肉量(kg)腰肉量(kg)椎骨數(shù)(個）瘦肉量(kg)5.1953-0.1036-9.3853-8.3863-0.5514眼肌面積(cm2)-0.10360.1100-0.1653-0.0448-0.1699腿肉量(kg)-9.3853-0.165325.74088.44211.3132腰肉量(kg)-8.3863-0.04488.442139.52651.4638椎骨數(shù)(個）-0.5514-0.16991.31321.46381.4256單因素一元協(xié)方差分析

各組平方和的計算公式為：

乘積和計算的基本公式如下：

， ， ， 或 協(xié)方差分析簡表

校正增重的方差分析變因dfSSxSSySPxybyxdf′SS′MS′F總變異31691381889428156

307427.65

組間3675523183880

組內286238316576242750.389277129.13264.04

校正處理間3298.5299.510.377

回歸系數(shù)的計算公式校正自由度的計算公式總校正自由度組內校正自由度組間校正自由度或校正平方和的計算公式為

總校正平方和組內校正平方和組間校正平方和校正均方的計算公式

組間校正均方

組內校正均方F檢驗公式為協(xié)方差分析簡表的構建

單元格輸入形式G3=B3-1G5=B5-1G6=G3-G5H3=D3-（E3E3）C3H5=D5-（E5E5）C5H6=H3-H5I5=H5G5I6=H6G6J6=I6I5F5=E5C5寫入公式后的協(xié)方差分析簡表

協(xié)方差分析實例

在研究3種不同飼料對豬的增重效果時，用24頭始重不一樣的幼豬分為3組，每組8頭進行試驗，各組的增重結果見下表。

分組變量原始數(shù)據(jù)Ix1513111212161417

y8583657680918490IIx1716181821221918

y9790100951031069994IIIx2224202325273032

y89918395100102105110原始數(shù)據(jù)經(jīng)計算后所得的組平均數(shù)和總和

分組變量組平均數(shù)組總和Ix13.75110

y81.75654IIx18.625149

y98784IIIx25.375203

y96.875775原始數(shù)據(jù)經(jīng)計算后所得的平方和和乘積和

變因自由度x平方和y平方和乘積和總的23720.52555.9581080.75組間2545.251317.583659.875組內21175.251238.375420.875各項乘積和的計算

根據(jù)乘積公式進行計算；計算相關系數(shù)，然后除以二個變量平方和的幾何平均數(shù)；計算樣本協(xié)方差，然后用自由度與樣本協(xié)方差相乘；計算總體協(xié)方差，然后用樣本含量與總體協(xié)方差相乘各項乘積和的計算，有以下幾種方法：計算總體協(xié)方差，然后用樣本含量與總體協(xié)方差相乘分組Array1Array2協(xié)方差樣本含量乘積和IA1：A8B1：B813.81258110.5IIA9：A16B9：B168.125865IIIA17：