統計分析
Microsoft Excel 2000提供許多的功能協助您分析統計資料。這程式建有數字的函數,如 平均數 、 中位數 、和 眾數 ,和簡單的分析作業。當內建的統計函數不夠,您可以開啟 分析工具箱 。
分析工具箱 ,是一個增益模組,提供一組函數和工具擴大Excel內建分析的能力。您可以使用這個 工具箱 建立直方圖、等級和百分比表,資料的隨機或週期性抽取樣本,進行回歸分析,取得樣本資料的統計估計,產生不平均的亂數,應用 傅立葉 轉換(FFT)法和其他轉換方法轉換資料等等其他更多的功能。
使用分析工具箱
資料 分析工具箱 的功能足夠出一本自己的書。在這一章,我們將焦點放在分析統計資料的應用能力。
請參閱
更進一步有關特殊的指令和函數這裡所沒提到的,請查閱Excel的線上說明系統。
如果您Excel執行的完整安裝,那您每次執行Excel,資料 分析工具箱 就可以用。您可以使用 工具箱 函數就如同其他Excel的函數,按照下面的步驟去使用分析工具:
- 從 工具 功能表選擇 資料分析 。第一次使用這個指令,需要等待一段時間讓Excel從磁片讀取檔案,然後您就會看到下面的對話方塊,如圖15-1。
圖15-1. 資料分析 對話方塊,展示工具的清單。
- 要使用一項分析工具,選取清單方塊其中的名稱然後按 確定 。
- 將出現的對話方塊完成。在大多例子,意味著要您指定輸入分析的資料範圍,並選取您要的選項。
如果 資料分析 指令沒有出現在 工具 功能表,或者使用 工具箱 函數的公式傳回一個#NAME?錯誤的值,在 工具 功能表選取 增益集 ,然後從裡面所提供 現有的增益集功能 清單選擇 資料分析 ,並按 確定 。如果 分析工具箱 沒有 現有的增益集功能 清單,您必須重新執行安裝。
分析資料的分配
在統計中,將測量的集合叫做「分配」。在Microsoft Excel 2000,您可以使用多種工具進行資料分配的分析:內建統計函數,樣本與母體統計函數,敘述統計工具─自動使用敘述統計函數做出報告,直方圖工具箱,等級和百分比函數和等級和百分比工具箱一起的。
內建統計函數
您可以使用Microsoft Excel內建統計函數分析測量一個群組或母體。在這一節,我們會將只討論一般常用的統計函數。Excel也提供進階的統計函數LINEST、LOGEST、TREND和GROWTH,用來操作數字的陣列。
請參閱
更多有關LI-NEST,LO-GEST,TREND,和GROWTH的資訊,請見 本章<線性和指數回歸> 。
圖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. VAR和STDEV函數測量考試成績實例的分佈。
儲存格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工作表函數使用的公式得到相同的統計資料。下面列一張統計和公式的表格。
統計