常用EXCEL函数使用详解.ppt

《常用EXCEL函数使用详解.ppt》由会员分享，可在线阅读，更多相关《常用EXCEL函数使用详解.ppt（77页珍藏版）》请在优知文库上搜索。

1、常用常用EXCEL函数讲解函数讲解 新手、初级用户、中级用户、高级用户和专家五个层次新手、初级用户、中级用户、高级用户和专家五个层次 新手：学习者需要大致了解到新手：学习者需要大致了解到Excel的基本操作方法和常用功能，诸如输的基本操作方法和常用功能，诸如输入数据，查找替换，设置单元格格式，排序、汇总、筛选和保存工作簿。入数据，查找替换，设置单元格格式，排序、汇总、筛选和保存工作簿。 初级用户：可以开始在工作中运用初级用户：可以开始在工作中运用Excel，比如建立一个简单的表格，画，比如建立一个简单的表格，画一张简单的图表，会一些基本简单的函数，如一张简单的图表，会一些基本简单的函数，如SU

2、M、IF 等。等。中级用户三个标志：中级用户三个标志：一是理解并熟练使用各个一是理解并熟练使用各个Excel菜单命令菜单命令二是熟练使用数据透视表二是熟练使用数据透视表三是至少掌握三是至少掌握20个常用函数以及函数的嵌套运用，掌握的基本函数有个常用函数以及函数的嵌套运用，掌握的基本函数有SUM函数、函数、IF函数、函数、VLOOKUP函数、函数、INDEX函数、函数、MATCH函数、函数、OFFSET函数、函数、TEXT函数等等函数等等 高级用户两个标志：高级用户两个标志：一是熟练运用数组公式，也就是那种用花括号包围起来，必须用一是熟练运用数组公式，也就是那种用花括号包围起来，必须用组合键才能

3、完成录入的公式。组合键才能完成录入的公式。Excel专家：专家： 从从Excel的功能细分来看，精通全部的人想必寥寥无几。的功能细分来看，精通全部的人想必寥寥无几。Excel是应用性太强的软件，意味着一个没有任何工作经验的普通学生是应用性太强的软件，意味着一个没有任何工作经验的普通学生是很难成为是很难成为Excel专家的。所以专家的。所以Excel专家也必定是某个或多个行业的专专家也必定是某个或多个行业的专家，他们都拥有丰富的行业知识和经验。高超的家，他们都拥有丰富的行业知识和经验。高超的Excel技术配合行业经技术配合行业经验来共同应用，才有可能把验来共同应用，才有可能把Excel发挥到极致

4、，所以，如果希望成为发挥到极致，所以，如果希望成为Excel专家，就不能只单单学习专家，就不能只单单学习Excel了了 。Excel函数的种类财务函数日期函数时间函数数学与三角函数统计函数数据库管理函数文本函数信息类函数 函数的基本语法函数的基本语法为： = 函数名(参数1,参数2,参数n)。注意问题：函数名与其后的括号“（”之间不能有空格。当有多个参数时，参数之间要用逗号“，”分隔。参数部分总长度不能超过1024个字符。参数可以是数值、文本、逻辑值、单元格引用，也可以是各种表达式或函数。函数中的逗号“，”、引号“”等都是半角字符，而不是全角字符。常用函数介绍求和函数求和函数: SUM/SUM

5、IF/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函

6、数函数 取整函数取整函数: 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)

7、 SUMPRODUCT函数 :在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和 = SUMPRODUCT(array1,array2,array3, .) 求和函数: 应用举例SUM函数 SUMIF函数SUMPRODUCT函数求和函数: 应用举例 隔栏加总计算某预算表如图所示，每个月分为“预算数”和“实际数”，现要求计算年度“预算数”和“实际数”。这实际上就是隔栏加总问题。如果一个一个单元格相加是很繁琐的。解决方法：巧妙应用第3行的标题。求和函数: 应用举例 往下（或往右）累加汇总往下（或往右）累加汇总是常见的实务问题。解决方法：使用SUM函数，但单元格引用的第一个地址应为绝对引用，

8、而第二个地址为相对引用。求和函数: 应用举例 动态汇总当天数据工作表中存在每天的数据，要求动态汇总计算当前的数据。可使用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

9、个数的最小值 = 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函数: 计算给定区域内满足特定条件的单元格的

10、数目 = COUNTIF(range,criteria) 计数函数:应用举例COUNT函数:COUNTA 函数:COUNTIF函数:计数函数应用计算销售业绩计数函数:计算高于平均业绩的人数条件函数和逻辑函数: IF函数、AND函数、OR函数IF函数: IF函数也称条件函数，它根据参数条件的真假，返回不同的结果。 = IF(条件表达式，条件值为真时返回的值，条件值为假时返回的值) AND函数：AND函数表示逻辑与，当所有条件都满足时（即所有参数的逻辑值都为真时），AND函数返回TRUE，否则，只要有一个条件不满足即返回FALSE。 = AND(条件1，条件2，条件N)OR函数：只要有一个条件满足

11、时，OR函数返回TRUE，只有当所有条件都不满足时才返回FALSE。 = OR(条件1,条件2,条件N)说明：这3个函数常常联合使用。IF函数应用举例 之一计算奖金IF函数应用举例 之二计算增长率计算增长率当被比较年份没有数据时，会出现被除数为0的错误。可以利用IF函数进行处理。如图，去年没有数据，而今年有数据，就显示“新增项目”。去年有数据，而今年没有数据，就显示“已经停产”。IF函数应用举例 之三自动归类问题 为了有效管理零用金，希望在输入现金开支数据后，该金额数据自动依部门归类到适当的列，从而更加醒目地标示出来。解决办法：使用IF函数如图，先在单元格F2输入公式“=IF($D2=F$1,

12、$C2, )”。选定单元格区域F2:J2，按【F2】，使处于编辑状态。按【Ctrl+Enter】组合键注意单元格的引用方式。AND函数应用举例OR函数应用举例联合使用IF、AND和OR函数计算奖金日期函数: DAY、DATE、DAYS360、TODAY函数DAY函数: 返回以序列号表示的某日期的天数，用整数 131 表示。 = DAY(日期序列号) 例如 DAY(2006-12-22)=22DATE函数：返回代表特定日期的序列号。 = DATE(年,月,日) 例如 DATE(2006,12,23)= 2006-12-23DAYS360函数：按照一年 360 天计（每个月以 30 天，一年共计

13、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()&月份收支情况“将日期转换为星期中文星期

14、：=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”)日期函数应用举例确定具体日期日期函数应用举例确定两个日期间的天数日期函数应用举例设置日期显示格式日期函数应用举例编制下周计划表编制下周计划表可以在本周的任何一天制作下周的计划表注意：任何一天的日期减去本身的星期数，就一定等于上一个星期天的日期。日期函数综合应用设计考勤表时间函数输入

15、时间输入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函数：返回

16、指定日期往前或往后几个月的日期。例：2007年4月12日之后3个月的日期：=EDATE(“2007-4-12”,3)，为2007-7-122007年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-28EOMONTH函数EOMONTH函数：返回指定日期往前或往后几个月的特定月份的月底日期。例：2007年4月12日之后3个月的月末日期：= EOMONTH(“2007-4-12”,3)，为2007-7-312007年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)表示只有