常用EXCEL函数使用详解ppt课件.ppt

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,.,*,常用,EXCEL,函数讲解,2018,年,9,月,18,日,.,新手、初级用户、中级用户、高级用户和专家五个层次,新手：学习者需要大致了解到,Excel,的基本操作方法和常用功能，诸如输入数据，查找替换，设置单元格格式，排序、汇总、筛选和保存工作簿。,初级用户：可以开始在工作中运用,Excel,，比如建立一个简单的表格，画一张简单的图表，会一些基本简单的函数，如,SUM,、,IF,等。,中级用户三个标志：,一是理解并熟练使用各个,Excel,菜单命令,二是熟练使用数据透视表,三是至少掌握,20,个常用函数以及函数的嵌套运用，掌握的基本函数有,SUM,函数、,IF,函数、,VLOOKUP,函数、,INDEX,函数、,MATCH,函数、,OFFSET,函数、,TEXT,函数等等,.,高级用户两个标志：,一是熟练运用数组公式，也就是那种用花括号包围起来，必须用,组合键才能完成录入的公式。,Excel,专家：从,Excel,的功能细分来看，精通全部的人想必寥寥无几。,Excel,是应用性太强的软件，意味着一个没有任何工作经验的普通学生是很难成为,Excel,专家的。所以,Excel,专家也必定是某个或多个行业的专家，他们都拥有丰富的行业知识和经验。高超的,Excel,技术配合行业经验来共同应用，才有可能把,Excel,发挥到极致，所以，如果希望成为,Excel,专家，就不能只单单学习,Excel,了。,.,Excel,函数的种类,财务函数,日期函数,时间函数,数学与三角函数,统计函数,数据库管理函数,文本函数,信息类函数,.,函数的基本语法,函数的基本语法为：,=,函数名,(,参数,1,参数,2,参数,n,),。,注意问题：,函数名与其后的括号,“,（,”,之间不能有空格。,当有多个参数时，参数之间要用逗号“，”分隔。,参数部分总长度不能超过,1024,个字符。,参数可以是数值、文本、逻辑值、单元格引用，也可以是各种表达式或函数。,函数中的逗号“，”、引号,“,”,等都是半角字符，而不是全角字符。,.,常用函数介绍,求和函数,:SUM/SUMIF/SUMPRODUCT,函数,数学函数,AVERGAE,、,MAX,、,MIN,、,ABS,、,SQRT,函数,计数函数,:COUNT,、,COUNTA,、,COUNTIF,函数,条件函数和逻辑函数,:IF,函数、,AND,函数、,OR,函数,日期和时间函数,:DAY,、,DATE,、,DAYS360,、,TODAY,函数等,分析工具库里的日期函数,查找和引用函数,:VLOOKUP,、,HLOOKUP,、,MATCH,、,INDEX,、,CHOOSE,、,OFFSET,函数,四舍五入函数,:ROUND,、,FLOOR,和,CEILING,函数,取整函数,:INT,函数,信息函数,:ISBLANK,、,ISTEXT,、,ISNUMBER,、,ISERROR,函数,文本函数：,LEN,、,LEFT,、,RIGHT,、,TRIM,、,FIND,、,TEXT,等函数,排序函数,:RANK,、,LARGE,、,SMALL,函数,数据库函数：,DGET,、,DMAX,、,DMIN,、,DSUM,、,DAVGEAGE,函数,其他函数,.,求和函数,SUM/SUMIF/SUMPRODUCT,函数,SUM,函数,:,无条件求和,=SUM(,参数,1,参数,2,参数,N,),SUMIF,函数,:,条件求和,=,SUMIF(range,criteria,sum_range,),SUMPRODUCT,函数,:,在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和,=SUMPRODUCT(array1,array2,array3,.),.,求和函数,:,应用举例,SUM,函数,SUMIF,函数,SUMPRODUCT,函数,.,求和函数,:,应用举例,隔栏加总计算,某预算表如图所示，每个月分为“预算数”和“实际数”，现要求计算年度“预算数”和“实际数”。这实际上就是隔栏加总问题。,如果一个一个单元格相加是很繁琐的。,解决方法：巧妙应用第,3,行的标题。,.,求和函数,:,应用举例,往下（或往右）累加汇总,往下（或往右）累加汇总是常见的实务问题。,解决方法：使用,SUM,函数，但单元格引用的第一个地址应为绝对引用，而第二个地址为相对引用。,.,求和函数,:,应用举例,动态汇总当天数据,工作表中存在每天的数据，要求动态汇总计算当前的数据。,可使用,SUMIF,函数。但要注意条件的写法。,.,数学函数,AVERGAE,、,MAX,、,MIN,、,ABS,、,SQRT,函数,常用的数学函数有：,AVERGAE,函数：求平均值,MAX,：求最大值,MIN,：求最小值,ABS,：求绝对值,SQRT,：计算平方根,.,平均值函数,:AVERGAE,函数,AVERGAE,函数,:,求,N,个数的平均值,=,AVERGAE,(,参数,1,参数,2,参数,N,),.,最大值与最小值函数,:MAX,、,MIN,函数,MAX,函数,:,求,N,个数的最大值,=,MAX,(,参数,1,参数,2,参数,N,),MIN,函数,:,求,N,个数的最小值,=MIN(,参数,1,参数,2,参数,N,),.,绝对值函数,:ABS,函数,ABS函数,:,求某数的绝对值,=,ABS,(,参数,),例如，,ABS(-100)=100,.,开平方函数,:SQRT,函数,SQRT函数,:,求某数的平方根,=,SQRT,(,参数,),例如，,SQRT(2)=1.4142135623731,.,计数函数,:COUNT,、,COUNTA,、,COUNTIF,函数,COUNT函数,:,计算给定区域内数值型参数的数目,=COUNT(,参数,1,参数,2,参数,N,),COUNTA,函数,:,返回参数列表中非空值的单元格个数,=COUNTA(,参数,1,，参数,2,，,，参数,N,),COUNTIF,函数,:,计算给定区域内满足特定条件的单元格的数目,=,COUNTIF(range,criteria,),.,计数函数,:,应用举例,COUNT函数,:,COUNTA,函数,:,COUNTIF,函数,:,.,计数函数应用,计算销售业绩,.,计数函数,:,计算高于平均业绩的人数,.,条件函数和逻辑函数,:IF,函数、,AND,函数、,OR,函数,IF,函数,:IF,函数也称条件函数，它根据参数条件的真假，返回不同的结果。,=IF(,条件表达式，条件值为真时返回的值，条件值为假时返回的值,),AND,函数：,AND,函数表示逻辑与，当所有条件都满足时（即所有参数的逻辑值都为真时），,AND,函数返回,TRUE,，否则，只要有一个条件不满足即返回,FALSE,。,=AND(,条件,1,，条件,2,，,条件,N),OR,函数：只要有一个条件满足时，,OR,函数返回,TRUE,，只有当所有条件都不满足时才返回,FALSE,。,=OR(,条件,1,条件,2,条件,N),说明：这,3,个函数常常联合使用。,.,IF,函数应用举例 之一,计算奖金,.,IF,函数应用举例 之二,计算增长率,计算增长率,当被比较年份没有数据时，会出现被除数为,0,的错误。,可以利用,IF,函数进行处理。,如图，去年没有数据，而今年有数据，就显示“新增项目”。,去年有数据，而今年没有数据，就显示“已经停产”。,.,IF,函数应用举例 之三,自动归类问题,为了有效管理零用金，希望在输入现金开支数据后，该金额数据自动依部门归类到适当的列，从而更加醒目地标示出来。,解决办法：使用,IF,函数,如图，先在单元格,F2,输入公式“,=IF($D2=F$1,$C2,)”,。,选定单元格区域,F2:J2,，按,【F2】,，使处于编辑状态。,按,【,Ctrl+Enter,】,组合键,注意单元格的引用方式。,.,AND,函数应用举例,.,OR,函数应用举例,.,联合使用,IF,、,AND,和,OR,函数计算奖金,.,日期函数,:DAY,、,DATE,、,DAYS360,、,TODAY,函数,DAY,函数,:,返回以序列号表示的某日期的天数，用整数 131 表示,。,=DAY(,日期序列号,),例如,DAY(2006-12-22)=22,DATE,函数：,返回代表特定日期的序列号,。,=DATE(,年,月,日,),例如,DATE(2006,12,23)=2006-12-23,DAYS360,函数：,按照一年 360 天计（每个月以 30 天，一年共计 12 个月），返回两个日期间相差的天数,。,=DAYS360(,开始日期,截止日期,逻辑值,),例如,DAYS360(2000-1-15,2005-12-16)=2131,天,TODAY,函数：返回系统当前的日期。,.,日期函数应用,(,举例）,设置动态标题：,=,今天是,&,TEXT(TODAY(),yyyy,年,m,月,d,日,),=,今天是,&,TEXT(TODAY(),yyyy,年,m,月,d,日,)&,TEXT(WEEKDAY(TODAY(),aaaa,),设置上月标题：,=IF(MONTH(TODAY()=1,12,MONTH(TODAY()-1)&,月份收支情况“,设置本月标题,=MONTH(TODAY()&,月份收支情况“,将日期转换为星期,中文星期：,=TEXT(WEEKDAY(A1),aaaa),英文星期：,=TEXT(WEEKDAY(A1),“dddd),计算,2,年,5,个月,20,天后的日期,=DATE(YEAR(A1)+2,MONTH(A1)+5,DAY(S1)+20),计算实际岁数：,=INT(YEARFRAC(,出生日期,今天,1),=DATEIF(,出生日期,今天,”Y”),.,日期函数应用举例,确定具体日期,.,日期函数应用举例,确定两个日期间的天数,.,日期函数应用举例,设置日期显示格式,.,日期函数应用举例,编制下周计划表,编制下周计划表,可以在本周的任何一天制作下周的计划表,注意：任何一天的日期减去本身的星期数，就一定等于上一个星期天的日期。,.,日期函数综合应用,设计考勤表,.,时间函数,输入时间,输入,22:00,与输入,10:00 PM,是一样的。,如何计算跨午夜零时的时间间隔？,输入：,=B2+(B2A2)-A2,或者：,=B2+IF(B2A2,1,0)-A2,思路：如果下班时间小于上班时间，就表示已经过了,1,天，因此要加,1,。否则，如果下班时间大于上班时间，就表示还在当前，因此不需要加,1,天。,.,分析工具库里的日期函数,EDATE,函数,EOMONTH,函数,WEEKNUM,函数,WORKDAY,函数,NETWORKDAYS,函数,一个特殊的日期函数：,DATEDIF,注意：在使用这些函数之前，必须加载分析工具库，即：单击,【工具】,【加载宏】命令，打开【加载宏】对话框，选择“,分析工具库,”，,如上图。,.,EDATE,函数,EDATE,函数：返回指定日期往前或往后几个月的日期。,例：,2007,年,4,月,12,日之后,3,个月的日期：,=EDATE(“2007-4-12”,3),，为,2007-7-12,2007,年,4,月,12,日之前,3,个月的日期：,=EDATE(“2007-4-12”,-3),，为,2007-1-12,计算应付账款的到期日：,如果一笔应付款的到期日为自交易日起,3,个月的那一天，比如交易日为,2006,年,11,月,30,日，满,3,个月后为,2007-2-28,：,=EDATE(“2006-11-30”,3),，为,2007-2-28,.,EOMONTH函数,EOMONTH,函数：返回指定日期往前或往后几个月的特定月份的月底日期。,例：,2007,年,4,月,12,日之后,3,个月的月末日期：,=EOMONTH(“2007-4-12”,3),，为,2007-7-31,2007,年,4,月,12,日之前,5,个月的月末日期：,=EDATE(“2007-4-12”,-5),，为,2006-11-30,计算应付账款的到期日：,如果一笔应付款的到期日为自交易日起满,3,个月后的下一个月的,5,号，比如交易日为,2006,年,11,月,20,日，满,3,个月后下个月,5,号就是,2007-3-5,：,=EOMONTH(2006-11-20,3-(DAY(A1)0.5),表示只有在完成率超过,50%,时才利用公式,(B2-0.49999)*10,进行计算。,.,信息函数,:ISBLANK,、,ISTEXT,、,ISNUMBER,、,ISERROR,函数,ISBLANK,函数：判断单元格是否为空白单元格,ISTEXT,函数：判断单元格数据是否为文本,ISNUMBER,函数：判断单元格数据是否为数字,ISERROR,函数：判断单元格是否出现错误,.,文本函数,LEN,、,LEFT,、,RIGHT,、,TRIM,、,FIND,、,TEXT,等函数,LEN,函数：获取字符串的长度,LEFT,函数：获取字符串左边指定个数的文本,RIGHT,函数：获取字符串右边指定个数的文本,TRIM,函数：取消字符串两侧的空格,FIND,函数：查找某字符在字符串中第一次出现的位置,TEXT,函数：将数值转换为按指定数字格式表示的文本,其他的文本函数,.,文本函数应用举例,例,1,：,LEN(ABCD),=4,LEFT(ABCD,2),=,AB,RIGHT,(ABCD,2),=,CD,TRIM(ABCD),=,ABCD,FIND(.,12345.65),=6,TEXT(12345.6687,￥,0.00)=,￥,12345.67,例,2,：从身份证号码获取出生日期和性别,.,文本函数应用举例,例,3,：将科目编码与科目名称分离（假设科目编码与科目名称之间有一个空格）,问题：如果科目编码与科目名称之间没有空格，该怎么办呢？,可使用,LEN,函数和,LENB,函数：,.,文本函数应用举例,例,4,：编制简单的统计表,选取单元格,C2,。,输入“,=REPT(,”,，（不要按回车键，还没有完成输入）,单击,【,插入,】,【,特殊符号,】,命令，插入一个黑正方形。,接着输入“,B2*50),”,将单元格,C2,向下填充复制。,最后将,C,列的各个单元格的字体颜色分别设置为不同的颜色,.,排序函数,:RANK,、,LARGE,、,SMALL,函数,RANK,函数：返回一个数字在数字列表中的排位。,LARGE,函数：返回数据集中第,k,个最大值。例如，可以使用函数,LARGE,得到第一名、第二名或第三名的得分。,SMALL,函数：返回数据集中第,k,个最小值。,.,排序函数应用举例,.,数据库函数：,DGET,、,DMAX,、,DMIN,、,DSUM,、,DAVGEAGE,函数,Microsoft Excel,提供了一些用于对存储在列表或数据库中的数据进行分析的函数，这些函数统称为数据库函数（,Dfunctions,）。这些函数的语法结构基本相同，即：,Dfunctions(database,field,criteria),函数的三个参数,database,、,field,和,criteria,的含义如下：,database,：构成列表或数据库的单元格区域。数据库是包含一组相关数据的列表，其中包含相关信息的行为记录，而包含数据的列为字段。列表的第一行包含着每一列的标志项。,field,：指定函数所使用的数据列。列表中的数据列必须在第一行具有标志项。,Field,可以是文本，即两端带引号的标志项，如“使用年数”或“产量”；此外，,Field,也可以是代表列表中数据列位置的数字：,1,表示第一列，,2,表示第二列，等等。,criteria,：为一组包含给定条件的单元格区域。可以为参数,criteria,指定任意区域，只要它至少包含一个列标志和列标志下方用于设定条件的单元格。,在财务管理中，常用的数据库函数有,DSUM,函数、,DMAX,函数、,DMIN,函数、,DGET,函数等。,.,复合条件的语法,同一行的构成了“与”关系条件。,查找薪金在,5000,至,8000,元之间的纪录。,同一列构成了“或”关系条件。,查找部门为销售部和办公室的记录。,不同列、同行构成了不同字段的“与”关系。,查找男性且为经理的纪录。,不同列、不同行构成了不同字段的“或”关系条件。,查找职称为高级工程师或职务为经理的纪录。,不同列、不同行的“与”关系和“或”关系的复杂条件：,查找办公室的女性经理、或者销售部的男性高级工程师的纪录。,.,DSUM,函数应用举例,.,DGET,函数应用举例,.,DMAX,函数和,DMIN,函数应用举例,.,谢谢！,.,