1、1,統(tǒng) 計 分 析 Excel在統(tǒng)計分析中的應(yīng)用,2,主要內(nèi)容,3,一、如何進行統(tǒng)計分析,二、數(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è)置

2、打印多頁時的打印相同的標題 技巧12：如何建立和使用工作組進行相同操作,4,技巧1：如何快速輸入序號,輸入數(shù)值型數(shù)字：輸入1，2，然后拖動鼠標 輸入文本型數(shù)字：輸入“1”,然后拖動鼠標 精確輸入：輸入1，然后編輯填充序列 輸入帶字母的數(shù)字：單元格數(shù)字自定義，如”shyy”000,5,在“二、數(shù)據(jù)輸入”表中編號列中 任務(wù)1：輸入數(shù)字型編號1-20。 任務(wù)2：輸入文本型數(shù)字：1，然后拖動鼠標至20。 任務(wù)3：利用精確輸入，輸入編號1-100。 任務(wù)4：輸入shyy001-shyy100。,技巧2：如何用填充數(shù)據(jù)和自定義序列輸入數(shù)據(jù),填充數(shù)據(jù) 填充等差數(shù)列、等比數(shù)列 填充日期 自定義序列 如自定義姓

3、名的序列,6,在“二、數(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：定義姓名的序列，然后刪除姓名，用自定義序列的方式輸入。,技巧3：如何準確輸入身份證號、用身份證提取信息,利用“數(shù)據(jù)有效性”設(shè)置身份證號的長度：18位 用身份證提取信息： 提取性別(E3)：=mid(d3,17,1) 提取出生日期 (F3)：=mid(d3,7.8) 轉(zhuǎn)換日期格式：=Text(mid(d3,7.8),”#-00-

4、00”) 利用身份證生成數(shù)據(jù) 轉(zhuǎn)換性別：=if(mod(f3,2)=1,”男”,”女”) 計算年齡（以年）：=year(today()-year(F3) 計算年齡（以日期）：=datedif(J3,TODAY(),y),7,在“二、數(shù)據(jù)輸入”表中 任務(wù)9：設(shè)置身份證號的長度為18。 任務(wù)10：利用身份證號生成性別。 任務(wù)11：利用身份證號生成出生日期。 任務(wù)12：計算年齡和工齡。,技巧4：如何利用數(shù)據(jù)有效性輸入數(shù)據(jù),數(shù)據(jù)有效性序列，以逗號分隔輸入分類數(shù)據(jù),8,在“二、數(shù)據(jù)輸入”表中 任務(wù)13：使用數(shù)據(jù)有效性設(shè)置學(xué)歷的類型，并根據(jù)原始表進行選擇。 任務(wù)14：使用數(shù)據(jù)有效性設(shè)置職務(wù)的類型。 任務(wù)1

5、5：設(shè)置手機號碼的數(shù)字類型為文本，并設(shè)置文本長度為11。 任務(wù)16：E-mail為內(nèi)部郵箱（如姓名全拼字母）的輸入方法。,技巧5：如何用不同方式輸入日期和時間,輸入可更新時間 =today() =now() 輸入不可更新時間 直接輸入，以-或/分隔 使用快捷鍵：ctrl+;，ctrl+shift+;,9,在“二、數(shù)據(jù)輸入”表中 任務(wù)17：在空白處，輸入可更新日期和時間。 任務(wù)18：在空白處，輸入不可更新的日期和時間。,技巧7：如何為行設(shè)置不同填充顏色,首先判斷奇數(shù)行和偶數(shù)行；=mod(row( ),2) 然后，篩選出奇數(shù)行或偶數(shù)行； 最后，設(shè)置不同的填充顏色。,10,在“二、數(shù)據(jù)輸入”表中 任

6、務(wù)19：判斷奇數(shù)行和偶數(shù)行。 任務(wù)20：奇數(shù)行和偶數(shù)行設(shè)置不同的填充顏色。,技巧8：如何有選擇地利用“選擇性粘貼”,數(shù)值：僅粘貼數(shù)字結(jié)果，而非粘貼公式 轉(zhuǎn)置：行列互換,11,在“二、數(shù)據(jù)輸入”表中 任務(wù)21：粘貼年齡列為選擇性粘貼，至空白處。 任務(wù)22：選擇性粘貼B2:N22轉(zhuǎn)置至自B25單元格開始處。,技巧9：如何實現(xiàn)在單元格里換行,自動換行：格式單元格對齊自動換行； 手動換行：ALT+ENTER,12,在“二、數(shù)據(jù)輸入”表中 任務(wù)23：在備注N3中隨意輸入文字，然后設(shè)置自動換行。 任務(wù)24： 對N3中的文字進行手動強制換行。,技巧10：如何顯示標題行固定不變,窗口凍結(jié)窗格,13,在“二、數(shù)

7、據(jù)輸入”表中 任務(wù)25： 設(shè)置標題行固定不變。,技巧11：如何設(shè)置打印多頁時的打印相同的標題,文件頁面設(shè)置工作表頂端標題行（或左側(cè)標題行）,14,在“二、數(shù)據(jù)輸入”表中 任務(wù)26： 設(shè)置打印標題行為頂端標題行。,技巧12：如何建立和使用工作組進行相同操作,快速生成格式相同的工作表，如1-12月報表,15,任務(wù)27： 新建工作簿，生成1-12月格式相同報表，并設(shè)置相同的格式。,三、數(shù)據(jù)的處理顯示和查詢,（一）排序數(shù)據(jù) （二）使用條件格式標識數(shù)據(jù) （三）篩選數(shù)據(jù),16,（一）排序數(shù)據(jù),升序：如按年齡 降序 多關(guān)鍵字排序：如按年齡升序，再按工齡升序 自定義排序： 如按學(xué)歷排序，碩士-本科-專科，按職

8、務(wù)大小排序，經(jīng)理主管職員 按筆畫排序 按行排序,17,在表“三、數(shù)據(jù)處理（排序、篩選、條件格式）”中， 任務(wù)28： 對年齡升序。 任務(wù)29：對年齡升序，再按工齡降序。 任務(wù)30：按學(xué)歷由高到低排序。 任務(wù)31：對姓名按筆畫排序。,（二）使用條件格式標識數(shù)據(jù),格式條件格式 如工齡10年以上用紅色加粗顯示。,18,在表“三、數(shù)據(jù)處理（排序、篩選、條件格式）”中， 任務(wù)32：工齡15年以上用紅色加粗顯示,（三）篩選數(shù)據(jù),自動篩選 高級篩選,19,自動篩選,數(shù)據(jù)篩選自動篩選 根據(jù)列表篩選：如篩選性別是男的 自定義篩選：如篩選年齡30歲以下的 空白 非空白 注：？代表單個字符，*代表多個字符,20,在表

9、“三、數(shù)據(jù)處理（排序、篩選、條件格式）”中， 任務(wù)33： 篩選性別為男的。 任務(wù)34：篩選年齡30歲以下的。,高級篩選,設(shè)置多個條件區(qū)域 條件是“與”的關(guān)系：同行 如篩選年齡35歲以下，且職務(wù)是經(jīng)理的 條件是“或”的關(guān)系：不同行 如篩選年齡35歲以下，或職務(wù)是經(jīng)理的,21,在表“三、數(shù)據(jù)處理（排序、篩選、條件格式）”中， 任務(wù)35：篩選年齡35歲以下，且職務(wù)是經(jīng)理的 任務(wù)36：篩選年齡35歲以下，或職務(wù)是經(jīng)理的,四、數(shù)據(jù)的匯總分析,（一）分組分析法 （二）合并計算匯總數(shù)據(jù) （三）數(shù)據(jù)透視表從不同角度分析表中的數(shù)據(jù),22,（一）分組分析法,分類匯總的方法：先排序，后匯總 如男、女人數(shù) 利用函數(shù)的

10、方法 COUNTIF函數(shù) 如學(xué)歷構(gòu)成 FREQUENCY函數(shù) 如年齡構(gòu)成 利用統(tǒng)計分析工具庫直方圖,23,在表“四、數(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)成。,（二）合并計算,數(shù)據(jù)合并計算,24,在表“四、數(shù)據(jù)匯總分析(二）合并計算”中， 任務(wù)41：在銷售情況總表中計算總銷售數(shù)量。 任務(wù)42：在銷售情況總表中計算平均銷售數(shù)量、總銷售金額。,25,（三）數(shù)據(jù)透視表,25,在表“四、數(shù)據(jù)匯總分析(三）數(shù)據(jù)透視表”中， 任務(wù)43：制作“企業(yè)

11、員工檔案管理表”數(shù)據(jù)透視表并進行瀏覽和修改。,五、數(shù)據(jù)匯總結(jié)果的表示,（一）繪制統(tǒng)計表 （二）繪制統(tǒng)計圖,26,27,（一）繪制統(tǒng)計表,如何繪制分組匯總表（次數(shù)分布表）,27,在表“五、數(shù)據(jù)匯總結(jié)果表示繪制統(tǒng)計表和統(tǒng)計圖(一）”中， 任務(wù)44：按照統(tǒng)計要求制作年齡構(gòu)成表。,統(tǒng)計表設(shè)計口訣,28,標題準確又簡明 布局均勻又美觀 頂線底線加粗線 左右兩側(cè)不要封 橫欄縱欄要對應(yīng) 計量單位要注明,單位相同右上方 縱欄不同右下方 橫行不同加一欄 欄目多時加序號 主體敘述欄不同 主體欄用甲乙丙 敘述欄用1、2、3,29,（二）繪制統(tǒng)計圖,柱形圖：數(shù)值的變化和對比 條形圖：數(shù)值的變化和對比，表現(xiàn)數(shù)據(jù)差額 餅

12、圖：顯示一組數(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ù)

13、50：根據(jù)考核表，繪制雷達圖。,30,六、高級統(tǒng)計分析,工具加載宏分析工具庫 （一）描述統(tǒng)計法認識數(shù)據(jù)總體的特征 （二）相關(guān)及回歸分析法因變量如何受其他自變量（1個變量或多個變量）影響的 （三）時間序列預(yù)測法根據(jù)過去的模式預(yù)測未來 （四）方差分析法分類變量對數(shù)值變量有無影響 （五）抽樣推斷法如何由抽取的樣本數(shù)據(jù)推斷總體 （六）假設(shè)檢驗法檢驗假設(shè)是否成立,30,31,（一）描述統(tǒng)計法,工具數(shù)據(jù)分析描述統(tǒng)計，顯示如下統(tǒng)計量 平均值、標準誤差、中位數(shù)、眾數(shù)、標準差、方差、 峰度、偏度 最小值、最大值 求和 觀測數(shù),31,在表“六、高級統(tǒng)計分析（一）描述統(tǒng)計”中， 任務(wù)51：根據(jù)歷史銷售額，計算顯示描

14、述統(tǒng)計量。,32,（二）相關(guān)及回歸分析法,相關(guān)分析：計算相關(guān)系數(shù)，檢驗變量間的變化是否相關(guān)。 回歸分析：利用變量間的關(guān)系進行預(yù)測。 線性回歸,32,在表“六、（二）相關(guān)回歸分析”中， 任務(wù)52：根據(jù)年份和銷售額，繪制散點圖。 任務(wù)53：計算相關(guān)系數(shù)。 任務(wù)54：進行回歸分析。 任務(wù)55：根據(jù)回歸分析結(jié)果和年份數(shù)據(jù)，預(yù)測銷售額。,33,（三）時間序列預(yù)測法,移動平均法 指數(shù)平滑法 趨勢模型預(yù)測法,33,在表“六、（三）時間序列預(yù)測”中， 任務(wù)56：利用移動平均法計算一次移動平均和二次移動平均預(yù)測值。 任務(wù)57：利用指數(shù)平滑法預(yù)測。,34,（四）方差分析法（ANOVA）,推斷兩組或多組資料的總體均

15、值是否相同，判斷分類變量對數(shù)值變量是否有顯著影響。 單因素方差分析 雙因素方差分析,34,在表“六、（四）方差分析”中， 任務(wù)58： 為實驗三種鎮(zhèn)咳藥（可待因、復(fù)方1號、復(fù)方2號），分別用這三 種藥給三組小白鼠灌胃，測得小白鼠延遲咳嗽時間（秒）如表所示，利用 單因素方差分析，比較三種藥物的鎮(zhèn)咳作用。（顯著性水平=0.05）,35,（五）抽樣推斷法,根據(jù)部分資料推斷總體的數(shù)據(jù)特征。 推斷的區(qū)間：95%，t=1.96,35,在表“六、（五）抽樣推斷法”中， 任務(wù)59： 某企業(yè)有職工1385人，現(xiàn)從中隨機抽出50人，調(diào)查工資收入情 況，請以95%的置信度估計該企業(yè)月工資的范圍。,36,（六）假設(shè)檢驗

16、法,檢驗是否應(yīng)拒絕原先的假設(shè)。 F檢驗：檢驗兩個正態(tài)總體方差有無顯著差異。 t檢驗：檢驗兩個總體均值有無顯著差異。 雙樣本等方差 雙樣本異方差 平均值的成對二樣本 Z檢驗：檢驗兩個正態(tài)總體均值有無顯著差異。 雙樣本平均差檢驗,36,37,F檢驗：檢驗兩個正態(tài)總體方差有無顯著差異,在表“六、（六）假設(shè)檢驗法”中， 任務(wù)60：已知某校男女新生錄取分數(shù)，判斷方差是否相等？,38,t檢驗：檢驗兩個總體均值有無顯著差異,雙樣本等方差 雙樣本異方差 平均值的成對二樣本,在表“六、（六）假設(shè)檢驗法”中， 任務(wù)63：某醫(yī)療器械公司最近推出一種新的減肥儀器，并以“免費試用”的營銷手段大肆宣傳。國家質(zhì)最監(jiān)督機構(gòu)為了檢查其實際效果，隨機訪問了10 位試用者，得知其試用前后體重的變化如“減肥儀器試用前后的體重變化”表，請檢驗體重是否有顯著變化？,在表“六、（六）假設(shè)檢驗法”中， 任務(wù)61：根據(jù)“某高校男女新生錄取分數(shù)”表，判斷男女新生錄取分數(shù)是否存在顯著差別？,在表“六、（六）假設(shè)檢驗法”中， 任務(wù)62： 根據(jù)“分性別的工資表”首先根據(jù)F檢驗，判斷是否存在異方差，然后判斷男女工資是否存在顯著性差異？,39,