統(tǒng)計分析培訓(xùn)(上課用)

統(tǒng)計分析

——Excel在統(tǒng)計分析中的應(yīng)用1精選課件?鄭振華主要內(nèi)容一、如何進行統(tǒng)計分析三、數(shù)據(jù)的處理四、數(shù)據(jù)的匯總分析二、數(shù)據(jù)的輸入五、數(shù)據(jù)匯總結(jié)果的表示六、高級統(tǒng)計分析介紹統(tǒng)計分析的流程介紹如何顯示和查詢數(shù)據(jù)介紹常用的匯總分析方法介紹數(shù)據(jù)的輸入技巧如何繪制統(tǒng)計表和統(tǒng)計圖統(tǒng)計分析工具庫的應(yīng)用2一、如何進行統(tǒng)計分析3二、數(shù)據(jù)輸入與編輯技巧技巧1：如何快速輸入序號，如1-100，shyy001-shyy100技巧2：如何用填充數(shù)據(jù)和自定義序列輸入數(shù)據(jù)，如姓名技巧3：如何準確輸入身份證號、用身份證提取信息，如性別、年齡技巧4：如何利用數(shù)據(jù)有效性輸入數(shù)據(jù)，如學(xué)歷、職務(wù)技巧5：如何用不同方式輸入日期和時間技巧6：如何在多個單元格輸入相同數(shù)據(jù)技巧7：如何為行設(shè)置不同填充顏色防止輸錯行技巧8：如何有選擇地利用“選擇性粘貼”技巧9：如何實現(xiàn)在單元格里手動換行技巧10：如何顯示標題行固定不變技巧11：如何設(shè)置打印多頁時的打印相同的標題技巧12：如何建立和使用工作組進行相同操作4技巧1：如何快速輸入序號輸入數(shù)值型數(shù)字：輸入1，2，然后拖動鼠標輸入文本型數(shù)字：輸入“’1”,然后拖動鼠標精確輸入：輸入1，然后編輯—填充—序列輸入帶字母的數(shù)字：單元格—數(shù)字—自定義，如”shyy”000在“二、數(shù)據(jù)輸入”表中編號列中任務(wù)1：輸入數(shù)字型編號1-20。任務(wù)2：輸入文本型數(shù)字：‘1，然后拖動鼠標至20。任務(wù)3：利用精確輸入，輸入編號1-100。任務(wù)4：輸入shyy001-shyy100。5技巧2：如何用填充數(shù)據(jù)和自定義序列輸入數(shù)據(jù)填充數(shù)據(jù)填充等差數(shù)列、等比數(shù)列填充日期自定義序列如自定義姓名的序列在“二、數(shù)據(jù)輸入”表中任務(wù)5：在空白處，填充等差數(shù)列為奇數(shù)列，初始值為1，終止值為10。任務(wù)6：在空白處，填充等比數(shù)列，初始值為1，比例為2，終止值為64。任務(wù)7：在空白處，填充日期2010-4-26，步長為7，終止值為2010-12-31。任務(wù)8：定義姓名的序列，然后刪除姓名，用自定義序列的方式輸入。6技巧3：如何準確輸入身份證號、用身份證提取信息利用“數(shù)據(jù)有效性”設(shè)置身份證號的長度：18位用身份證提取信息：提取性別(E3)：=mid(d3,17,1)提取出生日期(F3)：=mid(d3,7.8)轉(zhuǎn)換日期格式：=Text(mid(d3,7.8),”#-00-00”)利用身份證生成數(shù)據(jù)轉(zhuǎn)換性別：=if(mod(f3,2)=1,”男”,”女”)計算年齡（以年）：=year(today())-year(F3)計算年齡（以日期）：=datedif(J3,TODAY(),"y")在“二、數(shù)據(jù)輸入”表中任務(wù)9：設(shè)置身份證號的長度為18。任務(wù)10：利用身份證號生成性別。任務(wù)11：利用身份證號生成出生日期。任務(wù)12：計算年齡和工齡。7技巧4：如何利用數(shù)據(jù)有效性輸入數(shù)據(jù)數(shù)據(jù)—有效性—序列，以逗號分隔輸入分類數(shù)據(jù)在“二、數(shù)據(jù)輸入”表中任務(wù)13：使用數(shù)據(jù)有效性設(shè)置學(xué)歷的類型，并根據(jù)原始表進行選擇。任務(wù)14：使用數(shù)據(jù)有效性設(shè)置職務(wù)的類型。任務(wù)15：設(shè)置手機號碼的數(shù)字類型為文本，并設(shè)置文本長度為11。任務(wù)16：E-mail為內(nèi)部郵箱（如姓名全拼字母@）的輸入方法。8技巧5：如何用不同方式輸入日期和時間輸入可更新時間=today()=now()輸入不可更新時間直接輸入，以-或/分隔使用快捷鍵：ctrl+;，ctrl+shift+;在“二、數(shù)據(jù)輸入”表中任務(wù)17：在空白處，輸入可更新日期和時間。任務(wù)18：在空白處，輸入不可更新的日期和時間。9技巧7：如何為行設(shè)置不同填充顏色首先判斷奇數(shù)行和偶數(shù)行；=mod(row(),2)然后，篩選出奇數(shù)行或偶數(shù)行；最后，設(shè)置不同的填充顏色。在“二、數(shù)據(jù)輸入”表中任務(wù)19：判斷奇數(shù)行和偶數(shù)行。任務(wù)20：奇數(shù)行和偶數(shù)行設(shè)置不同的填充顏色。10技巧8：如何有選擇地利用“選擇性粘貼”數(shù)值：僅粘貼數(shù)字結(jié)果，而非粘貼公式轉(zhuǎn)置：行列互換在“二、數(shù)據(jù)輸入”表中任務(wù)21：粘貼年齡列為選擇性粘貼，至空白處。任務(wù)22：選擇性粘貼B2:N22轉(zhuǎn)置至自B25單元格開始處。11技巧9：如何實現(xiàn)在單元格里換行自動換行：格式—單元格—對齊—自動換行；手動換行：ALT+ENTER在“二、數(shù)據(jù)輸入”表中任務(wù)23：在備注N3中隨意輸入文字，然后設(shè)置自動換行。任務(wù)24：對N3中的文字進行手動強制換行。12技巧10：如何顯示標題行固定不變窗口—凍結(jié)窗格在“二、數(shù)據(jù)輸入”表中任務(wù)25：設(shè)置標題行固定不變。13技巧11：如何設(shè)置打印多頁時的打印相同的標題文件—頁面設(shè)置—工作表—頂端標題行（或左側(cè)標題行）在“二、數(shù)據(jù)輸入”表中任務(wù)26：設(shè)置打印標題行為頂端標題行。14技巧12：如何建立和使用工作組進行相同操作快速生成格式相同的工作表，如1-12月報表任務(wù)27：新建工作簿，生成1-12月格式相同報表，并設(shè)置相同的格式。15三、數(shù)據(jù)的處理——顯示和查詢（一）排序數(shù)據(jù)（二）使用條件格式標識數(shù)據(jù)（三）篩選數(shù)據(jù)16（一）排序數(shù)據(jù)升序：如按年齡降序多關(guān)鍵字排序：如按年齡升序，再按工齡升序自定義排序：如按學(xué)歷排序，碩士-本科-?？?，按職務(wù)大小排序，經(jīng)理—主管—職員按筆畫排序按行排序在表“三、數(shù)據(jù)處理（排序、篩選、條件格式）”中，任務(wù)28：對年齡升序。任務(wù)29：對年齡升序，再按工齡降序。任務(wù)30：按學(xué)歷由高到低排序。任務(wù)31：對姓名按筆畫排序。17（二）使用條件格式標識數(shù)據(jù)格式—條件格式如工齡10年以上用紅色加粗顯示。在表“三、數(shù)據(jù)處理（排序、篩選、條件格式）”中，任務(wù)32：工齡15年以上用紅色加粗顯示18（三）篩選數(shù)據(jù)自動篩選高級篩選19自動篩選數(shù)據(jù)—篩選—自動篩選根據(jù)列表篩選：如篩選性別是男的自定義篩選：如篩選年齡30歲以下的空白非空白注：？代表單個字符，*代表多個字符

在表“三、數(shù)據(jù)處理（排序、篩選、條件格式）”中，任務(wù)33：篩選性別為男的。任務(wù)34：篩選年齡30歲以下的。20高級篩選設(shè)置多個條件區(qū)域條件是“與”的關(guān)系：同行如篩選年齡35歲以下，且職務(wù)是經(jīng)理的條件是“或”的關(guān)系：不同行如篩選年齡35歲以下，或職務(wù)是經(jīng)理的在表“三、數(shù)據(jù)處理（排序、篩選、條件格式）”中，任務(wù)35：篩選年齡35歲以下，且職務(wù)是經(jīng)理的任務(wù)36：篩選年齡35歲以下，或職務(wù)是經(jīng)理的21四、數(shù)據(jù)的匯總分析（一）分組分析法（二）合并計算——匯總數(shù)據(jù)（三）數(shù)據(jù)透視表——從不同角度分析表中的數(shù)據(jù)22（一）分組分析法分類匯總的方法：先排序，后匯總?cè)缒?、女人?shù)利用函數(shù)的方法COUNTIF函數(shù)如學(xué)歷構(gòu)成FREQUENCY函數(shù)如年齡構(gòu)成利用統(tǒng)計分析工具庫——直方圖在表“四、數(shù)據(jù)匯總分析(一）分組分析”中，任務(wù)37：利用分類匯總統(tǒng)計男女人數(shù)。任務(wù)38：利用COUNTIF函數(shù)統(tǒng)計學(xué)歷構(gòu)成。任務(wù)39：利用FREQUENCY函數(shù)統(tǒng)計齡構(gòu)成。任務(wù)40：利用直方圖統(tǒng)計年齡構(gòu)成。23（二）合并計算數(shù)據(jù)—合并計算在表“四、數(shù)據(jù)匯總分析(二）合并計算”中，任務(wù)41：在銷售情況總表中計算總銷售數(shù)量。任務(wù)42：在銷售情況總表中計算平均銷售數(shù)量、總銷售金額。24（三）數(shù)據(jù)透視表25在表“四、數(shù)據(jù)匯總分析(三）數(shù)據(jù)透視表”中，任務(wù)43：制作“企業(yè)員工檔案管理表”數(shù)據(jù)透視表并進行瀏覽和修改。25五、數(shù)據(jù)匯總結(jié)果的表示（一）繪制統(tǒng)計表（二）繪制統(tǒng)計圖26（一）繪制統(tǒng)計表如何繪制分組匯總表（次數(shù)分布表）27某公司年齡構(gòu)成表年齡人數(shù)（人）比例（%）30歲以下21030-3552535-40115540歲以上210合計20100在表“五、數(shù)據(jù)匯總結(jié)果表示—繪制統(tǒng)計表和統(tǒng)計圖(一）”中，任務(wù)44：按照統(tǒng)計要求制作年齡構(gòu)成表。27統(tǒng)計表設(shè)計口訣標題準確又簡明布局均勻又美觀頂線底線加粗線左右兩側(cè)不要封橫欄縱欄要對應(yīng)計量單位要注明單位相同右上方縱欄不同右下方橫行不同加一欄欄目多時加序號主體敘述欄不同主體欄用甲乙丙敘述欄用1、2、328（二）繪制統(tǒng)計圖柱形圖：數(shù)值的變化和對比條形圖：數(shù)值的變化和對比，表現(xiàn)數(shù)據(jù)差額餅圖：顯示一組數(shù)據(jù)結(jié)構(gòu)比例圓環(huán)圖：顯示多組數(shù)據(jù)的結(jié)構(gòu)比例折線圖：顯示數(shù)據(jù)的變化趨勢，利用折線圖預(yù)測雷達圖：相對于中心點及批次數(shù)據(jù)類別間的差別散點圖：顯示成對數(shù)據(jù)之間的關(guān)系氣泡圖：散點圖的特殊類型組合圖表：如柱形圖+折線圖29在表“五、數(shù)據(jù)匯總結(jié)果表示—繪制統(tǒng)計表和統(tǒng)計圖(一）”中，任務(wù)45：根據(jù)年齡構(gòu)成表繪制柱形圖。任務(wù)46：根據(jù)年齡構(gòu)成表繪制餅圖。任務(wù)47：根據(jù)一公司和二公司年齡構(gòu)成表繪制圓環(huán)圖。在表“五、數(shù)據(jù)匯總結(jié)果表示—繪制統(tǒng)計表和統(tǒng)計圖(二）”中，任務(wù)48：根據(jù)銷售額歷史數(shù)據(jù)繪制折線圖，并添加趨勢線進行預(yù)測。任務(wù)49：根據(jù)銷售額歷史數(shù)據(jù)繪制線-柱圖的組合圖。任務(wù)50：根據(jù)考核表，繪制雷達圖。29六、高級統(tǒng)計分析工具—加載宏—分析工具庫（一）描述統(tǒng)計法——認識數(shù)據(jù)總體的特征（二）相關(guān)及回歸分析法——因變量如何受其他自變量（1個變量或多個變量）影響的（三）時間序列預(yù)測法——根據(jù)過去的模式預(yù)測未來（四）方差分析法——分類變量對數(shù)值變量有無影響（五）抽樣推斷法——如何由抽取的樣本數(shù)據(jù)推斷總體（六）假設(shè)檢驗法——檢驗假設(shè)是否成立3030（一）描述統(tǒng)計法工具—數(shù)據(jù)分析—描述統(tǒng)計，顯示如下統(tǒng)計量平均值、標準誤差、中位數(shù)、眾數(shù)、標準差、方差、峰度、偏度最小值、最大值求和觀測數(shù)31在表“六、高級統(tǒng)計分析（一）描述統(tǒng)計”中，任務(wù)51：根據(jù)歷史銷售額，計算顯示描述統(tǒng)計量。31（二）相關(guān)及回歸分析法相關(guān)分析：計算相關(guān)系數(shù)，檢驗變量間的變化是否相關(guān)?；貧w分析：利用變量間的關(guān)系進行預(yù)測。線性回歸32在表“六、（二）相關(guān)回歸分析”中，任務(wù)52：根據(jù)年份和銷售額，繪制散點圖。任務(wù)53：計算相關(guān)系數(shù)。任務(wù)54：進行回歸分析。任務(wù)55：根據(jù)回歸分析結(jié)果和年份數(shù)據(jù)，預(yù)測銷售額。32（三）時間序列預(yù)測法移動平均法指數(shù)平滑法趨勢模型預(yù)測法33在表“六、（三）時間序列預(yù)測”中，任務(wù)56：利用移動平均法計算一次移動平均和二次移動平均預(yù)測值。任務(wù)57：利用指數(shù)平滑法預(yù)測。33（四）方差分析法（ANOVA）

推斷兩組或多組資料的總體均值是否相同，判斷分類變量對數(shù)值變量是否有顯著影響。單因素方差分析雙因素方差分析34在表“六、（四）方差分析”中，任務(wù)58：

為實驗三種鎮(zhèn)咳藥（可待因、復(fù)方1號、復(fù)方2號），分別用這三種藥給三組小白鼠灌胃，測得小白鼠延遲咳嗽時間（秒）如表所示，利用單因素方差分析，比較三種藥物的鎮(zhèn)咳作用。（顯著性水平=0.05）34（五）抽樣推斷法根據(jù)部分資料推斷總體的數(shù)據(jù)特征。推斷的區(qū)間：95%，t=1.9635在表“六、（五）抽樣推斷法”中，任務(wù)59：某企業(yè)有職工1385人，現(xiàn)從中隨機抽出50人，調(diào)查工資收入情況，請以95%的置信度估計該企業(yè)月工資的范圍。35（六）假設(shè)檢驗法檢驗是否應(yīng)拒絕原先的假設(shè)。F檢驗：檢驗兩個正態(tài)總體方差有無顯著差異。t檢驗：檢驗兩個總體均值有無顯著差異。雙樣本等方差雙樣本異方差平均值的成對二樣本Z檢驗：檢驗兩個正態(tài)總體均值有無顯著差異。雙樣本平均差檢驗3636F檢驗：檢驗兩個正態(tài)總體方差有無顯著差異在表“六、（六）假設(shè)檢驗法”中，任務(wù)60：已知某校男女新生錄取分數(shù)，判斷方差是否相等？37t檢驗：檢驗兩個總體均值有無顯著差異雙樣本等方差雙樣本異方差平均值的成對二樣本在表“六、（六）假設(shè)檢驗法”中，任務(wù)63：某醫(yī)療器械公司最近推出一種新的減肥儀器，并以“免費試用”的營銷手段大肆宣傳。國家質(zhì)最監(jiān)督機構(gòu)為了檢查其實際效果，隨機訪問了10位試用者，得知其試用前后體重的變化如“減肥儀器試用前后的體重變化”表，請檢驗體重是否有顯著變化？在表“六、（六）假設(shè)檢驗法”中，任務(wù)61：根據(jù)“某高校男女新生錄取分數(shù)”表，判斷男女新生錄取分數(shù)是否存在顯著差別？在表“六、（六）假設(shè)檢驗法”中，任務(wù)62：根據(jù)“分性別的工資表”首先根據(jù)F檢驗，判斷是否存在異方差，然后判