常用的20个EXCEL函数的使用详解讲解课件.ppt

1、常用的20个EXCEL函数 第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函数 日

3、期和时间函数: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/S

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

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

6、(参数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 函数:

7、返回参数列表中非空值的单元格个数 =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函数应用举例 之一 计算奖金 销售人员 销售额奖金奖金提成比例AAAA65426.16500以下2%BBBB3256.5500以上4%CCCC87635.04DDDD4549.08EEEE75530.2IF函数应用举例 之二 计算增长率 计算增长率 当被比较年份没有数据时，会出现被除数为0的错误。可以利用

9、IF函数进行处理。如图，去年没有数据，而今年有数据，就显示“新增项目”。去年有数据，而今年没有数据，就显示“已经停产”。IF函数应用举例 之三 自动归类问题 为了有效管理零用金，希望在输入现金开支数据后，该金额数据自动依部门归类到适当的列，从而更加醒目地标示出来。解决办法：使用IF函数 如图，先在单元格F2输入公式“=IF($D2=F$1,$C2,)”。选定单元格区域F2:J2，按【F2】，使处于编辑状态。按【Ctrl+Enter】组合键 注意单元格的引用方式。AND函数应用举例函数应用举例 销售人员 销售额奖金奖金提成比例AAAA65426.16100-4002%BBBB3256.5400以

10、上4%CCCC87635.04DDDD45418.16EEEE75530.2OR函数应用举例函数应用举例 商品销售量AA654真BB455假CC544真DD233假联合使用联合使用IF、AND和和OR函数函数 计算奖金计算奖金 日期函数日期函数:DAY、DATE、DAYS360、TODAY函数 DAY函数:返回以序列号表示的某日期的天数，用整数 131 表示。=DAY(日期序列号)例如 DAY(?6-12-22)=22 DATE函数：返回代表特定日期的序列号。=DATE(年,月,日)例如 DATE(2006,12,23)=?6-12-23 DAYS360函数：按照一年 360 天计（每个月以

11、30 天，一年共计 12 个月），返回两个日期间相差的天数。=DAYS360(开始日期,截止日期,逻辑值)例如 DAYS360(?0-1-15,?5-12-16)=2131天 TODAY函数：返回系统当前的日期。日期函数应用日期函数应用(举例）举例）设置动态标题：尽今天是&TEXT(TODAY(),yyyy 年m月d日)尽今天是&TEXT(TODAY(),yyyy年m月d日)&TEXT(WEEKDAY(TODAY(),aaaa)设置上月标题：?位呎?佔?位呎?佔?月份收支情况“设置本月标题?乏?吨?奁?月份收支情况“将日期转换为星期 中文星期：=TEXT(WEEKDAY(A1),aaaa)英文

12、星期：=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()

13、-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(?6-9-12,

14、?6-12-7,d)确定两个日期的间隔天数86=VALUE(?6-12-7-?6-9-12)确定两个日期间的工作日天数(不包括政府假日)35=NETWORKDAYS(?6-11-12,?6-12-31)确定两个日期间的工作日天数(包括政府假日)31=NETWORKDAYS(?6-4-8,?6-5-25,?6-5-1;?6-5-2;?6-5-3)返回当前日距月末还有多少个工作日4=NETWORKDAYS(TODAY(),EOMONTH(TODAY(),0)日期函数应用举例日期函数应用举例 设置日期显示格式 日期显示公式2006-12-07=TEXT(?6-12-7,yyyy-mm-dd)2006

15、年12月7日=TEXT(?6-12-7,yyyy年m月d日)2006/12/07=TEXT(?6-12-7,yyyy/mm/dd)07/12/2006=TEXT(?6-12-7,dd/mm/yyyy)2006-12-07 Thursday=TEXT(?6-12-7,yyyy-mm-dd)&TEXT(WEEKDAY(?6-12-7,1),dddddd)2006年12月7日 星期四=TEXT(?6-12-7,yyyy年m月d日)&TEXT(?6-12-7,$-804aaaa;)2006年12月=TEXT(?6-12-7,yyyy年m月)12月7日=TEXT(?6-12-7,m月d日)日期函数应用举

16、例日期函数应用举例编制下周计划表编制下周计划表 编制下周计划表 可以在本周的任何一天制作下周的计划表 注意：任何一天的日期减去本身的星期数，就一定等于上一个星期天的日期。日期函数综合应用设计考勤表 时间函数时间函数 输入时间 输入22:00与输入10:00 PM是一样的。如何计算跨午夜零时的时间间隔？输入：=B2+(B2A2)-A2 或者：=B2+IF(B2A2,1,0)-A2 思路：如果下班时间小于上班时间，就表示已经过了1天，因此要加1。否则，如果下班时间大于上班时间，就表示还在当前，因此不需要加1天。分析工具库里的日期函数分析工具库里的日期函数 EDATE函数 EOMONTH函数 WEE

17、KNUM函数 WORKDAY函数 NETWORKDAYS函数 一个特殊的日期函数：DATEDIF 注意：在使用这些函数之前，必须加载分析工具库，即：单击【工具】【加载宏】命令，打开【加载宏】对话框，选择“分析工具库”，如上图。EDATE函数函数 EDATE函数：返回指定日期往前或往后几个月的日期。例：2007年4月12日之后3个月的日期：=EDATE(“2007-4-12”,3)，为2007-7-12 2007年4月12日之前3个月的日期：=EDATE(“2007-4-12”,-3)，为2007-1-12 计算应付账款的到期日：如果一笔应付款的到期日为自交易日起3个月的那一天，比如交易日为20

18、06年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 计算应付账款的到期日：如果一笔应付款的到期日为自交易日起满3个月后的下一个月的5号，比如交易日为2006年11月20日，满3个月后下个月5号就是2007-3-

19、5：=EOMONTH(?6-11-20,3-(DAY(A1)0.5)表示只有在完成率超过50%时才利用公式(B2-0.49999)*10进行计算。信息函数:ISBLANK、ISTEXT、ISNUMBER、ISERROR函数 ISBLANK函数：判断单元格是否为空白单元格 ISTEXT函数：判断单元格数据是否为文本 ISNUMBER函数：判断单元格数据是否为数字 ISERROR函数：判断单元格是否出现错误 文本函数 LEN、LEFT、RIGHT、TRIM、FIND、TEXT等函数 LEN函数：获取字符串的长度 LEFT函数：获取字符串左边指定个数的文本 RIGHT函数：获取字符串右边指定个数的文

20、本 TRIM函数：取消字符串两侧的空格 FIND函数：查找某字符在字符串中第一次出现的位置 TEXT函数：将数值转换为按指定数字格式表示的文本 其他的文本函数 文本函数应用举例文本函数应用举例 例1：LEN(ABCD)=4 LEFT(ABCD,2)=AB RIGHT(ABCD,2)=CD TRIM(ABCD)=ABCD FIND(.,S45.65)=6?员?￥0.00)=￥12345.67 例2：从身份证号码获取出生日期和性别 文本函数应用举例文本函数应用举例 例3：将科目编码与科目名称分离（假设科目编码与科目名称之间有一个空格）问题：如果科目编码与科目名称之间没有空格，该怎么办呢？可使用LE

21、N函数和LENB函数：文本函数应用举例文本函数应用举例 例4：编制简单的统计表 选取单元格C2。输入“=REPT(”，（不要按回车键，还没有完成输入）单击【插入】【特殊符号】命令，插入一个黑正方形。接着输入“,B2*50)”将单元格C2向下填充复制。最后将C列的各个单元格的字体颜色分别设置为不同的颜色 排序函数:RANK、LARGE、SMALL函数 RANK函数：返回一个数字在数字列表中的排位。LARGE函数：返回数据集中第 k 个最大值。例如，可以使用函数 LARGE 得到第一名、第二名或第三名的得分。SMALL函数：返回数据集中第 k 个最小值。排序函数应用举例排序函数应用举例 数据库函数

22、：DGET、DMAX、DMIN、DSUM、DAVGEAGE函数 Microsoft Excel提供了一些用于对存储在列表或数据库中的数据进行分析的函数，这些函数统称为数据库函数（Dfunctions）。这些函数的语法结构基本相同，即：Dfunctions(database,field,criteria)函数的三个参数database、field和criteria的含义如下：database：构成列表或数据库的单元格区域。数据库是包含一组相关数据的列表，其中包含相关信息的行为记录，而包含数据的列为字段。列表的第一行包含着每一列的标志项。field：指定函数所使用的数据列。列表中的数据列必须在第一

23、行具有标志项。Field可以是文本，即两端带引号的标志项，如“使用年数”或“产量”；此外，Field也可以是代表列表中数据列位置的数字：1表示第一列，2表示第二列，等等。criteria：为一组包含给定条件的单元格区域。可以为参数criteria指定任意区域，只要它至少包含一个列标志和列标志下方用于设定条件的单元格。在财务管理中，常用的数据库函数有DSUM函数、DMAX函数、DMIN函数、DGET函数等。复合条件的语法复合条件的语法 同一行的构成了“与”关系条件。查找薪金在5000至8000元之间的纪录。同一列构成了“或”关系条件。查找部门为销售部和办公室的记录。不同列、同行构成了不同字段的“

24、与”关系。查找男性且为经理的纪录。不同列、不同行构成了不同字段的“或”关系条件。查找职称为高级工程师或职务为经理的纪录。不同列、不同行的“与”关系和“或”关系的复杂条件：查找办公室的女性经理、或者销售部的男性高级工程师的纪录。DSUM函数应用举例 DGET函数应用举例 DMAX函数和DMIN函数应用举例 其他函数其他函数 其他函数有很多，可根据需要使用。下面利用矩阵运算函数 MMULT 来计算材料的采购量。想一想，如果用SUMPRODUCT函数该怎么做？要联合使用 SUMPRODUCT 函数和TRANSPOSE 函数，并且以数组公式输入。使用自定义函数使用自定义函数 自定义函数以Functio

25、n开头，以End Function结束。在Function与End Function之间的是程序代码。自定义函数的返回值只有一个，并且只能通过表达式来调用。自定义函数可以有参数，也可以没有参数。自定义函数的程序结构如下：不带参数的自定义函数结构如下：Function函数名()（程序代码）函数名=表达式 End Function 带参数的自定义函数如下：Function 函数名(参数1,参数2,)（程序代码）函数名=表达式 End Function 使用自定义函数使用自定义函数 创建自定义函数 打开VBE界面 插入模块。编写自定义函数程序代码。使用自定义函数使用自定义函数 在工作表中使用自定义函数 单击工作表上的任一单元格。单击编辑栏的【插入函数】按钮，或执行【插入】【函数】命令，打开【插入函数】对话框。在【或选择类别】中选“用户定义”，在【选择函数】中选自定义函数。单击【确定】按钮，打开自定义函数的对话框。输入有关参数。