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

1、用Excel建立一套小型人事數(shù)據(jù)管理系統(tǒng)前言:在現(xiàn)實(shí)生活中，很多小型企業(yè)并沒(méi)有給人事部門配備專門的人事管理軟件。實(shí)際上，一 套專門的人事管理軟件費(fèi)用昂貴，而且不一定適合企業(yè)的實(shí)際需求。自己動(dòng)手，用 Excel來(lái)建立適于本企業(yè)具體需求的人事管理系統(tǒng)是一個(gè)不錯(cuò)的選擇。Excel的功能強(qiáng)大，而操作又很方便，每月準(zhǔn)確無(wú)誤的統(tǒng)計(jì)企業(yè)員工增減變化情況，年底分 析大量的人事數(shù)據(jù)等等復(fù)雜的工作都能通過(guò)Excel輕松完成?，F(xiàn)在我們給讀者朋友們提供這樣一個(gè)實(shí)例一一通過(guò) Excel來(lái)建立一套小型人事數(shù)據(jù)管理系統(tǒng)。正文：首先我們打開(kāi)一個(gè)新的Excel表建立一個(gè)人事信息庫(kù)框架，信息項(xiàng)目的設(shè)置您可根據(jù)本單位實(shí)際情況而定。

2、如圖（一）所示：圖一打開(kāi)一個(gè)新的Excel表建立一個(gè)人事信息庫(kù)框架接下來(lái)請(qǐng)您不要急著錄入人員信息，我們要對(duì)一些信息項(xiàng)進(jìn)行函數(shù)設(shè)置，以便系統(tǒng)可以 自動(dòng)生成相關(guān)信息，這會(huì)使我們的工作產(chǎn)生事半功倍的效果。1 、 性別、出生月日、年齡的自動(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,"

3、男 "," 女 ")含義：“ LEN(E3)=15'表示看 E3中是否有15個(gè)字符；“MID(E3,15,1) ”表示在 E3中從第15位開(kāi)始提取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è)字符開(kāi)始取1 個(gè)字符， 如果不夠15 個(gè)字符就從第17 個(gè)字符開(kāi)始取1 個(gè)字符。 我們的身份證號(hào)碼一般是15 位或 18 位。“ IF(MOD(IF(LEN(E3)=15,MID(E3,

4、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);“MID(E3,7,4) ”表示在 E3中從第7個(gè)字符開(kāi)始連續(xù)取 4個(gè)字符表示年，用類似的表示方法一個(gè)人的出生年月日便可以通過(guò)函數(shù)設(shè)置表示出來(lái)，如果為了看起來(lái)方便，我們可以將 單元格格式設(shè)置成年、月、日的日期格式，這樣顯示的結(jié)果會(huì)非常容易理解。(3) 年齡 : =DATED

5、IF(G3,TODAY(),"Y")含義：“ DATEDIF(date1,date2, “ Y” )”表示兩個(gè)日期的差值 ;“TODAY。表示系統(tǒng)自帶的日期即顯示當(dāng)日日期 ；G3所表示的出生月日之間的年份差“DATEDIF(G3,TODAY(),"Y")” 表示今天的日期與值，這樣一個(gè)人的年齡就會(huì)容易的顯示出來(lái)了。2、勞動(dòng)合同期限的自動(dòng)生成和提前30天定期提醒功能設(shè)置勞動(dòng)合同管理也是人事管理中不可缺少的一部分，特別是勞動(dòng)合同到期續(xù)簽問(wèn)題更是不可忽視。如果由于我們的人為原因造成了勞動(dòng)合同漏簽、延期簽訂等問(wèn)題可能會(huì)給公司以及員工個(gè)人帶來(lái)一定的損失。 從一個(gè)人

6、入職簽訂試用期合同開(kāi)始，再到簽訂正式勞動(dòng)合同以及后來(lái)的續(xù)簽勞動(dòng)合同，一系列的日期如何能夠讓系統(tǒng)自動(dòng)生成，并且形成系統(tǒng)提前30天自動(dòng)提醒我們的功能呢？這需要對(duì)一些信息項(xiàng)進(jìn)行函數(shù)設(shè)置。如圖(二)所示：圖二信息項(xiàng)進(jìn)行函數(shù)設(shè)置(1)試用期到期時(shí)間：=DATE(YEAR(P3),MONTH(P3)+3,DAY(P3)-1)含義：“ DATE(YEAR(),MONTH(),DAY() 顯示指定日期;在這里我們假設(shè)試用期為3個(gè)月，我們需要在 Q3單元格中輸入上述公式，其中MONTH(P3)+昧示在此人入職時(shí)間月的基礎(chǔ)上增加三個(gè)月。而DAY(P3)-1是根據(jù)勞動(dòng)合同簽訂為整年正月而設(shè)置的。 比如2005年11

7、月6日到2006年11月5日為一個(gè)勞動(dòng)合同簽訂期。(2)勞動(dòng)合同到期時(shí)間：=DATE(YEAR(P3)+3,MONTH(P3),DAY(P3)-1)我們同樣采用上述函數(shù)的設(shè)置方法。這里我們假設(shè)勞動(dòng)合同期限為3年，則我們需要設(shè)置成YEAR(P3)+3,另外這個(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年即可，無(wú)需天數(shù)上減 1。(4)試用期提前7天提醒：=IF(DATE

8、DIF(TODAY(),Q3,"d")=7,"試用期快結(jié)束了 ","")這里用到了 DATEDIF函數(shù)，表示兩個(gè)日期差值，但是需要注意的是，我們要表示提前7天提醒，所以，將TODAY()®數(shù)寫到試用期時(shí)間前面即TODAY(),Q3而不能表示成Q3,TODAY()。其中“d”表示兩個(gè)日期天數(shù)差值。我們用 IF()函數(shù)來(lái)表示顯示要求，那么這個(gè)函數(shù)設(shè)置的 含義為：如果差值為7則顯示“試用期快結(jié)束了”否則不顯示信息，在編輯函數(shù)時(shí)用表 示不顯示任何信息。(5)提前 30 天提醒:=IF(DATEDIF(TODAY(),S4,"

9、;m")=1,"該簽合同了 ","")函數(shù)設(shè)置方法同上，其含義是兩個(gè)日期相差 1個(gè)月則顯示“該簽合同了"否則不顯示任 何信息。這里沒(méi)有設(shè)置成相差 30天提醒是因?yàn)榭紤]到設(shè)置成月更利于我們?nèi)耸鹿ぷ鞯牟僮鳌?同樣需要注意的是不要將顯示“今天日期”函數(shù)與顯示“合同到期日期”函數(shù)順序顛倒。其中"m”表示月的含義。3、采用“記錄單”錄入信息通過(guò)對(duì)一些人事信息項(xiàng)進(jìn)行函數(shù)設(shè)置后，我們便可以開(kāi)始錄入信息了。逐行的鍵入人事信息，會(huì)讓人很快產(chǎn)生疲勞感，甚至?xí)霈F(xiàn)串行或輸錯(cuò)信息的工作失誤。我們可以采用Excel自帶的“記錄單”功能來(lái)解決這個(gè)問(wèn)題。

10、請(qǐng)點(diǎn)擊編輯欄中的“數(shù)據(jù)”一“記錄單”如圖(三) 我們可以用“Tab鍵進(jìn)行項(xiàng)目的換行錄入如圖 (四)。圖三記錄單a nct L1,3(舅%am1 hJ4K76"11 ：,.6?，氏* F C<g . octIQ F QW11 *,1 廠。1JjJoli14 g is . m?:運(yùn), DM1T Q|f13 ' Cfil«*H.門Jli&£F4WnHkt *f力一aJi更UM9itsUtL frfeS*H =tl.M正 蟲Rtt*第丘制Ad"zn»-ii-fqiuai. ”Emw-il情MiEWHK-Vli-f叁 aiUElT

11、- 3IC -AJKIAM睥腳jprrLT7*.*FW-Tip .KU那 號(hào)* | *F |ffllMI *拒；”12«0E-1：L 2TO-2-S*WT3)/T-EMM-M 力叫”1圖四Tab鍵進(jìn)行項(xiàng)目的換行錄入4、用“窗口凍結(jié)”功能可以進(jìn)行簡(jiǎn)單的數(shù)據(jù)查詢當(dāng)我們錄入完數(shù)據(jù)后， 我們希望能夠非常方便的查詢信息，但是由于信息庫(kù)所涉及的項(xiàng)目很多，我們常常會(huì)遇到這樣的情況：看到左邊的信息又看不到右邊的信息，或者看到下面的信息又不知道此信息所對(duì)應(yīng)的信息項(xiàng)。這時(shí)我們可以采用“窗口凍結(jié)”功能。例如我現(xiàn)在想保留各信息項(xiàng)，同時(shí)保留每個(gè)人的編號(hào)、姓名、部門，讓其他信息可以根據(jù)需要進(jìn)行查找， 這時(shí)我們可

12、以點(diǎn)擊 D3單元格，然后點(diǎn)擊編輯菜單欄的“窗口”，選擇“凍結(jié)窗口”，就可 以出現(xià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列，請(qǐng)將 光標(biāo)放到他們的交叉單元格D3上進(jìn)行設(shè)置。如果想取消凍結(jié)功能可以點(diǎn)擊“窗口”菜單選擇取“消凍結(jié)窗口”功能。5、“自動(dòng)篩選”功能可以進(jìn)行簡(jiǎn)單的數(shù)據(jù)統(tǒng)計(jì)有時(shí)我們想非?？焖俚牡玫揭恍?shù)據(jù)，比如本公司在職人員中， 本科生學(xué)歷的男生有多少人？這時(shí)我們可以

13、采用自動(dòng)篩選功能。請(qǐng)選擇“人員類別”單元格，點(diǎn)擊編輯菜單欄上的“數(shù)據(jù)”選擇“篩選”，點(diǎn)擊“自動(dòng)篩選”。在每個(gè)信息項(xiàng)單元格右下角都會(huì)出現(xiàn)選擇按鈕。我們分別在人員類別處選擇在職（圖（六），在學(xué)歷處選擇本科（圖（七），在性別處選擇男（圖（八），最后用鼠標(biāo)將顯示的性別全部選上，這時(shí)注意圖（九）中用紅筆圈住的地方就是我們所需要的數(shù)據(jù)了。圖六分別在人員類別處選擇在職圖七在學(xué)歷處選擇本科圖八在性別處選擇男圖九 紅筆圈住的地方就是我們所需要的數(shù)據(jù)6、用“數(shù)據(jù)透視表”功能快速匯總各項(xiàng)數(shù)據(jù)口51| “1， «®i K(D H< k|i 工 Jj WS Ui in *ii ：i3 印二 #

14、 *中，七 £ 少 口&-5-* W： MJg W*n 一1 . UY * 如ffninirgw f *1，j' ''i'f 后"ini' &n, +t*n 鼾函中職A，7之堪中一丁 一 - TW 毛：M3- a-Jd和CBTfW*MTT- ll 看上JTCUI I - i JHi J-3-JllMt |iai-M r »>hm|iw-1-1or± w± 也】一：171不:nWw-I.以上功能的設(shè)置多用于日常人事工作中，但到年底我們需要對(duì)這一年的人事情況進(jìn)行大量的信息總結(jié)，比如人員的

15、離職情況，入職情況，各類數(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)， 如圖（十）三-回產(chǎn)口百瓦國(guó)力、* T4清丁 I算。） 戶 L e I a i £ 一匕E£ H .慶 利EAWBHMHUEMI 名財(cái)懂-jUnt!h12'Xd濰篤,后富日13工”XffWjg產(chǎn)配,皿11界&1；五 jqanHE.iaMM-ih *2!-中倡得丘 Z5fOM-5-G MMMhMRL& 1-TTN宿，|別:?卞勸 ±l*b Q-

16、tX .：i . -.1._uu_-r-a自 F用 I4*| .1! ij m*A5Vantitav£pg-IipMW I UXHUilBIEj 叁;HB1力iMQ為 5 jli*.L/EL加語(yǔ)-*$/：w>g|iRiN3ag I：4Fl173lU'-4-E:"維m:網(wǎng)"I*出 于士 MTM-rEtVUiJ I AMT 國(guó)照|士工*;抬*必,圖十 選擇“數(shù)據(jù)透視表和數(shù)據(jù)透視圖”選項(xiàng)選擇“數(shù)據(jù)透視表”接下來(lái)點(diǎn)擊“下一步”即可，我們需要確定建立數(shù)據(jù)透視表的數(shù)據(jù)源區(qū)域，一般系統(tǒng)會(huì)自動(dòng)將整個(gè)信息庫(kù)區(qū)域設(shè)置成我們要選擇的區(qū)域。在出現(xiàn)“數(shù)據(jù)透視表和數(shù)據(jù)透視圖向?qū)?/p>

17、-3”第3步時(shí)我們選擇“現(xiàn)有工作表”，將工作表區(qū)域選擇在新的 sheet中A3單元格”如圖（十一）。點(diǎn)擊“完成”后，便會(huì)出現(xiàn)（圖（十二）結(jié)果。這里解釋一下我 們?yōu)槭裁匆x擇A3單元格，這主要是因?yàn)轱@示區(qū)上面有兩行用來(lái)放置頁(yè)字段。圖H一將工作表區(qū)域選擇在新的 sheet中A3單元格a> mm習(xí)葷砂 電法|盧L4所在El 字啊梗 口事業(yè) 習(xí)孑所 u學(xué)悔TILI比牛斗翼將時(shí)同 一 hE1 J' IX-人學(xué)都尊養(yǎng) K理KI要 ?;鉗昌利爐圖十二藍(lán)圈圈住的地方例如我們現(xiàn)在想統(tǒng)計(jì)各部門2003年入職人員情況。我們便可以將相關(guān)項(xiàng)目用鼠標(biāo)托至指定位置，如圖（十二）紅色箭頭顯示。通過(guò)對(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)圈圈住的地方。這