Excel-人力資源與行政管理60課件

1、Excel第一章在人力資源與行政管理中的應用CONTENTS01收發(fā)文件登記表02辦公用品采購記錄表03辦公用品領用登記表04辦公室值班記錄表目錄05文件、資料借閱管理表06車輛使用與管理表新建工作簿的方法01在Excel工作窗口中創(chuàng)建在系統(tǒng)中創(chuàng)建工作簿文件工作表的移動和復制頁面設置與打印預覽02在頁眉中添加公司logo 紙張大小和頁邊距單擊快速訪問工具欄的保存按鈕 。三種方法1LGO1.1.2 保存工作簿2單擊功能區(qū)的【文件】【保存】按鈕或【另存為】按鈕。按組合鍵，或是按組合鍵。3對于新建工作簿的首次保存，“保存”和“另存為”命令的作用完全相同。對于之前已經(jīng)保存過的現(xiàn)有工作簿，再次執(zhí)行保存操

2、作時，【保存】命令直接將編輯修改后的內(nèi)容保存到當前工作簿中，工作簿的文件名和保存路徑不會有任何變化?！玖泶鏋椤棵顒t會打開【另存為】對話框，允許用戶對文件名和保存路徑重新進行設置，得到當前工作簿的副本。LGO“保存”和“另存為”的區(qū)別頁眉設置對話框LGO在頁眉中添加公司logo 在頁眉中插入圖片自定義快速訪問工具欄LGO1.1.5 打印預覽打印預覽在部分公司，辦公用品的采購、發(fā)放、管理工作要求由辦公室統(tǒng)一負責，采購部門與辦公室分別配備人員對辦公用品到貨明細進行記錄。LGO1.2辦公用品采購記錄表企業(yè)為控制辦公消耗成本，規(guī)范辦公用品的發(fā)放、領用，會要求對辦公用品的領用進行登記管理。LGO1.3辦

3、公用品領用登記表移動或復制工作表LGO工作表的復制和移動在選項卡中選擇移動或復制工作表命令大多數(shù)公司都會執(zhí)行辦公室人員值班制度，用以維護日常工作秩序，及時反饋信息和處置突發(fā)性事件。LGO1.4辦公室值班記錄表為了保證重要文件資料的完整性及保密性，很多公司會對閱讀和使用涉密文件、資料進行登記管理。LGO1.5文件、資料借閱管理表設置單元格邊框完成后的效果車輛管理的統(tǒng)一化和制度化，是真實反應公司車輛實際情況的基礎，以下介紹車輛使用與管理表的制作。LGO1.6車輛使用與管理表Excel允許用戶根據(jù)自己的需要和使用習慣，對選項卡和命令組進行顯示、隱藏以及次序的調(diào)整。LGO可定制的自定義選項卡顯示選項卡

4、添加自定義選項卡和命令快速訪問工具欄中的命令按鈕不會因為功能選項卡的切換而隱藏，因此使用會更加方便。LGO可定制的快速訪問工具欄自定義快速訪問工具欄在快速訪問工具欄上添加命令Excel第二章在人力資源與行政管理中的應用CONTENTS01數(shù)據(jù)源表格和報表表格02結構合理的員工信息表03制作崗位變動表04人事信息數(shù)據(jù)查詢表目錄1CONTENTS05制作員工生日信息表06勞動合同到期前提醒07對重要的文件進行保護08自動跳轉的工作表目錄目錄2合并單元格手工添加匯總行1 制作基礎數(shù)據(jù)表注意事項使用空格對齊日期格式不規(guī)范第一行寫入文件標題防止工號重復輸入輸入身份證號碼2 結構合理的員工信息表身份證號中

5、提取性別提取出生日期計算員工年齡和轉正時間美化員工信息表防止工號的重復輸入設置數(shù)據(jù)有效性員工工號必須具有唯一性，在員工信息表中可以根據(jù)工號區(qū)分重名員工。因此在輸入數(shù)據(jù)時要限制重復數(shù)據(jù)的錄入。自定義錯誤提示公式的概念和組成要素EXCEL公式是指以等號“=”為引導，通過運算符、函數(shù)、參數(shù)等按照一定的順序組合進行數(shù)據(jù)運算處理的等式。（1）、運算符：是指一些符號，如加（+）、減（-）、乘（*）、除（/）等。（2）、單元格引用：可以是當前工作表中的單元格，也可以是當前工作簿其他工作表中的單元格或是其他工作簿中的單元格。（3）、值或字符串：比如數(shù)字8或字符“A”。（4）、工作表函數(shù)和參數(shù)：例如用于求和的S

6、UM函數(shù)以及它的參數(shù)。（5）、括號：控制著公式中各表達式的計算順序。相對引用、絕對引用和混合引用在公式中，如果A1單元格公式為“=B1”，那么A1就是B1的引用單元格，B1就是A1的從屬單元格。從屬單元格與引用單元格之間的位置關系稱為單元格引用的相對性，可分為3種不同的引用方式，即相對引用、絕對引用和混合引用，不同引用方式用美元符號“$”進行區(qū)別。當復制公式到其他單元格時，Excel保持從屬單元格與引用單元格的相對位置不變，稱為相對引用。當復制公式到其他單元格時，Excel保持公式所引用的單元格絕對位置不變，稱為絕對引用。當復制公式到其他單元格時，Excel僅保持所引用單元格的行或列方向之一的

7、絕對位置不變，而另一個方向位置發(fā)生變化，這種引用方式稱為混合引用。在編輯欄內(nèi)選中公式中的單元格地址部分，然后依次按鍵，可以在不同引用方式之間進行切換。單元格的引用類型及特點引用類型A1樣式特點絕對引用=$A$1公式向右向下復制時，都不會改變引用關系。行絕對引用、列相對引用=A$1公式向下復制時，不改變引用關系。公式向右復制時，引用的列標發(fā)生變化。行相對引用、列絕對引用=$A1公式向右復制時，不改變引用關系。公式向下復制時，引用的行號發(fā)生變化。相對引用=A1公式向右向下復制均會改變引用關系。COUNTIF函數(shù)統(tǒng)計不同性別人數(shù)用于在指定區(qū)域中，統(tǒng)計符合某個條件的單元格的數(shù)量。B$2:B$10是要統(tǒng)

8、計數(shù)量的單元格范圍，D3是指定要統(tǒng)計的條件。利用數(shù)據(jù)有效性制作下拉菜單設置有效性序列來源利用數(shù)據(jù)有效性的“序列”條件，可以限制在單元格區(qū)域中必須輸入某一特定序列中的內(nèi)容。假如需要輸入性別信息，可以通過設置數(shù)據(jù)有效性，在下拉列表中選擇輸入內(nèi)容。從下拉列表中選擇輸入用自定義格式快速輸入員工工號設置自定義單元格格式“自定義”格式類型允許用戶創(chuàng)建和使用符合一定規(guī)則的數(shù)字格式，應用于數(shù)值或文本數(shù)據(jù)時，可以改變數(shù)據(jù)的顯示方式。完整的自定義格式代碼分為4個區(qū)段，并且以半角分號“;”間隔，每個區(qū)段中的代碼可以對相應類型的數(shù)值產(chǎn)生作用：大于0;小于0;等于0;文本將自定義格式轉換為實際值在剪貼板中粘貼內(nèi)容Off

9、ice剪貼板是用來臨時存放交換信息的臨時存儲區(qū)域，可以看作是信息的中轉站，能夠在不同單元格或是不同工作表之間做數(shù)據(jù)的移動或復制。利用剪貼板功能，可以將自定義格式轉換為單元格中的實際值。輸入身份證號碼先設置單元格數(shù)字格式為文本，然后輸入身份證號碼身份證號碼是一類比較特殊的數(shù)據(jù)，如果在單元格中直接輸入身份證號碼時，Excel會默認作為數(shù)值型數(shù)據(jù)處理，由于超過Excel的最大數(shù)據(jù)處理精度，15位之后的部分將全部變?yōu)?。先輸入一個半角撇號 ，然后輸入身份證號碼認識Excel運算符Excel包含4種類型的運算符：（1）、算術運算符：主要包括加、減、乘、除、百分比以及乘冪等各種常規(guī)的算術運算。（2）、比較

10、運算符：用于比較數(shù)據(jù)的大小，包括對文本或數(shù)值的比較。（3）、文本運算符：主要用于將字符或字符串進行連接與合并。（4）、引用運算符：這是Excel特有的運算符，主要用于產(chǎn)生單元格引用。認識Excel運算符當公式中使用多個運算符時，Excel將根據(jù)各個運算符的優(yōu)先級順序進行運算，對于同一級次的運算符，則按從左到右的順序運算。順序符號說明1: （空格） ,引用運算符：冒號、單個空格和逗號2-算術運算符：負號（取得與原值正負號相反的值）3%算術運算符：百分比4算術運算符：乘冪5*和/算術運算符：乘和除（注意區(qū)別數(shù)學計算中的、）6+和-算術運算符：加和減7&文本運算符：連接文本8=,=,比較運算符：比較

11、兩個值（注意區(qū)別數(shù)學計算中的 、 、）認識IF函數(shù)IF函數(shù)用于執(zhí)行真假值的判斷，根據(jù)邏輯計算的真假值返回不同的結果。如果指定條件的計算結果為TRUE，IF函數(shù)將返回某個值；如果該條件的計算結果為FALSE，則返回另一個值。第一個參數(shù)是結果可能為TRUE或FALSE的任意值或表達式。第二個參數(shù)是判斷結果為TRUE時所要返回的結果。第三參數(shù)是判斷結果為FALSE時所要返回的結果。身份證有關的計算計算性別：=IF(MOD(MID(C2,17,1),2),男,女)出生日期：=TEXT(MID(C2,7,8),0-00-00)*1DATEDIF函數(shù)DATEDIF函數(shù)用于計算兩個日期之間的天數(shù)、月數(shù)或年數(shù)

12、。該函數(shù)是一個隱藏的的日期函數(shù)，在Excel的函數(shù)列表中沒有顯示此函數(shù)，幫助文件中也沒有相關說明。第一參數(shù)表示時間段內(nèi)的起始日期，第二參數(shù)代表時間段內(nèi)的結束日期，第三參數(shù)為所需信息的返回類型，該參數(shù)不區(qū)分大小寫。unit參數(shù)函數(shù)返回結果Y時間段中的整年數(shù)M時間段中的整月數(shù)D時間段中的天數(shù)MD日期中天數(shù)的差。忽略日期中的月和年YM日期中月數(shù)的差。忽略日期中的日和年YD日期中天數(shù)的差。忽略日期中的年VLOOKUP函數(shù)使用通配符的查詢使用動態(tài)的第三參數(shù)IFERROR函數(shù)3 制作崗位變動表VLOOKUP函數(shù)函數(shù)作用：在數(shù)據(jù)區(qū)域首列中查找一個內(nèi)容，并返回與之對應的其他列中的內(nèi)容。函數(shù)用法：VLOOKUP

13、(要查找的內(nèi)容,要查找的區(qū)域,返回該區(qū)域第幾列的內(nèi)容,精確匹配還是近似匹配)VLOOKUP函數(shù)的特點：1）、VLOOKUP函數(shù)查找值支持使用通配符（?號和*號）進行查詢。2）、第4個參數(shù)決定了查找方式。如果為0或FASLE，用精確匹配方式進行查找，而且支持無序查找；如果為1或TRUE，則使用模糊匹配方式進行查找，并且要求第2個參數(shù)的首列或首行按升序排列。3）、第3個參數(shù)中的列號，不能理解為工作表中實際的列號，而是指定返回值在查找范圍中的第幾列。4）、如果查找值與數(shù)據(jù)區(qū)域關鍵字的數(shù)據(jù)類型不一致，也會返回錯誤值#N/A。5）、如果有多條滿足條件的記錄時，只能返回第一個滿足條件的記錄。6）、查詢區(qū)域

14、中的首列必須要包含查詢值，否則無法正常查詢。IFERROR函數(shù)函數(shù)作用：如果公式的計算結果錯誤，則返回指定的值，否則返回公式的結果。函數(shù)用法：IFERROR(用于檢查錯誤值的公式, 公式計算結果為錯誤值時要返回的值)定義名稱名稱是被特殊命名的公式，也是以等號“=”開頭，組成元素可以是常量數(shù)據(jù)、常量數(shù)組、單元格引用、或是函數(shù)公式等，已定義的名稱可以在其他名稱或公式中調(diào)用。創(chuàng)建名稱可以使公式變得更加簡潔，另外創(chuàng)建名稱可以生成動態(tài)的數(shù)據(jù)源，是動態(tài)圖表制作的必要的手段之一。使用名稱框根據(jù)所選內(nèi)容創(chuàng)建使用名稱管理器使用【定義名稱】命令定義名稱OFFSET函數(shù)COUNTA函數(shù)MATCH函數(shù)4 制作員工人事

15、信息數(shù)據(jù)查詢表OFFSET函數(shù)函數(shù)作用：該函數(shù)以指定的引用為參照，通過給定偏移量得到新的引用，返回的引用可以為一個單元格或單元格區(qū)域，也可以指定返回的行數(shù)或列數(shù)。函數(shù)用法：OFFSET(基點,需要偏移的行數(shù),需要偏移的列數(shù),新引用的行數(shù),新引用的列數(shù))COUNTA函數(shù)與MATCH函數(shù)COUNTA函數(shù)作用：計算指定范圍中不為空的單元格的個數(shù)。MATCH函數(shù)作用：在單元格區(qū)域中搜索指定項，然后返回該項在單元格區(qū)域中的相對位置。MATCH函數(shù)用法：=MATCH(要查找的內(nèi)容,在哪個區(qū)域查找,查找的方式)數(shù)據(jù)篩選文本篩選數(shù)值篩選生成連續(xù)序號SUBTOTAL函數(shù)用法數(shù)據(jù)排序單條件排序多條件排序自定義序列

16、使用自定義序列排序5 制作員工生日信息表數(shù)據(jù)篩選篩選數(shù)據(jù)列表的作用是只顯示符合用戶指定條件的行，隱藏不符合條件的其他行。TODAY函數(shù)生成系統(tǒng)當前的日期AND函數(shù)和OR函數(shù)兩種常用的邏輯關系， “與”和“或”數(shù)據(jù)排序單條件排序多條件排序自定義序列使用自定義序列排序6 勞動合同到期前提醒數(shù)據(jù)排序Excel 2010能夠支持64個排序條件，可以根據(jù)需要按行或列，進行升序、降序或是自定義排序操作，而且可以根據(jù)單元格內(nèi)的背景色以及字體顏色等條件進行排序。數(shù)值排序多條件排序常用函數(shù)TODAY函數(shù)用于生成系統(tǒng)當前的日期。AND函數(shù)和OR函數(shù)分別對應兩種常用的邏輯關系，即“與”和“或”。對于AND函數(shù)，所有

17、參數(shù)的邏輯值為真時返回 TRUE，只要一個參數(shù)的邏輯值為假即返回FALSE。對于OR函數(shù)，當所有參數(shù)的邏輯值都為假時，才返回FALSE，只要一個參數(shù)的邏輯值為真，即返回TRUE。條件格式使用Excel的條件格式功能，能夠快速對特定條件的單元格進行突出標識，使數(shù)據(jù)更加直觀易讀。用戶可以預置一種單元格格式或是單元格內(nèi)的圖形效果，在符合指定的條件時，自動應用于目標單元格。可預置的單元格格式包括單元格邊框、底紋、字體顏色等，單元格圖形效果包括數(shù)據(jù)條、色階和圖標集三種類型。按F12鍵，在彈出的【另存為】對話框底部，單擊【工具】【常規(guī)選項】，彈出【常規(guī)選項】對話框。用戶可以為工作簿設置更多的保存選項。兩種

18、方法1LGO7 對重要文件進行保護2單擊【文件】選項卡，再依次單擊【保護工作簿】【用密碼進行加密】，在彈出的【加密文檔】對話框中輸入密碼?！竟健窟x項卡，單擊【定義名稱】，彈出【新建名稱】對話框，在“名稱”文本框中輸入“目錄”，在“引用位置”文本框輸入以下公式：=INDEX(GET.WORKBOOK(1),ROW(A1)&T(NOW()兩個步驟1LGO8 自動跳轉的工作表目錄2在B1單元格輸入以下公式向下復制，到出現(xiàn)空白單元格為止。=IFERROR(HYPERLINK(目錄&!A1,MID(目錄,FIND(,目錄)+1,99),)INDEX函數(shù)根據(jù)指定的行號和列號，返回指定區(qū)域中的值。HYPE

19、RLINK函數(shù)能夠生成鏈接的特殊函數(shù)。Excel第三章在人力資源與行政管理中的應用CONTENTS01員工結構統(tǒng)計分析表02員工離職原因分析表03分析員工離職率04全年在職職工人數(shù)變化目錄05統(tǒng)計分析人事信息定位可見單元格3.1 制作員工結構統(tǒng)計分析表建立工作表副本不同學歷員工的人數(shù)和占比3.1.2 統(tǒng)計員工學歷構成設置值顯示方式數(shù)據(jù)透視表結構值區(qū)域行區(qū)域列區(qū)域篩選區(qū)域數(shù)據(jù)透視表常用術語術語含義數(shù)據(jù)源用于創(chuàng)建數(shù)據(jù)透視表的數(shù)據(jù)列表列字段等價于數(shù)據(jù)列表中的列行字段在數(shù)據(jù)透視表中具有行方向的字段頁字段數(shù)據(jù)透視表中進行分頁的字段字段標題用于描述字段內(nèi)容項組成字段的成員組一組項目的組合分類匯總數(shù)據(jù)透視表

20、中對一行或一列單元格的分類匯總刷新重新計算數(shù)據(jù)透視表，反映目前數(shù)據(jù)源的狀態(tài)在數(shù)據(jù)透視表的值區(qū)域單擊鼠標右鍵，在快捷菜單中單擊【值匯總依據(jù)】命令，可以根據(jù)需要選擇求和、計數(shù)、平均值、最大值、最小值、乘積等多種匯總方式。數(shù)據(jù)透視表值顯示方式選項數(shù)值區(qū)域字段顯示為無計算數(shù)據(jù)透視表中的原始數(shù)據(jù)總計的百分比每個數(shù)值項占所有匯總的百分比值列匯總的百分比每個數(shù)值項占列匯總的百分比值行匯總的百分比每個數(shù)值項占行匯總的百分比值百分比以選定的參照項為100%，其余項基于該項的百分比父行匯總的百分比在多個行字段的情況下，以父行匯總為100%，計算每個數(shù)值項的百分比父列匯總的百分比在多個列字段的情況下，以父列匯總為1

21、00%，計算每個數(shù)值項的百分比父級匯總的百分比某一項數(shù)據(jù)占父級總和的百分比差異以選中的某個基本項為參照，顯示其余項與該項的差異值差異百分比以選中的某個基本項為參照，顯示其余項與該項的差異值百分比按某一字段匯總根據(jù)選定的某一字段進行匯總按某一字段匯總的百分比將根據(jù)字段匯總的結果顯示為百分比升序排列對某一字段進行排名，顯示按升序排列的序號降序排列對某一字段進行排名，顯示按降序排列的序號指數(shù)計算數(shù)據(jù)的相對重要性。使用公式：單元格的值總體匯總之和/（行總計列總計）常用布局1調(diào)整數(shù)據(jù)透視表布局常用布局22季度加班費數(shù)據(jù)透視表篩選器兩個季度加班費不顯示分類匯總數(shù)據(jù)透視表分類匯總顯示方式在右鍵菜單中操作員工

22、年齡結構3.1.3 統(tǒng)計員工年齡結構為年齡字段創(chuàng)建組每一年入職人數(shù)3.1.4 統(tǒng)計每年入職人數(shù)為日期字段創(chuàng)建組3.1.5 分析員工性別占比認識圖表元素標題圖例項縱坐標軸橫坐標軸數(shù)據(jù)系列網(wǎng)格線數(shù)據(jù)標簽趨勢線圖表區(qū)繪圖區(qū)插入切片器3.1.6 各部門性別構成動態(tài)圖表用切片器查看不同部門信息數(shù)據(jù)透視圖中的圖表元素圖例字段軸字段報表篩選字段保存模板使用模板創(chuàng)建統(tǒng)一樣式的圖表應用模板凍結窗格3.2 員工離職原因分析表不同月份、不同原因的離職人數(shù)員工離職率：離職率是企業(yè)用以衡量內(nèi)部人力資源流動狀況的一個重要指標，通過對離職率的考察，可以了解企業(yè)對員工的吸引和滿意程度。一定范圍內(nèi)的員工流動，能夠使企業(yè)利用優(yōu)勝

23、劣汰的人才競爭機制，保持企業(yè)的活力和創(chuàng)新能力。但是離職率過高，則會影響到企業(yè)的穩(wěn)定發(fā)展，使人力資源成本增加、組織效率下降。3.3 用組合圖表分析員工離職率各年度員工離職率Excel 2010內(nèi)置了11種圖表類型，包括：柱形圖、折線圖、餅圖、條形圖、面積圖、XY散點圖、股價圖、曲面圖、圓環(huán)圖、氣泡圖和雷達圖，每種圖表類型還包含多種子圖表類型。Excel中的圖表類型用圖表展示人數(shù)變化的優(yōu)勢：將全年在職職工人數(shù)繪制成柱形圖，并且按不同季度使用顏色進行區(qū)分，能夠使圖表看起來更加直觀。3.4 全年在職職工人數(shù)變化全年在職人數(shù)變化COUTNIFS函數(shù)作用：用于對某一區(qū)域內(nèi)滿足多重條件的單元格進行計數(shù)。CO

24、UTNIFS函數(shù)用法：COUNTIFS(條件區(qū)域1,條件1,條件區(qū)域2,條件2,條件區(qū)域n,條件n)3.5 統(tǒng)計分析人事信息統(tǒng)計分析人士信息Excel第四章在人力資源與行政管理中的應用CONTENTS01人員增補申請表02招聘費用預算表03招聘面試通知單04用甘特圖展示招聘進度目錄1CONTENTS05制作帶照片的員工胸卡06重命名員工照片07招聘錄用統(tǒng)計分析表目錄24.1 制作人員增補申請表增補申請表人員增補申請表能方便人力資源管理部門統(tǒng)計企業(yè)的招聘人數(shù)，幫助人事部門招聘到更加適合崗位需求的人員，是人事管理的必備資料之一。4.2 制作招聘費用預算表招聘費用預算表常規(guī)的招聘費用包括廣告宣傳費、

25、海報制作費用、場地租用費用、表格資料的打印復印費用和招聘人員的餐飲費和交通費用等。4.3 制作招聘面試通知單Word中的面試通知單經(jīng)過對應聘人員的初步篩選，人力資源部篩除明顯不符合崗位任職資格的人員，接下來的工作是通知基本符合錄用要求的人員參加面試。如果招聘人員較少時可以電話直接通知，但是如果招聘崗位較多，使用電子郵件通知的方式會更方便快捷。插入合并域4.3.3用郵件合并發(fā)送面試通知單修改域代碼4.4 用甘特圖展示項目進度甘特圖效果甘特圖又稱為項目進度圖，以圖示的方式展示活動列表和時間刻度，形象地表示出項目的活動順序與持續(xù)時間。甘特圖的橫軸表示時間，縱軸表示項目，線條表示在整個期間的計劃和實際

26、活動完成情況，能夠直觀地表明任務計劃在什么時候進行，及實際進展與計劃要求的對比。由此可發(fā)現(xiàn)某項任務還剩下哪些工作要做，并可評估工作進度。4.5制作帶照片的員工胸卡利用Excel人事表格中的數(shù)據(jù)和Word的郵件合并功能相結合的方法批量制作員工胸卡，能夠節(jié)約時間和成本。郵件合并添加照片輸入照片域代碼 注意照片域代碼的插入位置創(chuàng)建人事數(shù)據(jù)表在Excel中準備好數(shù)據(jù)創(chuàng)建“員工胸卡”Word文檔在Word中制作胸卡框架復制到記事本修改文件擴展名.由txt” 修改為“.bat”使用公式=ren &A2&.jpg &B2&.jpg“4.6重命名員工照片雙擊運行文件擴展名利用Excel人事表格中的數(shù)據(jù)和Wor

27、d的郵件合并功能相結合的方法批量制作員工胸卡，能夠節(jié)約時間和成本。郵件合并添加照片輸入照片域代碼 注意照片域代碼的插入位置創(chuàng)建人事數(shù)據(jù)表在Excel中準備好數(shù)據(jù)創(chuàng)建“員工胸卡”Word文檔在Word中制作胸卡框架各月份招聘完成情況折線圖不同渠道招聘占比動態(tài)圖表招聘情況匯總表年度招聘完成情況匯總表4.7招聘錄用統(tǒng)計分析表各招聘渠道分析圖表快速求和4.7.1制作招聘情況匯總表對比各月份完成率變化4.7.2年度招聘完成情況匯總表設置迷你圖顏色添加高低點連線4.7.3各月份招聘完成情況折線圖設置迷你圖顏色定義名稱4.7.4制作不同渠道招聘占比的動態(tài)圖表設置迷你圖顏色4.7.5各招聘渠道分析圖表圖表最終

28、效果餅圖主要用于顯示一個數(shù)據(jù)系列中每一項的大小與各項總和的比例，以每個數(shù)據(jù)點的扇區(qū)面積顯示數(shù)據(jù)大小。使用餅圖時需要注意：要繪制的數(shù)值沒有負值和零值。類別一般不要超過七個。Excel第五章在人力資源與行政管理中的應用CONTENTS01培訓計劃完成統(tǒng)計表 02員工培訓考核統(tǒng)計表03培訓成績統(tǒng)計分析表目錄5.1 培訓計劃完成統(tǒng)計表統(tǒng)計表效果制定出培訓計劃后，還需要對培訓計劃的落實情況進行跟蹤。如果培訓計劃和實際培訓的差異較大，則需要進一步分析產(chǎn)生的原因，確定具體是培訓計劃本身的制定存在瑕疵還是實際培訓執(zhí)行階段出現(xiàn)問題，因此需要制作一份培訓計劃完成統(tǒng)計表對培訓計劃完成情況進行匯總。Excel常用快捷

29、鍵執(zhí)行操作快捷鍵組合查看幫助文件F1重復最后一次操作F4顯示“定位”對話框F5重新計算F9另存為F12顯示“單元格格式”對話框Ctrl+1復制選定區(qū)域Ctrl+C剪切選定區(qū)域Ctrl+X粘貼選定區(qū)域Ctrl+V撤消最后一次操作Ctrl+Z選定活動單元格所在的當前區(qū)域Ctrl+A保存當前操作Ctrl+S打印工作表Ctrl+P打開新工作表Ctrl+O （字母O）新建工作簿Ctrl+N打開查找對話框Ctrl+F打開替換對話框Ctrl+H清除選定區(qū)域的內(nèi)容Delete刪除選定區(qū)域Ctrl+- （數(shù)字小鍵盤的減號）插入行或列Ctrl+ （數(shù)字小鍵盤的加號）選定當前區(qū)域Ctrl+* （數(shù)字小鍵盤的乘號）5

30、.2 制作員工培訓考核統(tǒng)計表培訓考核統(tǒng)計表每次（期）培訓結束后，培訓主管部門組織培訓員工進行考試，及時評定、匯總考核的成績。本節(jié)學習員工培訓考核統(tǒng)計表的制作，以便對員工的學習情況等進行評價記錄。簡單的高級篩選認識LOOKUP函數(shù)用RANK函數(shù)對成績排名IF函數(shù)多條件判斷5.3 成績統(tǒng)計分析表IF函數(shù)多條件判斷在Excel中對指定數(shù)值進行多個條件的判斷時，需要根據(jù)每一個條件依次進行判斷。5.3.1判斷培訓成績等次認識LOOKUP函數(shù)典型用法為：=LOOKUP(1,0/(條件),目標區(qū)域)如果查詢區(qū)域中有多個符合條件的記錄，LOOKUP函數(shù)僅返回最后一條記錄。RANK函數(shù)返回一列數(shù)字的數(shù)字排位。第

31、一參數(shù)是需要找到其排位的數(shù)字。第二參數(shù)是指定數(shù)字列表的范圍。第三參數(shù)用于指定數(shù)字排位的方式，如果為0（零）或省略，按照降序排列。如果不為零，則按照升序排列。5.3.2培訓成績排名中國式排名無論有幾個并列名次，后續(xù)的排名緊跟前面的名次順延生成，并列排名不占用名次。5.3.2提取指定條件的員工名單高級篩選高級篩選功能是自動篩選的升級，不僅包含了自動篩選的所有功能，而且能夠設置更多更復雜的篩選條件。位于同一行的各個條件表示相互之間是“與”的關系。位于不同行的各個條件表示相互之間是“或”的關系。Excel第六章在人力資源與行政管理中的應用CONTENTS01制作考勤表02出勤情況分析03不同部門工資增

32、幅變化目錄05計算銷售提成04制作、匯總工資表6.1 整理考勤機數(shù)據(jù)考勤機數(shù)據(jù)雖然市面上的考勤機種類非常多，功能也越來越強大，但是實際應用中，仍然需要人力資源管理人員對請假、遲到、早退、未打卡等一些異常情況進行單獨處理，最終以考勤機的打卡時間為依據(jù)，輔以請休假等特殊情況的記錄生成考勤表。6.1.1統(tǒng)計出勤狀態(tài)和早退遲到時間出勤狀態(tài)公式=IF(D2-INT(D2)=-18:00),正常,遲到/早退)日期和時間數(shù)據(jù)日期和時間混合型的數(shù)據(jù)，實際上是由整數(shù)組成的日期序列值和由小數(shù)組成的時間序列值兩部分組成。統(tǒng)計遲到早退次數(shù)篩選不重復記錄6.1.2制作加班統(tǒng)計表公式的復制與填充方法1拖曳填充柄。單擊輸入

33、公式的單元格，指向該單元格右下角，當鼠標指針變?yōu)楹谏?”字型填充柄時，按住鼠標左鍵向下拖曳至目標單元格。方法2雙擊填充柄。單擊輸入公式的單元格，雙擊該單元格右下角的填充柄，公式將向下填充到當前單元格所位于的不間斷區(qū)域的最后一行。方法3 填充公式。在一個單元格中輸入公式后，然后選中需要輸入相同公式的單元格區(qū)域，按組合鍵或單擊【開始】選項卡的【填充】下拉按鈕，在擴展菜單中單擊【向下】按鈕。按指定條件求和SUMIF函數(shù)第一參數(shù)用于判斷條件的單元格區(qū)域。第二參數(shù)用于確定求和的條件。第三參數(shù)是要求和的實際單元格區(qū)域。其語法可以理解為：SUMIF(條件判斷區(qū)域,求和條件,求和區(qū)域)求和條件使用通配符按多

34、個條件求和SUMIFS函數(shù)用法：SUMIFS(求和區(qū)域,條件區(qū)域1,條件1,條件區(qū)域2,條件2,)多求和條件使用通配符除了使用考勤機打卡記錄員工的考勤之外，還有部分企業(yè)會使用“考勤表”的形式記錄員工出勤。設計合理的考勤表不僅能夠直觀顯示員工的出勤狀況，還可以減少統(tǒng)計人員的工作量。6.2 用Excel制作考勤表考勤表常用日期類函數(shù) DAY函數(shù)返回某個日期的在一個月中的天數(shù)，結果是介于 1 到 31 之間的整數(shù)。YEAR函數(shù)返回對應于某個日期的年份，結果為19009999之間的整數(shù)。MONTH函數(shù)返回日期中的月份，結果是介于1（一月）到12（十二月）之間的整數(shù)。DATE函數(shù)可以根據(jù)指定的年份數(shù)、月

35、份數(shù)和一月中的天數(shù)返回日期序列。EOMONTH函數(shù)返回指定日期之前或之后指定月份數(shù)的月末日期。WEEKDAY函數(shù)返回對應于某個日期的一周中的第幾天。一個月結束后，通常需要制作一份月度出勤匯總表，對員工的出勤情況進行匯總后張貼公示或提請領導簽字后再以此制作工資表。6.3 制作月度出勤匯總表匯總表效果6.4 部門出勤情況分析統(tǒng)計部門出勤情況時，通常會統(tǒng)計部門的出勤率或是缺勤率，部門出勤率的計算方法為部門實際出勤天數(shù)之和除以應出勤天數(shù)之和*100%。部門出勤率分析表平均出勤率分析表6.5制作職工帶薪年休假申請審批單凡連續(xù)工作1年以上的均可以享受帶薪年休假，職工在休假期間享受與正常工作期間相同的工資收

36、入。累計工作滿1年不滿10年的，年休假5天；滿10年不滿20年的，年休假10天；滿20年的年休假15天。職工年休假記錄臺帳帶薪年休假申請審批單6.6用圖表展示不同部門工資增幅變化年度工資結構分析通常包括薪資增長狀況、不同薪資結構對比、不同職位和級別的薪資數(shù)據(jù)、獎金福利狀況、長期激勵措施以及未來薪資走勢分析等。本節(jié)以工資增幅數(shù)據(jù)為例，學習使用圖表展示不同部門工資增幅變化的情況。不同部門工資增幅變化6.7使用條件格式展示工資增幅變化使用圖表展示數(shù)據(jù)的優(yōu)點是效果美觀，但是其操作步驟較多，剛剛學習Excel的用戶往往不容易掌握，可以使用條件格式實現(xiàn)類似的效果。不同部門工資增幅變化6.8制作工資表企業(yè)有

37、每月為職工代扣、代繳工資薪金所得部分個人所得稅的義務。根據(jù)有關法規(guī)：工資薪金所得以每月收入額減除費用3500元后的余額為應納稅所得額，即： 月應納稅所得額=月工資收入額-3500（基數(shù)） 月應納稅額=月應納稅所得額適用稅率-速算扣除數(shù)個人所得稅稅率表級數(shù) 含稅級距稅率(%)計算公式速算扣除數(shù)1不超過1500元的 3T=(A-3500)*3%-002超過1500元至4500元的部分 10T=(A-3500)*10%-1051053超過4500元至9000元的部分 20T=(A-3500)*20%-5555554超過9000元至35000元的部分 25T=(A-3500)*25%-10051005

38、5超過35000元至55000元的部分 30T=(A-3500)*30%-277527756超過55000元至80000元的部分 35T=(A-3500)*35%-550555057超過80000元的部分 45T=(A-3500)*45%-1350513505本表含稅級距指以每月收入額減除費用3500元后的余額或者減除附加減除費用后的余額。6.8.2 計算個人所得稅個稅計算公式=ROUND(MAX(應稅工資-3500)*3;10;20;25;30;35;45%-0;105;555;1005;2755;5505;13505,0),2)6.9分類匯總工資表在月度工資表中往往需要按照部門統(tǒng)計當月工資的小計，使用分類匯總功能可以簡化統(tǒng)計工作量，提高工作效率。不同部門工資增幅變化部門分類匯總統(tǒng)計打印不同匯總結果高亮顯示部門小計6.10 每頁帶小計的工資表如果工資表的數(shù)據(jù)量比較多，當需要打印時，在每一頁的最后加上本頁小計，在最后一頁加上總計，能夠增加表格的可讀性。類似的打印效果在一些財務軟件中已經(jīng)實現(xiàn)了自動化，用Excel自帶的功能也可以實現(xiàn)這樣的效果。設置后的打印預覽效果6.11 按部門拆分工作表如果將工資表中的內(nèi)容按不同