EXCLE常见函数应用PPT.ppt

,H,R,xcel,k,从,HR,变,HERO,提高桌面生产力,Excel,人力资源管理模块企业级应用课程,常见函数应用,本章介绍,精选了,HR,在工作中最常用的,20,个左右的,Excel,函数,，力求通过科学的讲解方法，把这些函数的使用方法及应用讲清、讲透，让你轻松跨越函数障碍，实现,Excel,水平快速进步，,提高工作效率。,1,、逻辑函数：,if,、,and,、,or,2,、文本函数：,left,、,right,、,mid,、,len,、,text,3,、日期函数：,today,、,now,、,date,、,edate,、,datedif,4,、引用函数：,vlookup,5,、统计函数：,sumifs,、,countifs,1.1,函数入门介绍,单元格引用,绝对引用与相对引用,$F$4,$F4,F$4,固定单元格,固定列,固定行,2.2,逻辑函数：,IF/AND/OR,主要功能：根据对指定条件的逻辑判断的真假结果，返回相对应的内容。,使用格式：,=IF(Logical,Value_if_true,Value_if_false),=IF(A1+C1=E1,正确,错误,),代表逻辑判断表达式,示当判断条件为逻辑“真（,TRUE,）”时的显示内容，如果忽略返回“,TRUE”,表示当判断条件为逻辑“假（,FALSE,）”时的显示内容，如果忽略返回“,FALSE”,IF,逻辑函数：,IF/AND/OR,本节回顾,If,函数是最常用的条件判断函数，条件成立或不成立，结果是不一样的。在,IF,多层嵌套时，一定要注意书写顺序，从小到大或从大到小，遵循一个顺序，基本就不会写错。,对于,AND,和,OR，AND,需要多个条件同时成立时才成立，,OR,多个条件中任意一个条件成立即成立，,AND,或者,OR,可以与,IF,结合起来使用，作为,IF,的判断条件。,一定要自己动手多写写函数呀，写多了，也就通透了！,2.3,文本函数,文本函数：,LEFT/RIGHT/MID/LEN/TEXT,本节介绍,在工作中，我们经常需要从一个文本字符串中提取出部分字符，比如把邮件编码和地址分来，从身份证号码中提取出生日期和性别等。因此，掌握常用的文本函数就必不可少。,本节中我们选取了六个常见的文本函数，,LEFT/RIGHT/MID/LEN/TEXT，,这六个函数比较简单，但应用性却很强。,02,常见函数应用,02-02,常见的文本函数,1,文本函数,1,LTFT/RIGHT/MID,2,LEN/LENB,3,TEXT,2,场景练习,1,根据身份证号码提取出生日期、性别,=MID(C2,17,1),主要功能：从文本字符串中指定的起始位置返回指定长度的字符。,使用格式：,=MID(Text,Start_num,Num_chars),准备从中提取字符串的文本字符串,准备提取的第,1,个字符串的位置，如：从第,17,为开始提取字符串,指定所要提取字符串的长度,MID,=LEFT(C2,2),主要功能：从一个文本字符串的第一个字符开始，截取指定数目的字符。,使用格式：,=LEFT(text,num_chars),代表要截字符的字符串,代表要截取的字符串长度,LEFT,=LEN(A2),主要功能：统计文本字符串中字符数目。,使用格式：,=LEFT(text,num_chars),代表要统计的文本字符串,LEN,LEN,要统计时，无论中全角字符，还是半角字符，每个字符均计为“,1”,；,LENB,在统计时，半角字符计为“,1”,，全角字符计为“,2”,。,=TEXT(MID(C2,7,8),0000-00-00),主要功能：将数值转化为设置单元格格式中的数字,格式类型。,使用格式：,=TEXT(value,format_text),为数字数值,设置单元格格式中自己所要选用的格式，如：,”0000,年,00,月,00,日,”,TEXT,文本函数：,LEFT/RIGHT/MID/LEN/LENB/TEXT,本节回顾,LEFT/RIGHT/MID,主要用于提取文本字符；,LEN/LENB,主要用于计算文本长度；,TEXT,主要用于转换文本格式等。,这几个函数往往与其他函数结合使用，比如提取身份证证号的性别显示信息并进行判断，就是,MID,函数与,IF,函数等的结合。,涉及文本操作的时候，要记得应用他们。,2.4,日期类函数,日期类函数：,TODAY/NOW/DATE/EDATE/DATEDIF,本节介绍,Excel,提供了很多的日期和时间函数，这样使得我们在处理日期和时间时非常方便，比如计算合同到期日，计算年龄、工龄等关键信息。,本节中我们选取了五个常见的日期类函数，,TODAY/NOW/DATE/EDATE/DATEDIF，,这五个函数公式结构并不复杂，学会马上就能应用,现在就一起来学习吧。,02,常见函数应用,02-03,常用的日期函数,1,日期类函数,1,TODAY/NOW,2,DATE,、,EDATE,3,DATEDIF,2,场景练习,1,根据身份证号码提取年龄,2,用名称管理器，计算员工工龄,3,HR,实景,1,员工年龄计算表,2,职工退休提醒,3,职工到期合同提醒,=DATE(A2,B2+1,0),主要功能：给出指定数值的日期。,使用格式：,=DATE(year,month,day),year,为指定的年份数值（小于,9999,）,month,为指定的月份数值（可以大于,12,）,DATE,day,为指定的天数,主要功能：计算返回两个日期参数的差值。,使用格式：,=DATEDIF(date1,date2,”y”),=DATEDIF(date1,date2,”m”),=DATEDIF(date1,date2,”d”),DATEDIF,=DATEDIF(,入职日期,TODAY(),D),开始日期，如：入职日期,结束日期，如：离职日期,计算两个相差日期之间的年,Y/,月,M/,日,D,EDATE,主要功能：计算开始日期之前,(,或后,),的,X,月份后的日期。,使用格式：,=EDATE(start_date,months),=EDATE(A2,C2*12),开始日期,表示之前或之后的月份数,日期类函数：,TODAY/NOW/DATE/EDATE/DATEDIF,本节回顾,下面我带大家简单回顾一下本节课程的主要内容：,TODAY/NOW,主要用于插入当前日期及时间，方便日期的动态计算；,DATE,主要用于合并年月日信息显示日期；,EDATE,主要用于一段间隔之后的具体日期，比如计算合同到期日；,DATEDIF,主要用于计算时间间隔，如年龄、工龄等，与,TODAY,函数配合使用比较常见。,DATEDIF,不属于,Excel,内嵌进的函数，一定要保证拼写正确,2.5,查找引用类函数,查找引用类函数：,VLOOKUP/HLOOKUP/LOOKUP,本节介绍,查找引用数据是日常,HR,管理工作中使用,Excel,最频繁的一项操作，其重要性不言而喻。可以说是使用,Excel,必须掌握的一项技能。,提到查找引用，大家第一时间就会想到,VLOOKUP,函数，,VLOOKUP,被称之为,Excel,中的大众情人，谁都离不开。查找引用函数家族中，除了,VLOOKUP,函数，比较常见的还有,HLOOKUP,函数与,LOOKUP,函数。他们究竟使用于哪些查找场景呢？,马上一起来学习吧。,02,常见函数应用,02-04,查找类函数,1,VLOOKUP,家族,1,VLOOKUP,2,H,LOOKUP,3,LOOKUP,2,场景练习,1,查找员工基本信息制作名片,2,查找基本工资及奖金额度,3,HR,实景,1,员工工资表模板,VLOOKUP,主要功能：按列查找，最终返回该列所需查询列序所对应的值。,使用格式：,=VLOOKUP,（,Lookup_value,Table_array,Col_index_number,Range_lookup,）,=VLOOKUP($G$1,表,1,6,0),被查找的值，比如：序号,2,查找的数据源，且被查找值（序号）在最左侧,返回值所在列数，第？列,默认写,0,，表示精确查找,LOOKUP,主要功能：在一组从小到大的数组中，返回被查找值所处区间对应的返回值。,使用格式：,=LOOKUP,（,Lookup_value,Lookup_vector,Result_vector,）,=LOOKUP(,绩效得分,$P$2:$P$6,$Q$2:$Q$6),被查找的值,含有被查找值的数据源，且按从小到大逻辑升序,与,范围大小一致的返回值所在区域,查找引用类函数：,VLOOKUP/HLOOKUP/LOOKUP,本节回顾,下面我带大家简单回顾一下本节课程的主要内容：,VLOOKUP,函数是我们最熟悉最常用的查找引用类函数，一定要牢固记忆其函数结构及参数意义，正确使用精确匹配和模糊匹配的两种情况。,与,VLOOKUP,函数按列查找不同，,HLOOKUP,函数是按行查找，函数结构基本相似。逆向查询时，则经常用到,LOOKUP,函数。,多练习，多总结他们使用过程中可能产生的问题。,2.6,统计类函数,条件计数求和函数：,SUMIF/SUMIFS/COUNTIF/COUNTIFS,本节介绍,计数与求和，是,HR,管理中经常要处理的问题之一。简单的计数与求和，往往通过,SUM,函数和,COUNT,函数就能轻松解决，大部分对这两个函数也都比较熟悉。,而对于相对复杂一些的条件计数与求和，比如每个年龄段的人数，某个学历层次的人数，不同部门的男女人数等问题，就得使用到更高阶的函数，如单个条件计数与求和的,COUNIF/SUMIF,多个条件计数与求和的,COUNTIFS/SUMIFS,等,02,常见函数应用,02-05,统计类函数,1,条件计数与求和,1,SUM,2,SUMIFS,3,COUNTIFS/COUNTA/COUNTBLACK,2,场景练习,1,不同工作表的合并,2,多条件求和与计数,3,HR,实景,1,员工籍贯分布情况统计,SUMIFS,主要功能：对一组给定条件指定的单元格求和,使用格式：,=SUMIFS,（求和区域,条件区域,1,条件,1,）,=SUMIFS(,表,2,人数,表,2,省份,J1),用于求和的区域，如：人数、合同金额、得分,条件的判定区域，如：性别、部门、年龄,条件判定区域需要满足的具体条件，比如：性别：“男”,本节回顾,下面我带大家简单回顾一下本节课程的主要内容：,单条件计数与求和，我们使用的函数是,COUNTIF,与,SUMIF；,多条件计数与求和，我们使用的函数是,COUNTIFS,与,SUMIFS,正确地区分条件和计算区域是掌握这四个函数的关键，函数结构也要记清楚呀,条件计数求和函数：,SUMIF/SUMIFS/COUNTIF/COUNTIFS,Thanks,从,HR,变,HERO,Excel,人力资源管理模块企业级应用课程,