VLOOKUP函数的使用方法

Electric Fever Co Ltd Add 355 Qin Qiao Road Jin Qiao Export Processing Zone Shanghai 201 206 P R China Tel 86 21 6100 2500 Fax 86 21 6100 2522 VLOOKUP 函数的使用方法 入门级 函数的使用方法 入门级 前言 VLOOKUP 函数是 EXCEL 最常用的查找函数 为了方便大家学习 兰色幻想特针对 VLOOKUP 函数的 使用和扩展应用 进行一次全面综合的说明 本文为入门部分 1 小心空格 2 格式要一样 3 注意如果在同一个 EXCL 文件下和不同的 SHEET 下 在拖拉后会自动跑格 这样会导致数据段一 直向下延伸 因此要注意用 进行锁定 要将数据 COPY 到另外一个工作表中 1 VLOOKUP 怎么用 怎么用 一 入门级一 入门级 VLOOKUP 是一个查找函数 给定一个查找的目标 它就能从指定的查找区域中查找返回想要查找到的值 它 的基本语法为 VLOOKUP 查找目标 查找范围 返回值的列数 精确 OR 模糊查找 下面以一个实例来介绍一下这四个参数的使用 例 1 如下图所示 要求根据表二中的姓名 查找姓名所对应的年龄 公式 B13 VLOOKUP A13 B 2 D 8 3 0 参数说明 Electric Fever Co Ltd Add 355 Qin Qiao Road Jin Qiao Export Processing Zone Shanghai 201 206 P R China Tel 86 21 6100 2500 Fax 86 21 6100 2522 1 查找目标 就是你指定的查找的内容或单元格引用 本例中表二 A 列的姓名就是查找目标 我们要根据表二 的 姓名 在表一中 A 列进行查找 公式 B13 VLOOKUP A13 B 2 D 8 3 0 2 查找范围 VLOOKUP A13 B 2 D 8 3 0 指定了查找目标 如果没有说从哪里查找 EXCEL 肯定会很为难 所以下一步我们就要指定从哪个范围中进行查找 VLOOKUP 的这第二个参数可以从一个 单元格区域中查找 也可以从一个常量数组或内存数组中查找 本例中要从表一中进行查找 那么范围我们要怎么 指定呢 这里也是极易出错的地方 大家一定要注意 给定的第二个参数查找范围要符合以下条件才不会出错 A 查找目标一定要在该区域的第一列 本例中查找表二的姓名 那么姓名所对应的表一的姓名列 那么表一的 姓名列 列 一定要是查找区域的第一列 象本例中 给定的区域要从第二列开始 即 B 2 D 8 而不能是 A 2 D 8 因为查找的 姓名 不在 A 2 D 8 区域的第一列 B 该区域中一定要包含要返回值所在的列 本例中要返回的值是年龄 年龄列 表一的 D 列 一定要包括在这 个范围内 即 B 2 D 8 如果写成 B 2 C 8 就是错的 3 返回值的列数 B13 VLOOKUP A13 B 2 D 8 3 0 这是 VLOOKUP 第 3 个参数 它是一个整数值 它怎么得来的呢 它是 返回值 在第二个参数给定的区域中的列数 本例中我们要返回的是 年龄 它是第二个参数 查找范围 B 2 D 8 的第 3 列 这里一定要注意 列数不是在工作表中的列数 不是第 4 列 而是在查找范围区 域的第几列 如果本例中要是查找姓名所对应的性别 第 3 个参数的值应该设置为多少呢 答案是 2 因为性别在 B 2 D 8 的第 2 列中 4 精确 OR 模糊查找 VLOOKUP A13 B 2 D 8 3 0 最后一个参数是决定函数精确和模糊查找的关键 精确即完全一样 模糊即包含的意思 第 4 个参数如果指定值是 0 或 FALSE 就表示精确查找 而值为 1 或 TRUE 时则表示模糊 这里兰色提醒大家切记切记 在使用 VLOOKUP 时千万不要把这个参数给漏掉了 如果缺少这个参 数默为值为模糊查找 我们就无法精确查找到结果了 好了 关于 VLOOKUP 函数的入门级应用就说到这里 VLOOKUP 函数可不只是这么简单的查找 我们讲的还 只是 1 10 的用法 其他的没法在一篇文章中说明 敬请期待 VLOOKUP 的使用方法 进阶篇 吧 本文为兰色幻想原创 转载请注明作者和转自 excel 精英培训 兰色幻想 VLOOKUP 函数的使用方法 初级篇 函数的使用方法 初级篇 上一讲咱们学习了 VLOOKUP 的基本用法和示例 本讲将介绍 VLOOKUP 在使用中的一些小技巧 Electric Fever Co Ltd Add 355 Qin Qiao Road Jin Qiao Export Processing Zone Shanghai 201 206 P R China Tel 86 21 6100 2500 Fax 86 21 6100 2522 一 一 VLOOKUP 多行查找时复制公式的问题多行查找时复制公式的问题 VLOOKUP 函数的第三个参数是查找返回值所在的列数 如果我们需要查找返回多列时 这个列数值需要一个个 的更改 比如返回第 2 列的 参数设置为 2 如果需要返回第 3 列的 就需要把值改为 3 如果有十几列会很 麻烦的 那么能不能让第 3 个参数自动变呢 向后复制时自动变为 2 3 4 5 在 EXCEL 中有一个函数 COLUMN 它可以返回指定单元格的列数 比如 COLUMNS A1 返回值 1 COLUMNS B1 返回值 2 而单元格引用复制时会自动发生变化 即 A1 随公式向右复制时会变成 B1 C1 D1 这样我们用 COLUMN 函数就可以转换成数字 1 2 3 4 例 下例中需要同时查找性别 年龄 身高 体重 公式 VLOOKUP A13 B 2 F 8 COLUMN B1 0 公式说明 这里就是使用 COLUMN B1 转化成可以自动递增的数字 二 二 VLOOKUP 查找出现错误值的问题 查找出现错误值的问题 Electric Fever Co Ltd Add 355 Qin Qiao Road Jin Qiao Export Processing Zone Shanghai 201 206 P R China Tel 86 21 6100 2500 Fax 86 21 6100 2522 1 如何避免出现错误值 EXCEL2003 在 VLOOKUP 查找不到 就 N A 的错误值 我们可以利用错误处理函数把错误值转换成 0 或空值 即 IF ISERROR VLOOKUP 参数略 VLOOKUP 参数略 EXCEL2007 EXCEL2010 中提供了一个新函数 IFERROR 处理起来比 EXCEL2003 简单多了 IFERROR VLOOKUP 2 VLOOKUP 函数查找时出现错误值的几个原因 A 实在是没有所要查找到的值 B 查找的字符串或被查找的字符中含有空格或看不见的空字符空格或看不见的空字符 验证方法是用 号对比一下 如果结果是 FALSE 就表示两个单元格看上去相同 其实结果不同 C 参数设置错误 VLOOKUP 的最后一个参数没有设置成 1 或者是没有设置掉 第二个参数数据源区域 查 找的值不是区域的第一列 或者需要反回的字段不在区域里 参数设置在入门讲里已注明 请参阅 D 数值格式不同 如果查找值是文本 被查找的是数字类型 就会查找不到 解决方法是把查找的转换成文本 或数值 转换方法如下 文本转换成数值 1 或 或 1 数值转抱成文本 赵三 A002 杨五 A003 孙二 A004 5 多条件查找 多条件查找 VLOOKUP 函数需要借用数组才能实现 函数需要借用数组才能实现 二 二 VLOOKUP 函数的多条件查找 函数的多条件查找 VLOOKUP 函数需要借用数组才能实现多条件查找 例 2 要求根据部门和姓名查找 C 列的加班时间 Electric Fever Co Ltd Add 355 Qin Qiao Road Jin Qiao Export Processing Zone Shanghai 201 206 P R China Tel 86 21 6100 2500 Fax 86 21 6100 2522 分析 我们可以延用例 1 的思路 我们的努力方向不是让 VLOOKUP 本身实现多条件查找 而是想办法重构一 个数组 多个条件我们可以用 销售赵三 5 人事杨五 3 销售赵三 6 4 完成了数组的重构后 接下来就是 VLOOKUP 的基本查找功能了 另外公式中含有多个数据与多个数据运 算 A2 A5 B2 B5 所以必须以数组形式输入 即按 ctrl shift 后按 ENTER 结束输入 6 多项查找 多项查找 三 三 VLOOKUP 函数的批量查找 函数的批量查找 VLOOKUP 一般情况下只能查找一个 那么多项该怎么查找呢 例 3 要求把如图表中所有张一的消费金额全列出来 Electric Fever Co Ltd Add 355 Qin Qiao Road Jin Qiao Export Processing Zone Shanghai 201 206 P R China Tel 86 21 6100 2500 Fax 86 21 6100 2522 分析 经过前面的学习 我们也有这样一个思路 我们在实现复杂的查找时 努力的方向是怎么重构一个查找内 容和查找的区域 要想实现多项查找 我们可以对查找的内容进行编号 第一个出现的是后面连接 1 第二个出现 的连接 2 公式 VLOOKUP B 9 ROW A1 IF 1 0 B 2 B 6 COUNTIF INDIRECT b2 b ROW 2 6 B 9 C 2 C 6 2 公式剖析 1 B 9 ROW A1 连接序号 公式向下复制时会变成 B 9 连接 1 2 3 2 给所有的张一进行编号 要想生成编号 就需要生成一个不断扩充的区域 INDIRECT b2 b ROW 2 6 然后在这个逐行扩充的区域内统计 张一 的个数 在连接上 B 2 B 6 后就可 以对所有的张一进行编号了 3 IF 1 0 把编号后的 B 列和 C 组重构成一个两列数组 通过以上的讲解 我们需要知道 VLOOKUP 函数的基本用法是固定的 要实现高级查找 就需要借助其他函 数来重构查找内容和查找数组 至此 VLOOKUP 函数从入门到高级的四篇 VLOOKUP 函数使用教程全部结束了 VLOOKUP 函数在数组运算中 还有着其他应用 但只是配角了 所以本系列不再介绍 由于笔者水平有限 不免有错漏之处 请大家多多指点 兰色幻想于 2011 年 11 月 22 日 Electric Fever Co Ltd Add 355 Qin Qiao Road Jin Qiao Export Processing Zone Shanghai 201 206 P R China Tel 86 21 6100 2500 Fax 86 21 6100 2522 原创 原创 vlookup 函数查找函数查找 12 种易犯错误 种易犯错误 1 3 vlookup 函数是一个非常好用的查找函数 但由于种种原因 在实际使用时会遇到种种让人搞不明白的错误 于是 兰色就把常遇到的 vlookup 错误问题来一次大整理 希望能对同学们有用 本文由兰色幻想原创 转载请注明转自 excel 精英培训网 一 函数参数使用错误 第 1 种 第 2 个参数区域设置错误之 1 例 如下图所示 根据姓名查找龄时产生错误 错误原因 vlookup 函数第二个参数是查找区域 该区域的第 1 列有一个必备条件 就是查找的对象 A9 必须对应于区域的第 1 列 本例中是根据姓名查找的 那么 第二个参数姓名必须是在区域的第 1 列位置 而上述 公式中姓名列是在区域 A1 E6 的第 2 列 所以公式应改为 VLOOKUP A9 B1 E6 3 0 第 2 种 第 2 个参数区域设置错误之 2 例 2 如下图所示根据姓名查找职务时产生查找错误 Electric Fever Co Ltd Add 355 Qin Qiao Road Jin Qiao Export Processing Zone Shanghai 201 206 P R China Tel 86 21 6100 2500 Fax 86 21 6100 2522 错误原因 本例是根据姓名查找职务 可大家注意一下 第 2 个参数 B1 D6 根本就没有包括 E 列的职务 当然 会产生错误了 所以公式应改为 VLOOKUP A9 B1 E6 4 0 第 3 种 第 4 个参数少了或设置错误 例 3 如下图所示根据工号查找姓名 错误原因 vlookup 第四个参数为 0 时表示精确查找 为 1 或省略时表示模糊查找 如果忘了设置第 4 个参数 则会被公式误以为是故意省略 按模糊查找进行 当区域也不符合模糊查找规则时 公式就会返回错误值 所以公 式应改为 Electric Fever Co Ltd Add 355 Qin Qiao Road Jin Qiao Export Processing Zone Shanghai 201 206 P R China Tel 86 21 6100 2500 Fax 86 21 6100 2522 VLOOKUP A9 A1 D6 2 0 或 VLOOKUP A9 A1 D6 2 注 当参数为 0 时可以省略 但必须保留 号 兰色说 今天所介绍的 1 3 错误是最简单的查找错误 可能有些同学已能轻松处理 明天咱们继续介绍 VLOOKUP 函数的其他查找错误 可能你处理起来就没这么轻松了 Lookup 数与行列比 Lookup 的工作职责是什么呢 用一个数与一行或一列数据依次进行比较 发现匹配的数值后 将另一组数据中对 应的数值提取出来 工资税率表 用数值比较 根据不同的工资进行不同的税率计算是一个常见的应用 我们来看这张 工资税率查询 表 见图 1 现在要在右 侧根据 收入 F 列 直接得到对应的 税率 G 列 在计算第 1 个 税率 时 输入函数公式 LOOKUP F4 B 3 B 8 D 3 D 8 回车 便可得到 36 00 这个结果是怎么来的 用 F4 中的第 1 个收入数 123 409 与左侧表的 收入最低 各档数据 B 3 B 8 进行 对比 虽然 123 409 在 收入最低 各档数中没有完全一致的数据与之匹配 但是会与其中小于它的最大数 58 501 相匹配 这样 同一行对应的 36 00 就提取出来了 图书销售表 用文本比较 Lookup 函数的对比数还可以是文本 在这张图书销售查询表中 见图 2 用下表输入的 编号 A15 单元格 文 本当作查询数 与上表的 编号 一列 A 3 A 11 进行对比 查询到了匹配的文本后 将 教材名称 一列 B 3 B 11 对应的数据提取出来 公式是 LOOKUP A15 A 3 A 11 B 3 B 11 Electric Fever Co Ltd Add 355 Qin Qiao Road Jin Qiao Export Processing Zone Shanghai 201 206 P R China Tel 86 21 6100 2500 Fax 86 21 6100 2522 Vlookup 数与表格比 Lookup 有一个大哥 Vlookup 函数 两兄弟有很多相似之处 但大哥本领更大 Vlookup 用对比数与一个 表 进 行对比 而不是 Lookup 函数的某 1 列或 1 行 并且 Vlookup 可以选择采用精确查询或是模糊查询方式 而 Lookup 只有模糊查询 模糊匹配 用 Vlookup 函数进行模糊查询时 几乎与 Lookup 的作用完全一致 我们用 Vlookup 函数来提取第 1 个例子中的工 资税率结果 函数公式为 VLOOKUP F4 B 3 D 8 3 TRUE 在这个函数中 用第 1 个收入 123 409 F4 单元格 当作对比数 用它与左侧表 B 3 D 8 的第 1 列数进 行对比 虽然 123 409 在 收入最低 各档数中没有完全一致的数据与之匹配 但是函数的最后一个参数是 TURE TURE 就是模糊查询 所以它会与其中小于它的最大数 58 501 相匹配 并将表中第 3 列 函数的第 3 个参 数为 3 对应的数据提取出来 所以结果同样是 36 00 订单明细表 精确匹配 有时候 我们需要精益求精 在下面这个 订单明细表 见图 3 中 最后一列 货运费用 中的数据要通过 交货方 式 从左侧 配送公司收费表 中进行匹配查询 这是一个典型的精确查询的例子 计算第 1 个数据的函数公式是 VLOOKUP H3 B 2 D 6 3 FALSE 小提示 把最后一个参数从 TRUE 变更成 FLASE 就是精确匹配 而精确查询 就是查询数要与查询表第 1 列中的数据完 全一致才能匹配提取 否则结果返回错误值 N A Electric Fever Co Ltd Add 355 Qin Qiao Road Jin Qiao Export Processing Zone Shanghai 201 206 P R China Tel 86 21 6100 2500 Fax 86 21 6100 2522 名师点评 Excel 为我们提供了近 20 个有关 查找和引用 的函数 除了最常用的 Lookup Vlookup 还有 Choos Row Colum Index 和 Match 等 大家可以通过函数的帮助查看具体的功能 这些函数往往不是单独使 用 可以与其他函数和 Excel 中的一些功能进行配合 希望大家以后再为数据 找朋友 时 不再有 众里寻它千百度 之感 直接 慕然回首 那数已在灯火阑珊处 了 这是由于提问中两个表格的 A 列的数字格式是不一样的 一个是数值格式的数字 一个是文本格式的 数字 只要把两列的格式设置为一样 因为都是数字 建议都设置为数值格式那么原来提问的公式就可 以用了 如不想改格式的 也可以把公式改为这样就能用了