常用EXCEL函数使用详解.ppt

1、常用EXCEL函数讲解,2018年9月18日,新手、初级用户、中级用户、高级用户和专家五个层次 新手：学习者需要大致了解到Excel的基本操作方法和常用功能，诸如输入数据，查找替换，设置单元格格式，排序、汇总、筛选和保存工作簿。 初级用户：可以开始在工作中运用Excel，比如建立一个简单的表格，画一张简单的图表，会一些基本简单的函数，如SUM、IF 等。 中级用户三个标志： 一是理解并熟练使用各个Excel菜单命令 二是熟练使用数据透视表 三是至少掌握20个常用函数以及函数的嵌套运用，掌握的基本函数有SUM函数、IF函数、VLOOKUP函数、INDEX函数、MATCH函数、OFFSET函数、T

2、EXT函数等等,高级用户两个标志： 一是熟练运用数组公式，也就是那种用花括号包围起来，必须用组合键才能完成录入的公式。 Excel专家： 从Excel的功能细分来看，精通全部的人想必寥寥无几。Excel是应用性太强的软件，意味着一个没有任何工作经验的普通学生是很难成为Excel专家的。所以Excel专家也必定是某个或多个行业的专家，他们都拥有丰富的行业知识和经验。高超的Excel技术配合行业经验来共同应用，才有可能把Excel发挥到极致，所以，如果希望成为Excel专家，就不能只单单学习Excel了 。,Excel函数的种类,财务函数 日期函数 时间函数 数学与三角函数 统计函数 数据库管理函

3、数 文本函数 信息类函数,函数的基本语法,函数的基本语法为： = 函数名(参数1,参数2,参数n)。 注意问题： 函数名与其后的括号“（”之间不能有空格。 当有多个参数时，参数之间要用逗号“，”分隔。 参数部分总长度不能超过1024个字符。 参数可以是数值、文本、逻辑值、单元格引用，也可以是各种表达式或函数。 函数中的逗号“，”、引号“”等都是半角字符，而不是全角字符。,常用函数介绍,求和函数: SUM/SUMIF/SUMPRODUCT函数 数学函数 AVERGAE、 MAX、MIN、 ABS、 SQRT函数 计数函数: COUNT、COUNTA、COUNTIF函数 条件函数和逻辑函数: IF

4、函数、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函数 其

5、他函数,求和函数 SUM/SUMIF/SUMPRODUCT函数,SUM函数 :无条件求和 = SUM (参数1,参数2,参数N) SUMIF函数 :条件求和 =SUMIF(range,criteria,sum_range) SUMPRODUCT函数 :在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和 = SUMPRODUCT(array1,array2,array3, .),求和函数: 应用举例,SUM函数 SUMIF函数 SUMPRODUCT函数,求和函数: 应用举例 隔栏加总计算,某预算表如图所示，每个月分为“预算数”和“实际数”，现要求计算年度“预算数”和“实际数”。这实际上就

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

7、均值函数: 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

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

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

10、，就显示“新增项目”。 去年有数据，而今年没有数据，就显示“已经停产”。,IF函数应用举例 之三,自动归类问题 为了有效管理零用金，希望在输入现金开支数据后，该金额数据自动依部门归类到适当的列，从而更加醒目地标示出来。 解决办法：使用IF函数 如图，先在单元格F2输入公式“=IF($D2=F$1,$C2, )”。 选定单元格区域F2:J2，按【F2】，使处于编辑状态。 按【Ctrl+Enter】组合键 注意单元格的引用方式。,AND函数应用举例,OR函数应用举例,联合使用IF、AND和OR函数计算奖金,日期函数: DAY、DATE、DAYS360、TODAY函数,DAY函数: 返回以序列号表示

11、的某日期的天数，用整数 131 表示。 = 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()

12、,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(YEARFRA

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

14、则，如果下班时间大于上班时间，就表示还在当前，因此不需要加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(“2

15、007-4-12”,-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 计算应付账

16、款的到期日： 如果一笔应付款的到期日为自交易日起满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个工作日的日期为（要出去国家

17、法定的五一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函数：计算两个日期之间的天数、月数或年数。这个函数是一个特殊

18、函数，在函数清单中找不到，在帮助信息中也找不到。 DATEDIF(开始日期, 结束日期, 单位) 单位 意义 “Y ” 时间段中的总年数 “M ” 时间段中的总月数 “D ” 时间段中的总天数 “MD ” 两日期中天数的差，忽略日期数据中的年和月 “YM ” 两日期中月数的差，忽略日期数据中的年和日 “YD ” 两日期中天数的差，忽略日期数据中的年 例如：某职员进公司日期为1985年3月20日，离职时间为2007年8月9日，那么他在公司工作了多少年、多少月和多少天？ 工作年数：=DATEDIF(“1985-3-20”,“2007-8-9”,“Y”)=22年 工作月数：=DATEDIF(“198

19、5-3-20”,“2007-8-9”,“YM”)=4个月 工作天数：=DATEDIF(“1985-3-20”,“2007-8-9”,“MD”)=20天 问题：如果某人2月1日到职，2月28日离职，任职时间是28天还是1个月？ 注意：2月1日到职，开始日期应为1月31日；2月28日离职，结束日期应为3月1日。,查找和引用函数: VLOOKUP、 HLOOKUP、 MATCH、 INDEX、CHOOSE、OFFSET函数,VLOOKUP函数：在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。 HLOOKUP函数：从表格或数值数组的首行查找指定的数值，并由此返回表格或

20、数组当前列中指定行处的数值。 MATCH函数：返回在指定方式下与指定数值匹配的数组中元素的相应位置。 INDEX函数：返回表格或区域中的数值或对数值的引用。 CHOOSE函数:：根据指定的索引值返回数组中的数据 OFFSET函数：动态引用单元格。 提示：我们还可以利用名称来查找数据（详见文件“通过名称查找数据.xls”）,VLOOKUP函数应用举例 之一,VLOOKUP函数应用举例 之二制作采购表,根据两个表格制作采购表，如图所示，计算步骤如下： 单元格区域B10:B13为各种商品的采购数量，由人工输入。 在单元格C10中输入公式“=VLOOKUP(A10,$A$3:$B$6,2,FALSE)

21、”，获取某商品的单价。 在单元格D10中输入公式=VLOOKUP(B10,$D$3:$H$6,MATCH(A10,$D$3:$H$3,0),TRUE)”，获取某商品的折扣。 在单元格E10中输入公式“=B10*C10*(1-D10)”，计算采购金额。 选取单元格区域C10:E10，将其向下填充复制到单元格区域C13:E13，得到其他商品的单价、折扣和采购金额。,VLOOKUP函数应用举例 之三根据产品代码查询该产品的详细信息,根据产品代码查询该产品的详细信息，如图所示。 说明：由于产品代码是唯一的标识符，没有重复，所以可以利用VLOOKUP函数或者HLOOKUP函数进行查找。,HLOOKUP函

22、数应用举例 之一,HLOOKUP函数应用举例 之二,根据指定的行、列查找数据,HLOOKUP函数应用举例 之三计算底薪佣金制的薪金,推销员的薪金采用底薪佣金制，计算规则如下： 营业额 底薪 佣金比率 0-299,999 25,000 0% 300,000-499,999 25,000 1% 500,000-999,999 30,000 2% 1,000,000-1,499,999 35,000 3% 1,500,000以上 40,000 4% 整理上述数据到工作表，并输入相应计算公式，即得各个推销人员的薪金。注意设计底薪佣金制计算规则表时要采用各级的底限值。,MATCH函数应用举例 之一基本应

23、用,注意：MATCH返回的位置是相对于指定的单元格区域而言的，而不是对整个工作表区域而言的。 例如：MATCH(455,A2:E2,0)=3,MATCH函数应用举例 之二比较两张工作表，将仅存在于其中某个工作表的数据筛选出来,如图，在两个工作表中有重复的数据，现在要在工作表“财务部上报”中将工作表“销售部上报”中重复的数据隐藏起来。 筛选步骤： 以工作表“财务部上报”为准，设计一个辅助列，在单元格B2输入公式： =ISERROR(MATCH(A2,销售部上报!$A$2:$A$7,0，并向下复制到数据区域的末尾。 单击【数据】【筛选】【自动筛选】命令。 筛选出需要对数据。 最后采用选择性粘贴的方

24、法将数据复制到别处。,INDEX函数应用举例,MATCH函数与INDEX函数联合使用 查找某部门某项费用的数额,根据指定的部门名称和费用项目，查找相应的金额。如下图。 说明：先用MATCH查找费用项目在第几行，用MATCH查找部门在第几列，然后用INDEX函数取出行列交叉处的数据。,利用数组公式处理多维数据的查询,我们也可以利用连字符&连接多个条件进行多维数据的查询。 先定义名称，再利用MATCH函数和INDEX函数进行查询。,CHOOSE函数应用举例,CHOOSE函数语法：CHOOSE(索引号,值1,值2,) 例如，CHOOSE(1,AA,BB,CC,DD)=AA CHOOSE(3,AA,B

25、B,CC,DD)=“CC 举例：依在本单位工作年限发放中秋节礼品 礼品发放规定：本单位工龄未满3年的送咖啡壶；满3年未满6年者送饮水机；满6年未满9年者送电磁炉；9年以上者送电烤箱。 说明： 也可以使用IF函数。 注意：本单位工龄的计算方法。Excel默认的日期系统是1900系统。 也可以利用YEARFRAC函数计算工龄（或年龄）： =INT(YEARFRAC(C4,$H$1),OFFSET函数应用,OFFSET函数语法： OFFSET(参照单元格, 列位移量, 行位移量, 高度, 宽度) 例如，公式=OFFSET(C3,2,3,1,1)将返回单元格F5中的值，这里，当前指定的引用为单元格C3

26、，以此为参照系，向下偏移2行，为第5行；向右偏移3列，为F列；高度和宽度均为1，表示仅为一个单元格。 计算步骤： 在单元格B13输入 =OFFSET(INDIRECT(“A”&MATCH($B$10,$A$1:$A$7,0),0,COLUMN(A1)，然后将单元格B13向右复制。 在上述公式中，首先使用MATCH查找单元格B10中的数字所在A列的行，然后利用INDIRECT返回A列中该行所在单元格的数据，最后利用OFFSET函数取得该行各列的数据。 在单元格B14输入=SUM(OFFSET(B1,1,$B$10)，计算到指定月份某部门的合计数，然后将单元格B14向右复制。 说明：OFFSET函

27、数在制作动态查询和动态图表时是非常有用的。,从多个表格区域中查询数据 之一根据销售人员任期确定提成率,有多个表格需要查询，根据实际条件决定要查询那个表格。 利用IF语句判断使用那个表格。 实例如下，销售人员任期不同，则提成率也不同。,从多个表格区域中查询数据 之二,有多个表格区域，结构相同。 根据指定的部门、月份、费用项目查询相应的费用金额。,从多个工作表中查询数据,某公司将客户分为A、B、C三个等级，给于不同的客户编号（分别以A、B、C开头）。不同等级客户的折扣率是不同的，而同一等级客户里不同产品的折扣率也是不同的。在确定客户等级和产品之后，还得依据订购数量给于该客户最终的折扣。怎样编写查询

28、公式？ 太复杂了！,从多个工作表中查询数据,考虑到从不同的工作表进行查询，而工作表名称即为“客户”加客户编号的第一个字母，因此可以利用INDIRECT函数获取要查询工作表的单元格区域，利用MATCH函数确定指定商品编号所在的列和采购金额所在的行，再利用INDEX函数取出相应的折扣率。 单元格G2的公式如下： =INDEX(INDIRECT(客户&LEFT($B2,1)&!$B$4:$E$7), MATCH(F2,INDIRECT(客户&LEFT($B2,1)&!$A$4:$A$7), MATCH($C2,INDIRECT(客户&LEFT($B2,1)&!$B$3:$E$3),区分大小写的查询,

29、VLOOKUP函数和HLOOKUP函数不区分大小写。 如果要区分大小写进行查询，可以联合使用INDEX函数、MATCH函数和EXACT函数。,四舍五入函数: ROUND、FLOOR和CEILING函数,ROUND函数: 返回某个数字按指定位数舍入后的数字。 FLOOR函数：根据指定基数，将数字沿绝对值减小的方向向下舍入到最接近的倍数。 CEILING函数 ：将参数 Number 向上舍入（沿绝对值增大的方向）到最接近的倍数。 例如：ROUND(300.5485,2)=300.55 FLOOR(2.5, 1)= 2， FLOOR(-2.5, -2)= -2 CEILING(2.5, 1)=3，

30、CEILING(-2.5, -2)= -4,四舍五入函数应用举例: 产品价格标定,应用举例：某贸易公司经常要以汇率换算产品的成本价格，加上必要的管理费及预期利润后就是产品的价格。因此，产品价格的百位数以下都会有零头。公司的政策是：凡是小于30的尾数去掉，而大于或等于30 的尾数则进位成100。例如，4004被标成4000， 4227被标成4200，而2145被标成2200，1765被标成1800。那么，该怎样设定计算公式计算价格标定？ 计算思路：以100为基数利用FLOOR函数取出元时定价的尾部部分，然后利用IF函数判断该尾数是否大于或等于30，然后再决定是利用CEILING函数还是FLOOR

31、函数处理原数据。,取整函数: INT函数,INT函数: 将数字向下舍入到最接近的整数 。 = INT(数字) 例如： INT(300.5485)=300， INT(-300.5485)=-301 例：某企业根据经营部门的完成率进行评分，标准如下：完成率59.99%以下为1分，超过60%为2分， 超过70%为3分，超过80%为4分，超过90%为5分。 分析：我们可以使用IF函数进行计算。但由于各个标准的间隔正好是10%，因此也可以利用INT函数进行运算，并进行评分。 说明：公式中(B2-0.49999)*10用于计算超过49.999%的有多少个10%，而(B20.5)表示只有在完成率超过50%

32、时才利用公式(B2-0.49999)*10进行计算。,信息函数: ISBLANK、ISTEXT、ISNUMBER、ISERROR函数,ISBLANK函数：判断单元格是否为空白单元格 ISTEXT函数：判断单元格数据是否为文本 ISNUMBER函数：判断单元格数据是否为数字 ISERROR函数：判断单元格是否出现错误,文本函数LEN、LEFT、RIGHT、TRIM、FIND、TEXT等函数,LEN函数：获取字符串的长度 LEFT函数：获取字符串左边指定个数的文本 RIGHT函数：获取字符串右边指定个数的文本 TRIM函数：取消字符串两侧的空格 FIND函数：查找某字符在字符串中第一次出现的位置 TEXT函数：将数值转换为按指定数字格式表示的文本 其他的文本函数,文本函数应用举例,例1： LEN(ABCD)=4 LEFT(ABCD,2)= AB RIGHT(ABCD,2)= CD TRIM( ABCD )= ABCD FIND(.,12345.65)=6 TEXT(12345.6687,￥0.00)= ￥12345.67 例2：从身份证号码获取出生日期和性别,文本函数应用举例,例3：将科目编码与科目名称分离（假设科目编码与科目名称之间有一个空格） 问题：如果科目