Excel2010常用函数

《Excel2010常用函数》由会员分享，可在线阅读，更多相关《Excel2010常用函数（39页珍藏版）》请在金锄头文库上搜索。

1、 EXCEL2010 高效办公助手 常用函数应用 大钢牙甜甜整理 2012 4 12 1 目目 录录 单条件求和 SUMIF 函数 2 符合多个条件的数据求和 SUMIFS 函数 2 满足多重条件的单元格数目 COUNTIFS 函数 2 不显示计算结果为 0 值的项 AND 函数 IF 函数 3 判断单元格是否为空 ISBIANK 函数 4 名次排位函数 RANK 函数 5 提取字符串中的某些字符函数 LEFT RIGHT MID 7 插入当前时间函数 NOW函数 8 计算两个日期间的天数 DATEVALUE 函数 9 分解时间函数 YEAR 函数 9 计算员工工龄函数 YEAR 函数 11

2、去尾取整函数 INT函数 11 向下舍数字函数 ROUNDDOWN 函数 11 余数函数 MOD 函数 12 四舍五入函数 ROUND函数 13 查找函数 13 寻找并参照函数 vlookup 函数 只查找第一次出现的 13 VLOOKUP 函数的使用方法 入门级 14 VLOOKUP 函数的使用方法 初级篇 16 VLOOKUP 函数的使用方法 进阶篇 18 VLOOKUP 函数的使用方法 高级篇 21 返回指定行 列或单元格的值 INDEX 函数 24 返回匹配值的位置函数 MATCH 函数 只查找第一次出现的 26 INDEX 和 MATCH函数组合应用 相当于 vlookup 函数 2

3、7 引用函数 OFFSET 31 OFFSET 函数的用法及应用实战讲解 上 34 OFFSET 函数的经典应用 下 37 2 单条件求和单条件求和 SUMIF 函数函数 如要统计 C 列中的数据 要求统计条件是 B列中数据为 条件一 并将结果放在 C6 单元格中 我们 只要在 C6 单元格中输入公式 SUMIF B2 B5 条件一 C2 C5 即完成这一统计 符合多个条件的数据求和符合多个条件的数据求和 SUMIFS 函数函数 统计 5 班中语文名次 数学名次均位于前 20 名同学的总分之和 在相应单元格内 输入公式 SUMIFS M2 M80 A2 A80 5 D2 D80 20 F2 F

4、80 60 C2 C5 60 3 按回车键即可统计满足多个条件的学生人数 返回语文和数学成绩都及格的人数 相关说明 COUNTIFS 函数语法 COUNTIFS range1 criteria1 range2 criteria2 range1 range2 是计算关联条件的 1 至 127 个区域 每个区域中的单元格 必须是数字或包含数字的名称 数组或引用 空值和文本值会被忽略 criteria1 criteria2 是数字 表达式 单元格引用或文本形式的 1 至 127 个条件 用于定义要对哪些单元格进行计算 例如 条件可以表示为 32 32 32 apples 或 B4 仅当区域中的每一单

5、元格满足为其指定的所有相应条件时才对其进行计算 可以在条件中使用通配符 问号 和星号 问号匹配任意单个字符 星号 匹配任意一串字符 如果要查找实际的问号或星号 请在该字符前键入波形符 补充 大钢牙甜甜个1 w呃2 e钢3 1大4 大钢牙甜甜5 w飞6 区域中 大钢牙甜甜 出现次数 COUNTIF A1 C6 大钢牙甜甜 返回值为 2 不显示计算结果为不显示计算结果为 0 值的项值的项 AND 函数函数 IF 函数函数 在 Excel 中 使用公式进行填充运算时 当引用单元格都没有输入数值时 结果单 元格会显示 0 值 如果不显示计算结果为不显示计算结果为 O O 值的项值的项 可使用 AND

6、函数不显示计算结果为 O 值的项 4 如上图所示 当 B2 和 C2 单元格同时为空值时 D2 单元格公式的计算结果显示为 0 如果不显示计算结果为 O 值的项可将 D2 单元格的公式改为 IF AND B2 C2 B2 C2 如上图所示 当 B2 和 C2 单元格同时为空值时 D2 单元格不显示 0 值 其它的公 式可使用 Excel 自动填充进行公式填充 Excel2007 使用 AND 函数可判断不显示计算结果为 O 值的项 相关说明 相关说明 AND 函数语法 AND logical1 logical2 所有参数的逻辑值为真时 返回 TRUE 只要一个参数的逻辑值为假 即返回 FALS

7、E 参数必须是逻辑值 TRUE 或 FALSE 或者包含逻辑值的数组或引用 如果数组或引用参数中包含文本或空白单元格 则这些值将被忽略 如果指定的单元格区域内包括非逻辑值 则 AND 将返回错误值 VALUE 判断单元格是否为空判断单元格是否为空 ISBIANK 函数函数 第一 isblank 函数的用法判断单元格是否为空 5 Excel 中 isblank 函数的用法是判断单元格是否为空 下图所示 A1 单元格没有输入任何东西 A2 单元格输入 我们在 C1 单元格输入公式 IF ISBLANK A1 TRUE FALSE 公式的意思是用 ISBLANK A1 做为 IF 条件 如果单元格为

8、空 则返回 TRUE 反之返回 FALSE 解释 isblank 函数的用法是判别该单元格是否为空单元格 真空 而 A2 单元 格中是 为假空而非真空单元格 所以会返回 FALSE 第二 isblank 函数的用法实例 从帮助文件我们得知 isblank 函数的用法是检验指定值并返回 TRUE 或 FALSE 例 如 如果参数 value 引用的是空单元格 ISBLANK 函数返回逻辑值 TRUE 否则 返回 FALSE 下面截图根据 AB 列成绩 如果两科成绩都不为空 就求两科的平均分 如果任意 一科为空 则返回 缺考 我们根据题意 在 C2 单元格写出公式 IF OR ISBLANK A2

9、 ISBLANK B2 缺考 AVERAGE A2 B2 并下拉复制完成 名次排位函数名次排位函数 RANK 函数函数 rank 函数最常用的是求某一个数值在某一区域内的排名 rank 函数语法形式 rank number ref order 6 函数名后面的参数中 number 为需要求排名的那个数值或者单元格名称 单元格内 必须为数字 ref 为排名的参照数值区域 order 的为 0 和 1 默认不用输入 得到 的就是从大到小的排名 若是想求倒数第几 order 的值请使用 1 下面给出几个 rank 函数的范例 示例 1 正排名 此例中 我们在 B2 单元格求 20 这个数值在 A1

10、A5 区域内的排名情况 我们并没 有输入 order 参数 不输入 order 参数的情况下 默认 order 值为 0 也就是从高到低 排序 此例中 20 在 A1 A5 区域内的正排序是 1 所以显示的结果是 1 示例 2 倒排名 此例中 我们在上面示例的情况下 将 order 值输入为 1 发现结果大变 因为 order 值为 1 意思是求倒数的排名 20 在 A1 A5 区域内的倒数排名就是 4 示例 3 求一列数的排名 在实际应用中 我们往往需要求某一列的数值的排名情况 例如 我们求 A1 到 A5 单元格内的数据的各自排名情况 我们可以使用单元格引用的方法来排名 rank a1 a

11、1 a5 此公式就是求 a1 单元格在 a1 a5 单元格的排名情况 当我们使用 自动填充工具拖拽数据时 发现结果是不对的 仔细研究一下 发现 a2 单元格的公式 7 居然变成了 rank a2 a2 a6 这超出了我们的预期 我们比较的数据的区域是 a1 a5 不能变化 所以 我们需要使用 符号锁定公式中 a1 a2 这段公式 所以 a1 单元 格的公式就变成了 rank a1 a 1 a 5 提取字符串中的某些字符函数提取字符串中的某些字符函数 LEFT RIGHT MID LEFT RIGHT MID 这三个函数都是文本的提取的 前两个的格式是一样的 只是 提取的方向正好相反 LEFT

12、是从左向右取 RIGHT 是从右向左取 LEFT text num chars RIGHT text num chars 第一个参数 text 是文本 是你想要在那提取东西的文本 可以是一个字符 串 或是一个单元格 第二个参数 chars 是想要提取的个数 比如 LEFT A1 2 也是从 A1 单元格的文本里 从左边第一位开始 向 右提取两位 比如 A1 的数据是 找吃的蚂蚁 得到的结果是 找吃 那么 RIGHT A1 2 也就是从 A1 单元格的文本里 从右边第一位开 向 左提取两位 这样就得到 蚂蚁 当然你也可以这样写 如果你喜欢 LEFT 找吃的蚂蚁 2 MID 这个函数也是从左向右提

13、取的 但不一定是从第一个起 可以从中间开始 先看它的格式吧 MID text start num num chars 第一个参数也是 text 它的属性与前面两个是一样的 第二个参数 star num 也就是要提取的开始字符 第三个参数 num chars 是要提取的个数 用前面的例子得到 找吃 的结果的 MID A1 1 2 而得到 蚂蚁 的 公式是 MID A1 4 2 第一个公式的意思是 在 A1 单元格里提取文本 从文本的第 1 位开始 向右 提取 2 个 第二个公式的意思是 在 A1 单元格里提取文本 从文本的第 4 位开始 向右 提取 2 个 注意 MID 函数只能从左向右提取的

14、8 插入当前时间函数插入当前时间函数 NOW函数函数 TODAY 和 NOW 都是 Excel 中与日期时间相关的函数 二者的不同之处是 TODAY 函数仅插入当前的日期 如 TODAY 会在所输入的单元格中显示当前日期 如 2009 5 14 而 NOW 函数同时插入日期和时间 如 NOW 单元格中显示为 2009 5 14 16 06 因此 当需要计算某日期距今天有多少整数天时 应使用 TODAY 函数 例如今天为 2009 年 5 月 14 日 计算上海世博会距今有多少天 DATE 2010 5 1 TODAY 公式返回 352 天 9 计算两个日期间的天数计算两个日期间的天数 DATE

15、VALUE 函数函数 分解时间函数分解时间函数 YEAR 函数函数 10 11 计算员工工龄函数计算员工工龄函数 YEAR 函数函数 去尾取整函数去尾取整函数 INT函数函数 注 不可调整小数位数 向下舍数字函数向下舍数字函数 ROUNDDOWNROUNDDOWN 函数函数 功能 按指定的位数对数值进行舍入 格式 ROUNDDOWN 数值或数值单元格 指定的位数 示例 12 12 351 325 525 保留2位小数 舍去千分位及以后的小数位保留到百分位 ROUNDDOWN A1 2 12 35 ROUNDDOWN A2 2 325 52 舍去小数位保留整数 舍去十分位及以后的小数位保留整数部

16、分 ROUNDDOWN A1 0 12 ROUNDDOWN A2 0 325 整数保留到十位 整数部分舍去个位上大于0的数字 用0代替 保留十位及以前的高位数字 ROUNDDOWN A1 1 10 ROUNDDOWN A2 1 320 说明 函数 ROUNDDOWN 的第 1 个参数可以是具体的数值也可以是数值单元格引用 函数 ROUNDDOWN 的第 2 个参数 指定保留的位数 保留小数位用正整数表示 即 1 2 3 4 对应十分位 百分位 千分位 万分位 保留整数位用非正整数 表示 即 0 1 2 3 对应个位 十位 百位 函数 ROUND 与函数 ROUNDDOWN 的对比 ROUND 函数按指定位数把右侧数位上的数字进行四舍五入 ROUNDDOWN 函数按指定位数把右侧数位上的数字舍弃为 0 余数函数余数函数 MOD 函数函数 13 四舍五入函数四舍五入函数 ROUND 函数函数 查找函数查找函数 寻找寻找并并参照函数参照函数 vlookup 函数函数 只查找第一次出现的 只查找第一次出现的 首先我们介绍下使用的函数 vlookup 的几个参数 vlookup 是判断引用数据的