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

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

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

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

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

5、(EE3,113,22)含義：DATTE(YYEARR,MOONTHH,DAAY);“MIID(EE3,77,4)”表示示在E33中從第第7個(gè)字字符開(kāi)始始連續(xù)取取4個(gè)字字符表示示年，用用類(lèi)似的的表示方方法一個(gè)個(gè)人的出出生年月月日便可可以通過(guò)過(guò)函數(shù)設(shè)設(shè)置表示示出來(lái)，如如果為了了看起來(lái)來(lái)方便，我我們可以以將單元元格格式式設(shè)置成成年、月月、日的的日期格格式，這這樣顯示示的結(jié)果果會(huì)非常常容易理理解。(3)年齡: =DDATEEDIFF(G33,TOODAYY(),Y)含義：“DAATEDDIF(datte1,datte2,“Y”)”表示示兩個(gè)日日期的差差值;“TOODAYY()”表表示系統(tǒng)統(tǒng)自帶的的日

6、期即即顯示當(dāng)當(dāng)日日期期;“DAATEDDIF(G3,TODDAY(),Y)”表示示今天的的日期與與G3所所表示的的出生月月日之間間的年份份差值，這這樣一個(gè)個(gè)人的年年齡就會(huì)會(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è)人入職簽訂試用期合同開(kāi)始，再到簽訂正式勞動(dòng)合同以及后來(lái)的續(xù)簽勞動(dòng)合同，一系列的日期如何能夠讓系統(tǒng)自動(dòng)生成，并且形成系統(tǒng)提前30天自動(dòng)提醒我們的功能呢？這需要對(duì)一些信

7、息項(xiàng)進(jìn)行函數(shù)設(shè)置。如圖(二)所示:圖二 信息息項(xiàng)進(jìn)行行函數(shù)設(shè)設(shè)置(1)試用期期到期時(shí)時(shí)間: =DAATE(YEAAR(PP3),MONNTH(P3)+3,DAYY(P33)-11)含義：“DAATE(YEAAR(),MOONTHH(),DAYY()”顯示示指定日日期;在這里里我們假假設(shè)試用用期為33個(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í)間: =DDATEE(YEEAR(P3)+

8、3,MONNTH(P3),DAAY(PP3)-1)我們同同樣采用用上述函函數(shù)的設(shè)設(shè)置方法法。這里里我們假假設(shè)勞動(dòng)動(dòng)合同期期限為33年，則我我們需要要設(shè)置成成YEAAR(PP3)+3，另外外這個(gè)數(shù)數(shù)值依然然以入職職日期為為計(jì)算根根據(jù)，所所以天數(shù)數(shù)上還要要設(shè)置成成DAYY(P33)-11的格式式。(3)續(xù)簽合合同到期期時(shí)間: =DDATEE(YEEAR(S3)+1,MONNTH(S3),DAAY(SS3)這里需需要注意意的是續(xù)續(xù)簽合同同計(jì)算是是以前份份合同簽簽訂到期期日期為為根據(jù)的的，所以以只在前前一份合合同到期期時(shí)間的的基礎(chǔ)上上增加11年即可可，無(wú)需需天數(shù)上上減1。(4)試用期期提前77天提醒醒

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

10、: =IFF(DAATEDDIF(TODDAY(),SS4,m)=1,該簽簽合同了了,)函數(shù)設(shè)設(shè)置方法法同上，其其含義是是兩個(gè)日日期相差差1個(gè)月月則顯示示“該簽簽合同了了”否則則不顯示示任何信信息。這這里沒(méi)有有設(shè)置成成相差330天提提醒是因因?yàn)榭紤]慮到設(shè)置置成月更更利于我我們?nèi)耸率鹿ぷ鞯牡牟僮?。同同樣需要要注意的的是不要要將顯示示“今天天日期”函函數(shù)與顯顯示“合合同到期期日期”函函數(shù)順序序顛倒。其其中mm表示示月的含含義。3、 采用用“記錄錄單”錄錄入信息息通過(guò)對(duì)對(duì)一些人人事信息息項(xiàng)進(jìn)行行函數(shù)設(shè)設(shè)置后，我我們便可可以開(kāi)始始錄入信信息了。逐逐行的鍵鍵入人事事信息，會(huì)會(huì)讓人很很快產(chǎn)生生疲勞感感，甚

11、至至?xí)霈F(xiàn)現(xiàn)串行或或輸錯(cuò)信信息的工工作失誤誤。我們們可以采采用Exxcell自帶的的“記錄錄單”功功能來(lái)解解決這個(gè)個(gè)問(wèn)題。請(qǐng)請(qǐng)點(diǎn)擊編編輯欄中中的“數(shù)數(shù)據(jù)”“記錄錄單”如如圖(三三)我們們可以用用“Taab鍵進(jìn)進(jìn)行項(xiàng)目目的換行行錄入如如圖(四四)。”圖三 記錄錄單圖四 Taab鍵進(jìn)進(jìn)行項(xiàng)目目的換行行錄入4、 用用“窗口口凍結(jié)”功功能可以以進(jìn)行簡(jiǎn)簡(jiǎn)單的數(shù)數(shù)據(jù)查詢?cè)儺?dāng)我們們錄入完完數(shù)據(jù)后后，我們們希望能能夠非常常方便的的查詢信信息，但但是由于于信息庫(kù)庫(kù)所涉及及的項(xiàng)目目很多，我我們常常常會(huì)遇到到這樣的的情況:看到左左邊的信信息又看看不到右右邊的信信息，或或者看到到下面的的信息又又不知道道此信息息所對(duì)應(yīng)

12、應(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)如圖圖(五)情況:圖五 查詢?cè)冃畔⑦@樣我我們會(huì)非非常容易易查到某某人相關(guān)關(guān)信息，但但是值得得注意的的是設(shè)置置窗口凍凍結(jié)的規(guī)規(guī)律:如如果你想想凍結(jié)第第2行請(qǐng)請(qǐng)將光標(biāo)標(biāo)放到第第3行單單元格處處，進(jìn)行行凍結(jié)窗窗口設(shè)置置。如果果你想凍凍結(jié)C列列，請(qǐng)將將光標(biāo)放放到D列列單元格格處，進(jìn)進(jìn)行凍結(jié)結(jié)窗口設(shè)設(shè)置。如如果你

13、即即想凍結(jié)結(jié)第2行行又想凍凍結(jié)C列列，請(qǐng)將將光標(biāo)放放到他們們的交叉叉單元格格D3上上進(jìn)行設(shè)設(shè)置。如如果想取取消凍結(jié)結(jié)功能可可以點(diǎn)擊擊“窗口口”菜單單選擇取取“消凍凍結(jié)窗口口”功能能。5、“自動(dòng)動(dòng)篩選”功功能可以以進(jìn)行簡(jiǎn)簡(jiǎn)單的數(shù)數(shù)據(jù)統(tǒng)計(jì)計(jì)有時(shí)我我們想非非常快速速的得到到一些數(shù)數(shù)據(jù)，比比如本公公司在職職人員中中，本科科生學(xué)歷歷的男生生有多少少人?這這時(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)別處處選擇在在職(圖圖(六)

14、，在在學(xué)歷處處選擇本本科(圖圖(七)，在在性別處處選擇男男(圖(八)，最后后用鼠標(biāo)標(biāo)將顯示示的性別別全部選選上，這這時(shí)注意意圖(九九)中用用紅筆圈圈住的地地方就是是我們所所需要的的數(shù)據(jù)了了。圖六 分別別在人員員類(lèi)別處處選擇在在職圖七 在學(xué)學(xué)歷處選選擇本科科圖八 在性性別處選選擇男圖九 紅筆筆圈住的的地方就就是我們們所需要要的數(shù)據(jù)據(jù)6、用“數(shù)數(shù)據(jù)透視視表”功功能快速速匯總各各項(xiàng)數(shù)據(jù)據(jù)以上功功能的設(shè)設(shè)置多用用于日常常人事工工作中，但但到年底底我們需需要對(duì)這這一年的的人事情情況進(jìn)行行大量的的信息總總結(jié)，比比如人員員的離職職情況，入入職情況況，各類(lèi)類(lèi)數(shù)據(jù)構(gòu)構(gòu)成比例例等。這這項(xiàng)工作作更是一一項(xiàng)非常常繁重

15、的的工作。我我們可以以利用EExceel自帶帶的“數(shù)數(shù)據(jù)透視視表”功功能為我我們排憂憂解難。請(qǐng)點(diǎn)擊擊編輯菜菜單欄中中“數(shù)據(jù)據(jù)”選項(xiàng)項(xiàng)，選擇擇“數(shù)據(jù)據(jù)透視表表和數(shù)據(jù)據(jù)透視圖圖”選項(xiàng)項(xiàng)，如圖圖(十)圖十 選擇擇“數(shù)據(jù)據(jù)透視表表和數(shù)據(jù)據(jù)透視圖圖”選項(xiàng)項(xiàng)選擇“數(shù)數(shù)據(jù)透視視表”接接下來(lái)點(diǎn)點(diǎn)擊“下下一步”即即可，我我們需要要確定建建立數(shù)據(jù)據(jù)透視表表的數(shù)據(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單元格格” 如如圖(十十一

16、)。點(diǎn)點(diǎn)擊“完完成”后后，便會(huì)會(huì)出現(xiàn)(圖(十十二)結(jié)果。這這里解釋釋一下我我們?yōu)槭彩裁匆x選擇A33單元格格，這主主要是因因?yàn)轱@示示區(qū)上面面有兩行行用來(lái)放放置頁(yè)字字段。圖十一 將將工作表表區(qū)域選選擇在新新的shheett中A33單元格格圖十二 藍(lán)藍(lán)圈圈住住的地方方例如我我們現(xiàn)在在想統(tǒng)計(jì)計(jì)各部門(mén)門(mén)20003年入入職人員員情況。我我們便可可以將相相關(guān)項(xiàng)目目用鼠標(biāo)標(biāo)托至指指定位置置，如圖圖(十二二)紅色色箭頭顯顯示。通通過(guò)對(duì)信信息項(xiàng)目目的拖拽拽，系統(tǒng)統(tǒng)會(huì)自動(dòng)動(dòng)出現(xiàn)相相關(guān)信息息的統(tǒng)計(jì)計(jì)數(shù)據(jù)。如如果我們們還想出出現(xiàn)統(tǒng)計(jì)計(jì)數(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ò)這這個(gè)功能