excel相关统计分析.xls

《excel相关统计分析.xls》由会员分享，可在线阅读，更多相关《excel相关统计分析.xls（21页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、Statistical functions in EXCEL(About hypothesis testing):ZTESTTDISTTINVCHIDISTCHIINVCHITESTFDISTFINVCONFIDENCECRITBINOMEXPONDISTLINEST*ZTESTZTEST傳回雙尾 z-檢定之 P 值。z-檢定為 x 產生一個相對於 陣列之標準度量，然後傳回常態分配的雙尾機率值。您可以使用本函數來判斷某一觀測值是否由某一特定之母群體中取出。Actaully,it returns the P-value for one sided Z-test in EXCEL!語法ZTEST(

2、陣列,x,sigma)Array 是要檢定相對於 x 之陣列或資料範圍。X 是要檢定之數值。Sigma 是母群體(已知)的標準差。如被省略，則使用樣本標準差。註解如果 陣列是空值，則 ZTEST傳回錯誤值#N/A。ZTEST計算的公式是：範例ZTEST(3,6,7,8,6,5,4,2,1,9,4)等於 0.090574ZTEST(3,6,7,8,6,5,4,2,1,9,4)=0.090574259AVERAGE(3,6,7,8,6,5,4,2,1,9)=5.1STDEV(3,6,7,8,6,5,4,2,1,9)=2.601281735STDEV(3,6,7,8,6,5,4,2,1,9)/SQR

3、T(10)=0.822597512)(1),(nxNORMSDISTxarrayZTESTz0=(A39-4)/A43=1.3372274821-sided p-value=1-NORMSDIST(z0)=1-NORMSDIST(A45)=0.090574259Hence,the result of ZTEST is the 1-sided,not 2-sided,p-value!The 2-sided p-value shall be:2-sided p-value=2*(1-NORMSDIST(z0)=2*(1-NORMSDIST(A45)=0.181148517Actually,t te

4、sts shall be used when sigma is unknown.The correct p-value for a 2-sided t-test forthis example shall bet0=(A39-4)/A43=1.337227482TDIST(t0,9,2)=TDIST(A54,9,2)0.213960113Correct examples for Z-tests are as follows.Assume sigma=2 for this example,then the p-value for 1-sided z-test becomesZTEST(3,6,7

5、,8,6,5,4,2,1,9,4,2)=0.04099512That is,z0=(A39-4)/2*sqrt(10)=1.7392527131-sided p-value=1-NORMSDIST(z0)=1-NORMSDIST(A63)=0.040995122-sided p-value=2*(1-NORMSDIST(z0)=2*(1-NORMSDIST(A63)=0.081990239Suppose H0 is changed to be mu=mu0=6,then the p-value for 1-sided z-test are computed asfollows.ZTEST(3,

6、6,7,8,6,5,4,2,1,9,6,2)=0.922635491ZTEST computes the follows:z0=(A39-6)/2*sqrt(10)=-1.4230249471-NORMSDIST(z0)=1-NORMSDIST(A73)=0.922635491This is the p-value for 1-sided z-test of H0:mu=6 vs.H1:mu6.Since the sample average=5.1,the 1-sided z-test shall use H0:mu=6 vs.H1:mu6 or mu6,the correct answer

7、 is(see(4-22)of P.150 of textbook):2*(1-NORMSDIST(ABS(A73)0.154729018*TDISTTDIST傳回 Student 氏之 t-分配。t-分配是用於小樣本資料組的假設檢定。使用此函數就不用建一個 t-分配的臨界值表格。語法TDIST(x,degrees_freedom,tails)X 是要用來評估分配的數值。Degrees_freedom 是用來指出自由度的整數值。Tails 指定要傳回的分配尾數的個數。如果 tails=1，則 TDIST傳回單尾分配。如果 tails=2，則 TDIST傳回雙尾分配。註解如果引數之中任何一項不是

8、數值，則 TDIST傳回錯誤值#VALUE!。如果 degrees_freedom 1，則 TDIST傳回錯誤值#NUM!。如果 degrees_freedom 與 tails 不是整數，會只取至整數位數。如果 tails 不為 1 或 2，則 TDIST傳回錯誤值#NUM!。TDIST的計算式是 TDIST=p(xX)，而 X 是 t-分配的隨機變數。範例TDIST(1.96,60,2)等於 0.054645TDIST(1.671,60,1)=0.049965152TDIST(1.671,60,2)=0.099930304*TINVTINV傳回指定自由度的 Student 氏的反 t-分配。

9、語法TINV(probability,degrees_freedom)Probability 是一個雙尾 Student 氏 t-分配的機率值。Degrees_freedom 是構成該分配的自由度數目。註解如果引數之中任何一項不是數值，則 TINV傳回錯誤值#VALUE!。如果 probability 1，則 TINV傳回錯誤值#NUM!。如果 degrees_freedom 不是整數，會只取至整數位數。如果 degrees_freedom 1，則 TINV傳回錯誤值#NUM!。TINV的計算式是 TINV=p(tX)，X 是 t-分配的隨機變數。TINV使用反覆運算之技巧來計算此函數。從給予

10、的機率值開始，TINV反覆計算至誤差值在3x10-7 以內為止。如果 TINV在反覆計算 100 次後仍不收斂，則傳回錯誤值#N/A。範例TINV(0.054645,60)等於 1.96TINV(0.05,60)=2.000297172TINV(0.1,60)=1.670648544*CHIDISTCHIDIST傳回單尾卡方分配的機率值。c2 分配與 c2 測試有關。c2 是用來比較觀測值和預期值的差異。例如就遺傳學的經驗，假設植物會繼承上一代的特定顏色。藉由比較觀察結果和原先的預測，您可以決定原先的假設是否有效。語法CHIDIST(x,degrees_freedom)x 為用以進行 c2 檢

11、定的數值。degrees_freedom 即自由度。註解只要任何一個引數不是數值，則 CHIDIST會傳回錯誤值#VALUE!。如果 x 是負數，則 CHIDIST會傳回錯誤值#NUM!。如果 degrees_freedom 不是整數，則會將小數部分捨去，只取整數部份。如果 degrees_freedom x)方式來計算，其中，X 為 c2 的隨機變數。範例CHIDIST(18.307,10)等於 0.050001*CHIINVCHIINV傳回單尾卡方分配的反函數值。如果 probability=CHIDIST(x,?，則 CHIINV(probability，?=x。使用此分配進行觀察值與預

12、測值的比較，以決定您原始的假設是否有效。語法CHIINV(probability,degrees_freedom)Probability 為卡方分配所使用的機率。Degrees_freedom 為自由度。註解只要任何一個引數不是數值，則 CHIINV會傳回錯誤值#VALUE!。如果 probability 1，則 CHIINV會傳回錯誤值#NUM!。如果 degrees_freedom 不是整數，則只取整數部份。如果 degrees_freedom chi-square)，其中：Aij=第 i 列第 j 行的實際值Eij=第 i 列第 j 行的期望值r=列的個數c=欄的個數CHITEST傳回一

13、個 chi-square 統計量的機率值與自由度 df，而 df=(r-1)(c-1)。範例上述資料的 chi-square 統計量為 16.16957，且自由度為 2。CHITEST(B3:C5,B9:C11)等於 0.000308*FDISTFDIST傳回 F 機率分配。您可以使用這項函數來決定兩組資料是否有不同的變異程度。例如，您可以檢查男生和女生的高中入學成績，是否女生成績的變異性不同於男生。語法FDIST(x,degrees_freedom1,degrees_freedom2)X 為用來求算此函數的參數數值。Degrees_freedom1 為分子的自由度。Degrees_freed

14、om2 為分母的自由度。註解如果有任何一個引數不是數值，則 FDIST會傳回錯誤值#VALUE!。如果 x 是負數，則 FDIST會傳回錯誤值#NUM!。如果 degrees_freedom1 或 degrees_freedom2 不是整數，則只取整數部份。如果 degrees_freedom1 1 或 degrees_freedom1?1010，則 FDIST會傳回錯誤值#NUM!。如果 degrees_freedom2 1 或 degrees_freedom2?1010，則 FDIST會傳回錯誤值#NUM!。FDIST的計算方式是 FDIST=P(F x)。其中 F 為 F 分配中的隨機變

15、數。ricjijijijEEAX1122)(範例FDIST(15.20675,6,4)等於 0.01FDIST(7.50,2,1)=0.25*FINVFINV傳回 F 機率分配的反函數值。如果 p=FDIST(x,.)，則 FINV(p,.)=x。F 機率分配可以在 F 檢定中使用，F 檢定是用來比較兩組資料的變異程度。例如，您可以分析美國和加拿大的收入分配，以找出這兩個國家收入的變異程度是否相似。語法FINV(probability,degrees_freedom1,degrees_freedom2)Probability 是和 F 累加分配有關的機率值。Degrees_freedom1 為

16、分子的自由度。Degrees_freedom2 為分母的自由度。註解如果有任何一個引數不是數值，則 FINV會傳回錯誤值#VALUE!。如果 probability 1，則 FINV會傳回錯誤值#NUM!。如果 degrees_freedom1 或 degrees_freedom2 不是整數，則只取整數部份。如果 degrees_freedom1 1 或 degrees_freedom1?1010，則 FDIST會傳回錯誤值#NUM!。如果 degrees_freedom2 1 或 degrees_freedom2?1010，則 FDIST會傳回錯誤值#NUM!。FINV函數可用來傳回 F 分

17、配的臨界值。例如，變異數分析(ANOVA)的計算結果通常包括在0.05 有效水平下的 F 統計值、F 機率以及 F 臨界值。若要傳回 F 的臨界值，請將有效水平設為 FINV函數中的probability 引數。FINV使用反覆運算的技巧來計算函數值。給定一機率值，FINV會重覆運算直到輸出結果的精確度達到?3x10-7 以內。如果 FINV在 100 次反覆運算後並沒有減少，則函數會傳回錯誤值#N/A。範例FINV(0.01,6,4)等於 15.20675FINV(0.25,2,1)=7.50000595*CONFIDENCECONFIDENCE傳回母體平均數的信賴區間。此信賴區間為樣本平均

18、數左右任一邊的範圍。例如，如果您郵購某種產品，您可以用特定的信賴區間來決定貨品到達的最早日期和最晚日期。語法CONFIDENCE(alpha,standard_dev,size)Alpha 是用來推算信賴度的明顯程度。信賴度等於 100*(1-alpha)%，換言之，0.05 的 alpha 值所指的是 95%信賴度。Standard_dev 為此資料的母體標準差，且假定為已知。Size 樣本大小。註解如果有任何一個引數不是數值，則 CONFIDENCE會傳回錯誤值#VALUE!。如果 alpha 1，則 CONFIDENCE會傳回錯誤值#NUM!。如果 standard_dev 0，則 CO

19、NFIDENCE會傳回錯誤值#NUM!。如果 size 不是整數，則只取整數部份。如果 size 1，則 CONFIDENCE會傳回錯誤值#NUM!。如果我們假設 alpha 等於 0.05，必須計算標準常態曲線中，等於(1-alpha)或 95%的範圍。此數值是 1.96，信賴區間為：範例假設觀察結果如下，在 50 位回數票乘客樣本中，因工作而須搭乘的平均時間為 30 分鐘，母體標準差為 2.5 分鐘。則母體平均數的 95%信賴區間為：或CONFIDENCE(0.05,2.5,50)等於 0.692951.=30 0.692951 分鐘 =29.3 分鐘到 30.7 分鐘。*CRITBINO

20、MCRITBINOM傳回累計二項分配函數大於或等於臨界值 alpha 之最小整數。這函數可用於品管應用程式中。例如，使用 CRITBINOM來決定我們允許生產線上生產不良品的最大數目。語法CRITBINOM(trials,probability_s,alpha)(96.1nx)505.2(96.130 Trials 為二項式分配之測試個數。Probability_s 每次測試的成功機率。Alpha 為臨界值。註解如果有任何一個引數不是數值，則 CRITBINOM會傳回錯誤值#VALUE!。如果 trials 不是整數，則只取整數部份。如果 trials 0，CRITBINOM會傳回錯誤值#NU

21、M!。如果 probability_s 1，則 CRITBINOM會傳回錯誤值#NUM!。如果 alpha 1，則 CRITBINOM會傳回錯誤值#NUM!。範例CRITBINOM(6,0.5,0.75)等於 4*EXPONDISTEXPONDIST傳回指數分配之機率值。你可使用 EXPONDIST建立兩事件間的時間模式，例如銀行自動櫃員機在提款時所花費的時間，您可以使用 EXPONDIST來計算這段程序最多花一分鐘的機率。語法EXPONDIST(x,lambda,cumulative)X 為函數的值。Lambda 為參數的值。Cumulative 是一個邏輯值，用來指定指數函數的形式。如果此

22、引數是 TRUE，則 EXPONDIST會傳回累加分配函數；如果是 FALSE，則傳回機率密度函數。註解如果 x 或 lambda 不是數值，則 EXPONDIST會傳回錯誤值#VALUE!。如果 x 0，則 EXPONDIST會傳回錯誤值#NUM!。如果 lambda 0，則 EXPONDIST會傳回錯誤值#NUM!。機率密度函數的公式是：累加分配函數的公式是：範例EXPONDIST(0.2,10,TRUE)等於 0.864665xexf);(xexF1);(EXPONDIST(0.2,10,FALSE)等於 1.353353*LINESTLINEST使用最小平方法計算最適合於觀測資料組的迴

23、歸直線公式，並傳回該直線公式的陣列。因為這個函數傳回的是數值陣列，所以它必須以陣列公式的形態輸入。此直線之公式是：y=mx+b 或y=m1x1+m2x2+.+b(如果 x 值不只一個範圍)其中，因變數 y 值是自變數 x 值的函數。m 值是每個 x 值的係數，而 b 則為常數項。公式中的 y、x 和 m 也可以是向量。LINEST 傳回的陣列是 mn,mn-1,.,m1,b，以及其他的迴歸統計量。語語法法LINESTLINEST(known_ysknown_ys,known_xs,const,stats)Known_ys 是一組符合 y=mx+b 運算關係的已知 y 值。如果 known_ys

24、 陣列是個單一直欄，則 known_xs 陣列中的每一直欄將被視為一個獨立的變數。如果 known_ys 陣列是個單一橫列，則 known_xs 陣列中的每一橫列將被視為一個獨立的變數。Known_xs 是一組或多組符合 y=mx+b 運算關係的已知 x 值，這是個可省略的引數。known_xs 陣列會含有一組或多組變數。如果只用到一個變數，known_ys 與 known_xs 可以是任何形狀的範圍，只要兩者有相同的維數即可。如果有多個變數，則 known_ys 陣列必須是個向量(也就是，高度為一列或寬度為一欄的範圍)。如果 known_xs 引數被省略了，則假定它是與 known_ys 大

25、小相同的陣列 1,2,3,.。Const 為強迫指定 b 為 0 的邏輯值。如果 const 為 TRUE或被省略了，常數項 b 將依計算而得。如果 const 為 FALSE，常數項 b 將被設定為 0，公式變作 y=mx。Stats 為指定是否要傳回額外的迴歸分析統計量的邏輯值。如果 stats 為 TRUE 時，LINEST 傳回額外的迴歸統計量，所以傳回的陣列是；mn,mn-1,.,m1,b;sen,sen-1,.,se1,seb;r2,sey;F,df;ssreg,ssresid。如果 stats 為 FALSE 或被省略了，LINEST 只傳回係數 m 和常數項 b。*做迴歸分析1

26、.請在 工具 功能表上按一下 增益集。選取分析工具箱核取方塊。2.若要使用分析工具箱請在 工具 功能表上按一下 資料分析。就有你要的迴迴歸歸的功能3.進到迴歸的功能後按說明,它就會告訴您怎麼使用這個工具,以下是它的內容:關關於於 迴迴歸歸分分析析 對對話話方方塊塊輸輸入入 Y Y 範範圍圍輸入相依資料範圍的參照。範圍必須只包含單一欄的資料。輸輸入入 X X 範範圍圍輸入獨立資料範圍的參照。Microsoft Excel 會以遞增的順序、從左到右地排列這個範圍內獨立變數的順序。獨立變數的最大個數為 16。標標記記如果輸入範圍的第一列或欄內、或範圍裡面包含了標記，請選取這個核取方塊。如果輸入範圍中

27、沒有標記，則清除這個核取方塊。Microsoft Excel 會替輸出表格產生適當的資料標記。信信賴賴度度選取這個選項可在摘要輸出表格中包含額外的等級。請在方塊中輸入除了預設值 95%之外的其它信賴度等級。常常數數為為零零選取這個選項可強制迴歸線通過原點。輸輸出出範範圍圍在輸出表格左上角儲存格輸入參照。摘要輸出表格中至少應保留七欄，用來容納變異數分析表、係數、y 估計值的標準誤差、r2 值、觀察點的個數以及係數的標準誤差。新新工工作作表表按一下可在目前的活頁簿中插入新的工作表，並從新工作表的 A1 儲存格開始貼入結果。若要替新的工作表命名，請在方塊中鍵入名稱。新新活活頁頁簿簿按一下以建立新的活

28、頁簿，並將新工作表中的結果貼入新活頁簿。殘殘差差選取這個選項可在殘差輸出表格中包含殘差。標標準準化化殘殘差差選取這個選項可在殘差輸出表格中包含標準化殘差。殘殘差差圖圖選取這個選項可產生每一個獨立變數與殘差的比對圖。樣樣本本迴迴歸歸線線圖圖選取這個選項可產生預測值與觀察值的比對圖。常常態態機機率率圖圖選取這個選項可產生常態機率圖。以下是知識的相關網頁:http:/ Excel 會以遞增的順序、從左到右地排列這個範圍內獨立變數的順序。獨立變數的最大個數為 16。如果輸入範圍的第一列或欄內、或範圍裡面包含了標記，請選取這個核取方塊。如果輸入範圍中沒有標記，則清除這個核取方塊。Microsoft Ex

29、cel 會替輸出表格產生適當的資料標記。選取這個選項可在摘要輸出表格中包含額外的等級。請在方塊中輸入除了預設值 95%之外的其它信賴度等級。在輸出表格左上角儲存格輸入參照。摘要輸出表格中至少應保留七欄，用來容納變異數分析表、係數、y 估計值的標準誤差、r2 值、觀察點的個數以及係數的標準誤差。按一下可在目前的活頁簿中插入新的工作表，並從新工作表的 A1 儲存格開始貼入結果。若要替新的工作表命名，請在方塊中鍵入名稱。如果輸入範圍的第一列或欄內、或範圍裡面包含了標記，請選取這個核取方塊。如果輸入範圍中沒有標記，則清除這個核取方塊。Microsoft Excel 會替輸出表格產生適當的資料標記。在輸

30、出表格左上角儲存格輸入參照。摘要輸出表格中至少應保留七欄，用來容納變異數分析表、係數、y 估計值的標準誤差、r2 值、觀察點的個數以及係數的標準誤差。ztestTtest336641975388269145144252417151998765432100246810系列1Log.(系列1)98765432100246810系列1Log.(系列1)-4 0.000134-3.9 0.000199-3.8 0.000292-3.7 0.000425-3.6 0.000612-3.5 0.000873-3.4 0.001232-3.3 0.001723-3.2 0.002384-3.1 0.0032

31、67-3 0.004432-2.9 0.005953-2.8 0.007915-2.7 0.010421-2.6 0.013583-2.5 0.017528-2.4 0.022395-2.3 0.028327-2.2 0.035475-2.1 0.043984-2 0.053991-1.9 0.065616-1.80.07895-1.7 0.094049-1.6 0.110921-1.5 0.129518-1.4 0.149727-1.3 0.171369-1.2 0.194186-1.1 0.217852-1 0.241971-0.9 0.266085-0.8 0.289692-0.7 0.

32、312254-0.6 0.333225-0.5 0.352065-0.40.36827-0.3 0.381388-0.2 0.391043-0.1 0.3969530 0.3989420.1 0.3969530.2 0.3910430.3 0.3813880.40.36827543210-1-2-3-4-500.050.10.150.20.250.30.350.40.450.5 0.3520650.6 0.3332250.7 0.3122540.8 0.2896920.9 0.2660851 0.2419711.1 0.2178521.2 0.1941861.3 0.1713691.4 0.1

33、497271.5 0.1295181.6 0.1109211.7 0.0940491.80.078951.9 0.0656162 0.0539912.1 0.0439842.2 0.0354752.3 0.0283272.4 0.0223952.5 0.0175282.6 0.0135832.7 0.0104212.8 0.0079152.9 0.0059533 0.0044323.1 0.0032673.2 0.0023843.3 0.0017233.4 0.0012323.5 0.0008733.6 0.0006123.7 0.0004253.8 0.0002923.9 0.0001994 0.000134543210-1-2-3-4-500.050.10.150.20.250.30.350.40.45