Excel(2007版)在人力資源管理中的高級(jí)應(yīng)用（下冊(cè)）舒雄 編著中國(guó)青年出版社圖書在版編目（CIP）數(shù)據(jù)Excel（2007 版）在人力資源管理中高級(jí)應(yīng)用/舒雄等編著．—北京：中國(guó) 青年出版社，2009.9ISBN 978-7-80218-208-0Ⅰ．用… Ⅱ．李… Ⅲ．電子表格系統(tǒng)，Excel Ⅳ．TP391.13中國(guó)版本圖書館 CIP 數(shù)據(jù)核字（2007）第 012833 號(hào)策劃編輯 劉全 封面設(shè)計(jì) 靈動(dòng)設(shè)計(jì) 責(zé)任編輯 鐘梅娟 責(zé)任校對(duì) 李珊中國(guó)青年出版社出 版 發(fā) 行社 址 北京市阜成路 8 號(hào) 郵 編 100830 (010)68768548網(wǎng) 址 經(jīng) 銷 新華書店發(fā)行部(010)68371900(010)88530478(傳真)(010)68768541(010)68767294(傳真)零售店讀者服務(wù)部北京青年文苑(010)68371105(010)62529336編輯部(010)68193075承 印 廊坊時(shí)嘉印刷有限公司版 次 2009 年 9月第 1 版 2009 年 9月第 1 次印刷 規(guī) 格 880×1230 開 本 1/32印 張 8 字 數(shù) 246 千字 書 號(hào) ISBN 978-7-80218-208-0定 價(jià) 120.00 元（全套共 5 冊(cè)）本書如有印裝質(zhì)量問(wèn)題，可與發(fā)行部聯(lián)系調(diào)換前 言Excel 集數(shù)據(jù)采集、編輯、分析于一身，具有強(qiáng)大的數(shù)據(jù)處理和圖 表分析功能，在人力資源管理工作中得到廣泛應(yīng)用。

本書根據(jù)人力資源管理工作的實(shí)際需要，由淺入深、循序漸進(jìn)地講解了人力資源管理工作必需掌握的 Excel 軟件技能，并提供了多種 實(shí)用模板通過(guò)學(xué)習(xí)本書，讀者能夠根據(jù)實(shí)際需要設(shè)計(jì)出工作表和數(shù) 據(jù)圖表，解決企業(yè)人力資源管理工作中的繁雜問(wèn)題本書共分 11 章，主要內(nèi)容如下第 1 章講解 Excel 2007 的基本操作，包括工作簿、工作表和單元 格的基本操作，數(shù)據(jù)的輸入與修改、批注的使用、工作表與工作簿的保護(hù)等內(nèi)容第 2 章～第 11 章講解了員工檔案管理系統(tǒng)、員工離職程 序表、員工學(xué)歷分析圖、員工出勤統(tǒng)計(jì)表、員工在職培訓(xùn)管理系統(tǒng)、員工加班記錄表、員工年度考核系統(tǒng)、員工業(yè)績(jī)獎(jiǎng)金評(píng)定系統(tǒng)、員工工資管理系統(tǒng)和人力資源薪資調(diào)查系統(tǒng)等制作方法在第 2 章～第 11 章中還安排了與該章內(nèi)容相似的其他常用模板，讀者可以舉一反三， 全面掌握 Excel 在人力資源管理工作中的應(yīng)用本書特點(diǎn)：圖解方式、版式新穎，實(shí)例經(jīng)典、內(nèi)容實(shí)用，語(yǔ)言簡(jiǎn) 練、通俗易懂，步驟詳細(xì)、一看即會(huì)請(qǐng)登錄網(wǎng)址 尚 IT 生活秀”文件夾中選擇本書實(shí)例文件和其他模板文件進(jìn)行下載 本書由舒雄執(zhí)筆，另外陳豫龍、郭彥芳、李鵬杰、張斯予、馬洪亮、張婷、劉方源、吳平和劉江斌也參與了部分編寫工作。

如果您對(duì)本書有何意見和建議，請(qǐng)通過(guò) yuanxun.007@ 與 我們聯(lián)系編 者2009 年 5 月i下冊(cè)目錄第 5 章 員工出勤統(tǒng)計(jì)表................................................................................. 1055.1 創(chuàng)建員工出勤統(tǒng)計(jì)表 ................................................................................. 1055.1.1 創(chuàng)建并格式化表格 ......................................................................... 1055.1.2 設(shè)置數(shù)據(jù)有效性 ............................................................................. 1065.1.3 計(jì)算請(qǐng)假天數(shù) ................................................................................. 1075.1.4 計(jì)算應(yīng)扣工資 ................................................................................. 1085.2 統(tǒng)計(jì)月度出勤 ............................................................................................. 1095.2.1 創(chuàng)建數(shù)據(jù)透視表 ............................................................................. 1095.2.2 利用數(shù)據(jù)透視表查看員工的出勤...................................................1115.2.3 修改數(shù)據(jù)透視表布局 ..................................................................... 1125.2.4 編輯數(shù)據(jù)透視表的統(tǒng)計(jì)方式.......................................................... 1135.2.5 設(shè)置數(shù)據(jù)透視表中的值顯示格式.................................................. 1145.2.6 更新數(shù)據(jù)源 ..................................................................................... 1155.2.7 套用表格樣式 ................................................................................. 1165.2.8 刪除數(shù)據(jù)透視表 ............................................................................. 1165.3 創(chuàng)建數(shù)據(jù)透視圖 ......................................................................................... 1175.4 其他應(yīng)用實(shí)例 ............................................................................................. 119第 6 章 員工在職培訓(xùn)管理系統(tǒng) ................................................................... 1206.1 創(chuàng)建在職培訓(xùn)日程表 ................................................................................. 1206.1.1 建立基本表格 ................................................................................. 1206.1.2 設(shè)置時(shí)間格式 ................................................................................. 1206.1.3 自動(dòng)填充數(shù)據(jù) ................................................................................. 1216.1.4 建立單元格之間的鏈接 ................................................................. 1226.2 拼音字段的應(yīng)用 ......................................................................................... 1236.2.1 添加拼音 ......................................................................................... 1236.2.2 顯示拼音 ......................................................................................... 1246.2.3 設(shè)置拼音字段 ................................................................................. 1256.3 創(chuàng)建在職培訓(xùn)成績(jī)表 ................................................................................. 1266.3.1 建立基本表格 ................................................................................. 1276.3.2 插入圖片 ......................................................................................... 1276.3.3 凍結(jié)窗口 ......................................................................................... 1286.4 統(tǒng)計(jì)培訓(xùn)成績(jī) ............................................................................................. 1296.4.1 計(jì)算平均成績(jī) ................................................................................. 1296.4.2 統(tǒng)計(jì)成績(jī)排名 ................................................................................. 1306.5 公式中單元格的引用 ................................................................................. 1316.5.1 相對(duì)引用 ......................................................................................... 1326.5.2 絕對(duì)引用 ......................................................................................... 1336.5.3 混合引用 ......................................................................................... 133目 錄6.5.4 引用其他工作表的單元格.............................................................. 1346.6 在職培訓(xùn)成績(jī)分析 ..................................................................................... 1356.6.1 建立基本表格 ................................................................................. 1356.6.2 應(yīng)用自動(dòng)換行功能 ......................................................................... 1356.6.3 為表格添加邊框 ............................................................................. 1366.6.4 統(tǒng)計(jì)總?cè)藬?shù) ..................................................................................... 1376.6.5 統(tǒng)計(jì)男女員工的人數(shù) ..................................................................... 1396.6.6 統(tǒng)計(jì)最高分 ..................................................................................... 1406.6.7 統(tǒng)計(jì)最低分 ..................................................................................... 1406.6.8 分別統(tǒng)計(jì)男、女員工的平均分...................................................... 1416.6.9 統(tǒng)計(jì)各分?jǐn)?shù)段的人數(shù) ..................................................................... 1426.6.10 計(jì)算各分?jǐn)?shù)段人數(shù)占總?cè)藬?shù)的比例............................................ 1466.7 顯示所有公式 ............................................................................................. 1476.8 常見公式錯(cuò)誤及糾正辦法 ......................................................................... 1486.9 其他應(yīng)用實(shí)例 ............................................................................................. 151第 7 章 員工加班記錄表................................................................................. 1527.1 創(chuàng)建員工加班記錄表 ................................................................................. 1527.2 使用公式輸入加班數(shù)據(jù) ............................................................................. 1537.2.1 日期的輸入 ..................................................................................... 1537.2.2 加班時(shí)間的計(jì)算 ............................................................................. 1547.2.3 計(jì)算加班費(fèi) ..................................................................................... 1557.3 使用加班記錄表 ......................................................................................... 1557.3.1 完善加班記錄表 ............................................................................. 1567.3.2 審核公式 ......................................................................................... 1567.3.3 錯(cuò)誤檢查 ......................................................................................... 1587.3.4 驗(yàn)證公式計(jì)算結(jié)果 ......................................................................... 1597.4 其他應(yīng)用實(shí)例 ............................................................................................. 161第 8 章 員工年度考核系統(tǒng) ............................................................................ 1628.1 創(chuàng)建季度考核表 ......................................................................................... 1628.1.1 創(chuàng)建出勤統(tǒng)計(jì)表 ............................................................................. 1628.1.2 設(shè)置小數(shù)位數(shù) ................................................................................. 1638.1.3 創(chuàng)建績(jī)效表 ..................................................................................... 163vii8.1.4 使用函數(shù) INDEX 引用數(shù)據(jù) ........................................................... 1648.1.5 計(jì)算總成績(jī) ..................................................................................... 1668.2 創(chuàng)建年度考核表 ......................................................................................... 1678.2.1 建立基本表格 ................................................................................. 1678.2.2 利用合并計(jì)算功能計(jì)算成績(jī).......................................................... 1678.2.3 計(jì)算總成績(jī) ..................................................................................... 1698.2.4 突出顯示特定成績(jī) ......................................................................... 1708.2.5 計(jì)算成績(jī)排名 ................................................................................. 1718.3 利用排序功能查看年度考核表.................................................................. 1728.3.1 簡(jiǎn)單排序 ......................................................................................... 1728.3.2 復(fù)雜排序 ......................................................................................... 1738.3.3 自定義排序 ..................................................................................... 1748.4 利用篩選功能查看年度考核表.................................................................. 1768.4.1 自動(dòng)篩選 ......................................................................................... 1768.4.2 自定義篩選 ..................................................................................... 1768.4.3 高級(jí)篩選 ......................................................................................... 1778.5 其他應(yīng)用實(shí)例 ............................................................................................. 180第 9 章 員工業(yè)績(jī)獎(jiǎng)金評(píng)定系統(tǒng) ................................................................... 1819.1 創(chuàng)建獎(jiǎng)金規(guī)范表 ......................................................................................... 1819.2 創(chuàng)建獎(jiǎng)金評(píng)定表 ......................................................................................... 1819.2.1 建立基本表格 ................................................................................. 1819.2.2 計(jì)算獎(jiǎng)金比例 ................................................................................. 1839.2.3 計(jì)算基本業(yè)績(jī)獎(jiǎng)金 ......................................................................... 1849.2.4 計(jì)算本月累計(jì)銷售額 ..................................................................... 1859.2.5 計(jì)算累計(jì)銷售獎(jiǎng)金 ......................................................................... 1859.2.6 計(jì)算員工總獎(jiǎng)金 ............................................................................. 1869.3 評(píng)估員工業(yè)績(jī) ............................................................................................. 1879.3.1 使用圖表查看員工業(yè)績(jī) ................................................................. 1879.3.2 添加趨勢(shì)線 ..................................................................................... 1889.4 創(chuàng)建獎(jiǎng)金查詢表 ......................................................................................... 1899.5 其他應(yīng)用實(shí)例 ............................................................................................. 191目 錄第 10 章 員工工資管理系統(tǒng).......................................................................... 19210.1 創(chuàng)建員工工資表 ....................................................................................... 19210.1.1 復(fù)制工作表 ................................................................................. 19210.1.2 制作崗位工資表 ......................................................................... 19310.1.3 制作員工獎(jiǎng)金表 ......................................................................... 19410.1.4 制作員工補(bǔ)貼表 ......................................................................... 19510.1.5 制作員工工資明細(xì)表.................................................................. 19710.1.6 計(jì)算應(yīng)發(fā)工資額 ......................................................................... 19910.1.7 計(jì)算扣除保險(xiǎn)費(fèi) ......................................................................... 19910.1.8 計(jì)算個(gè)人所得稅 ......................................................................... 20010.1.9 計(jì)算實(shí)發(fā)工資額 ......................................................................... 20210.2 工資額匯總 ............................................................................................... 20310.2.1 分類匯總工資總額...................................................................... 20310.2.2 分類匯總工資平均值.................................................................. 20510.2.3 刪除分類匯總 ............................................................................. 20610.3 制作工資條 ............................................................................................... 20710.4 預(yù)覽打印效果 ........................................................................................... 20810.5 頁(yè)面設(shè)置 ................................................................................................... 20910.5.1 設(shè)置頁(yè)邊距及頁(yè)眉頁(yè)腳邊距...................................................... 20910.5.2 調(diào)整打印比例 ............................................................................. 21010.5.3 設(shè)置打印方向 ............................................................................. 21010.5.4 打印網(wǎng)格線 ................................................................................. 21110.5.5 設(shè)置打印順序 ............................................................................. 21210.5.6 設(shè)計(jì)頁(yè)眉頁(yè)腳 ............................................................................. 21210.6 打印范圍設(shè)置 ........................................................................................... 21410.6.1 調(diào)整分頁(yè)符位置 ......................................................................... 21410.6.2 刪除分頁(yè)符 ................................................................................. 21510.6.3 設(shè)置打印頁(yè)數(shù)范圍和份數(shù).......................................................... 21510.6.4 一次打印多個(gè)工作簿.................................................................. 21610.7 其他應(yīng)用實(shí)例 ........................................................................................... 217第 11 章 人力資源薪資調(diào)查系統(tǒng)................................................................. 21811.1 設(shè)計(jì)調(diào)查問(wèn)卷的內(nèi)容................................................................................ 218ix11.2 創(chuàng)建薪資調(diào)查問(wèn)卷 ................................................................................... 21811.2.1 制作問(wèn)卷標(biāo)題.............................................................................. 21911.2.2 制作說(shuō)明文字.............................................................................. 22011.2.3 添加分組框和選項(xiàng)按鈕.............................................................. 22111.2.4 添加分組框和復(fù)選框.................................................................. 22411.2.5 添加標(biāo)簽項(xiàng)和組合框.................................................................. 22511.3 測(cè)試調(diào)查問(wèn)卷 ........................................................................................... 22711.4 制作調(diào)查結(jié)果統(tǒng)計(jì)表................................................................................ 22811.4.1 制作基本統(tǒng)計(jì)表.......................................................................... 22811.4.2 添加批注 ..................................................................................... 22911.5 創(chuàng)建統(tǒng)計(jì)表與調(diào)查問(wèn)卷之間的聯(lián)系........................................................ 22911.5.1 為單選項(xiàng)按鈕創(chuàng)建鏈接.............................................................. 22911.5.2 為復(fù)選框創(chuàng)建鏈接...................................................................... 23111.5.3 為組合框創(chuàng)建鏈接...................................................................... 23211.6 實(shí)現(xiàn)調(diào)查問(wèn)卷自動(dòng)記錄............................................................................ 23311.6.1 添加自動(dòng)記錄子程序.................................................................. 23411.6.2 添加自動(dòng)記錄按鈕...................................................................... 23511.6.3 測(cè)試調(diào)查問(wèn)卷.............................................................................. 23611.7 封裝工作簿 ............................................................................................... 23811.7.1 保護(hù)工作表.................................................................................. 23811.7.2 隱藏工作表.................................................................................. 23811.8 統(tǒng)計(jì)調(diào)查結(jié)果 ........................................................................................... 23911.8.1 創(chuàng)建調(diào)查結(jié)果統(tǒng)計(jì)表.................................................................. 23911.8.2 統(tǒng)計(jì)單項(xiàng)選擇題.......................................................................... 24011.8.3 統(tǒng)計(jì)多項(xiàng)選擇題.......................................................................... 24111.8.4 統(tǒng)計(jì)下拉列表框選擇題.............................................................. 24211.9 其他應(yīng)用實(shí)例 ........................................................................................... 243第 5章 員工出勤統(tǒng)計(jì)表人力資源管理者每天都要對(duì)員工的出勤情況進(jìn)行統(tǒng)計(jì)，因此創(chuàng)建一份 易于操作的出勤統(tǒng)計(jì)表是十分必要的。

本章介紹制作員工出勤統(tǒng)計(jì)表的方法5.1 創(chuàng)建員工出勤統(tǒng)計(jì)表在創(chuàng)建員工出勤統(tǒng)計(jì)表之前，一定要清楚工作表應(yīng)包含的數(shù)據(jù)及格 式下面介紹創(chuàng)建員工出勤統(tǒng)計(jì)表的過(guò)程5.1.1 創(chuàng)建并格式化表格員工出勤統(tǒng)計(jì)表應(yīng)包含請(qǐng)假日期、請(qǐng)假時(shí)間、請(qǐng)假類別以及員工基本 信息等內(nèi)容建立基本表格的具體操作步驟如下1 新建工作簿，以“員工出勤統(tǒng)計(jì)表”為名稱保存107圖 5.12 如圖 5.1 所示，重命名工作表 Sheet1為“出勤統(tǒng)計(jì)表”，在 工作表中輸入統(tǒng)計(jì)表的標(biāo)題和表頭，并進(jìn)行美化，設(shè)置表格標(biāo)題單 元格底色3 選擇單元格區(qū)域 C3:C23 和 I3:I23，單擊【開始】選項(xiàng)卡，在【數(shù)字】組中單擊按鈕4 在彈出【設(shè)置單元格格式】對(duì)話框中，單擊【數(shù)字】標(biāo)簽，在【分類】列表 框中選擇【貨幣】選項(xiàng)，在【負(fù)數(shù)】列表框中選擇【￥1.234】選項(xiàng)第 5 章 員工出勤統(tǒng)計(jì)表5.1.2 設(shè)置數(shù)據(jù)有效性公司規(guī)定員工請(qǐng)假類別只有病假、事假、婚假和產(chǎn)假 4 種，可以利用 數(shù)據(jù)有效性定義單元格序列，使用下拉列表進(jìn)行數(shù)據(jù)輸入具體操作步驟 如圖 5.2～圖 5.7 所示1 在工作表單元格區(qū)域 L2:L5 中輸入“病假”、“事假”、“婚假”、“產(chǎn)假” 數(shù)據(jù)。

2 選擇 F 列，單擊【數(shù)據(jù)】選項(xiàng)卡，在【數(shù)據(jù)工具】組中單擊【數(shù)據(jù)有效性】按鈕3 彈出【數(shù)據(jù)有效性】對(duì)話 框，單擊【設(shè)置】標(biāo)簽，在【允 許】下拉列表框中選擇【序列】 選項(xiàng)4 在【來(lái)源】文本框中輸入 “=$L$2:$L$5”圖 5.25 單擊【輸入信息】標(biāo)簽，在【輸入信息】文本框中輸入“請(qǐng)輸入請(qǐng)假類別”6 單擊【出錯(cuò)警告】標(biāo)簽， 在【標(biāo)題】文本框中輸入“輸 入錯(cuò)誤”，在【錯(cuò)誤信息】文本 框中輸入“請(qǐng)使用下拉列表輸 入”，單擊【確定】按鈕圖 5.37 單擊選擇單元格 F2，出現(xiàn)提示信息“請(qǐng)輸入請(qǐng)假類別”，在單元格右側(cè)添加下箭頭圖 5.4圖 5.58 在單元格中，單擊其右邊的下三角按鈕，選擇請(qǐng)假類型的選項(xiàng)圖 5.69 如果輸入了非數(shù)據(jù)有效性定義的數(shù)據(jù)，會(huì)彈出【輸入錯(cuò)誤】對(duì)話框圖 5.75.1.3 計(jì)算請(qǐng)假天數(shù)10 單擊【重試】按鈕，“探親假”選擇顯示，可進(jìn)行修改；單擊【取消】 按鈕，重新輸入在計(jì)算員工因請(qǐng)假而扣除工資時(shí)，需要按照請(qǐng)假天數(shù)來(lái)計(jì)算具體計(jì)算的操作步驟如圖 5.8～圖 5.10 所示1 根據(jù)實(shí)際出勤情況，在表格中輸入出勤統(tǒng)計(jì)數(shù)據(jù)圖 5.82 請(qǐng)假時(shí)間是以“小時(shí)為單位記錄的，而每天的工作時(shí)間是 8 小時(shí)，因此在計(jì) 算請(qǐng)假天數(shù)時(shí)，應(yīng)以請(qǐng)假時(shí)間除以 8。

在 H3 單元格中 輸入公式“=G3/8”圖 5.93 按 Enter 鍵，得到員工“江思情”請(qǐng)假天數(shù)為 3，向下復(fù)制公式到單元格 H23，得到其余員工的請(qǐng)假 天數(shù)圖 5.105.1.4 計(jì)算應(yīng)扣工資公司規(guī)定員工請(qǐng)事假扣除當(dāng)天全額工資，請(qǐng)病假扣除當(dāng)天工資的一 半，請(qǐng)婚嫁和產(chǎn)假不扣工資計(jì)算應(yīng)扣工資的具體操作步驟如圖 5.11 和圖5.12 所示1 在單元格 I3 中輸入函數(shù)“=IF(F3="病假"，C3/30*H3*0.5 ， IF(F3=" 事假"，C3/30*H3，0))圖 5.11109第 5 章 員工出勤統(tǒng)計(jì)表函數(shù) IF 的功能是根據(jù)對(duì)指定的條件計(jì)算結(jié)果為 TRUE（真）或 FALSE（假），返回不同的結(jié)果格式：IF(logical_test,value_if_true,value_if_false) 參數(shù)：logical_test（邏輯表達(dá)式）表示計(jì)算結(jié)果為 TRUE（真）或 FALSE（假）的任意值或表達(dá)式，此參數(shù)可使用任何比較運(yùn)算符；value_if_true（為真時(shí)的值）表示 logical_test 為 TRUE 時(shí)返回的值，value_if_true 可以 是其他公式；value_if_false（為假時(shí)的值）是 logical_test 為 FALSE 時(shí)返 回的值，value_if_false 可以是其他公式。

2 按 Enter 鍵。