1、Excel在統(tǒng)計工作中的應用技巧,2017年11月,崗位知識培訓,Excel是統(tǒng)計工作以及日常工作的基本工具，根據培訓要求和工作實際，我著重介紹Excel在數據搜集、錄入、整理、匯總、展示等過程中的應用技巧，同時再簡要介紹有關常用統(tǒng)計公式與函數的使用技巧。,一、用Excel搜集與整理數據,用Excel搜集數據 用Excel錄入數據 用Excel整理數據 用Excel作統(tǒng)計圖,（一）用Excel搜集數據,搜集數據的方法有多種，可以采用統(tǒng)計報 表、典型調查、重點調查或抽樣調查。 針對抽樣調查，Excel的數據分析工具中提 供了一個專門的“抽樣”工具，可以幫助使 用者快速完成抽樣工作。,使用Exce

2、l進行抽樣，首先要對各個總 體單位進行編號，編號可以按隨機原則， 也可以按有關標志。,例 ：假定有100名消費者，從中選取15人進行工資收入調查。 首先，將100名消費者編號為1100號，利用填充功能輸入編號。,向右填充：,第一步：單擊“數據”菜單，選擇“數據分析”選項,說明：若無數據分析選項，可在“文件”菜單下選擇選項加載項轉到，在彈出的對話框中選擇分析工具庫，便可出現數據分析選項。OFFICE20102013版本均可以。,其次，利用“抽樣”工具進行抽樣的具體操作如下：,第二步：打開“數據分析”對話框，從其對話框的“分析工具”列表中選擇“抽樣”選項,第三步：打開“抽樣”對話框，確定輸入區(qū)域、

3、抽樣方法和輸出區(qū)域,1確定輸入區(qū)域 只能對于數值型數據進行抽樣，文本形式數據無效。 2選定抽樣方法 在“抽樣方法”框中，有“周期”和“隨機”兩種抽樣模式。 （1）“周期”模式即所謂的等距抽樣。此種抽樣方法，需要確定周期間隔，周期間隔是由總體單位數除以要抽取的樣本數而求得的整數。若要在100個總體單位中抽取5個樣本，則在“間隔”框中輸入20。 （2）“隨機”模式適用于簡單隨機抽樣等。,3指定輸出方向 在“輸出選項”框中有三種輸出去向。在“輸出區(qū)域”框中輸入存放樣本單位的起始單元格編號。在本例中，輸入區(qū)域為$A$18。也可以通過選擇“新工作表”或“新工作簿”將抽樣結果放在新工作表或新工作薄中。,第

4、四步：單擊“確定”按鈕后，在指定的位置給出抽樣的結果。,（二）用Excel錄入數據,1、輸入日期、時間 在單元格中插入當前日期： Ctrl + ; 在單元格中插入當前時間：Ctrl + Shift + ;,（1）輸入負數 在單元格中輸入負數時，可在負數前輸入“-”作標識，也可將數字置在（）括號內來標識。,2、輸入數字,（2）輸入分數 Excel中日期的輸入方法也是用斜杠來區(qū)分年月日的，為了避免將輸入的分數與日期混淆，在單元格中輸入分數時，要在分數前輸入“0”（零）以示區(qū)別，并且在“0”和分子之間要有一個空格隔開。 這種方式存儲的分數可以作為數值參與運算，而 1/2 作為文本存儲的分數不能參與運

5、算。,例： 輸入“1/2”，按回車鍵則顯示“1月2日”； 輸入“0 1/2”，按回車鍵則顯示“1/2”，編輯欄里顯示0.5； 輸入“8 1/2”，按回車鍵則單元格顯示“8 1/2”，而在編輯欄中顯示“8.5”。,（3）輸入小數 當需要輸入大量帶有固定小數位的數字或帶有固定位數的以“0”字符串結尾的數字時 正數表示小數點后的位數，負數表示小數點前零的個數。,正數表示小數點后的位數，負數表示小數點前零的個數。 如果要在輸入“88”后顯示“0.088”，要在“位數”微調框中輸入“3”。 如果要在輸入“88”后顯示88000”，就在“位數”微調框中輸入“-3”。 注意：在完成輸入帶有小數位或結尾零字符

6、串的數字后，應清除對“自動設置小數點”符選框的選定，以免影響后續(xù)的輸入。,（4）文本類型數字輸入 組織機構代碼、電話號碼、行業(yè)代碼等需要將數字當成文本輸入。常用的錄入的方式有三種: 1）、單元格格式的設置,2）、在輸入第一個字符前，鍵入單引號“ ”； 例：鍵入027，單元格中顯示027 3）、先鍵入等號“= “ ” 。 例：鍵入=001，單元格中顯示001,編輯自定義填充序列 解決日常工作中，一些無法通過常規(guī)方法實現快速填充的序列。 例如生產經營活動一、生產經營活動二、生產經營活動三.等。,（5）填充功能,“excel選項” “高級” “常規(guī)” “創(chuàng)建用于排序和填充序列的列表” “編輯自定義列

7、表”,輸入完成后單擊“添加”“確定”,（三）用Excel整理數據,在統(tǒng)計工作中，我們從企業(yè)或個體戶取得了大量的基層調查數據。這些數據是雜亂的，沒有規(guī)則的，因此，必須對搜集到的大量的原始數據加工整理。 Excel提供了多種數據整理工具：排序、篩選、分組、分類匯總、數據透視表、直方圖等。,1、數據有效性 數據有效性用來定義在單元格中可以輸入或應該在單元格中輸入哪些數據。 通過配置數據有效性，可以防止用戶輸入無效數據，或者圈釋無效數據。,例：設置工業(yè)個體戶從業(yè)人員人數的有效性規(guī)則為：020人。,設置數據有效性的方法：,（1）在未輸入數據前，可以這樣做： “數據” “數據工具” “數據有效性”,（2）

8、在未輸入數據前，還可以這樣做：,在來源框中輸入“許昌市,洛陽市”，選項時間用西文 的逗號（,）隔開,（3）在輸入數據后，可以這樣做：,2、條件格式,主要用于查找符合某些條件的數據，對滿足條件的單元格，可以通過字體或者顏色上面的變化等，做出一些醒目的標記。 也常用于分段顯示某些數據，如不同工資水準、成績。,（1）突出顯示單元格,“開始” “樣式” “條件格式” “突出顯示單元格規(guī)則”,例：快速檢查是否有重復的個體戶。,（2）分段顯示數據,“開始” “樣式” “條件格式” “色階”,例：使用色階，分段顯示單元格，實現主營業(yè)務收入的可視化效果。,（3）清除條件格式規(guī)則,（四）用Excel作統(tǒng)計圖,E

9、xcel提供了大量的統(tǒng)計圖形供用戶根據需要選擇使用。Excel提供的圖形工具有：柱形圖、折線圖、餅圖、散點圖、面積圖、環(huán)形圖、雷達圖等。 各種圖的作法大同小異，下面以兩軸線柱圖為例，介紹制作統(tǒng)計圖的工作步驟。,STEP 1 選中數據，點擊“插入”“柱形圖；,STEP 2 在需要更改的系列的圖例上點擊右鍵，選擇“設置數據系列格式”；,STEP 3 在“系統(tǒng)選項”“系列繪制在”中，選擇“次坐標軸”，然后單擊“關閉”,STEP 4在欲改為折線的系列的柱形上單擊右鍵，選擇“更改系列圖表類型”；,STEP 5 在“更改圖表類型”中，選擇“折線圖”；,STEP 6 添加“數據標簽”等進行完善和美化。,二、

10、EXCEL打印設置,在我們實際工作中，如何把收集、整理、匯總和分析所得到的EXCEL表格數據進行標準化、規(guī)范化的打?。看蠹覒撌褂玫帽容^多，下面主要介紹三個技巧： 技巧一：設置打印標題 假如打印內容較多，需要打印多頁，通過設置頂端標題行，實現在每一頁上打印列標簽；通過設置左端標題列，實現在每一頁上打印行標簽。 “頁面布局” 打印標題” “頁面設置”,技巧二：插入頁碼 “插入” “文本” “頁眉和頁腳”,返回普通視圖，可在“視圖”選項卡上的“工作簿視圖”組中單擊“普通”,技巧三：不打印錯誤值,三、統(tǒng)計公式與函數,Excel作為數據處理軟件，要達到提升數據綜合分析能力、實現高效統(tǒng)計的目標，還必須全

11、面、熟練掌握和應用統(tǒng)計公式與函數。 1.在單元格中輸入含等號的運算式，EXCEL中稱之為公式，都是數學里面的基本運算。 2.EXCEL中提供了豐富的函數，分為財務函數、日期與時間函數、數學與三角函數、統(tǒng)計函數、查找引用函數、數據庫函數、文本函數、邏輯函數等幾大類。 說明：由于大部分函數專業(yè)性強，本次崗位知識培訓主要介紹幾個在統(tǒng)計工作中常用的函數及與使用公式函數相關的知識點，其他可根據自己的需要自行學習。,1.數據的保護 1)對文件的保護 第一步，創(chuàng)建完成一個EXCEL文檔后，點擊“文件”菜單的子菜單“另存為”，如下圖：,第二步，再點擊“工具”菜單下拉欄中的“常規(guī)選項” 這時輸入文件共享所需要的

12、打開權限密碼就可實現對文檔的保護。,（二）常用函數的應用,1.常用統(tǒng)計函數 求數組或單元格數字的算術平均值函數AVERAGE 求數據集最大值函數MAX與最小值函數MIN 求數據集中第K個最大值LARGE與第k個最小值SMALL 求數據集中的中位數函數MEDIAN 求數據集中出現頻率最多的數（眾數）函數MODE,3.VLOOKUP函數（字段匹配函數） 強大的vlookup函數是做統(tǒng)計分析中最常用的函數之一，因為很多字段信息都是分布在各個不同的表里面，原數據表可能沒有我們需要的字段，而需要從其他數據表中獲取相關的字段信息，這時候就是發(fā)揮vlookup函數作用的時候了。,特別說明： 一是括號里有四個

13、參數，是必需的。最后一個參數range_lookup是個邏輯值，我們常常輸入一個0字，或者False;其實也可以輸入一個1字，或者true。 二是Lookup_value是一個很重要的參數，它可以是數值、文字字符串、或參照地址。參照地址的單元格格式類別與去搜尋的單元格格式的類別要一致。 三是在使用該函數時，lookup_value的值必須在table_array中處于第一列。 四是存在無數多列數據時，則第三個參數可使用函數COLUMN函數，即COLUMN(目標值所在列)，注意判斷是否需+1或-1。列數較小時，也可直接輸入數值。 五是VLOOKUP中的V參數表示垂直方向。還有一個橫向查找函數HL

14、OOKUP，和VLOOKUP函數屬于一類函數，HLOOKUP是按行查找的，VLOOKUP是按列查找的，使用方法基本一致。,COUNT函數只是最基本的統(tǒng)計分析函數，是其他計數類函數的基礎。下面給大家介紹幾個CONUT函數家族的其他幾個成員。 COUNTA函數。 COUNTA函數計算區(qū)域中不為空的單元格的個數。 COUNTBLANK函數。計算指定單元格區(qū)域中空白單元格的個數。 COUNTIF函數。用法一：利用COUNTIF函數識別重復數據。用法二：按照條件格式計數。,6.字段分列與合并函數 在數據分析的過程中，很多時候我們都需要進行數據抽取，即保留原數據表中某些字段的部分信息，組成一個新的字段。可以是截取某一個字段的部分信息字段分列；也可以是將某幾個字段合并成為一個