每天清晨,与您相约 在G3单元格输入公式: =VLOOKUP(F3,$B$2:$D$15,3,0) 确定,即可查找到“林三”的得分; 公式向下填充,即可查找到“陆七”的得分。 如本示例中,查阅值是F3单元格“林三”,我们要在“姓名”一列中查找“林 本示例公式解释: 注意:查找区域的绝对引用: 在公式中,第二个参数“查找区域”,使用的是绝对引用$B$2:$D$15。 绝对引用的作用是:公式填充到其他行列时,该区域不变。 本示例,查找完“林三”的得分,公式向下填充,再去查找“陆七”得分,查找区域始终不应改变,应该是包含所有姓名与得分的B2:D15区域,所以,该区域绝对引用。 比如,以下数据: 要求查找多人多条信息,这种情况,就需要灵活改动VLOOKUP函数参数,实现用一个公式返回多行多列数据。 公式实现过程如下: 我们可以看出,几行几列数据,是用一个公式完成的,该公式是: =VLOOKUP($B18,$C$2:$G$15,COLUMN(B1),0) 公式向下,向右填充,记得到所有要求查找的返回值。 如下图: 采购数量不同,所得折扣也不同,如右侧的折扣表。 公式“=VLOOKUP(B2,$E$3:$F$6,2)”,省略了第四个参数,即查找方式,省略就代表把第四个参数设置成TRUE或1,即是近似查找。 近似查找返回值是:比查阅值小且最接近的查询区域首列中的区间值所对应的返回值。 本示例中
区间查找有一最最重要的注意事项: 查找区域的区间值必须是从小到大排列,否则查找不到正确结果。 本示例,区间值0、100、200、300是从小到大依次排列的。 如下图: 公式“=VLOOKUP(B2,{0,"不合格";60,"合格";70,"良好";85,"优秀"},2)”,省略了第四个参数,即是近似查找。 其中,{0,"不合格";60,"合格";70,"良好";85,"优秀"}是下图数组的变相写法: 等级查找是区间查找的特殊方式,也可以写成区间查找的公式: 如下图: 查找G型号系列产品的销量,可以把查找值用通配符表示。此种方法可以查找字符串中含有某个关键值的对应返回值。 如下图: 查找仓库二键盘的销量,查找条件必须符合仓库是“仓库二”、商品是“键盘”两个条件。 公式: =VLOOKUP(E2&F2,IF({1,0},A2:A13&B2:B13,C2:C13),2,0) 按“CTRL+SHIFT+ENTER”键确认,即得结果。 第一个参数: E2&F2,用文本连接符,将E2单元格“仓库二”与F2单元格“键盘”,连接在一起,形成新的查询条件:仓库二键盘。 第二个参数: IF({1,0},A2:A13&B2:B13,C2:C13),生成一个新的查询区域: 第三个参数: 2,新的查找区域里,返回值在第二列。 第四个参数: 0,精确查找。 VLOOKUP函数要求查询值必须位于查询区域的首列。比如,下图中的数据: 原数据区,“部门”位于“姓名”的左侧,而要求按照姓名去查询部门,那直接用VLOOKUP函数进行查找,是查不到结果的。 我们需要构建一个新的查询数据区,将“姓名”置于“部门”的左侧。这种新的查询数据区,可以通过IF和CHOOSE两个函数来实现。 在E2输入公式: =VLOOKUP(D2,IF({1,0},B1:B10,A1:A10),2,0), 结果如下图: 其中: IF({1,0},B1:B10,A1:A10),构造出姓名在前,部门在后的新的查询区域,如下图: 也可在E2输入公式: =VLOOKUP(D2,CHOOSE({1,2},B1:B10,A1:A10),2,0), 结果如下图: 其中: CHOOSE({1,2},B1:B10,A1:A10),构造出姓名在前,部门在后的新的查询区域,如下图: 如下图,将表1中的消费记录,按名称,引用到表2中: 其实,跨工作表的引用,和同一工作表数据引用方法是一样的,只不过多了一步点击工作表的名称,即选择工作表而已。 引用过程如下: 切记: 在选了引用位置“sheet1!A1:B13”以后,不要再去点回sheet2,除非公式后面需要sheet2中的数据。在公式编辑过程中,鼠标的点击位置会随时记录。 有关文章公式的详细解释,可点击相应题目查看: Excel205 | VLOOKUP函数使用方法之进阶篇——多条件查找、逆向查找 Excel204 | VLOOKUP函数使用方法之提升篇——区间查找、等级评定、模糊查找 Excel203 | VLOOKUP函数使用方法之小成篇——多行多列查找 |
|
来自: bitterfleabane > 《待分类》