EXCEL2010函数教案

EXCEL2010函数一、为什么要学习函数文峰汽车商学院，打造学习型组织强大功能1效率2能力3二、成为函数高手的捷径文峰汽车商学院，打造学习型组织积极的心态循序渐进多阅读多实践善用资源学以致用知道Excel一种电子表格，会打开Excel，简单的输入数据和保存等幼儿园水平会制作简单报表和最常用的功能，如：简单的格式设置、打印等小学水平会最常用的公式、函数和较简单的图表，可以自己设计有简单计算功能的表格初中水平掌握30个以上的常用函数，熟悉常用图表，会使用名称、自定义和条件格式、有效性、组及分级显示、高级筛选、自定义排序、合并、分列，共享，快照，普通数据透视表，设计普通自动计算功能的表格，安全性的概念与简单应用，会录制宏，简单控件应用，高中水平熟悉工程、财务、统计等大部分不常用的函数，动态图表的熟练使用，数组公式的普通应用，初步了解vba和简单程序编制，制作自定义函数.大学水平研究vba及Excel与office其他组件的相互配合，能解决Excel中的所有问题，研究出大量实用加载宏模板硕士水平博士水平超出单纯的Excel范畴，重点研究Excel与其他软件系统的配合，如SQL、VB、PB三、函数的基本概念文峰汽车商学院，打造学习型组织公式对数据执行操作和运算的等式函数预定义的计算公式使用一些称为参数的特定数值按特定的顺序或结构进行计算公式：=(A1+A2+A3+…+A100)/100函数：=AVERAGE(A1：A100)四、函数的构成文峰汽车商学院，打造学习型组织构成：等号+函数名+括号+参数=F(X)等号括号函数名参数参数形式：=SUM(A1,C3:C6,E5/3,SUM(F1:F9),365)单元格引用表达式函数常量参数可以是数字、文本、形如TRUE或FALSE的逻辑值、数组、形如#N/A的错误值或单元格引用。给定的参数必须能产生有效的值。参数也可以是常量、公式或其它函数。参数不仅仅是常量、公式或函数，还可以是数组、单元格引用等课程目录文峰汽车商学院，打造学习型组织函数的定义及各类运算符（引用、运算、比较、文本）函数运算常见产生错误的各类原因（NA、REF、#value、#DIV0、#NAME）常用函数逻辑函数（TRUEFALSE、AND、OR、IF)统计函数（COUNT系列、SUMIF(S),AVERAGEIF(S)，RANK)查找函数（VLOOKUP、HLOOKUP、MATCH、INDEX、SEARCH)引用函数(INDIRECT、ROW、COLUMN)日期函数（DATEDIF、EOMONTH、EDATE)字符函数（TEXT、LEN、MID、RIGHT、LEFT、SUBSTITUTE)数组数组概念数组公式/统计/查询构建数组IF{1，0}工作中应用奖金发放规则（IFANDOR)排班表统计（COUNTACOUNTIF数组查询）汽车销量统计（SUMIF(S)、RANK、数组求和）客户地址整理（LEN、RIGHT、LEFT、SEARCH)日期提取及转换（SUBSTITUTE、TEXT)福利统计（DATEDIF、EOMONTH、SUMIF)人事信息整理（IF、MOD、EDATE、MID、TEXT、IF、COUNTIF)二级下拉菜单（INDIRECT、ROW)多工作表汇总查询/计算（INDIRECT、VLOOKUP、MATCH、COUNTIF）一对多条件查询（INDEX、MIN、SMALL、IF、COUNTIF、ROW、COLUMN)一、运算符文峰汽车商学院，打造学习型组织引用运算符含义示例：区域运算，生成两个引用之间所有单元格的引用A1：A3空格交叉运算,生成对两个引用中共有单元格的计算A1：A3A1：C4，联合运算，合并多个引用为一个引用A1：A3，A1：C4算术运算符含义示例+（加号）加法3+3–（减号）减法3–1负数–1*（星号）乘法3*3/（正斜杠）除法3/3%（百分号）百分比20%^（脱字号）乘方3^2比较运算符含义示例=(等号)等于A1=B1（大于号）大于A1B1（小于号）小于A1B1=（大于等于号）大于或等于A1=B1=（小于等于号）小于或等于A1=B1（不等号）不等于A1B1文本运算符含义示例&（与号）将两个值连接（或串联）起来产生一个连续的文本值North&wind的结果为Northwind二、常见运算产生错误的原因文峰汽车商学院，打造学习型组织一、“#VALUE!”四、“#REF!”二、“#DIV/O!”三、“#NAME！”当使用错误的参数或运算对象类型时，将产生错误值“#VALUE!”。删除了由其他公式引用的单元格，或将移动单元格粘贴到由其他公式引用的单元格中。当公式被零除时，将会产生错误值“#DIV/O!”。公式错误，无法自动更正当数值对函数或公式不可用时，出现该错误五、“#NA!”文峰汽车商学院，打造学习型组织逻辑函数TRUE/FALSE:用来返回参数中的逻辑值。可以直接在单元格中输入TRUE或者FALSE，通常不单独使用。引申为是和否、条件成立和不成立、真和假、在公式中还可以转换为0和1。在=-*/运算中false相当于0，true相当于1文峰汽车商学院，打造学习型组织逻辑函数AND:其逻辑表达式为：AND(Logical1,Logical2)Logical1、Logical2....是1到255个结果为TURE或FALSE的检测条件，检测内容可以使逻辑值、数组或引用当所有检测条件为真时，AND函数结果为TURE只要有一个条件为假时，AND函数结果即为FALSEOR:其逻辑表达式为：OR(Logical1,Logical2)Logical1、Logical2....是1到255个结果为TURE或FALSE的检测条件，检测内容可以使逻辑值、数组或引用只要一个逻辑值为真，OR函数即为TURE只有所有逻辑值都为假时，OR函数才为FALSEAND/OR通常与IF嵌套使用文峰汽车商学院，打造学习型组织逻辑函数IF:判断真假函数•用途:根据指定的条件来判断其“真”（TRUE）、“假”（FALSE），根据逻辑计算的真假值，从而返回相应的内容。•语法：IF(logical_test,value_if_true,value_if_false)•提示：在IF函数书写时，如果有文本出现，必须使用英文状态双引号表示引用。该规则同样适用于Excel所有函数IF嵌套函数使用所谓嵌套函数，就是指在某些情况下，可能需要将某函数作为另一函数的参数使用SUMIF:带条件的求和函数•用途:根据一定的条件进行求和。•语法：SUMIF（条件区域，条件，求和区域）•例如：分别统计金卡和普卡的消费金额统计函数SUMIFS:带条件的求和函数•用途:根据一定的条件进行求和。•语法：SUMIF（求和区域，条件区域，条件….）•例如：分别统计一期和二期金卡的消费金额统计函数文峰汽车商学院，打造学习型组织统计函数COUNT:统计数字个数•用途:统计数值型的数字计算进去；但是错误值、空值、逻辑值、文字则被忽略•语法：COUNT(value1,value2,...)COUNTA:统计非空单元格个数•用途:对“（）”内参数的个数进行统计，参数可以是单元格、单元格区域、数字、字符等，统计只按个数统计，内容不影响•语法：COUNTA(value1,value2,...)文峰汽车商学院，打造学习型组织统计函数COUNTIF:条件统计•用途:用来对搜索指定条件的单元格的个数，即只求和被满足条件的单元格个数。•语法：COUNTIF(range,criteria)Range为需要计算其中满足条件的单元格数目的单元格区域。Criteria为确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式、单元格引用或文本。COUNTIFS:满足多重条件的单元格数目•用途:计算某个区域中多重条件的单元格的数目•语法：COUNTIFS（条件区域，条件…..）•例如：统计一期消费金额大于1000的笔数统计函数文峰汽车商学院，打造学习型组织统计函数AVERAGEIF:条件求平均值用途:求某个区域内满足给定条件指定的单元格的平均值（算术平均值）语法：=averageif(range,criteria,[average_range])=Averageif（条件区，条件，平均值区域）参数Range表示：条件区——第二个参数条件所在的范围。参数Criteria表示：条件——是用来定义计算平均值的单元格。参数Average_range：平均值区域——参与计算平均值的单元格。（这参数可以省略，当条件区和平均值区域一致时）文峰汽车商学院，打造学习型组织统计函数AVERAGEIFS:多条件计算平均数•用途:回满足多重条件的所有单元格的平均值（算术平均值）语法：averageifs(average_range,criteria_range1,criteria1,crileria_range2,criteria2,...)参数average_range表示：求平均值区域——参与计算平均值的单元格。参数criteria_range,criteria_range2，...表示：条件区——criteria条件所在的范围。参数criteria1,criteria2，...表示：条件——是用来定义计算平均值的单元格。（形式可以是数字、表达式、单元格引用或文本的条件。文峰汽车商学院，打造学习型组织统计函数RANK:排名统计•用途:计算一个数字在一组数字列表中的排位语法：RANK(number,ref,order)1、Number为需要找到排位的数字。2、Ref为数字列表数组或对数字列表的引用。Ref中的非数值型参数将被忽略。3、Order为一数字，指明排位的方式。如果order为0（零）或省略，MicrosoftExcel对数字的排位是按照降序方式进行排列。如果order不为零，MicrosoftExcel对数字的排位则按照升序进行排列。MATCH:匹配函数•用途:返回指定数值在指定数组区域中的位置。语法：MATCH(lookup_value,lookup_array,match_type)lookup_value：需要在数据表（lookup_array）中查找的值。可以为数值（数字、文本或逻辑值）或对数字、文本或逻辑值的单元格引用。可以包含通配符、星号(*)和问号(?)。星号可以匹配任何字符序列；问号可以匹配单个字符。lookup_array：可能包含有所要查找数值的连续的单元格区域，区域必须是某一行或某一列，即必须为一维数据，引用的查找区域是一维数组。match_type：表示查询的指定方式，用数字-1、0或者1表示，match_type省略相当于match_type为1的情况。查找函数VLOOKUP:寻找并参照函数•用途:给定一个查找目标，它就能指定的查找区域中查找并且返回想要查找到的值。•语法：VLOOKUP(查找目标,查找范围,返回值的列数,精确OR模糊查找)•相似函数：HLOOKUP•备注：VLOOKUP进行纵向查找，HLOOKUP进行横向查找。查找函数INDEX•用途:返回数据表区域的值•语法：=index（数据表区域，行数，列数）•备注：通常与MATCH匹配试用查找函数SEARCH:寻找函数•用途:返回一个指定字符或文本字符串在字符串中第一次出现的位置,从左到右查找，忽略英文字母的大小写语法：search（find_text，within_text，start_num）=search（要查找的字符，字符所在的文本，从第几个字符开始查找）第三个参数可以省略（默认从第一个开始查找）。search函数的参数find_text可以使用通配符“*”，“？”。如果参数find_text就是问号或星号，则必须在这两个符号前加上“～”符号查找函数INDIRECT•用途:引用，并显示其内容•语法：=INDIRECT(ref_text,[a1])引用的两中形式。一种加引号，一种不加引号。=INDIRECT(A1)——加引号，文本引用=INDIRECT(A1)——不加引号，地址引用引用函