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

用Excel打造学校人事年报和人性化提醒模板
作者：张运明曾祥涌郭桂君
来源：《中国教育信息化·基础教育》2009年第10期
摘要:本文借助Excel2003,运用公式和函数建立起一套学校人事年报和人性化提醒模板,能够准确高效地完成学校人事年报,并人性化提醒教职工生日、退休日等。

关键词:Excel;人事年报;提醒模板
中图分类号:G434 文献标识码:B 文章编号:1673-8454(2009)20-0069-05
一、建立学校教职工花名册模板
准备工作包括将文件命名为:学校人事年报和人性化提醒模板;工作表分别命名为:教职工花名册、专任教师职称年龄、专任教师分课程分学历、生日及退休提醒。

1.按表1建立表头
2.设置每页显示表头
依次进入文件→页面设置,在【工作表】的【顶端标题行】中输入:$1:$4。

或者单击【顶端标题行】右侧的伸缩按钮 ,在表中拖选表头,再单击伸缩按钮。

单击【确定】,完成设置。

3.填充序号
在A5单元格中输入:1,选中A5单元格,依次进入编辑→填充→序列。

在对话框中选中序列产生在“列”,在终止值中输入:50(本例行政管理人员2人,专业技术人员46人,工勤人员2人,共50人)。

4.调整行高列宽
单击行号和列标的交汇处,选定整个工作表。

把鼠标放在行号或列标的交接处,会出现有上下箭头的图标或左右箭头的图标,拖动鼠标,按多数行的行高或多数列的列宽设置,松开鼠标键。

其他行列再作适当调整。

5.设置数据区域的特殊格式
按住【Ctrl】键,在列标上单击F、I、N、V、X,选中这5列,依次进入格式→单元格,单击【数字】选项卡,在【分类】中选中“自定义”,在其右侧的【类型】框中输入:yyyy.mm,单击【确定】。

注意:小数点只能输入减号代替。

拖选L5∶L54区域,在【类型】框中输入:@。

默认情况下,Excel每个单元格所能显示的数字为11位,超过11位的数字就会用科学计数法显示,必须将数字属性改成文本属性。

注意:必须在输入号码之前把格式设置好。

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四个区域,在【来源】中分别输入:研究生,本科,专科,高中级,高中以下;入党,入团;初中,高中;政治,语文,数学,物理,化学,生物,地理,历史,外语,信息技术,体育,音乐,美术,劳动技术,其他,当年不任课。

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

8.设置隔行着色
拖选行号5:54,依次进入格式→条件格式。

在【条件1】的下拉列框中选择【公式】,在右侧输入:=MOD(ROW(),3)=0。

单击【格式】,在【图案】选项卡中选择一种颜色。

两次【确定】,完成设置。

这样,输入数据不容易看错行。

其中,函数ROW是返回一个引用的行号,函数MOD
是返回两数相除的余数;行号除以3余数为0,就是“隔两行有一行着色”的意思。

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"&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)=1 8,DATE(MID(L5,7,4),MID(L5,11,2),MID(L5,13,2)),"")),DATE((MID($O$2,2,4)),"9","1"),"y"))。

函数DATEDIF()在Excel2003是隐匿使用的。

公式的的含义是用第二个参数减去第一个参数,第三个参数是结果“差”的单位。

第一个参数是根据身份证号码提取的日期。

第二个参数是以从
O2单元格中提取的数字作为“年”,以“9”作为月,以“1”作为日。

因为学校的学年初报表包含专任教师的统计数据,是以9月1日为界限的(注意:年度末呈报学校的教职工花名册时请将公式中的9月1日改为12月31日)。

第三个参数“y”表明返回的是整年数。

在W5单元格中输入:=IF(V5=""," ",MID($O$2,2,4)-YEAR(V5)+1)。

IF函数的第三个参数是根据工龄的计算公式(工龄=年-年+1)来设计。

“+1”表示工龄是两头算,即算虚年不算实年。

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

11.设定允许编辑区域
选定整个工作表,依次进入格式→单元格,单击【保护】选项卡,去掉【锁定】前的对勾“√”。

再拖选整个工作表设定了公式的区域,在【锁定】前打上对勾“√”。

依次进入工具→保护→保护工作表,在对话框中设定密码。

这样,可保护公式不因误操作而被破坏。

12.输入数据
为了不造成统计的遗漏,手工输入的数据要确保前后一致,如外语不能输入英语。

还要注意不要随意输入空格。

13.隐藏列
12月份年报时,选定F、G、I、J、L、V、X、Z、AA列,在列标处右击快捷菜单中选择【隐藏】。

二、建立中学专任教师职称年龄模板
1.定义名称
在“教职工花名册”工作表中,执行插入→名称→定义命令,在【在当前工作簿的名称】文本框中输入“职称”,鼠标放在【引用位置】框内,再用鼠标拖选表的G7∶G52区域,单击【添加】按钮。

这样就将G7∶G52区域定义为“职称”了。

接着重复在当前工作簿的名称→引用位置步骤,将M7∶M52、O7∶O52、P7∶P52、S7∶S52、Z7∶Z52、AA7∶AA52几个区域分别命名为:性别、年龄、民族、学历、年级、学科。

这些名称将在这个工作簿中起作用,并在下文的公式中得到使用,它们可以简化公式。

2.按表2建好统计表
3.输入公式
F10:{=SUM((学校人事年报和人性化提醒模板.xls!年级="初中")*(学校人事年报和人性化提醒模板.xls!职称="中高")*(学校人事年报和人性化提醒模板.xls!年龄
G10:{=SUM((学校人事年报和人性化提醒模板.xls!年级="初中")*(学校人事年报和人性化提醒模板.xls!职称="中高")*(学校人事年报和人性化提醒模板.xls!年龄
H10:{=SUM((学校人事年报和人性化提醒模板.xls!年级="初中")*(学校人事年报和人性化提醒模板.xls!职称="中高")*(学校人事年报和人性化提醒模板.xls!年龄
I10:{=SUM((学校人事年报和人性化提醒模板.xls!年级="初中")*(学校人事年报和人性化提醒模板.xls!职称="中高")*(学校人事年报和人性化提醒模板.xls!年龄
J10:{=SUM((学校人事年报和人性化提醒模板.xls!年级="初中")*(学校人事年报和人性化提醒模板.xls!职称="中高")*(学校人事年报和人性化提醒模板.xls!年龄
K10:{=SUM((学校人事年报和人性化提醒模板.xls!年级="初中")*(学校人事年报和人性化提醒模板.xls!职称="中高")*(学校人事年报和人性化提醒模板.xls!年龄
L10:{=SUM((学校人事年报和人性化提醒模板.xls!年级="初中")*(学校人事年报和人性化提醒模板.xls!职称="中高")*(学校人事年报和人性化提醒模板.xls!年龄
M10:{=SUM((学校人事年报和人性化提醒模板.xls!年级="初中")*(学校人事年报和人性化提醒模板.xls!职称="中高")*(学校人事年报和人性化提醒模板.xls!年龄
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!民族"汉")*(学校人事年报和人性化提醒模板.xls!性别="女"))}。

将公式填充至E14;并将E10:E14区域公式中的“民族"汉"”分别替换为:职称="中高"、职称="中一"、职称="中二"、职称="中三"、职称="未评"。

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

在E6中输入:=SUM(E7,E15)。

将此公式填充至N6。

在D6中输入:=SUM(F6:N6)。

将此公式填充至D22。

4. D6:N22区域不显示0值
有三种实现的方法。

①依次进入格式→条件格式,设置【单元格数值】“等于”“0”。

单击【格式】按钮,字体颜色选择“白色”(与底色同色)。

②执行工具→选项→视图,去掉【零值】前的对勾“√”。

③在右击的快捷菜单上单击【设置单元格格式】,执行数字→分类→自定义,在【类型】框中,键入0;-0;;@(注意键入的是英文半角字符)。

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

四、建立生日及退休提醒模板
1.按表3建好表头。

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

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.设置条件格式
为了让生日和退休的提醒更加醒目,可以设置条件格式。

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

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

参考文献:
[1]王成春,萧雅云.Excel函数应用秘笈[M].北京:中国铁道出版社,2005.
[2]雪之舫工作室.Excel函数应用实例详解[M].北京:中国铁道出版社,2004.
[3]郑小玲,梁露,赵丹亚.Excel在信息管理中的应用[M].北京:人民邮电出版社,2004.
[4]恒盛杰资讯.Excel公式、函数与图表经典[M].北京:中国青年出版社,2007.(编辑:隗爽)。