资源描述
使用 VLOOKUP、MATCH禾口 INDEX 执行动态搜索常用公式可在软件中按F1协助中查找。VLOOKUP网友简释：从另一个表中查找对应值并提取数据自动填写ABCAB4三毛男106王小虎男77彭湃男108狗蛋男89小雪女22在表二的B1列里输入公式：=VLOOKUP（A1,表一 !A1 : B9,2|false）翻译：=我找（王小丫，在表一里找，这个范围，提取其后第N列的值（其自身算1），准确找）注意逗号的有无，冒号的位置。可将表一打开，有时可点表一的单元格代替输入 一些数值.这样，在B1里就显示王小丫的性别了公式的内容是这样的，vlookup是excel的查找函数，括号里首先是你要查找的值， 这里我们引用的是A1中的内容，也就是王小丫，然后用 ，“ ”隔开，第二个参数 是查找范围，也就是我们想要在哪个范围内查找，当然我们这里用的是表一里的 A1到B9，为什么B列也要包含一会再说，标注好查找范围之后，也用：隔开, 在接下去是返回值的列号，也就是说，查找到你要查的人名之后，这里还以王小 丫为例，在表一的A1到B9中找到王小丫之后，我们需要返回的值，是王小丫 所在列的后面第几列，因为我们要显示的是性别，那在我们查找的范围里，姓名 是第一列，性别是第二列，所以我们要返回的是第二列的内容， 下一个参数就设 置成2。这也是查找范围为什么要把第二列也包含在内的原因。最后一个参数， 是模糊查找功能，一般就设置为false，我们只查找完全匹配的内容，就是说， 任命必须完全一样，才有效。不知道这样说可不能够，需要注意的是，表一的内容，你要查找的列必须以 升序排列。这是必须注意的，如果你要连年龄也一起显示出来， 那就将查找范围 扩大为A1到C9,返回的列号变成3官方正文:本文是由 Microsoft MVP （最有价值专家）Ashish Mathur编写的。相关详细信息，请访问 Microsoft MVP 网站。在本文中，我将介绍您能够在Excel中用于 动态搜索”的工具。我所 说的动态搜索”是指在行或列中搜索特定数据，然后在另一单元格中 返回值这个功能。我使用的工具为函数VLOOKUP、MATCH和INDEX。使用 VLOOKUPVLOOKUP在表格的最左侧列中搜索值，然后从您在表格中指定的列 在同一行中返回值。（VLOOKUP中的V表示垂直。）为了说明VLOOKUP,我们来看一项任务：使用区域 B3: C11 下 图所示 一中的数据为区域 E3: E11中列出的名称在区域 F3： F11 中返回每小时费率。请注意，E3： E11中名称的顺序与 B3： B11不 同。BI：EF1 CF1naf 23*itw 1-VLOOKUPi.2 fAtSf)4hmi125S12.55510 161155 Illr5S7g鹑nv*108125S1LS59恫苓16.55125S10EK11351011110 丄15$12L13这里采用的简单逻辑是：在区域 B3: B11中搜索区域E3: E11中列出的名称。为区域 C3: C11中列出的名称返回每小时费率，然后 将费率放在区域F3： F11中。实现此目的的公式为:二VLOOKUP(E3,$B$3 : $C$11,2,FALSE)若要理解此公式，请考虑 VLOOKUP的语法:VLOOKUP(lookup_value,table_array,col_i ndex_ nu m,ran ge_lookup)现在，我们将详细说明此特殊示例的语法中的参数：lookup_value引用包含要查找的值的单元格。所以，对于单元格 F3， lookup_value 是 E3。table_array在这里，table_array引用包含要查找的数据和要返回的数据的区域。在我们的示例中，区域B3: C11是将从中返回每小时费率的列表。col_index_num 这引用区域$B$3: $C$11中包含要返回的数据的列号。在我们的示例中，每小时费率位于第2列中。range_lookup此值指定希望 VLOOKUP查找精确匹配值还是 近似匹配值。如果为TRUE或省略，则能够返回近似或精确匹配值。 为使此参数正常工作，table_array第一列中的值必须按升序放置。 如果为FALSE如本示例所示，VLOOKUP将只查找精确匹配值。 在这种情况下，不需要对table_array第一列中的值实行排序。插入或删除数据时可能会导致的错误现在，我们来看看公式中的复杂情况。使用ISERROR处理缺少的名称如果从单元格B9中删除了名称廖怡苓，则 F5中的结果将为#N/A，因为在单元格 F5中，公式在区域 B3： C11中找不到名称。 要隐藏错误值，公式是：=IF(ISERROR(VLOOKUP(E3,$B$3 $C$11,2,FALSE),VLOOKUP(E3,$B$3 $C$11,2,FALSE)这里的基础逻辑是：如果公式返回错误值，则在该单元格中显示空白； 否则，显示每小时费率。使用MATCH处理插入的列正如您在上述 VLOOKUP公式中所看到的，col_index_name输入为2,所以公式不是动态的。也就是说，如果在此区域的B列和C列之间插入一个空列，则公式将返回0，因为第2列不再包含任何数据。所以，现在的任务是使col_index_name足以动态地确定包含标 题每小时费率的单元格的列号。要实现此目的，我们使用MATCH函 数。MATCH函数的语法为：MATCH(lookup_value,lookup_array,match_type)现在，我们将详细说明此特殊示例的语法中的参数：Lookup_value包含每小时费率的单元格的引用。在这种情况下，我们能够输入$F$1或将lookup_value指定为每小时费率”。 lookup_array这是您期望每小时费率所在的列一这是动态 搜索。值得注意的是，对于lookup_array应只存有一行。也就是说， 不输入区域$B$1: $D$2 ;而应输入 $B$1： $D$1。match_type输入0查找精确匹配值，或输入 1查找近似匹配值。在我们的示例中，我们输入0MATCH公式现在是： MATCH($F$1,$B$1: $D$1,0) 公式结果是2。现在，如果在区域 B3: C11中插入一列，该公式将得出 3。单元格 F3中的VLOOKUP公式现在能够输入为：二VLOOKUP（E3,$B$3 : $D$11,MATCH（$F$1,$B$1:$D$1,0）,FALSE）提示 为防止错误值显示，您也能够采用ISERROR函数，如上所述。 使用INDEX增强灵活性当VLOOKUP函数在表格的最左侧列中搜索值，然后从您在表格中 指定的列在同一行中返回值时，INDEX函数更加动态。它能够在表 格中的任何列中搜索值，并在同一行的另一列中返回值。例如，如果 每小时费率存有于区域 A3: A11中，而不是C3： C11，则我们的 原始VLOOKUP公式将失败，因为要搜索的值（名称）不在表的最 左侧列中（A3: B11区域内）。INDEX提供的解决方法是：1. 在区域D3:D11中的名称所出现的行中搜索（区域A3 : B11）2. 在每小时费率所出现的列中搜索。3. 返回行和列相交部分的值。INDEX函数的语法为:INDEX(array,row_ nu m,colu mn_num)该命令的语法能够如下解释:Array这与 VLOOKUP函数中的table_array对应。在以下图 片所示的示例中，array为A1 : B11。Row_num 在区域 B1: B11中引用区域 D3: D11中显示的 名称的行号。如前所述，使用 MATCH函数确定行号。Column_num引用区域A1 : B11中包含所需数据的列号。同 样，使用MATCH函数确定列号。AB一_|tIFGHI1 HKlaitszj4125512J5Ss1016h556ll.SS林壬帕ll.SS716.5S1081Z551155*1&.55山1235idwuw1155所以，INDEX执行此基本操作：在区域A1 : B11中搜索特定行和列 相交部分的值。如上面的图片所示，实现此目的公式是:=INDEX($A$1 : $B$11,MATCH(D3,$B$1$B$11,0),MATCH($E$1,$1: $1,0)
展开阅读全文