打造学校人事年报和人性化提醒模板

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

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

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

1.1按图1建立表头。

图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建好统计表。

图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.建立专任教师分课程分学历模板

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

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

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

4.1按表4建好表头。

图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天时，单元格被填充为“浅黄色”;当教职工生日的月份与系统当前月份相同时，单元格被填充为“浅蓝色”。

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