学校人事年报是学校办公室每年的常规工作，手工统计繁琐飞逝且容易出错。每逢有教职工过生日、退休之时，送出温馨的祝贺或提醒，多一些人情味，增加些凝聚力，且不很好?如能借助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”是表示工龄是两头算，即算虚年不算实年