excel實(shí)用技巧建立人力資源系統(tǒng)

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

2、通過(guò)EExceel來(lái)建建立一套套小型人人事數(shù)據(jù)據(jù)管理系系統(tǒng)。 正文：首先先我們打打開(kāi)一個(gè)個(gè)新的EExceel表建建立一個(gè)個(gè)人事信信息庫(kù)框框架，信信息項(xiàng)目目的設(shè)置置您可根根據(jù)本單單位實(shí)際際情況而而定。如如圖(一一)所示示:圖一 打打開(kāi)一個(gè)個(gè)新的EExceel表建建立一個(gè)個(gè)人事信信息庫(kù)框框架接下下來(lái)請(qǐng)您您不要急急著錄入入人員信信息，我我們要對(duì)對(duì)一些信信息項(xiàng)進(jìn)進(jìn)行函數(shù)數(shù)設(shè)置，以以便系統(tǒng)統(tǒng)可以自自動(dòng)生成成相關(guān)信信息，這這會(huì)使我我們的工工作產(chǎn)生生事半功功倍的效效果。1、 性別別、出生生月日、年齡的的自動(dòng)填填充功能能設(shè)置我們們先對(duì)“性性別”“出出生年月月”“年年齡”進(jìn)進(jìn)行函數(shù)數(shù)設(shè)置。當(dāng)我們們輸入某某人身份

3、份證號(hào)碼碼時(shí)，系系統(tǒng)便會(huì)會(huì)自動(dòng)生生成“性性別”，“出出生年月月”及“年年齡”，這這樣就減減少了我我們錄入入的工作作量。請(qǐng)請(qǐng)分別選選擇性別別、出生生月日、年齡信信息項(xiàng)單單元格輸輸入下列列公式:(11)性別別: =IF(MODD(IFF(LEEN(EE3)=15,MIDD(E33,155,1),MIID(EE3,117,11),2)=1,男,女)含義義：“LLEN(E3)=155”表示示看E33中是否否有155個(gè)字符符;“MMID(E3,15,1)”表表示在EE3中從從第155位開(kāi)始始提取11位字符符;“MMOD(MIDD()，22)=11”表示示提取的的字符除除以2余余數(shù)為11;“IIF(LLE

4、N(E3)=155,MIID(EE3,115,11),MMID(E3,17,1)”表表示看EE3中是是否夠115個(gè)字字符，如如果夠就就從第115個(gè)字字符開(kāi)始始取1個(gè)個(gè)字符，如如果不夠夠15個(gè)個(gè)字符就就從第117個(gè)字字符開(kāi)始始取1個(gè)個(gè)字符。我們的的身份證證號(hào)碼一一般是115位或或18位位?！癐IF(MMOD(IF(LENN(E33)=115,MMID(E3,15,1),MIDD(E33,177,1),22)=11,男男,女”表表示所取取字符除除以2如如果余數(shù)數(shù)為1顯顯示男，否否則顯示示女。(22)出生生年月: =DDATEE(MIID(EE3,77,4),MIID(EE3,111,22),MMI

5、D(E3,13,2)含義義：DAATE(YEAAR,MMONTTH,DDAY);“MMID(E3,7,44)”表表示在EE3中從從第7個(gè)個(gè)字符開(kāi)開(kāi)始連續(xù)續(xù)取4個(gè)個(gè)字符表表示年，用用類(lèi)似的的表示方方法一個(gè)個(gè)人的出出生年月月日便可可以通過(guò)過(guò)函數(shù)設(shè)設(shè)置表示示出來(lái)，如如果為了了看起來(lái)來(lái)方便，我我們可以以將單元元格格式式設(shè)置成成年、月月、日的的日期格格式，這這樣顯示示的結(jié)果果會(huì)非常常容易理理解。(33)年齡齡: =DATTEDIIF(GG3,TTODAAY(),YY)含義義：“DDATEEDIFF(daate11,daate22,“YY”)”表表示兩個(gè)個(gè)日期的的差值;“TTODAAY()”表示示系統(tǒng)自自

6、帶的日日期即顯顯示當(dāng)日日日期;“DDATEEDIFF(G33,TOODAYY(),Y)”表表示今天天的日期期與G33所表示示的出生生月日之之間的年年份差值值，這樣樣一個(gè)人人的年齡齡就會(huì)容容易的顯顯示出來(lái)來(lái)了。2、 勞勞動(dòng)合同同期限的的自動(dòng)生生成和提提前300天定期期提醒功功能設(shè)置置勞動(dòng)動(dòng)合同管管理也是是人事管管理中不不可缺少少的一部部分，特特別是勞勞動(dòng)合同同到期續(xù)續(xù)簽問(wèn)題題更是不不可忽視視。如果果由于我我們的人人為原因因造成了了勞動(dòng)合合同漏簽簽、延期期簽訂等等問(wèn)題可可能會(huì)給給公司以以及員工工個(gè)人帶帶來(lái)一定定的損失失。從一一個(gè)人入入職簽訂訂試用期期合同開(kāi)開(kāi)始，再再到簽訂訂正式勞勞動(dòng)合同同以及后后

7、來(lái)的續(xù)續(xù)簽勞動(dòng)動(dòng)合同，一一系列的的日期如如何能夠夠讓系統(tǒng)統(tǒng)自動(dòng)生生成，并并且形成成系統(tǒng)提提前300天自動(dòng)動(dòng)提醒我我們的功功能呢？這需要要對(duì)一些些信息項(xiàng)項(xiàng)進(jìn)行函函數(shù)設(shè)置置。如圖圖(二)所示:圖二 信信息項(xiàng)進(jìn)進(jìn)行函數(shù)數(shù)設(shè)置(11)試用用期到期期時(shí)間: =DDATEE(YEEAR(P3),MOONTHH(P33)+33,DAAY(PP3)-1)含義義：“DDATEE(YEEAR(),MMONTTH(),DAAY()”顯顯示指定定日期;在這這里我們們假設(shè)試試用期為為3個(gè)月月，我們們需要在在Q3單單元格中中輸入上上述公式式，其中中MONNTH(P3)+3表表示在此此人入職職時(shí)間月月的基礎(chǔ)礎(chǔ)上增加加三個(gè)月

8、月。而DDAY(P3)-1是是根據(jù)勞勞動(dòng)合同同簽訂為為整年正正月而設(shè)設(shè)置的。比如220055年111月6日日到20006年111月55日為一一個(gè)勞動(dòng)動(dòng)合同簽簽訂期。(22)勞動(dòng)動(dòng)合同到到期時(shí)間間: =DATTE(YYEARR(P33)+33,MONNTH(P3),DAAY(PP3)-1)我們們同樣采采用上述述函數(shù)的的設(shè)置方方法。這這里我們們假設(shè)勞勞動(dòng)合同同期限為為3年，則我我們需要要設(shè)置成成YEAAR(PP3)+3，另外外這個(gè)數(shù)數(shù)值依然然以入職職日期為為計(jì)算根根據(jù)，所所以天數(shù)數(shù)上還要要設(shè)置成成DAYY(P33)-11的格式式。(33)續(xù)簽簽合同到到期時(shí)間間: =DATTE(YYEARR(S33

9、)+11,MOONTHH(S33),DDAY(S3)這里里需要注注意的是是續(xù)簽合合同計(jì)算算是以前前份合同同簽訂到到期日期期為根據(jù)據(jù)的，所所以只在在前一份份合同到到期時(shí)間間的基礎(chǔ)礎(chǔ)上增加加1年即即可，無(wú)無(wú)需天數(shù)數(shù)上減11。(44)試用用期提前前7天提提醒: =IFF(DAATEDDIF(TODDAY(),QQ3,d)=7,試用用期快結(jié)結(jié)束了,)這里里用到了了DATTEDIIF函數(shù)數(shù)，表示示兩個(gè)日日期差值值，但是是需要注注意的是是，我們們要表示示提前77天提醒醒，所以以，將TTODAAY()函數(shù)寫(xiě)寫(xiě)到試用用期時(shí)間間前面即即TODDAY(),QQ3而不不能表示示成Q33，TOODAYY()。其中“d

10、d”表示示兩個(gè)日日期天數(shù)數(shù)差值。我們用用IF()函數(shù)數(shù)來(lái)表示示顯示要要求，那那么這個(gè)個(gè)函數(shù)設(shè)設(shè)置的含含義為:如果差差值為77則顯示示“試用用期快結(jié)結(jié)束了”否否則不顯顯示信息息，在編編輯函數(shù)數(shù)時(shí)用“”表表示不顯顯示任何何信息。(55)提前前30天天提醒: =IIF(DDATEEDIFF(TOODAYY(),S4,m)=11,該該簽合同同了,)函數(shù)數(shù)設(shè)置方方法同上上，其含含義是兩兩個(gè)日期期相差11個(gè)月則則顯示“該該簽合同同了”否否則不顯顯示任何何信息。這里沒(méi)沒(méi)有設(shè)置置成相差差30天天提醒是是因?yàn)榭伎紤]到設(shè)設(shè)置成月月更利于于我們?nèi)巳耸鹿ぷ髯鞯牟僮髯鳌Ｍ瑯訕有枰⒆⒁獾氖鞘遣灰獙@示“今今天日期期”

11、函數(shù)數(shù)與顯示示“合同同到期日日期”函函數(shù)順序序顛倒。其中m表表示月的的含義。3、 采采用“記記錄單”錄錄入信息息通過(guò)過(guò)對(duì)一些些人事信信息項(xiàng)進(jìn)進(jìn)行函數(shù)數(shù)設(shè)置后后，我們們便可以以開(kāi)始錄錄入信息息了。逐逐行的鍵鍵入人事事信息，會(huì)會(huì)讓人很很快產(chǎn)生生疲勞感感，甚至至?xí)霈F(xiàn)現(xiàn)串行或或輸錯(cuò)信信息的工工作失誤誤。我們們可以采采用Exxcell自帶的的“記錄錄單”功功能來(lái)解解決這個(gè)個(gè)問(wèn)題。請(qǐng)點(diǎn)擊擊編輯欄欄中的“數(shù)數(shù)據(jù)”“記錄錄單”如如圖(三三)我們們可以用用“Taab鍵進(jìn)進(jìn)行項(xiàng)目目的換行行錄入如如圖(四四)?！眻D三 記記錄單圖四 TTab鍵鍵進(jìn)行項(xiàng)項(xiàng)目的換換行錄入入4、 用“窗窗口凍結(jié)結(jié)”功能能可以進(jìn)進(jìn)行簡(jiǎn)單單

12、的數(shù)據(jù)據(jù)查詢(xún)當(dāng)我我們錄入入完數(shù)據(jù)據(jù)后，我我們希望望能夠非非常方便便的查詢(xún)?cè)冃畔?，但但是由于于信息?kù)庫(kù)所涉及及的項(xiàng)目目很多，我我們常常常會(huì)遇到到這樣的的情況:看到左左邊的信信息又看看不到右右邊的信信息，或或者看到到下面的的信息又又不知道道此信息息所對(duì)應(yīng)應(yīng)的信息息項(xiàng)。這這時(shí)我們們可以采采用“窗窗口凍結(jié)結(jié)”功能能。例如如我現(xiàn)在在想保留留各信息息項(xiàng)，同同時(shí)保留留每個(gè)人人的編號(hào)號(hào)、姓名名、部門(mén)門(mén)，讓其其他信息息可以根根據(jù)需要要進(jìn)行查查找，這這時(shí)我們們可以點(diǎn)點(diǎn)擊D33單元格格，然后后點(diǎn)擊編編輯菜單單欄的“窗窗口”，選選擇“凍凍結(jié)窗口口”，就就可以出出現(xiàn)如圖圖(五)情況:圖五 查查詢(xún)信息息這樣樣我們會(huì)會(huì)非常

13、容容易查到到某人相相關(guān)信息息，但是是值得注注意的是是設(shè)置窗窗口凍結(jié)結(jié)的規(guī)律律:如果果你想凍凍結(jié)第22行請(qǐng)將將光標(biāo)放放到第33行單元元格處，進(jìn)進(jìn)行凍結(jié)結(jié)窗口設(shè)設(shè)置。如如果你想想凍結(jié)CC列，請(qǐng)請(qǐng)將光標(biāo)標(biāo)放到DD列單元元格處，進(jìn)進(jìn)行凍結(jié)結(jié)窗口設(shè)設(shè)置。如如果你即即想凍結(jié)結(jié)第2行行又想凍凍結(jié)C列列，請(qǐng)將將光標(biāo)放放到他們們的交叉叉單元格格D3上上進(jìn)行設(shè)設(shè)置。如如果想取取消凍結(jié)結(jié)功能可可以點(diǎn)擊擊“窗口口”菜單單選擇取取“消凍凍結(jié)窗口口”功能能。5、“自自動(dòng)篩選選”功能能可以進(jìn)進(jìn)行簡(jiǎn)單單的數(shù)據(jù)據(jù)統(tǒng)計(jì)有時(shí)時(shí)我們想想非?？炜焖俚牡玫玫揭恍┬?shù)據(jù)，比比如本公公司在職職人員中中，本科科生學(xué)歷歷的男生生有多少少人?這這

14、時(shí)我們們可以采采用自動(dòng)動(dòng)篩選功功能。請(qǐng)請(qǐng)選擇“人人員類(lèi)別別”單元元格，點(diǎn)點(diǎn)擊編輯輯菜單欄欄上的“數(shù)數(shù)據(jù)”選選擇“篩篩選”，點(diǎn)點(diǎn)擊“自自動(dòng)篩選選”。在在每個(gè)信信息項(xiàng)單單元格右右下角都都會(huì)出現(xiàn)現(xiàn)選擇按按鈕。我我們分別別在人員員類(lèi)別處處選擇在在職(圖圖(六)，在在學(xué)歷處處選擇本本科(圖圖(七)，在在性別處處選擇男男(圖(八)，最后后用鼠標(biāo)標(biāo)將顯示示的性別別全部選選上，這這時(shí)注意意圖(九九)中用用紅筆圈圈住的地地方就是是我們所所需要的的數(shù)據(jù)了了。圖六 分分別在人人員類(lèi)別別處選擇擇在職圖七 在在學(xué)歷處處選擇本本科圖八 在在性別處處選擇男男圖九 紅紅筆圈住住的地方方就是我我們所需需要的數(shù)數(shù)據(jù)6、用“數(shù)數(shù)據(jù)

15、透視視表”功功能快速速匯總各各項(xiàng)數(shù)據(jù)據(jù)以上上功能的的設(shè)置多多用于日日常人事事工作中中，但到到年底我我們需要要對(duì)這一一年的人人事情況況進(jìn)行大大量的信信息總結(jié)結(jié)，比如如人員的的離職情情況，入入職情況況，各類(lèi)類(lèi)數(shù)據(jù)構(gòu)構(gòu)成比例例等。這這項(xiàng)工作作更是一一項(xiàng)非常常繁重的的工作。我們可可以利用用Exccel自自帶的“數(shù)數(shù)據(jù)透視視表”功功能為我我們排憂(yōu)憂(yōu)解難。請(qǐng)點(diǎn)點(diǎn)擊編輯輯菜單欄欄中“數(shù)數(shù)據(jù)”選選項(xiàng)，選選擇“數(shù)數(shù)據(jù)透視視表和數(shù)數(shù)據(jù)透視視圖”選選項(xiàng)，如如圖(十十)圖十 選選擇“數(shù)數(shù)據(jù)透視視表和數(shù)數(shù)據(jù)透視視圖”選選項(xiàng)選擇擇“數(shù)據(jù)據(jù)透視表表”接下下來(lái)點(diǎn)擊擊“下一一步”即即可，我我們需要要確定建建立數(shù)據(jù)據(jù)透視表表的數(shù)

16、據(jù)據(jù)源區(qū)域域，一般般系統(tǒng)會(huì)會(huì)自動(dòng)將將整個(gè)信信息庫(kù)區(qū)區(qū)域設(shè)置置成我們們要選擇擇的區(qū)域域。在出出現(xiàn)“數(shù)數(shù)據(jù)透視視表和數(shù)數(shù)據(jù)透視視圖向?qū)?dǎo)-3”第第3步時(shí)時(shí)我們選選擇“現(xiàn)現(xiàn)有工作作表”，將將工作表表區(qū)域選選擇在新新的shheett中A33單元格格” 如如圖(十十一)。點(diǎn)擊“完完成”后后，便會(huì)會(huì)出現(xiàn)(圖(十十二)結(jié)果。這里解解釋一下下我們?yōu)闉槭裁匆x擇AA3單元元格，這這主要是是因?yàn)轱@顯示區(qū)上上面有兩兩行用來(lái)來(lái)放置頁(yè)頁(yè)字段。圖十一 將工作作表區(qū)域域選擇在在新的ssheeet中AA3單元元格圖十二 藍(lán)圈圈圈住的地地方例如如我們現(xiàn)現(xiàn)在想統(tǒng)統(tǒng)計(jì)各部部門(mén)20003年年入職人人員情況況。我們們便可以以將相關(guān)關(guān)項(xiàng)目用用鼠標(biāo)托托至指定定位置，如如圖(十十二)紅紅色箭頭頭顯示。通過(guò)對(duì)對(duì)信息項(xiàng)項(xiàng)目的拖拖拽，系系統(tǒng)會(huì)自自動(dòng)出現(xiàn)現(xiàn)相關(guān)信信息的統(tǒng)統(tǒng)計(jì)數(shù)據(jù)據(jù)。如果果我們還還想出現(xiàn)現(xiàn)統(tǒng)計(jì)數(shù)數(shù)據(jù)的圖圖示，這這時(shí)我們們可以點(diǎn)點(diǎn)擊數(shù)據(jù)據(jù)透視表表編輯菜菜單欄上上的圖例例圖標(biāo)，如如圖(十十二)藍(lán)藍(lán)圈圈住住的地方方。這時(shí)時(shí)會(huì)出現(xiàn)現(xiàn)chaart11，如圖圖(十三三)所示示。如果果我們想想改變圖圖例顯示示類(lèi)型可可以點(diǎn)擊擊圖表向向?qū)нM(jìn)行行自由選選擇。圖十三 完成通過(guò)過(guò)