办公软件EXCEL函数培训PPT课件.ppt

1、目 录1 1、函数相关概念、函数相关概念: :2 2、常用函数、常用函数3 3、常用函数组合、常用函数组合4 4、EXCELEXCEL中宏的运用中宏的运用第一部分第一部分: :相关概念相关概念1.1.函数语法函数语法: : 由由函数名函数名+ +括号括号+ +参数参数组组成成 例例: : 求和函数求和函数: SUM(A1,B2,: SUM(A1,B2,) ) 参数与参数之间用参数与参数之间用英文英文 逗号逗号“ , ”表示表示 2 2、运算符、运算符公式运算符公式运算符: : + - + - * * / % / % 比较运算符比较运算符: : = = = = 引用运算符引用运算符: : , :

2、, : 3 3、单元格引用、单元格引用单元格的相对引用与绝对引用单元格的相对引用与绝对引用( (按按F4F4键切换键切换):): 样式样式: : A1 A1 相对引用第相对引用第A A列第列第1 1行行 $ $A1 A1 锁定第锁定第A A列列 A A$ $1 1 锁定第锁定第1 1行行 $ $A A$ $1 1 锁定第锁定第A A列与第列与第1 1行行第二部分第二部分: :常用函数常用函数( (一一) )数学函数数学函数: : ( (二二) )日期函数日期函数: :( (三三) )信息函数信息函数: :( (四四) )逻辑函数逻辑函数: :( (五五) )文本函数文本函数: :( (六六)

3、)查找与引用函数查找与引用函数: :( (一) )数学函数: :条件求和条件求和:SUMIF:SUMIF求个数求个数:COUNT:COUNT求符合条件个数求符合条件个数:COUNTIF:COUNTIF求和求和:SUM :SUM 求算书平均数求算书平均数:AVERAGE:AVERAGE四舍五入函数四舍五入函数:ROUND:ROUND排位排位:RANK:RANK1.1.求和:SUM:SUM( (所有函数公式前必加” ”= =”) ”)SUM( SUM( 数值数值1, 1, 数值数值2,2,) ) A1=6 A2=7 A3=8 B1=9 B2=10 A1=6 A2=7 A3=8 B1=9 B2=10

4、 B3=11B3=11 =SUM(6,8)=14 =SUM(6,8)=14 =SUM(A1,A3)=6+8=14 =SUM(A1,A3)=6+8=14 =SUM(A1:A3)=6+7+8=14 =SUM(A1:A3)=6+7+8=14 =SUM(A1:B3)=6+7+8+9+10+11=51 =SUM(A1:B3)=6+7+8+9+10+11=512.2.条件求和:SUMIF:SUMIFSUMIF( SUMIF( 范围范围, , 条件条件, ,要求和范围要求和范围) ) A B C 1 100 20 1000 2 200 40 2000 3 300 60 3000=SUMIF(A1:A3, =

5、SUMIF(A1:A3, “=200=200”,B1:B3)=100,B1:B3)=100=SUMIF(A1:A3, =SUMIF(A1:A3, “=200=200”,C1:C3)=5000,C1:C3)=50003.3.求个数:COUNT:COUNTCOUNT( COUNT( 数值数值1,1,数值数值2,2,) ) A B C 1 100 20 1000 2 200 40 2000 3 300 60 3000 =COUNT(A1:A3)=3=COUNT(A1:A3)=3 =COUNT(B1:B3)=3=COUNT(B1:B3)=34.4.求符合条件个数:COUNTIF:COUNTIFCOUN

6、TIF( COUNTIF( 范围范围, ,条件条件) ) A B C 1 100 20 1000 2 200 40 2000 3 300 60 3000 =COUNTIF(A1:A3,=COUNTIF(A1:A3,“=200=200”）=2=2 =COUNTIF(A1:A3, =COUNTIF(A1:A3, “200200” ）=1=15.5.求算术平均数:AVERAGE:AVERAGEAVERAGE( AVERAGE( 数值数值1,1,数值数值2,2,) ) A B C 1 100 20 1000 2 200 40 2000 3 300 60 3000 =AVERAGE(A1:A3)=200

7、=AVERAGE(A1:A3)=200 =AVERAGE(A1:A3,B1)=155 =AVERAGE(A1:A3,B1)=1556.6.四舍五入函数:ROUND:ROUNDROUND( ROUND( 数值数值, ,小数位小数位) ) A B C 1 100 20 1000 2 200 40 2000 3 300 60 3000 =ROUND(A1/A3,2)=0.33=ROUND(A1/A3,2)=0.33 =ROUNDUP(A1/A3,2)=0.34 =ROUNDUP(A1/A3,2)=0.34 =ROUNDDOWN(A1/A3,2)=0.33 =ROUNDDOWN(A1/A3,2)=0.

8、33 =ROUNDDOWN(31415.92654, -2) =ROUNDDOWN(31415.92654, -2)将将 31415.92654 31415.92654 向下舍入到小数点左侧两位向下舍入到小数点左侧两位 (31400)(31400)7. 7.排位:RANK:RANKRANK( RANK( 数值数值, ,范围范围, ,数值数值) ) 1-1-升序升序,0-,0-降序降序 A B CA B C 1 100 20 1000 1 100 20 1000 2 200 40 2000 2 200 40 2000 3 300 60 3000 3 300 60 3000=RANK(A1,A1:

9、A3,1)=1=RANK(A1,A1:A3,1)=1=RANK(A1,A1:A3,0)=3=RANK(A1,A1:A3,0)=3( (二) )日期函数: :返回日期序列年份返回日期序列年份YEARYEAR返回日期序列月份返回日期序列月份MONTHMONTH返回日期序列特定天返回日期序列特定天DATEDATE返回系统今天日期返回系统今天日期TODAYTODAY返回系统今天日期与时间返回系统今天日期与时间NOWNOW返回对应日期的星期数返回对应日期的星期数WEEKDAYWEEKDAY1.1.返回日期序列年份YEARYEARYEAR( YEAR( 日期日期) ) A1=2003-07-29 星期二

10、=YEAR=YEAR（A1A1）=2003=2003 2.2.返回日期序列月份MONTHMONTHMONTH( MONTH( 日期日期) ) A1=2003-07-29 星期二 =MONTH=MONTH（A1A1）=7=7 3.3.返回日期序列特定天DATEDATEDATE( DATE( 日期日期) ) A1=2003-07-29 星期二 =DATE=DATE（A1A1）=29=29 4.4.返回系统今天日期TODAYTODAYTODAY( )TODAY( ) 或NOW() A1=2003-07-29 星期二 =TODAY =TODAY（）（）= 2003-07-29= 2003-07-29

11、5.5.返回系统今天日期与时间NOWNOWNOW( )NOW( ) A1=2003-07-29 星期二 =NOW =NOW（）（）= 2003-07-29 14= 2003-07-29 14：55556.6.返回对应日期的星期数WEEKDAYWEEKDAYWEEKDAY(WEEKDAY(日期日期 ) ) A1=2003-07-29 星期二 =WEEKDAY =WEEKDAY（A1A1）= 3= 3( (三) )信息函数: :测试是否为错误值函数测试是否为错误值函数ISERRORISERROR测试是否为错误值测试是否为错误值#N/A#N/A函数函数 ISNA ISNA1.1.测试是否为错误值函数

12、ISERRORISERRORISERROR( )ISERROR( ) 括号中为：括号中为：#N/A#N/A、#VALUE#VALUE、#REF#REF、#DIV/0#DIV/0、#NUM#NUM、#NAME#NAME？或？或#NULL#NULL时为时为TRUETRUE =ISERROR =ISERROR（ #N/A#N/A ）= TRUE = TRUE 说明说明结果错误结果错误 =ISERROR =ISERROR（ #N/A#N/A ）= FALSE = FALSE 说明说明结果结果 正确2.2.测试是否为错误值#N/A#N/A函数 ISNAISNAISNA( )ISNA( ) 括号中为：括号

13、中为：#N/A#N/A时为时为TRUETRUE =ISNA =ISNA（ #N/A#N/A ）= TRUE= TRUE( (四) )逻辑函数: :将条件按且方式结合函数将条件按且方式结合函数ANDAND将条件按或方式结合函数将条件按或方式结合函数OROR将条件值反过来函数将条件值反过来函数NOTNOT执行条件函数执行条件函数IFIF1.1.将条件按且方式结合函数ANDANDAND( AND( 条件条件) ) 括号中条件值均为括号中条件值均为TRUETRUE，则为，则为TRUETRUE；如果任一个为如果任一个为FALSEFALSE，则为，则为FALSEFALSE =AND =AND（ 53,21

14、 53,21 ）= TRUE= TRUE =AND =AND（ 13,21 13,21 ）= FALSE= FALSE2.2.将条件按或方式结合函数OROROR( OR( 条件条件) ) 括号中条件值均为括号中条件值均为FALSEFALSE，则为，则为FALSEFALSE；如果任一个为如果任一个为TRUETRUE，则为，则为TRUETRUE =OR =OR（ 53,21 53,21 ）= TRUE= TRUE =OR =OR（ 13,21 13,21 ）= TRUE= TRUE =OR =OR（ 13,25 13,25 ）= FALSE= FALSE3.3.将条件值反过来函数NOTNOTNOT

15、( NOT( 条件条件) ) 括号中条件值为括号中条件值为FALSEFALSE，则为，则为TRUE TRUE ；如果为；如果为TRUETRUE，则为，则为FALSEFALSE =NOT =NOT（ 53 53 ）= FALSE= FALSE =NOT =NOT（ 13 13 ）= TRUE= TRUE4.4.执行条件函数IF IFIF( IF( 条件条件, , 执行条件真执行条件真, , 执行条件执行条件假假) ) 可以执行七层嵌套. =IF =IF（ 53 ,2,3 53 ,2,3）= 2= 2 =IF =IF（ 53 ,2,3 53, =IF (53, IF IF ( 12,6,7) ,(

16、 12,6,7) ,0)=70)=7( (五) )文本函数: :截取函数截取函数LEFT RIGHT MIDLEFT RIGHT MID计算字符长度计算字符长度LENLEN合并字符函数合并字符函数CONCATENATECONCATENATE在字符串中查找特定字符在字符串中查找特定字符FINDFIND比较两个字符是否完全相符比较两个字符是否完全相符EXACTEXACT将数值转化为文本将数值转化为文本TEXTTEXT将数值型字符转化为数值将数值型字符转化为数值VALUEVALUE1.1.截取函数LEFT RIGHT MIDLEFT RIGHT MIDLEFT( LEFT( 文本文本, , 数值数值

17、) ) 从左边截取从左边截取RIGHT ( RIGHT ( 文本文本, , 数值数值) ) 从右边截取从右边截取MID ( MID ( 文本文本, , 开始位，数值开始位，数值) ) 从中间截取从中间截取 =LEFT =LEFT（ “abcdefabcdef”,2,2）= ab= ab =RIGHT =RIGHT（ “abcdefabcdef”,2 ,2 ）=ef=ef = MID = MID（ “abcdefabcdef”,2,3 ,2,3 ）= bcd= bcd2.2.计算字符长度LENLENLEN( LEN( 文本文本) ) 计算字符串的长度计算字符串的长度 =LEN =LEN（ “ab

18、cdefabcdef”）=6=63.3.合并字符函数CONCATENATECONCATENATECONCATENATE( CONCATENATE( 文本文本1 1，) ) 合并字符串合并字符串 =CONCATENATE =CONCATENATE（ “a a”, , “efef”）= = aefaef4.4.在字符串中查找特定字符FINDFIND 查找一个字符在另一个查找一个字符在另一个FIND( FIND( 文本文本, , 范围范围, ,数值数值) )字符串中的位置字符串中的位置 数值表示查找第几个数值表示查找第几个. . =FIND =FIND（ “a a”, , “abcaefabcaef

19、”,1,1）= 1= 1 =FIND =FIND（ “a a”, , “abcaefabcaef”,2,2）= 4= 45.5.比较两个字符是否完全相符EXACTEXACTEXACT( EXACT( 文本文本1,1,文本文本2)2) 比较两个字符是否完全相符比较两个字符是否完全相符, ,是则是则为为TRUE,TRUE,否则为否则为FALSEFALSE. . =EXACT =EXACT（ “a a”, , “a a”）= TRUE= TRUE =EXACT=EXACT（ “a a”, , “abab”）= FALSE= FALSE =EXACT =EXACT（ “a a”, , “A A”）=

20、FALSE= FALSE6.6.将数值转化为文本TEXTTEXTTEXT( TEXT( 数值数值, ,参数参数) ) 将数值转化为文本将数值转化为文本, ,参数一般参数一般为为0 0. . =TEXT(123.64 ,0)= 124 =TEXT(123.64 ,0)= 124 =TEXT(123.64,0.01)=123.64=TEXT(123.64,0.01)=123.647. 7.将数值型字符转化为数值VALUEVALUE 将文本型数值转化为数值将文本型数值转化为数值. . =VALUE =VALUE（ “ 1234 1234 ” ）= 1234= 1234VALUE( VALUE( 数值

21、文本数值文本) )( (六) )查找与引用函数: :查找表格中的值以列的方式查找表格中的值以列的方式VLOOKUPVLOOKUP Vlookup( Vlookup( 文本文本, ,范围范围, ,列列,FALSE),FALSE) 文本文本-条件条件 范围范围-条件所在的列条件所在的列 列列-范围中对应列用数值表范围中对应列用数值表示示 FALSE- FALSE-精确查找精确查找一、VLOOPUP注注意意以以参参照照文文本本选选择择区区域域范范围围二、 SUMPRODUCT1 1、使用、使用SUMPRODUCTSUMPRODUCT进行多条件求和进行多条件求和 SUMPRODUCT(SUMPRODU

22、CT(条件条件1)1)* *( (条件条件2)2)* * ( (条条件件3) 3) * *(条件条件n)n)* *某区域某区域) )汇总同时满足条件汇总同时满足条件1 1、条件、条件2 2到条件到条件n n的的记录指定区域的汇总金额记录指定区域的汇总金额实例如下图：实例如下图：2、在给定的几组数组中，将数组间对应的元素相乘，并返回乘积之和。=SUMPRODUCT(array1,array2,array3, .)第三部分第三部分: :常用函数组合常用函数组合 用IF、VLOOPUP、ISERROR函数进行组合生成函数公式。=IF(ISERROR(VLOOKUP(A2,Sheet2!$B$2:$C

23、$6,2,FALSE)=TRUE,0,VLOOKUP(A2,Sheet2!$B$2:$C$6,2,FALSE)实例如下图：第四部分第四部分:EXCEL:EXCEL中宏的运用中宏的运用 1、什么是宏？宏是EXCEl中一些命令的集合。 即是VBA ，VBA是基于Visual Basic 发展而来的，它们具有相似的语言结构，是一种可视化的、面向对象的、采用事件驱动方式的结构化高级程序设计语言VBA 不但继承了VB 的开发机制，而且VBA 还具有与VB 相似的语言结构，它们的集成开发环境IDE（Intergrated Development Environment）也几乎相同。但是，经过优化，VBA

24、专门用于Office 的各应用程序。VB 可运行直接来自Windows 95 或NT 桌面上的应用程序，而VBA 的项目（Project）仅由使用VBA 的Excel、Word、 PowerPoint 等称为宿主（Host）的Office 应用程序（Application）来调用。2 2、其他开发语言简介、其他开发语言简介随着互联网的兴起，互联网向智能化方向发展，PHP语言已经为大部份企业广泛应用和重视，也是各大IT公司首选的互联网编程语言（如：百度、腾迅、淘宝、网易等）移动4G、3G的运用和不断普及，基于手机应用的ANDROID软件开发语言也蓬勃发展。JAVA是一种简单的，跨平台的，面向对象

25、的，分布式的，解释的，健壮的安全的，结构的中立的，可移植的，性能很优异的多线程的，动态的语言。是由Sun Microsystems公司于1995年5月推出，并迅速发展，对C+语言形成有力的冲击。（Java，James Gosling）3 3、EXCELEXCEL中宏运用的实例中宏运用的实例以公司的项目工资表为例：依据项目人事资料、考以公司的项目工资表为例：依据项目人事资料、考勤资料生成工资表，最后汇总由银行代发的过程。勤资料生成工资表，最后汇总由银行代发的过程。 结束语当你尽了自己的最大努力时，失败也是伟大的，所以不要放弃，坚持就是正确的。When You Do Your Best, Failure Is Great, So DonT Give Up, Stick To The End谢谢大家荣幸这一路，与你同行ItS An Honor To Walk With You All The Way演讲人：XXXXXX 时 间：XX年XX月XX日