EXCEL培训-函数中级版

***2015.7.28EXCEL高效办公-函数篇班规手机关机或静音状态培训过程中不得接听电话、发短信服从讲师安排，不得迟到函数版开篇主题分享、提问函数版开篇主题分享、提问开篇3、EXCEL用户水平的5个层次4、EXCEL的优势1、案例-sum函数2、案例-发货日报表5、成为EXCEL高手的捷径6、EXCEL功能概览导入开篇-案例1、案例-sum函数设置范例1、数组求和：{=SUM((G12:G21>100)*G12:G21)}2、数据类型转换求和：=SUM(VALUE(H5))；=SUM(--(H5))；=SUM((H5)*1)；=SUM((H5)/1)3、多条件求和1：{=SUM(((G23:G32<100)+(G23:G32>110))*G23:G32)}=SUM(((G23:G32<100)+(G23:G32=110)+(G23:G32=120))*G23:G32)4、多条件求和2：{=SUM((H34:H43=“部门")*(I34:I43="男")*J34:J43)}5、多工作表求和时表名称快捷输入：=SUM(附表一:附表五!B3:B10)6、快速设置当前表以外的所有相同工作表区域的总和公式：=SUM('*'!B3)7、用SUM函数计数：{=SUM((H94:H102="男")*(I94:I102="汉"))}8、求1累加到100之和：{=SUM(ROW(1:100))}9、多个工作表相同区域求前三名产量之和：{=SUM(LARGE(CHOOSE({1,2,3,4,5},附表一!B3:B10,附表二!B3:B10,附表三!B3:B10,附表四!B3:B10,附表五!B3:B10),ROW(1:3)))}开篇-案例1、案例-运输金额计算嵌套函数IF(M3="车",N3,IF(AND(N3>0,N3<=0.75),1,IF(AND(N3>0.75,N3<1.25),1,IF(AND(N3>=1.25,N3<1.75),1.5,IF(AND(N3>=1.75,N3<2.25),2,IF(AND(N3>=2.25,N3<2.75),2.5,IF(AND(N3>=2.75,N3<3.25),3,)&IF(AND(N3>=3.25,N3<3.75),3.5,))))))&IF(AND(N3>=3.75,N3<4.25),4,IF(AND(N3>=4.25,N3<4.75),4.5,IF(AND(N3>=4.75,N3<5.25),5,IF(AND(N3>=5.25,N3<5.75),5.5,IF(AND(N3>=5.75,N3<6.25),6,IF(AND(N3>=6.25,N3<6.75),6.5,)&IF(AND(N3>=6.75,N3<7.25),7,))))))&IF(AND(N3>=7.25,N3<7.75),7.5,IF(AND(N3>=7.75,N3<8.25),8,IF(AND(N3>=8.25,N3<8.75),8.5,IF(AND(N3>=8.75,N3<9.25),9,IF(AND(N3>=9.25),N3,))))))*1开篇-用户水平2、EXCEL用户水平的5个层次1、刚开始接触…………新手2、基本掌握常用功能………初级用户3、熟练使用常用功能+数据透视表+函数公式+简单的宏……中级用户4、……+数组公式+VBA简单编程……高级用户5、EXCEL技能+专业知识+行业经验……专家（EXCEL专家也常常是行业专家）开篇-优势4、EXCEL的优势1、用途极为广泛，集成了最优秀的数据计算与分析功能，能助您在职场上取得成功，已经成为一种生产工具!2、在同类电子表格处理软件中处于绝对优势地位，具有良好的兼容性；3、博大精深，一般用户只使用了5%的功能!很多用户用不上的原因：一是根本不知道还有其他功能；二是知道有别的功能，但不知道怎么用；三是觉得暂时用不上。开篇-捷径5、成为EXCEL高手的捷径1、主动学习：宁愿加班加点，也不愿提高自己的工作技能；2、正确的学习方法：1）循序渐进：从新手到专家各阶段的学习特点2）善用资源，学以致用，带着问题学习3）多阅读，多实践——组合创新用法4）参加培训、各种交流等不一定要精通VBA才是高手——要充分利用EXCEL内置功能开篇-功能6、EXCEL功能概览统计分析基础技巧图形展示表格管理数据整理合并转换函数与宏分类汇总交叉分析方案工具使用控件应用图形种类数据钻取在线演示趋势分析EXCEL管理报告、文件POWERPOINT文本概念性图表量化性图表函数版开篇主题分享、提问主题函数篇基本概念函数通览常用函数嵌套函数一、基本概念运算符相关公式与函数函数语法$相对绝对混合引用内置函数嵌套函数扩展函数公式与函数什么是函数？Excel函数即是预先定义，执行计算、分析等处理数据任务的特殊公式。函数语法：由函数名+左括号+参数+右括号参数与参数之间用逗号表示，以常用的求和函数SUM为例，它的语法是“SUM(number1,number2,......)”。其中“SUM”称为函数名称，一个函数只有唯一的一个名称，它决定了函数的功能和用途。函数名称后紧跟左括号，接着是用逗号分隔的称为参数的内容，最后用一个右括号表示函数结束。参数是函数中最复杂的组成部分，它规定了函数的运算对象、顺序或结构等。使得用户可以对某个单元格或区域进行处理，如分析存款利息、确定成绩名次、计算三角函数值等。公式与函数什么是公式？

函数与公式既有区别又互相联系。如果说函数是Excel预先定义好的特殊公式，公式就是由用户自行设计对工作表进行计算和处理的计算式。公式求和就是用四则运算符进行的，如A1+A2函数求和的就是SUM,SUMIF,,,,等等，函数一般都比较不直观，而且都是有英文代号和括号；运算符、优先级与基本运算Excel包含四种类型的运算符：1、算术运算符2、比较运算符3、文本运算符4、引用运算符运算符-算术运算符算术运算符含义（示例）+（加号）加法运算(3+3)–（减号）减法运算(3–1)

负(–1)*（星号）乘法运算(3*3)/（正斜线）除法运算(3/3)%（百分号）百分比(20%)^（插入符号）乘幂运算(3^2)完成基本的数学运算，如加法、减法和乘法，连接数字和产生数字结果等，使用以下算术运算符：运算符-比较运算符比较运算符含义（示例）=（等号）等于(A1=B1)>（大于号）大于(A1>B1)<（小于号）小于(A1<B1)>=（大于等于号）大于或等于(A1>=B1)<=（小于等于号）小于或等于(A1<=B1)<>（不等号）不相等(A1<>B1)使用下列运算符比较两个值。当用运算符比较两个值时，结果是一个逻辑值，不是TRUE就是FALSE。运算符-文本连接运算符文本运算符含义（示例）&（和号）将两个文本值连接或串起来产生一个连续的文本值

(“好好”&”学习”)使用和号(&)加入或连接一个或更多文本字符串以产生一串文本。运算符-引用运算符使用以下运算符可以将单元格区域合并计算。引用运算符含义（示例）:（冒号）区域运算符，产生对包括在两个引用之间的所有单元格的引用(B5:B15),（逗号）联合运算符，将多个引用合并为一个引用(SUM(B5:B15,D5:D15))

（空格）交叉运算符产生对两个引用共有的单元格的引用。(B7:D7C6:C8)运算符-优先级公式中同时用到多个运算符，Excel将按下表所示的顺序进行运算。次序运算符说明1:（冒号）1

1（单个空格）,（逗号）引用运算符2–负号（例如–1）3%百分比4^乘幂5*和/乘和除6+和–加和减7&连接两个文本字符串（连接）8=<><=>=<>比较运算符运算符-优先级如果公式中包含相同优先级的运算符，例如，公式中同时包含乘法和除法运算符，则Excel将从左到右进行计算若要更改求值的顺序，请将公式中要先计算的部分用括号（）括起来。注意，excel中的括号仅有（）一种，{}不是运算符，不是括号。$引用相对引用当把公式复制到其他单元格时，行或者列就会改变。因为引用的是当前行或者列的实际偏移量。如单元格显示为A1，向下复制会自动变成A2，向右复制向B1。相对引用实际上是引用的单元格的相对位置。$引用相对引用当把公式复制到其他单元格时，行或者列就会改变。因为引用的是当前行或者列的实际偏移量。如单元格显示为A1，向下复制会自动变成A2，向右复制向B1。相对引用实际上是引用的单元格的相对位置。$引用绝对引用，当复制公式时，行和列不会改变。因为引用的是单元格的实际地址。单元格显示为$A$1。此时向下或向右复制单元格，显示的内容不变，还是$A$1，所引用的对象不变，还是当前A1单元格的数据。$引用混合引用，行或者列当中有一个是绝对引用，另一个是相对引用。单元格显示为$A1（绝对引用列）或A$1（绝对引用行）。$A1向右复制，会变为$A1，所引用的单元格为当前表的A1单元格。向下复制，会变为$A2，所引用的单元格实际是A2。$引用规律：加上了绝对地址符“$”的列标和行号为绝对地址，在公式向旁边复制时不会发生变化，没有加上绝对地址符号的列标和行号为相对地址，在公式向旁边复制时会跟着发生变化。混合引用时部分地址发生变化。$在谁的前面就绝对引用谁F4是在四种引用间相互转换的快捷键。内置、嵌套、扩展函数

按照函数的来源，Excel函数可以分为内置函数和扩展函数两大类。前者只要启动了Excel，用户就可以使用它们;而后者必须通过单击“工具→加载宏”菜单命令加载，然后才能像内置函数那样使用。函数是否可以是多重的呢？也就是说一个函数是否可以是另一个函数的参数呢？当然可以，这就是嵌套函数的含义。所谓嵌套函数，就是指在某些情况下，您可能需要将某函数作为另一函数的参数使用。二、函数通览1、日期与时间函数2、数学与三角函数3、逻辑函数

4、查找与引用函数5、数据库函数 6、文本函数7、统计函数

8、财务函数 9、工程函数 10、信息函数

11、自定义函数

1、日期时间函数:返回日期序列年份YEAR返回日期序列月份MONTH返回日期序列特定天DATE返回系统今天日期TODAY返回系统今天日期与时间NOW返回对应日期的星期数WEEKDAY计算两个日期之间的天数、月数或年数DATEDIF2、数学函数:条件求和:SUMIF求个数:COUNT求符合条件个数:COUNTIF求和:SUM求算书平均数:AVERAGE四舍五入函数:ROUND排位:RANK3、逻辑函数:将条件按且方式结合函数AND将条件按或方式结合函数OR将条件值反过来函数NOT执行条件函数IF4、查找与引用函数:查找表格中的值以列的方式VLOOKUPVlookup(已知条件,范围,第几列,FALSE)

文本----条件范围----条件所在的列列----范围中对应列用数值表示

FALSE----精确查找（0）6、文本函数:截取函数LEFTRIGHTMID计算字符长度LEN合并字符函数CONCATENATE在字符串中查找特定字符FIND比较两个字符是否完全相符EXACT将数值转化为文本TEXT将数值型字符转化为数值VALUE文本函数Left根据所指定的字符数，LEFT返回文本字符串中第一个字符或前几个字符，即返回文本左边的几个字符。示例：=left（”广播电视”，2）计算结果：广播36文本函数Right根据所指定的字符数返回文本字符串中最后一个或多个字符，即返回文本右边的几个字符。示例：=right（”广播电视”，2）计算结果：电视37文本函数Mid返回文本字符串中从指定位置开始的特定数目的字符，该数目由用户指定示例：=mid（“广播电视”，3,1），结果：电38文本函数Len返回文本字符串中的字符数示例：=len（“广播电视”）结果：439文本函数ReplaceREPLACE使用其他文本字符串并根据所指定的字符数替换某文本字符串中的部分文本。=REPLACE(“广播电视”,3,1,“abc”)，结果：广播abc视

40文本函数Clean删除文本中不能打印的字符。对从其他应用程序中输入的文本使用本函数，将删除其中含有的当前操作系统无法打印的字符。例如，可以删除通常出现在数据文件头部或尾部、无法打印的低级计算机代码。如从程序导出数据中存在的换行符等。41文本函数FIND用于在第二个文本串中定位第一个文本串，并返回第一个文本串的起始位置的值，该值从第二个文本串的第一个字符算起。示例=FIND(“东”,“焦东阳”)，结果：2.=FIND(“东”,“东阳市焦东阳”，2)，结果：5.42文本函数连字符&连字符不属于函数，主要功能为连接两个文本字符串。为便于掌握，将其放在文本函数部分讲解。示例：=“焦东阳”&“东阳市”结果：4310、信息函数:查看是否为空白 ISBLANK查看是否为错误 ISERROR查看是否为"#N/A"以外的错误 ISERR查看是否为"#N/A" ISNA查看数值是否为偶数 ISEVEN查看数值是否为奇数 ISODD查看数值是否为逻辑值