VLOOKUP-2.xls

1、 10,20,30;40,50,60，是两行三列、包含六个元素的二维数组常量；二维数组常量需要行列等长 如果你觉得输数组常量麻烦（要区分【,】、【;】，文本要加【“】）， 可以先将数据输入单元格区域，然后在其它单元格输入【=】，用鼠标选定该区域后按F9，显示的就是数组常量，然后复制到公式中 其实将以前使用的vlookup公式中第三个参数（区域不要太大）选定按F9，再回车，可以看到公式正常运作；此时已使用了数组常量 以下举例中的公式并不是数组公式，输入时不用Ctrl+Shift+Enter一齐按 搬一个由chenjun版主提供的计算个人所得税的例子： 应应税税所所得得税税率率速速算算扣扣除除数数

2、 税税款款 650020375925 再搬一个前面的例子 汉汉字字字字符符拼拼音音首首字字母母 海H是否被公式中的数组常量吓一跳？不是一个个输的，按F9得到的； 汉汉字字字字符符拼拼音音首首字字母母 螺丝扣LSK对于此类固定的数组常量可以将其定义一个名称，公式就简洁多了，还不占用单元格 公式中的【拼音】就是一个定义的名称，可以自【插入】【名称】【定义】中查看 从从右右向向左左查查询询 我们知道，vlookup查找的列必须位于查找区域的最左列，有时会需要按右面的查找左边的值 以【表一】为例，要按【姓名】查询【工号】很容易；但反过来按【工号】查找【姓名】直接使用vlookup就不行了 此时可以使用

3、的公式很多，如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是一行二列（横向）常

4、量数组，后面的两个区域是多行一列（纵向），即两个数组的方向不同，这样才会生成两列多行数组 张三丰 李四光 王麻子 赵六儿 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 【表一】是定

5、义的单元格区域$B$9:$F$12的名称，在名称框（编辑栏左边）可以选定；也可在插入名称定义中查看修改 注意范围已改为$C$8:$E$12，确保工号在第一列；由于范围的改变，籍贯位于该范围的第3列，因此第三个参数为3 根据工号在$C$8:$F$12中查找出生年月，但此时返回的是时间序列值（即格式没有带过来） 近似匹配查找通常情况下用于累进数值的查找。此时第四个参数省略，或为true，或为非0数值。（EXCEL中0等同FALSE，非零值均视为TRUE） 试着输入5000，你会发现税率为20%，应是15%（由条件指定），虽然对最后计算结果没有影响。以下公式可以解决这个问题： 由于EXCEL中汉字字

6、符是按拼音排序的，因此汉字字符比较时的大小与拼音同序（一般情况下） 左边的表是根据汉字拼音首字母排列，并取各字母的临界点汉字字符（该字母的“最小汉字”） 通过将“家”与表中字符比较后，找到比“家”小的最大值“夻”，返回J 这个例子一般用于姓名的缩写，而上面的公式只找第一个字符，因此需要修改 这个公式取得前三个汉字字符的拼音字母。使用MID依次取出字符后由Vlookup查询到字母，最后“40,50,60，是两行三列、包含六个元素的二维数组常量；二维数组常量需要行列等长 可以先将数据输入单元格区域，然后在其它单元格输入【=】，用鼠标选定该区域后按F9，显示的就是数组常量，然后复制到公式中 其实将以

7、前使用的vlookup公式中第三个参数（区域不要太大）选定按F9，再回车，可以看到公式正常运作；此时已使用了数组常量 公式中的【拼音】就是一个定义的名称，可以自【插入】【名称】【定义】中查看 以【表一】为例，要按【姓名】查询【工号】很容易；但反过来按【工号】查找【姓名】直接使用vlookup就不行了 但由chenjun版主提供的使用vlookup的解法我觉得很有参考价值，介绍这个用法的目的主要是学会将两个一维数组合并为一个二维数组 可以看到，在该内存数组中，【工号】位于【姓名】的左侧，Vlookup在该内存数组中查找 注意1,0是一行二列（横向）常量数组，后面的两个区域是多行一列（纵向），即两

8、个数组的方向不同，这样才会生成两列多行数组 处处理理查查找找错错误误 返返回回 vlookup使用中一般会产生以下错误： #VALUE!和 #REF!是由于col_index_num设置不当造成的，虽然可以使用ISERROR剔除，但建议不要这么做，因为这类错误需要纠正。 有时col_index_num参数使用column()函数生成，当设置不当造成的错误用ISERROR剔除时，会与#N/A错误混淆，不利于公式查错。 #N/A是常见的错误信息，需要针对不同情况处理： 精确匹配查找时出现，是由于未找到完全匹配的值。 近似匹配查找时出现，是由于查找值小于数据区的最小值。 如果vlookup的参数产生

9、错误，vlooukp也返回相同错误，那不在本讨论范围内。 精确匹配查找时出现#N/A错误 首先检查引用范围是否正确，查询值是否在数据区的第一列。 当确定引用正确、不应出现#N/A（即应该查找到值）时，检查是否存在空格或其它字符、格式是否一致 表表一一 张三丰100 对于空格或其他不可见字符，可以使用len函数测试文本长度来确定，或使用code测试其ASCII码 李四军 200 B18单元格长度 4 B18第四个字符码 32 数 据 含 空 格 等 张三丰 #N/A 该错误由于查询字符“张三丰 ”后含空格，可能看不出，但在精确匹配查找时造成不完全匹配 100 对于lookup_value中的空格

10、，将其中的空格替换掉，或使用TRIM去除空格后查找 李四军#N/A 该错误是由于查询表表四中“李四军”后含空格，在精确匹配查找时不完全匹配 200 对于Table_array中的空格，也可以使用trim。 这个公式中的Table_array已不是直接引用单元格区域，而是引用对$B$87:$C$88运算trim后的内存数组 因此，这是一个数组公式，需要按Ctrl+Shift+Enter输入 如果数据区比较大，数组公式会很慢；不如直接使用替换将数据区的空格替换后使用普通公式。 李四军 #N/A 该错误由于查询字符“李四军”后含不可见字符（非空格），使用trim、clean、替换空格都不能去除。 由

11、网页复制、其他程序转出的表格，经常会出现这类字符，CODE可以测试其并非空格 对于这类不可见字符，可以先复制该字符，然后替换该字符为空白 表表二二 100A 查找内容格式不符，也会造成N/A错误 200B 可以使用鼠标选定几个单元格，看状态栏的合计。有，则为数值，无，则为文本。 数 据 格 式 不 一 致 100#N/A 这两个错误都是由于格式不一致造成，一个是按数值查文本，一个是按文本查数值 200#N/A 对于此类错误，可以修改查找值、数据区的格式，使之统一；也可以在公式中处理 A 这个公式将数值100用&连接空字符串“，强制转为文本 B 这个公式将文本200用*1运算，强制转为数值 要注

12、意的是采用修改格式的方法，仅仅将单元格格式更改还不行： 例如对于包含数值的常规单元格将格式改为文本后，单元格中的值仍是数值形式,需要激活（双击）才会真正转为文本。 单元格很多时，采用逐个激活的方法肯定会累死。可以采用分列或选择性粘贴的方法： 当某列数据需要全部转换时，采用分列是个好办法。它可以将文本转为数值，也可将数值转为文本。 分列位于菜单栏数据分列 由文本转数值也可以 复制一空白单元格，选定需转换的数据区，选择性粘贴加 当确定应该出现#N/A（即查找值不存在）时，如果不要显示错误，可以使用条件格式或直接在公式中处理 表表三三 A100 注意使用条件格式处理后单元格内的值仍是#N/A，其他单

13、元格引用该单元格也会返回#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。 对于此类错误

14、，只要数据区设计合理就可避免（建立可能的最小值）,当然也可以使用ISNA去除。 #VALUE!和 #REF!是由于col_index_num设置不当造成的，虽然可以使用ISERROR剔除，但建议不要这么做，因为这类错误需要纠正。 有时col_index_num参数使用column()函数生成，当设置不当造成的错误用ISERROR剔除时，会与#N/A错误混淆，不利于公式查错。 当确定引用正确、不应出现#N/A（即应该查找到值）时，检查是否存在空格或其它字符、格式是否一致 对于空格或其他不可见字符，可以使用len函数测试文本长度来确定，或使用code测试其ASCII码 该错误由于查询字符“张三丰

15、”后含空格，可能看不出，但在精确匹配查找时造成不完全匹配 对于lookup_value中的空格，将其中的空格替换掉，或使用TRIM去除空格后查找 该错误是由于查询表表四中“李四军”后含空格，在精确匹配查找时不完全匹配 这个公式中的Table_array已不是直接引用单元格区域，而是引用对$B$87:$C$88运算trim后的内存数组 如果数据区比较大，数组公式会很慢；不如直接使用替换将数据区的空格替换后使用普通公式。 该错误由于查询字符“李四军”后含不可见字符（非空格），使用trim、clean、替换空格都不能去除。 由网页复制、其他程序转出的表格，经常会出现这类字符，CODE可以测试其并非空

16、格 可以使用鼠标选定几个单元格，看状态栏的合计。有，则为数值，无，则为文本。 这两个错误都是由于格式不一致造成，一个是按数值查文本，一个是按文本查数值 对于此类错误，可以修改查找值、数据区的格式，使之统一；也可以在公式中处理 例如对于包含数值的常规单元格将格式改为文本后，单元格中的值仍是数值形式,需要激活（双击）才会真正转为文本。 单元格很多时，采用逐个激活的方法肯定会累死。可以采用分列或选择性粘贴的方法： 当某列数据需要全部转换时，采用分列是个好办法。它可以将文本转为数值，也可将数值转为文本。 当确定应该出现#N/A（即查找值不存在）时，如果不要显示错误，可以使用条件格式或直接在公式中处理

17、注意使用条件格式处理后单元格内的值仍是#N/A，其他单元格引用该单元格也会返回#N/A错误。 这个单元格使用条件格式处理，条件格式公式isna(D55)，并设定条件字体颜色与底色相同。 使用ISNA测试vlookup函数是否返回#N/A，如vlookup函数返回#N/A，则ISNA函数返回TRUE。再使用IF函数判断，即可去除错误。 精确查找时，也可以直接判断查找值是否存在来去除#N/A错误，如此例使用COUNTIF测试数据区是否有查找值。 tang1971hi 等级：铁杆会员一 表五的辅助列公式应改成这个，否则可能出错。 =COUNTIF($C$141:C141,$C$148) shangyu 等级：版主 只要最上一个公式的上一格空，应该不会出错。 使用CUNTIF当然可以，但这是一个易失函数，数据在几千行时速度将会有明显区别。 -