vlookup应用实例

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)Lookup_value为需要在Table_array第一列中查找的数值。可以为数值、引用或文本字符串。需要注意的是类型必须与table_array第一列的类型一致。查找文本时，文本不区分大小写；可以使用通配符“*”、“?”。Table_array为需要在其中查找数据的数据表。可以使用对区域或区域名称的引用、常数数组、计算后的内存数组。对区域引用时，可以引用整列,excel会自动判断使用区域。该参数的第一列必须包含查找的内容，其它列包含需返回的内容；返回内容的列序号由下个参数指定。Col_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判断去除错误信息。有两个并列条件（不同字段）的查询vlookup应用实例精确匹配查询之基本运用精确查找是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）这是一张个所税税率表，对于不确定的收入，可以利用近似匹配查找税率及速扣数表二级数应税所得超过且不超过税率(%)速算扣除数15005025002000102532000500015125450002000020375520000400002513756400006000030337576000080000356375880000100000401037591000004515375应税所得税率速算扣除数税款5360203756975360在表中未列出，因此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查询到字母，最后“&”连接文本囕R仨S使用该例关键是【表三】的建立；同理，如果要查找汉字的完整拼音，可以建立一个由各种拼音组成的类似列表。他T但这个方法并不一直正确，由于多音字及文字大小排序与其拼音不完全一致的原因，有时返回的字母可能不是你要的屲W夕X丫Y帀Z区别查询结果的空白与0值表四姓名内容张三丰0vlookup对于查找到的0值会返回0；张三丰0李四光0对于空白单元格（非空字符串）也会返回0；李四光王麻子对空字符串仍返回空字符串。王麻子赵六儿赵六儿张三丰0如要对0值和空白单元格区别，可以直接判断是否＝，或使用len函数测试返回值的长度；李四光如不需要0值，可直接判断＝0，或在【工具】—【选项】中将【零值】去掉。依次序返回同一查询结果的多列内容按姓名在【表一】中查询全部内容姓名工号性别籍贯出生年月李四光KT002女天津1980年9月王麻子KT003男河北1975年3月当查询某个结果的多列内容，并要依次取出，可使用column()函数作为index_num参数column()函数返回公式所在列的列号，当公式复制时，会随单元格的移动相应改变lookup_value参数使用混合引用，如$B96。即对列标绝对引用（列标前加$）、行号相对引用(行号前没有$)；复制时引用单元格会改变。上面公式只要输入C96中的公式，然后向右、向下复制就可以。返回同一查询结果的多列（不按原次序）的内容按姓名及不同项目（不依次序）在【表一】中查询姓名性别籍贯出生年月工号先由match函数确定项目内容在B8:F8区域的位置，将返回值作为vlookup的第三个参数。赵六儿女河南1985年12月KT004注意公式中Match函数的第一个参数使用行号绝对引用、列标相对引用；王麻子男河北1975年3月KT003Vlookup函数的第一个参数使用行号相对引用、列标绝对引用；按部分内容模糊查询按姓在【表一】中查询姓姓名工号性别籍贯出生年月王王麻子KT003男河北1975年3月vlookup支持通配符【*】和【?】，因此当查找内容不全时，可以使用通配符；如例公式中使用【&】将查找文本与通配符连接后查找，注意通配符连接位置注意查找中，有多个符合条件的结果时，只会返回第一个在同一单元格按不同字段查询在同一单元格按姓名或工号在【表一】中查询姓名或工号性别籍贯出生年月KT002女天津1980年9月使用countif判断，如果在姓名区中有C131单元格的内容，按姓名查找；否则按工号查找李四光女天津1980年9月或者使用isna判断，如果按姓名查找出错，就按工号查找；王麻子男河北1975年3月或者使用一个vlookup，第二、三个参数由判断结果决定（查找范围、列号）。返回多个符合条件的查询结果表五查找的结果不止一个，并要全部返回姓名工号vlookup对于包含多个结果的查找只会返回第一个结果（数据区由上向下第一个）1张三丰KT001但有时需要返回所有的结果。如例，需要按姓名查找同名的各个工号1李四光KT002这种情况一般使用数组公式解决。但数据量大时，使用数组公式会由于计算量大，速度会很慢1李四光KT003vlookup没有直接的解决方法，需要添加辅助列（注意辅助列需添加在左侧，因为要根据辅助列的内容查找）2张三丰KT0043张三丰KT005表五左侧白色区域为辅助列，B141公式为=($C$148=C141)+B140；等同=IF($C$148=C141,1,0)+B140姓名工号该公式结果为：如姓名列内容与查找姓名相同，则按顺序显示1、2、3……，从而将相同姓名区分张三丰KT001然后，在查询中查找数值1、2、3……即可查找到对应查找内容的多个结果(注意查找区域由辅助列开始）KT004为便于复制，vlookup第一个参数数值1、2、3……使用函数row()返回的行号产生KT005当然，辅助列中的公式可以改为其它，只要达到区别相同内容的目的有两个并列条件（不同字段）的查询表六：查找某业务员对某客户的销售额业务员客户销售额对于此类根据两个或多个并列条件查找某个结果的问题，使用vlookup也需要添加辅助列张三丰商场A张三丰商场A100B158公式=C158&D158，即将被查找的内容合并作为一列，并以此作为查找列。张三丰超市B张三丰超市B200李四光店铺C李四光店铺C300有些数据需要在两个数据间插入一特殊符号(查询数据中没有的任意符号)以示区别李四光超市B李四光超市B400例如两个条件分别是：张三丰店铺C张三丰店铺C500李四光商场A李四光商场A600此时就需要用其他符号将其区别：业务员客户销售额李四光超市B400使用【&】将要查询的两个条件合并后作为查询值，在区域$B$158:$E$163中查询第4列内容使用数组常量的查询一般，使用vlookup前需要有一个对应的表格供查找，即作为vlookup的Table_array参数的表格区域；但有时这个表格可能会显得累赘；如果数据不是太多而且比较固定，可以考虑使用数组常量代替单元格区域的方法；所谓数组常量就是直接输入的一组数组，以“{}”开始结束、同一行的数值用逗号“，”分隔、同一列的数值用分号“；”分隔如{10,20,30}，是一行三列（横向）、包含三个元素的一维数组