統(tǒng)計學(xué)基礎(chǔ)9-Excel數(shù)據(jù)分析與統(tǒng)計計算課件

9Excel數(shù)據(jù)分析與統(tǒng)計計算通過本章的學(xué)習(xí)，讀者應(yīng)重點(diǎn)掌握以下內(nèi)容:Excel特點(diǎn)及功能；利用Excel對原始數(shù)據(jù)進(jìn)行分組整理，制作統(tǒng)計表和統(tǒng)計圖；利用Excel計算各種靜態(tài)指標(biāo)和動態(tài)指標(biāo)；利用Excel計算兩類總指數(shù)、對指數(shù)體系進(jìn)行因素分析；利用Excel作隨機(jī)抽樣和區(qū)間估計；利用Excel計算相關(guān)系數(shù)、作一元回歸分析。

教學(xué)目標(biāo)關(guān)鍵詞匯統(tǒng)計分析（StatisticalAnalysis）統(tǒng)計推理(StatisticalInference)回歸分析(RegressionAnalysis)9Excel數(shù)據(jù)分析與統(tǒng)計計算通過本章的學(xué)習(xí)，讀9Excel數(shù)據(jù)分析與統(tǒng)計計算9.1Excel統(tǒng)計應(yīng)用概述9.2數(shù)據(jù)整理中Excel的應(yīng)用9.3靜態(tài)指標(biāo)分析中Excel的應(yīng)用9.4動態(tài)指標(biāo)分析中Excel的應(yīng)用9.5統(tǒng)計指數(shù)中Excel的應(yīng)用9.6抽樣推斷中Excel的應(yīng)用9.7相關(guān)分析和回歸分析中Excel的應(yīng)用◎知識歸納◎習(xí)題與思考題9Excel數(shù)據(jù)分析與統(tǒng)計計算9.1Excel統(tǒng)計應(yīng)用9.1Excel統(tǒng)計應(yīng)用概述9.1.1Excel的統(tǒng)計功能1、Excel的函數(shù)統(tǒng)計功能函數(shù)是Excel預(yù)定義的內(nèi)置公式，它可以接受被稱為參數(shù)的特定值，按函數(shù)的內(nèi)置語法進(jìn)行特定的運(yùn)算，返回一定的函數(shù)運(yùn)算結(jié)果。Excel提供了豐富的函數(shù)，其中統(tǒng)計函數(shù)就多達(dá)80種。在Excel中，要使用函數(shù)，通常有兩種方法:一種是點(diǎn)擊“插入”菜單，選擇“函數(shù)”命令，然后在“插入函數(shù)”對話框中選擇具體函數(shù)，即可打開函數(shù)參數(shù)設(shè)置對話框進(jìn)行參數(shù)設(shè)置；另一種是直接在參數(shù)單元格內(nèi)輸入函數(shù)及參數(shù)。

2、Excel的數(shù)據(jù)管理功能利用Excel可以把工作表中的數(shù)據(jù)做成一個類似數(shù)據(jù)庫的數(shù)據(jù)清單，可以實現(xiàn)記錄的增加、修改、刪除、查找與編輯，具有排序、篩選、分類匯總功能。數(shù)據(jù)清單中的列是數(shù)據(jù)庫的字段，列標(biāo)志是字段名稱，數(shù)據(jù)清單中的每一行對應(yīng)數(shù)據(jù)庫中的一個記錄。

3、Excel繪圖功能

Excel具有強(qiáng)大的繪制統(tǒng)計圖表功能，可以繪制柱狀圖、條形圖、折線圖、餅圖、散點(diǎn)圖、面積圖、圓形圖、雷達(dá)圖、曲面圖、氣泡圖、股價圖、圓錐圖、棱錐圖等，而且每一種類型又提供了幾種不同的子類型，利用圖表向?qū)Ь涂奢p松完成。9.1Excel統(tǒng)計應(yīng)用概述9.1.1Excel的統(tǒng)計功4、分析工具庫“分析工具庫”包括下述工具:方差分析、相關(guān)系數(shù)、協(xié)方差、描述統(tǒng)計、指數(shù)平滑、雙樣本F檢驗分析、傅利葉分析、直方圖、移動平均、隨機(jī)數(shù)發(fā)生器、排位與百分比排位、回歸分析、抽樣分析、t檢驗、z檢驗。若要使用這些工具，單擊“工具”菜單中的“數(shù)據(jù)分析”命令，在“數(shù)據(jù)分析”對話框中選擇相應(yīng)分析工具即可。如果“工具”菜單中沒有顯示“數(shù)據(jù)分析”命令，則需要裝載“分析工具庫”。單擊“工具”菜單中“加載宏”，打開加載宏對話框，復(fù)選框“分析工具庫”即可。4、分析工具庫9.1.2Excel公式與函數(shù)的操作

公式是Excel中的核心，它由單元格內(nèi)的一系列數(shù)值(或者能夠代表數(shù)值的單元格、單元格區(qū)域名稱)、運(yùn)算符和工作表函數(shù)組成。利用公式，用戶不僅可以進(jìn)行數(shù)學(xué)運(yùn)算(如加、減、乘、除等)和邏輯判斷(如真與假)，還可以完成工作表數(shù)據(jù)比較及文本合并等。函數(shù)是Excel中內(nèi)置的公式，函數(shù)使各種運(yùn)算變得更加簡便。如能熟練掌握函數(shù)，并能加以利用，可大大提高工作效率。與公式相比，函數(shù)具有特定的語法結(jié)構(gòu)，必須嚴(yán)格遵守，否則Excel會彈出錯誤提示信息。函數(shù)以函數(shù)名稱(如SUM)開始，緊接著是左圓括號、參數(shù)1、參數(shù)2、…、右圓括號，當(dāng)有多個參數(shù)時，參數(shù)間以逗號分隔。使用函數(shù)時，函數(shù)名稱前的符號“=”和其后的括號“()”是必不可少的，因此“=”和“()”被稱為函數(shù)的結(jié)構(gòu)。所謂參數(shù)是指函數(shù)中用來執(zhí)行操作或計算的值。一個函數(shù)可以沒有參數(shù)，也可有一個或多個參數(shù)。常用的參數(shù)類型有:數(shù)值、文本、形如TRUE或FALSE的邏輯值、數(shù)組、引用單元格、錯誤值#N／A等。常量、公式或其他函數(shù)也可在函數(shù)中作為參數(shù)。如在IF函數(shù)“=IF(AVERAGE(B3:G3)>50,SUM(B4:G4)，0)”中，有AVERAGE函數(shù)、SUM函數(shù)作為其參數(shù)。9.1.2Excel公式與函數(shù)的操作公式是1、公式的創(chuàng)建與編輯

(1)公式的創(chuàng)建為了建立公式，必須首先鍵入“=”號，以表示編輯公式即將開始。在一個完整的公式中，Excel將從左到右采用運(yùn)算符，并按照運(yùn)算符的優(yōu)先順序進(jìn)行計算。如果要更改公式的運(yùn)算順序，需要使用圓括號。一般可使用下列操作步驟建立公式：選擇要建立公式的單元格。鍵入“=”。一般先將鼠標(biāo)指針指向編輯欄，并單擊，然后輸入等號“=”；如果要利用Excel函數(shù)建立公式，則可在編輯欄左側(cè)單擊插入函數(shù)按鈕。輸入公式的內(nèi)容，比如“=SUM(B4:G4)”。為了完成公式的編輯，確認(rèn)輸入的公式，可按Enter鍵或用鼠標(biāo)單擊編輯欄左側(cè)的“√”按鈕；如果要取消編輯的公式，則可單擊編輯欄的“×”按鈕。（2）公式中的運(yùn)算符顧名思義，運(yùn)算符就是用來進(jìn)行運(yùn)算的符號，在Excel中有4種類型的運(yùn)算符，即算術(shù)運(yùn)算符、比較運(yùn)算符、文本運(yùn)算符和引用運(yùn)算符。①算術(shù)運(yùn)算符算術(shù)運(yùn)算符可以完成基本的數(shù)學(xué)運(yùn)算，如加、減、乘、除、乘方(如乘冪的2次方)、開方(如乘冪的1／2=0.5次方)等。②比較運(yùn)算符比較運(yùn)算符可以用于比較兩個值，并產(chǎn)生邏輯值True或False。1、公式的創(chuàng)建與編輯③文本運(yùn)算符在Excel中，文本運(yùn)算符只有一種，即“＆”，該運(yùn)算符可將文本或數(shù)值連接成串。④引用運(yùn)算符除了以上運(yùn)算符外，當(dāng)Excel公式引用一個單元格區(qū)域或多個單元格區(qū)域時，還需要采用引用符號。常用的引用運(yùn)算符有個“:”，“，”，“”(空格)，分別表示引用范圍、合并引用、交叉引用。當(dāng)引用一個單元格區(qū)域時，需要知道區(qū)域的左上角位置和右下角位置，如左上角位置為第2列第5行(B5)，右下角位置為第4列第8行(D8)，則該區(qū)域可表示為“B5:D8”。如果要計算三個單元格區(qū)域“B5:D8”，“A1:A6”和“H1:H5”之間的和，則可在編輯欄輸入“=SUM(B5:D8,A1:A6,H1:H5)”來完成。⑤運(yùn)算符的優(yōu)先順序如果公式中包含了相同優(yōu)先級的運(yùn)算符(如+、-)，則計算從左到右進(jìn)行。當(dāng)公式中包括有多個運(yùn)算符時，必須考慮優(yōu)先級別的問題。只有弄清了運(yùn)算符的優(yōu)先級別，哪些運(yùn)算符的優(yōu)先級別相同，哪些優(yōu)先級別排在前面，哪些排在后面，才不至于造成運(yùn)算的錯誤。

(3)公式編輯編輯公式時，單元格絕對引用和混合引用需要輸入“＄”號，絕對引用要分別在列標(biāo)和行號之前輸入“＄”號，混合引用要在列標(biāo)或行號之前輸入“＄”號。輸入“＄”號時，需要將輸入方式改變?yōu)橛⑽模倚璋碨hift鍵輸入“＄”號。為了操作更簡便，可采用F4功能鍵解決此問題。如需對單元格A1加“＄”號，可將光標(biāo)放在A1的旁邊，第一次按F4功能鍵A1會變?yōu)椤鏏＄1，第二次按F4功能鍵會變?yōu)锳＄1，第三次按F4功能鍵會變?yōu)椤鏏1，第四次按F4功能鍵又會返回到相對引用狀態(tài)，即A1。③文本運(yùn)算符

在默認(rèn)狀態(tài)下，在輸入公式的單元格中顯示的只是結(jié)果，而不是公式。若想在單元格中顯示公式，可以使用快捷鍵“Ctrl+”進(jìn)行公式內(nèi)容與結(jié)果的切換。

2、Excel常用函數(shù)

Excel為用戶提供了11類(數(shù)學(xué)和三角函數(shù)、統(tǒng)計函數(shù)、日期與時間函數(shù)、文本函數(shù)、邏輯函數(shù)、查詢和引用函數(shù)、數(shù)據(jù)庫函數(shù)、信息函數(shù)、工程函數(shù)、財務(wù)函數(shù)、用戶定義函數(shù))，約400個函數(shù)。數(shù)據(jù)分析涉及的數(shù)學(xué)函數(shù)、統(tǒng)計函數(shù)、日期與時間函數(shù)、文本函數(shù)、邏輯函數(shù)等較多，應(yīng)用時可參見本書的有關(guān)章節(jié)。

返回在默認(rèn)狀態(tài)下，在輸入公式的單元格中顯示的只是9.2統(tǒng)計整理中Excel的應(yīng)用9.2.1數(shù)據(jù)輸入1、數(shù)據(jù)的手動輸入建立一個新的Excel文件之后，便可進(jìn)行數(shù)據(jù)的輸入操作。Excel中以單元格為單位進(jìn)行數(shù)據(jù)的輸入操作。一般用上下左右光標(biāo)鍵，Tab鍵或鼠標(biāo)選中某一單元格，然后輸入數(shù)據(jù)。

Excel中的數(shù)據(jù)按類型不同通?？煞譃樗念?數(shù)值型、字符型、日期型和邏輯型。Excel根據(jù)輸入數(shù)據(jù)的格式自動判斷數(shù)據(jù)屬于什么類型。如日期型的數(shù)據(jù)輸入格式為“年／月／日”、“年月日”或“時：分：秒”。要輸入邏輯型的數(shù)據(jù)，輸入“True”(真)或“False”(假)即可。若數(shù)據(jù)由數(shù)字與小數(shù)點(diǎn)構(gòu)成，Excel自動將其識別為數(shù)字型，Excel允許在數(shù)值型數(shù)據(jù)前加入貨幣符號，Excel將其視為貨幣數(shù)值型，Excel也允許數(shù)值型數(shù)據(jù)用科學(xué)計數(shù)法表示，如2109在Excel中可表示為2E+9。除了以上三種格式以外的數(shù)據(jù)，Excel將其視為字符型處理。9.2統(tǒng)計整理中Excel的應(yīng)用9.2.1數(shù)據(jù)輸入2、公式生成數(shù)據(jù)

Excel的數(shù)據(jù)中也可由公式直接生成。例如，在當(dāng)前工作表中A1和B1單元格中已輸入了數(shù)值數(shù)據(jù)，欲將A1與B1單元格的數(shù)據(jù)相加的結(jié)果放入C1單元格中，可按如下步驟操作:用鼠標(biāo)選定C1單元格，然后輸入公式“=A1+B1”或輸入“=SUM(a1:b1)”，回車之后即可完成操作。C1單元格此時存放實際上是一個數(shù)學(xué)公式“A1+B1”，因此C1單元格的數(shù)值將隨著A1、B1單元格的數(shù)值的改變而變化。Excel提供了完整的算術(shù)運(yùn)算符，如+(加)、-(減)、(乘)、／(除)、％(百分比)、^(指數(shù))和豐富的函數(shù)，如SUM(求和)、CORREL(求相關(guān)系數(shù))、STDEV(求標(biāo)準(zhǔn)差)等，供用戶對數(shù)據(jù)執(zhí)行各種形式的計算操作。在Excel幫助文件中可以查到各類算術(shù)運(yùn)算符和函數(shù)的完整使用說明。2、公式生成數(shù)據(jù)3、復(fù)制生成數(shù)據(jù)

Excel中的數(shù)據(jù)也可由復(fù)制生成。實際上，在生成的數(shù)據(jù)具有相同的規(guī)律性的時候，大部分的數(shù)據(jù)可以由復(fù)制生成?？梢栽诓煌瑔卧裰g復(fù)制數(shù)據(jù)，也可以在不同工作表或不同工作簿之間復(fù)制數(shù)據(jù)，可以一次復(fù)制一個數(shù)據(jù)，也可同時復(fù)制一批數(shù)據(jù)，為數(shù)據(jù)輸入帶來了極大的方便。普通單元格的復(fù)制結(jié)果與公式單元格的復(fù)制結(jié)果相差較大，下面分別予以說明。（1）普通單元格指的是非公式的單元格。①拖動鼠標(biāo)選定待復(fù)制的區(qū)域，選定之后該區(qū)域變?yōu)楹谏?。Excel可以進(jìn)行整行、整列或整個表格的選定操作。例如，如果要選定表格的第一列，可直接用鼠標(biāo)單擊列標(biāo)“A”，如果要選定表格的第一行，可直接用鼠標(biāo)單擊行標(biāo)“1”，如果要選定整個表格，可直接點(diǎn)擊最左上角的全選按鈕。②選定區(qū)域之后，用鼠標(biāo)右擊該區(qū)域，在彈出的菜單中選擇“復(fù)制”命令，將區(qū)域內(nèi)容復(fù)制到粘貼板之中?？梢园l(fā)現(xiàn)該區(qū)域已被虛線包圍。③用鼠標(biāo)右擊目標(biāo)區(qū)域，在彈出的菜單中選擇“粘貼”命令，則單元格區(qū)域的復(fù)制即告完成。3、復(fù)制生成數(shù)據(jù)

（2）公式單元格的復(fù)制一般可分為兩種，一種是值復(fù)制，一種是公式復(fù)制。值復(fù)制指的是只復(fù)制公式的計算結(jié)果到目標(biāo)區(qū)域，公式復(fù)制指的是僅復(fù)制公式本身到目標(biāo)區(qū)域。下面對它們的操作步驟分別予以說明。①值復(fù)制:a拖動鼠標(biāo)選定待復(fù)制區(qū)域。

b用鼠標(biāo)右擊選定區(qū)域，選擇“復(fù)制”選項。

c用鼠標(biāo)右擊目標(biāo)區(qū)域，再單擊“選擇性粘貼”子菜單。出現(xiàn)復(fù)制選項，選定“數(shù)值”選項，然后單擊“確定”按鈕，則值復(fù)制即告完成。②公式復(fù)制:公式復(fù)制是Excel數(shù)據(jù)成批計算的重要操作方法。要熟練公式復(fù)制的操作首先要區(qū)分好兩個概念:單元格的相對引用與絕對引用。

4、數(shù)據(jù)自動填充數(shù)據(jù)的自動填充可以采用“填充柄”、“Ctrl+Enter”組合鍵或“填充”命令來完成。利用“填充”命令進(jìn)行自動填充時，首先要在一個單元格中輸入第一個數(shù)據(jù)，然后選定一個單元格區(qū)域，使輸入的第一個數(shù)據(jù)位于單元格區(qū)域的頂行、底行、最左邊或最右邊，單擊“編輯”菜單中的“填充”命令，在出現(xiàn)的級連菜單中根據(jù)需要選擇“向下填充”、“向上填充”、“向左填充”或“向右填充”，可以完成行或列的填充。以上操作也可以通過選取“填充序列”命令，在“序列”對話框中選擇“自動填充”來完成。通過“序列”對話框，還可以完成等差數(shù)列、等比數(shù)列的填充。（2）公式單元格的復(fù)制一般可分為兩種，一種是值復(fù)5、利用“記錄單”編輯數(shù)據(jù)比較簡單的數(shù)據(jù)清單，數(shù)據(jù)的編輯可以直接在數(shù)據(jù)清單中操作。如果數(shù)據(jù)清單中的數(shù)據(jù)非常復(fù)雜，就可以利用“記錄單”數(shù)據(jù)的操作。數(shù)據(jù)“記錄單”具有瀏覽記錄、添加記錄、修改記錄和刪除記錄等作用。使用“記錄單”命令，可以在數(shù)據(jù)清單中一次輸入、顯示、查找或刪除一行完整記錄。選中數(shù)據(jù)清單中的任意單元格，在“數(shù)據(jù)”菜單中選擇“記錄單”命令，則彈出“數(shù)據(jù)清單”對話框。在“數(shù)據(jù)清單”對話框中，可以完成以下操作:

（1）瀏覽記錄（2）瀏覽符合條件的記錄（3）添加記錄（4）修改記錄（5）刪除記錄5、利用“記錄單”編輯數(shù)據(jù)9.2.2統(tǒng)計分組1、統(tǒng)計數(shù)據(jù)的排序與篩選（1）統(tǒng)計數(shù)據(jù)排序數(shù)據(jù)的排序是以數(shù)據(jù)清單中的一個或幾個字段為關(guān)鍵字，對整個數(shù)據(jù)清單的所有個體進(jìn)行重新排列。排序可以按升序，也可以按降序。對于數(shù)字型字段，排序是按數(shù)值的大??；對于字符型字段，排序是按ASCII碼大??；中文字段按拼音或筆畫排序。通過排序，可以清楚地反映數(shù)據(jù)之間的大小關(guān)系?！癜磫巫侄闻判颉癜炊嘧侄闻判颉纠?.1】6個企業(yè)某年的主要財務(wù)指標(biāo)如圖9.1所示。圖9.1某年度的企業(yè)財務(wù)數(shù)據(jù)9.2.2統(tǒng)計分組1、統(tǒng)計數(shù)據(jù)的排序與篩選a、以“主營業(yè)務(wù)收入”為關(guān)鍵字，按升序排列以上數(shù)據(jù)。選中“主營業(yè)務(wù)收入”字段下任一單元格，再單擊工具欄上的“升序排序”按鈕。

b、以“主營業(yè)務(wù)收入”為主要關(guān)鍵字，“主要業(yè)務(wù)利潤”為次要關(guān)鍵字，“凈利潤”為第三關(guān)鍵字，對數(shù)據(jù)全部進(jìn)行降序排序。單擊“數(shù)據(jù)”菜單中的“排序”命令，彈出“排序”對話框。單擊“主要關(guān)鍵字”下拉列表框右面的下拉按鈕，在下拉列表中選擇“主營業(yè)務(wù)收入”，然后在右邊的單選按鈕中選中“降序”。照此方法，依次設(shè)置“次要關(guān)鍵字”和“第三關(guān)鍵字”。如果需要進(jìn)一步設(shè)置，可單擊“排序”對話框中的左下角的“選項”按鈕，在彈出“排序選項”對話框中進(jìn)行詳細(xì)設(shè)置。在“排序”對話框中，單擊“確定”按鈕，完成數(shù)據(jù)排序。

(2)統(tǒng)計數(shù)據(jù)的篩選利用Excel提供的篩選功能，可以把符合要求的數(shù)據(jù)集中在一起，把不符合要求的數(shù)據(jù)隱藏起來。使用自動篩選的步驟如下:●自動篩選自動篩選是一種快速的篩選方法，它可以方便地將滿足條件的數(shù)據(jù)顯示在工作表上，將不滿足條件的數(shù)據(jù)隱藏起來。使用自動篩選的步驟如下:在數(shù)據(jù)清單中選擇任一單元格為當(dāng)前單元格。選中“數(shù)據(jù)”菜單中的“篩選自動篩選”命令，會看到在數(shù)據(jù)清單中的每一列字段名旁都會出現(xiàn)一個下拉箭頭按鈕。單擊某一個下拉按鈕，在列表框中選定篩選的條件。篩選條件主要有:前10個…、自定義…和具體的數(shù)值等。a、以“主營業(yè)務(wù)收入”為關(guān)鍵字，按升序排列以“前10個…”選項只對數(shù)字型變量有效，篩選的數(shù)據(jù)個數(shù)不一定只是10個，可以根據(jù)需要設(shè)定需要篩選的數(shù)據(jù)個數(shù)。方法是單擊“前10個…”選項，彈出“自動篩選前10個”對話框。在對話框中可以設(shè)定篩選的條件為最大或最小，以及篩選值的項數(shù)(或百分比)。完成自動篩選后，再次單擊數(shù)據(jù)菜單中的“篩選自動篩選”命令將退出自動篩選狀態(tài)，字段名旁的下拉按鈕同時消失?！窀呒壓Y選自動篩選只能適用于比較簡單的條件，如果需要指定的篩選條件比較多，就需要使用Excel的高級篩選功能。高級篩選的關(guān)鍵是條件區(qū)域的設(shè)定。通常是將條件區(qū)域放在整個數(shù)據(jù)清單的下邊(以防止被篩選隱含，并且不改動數(shù)據(jù)清單的位置)，至少要用一個空行隔開。條件區(qū)域的第1行為字段名，第2行及以下各行為條件值。同一行條件之間為“與”的關(guān)系，不同行條件之間為“或”的關(guān)系，可采用的條件符號有＞、＜、≥、≤。設(shè)置好條件區(qū)域后，選定數(shù)據(jù)清單中的任一單元格，在“數(shù)據(jù)”菜單中選擇“篩選高級篩選”命令，在彈出的“高級篩選”對話框中，根據(jù)需要選擇顯示篩選結(jié)果的方式，并分別指定數(shù)據(jù)清單(列表區(qū)域)和條件區(qū)域所在單元格的位置，單擊“確定”按鈕即完成高級篩選。

2、統(tǒng)計數(shù)據(jù)的分組（類）匯總

(1)分類匯總為了使數(shù)據(jù)清單的內(nèi)容更加清晰明確，可以利用Excel的分類匯總功能將數(shù)據(jù)歸組（類），并進(jìn)行求和、均值等計算，并將計算結(jié)果顯示出來，以便對數(shù)據(jù)進(jìn)行進(jìn)一步分析?！扒?0個…”選項只對數(shù)字型變量有效，篩選的數(shù)(2)利用頻數(shù)分布函數(shù)(FREQUENCY)分組單純利用排序與分類匯總還不能很好地描述數(shù)據(jù)的分布狀態(tài)，為此，Excel提供了一個頻數(shù)分布函數(shù)(FREQUENCY)，利用它可以對數(shù)據(jù)進(jìn)行分組，建立頻數(shù)分布，從而更好地描述數(shù)據(jù)分布狀態(tài)。該函數(shù)以一列垂直數(shù)組返回某個區(qū)域中數(shù)據(jù)的頻率分布。例如，使用函數(shù)FREQUENCY可以計算在給定的分?jǐn)?shù)范圍內(nèi)測驗分?jǐn)?shù)的個數(shù)。語法:FREQUENCY(data_array,bins_array)

其中:data_array為一數(shù)組或?qū)σ唤M數(shù)值的引用，用來計算頻率。如果data_array中不包含任何數(shù)值，函數(shù)FREQUENCY返回零數(shù)組。bins_array為間隔的數(shù)組或?qū)﹂g隔的引用，該間隔用于對data_array中的數(shù)值進(jìn)行分組。如果bins_array中不包含任何數(shù)值，函數(shù)FREQUENCY返回data_array中元素的個數(shù)。在選定相鄰單元格區(qū)域(該區(qū)域用于顯示返回的分布結(jié)果)后，函數(shù)FREQUENCY應(yīng)以數(shù)組公式的形式輸入。返回的數(shù)組中的元素個數(shù)比bins_array(數(shù)組)中的元素個數(shù)多1。返回的數(shù)組中所多出來的元素表示超出最高間隔的數(shù)值個數(shù)。例如，如果要計算輸入到三個單元格中的三個數(shù)值區(qū)間(間隔)，一定要在四個單元格中輸入FREQUENCY函數(shù)計算的結(jié)果。多出來的單元格將返回data_array中大于第三個間隔值的數(shù)值個數(shù)。(2)利用頻數(shù)分布函數(shù)(FREQUENCY9.2.3制作統(tǒng)計圖1、統(tǒng)計分析圖表制作

Excel為繪制統(tǒng)計圖表提供了一整套便利的制作技術(shù)，可以從工作表數(shù)據(jù)中創(chuàng)建既復(fù)雜又準(zhǔn)確、漂亮的圖表。

【例9.5】下面以餅圖為例，說明創(chuàng)建圖表一般要經(jīng)過的幾個步驟:

（1）確定制圖的目的，根據(jù)制圖的目的搜集和審核統(tǒng)計資料。（2）將統(tǒng)計資料輸入到Excel中去。例如圖9.4所示教師職稱的原始統(tǒng)計資料。圖9.4教師職稱的原始統(tǒng)計資料9.2.3制作統(tǒng)計圖1、統(tǒng)計分析圖表制作圖9

（3）計算職工總數(shù)。選中單元格B6，在編輯欄內(nèi)輸入公式“=SUM（B2:B5）”，按回車鍵。（4）計算教師比重。選中單元格C2，并把單元格的數(shù)字格式設(shè)成“百分比”，在編輯欄內(nèi)輸入公式“=B2/B5”，按回車鍵。照此方法依次計算單元格C3、C4、C5中的數(shù)據(jù)。選中單元格C6，在編輯欄內(nèi)輸入公式“=SUM（C2:C5）”，按回車鍵，即得教師職稱計算結(jié)果。（5）在想繪制圖的數(shù)據(jù)中任選一個單元格，然后在“插入”菜單中選中“圖表”命令，或單擊工具欄上的“圖表向?qū)А卑粹o，彈出“圖表向?qū)?步驟之1”對話框。在“圖表向?qū)?步驟之1”對話框中包括“取消”、“下一步”、“完成”和“按下不放可查看示例”按鈕。單擊“取消”即停止創(chuàng)建圖表，單擊“按下不放可查看示例”可以查看所選圖表的效果，完成指定圖表屬性后即可單擊“完成”，圖表就立刻被創(chuàng)建出來。（6）決定圖形。在“圖表向?qū)?步驟之1圖表類型”對話框中選中所需圖表類型，在子圖表類型中再選中一種子類型，比如餅圖三維餅圖，單擊“下一步”按鈕。（7）確定要繪制圖表的數(shù)據(jù)范圍。此步可規(guī)定圖表使用的數(shù)據(jù)區(qū)域以及每一數(shù)據(jù)系列名字和數(shù)值的區(qū)域。進(jìn)入“圖表向?qū)?步驟之2圖表源數(shù)據(jù)”對話框，單擊“數(shù)據(jù)區(qū)域”后輸入框右側(cè)的“拾取”按鈕，從工作表中選擇單元格區(qū)域A1:A5,C1:C5,在“系列產(chǎn)生在”后的單選框中選“列”。（3）計算職工總數(shù)。選中單元格B6，在編輯(8)給圖表命名。單擊“下一步”按鈕進(jìn)入“圖表向?qū)?步驟之3圖表選項”對話框，在“標(biāo)題”選項卡的“圖表標(biāo)題”下的文本框中輸入“教師職稱結(jié)構(gòu)”。單擊“數(shù)據(jù)標(biāo)志”選項卡，在“數(shù)據(jù)標(biāo)簽包括”下的復(fù)選框中選擇“百分比”和“類別名稱”，如圖9.5所示。圖9.5圖表向?qū)?步驟之3圖表選項(8)給圖表命名。單擊“下一步”按鈕進(jìn)入“圖

（9）圖表位置的選中。單擊“下一步”按鈕，彈出“圖表向?qū)?步驟之4圖表位置”對話框。有兩個選項：“新工作表”意味著將圖表作為一個對象放在一個獨(dú)立的工作表中；“嵌入工作表”是將圖表作為一個對象放在工作表中。選擇默認(rèn)選項，單擊“完成”按鈕，即可完成圖表制作。（10）鼠標(biāo)指向圖中的標(biāo)識雙擊，都可以對圖形中的內(nèi)容進(jìn)行修飾。如鼠標(biāo)指向“教師職稱結(jié)構(gòu)”雙擊，彈出“圖表標(biāo)題格式”對話框。在“字體”選項卡中設(shè)置字體為“隸書、常規(guī)、14號”，即可修改圖形。

2直方圖制作

【例9.6】以例9.3資料為例，說明如何用直方圖分析工具對學(xué)生成績進(jìn)行分析。

(1)打開工作表，將成績按升序排序。

(2)在單元格D1輸入文字“分組”，在單元格區(qū)域D2:D6分別輸入59、69、79、89、99，表示分組區(qū)間的間隔點(diǎn)。

(3)選擇“工具”菜單下的“數(shù)據(jù)分析”選項，在“數(shù)據(jù)分析”對話框中選擇“直方圖”，單擊“確定”按鈕。

(4)在“直方圖”對話框的“輸入?yún)^(qū)域”后輸入單元格區(qū)域＄B＄1:＄B＄11；在“接收區(qū)域”后輸入＄D＄1:＄D＄6，選中“標(biāo)志”復(fù)選框；在“輸出區(qū)域”后輸入＄E＄1，選中“圖表輸出”和“累積百分率”復(fù)選框，如圖9.6所示。（9）圖表位置的選中。單擊“下一步”按鈕，圖9.6“直方圖”對話框選項設(shè)置(5)單擊“確定”按鈕，Excel會同時生成一個頻率分布表和一個圖表。注意:直方圖顯示等于或大于區(qū)間值并小于下一個區(qū)間值的輸入值的數(shù)量，并將其顯示于“頻率”列中。表中的最后一個值等于或大于最后一個區(qū)間值的輸入值的數(shù)量。返回圖9.6“直方圖”對話框選項設(shè)置(5)單9.3靜態(tài)指標(biāo)分析中Excel的應(yīng)用9.3.1用函數(shù)方法計算均值和變異指標(biāo)

統(tǒng)計數(shù)據(jù)的描述均可以利用Excel中提供的有關(guān)統(tǒng)計函數(shù)和公式或描述統(tǒng)計分析工具來完成。下面就描述統(tǒng)計中有關(guān)靜態(tài)平均指標(biāo)、標(biāo)志變異指標(biāo)如何在Excel中進(jìn)行計算加以說明。

1、靜態(tài)平均指標(biāo)靜態(tài)平均指標(biāo)有算術(shù)平均數(shù)、調(diào)和平均數(shù)、位置平均數(shù)(眾數(shù)、中位數(shù))等。

(1)算術(shù)平均數(shù)算術(shù)平均數(shù)分為簡單算術(shù)平均數(shù)和加權(quán)算術(shù)平均數(shù)。簡單算術(shù)平均數(shù)可利用AVERAGE函數(shù)計算。例如，某公司10名高級管理人員的工資(單位:元)分別為:2500、2000、3000、2600、2100、2300、2500、3500、4000、3600，則平均工資的計算過程為:

首先，將這10名高級管理人員的月工資輸入A1～A10單元格內(nèi)，排序不排序均可。然后，單擊任一空單元格，輸入“=AVERAGE(A1:A10)”，回車確定即可得到月平均工資2830元/人。加權(quán)算術(shù)平均數(shù)，首先列出計算表，然后利用公式計算。9.3靜態(tài)指標(biāo)分析中Excel的應(yīng)用9.3.1用函數(shù)方

（2）調(diào)和平均數(shù)簡單調(diào)和平均數(shù)可以用HARMEAN函數(shù)計算。例如，買同一種菜，分三次買，每次價格不同，分別為0.25元／千克、0.4元／千克、0.50元／千克，則可先單擊任一單元格，再輸入“=HARMEAN(0.25，0.4，0.5)”，確定后，便得到三次購買的平均價格0.38元。加權(quán)調(diào)和平均數(shù)也可列表按照公式計算。例如，有一加權(quán)調(diào)和平均數(shù)計算資料，將金額和價格輸入后，如表9.5所示。表9.5金額和價格資料

下面先計算購買量:單擊D2單元格，輸入公式“=B2／C2”后回車，得出第一次購買量5，并利用填充柄功能計算出第二、第三次購買量。然后單擊D5單元格，輸入“=SUM(D2:D4)”求出購買總量，并利用填充柄功能，計算出總金額，最后單擊任一空格單元格，輸入“=B5／D5”，即得出加權(quán)調(diào)和平均價格1.85元。（2）調(diào)和平均數(shù)表9.5金額和價格資料

（3）位置平均數(shù)位置平均數(shù)有眾數(shù)和中位數(shù)，如果掌握的數(shù)據(jù)是原始資料，將原始數(shù)據(jù)輸入Excel后，可利用統(tǒng)計函數(shù)MODE、MEDIAN分別計算。如果掌握的數(shù)據(jù)是分組資料，可按下限或上限公式計算。

2標(biāo)志變異指標(biāo)標(biāo)志變異指標(biāo)有全距、平均差、標(biāo)準(zhǔn)差和標(biāo)志變異系數(shù)。（1）全距如果數(shù)據(jù)排序后，那么最大標(biāo)志值和最小標(biāo)志值自然就顯示出來，全距就容易求出。如果數(shù)據(jù)沒有排序，可用MAX和MIN函數(shù)求得最大標(biāo)志值和最小標(biāo)志值來計算全距。（2）平均差（3）標(biāo)準(zhǔn)差在Excel中，對于未分組的資料，可直接用統(tǒng)計函數(shù)STDEV計算標(biāo)準(zhǔn)差；對于已分組的資料，由于各組權(quán)數(shù)不同，計算時必須加權(quán)計算。其操作過程和上例求平均差類似，在計算E列時，輸入“=(C2-920)^2”或輸入“=(C2-920)*(C2-920)”，即可計算（x-x）2項。（4）標(biāo)志變異系數(shù)標(biāo)準(zhǔn)差除以算術(shù)平均數(shù)得到標(biāo)志變異系數(shù)，因此，要計算標(biāo)志變異系數(shù)，可先按照上述方法計算算術(shù)平均數(shù)和標(biāo)準(zhǔn)差，再計算標(biāo)志變異系數(shù)。（3）位置平均數(shù)9.3.2用描述統(tǒng)計工具測度均值和變異指標(biāo)

描述統(tǒng)計分析工具用于生成數(shù)據(jù)源區(qū)域中數(shù)據(jù)的單變量統(tǒng)計分析報表，它可以同時計算出一組數(shù)據(jù)的多個常用統(tǒng)計量，提供有關(guān)數(shù)據(jù)集中趨勢和離中趨勢以及分布形態(tài)等方面的信息。

【例9.10】調(diào)查某企業(yè)生產(chǎn)車間10名工人的月工資水平，資料如圖9.7所示。用描述統(tǒng)計工具對工人工資數(shù)據(jù)進(jìn)行分析。圖9.7工資資料9.3.2用描述統(tǒng)計工具測度均值和變異指標(biāo)

首先，選擇“工具”菜單的“數(shù)據(jù)分析”選項，在彈出的“數(shù)據(jù)分析”對話框的分析工具中選擇“描述統(tǒng)計”，單擊“確定”按鈕，彈出“描述統(tǒng)計”對話框?！懊枋鼋y(tǒng)計”對話框包括以下內(nèi)容:

輸入?yún)^(qū)域:輸入待分析數(shù)據(jù)所在的單元格區(qū)域。本例輸入B1:B11。分組方式:如果需要指出輸入?yún)^(qū)域中的數(shù)據(jù)是按行還是按列排列，則單擊“逐行”或“逐列”。本例選擇“逐列”。標(biāo)志位于第一行:若輸入?yún)^(qū)域包括列標(biāo)志行，則必須選中此復(fù)選框。否則，不能選中該復(fù)選框，此時Excel自動以列1、列2、列3……作為數(shù)據(jù)的列標(biāo)志。本例選中此復(fù)選框。輸出選項:下有三個單選按鈕為“輸出區(qū)域”、“新工作表組”和“新工作簿”。如果指定輸出到當(dāng)前工作表的某個單元格區(qū)域，這時需在“輸出區(qū)域”框鍵入輸出單元格區(qū)域的左上角單元格地址；如果指定輸出到新工作表組，這時需要輸入工作表名稱；也可以指定輸出到新工作簿。本例選中將結(jié)果輸出到“輸出區(qū)域”，并輸入左上角單元格地址D1。匯總統(tǒng)計：若選中，則顯示描述統(tǒng)計結(jié)果，否則不顯示結(jié)果。本例選中。均值置信度:若需要輸出包含均值的置信度，則選中此復(fù)選框，然后在右側(cè)的編輯框中，輸入所要使用的置信度。本例輸入95％，表明要計算在顯著性水平為5％時的均值置信度。第K大/小值:如果需要在輸出表的某一行中包含每個區(qū)域的數(shù)據(jù)的第K個最大／小值，則選中此復(fù)選框。然后在右側(cè)的編輯框中，輸入K的數(shù)值。本例均選，并輸入數(shù)值1，表示要求輸出第1大/小的數(shù)值。其次，在“描述統(tǒng)計”對話框中，單擊“確定”按鈕。返回首先，選擇“工具”菜單的“數(shù)據(jù)分析”選項，在9.4動態(tài)指標(biāo)分析中Excel的應(yīng)用9.4.1計算增長量和平均增長量1、計算增長量增長量即增加量，其一般計算公式為:增長量=報告期水平－基期水平。在比較發(fā)展水平時，按采用基期的固定與否，增長量可以分為逐期增長量和累積增長量兩種形式。

【例9.11】某單位本年度12個月的銷售資料如圖9.8所示。用Excel計算逐期增長量，單擊單元格C3，輸入公式“=B3-B2”，回車確定后即得第2期的逐期增長量。利用填充柄功能，即鼠標(biāo)指向C3單元格右下角小黑方塊，鼠標(biāo)指針變?yōu)楹凇笆弊謺r，按下左鍵，并向下拖拽至C13單元格后松開，得出各期的逐期增長量。用Excel計算累積增長量，單擊單元格D3，輸入公式“=B3-750”，回車確定后即得第2期的累積增長量。利用填充柄功能，即鼠標(biāo)指向D3單元格右下角小黑方塊，鼠標(biāo)指針變?yōu)楹凇笆弊謺r，按下左鍵，并向下拖拽至D13單元格后松開，得出各期的累積增長量。9.4動態(tài)指標(biāo)分析中Excel的應(yīng)用9.4.1計算增長量圖9.8某單位本年度銷售資料2、計算平均增長量平均增長量是一種序時平均數(shù)。其計算公式為:平均增長量=第n期發(fā)展水平-基期發(fā)展水平〖〗逐項增長量項數(shù)其中，逐項增長量項數(shù)等于時期數(shù)列項數(shù)1。用Excel計算平均增長量，單擊單元格E1，輸入公式“=（B13-B2）/（12-1）”，回車確定即可得到平均增長量18.18182。圖9.8某單位本年度銷售資料2、計算平均增9.4.2計算發(fā)展速度和平均發(fā)展速度1、計算發(fā)展速度發(fā)展速度是相對指標(biāo)，一般用百分?jǐn)?shù)表示。計算公式為:

根據(jù)采用基期的不同，發(fā)展速度可以分為定基發(fā)展速度和環(huán)比發(fā)展速度。仍以例9.11說明如下：由于基期都是固定的，用Excel計算定基發(fā)展速度與計算累積增長量基本相似，只是公式不同。單擊單元格C3，輸入公式“=（B3/750）*100%”，回車確定后即得第2期的定基發(fā)展速度。利用填充柄功能，即鼠標(biāo)指向C3單元格右下角小黑方塊，鼠標(biāo)指針變?yōu)楹凇笆弊謺r，按下左鍵，并向下拖拽至C13單元格后松開，得出各期的定基發(fā)展速度。用Excel計算環(huán)比發(fā)展速度與用Excel計算逐期增長量基本相似，只是公式不同。單擊單元格D3，輸入公式“=（B3/B2）*100%”，回車確定后即得第2期的環(huán)比發(fā)展速度。利用填充柄功能，即鼠標(biāo)指向D3單元格右下角小黑方塊，鼠標(biāo)指針變?yōu)楹凇笆弊謺r，按下左鍵，并向下拖曳至D13單元格后松開，得出各期的環(huán)比發(fā)展速度。9.4.2計算發(fā)展速度和平均發(fā)展速度1、計算2、計算平均發(fā)展速度平均發(fā)展速度是各期環(huán)比發(fā)展速度的動態(tài)序時平均數(shù)。它自能根據(jù)需要采用幾何平均法和方程法來計算。方程法需要求解比較復(fù)雜的高次方程，實際工作中都根據(jù)事先編制好的“平均發(fā)展速度查對表”來計算。這里只介紹幾何平均法。其計算公式一般采用：，其中，n為逐項增長量項數(shù)即等于時期數(shù)列項數(shù)1；an是動態(tài)數(shù)列第n期發(fā)展水平；a0是動態(tài)數(shù)列初期發(fā)展水平。以上例9.11說明如下：用Excel計算平均發(fā)展速度，單擊單元格E1，輸入公式“=（B13-B2）^（1/（12-1））”，回車確定即可得到平均發(fā)展速度1.02172246。2、計算平均發(fā)展速度9.4.3計算長期發(fā)展趨勢

長期發(fā)展趨勢的測定方法主要有時距擴(kuò)大法、移動平均法和最小平方法。對于時距擴(kuò)大法，可通過SUM函數(shù)(求和函數(shù))方便求得，這里就不再舉例說明。

1、移動平均法在Excel中，移動平均法可使用移動平均工具進(jìn)行。

【例9.12】以圖9.9資料為例，進(jìn)行四項、五項平均。原始資料輸入Excel后，如圖9.9所示A、B兩列。圖9.9移動平均原始數(shù)據(jù)及輸出結(jié)果9.4.3計算長期發(fā)展趨勢長期發(fā)展趨勢的測

首先，進(jìn)行四項移動平均。單擊“工具”菜單中“數(shù)據(jù)分析”命令，選擇“移動平均”工具，打開移動平均設(shè)置對話框。其次，在“輸入?yún)^(qū)域”內(nèi)輸入數(shù)據(jù)所在區(qū)域“＄B＄2:＄B＄17”；“間隔”欄內(nèi)輸入“4”(再進(jìn)行移動平均時輸入“2”)；“輸出區(qū)域”欄內(nèi)輸入放置結(jié)果區(qū)域“＄C＄2:＄C＄17”，確定即可。五項移動平均與此類似。

2、最小平方法利用最小平方法可以對直線趨勢進(jìn)行測定，也可以對曲線趨勢進(jìn)行測定。

【例9.13】以上例9.12資料為例，說明在Excel中如何運(yùn)用最小平方法來建立直線趨勢方程。原始資料輸入Excel后，如圖9.10所示。圖9.10最小平方法的原始數(shù)據(jù)與結(jié)果首先，進(jìn)行四項移動平均。單擊“工具”菜單中“

計算C列。單擊C2，輸入“=A2*A2”，并用填充柄功能計算C3～C17；再計算D列，單擊D2，輸入“=A2*B2”，并利用填充柄功能計算D3～D17。然后計算合計，單擊A18，輸入“=SUM(A2:A17)”得136，再次利用填充柄功能，計算B、C、D各列的合計數(shù)(即18行的值)。下面計算參數(shù)a、b:

先計算b，單擊任一單元格，輸入“=(D18-A18*B18／16)(C18-A18*A18／16)”確定后即得b的值0.2014706。再計算a，單擊任一單元格，輸入“=B18／16-0.2014706*A18/16”得a的值4.725。于是，建立直線趨勢方程:y=4.725+0.2014706t。若計算各對應(yīng)年份的趨勢值，單擊E2，輸入“=4.725+0.2014706*A2”，并用填充柄計算即可。最小平方法實際上就是回歸分析，只不過這里是以時間為可控制變量(X)，所分析的動態(tài)指標(biāo)為因變量(y)，因此可使用LINEST、INTERCEPT、SLOPE等函數(shù)或回歸分析工具來建立趨勢方程。返回計算C列。單擊C2，輸入“=A2*A2”，9.5統(tǒng)計指數(shù)中Excel的應(yīng)用Excel中沒有專門的函數(shù)來計算統(tǒng)計指數(shù)，但是用一般的公式和函數(shù)計算也很方便。下面我們舉例說明綜合指數(shù)和平均數(shù)指數(shù)的計算方法以及如何利用指數(shù)體系進(jìn)行因素分析。9.5.1計算綜合指數(shù)

綜合指數(shù)又分為數(shù)量指標(biāo)綜合指數(shù)和質(zhì)量指標(biāo)綜合指數(shù)。

【例9.14】下面以某商場三種商品的價格及銷售量資料為例，輸入Excel后，如圖9.11所示。圖9.11Excel綜合指數(shù)計算結(jié)果9.5統(tǒng)計指數(shù)中Excel的應(yīng)用Excel

首先，要計算G、H、I、J四列的銷售額。對于G列，單擊G3單元格，輸入“=C3*E3”，并利用填充柄功能計算G4和G5；H、I、J列均可仿此計算；然后單擊G6單元格，輸入“=SUM(G3:G5)”，并利用填充柄功能，計算出G、H、I、J各列的總銷售額。

1、數(shù)量指標(biāo)綜合指數(shù)的計算數(shù)量指標(biāo)綜合指數(shù)就是以價格作為同度量因素(權(quán)數(shù))，來編制商品銷售量指數(shù)，并反映銷售量的變動情況。在一般情況下，同度量因素(價格)要固定在基期。因此，單擊任一空單元格，例如圖9.11，在I7中，輸入“=I6／G6*100”得100.275％，即商品銷售量綜合指數(shù)Kq為100.275％。它說明了該商場三種商品總的銷售量，報告期比基期平均增長了0.275％，而由于銷售量的增長使商品銷售額增加了(=I6-G6)，即6100元。

2、質(zhì)量指標(biāo)綜合指數(shù)的計算質(zhì)量指標(biāo)綜合指數(shù)就是以商品銷售量作為同度量因素(權(quán)數(shù))，來編制價格指數(shù)，反映商品價格的變動情況。一般情況下，同度量因素(銷售量)固定在報告期。因此，單擊任一空單元格，例如，H7，輸入“=H6／I6*100”，得96％，即商品價格綜合指數(shù)Kp為96％。它說明了該商場三種商品綜合價格指數(shù)下降了4％，由于價格的下降，使商品銷售額減少了(=H6-I6)89000元。

3、銷售額指數(shù)單擊任一單元格，例如，G7，輸入“=H6／G6*100”，得96.26％。說明由于價格和銷售量共同影響，使報告期銷售額下降了3.74％，共減少了82900元。它是由于銷售量提高使銷售額增加6100元和價格降低使銷售額減少了89000元兩個因素共同影響的結(jié)果。首先，要計算G、H、I、J四列的銷售額。對于G9.5.2計算平均指數(shù)1、加權(quán)算術(shù)平均指數(shù)在一定條件下，根據(jù)基期同一度量因素編制的數(shù)量指標(biāo)綜合指數(shù)可以變形為加權(quán)算術(shù)平均數(shù)指數(shù)。一般來說，加權(quán)算術(shù)平均數(shù)指數(shù)公式多用于計算數(shù)量指標(biāo)指數(shù)。下面以圖9.12資料為例，說明如何計算算術(shù)平均數(shù)指數(shù)。

圖9.12計算算術(shù)平均數(shù)指數(shù)9.5.2計算平均指數(shù)1、加權(quán)算術(shù)平均指數(shù)

在圖9.12中，分別插入并計算G、J兩列；單擊G3單元格，輸入“=F3／E3”；然后利用填充柄功能計算G4、G5的值；單擊J3，輸入“=G3*H3”，并利用填充柄功能計算J4、J5；最后計算合計數(shù)，單擊H6，輸入“=SUM(H3:H5)”求出基期總銷售額，并利用填充柄功能計算J6。單擊任一空單元格，例如J7，輸入“=J6／H6”，即得銷售量的算術(shù)平均數(shù)指數(shù)為100.275％。

2、加權(quán)調(diào)和平均指數(shù)在一定條件下，根據(jù)報告期同度量因素計算的質(zhì)量指標(biāo)綜合指數(shù)可以變形為加權(quán)調(diào)和平均指數(shù)。一般來說，加權(quán)調(diào)和平均指數(shù)公式多用于計算質(zhì)量指標(biāo)指數(shù)。具體方法與加權(quán)算術(shù)平均指數(shù)類似。在圖9.12中，分別插入并計算G、J兩列；9.5.3進(jìn)行因素分析【例9.15】現(xiàn)以某企業(yè)各類職工月工資及職工人數(shù)資料為例，說明平均工資指數(shù)體系的編制操作方法，如圖9.13所示。圖9.13平均工資指數(shù)體系的因素分析9.5.3進(jìn)行因素分析【例9.15】現(xiàn)以某

首先計算月工資總額：單擊F3，輸入“=B3*D3”，并用填充柄功能計算F4；單擊G3，輸入“=C3*E3”，并用填充柄功能計算G4；單擊H3，輸入“=B3*E3”，并用填充柄功能計算H4。然后計算合計數(shù)，單擊D5，輸入“=SUM(D3:D4)”得1000，并利用填充柄功能，計算E、F、G、H列的合計數(shù)?？勺儤?gòu)成指數(shù)(平均工資指數(shù))：單擊一空單元格，例如，F(xiàn)7，輸入“=(G5／E5)／(F5／D5)”得1.1964。說明總平均工資增長了19.64％，每個職工平均增長了(=G5／E5-F5／D5)90元。固定構(gòu)成指數(shù)：單擊一空單元格，例如，G7，輸入“=(G5／E5)／(H5／E5)”，得1.2884。說明各類職工月工資的提高使總平均工資提高了28.84％，月工資的提高使每個職工平均增加(=G5／E5-H5／E5)150元。結(jié)構(gòu)影響指數(shù)：單擊一空單元格，輸入“=(H5／E5)／(F5／D5)”，得0.9286。說明各類職工人數(shù)的變動，使總平均工資下降了7.14％，平均每個職工減少(=H5／E5-F5／D5)40元。返回首先計算月工資總額：單擊F3，輸入“=B39.6抽樣推斷中Excel的應(yīng)用

9.6.1按不同模式抽選調(diào)查單位

抽樣組織形式主要有簡單隨機(jī)抽樣（即純隨機(jī)抽樣）、機(jī)械抽樣、類型抽樣、整群抽樣及多階段抽樣等。機(jī)械抽樣是等距離抽樣，類型抽樣是分層（類）隨機(jī)抽樣，整群抽樣是根據(jù)隨機(jī)原則成群（組）抽樣，多階段抽樣可以說是多階段地簡單隨機(jī)抽樣。它們都是根據(jù)隨機(jī)原則，結(jié)合具體研究對象的性質(zhì)、調(diào)查工作的目的和條件，合理有效地取得所需多樣本。因此，本文主要介紹如何利用Excel進(jìn)行隨機(jī)抽樣過程。

Excel提供了隨機(jī)數(shù)發(fā)生器工具，可用來產(chǎn)生基于均勻分布、正態(tài)分布、二項分布、泊松分布以及一般離散分布的隨機(jī)數(shù)。下面從總體容量為20個數(shù)據(jù)中抽取一個容量為10的樣本。

(1)建立一個總體容量為20個數(shù)據(jù)的工作表。

(2)選擇B1:B10區(qū)域，在工具欄中單擊“函數(shù)”按鈕，打開“插入函數(shù)”對話框，在“選擇類別”列表中選擇“數(shù)學(xué)與三角函數(shù)”，在“函數(shù)名”列表中選擇隨機(jī)函數(shù)“RAND”，打開隨機(jī)函數(shù)對話框，同時按住Ctrl+Shift鍵，單擊“確定”按鈕，B1:B10單元格區(qū)域中將顯示一組大于0、小于1的隨機(jī)數(shù)。

(3)將單元格B1中的公式改為“10*RAND()”，同時按住Ctrl+Shift鍵，回車確定，可以看到B1:B10中的數(shù)值均擴(kuò)大10倍。

(4)選擇C1:C10區(qū)域，在工具欄中單擊“函數(shù)”按鈕，打開“插入函數(shù)”對話框，在“選擇類別”列表中選擇“數(shù)學(xué)與三角函數(shù)”，在“函數(shù)名”列表中選擇取整函數(shù)9.6抽樣推斷中Excel的應(yīng)用9.6.1按不同模式抽“CEILING”，打開取整函數(shù)對話框，在“Number”中輸入“B1:B10”，在“Significance”中輸入“1”，按住Ctrl+Shift鍵，同時單擊“確定”按鈕。

(5)選擇D1:D10區(qū)域，在工具欄中單擊“函數(shù)”按鈕，打開“粘貼函數(shù)”對話框，在“選擇類別”列表中選擇“查找與引用”，在“函數(shù)名”列表中選擇索引函數(shù)“INDEX”，打開“選定參數(shù)”對話框，選擇引用(Reference)，單擊“確定”按鈕，進(jìn)入索引函數(shù)INDEX窗口。

(6)在“reference”中輸入“A1:A20”，在“Rownum”中輸入“C1:C10”。按住Ctrl+Shift鍵，同時單擊“確定”按鈕。即可得到所需的樣本?！癈EILING”，打開取整函數(shù)對話框，在“Number”9.6.2用函數(shù)方法進(jìn)行區(qū)間估計

利用Excel提供的有關(guān)統(tǒng)計函數(shù)，可以對總體平均數(shù)進(jìn)行區(qū)間估計。現(xiàn)以圖9.14為例，假定要求在95％的概率保證程度下，對該企業(yè)工人的平均日產(chǎn)量進(jìn)行估計，以及假定要求平均日產(chǎn)量的允許誤差范圍Δx=3件，估計該企業(yè)工人的平均日產(chǎn)量。首先計算出樣本平均數(shù)和標(biāo)準(zhǔn)差(假定它們分別放在H1、H2單元格內(nèi))。再計算樣本平均誤差：單擊H3，輸入“=H2／SQRT(20)”。對于第一類問題，利用函數(shù)NORMINV計算平均日產(chǎn)量的上限和下限。下面使用函數(shù)對話框方式使用函數(shù)。單擊H4單元格，選擇“插入”菜單中“函數(shù)”命令，打開選擇函數(shù)對話框，在“函數(shù)分類”中選擇“統(tǒng)計”類別，在“函數(shù)名”欄內(nèi)選擇“NORMINV函數(shù)”，單擊確定，打開NORMINV函數(shù)設(shè)置對話框。本例中，給定的概率保證程度為95％，因此在進(jìn)行NORMINV函數(shù)參數(shù)設(shè)置時，Probability項要輸入“0.95+(1-0.95)／2”即0.975；在Mean和Standard_dev項內(nèi)輸入樣本平均數(shù)和樣本平均誤差，即H1、H3，確定后計算出樣本平均數(shù)的上限44.22。9.6.2用函數(shù)方法進(jìn)行區(qū)間估計利用Exc

在Probability項中輸入“(1-0．95)／2”即0.025，計算出樣本平均數(shù)的下限37.78。對于第二類問題，樣本平均數(shù)的上限和下限容易計算，分別為44和38。計算概率保證程度可使用NORMDIST函數(shù)。打開NORMDIST函數(shù)參數(shù)設(shè)置對話框。在X欄內(nèi)分別輸入計算出的上限和下限，在Mean和Standard_dev欄內(nèi)輸入樣本平均數(shù)和樣本平均誤差，在Cumulative欄內(nèi)輸入“true”，這樣就分別計算出上限和下限的累積概率0.9661和0.0339，最后上限累積概率減去下限累積概率即為概率保證程度0.93。總體成數(shù)本身是樣本平均數(shù)的特例，因此，總體成數(shù)的區(qū)間估計與此相同。圖9.1420個工人日產(chǎn)量分組資料及估計結(jié)論返回在Probability項中輸入“(1-09.7相關(guān)分析和回歸分析中Excel的應(yīng)用9.7.1相關(guān)分析

利用Excel計算相關(guān)系數(shù)分析變量之間的相關(guān)性，可以按照相關(guān)系數(shù)公式使用常規(guī)方法計算，還可使用CORREL函數(shù)或相關(guān)系數(shù)分析工具計算。

【例9.16】現(xiàn)以圖9.15生產(chǎn)同類產(chǎn)品的6個企業(yè)的月產(chǎn)量和單位成本資料，說明月產(chǎn)量與單位成本之間的相關(guān)系數(shù)如何利用Excel進(jìn)行計算。

圖9.15月產(chǎn)量與單位成本之間的相關(guān)系數(shù)計算表9.7相關(guān)分析和回歸分析中Excel的應(yīng)用9.7.1相關(guān)1使用常規(guī)方法計算相關(guān)系數(shù)使用常規(guī)方法就是利用公式計算相關(guān)系數(shù)：

首先，將數(shù)據(jù)輸入Excel中，例如圖中B、C兩列，然后計算相關(guān)系數(shù)所需要的各項合計數(shù)。單擊D2單元格，輸入“=B2*B2”，并利用填充柄功能，計算D列。類此，可以計算出E、F兩列。然后，單擊B8單元格，輸入“=SUM(B2:B7)”，求出B列合計數(shù)，并利用填充柄功能，按住鼠標(biāo)左鍵向右拖至F8單元格計算出C、D、E、F各列的合計數(shù)。最后，按照公式計算相關(guān)系數(shù)，可以首先計算分子，單擊任一空單元格，輸入“6*E8-B8*C8”，得出結(jié)果-162。然后再計算分母，單擊另一單元格，輸入“=SQRT((6*D8-B8*B8)*(6*F8-C8*C8))”，得出結(jié)果168.997。然后單擊一單元格輸入“=-162／168.997”，即得相關(guān)系數(shù)為-0.9586。也可將分子、分母合成一個式子計算，輸入“=(6*E8-B8*C8)／SQRT((6*D8-B8*B8)*(6*F8-C8*C8))”。

2用CORREL函數(shù)計算相關(guān)系數(shù)單擊“插入”菜單里的“函數(shù)”命令，選擇函數(shù)類別“統(tǒng)計”里的“CORREL函數(shù)”，打開相關(guān)系數(shù)函數(shù)CORREL對話框。在Array1、Array2里分別輸入兩列數(shù)據(jù)所在區(qū)域“B2:B7”和“C2:C7”，即可求得相關(guān)系數(shù)-0.9586。1使用常規(guī)方法計算相關(guān)系數(shù)3、利用相關(guān)系數(shù)分析工具計算單擊“工具”菜單中“數(shù)據(jù)分析”命令，選擇相關(guān)函數(shù)分析工具。在輸入?yún)^(qū)域內(nèi)輸入“＄B＄2:＄C＄7”；分組方式需要指出數(shù)據(jù)是按列還是按行排列。如果輸入數(shù)據(jù)區(qū)域第一行中包含標(biāo)志項，選中“標(biāo)志位于第一行”。在輸出選項中，選擇結(jié)果放置位置，是在原工作表、新工作表還是新工作簿中。設(shè)置完成單擊“確定”，得到變量間的相關(guān)系數(shù)。利用相關(guān)系數(shù)分析工具可進(jìn)行多個變量間的相關(guān)系數(shù)的計算。若取得的資料是分組資料，只有利用常規(guī)方法，相應(yīng)地利用以下公式計算相關(guān)系數(shù)：

其操作過程與未分組資料的計算類似。3、利用相關(guān)系數(shù)分析工具計算9.7.2一元線性回歸分析

在Excel中擬合回歸直線，可使用常規(guī)方法，建立回歸直線方程，也可以使用回歸函數(shù)或回歸分析工具來建立回歸方程?；貧w分析包括線性回歸分析和非線性回歸分析，而線性回歸分析又可分為一元回歸分析和多元回歸分析。本章只介紹一元線性回歸分析方法?，F(xiàn)仍以圖9.15生產(chǎn)同類產(chǎn)品的6個企業(yè)的月產(chǎn)量和單位成本資料，說明一元線性回歸分析方法。

1、用常規(guī)方法建立回歸直線方程通過公式求得參數(shù)a、b的值。

其操作方法與計算相關(guān)系數(shù)相同，不再具體說明。9.7.2一元線性回歸分析在Excel中擬2、用統(tǒng)計函數(shù)建立回歸直線方程

Excel提供了一個求截距的函數(shù)INTERCEPT和一個求斜率的函數(shù)SLOPE，用這兩個函數(shù)可以建立回歸直線方程。另外，Excel還提供了一個既能用于一元線性回歸，又能用于多元線性回歸和自回歸的函數(shù)LINEST，也可以用它來建立回歸直線方程。（1）使用截距函數(shù)INTERCEPT和斜率函數(shù)SLOPE來建立回歸方程單擊任一單元格，輸入“=INTERCEPT(C2:C7，B2:B7)”求得回歸方程的截距79.87。單擊另一單元格，輸入“=SLOPE(C2:C7，B2:B7)”求得回歸方程的斜率-1.106。這樣我們就建立了回歸直線方程yc=79.87-1.106x。（2）使用LINEST函數(shù)來建立回歸直線方程在LINEST函數(shù)中，回歸方程的表達(dá)式與常規(guī)不同，它是用y=mx+b的形式來表示的，而且給出的結(jié)果除斜率和截距外，還給出估計標(biāo)準(zhǔn)誤差等數(shù)值，并以數(shù)組的形式給出。具體操作如下：首先，選定兩列五行作為放置計算結(jié)果的單元格區(qū)域，例如選定D2:E6。然后單擊“插入”菜單中的“函數(shù)”命令，選擇函數(shù)類別“統(tǒng)計”里的“LINEST函數(shù)”，打開回歸函數(shù)LINEST對話框。在Known_y’s里輸入因變量數(shù)據(jù)所在區(qū)域“C2:C7”；在Known_x’s里輸入自變量數(shù)據(jù)所在區(qū)域“B2:B7”。

Const欄內(nèi)要輸入邏輯值，用以指定是否要強(qiáng)制常數(shù)b為0。如果輸入“TRUE”或忽略，則給出正常B值，如果輸入“FALSE”，則給出B=0。我們輸入“TRUE”。

Stats欄內(nèi)也要輸入邏輯值，如果要給出附加的回歸統(tǒng)計值，則輸入“TRUE”，如果只需給出斜率和截距，則輸入“FALSE”；我們輸入“TRUE”。2、用統(tǒng)計函數(shù)建立回歸直線方程

輸入后，按Ctrl+Shift+Enter組合鍵，則計算結(jié)果計入選定的區(qū)域，如圖9.16所示。圖9.16回歸函數(shù)LINEST分析結(jié)果

其中，D列和E列中2行分別為斜率和截距；第3行分別為斜率和截距的標(biāo)準(zhǔn)差；第4行分別為判定系數(shù)和估計標(biāo)準(zhǔn)誤差；第5行分別為F統(tǒng)計量和自由度；第6行分別為回歸平方和剩余平方和。這些數(shù)據(jù)用于回歸直線擬合度和顯著性的檢驗。

LINEST函數(shù)可用于多元線性分析，在Known_x’s里輸入多變量數(shù)據(jù)所在區(qū)域，其他項不變。返回輸入后，按Ctrl+Shift+Enter組

本章主要講解了Excel的特點(diǎn)、基本操作及其在統(tǒng)計分析中的各種應(yīng)用。

Excel具有四項統(tǒng)計功能：函數(shù)統(tǒng)計功能、數(shù)據(jù)管理功能、繪圖功能和分析工具庫，公式是Excel中的核心，函數(shù)是Excel中內(nèi)置的公式，函數(shù)使各種運(yùn)算變得更加簡便。

Excel在統(tǒng)計分析中的初級應(yīng)用，主要包括數(shù)據(jù)錄入、統(tǒng)計分組和制作統(tǒng)計圖。利用Excel可以進(jìn)行靜態(tài)統(tǒng)計指標(biāo)分析和動態(tài)指標(biāo)分析。靜態(tài)指標(biāo)主要包括平均指標(biāo)和變異指標(biāo)。在Excel中對這些指標(biāo)進(jìn)行計算時，是通過運(yùn)用Excel提供的各種函數(shù)實現(xiàn)的。動態(tài)指標(biāo)主要包括（平均）增長量、（平均）發(fā)展速度和長期發(fā)展趨勢。其中，（平均）增長量、（平均）發(fā)展速度的計算是通過相應(yīng)的公式實現(xiàn)的，比較簡單，而長期發(fā)展速度的計算較為復(fù)雜。對長期發(fā)展趨勢的測定，主要有時距擴(kuò)大法、移動平均法和最小二乘法。因時距擴(kuò)大法可通過SUM函數(shù)（求和函數(shù)）方便求得，本章沒有講解。知識歸納本章主要講解了Excel的特點(diǎn)、基本操作及其在統(tǒng)計分析中的對于計算統(tǒng)計指數(shù)，進(jìn)行抽樣推斷，也可以應(yīng)用Excel。統(tǒng)計指數(shù)分析主要包括綜合指數(shù)、平均指數(shù)的計算以及因素分析。抽樣推斷主要包括按不同模式對調(diào)查單位的抽選和用函數(shù)方法進(jìn)行區(qū)間估計。相關(guān)分析和回歸分析中Excel的應(yīng)用。這應(yīng)屬于Excel在統(tǒng)計分析中的高級應(yīng)用。相關(guān)分析就是應(yīng)用各種方法計算相關(guān)系數(shù)。在回歸分析中只介紹了簡單的一元線性回歸。相關(guān)回歸方程的確定主要通過常規(guī)方法、統(tǒng)計函數(shù)和回歸分析工具三種途徑實現(xiàn)。返回對于計算統(tǒng)計指數(shù)，進(jìn)行抽樣推斷，也可以應(yīng)用Excel。統(tǒng)計指習(xí)題與思考題

1、小學(xué)四年級甲班同學(xué)的成績?nèi)缦?，試編制相關(guān)的柱狀圖。習(xí)題與思考題1、小學(xué)四年級甲班同學(xué)的成績?nèi)缦?/p>

2根據(jù)下表用Excel中相關(guān)的公式和函數(shù)計算：（1）法律基礎(chǔ)的平均成績；（2）計算機(jī)基礎(chǔ)不及格學(xué)生的個數(shù)；（3）法律基礎(chǔ)和計算機(jī)基礎(chǔ)課程的最高分。2根據(jù)下表用Excel中相關(guān)的公式和函數(shù)計算：

3、計算季度產(chǎn)量和單位成本間的相關(guān)系數(shù)。

已知數(shù)據(jù)為B、C兩列。填列計算相關(guān)系數(shù)時所需的各項合計數(shù)。3、計算季度產(chǎn)量和單位成本間的相關(guān)系數(shù)。要求：（1）計算均值、眾數(shù)、方差；（2）計算中位數(shù)、偏度；（3）這組資料顯示的結(jié)果與調(diào)查結(jié)論是否一致。

4、據(jù)調(diào)查，每個人平均會花45分鐘時間聽音樂，下面是其中的樣本資料。要求：4、據(jù)調(diào)查，每個人平均會花45分鐘時間聽音樂，下面是

計算該地區(qū)平均人均收入的中位數(shù)、均值及標(biāo)準(zhǔn)差。5.已知某地區(qū)農(nóng)民家庭按年人均收入分組的資料如下表所示。計算該地區(qū)平均人均收入的中位數(shù)、均值及標(biāo)準(zhǔn)差。5.已

6、某城市居民2003年家庭人均可支配收入（元/月）如下表所示。請根據(jù)上表數(shù)據(jù)，用Excel繪制：（1）直方圖；（2）曲線圖；（3）折線圖。6、某城市居民2003年家庭人均可支配收入（元

7.經(jīng)濟(jì)學(xué)家擔(dān)心，失業(yè)救濟(jì)措施可能會使商業(yè)循環(huán)衰退期階段被臨時解雇的人數(shù)發(fā)生扭曲。其中一個基本的猜測是：失業(yè)救濟(jì)將會使公司裁減更多的雇傭人員。為驗證這一猜測是否成立，收集了如下一組樣本資料。

試就給定的資料進(jìn)行回歸分析。7.經(jīng)濟(jì)學(xué)家擔(dān)心，失業(yè)救濟(jì)措施可能會使商業(yè)8.一家大型家具制造商，在14個城市的試營業(yè)額及廣告費(fèi)用資料如下（單位：百萬元）。

通過建立廣告費(fèi)與營業(yè)額之間的經(jīng)驗方程分析：廣告額是否明顯影響營業(yè)額的高低。返回8.一家大型家具制造商，在14個城市的試營9Excel數(shù)據(jù)分析與統(tǒng)計計算通過本章的學(xué)習(xí)，讀者應(yīng)重點(diǎn)掌握以下內(nèi)容:Excel特點(diǎn)及功能；利用Excel對原始數(shù)據(jù)進(jìn)行分組整理，制作統(tǒng)計表和統(tǒng)計圖；利用Excel計算各種靜態(tài)指標(biāo)和動態(tài)指標(biāo)；利用Excel計算兩類總指數(shù)、對指數(shù)體系進(jìn)行因素分析；利用Excel作隨機(jī)抽樣和區(qū)間估計；利用Excel計算相關(guān)系數(shù)、作一元回歸分析。

教學(xué)目標(biāo)關(guān)鍵詞匯統(tǒng)計分析（StatisticalAnalysis）統(tǒng)計推理(StatisticalInference)回歸分析(RegressionAnalysis)9Excel數(shù)據(jù)分析與統(tǒng)計計算通過本章的學(xué)習(xí)，讀9Excel數(shù)據(jù)分析與統(tǒng)計計算9.1Excel統(tǒng)計應(yīng)用概述9.2數(shù)據(jù)整理中Excel的應(yīng)用9.3靜態(tài)指標(biāo)分析中Excel的應(yīng)用9.4動態(tài)指標(biāo)分析中Excel的應(yīng)用9.5統(tǒng)計指數(shù)中Excel的應(yīng)用9.6抽樣推斷中Excel的應(yīng)用9.7相關(guān)分析和回歸分析中Excel的應(yīng)用◎知識歸納◎習(xí)題與思考題9Excel數(shù)據(jù)分析與統(tǒng)計計算9.1Excel統(tǒng)計應(yīng)用9.1Excel統(tǒng)計應(yīng)用概述9.1.1Excel的統(tǒng)計功能1、Excel的函數(shù)統(tǒng)計功能函數(shù)是Excel預(yù)定義的內(nèi)置公式，它可以接受被稱為參數(shù)的特定值，按函數(shù)的內(nèi)置語法進(jìn)行特定的運(yùn)算，返回一定的函數(shù)運(yùn)算結(jié)果。Excel提供了豐富的函數(shù)，其中統(tǒng)計函數(shù)就多達(dá)80種。在Excel中，要使用函數(shù)，通常有兩種方法:一種是點(diǎn)擊“插入”菜單，選擇“函數(shù)”命令，然后在“插入函數(shù)”對話框中選擇具體函數(shù)，即可打開函數(shù)參數(shù)設(shè)置對話框進(jìn)行參數(shù)設(shè)置；另一種是直接在參數(shù)單元格內(nèi)輸入函數(shù)及參數(shù)。

2、Excel的數(shù)據(jù)管理功能利用Excel可以把工作表中的數(shù)據(jù)做成一個類似數(shù)據(jù)庫的數(shù)據(jù)清單，可以實現(xiàn)記錄的增加、修改、刪除、查找與編輯，具有排序、篩選、分類匯總功能。數(shù)據(jù)清單中的列是數(shù)據(jù)庫的字段，列標(biāo)志是字段名稱，數(shù)據(jù)清單中的每一行對應(yīng)數(shù)據(jù)庫中的一個記錄。

3、Excel繪圖功能

Excel具有強(qiáng)大的繪制統(tǒng)計圖表功能，可以繪制柱狀圖、條形圖、折線圖、餅圖、散點(diǎn)圖、面積圖、圓形圖、雷達(dá)圖、曲面圖、氣泡圖、股價圖、圓錐圖、棱錐圖等，而且每一種類型又提供了幾種不同的子類型，利用圖表向?qū)Ь涂奢p松完成。9.1Excel統(tǒng)計應(yīng)用概述9.1.1Excel的統(tǒng)計功4、分析工具庫“分析工具庫”包括下述工具:方差分析、相關(guān)系