常用EXCEL函数使用详解

计数函数:应用举例
COUNT函数:
COUNTA 函数:
COUNTIF函数:
计数函数应用 ——计算销售业绩
计数函数: ——计算高于平均业绩的人数
条件函数和逻辑函数: IF函数、AND函数、OR函数
IF函数: IF函数也称条件函数，它根据参数条件的真假， 返回不同的结果。
= IF(条件表达式，条件值为真时返回的值，条件值为假时返回的值)
高级用户两个标志：
一是熟练运用数组公式，也就是那种用花括号包围起来，必须用 <Ctrl+Shift+Enter>组合键才能完成录入的公式。
Excel专家： 从Excel的功能细分来看，精通全部的人想必寥寥无几。 Excel是应用性太强的软件，意味着一个没有任何工作经验的普通学生
是很难成为Excel专家的。所以Excel专家也必定是某个或多个行业的专
TODAY函数：返回系统当前的日期。
日期函数应用(举例）
设置动态标题：
="今天是 "&TEXT(TODAY(),"yyyy年m月d日")
="今天是 "&TEXT(TODAY(),"yyyy年m月d日")&" "&TEXT(WEEKDAY(TODAY()),"aaaa")
设置上月标题：
=IF(MONTH(TODAY())=1,12,MONTH(TODAY())-1)&"月份收支情况“
求和函数: 应用举例 —— 往下（或往右）累加汇总
往下（或往右）累加汇总是常见的实务问题。 解决方法：使用SUM函数，但单元格引用的第一个地址 应为绝对引用，而第二个地址为相对引用。
求和函数: 应用举例 —— 动态汇总当天数据
工作表中存在每天的数据，要求动态汇总计算当前的数据。 可使用SUMIF函数。但要注意条件的写法。
家，他们都拥有丰富的行业知识和经验。高超的Excel技术配合行业经 验来共同应用，才有可能把Excel发挥到极致，所以，如果希望成为
Excel专家，就不能只单单学习Excel了 。
Excel函数的种类
财务函数 日期函数 时间函数 数学与三角函数 统计函数 数据库管理函数 文本函数 信息类函数
函数的基本语法
最大值与最小值函数: MAX、 MIN函数
MAX函数 : 求N个数的最大值 = MAX(参数1,参数2,…,参数N) MIN函数: 求N个数的最小值 = MIN(参数1,参数2,…,参数N)
绝对值函数: ABS函数
ABS函数: 求某数的绝对值 = ABS(参数) 例如，ABS(-100)=100
计算实际岁数：=INT(YEARFRAC(出生日期,今天,1)
=DATEIF(出生日期,今天,”Y”)
日期函数应用举例——确定具体日期
说明 确定某个月的最后一天 确定上个星期日的日期 确定上个星期六的日期 确定当年年份 确定当年月份 确定当日日期 确定当日是星期几 确定当日是星期几 确定一个日期所属的季度 确定上月月末日期 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()) 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())
当被比较年份没有数据时，会出现被除数为0的错误。 可以利用IF函数进行处理。 如图，去年没有数据，而今年有数据，就显示“新增项目”。 去年有数据，而今年没有数据，就显示“已经停产”。
IF函数应用举例 之三
自动归类问题
为了有效管理零用金，希望在输入现金开支数据后，该金额数据自 动依部门归类到适当的列，从而更加醒目地标示出来。
OR函数应用举例
商品 AA BB CC DD 销售量 654 455 544 233
真 假 真 假
联合使用IF、AND和OR函数 计算奖金
日期函数: DAY、DATE、DAYS360、TODAY函数
DAY函数: 返回以序列号表示的某日期的天数，用整数 1～31 表示。
= DAY(日期序列号) 例如 DAY("2006-12-22")=22
IF函数应用举例 之一 ——计算奖金
销售人员 销售额 奖金 AAAA 654 26.16 BBBB 325 6.5 CCCC 876 35.04 DDDD 454 9.08 EEEE 755 30.2 奖金提成比例 500以下 500以上
2% 4%
IF函数应用举例 之二 ——计算增长率
计算增长率
常用函数介绍
求和函数: 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函数 其他函数
日期函数应用举例 ——设置日期显示格式
日期显示 2006-12-07 2006年12月7日 2006/12/07 07/12/2006 2006-12-07 Thursday 2006年12月7日 星期四 2006年12月 12月7日 =TEXT("2006-12-7","yyyy-mm-dd") =TEXT("2006-12-7","yyyy年m月d日") =TEXT("2006-12-7","yyyy/mm/dd") =TEXT("2006-12-7","dd/mm/yyyy") =TEXT("2006-12-7","yyyy-mm-dd") & " " & TEXT(WEEKDAY("2006-12-7",1),"dddddd") =TEXT("2006-12-7","yyyy年m月d日") & " " & TEXT("2006-12-7","[$-804]aaaa;@") =TEXT("2006-12-7","yyyy年m月") =TEXT("2006-12-7","m月d日") 公式
解决办法：使用IF函数
如图，先在单元格F2输入公式“=IF($D2=F$1,$C2, "")”。 选定单元格区域F2:J2，按【F2】，使处于编辑状态。 按【Ctrl+Enter】组合键 注意单元格的引用方式。
AND函数应用举例
销售人员 销售额 奖金 AAAA 654 26.16 BBBB 325 6.5 CCCC 876 35.04 DDDD 454 18.16 EEEE 755 30.2 奖金提成比例 100-400 400以上 2% 4%
数学函数 AVERGAE、 MAX、MIN、 ABS、 SQRT函数
常用的数学函数有：
• • • • • AVERGAE函数：求平均值 MAX：求最大值 MIN：求最小值 ABS：求绝对值 SQRT：计算平方根
平均值函数: AVERGAE函数
AVERGAE函数 :求N个数的平均值 = AVERGAE(参数1,参数2,…,参数N)
AND函数：AND函数表示逻辑与，当所有条件都满足时 （即所有参数的逻辑值都为真时），AND函数返回 TRUE，否则，只要有一个条件不满足即返回FALSE。
= AND(条件1，条件2，…条件N)
OR函数：只要有一个条件满足时，OR函数返回TRUE， 只有当所有条件都不满足时才返回FALSE。
= OR(条件1,条件2,…条件N) 说明：这3个函数常常联合使用。
求和函数: 应用举例
SUM函数
SUMIF函数
SUMPRODUCT函数
求和函数: 应用举例 —— 隔栏加总计算
某预算表如图所示，每个月分为“预算数”和“实际 数”，现要求计算年度“预算数”和“实际数”。这实 际上就是隔栏加总问题。如果一个一个单元格相加是很 繁琐的。 解决方法：巧妙应用第3行的标题。
函数的基本语法为：
= 函数名(参数1,参数2,…,参数n)。
注意问题：
函数名与其后的括号“（”之间不能有空格。 当有多个参数时，参数之间要用逗号“，”分隔。 参数部分总长度不能超过1024个字符。 参数可以是数值、文本、逻辑值、单元格引用，也可以是各 种表达式或函数。 函数中的逗号“，”、引号“"”等都是半角字符，而不是 全角字符。
求和函数 SUM/SUMIF/SUMPRODUCT函数
SUM函数 :无条件求和 = SUM (参数1,参数2,…,参数N) SUMIF函数 :条件求和 =SUMIF(range,criteria,sum_range) SUMPRODUCT函数 :在给定的几组数组中，将 数组间对应的元素相乘，并返回乘积之和 = SUMPRODUCT(array1,array2,array3, ...)
开平方函数: SQRT函数
SQRT函数: 求某数的平方根 = SQRT(参数) 例如，SQRT(2)=1.4142135623731
计数函数: COUNT、 COUNTA、COUNTIF函数
COUNT函数: 计算给定区域内数值型参数的数 目 = COUNT(参数1,参数2,…,参数N) COUNTA 函数: 返回参数列表中非空值的单元 格个数 = COUNTA(参数1，参数2，…，参数N ) COUNTIF函数: 计算给定区域内满足特定条件 的单元格的数目 = COUNTIF(range,criteria)
日期函数应用举例 ——确定两个日Fra Baidu bibliotek间的天数
说明 确定两个日期的间隔天数 确定两个日期的间隔天数 确定两个日期间的工作日天数(不包括政府假日) 确定两个日期间的工作日天数(包括政府假日) 返回当前日距月末还有多少个工作日
结果
公式
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))
设置本月标题
=MONTH(TODAY())&"月份收支情况“
将日期转换为星期
中文星期：=TEXT(WEEKDAY(A1),"aaaa") 英文星期：=TEXT(WEEKDAY(A1),“dddd")
计算2年5个月20天后的日期
=DATE(YEAR(A1)+2,MONTH(A1)+5,DAY(S1)+20)
常用EXCEL函数讲解
2018年9月18日
新手、初级用户、中级用户、高级用户和专家五个层次 新手：学习者需要大致了解到Excel的基本操作方法和常用功能，诸如输
入数据，查找替换，设置单元格格式，排序、汇总、筛选和保存工作簿。
初级用户：可以开始在工作中运用Excel，比如建立一个简单的表格，画 一张简单的图表，会一些基本简单的函数，如SUM、IF 等。 中级用户三个标志： 一是理解并熟练使用各个Excel菜单命令 二是熟练使用数据透视表 三是至少掌握20个常用函数以及函数的嵌套运用，掌握的基本函数有 SUM函数、IF函数、VLOOKUP函数、INDEX函数、MATCH函数、 OFFSET函数、TEXT函数等等
DATE函数：返回代表特定日期的序列号。
= DATE(年,月,日) 例如 DATE(2006,12,23)= "2006-12-23"
DAYS360函数：按照一年 360 天计（每个月以 30 天， 一年共计 12 个月），返回两个日期间相差的天数。
= DAYS360(开始日期,截止日期,逻辑值)
例如 DAYS360("2000-1-15","2005-12-16")=2131天