EXCEL2010函数教案

1、EXCEL2010函数一、为什么要学习函数文峰汽车商学院，打造学习型组织强大功能1 1效率2 2能力3 3二、成为函数高手的捷径文峰汽车商学院，打造学习型组织积极的心态循序渐进多阅读多实践善用资源学以致用知道Excel一种电子表格，会打开Excel，简单的输入数据和保存等幼幼儿园儿园水平水平 EXCEL 水 平 划 分会制作简单报表和最常用的功能，如：简单的格式设置、打印等小小学学水平水平会最常用的公式、函数和较简单的图表，可以自己设计有简单计算功能的表格初中水平初中水平掌握30个以上的常用函数，熟悉常用图表，会使用名称、自定义和条件格式、有效性、组及分级显示、高级筛选、自定义排序、合并、分列

2、，共享，快照，普通数据透视表，设计普通自动计算功能的表格，安全性的概念与简单应用，会录制宏，简单控件应用，高中水平高中水平熟悉工程、财务、统计等大部分不常用的函数，动态图表的熟练使用，数组公式的普通应用，初步了解vba和简单程序编制，制作自定义函数. 大大学学水平水平研究vba及Excel与office其他组件的相互配合，能解决Excel中的所有问题，研究出大量实用加载宏模板硕硕士水平士水平博士水平博士水平超出单纯的Excel范畴，重点研究Excel与其他软件系统的配合，如SQL、VB、PB三、函数的基本概念文峰汽车商学院，打造学习型组织公式对数据执行操作和运算的等式函数预定义的计算公式使用一

3、些称为参数的特定数值按特定的顺序或结构进行计算公式：=(A1+A2+A3+A100)/100=(A1+A2+A3+A100)/100函数：=AVERAGE(A1=AVERAGE(A1：A100)A100)四、函数的构成文峰汽车商学院，打造学习型组织构成：等号+函数名+括号+参数= F ( X )等号括号函数名参数参数形式：=SUM( A1, C3:C6, E5/3, SUM(F1:F9), 365)单元格引用表达式函数常量参数可以是数字、文本、形如 TRUE 或 FALSE 的逻辑值、数组、形如 #N/A 的错误值或单元格引用。给定的参数必须能产生有效的值。参数也可以是常量、公式或其它函数。参

4、数不仅仅是常量、公式或函数，还可以是数组、单元格引用等课程目录文峰汽车商学院，打造学习型组织函数的定义及各类运算符（引用、运算、比较、文本函数的定义及各类运算符（引用、运算、比较、文本）函数运算常见产生错误的各类原因（函数运算常见产生错误的各类原因（NANA、REFREF、#value#value、#DIV0#DIV0、#NAME#NAME）常用函数常用函数逻辑函数（逻辑函数（TRUE FALSE TRUE FALSE 、AND AND 、OROR、 IFIF) )统计函数（统计函数（COUNTCOUNT系列、系列、SUMIF(S),AVERAGEIF(S)SUMIF(S),AVERAGEIF

5、(S)，RANK)RANK)查找函数（查找函数（VLOOKUPVLOOKUP、HLOOKUPHLOOKUP、MATCHMATCH、INDEXINDEX、SEARCH)SEARCH)引用函数引用函数( (INDIRECTINDIRECT、ROWROW、COLUMN)COLUMN)日期函数（日期函数（DATEDIF DATEDIF 、EOMONTH EOMONTH 、EDATEEDATE) )字符函数字符函数（TEXTTEXT、LENLEN、MIDMID、RIGHTRIGHT、LEFTLEFT、SUBSTITUTESUBSTITUTE) ) 数组数组数组概念数组概念数组公式数组公式/ /统计统计/

6、 /查询查询 构建数组构建数组IF1IF1，00工作中应用工作中应用奖金发放规则（奖金发放规则（IF AND OR)IF AND OR)排班表统计（排班表统计（COUNTA COUNTIF COUNTA COUNTIF 数组查询）数组查询）汽车销量统计（汽车销量统计（SUMIF(S)SUMIF(S)、RANKRANK、数组求和）、数组求和）客户地址整理（客户地址整理（LEN LEN 、RIGHTRIGHT、 LEFT LEFT 、SEARCHSEARCH) )日期提取及转换（日期提取及转换（SUBSTITUTESUBSTITUTE、TEXT)TEXT)福利福利统计（统计（DATEDIF DAT

7、EDIF 、EOMONTH EOMONTH 、SUMIFSUMIF) )人事信息整理（人事信息整理（IFIF、 MOD MOD、 EDATE EDATE、 MID MID、 TEXT TEXT、 IF IF 、COUNTIFCOUNTIF) )二级下拉菜单（二级下拉菜单（INDIRECT INDIRECT 、ROWROW) )多工作表汇总查询多工作表汇总查询/ /计算（计算（INDIRECTINDIRECT、VLOOKUPVLOOKUP、MATCHMATCH、COUNTIFCOUNTIF）一对多条件查询一对多条件查询（INDEX INDEX 、MIN MIN 、SMALL SMALL 、IF

8、IF 、COUNTIFCOUNTIF、 ROW ROW、 COLUMN)COLUMN)一、运算符文峰汽车商学院，打造学习型组织引用运算符引用运算符含义含义示例示例：区域运算，生成两个引用之间所有单元格的引用A1：A3空格交叉运算,生成对两个引用中共有单元格的计算A1：A3 A1：C4，联合运算，合并多个引用为一个引用A1：A3，A1：C4算术运算符算术运算符含义含义示例示例+（加号）加法3+3（减号）减法31负数1*（星号）乘法3*3/（正斜杠）除法3/3%（百分号）百分比20%（脱字号）乘方32比较运算符比较运算符含义含义示例示例=(等号)等于A1=B1（大于号）大于A1B1（小于号）小于A

9、1=（大于等于号）大于或等于A1=B1=（小于等于号）小于或等于A1=B1（不等号）不等于A1B1文本运算符文本运算符含义含义示例示例&（与号）将两个值连接（或串联）起来产生一个连续的文本值North&wind 的结果为 Northwind二、常见运算产生错误的原因文峰汽车商学院，打造学习型组织一一、“#VALUE!” 四四、“#REF!” 二二、“#DIV/O!” 三三、“#NAME！” 当当使用错误的参数或运算对象类型使用错误的参数或运算对象类型时时，将将产生错误值产生错误值“#VALUE!”。 删除删除了由其他公式引用的单元格，或将移动单元格粘贴到由其他了由其他公式引用的

10、单元格，或将移动单元格粘贴到由其他公式引用的单元格中。公式引用的单元格中。 当当公式被零除时，将会产生错误值公式被零除时，将会产生错误值“#DIV/O!”。 公式错误，无法自动更正公式错误，无法自动更正当数值对函数或公式不可用时，出现该错误当数值对函数或公式不可用时，出现该错误五五、“#NA!” 文峰汽车商学院，打造学习型组织逻辑函数逻辑函数TRUE/FALSE: 用来返回参数中的逻辑值。可以直接在单元格中输入TRUE或者FALSE，通常不单独使用。引申为是和否、条件成立和不成立、真和假、在公式中还可以转换为0和1。在=-*/运算中false相当于0，true相当于1文峰汽车商学院，打造学习型

11、组织逻辑函数逻辑函数AND: 其其逻辑表达式为：逻辑表达式为：AND(Logical1,Logical2)Logical1、Logical2.是是1到到255个结果为个结果为TURE或或FALSE的检测条件，检测内容可以的检测条件，检测内容可以使逻辑值、数组或引用使逻辑值、数组或引用当所有检测条件为真时，当所有检测条件为真时，AND函数结果为函数结果为TURE只要有一个条件为假时，只要有一个条件为假时，AND函数结果即为函数结果即为FALSEOR:其其逻辑表达式为：逻辑表达式为：OR(Logical1,Logical2)Logical1、Logical2.是是1到到255个结果为个结果为TUR

12、E或或FALSE的检测条件，检测内容可以的检测条件，检测内容可以使逻辑值、数组或引用使逻辑值、数组或引用只要一个逻辑值为真，只要一个逻辑值为真，OR函数即为函数即为TURE只有所有逻辑值都为假时，只有所有逻辑值都为假时，OR函数才为函数才为FALSEAND/OR 通常与通常与IF嵌套使用嵌套使用文峰汽车商学院，打造学习型组织逻辑函数逻辑函数IF: 判断真假函数用途:根据指定的条件来判断其“真”（TRUE）、“假”（FALSE），根据逻辑计算的真假值，从而返回相应的内容。语法： IF(logical_test,value_if_true,value_if_false) 提示：IF嵌套函数使用所谓

13、嵌套函数，就是指在某些情况下，可能需要将某函数作为另一函数的参数使用SUMIF: 带条件的求和函数用途:根据一定的条件进行求和。语法：SUMIF（条件区域，条件，求和区域）例如：分别统计金卡和普卡的消费金额统计统计函数函数SUMIFS: 带条件的求和函数用途:根据一定的条件进行求和。语法：SUMIF（求和区域，条件区域，条件.）例如：分别统计一期和二期金卡的消费金额统计统计函数函数文峰汽车商学院，打造学习型组织统计函数统计函数COUNT: 统计数字个数用途:统计数值型的数字计算进去；但是错误值、空值、逻辑值、文字则被忽略语法： COUNT(value1,value2, .)COUNTA: 统计

14、非空单元格个数用途:对“（）”内参数的个数进行统计，参数可以是单元格、单元格区域、数字、字符等，统计只按个数统计，内容不影响语法： COUNTA(value1,value2, .)文峰汽车商学院，打造学习型组织统计函数统计函数COUNTIF: 条件统计用途:用来对搜索指定条件的单元格的个数，即只求和被满足条件的单元格个数。语法： COUNTIF(range,criteria) Range 为需要计算其中满足条件的单元格数目的单元格区域。 Criteria 为确定哪些单元格将被计算在内的条件，其形式可以 为数字、表达式、单元格引用或文本。COUNTIFS: 满足多重条件的单元格数目用途:计算某个

15、区域中多重条件的单元格的数目语法：COUNTIFS（条件区域，条件.）例如：统计一期消费金额大于1000的笔数统计统计函数函数文峰汽车商学院，打造学习型组织统计函数统计函数AVERAGEIF: 条件求平均值用途:求某个区域内满足给定条件指定的单元格的平均值（算术平均值）语法： =averageif(range, criteria, average_range)=Averageif（条件区，条件，平均值区域）参数Range表示：条件区第二个参数条件所在的范围。 参数Criteria表示：条件是用来定义计算平均值的单元格。 参数Average_range：平均值区域参与计算平均值的单元格。（这参数

16、可以省略，当条件区和平均值区域一致时）文峰汽车商学院，打造学习型组织统计函数统计函数AVERAGEIFS:多条件计算平均数用途:回满足多重条件的所有单元格的平均值（算术平均值）语法： averageifs(average_range,criteria_range1,criteria1,crileria_range2,criteria2,.)参数average_range表示：求平均值区域参与计算平均值的单元格。参数criteria_range,criteria_range2，.表示：条件区criteria条件所在的范围。参数criteria1,criteria2，.表示：条件是用来定义计算平均

17、值的单元格。 （形式可以是数字、表达式、单元格引用或文本的条件。文峰汽车商学院，打造学习型组织统计函数统计函数RANK:排名统计用途:计算一个数字在一组数字列表中的排位 语法：RANK(number,ref,order)1、Number 为需要找到排位的数字。2、Ref 为数字列表数组或对数字列表的引用。Ref 中的非数值型参数将被忽略。3、Order 为一数字，指明排位的方式。如果 order 为 0（零）或省略，Microsoft Excel 对数字的排位是按照降序方式进行排列。如果 order 不为零，Microsoft Excel 对数字的排位则按照升序进行排列。MATCH:匹配函数匹

18、配函数用途:返回指定数值在指定数组区域中的位置。语法：MATCH(lookup_value, lookup_array, match_type)lookup_value：需要在数据表（lookup_array）中查找的值。可以为数值（数字、文本或逻辑值）或对数字、文本或逻辑值的单元格引用。可以包含通配符、星号 (*) 和问号 (?)。星号可以匹配任何字符序列；问号可以匹配单个字符。lookup_array：可能包含有所要查找数值的连续的单元格区域，区域必须是某一行或某一列，即必须为一维数据，引用的查找区域是一维数组。match_type：表示查询的指定方式，用数字-1、0或者1表示，match

19、_type省略相当于match_type为1的情况。查找查找函数函数VLOOKUP: 寻找并参照函数用途:给定一个查找目标，它就能指定的查找区域中查找并且返回想要查找到的值。语法：VLOOKUP(查找目标,查找范围,返回值的列数,精确OR模糊查找)相似函数：HLOOKUP备注：VLOOKUP进行纵向查找，HLOOKUP进行横向查找。查找查找函数函数INDEX用途:返回数据表区域的值语法：=index（数据表区域，行数，列数）备注：通常与MATCH匹配试用查找查找函数函数SEARCH: 寻找函数用途:返回一个指定字符或文本字符串在字符串中第一次出现的位置 ,从左到右查找，忽略英文字母的大小写 语

20、法：search（find_text，within_text，start_num）=search（要查找的字符，字符所在的文本，从第几个字符开始查找）第三个参数可以省略（默认从第一个开始查找）。search函数的参数find_text可以使用通配符“*”，“？”。如果参数find_text就是问号或星号，则必须在这两个符号前加上“”符号查找查找函数函数INDIRECT用途:引用，并显示其内容语法：=INDIRECT(ref_text,a1)引用的两中形式。一种加引号，一种不加引号。=INDIRECT(A1)加引号，文本引用=INDIRECT(A1)不加引号，地址引用引用引用函数函数ROW用途:

21、返回所选择的某一个单元格的行数 语法：=row(reference)如果省略reference，则默认返回row函数所在单元格的行数引用引用函数函数COLUMN用途:返回所选择的某一个单元格的列数 语法：=COLUMN(reference)如果省略reference，则默认返回column函数所在单元格的列数DATEDIF 用途:计算两个日期之间的天数、月数或年数。这个函数是一个特殊函数，在函数清单中找不到，在帮助信息中也找不到。 语法： DATEDIF(开始日期, 结束日期, 单位) 单位 意义 “Y ” 时间段中的总年数 “M ” 时间段中的总月数 “D ” 时间段中的总天数 “MD ”

22、两日期中天数的差，忽略日期数据中的年和月 “YM ” 两日期中月数的差，忽略日期数据中的年和日 “YD ” 两日期中天数的差，忽略日期数据中的年日期函数日期函数EOMONTH 用途:返回指定日期往前或往后几个月的特定月份的月底日期。例：2007年4月12日之后3个月的月末日期：= EOMONTH(“2007-4-12”,3)，为2007-7-312007年4月12日之前5个月的月末日期：=EDATE(“2007-4-12”,-5)，为2006-11-30计算应付账款的到期日：如果一笔应付款的到期日为自交易日起满3个月后的下一个月的5号，比如交易日为2006年11月20日，满3个月后下个月5号就

23、是2007-3-5：=EOMONTH(2006-11-20,3-(DAY(A1)=5)+5日期函数日期函数EDATE 用途:返回指定日期往前或往后几个月的日期。 例：2007年4月12日之后3个月的日期：=EDATE(“2007-4-12”,3)，为2007-7-122007年4月12日之前3个月的日期：=EDATE(“2007-4-12”,-3)，为2007-1-12计算应付账款的到期日：如果一笔应付款的到期日为自交易日起3个月的那一天，比如交易日为2006年11月30日，满3个月后为2007-2-28：=EDATE(“2006-11-30”,3)，为2007-2-28日期函数日期函数字符函

24、数字符函数Mid: 截取字符串函数用途: Mid返回文本串中从指定位置开始的特定数目的字符，该数目可以由用户指定.语法：Mid(文本串，开始位置，返回字符的个数)参数：开始位置是文本中要提取的第一个字符的位置，文本中第一个字符的开始位置为1，以此类推。例如：A1单元格有身份证号码使用Mid(A1,7,4)得到结果”1982”字符函数字符函数Left/Right: 截取字符串函数用途: 根据指定的字符数返回文本串中的第一个或前几个字符。语法：Left(文本，返回的字符数) Right(文本，返回的字符数)参数：返回的字符数必须大于或等于0实例：返回客户姓名

25、中的姓氏 A1单元格字符串“张三”,使用Left(A1,1) 得到结果“张”字符函数字符函数LEN: 计算字符串长度用途: LEN返回文本的字符数 语法：LEN(文本)参数：此函数用于双字节字符，且空格也将作为字符进行统计。例如：检查身份证号码是否输入了18位。 A1单元格有身份证号码使用LEN(A1)得到结果18类似的函数LENB，返回单字节字符。例如：LENB(“ABC”) 返回 3，LENB(“张三”)返回4字符函数字符函数SUBSTITUTE: 替换指定文本用途: 在文字串中用新文本替代旧文本。如果需要在一个文字串中替换指定文本，可以使用SUBS

26、TITUTE；如果需要在某一字符串中替换指定位置处的任意文本，就应该使用函数REPLACE。 语法： SUBSTITUTE(需要替换其部分字符的文本，需被替换旧文本，用于替换的文本，第几次出现的旧文本)参数：如果不输入第四个参数将替换所有旧文本。例如：A1单元格有客户编码”G000G001”,将第二个”G”替换成”0”.使用SUBSTITUTE(A1,G,“0,2)得到结果”G0000001”使用SUBSTITUTE(A1,G,“0)得到结果”00000001”字符函数字符函数TEXT:用途:将数值转换为按指定数字格式表示的文本语法：TEXT(value,format_text)例：TEXT(

27、12345.6687,￥0.00)= ￥12345.67 20150601 =-TEXT(20150601,0!/00!/00)求余函数求余函数MOD:用途:两个数值表达式作除法运算后的余数语法：mod(nExp1,nExp2)例：MOD(7，2)= 1数组公式数组公式数组公式在计算时，把多个数据做为一个整体进行计算，其结果是返回多个数值。编辑栏可以看到以“”括起来的公式就是数组公式。普通公式则只是返回单个数值，即使在引用时引用了多个数值，但实际需要返回的只是其中的一个。数组求和数组求和数组求和：给定的机组数组中，将数组间对应的元素相乘，并返回乘积之和。常用数组常用数组用途： VLOOKUP从右至左匹配使用IF1, 0比如这个公式=VLOOKUP(U3,IF(1,0,S3:S15,Q3:Q15),2,0)1,0 常数数组 1和0 在逻辑中 不为0的值都是真值,为0的值是假值 因此在IF函数中1返回真值的部分 S3:S15 ,0返回假值的