首页

excel人事年报模板

学校人事年报是学校办公室每年的常规工作,手工统计繁琐飞逝且容易出错。每逢有教职工过生日、退休之时,送出温馨的祝贺或提醒,多一些人情味,增加些凝聚力,且不很好?如能借助WPS表格,运用公式和函数建立起一套人事年报和人性化提醒模板,可以一箭双雕、一劳永逸。

准备工作:文件命名为“学校人事年报和人性化提醒模板”;工作表分别命名为“教职工花名册”“专任教师职称年龄”“专任教师分课程分学历”“生日及退休提醒”。

1.建立学校教职工花名册模板

1.1按图1建立表头。

excel人事年报模板

图1 教职工花名册

1.2设置每页显示表头。依次进入【文件→页面设置】,在【工作表】的【项端标题行】中输入“$1:$4”。或者单击【项端标题行】右侧的伸缩按钮“”,在成绩表中拖选表头,再单击伸缩按钮。单击【确定】,完成设置。

1.3填充序号。在A5单元格中输入“1”,选中A5单元格,依次进入【编辑→.填充→序列】。在对话框中选中【序列产生在“列”】,在终止值中输入“50”(本例行政管理人员2人,专业技术人员46人,工勤人员2人,共50人)。

1.4调整行高列宽。单击行号和列标的交汇处,选定整个工作表。把鼠标放在行号或列标的交接处,会出现有上下箭头的图标“”或左右箭头的图标“”,拖动鼠标,按多数行的行高或多数列的列宽设置,松开鼠标键。

1.5设置数据区域的特殊格式。

按住键盘上的【Ctrl】键,在列标上单击F、I、N、V、X,选中这5列,依次进入【格式→单元格】,单击【数字】选项卡,在【分类】中选中“自定义”,在其右侧的“类型”框中输入“yyyy.mm”,单击【确定】。注意:小数点只能输入减号代替。

拖选L5:L54区域,在“类型”框中输入“@”。在默认情况下,Excel每个单元格所能显示的数字为11位,超过11位的数字就会用科学计数法显示,必须将数字属性改成文本属性。注意:必须在输入号码之前把格式定好;如果输好号码再定格式,显示还是会不正确。

1.6设置数据有效性

设置日期区域数据的有效性。拖选F5:F54、I5:I54、V5:V54和X5:X54四个区域,依次进入【数据→有效性】,在【设置】选项卡中的【允许】列表选择【日期】,在【数据】列表中选择【大于】在【开始日期】框中输入“1948-01-01”。在【输入信息】选项卡中的【标题】框中输入“请输入:”,在【输入信息】框中输入“6位数日期,中间用“-”连接”。在【出错警告】选项卡中勾选【输入无效数据时显示出错警告】复选框,在【样式】列表中选择【停止】,在【标题】框中输入“日期错误”,在【出错信息】框中输入“请重新输入6位数日期!”。在【输入法模式】选项卡中选取【关闭(英文模式)】。单击【确定】。

设置C5:C54姓名区域数据的有效性。在【允许】列表选择中【自定义】,在【公式】框中输入“=COUNTIF(C:C,C5)=1”。然后在【输入信息】和【出错警告】选项卡的相关框中依次输入“请输入”“姓名”“姓名重复”“请检查后重新输入姓名”等提示信息。【输入法模式】选取【打开】。公式的设置是为了保证输入姓名的唯一性。

设置身份证号码区域数据的唯一性和有效性。在【公式】框中输入 “=AND(COUNTIF(L:L,L5)=1,OR(LEN(L5)=15,LEN(L5)=18))”。然后在【输入信息】和【出错警告】选项卡的相关框中依次输入“请输入:”“15或18位身份证号码”“身份证号码错误”“请检查其唯一性和位数!”。【输入法模式】选取【关闭】。其中,“COUNTIF(L:L,L5)=1”用于判断身份证号码的唯一性。“(LEN(L5)=15,LEN(L)=18))”用于限定输入的数据必须是 15位或18位。LEN函数是一个表示文本长度的函数,OR、AND函数分别是表示“或”、“和”意思的函数。

设置职称、学历和任教年级区域数据的有效性。拖选G5:G52和J5:J52区域,在【允许】列表中选择【序列】,在【来源】中输入“中高,中一,中二,中三,未评”(中间的标点符号属英文半角),勾选【提供下拉箭头】。以后要输入数据,单击单元格时就会在其右侧出现一个倒三角标志“”,单击它,将出现一个下拉列表,可用鼠标选择。同理设置S5:S54 、Y5:Y54、Z5:Z52、AA5:AA52四个区域,在【来源】中分别输入“研究生,本科,专科,高中级,高中以下”“入党,入团”“初中,高中” “政治,语文,数学,物理,化学,生物,地理,历史,外语,信息技术,体育,音乐,美术,劳动技术,其他,当年不任课”。

1.7插入批注。O2单元格的日期数据“(2009年1月1日至2009年12月30日)”与表中的公式有关,不能随意修改或删除,需要提醒使用者“此单元格只能更改年份,否则,会引起表格中公式自动计算的错误。”

1.8设置隔行着色。拖选行号5:54,依次进入【格式→条件格式】。在【条件1】的下拉列框中选择【公式】,在右侧输入=MOD(ROW(),3)=0。单击【格式】,在【图案】选项卡中选择一种颜色。两次【确定】,完成设置。隔行着色显示,便于输入数据不错行。其中,函数ROW是返回一个引用的行号,函数MOD是返回两数相除的余数;行号除以3余数为0,就是“隔两行着色”的意思。

1.9输入公式

在H5单元格中输入公式“=CONCATENATE(TEXT(F5,"yyyy.mm"),G5)”。其中,“TEXT(F5,"yyyy.mm")”是将F5的数字格式转换成文本。然后用CONCATENATE函数把F5和G5两个单元格的文本连接起来。将任职时间和任职资格分成两列,一是为了方便输入,二是为了便于分类统计。

在K5单元格中输入公式“=TEXT(I5,"yyyy.mm")&J5”,把I5和J5两个单元格的文本连接起来。

在M5和N5单元格分别输入公式“=IF(L5=""," ",IF(LEN(L5)=15,IF(MOD(MID(L5,15,1),2)=1,"男"," 女"),IF(MOD(MID(L5,17,1),2)=1,"男","女")));”“=IF(L5=""," ",IF(MID(L5,7,2)="19",DATE(MID(L5,7,4),MID(L5,11,2),MID(L5,13,2)),DATE("19"& amp;MID(L5,7,2),MID(L5,9,2),MID(L5,11,2))))”。这两个公式是根据L5单元格的身份证号码提取性别和出生日期。15位身份证号码的第7、8位代表出生年份(两位数),第9、10位代表出生月份,第11、12位代表出生日期,第15位代表性别,奇数为男,偶数为女。18位身份证号码的第7、8、9、10位代表出生年份(四位数),第11、12位代表出生月份,第13、14位代表出生日期,第17位代表性别,奇数为男,偶数为女,最后一位是校验码。

M5单元格中的公式由4个IF函数构成。第三和第四个IF函数是第二个IF函数的参数。这3个IF函数合起来又是第一个IF函数的参数。第一个 IF函数是是根据L5单元格是否为‘空’,决定下一步怎么办。如果L5单元格为‘空’,则M5单元格也为空,否则,执行第二个IF函数。公式中的 “LEN(L5)=15”是一个逻辑判断语句,LEN函数提取L5单元格中的字符长度,如果该字符的长度等于15,则执行第三个IF函数,否则就执行第四个IF函数。在第三个IF函数中,MID函数从L5的指定位置(第15位)提取1个字符,而MOD函数将该字符与2相除,获取两者的余数。如果余数是1说明条件成立,这时就会在M5单元格中填入“男”,反之则会填入“女”。如果LEN函数提取的L5单元格中的字符长度不等于15,则会执行第四个IF函数。只不过MID函数是从L5的第17位即倒数第2位提取1个字符。

N5单元格中的公式有两个IF函数。第二个IF函数是说如果发现L5单元格字符串的第7个字符串开始的连续2个字符串是‘19’,就会执行第二个参数,即日期函数DATE,否则执行第三个参数。日期函数DATE有3个参数,分别是年、月、日。

在O5单元格中输入公式“=IF(L5=""," ",DATEDIF(IF(LEN(L5)=15,DATE("19"& MID(L5,7,2),MID(L5,9,2),MID(L5,11,2)),IF(LEN(L5)=18,DATE(MID(L5,7,4),MID(L5,11,2),MID(L5,13,2)),"")),DATE((MID($O$2,2,4)),"9","1"),"y"))”。函数DATEDIF()是一个老版本的Excel粘贴函数,从Excel2000及以后的版本中无所查及,但系统一直隐匿可用,此公式的的含义是用第二个参数减去第一个参数,第三个参数是结果‘差’的单位。第一个参数是根据身份证号码提取的日期。第二个参数是以从O2单元格中提取的数字作为“年”,以 “9”作为月,以“1”作为日。因为学校的学年初报表包含专任教师的统计数据,是以9月1日为界限的。注意:年度末呈报单位的人员花名册时请将公式中的9 月1日改为12月31日。第三个参数“y”表明返回的是整年数。

在W5单元格中输入公式“=IF(V5=""," ",MID($O$2,2,4)-YEAR(V5)+1)”。IF函数的第三个参数是根据工龄的计算公式(工龄=年-年+1)来设计。“+1”是表示工龄是两头算,即算虚年不算实年。

1.10复制公式。拖选H5:W5区域,在其右下角有一个小“十”字,鼠标放在上面会出现一个大“十”字,此时拖动鼠标至W54,松开鼠标,上述公式被自动填充到应设公式的区域。

1.11设定允许编辑区域。选定整个工作表,依次进入【格式→单元格】,单击【保护】选项卡,去掉【锁定】前的对勾“√”。再拖选整个工作表设定了公式的区域,在【锁定】前打上对勾“√”。依次进入【工具→保护→保护工作表】,在对话框中设定密码。

1.12输入数据。将需手工输入的数据输入表里,注意有些数据要确保前后的一致,如“外语”学科不能输入“英语”,否则,会造成统计的遗漏。

1.13隐藏列。12月份年报时,选定F、G、I、J、L、V、X、Z、AA列,在列标的右击快捷菜单中选择【隐藏】。 2.建立专任教师职称年龄模板

2.1定义名称。在“教职工花名册”工作表中,执行【插入→名称→定义】命令,在【在当前工作簿的名称】文本框中输入“职称”,鼠标放在【引用位置】框内,再用鼠标拖选表的G7:G52区域,单击【添加】按钮。这样就将G7:G52区域定义为“职称”了。接着重复【在当前工作簿的名称→引用位置】步骤,将M7:M52、O7:O52、P7:P52、S7:S52、Z7:Z52、AA7:AA52几个区域分别命名为“性别”“年龄”“民族”“学历” “年级”“学科”。这些名称将在这个工作簿中起作用,并在下文的公式中得到使用,它们可以简化公式。

2.2按表2建好统计表。

excel人事年报模板

图2 专任教师职称年龄

2.3输入公式

F10:{=SUM((学校人事年报和人性化提醒模板.xls!年级="初中")*(学校人事年报和人性化提醒模板.xls!职称="中高")* (学校人事年报和人性化提醒模板.xls!年龄<=25))}。这是一个数组公式,是对满足条件“年级是初中、职称是中高、年龄是小于等于25岁” 的人计数。其中的大括号是数组公式的标志,不输入,在输入或复制粘贴公式后同时按下键盘上的【Ctrl+Shift+Enter】组合键,将自动产生这个标志。

G10:{=SUM((学校人事年报和人性化提醒模板.xls!年级="初中")*(学校人事年报和人性化提醒模板.xls!职称="中高")*(学校人事年报和人性化提醒模板.xls!年龄<=30))-F10}。

H10:{=SUM((学校人事年报和人性化提醒模板.xls!年级="初中")*(学校人事年报和人性化提醒模板.xls!职称="中高")*(学校人事年报和人性化提醒模板.xls!年龄<=35))-SUM(F10:G10)}。

I10:{=SUM((学校人事年报和人性化提醒模板.xls!年级="初中")*(学校人事年报和人性化提醒模板.xls!职称="中高")*(学校人事年报和人性化提醒模板.xls!年龄<=40))-SUM(F10:H10)}。

J10:{=SUM((学校人事年报和人性化提醒模板.xls!年级="初中")*(学校人事年报和人性化提醒模板.xls!职称="中高")*(学校人事年报和人性化提醒模板.xls!年龄<=45))-SUM(F10:I10) }。

K10:{=SUM((学校人事年报和人性化提醒模板.xls!年级="初中")*(学校人事年报和人性化提醒模板.xls!职称="中高")*(学校人事年报和人性化提醒模板.xls!年龄<=50))-SUM(F10:J10)}。

L10:{=SUM((学校人事年报和人性化提醒模板.xls!年级="初中")*(学校人事年报和人性化提醒模板.xls!职称="中高")*(学校人事年报和人性化提醒模板.xls!年龄<=55))-SUM(F10:K10)}。

M10:{=SUM((学校人事年报和人性化提醒模板.xls!年级="初中")*(学校人事年报和人性化提醒模板.xls!职称="中高")*(学校人事年报和人性化提醒模板.xls!年龄<=60))-SUM(F10:L10)}。

N10:{=SUM((学校人事年报和人性化提醒模板.xls!年级="初中")*(学校人事年报和人性化提醒模板.xls!职称="中高")*(学校人事年报和人性化提醒模板.xls!年龄>=61))}。

拖选F10:N10区域,拖动其右下角的填充柄至N14。单击F11单元格,按【Ctrl+H】组合键,在【查找内容】和【替换为】框中分别输入 “中高”“中一”,单击9次“替换”,鼠标进入F12单元格。同样地,将F12:N12、 F13:N13、F14:N14区域公式中的“中”高分别替换为“中二”“中三”“未评”。

拖选F10:N10区域,拖动其右下角的填充柄至N8,将F8:N8和F9:N9区域公式中的“职称="中高"”分别替换为“性别="女"”“民族<>"汉"”。

在F7输入“=SUM(F10:F14)”,将此公式填充到N7。

拖选F7:N14区域,复制,在F15单元格【粘贴】。将F15:N22区域公式中的“初中”替换为“高中”。

在E7中输入“{=SUM((学校人事年报和人性化提醒模板.xls!年级="初中")*(学校人事年报和人性化提醒模板.xls!性别="女"))”。

在E9中输入“{={SUM((学校人事年报和人性化提醒模板.xls!年级="初中")*(学校人事年报和人性化提醒模板.xls!民族& lt;>"汉")*(学校人事年报和人性化提醒模板.xls!性别="女"))}”。将公式填充至E14;并将E10:E14区域公式中的“民族& lt;>"汉"”分别替换为“职称="中高"”“职称="中一"”“职称="中二"”“职称="中三"”“职称="未评"”。

拖选E7:E14区域,将公式复制到E15:E22区域,并将公式中的‘初中”替换成“高中”。

在E6中输入“=SUM(E7,E15)”,并将此公式填充至N6。

在D6中输入“=SUM(F6:N6)”,并将此公式填充至D22。

2.4 D6:N22区域不显示0值。有三种实现的方法。①依次进入【格式→条件格式】,设置为“单元格数值”“等于”“0”。单击【格式】按钮,【字体】颜色选择“白色”(与底色同色)。②执行【工具→选项→视图】,去掉【零值】前的“对勾”。③在右击的快捷菜单上单击【设置单元格格式】,执行【数字→分类→自定义】,在【类型】框中,键入“0;-0;;@”(注意键入的是英文半角字符)。

3.建立专任教师分课程分学历模板

excel人事年报模板

图3 专任教师分课程分学历

本模板的建立过程与“专任教师职称学历模板”大致相同,这里不再赘述。

4.建立生日及退休提醒模板

4.1按表4建好表头。

excel人事年报模板

图4 生日及退休提醒

4.2引用数据。在B4 、C4、D4、E4、H4中分别输入“=教职工花名册!C5”“=教职工花名册!L5、=教职工花名册!M5”“=教职工花名册!N5”“=教职工花名册!V5”,并填充至适当位置。

4.3输入公式

在F2中输入“=TODAY()”。这个日期将随电脑系统当前日期而变化。

在F4中输入“=IF(D4=""," ",DATEDIF(E4,TODAY(),"Y"))”。这将计算出教职工自出生至“今”的“周岁”。

在G4中输入 “=IF(D14="","",IF(E14="","",IF(DATE(YEAR(TODAY()),MONTH($E14),DAY($E14))-TODAY()=0," 生日",IF(OR(DATE(YEAR(TODAY()),MONTH($E14),DAY($E14))-TODAY()=1,DATE(YEAR(TODAY()),MONTH($E14),DAY($E14))-TODAY()=2)," 准备祝贺",""))))”。公式的意思是,“今天”如果与出生日期同月同日,则显示文字“生日”;如果与出生日期相比,是同月少1天或2天,则显示文字 “准备祝贺”;否则不显示。

在I4中输入“=IF(E4=""," ",IF(C4="男",DATE(YEAR(E4)+60,MONTH(E4),DAY(E4)),DATE(YEAR(E4)+55,MONTH(E4),DAY(E4))))”,以男60岁、女55岁为标准,计算退休日期。

在J4中输入“=IF(H4=""," ",YEAR(NOW())-YEAR(H4)+1)”,计算工龄。

在K4中输入“=IF(D4=""," ",IF(C4="男",IF(F4=59,"准备退休",IF(F4>=60,"退休"," ")),IF(F4=54,"准备退休",IF(F4>=55,"退休"," "))))”。意思是,如果男的满60岁,女的满55岁,就显示文字“退休”;如果男的满59岁,女的满54岁,就显示文字“准备退休”;否则就不显示。

4.4设置条件格式。为了让生日和退休的提醒更加醒目,可以设置条件格式。拖选A4:L53区域,执行“格式→条件格式”命令,在下拉列表中选取【公式】,在框中输入“=TODAY()=DATE(YEAR(TODAY()),MONTH($E11),DAY($E11))”。再单击【格式】,在【图案】选项卡中选择粉红色。单击【添加】按钮,添加【条件2】,仿照上面操作,输入公式:“=OR(DATE(YEAR(TODAY()),MONTH($E11),DAY($E11))-TODAY()=2, DATE(YEAR(TODAY()),MONTH($E11),DAY($E11))- TODAY()=1)”,颜色选黄色。再添加【条件3】,输入“=MONTH($E11)=MONTH(TODAY())”,颜色选浅蓝色。经过设置后,当教职工生日的日期与系统当前日期相同时,单元格被填充为“粉红色”;当教职工生日的日期比系统当前日期提前1天或2天时,单元格被填充为“浅黄色”;当教职工生日的月份与系统当前月份相同时,单元格被填充为“浅蓝色”。

自此,一套多用途的自动化人事模板大功告成了。