人事信息數(shù)據(jù)統(tǒng)計分析-2課件

1、Office職場應(yīng)用書庫 Excel 2010人力資源管理入門與實戰(zhàn)體驗第1頁，共24頁。人事信息數(shù)據(jù)統(tǒng)計分析制作人事信息數(shù)據(jù)表人事數(shù)據(jù)的條件求和計數(shù)分析員工學歷水平人事數(shù)據(jù)表的兩表數(shù)據(jù)核對員工人事信息數(shù)據(jù)查詢表（員工信息卡片）統(tǒng)計不同年齡段員工信息人力資源月報動態(tài)圖表第2頁，共24頁。制作人事信息數(shù)據(jù)表創(chuàng)建人事信息數(shù)據(jù)表利用數(shù)據(jù)有效性防止工號重復(fù)輸入身份證號中提取生日、性別等有效信息應(yīng)用DATEDIF函數(shù)計算員工工齡設(shè)置每頁頂端標題行和底端標題行美化表格第3頁，共24頁。人事數(shù)據(jù)的條件求和計數(shù)人事數(shù)據(jù)的單字段單條件求和計數(shù)人事數(shù)據(jù)的單字段多條件求和計數(shù)人事數(shù)據(jù)的多字段多條件求和計數(shù)DSUM數(shù)

2、據(jù)庫函數(shù)的應(yīng)用第4頁，共24頁。個人所得稅代扣代繳表創(chuàng)建個人所得稅代扣代繳表使用數(shù)組公式計算應(yīng)納稅額編制個人實發(fā)工資公式第5頁，共24頁。人事數(shù)據(jù)的單字段多條件求和計數(shù)具體的操作步驟如下：步驟1：借助SUM函數(shù)結(jié)合數(shù)組公式實現(xiàn)。在“Sheet1”工作表中的I5單元格中輸入“年齡段在3040歲之間的人數(shù)”，再在J5單元格中輸入公式：“=SUM(F2:F20=30)*(F2:F20 =30)-COUNTIF(F2:F20,40)”，即可計算出年齡段在3540歲之間的人數(shù)為“8”，如圖4-52所示。第6頁，共24頁。人事數(shù)據(jù)的單字段多條件求和計數(shù)具體的操作步驟如下：步驟3：借助SUMPRODUCT函

3、數(shù)來實現(xiàn)。在“Sheet1”工作表中選擇J7單元格，在其中輸入公式“=SUMPRODUCT(F2:F20=30)*(F2:F20=40)”，即可計算出年齡段在3540歲之間的人數(shù)為“8”，如圖4-53所示。第7頁，共24頁。人事數(shù)據(jù)的單字段多條件求和計數(shù) 在人事數(shù)據(jù)的單字段多條件求和計數(shù)過程中使用到了Excel函數(shù)庫中的SUM、COUNTIF和SUMPRODUCT 函數(shù)，其中SUMPRODUCT函數(shù)的相關(guān)說明信息如下：基本功能：將幾組給定的數(shù)組對應(yīng)的元素相乘，所得結(jié)果再進行加總。語法結(jié)構(gòu)：SUMPRODUCT (array1, array2, array3,) 參數(shù)說明：array1, arr

4、ay2, array3,為給定的數(shù)組，可以包含30個數(shù)組。第8頁，共24頁。分析員工學歷水平分析員工學歷水平編制員工學歷透視圖第9頁，共24頁。人事數(shù)據(jù)表的兩表數(shù)據(jù)核對利用“條件格式”比照核對兩表格數(shù)據(jù)利用“數(shù)據(jù)透視表”比照核對兩表格數(shù)據(jù)利用VLOOKUP函數(shù)比照核對兩表數(shù)據(jù)第10頁，共24頁。利用“數(shù)據(jù)透視表”比照核對兩表格數(shù)據(jù)具體的操作步驟如下：步驟1：創(chuàng)建一個名稱為“數(shù)據(jù)表格對照表利用數(shù)據(jù)透視表”工作簿，將工作表“Sheet1”、“Sheet2”、“Sheet3”分別重命名為“數(shù)據(jù)表一”、“數(shù)據(jù)表二”、“合并表”。步驟2：在“數(shù)據(jù)表一”工作表中輸入表格數(shù)據(jù)。在“數(shù)據(jù)表一”工作表中選擇A1

5、和B1單元格，在其中分別輸入“姓名”和“身份證號”，再在下面單元格中輸入具體的數(shù)據(jù)信息，如圖4-87所示。步驟3：在“數(shù)據(jù)表二”工作表中輸入表格數(shù)據(jù)。在“數(shù)據(jù)表二”工作表中選擇A1和B1單元格，在其中分別輸入“姓名”和“身份證號”，再在下面單元格中輸入具體數(shù)據(jù)信息，如圖4-88所示。 第11頁，共24頁。利用“數(shù)據(jù)透視表”比照核對兩表格數(shù)據(jù)具體的操作步驟如下：步驟4：復(fù)制工作表中的數(shù)據(jù)。將“數(shù)據(jù)表一”和“數(shù)據(jù)表二”中的內(nèi)部復(fù)制到“合并表”工作表中，單擊E2單元格將光標定位在該單元格中，如圖4-89所示。步驟5：啟動數(shù)據(jù)透視表。在“合并表”工作表中選擇“插入”選項卡，在“表格”工具欄中單擊【數(shù)據(jù)

6、透視表】按鈕，即可打開【創(chuàng)建數(shù)據(jù)透視表】對話框，單擊“選擇一個表或區(qū)域”右側(cè)的【折疊】按鈕返回“合并表”工作表，在其中選擇A1:B25 單元格區(qū)域，如圖4-90所示，第12頁，共24頁。利用“數(shù)據(jù)透視表”比照核對兩表格數(shù)據(jù)具體的操作步驟如下：步驟6：單擊【展開】按鈕，即可打開【創(chuàng)建數(shù)據(jù)透視表】對話框，在“表/區(qū)域”文本框中看到選擇的單元格區(qū)域，在其中選擇“選擇放置數(shù)據(jù)透視表的位置”設(shè)置區(qū)域中的“現(xiàn)有工作表”單選項，如圖4-91所示。步驟7：單擊【確定】按鈕，即可在“合并表”工作表中創(chuàng)建一個數(shù)據(jù)透視表框架。選擇“數(shù)據(jù)透視表字段列表”“姓名”選項，按住鼠標左鍵不放拖至工作表中顯示“在以下區(qū)域間拖動

7、字段”區(qū)域的“行標簽”之中再松開鼠標，如圖4-92所示。步驟8：在“合并表”工作表中選擇“數(shù)據(jù)透視表字段列表”“身份證號碼”選項，將此選項也拖至工作表中顯示“在以下區(qū)域間拖動字段”區(qū)域的“行標簽”之中，即可自動將兩個表格中不一致的身份證號碼以如圖4-93所示形式顯示出來。第13頁，共24頁。員工人事信息數(shù)據(jù)查詢表VLOOKUP函數(shù)查詢?nèi)藛T信息美化和打印表格（員工信息卡片）第14頁，共24頁。統(tǒng)計不同年齡段員工信息應(yīng)用COUNTIF函數(shù)統(tǒng)計分段信息使用FREQUENCY數(shù)組公式法統(tǒng)計分段信息第15頁，共24頁。使用FREQUENCY數(shù)組統(tǒng)計分段信息具體的操作步驟如下：步驟1：輸入表格標題。在“S

8、heet1”工作表中選擇H13:I19單元格區(qū)域，在其中輸入相應(yīng)的表格標題信息，如圖4-131所示。步驟2：編制FREQUENCY數(shù)組公式。在“Sheet1”工作表中選擇I15單元格，向下拖曳至I18單元格，選中I15:I18單元格區(qū)域。在編輯欄中輸入公式：“=FREQUENCY(F3:F27, H15:H18)”，按下Ctrl+Shift+Enter組合鍵確認，此時在I15:I18單元格區(qū)域的單元格中將分別顯示輸出結(jié)果，如圖4-132所示。步驟3：編制合計分段信息公式。在“Sheet1”工作表中選擇I19單元格，在其中輸入公式“=SUM(I15:I18)”，即可在I19單元格中顯示輸出的結(jié)果

9、，如圖4-133所示。第16頁，共24頁。人力資源月報動態(tài)圖表創(chuàng)建數(shù)據(jù)源工作表數(shù)據(jù)分析匯總建立窗體控件對數(shù)據(jù)自動降序排列定義動態(tài)數(shù)據(jù)區(qū)域名稱繪制柏拉圖美化圖表區(qū)第17頁，共24頁。繪制柏拉圖下面介紹繪制柏拉圖圖表的具體操作步驟：步驟1：繪制柱形圖。在“數(shù)據(jù)分析”工作表中選擇M24:O28單元格區(qū)域，選擇“插入”選項卡，在“圖表”工具欄中單擊【柱形圖】按鈕，從圖表類型中選擇“簇狀柱形圖”選項，即可在工作表中添加一個圖表，如圖4-179所示。步驟2：添加數(shù)據(jù)標志。在圖表中選擇繪圖區(qū)并右擊，從彈出菜單中選擇“添加數(shù)據(jù)標簽”選項，即可在柱形圖頂端添加數(shù)據(jù)標簽，如圖4-180所示。第18頁，共24頁。繪

10、制柏拉圖下面介紹繪制柏拉圖圖表的具體操作步驟：步驟3：更改圖表類型。為區(qū)別“部門分析”和“累計比率”，可將“累計比率”圖表類型調(diào)整為折線圖，在圖表中選擇“累計比率”柱形圖并右擊，從彈出菜單中選擇“更改圖表類型”菜單項，即可打開【更改圖表類型】對話框，在其中選擇“折線圖”選項并在右側(cè)圖表類型中選擇“帶數(shù)據(jù)標記的折線圖”選項，如圖4-181所示。單擊【確定】按鈕，即可將“累計比率”圖表類型更改為“折線圖”，如圖4-182所示。第19頁，共24頁。繪制柏拉圖下面介紹繪制柏拉圖圖表的具體操作步驟：步驟4：調(diào)整數(shù)據(jù)系列格式。在圖表中選擇“累計比率”的柱形圖并右擊，從彈出菜單中選擇“設(shè)置數(shù)據(jù)系列格式”菜單

11、項，即可打開【設(shè)置數(shù)據(jù)系列格式】對話框，選擇“系列選項”選項卡，在右側(cè)設(shè)置區(qū)域中勾選“次坐標軸”，如圖4-183所示。單擊【關(guān)閉】按鈕，即可在工作表中看到所調(diào)整后的結(jié)果顯示，如圖4-184所示。第20頁，共24頁。繪制柏拉圖下面介紹繪制柏拉圖圖表的具體操作步驟：步驟5：調(diào)整數(shù)據(jù)標志。在圖表中選擇“部門分析”柱形圖并右擊，從彈出菜單中選擇“設(shè)置數(shù)據(jù)標簽格式”菜單項，即可打開【設(shè)置數(shù)據(jù)標簽格式】對話框，選擇“數(shù)字”選項卡，在“類別”中選擇“自定義”，在“類型”中選擇“#人”如圖4-185所示。單擊【關(guān)閉】按鈕，即可看到所調(diào)整后的結(jié)果顯示，如圖4-186所示。第21頁，共24頁。繪制柏拉圖下面介紹繪

12、制柏拉圖圖表的具體操作步驟：步驟6：調(diào)整數(shù)據(jù)標志位置。在圖表中選擇“累計比率”的柱形圖，從右鍵菜單中選擇“設(shè)置數(shù)據(jù)標簽格式”菜單項，即可打開【設(shè)置數(shù)據(jù)標簽格式】對話框，選擇“標簽選項”選項卡，在“標簽位置”中選擇“靠上”單選項，如圖4-187所示。單擊【關(guān)閉】按鈕，即可在工作表中看到調(diào)整后的結(jié)果顯示，如圖4-188所示。第22頁，共24頁。專家課堂（常見問題與解答）點撥1：在出現(xiàn)電源故障或其他問題后重新啟動 Microsoft Office 程序時，此程序?qū)⒆詣哟蜷_任何恢復(fù)的文件，如果由于某種原因該恢復(fù)文件無法打開，該怎么辦？解答：這時就需要手動打開該恢復(fù)文件，要想手動恢復(fù)文件，必須已打開自動

13、恢復(fù)或自動保存功能，并確保將保存時間間隔設(shè)置為盡可能頻繁保存。手動打開自動保存文件的方法為：選擇“文件”主選項卡，在【文件】窗口中單擊【打開】按鈕，在【打開】對話框中將“C:UsersyourusernameAppDataRoamingMicrosoftprogramname”和文件掩碼鍵入到窗口頂端的地址欄中，以顯示自動保存的文件列表（“yourusername”為用戶名，“programname”為所使用程序的名稱），選中要恢復(fù)的文件的名稱并單擊【打開】按鈕即可。點撥2：在制作表格過程中，有時會在表格中輸入超過千位的數(shù)值，為了能更好地顯示數(shù)據(jù)，如何在數(shù)據(jù)中添加千位分隔符呢？解答：在數(shù)據(jù)中添