《EXCEL2010函数教（学）案》由会员分享，可在线阅读，更多相关《EXCEL2010函数教（学）案（43页珍藏版）》请在金锄头文库上搜索。
1、 EXCEL2010函数 一 为什么要学习函数 二 成为函数高手的捷径 积极的心态 循序渐进 EXCEL水平划分 三 函数的基本概念 公式 对数据执行操作和运算的等式 函数 预定义的计算公式使用一些称为参数的特定数值按特定的顺序或结构进行计算 公式 A1 A2 A3 A100 100 函数 AVERAGE A1 A100 四 函数的构成 构成 等号 函数名 括号 参数 F X 等号 括号 函数名 参数 参数形式 SUM A1 C3 C6 E5 3 SUM F1 F9 365 单元格引用 表达式 函数 常量 参数可以是数字 文本 形如TRUE或FALSE的逻辑值 数组 形如 N A的错误值或单元
2、格引用 给定的参数必须能产生有效的值 参数也可以是常量 公式或其它函数 参数不仅仅是常量 公式或函数 还可以是数组 单元格引用等 课程目录 一 运算符 二 常见运算产生错误的原因 一 VALUE 四 REF 二 DIV O 三 NAME 当使用错误的参数或运算对象类型时 将产生错误值 VALUE 删除了由其他公式引用的单元格 或将移动单元格粘贴到由其他公式引用的单元格中 当公式被零除时 将会产生错误值 DIV O 公式错误 无法自动更正 当数值对函数或公式不可用时 出现该错误 五 NA 逻辑函数 TRUE FALSE 用来返回参数中的逻辑值 可以直接在单元格中输入TRUE或者FALSE 通常不
3、单独使用 引申为是和否 条件成立和不成立 真和假 在公式中还可以转换为0和1 在 运算中false相当于0 true相当于1 逻辑函数 AND 其逻辑表达式为 AND Logical1 Logical2 Logical1 Logical2 是1到255个结果为TURE或FALSE的检测条件 检测内容可以使逻辑值 数组或引用当所有检测条件为真时 AND函数结果为TURE只要有一个条件为假时 AND函数结果即为FALSE OR 其逻辑表达式为 OR Logical1 Logical2 Logical1 Logical2 是1到255个结果为TURE或FALSE的检测条件 检测内容可以使逻辑值 数组
4、或引用只要一个逻辑值为真 OR函数即为 TURE 只有所有逻辑值都为假时 OR函数才为 FALSE AND OR通常与IF嵌套使用 逻辑函数 IF 判断真假函数 用途 根据指定的条件来判断其 真 TRUE 假 FALSE 根据逻辑计算的真假值 从而返回相应的内容 语法 IF logical test value if true value if false 提示 在IF函数书写时 如果有文本出现 必须使用英文状态双引号表示引用 该规则同样适用于Excel所有函数IF嵌套函数使用所谓嵌套函数 就是指在某些情况下 可能需要将某函数作为另一函数的参数使用 SUMIF 带条件的求和函数 用途 根据一定
5、的条件进行求和 语法 SUMIF 条件区域 条件 求和区域 例如 分别统计金卡和普卡的消费金额 统计函数 SUMIFS 带条件的求和函数 用途 根据一定的条件进行求和 语法 SUMIF 求和区域 条件区域 条件 例如 分别统计一期和二期金卡的消费金额 统计函数 统计函数 COUNT 统计数字个数 用途 统计数值型的数字计算进去 但是错误值 空值 逻辑值 文字则被忽略语法 COUNT value1 value2 COUNTA 统计非空单元格个数 用途 对 内参数的个数进行统计 参数可以是单元格 单元格区域 数字 字符等 统计只按个数统计 内容不影响语法 COUNTA value1 value2
6、统计函数 COUNTIF 条件统计 用途 用来对搜索指定条件的单元格的个数 即只求和被满足条件的单元格个数 语法 COUNTIF range criteria Range为需要计算其中满足条件的单元格数目的单元格区域 Criteria为确定哪些单元格将被计算在内的条件 其形式可以为数字 表达式 单元格引用或文本 COUNTIFS 满足多重条件的单元格数目 用途 计算某个区域中多重条件的单元格的数目语法 COUNTIFS 条件区域 条件 例如 统计一期消费金额大于1000的笔数 统计函数 统计函数 AVERAGEIF 条件求平均值 用途 求某个区域内满足给定条件指定的单元格的平均值 算术平均值
7、语法 averageif range criteria average range Averageif 条件区 条件 平均值区域 参数Range表示 条件区 第二个参数条件所在的范围 参数Criteria表示 条件 是用来定义计算平均值的单元格 参数Average range 平均值区域 参与计算平均值的单元格 这参数可以省略 当条件区和平均值区域一致时 统计函数 AVERAGEIFS 多条件计算平均数 用途 回满足多重条件的所有单元格的平均值 算术平均值 语法 averageifs average range criteria range1 criteria1 crileria range2
8、 criteria2 参数average range表示 求平均值区域 参与计算平均值的单元格 参数criteria range criteria range2 表示 条件区 criteria条件所在的范围 参数criteria1 criteria2 表示 条件 是用来定义计算平均值的单元格 形式可以是数字 表达式 单元格引用或文本的条件 统计函数 RANK 排名统计 用途 计算一个数字在一组数字列表中的排位语法 RANK number ref order 1 Number为需要找到排位的数字 2 Ref为数字列表数组或对数字列表的引用 Ref中的非数值型参数将被忽略 3 Order为一数字
9、指明排位的方式 如果order为0 零 或省略 MicrosoftExcel对数字的排位是按照降序方式进行排列 如果order不为零 MicrosoftExcel对数字的排位则按照升序进行排列 MATCH 匹配函数 用途 返回指定数值在指定数组区域中的位置 语法 MATCH lookup value lookup array match type lookup value 需要在数据表 lookup array 中查找的值 可以为数值 数字 文本或逻辑值 或对数字 文本或逻辑值的单元格引用 可以包含通配符 星号 和问号 星号可以匹配任何字符序列 问号可以匹配单个字符 lookup array
10、可能包含有所要查找数值的连续的单元格区域 区域必须是某一行或某一列 即必须为一维数据 引用的查找区域是一维数组 match type 表示查询的指定方式 用数字 1 0或者1表示 match type省略相当于match type为1的情况 查找函数 VLOOKUP 寻找并参照函数 用途 给定一个查找目标 它就能指定的查找区域中查找并且返回想要查找到的值 语法 VLOOKUP 查找目标 查找范围 返回值的列数 精确OR模糊查找 相似函数 HLOOKUP备注 VLOOKUP进行纵向查找 HLOOKUP进行横向查找 查找函数 INDEX 用途 返回数据表区域的值语法 index 数据表区域 行数
11、列数 备注 通常与MATCH匹配试用 查找函数 SEARCH 寻找函数 用途 返回一个指定字符或文本字符串在字符串中第一次出现的位置 从左到右查找 忽略英文字母的大小写语法 search find text within text start num search 要查找的字符 字符所在的文本 从第几个字符开始查找 第三个参数可以省略 默认从第一个开始查找 search函数的参数find text可以使用通配符 如果参数find text就是问号或星号 则必须在这两个符号前加上 符号 查找函数 INDIRECT 用途 引用 并显示其内容语法 INDIRECT ref text a1 引用的两中
12、形式 一种加引号 一种不加引号 INDIRECT A1 加引号 文本引用 INDIRECT A1 不加引号 地址引用 引用函数 ROW 用途 返回所选择的某一个单元格的行数语法 row reference 如果省略reference 则默认返回row函数所在单元格的行数 引用函数 COLUMN 用途 返回所选择的某一个单元格的列数语法 COLUMN reference 如果省略reference 则默认返回column函数所在单元格的列数 DATEDIF 用途 计算两个日期之间的天数 月数或年数 这个函数是一个特殊函数 在函数清单中找不到 在帮助信息中也找不到 语法 DATEDIF 开始日期
13、结束日期 单位 单位意义 Y 时间段中的总年数 M 时间段中的总月数 D 时间段中的总天数 MD 两日期中天数的差 忽略日期数据中的年和月 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
14、号 比如交易日为2006年11月20日 满3个月后下个月5号就是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
15、 日期函数 字符函数 Mid 截取字符串函数 用途 Mid返回文本串中从指定位置开始的特定数目的字符 该数目可以由用户指定 语法 Mid 文本串 开始位置 返回字符的个数 参数 开始位置是文本中要提取的第一个字符的位置 文本中第一个字符的开始位置为1 以此类推 例如 A1单元格有身份证号码 352624198208226319 使用Mid A1 7 4 得到结果 1982 字符函数 Left Right 截取字符串函数 用途 根据指定的字符数返回文本串中的第一个或前几个字符 语法 Left 文本 返回的字符数 Right 文本 返回的字符数 参数 返回的字符数必须大于或等于0实例 返回客户姓名
16、中的姓氏A1单元格字符串 张三 使用Left A1 1 得到结果 张 字符函数 LEN 计算字符串长度 用途 LEN返回文本的字符数语法 LEN 文本 参数 此函数用于双字节字符 且空格也将作为字符进行统计 例如 检查身份证号码是否输入了18位 A1单元格有身份证号码 352624198208226319 使用LEN A1 得到结果18类似的函数LENB 返回单字节字符 例如 LENB ABC 返回3 LENB 张三 返回4 字符函数 SUBSTITUTE 替换指定文本 用途 在文字串中用新文本替代旧文本 如果需要在一个文字串中替换指定文本 可以使用SUBSTITUTE 如果需要在某一字符串中替换指定位置处的任意文本 就应该使用函数REPLACE 语法 SUBSTITUTE 需要替换其部分字符的文本 需被替换旧文本 用于替换的文本 第几次出现的旧文本 参数 如果不输入第四个参数将替换所有旧文本 例如 A1单元格有客户编码 G000G001 将第二个 G 替换成 0 使用SUBSTITUTE A1 G 0 2 得到结果 G0000001 使用SUBSTITUTE A1 G 0 得到结果
