论文部分内容阅读
使用Excel进行人事信息管理，具有无须编程、简单易行的特点，在各行各业中都能得到广泛的应用。学校的人事信息有其自身的特点，巧妙地运用Excel及其函数，可以自动产生学号、选择专业、利用身份证号码的信息获得学生的出生年月、性别和生源省份等信息，减少了工作量，能有效地保证其正确性。
对学校来说，每年的新生报到，我们都要对每位学生的信息进行注册，如学号、姓名、专业、政治面貌、生源地（省份）、出生年月、性别、身份证号码等等。对一所上万人的学校而言，工作量之大可想而知。如何有效地减少学生信息的输入工作量，而又要尽量保证不出差错？我为学校学生管理处设计了如下一套方案。现介绍给大家，希望对从事相关学校的人事信息管理工作的人员有所帮助。
一、自动产生学号
学号往往由学校代码和专业代码加年号，再加流水号组成，如‘3602080001’，‘3625080012’等，前面的‘360208’是学校和专业代码加年号，这些是固定的，后面‘0001’，‘0002’是流水号，这样复杂的学号，要自动产生就不能用EXCEL的“自定义序列”来实现，而要采用特殊的方法。具体操作方法如下：
1) 在“学生管理表”中选中“学号”单元格列，单击“格式/单元格”命令，打开“单元格格式”对话框，选“数字”标签中的分类项中选“自定义”在类型中输入“"360208"0000”，
2) 在A2单元格中输入“=IF(B2=""，""，COUNTA($B$2:B2))”，然后把鼠标移到A2单元格的右下方，鼠标变成十字形状时，按下左键拖拽填充到A列下面的单元格中，这样在B列输入姓名后，A列中就会自动输入序号，如“3602080001”，“3602080002”，“3602080003”……。如图1所示。
图1自动产生序号
二、选择输入专业和政治面貌
一般专业和政治面貌和种类都不会很多，EXCEL虽然有记忆功能，但至少也要输入一些汉字，才可选择所需要的，下面介绍的是在另外一张工作表中输入好专业和政治面貌，就可能单元格后的倒三角，用下拉框来选择。具体操作方法如下：
1) 在同一工作簿中选sheet2工作表，在其中输入专业、政治面貌（党员、团员、群众）的内容。
2) 再选择A2：A14，单击“插入/名称/定义”命令，打开“名称定义”对话框，在当前工作簿名称中输入“专业”，在引用位置中输入“=OFFSET(Sheet2!$A$2，，，COUNTA(Sheet2!$A:$A)-1)”，单击“添加”按钮。
同样，再选择B2：B4，在“名称定义”中输入“政治面貌”，引用位置中输入“=OFFSET(Sheet2!$B$2，，，COUNTA(Sheet2!$B:$B)-1)”，再单击“添加”按钮即可。
3) 选中“专业”单元格列，单击“数据/有效性”命令，打开“数据有效性”对话框，选择“设置”标签中的有效性条件中允许中选“序列”，来源中输入“=专业”。
同样，选中“政治面貌”单元格列，再在“数据有效性”对话框的有效性中允许中选“序列”，来源中输入“=政治面貌”，再单击“确定”按钮。
4）在Sheet1的“学生管理表”中输入各学生的专业和政治面貌时，只要点击对应的单元格右侧的倒三角就可打开下拉框，从其中选择所需要的专业和政治面貌即可。如图2所示。
图2 下拉框选取专业
三、从身份证号码中提取生源地（省份）、出生年月、性别信息
学生的生源地或所在的省市县、出生年月或年龄和性别信息暂不用直接输入，待身份证号码输入完成后，利用EXCEL相关函数从身份证号码中提取。
1) 学生的生源地信息，可不用直接输入，身份证号码的前二位就表示了此信息，因此可从中直接提取。
具体操作方法为：先选择E2单元格，输入“=IF(MID(H2，1，2)<="63"，CHOOSE(IF(MID(H2，1，2)<="15"，MID(H2，1，2)-10，IF(MID(H2，1，2)<="23"，MID(H2，1，2)-15，IF(MID(H2，1，2)<="37"，MID(H2，1，2)-22，IF(MID(H2，1，2)<="46"， MID(H2，1，2)-25，IF(MID(H2，1，2)<="54"， MID(H2，1，2)-28，MID(H2，1，2)-34)))))，"北京"，"天津"，"河北"，"山西"，"内蒙古"，"辽宁"，"吉林"，"黑龙江"，"上海"，"江苏"，"浙江"，"安徽"，"福建"，"江西"，"山东"，"河南"，"湖北"，"湖南"，"广东"，"广西"，"海南"，"重庆"，"四川"，"贵州"，"云南"，"西藏"，"陕西"，"甘肃"，"青海")，IF(MID(H2，1，2)="64"，"宁夏"，"新疆"))”，再向下拖动填充柄，将公式复制到E列其它单元格即可。
CHOOSE（index_num，value1，value2，…）的语法是根据给定的索引值，从参数串中选出相应值或操作，但最多只能选取二十九个相应值或操作。由于我国除港澳台外，有三十一个省市自治区，所以采用IF嵌套来处理。
若生源地需要更为详细的省、市、县信息，身份证号码的前六位表示了此类信息，可以用VLOOKUP函数从代码表中建立链接来获取。大概做法是，先在国家统计局网站上下载最新的城市代码表，通过整理成EXCEL工作表Sheet3。
然后，在E2单元格中输入“=VLOOKUP(LEFT(H2，6)，Sheet3!$A$2:$B$3522，2，FALSE”，再将鼠标移到H2单元格的右下方，鼠标变成十字形状时，按下左键拖拽填充到H列下面的单元格中即可。
2) 学生的出生年月信息，第一代15位的身份证号码第七位到第十位（第二代18位的身份证号码第七位到第十二位）表示了此类信息，可从中直接提取。
具体操作方法为：先选择F2单元格，输入“=IF（LEN（H2）=15，CONCATENATE（"19"，MID（H2，7，2），"年"，MID（H2，9，2），"月"），CONCATENATE（MID（H2，7，4），"年"，MID（H2，11，2），"月"））”，再向下拖动填充柄，将公式复制到F列其它单元格即可。
如果要求学生的年龄信息，可以F2单元格中输入“=YEAR (NOW())-IF(LEN(B2)=15，CONCATENATE("19"，MID(B2，7，2))，CONCATENATE(MID(B2，7，4)))”， 再向下拖动填充柄，将公式复制到F列其它单元格中即可。
3) 学生的性别信息，第一代15位的身份证号码第十五位（第二代18位的身份证号码第十七位）表示了此类信息，后直接生成。
具体操作方法为：先选择G2单元格，输入“=IF（LEN（H2）=15，IF（MOD（MID（H2，15，1），2）=1，"男"，"女"），IF（MOD（MID（H2，17，1），2）=1，"男"，"女"））”，再向下拖动填充柄，将公式复制到G列其它单元格即可。
四、总结
使用Excel进行人事信息管理，具有无须编程、简单易行的特点，在各行各业中都能得到广泛的应用。本文利用人事信息自身的特点，巧妙地利用Excel及其函数，所之自动产生学号，可选择专业，利用身份证号码的信息获得学生的出生年月、性别和生源省份等信息，减少了人事信息的输入量，有效地保证了其正确性。希望对从事人事统计管理的人员有所帮助，增强其办事效果，提高工作能力。
对学校来说，每年的新生报到，我们都要对每位学生的信息进行注册，如学号、姓名、专业、政治面貌、生源地（省份）、出生年月、性别、身份证号码等等。对一所上万人的学校而言，工作量之大可想而知。如何有效地减少学生信息的输入工作量，而又要尽量保证不出差错？我为学校学生管理处设计了如下一套方案。现介绍给大家，希望对从事相关学校的人事信息管理工作的人员有所帮助。
一、自动产生学号
学号往往由学校代码和专业代码加年号，再加流水号组成，如‘3602080001’，‘3625080012’等，前面的‘360208’是学校和专业代码加年号，这些是固定的，后面‘0001’，‘0002’是流水号，这样复杂的学号，要自动产生就不能用EXCEL的“自定义序列”来实现，而要采用特殊的方法。具体操作方法如下：
1) 在“学生管理表”中选中“学号”单元格列，单击“格式/单元格”命令，打开“单元格格式”对话框，选“数字”标签中的分类项中选“自定义”在类型中输入“"360208"0000”，
2) 在A2单元格中输入“=IF(B2=""，""，COUNTA($B$2:B2))”，然后把鼠标移到A2单元格的右下方，鼠标变成十字形状时，按下左键拖拽填充到A列下面的单元格中，这样在B列输入姓名后，A列中就会自动输入序号，如“3602080001”，“3602080002”，“3602080003”……。如图1所示。
图1自动产生序号
二、选择输入专业和政治面貌
一般专业和政治面貌和种类都不会很多，EXCEL虽然有记忆功能，但至少也要输入一些汉字，才可选择所需要的，下面介绍的是在另外一张工作表中输入好专业和政治面貌，就可能单元格后的倒三角，用下拉框来选择。具体操作方法如下：
1) 在同一工作簿中选sheet2工作表，在其中输入专业、政治面貌（党员、团员、群众）的内容。
2) 再选择A2：A14，单击“插入/名称/定义”命令，打开“名称定义”对话框，在当前工作簿名称中输入“专业”，在引用位置中输入“=OFFSET(Sheet2!$A$2，，，COUNTA(Sheet2!$A:$A)-1)”，单击“添加”按钮。
同样，再选择B2：B4，在“名称定义”中输入“政治面貌”，引用位置中输入“=OFFSET(Sheet2!$B$2，，，COUNTA(Sheet2!$B:$B)-1)”，再单击“添加”按钮即可。
3) 选中“专业”单元格列，单击“数据/有效性”命令，打开“数据有效性”对话框，选择“设置”标签中的有效性条件中允许中选“序列”，来源中输入“=专业”。
同样，选中“政治面貌”单元格列，再在“数据有效性”对话框的有效性中允许中选“序列”，来源中输入“=政治面貌”，再单击“确定”按钮。
4）在Sheet1的“学生管理表”中输入各学生的专业和政治面貌时，只要点击对应的单元格右侧的倒三角就可打开下拉框，从其中选择所需要的专业和政治面貌即可。如图2所示。
图2 下拉框选取专业
三、从身份证号码中提取生源地（省份）、出生年月、性别信息
学生的生源地或所在的省市县、出生年月或年龄和性别信息暂不用直接输入，待身份证号码输入完成后，利用EXCEL相关函数从身份证号码中提取。
1) 学生的生源地信息，可不用直接输入，身份证号码的前二位就表示了此信息，因此可从中直接提取。
具体操作方法为：先选择E2单元格，输入“=IF(MID(H2，1，2)<="63"，CHOOSE(IF(MID(H2，1，2)<="15"，MID(H2，1，2)-10，IF(MID(H2，1，2)<="23"，MID(H2，1，2)-15，IF(MID(H2，1，2)<="37"，MID(H2，1，2)-22，IF(MID(H2，1，2)<="46"， MID(H2，1，2)-25，IF(MID(H2，1，2)<="54"， MID(H2，1，2)-28，MID(H2，1，2)-34)))))，"北京"，"天津"，"河北"，"山西"，"内蒙古"，"辽宁"，"吉林"，"黑龙江"，"上海"，"江苏"，"浙江"，"安徽"，"福建"，"江西"，"山东"，"河南"，"湖北"，"湖南"，"广东"，"广西"，"海南"，"重庆"，"四川"，"贵州"，"云南"，"西藏"，"陕西"，"甘肃"，"青海")，IF(MID(H2，1，2)="64"，"宁夏"，"新疆"))”，再向下拖动填充柄，将公式复制到E列其它单元格即可。
CHOOSE（index_num，value1，value2，…）的语法是根据给定的索引值，从参数串中选出相应值或操作，但最多只能选取二十九个相应值或操作。由于我国除港澳台外，有三十一个省市自治区，所以采用IF嵌套来处理。
若生源地需要更为详细的省、市、县信息，身份证号码的前六位表示了此类信息，可以用VLOOKUP函数从代码表中建立链接来获取。大概做法是，先在国家统计局网站上下载最新的城市代码表，通过整理成EXCEL工作表Sheet3。
然后，在E2单元格中输入“=VLOOKUP(LEFT(H2，6)，Sheet3!$A$2:$B$3522，2，FALSE”，再将鼠标移到H2单元格的右下方，鼠标变成十字形状时，按下左键拖拽填充到H列下面的单元格中即可。
2) 学生的出生年月信息，第一代15位的身份证号码第七位到第十位（第二代18位的身份证号码第七位到第十二位）表示了此类信息，可从中直接提取。
具体操作方法为：先选择F2单元格，输入“=IF（LEN（H2）=15，CONCATENATE（"19"，MID（H2，7，2），"年"，MID（H2，9，2），"月"），CONCATENATE（MID（H2，7，4），"年"，MID（H2，11，2），"月"））”，再向下拖动填充柄，将公式复制到F列其它单元格即可。
如果要求学生的年龄信息，可以F2单元格中输入“=YEAR (NOW())-IF(LEN(B2)=15，CONCATENATE("19"，MID(B2，7，2))，CONCATENATE(MID(B2，7，4)))”， 再向下拖动填充柄，将公式复制到F列其它单元格中即可。
3) 学生的性别信息，第一代15位的身份证号码第十五位（第二代18位的身份证号码第十七位）表示了此类信息，后直接生成。
具体操作方法为：先选择G2单元格，输入“=IF（LEN（H2）=15，IF（MOD（MID（H2，15，1），2）=1，"男"，"女"），IF（MOD（MID（H2，17，1），2）=1，"男"，"女"））”，再向下拖动填充柄，将公式复制到G列其它单元格即可。
四、总结
使用Excel进行人事信息管理，具有无须编程、简单易行的特点，在各行各业中都能得到广泛的应用。本文利用人事信息自身的特点，巧妙地利用Excel及其函数，所之自动产生学号，可选择专业，利用身份证号码的信息获得学生的出生年月、性别和生源省份等信息，减少了人事信息的输入量，有效地保证了其正确性。希望对从事人事统计管理的人员有所帮助，增强其办事效果，提高工作能力。