統計分析
_
首頁 線上購物 接課行事曆 教學資料 美食消息 新知新聞 黑輪Q&A 黑輪BLOG 黑輪日記 童軍團友會 好貨推薦

My status
首頁
向上

 

 

統計分析

 

Microsoft Excel 2000提供許多的功能協助您分析統計資料。這程式建有數字的函數,如 平均數  中位數 、和 眾數 ,和簡單的分析作業。當內建的統計函數不夠,您可以開啟 分析工具箱 

 分析工具箱 ,是一個增益模組,提供一組函數和工具擴大Excel內建分析的能力。您可以使用這個 工具箱 建立直方圖、等級和百分比表,資料的隨機或週期性抽取樣本,進行回歸分析,取得樣本資料的統計估計,產生不平均的亂數,應用 傅立葉 轉換(FFT)法和其他轉換方法轉換資料等等其他更多的功能。


 

使用分析工具箱

 

資料 分析工具箱 的功能足夠出一本自己的書。在這一章,我們將焦點放在分析統計資料的應用能力。

請參閱

更進一步有關特殊的指令和函數這裡所沒提到的,請查閱Excel的線上說明系統。

如果您Excel執行的完整安裝,那您每次執行Excel,資料 分析工具箱 就可以用。您可以使用 工具箱 函數就如同其他Excel的函數,按照下面的步驟去使用分析工具:

  1.  工具 功能表選擇 資料分析 。第一次使用這個指令,需要等待一段時間讓Excel從磁片讀取檔案,然後您就會看到下面的對話方塊,如圖15-1
    15-1. 資料分析 對話方塊,展示工具的清單。
  2. 要使用一項分析工具,選取清單方塊其中的名稱然後按 確定 
  3. 將出現的對話方塊完成。在大多例子,意味著要您指定輸入分析的資料範圍,並選取您要的選項。

如果 資料分析 指令沒有出現在 工具 功能表,或者使用 工具箱 函數的公式傳回一個#NAME?錯誤的值,在 工具 功能表選取 增益集 ,然後從裡面所提供 現有的增益集功能 清單選擇 資料分析 ,並按 確定 。如果 分析工具箱 沒有 現有的增益集功能 清單,您必須重新執行安裝。

 

分析資料的分配

 

在統計中,將測量的集合叫做「分配」。在Microsoft Excel 2000,您可以使用多種工具進行資料分配的分析:內建統計函數,樣本與母體統計函數,敘述統計工具─自動使用敘述統計函數做出報告,直方圖工具箱,等級和百分比函數和等級和百分比工具箱一起的。

 

內建統計函數

您可以使用Microsoft Excel內建統計函數分析測量一個群組或母體。在這一節,我們會將只討論一般常用的統計函數。Excel也提供進階的統計函數LINEST、LOGEST、TREND和GROWTH,用來操作數字的陣列。

請參閱

更多有關LI-NESTLO-GESTTREND,和GROWTH的資訊,請見 本章<線性和指數回歸> 

 
A的函數

Excel有一組函數提供您較多彈性,在您計算包含有文字和邏輯數值的資料。這些函數是AVERAGEACOUNTAMAXAMINASTDEVAVARAVARPA

一般,這些函數沒有"A"的版本,會將儲存格包含文字的部分忽略。舉例來說,如果有一個10個儲存格的範圍裡包含一個文字的值,AVERAGE會忽略這個儲存格然後除以9算出平均數,而AVERAGEA則會將文字包含範圍之內,然後除以10

舉例來說,如果圖15-2工作表儲存格B7包含"none"這個字取代一個數值,AVERAGE會傳回$469.09,表示這個儲存格式直接被跳過了,可能就是您想要的結果。然而使用AVERAGEA函數,結果會是$430.00,就好像這個儲存格用0代替文字。這樣的做法會是很有用的,當您想把所有的儲存格包含在您計算之內,特別是遇到某些的情況下,您要使用公式傳回如"none"的文字旗標。

AVERAGE函數

AVERAGE函數計算算術平均數或平均數,由一連續的數值的總和除以總數所得到的值。

這個函數的格式

=AVERAGE(number1,number2,.....)

AVERAGE會將空白、邏輯值、和文字儲存格忽略,而且可以改用長的公式。舉例來說,要算出圖15-2B4到B15儲存格的平均銷售數字,您可以用這個公式

=(B4+B5+B6+B7+B8+B9+B10+B11+B12+B13+B14+B15)/12

算出結果是$550.08。這個方法跟SUM函數有一個相同的缺點是,使用+運算符號取代一個儲存格的範圍:每次您改變範圍重新計算平均時,您必須編輯這個儲存格的參考和除數。顯然比較有效的是輸入

=AVERAGE(B4:B15)

15-2. 使用工作表示範某些Excel的內建函數。
MEDIAN,MODE,MAX,MIN,COUNT和COUNTA函數

這些函數包含相同的引數:基本上就是一個儲存格範圍,或用冒號隔開的一列數字。這些函數的格式如下:

=MEDIAN(number1,number2,.....)
=MODE(number1,number2,.....)
=MAX(number1,number2,.....)
=MIN(number1,number2,.....)
=COUNT(number1,number2,.....)
=COUNTA(number1,number2,.....)

MEDIAN函數是計算一組數字的中間值。中間值是這組數字的中間,平均數會比中間值還要大或小。如果指定的數列個數是奇數,這個值會傳回這組數字中兩個中間值的平均值。舉例來說,這個公式

=MEDIAN(1,3,4,6,8,13,35)

傳回7。

MODE函數是決定一組數字發生頻率最高的值。舉例來說,這個公式

=MODE(1,3,3,6,7)

傳回3。如果連一個數字都沒有,MODE傳回#N/A錯誤的值。

MAX函數傳回範圍內最大的數值。舉例來說,在圖15-2的工作表,您可以用這個公式

=MAX(B4:B15)

決定yak銷售最高的月份:$954

MIN函數傳回範圍內最小的數值。舉例來說,在圖15-2的工作表,您可以用這個公式決定yak銷售最低的月份:$156。

=MIN(B4:B15)

COUNT函數告訴您一個給定範圍的含有數值資料的儲存格個數,包含日期和公式的值是數值的舉例來說,在圖15-2工作表,公式

=COUNT(F3:F16)

傳回13─F3:F16範圍包含數值的個數。

COUNT函數僅會計算範圍內的數字,而且會將空白、和包含文字、邏輯值,及錯誤的值等忽略。若要計算所有非空白的儲存格(不管裡面包含什麼),您可以用COUNTA函數。在圖15-2工作表,公式

=COUNTA(F3:F16)

傳回14,因為COUNTA函數將儲存格F3裡的文字資料包含在內。(請見 本章<A的函數> )

SUMIF和COUNTIF函數

SUMIF函數很像SUM,但會加入總計時會先作測試。這個函數的格式

=SUMIF(range,criteria,sum_range)

引數range指定您想要測試的範圍,criteria是指定這個範圍每個儲存格要執行測試的條件,而sum_range指定要加總的符合的數值。舉例來說,在圖15-2,如果包含月的名稱那一欄定義為monthNames,包含yak銷售定義為yak Sales,您可以這個公式找到六月的銷售額,

=SUMIF(monthNames,"June",yakSales)

其傳回的值為585。

同樣的,COUNTIF計算符合指定條件,格式如下

=COUNTIF(range,criteria)

舉例來說,您可以下面這個公式找yak低於$600的銷售的月數,

=COUNTIF(yakSales,"<600")

傳回數值是8。注意您可以在引數criteria使用關係運算子測試更複雜的狀況。

SUMPRODUCT和SUMSQ函數

SUMPRODUCT函數傳回兩個以上陣列的個對應的元素的乘績,加總所有乘績,然後把乘績的總和傳回。引數裡的非數值的值會視為0。這個公式的格式是

=SUMPRODUCT(array1,array2,array3,.....)

圖15-3顯示一張使用SUMPRODUCT的工作表,F6的公式:

=SUMPRODUCT(B2:G2,B3:G3)

決定生產所有六種機械附件需要扣鏈齒輪的總數。它是把B2*B3,C2*C3然後一直到G2*G3,將六個乘積加總起來。

SUMPRODUCT函數可以接受多達30個陣列引數。每個陣列必須有相同的維度,否則SUMPRODUCT會傳回#VAULE!錯誤的值。


15-3. 工作表使用SUMPRODUCT計算製造所有六種機械附件所需的扣鏈齒輪。

SUMSQ函數跟SUBPRODUCT函數相近,除了它是將引數的數字開平方根,傳回所有平方根的總和,而不是乘積。這個函數的形式是

=SUMSQ(number1,number2,...)

舉例,公式=SUMSQ(5,6)

傳回61(25+36)。

 

樣本與母體統計函數

變異數和標準差,是統計上用來測量一個群組、母體的分配情形。標準差是變異數的平方根。通常,大約百分之68的一般分佈位於在平均數一個標準差左右,大約百分之95位於兩個標準左右。標準差比較大代表母體離平均數很遠;標準差比較小代表母體離平均數很近。

四個統計函數─VAR,VARP,STDEV,和STDEVP─計算一段範圍儲存格的數值的變異數和標準差。在您計算某個群組的變異數和標準差之前,您必須決定這些數值是反映母體的全部或是一個母體樣本的代表。VAR和STDEV函數假設這些數值僅是母體的樣本;VARP和STDEVP函數代表這些數值即代表母體的全部。

計算樣本統計:VAR和STDEV

VAR和STDEV函數的格式是

=VAR(number1,number2,...)

=STDEV(number1,number2,...)

圖15-4顯示五個學生的考試成績,假設儲存格B4:E8的成績僅反映所有母體一部份。

15-4. VARSTDEV函數測量考試成績實例的分佈。

儲存格C13用VAR函數計算這個群組考試成績樣本的變異數:

=VAR(B4:E8)

儲存格C14用STDEV計算標準差:

=STDEV(B4:E8)

如同上面所顯示的,VAR函數傳回52.98,STDEV函數傳回7.28。假設在這個例子考試成績成一般分配,我們可以推論有68%的學生得到的分數在80.07(87.35-7.28)和94.63(87.35+7.28)之間。

計算所有母體統計:VARP和STDEVP

如果您分析的數值要表現整個母體而不是樣本,用VARP和STDEVP函數計算變異數和標準差。計算所有母體的變異數,用這個公式

=VARP(number1,number2,...)

求標準差,用這個公式

=STDEVP(number1,number2,...)

假設圖15-4顯示,工作表儲存格B4:E8代表所有的母體,您可以用公式計算變異數和標準差

=VARP(B4:E8)

儲存格C14用STDEV計算標準差:

=STDEVP(B4:E8)

VARP函數傳回50.33,STDEVP函數傳回7.09。

SUMX2PY2,SUMX2MY2,和SUMXMY2函數

SUMX2PY2,SUMX2MY2,SUMXMY2函數讓您利用平方和的總和的運算完成三個變異數,這統計的計算使用很多。SUMX2PY2函數計算相對X和Y平方和的總和,其中X和Y包含相同數目的元素。SUMX2MY2函數計算相對X和Y平方差的總和。最後SUMXMY2函數計算相對X和Y差的平方總和。這些函數的格式

=SUMX2PY2(array_x,array_y)

=SUMX2MY2(array_x,array_y)

=SUMXMY2(array_x,array_y)
請參閱

有關更多使用陣列的資訊,請見 第六章<使用陣列> 

舉例來說,這個三函數都使用相同的兩個陣列,您可以建立下面的公式:

=SUMX2PY2({1,2,3,4},{2,4,6,8})

傳回150

=SUMX2MY2({1,2,3,4},{2,4,6,8})

傳回-90

=SUMXMY2({1,2,3,4},{2,4,6,8})

傳回30

 

敘述統計工具

 敘述統計 工具可以輸入一組以上的資料產生一個的敘述統計分析的表格。如下圖15-5顯示,在輸入範圍每個變量,這個工具輸出的範圍包含下列資訊:平均值、標準誤、中位數、眾數、樣本變異數、峰度值、偏態,範圍、最小值、最大值、總計、計數、最大最小值(指定您想要的任何k值),平均數的信賴區間。


15-5. 產生1000的平均值100標準差2一般分佈的亂數,然後用 敘述統計 工具證實它的標準。

要使用 敘述統計 工具,從 工具 功能表選擇 資料分析 ,在 資料分析 對話方塊選擇 敘述統計 選項,並且按 確認 。顯示如圖15-6對話方塊。

15-6. 使用 敘述統計 的工具來建立一個敘述統計的表格。

 敘述統計 工具需要包含一個或多個以上變數的輸入範圍,和一個輸出範圍。您必須分組方式是逐欄還是逐列。如果您的資料含有一列標籤,一定要核取 類別軸標記是在第一列上 的選項。然後Excel會使用在輸出的表格標籤識別變數。

如果您想要如圖15-5詳細輸出的表格,請核取 摘要統計 的選項;否則就讓這個核取方塊空白,然後選擇核取方塊個別想要Excel給您的資訊的項目。

請參閱

有關更多亂數產生的資訊,請見 本章<產生亂數> 

像其他 分析工具箱 中的工具一樣, 敘述統計 建立一個常數的表格。如果一個常數表格不適合您的需要,您可以從其他 分析工具箱 工具或是Excel工作表函數使用的公式得到相同的統計資料。下面列一張統計和公式的表格。

統計 公式
平均數 =AVERAGE(number1,number2,...)
標準誤 和STEYX(known_y's,known_x's)近似,但不是一般分配要使用±-distribution。
中間值 =MEDIAN(number1,number2,...)
眾數 =MODE(number1,number2,...)
標準差(樣本) =STDEV(number1,number2,...)
標準差(母群體) =STDEVP(number1,number2,...)
變異數(樣本) =VAR(number1,number2,...)
變異數(母群體) =VARP(number1,number2,...)
峰度 =KURT(number1,number2,...)
偏態 =SKEW(number1,number2,...)
範圍 =MAX(number1,number2,...)-MIN (number1,number2,...)
最小值 =MIN(number1,number2,...)
最大值 =MAX(number1,number2,...)
總和 =SUM(number1,number2,...)
個數 =COUNT(value1,value2,...)
第K個最大值 =LARGE(array,k)
第K個最小值 =SMALL(array,k)
信賴區間 和CONFIDENCE(alpha, standard_dev,size)類似,但使用不同的演算法。

 

直方圖工具

直方圖(通常是簡單的縱行圖表)是種測定集合的圖表,標示出落在數個區間(稱為組界)中每個測量出的數字 (稱為頻率) 。

看Microsoft Excel直方圖工具如何運作,我們用一張包含1000筆成績的SAT測驗的表格(輸入範圍僅能為數值資料)。以50分的區間看所有成績的明細,設定分配的「組界」,如圖15-7。


15-7. 欄位F包含分配的「組界」。

下一步從 工具 功能表選擇 資料分析 ,選取 直方圖 ,然後按 確定 。出現 直方圖 對話方塊,如圖15-8

15-8. 當您從 資料分析 對話方塊選擇 直方圖 工具,這個對話方塊會出現。

 直方圖 工具需要三項資訊:資料範圍(在這個例子,D2:D1001)、組界範圍(F2:F20),和用來顯示分析結果左邊最上面儲存格的範圍(G1)。

注意

如果您在 組界範圍 編輯方塊留白,Excel會利用輸入範圍開端和結尾的最大值和最小值建立均等的組距範圍。區間的數目是輸入值數目的開平方根。

一般來說, 直方圖 工具會建立柏拉圖(排序的)分析,包含累計百分比、和產生一張圖表。現在,忽略其他選項選擇 圖表輸出 選項。當您按 確定 後,Excel會建立一張圖表,並將分析寫在欄位G和H,如圖15-9顯示。

15-9. 分析結果告訴我們有三個成績最少900但低於95048個成績最少950但低於1000等等。

 頻率 欄位, 直方圖 工具報告的輸入的數字大於等於組界但小於下一個組界。表格最後的數值報告輸入的數值大於或等於最一個組界。

注意 直方圖 工具會在 組界 欄位複製您組界欄位中的組界,如果您將輸出放在緊鄰組界的位置會很方便。不幸的,如果您試著將輸出範圍覆寫至組界範圍,您會得到一個錯誤訊息。如果您把輸出放在緊鄰組界,如圖15-9,您可以將原來的組界刪掉。

因為 直方圖 工具會拷貝組界範圍,填入數值常數的範圍是最好的方法,而不是填入公式。如果您使用公式,確定公式不可以包含相對參照;否則當 直方圖 拷貝範圍時,公式可能產生不可預期的結果。

繪製分配分析

 直方圖  圖表輸出 選項, 直方圖 工具會進行分析,同時建立一個圖表(圖15-10)。此圖表由圖15-7的資料所建立的(我們把圖放大,便於觀看)。標準Excel的長條圖可以調整成任何其他的圖表。


15-10. 直方圖 工具能自動建立像這樣的長條圖。
柏拉圖和累計百分率選項

使用 直方圖 話方塊 柏拉圖 選項對輸出結果排序,和 累計百分率 選項產生列出每個組界的累計百分率表格。舉例,用圖15-7的資料,建立一個累計百分率表格,顯示82.6%的學生分數低於1100。

用FREQUENCY函數分析分配

 直方圖 工具會產生一組數值的常數。如果您寧可建立輸入資料的公式連結,您可以使用 分析工具箱 的FREQUENCY陣列函數,函數的格式

=FREQUENCY(data_array,bins_array)

使用FREQUENCY函數,請按照下列步驟:

  1. 建立一欄組界,就如您 直方圖 工具想要的。
  2. 選取您想要顯示輸出的所有範圍。這個範圍必須是一欄儲存格;FREQUENCY函數的輸出範圍不能使用一列或多欄範圍。
  3. 輸入公式,指定第一個引數 輸入範圍 和第二個引數 組界範圍 。按Ctrl+Shift+Enter鎖定為陣列公式。

應用圖15-7的資料顯示FREQUENCY函數。


15-11. 使用FREQUENCY函數將分佈分析和輸入資料連結。

 

分析排名和百分率的函數

 分析工具 包含數種可以從一組輸入的資料整理排名和百分率的函數:PERCENTRANK,PERCENTILE,QUARTILE,SMALL,LARGE和RANK。

PERCENTRANK函數

PERCENTRANK函數傳回某數值在一組資料百分比的等級。您可以使用這個這個函數建立百分比表格,它會連結輸入範圍,只要輸入的數值改變,百分比的計算也會跟著改變。我們用這個函數在圖15-2的欄位E建立百分比等級。

PERCNETRANK函數的格式

=PERCNETRANK(array,x,signficance)

15-12. PERCENTRANK連結百分比計算去輸入數值。

引數array是指定輸入的範圍(在我們的例子,這裡是D2:D1001),而x指定您要獲得排名的數值。引數significance是選擇性的,指定百分比要傳回幾個小數點;如果忽略,結果會傳回三個小數點(0.xxx或xx.x%)

PERCENTILE和QUARTILE函數

您可以使用PERCENTILE函數,在一個輸入範圍找到指定某個百分比的值。它的格式

=PERCENTILE(array,k)

舉例,找圖15-12裡第87百分位成績是多少,您可以用這個公式

=PERCENTILE(D2:D1001,0.87)

結果在1110.13─代表SAT成績第87百分位。

您必須用0到1之間的小數表示百分比。

QUARTILE函數,格式是

=QUARTILE(array,quart)

運作和PERCENTILE函數很像,除了它僅能傳回一組資料的最低、第25百分位、中間值、第75百分位、最大值。第一個引數指定輸入範圍。第二個引數,一定要用0,1,2,3,4指定傳回的值,就如下列的表格。

引數 結果
0 最小值
1 第百分之二十五
2 中間值(第百分之五十)
3 第百分之七十五
4 最大值

注意:可以用MIN函數代替UARTILE(range,0)函數,MEDIAN函數代替QUARTILE(range,2)函數,MAX函數代替QUARTILE(range,4)函數。這些函數比QUARTILE快,特別是有很多的資料的時候。

SMALL和LARGE函數

SMALL和LARGE函數傳回輸入範圍的第k個最小值,和第k個最大值,格式是

=SMALL(array,k)

=LARGE(array,k)

找前圖15-12第十五高的分數,您可以使用這個公式

=LARGE(D2:D10001,15)

傳回1158,抽樣中SAT分數第15高。

RANK函數

RANK函數,格式是

=RANK(number,ref,order)

傳回一組數目指定數字的排名後的位置。舉例來說,要找圖15-12資料中分數1100的排名為何,您可以用這個公式

=RANK(1100,D2:D1001)

結果分數1100在樣本裡的為第175高分。在預設值中,最高是排名第一,第二高是排名第二,等等.......。如果您想要由小排到大,加第三個引數,這個數可以是比零大的任何數。

如果RANK不能由第一個引數和輸入的數值剛好找到數字,將傳回#N/A錯誤的值。

 

等級和百分比分析工具

假設您想對圖15-7的成績排名。您可以以降冪排序的方式排名,最好的成績在最上面,最差的在最下面。要得到某一成績的等級,您可以建立就像排序過的遞增數列,1就是最好的,1000就是最差的。

 分析工具箱  等級和百分比 工具不僅可以幫您完成這些工作,還可以對輸入每個值建立百分比的功能。要使用這個工具,從 工具 功能表選 資料分析 ,選取 等級和百分比 選項,並按 確定 。出現一個對話方塊要求 輸入範圍  輸出範圍 。就如 直方圖 一樣, 輸入範圍 必須只能是數值資料。

圖15-13欄位E和H顯示使用 等級和百分比 工具分析圖15-7測驗成績的欄位D。

假設您在輸入範圍考試成績第421個人的學生ID,成績第600個人的學生ID等等。一個簡單得到這項資訊的方法,在欄位 等級 隔壁插入新的一欄,然後用INDEX函數從表格得到ID。結果顯示在圖15-14。


15-13. 使用 等級和百分比 工具在欄位D分析總分。

15-14. 使用INDEX函數將學生ID和分數排名相配。

在這個範例中,我們用 等級和百分比 工具分析一欄簡單的資料。我們也可以用這個工具分析全部三項成績─動詞、數學、總分。在這個例子,我們會指定範圍B1:D1001為輸入範圍,然後工具會產生12欄輸出,每個輸入欄位4。

注意

確定您分析的資料僅包含數值或絕對參考的公式。如果輸入的儲存格包含相對參考,可能排序時會擾亂輸出範圍。


 

產生亂數

 

Microsoft Excel內建亂數函數RAND,產生介於0與1之間均勻分配的亂數,換句話說,由RAND函數作為公式傳回的每個數值,在0和1之間出現的機率相同。因為樣本相當的小,分配並不很均勻。除非,重覆測試RAND函數到不偏向其分佈範圍任何位置。

您可以用 分析工具箱 的亂數元件建立一組不均勻的亂數,然後使用 直方圖 排序並繪出圖表。亂數對蒙第卡羅(摩納哥城市,世界著名賭城)的決策分析和其他種類的模擬是非常用有的。亂數分佈提供六種型態: 均等分配  常態分配  白努力分配  二項分配  波氏分配  離散分配 (使用者定義)。除此之外,您可以使用 複製數列 選項在指定區間建立非亂數。( 複製數列 選項可以從 編輯 功能表的 檔案 子功能表的Excel一系列的指令代替)

要使用 亂數產生器 ,從 工具 功能表選 資料分析 ,在 資料分析 對話方塊選取 亂數產生器 的選項,並按 確定 

Excel出現一個對話方塊,如圖15-15

15-15. 亂數產生器 對話方塊中的 參數 部分會隨著您所選擇的分配型態而改變

 變數個數  亂數個數 方塊中,指定幾欄數字和每欄有多少數字。舉例來說,如果您要10欄每一欄要100個數字,在 變數個數 編輯方塊指定10, 亂數個數 編輯方塊指定100。

這個對話方塊的 參數 區塊的改變,由您選取何種分配來決定。如圖15-15顯示,當您選取 均等分配 選項,您可以指定分配的終點。

您也可以指定亂數基值。然而,每次您用相同的基值產生一組特別分配的亂數,您就可以得到相同排列的數字。所以如果您需要重製某種排列的亂數,您就需要指定基值。

所有分配種類,都可以用 輸出範圍 編輯方塊告訴Excel您要將亂數放在哪裡。如果指定的範圍包含資料,在資料覆寫前會出現一個警告訊息。

 

均等分配選項

 均等分配 選項和RANDBETWEEN函數很像,產生一組界於指定的開端和結尾、均勻分配的實數。

當您想要的端點超過0和1或相同基值時,可以使用這個比較方便的選項代替RAND。

請參閱

更多有關RANDRANDBET- WEEN的資訊,請見 第十二章<RANDRANDBETWEEN函數> 

 

常態分配選項

一個常態分配有下列幾個特徵

  • 有一個特定的值,平均數,出現比任何數還要多。
  • 平均數之上和之下的數值個數幾乎相同。
  • 接近平均數的數多過遠離平均數的數。

產生常態分配亂數,要指定兩個參數:平均數和標準差。

標準差是亂數和平均數差的絕對值的平均。在常態分配大約有68%會掉落在與平均值一個標準差的距離。

您可以使用 敘述統計 工具驗證常態分配的「一般性」。舉例來說,圖15-5顯示由用 常態分配 選項,平均數100標準差2,所產生的一個有1000個亂數的敘述統計的表格。因為這個樣本很小,輸出不一定依照統計原理。

請參閱

更多有關敘述統計工具的資訊,請見 本章<敘述統計工具> 

 

白努力分配選項

 白努力分配 選項用來模擬一個數字的試驗成功的機率。給定所有試驗有相等成功的機率,其中一項成功並不影響之後的試驗。(注意:「成功」並不隱含價值暗示。換句話說,您可以把這項分配模擬失誤當作成功一樣)。 白努力 分配的輸出不是0就是1。

儲存格傳回1的機率由這項分配唯一的參數p給定,其中p必須是0到1之間的數。舉例來說,如果您想要一組100個白努力的亂數總和為27,您要定義 輸出範圍 為100,並指定p值為0.27。

 

二項分配選項

 二項分配 選項,給定特定的機率次數,模擬固定試驗成功的次數。就如 白努力分配 選項,試驗是假設獨立的;因此任何一個出現不會影響到任何其他的。

產生 二項分配 的數字,要指定機率值─參數p─試驗成功的次數。(再次強調,「成功」並不隱含數值暗示。換句話說,您可以把這項分配模擬失誤當作成功一樣。)

舉例來說,幾設您一週有10個銷售展示會,終止了20%;您想知道隔年成功的機率將會是多少。在 亂數個數 編輯方塊輸入50,在 p 方塊輸入0.2, 實驗的次數 編輯方塊輸入10,去知道您可以預期明年四個禮拜沒有展示會的機率。

 

波氏分配選項

 波氏分配 選項指定發生的機率模擬一段時間某個事件發生的機率。假設事件是獨立的,也就是每次事件不會影響其他可能性的事件。

 波氏分配 選項需要一個簡單的參數λ(lambda),表示預期一個獨立事件預期的出現。舉例來說,假設您預期平均一天收到10通電話服務。您想知道預估一年期間一天收到18通以上電話的機率有多少?要得到這項資訊,在 亂數個數 編輯方塊輸入260(52週乘以5天), λ(L) 編輯方塊輸入10。結果是您將會大約有三個禮拜會收到18以上的電話服務。

 

離散分配選項

 離散分配 選項是依照指定成功機率和每個成功相關機率的表格,建立自訂的分配的格式。機率的值必須在0和1之間,表格內的機率值和必須為1。要使用 離散分配 選項,您要指定可能結果和機率兩個欄位的範圍,欄位的範圍在這個選項僅有一個參數。

 

複製數列選項

 亂數產生器 對話方塊的 複製數列 選項用來建立非隨機的數字。選取 複製數列 選項,顯示圖15-19的對話方塊。

15-16. 複製數列 選項讓您建立一個人工選擇性重覆的數列。

您可以把 複製數列 選項當作是一個特別的 數列 指令。讓您建立一個以上的人工選擇性的複製區間的數列。

舉例來說,建立一個1,1,4,4,7,7,10,10,1,1,4,4,7,7,10的數列,先完成如圖15-16的對話方塊,需要兩組1到10間的數列,間隔為3,每個循環重覆每個數字兩次。

如果間隔讓數列大於指定的上限, 複製數列 選項會去掉最後的區間以包含上限。舉例來說,如果您指定 間隔 為4,數字由1到10,Excel會產生1,5,9和10。

 

母體抽樣

 

 抽樣 工具用來從一個龐大的群組的數字取出一個子集合。從輸入範圍中,您可以指定樣本數隨機抽樣或每隔一段數目抽樣。抽樣工具會把抽樣的數拷貝到指定的範圍。

要使用 抽樣 工具,從 工具 功能表選擇 資料分析 ,在 資料分析 對話方塊選取 抽樣 ,然後按 確定 。圖15-17顯示對話方塊出現的樣子。

 
文字性資料的抽樣

要使抽樣範圍包含文字的抽樣一樣可以執行,請依照下列步驟:

  1. 沿著文字建立一欄從1開始遞增的數列。
  2. 使用 抽樣 工具從數列抽選數字。
  3. 以結果的數字為引數,用INDEX函數抽選文字資料。

15-17. 抽樣 工具讓您抽選隨機或週期性數值母體的子集合。

在輸入範圍的值必須是數字,他們可以包括空白值和日期,被提供的日期必須輸入數字,不能是文字。舉例來說,簡化一個每日商品價格的圖表,您可以使用 抽樣 工具去抽出每個第n個資料點,然後從抽出的資料中建立一個新的圖表。

 

計算移動平均

 

移動平均是一種藉由每段時間測量的平滑波動,預測樣本趨勢分析的技術。由隨機「干擾」造成的波動是測量方法產生的。舉例來說,測量小孩成長的高度,跟尺的準確度和小孩是否站直或無精打采的。您可以進行一連串的測量,然而,每個一段時間平滑它們,小孩實際的生長速率會反應在曲線上。

平滑測量從其他導致偏差的臨時狀況得到。舉例來說,月銷售,可能因為該月份工作天數或明星推銷員缺席去度假而變化。

假設您建立如圖15-18的18個月份銷售需求曲線。為了從資料求得較少「干擾」的趨勢線,您可以繪製一個六個月的移動平均。移動平均線第一個點是第一個六個月的平均的樣子(2000年二月到六月),等等。您可以用 移動平均法 工具去得到您要想的分析。

15-18. 我們用18個月需求曲線示範Excel 平均移動法 工具。

要使用 移動平均法 工具,從 工具 功能表的 資料分析 ,在 資料分析 對話方塊選取 移動平均法 選項,並且按 確認 

 移動平均法 工具需要三項資訊:包含您想要分析的資料的範圍和平均資料出現的範圍,和資料平均的間隔。舉例,要決定三個月的移動平均,指定 間隔 為3。

圖15-19重疊原來圖15-18的需求曲線,顯示六個月的平均曲線。 平均移動法 產生資料在欄位C,其用來在圖表建立較直的線。注意,工具輸出的第一個儲存格包含#N/A錯誤的值。當 間隔 為n,開始輸出的時候總有n-1個#N/A錯誤。在圖表包含那些值並不會有問題,因為Excel簡化了,讓第一個區域畫上空白的線。

 

 

線性和指數回歸

 

Excel有幾個執行線性回歸的陣列函數─LINEST,TREND, FORECAST,SLOPE和STEYX,以及指數回歸─LOGEST和GROWTH。這些函數輸入陣列公式,然後產生陣列結果。您可以使用這些函數其中一個變數或數個獨立的變數。

「回歸」的觀點,在這裡可能會使某些人困擾,因為回歸一般認為和向後移動有關,然而在統計的觀點,回歸是用來預測未來。為了更了解這個觀點,我們建議消除您心中的字典的定義,開始新的定義:「回歸是一種統計的方法,讓您可以找到一組資料描述的方程式。」

通常商業上試著用過去的銷售和達成銷售計劃預測未來。簡單的銷售百分比技術指出資產和和負債隨著銷售的變化,並指出他們的百分比。雖然使用銷售百分比預測通常會滿足短期或緩慢的成長,但這種技術在加速成長時就失去正確性。

 回歸分析 使用在較複雜的方程式,分析龐大的資料然後轉換成座標或是曲線。在過去回歸分析並不廣泛使用,因為龐大數字的繁複的運算。自從試算表應用軟體如Excel開始提供內建回歸運算分析,逐漸被廣泛使用。

「線性回歸」會繪出一組資料最適合的斜率。根據一年的銷售情況,線性回歸可以根據給您斜率和銷售資料最適合直線的y軸截距(直線經過y軸的點),告訴您三月預估銷售。根據這條線的未來,您可以假設線性的成長情況測試未來。

「指數回歸」產生一組資料最適合的指數曲線,並假設不會隨時間呈線性改變。舉例來說,一系列的人口成長測量幾乎是以指數曲線表示比直線還要好。

「多重回歸」分析一組以上的資料,經常用來產生一個上實際上的推測。您可以同時執行線性和指數雙重回歸分析。舉例來說,假設您要推測一間房子適當的價格,以平方英尺、房間數、大小和屋齡。使用多重回歸公式,根據資料庫存在的房子的資訊,來估計價格。

 

計算線性回歸分析

下列代數方程式描述一組具有一個獨立變數資料的直線。

y=mx+b

x表示自變數,y是因變數,m是直線的斜率,而b是y截距。這條線及多重回歸分析自變數的一個數所做的貢獻所得的結果,回歸方程式直線的格式

y=m1x1+m2x2+....+mnxn+b

y是因變數,x1到xn是n個自變數,m1到mn是每個自變數的係數,b是常數。

LINEST函數使用一般方程式,給定一組y和一組已知自變數的值,傳回m1到mn和b值。函數的格式

=LINEST(known_y's,known_x's,const,stats)

引數known_y's就如您所知的y,這個引數可以是單一的欄、單一的列,或是矩形範圍的儲存格。如果known_y's是單一的欄,則引數known_x's的每個欄視為自變數。相同的,如果known_y's是單一列位,則引數known_x's的每個列視為自變數。如果known_y's一個矩形範圍的儲存格,只能使用一個自變數;且known_x's必須和known_y's相同的大小和型態。

如果您省略known_x's,Excel會使用數列1,2,3,4等等。

引數const和stats是選擇性的。如果用了其中一個,一定要是邏輯常數─不是TRUE或FALSE。(您可以用1代替TRUE,0代替FLASE)內定的const和stats值分別是TURE跟FALSE。如果您設定stats為FALSE,LINEST傳回的陣列會包括下面確認的統計。

se1到sen 標準誤每個係數
seb 標準誤的常數b
r2 要決定的係數
sey y的標準誤
F F統計
df 自由度
ssreg 平方和的回歸
ssresid 殘餘的平方和

在使用LINEST建立公式前,您要選擇足夠大範圍保留函數傳回的陣列。

如果您忽略stats引數(或明確地設為FALSE),傳回的陣列會包含您每個自變數其中一個儲存格和一個b的儲存格。如果引進有效的統計,結果陣列看起來像這個:

mnmn    		mn-1	...	m2       		m1      		b
sen     		sen-1	...	se2     		se1     		seb
r2      		sey
F       		df
ssreg   		ssresid

在選擇包含輸出結果的陣列之後,在每一個結果的陣列,輸入函數並按 Ctrl+Enter 輸入這個函數

注意:不管有沒有確認統計,自變數的標準誤係數將傳回目前您輸入資料的位置。舉例來說,如果您有四個自變數構成四個欄,LINEST計算最左邊會當成x1,但在結果陣列的四個欄傳回m1。圖15-20顯示一個使用LINEST單一自變數的簡單的範例。工作表整個欄B表示一個小公司的產品需求。欄A代表這段期間的月份。假設您想要計算描述需求和月份最好之間關係的回歸線的斜率和y截距。要達成這件事,選擇範圍F6:G6,輸入公式

=LINEST(B2:B19,A2:A19)

並按 Ctrl+Shift+Enter 。儲存格F6傳回的結果20.613,是回歸線的斜率,G6的數4002.065,是這條線y截距。


15-20. LINEST函數計算回歸線的斜率和y截距。
TREND函數

LINEST函數傳回資料最佳解的數學描述的直線。TREND使您沿這條直線找到所有點。您可以使用TREND所繪的趨勢線的數字─直線可以有助於對實際資料建立概念。您也可以用TREND推斷,或做智慧推斷(小心。雖然您可以使用TREND繪出資料最佳解的直線,然而它不能告訴您這條直線對未來是否是一個好的預測。由LINEST傳回的驗證統計能幫您做好估計。)

TREND函數接受4個引數

=TREND(known_y's,known_x's,new x's,const)

前面二個引數分別代表您已知的因變數和自變數。如同LINEST,known_y's這個引數可以是單一的欄、單一的列,或是矩形範圍的儲存格。known_x's引數也和LINEST一樣描述的格式。

第三和第四個引數是選擇性的。如果您忽略new_x's,TREND函數認為new_x's和known_x's一樣。如果您引用const,這個引數的值一定是TRUE或FALSE(或1或0)。如果const是TRUE,TREND強迫b為0。

要計算您已知資料最佳解的趨勢線上資料點,簡化第三四個引數。結果陣列會和known_x's範圍有相同的大小。在圖15-21,我們用TREND找出圖15-20範例描述的資料找回歸線上每一點的值。要產生這些值,我們選取範圍C2:C19,和用 Ctrl+Shift+Enter 輸入下面陣列公式:

=TREND(B2:B19,A2:A19)

要從存在的資料推斷,您必須給new_x's一個範圍。您可以隨您想要的,給new_x's很多或很少儲存格。結果陣列會和new_x's範圍有相同的大小。在圖15-22,我們用TREND計算第19,20,21月的需求。為了求得這些值,我們在A21:A23,輸入數字19到21,選取C21:C23,並且輸入下面陣列公式:

=TREND(B2:B19,A2:A19,A21:A23)

15-21. TREND函數建立可以畫直線或圖表的一連串的資料。

15-22. TREND可以預測第192021月的銷售情況。
FORECAST函數

FORECAST函數和TREND相似,除了它傳回的是沿著線單一的點,而不是傳回這條直線定義的任何陣列。函數的格式

=FORECAST(known_y's,known_x's)

引數x是您想要推斷的值的資料點。舉例來說,不用TREND,我們可以用FORECAST函數推斷圖15-22儲存格C23,輸入這個式子

=FORECAST(21,B2:B19,A2:A19)

引數x是回歸線第21個資料點。您可以用這個函數,計算未來任何一個點。

SLOPE函數

SLOPE函數傳回線性回歸的斜率。斜率的定義是在回歸線的任兩點的垂直的距離除以水平的距離。這個值和LINEST函數傳回陣列的第一個數一樣。換句話說,SLPOE計算由FORECAST和TREND計算資料點的數值所得到的線的軌道。SLOPE函數的形式

=SLOPE(known_y's,known_x's)

要找圖15-20範例裡敘述的資料的回歸線斜率,我們可以用這個公式

=SLOPE(B2:B19,A2:A19)

傳回20.613

STEYX函數

STEYX函數計算回歸的標準誤,預測每個x給的y產收標準誤量的測度。

=STEYX(known_y's,known_ x's)

如果我們將這個函數用在圖15-20顯示的工作表,公式

=STEYX(B2:B19,A2:A19)

會傳回標準誤差值12.96562

 

計算指數回歸統計

這個式子敘述一個指數回歸曲線

y=b*m1x1*m2x2*...*mnxn

如果僅有一個自變數,式子是

y=b*mx
LOGEST函數

LOGEST函數使用和LINEST很像,除了您是用來分析非線性資料之外。LOGEST傳回每個自變數係數加常數b。

函數的形式是

=LOGEST(known_y's,known_x's,const,stats)

LOGEST函數同樣的和LINEST函數接受四個引數,以同一個方式傳回陣列的結果。如果您設定選擇性引數stats為TRUE,這個函數也會傳回確認的統計。

請參閱

更多有關LOGEST函數底下的公式和引數,請見 本章<LINEST函數> 

注意

LINESTLOGEST函數僅傳回計算直線或曲線的y軸。兩者之間的差是LINEST投射的是直線,LOGEST投射的是指數曲線。您必須小心配合適當的函數在手邊的分析。LINEST函數可能比較適合銷售計劃,LOGEST函數比較適合應用在統計分析或母體趨勢。

GROWTH函數

鑒於LOGEST函數傳回一個一組資料最適指數回歸的數學描述,GROWTH函數讓您找到曲線上的點。GROWTH函數的運作很像線性的副本TREND,格式是

=GROWTH(known_y's,known_x's,new_x's,const)
請參閱

想要獲得更多有關GROWTH函數的引數的資料,請見 <TREND函數> 

 
 

EmailAllen@Chen.name