EXCEL函数

EXCEL函数Excel函数的种类函数的基本语法函数的调用嵌套函数的使用使用Excel的帮助来理解函数常用函数介绍使用自定义函数Excel函数的种类•财务函数•日期函数•时间函数•数学与三角函数•统计函数•数据库管理函数•文本函数•信息类函数函数的基本语法•函数的基本语法为：=函数名(参数1,参数2,…,参数n)。•注意问题：函数名与其后的括号“（”之间不能有空格。当有多个参数时，参数之间要用逗号“，”分隔。参数部分总长度不能超过1024个字符。参数可以是数值、文本、逻辑值、单元格引用，也可以是各种表达式或函数。函数中的逗号“，”、引号“”等都是半角字符，而不是全角字符。函数的调用•直接在单元格中输入函数•利用插入函数按钮或命令插入函数嵌套函数的使用•当一个函数中的参数为另外一个函数时，就是在使用嵌套函数。•利用插入函数按钮或命令插入各个函数。使用Excel的帮助来理解函数•直接在Excel右上角输入函数名•利用Excel函数帮助任务窗格。常用函数介绍•求和函数: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函数应用举例之一——计算奖金销售人员销售额奖金奖金提成比例AAAA65426.16500以下2%BBBB3256.5500以上4%CCCC87635.04DDDD4549.08EEEE75530.2IF函数应用举例之二——计算增长率•计算增长率当被比较年份没有数据时，会出现被除数为0的错误。可以利用IF函数进行处理。如图，去年没有数据，而今年有数据，就显示“新增项目”。去年有数据，而今年没有数据，就显示“已经停产”。IF函数应用举例之三•自动归类问题为了有效管理零用金，希望在输入现金开支数据后，该金额数据自动依部门归类到适当的列，从而更加醒目地标示出来。•解决办法：使用IF函数如图，先在单元格F2输入公式“=IF($D2=F$1,$C2,)”。选定单元格区域F2:J2，按【F2】，使处于编辑状态。按【Ctrl+Enter】组合键注意单元格的引用方式。AND函数应用举例销售人员销售额奖金奖金提成比例AAAA65426.16100-4002%BBBB3256.5400以上4%CCCC87635.04DDDD45418.16EEEE75530.2OR函数应用举例商品销售量AA654真BB455假CC544真DD233假联合使用IF、AND和OR函数计算奖金日期函数:DAY、DATE、DAYS360、TODAY函数•DAY函数:返回以序列号表示的某日期的天数，用整数1～31表示。=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”)日期函数应用举例——确定具体日期说明示例数据结果公式确定某个月的最后一天2006-12-72006-12-31=DATE（YEAR(B2),MONTH(B2)+1,0）确定上个星期日的日期2007-3-25=TODAY()-MOD(TODAY()-1,7)确定上个星期六的日期2007-3-24=TODAY()-MOD(TODAY(),7)确定当年年份2007=YEAR(TODAY())确定当年月份3=MONTH(TODAY())确定当日日期27=DAY(TODAY())确定当日是星期几2=WEEKDAY(TODAY(),2)确定当日是星期几Tuesday=TEXT(WEEKDAY(TODAY(),2),dddddd)确定一个日期所属的季度2006-12-74=ROUNDUP(MONTH(B10)/3,0)确定上月月末日期2007-2-28=TODAY()-DAY(TODAY())日期函数应用举例——确定两个日期间的天数说明结果公式确定两个日期的间隔天数86=DATEDIF(2006-9-12,2006-12-7,d)确定两个日期的间隔天数86=VALUE(2006-12-7-2006-9-12)确定两个日期间的工作日天数(不包括政府假日)35=NETWORKDAYS(2006-11-12,2006-12-31)确定两个日期间的工作日天数(包括政府假日)31=NETWORKDAYS(2006-4-8,2006-5-25,{2006-5-1;2006-5-2;2006-5-3})返回当前日距月末还有多少个工作日4=NETWORKDAYS(TODAY(),EOMONTH(TODAY(),0))日期函数应用举例——设置日期显示格式日期显示公式2006-12-07=TEXT(2006-12-7,yyyy-mm-dd)2006年12月7日=TEXT(2006-12-7,yyyy年m月d日)2006/12/07=TEXT(2006-12-7,yyyy/mm/dd)07/12/2006=TEXT(2006-12-7,dd/mm/yyyy)2006-12-07Thursday=TEXT(2006-12-7,yyyy-mm-dd)&&TEXT(WEEKDAY(2006-12-7,1),dddddd)2006年12月7日星期四=TEXT(2006-12-7,yyyy年m月d日)&&TEXT(2006-12-7,[$-804]aaaa;@)2006年12月=TEXT(2006-12-7,yyyy年m月)12月7日=TEXT(2006-12-7,m月d日)日期函数应用举例——编制下周计划表•编制下周计划表可以在本周的任何一天制作下周的计划表注意：任何一天的日期减去本身的星期数，就一定等于上一个星期天的日期。日期函数综合应用——设计考勤表时间函数•输入时间输入22:00与输入10:00PM是一样的。•如何计算跨午夜零时的时间间隔？输入：=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-122007年4月12日之前3个月的日期：=EDATE(“2007-4-12”,-3)，为2007-1-12计算应付账款的到期日：如果一笔应付款的到期日为自交易日起3个月