☰

根据三个条件进行查找

tag-icon • tag-icon microsoft-excel vlookup
根据三个条件进行查找

我有这张表。

三大标准

我想根据三个条件查找执行率 (%) 的值：年份、星期和机器。因此，我想告诉查找搜索年份：2018、星期：2、机器：Filler，我应该得到数字 100%。我想对 CILT 日期、持续时间 (%) 和分数执行相同操作。

对于这种事情，VLOOKUP 的作用似乎非常有限。

我找到的有关INDEX和MATCH的信息没有得到很好的解释。

VLOOKUP 能做到这一点吗？如果不行，如何检索所需的值？

我有这个公式

=INDEX('L5 数据'!M5:M15,SUMPRODUCT(--('L5 数据'!A5:A15="2018")('L5 数据'!B5:B15="2")('L5 数据'!F5:F15="填充")*ROW('L5 数据'!5:15)))

但它返回的是下一行的值。换句话说，对于年份：2018、周数：2、机器：Filler，它返回的是 81.8% 而不是 100%。可能是什么原因？

答案1

您问了三个问题。

一个是：“可以使用 VLOOKUP() 来执行此操作吗？” Miroxlav 发布了一个很好的答案，展示了如何使用辅助列来做到这一点。

你的第二个问题询问如何获得你想要的结果，第三个问题询问当前公式返回错误答案的原因是什么。我会尝试回答最后两个问题。

您的公式几乎正确运行。让我们进行一些故障排除以找出问题所在。

首先，您的公式有几个拼写错误 - 括号之间的两处缺少乘法符号。Excel 建议对此进行更正，并且为了简单起见，我删除了数据表引用（“L5 数据”！）：

=INDEX(M5:M15,SUMPRODUCT(--(A5:A15="2018")*(B5:B15="2")*(F5:F15="Filler")*ROW(5:15)))

Excel 有一个内置故障排除功能您可以在公式栏中突出显示公式的一部分，然后按 F9 来查看价值公式中突出显示的部分。

单击包含公式的单元格，然后在公式栏中突出显示表达式 (A5:A15="2018")，并按 F9（功能键 9）。您将看到一个包含所有FALSE值的数组。这表示 A5:A15 中没有单元格 =“2018”。这是因为 2018 用引号括起来了。如果您删除引号并重试，您将获得您期望的数组{TRUE;TRUE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE}- 范围内的前 5 个单元格是 = 2018。

第二个表达式也存在同样的问题 - 删除 中 2 周围的引号(B5:B15="2")。第三个表达式(F5:F15="Filler") 做需要引号，因为“Filler”是文本而不是数字。

现在突出显示(A5:A15=2018)*(B5:B15=2)*(F5:F15="Filler")并按下 F9。您将看到结果是{0;0;0;1;0;0;0;0;0;0;0}。这里发生了什么？将逻辑值数组相乘会将 True/False 值转换为 1 和 0，并执行与逻辑 AND() 等效的操作。因此，此结果表示在行 5:15 的范围内只有一个位置，其中 A 列为 2018，B 列为 2，F 列为“Filler”。那个位置是第四个。

接下来，公式将此数组乘以行号为 5:15 的数组。使用 F9，您可以看到结果 - {0;0;0;8;0;0;0;0;0;0;0}。现在 SUMPRODUCT() 将该数组的元素相加并返回结果 8，这是所有三个条件都为真的行号。同样，您可以使用 F9 查看此结果。

现在主要问题来了——经过这些中间计算后，您的公式为：=INDEX(M5:M15,8)，返回 M5:M15 范围内的第 8 个元素。但您不需要第 8 个元素，您需要第 4 个元素，因为 M5:M15 范围从第 5 行开始，而 100% 位于 M5:M15 范围内的第 4 个单元格中。

因此解决方案是从 SUMPRODUCT() 中减去 4：

=INDEX(M5:M15,SUMPRODUCT((A5:A15=2018)*(B5:B15=2)*(F5:F15="Filler")*ROW(5:15))-4)

这将返回正确的结果 - 100%。

笔记：

  1. 无论我如何努力，我都无法让您的公式返回 81.8%。
  2. 公式中的双重一元负数 (--) 不是必需的。它通常用于将逻辑数组转换为 1 和 0'，但数组的乘法可以实现这一点。

希望这能有所帮助。我列出的更改将使您的公式正常工作。如果您想使用 VLOOKUP()，请使用 Miroxlav 的答案。祝你好运。

答案2

是的，VLOOKUP() 可以做到这一点，为您提供所需的单个值（如果您需要总和，请使用 SUMIF()）。
在这些情况下，我通常为 VLOOKUP() 创建并使用复合键。
就您而言，这样的键仅包含所有三列的值。
它使您能够使用三列指定查找条件。

细节：

  1. 插入带标题的 A 列年|月|机器和公式（适用于 A5）

    =B5 & "|" & C5 & "|" & D5

    因此对于 A5 它将评估为2018|1|Filler。

  2. 然后将配方传播下去。

  3. 无论您想在哪里进行查找，只需组装相同的键并执行 VLOOKUP()，例如：

    =VLOOKUP("2018|2|Filler", A5:R9999, 8, FALSE)
    • 将搜索到的表达式调整为您想要的，例如2018|1|Labeler或根据其他单元格中的值进行组合。
    • 调整数据范围A5:R9999为实际范围。
    • 调整值8以匹配列Execution (%)，从上述范围的左列开始计算。

一旦完成此操作，您还可以基于其他三列创建类似的查找机制。只需将另一列添加到左边相同数据。（原因：VLOOKUP() 期望查找列位于最左边。）

笔记：通常，使用分隔符（在上面的例子中我使用了）|来保持键的唯一性非常重要。否则2018，、、10和、将创建相同的键Filler，这显然是错误的。对于分隔符，请使用值中不存在的字符。201810Filler

更新：此解决方案有效。在下面的评论中，有人提到了一些问题，现在这些问题已得到纠正或要点更加明确。感谢 Rajesh 的校对。

答案3

你的公式应该是这样的，

{=INDEX(K$96:K$100,SUMPRODUCT(($D$96:$D$100="2018")*($E$96:$E$100=2)*($I$96:$I$100="Filler")*ROW($96:$100)))}

注意：只需在每个数据范围之间放置“ * ”符号，并且不要在 SUMPRODUCT 后使用“ - - ”。

笔记，它是CSE 公式所以结束Ctrl+Shift+Enter。

另外，使用您的数据范围代替我的。

希望这对你有帮助。

相关内容