人事必备：用EXCEL表轻松建立人事数据库

《人事必备：用EXCEL表轻松建立人事数据库》由会员分享，可在线阅读，更多相关《人事必备：用EXCEL表轻松建立人事数据库（13页珍藏版）》请在装配图网上搜索。

1、编号：时间：2021年X月X日书山有路勤为径，学海无涯苦作舟 页码：第8页共11页轻松运用EXCEL建立人事信息库接下来请您不要急着录入人员信息，我们要对一些信息项进行函数设置，以便系统可以自动生成相关信息，这 会使我们的工作产生事半功倍的效果。1、性别、出生月日、年龄的自动填充功能设置我们先对“性别”“出生年月”“年龄”进行函数设置。当我们输入某人身份证号码时，系统便会自动生成 “性别”，“出生年月”及“年龄”，这样就减少了我们录入的工作量。请分别选择性别、出生月日、年龄信息 项单元格输入下列公式：性别:=IF(MOD(IF(LEN(E3) =15,MID(E3,15,1),MID(E3,1

2、7,1), 2)=1,男，女”)含义：“LEN(E3)=15表示看E3中是否有15个字符；“MID(睑15, 1) ”表示在E3中从第15位开始提取1位字符；MOD (MID 0, 2)=1”表示提取的字符除以2余数为1;aIF(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, D), 2)=1,男女”

3、表示所取字符除以 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(datel,date2, “Y)”表示

4、两个日期的差值;“ TODAY ()”表示系统自带的日期即显示当日日期;“DATEDIF(G3, TODAY。，Y) ”表示今天的日期与G3所表示的出生月日之间的年份差值，这样一个人的年 龄就会容易的显示出来了。2、劳动合同期限的自动生成和提前30天定期提醒功能设置劳动合同管理也是人事管理中不可缺少的一部分，特别是劳动合同到期续签问题更是不可忽视。如果由于 我们的人为原因造成了劳动合同漏签、延期签订等问题可能会给公司以及员工个人带来一定的损失。从一个人 入职签订试用期合同开始，再到签订正式劳动合同以及后来的续签劳动合同，一系列的日期如何能够让系统自S A-I12=0ATE(YEAR(P3),

5、KWTHCP3H3, DAY(P3)-1)动生成，并且形成系统提前30天自动提醒我们的功能呢？这需要对一些信息项进行函数设置。如图(二)所示:图二信息项进行函数设置(1)试用期到期时间：:DATE (YEAR (P3), MONTH (P3)+3, DAY(P3)-1)含义：a DATE (YEAR 0, MONTH 0, DAY 0) w 显示指定日期;在这里我们假设试用期为3个月，我们需要在Q3单元格中输入上述公式，其中MONTH(P3)+3表示在此人 入职时间月的基础上增加三个月。而DAY(P3)-1是根据劳动合同签订为整年正月而设置的。比如2005年11月 6日到2006年11月5日为

6、一个劳动合同签订期。(2)劳动合同到期时间：: DATE (YEAR (P3)+l, MONTH (P3), DAY(P3)-1)我们同样采用上述函数的设置方法。这里我们假设劳动合同期限为1年，则我们需要设置成YEAR(P3)+1, 另外这个数值依然以入职日期为计算根据，所以天数上还要设置成DAY(P3)T的格式。(3)续签合同到期时间：=DATE (YEAR (S3) +1, MONTH (S3), DAY (S3)这里需要注意的是续签合同计算是以前份合同签订到期日期为根据的，所以只在前一份合同到期时间的基 础上增加1年即可，无需天数上减1。(4)试用期提前7天提醒：二讦94丁口讦(10口人

7、丫()3,丁)二7,试用期快结束了”,)这里用到了 DATEDIF函数，表示两个日期差值，但是需要注意的是，我们要表示提前7天提醒，所以，将 TODAY。函数写到试用期时间前面即TODAY(),Q3而不能表示成Q3, TODAY() 0其中“d”表示两个日期天数差 值。我们用IF0函数来表示显示要求，那么这个函数设置的含义为:如果差值为7则显示“试用期快结束了”否则不显示信息，在编辑函数时用表示不显示任何信息。（5）提前30天提醒：二邛（口八丁。讦（10口底丫（），54,、）二1,”该签合同了”，）函数设置方法同上，其含义是两个日期相差1个月则显示“该签合同了“否则不显示任何信息。这里没有 设

8、置成相差30天提醒是因为考虑到设置成月更利于我们人事工作的操作。同样需要注意的是不要将显示“今 天日期”函数与显示合同到期日期”函数顺序颠倒。其中表示月的含义。3、采用“记录单”录入信息通过对一些人事信息项进行函数设置后，我们便可以开始录入信息了。逐行的键入人事信息，会让人很快 产生疲劳感，甚至会出现串行或输错信息的工作失误。我们可以采用Excel自带的“记录单”功能来解决这个 问题。请点击编辑栏中的“数据” 一 “记录单”如图（三）我们可以用“Tab键进行项目的换行录入如佟1（四）/ittfl阴，.瞬如、口直山 6,上 A凌导” “1 刍，g16 R(33 0.X第3页共11页图三记录单编号

9、：时间：2021年X月X日书山有路勤为径，学海无涯苦作舟 页码：第8页共11页第5页共11页界5Q)视的 钻入9 ISXO) XA01 dt - J 3 - X 10 S 1*W*9S50101W1*4份口号现gH1915. !(002 。纥0(H 00501007 ooe08010 on 012013014015 016.yj oi71小 F%5fit入/我U义的月人1信才户口在地事业境较0历人外日期28625 2W-6-1 2C04-6-1200571Y20053-22004-3-29I , . I JSMl/$hct2/Shcot3/262128陋3日6娜第、0。U以R .& u ； 3

10、,En . / 二图四Tab键进行项目的换行录入4、用“窗口冻结”功能可以进行简单的数据查询当我们录入完数据后，我们希望能够非常方便的查询信息，但是由于信息库所涉及的项目很多，我们常常 会遇到这样的情况:看到左边的信息又看不到右边的信息，或者看到下而的信息又不知道此信息所对应的信息 项。这时我们可以采用“窗口冻结”功能。例如我现在想保留各信息项.同时保留每个人的编号、姓名、部门， 让其他信息可以根据需要进行查找，这时我们可以点击D3单元格，然后点击编辑菜单栏的“窗口”，选择“冻 结窗口”，就可以出现如图（五）情况：Iscel - KB. 5?文件如 MO) DCD 施入9 帮五m IM (1)

11、融柔) tat) HMQ0- - ff X7- - ? X - ； I H谆俸7,10 , 1tAzLiJEJULaiajjJiLJJlLfl般H4!L34 .A BCJ_ K L 1 M N 0P【 QRT 1 Uw）：人1人手信息源2rj 科贯 段7也业权 专业学历学位人司日期玳丽加打3前？大爱/为的含曳期货间SM30XR父筌巴酎间良首”天良漏无日期人员劫洋缭部W无$ 比先幽 *_2003T-II20Q3YT02004-1-1!4A4BZCUMt+*1 嵇+OilM A 8onM 10 8OCX G XC 2200IT恒聚23 %21。虺MUSI8匕原市北京*国虹20C4-5-52aH-8

12、Y2GCS-5-520C$-$-5任职在职料备* 因津巾天津本科学士2007-720CS-10-62M/T-72007-7-7在职也F影舞T山东找济lhtw 华士200-7-7 2gdU2MsT-77007-1-7在F27 % fflrx影导23堂凝含皿1|水WJ0OI-I200X7120C4-I-I2005-1-123 即财舞” 上海印上海本科学士?O0-li20OX-1020M-1-”翔。5-1-11在距29 1。智 IrXX行现人事部蹲甫备北京本科学士20g仝抬 2004-12-27200Gs28NJM靛26在用3Q h28,制各政人事舞出北省次汉本科学士20C4-5-52aH8y2MS

13、-5-5200-5-5在配31 *0029 jfiXX行政人有那河;号北京本科学士20057?2005-10-620C6-T-72007T-7在眼行成人本部筷百杳西安9科无|2003-MO2003-4-92004-MC颔05T70踝11LL2一 一.-4u JU,Uu,Ju-U2 M、加L2fL3rtl（人制人品,人员XSariU年人员信导二空/|;里财 一日电映国）、nou& c a二辞三jL8，。 /3（ 40 粒格m用”曲式微qkf,aI”由用q “ 三 ,r c 图五查询信息这样我们会非常容易查到某人相关信息，但是值得注意的是设置窗口冻结的规律:如果你想冻结第2行请 将光标放到第3行单

14、元格处，进行冻结窗口设置。如果你想冻结C列，请将光标放到D列单元格处，进行冻结 窗口设置。如果你即想冻结第2行又想冻结C歹ij,请将光标放到他们的交叉单元格D3上进行设置。如果想取 消冻结功能可以点击“窗口”菜单选择取“消冻结窗口”功能。5、“自动筛选”功能可以进行简单的数据统计有时我们想非常快速的得到一些数据，比如本公司在职人员中，本科生学历的男生有多少人?这时我 们可以采用自动筛选功能。请选择“人员类别”单元格，点击编辑菜单栏上的“数据”选择“筛选”，点击“自 动筛选”。在每个信息项单元格右下角都会出现选择按钮。我们分别在人员类别处选择在职（图（六），在学历 处选择本科（图（七），在性别处

15、选择男（图（八），最后用鼠标将显示的性别全部选上，这时注意图（九）中用红 笔圈住的地方就是我们所需要的数据了。编号：时间：2021年x月X日书山有路勤为径，学海无涯苦作舟 页码：第7页共11页E licxoroft Eicel - HI0回区“S文神9 乐的入a眄4工及2城DIX睹状5 _ 6 X4-d J心心A “4$ 、二乜支L:，10 B 1 !】肝应草JJL缪;工单一三分，iutjU :，mvsH：jXLKV 0PQR$TVV 尊XY T1人串偌工才z隼喙语屏 :户口(Z空出箪.久由G期菊7天春.94台包网t?将请3原。已 F赛笥3味委日嬲8aR人员窄3A2522a 卜泡布北京 hit

16、” 1无产无2005-1-102003-1-10刈 AQ-9a3-q-92001-1-1020Gt-l-102CW-1-IU以06-1-1。虬Q5反轲尢备松庄一 一 B 1 A好无2003-1-1034-920M-1-IO2006-1-10流2个&式以尚需ff?千沙云2003-1-102WX-92004-1-W2M6-1-10.)74?汉天津币切 200J-I-J2CO3-3-3I20M-1-1XM 5-1-19反北京小北京博士博士2005-1-32W3-3-3I2094-1-12WAI-I依里26QS-M206-1-112屈务28X .1A市上海I 工格学士 20M-9-28胸7202005

17、3北25汉北方*格华士20MB5M-8-4?4汉湖LG*科*2005-T-TKO5-I0-6200257O30-T-7夕55K?-7-7第6页共11页1522反西文*学士2ggM2W5-10-62gi7理1622汉北方市北方才解学2003-1-12W3-3-3I20M-M在JR1730天律而租祎转2003-1-132CW-4-102。8十II2005-1-11在取13笠汉河妗石猿正*移学士20MT物2OM-12-2T2005902gs% 28夜W1$X .&山尊令济南雨2004-5-5C4-8-420DM-5KW 6-3-5钎2022反刘四川学土2gIT2W5T0-6200i7itn?-?-?

18、理2122汉送南*神2005-7-72CO5H0-6200-1-72W?-7-7在取222?汉Q本系200J-I-3式87引2W20041。住EHM 人 WM/0人*格色之微.G）蓼事遇d2 g（5#京遏neiE再名jfiK塾）干2003-1-10狗OtlTO20037-9- 一 一 4二 N。*ba)2booiWxxTHAIKS睫理M 士t200S-1-I283,7004-1-1 xr s*垣部N品蛀城st F4磺士200!-l2CO3-3!M-l-l马分刀e 士200S-l2832312004-1-L科, B|XI研庶部些目Ttt阱乂3中4r200N-12 til2aB 分 31283 4

19、-3020G4-12-77史史11OCTW)010 加n 河2研刈XflWJm 川加(p 1XT-t2QO4-A2B?。，*x_ rxx5及募工行和2X过川南参:igCM r2004-5-6WOO-tr-6而血HTWW2S4” 13gl6NSCMh攵ii*e8nXn上堂3MT*200M-T2867662000-T-T*xx研友的MbtJCM公K2S4*gl 6女 .-na ;o住N-E女於T200i7267gJ加十7皿见女郎-Elrtf_2X36I9MOI2，25女1?03工】j月win l分曲以24 （J，Xa“斤B，天布天亦W不JL Z。 】“J 200l-ll2S3T70W0TlM47

20、TLUU13fbois行如HXtlfP 工程界人5。195九 13 jXOdlOMh”药.198*11 M，4 日l”3军11月X日lB震同也病|*M200l-ll2CO3-4-307CC4-1-11M铲JAweswtiiRNB22双院百育西安科*2W2BRa-l2？72005-P-how仍仰$ u*5i-JiLJE2XW19B，12460193赤】J月Nd22次而T2001-5-5ROC-QRGSKitnyT2 乂 661 如 H240725攵198汪11月翻口次天 it It“检*7t200&-7-T28nwce-T-? luv， l.ae. m feeXlHutf2iZ 5JtX5/一

21、m a3I.UU1A10 XZ用47青立技匚今州却极襄今次员a明南号J抄。之 g图十选择“数据透视表和数据透视图”选项编号：时间：2021年X月X日书山有路勤为径，学海无涯苦作舟 页码：第8页共11页选择“数据透视表”接下来点击“下一步”即可，我们需要确定建立数据透视表的数据源区域，一般系统 会自动将整个信息库区域设置成我们要选择的区域在出现“数据透视表和数据透视图向导-3”第3步时我们 选择“现有工作表”，将工作表区域选择在新的sheet中A3单元格”如图（十一）。点击“完成”后，便会出 现（图（十二）结果。这里解释一下我们为什么要选择A3单元格，这主要是因为显示区上而有两行用来放置页字段。

22、图十一将工作表区域选择在新的sheet中A3单元格Elicwpft Excel1 时博9巴又伴9 璃011奥图W 独人3 踪物 工具 刈J5 剪叩 利防卸段九森美做0；可费 . S x;一 d j二州二力二/ g 工。a回切匕/体雅日/ u ，与与包月 ，力勿拿注一力a。Z上祖 ，紧J雷书!一期 gB4& 2003-1-1AB 1 C 一 . 一， ,DgF 1 IJ 1Kl Ll - lNlOl -1 _ 心-二二二二;类求米钝去 ，乂妻毒 丁mwwm r 1 irjf小 18 三一 不二3廿魏项:入司日期入司日期R4 snf20C6-1-J1 2W3-3-10SOttMTl忌计S研发郃1

23、113 桐（u*sjs*遢n*萩k6行政人人事1i *LJ峭包郃11113 口年龄48醐部z 2J民族g傩郃i!i1q 口施贯10方务而i1口所砂11总计74口毕业院校| 口专业Id学历学R、入司日期1（试用羽列斯时间7i砺卸械练y2324252627282g30业，M Sheet 1 Zret2/Chxr11IJ图十二蓝圈圈住的地方例如我们现在想统计各部门2003年入职人员情况。我们便可以将相关项目用鼠标托至指定位置，如图（十 二）红色箭头显示。通过对信息项目的拖拽，系统会自动出现相关信息的统计数据。如果我们还想出现统计数 据的图示，这时我们可以点击数据透视表编辑菜单栏上的图例图标，如图（十

24、二）蓝圈圈住的地方。这时会出现 chart b如图（十三）所示。如果我们想改变图例显示类型可以点击图表向导进行自由选择，第9页共11页编号：时间：2。21年X月X日书山有路勤为径，学海无涯苦作舟 页码：第13页共11页冷劫根豪字黄女渍 X印50纭三8携潮s计数项:入司m期可, 威彼贯非屿立历位司和 年民篇户毕专学学入试冽i西）,主乐图十三完成通过这个功能我们可以很容易的统计出各种人事资料，比如学历构成，性别比例等。同时您还可以根据自 己的需要来设置布局。当出现“数据透视表和数据透视图向导”第3步时，我们可以点击“布局”按钮，接下 来会出现“数据透视表和数据透视图向导-布局”在这里我们可以通过对

25、话框中右侧的数据按钮添加或删除我 们需要的数据项。如图（十四）图十四统计人事资料以上讲述的是一些常用功能的设置，您还可以举一反三，加入一些好用的小功能19970124 转换成 1997-1-24执行，数据，分列，下一步，下一步，在选择框中选择“日期”后面格式为YMD最后点击完成即可。19970124 转换成 1997-01-241、假如日期在 A1,可以在 B1 中输入:=MID(A1,1, 4)F-，Z&MID(A1, 5, 2)F-，Z&MID(A1, 7, 2)2、复制Bl,选中Al,鼠标右键，选择性粘贴，数值，确定3、删除B1完成操作(注意将A1设为常规或数值格式)1997-1-24转换成数值型格式19970124?1、假如日期在A1,可以在B1中输入:=TEXT(Al,z，yyyymmddz，)2、复制Bl,选中Al,鼠标右键，选择性粘贴，数值，确定3、删除B1完成操作（设为常规或数值格式）第n贞共11页