vlookup应用实例.xls

VLOOKUPVLOOKUP lookup valuelookup value table arraytable array col index numcol index num range lookup Lookup valueLookup value 为需要在Table array第一列中查找的数值 可以为数值 引用或文本字符串 需要注意的是类型必须与table array第一列的类型一致 查找文本时 文本不区分大小写 可以使用通配符 Table arrayTable array 为需要在其中查找数据的数据表 可以使用对区域或区域名称的引用 常数数组 计算后的内存数组 对区域引用时 可以引用整列 excel会自动判断使用区域 该参数的第一列必须包含查找的内容 其它列包含需返回的内容 返回内容的列序号由下个参数指定 Col index numCol index num 为table array中待返回的匹配值的列序号 如为1时 返回table array第一列中的数值 为2 返回table array第二列中的数值 以此类推 如果col index num小于1 函数 VLOOKUP 返回错误值值 VALUE 如果col index num大于table array的列数 函数 VLOOKUP 返回错误值 REF Range lookup 为一逻辑值 指明函数VLOOKUP返回时是精确匹配还是近似匹配 如果为TRUE或省略 则返回近似匹配值 也就是说 如果找不到精确匹配值 则返回小于lookup value的最大数值 近似匹配查询一般用于数值的查询 table array的第一列必须按升序排列 否则不能返回正确的结果 如果range value为FALSE 或0 函数VLOOKUP将返回精确匹配值 此时 table array不必进行排序 如果找不到 则返回错误值 N A 可isna检测错误后使用if判断去除错误信息 处理查找错误 例1精确匹配查询之基本运用例8在同一单元格按不同字段查询 例2数值近似匹配查询之基本运用例9返回多个符合条件的查询结果 例3文本近似匹配查询之基本运用例10有两个并列条件 不同字段 的查询 例4区别查询结果的空白与0值例11使用数组常量的查询 例5依次序返回同一查询结果的多列内容例12从右向左查询 例6返回同一查询结果的多列 不按原次序 的内容 例7按部分内容模糊查询 以上举例是vlookup的基本运用 再结合其它函数 可以发挥其更多作用 可以为数值 引用或文本字符串 需要注意的是类型必须与table array第一列的类型一致 该参数的第一列必须包含查找的内容 其它列包含需返回的内容 返回内容的列序号由下个参数指定 如为1时 返回table array第一列中的数值 为2 返回table array第二列中的数值 以此类推 如果col index num大于table array的列数 函数 VLOOKUP 返回错误值 REF 如果为TRUE或省略 则返回近似匹配值 也就是说 如果找不到精确匹配值 则返回小于lookup value的最大数值 近似匹配查询一般用于数值的查询 table array的第一列必须按升序排列 否则不能返回正确的结果 此时 table array不必进行排序 如果找不到 则返回错误值 N A 可isna检测错误后使用if判断去除错误信息 有两个并列条件 不同字段 的查询 vlookupvlookup应应用用实实例例 返返回回 精精确确匹匹配配查查询询之之基基本本运运用用 精确查找是vlookup最基本也是最常用的功能 对于数据量大的查找 其速度比菜单中的查找还快 设置vlookup第四个参数为false或0 即为精确查找 精确查找适用于文本 也适用于数值 但对数值查找时须注意格式一致 否则会出错 表表一一姓姓名名工工号号性性别别籍籍贯贯出出生生年年月月 张三丰KT001男北京1970年8月 李四光KT002女天津1980年9月 王麻子KT003男河北1975年3月 赵六儿KT004女河南1985年12月 姓姓名名籍籍贯贯 赵六儿河南根据姓名在 B 9 F 12中查找籍贯 B 9 F 12区域最好使用绝对引用 可在公式中选定区域按F4转换 便于复制 查找的姓名必须位于 B 6 F 9的第一列 籍贯在 B 6 F 9位于第四列 因此第三个参数为4 精确查找 第四个参数FALSE 姓姓名名性性别别 李四光女根据姓名在 表一 中查找籍贯 表一 是定义的单元格区域 B 9 F 12的名称 在名称框 编辑栏左边 可以选定 也可在插入 名称 定义中查看修改 工工号号籍籍贯贯 KT002天津根据工号在 C 8 E 12中查找籍贯 注意范围已改为 C 8 E 12 确保工号在第一列 由于范围的改变 籍贯位于该范围的第3列 因此第三个参数为3 工工号号出出生生年年月月 KT00125781根据工号在 C 8 F 12中查找出生年月 但此时返回的是时间序列值 即格式没有带过来 1970年8月需要重新设定单元格格式 1970年8月或在公式中使用text设定格式 数数值值近近似似匹匹配配查查询询之之基基本本运运用用 近似匹配查找通常情况下用于累进数值的查找 此时第四个参数省略 或为true 或为非0数值 EXCEL中0等同FALSE 非零值均视为TRUE 这是一张个所税税率表 对于不确定的收入 可以利用近似匹配查找税率及速扣数 表表二二级级数数 应应税税所所得得超超过过且且不不超超过过税税率率 速速算算扣扣除除数数 150050 250020001025 32000500015125 450002000020375 52000040000251375 64000060000303375 76000080000356375 8800001000004010375 91000004515375 应应税税所所得得税税率率速速算算扣扣除除数数 税税款款 536020375697 5360在表中未列出 因此EXCEL查找小于5360的最大值即5000 并返回对应的税率等 注意公式中第四个参数省略 或为true 或为非0数值 并且查找的第一列必须升序排列 否则不会返回期望的结果 需要注意的是此时不一定返回错误 试着输入5000 你会发现税率为20 应是15 由条件指定 虽然对最后计算结果没有影响 以下公式可以解决这个问题 应应税税所所得得税税率率速速算算扣扣除除数数 税税款款 500015125625 文文本本近近似似匹匹配配查查询询之之基基本本运运用用 表表三三对于文本 一般不使用近似匹配查找 非模糊查找 吖A并非文本不能用于近似查找 主要是没有太多用途 除了下面这个例子 八B 嚓C这个例子利用文本近似匹配查找汉字的第一个拼音字母 咑D由于EXCEL中汉字字符是按拼音排序的 因此汉字字符比较时的大小与拼音同序 一般情况下 鵽E左边的表是根据汉字拼音首字母排列 并取各字母的临界点汉字字符 该字母的 最小汉字 发F 猤G汉汉字字字字符符拼拼音音首首字字母母 铪H家J 夻J 咔K通过将 家 与表中字符比较后 找到比 家 小的最大值 夻 返回J 垃L这个例子一般用于姓名的缩写 而上面的公式只找第一个字符 因此需要修改 嘸M 旀N汉汉字字字字符符拼拼音音首首字字母母 噢O普利卡PLK 妑P 七Q这个公式取得前三个汉字字符的拼音字母 使用MID依次取出字符后由Vlookup查询到字母 最后 20 30 40 是一列四行 纵向 包含四个元素的一维数组常量 10 20 30 40 50 60 是两行三列 包含六个元素的二维数组常量 二维数组常量需要行列等长 如果你觉得输数组常量麻烦 要区分 文本要加 可以先将数据输入单元格区域 然后在其它单元格输入 用鼠标选定该区域后按F9 显示的就是数组常量 然后复制到公式中 其实将以前使用的vlookup公式中第三个参数 区域不要太大 选定按F9 再回车 可以看到公式正常运作 此时已使用了数组常量 以下举例中的公式并不是数组公式 输入时不用Ctrl Shift Enter一齐按 搬一个由chenjun版主提供的计算个人所得税的例子 应应税税所所得得税税率率速速算算扣扣除除数数 税税款款 650020375925 再搬一个前面的例子 汉汉字字字字符符拼拼音音首首字字母母 海H是否被公式中的数组常量吓一跳 不是一个个输的 按F9得到的 汉汉字字字字符符拼拼音音首首字字母母 螺丝扣LSK对于此类固定的数组常量可以将其定义一个名称 公式就简洁多了 还不占用单元格 公式中的 拼音 就是一个定义的名称 可以自 插入 名称 定义 中查看 从从右右向向左左查查询询 我们知道 vlookup查找的列必须位于查找区域的最左列 有时会需要按右面的查找左边的值 以 表一 为例 要按 姓名 查询 工号 很容易 但反过来按 工号 查找 姓名 直接使用vlookup就不行了 此时可以使用的公式很多 如lookup index match offset match indirect match等 但由chenjun版主提供的使用vlookup的解法我觉得很有参考价值 介绍这个用法的目的主要是学会将两个一维数组合并为一个二维数组 工工号号姓姓名名 KT002李四光这个公式先由 IF 1 0 C 9 C 12 B 9 B 12 计算出内存数组 KT001 张三丰 KT002 李四光 KT003 王麻子 KT004 赵六儿 可以看到 在该内存数组中 工号 位于 姓名 的左侧 Vlookup在该内存数组中查找 注意 1 0 是一行二列 横向 常量数组 后面的两个区域是多行一列 纵向 即两个数组的方向不同 这样才会生成两列多行数组 张三丰 李四光 王麻子 赵六儿 KT001 KT002 KT003 KT004 精确查找是vlookup最基本也是最常用的功能 对于数据量大的查找 其速度比菜单中的查找还快 设置vlookup第四个参数为false或0 即为精确查找 根据姓名在 B 9 F 12中查找籍贯 B 9 F 12区域最好使用绝对引用 可在公式中选定区域按F4转换 便于复制 查找的姓名必须位于 B 6 F 9的第一列 籍贯在 B 6 F 9位于第四列 因此第三个参数为4 精确查找 第四个参数FALSE 表一 是定义的单元格区域 B 9 F 12的名称 在名称框 编辑栏左边 可以选定 也可在插入 名称 定义中查看修改 注意范围已改为 C 8 E 12 确保工号在第一列 由于范围的改变 籍贯位于该范围的第3列 因此第三个参数为3 根据工号在 C 8 F 12中查找出生年月 但此时返回的是时间序列值 即格式没有带过来 近似匹配查找通常情况下用于累进数值的查找 此时第四个参数省略 或为true 或为非0数值 EXCEL中0等同FALSE 非零值均视为TRUE 试着输入5000 你会发现税率为20 应是15 由条件指定 虽然对最后计算结果没有影响 以下公式可以解决这个问题 由于EXCEL中汉字字符是按拼音排序的 因此汉字字符比较时的大小与拼音同序 一般情况下 左边的表是根据汉字拼音首字母排列 并取各字母的临界点汉字字符 该字母的 最小汉字 通过将 家 与表中字符比较后 找到比 家 小的最大值 夻 返回J 这个例子一般用于姓名的缩写 而上面的公式只找第一个字符 因此需要修改 这个公式取得前三个汉字字符的拼音字母 使用MID依次取出字符后由Vlookup查询到字母 最后 40 50 60 是两行三列 包含六个元素的二维数组常量 二维数组常量需要行列等长 可以先将数据输入单元格区域 然后在其它单元格输入 用鼠标选定该区域后按F9 显示的就是数组常量 然后复制到公式中 其实将以前使用的vlookup公式中第三个参数 区域不要太大 选定按F9 再回车 可以看到公式正常运作 此时已使用了数组常量 公式中的 拼音 就是一个定义的名称 可以自 插入 名称 定义 中查看 以 表一 为例 要按 姓名 查询 工号 很容易 但反过来按 工号 查找 姓名 直接使用vlookup就不行了 但由chenjun版主提供的使用vlookup的解法我觉得很有参考价值 介绍这个用法的目的主要是学会将两个一维数组合并为一个二维数组 可以看到 在该内存数组中 工号 位于 姓名 的左侧 Vlookup在该内存数组中查找 注意 1 0 是一行二列 横向 常量数组 后面的两个区域是多行一列 纵向 即两个数组的方向不同 这样才会生成两列多行数组 处处理理查查找找错错误误 返返回回 vlookup使用中一般会产生以下错误 VALUE 和 REF 是由于col index num设置不当造成的 虽然可以使用ISERROR剔除 但建议不要这么做 因为这类错误需要纠正 有时col index num参数使用column 函数生成 当设置不当造成的错误用ISERROR剔除时 会与 N A错误混淆 不利于公式查错 N A是常见的错误信息 需要针对不同情况处理 精确匹配查找时出现 是由于未找到完全匹配的值 近似匹配查找时出现 是由于查找值小于数据区的最小值 如果vlookup的参数产生错误 vlooukp也返回相同错误 那不在本讨论范围内 精确匹配查找时出现 N A错误 首先检查引用范围是否正确 查询值是否在数据区的第一列 当确定引用正确 不应出现 N A 即应该查找到值 时 检查是否存在空格或其它字符 格式是否一致 表表一一 张三丰100 对于空格或其他不可见字符 可以使用len函数测试文本长度来确定 或使用code测试其ASCII码 李四军 200 B18单元格长度 4 B18第四个字符码 32 数 据 含 空 格 等 张三丰 N A 该错误由于查询字符 张三丰 后含空格 可能看不出 但在精确匹配查找时造成不完全匹配 100 对于lookup value中的空格 将其中的空格替换掉 或使用TRIM去除空格后查找 李四军 N A 该错误是由于查询表表四中 李四军 后含空格 在精确匹配查找时不完全匹配 200 对于Table array中的空格 也可以使用trim 这个公式中的Table array已不是直接引用单元格区域 而是引用对 B 87 C 88运算trim后的内存数组 因此 这是一个数组公式 需要按Ctrl Shift Enter输入 如果数据区比较大 数组公式会很慢 不如直接使用替换将数据区的空格替换后使用普通公式 李四军 N A 该错误由于查询字符 李四军 后含不可见字符 非空格 使用trim clean 替换空格都不能去除 由网页复制 其他程序转出的表格 经常会出现这类字符 CODE可以测试其并非空格 对于这类不可见字符 可以先复制该字符 然后替换该字符为空白 表表二二 100A 查找内容格式不符 也会造成 N A错误 200B 可以使用鼠标选定几个单元格 看状态栏的合计 有 则为数值 无 则为文本 数 据 格 式 不 一 致 100 N A 这两个错误都是由于格式不一致造成 一个是按数值查文本 一个是按文本查数值 200 N A 对于此类错误 可以修改查找值 数据区的格式 使之统一 也可以在公式中处理 A 这个公式将数值100用 连接空字符串 强制转为文本 B 这个公式将文本200用 1运算 强制转为数值 要注意的是采用修改格式的方法 仅仅将单元格格式更改还不行 例如对于包含数值的常规单元格将格式改为文本后 单元格中的值仍是数值形式 需要激活 双击 才会真正转为文本 单元格很多时 采用逐个激活的方法肯定会累死 可以采用分列或选择性粘贴的方法 当某列数据需要全部转换时 采用分列是个好办法 它可以将文本转为数值 也可将数值转为文本 分列位于菜单栏 数据 分列 由文本转数值也可以 复制一空白单元格 选定需转换的数据区 选择性粘贴 加 当确定应该出现 N A 即查找值不存在 时 如果不要显示错误 可以使用条件格式或直接在公式中处理 表表三三 A100 注意使用条件格式处理后单元格内的值仍是 N A 其他单元格引用该单元格也会返回 N A错误 C200 当C55选B时 显然应该返回错误 数据区没有B 处理错误 B N A 这个单元格使用条件格式处理 条件格式公式 isna D55 并设定条件字体颜色与底色相同 使用ISNA测试vlookup函数是否返回 N A 如vlookup函数返回 N A 则ISNA函数返回TRUE 再使用IF函数判断 即可去除错误 精确查找时 也可以直接判断查找值是否存在来去除 N A错误 如此例使用COUNTIF测试数据区是否有查找值 近似匹配查找时出现 N A错误 近似匹配查找时 除了前述的格式等原因 查找值小于数据区的最小值将返回 N A 对于此类错误 只要数据区设计合理就可避免 建立可能的最小值 当然也可以使用ISNA去除 VALUE 和 REF 是由于col index num设置不当造成的 虽然可以使用ISERROR剔除 但建议不要这么做 因为这类错误需要纠正 有时col index num参数使用column 函数生成 当设置不当造成的错误用ISERROR剔除时 会与 N A错误混淆 不利于公式查错 当确定引用正确 不应出现 N A 即应该查找到值 时 检查是否存在空格或其它字符 格式是否一致 对于空格或其他不可见字符 可以使用len函数测试文本长度来确定 或使用code测试其ASCII码 该错误由于查询字符 张三丰 后含空格