公司员工信息表管理

1、课题：公司员工信息表管理教学要求：1掌握数据的有效性、快速输入、函数的使用方法。2.掌握数据的分类汇总、数据透视表方法。课时安排： 2 学时教学重点、难点：1掌握数据的有效性、快速输入、函数的使用方法。2. 掌握数据的分类汇总、数据透视表方法。教学环境及教具准备：电脑 1 台/ 人，装有 MicrosoftOffice2003 办公软件。教学过程：项目：公司员工信息表管理1、项目提出本项目以人资源信息表的管理为例介绍快速输入数据、 分析数据和查询数据的方法和经 验。通过该项目，学习和掌握如何对数据有效性、前缀格式填充、mid、 today、vlookup 函数、数据分类汇总及数据透视表的运用。

2、2、项目要求与分析要求：（ 1）启动 Excel2003 软件，打开素材文件。（2）在 “北京分公司员工信息表” 工作表中完成：1）用填充柄方法在 A5单元格开始的列中输入数据：“0001 ”、“0002”、“0003”、 “0012”。2）运用前缀格式（前缀都是统一为：BJX ）的方法在 B列输入如效果图所示工作证号。3）运用数据有效性，设置 C 列中“姓名”的数据有效性为 “员工登记表”中的姓 名，并通过下拉框选择如效果图所示的姓名，完成“姓名”一列数据的输入。4）运用数据有效性，设置 D 列部门的有效性数据为序列“研发部，市场部，销售 部”，并通过下拉框选择完成“部门”一列数据的输入。5

3、）在 单 元 格 F15 、 F16 分 别 输 入 下 列 身 份 证 号 码 ：6）运用函数：利用身份证号码计算出生年月、年龄及性别。&0001e1)0027boos驴弓丁作訐号O?co4E阪5：0)00611 twi3_t)ooa13 r(mA ocioIF V1011iTbciatjiiii王罐锋EJX238钱乐EJX333齐晓励EJX334王鼎晶BJX234侗祖棗EJ：142ft 上戸1邹柯押妍EJX333陶着光EJX141弓牘取EJX237刘柄尤FJX239 M 红E；JX2丸安为军丈丈11010819

4、7611028521Fn0106L975U258CK0108L38JlL248&L11012070367217j01U9LJ711124b4 1101OSL37DO01OE52 ，201220Ld55ara3S350109L97212168G32 022i2nrsrKn24Fiz 1020219BD1218CEL 31D1061 972110289241976/1L/023413T5/11/2535iyyu/n.1301972/07/031976/1L/L219TC/08AD40l9f.5；Q7/O3151972/12/U127S/0E/D21980/12/131972/11/02lOlUBg

5、OaOSSeE? 196S/03/0B3043衣亠EL-亠 t- 用至 UI - -分析：该项目涉及到数据有效性、前缀格式填充、mid、today、vlookup函数、分类汇总、数（3）在“信息查询表”工作表中完成:1）运用数据有效性，定义 C3单元格的姓名为“北京分公司员工信息表”工作表的 姓名，并通过下拉框选择。2）在C4、C5、C6单元格运用 VLOOKUP函数，根据姓名，在“北京分公司员工 信息表”中查询数据，并返回该员工的年龄、文化程度和职务。（4）在“员工登记表”工作表中，对数据清单利用高级筛选，筛选出性别为女年龄在30-40之间文化程度为“大学本科”或者民族是回族的所有记录，条件

6、区域：S4： W6，筛选结果放至以Q9单元格为左上角的区域。（5） 在“sheetl”工作表中，利用分类汇总求出各种文化程度的平均工资情况。（先对 文化程度递增排序，再使用分类汇总）（6）在“sheet2”工作表中，利用数据透视表功能求出各部门中各职务的文化程度分布情况。将数据透视表放在B16单元格起始的位置（7 ）按原文件名保存文件。效果图：FCH:据透视表等操作。3、项目实现（1）启动Excel2003应用程序，打开素材文件。2)单击“北京分公司员工信息表”工作表：1) 双击 A5 单元格，输入：0001(0001 前面要加英文状态下的单引号 )，按回车， 利用填充柄将数据复制到 A16

7、单元格。2) 选择 B5 ：B16 单元格区域，单击右键，选择“单元格格式”命令，在“数字” 选项卡中选择“自定义”，将“ G/通用格式”修改为：”BJX”# ,(其中双引号 不能省略，双引号括起来的表示统一的前缀，该题的前缀为BJX，后面的#表示后面输入的是数字，如果输入的是文本，就将#号改为)，然后回到 B5 单元格，依次输入 BJX 后面的数字就可以了，如 B5 单元格输入 141， B6 单元 格输入238,，前缀BJX会自动显示出来。3)单击“员工登记表”工作表，选择C5: C36，选择菜单栏“插入一名称一定义”命令，在当前工作簿中的名称栏中输入“name”(其实输入什么不重要，主要

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

9、名。5)在“北京分公司员工信息表” 工作表中， 单击 F15 单元格， 输入(身份证号前要输单引号) ，用同样的方法在 F16 单 元格输入另一身份证号码。6)1 8位身份证号码： 16位为地区代码， 710位为出生年份( 4位)， 1112位 为出生月份， 1314 位为出生日期，第 1517位为顺序号，并能够判断性别， 奇数为男，偶数为女，第 18位为校验码。出生日期： = MID(F5,7,4)&/&MID(F5,11,2)&/&MID(F5,13,2)年龄：=(TODAY( )-G5)/365)性别：= IF(MOD(V ALUE(MID(F5,

10、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 个字符。& (字符连接符) ， 将两个文字连接起来生成一个新

11、文字。如： “多媒体” & “计算机”的运算结果是 “多媒体计算机” 。取余数函数 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,O）（4）在“员工登记表”工

12、作表中，条件区域和筛选结果如下图所示。RSTVVXYZ性别性别民族民族年龄年龄年龄文化提度文化提度回女=3040大学本科No_姓名姓名性别性别民族民族出生年月出生年月年静年静工作日期工作日期工龄工龄文化程度1钱跃之北京市1375-11-25301995-9111大学本科3陈13男回曉西城1977-11-1281393-12412研究生S于卫平回宁夏朮宁1S52-3-6541965-12-13S尢学本科7任水滨立河北青县1570-11-13519-12-111大学本科2E岳晉生男回四川谨宁1952-11-1S31969-7-137研究生31颜红女回1972/11/02331997-S-10g研究

13、生（5）在“ sheetT工作表中，将光标放在数据区域中任一个单元格，先对文化程度关键字 按升序排序，然后选择“数据一分类汇总”菜单命令，分类字段：文化程度；汇总方式：平 均值；汇总项：平均工资。确定。如图所示。分类汇总效果图人力酬源信需表Mil职务性刖勺龄工將本科3000帀场邸王嚴55与朮452WW市场部煎目经良 矣为军里39本科2300芥料平均悄29W工旨师王新力里2?1MW0工捏师女33博士博士平均值3400餘娟女38犬专32WJH程巧陈坍為2iW莓小琳女25大专2*00市场邮辽朋男30大专2000大专平均值2330淫理異述一31 (W会计白俺女40硕士3? 00确士平均值2EOO2&50（6）在“ sheet2”工作表中，将光标放在数据区域中任一个单元格，选择“数据一数据透视表和数据透视图”菜单命令，单击两次下一步，在步骤之3单击“布局”按钮，将“部门”拖放在行字段，“文化程度”拖放在列字段，数据项为