Excel(2007版)在人力資源管理中的高級應(yīng)用（下冊） 舒雄 編著 中國青年出版社 圖書在版編目（CIP）數(shù)據(jù) Excel（2007 版）在人力資源管理中高級應(yīng)用/舒雄等編著．—北京：中國 青年出版社，2009.9 ISBN 978-7-80218-208-0 Ⅰ．用… Ⅱ．李… Ⅲ．電子表格系統(tǒng)，Excel Ⅳ．TP391.13 中國版本圖書館 CIP 數(shù)據(jù)核字（2007）第 012833 號 策劃編輯 劉全 封面設(shè)計(jì) 靈動設(shè)計(jì) 責(zé)任編輯 鐘梅娟 責(zé)任校對 李珊 中國青年出版社 出 版 發(fā) 行 社 址 北京市阜成路 8 號 郵 編 100830 (010)68768548 網(wǎng) 址 經(jīng) 銷 新華書店 發(fā)行部 (010)68371900 (010)88530478(傳真) (010)68768541 (010)68767294(傳真) 零售店 讀者服務(wù)部 北京青年文苑 (010)68371105 (010)62529336 編輯部 (010)68193075 承 印 廊坊時嘉印刷有限公司 版 次 2009 年 9月第 1 版 2009 年 9月第 1 次印刷 規(guī) 格 880×1230 開 本 1/32 印 張 8 字 數(shù) 246 千字 書 號 ISBN 978-7-80218-208-0 定 價 120.00 元（全套共 5 冊） 本書如有印裝質(zhì)量問題，可與發(fā)行部聯(lián)系調(diào)換 前 言 Excel 集數(shù)據(jù)采集、編輯、分析于一身，具有強(qiáng)大的數(shù)據(jù)處理和圖 表分析功能，在人力資源管理工作中得到廣泛應(yīng)用。 本書根據(jù)人力資源管理工作的實(shí)際需要，由淺入深、循序漸進(jìn)地 講解了人力資源管理工作必需掌握的 Excel 軟件技能，并提供了多種 實(shí)用模板。通過學(xué)習(xí)本書，讀者能夠根據(jù)實(shí)際需要設(shè)計(jì)出工作表和數(shù) 據(jù)圖表，解決企業(yè)人力資源管理工作中的繁雜問題。 本書共分 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è)績獎金評定系統(tǒng)、員工 工資管理系統(tǒng)和人力資源薪資調(diào)查系統(tǒng)等制作方法。在第 2 章～第 11 章中還安排了與該章內(nèi)容相似的其他常用模板，讀者可以舉一反三， 全面掌握 Excel 在人力資源管理工作中的應(yīng)用。 本書特點(diǎn)：圖解方式、版式新穎，實(shí)例經(jīng)典、內(nèi)容實(shí)用，語言簡 練、通俗易懂，步驟詳細(xì)、一看即會。 請登錄網(wǎng)址 尚 IT 生活秀”文件夾中選擇本書實(shí)例文件和其他模板文件進(jìn)行下載。 本書由舒雄執(zhí)筆，另外陳豫龍、郭彥芳、李鵬杰、張斯予、馬洪 亮、張婷、劉方源、吳平和劉江斌也參與了部分編寫工作。 如果您對本書有何意見和建議，請通過 yuanxun.007@ 與 我們聯(lián)系。 編 者 2009 年 5 月 i 下冊目錄 第 5 章 員工出勤統(tǒng)計(jì)表................................................................................. 105 5.1 創(chuàng)建員工出勤統(tǒng)計(jì)表 ................................................................................. 105 5.1.1 創(chuàng)建并格式化表格 ......................................................................... 105 5.1.2 設(shè)置數(shù)據(jù)有效性 ............................................................................. 106 5.1.3 計(jì)算請假天數(shù) ................................................................................. 107 5.1.4 計(jì)算應(yīng)扣工資 ................................................................................. 108 5.2 統(tǒng)計(jì)月度出勤 ............................................................................................. 109 5.2.1 創(chuàng)建數(shù)據(jù)透視表 ............................................................................. 109 5.2.2 利用數(shù)據(jù)透視表查看員工的出勤...................................................111 5.2.3 修改數(shù)據(jù)透視表布局 ..................................................................... 112 5.2.4 編輯數(shù)據(jù)透視表的統(tǒng)計(jì)方式.......................................................... 113 5.2.5 設(shè)置數(shù)據(jù)透視表中的值顯示格式.................................................. 114 5.2.6 更新數(shù)據(jù)源 ..................................................................................... 115 5.2.7 套用表格樣式 ................................................................................. 116 5.2.8 刪除數(shù)據(jù)透視表 ............................................................................. 116 5.3 創(chuàng)建數(shù)據(jù)透視圖 ......................................................................................... 117 5.4 其他應(yīng)用實(shí)例 ............................................................................................. 119 第 6 章 員工在職培訓(xùn)管理系統(tǒng) ................................................................... 120 6.1 創(chuàng)建在職培訓(xùn)日程表 ................................................................................. 120 6.1.1 建立基本表格 ................................................................................. 120 6.1.2 設(shè)置時間格式 ................................................................................. 120 6.1.3 自動填充數(shù)據(jù) ................................................................................. 121 6.1.4 建立單元格之間的鏈接 ................................................................. 122 6.2 拼音字段的應(yīng)用 ......................................................................................... 123 6.2.1 添加拼音 ......................................................................................... 123 6.2.2 顯示拼音 ......................................................................................... 124 6.2.3 設(shè)置拼音字段 ................................................................................. 125 6.3 創(chuàng)建在職培訓(xùn)成績表 ................................................................................. 126 6.3.1 建立基本表格 ................................................................................. 127 6.3.2 插入圖片 ......................................................................................... 127 6.3.3 凍結(jié)窗口 ......................................................................................... 128 6.4 統(tǒng)計(jì)培訓(xùn)成績 ............................................................................................. 129 6.4.1 計(jì)算平均成績 ................................................................................. 129 6.4.2 統(tǒng)計(jì)成績排名 ................................................................................. 130 6.5 公式中單元格的引用 ................................................................................. 131 6.5.1 相對引用 ......................................................................................... 132 6.5.2 絕對引用 ......................................................................................... 133 6.5.3 混合引用 ......................................................................................... 133 目 錄 6.5.4 引用其他工作表的單元格.............................................................. 134 6.6 在職培訓(xùn)成績分析 ..................................................................................... 135 6.6.1 建立基本表格 ................................................................................. 135 6.6.2 應(yīng)用自動換行功能 ......................................................................... 135 6.6.3 為表格添加邊框 ............................................................................. 136 6.6.4 統(tǒng)計(jì)總?cè)藬?shù) ..................................................................................... 137 6.6.5 統(tǒng)計(jì)男女員工的人數(shù) ..................................................................... 139 6.6.6 統(tǒng)計(jì)最高分 ..................................................................................... 140 6.6.7 統(tǒng)計(jì)最低分 ..................................................................................... 140 6.6.8 分別統(tǒng)計(jì)男、女員工的平均分...................................................... 141 6.6.9 統(tǒng)計(jì)各分?jǐn)?shù)段的人數(shù) ..................................................................... 142 6.6.10 計(jì)算各分?jǐn)?shù)段人數(shù)占總?cè)藬?shù)的比例............................................ 146 6.7 顯示所有公式 ............................................................................................. 147 6.8 常見公式錯誤及糾正辦法 ......................................................................... 148 6.9 其他應(yīng)用實(shí)例 ............................................................................................. 151 第 7 章 員工加班記錄表................................................................................. 152 7.1 創(chuàng)建員工加班記錄表 ................................................................................. 152 7.2 使用公式輸入加班數(shù)據(jù) ............................................................................. 153 7.2.1 日期的輸入 ..................................................................................... 153 7.2.2 加班時間的計(jì)算 ............................................................................. 154 7.2.3 計(jì)算加班費(fèi) ..................................................................................... 155 7.3 使用加班記錄表 ......................................................................................... 155 7.3.1 完善加班記錄表 ............................................................................. 156 7.3.2 審核公式 ......................................................................................... 156 7.3.3 錯誤檢查 ......................................................................................... 158 7.3.4 驗(yàn)證公式計(jì)算結(jié)果 ......................................................................... 159 7.4 其他應(yīng)用實(shí)例 ............................................................................................. 161 第 8 章 員工年度考核系統(tǒng) ............................................................................ 162 8.1 創(chuàng)建季度考核表 ......................................................................................... 162 8.1.1 創(chuàng)建出勤統(tǒng)計(jì)表 ............................................................................. 162 8.1.2 設(shè)置小數(shù)位數(shù) ................................................................................. 163 8.1.3 創(chuàng)建績效表 ..................................................................................... 163 vii 8.1.4 使用函數(shù) INDEX 引用數(shù)據(jù) ........................................................... 164 8.1.5 計(jì)算總成績 ..................................................................................... 166 8.2 創(chuàng)建年度考核表 ......................................................................................... 167 8.2.1 建立基本表格 ................................................................................. 167 8.2.2 利用合并計(jì)算功能計(jì)算成績.......................................................... 167 8.2.3 計(jì)算總成績 ..................................................................................... 169 8.2.4 突出顯示特定成績 ......................................................................... 170 8.2.5 計(jì)算成績排名 ................................................................................. 171 8.3 利用排序功能查看年度考核表.................................................................. 172 8.3.1 簡單排序 ......................................................................................... 172 8.3.2 復(fù)雜排序 ......................................................................................... 173 8.3.3 自定義排序 ..................................................................................... 174 8.4 利用篩選功能查看年度考核表.................................................................. 176 8.4.1 自動篩選 ......................................................................................... 176 8.4.2 自定義篩選 ..................................................................................... 176 8.4.3 高級篩選 ......................................................................................... 177 8.5 其他應(yīng)用實(shí)例 ............................................................................................. 180 第 9 章 員工業(yè)績獎金評定系統(tǒng) ................................................................... 181 9.1 創(chuàng)建獎金規(guī)范表 ......................................................................................... 181 9.2 創(chuàng)建獎金評定表 ......................................................................................... 181 9.2.1 建立基本表格 ................................................................................. 181 9.2.2 計(jì)算獎金比例 ................................................................................. 183 9.2.3 計(jì)算基本業(yè)績獎金 ......................................................................... 184 9.2.4 計(jì)算本月累計(jì)銷售額 ..................................................................... 185 9.2.5 計(jì)算累計(jì)銷售獎金 ......................................................................... 185 9.2.6 計(jì)算員工總獎金 ............................................................................. 186 9.3 評估員工業(yè)績 ............................................................................................. 187 9.3.1 使用圖表查看員工業(yè)績 ................................................................. 187 9.3.2 添加趨勢線 ..................................................................................... 188 9.4 創(chuàng)建獎金查詢表 ......................................................................................... 189 9.5 其他應(yīng)用實(shí)例 ............................................................................................. 191 目 錄 第 10 章 員工工資管理系統(tǒng).......................................................................... 192 10.1 創(chuàng)建員工工資表 ....................................................................................... 192 10.1.1 復(fù)制工作表 ................................................................................. 192 10.1.2 制作崗位工資表 ......................................................................... 193 10.1.3 制作員工獎金表 ......................................................................... 194 10.1.4 制作員工補(bǔ)貼表 ......................................................................... 195 10.1.5 制作員工工資明細(xì)表.................................................................. 197 10.1.6 計(jì)算應(yīng)發(fā)工資額 ......................................................................... 199 10.1.7 計(jì)算扣除保險費(fèi) ......................................................................... 199 10.1.8 計(jì)算個人所得稅 ......................................................................... 200 10.1.9 計(jì)算實(shí)發(fā)工資額 ......................................................................... 202 10.2 工資額匯總 ............................................................................................... 203 10.2.1 分類匯總工資總額...................................................................... 203 10.2.2 分類匯總工資平均值.................................................................. 205 10.2.3 刪除分類匯總 ............................................................................. 206 10.3 制作工資條 ............................................................................................... 207 10.4 預(yù)覽打印效果 ........................................................................................... 208 10.5 頁面設(shè)置 ................................................................................................... 209 10.5.1 設(shè)置頁邊距及頁眉頁腳邊距...................................................... 209 10.5.2 調(diào)整打印比例 ............................................................................. 210 10.5.3 設(shè)置打印方向 ............................................................................. 210 10.5.4 打印網(wǎng)格線 ................................................................................. 211 10.5.5 設(shè)置打印順序 ............................................................................. 212 10.5.6 設(shè)計(jì)頁眉頁腳 ............................................................................. 212 10.6 打印范圍設(shè)置 ........................................................................................... 214 10.6.1 調(diào)整分頁符位置 ......................................................................... 214 10.6.2 刪除分頁符 ................................................................................. 215 10.6.3 設(shè)置打印頁數(shù)范圍和份數(shù).......................................................... 215 10.6.4 一次打印多個工作簿.................................................................. 216 10.7 其他應(yīng)用實(shí)例 ........................................................................................... 217 第 11 章 人力資源薪資調(diào)查系統(tǒng)................................................................. 218 11.1 設(shè)計(jì)調(diào)查問卷的內(nèi)容................................................................................ 218 ix 11.2 創(chuàng)建薪資調(diào)查問卷 ................................................................................... 218 11.2.1 制作問卷標(biāo)題.............................................................................. 219 11.2.2 制作說明文字.............................................................................. 220 11.2.3 添加分組框和選項(xiàng)按鈕.............................................................. 221 11.2.4 添加分組框和復(fù)選框.................................................................. 224 11.2.5 添加標(biāo)簽項(xiàng)和組合框.................................................................. 225 11.3 測試調(diào)查問卷 ........................................................................................... 227 11.4 制作調(diào)查結(jié)果統(tǒng)計(jì)表................................................................................ 228 11.4.1 制作基本統(tǒng)計(jì)表.......................................................................... 228 11.4.2 添加批注 ..................................................................................... 229 11.5 創(chuàng)建統(tǒng)計(jì)表與調(diào)查問卷之間的聯(lián)系........................................................ 229 11.5.1 為單選項(xiàng)按鈕創(chuàng)建鏈接.............................................................. 229 11.5.2 為復(fù)選框創(chuàng)建鏈接...................................................................... 231 11.5.3 為組合框創(chuàng)建鏈接...................................................................... 232 11.6 實(shí)現(xiàn)調(diào)查問卷自動記錄............................................................................ 233 11.6.1 添加自動記錄子程序.................................................................. 234 11.6.2 添加自動記錄按鈕...................................................................... 235 11.6.3 測試調(diào)查問卷.............................................................................. 236 11.7 封裝工作簿 ............................................................................................... 238 11.7.1 保護(hù)工作表.................................................................................. 238 11.7.2 隱藏工作表.................................................................................. 238 11.8 統(tǒng)計(jì)調(diào)查結(jié)果 ........................................................................................... 239 11.8.1 創(chuàng)建調(diào)查結(jié)果統(tǒng)計(jì)表.................................................................. 239 11.8.2 統(tǒng)計(jì)單項(xiàng)選擇題.......................................................................... 240 11.8.3 統(tǒng)計(jì)多項(xiàng)選擇題.......................................................................... 241 11.8.4 統(tǒng)計(jì)下拉列表框選擇題.............................................................. 242 11.9 其他應(yīng)用實(shí)例 ........................................................................................... 243 第 5章 員工出勤統(tǒng)計(jì)表 人力資源管理者每天都要對員工的出勤情況進(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ì)表的過程。 5.1.1 創(chuàng)建并格式化表格 員工出勤統(tǒng)計(jì)表應(yīng)包含請假日期、請假時間、請假類別以及員工基本 信息等內(nèi)容。建立基本表格的具體操作步驟如下。 1 新建工作簿，以“員工出勤統(tǒng)計(jì)表”為名稱保存。 107 圖 5.1 2 如圖 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è)置單元格格式】對話框中，單擊【數(shù)字】標(biāo)簽，在【分類】列表 框中選擇【貨幣】選項(xiàng)，在【負(fù)數(shù)】列表框中選擇【￥1.234】選項(xiàng)。 第 5 章 員工出勤統(tǒng)計(jì)表 5.1.2 設(shè)置數(shù)據(jù)有效性 公司規(guī)定員工請假類別只有病假、事假、婚假和產(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ù)有效性】對話 框，單擊【設(shè)置】標(biāo)簽，在【允 許】下拉列表框中選擇【序列】 選項(xiàng)。 4 在【來源】文本框中輸入 “=$L$2:$L$5”。 圖 5.2 5 單擊【輸入信息】標(biāo)簽， 在【輸入信息】文本框中輸入 “請輸入請假類別”。 6 單擊【出錯警告】標(biāo)簽， 在【標(biāo)題】文本框中輸入“輸 入錯誤”，在【錯誤信息】文本 框中輸入“請使用下拉列表輸 入”，單擊【確定】按鈕。 圖 5.3 7 單擊選擇單元格 F2，出現(xiàn) 提示信息“請輸入請假類別”， 在單元格右側(cè)添加下箭頭。 圖 5.4 圖 5.5 8 在單元格中，單擊其 右邊的下三角按鈕，選擇 請假類型的選項(xiàng)。 圖 5.6 9 如果輸入了非數(shù)據(jù) 有效性定義的數(shù)據(jù)，會彈 出【輸入錯誤】對話框。 圖 5.7 5.1.3 計(jì)算請假天數(shù) 10 單擊【重試】按鈕， “探親假”選擇顯示，可 進(jìn)行修改；單擊【取消】 按鈕，重新輸入。 在計(jì)算員工因請假而扣除工資時，需要按照請假天數(shù)來計(jì)算。具體計(jì) 算的操作步驟如圖 5.8～圖 5.10 所示。 1 根據(jù)實(shí)際出勤情況， 在表格中輸入出勤統(tǒng)計(jì)數(shù)據(jù)。 圖 5.8 2 請假時間是以“小時 為單位記錄的，而每天的工 作時間是 8 小時，因此在計(jì) 算請假天數(shù)時，應(yīng)以請假時 間除以 8。在 H3 單元格中 輸入公式“=G3/8”。 圖 5.9 3 按 Enter 鍵，得到員 工“江思情”請假天數(shù)為 3， 向下復(fù)制公式到單元格 H23，得到其余員工的請假 天數(shù)。 圖 5.10 5.1.4 計(jì)算應(yīng)扣工資 公司規(guī)定員工請事假扣除當(dāng)天全額工資，請病假扣除當(dā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.11 109 第 5 章 員工出勤統(tǒng)計(jì)表 函數(shù) IF 的功能是根據(jù)對指定的條件計(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 （為真時的值）表示 logical_test 為 TRUE 時返回的值，value_if_true 可以 是其他公式；value_if_false（為假時的值）是 logical_test 為 FALSE 時返 回的值，value_if_false 可以是其他公式。 2 按 Enter 鍵