公司员工信息表管理.doc

课题名称：公司员工信息表管理
【课时安排】 2学时
【教学方法与教学手段】 采用一体化教学，案例分析法
【教学环境】 装有office2003办公软件和广播教学软件的电脑（1台/人）
【教学重点】
1、掌握数据的有效性的设置、快速输入、函数的使用方法
2、掌握数据的分类汇总、数据透视表方法
【教学难点】
1、数据的有效性的设置、快速输入、函数的使用方法
【教学过程】
一、复习旧课
1、自动筛选和高级筛选的区别？
2、高级筛选要建条件区域，条件区域怎么创建？（并举例说明）
3、排序与分类汇总操作要注意的问题有哪些？
二、新课导入
在实际生活工作中，经常要快速处理一些大量的复杂和繁琐的数据信息，掌握在excel表中如何快速处理数据就是一项十分重要的工作了。
三、新课讲授

项目名称：公司员工信息表管理
1、项目点评
本项目以人力资源信息表的管理为例介绍快速输入数据、分析数据和查询数据的方法和经验。通过该项目，学习和掌握如何对数据有效性、前缀格式填充、mid、today、vlookup函数、数据分类汇总及数据透视表的运用。
2、相关知识点简介
（1）数据有效性设置，特别是对于不同表之间的数据的引用。
（2）单元格格式化的设置，对于自定义数据格式的设置是重点。
（3）if、mid、mod等函数的学习与应用。
（4）排序与分类汇总的设置与操作。
（5）高级筛选操作中条件区域的设置与操作。
3、项目说明
要求：
（1）启动Excel2003软件，打开素材文件。
（2）在“北京分公司员工信息表”工作表中完成：
1) 用填充柄方法在A5单元格开始的列中输入数据：“0001”、“0002”、“0003”……、“0012”。（说明：输入“’0001”）
2) 运用前缀格式（前缀都是统一为：BJX）的方法在B列输入如效果图所示工作证号。（说明：″BJX″#）
3) 运用数据有效性，设置C列中“姓名”的数据有效性为 “员工登记表”中的姓名，并通过下拉框选择如效果图所示的姓名，完成“姓名”一列数据的输入。（说明：执行菜单“插入”→“名称”→“定义”。）
4) 运用数据有效性，设置D列部门的有效性数据为序列“研发部，市场部，销售部”，并通过下拉框选择完成“部门”一列数据的输入。
5) 在单元格F15、F16分别输入下列身份证号码：110106197211028924、110103196803088657。
6) 运用函数：利用身份证号码计算出生年月、年龄及性别。（说明：18位身份证号码：1~6位为地区代码，7~10位为出生年份（4位），11~12位为出生月份，13~14位为出生日期，第15~17位为顺序号，并能够判断性别，奇数为男，偶数为女，第18位为校验码。）

（3）在“信息查询表”工作表中完成：
1) 运用数据有效性，定义C3单元格的姓名为“北京分公司员工信息表”工作表的姓名，并通过下拉框选择。（说明：仿照上问中的第三小题来完成）
2) 在C4、C5、C6单元格运用VLOOKUP函数，根据姓名，在“北京分公司员工信息表”中查询数据，并返回该员工的年龄、文化程度和职务。
（4）在“员工登记表”工作表中，对数据清单利用高级筛选，筛选出性别为女年龄在30-40之间文化程度为“大学本科”或者民族是回族的所有记录，条件区域：S4：W6，筛选结果放至以Q9单元格为左上角的区域。（说明：多个条件之间是“与”的关系，书写在同一行，当多个条件之间是“或”的关系，则书写在下一行。）
（5）在“sheet1”工作表中，利用分类汇总求出各种文化程度的平均工资情况。（先对文化程度递增排序，再使用分类汇总）
（6）在“sheet2”工作表中，利用数据透视表功能求出各部门中各职务的文化程度分布情况。将数据透视表放在B16单元格起始的位置.（说明：将“部门”拖放在行字段，“文化程度”拖放在列字段，数据项为姓名的计数。）
（7）按原文件名保存文件。
效果图：

4、项目分析与实现
该项目涉及到数据有效性、前缀格式填充、mid、today、vlookup函数、分类汇总、数据透视表等操作。
（1）启动Excel2003应用程序，打开素材文件。
（2）单击“北京分公司员工信息表”工作表：
1) 双击A5单元格，输入：’0001（0001前面要加英文状态下的单引号），按回车，利用填充柄将数据复制到A16单元格。
2) 选择B5：B16单元格区域，单击右键，选择“单元格格式”命令，在“数字”选项卡中选择“自定义”，将“G/通用格式”修改为：”BJX”# ，（其中双引号不能省略，双引号括起来的表示统一的前缀，该题的前缀为BJX，后面的#表示后面输入的是数字，如果输入的是文本，就将#号改为@），然后回到B5单元格，依次输入BJX后面的数字就可以了，如B5单元格输入141，B6单元格输入238，……，前缀BJX会自动显示出来。
3) 单击“员工登记表”工作表，选择C5：C36，选择菜单栏“插入—名称—定义”命令，在当前工作簿中的名称栏中输入“name”（其实输入什么不重要，主要是起个名字），接着回到“北京分公司员工信息表”工作表，选择C5：C16单元格区域，选择菜单栏“数据—有效性”，设置有效条件允许为“序列”，来源栏中输入“=name”，确定，这时在C5：C16中每个单元格旁边都会有一个下拉按钮，单击该按钮在下拉框中依次选择输入姓名。

4) 单击 “北京分公司员工信息表”工作表，选择D5：D16单元格区域，选择菜单栏“数据—有效性”，设置有效条件允许为“序列”，来源栏中输入：研发部，市场部，销售部（注意：逗号必须是英文状态下的），确定，这时在D5：D16中每个单元格旁边都会有一个下拉按钮，单击该按钮在下拉框中依次选择输入姓名。

5) 在“北京分公司员工信息表”工作表中，单击F15单元格，输入“’110106197211028924”（身份证号前要输单引号），用同样的方法在F16单元格输入另一身份证号码。

6) 18位身份证号码：1~6位为地区代码，7~10位为出生年份（4位），11~12位为出生月份，13~14位为出生日期，第15~17位为顺序号，并能够判断性别，奇数为男，偶数为女，第18位为校验码。
出生日期：= MID(F5,7,4)&"/"&MID(F5,11,2)&"/"&MID(F5,13,2)
年龄： =((TODAY( )-G5)/365)
性别： = IF(MOD(VALUE(MID(F5,15,3)),2)=0,"女","男")
几个函数说明：
IF函数：条件测试函数
格式：IF（logical_test, value_if_true, value_if_false）
功能：当逻辑测试条件logical_test为TRUE时，返回value_if_true的结果，否则返回value_if_false的结果。
任意位置取字符串函数MID
格式：MID（text, start_num, num_chars）
功能：返回文本字符串text中从start_num位置开始往后连续取num_chars个字符。
&（字符连接符），
将两个文字连接起来生成一个新文字。如：“多媒体”& “计算机”的运算结果是“多媒体计算机”。
取余数函数MOD
字符转数值函数VALUE
系统日期函数TODAY（）
格式：TODAY（）
功能：返回计算机系统内部时钟当前日期的序列数。
（3）在“信息查询表”工作表中完成：
1) 步骤与（2）的第三小题相似，参照。
2) 在C4、C5、C6单元格运用VLOOKUP函数，输入如下公式：
年龄：=VLOOKUP(C3,北京分公司员工信息表!C5:H16,6,0)
文化程度：=VLOOKUP(C3,员工登记表!C5:K36,9,0)
=VLOOKUP(C3,北京分公司员工信息表! C5:I16,7,0)
（4）在“员工登记表”工作表中，条件区域和筛选结果如下图所示。

（5）在“sheet1”工作表中，将光标放在数据区域中任一个单元格，先对文化程度关键字按升序排序，然后选择“数据—分类汇总”菜单命令，分类字段：文化程度；汇总方式：平均值；汇总项：平均工资。确定。如图所示。
分类汇总效果图

（6）在“sheet2”工作表中，将光标放在数据区域中任一个单元格，选择“数据—数据透视表和数据透视图”菜单命令，单击两次下一步，在步骤之3单击“布局”按钮，将“部门”拖放在行字段，“文化程度”拖放在列字段，数据项为年龄的计数，将数据透视表放在B16单元格起始的位置。
数据透视表效果图

（7）按原文件名保存文件。
5、项目验收及评价
（1）结合学生完成的情况进行点评，在下次课上通过抽查知识点给出考核成绩，
（2）展示学生优秀项目完成效果，激发学生学习热情。
【作业要求】
以七人学习小组为单位完成本次课堂上的三个项目，三个项目都作为作业检查的内容，每一个人都必须完全掌握。
【教学小结】
本次主要讲解了数据有效性、前缀格式填充、mid、today、vlookup函数、分类汇总、数据透视表等操作。需要多次讲解难点，多举实例让学生理解并能自己完成项目。