用Excel建立一套小型人事數(shù)據(jù)管理系統(tǒng)

1 用 Excel 建立一套小型人事數(shù)據(jù)管理系統(tǒng) Excel 的功能強(qiáng)大 而操作又很方便 每月準(zhǔn)確無誤的統(tǒng)計(jì)企業(yè)員工增減變化情況 年 底分析大量的人事數(shù)據(jù)等等復(fù)雜的工作都能通過 Excel 輕松完成 現(xiàn)在我們給讀者朋友們提 供這樣一個(gè)實(shí)例 通過 Excel 來建立一套小型人事數(shù)據(jù)管理系統(tǒng) 首先我們打開一個(gè)新的 Excel 表建立一個(gè)人事信息庫框架 信息項(xiàng)目的設(shè)置您可根據(jù)本 單位實(shí)際情況而定 如圖 一 所示 圖一 點(diǎn)擊 可看清晰大圖 接下來請(qǐng)您不要急著錄入人員信息 我們要對(duì)一些信息項(xiàng)進(jìn)行函數(shù)設(shè)置 以便系統(tǒng)可以 自動(dòng)生成相關(guān)信息 這會(huì)使我們的工作產(chǎn)生事半功倍的效果 1 性別 出生月日 年齡的自動(dòng)填充功能設(shè)置性別 出生月日 年齡的自動(dòng)填充功能設(shè)置 我們先對(duì) 性別 出生年月 年齡 進(jìn)行函數(shù)設(shè)置 當(dāng)我們輸入某人身份證號(hào)碼時(shí) 系統(tǒng)便會(huì)自動(dòng)生成 性別 出生年月 及 年齡 這樣就減少了我們錄入的工作量 請(qǐng)分別選擇性別 出生月日 年齡信息項(xiàng)單元格輸入下列公式 1 性別 IF MOD IF LEN E3 15 MID E3 15 1 MID E3 17 1 2 1 男 女 含義 含義 LEN E3 15 表示看 E3 中是否有 15 個(gè)字符 MID E3 15 1 表示在 E3 中從第 15 位開始提取 1 位字符 MOD MID 2 1 表示提取的字符除以 2 余數(shù)為 1 IF LEN E3 15 MID E3 15 1 MID E3 17 1 表示看 E3 中是否夠 15 個(gè)字符 如果 夠就從第 15 個(gè)字符開始取 1 個(gè)字符 如果不夠 15 個(gè)字符就從第 17 個(gè)字符開始取 1 個(gè)字符 我們的身份證號(hào)碼一般是 15 位或 18 位 IF MOD IF LEN E3 15 MID E3 15 1 MID E3 17 1 2 1 男 女 表示所取字符 除以 2 如果余數(shù)為 1 顯示男 否則顯示女 2 出生年月 DATE MID E3 7 4 MID E3 11 2 MID E3 13 2 含義 含義 DATE YEAR MONTH DAY 2 MID E3 7 4 表示在 E3 中從第 7 個(gè)字符開始連續(xù)取 4 個(gè)字符表示年 用類似的表 示方法一個(gè)人的出生年月日便可以通過函數(shù)設(shè)置表示出來 如果為了看起來方便 我們可以 將單元格格式設(shè)置成年 月 日的日期格式 這樣顯示的結(jié)果會(huì)非常容易理解 3 年齡 DATEDIF G3 TODAY Y 含義 含義 DATEDIF date1 date2 Y 表示兩個(gè)日期的差值 TODAY 表示系統(tǒng)自帶的日期即顯示當(dāng)日日期 DATEDIF G3 TODAY Y 表示今天的日期與 G3 所表示的出生月日之間的年份差 值 這樣一個(gè)人的年齡就會(huì)容易的顯示出來了 注 性別 出生月日 年齡的自動(dòng)填充后 一般還要復(fù)制性別 出生月日 年齡的自動(dòng)填充后 一般還要復(fù)制 選擇性粘貼 選選擇性粘貼 選 值值 因 因 為為 Excel 單元格中公示容易出錯(cuò) 還是真實(shí)數(shù)值比較安全 單元格中公示容易出錯(cuò) 還是真實(shí)數(shù)值比較安全 2 勞動(dòng)合同期限的自動(dòng)生成和提前勞動(dòng)合同期限的自動(dòng)生成和提前 30 天定期提醒功能設(shè)置天定期提醒功能設(shè)置 勞動(dòng)合同管理也是人事管理中不可缺少的一部分 特別是勞動(dòng)合同到期續(xù)簽問題更是不 可忽視 如果由于我們的人為原因造成了勞動(dòng)合同漏簽 延期簽訂等問題可能會(huì)給公司以及 員工個(gè)人帶來一定的損失 從一個(gè)人入職簽訂試用期合同開始 再到簽訂正式勞動(dòng)合同以及 后來的續(xù)簽勞動(dòng)合同 一系列的日期如何能夠讓系統(tǒng)自動(dòng)生成 并且形成系統(tǒng)提前 30 天自 動(dòng)提醒我們的功能呢 這需要對(duì)一些信息項(xiàng)進(jìn)行函數(shù)設(shè)置 如圖 二 所示 圖二 點(diǎn)擊可看清晰大圖 1 試用期到期時(shí)間 DATE YEAR P3 MONTH P3 3 DAY P3 1 含義 含義 DATE YEAR MONTH DAY 顯示指定日期 在這里我們假設(shè)試用期為 3 個(gè)月 我們需要在 Q3 單元格中輸入上述公式 其中 MONTH P3 3 表示在此人入職時(shí)間月的基礎(chǔ)上增加三個(gè)月 而 DAY P3 1 是根據(jù)勞動(dòng)合 同簽訂為整年正月而設(shè)置的 比如 2005 年 11 月 6 日到 2006 年 11 月 5 日為一個(gè)勞動(dòng)合同 簽訂期 2 勞動(dòng)合同到期時(shí)間 DATE YEAR P3 1 MONTH P3 DAY P3 1 3 我們同樣采用上述函數(shù)的設(shè)置方法 這里我們假設(shè)勞動(dòng)合同期限為 1 年 則我們需要設(shè) 置成 YEAR P3 1 另外這個(gè)數(shù)值依然以入職日期為計(jì)算根據(jù) 所以天數(shù)上還要設(shè)置成 DAY P3 1 的格式 3 續(xù)簽合同到期時(shí)間 DATE YEAR S3 1 MONTH S3 DAY S3 這里需要注意的是續(xù)簽合同計(jì)算是以前份合同簽訂到期日期為根據(jù)的 所以只在前一份 合同到期時(shí)間的基礎(chǔ)上增加 1 年即可 無需天數(shù)上減 1 4 試用期提前 7 天提醒 IF DATEDIF TODAY Q3 d 7 試用期快結(jié)束了 這里用到了 DATEDIF 函數(shù) 表示兩個(gè)日期差值 但是需要注意的是 我們要表示提前 7 天提醒 所以 將 TODAY 函數(shù)寫到試用期時(shí)間前面即 TODAY Q3 而不能表示成 Q3 TODAY 其中 d 表示兩個(gè)日期天數(shù)差值 我們用 IF 函數(shù)來表示顯示要求 那么 這個(gè)函數(shù)設(shè)置的含義為 如果差值為 7 則顯示 試用期快結(jié)束了 否則不顯示信息 在編輯 函數(shù)時(shí)用 表示不顯示任何信息 5 提前 30 天提醒 IF DATEDIF TODAY S4 m 1 該簽合同了 函數(shù)設(shè)置方法同上 其含義是兩個(gè)日期相差 1 個(gè)月則顯示 該簽合同了 否則不顯示任 何信息 這里沒有設(shè)置成相差 30 天提醒是因?yàn)榭紤]到設(shè)置成月更利于我們?nèi)耸鹿ぷ鞯牟僮?同樣需要注意的是不要將顯示 今天日期 函數(shù)與顯示 合同到期日期 函數(shù)順序顛倒 其 中 m 表示月的含義 3 采用采用 記錄單記錄單 錄入信息錄入信息 通過對(duì)一些人事信息項(xiàng)進(jìn)行函數(shù)設(shè)置后 我們便可以開始錄入信息了 逐行的鍵入人事 信息 會(huì)讓人很快產(chǎn)生疲勞感 甚至?xí)霈F(xiàn)串行或輸錯(cuò)信息的工作失誤 我們可以采用 Excel 自帶的 記錄單 功能來解決這個(gè)問題 請(qǐng)點(diǎn)擊編輯欄中的 數(shù)據(jù) 記錄單 如 圖 三 我們可以用 Tab 鍵進(jìn)行項(xiàng)目的換行錄入如圖 四 圖三 點(diǎn)擊可看清晰大圖 4 圖四 點(diǎn)擊 可看清晰大圖 4 用用 窗口凍結(jié)窗口凍結(jié) 功能可以進(jìn)行簡(jiǎn)單的數(shù)據(jù)查詢功能可以進(jìn)行簡(jiǎn)單的數(shù)據(jù)查詢 當(dāng)我們錄入完數(shù)據(jù)后 我們希望能夠非常方便的查詢信息 但是由于信息庫所涉及的項(xiàng) 目很多 我們常常會(huì)遇到這樣的情況 看到左邊的信息又看不到右邊的信息 或者看到下面 的信息又不知道此信息所對(duì)應(yīng)的信息項(xiàng) 這時(shí)我們可以采用 窗口凍結(jié) 功能 例如我現(xiàn)在 想保留各信息項(xiàng) 同時(shí)保留每個(gè)人的編號(hào) 姓名 部門 讓其他信息可以根據(jù)需要進(jìn)行查找 這時(shí)我們可以點(diǎn)擊 D3 單元格 然后點(diǎn)擊編輯菜單欄的 窗口 選擇 凍結(jié)窗口 就可 以出現(xiàn)如圖 五 情況 圖五 點(diǎn)擊 可看清晰大圖 這樣我們會(huì)非常容易查到某人相關(guān)信息 但是值得注意的是設(shè)置窗口凍結(jié)的規(guī)律 如果 你想凍結(jié)第 2 行請(qǐng)將光標(biāo)放到第 3 行單元格處 進(jìn)行凍結(jié)窗口設(shè)置 如果你想凍結(jié) C 列 請(qǐng)將光標(biāo)放到 D 列單元格處 進(jìn)行凍結(jié)窗口設(shè)置 如果你即想凍結(jié)第 2 行又想凍結(jié) C 列 5 請(qǐng)將光標(biāo)放到他們的交叉單元格 D3 上進(jìn)行設(shè)置 如果想取消凍結(jié)功能可以點(diǎn)擊 窗口 菜 單選擇取 消凍結(jié)窗口 功能 5 自動(dòng)篩選自動(dòng)篩選 功能可以進(jìn)行簡(jiǎn)單的數(shù)據(jù)統(tǒng)計(jì)功能可以進(jìn)行簡(jiǎn)單的數(shù)據(jù)統(tǒng)計(jì) 有時(shí)我們想非?？焖俚牡玫揭恍?shù)據(jù) 比如本公司在職人員中 本科生學(xué)歷的男生有多 少人 這時(shí)我們可以采用自動(dòng)篩選功能 請(qǐng)選擇 人員類別 單元格 點(diǎn)擊編輯菜單欄上的 數(shù)據(jù) 選擇 篩選 點(diǎn)擊 自動(dòng)篩選 在每個(gè)信息項(xiàng)單元格右下角都會(huì)出現(xiàn)選擇按鈕 我們分別在人員類別處選擇在職 圖 六 在學(xué)歷處選擇本科 圖 七 在性別處選擇男 圖 八 最后用鼠標(biāo)將顯示的性別全部選上 這時(shí)注意圖 九 中用紅筆圈住的地方就是我們所 需要的數(shù)據(jù)了 圖六 點(diǎn)擊可看清晰大圖 6 圖七 點(diǎn)擊 可看清晰大圖 圖八 點(diǎn)擊可看清晰大圖 圖九 點(diǎn)擊 可看清晰大圖 6 用 用 數(shù)據(jù)透視表數(shù)據(jù)透視表 功能快速匯總各項(xiàng)數(shù)據(jù)功能快速匯總各項(xiàng)數(shù)據(jù) 以上功能的設(shè)置多用于日常人事工作中 但到年底我們需要對(duì)這一年的人事情況進(jìn)行大 量的信息總結(jié) 比如人員的離職情況 入職情況 各類數(shù)據(jù)構(gòu)成比例等 這項(xiàng)工作更是一項(xiàng) 非常繁重的工作 我們可以利用 Excel 自帶的 數(shù)據(jù)透視表 功能為我們排憂解難 請(qǐng)點(diǎn)擊編輯菜單欄中 數(shù)據(jù) 選項(xiàng) 選擇 數(shù)據(jù)透視表和數(shù)據(jù)透視圖 選項(xiàng) 如圖 十 7 圖十 點(diǎn)擊 可看清晰大圖 選擇 數(shù)據(jù)透視表 接下來點(diǎn)擊 下一步 即可 我們需要確定建立數(shù)據(jù)透視表的數(shù)據(jù) 源區(qū)域 一般系統(tǒng)會(huì)自動(dòng)將整個(gè)信息庫區(qū)域設(shè)置成我們要選擇的區(qū)域 在出現(xiàn) 數(shù)據(jù)透視表 和數(shù)據(jù)透視圖向?qū)?3 第 3 步時(shí)我們選擇 現(xiàn)有工作表 將工作表區(qū)域選擇在新的 sheet 中 A3 單元格 如圖 十一 點(diǎn)擊 完成 后 便會(huì)出現(xiàn) 圖 十二 結(jié)果 這里解釋一下我 們?yōu)槭裁匆x擇 A3 單元格 這主要是因?yàn)轱@示區(qū)上面有兩行用來放置頁字段 圖十一 點(diǎn) 擊可看清晰大圖 8 圖十二 點(diǎn) 擊可看清晰大圖 例如我們現(xiàn)在想統(tǒng)計(jì)各部門 2003 年入職人員情況 我們便可以將相關(guān)項(xiàng)目用鼠標(biāo)托至 指定位置 如圖 十二 紅色箭頭顯示 通過對(duì)信息項(xiàng)目的拖拽 系統(tǒng)會(huì)自動(dòng)出現(xiàn)相關(guān)信息的 統(tǒng)計(jì)數(shù)據(jù) 如果我們還想出現(xiàn)統(tǒng)計(jì)數(shù)據(jù)的圖示 這時(shí)我們可以點(diǎn)擊數(shù)據(jù)透視表編輯菜單欄上 的圖例圖標(biāo) 如圖 十二 藍(lán)圈圈住的地方 這時(shí)會(huì)出現(xiàn) chart1 如圖 十三 所示 如果我們 想改變圖例顯示類型可以點(diǎn)擊圖表向?qū)нM(jìn)行自由選擇 圖十三 點(diǎn) 擊可看清晰大圖 通過這個(gè)功能我們可以很容易的統(tǒng)計(jì)出各種人