會計學1Excel在金融計量和經濟學中的應用Excel簡介MicrosoftExcel是美國微軟公司開發的Windows環境下的電子表格系統，它是目前應用為廣泛的辦公室表格處理軟件之一。Excel具有強有力的數據庫管理功能、豐富的宏命令和函數、強有力的決策支持工具，它具有以下主要特點。（1）分析能力（2）操作簡便（3）圖表能力（4）數據庫管理能力（5）宏語言功能（6）樣式功能（7）對象連接和嵌入功能（8）連接和合并功能第1頁/共40頁Excel基本操作鼠標操作法數據的手動輸入：數值型，字符型，日期型，和邏輯型公式生成數據：如SUM（求和）、CORREL（求相關系數）、STDEV（求標準差）復制生成數據：公式單元格的復制，一種是值復制，一種是公式復制。 =A2+B2，=$A$1+$B$1，=A$1+B$1（水平變化），與其它軟件交換數據的方法第2頁/共40頁Excel在描述統計中的應用在使用Excel進行數據分析時，要經常使用到Excel中一些函數和數據分析工具。其中，函數是Excel預定義的內置公式。它可以接受被稱為參數的特定數值，按函數的內置語法結構進行特定計算，后返回一定的函數運算結果。例如，SUM函數對單元格或單元格區域執行相加運算，PMT函數在給定的利率、貸款期限和本金數額基礎上計算償還額。函數的語法以函數名稱開始，后面是左圓括號、以逗號隔開的參數和右圓括號。參數可以是數字、文本、形如TRUE或FALSE的邏輯值、數組、形如#N/A的錯誤值，或單元格引用。給定的參數必須能產生有效的值。參數也可以是常量、公式或其它函數。

Excel還提供了一組數據分析工具，稱為“分析工具庫”，在建立復雜的統計分析時，使用現成的數據分析工具，可以節省很多時間。只需為每一個分析工具提供必要的數據和參數，該工具就會使用適宜的統計或數學函數，在輸出表格中顯示相應的結果。其中的一些工具在生成輸出表格時還能同時產生圖表。如果要瀏覽已有的分析工具，可以單擊“工具”菜單中的“數據分析”命令。如果“數據分析”命令沒有出現在“工具”菜單上，則必須通過“工具”菜單中的“加載宏”命令，在“加載宏”對話框中選擇并啟動它。第3頁/共40頁單變量分析，提供數據趨中性和易變性等有關信息。1．用鼠標點擊工作表中待分析數據的任一單元格。

2．選擇“工具”菜單的“數據分析”子菜單。

3．用鼠標雙擊數據分析工具中的“描述統計”選項。

4．出現“描述統計”對話框，對話框內各選項的含義如下：輸入區域：在此輸入待分析數據區域的單元格范圍。一般情況下Excel會自動根據當前單元格確定待分析數據區域。分組方式：如果需要指出輸入區域中的數據是按行還是按列排列，則單擊“行”或“列”。標志位于第一行/列：如果輸入區域的第一行中包含標志項（變量名），則選中“標志位于第一行”復選框；如果輸入區域的第一列中包含標志項，則選中“標志位于第一列”復選框；如果輸入區域沒有標志項，則不選任何復選框，Excel將在輸出表中生成適宜的數據標志。第4頁/共40頁均值置信度：若需要輸出由樣本均值推斷總體均值的置信區間，則選中此復選框，然后在右側的編輯框中，輸入所要使用的置信度。第K個大/小值：如果需要在輸出表的某一行中包含每個區域的數據的第K個大/小值，則選中此復選框。然后在右側的編輯框中，輸入K的數值。輸出區域：在此框中可填寫輸出結果表左上角單元格地址,用于控制輸出結果的存放位置。整個輸出結果分為兩列,左邊一列包含統計標志項，右邊一列包含統計值。根據所選擇的“分組方式”選項的不同，Excel將為輸入表中的每一行或每一列生成一個兩列的統計表。新工作表：單擊此選項，可在當前工作簿中插入新工作表，并由新工作表的A1單元格開始存放計算結果。如果需要給新工作表命名，則在右側編輯框中鍵入名稱。新工作簿：單擊此選項，可創建一新工作簿，并在新工作簿的新工作表中存放計算結果。匯總統計：指定輸出表中生成下列統計結果，則選中此復選框。這些統計結果有：平均值、標準誤差、中值、眾數、標準偏差、方差、峰值、偏度、極差（全距）小值、大值、總和、樣本個數。填寫完“描述統計”對話框之后，按“確定”按扭即可。第5頁/共40頁直方圖工具1．用鼠標點擊表中待分析數據的任一單元格。

2．選擇“工具”菜單的“數據分析”子菜單。3．用鼠標雙擊數據分析工具中的“直方圖”選項。4．出現“直方圖”對話框，對話框內主要選項的含義如下：輸入區域：在此輸入待分析數據區域的單元格范圍。接收區域（可選）：在此輸入接收區域的單元格范圍，該區域應包含一組可選的用來計算頻數的邊界值。這些值應當按升序排列。只要存在的話，Excel將統計在各個相鄰邊界值之間的數據出現的次數。如果省略此處的接收區域，Excel將在數據組的小值和大值之間創建一組平滑分布的接收區間。標志：如果輸入區域的第一行或第一列中包含標志項，則選中此復選框；如果輸入區域沒有標志項，則清除該復選框，Excel將在輸出表中生成適宜的數據標志。輸出區域：在此輸入結果輸出表的左上角單元格的地址。如果輸出表將覆蓋已有的數據，Excel會自動確定輸出區域的大小并顯示信息。柏拉圖：選中此復選框，可以在輸出表中同時顯示按降序排列頻率數據。如果此復選框被清除，Excel將只按升序來排列數據。累積百分比：選中此復選框，可以在輸出結果中添加一列累積百分比數值，并同時在直方圖表中添加累積百分比折線。如果清除此選項，則會省略以上結果。圖表輸出：選中此復選框，可以在輸出表中同時生成一個嵌入式直方圖表。

5．按需要填寫完“直方圖”對話框之后，按“確定”按扭即可。結果說明：完整的結果通常包括三列和一個頻率分布圖，第一列是數值的區間范圍，第二列是數值分布的頻數，第三列是頻數分布的累積百分比。第6頁/共40頁利用Excel繪制散點圖第7頁/共40頁數據透視表工具1．選中圖3中表格中有數據的任一單元格，然后選擇“數據”菜單的“數據透視表”子菜單，進入數據透視表向導。2．選擇“MicrosoftExcel數據清單或數據庫”為數據源。單擊“下一步”。3．選擇待分析的數據的區域，一般情況下Excel會自動根據當前單元格確定待分析數據區域，因此你只要直接單擊“下一步”按扭即可。4．確定數據透視表的結構，在此例中，要建立的是一個交叉頻數表，分別按語文和數學的成績對學生的人數進行交叉頻數分析，因此可按圖4將三個項目“學號”、“語文”、“數學”分別拖放到表格的指定部位，并且雙擊“求和項：學號”，將其改為記數項，然后單擊“下一步”按扭。

5．選擇數據透視表的顯示位置之后，單擊“完成按扭”，可出現如圖5所示的數據透視表。第8頁/共40頁排位與百分比工具．用鼠標點擊表中待分析數據的任一單元格。

2．選擇“工具”菜單的“數據分析”子菜單。

3．用鼠標雙擊數據分析工具中的“排位與百分比”選項。4．填寫完“排位與百分比”對話框，單擊“確定”按扭即可。第9頁/共40頁

Excel在推斷統計中的應用二項分布工具第10頁/共40頁（1）簡介在Excel中想要計算二項分布的概率分布、累積概率，需要利用Excel的工作表函數BINOMDIST。函數BINOMDIST適用于固定次數的獨立實驗，實驗的結果只包含成功或失敗二種情況，且每次實驗成功的概率固定不變。例如，已知次品概率的情況下，函數BINOMDIST可以計算10個產品中發現2個次品的概率。以下例子說明如何在Excel中計算二項分布的概率，以及如何建立二項分布圖表。第11頁/共40頁（2）操作步驟例子如下所示，一個推銷員打了六個電話，推銷成功的概率是0.3，那么可以按以下步驟建立推銷成功次數的概率分布圖表。1．如圖6所示，先在Excel之下建立好概率分布表格的框架。

圖6二項分布框架表第12頁/共40頁2.先在B7至F7單元格分別輸入概率計算公式:“=BINOMDIST(A7,6,0.3,0)”，“=BINOMDIST(A7,6,0.3,1)”“=C7-B7”，“=1-C7”，“=1-D7”3．公式的拷貝。選取B7至F7單元格，拖動“填充柄”至F13單元格即可完成公式的拷貝操作。4．下面開始創建二項分布圖表。選取B7至B13單元格，選取“插入”菜單的“圖表”子菜單。5．選擇“柱狀圖”，然后單擊“下一步”。

6．單擊“系列”標簽，單擊“分類(X)軸標志”框，并用鼠標選取A7至A13單元格為圖表X軸的軸標，然后單擊“下一步”。7．分別鍵入圖表名稱“二項分布圖”，X軸名稱“成功次數”，Y軸名稱“成功概率”，單擊“完成”按扭即可生成二項分布圖表。第13頁/共40頁BINOMDIST(number_s,trials,probability_s,cumulative)其中number_s為試驗成功的次數；trials為獨立試驗的次數；probability_s為每次試驗中成功的概率；cumulative為一邏輯值，用于確定函數的形式。如果cumulative為TRUE，函數BINOMDIST返回累積分布函數，即至多number_s次成功的概率；如果為FALSE，返回概率密度函數，即number_s次成功的概率。第14頁/共40頁函數CRITBINOM可稱為BINOMDIST的逆向函數，它返回使累積二項式分布概率P(X<=x)大于等于臨界概率值的小值。CRITBINOM(trials,probability_s,alpha)trials：貝努利實驗次數。probability_s：每次試驗中成功的概率。alpha：臨界概率。舉例:CRITBINOM(6,0.5,0.75)等于4，表明如果每次試驗成功的概率為0.5,那么6試驗中成功的次數小于等于4的概率恰好超過或等于0.75。第15頁/共40頁函數HYPGEOMDIST返回超幾何分布。給定樣本容量、總體容量和樣本總體中成功的次數，函數HYPGEOMDIST返回樣本取得給定成功次數的概率。使用函數HYPGEOMDIST可以解決有限總體的問題，其中每個觀察值或者為成功或者為失敗，且給定樣本區間的所有子集有相等的發生概率。

．語法：HYPGEOMDIST(sample_s,number_sample,population_s,number_population)sample_s：樣本中成功的次數。number_sample：樣本容量。population_s：樣本總體中成功的次數。number_population：樣本總體的容量。

3.舉例：容器里有20塊巧克力，8塊是焦糖的，其余12塊是果仁的。如果從中隨機選出4塊，下面函數計算式計算出只有一塊是焦糖巧克力的概率：YPGEOMDIST(1,4,8,20)=0.363261第16頁/共40頁例如，如果要找出5個反應敏捷的人，且已知具有這種特征的候選人的概率為0.3。以下公式將計算出在找到5個合格候選人之前，需要面試10個候選人的概率NEGBINOMDIST(10,5,0.3)=0.06871．舉例：POISSON(2,5,FALSE)=0.084224表明，若某一收費站每分種通過的轎車平均數量為5輛，那么某一分鐘通只2輛的概率為0.084224。例如，公式NORMDIST(6,5,2,0)返回平均值為5、標準差為2的正態分布當x=6時概率密度函數的數值，公式NORMDIST(60,50,4,1)返回平均值為50、標準差為4的正態分布當x=60時分布函數的數值。NORMSDIST(0)=0.5NORMSINV(0.5)=0第17頁/共40頁t分布函數TDIST1．說明：函數TDIST返回t分布的概率。t分布用于小樣本數據集合的假設檢驗。使用此函數可以代替t分布的臨界值表。2．語法：TDIST(x,degrees_freedom,tails)x：為需要計算分布的數字。degrees_freedom：為表示自由度的整數。tails：指明返回的分布函數是單尾分布還是雙尾分布。如果tails=1，函數TDIST返回單尾分布，TDIST的計算公式為TDIST=P(X>x)。如果tails=2，函數TDIST返回雙尾分布，TDIST的計算公式為TDIST=P(|X|>x)=P(X>xorX<-x)。3．舉例：TDIST(1.96,60,2)=0.054645第18頁/共40頁隨機抽樣的工具1.利用Excel的RANDBETWEEN(TOP,BOTTOM)函數實現總體的重復隨機抽樣。RANDBETWEEN(TOP,BOTTOM)函數可隨機返回介于TOP與BOTTOM之間的整數，抽取此整數對編號的樣本可作為總體的重復隨機抽樣的結果。2.RAND()函數返回的是0與1之間均勻的隨機數，利用Excel數據分析工具中的隨機數發生器，可以生成用戶指定類型分布的隨機數。例如0-1正態分布的隨機數，指定參數的泊松分布的隨機數等。

3.利用Excel易于產生各類型隨機數的特性，可以用類似的方法方便的進行隨機數字模擬試驗與隨機游走模擬試驗。第19頁/共40頁由樣本推斷總體利用Excel的幾個函數，如求平均函數AVERAGE、標準差函數STDEV、t分布函數TINV等的組合使用可以構造出一個專門用于實現樣本推斷總體的Excel工作表。以下例子先計算樣本的平均數和標準差，然后在一定置信水平上估計總體均值的區間范圍。1．構造工作表。如圖9（a）所示，首先在各個單元格輸入以下的內容，其中左邊是變量名，右邊是相應的計算公式。要顯示公式，用鼠標選擇“工具”菜單的“公式審核”子菜單的“公式審核模式”選項即可。2.為表格右邊的公式計算結果定義左邊的變量名。選定A4:B6,A8:B8和A10:B15單元格(先選擇第一部分，再按住CTRL鍵選取另外兩個部分)，選擇“插入”菜單的“名稱”子菜單的“指定”選項，用鼠標點擊“左列”選項,然后點擊“確定”按扭即可。

3．輸入樣本數據，和用戶指定的置信水平0.95,如圖9（b）所示。4．為樣本數據命名。選定D1:D11單元格，選擇“插入”菜單的“名稱”子菜單的“指定”選項，用鼠標點擊“首行”選項,然后點擊“確定”按扭，得到圖9（b）所示的計算結果。以上例子說明如何交叉組合使用Excel的公式和函數，以構造出一個能實現樣本推斷總體有關計算的Excel工作表。實際上，在用Excel進行數據統計處理之時，許多統計功能可以使用和上例類似的方法，通過組合使用Excel的各類統計函數和公式加以實現的。第20頁/共40頁假設檢驗以下例子利用Excel的正態分布函數NORMSDIST、判斷函數IF等，構造一張能夠實現在總體方差已知情況下進行總體均值假設檢驗的Excel工作表。1．構造工作表2.為表格右邊的公式計算結果定義左邊的變量名。選定A3:B4,A6:B8，A10:B11,A13:B15和A17:B19單元格，選擇“插入”菜單的“名稱”子菜單的“指定”選項，用鼠標點擊“左列”選項,然后點擊“確定”按扭即可。3．輸入樣本數據，以及總體標準差、總體均值假設、置信水平數據。

4．為樣本數據命名。選定C1:C11單元格，選擇“插入”菜單的“名稱”子菜單的“指定”選項，用鼠標點擊“首行”選項,然后點擊“確定”按扭，得到如圖11中所示的計算結果。結果說明：該例子的檢驗結果不論是單側還是雙側均為拒絕Ho假設。所以，根據樣本的計算結果，在5%的顯著水平之下,拒絕總體均值為35的假設。同時由單側顯著水平的計算結果還可以看出，在總體均值是35的假設之下，樣本均值小于等于31.4的概率僅為0.02030363。第21頁/共40頁假設檢驗第22頁/共40頁雙樣本等均值假設檢驗雙樣本等均值檢驗是在一定置信水平之下,在兩個總體方差相等的假設之下，檢驗兩個總體均值的差值等于指定平均差的假設是否成立的檢驗。我們可以直接使用在Excel數據分析中提供雙樣本等均值假設檢驗工具進行假設檢驗。以下通過一例說明雙樣本等均值假設檢驗的操作步驟。例子如下，某工廠為了比較兩種裝配方法的效率，分別組織了兩組員工，每組9人，一組采用新的裝配方法，另外一組采用舊的裝配方法。18個員工的設備裝配時間圖12中表格所示。根據以下數據，是否有理由認為新的裝配方法更節約時間？以上例子可按如下步驟進行假設檢驗。第23頁/共40頁第24頁/共40頁第25頁/共40頁正態性的X2檢驗χ2檢驗可以用來判斷所觀測的樣本是否來自某一特定分布的總體，這種檢驗亦稱為一致性檢驗。以下例子，已知某樣本的相關統計量和分組頻數分布如圖14所示，試用X2檢驗判斷該樣本是否來自一正態總體。第26頁/共40頁第27頁/共40頁第28頁/共40頁按同樣的方法可以作總體泊松分布、總體超幾何分布等其它分布的檢驗。此類統計應用也是由Excel各類公式和函數綜合使用而實現的,為了以后使用方便，和上面的一些例子一樣，一般需要將整個表格的計算框架和標志項羅列好，再保存成文件，以后只要對數據項稍作修改即可很快得到計算結果。如果對Excel宏語言較為熟悉，還可以將它編成一個宏語言程序，加入Excel的工具欄，這樣以后使用起來更為方便。第29頁/共40頁列聯表分析列聯表分析經常用來判斷同一個調查的對象的兩個特性之間是否存在明顯相關關系。例如，房地產商常常設計列聯表問卷，調查顧客的職業和顧客所選房子的類型是否有明顯的相關關系。列聯表分析同樣也可以由Excel加以實現，下面用一個例子給予說明。如圖19所示，表中是某裝修公司的調查報告，試用列聯表分析方法分析在顧客的所在地區和所選房子的地板類型之間是否存在明顯的相關關系。第30頁/共40頁操作步驟1．建立期望頻數表。如圖19所示，先建立期望頻數表的框架，然后在B10單元格輸入公式“=B$7*$F4/$F$7”，再利用“填充柄”將公式復制到表格的其它單元格，后利用Excel的求和函數sum計算行和與列和。2．計算X2

概率值。在A15單元格輸入標志項“卡方概率值”，先點擊B15單元格，從“插入”菜單中“函數”子菜單，選擇“統計函數”中的“CHITEST”函數，單擊“確定按扭，然后在彈出的對話框中分別添入實際頻數范圍“B4:E6”和預期頻數范圍“B10:E12”。后單擊“確定”按扭即可得到計算結果1E-07,如圖20所示。第31頁/共40頁（3）結果分析：以上的操作步驟完成整個列聯表的分析。其中，B15單元格的卡方概率值與B24單元格的卡方統計量是表格的兩個重要計算結果。其中卡方概率值等于1E-07，表明如果總體的兩類屬性是不相關的，那么得到以上觀察的樣本的概率是0.0000001。這個概率幾乎接近于0，所以可以認為總體的這兩個屬性是顯著相關的。第32頁/共40頁單因素方差分析單因素方差分析可用于檢驗兩個或兩個以上的總體均值相等的假設是否成立。此方法是對雙均值檢驗（如t-檢驗）的擴充。檢驗假定總體是服從正態分布的，總體方差是相等的，并且隨機樣本是獨立的。這種工具使用于完全隨機化試驗的結果分析。例子如圖22中所示，一產品制造商雇傭銷售人員向銷售商打電話。制造商想比較四種不同電話頻率計劃的效率，他從銷售人員中隨機選出32名，將他們隨機分配到4種計劃中，在一段時期內記錄他們的銷售情況已經在表中列出，