用微课学大学信息技术基础教学课件(共17单元)12制作人事信息数据表

任务4-3：制作人事信息数据表人工智能学院授课教师：*****大学信息技术基础FundamentalsofCollegeInformationTechnology任务目标本次任务学习目标010203掌握Excel2019中公式的使用；掌握Excel2019中常用函数的使用;掌握Excel2019中单元格的引用操作；大学信息技术基础FundamentalsofCollegeInformationTechnology任务场景年底，各个部门都在忙着做总结，综合管理部的小曹负责计算员工薪酬，他知道小傅擅长使用办公软件，于是向他请教人事信息数据的处理方法。人事信息数据是企业进行人事管理的基础和依据，因此，公司要求对当前的员工信息表和工资表进行科学管理，小傅首先帮忙完善了人事数据表格的字段信息，然后利用高效的数学函数完成了信息查询和薪资计算。任务准备——使用公式公式

公式是一个以等号开头的等式，通过对数据进行加、减、乘、除等运算，对单元格中的数据进行分析。公式可以引用同一工作表中的其他单元格、同一工作簿中不同工作表的单元格，也可以引用其他工作簿中工作表的单元格。公式的运算按照运算符进行，Excel中的运算符有4类：算术运算符、比较运算符、文本运算符和引用运算符。任务准备——使用公式公式1．算术运算符算术运算符包括加（+）、减（-）、乘（*）、除（/）、百分数（%）和乘方（^），适合各种基本的数学运算。算术运算符中优先级最高的是百分数（%），然后依次是乘方（^）、乘（*）和除（/）、加（+）和减（-）。2．比较运算符比较运算符包括等于（=）、大于（>）、小于（<）、大于等于（>=）、小于等于（<=）和不等于（<>）。比较运算符的作用是将两个值进行比较，运算结果为一个逻辑值TRUE或FALSE。其中TRUE表示条件成立，FALSE表示条件不成立。3．文本运算符文本运算符只有一个，即文本连接符（&），它的作用是将一个或多个文本数据连接成一个组合文本。例如，在单元格中输入“="Office"&"2019"”后回车，产生的结果为“Office2019”。4．引用运算符引用运算符的作用是进行引用，使用它可以对单元格去取进行合并计算。引用运算符包括冒号（：）、逗号（,）、空格和感叹号（！）。任务准备——函数的使用SUMIF()SUMIF函数的功能是对范围中符合指定条件的值求和，语法格式如下：SUMIF(range,criteria,[sum_range])

其中，range表示按照条件求值的单元格区域；criteria表示条件，其形式可以是数字、表达式、单元格引用、文本或函数；sum_range是可选参数，表示实际求和区域，如果省略，默认是range指定的区域。任务准备——函数的使用COUNTIF()COUNTIF函数的功能是统计满足某个条件的单元格的数量，语法格式如下：COUNTIF(range,criteria)其中，range表示按照条件统计的单元格区域。criteria表示条件，其形式可以是数字、表达式、单元格引用、文本或函数。任务准备——函数的使用IFS()IFS函数的功能是检查是否满足一个或多个条件，返回符合第一个TRUE条件的值。IFS可以取代多个嵌套IF语句。语法格式如下：IFS(logical_test1,value_if_true1[,logical_test1,value_if_true1][,logical_test1,value_if_true1]……)IFS函数允许测试最多127个不同的条件。任务准备——函数的使用IFERROR()IFERROR函数的功能是捕获和处理公式中的错误IFERROR返回公式计算结果为错误时指定的值；否则，它将返回公式的结果。语法格式如下：IFERROR(value,value_if_error)其中，value表示检查是否存在错误的参数；value_if_error表示公式计算结果为错误时要返回的值。任务准备——函数的使用REPLACE()REPLACE函数的功能是进行字符替换。语法格式如下：REPLACE(old_text,start_num,num_chars,new_text)其中，old_text表示要进行字符替换的文本；start_numvalue表示要替换为new_text的字符在旧文本中的位置；num_chars表示要从old_text中要替换的字符个数；new_text表示对old_text中字符进行替换的字符串。任务准备——函数的使用MID()MID函数的功能是提取文本字符串中指定位置开始的特定数目的字符。语法格式如下：MID(text,start_num,num_chars)其中，text表示目标文本字符串；start_num表示准备提取的以一个字符的位置，text中第一个字符位置为1；num_chars表示所要提取的字符串的长度任务准备——TEXT函数TEXT()TEXT函数的功能是根据指定的数字格式将数值转化成文本，语法格式如下：TEXT(value,format_text)其中，value表示数字，能够求值的数值公式，或者对数值单元格的引用；format_text表示文字形式的数据格式任务准备——函数的使用VLOOKUP()VLOOKUP函数是一个纵向查找函数，按列查找，最终返回该列所需查询序列所对应的值。语法格式如下：VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)其中，lookup_value表示需要在数据表首列进行搜索的值，可以是数值、引用或字符串；table_array表示要在其中搜索数字的区域；col_index_num表示返回匹配值在table_array中的列序号，range_lookup表示匹配方式，精确匹配用FALSE，模糊匹配用TRUE或省略，如图4.59所示。任务准备——函数的使用HLOOKUP()HLOOKUP函数是一个横向查找函数，按行查找，最终返回该行所需查询序列所对应的值。语法格式如下：HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)其中，lookup_value表示需要在数据表首行进行搜索的值，可以是数值、引用或字符串；table_array表示要在其中搜索数字的区域；row_index_num表示返回匹配值在table_array中的行序号，range_lookup表示匹配方式，精确匹配用FALSE，模糊匹配用TRUE或省略任务准备——单元格的引用单元格引用1．相对引用相对引用是指引用单元格的相对位置，它的引用形式直接用列表行号表示单元格。在列上填充时，列标不变，行号会随着填充而变化；在行上填充时，行号不变，列表会随着填充而变化。2．绝对引用绝对引用是指引用单元格的精确地址，与包含公式的单元格位置无关。它的引用形式为在行号或列标前都加一个符号$。$符号的作用是其固定作用，行号和列标都加上符号后再进行填充，引用的区域都不会发生变化。3．混合引用引用中既包含绝对引用又包含相对引用的称为混合引用，它的引用形式为只在行号或列标前加符号$，进行行或列的固定。混合引用有两种形式：一种是固定行，一种是固定列。任务演练——管理员工信息表小傅先帮小曹对人事数据信息完善了字段信息，但考虑到公司的快速发展，新晋员工人数逐年增长，小傅升级了员工编号；然后，根据已有的身份证信息，利用函数提炼每个员工的生日、年龄和工龄信息，并且对所有员工的学历信息做了简单统计。任务演练——管理员工信息表任务效果员工信息表效果

员工信息表中统计的效果任务演练——管理员工信息表员工编号升级1（1）选中C3单元格，单击“公式”选项卡→“函数库”组→“插入函数”按钮，弹出“插入函数”对话框，在“搜索函数”文本框中输入“REPLACE”，单击“转到”按钮，选中REPLACE函数，单击“确定”按钮，弹出“函数参数”对话框，在对话框中输入参数内容，如图所示，单击“确定”按钮，得到员工“王新亮”升级后的员工编号（2）将光标指针移至C3单元格右下角，当光标指针变为实心十字形时，双击鼠标，进行公式填充，升级其余员工编号。任务演练——管理员工信息表提取员工信息2（1）选中G3单元格，单击“公式”选项卡→“函数库”组→“插入函数”按钮，弹出“插入函数”对话框，在“搜索函数”文本框中输入“MID”，单击“转到”按钮，选中MID函数，单击“确定”按钮，弹出“函数参数”对话框，在对话框中输入参数内容，如图所示，按Enter键得到员工“王新亮”的出生年月。任务演练——管理员工信息表提取员工信息2（2）选中G3单元格，在编辑栏中编辑TEXT公式“=TEXT(MID(F3,7,6),"0年00月")”，按Enter键得到员工“王新亮”指定格式的出生年月，如图所示。（3）将鼠标指针移至G3单元格右下角，当鼠标指针变为实心十字形时，双击鼠标，进行公式填充，提取和转换其他员工的出生日期。任务演练——管理员工信息表计算年龄和工龄3（1）选中H3单元格，在编辑栏中输入公式“=YEAR(TODAY())-YEAR(G3)”，按Enter键得到员工“王新亮”的年龄，并进行公式填充，计算出其他员工的年龄。（2）选中H3单元格，参考上面的操作进行工龄计算，并将计算结果数据类型设置成常规，得到员工的工龄，如图所示。任务演练——管理员工信息表计算年龄和工龄3（1）选中H3单元格，在编辑栏中输入公式“=YEAR(TODAY())-YEAR(G3)”，按Enter键得到员工“王新亮”的年龄，并进行公式填充，计算出其他员工的年龄。（2）选中H3单元格，参考上面的操作进行工龄计算，并将计算结果数据类型设置成常规，得到员工的工龄，如图所示。任务演练——管理员工信息表提取性别4选中I3单元格，在编辑栏中输入公式“=IF(MOD(MID(F3,17,1),2)=1,"男","女")”，按Enter键得到员工“王新亮”的性别，如图所示，并进行公式填充，提取出其他员工的性别。任务演练——管理员工信息表学历分布5选中T11单元格，单击“公式”选项卡→“函数库”组→“插入函数”按钮，弹出“插入函数”对话框，在“搜索函数”文本框中输入“COUNTIF”，单击“转到”按钮，选中COUNTIF函数，单击“确定”按钮，弹出“函数参数”对话框，在对话框中输入参数内容，如图4.71其中，对计算区域M3:M42使用绝对引用。单击“确定”按钮，得到专科学历的人数。任务演练——管理员工信息表年龄层分布6（1）选中T18单元格，在编辑栏中输入公式：“=COUNTIF(H3:H42,"<=25")”，按Enter键得到“<=25岁”年龄层的人数。（2）选中T19单元格，在编辑栏中输入公式：“=COUNTIF(H3:H42,"<35")-COUNTIF(H3:H42,"<=25")，按Enter键得到“25岁至35岁”年龄层的人数。（3）选中T20单元格，在编辑栏输入公式：“=COUNTIF(H3:H42,”>=35“)”，按Enter键得到“>=35岁”年龄层的人数。任务拓展——管理员工工资表工资薪酬关乎员工基本利益，影响员工工作积极性和工作效率，关系到企业是否能良性发展，但是薪酬的计算规则复杂，项目繁多，工作量较大。小傅利用Excel中的VLOOKUP、HLOOKUP、IFS等函数，对员工工资表进行制作管理，简单而且高效。任务拓展——管理员工工资表任务效果任务拓展——管理员工工资表员工岗级1选中E4单元格，单击“公式”选项卡→“函数库”组→“插入函数”按钮，弹出“插入函数”对话框，在“搜索函数”文本框中输入“VLOOKUP”，单击“转到”按钮，选中VLOOKUP函数，单击“确定”按钮，弹出“函数参数”对话框，在对话框中输入参数内容，按Enter键得到员工“王新亮”的岗级。任务拓展——管理员工工资表计算岗位工资2选中G4单元格，在编辑栏中输入IFS公式“=IFS(E4="9级",1000,E4="8级",1500,E4="7级",2000,E4="6级",2500,E4="5级",3000)”，按Enter键得到员工“王新亮”的岗位工资任务拓展——管理员工工资表计算员工补贴3（1）选中H4单元格，单击“公式”选项卡→“函数库”组→“插入函数”按钮，弹出“插入函数”对话框，在“搜索函数”文本框中输入“HLOOKUP”，单击“转到”按钮，选中HLOOKUP函数，单击“确定”按钮，弹出“函数参数”对话框，在对话框中输入参数内容，如图4.80所示，按Enter键得到员工“王新亮”的补贴金额。任务拓展——管理员工工资表计算员工补贴3（2）选中H4单元格，编辑公式为“=IFERROR(HLOOKUP