☰

返回符合两个条件的所有值的列表

tag-icon • tag-icon microsoft-excel vlookup
返回符合两个条件的所有值的列表

我正在尝试设置一个公式来返回满足两个条件的值。例如

  A       B         C
1 Bob     Banana    10   
2 John    Banana    10
3 Steven  Apple     5
4 Elliott Apple     2
5 Andrew  Apple     5

我想要返回 B 列和 C 列的值分别等于 Apple 和 5 的名称列表。

我使用了以下公式的变体成功返回了一个值，但在将第二个值放入此公式时遇到了麻烦

=IFERROR(INDEX($A$1:$A$4,SMALL(IF($B$1:$B$4=$D$1,ROW($B$1:$B$4)-ROW($B$1)+1),ROWS($B$1:$B1))),"")

如果有人能帮我解决这个问题就太好了。谢谢。

答案1

此数组公式可以解决这个问题：

在此处输入图片描述

{=IFERROR(INDEX($J$38:$L$42, SMALL(IF(COUNTIFS($J$44, $K$38:$K$42, $K$44, $L$38:$L$42), ROW($J$38:$L$42)-MIN(ROW($J$38:$L$42))+1), ROW(A1)), COLUMN(A1)),"")}

注意：

  • 在单元格中完成公式J46，使用Ctrl+Shift+Enter，向右填充，然后向下填充。
  • 单元格J44&K44包含标准
  • 您的公式仅在一列中搜索值， =IFERROR(INDEX($A$1:$A$4
  • 而且它只符合一个条件，而不是两个，

IF($B$1:$B$4=$D$1

现在，让我描述一下数组公式背后的机制：

 =INDEX($J$38:$L$42, SMALL({False, False, 3, False, 5}, ROW(A1)), COLUMN(A1))

变成，

=INDEX($J$38:$L$42, 3, 5)

然后通过向右和向下拖动其他值来返回名称。

(IF(COUNTIFS($J$44, $K$38:$K$42, $K$44, $L$38:$L$42), ROW($J$38:$L$42)-MIN(ROW($J$38:$L$42))+1)

返回，

IF({0, 0, 1, 0, 1,}, 1, 2, 3, 4, 5)

最后是 TRUE/FALSE 和行号的组合3，5如下所示，

{False,False, 3, False, 5}INDEX与&结合SMALL，如上所示。

  • IFERROR如果公式找不到值，则用公式包裹以用空白替换错误。

根据需要调整公式中的单元格引用。

答案2

Rajesh S 采用了您尝试的方法并演示了如何使该方法发挥作用。如果数据是动态的，那么这种方法就足够了。但是如果您只需要定期提取结果表，我会提供一些非常简单的替代方案。这些可以在几分钟内完成，即使您没有咖啡来思考。

过滤

在下图中，我复制结果后取消选择了过滤器，以便您可以看到源数据。

在此处输入图片描述

  • 突出显示您想要用作标准的数据。
  • 打开过滤。
  • 在过滤器中选择您想要的值。这将隐藏您不想要的所有内容。
  • 选择显示的内容，复制并粘贴到结果区域。

需要不同的标准？选择新标准。复制并粘贴。

数据透视表

对于给定的一组条件，每条符合条件的记录都会有一个名称，然后在每条记录上重复该条件。如果您要针对不同的条件生成报告，则需要将条件与结果一起保留。但是，如果您不需要在每条记录上都这样做，则数据透视表提供了一种快速的方法。

在此处输入图片描述

  • 突出显示数据。
  • 从工具栏或菜单中插入数据透视表。
  • 将“名称”字段拖到“行”窗口。将“水果”和“数字”字段拖到过滤器。
  • 在过滤器中选择您的标准。

结果是符合条件的名称列表，其标题中总结了标准。

您可以复制数据透视表并有两个粘贴选项。常规粘贴将为您提供另一个数据透视表。您可以更改其中的过滤条件以适应其他情况。选择性粘贴、值将粘贴结果的副本。

相关内容