EXCEL與數(shù)據(jù)分析專業(yè)知識講座

1

(一)定量數(shù)據(jù)頻數(shù)分析

(二)利用數(shù)據(jù)透視表進(jìn)行分析三定量數(shù)據(jù)基本特性分析

(三)集中趨勢程度分析

(四)離散趨勢程度分析第1頁2第一節(jié)定量數(shù)據(jù)頻數(shù)分析一、定量數(shù)據(jù)分組二、單項式數(shù)組頻數(shù)分析

三、組距式數(shù)組頻數(shù)分析

第2頁3一、定量數(shù)據(jù)分組定量數(shù)據(jù)頻數(shù)分析往往是從數(shù)據(jù)分組開始，數(shù)據(jù)分組是根據(jù)分析研究需要，按照一定分組標(biāo)準(zhǔn)指定多種數(shù)據(jù)區(qū)域組別，并將數(shù)據(jù)從小分派到對應(yīng)數(shù)據(jù)區(qū)域內(nèi)。數(shù)據(jù)分組辦法有單項式分組和組距式分組兩種。單項式分組是將數(shù)據(jù)中出現(xiàn)每個數(shù)值作為一組，它一般只適用于數(shù)據(jù)變動范圍較小，不一樣數(shù)值個數(shù)較少，能夠一一列舉離散型數(shù)據(jù)。組距式分組是將變量值一種范圍作為一組，它一般適用于連續(xù)型數(shù)據(jù)和數(shù)據(jù)變動范圍較大、不一樣數(shù)值個數(shù)較多離散型數(shù)據(jù)。實際中組距式分組應(yīng)用更多。第3頁4例：單項式分組某班學(xué)生年紀(jì)分布表年紀(jì)（歲）人數(shù)（人）181920212211222132合計50第4頁5例：組距式分組某廠工人按日產(chǎn)量分組表按日產(chǎn)量分組（件）工人數(shù)（人）130-140140-150150-160160-170170-180392684合計50第5頁6二、單項式數(shù)組頻數(shù)分析頻數(shù)分布表制作與定性數(shù)據(jù)相同，可通過兩種辦法實現(xiàn)：第一種辦法：通過度類匯總實現(xiàn)第二種辦法：通過插入頻數(shù)分布函數(shù)（FREQUENCY)實現(xiàn)主要操作步驟：第6頁7（1）對“年紀(jì)”按升序排序；（2）將各組年紀(jì)值輸入到工作表中，作為組界；（3）單擊“插入”→單擊“函數(shù)”→選擇“FREQUENCY”；（4）選擇組界區(qū)域和數(shù)據(jù)區(qū)域單元格地址→單擊“確定”；（5）按下F2+Ctrl+Shift+Enter，得到頻數(shù)分布表：年紀(jì)（歲）學(xué)生人數(shù)（人）182192120552117225合計100學(xué)生年紀(jì)頻數(shù)分布表第7頁8深入可將頻數(shù)分布表做成頻數(shù)分布圖：選中“學(xué)生人數(shù)”下方數(shù)據(jù)區(qū)域→單擊圖表向?qū)Аx擇柱形圖，初步完成頻數(shù)分布圖：深入編輯頻數(shù)分布圖，主要是要將橫軸刻度改為實際年紀(jì)刻度值；第8頁9（1）右擊任一圖柱→單擊“源數(shù)據(jù)”，出現(xiàn)下面復(fù)選框：（2）單擊“系列”；（3）在“分類（X）軸標(biāo)志”右邊空白框中輸入作為X軸刻度組界數(shù)值所在單元格地址（也可直接用鼠標(biāo)選中組界數(shù)值區(qū)域）；（4）單擊“確定”，即完成頻數(shù)分布圖。（5）可深入編輯柱形圖顏色、文字等，以美化頻數(shù)分布圖。第9頁10第10頁11對于連續(xù)型數(shù)據(jù)和數(shù)據(jù)變動范圍較大、不一樣數(shù)值個數(shù)較多離散型數(shù)據(jù)，實際中要采取組距式分組，并在此基礎(chǔ)上進(jìn)行頻數(shù)分析，以揭示數(shù)值型數(shù)據(jù)分布特性。這里首先要處理問題是，數(shù)據(jù)總體可分為多少組，每組組距是多大，組限是多少。詳細(xì)分組時先算出極差（最大值-最小值），觀測數(shù)據(jù)變動最大范圍，然后結(jié)合數(shù)據(jù)特性來劃分，為了計算上方便，一般會以10及其倍數(shù)為組距。三、組距式數(shù)組頻數(shù)分析第11頁12組距式數(shù)組頻數(shù)分布表制作，可通過兩種辦法實現(xiàn)：

第一種辦法：通過插入頻數(shù)分布函數(shù)（FREQUENCY)實現(xiàn)第二種辦法：通過“數(shù)據(jù)分析”命令中“直方圖”工具實現(xiàn)第12頁13第一種辦法：通過插入頻數(shù)分布函數(shù)

（FREQUENCY)實現(xiàn)采取插入FREQUENCY函數(shù)實現(xiàn)，應(yīng)首先將各組組上限按升序輸入工作表，F(xiàn)REQUENCY函數(shù)將對應(yīng)于各組組限，計算大于上一組組限、不大于等于本組組限數(shù)據(jù)頻數(shù)，這里利用數(shù)組方式計算。第13頁14第二種辦法：通過“數(shù)據(jù)分析”命令中“直

方圖”工具實現(xiàn)（1）首先將各組上限值輸入工作表中；（2）選擇“工具”菜單中“數(shù)據(jù)分析”命令，出現(xiàn)數(shù)據(jù)分析窗口：（3）在“分析工具”選項框中選擇“直方圖”；第14頁15（4）單擊“確定”，出現(xiàn)“直方圖”復(fù)選框：（5）在“直方圖”復(fù)選框“輸入?yún)^(qū)域”框中輸入學(xué)生成績數(shù)據(jù)所在單元格地址，在“接收區(qū)域”框輸入分組組限所在單元格地址，在“輸出區(qū)域”框輸入生成頻數(shù)分析表放置單元格地址，并在下面選中“圖表輸出”；第15頁16（6）單擊“確定”，出現(xiàn)“頻數(shù)分布表”和“直方圖”成果。注意：（1）在“頻數(shù)分布表”和“直方圖”成果中，“接收”是指組限，而“頻率”事實上是指“頻數(shù)”。（2）利用“直方圖”工具直接作出所謂“直方圖”并不是真正意義上“直方圖”，而是“柱形圖”。直方圖與柱形圖主要區(qū)分是：直方圖各個條形之間沒有間隔，而柱形圖有。由于直方圖是根據(jù)數(shù)組成果繪制，而分組是連續(xù)，因而對應(yīng)條形間是挨在一起不能分開。為此，必須對圖形作深入修飾。第16頁17第二節(jié)利用數(shù)據(jù)透視表進(jìn)行分析一、創(chuàng)建數(shù)據(jù)透視表二、編輯數(shù)據(jù)透視表三、創(chuàng)建數(shù)據(jù)透視圖

第17頁18一、創(chuàng)建數(shù)據(jù)透視表數(shù)據(jù)透視表具有交互分析能力，能全面、靈活地對數(shù)據(jù)進(jìn)行分析、匯總。只需要變化對應(yīng)字段位置，即可得到多種分析成果。在使用“數(shù)據(jù)透視表”之前，需要創(chuàng)建數(shù)據(jù)透視表。詳細(xì)步驟如下：

1、單擊數(shù)據(jù)區(qū)域任一單元格2、單擊“數(shù)據(jù)”下拉菜單

→單擊“數(shù)據(jù)透視表和數(shù)據(jù)透視圖”3、單擊“下一步”，出現(xiàn)如下復(fù)選框：第18頁194、設(shè)置數(shù)據(jù)透視表數(shù)據(jù)區(qū)域；

5、單擊“下一步”，出現(xiàn)如下對話框：

6、選擇數(shù)據(jù)透視表顯示位置，然后單擊“完成”，出現(xiàn)如下界面：

第19頁207、設(shè)置詳細(xì)透視表數(shù)據(jù)，按照分析目標(biāo)將有關(guān)字段名拖動到對應(yīng)標(biāo)題區(qū)域，即完成了數(shù)據(jù)透視表創(chuàng)建。第20頁21第21頁22二、編輯數(shù)據(jù)透視表數(shù)據(jù)透視表創(chuàng)建完成后，還能夠進(jìn)行美化設(shè)計、數(shù)據(jù)設(shè)置與更改、數(shù)據(jù)篩選等編輯操作，以滿足顧客多種不一樣需要。創(chuàng)建數(shù)據(jù)透視表采取是Excel提供默認(rèn)樣式，假如顧客需要重新對生成數(shù)據(jù)透視表進(jìn)行美化設(shè)計，能夠“設(shè)置報告格式”直接選擇“自動套用格式”。使用過程中，還可根據(jù)需要更改數(shù)據(jù)透視表字段項、數(shù)據(jù)、顯示效果等，以達(dá)成需要分析效果。第22頁23三、創(chuàng)建數(shù)據(jù)透視圖數(shù)據(jù)透視圖是數(shù)據(jù)透視表圖形顯示效果，為了更直觀地反應(yīng)數(shù)據(jù)透視表匯總效果，能夠讓二者配合使用。當(dāng)顧客創(chuàng)建好數(shù)據(jù)透視表后，右擊數(shù)據(jù)透視表內(nèi)任一單元格，單擊“數(shù)據(jù)透視圖”按鈕，系統(tǒng)能夠直接用數(shù)據(jù)透視表生成數(shù)據(jù)透視圖，并自動添加一張工作表（以Chart1命名）來顯示數(shù)據(jù)透視圖。假如想把數(shù)據(jù)透視圖和數(shù)據(jù)透視表放在一起，以對照觀測事物特點，能夠右擊數(shù)據(jù)透視圖空白處，然后單擊“位置”，再在“圖表位置”復(fù)選框中選擇“作為其中對象插入”，并選好工作表名即可。第23頁24三集中趨勢程度分析

1、集中趨勢測定內(nèi)容

2、算術(shù)平均數(shù)

3、幾何平均數(shù)

4、眾數(shù)和中位數(shù)

第24頁25一、集中趨勢測定內(nèi)容對大量數(shù)據(jù)整頓之后，大多數(shù)情況下數(shù)據(jù)都會展現(xiàn)出一種鐘形分布，即各個變量值與中間位置距離越近，所出現(xiàn)次數(shù)越多；與中間位置距離越遠(yuǎn)，出現(xiàn)次數(shù)越少，從而形成了以中間值為中心集中趨勢。這個集中趨勢是現(xiàn)象共性特性，是現(xiàn)象規(guī)律性數(shù)量體現(xiàn)。一般來說，在進(jìn)行數(shù)據(jù)分析中，利用平均數(shù)來描述總體分布集中趨勢，這是一組數(shù)據(jù)典型代表值。其中最常用有：算術(shù)平均數(shù)、幾何平均數(shù)、眾數(shù)、中位數(shù)等。第25頁26二、算術(shù)平均數(shù)（一）非組數(shù)據(jù)算術(shù)平均數(shù)在實際應(yīng)用中，算術(shù)平均數(shù)是使用最廣泛中心趨勢值。根據(jù)錄入數(shù)據(jù)類型及整頓程度不一樣，算術(shù)平均數(shù)能夠包括非組數(shù)據(jù)算術(shù)平均數(shù)和組數(shù)據(jù)算術(shù)平均數(shù)兩種形式。非組數(shù)據(jù)算術(shù)平均數(shù)計算辦法是：將所有單個數(shù)據(jù)相加，然后除以數(shù)據(jù)總數(shù)目。設(shè)有n個數(shù)據(jù)，其算術(shù)平均數(shù)計算公式為：第26頁求非組數(shù)據(jù)算術(shù)平均數(shù)辦法1、使用SUM函數(shù)計算非組數(shù)據(jù)算術(shù)平均數(shù)2、使用AVERAGE函數(shù)計算非組數(shù)據(jù)算術(shù)平均數(shù)第27頁28（二）組數(shù)據(jù)算術(shù)平均數(shù)

在實際應(yīng)用中，顧客也許需要處理組數(shù)據(jù)。組數(shù)據(jù)是指根據(jù)某種標(biāo)準(zhǔn)將數(shù)據(jù)劃分為多種區(qū)間數(shù)據(jù)。設(shè)有n個數(shù)據(jù)，劃分為若干組，每組組中值為xi，每組數(shù)據(jù)頻數(shù)為fi，其算術(shù)平均數(shù)計算公式為：使用公式計算組數(shù)據(jù)算術(shù)平均數(shù)：例：對某廠工人工資整頓分組后得到如下資料：第28頁29工資（元）組中值x1000-20232023-30003000-400015002500

3500合計—要求計算工人平均工資。工人數(shù)（人）f150220

130500（1）在Excel中錄入工人數(shù)和組中值有關(guān)數(shù)據(jù)；（2）在“組中值”相鄰單元格中錄入xf，在其下方單元格中輸入公式“=A2*B2”

→單擊“Enter”，即出現(xiàn)第一組xf相乘數(shù)據(jù)成果；（3）單擊剛才數(shù)據(jù)成果單元格右下角，并同步按住“Ctrl”鍵，向下拖動鼠標(biāo)，直到最后一組數(shù)據(jù)；第29頁30（4）選中所有xf相乘數(shù)據(jù)成果單元格，單擊工具欄中“∑”，即得到∑xf數(shù)據(jù)成果；（5）在其相鄰單元格中輸入公式“=C5/B5”

→單擊“Enter”，即出現(xiàn)工人平均工資數(shù)據(jù)計算成果：2460。

算術(shù)平均數(shù)是反應(yīng)現(xiàn)象集中趨勢特性最主要指標(biāo)，是表白同類社會經(jīng)濟(jì)現(xiàn)象中各個個體在一定期間、地點條件下所達(dá)成一般水平，具有較高代表性。在實際中用途廣泛，常能夠用于比較同一時期同類現(xiàn)象在不一樣單位、不一樣地域間一般水平，也能夠作為評斷事物一種客觀標(biāo)準(zhǔn)或參照。第30頁31三、幾何平均數(shù)在Excel中能夠使用GEOMEAN函數(shù)計算幾何平均數(shù)。幾何平均數(shù)也是反應(yīng)現(xiàn)象集中趨勢辦法。在計算平均發(fā)展速度、平均增加率、平均收益率等指標(biāo)時，經(jīng)常用到幾何平均數(shù)。尤其是在多種投資項目中，收益率都是需要計算復(fù)利，用幾何平均數(shù)能較好地度量其集中趨勢。幾何平均數(shù)計算公式為：第31頁32注：這個成果是平均復(fù)利或平均發(fā)展速度。要計算平均收益率或平均增加率，就要通過公式計算：=幾何平均數(shù)計算成果-1四、眾數(shù)和中位數(shù)

和定性數(shù)據(jù)計算同樣，EXCEL中計算眾數(shù)是通過插入函數(shù)MODE（number1，number2，……）來實現(xiàn)；而計算中位數(shù)是通過插入函數(shù)MEDIAN（number1，number2，……）來實現(xiàn)。第32頁33第四節(jié)離散趨勢程度分析一、離散趨勢測定內(nèi)容二、極差三、標(biāo)準(zhǔn)差和方差四、利用“數(shù)據(jù)分析”命令進(jìn)行描述性分析

第33頁34一、離散趨勢測定內(nèi)容測定集中趨勢只是數(shù)據(jù)分析一種方面，要全面地結(jié)識現(xiàn)象還必須理解現(xiàn)象離散趨勢。由于雖然現(xiàn)象集中趨勢相同，其離散趨勢也也許不一樣，致使其數(shù)量特性也不一樣，因此，僅僅理解現(xiàn)象集中趨勢是不夠。離散趨勢主要是測定現(xiàn)象總體中各個體之間數(shù)值差異程度，體現(xiàn)在一種現(xiàn)象總體中各數(shù)值與平均數(shù)離差程度。離散趨勢指標(biāo)大小能夠說明平均數(shù)代表性大小；能夠反應(yīng)社會經(jīng)濟(jì)活動過程均衡性或穩(wěn)定性程度；還是抽樣推斷和相關(guān)分析主要指標(biāo)。測定離散趨勢主要辦法有極差、標(biāo)準(zhǔn)差和方差。第34頁35二、極差極差也稱全距，是一組數(shù)據(jù)中最大值與最小值差。它從數(shù)據(jù)取值范圍角度描述數(shù)據(jù)離散趨勢，是測定離散趨勢最簡單辦法。在Excel中計算極差辦法有兩種：

1、首先對一組數(shù)據(jù)排序，找出其中最大值與最小值，然后利用公式計算極差：極差=最大值-最小值

2、通過插入MAX函數(shù)MIN函數(shù)計算極差：極差=MAX（number1，number2…）-MIN（number1，number2…）第35頁36三、標(biāo)準(zhǔn)差和方差標(biāo)準(zhǔn)差和方差是描述數(shù)據(jù)離散趨勢最主要、最常用辦法。它從考查所有數(shù)據(jù)與算術(shù)平均數(shù)總體差距角度測度數(shù)據(jù)平均離散程度，進(jìn)而評價平均數(shù)對數(shù)據(jù)代表性大小。方差是標(biāo)準(zhǔn)差平方，實際中更多使用是標(biāo)準(zhǔn)差，其數(shù)學(xué)公式為：在Excel中計算標(biāo)準(zhǔn)差是通過插入STDEVP函數(shù)；計算方差是通過插入VARP函數(shù)。例如，A、B兩組各有6位學(xué)生參與同一次語文測驗，A組分?jǐn)?shù)為95、85、75、65、55、45，B組分?jǐn)?shù)為73、72、71、69、68、67。這兩組平均數(shù)都是70，但A組標(biāo)準(zhǔn)差為18.71分，B組標(biāo)準(zhǔn)差為2.37分，說明A組學(xué)生之間差距要比B組學(xué)生之間差距大得多。第36頁37四、利用“數(shù)據(jù)分析”命令進(jìn)行描述性分析在Excel中可使用專門數(shù)據(jù)描述性分析工具，它使得數(shù)據(jù)描述性分析實現(xiàn)愈加快捷和方便?；静僮鞑襟E如下：

（1）選擇“工具”菜單中“數(shù)據(jù)分析”命令，出現(xiàn)如下對話框：

（2）選擇“描述統(tǒng)計”，單擊“確定”，出現(xiàn)如下對話框：第37頁38

（3）在“輸入?yún)^(qū)域”框中輸入待分析數(shù)據(jù)所在單元格地址，選中“匯總統(tǒng)計”，單擊“確定”，即可出現(xiàn)描述性分析成果：第38頁39第39頁40分析成果中部分名稱含義解釋：*標(biāo)準(zhǔn)誤差：指樣本均值抽樣標(biāo)準(zhǔn)誤差；*區(qū)域：極差注意：利用“數(shù)據(jù)分析”命令計算標(biāo)準(zhǔn)差為樣本標(biāo)準(zhǔn)差，其數(shù)學(xué)定義為：其計算成果與利用STDEVP函數(shù)和VARP函數(shù)計算成果不完全相同；由于利用函數(shù)計算是總體標(biāo)準(zhǔn)差。方差亦是如此。第40頁411、對原始數(shù)據(jù)