第五章员工档案资料管理

第五章员工档案资料管理重要知识点：1、YEAR()、MONTH()、NOW（）函数的使用2、COUNTIF()、IF()函数使用3、文本函数LEN、LEFT、RIGHT、MID4、添加和编辑控件5、插入超链接Excel高级处理及应用技巧李晨曦员工档案资料管理本章主要介绍如何使用EXCEL制作员工档案资料管理，加强企业对员工的管理。本章首先介绍了员工档案资料表、员工工龄管理表及员工信息查询表相关表格的内容的编辑与制作，然后介绍如何制作一个操作系统窗口界面，用户可以通过窗口中的控件功能来完成与已有表格之间的切换链接，从而方便用户对不同表格数据信息的查看，使制作的表格更加的规范化，操作起来更加的方便快捷。Excel高级处理及应用技巧李晨曦实例完成图Excel高级处理及应用技巧李晨曦5.1创建员工档案资料表Excel高级处理及应用技巧李晨曦5.1创建员工档案资料表设置数据有效性单击“数据”选项卡下“数据工具”组里的“数据有效性”选项Excel高级处理及应用技巧李晨曦5.1创建员工档案资料表设置身份证输入格式“设置单元格格式”下单击“文本”选项Excel高级处理及应用技巧李晨曦5.1创建员工档案资料表YEAR(serial_number)返回某日期对应的年份。返回值为1900到9999之间的整数。Serial_number为一个日期值，其中包含要查找年份的日期。应使用DATE函数输入日期，或者将函数作为其他公式或函数的结果输入。例如，使用函数DATE(2008,5,23)输入2008年5月23日。如果日期以文本形式输入，则会出现问题。Excel高级处理及应用技巧李晨曦5.1创建员工档案资料表NOW()返回当前日期和时间所对应的序列号。如果在输入函数前，单元格的格式为“常规”，则结果将设为日期格式。Excel高级处理及应用技巧李晨曦5.1创建员工档案资料表DATE(year,month,day)返回代表特定日期的序列号。Year参数year可以为一到四位数字Month代表一年中从1月到12月（一月到十二月）各月的正整数或负整数。如果month大于12，则month从指定年份的第一个月起累加月份数。例如，DATE(2008,14,2)返回代表2009年2月2日的序列号。如果month小于1，则用month减去指定年份的月份数，再从该年份的第一个月起往向上累加，例如，DATE(2008,-3,2)返回代表2007年9月2日的序列号。Day代表一月中从1日到31日各天的正整数或负整数。如果day大于指定月份的天数，则day将从该月份的第一天开始累加天数。例如，DATE(2008,1,35)返回代表2008年2月4日的序列号。如果day小于1，则用day减去该月份的天数，再从该月份的第一天开始往上累加。例如，DATE(2008,1,-15)返回代表2007年12月16日的序列号。Excel高级处理及应用技巧李晨曦5.1创建员工档案资料表文本函数LEN(text)LEN返回文本字符串中的字符数。Text是要查找其长度的文本。空格将作为字符进行计数例如：A2为“Phoenix”=LEN(A2)第一个字符串的长度(11)Excel高级处理及应用技巧李晨曦5.1创建员工档案资料表MID(text,start_num,num_chars)返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定Text是包含要提取字符的文本字符串。Start_num是文本中要提取的第一个字符的位置。文本中第一个字符的start_num为1，以此类推。Num_chars指定希望MID从文本中返回字符的个数。例如：A2为“FluidFlow”=MID(A2,1,5)上面字符串中的5个字符，从第一个字符开始(Fluid)=MID(A2,7,20)上面字符串中的20个字符，从第七个字符开始(Flow)Excel高级处理及应用技巧李晨曦5.1创建员工档案资料表LEFT(text,num_chars)根据所指定的字符数，LEFT返回文本字符串中第一个字符或前几个字符。Text是包含要提取的字符的文本字符串。Num_chars指定要由LEFT提取的字符的数量。Num_chars必须大于或等于零。如果num_chars大于文本长度，则LEFT返回全部文本。如果省略num_chars，则假设其值为1。例如：A2=“SalePrice”=LEFT(A2,4)第一个字符串中的前四个字符(Sale)Excel高级处理及应用技巧李晨曦5.1创建员工档案资料表RIGHT(text,num_chars)RIGHT根据所指定的字符数返回文本字符串中最后一个或多个字符。Text是包含要提取字符的文本字符串。Num_chars指定要由RIGHT提取的字符的数量。Num_bytes按字节指定要由RIGHTB提取的字符的数量。注解Num_chars必须大于或等于零。如果num_chars大于文本长度，则RIGHT返回所有文本。如果省略num_chars，则假设其值为1。Excel高级处理及应用技巧李晨曦5.1创建员工档案资料表TEXT(value,format_text)将数值转换为按指定数字格式表示的文本Value为数值、计算结果为数字值的公式，或对包含数字值的单元格的引用。Format_text是作为用引号括起的文本字符串的数字格式。通过单击“设置单元格格式”对话框中的“数字”选项卡的“类别”框中的“数字”、“日期”、“时间”、“货币”或“自定义”并查看显示的格式，可以查看不同的数字格式。注解Format_text不能包含星号(*)。例如：=Text(“19800310”,”0000-00-00”)[1980-03-10]Excel高级处理及应用技巧李晨曦5.1创建员工档案资料表MOD(number,divisor)返回两数相除的余数。结果的正负号与除数相同Number为被除数。Divisor为除数。注解如果divisor为零，函数MOD返回错误值#DIV/0!。例如：=MOD(3,2)3/2的余数(1)Excel高级处理及应用技巧李晨曦5.1创建员工档案资料表利用公式计算性别=IF(MOD(MID(E3,17,1),2)=0,女,男)Excel高级处理及应用技巧李晨曦5.1创建员工档案资料表利用公式计算出出生年龄=TEXT(MID(E3,7,8),0000-00-00)Excel高级处理及应用技巧李晨曦5.1创建员工档案资料表计算员工年龄=YEAR(NOW())-YEAR(F3)Excel高级处理及应用技巧李晨曦5.1创建员工档案资料表统计员工人数【设置格式】在自定义选项中，在“类型”文本框中输入“#人”Excel高级处理及应用技巧李晨曦5.1创建员工档案资料表输入统计函数公式=COUNTIF(D3:D17,财务部)=COUNTIF(D3:D17,销售部)=COUNTIF(D3:D17,生产部)=COUNTIF(D3:D17,行政部)=COUNTIF(C3:C17,男)=COUNTIF(C3:C17,“女)=SUM(K3:K6)=M3/M4Excel高级处理及应用技巧李晨曦5.2制作员工工龄管理表随着《劳动法》的实施，公司将给予任职一年以上的员工以天数不等，并且不扣除薪资的特别休假。Excel高级处理及应用技巧李晨曦5.2制作员工工龄管理表新建单元格样式“开始”选项卡下，单击“样式”选项组中的“单元格样式”按钮，在展开的列表中单击“新建单元格样式”选项。在“样式名”文本框中输入创建的新样式名称，单击“格式”按钮Excel高级处理及应用技巧李晨曦5.2制作员工工龄管理表计算员工工龄=YEAR(NOW())-YEAR(F3)-IF(F3=DATE(YEAR(F3),MONTH(NOW()),DAY(NOW())),1,0)输入的公式中嵌套的IF函数内容是表示判断入职日是否比当前日期大，如果大则工龄减1，如果小则不减Excel高级处理及应用技巧李晨曦5.2制作员工工龄管理表计算年假天数年假天数=工龄*2=IF(G3=1,G3*2,0)Excel高级处理及应用技巧李晨曦5.2制作员工工龄管理表设置高级筛选要求：工龄大于5且学历是本科选择“数据”选项卡下的，单击“排序和筛选”选项组中的“高级”按钮Excel高级处理及应用技巧李晨曦5.3制作员工信息查询表定义名称选定A3：A17在“公式”选项卡单击“定义的名称”Excel高级处理及应用技巧李晨曦5.3制作员工信息查询表设置数据有效性选中B2单元格，在“数据”选项卡下单击“数据工具”选项组中的“数据有效性”，“允许”下拉下选择序列，“来源”文本框中输入“=员工编号”Excel高级处理及应用技巧李晨曦5.3制作员工信息查询表单击B2单元格右侧的下拉按钮，在展开的列表中可选择需要输入的员工编号Excel高级处理及应用技巧李晨曦5.3制作员工信息查询表引用员工姓名、年龄等=VLOOKUP(B2,员工工龄管理!A2:H17,2,0)=VLOOKUP(B2,人事档案资料表!A2:H17,7,0)Excel高级处理及应用技巧李晨曦5.3制作员工信息查询表显示数据信息Excel高级处理及应用技巧李晨曦5.4制作员工档案管理系统界面Excel高级处理及应用技巧李晨曦5.4制作员工档案管理系统界面1、新建工作表，并在“视图”选项卡下取消勾选“显示/隐藏”选项组中“网络线”复选框Excel高级处理及应用技巧李晨曦5.4制作员工档案管理系统界面2、插入背景图片Excel高级处理及应用技巧李晨曦5.4制作员工档案管理系统界面3、调整图片大小至合适位置Excel高级处理及应用技巧李晨曦5.4制作员工档案管理系统界面4、设置图片样式及形状选中图片，在“格式”选项卡下单击“图片样式”选项组中的“金属框架”样式选中图片，单击“图片样式”选项组中“图片形状”按钮，选择“圆角矩形”选项Excel高级处理及应用技巧李晨曦5.4制作员工档案管理系统界面5、插入文本框单击“插入”下“文本”选项组中“文本框”的下拉按钮，在展开的列表中单击“横排文本框”选项在文本框中输入文字，并设置字体的大小颜色等基本属性Excel高级处理及应用技巧李晨曦5.4制作员工档案管理系统界面6、再添加2个文本框，显示效果如下Excel高级处理及应用技巧李晨曦5.4制作员工档案管理系统界面7、选中“查看人事档案”文本框，单击鼠标右键选择“超链接”Excel高级处理及应用技巧李晨曦5.4制作员工档案管理系统界面8、在弹出的对话框中选择“人事档案资料表”，点确定。Excel高级处理及应用技巧李晨曦5.4制作员工档案管理系统界面9、同理为“查看人事档案”添加超链接Excel高级处理及应用技巧李晨曦5.4制作员工档案管理系统界面10、切换到“人事档案资料”工作表中，插入“五边形”选项Excel高级处理及应用技巧李晨曦5.4制作员工档案管理系统界面11、编辑文字及设置样式12、插入超链接即可Excel高级处理及应用技巧李晨曦本章到此结束，谢谢聆听！