excel函数的应用课件

《excel函数的应用课件》由会员分享，可在线阅读，更多相关《excel函数的应用课件（81页珍藏版）》请在金锄头文库上搜索。

1、第6章 使用函数,Excel函数的种类 函数的基本语法 函数的调用 嵌套函数的使用 使用Excel的帮助来理解函数 常用函数介绍 使用自定义函数,Excel函数的种类,财务函数 日期函数 时间函数 数学与三角函数 统计函数 数据库管理函数 文本函数 信息类函数,函数的基本语法,函数的基本语法为： = 函数名(参数1,参数2,参数n)。 注意问题： 函数名与其后的括号“（”之间不能有空格。 当有多个参数时，参数之间要用逗号“，”分隔。 参数部分总长度不能超过1024个字符。 参数可以是数值、文本、逻辑值、单元格引用，也可以是各种表达式或函数。 函数中的逗号“，”、引号“”等都是半角字符，而不是全

2、角字符。,函数的调用,直接在单元格中输入函数 利用插入函数按钮或命令插入函数,嵌套函数的使用,当一个函数中的参数为另外一个函数时，就是在使用嵌套函数 。 利用插入函数按钮或命令插入各个函数。,使用Excel的帮助来理解函数,直接在Excel右上角输入函数名 利用Excel函数帮助任务窗格。,常用函数介绍,求和函数: SUM/SUMIF/SUMPRODUCT函数 数学函数 AVERGAE、 MAX、MIN、 ABS、 SQRT函数 计数函数: COUNT、COUNTA、COUNTIF函数 条件函数和逻辑函数: IF函数、AND函数、OR函数 日期和时间函数: DAY、DATE、DAYS360、T

3、ODAY函数等 分析工具库里的日期函数 查找和引用函数: 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函数 :无

4、条件求和 = SUM (参数1,参数2,参数N) SUMIF函数 :条件求和 =SUMIF(range,criteria,sum_range) SUMPRODUCT函数 :在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和 = SUMPRODUCT(array1,array2,array3, .),求和函数: 应用举例,SUM函数 SUMIF函数 SUMPRODUCT函数,求和函数: 应用举例 隔栏加总计算,某预算表如图所示，每个月分为“预算数”和“实际数”，现要求计算年度“预算数”和“实际数”。这实际上就是隔栏加总问题。如果一个一个单元格相加是很繁琐的。 解决方法：巧妙应用第3行的标

5、题。,求和函数: 应用举例 往下（或往右）累加汇总,往下（或往右）累加汇总是常见的实务问题。 解决方法：使用SUM函数，但单元格引用的第一个地址应为绝对引用，而第二个地址为相对引用。,求和函数: 应用举例 动态汇总当天数据,工作表中存在每天的数据，要求动态汇总计算当前的数据。 可使用SUMIF函数。但要注意条件的写法。,数学函数 AVERGAE、 MAX、MIN、 ABS、 SQRT函数,常用的数学函数有： AVERGAE函数：求平均值 MAX：求最大值 MIN：求最小值 ABS：求绝对值 SQRT：计算平方根,平均值函数: AVERGAE函数,AVERGAE函数 :求N个数的平均值 = AV

6、ERGAE(参数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,

7、参数2,参数N) COUNTA 函数: 返回参数列表中非空值的单元格个数 = COUNTA(参数1，参数2，参数N ) COUNTIF函数: 计算给定区域内满足特定条件的单元格的数目 = COUNTIF(range,criteria),计数函数:应用举例,COUNT函数: COUNTA 函数: COUNTIF函数:,计数函数应用 计算销售业绩,计数函数: 计算高于平均业绩的人数,条件函数和逻辑函数: IF函数、AND函数、OR函数,IF函数: IF函数也称条件函数，它根据参数条件的真假，返回不同的结果。 = IF(条件表达式，条件值为真时返回的值，条件值为假时返回的值) AND函数：AND函数

8、表示逻辑与，当所有条件都满足时（即所有参数的逻辑值都为真时），AND函数返回TRUE，否则，只要有一个条件不满足即返回FALSE。 = AND(条件1，条件2，条件N) OR函数：只要有一个条件满足时，OR函数返回TRUE，只有当所有条件都不满足时才返回FALSE。 = OR(条件1,条件2,条件N) 说明：这3个函数常常联合使用。,IF函数应用举例 之一 计算奖金,IF函数应用举例 之二 计算增长率,计算增长率 当被比较年份没有数据时，会出现被除数为0的错误。 可以利用IF函数进行处理。 如图，去年没有数据，而今年有数据，就显示“新增项目”。 去年有数据，而今年没有数据，就显示“已经停产”。

9、,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(日期序列号) 例

10、如 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日“) =“今天是 “&TE

11、XT(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(出生日期,今天

12、,1) =DATEIF(出生日期,今天,”Y”),日期函数应用举例确定具体日期,日期函数应用举例 确定两个日期间的天数,日期函数应用举例 设置日期显示格式,日期函数应用举例编制下周计划表,编制下周计划表 可以在本周的任何一天制作下周的计划表 注意：任何一天的日期减去本身的星期数，就一定等于上一个星期天的日期。,日期函数综合应用设计考勤表,时间函数,输入时间 输入22:00与输入10:00 PM是一样的。 如何计算跨午夜零时的时间间隔？ 输入：=B2+(B2A2)-A2 或者：=B2+IF(B2A2,1,0)-A2 思路：如果下班时间小于上班时间，就表示已经过了1天，因此要加1。否则，如果下班时

13、间大于上班时间，就表示还在当前，因此不需要加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-1

14、2”,-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 计算应付账款的到期日：

15、如果一笔应付款的到期日为自交易日起满3个月后的下一个月的5号，比如交易日为2006年11月20日，满3个月后下个月5号就是2007-3-5： =EOMONTH(“2006-11-20“,3-(DAY(A1)=5)+5,WEEKNUM函数,WEEKNUM函数：返回指定日期是该年的第几周。 例： 2007年4月12日是2007年的第15周： =WEEKNUM(“2007-4-12”)，为第15周,WORKDAY函数,WORKDAY函数：返回某指定日期之前或之后的给定工作日天数的日期（除去双休日和国家法定假日） 。 例： 指定日期为2007年4月12日，往后30个工作日的日期为（要出去国家法定的五一

16、3天假日）2007年5月29日： =WORKDAY(“2007-4-12“,30,“2007-5-1“,“2007-5-2“,“2007-5-3“) 假若一项工程开始日期为2007年4月20日，预计需要60个工作日，那么预计完工的时间为哪天？,NETWORKDAYS函数,NETWORKDAYS函数：返回两个工作日之间的工作天数（除去双休日和国家法定假日）。 例： 2007年4月12日至2007年6月20日之间的工作天数（除去双休日和国家法定假日） 为47天。,一个特殊的日期函数：DATEDIF 确定两个日期间的年数、月数和天数,DATEDIF函数：计算两个日期之间的天数、月数或年数。这个函数是一个特殊函数，在函数清单中找不到，在帮助信息中也找不到。 DATEDIF(开始日期, 结束日期, 单位) 单位 意义 “Y ” 时间段中的总年数