Excel 的新 XLOOKUP 将取代 VLOOKUP,为 Excel 最流行的功能之一提供强大的替代品。 这个新功能解决了 VLOOKUP 的一些限制,并具有额外的功能。 这是你需要知道的。
目录
什么是 XLOOKUP?
新的 XLOOKUP 功能为 VLOOKUP 的一些最大限制提供了解决方案。 此外,它还取代了 HLOOKUP。 例如,XLOOKUP 可以向左看,默认为完全匹配,并允许您指定单元格范围而不是列号。 VLOOKUP 不是那么容易使用或用途广泛。 我们将向您展示这一切是如何运作的。
目前,XLOOKUP 仅对 Insiders 计划的用户可用。 任何人都行 加入 Insiders 计划 以在最新的 Excel 功能可用时立即访问它们。 Microsoft 将很快开始向所有 Office 365 用户推出它。
如何使用 XLOOKUP 函数
让我们直接深入了解 XLOOKUP 的实际应用示例。 以下面的示例数据为例。 我们希望为 A 列中的每个 ID 从 F 列返回部门。
这是一个经典的完全匹配查找示例。 XLOOKUP 函数只需要三个信息。
下图显示了带有六个参数的 XLOOKUP,但只有前三个是完全匹配所必需的。 因此,让我们关注它们:
Lookup_value:您要查找的内容。
Lookup_array:在哪里看。
Return_array:包含要返回的值的范围。
以下公式适用于此示例: =XLOOKUP(A2,$E$2:$E$8,$F$2:$F$8)
现在让我们在这里探讨 XLOOKUP 相对于 VLOOKUP 的几个优势。
没有更多的列索引号
VLOOKUP 臭名昭著的第三个参数是指定要从表数组返回的信息的列号。 这不再是一个问题,因为 XLOOKUP 使您能够选择要返回的范围(本示例中的 F 列)。
并且不要忘记,XLOOKUP 可以查看所选单元格左侧的数据,这与 VLOOKUP 不同。 更多关于这下面。
插入新列时,您也不再遇到公式损坏的问题。 如果在您的电子表格中发生这种情况,则返回范围会自动调整。
完全匹配是默认值
在学习 VLOOKUP 时,为什么必须指定精确匹配总是令人困惑。
幸运的是,XLOOKUP 默认为完全匹配——这是使用查找公式的更常见的原因)。 这减少了回答第五个参数的需要,并确保公式新手的错误更少。
所以简而言之,XLOOKUP 比 VLOOKUP 提出的问题更少,更人性化,也更耐用。
XLOOKUP 可以向左看
能够选择查找范围使 XLOOKUP 比 VLOOKUP 更通用。 使用 XLOOKUP,表列的顺序无关紧要。
VLOOKUP 受限于搜索表的最左侧列,然后从指定数量的列返回到右侧。
在下面的示例中,我们需要查找一个 ID(E 列)并返回该人的姓名(D 列)。
下面的公式可以实现:=XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8)
如果找不到怎么办
查找函数的用户非常熟悉 #N/A 错误消息,当他们的 VLOOKUP 或他们的 MATCH 函数无法找到所需的内容时,该错误消息会向他们打招呼。 这通常有一个合乎逻辑的原因。
因此,用户快速研究如何隐藏此错误,因为它不正确或无用。 当然,也有办法做到这一点。
XLOOKUP 带有自己的内置“如果未找到”参数来处理此类错误。 让我们在前面的示例中查看它的实际效果,但输入错误的 ID。
以下公式将显示文本“Incorrect ID”而不是错误消息:=XLOOKUP(A2,$E$2:$E$8,$D$2:$D$8,”Incorrect ID”)
使用 XLOOKUP 进行范围查找
虽然不如精确匹配那么常见,但查找公式的一种非常有效的用途是在范围内查找值。 举个例子。 我们希望根据花费的金额返还折扣。
这次我们不是在寻找一个特定的值。 我们需要知道 B 列中的值在 E 列范围内的哪个位置。这将决定获得的折扣。
XLOOKUP 有一个可选的第五个参数(记住,它默认为完全匹配),命名为匹配模式。
您可以看到 XLOOKUP 具有比 VLOOKUP 更强大的近似匹配功能。
可以选择查找小于 (-1) 或大于 (1) 所查找值的最接近匹配。 还有一个选项可以使用通配符 (2),例如 ? 或者 *。 默认情况下,此设置不像 VLOOKUP 那样启用。
如果未找到完全匹配,此示例中的公式将返回小于查找值的最接近的值: =XLOOKUP(B2,$E$3:$E$7,$F$3:$F$7,,-1)
但是,单元格 C7 中有一个错误,返回 #N/A 错误(未使用“如果未找到”参数)。 这应该返回 0% 的折扣,因为花费 64 不符合任何折扣的标准。
XLOOKUP 函数的另一个优点是它不需要像 VLOOKUP 那样按升序排列查找范围。
在查找表底部输入新行,然后打开公式。 通过单击并拖动角来扩大使用范围。
该公式立即纠正了错误。 范围底部有“0”不是问题。
就个人而言,我仍然会按查找列对表格进行排序。 底部有“0”会让我发疯。 但是公式没有被打破的事实是辉煌的。
XLOOKUP 也替换了 HLOOKUP 函数
如前所述,XLOOKUP 函数也是在这里替代 HLOOKUP。 一个功能代替两个。 优秀的!
HLOOKUP 函数是水平查找,用于沿行搜索。
不像它的兄弟 VLOOKUP 那样广为人知,但对于下面这样的示例很有用,其中标题位于 A 列,数据位于第 4 行和第 5 行。
XLOOKUP 可以在两个方向上查看 – 向下列和沿行。 我们不再需要两个不同的功能。
在此示例中,该公式用于返回与单元格 A2 中的名称相关的销售值。 它沿着第 4 行查找名称,并从第 5 行返回值: =XLOOKUP(A2,B4:E4,B5:E5)
XLOOKUP 可以自下而上查看
通常,您需要查找一个列表以查找第一次(通常是唯一一次)出现的值。 XLOOKUP 有一个名为搜索模式的第六个参数。 这使我们能够将查找切换到从底部开始并查找列表以查找最后一次出现的值。
在下面的示例中,我们希望在 A 列中找到每种产品的库存水平。
查找表按日期顺序排列,每个产品有多个库存检查。 我们希望返回上次检查时的库存水平(最后一次出现的产品 ID)。
XLOOKUP 函数的第六个参数提供了四个选项。 我们有兴趣使用“从后到先搜索”选项。
完整的公式如下所示: =XLOOKUP(A2,$E$2:$E$9,$F$2:$F$9,,,-1)
在这个公式中,第四个和第五个参数被忽略了。 它是可选的,我们想要完全匹配的默认值。
围捕
XLOOKUP 函数是 期待已久的继任者 VLOOKUP 和 HLOOKUP 函数。
本文使用了各种示例来展示 XLOOKUP 的优势。 其中之一是 XLOOKUP 可以跨工作表、工作簿以及表格使用。 文章中的示例保持简单,以帮助我们理解。
由于 动态数组被引入 Excel 很快,它也可以返回一系列值。 这绝对是值得进一步探索的东西。
VLOOKUP 的日子屈指可数。 XLOOKUP 就在这里,并将很快成为事实上的查找公式。