EXCLE常见函数应用.ppt
,从HR变HERO,提高桌面生产力,Excel人力资源管理模块企业级应用课程,常见函数应用,本章介绍,精选了HR在工作中最常用的20个左右的Excel函数，力求通过科学的讲解方法，把这些函数的使用方法及应用讲清、讲透，让你轻松跨越函数障碍，实现Excel水平快速进步，提高工作效率。1、逻辑函数：if、and、or 2、文本函数：left、right、mid、len、text 3、日期函数：today、now、date、edate、datedif4、引用函数：vlookup5、统计函数：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 常见函数应用,=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 常见函数应用,=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 常见函数应用,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 常见函数应用,SUMIFS,主要功能：对一组给定条件指定的单元格求和使用格式：=SUMIFS（求和区域,条件区域1,条件1）,=SUMIFS(表2人数,表2省份,J1),用于求和的区域，如：人数、合同金额、得分,条件的判定区域，如：性别、部门、年龄,条件判定区域需要满足的具体条件，比如：性别：“男”,本节回顾,下面我带大家简单回顾一下本节课程的主要内容：单条件计数与求和，我们使用的函数是COUNTIF与SUMIF；多条件计数与求和，我们使用的函数是COUNTIFS与SUMIFS 正确地区分条件和计算区域是掌握这四个函数的关键，函数结构也要记清楚呀,条件计数求和函数：SUMIF/SUMIFS/COUNTIF/COUNTIFS,
