内容
在我们关于函数的教程的第二部分 VPR (VLOOKUP) 在 Excel 中，我们将分析几个例子，这将帮助您指导所有的权力 VPR 解决最雄心勃勃的 Excel 任务。 这些示例假定您已经对该功能的工作原理有基本的了解。 如果没有，您可能有兴趣从本教程的第一部分开始，它解释了语法和基本用法。 VPR. 好吧，让我们开始吧。
按多个条件在 Excel 中搜索
功能 VPR in Excel 是一个非常强大的工具，用于在数据库中执行特定值的搜索。 但是，有一个很大的限制——它的语法只允许你搜索一个值。 如果要按多个条件搜索怎么办？ 您将在下面找到解决方案。
示例 1：按 2 个不同的条件搜索
假设我们有一个订单列表，我们想找到 商品数量 （数量），基于两个标准—— 客户名称 （客户） 产品名称 （产品）。 由于每个买家都订购了几种类型的商品，因此事情变得复杂，如下表所示：
常规功能 VPR 在这种情况下将不起作用，因为它将返回它找到的与给定查找值匹配的第一个值。 例如，如果您想知道一个项目的数量 糖果'由买方订购 杰里米·希尔（Jeremy Hill），写出如下公式：
=VLOOKUP(B1,$A$5:$C$14,3,FALSE)
=ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)
- 这个公式将返回结果 15对应产品 苹果，因为它是第一个匹配的值。
有一个简单的解决方法——创建一个额外的列来组合所有所需的标准。 在我们的示例中，这些是列 客户名称 （客户） 产品名称 （产品）。 不要忘记合并的列必须始终是搜索范围中最左边的列，因为函数是最左边的列 VPR 查找值时查找。
因此，您向表中添加一个辅助列，并将以下公式复制到其所有单元格中： =B2&C2. 如果希望字符串更具可读性，可以用空格分隔组合值： =B2&» «&C2. 之后，您可以使用以下公式：
=VLOOKUP("Jeremy Hill Sweets",$A$7:$D$18,4,FALSE)
=ВПР("Jeremy Hill Sweets";$A$7:$D$18;4;ЛОЖЬ)
or
=VLOOKUP(B1,$A$7:$D$18,4,FALSE)
=ВПР(B1;$A$7:$D$18;4;ЛОЖЬ)
细胞在哪里 B1 包含参数的串联值 Lookup_Array中 (lookup_value) 和 4 - 争论 Col_index_num为 (column_number)，即包含要检索的数据的列的编号。
示例 2：VLOOKUP 通过两个标准在另一张纸上查看表格
如果您需要通过从位于另一个工作表或另一个 Excel 工作簿中的第二个表（查找表）添加数据来更新主表（主表），那么您可以直接在您插入的公式中收集所需的值进入主表。
与前面的示例一样，您需要在 Lookup 表中添加一个包含组合值的辅助列。 此列必须是搜索范围中最左侧的列。
所以公式与 VPR 可能是这样的：
=VLOOKUP(B2&" "&C2,Orders!$A&$2:$D$2,4,FALSE)
=ВПР(B2&" "&C2;Orders!$A&$2:$D$2;4;ЛОЖЬ)
此处，B 列和 C 列分别包含客户名称和产品名称，链接 订单！$A&$2:$D$2 定义要在另一个工作表中查找的表。
为了使公式更具可读性，您可以为视图范围命名，然后公式看起来会简单得多：
=VLOOKUP(B2&" "&C2,Orders,4,FALSE)
=ВПР(B2&" "&C2;Orders;4;ЛОЖЬ)
要使公式起作用，您正在查看的表格最左列中的值必须以与搜索条件完全相同的方式组合。 在上图中，我们将 u2bu2band 的值组合在一起，在它们之间放置一个空格，就像在函数的第一个参数中需要做的一样（BXNUMX&“”&CXNUMX）。
记得！ 功能 VPR 限制为 255 个字符，它不能搜索长度超过 255 个字符的值。 请记住这一点，并确保所需值的长度不超过此限制。
我同意添加辅助列不是最优雅且并非总是可以接受的解决方案。 您可以在没有帮助列的情况下执行相同的操作，但这需要一个更复杂的公式和函数组合 指数 （索引）和 MATCH （更多暴露）。
我们使用VLOOKUP提取第2、3等值
你已经知道了 VPR 只能返回一个匹配值，更准确地说，是找到的第一个。 但是，如果这个值在查看的数组中重复多次，并且您想提取其中的第 2 个或第 3 个呢？ 如果所有值怎么办？ 问题看似复杂，但解决方案是存在的！
假设表的一列包含客户的姓名（Customer Name），另一列包含他们购买的产品（Product）。 让我们尝试查找给定客户购买的第 2、第 3 和第 4 件商品。
最简单的方法是在列之前添加一个辅助列 客户名称 并用每个名称的重复数填写客户名称，例如， 约翰·杜 1, 约翰·杜 2 等等。我们将使用该函数进行编号 COUNTIF (COUNTIF)，假设客户名称在 B 列中：
=B2&COUNTIF($B$2:B2,B2)
=B2&СЧЁТЕСЛИ($B$2:B2;B2)
之后就可以使用正常功能了 VPR找到所需的订单。 例如：
- 找到最适合您的地方 2次 客户订购的物品 丹·布朗:
=VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown2";$A$2:$C$16;3;ЛОЖЬ) - 找到最适合您的地方 3次 客户订购的物品 丹·布朗:
=VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown3";$A$2:$C$16;3;ЛОЖЬ)
实际上，您可以输入一个单元格引用作为查找值而不是文本，如下图所示：
如果你只是在寻找 2-E 重复，你可以通过创建一个更复杂的公式来做到这一点，而不需要辅助列：
=IFERROR(VLOOKUP($F$2,INDIRECT("$B$"&(MATCH($F$2,Table4[Customer Name],0)+2)&":$C16"),2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;ДВССЫЛ("$B$"&(ПОИСКПОЗ($F$2;Table4[Customer Name];0)+2)&":$C16");2;ИСТИНА);"")
在这个公式中：
- $ F $ 2 – 包含买家姓名的单元格（未更改，请注意 – 链接是绝对的）；
- $B$ - 柱子 客户名称;
- Table4 - 你的桌子（这个地方也可以是一个常规范围）；
- $ C16 – 表格或范围的结束单元格。
此公式仅查找第二个匹配值。 如果您需要提取剩余的重复，请使用之前的解决方案。
如果您需要所有匹配项的列表 - 函数 VPR 这不是一个助手，因为它一次只返回一个值——周期。 但是Excel有一个功能 指数 (INDEX)，它可以轻松应对这项任务。 这样的公式看起来如何，您将在以下示例中学习。
检索所需值的所有重复
正如刚才提到的 VPR 无法从扫描范围中提取所有重复值。 为此，您需要一个稍微复杂的公式，由几个 Excel 函数组成，例如 指数 （指数）， 小 （小）和 行 （线）
例如，下面的公式在区域 B2:B2 中查找单元格 F16 中值的所有重复项，并返回 C 列中相同行的结果。
{=IFERROR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3)),"")}
{=ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3));"")}
将此数组公式输入到多个相邻单元格中，例如单元格 F4：F8如下图所示。 单元格的数量必须等于或大于搜索值的最大可能重复次数。 不要忘记点击 Ctrl + Shift + Enter正确输入数组公式。
如果您有兴趣了解它的工作原理，让我们深入了解一下公式的细节：
部分1：
IF($F$2=B2:B16,ROW(C2:C16)-1,"")
ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"")
$F$2=B2:B16 – 将单元格 F2 中的值与范围 B2:B16 中的每个值进行比较。 如果找到匹配项，则表达式 字符串(C2:C16)-1 返回对应行的编号（值 -1 允许您不包括标题行）。 如果没有匹配，函数 IF (IF) 返回一个空字符串。
函数结果 IF (IF) 会有这样一个水平数组： {1,"",3,"",5,"","","","","","",12,"","",""}
部分2：
ROW()-3
СТРОКА()-3
这里的功能 行 (LINE) 用作附加计数器。 由于公式被复制到单元格 F4:F9 中，我们减去数字 3 从函数结果获取值 1 在单元格中 F4 （第 4 行，减去 3）得到 2 在单元格中 F5 （第 5 行，减去 3）等等。
部分3：
SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))
功能 小 （小）返回 哦哦 数据数组中的最小值。 在我们的例子中，返回哪个位置（从最小）由函数确定 行 (LINE)（见第 2 部分）。 所以，对于一个细胞 F4 功能 小（{数组}，1） 回报 1次 （最小）数组元素，即 1. 对于细胞 F5 回报 2次 数组中的最小元素，即 3等等。
部分4：
INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))
功能 指数 (INDEX) 只返回数组中特定单元格的值 C2：C16. 对于细胞 F4 功能 指数($C$2:$C$16) 将返回 苹果针对 F5 功能 指数($C$2:$C$16) 将返回 糖果' 等等。
部分5：
IFERROR()
ЕСЛИОШИБКА()
最后，我们把公式放到函数里面 IFERROR (IFERROR)，因为您不太可能对错误消息感到满意 ＃在 (#N/A) 如果公式复制到的单元格数小于正在查看的范围内的重复值数。
按已知行和列搜索 XNUMXD
在 Excel 中执行 XNUMXD 搜索涉及按已知行号和列号搜索值。 换句话说，您正在提取特定行和列交叉处的单元格值。
所以，让我们转向我们的表格，用函数编写一个公式 VPR，它将找到有关 XNUMX 月份销售的柠檬成本的信息。
有几种方法可以执行 XNUMXD 搜索。 查看选项并选择最适合您的选项。
VLOOKUP 和 MATCH 函数
你可以使用一堆函数 VPR （VLOOKUP）和 更多曝光 (MATCH) 查找字段交叉处的值 产品名称 （字符串）和 月 有问题的数组的（列）：
=VLOOKUP("Lemons",$A$2:$I$9,MATCH("Mar",$A$1:$I$1,0),FALSE)
=ВПР("Lemons";$A$2:$I$9;ПОИСКПОЗ("Mar";$A$1:$I$1;0);ЛОЖЬ)
上面的公式是一个正则函数 VPR，它在单元格 A2 到 A9 中查找值“Lemons”的完全匹配。 但由于您不知道三月份的销售额在哪一列，您将无法为第三个函数参数设置列号。 VPR. 而是使用该函数 更多曝光定义此列。
MATCH("Mar",$A$1:$I$1,0)
ПОИСКПОЗ("Mar";$A$1:$I$1;0)
翻译成人类语言，这个公式意味着：
- 我们正在寻找字符“Mar”——参数 Lookup_Array中 （查找值）；
- 查看从 A1 到 I1 的单元格 – 参数 查找数组 （查找数组）；
- 返回完全匹配 - 参数 比赛类型 （比赛类型）。
运用 0 在第三个参数中，你说函数 更多曝光 查找与您正在查找的值完全匹配的第一个值。 这相当于值 FALSE (FALSE) 用于第四个参数 VPR.
这是在 Excel 中创建双向搜索公式的方法，也称为二维搜索或双向搜索。
SUMPRODUCT 函数
功能 SUMPRODUCT (SUMPRODUCT) 返回所选数组的乘积之和：
=SUMPRODUCT(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar"),$A$2:$I$9)
=СУММПРОИЗВ(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar");$A$2:$I$9)
索引和匹配函数
在下一篇文章中，我将详细解释这些函数，所以现在你可以复制这个公式：
=INDEX($A$2:$I$9,MATCH("Lemons",$A$2:$A$9,0),MATCH("Mar",$A$1:$I$1,0))
=ИНДЕКС($A$2:$I$9;ПОИСКПОЗ("Lemons";$A$2:$A$9;0);ПОИСКПОЗ("Mar";$A$1:$I$1;0))
命名范围和交集运算符
如果您不熟悉所有这些复杂的 Excel 公式，您可能会喜欢这种直观且令人难忘的方式：
- 选择表，打开选项卡 公式 （公式）并单击 从选择创建 （从选择中创建）。
- 检查框 第一排 （在上面的行）和 左栏 （在左侧的列中）。 Microsoft Excel 将根据电子表格顶行和左列中的值为范围分配名称。 现在您可以直接使用这些名称进行搜索，而无需创建公式。
- 在任何空单元格中，写 =row_name 列名，例如像这样：
=柠檬三月
… 或相反亦然：
=马尔柠檬
请记住，行名和列名必须用空格分隔，在这种情况下，它的工作方式类似于交集运算符。
当您输入名称时，Microsoft Excel 将显示带有匹配名称列表的工具提示，就像您输入公式时一样。
- 媒体 输入 并检查结果
一般来说，无论您选择以上哪种方法，二维搜索的结果都是一样的：
在一个公式中使用多个 VLOOKUP
碰巧主表和查找表没有一个共同的列，这会阻止您使用通常的功能 VPR. 但是，还有一个表不包含我们感兴趣的信息，但与主表和查找表有一个共同的列。
让我们看一下下面的例子。 我们有一个带有列的主表 SKU（新），您要在其中添加一列，其中包含另一个表中的相应价格。 此外，我们有 2 个查找表。 第一个（查找表 1）包含更新的数字 SKU（新） 和产品名称，第二个（查找表 2）——产品名称和旧编号 SKU（旧）.
要将第二个查找表中的价格添加到主表中，您必须执行一个称为 double 的操作 VPR 或嵌套 VPR.
- 写一个函数 VPR，在表中查找产品名称 查找表 1运用 SKU，作为期望值：
=VLOOKUP(A2,New_SKU,2,FALSE)=ВПР(A2;New_SKU;2;ЛОЖЬ)这里 新品_SKU – 命名范围 $A：$B 在桌子上 查找表 1, 2 – 这是 B 列，其中包含商品名称（见上图）
- 编写一个公式从表格中插入价格 查找表 2 基于知名产品名称。 为此，请将您之前创建的公式粘贴为新函数的查找值 VPR:
=VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)=ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)这里 价格 – 命名范围 $ A：$ C 在桌子上 查找表 2, 3 是包含价格的 C 列。
下图显示了我们创建的公式返回的结果：
使用 VLOOKUP 和 INDIRECT 动态替换不同表中的数据
首先，让我们澄清一下“动态替换来自不同表的数据”这一表述的含义，以确保我们能够正确理解彼此。
有些情况下，有多个表格具有相同格式的数据，并且有必要根据在给定单元格中输入的值从某个表格中提取必要的信息。 我认为用一个例子来解释这一点更容易。
想象一下，您有多个地区的销售报告，这些报告具有相同的产品和相同的格式。 您想查找特定地区的销售数据：
如果你只有两个这样的报告，那么你可以使用一个可耻的简单公式和函数 VPR и IF (IF) 选择要搜索的报告：
=VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)
=ВПР($D$2;ЕСЛИ($D3="FL";FL_Sales;CA_Sales);2;ЛОЖЬ)
地点：
- $D$2 是包含产品名称的单元格。 请注意，我们在此处使用绝对引用以避免在将公式复制到其他单元格时更改查找值。
- $ D3 是一个带有区域名称的单元格。 我们使用绝对列引用和相对行引用，因为我们计划将公式复制到同一列中的其他单元格。
- FL_萨尔es и CA_销售 – 包含相应销售报告的表（或命名范围）的名称。 当然，您可以使用通常的工作表名称和单元格范围引用，例如 'FL 表'！$A$3:$B$10，但命名范围更方便。
但是，当有很多这样的表时，函数 IF 不是最好的解决方案。 相反，您可以使用该功能 间接 （间接）返回所需的搜索范围。
你可能知道，函数 间接 用于返回由文本字符串给出的链接，这正是我们现在所需要的。 所以，大胆地将上面公式中的表达式替换为函数 IF 与功能联系 间接. 这是一个组合 VPR и 间接 适用于：
=VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"_Sales");2;ЛОЖЬ)
地点：
- $D$2 – 这是一个带有产品名称的单元格，由于绝对链接，它没有变化。
- $ D3 是包含区域名称第一部分的单元格。 在我们的示例中，这 FL.
- _销售量 – 所有命名范围或表名称的公共部分。 当与单元格 D3 中的值组合时，它形成所需范围的完全限定名称。 以下是该功能新手的一些详细信息 间接.
INDIRECT 和 VLOOKUP 如何工作
首先，让我提醒你函数的语法 间接 （间接）：
INDIRECT(ref_text,[a1])
ДВССЫЛ(ссылка_на_текст;[a1])
第一个参数可以是单元格引用（A1 或 R1C1 样式）、范围名称或文本字符串。 第二个参数确定第一个参数中包含的链接样式：
- A1如果论点是 真实代码 (TRUE) 或未指定；
- R1C1如果 F作为电子 （错误的）。
在我们的例子中，链接具有样式 A1，因此您可以省略第二个参数并专注于第一个参数。
所以让我们回到我们的销售报告。 如果您还记得，那么每个报告都是位于单独工作表上的单独表格。 要使公式正常工作，您必须为表（或范围）命名，并且所有名称都必须有一个公共部分。 例如，像这样： CA_销售, FL_销售, TX_销售 等等。 如您所见，“_Sales”出现在所有名称中。
功能 间接 连接 D 列中的值和文本字符串“_Sales”，从而告诉 VPR 在哪个表中搜索。 如果单元格 D3 包含值“FL”，则公式将搜索表格 FL_销售, 如果“CA”——在表中 CA_销售 等等。
函数的结果 VPR и 间接 将如下：
如果数据位于不同的 Excel 书籍中，则需要在命名范围前添加书籍名称，例如：
=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"Workbook1!_Sales");2;ЛОЖЬ)
如果函数 间接 指另一个工作簿，该工作簿必须打开。 如果关闭，函数会报错。 ＃REF！ （#SSYL！）。