人事必备用EXCEL表轻松建立人事数据库样本.doc

轻松运用EXCEL建立人事信息库

接下来请您不要急着录入人员信息，咱们要对某些信息项进行函数设立，以便系统可以自动生成有关信息，这会使咱们工作产生事半功倍效果。
1、性别、出生月日、年龄自动填充功能设立
　　咱们先对“性别”“出生年月”“年龄”进行函数设立。当咱们输入某人身份证号码时，系统便会自动生成“性别”，“出生年月”及“年龄”，这样就减少了咱们录入工作量。请分别选取性别、出生月日、年龄信息项单元格输入下列公式:
　　(1)性别：=IF(MOD(IF(LEN(E3)=15,MID(E3,15,1),MID(E3,17,1)),2)=1,"男","女")
　　含义：“LEN(E3)=15”表达看E3中与否有15个字符;
　　“MID(E3,15,1)”表达在E3中从第15位开始提取1位字符;
　　“MOD(MID()，2)=1”表达提取字符除以2余数为1;
　　“IF(LEN(E3)=15,MID(E3,15,1),MID(E3,17,1)”表达看E3中与否够15个字符，如果够就从第15个字符开始取1个字符，如果不够15个字符就从第17个字符开始取1个字符。咱们身份证号码普通是15位或18位。
　　“IF(MOD(IF(LEN(E3)=15,MID(E3,15,1),MID(E3,17,1)),2)=1,"男","女"”表达所取字符除以2如果余数为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个字符开始持续取4个字符表达年，用类似表达办法一种人出生年月日便可以通过函数设立表达出来，如果为了看起来以便，咱们可以将单元格格式设立成年、月、日日期格式，这样显示成果会非常容易理解。
　　(3)年龄：=DATEDIF(G3,TODAY(),"Y")
　　含义：“DATEDIF(date1,date2,“Y”)”表达两个日期差值;
“TODAY()”表达系统自带日期即显示当天日期;
　　“DATEDIF(G3,TODAY(),"Y")”表达今天日期与G3所示出生月日之间年份差值，这样一种人年龄就会容易显示出来了。
2、劳动合同期限自动生成和提前30天定期提示功能设立
劳动合同管理也是人事管理中不可缺少一某些，特别是劳动合同到期续签问题更是不可忽视。如果由于咱们人为因素导致了劳动合同漏签、延期订立等问题也许会给公司以及员工个人带来一定损失。从一种人入职订立试用期合同开始，再到订立正式劳动合同以及日后续签劳动合同，一系列日期如何可以让系统自动生成，并且形成系统提前30天自动提示咱们功能呢？这需要对某些信息项进行函数设立。如图(二)所示:

图二 信息项进行函数设立
　　(1)试用期到期时间：=DATE(YEAR(P3),MONTH(P3)+3,DAY(P3)-1)
　　含义：“DATE(YEAR(),MONTH(),DAY())”显示指定日期;
　　在这里咱们假设试用期为3个月，咱们需要在Q3单元格中输入上述公式，其中MONTH(P3)+3表达在此人入职时间月基本上增长三个月。而DAY(P3)-1是依照劳动合同订立为全年正月而设立。例如11月6日到11月5日为一种劳动合同订立期。
　　(2)劳动合同到期时间：=DATE(YEAR(P3)+1,MONTH(P3),DAY(P3)-1)
　　咱们同样采用上述函数设立办法。这里咱们假设劳动合同期限为1年，则咱们需要设立成YEAR(P3)+1，此外这个数值依然以入职日期为计算依照，因此天数上还要设立成DAY(P3)-1格式。
　　(3)续签合同到期时间：=DATE(YEAR(S3)+1,MONTH(S3),DAY(S3))
　　这里需要注意是续签合同计算是此前份合同订立到期日期为依照，因此只在前一份合同到期时间基本上增长1年即可，无需天数上减1。
　　(4)试用期提前7天提示：=IF(DATEDIF(TODAY(),Q3,"d")=7,"试用期快结束了","")
　　这里用到了DATEDIF函数，表达两个日期差值，但是需要注意是，咱们要表达提前7天提示，因此，将TODAY()函数写到试用期时间前面即TODAY(),Q3而不能表达到Q3，TODAY()。其中“d”表达两个日期天数差值。咱们用IF()函数来表达显示规定，那么这个函数设立含义为:如果差值为7则显示“试用期快结束了”否则不显示信息，在编辑函数时用“”表达不显示任何信息。
　　(5)提前30天提示：=IF(DATEDIF(TODAY(),S4,"m")=1,"该签合同了","")
　　函数设立办法同上，其含义是两个日期相差1个月则显示“该签合同了”否则不显示任何信息。这里没有设立成相差30天提示是由于考虑到设立成月更利于咱们人事工作操作。同样需要注意是不要将显示“今天日期”函数与显示“合同到期日期”函数顺序颠倒。其中"m"表达月含义。
3、采用“记录单”录入信息
　　通过对某些人事信息项进行函数设立后，咱们便可以开始录入信息了。逐行键入人事信息，会让人不久产生疲劳感，甚至会浮现串行或输错信息工作失误。咱们可以采用Excel自带“记录单”功能来解决这个问题。请点击编辑栏中“数据”―“记录单”如图(三)咱们可以用“Tab键进行项目换行录入如图(四)。”

图三 记录单

图四 Tab键进行项目换行录入
　　4、用“窗口冻结”功能可以进行简朴数据查询
当咱们录入完数据后，咱们但愿可以非常以便查询信息，但是由于信息库所涉及项目诸多，咱们经常会遇到这样状况:看到左边信息又看不到右边信息，或者看到下面信息又不懂得此信息所相应信息项。这时咱们可以采用“窗口冻结”功能。例如我当前想保存各信息项，同步保存每个人编号、姓名、部门，让其她信息可以依照需要进行查找，这时咱们可以点击D3单元格，然后点击编辑菜单栏“窗口”，选取“冻结窗口”，就可以浮现如图(五)状况:

图五 查询信息
这样咱们会非常容易查到某人有关信息，但是值得注意是设立窗口冻结规律:如果你想冻结第2行请将光标放到第3行单元格处，进行冻结窗口设立。如果你想冻结C列，请将光标放到D列单元格处，进行冻结窗口设立。如果你即想冻结第2行又想冻结C列，请将光标放到她们交叉单元格D3上进行设立。如果想取消冻结功能可以点击“窗口”菜单选取取“消冻结窗口”功能。
5、“自动筛选”功能可以进行简朴数据记录
　　有时咱们想非常迅速得到某些数据，例如我司在职人员中，本科生学历男生有多少人?这时咱们可以采用自动筛选功能。请选取“人员类别”单元格，点击编辑菜单栏上“数据”选取“筛选”，点击“自动筛选”。在每个信息项单元格右下角都会浮现选取按钮。咱们分别在人员类别处选取在职(图(六))，在学历处选取本科(图(七))，在性别处选取男(图(八))，最后用鼠标将显示性别所有选上，这时注意图(九)中用红笔圈住地方就是咱们所需要数据了。

6、用“数据透视表”功能迅速汇总各项数据
　　以上功能设立多用于寻常人事工作中，但到年终咱们需要对这一年人事状况进行大量信息总结，例如人员离职状况，入职状况，各类数据构成比例等。这项工作更是一项非常繁重工作。咱们可以运用Excel自带“数据透视表”功能为咱们排忧解难。
　　请点击编辑菜单栏中“数据”选项，选取“数据透视表和数据透视图”选项，如图(十)

图十 选取“数据透视表和数据透视图”选项
选取“数据透视表”接下来点击“下一步”即可，咱们需要拟定建立数据透视表数据源区域，普通系统会自动将整个信息库区域设立成咱们要选取区域。在浮现“数据透视表和数据透视图向导-3”第3步时咱们选取“既有工作表”，将工作表区域选取在新sheet中A3单元格” 如图(十一)。点击完毕”后，便会浮现(图(十二))成果。这里解释一下咱们为什么要选取A3单元格，这重要是由于显示区上面有两行用来放置页字段。

图十一 将工作表区域选取在新sheet中A3单元格

图十二 蓝圈圈住地方
例如咱们当前想记录各部门入职人员状况。咱们便可以将有关项目用鼠标托至指定位置，如图(十二)红色箭头显示。通过对信息项目拖拽，系统会自动浮现有关信息记录数据。如果咱们还想浮现记录数据图示，这时咱们可以点击数据透视表编辑菜单栏上图例图标，如图(十二)蓝圈圈住地方。这时会浮现chart1，如图(十三)所示。如果咱们想变化图例显示类型可以点击图表向导进行自由选取。

图十三 完毕
　　通过这个功能咱们可以很容易记录出各种人事资料，例如学历构成，性别比例等。同步您还可以依照自己需要来设立布局。当浮现“数据透视表和数据透视图向导”第3步时，咱们可以点击“布局”按钮，接下来会浮现“数据透视表和数据透视图向导-布局”在这里咱们可以通过对话框中右侧数据按钮添加或删除咱们需要数据项。如图(十四)

图十四 记录人事资料

以上讲述是某些惯用功能设立，您还可以举一反三，加入某些好用小功能

19970124转换成1997-1-24
执行，数据，分列，下一步，下一步，在选取框中选取“日期”背面格式为YMD
最后点击完毕即可。

19970124转换成1997-01-24
1、如果日期在A1，可以在B1中输入：=MID(A1,1,4)&"-"&MID(A1,5,2)&"-"&MID(A1,7,2)
2、复制B1，选中A1，鼠标右键，选取性粘贴，数值，拟定
3、删除B1完毕操作
（注意将A1设为常规或数值格式）

1997-1-24转换成数值型格式19970124？
1、如果日期在A1，可以在B1中输入：=TEXT(A1,"yyyymmdd")
2、复制B1，选中A1，鼠标右键，选取性粘贴，数值，拟定
3、删除B1完毕操作
（设为常规或数值格式）