常用的20个EXCEL函数的使用详解

条件函数和逻辑函数: IF函数、AND函数、OR函数
IF函数: IF函数也称条件函数，它根据参数条件的真假， 返回不同的结果。
= IF(条件表达式，条件值为真时返回的值，条件值为假时返回的值)
AND函数：AND函数表示逻辑与，当所有条件都满足时 （即所有参数的逻辑值都为真时），AND函数返回 TRUE，否则，只要有一个条件不满足即返回FALSE。
日期函数应用举例——编制下周计划表
编制下周计划表
可以在本周的任何一天制作下周的计划表
注意：任何一天的日期减去本身的星期数，就一定等于上一个星 期天的日期。
日期函数综合应用——设计考勤表
时间函数
输入时间
输入22:00与输入10:00 PM是一样的。
如何计算跨午夜零时的时间间隔？
输入：=B2+(B2<A2)-A2 或者：=B2+IF(B2<A2,1,0)-A2 思路：如果下班时间小于上班时间，就表示已经过了1天，因此 要加1。否则，如果下班时间大于上班时间，就表示还在当前， 因此不需要加1天。
日期函数应用举例 ——确定两个日期间的天数
说明 确定两个日期的间隔天数 确定两个日期的间隔天数 确定两个日期间的工作日天数(不包括政府假日) 确定两个日期间的工作日天数(包括政府假日) 返回当前日距月末还有多少个工作日
结果
公式
86 =DATEDIF("2006-9-12","2006-12-7","d")
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日")
= COUNTA(参数1，参数2，…，参数N ) COUNTIF函数: 计算给定区域内满足特定条件 的单元格的数目
= COUNTIF(range,criteria)
计数函数:应用举例
COUNT函数: COUNTA 函数: COUNTIF函数:
计数函数应用 ——计算销售业绩
计数函数: ——计算高于平均业绩的人数
数学函数 AVERGAE、 MAX、MIN、 ABS、 SQRT函数
常用的数学函数有：
AVERGAE函数：求平均值 MAX：求最大值 MIN：求最小值 ABS：求绝对值 SQRT：计算平方根
平均值函数: AVERGAE函数
AVERGAE函数 :求N个数的平均值 = AVERGAE(参数1,参数2,…,参数N)
日期函数: DAY、DATE、DAYS360、TODAY函数
DAY函数: 返回以序列号表示的某日期的天数，用整数 1～31 表示。
= DAY(日期序列号) 例如 DAY("2006-12-22")=22
DATE函数：返回代表特定日期的序列号。
= DATE(年,月,日) 例如 DATE(2006,12,23)= "2006-12-23"
= AND(条件1，条件2，…条件N)
OR函数：只要有一个条件满足时，OR函数返回TRUE， 只有当所有条件都不满足时才返回FALSE。
= OR(条件1,条件2,…条件N)
说明：这3个函数常常联合使用。
IF函数应用举例 之一 ——计算奖金
销售人员 销售额 奖金
AAAA
654 26.16
BBBB
325
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
函数的调用
直接在单元格中输入函数 利用插入函数按钮或命令插入函数
嵌套函数的使用
当一个函数中的参数为另外一个函数时， 就是在使用嵌套函数 。
利用插入函数按钮或命令插入各个函数。
使用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函数 其他函数
最大值与最小值函数: MAX、 MIN函数
MAX函数 : 求N个数的最大值 = MAX(参数1,参数2,…,参数N)
MIN函数: 求N个数的最小值 = MIN(参数1,参数2,…,参数N)
绝对值函数: ABS函数
ABS函数: 求某数的绝对值 = ABS(参数)
例如，ABS(-100)=100
开平方函数: SQRT函数
设置动态标题：
="今天是 "&TEXT(TODAY(),"yyyy年m月d日")
="今天是 "&TEXT(TODAY(),"yyyy年m月d日")&" "&TEXT(WEEKDAY(TODAY()),"aaaa")
设置上月标题：
=IF(MONTH(TODAY())=1,12,MONTH(TODAY())-1)&"月份收支情况“
示例数据 2006-12-7
2006-12-7
结果
公式
2006-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())
分析工具库里的日期函数
常用的20个EXCEL函数
第6章 使用函数
Excel函数的种类 函数的基本语法 函数的调用 嵌套函数的使用 使用Excel的帮助来理解函数 常用函数介绍 使用自定义函数
Excel函数的种类
财务函数 日期函数 时间函数 数学与三角函数 统计函数 数据库管理函数 文本函数 信息类函数
函数的基本语法
AND函数应用举例
销售人员 销售额 奖金
AAAA
654 26.16
BBBB
325
6.5
CCCC
876 35.04
DDDD
454 18.16
EEEE
755 30.2
来自百度文库
奖金提成比例
100-400
2%
400以上
4%
OR函数应用举例
商品 销售量
AA
654
真
BB
455
假
CC
544
真
DD
233
假
联合使用IF、AND和OR函数 计算奖金
=TEXT("2006-12-7","yyyy/mm/dd")
07/12/2006
=TEXT("2006-12-7","dd/mm/yyyy")
2006-12-07 Thursday =TEXT("2006-12-7","yyyy-mm-dd") & " " & TEXT(WEEKDAY("2006-12-7",1),"dddddd")
设置本月标题
=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”)
日期函数应用举例——确定具体日期
说明 确定某个月的最后一天 确定上个星期日的日期 确定上个星期六的日期 确定当年年份 确定当年月份 确定当日日期 确定当日是星期几 确定当日是星期几 确定一个日期所属的季度 确定上月月末日期
解决方法：巧妙应用第3行的标题。
求和函数: 应用举例 —— 往下（或往右）累加汇总
往下（或往右）累加汇总是常见的实务问题。
解决方法：使用SUM函数，但单元格引用的第一个地址 应为绝对引用，而第二个地址为相对引用。
求和函数: 应用举例 —— 动态汇总当天数据
工作表中存在每天的数据，要求动态汇总计算当前的数据。 可使用SUMIF函数。但要注意条件的写法。
DAYS360函数：按照一年 360 天计（每个月以 30 天， 一年共计 12 个月），返回两个日期间相差的天数。
= DAYS360(开始日期,截止日期,逻辑值)
例如 DAYS360("2000-1-15","2005-12-16")=2131天
TODAY函数：返回系统当前的日期。
日期函数应用(举例）
函数的基本语法为：
= 函数名(参数1,参数2,…,参数n)。
注意问题：
函数名与其后的括号“（”之间不能有空格。 当有多个参数时，参数之间要用逗号“，”分隔。 参数部分总长度不能超过1024个字符。 参数可以是数值、文本、逻辑值、单元格引用，也可以是各 种表达式或函数。 函数中的逗号“，”、引号“"”等都是半角字符，而不是 全角字符。
求和函数: SUM/SUMIF/SUMPRODUCT函数
SUM函数 :无条件求和 = SUM (参数1,参数2,…,参数N)
SUMIF函数 :条件求和
=SUMIF(range,criteria,sum_range) SUMPRODUCT函数 :在给定的几组数组中，将 数组间对应的元素相乘，并返回乘积之和
6.5
CCCC
876 35.04
DDDD
454 9.08
EEEE
755 30.2
奖金提成比例
500以下
2%
500以上
4%
IF函数应用举例 之二 ——计算增长率
计算增长率
当被比较年份没有数据时，会出现被除数为0的错误。 可以利用IF函数进行处理。
如图，去年没有数据，而今年有数据，就显示“新增项目”。 去年有数据，而今年没有数据，就显示“已经停产”。
SQRT函数: 求某数的平方根 = SQRT(参数)
例如，SQRT(2)=1.4142135623731
计数函数: COUNT、 COUNTA、COUNTIF函数
COUNT函数: 计算给定区域内数值型参数的数 目
= COUNT(参数1,参数2,…,参数N) COUNTA 函数: 返回参数列表中非空值的单元 格个数
IF函数应用举例 之三
自动归类问题
为了有效管理零用金，希望在输入现金开支数据后，该金额数据自 动依部门归类到适当的列，从而更加醒目地标示出来。
解决办法：使用IF函数
如图，先在单元格F2输入公式“=IF($D2=F$1,$C2, "")”。 选定单元格区域F2:J2，按【F2】，使处于编辑状态。 按【Ctrl+Enter】组合键 注意单元格的引用方式。
3 =MONTH(TODAY())
27 =DAY(TODAY())
2 =WEEKDAY(TODAY(),2)
Tuesday =TEXT(WEEKDAY(TODAY(),2),"dddddd")
4 =ROUNDUP(MONTH(B10)/3,0)
2007-2-28 =TODAY()-DAY(TODAY())
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"})
= SUMPRODUCT(array1,array2,array3, ...)
求和函数: 应用举例
SUM函数 SUMIF函数 SUMPRODUCT函数
求和函数: 应用举例 —— 隔栏加总计算
某预算表如图所示，每个月分为“预算数”和“实际 数”，现要求计算年度“预算数”和“实际数”。这实 际上就是隔栏加总问题。如果一个一个单元格相加是很 繁琐的。