☰

单一搜索条件多个结果

tag-icon • tag-icon microsoft-excel-2007
单一搜索条件多个结果

我需要仅使用一个搜索条件输出一列已排序数据的结果。同一条件有时会出现多次。LOOKUP 仅查找第一次出现的情况。我需要在单元格中输入要匹配的单元格是 J8:J581，要显示的相应数据是 N8:N581

J   K L M N
bob       RED
bob       BLUE
Bob       Green
Sue       yellow
Sue       white
fred      grey
pete      brown

。

input=bob
output=    bob  RED
                BLUE
                Green

答案1

如果您只是希望返回类似上述示例的行，请使用 Excel 表格。

  1. 选择您的数据
  2. 创建表格Insert>Tables>Table（选中“我的表格有标题”框）
  3. 创建表格后，只需选择过滤按钮（表格标题行中的向下箭头）并选择过滤值（例如，在样本中选择 Bob），这将仅返回“Bob”行。

这是之前的图像：

桌子

之后：

在此处输入图片描述

编辑： 根据您的补充信息，我会考虑添加一个基于数据表的数据透视表。这将允许您创建信息的“概述”视图，以更好地查看哪些露营地只有一个 ID，哪些有多个 ID。它看起来会像这样：

在此处输入图片描述

此外，@pnuts 指出，Excel 下拉列表（即筛选列表）中的项目数限制为 10,000 个，但表格成员数没有记录在案的限制。我的表格有 10,000 行。

编辑2：如果您想要轻松地找到重复的值，那么数据透视表绝对是最佳选择。

  1. 取出您的初始数据表并添加一个“计数器”行。 =IF([@Name]=D1,F1+1,1)这将返回具有相同名称的行数。
  2. Insert>Tables>Pivot Table根据您的数据创建数据透视表。
  3. 像这样格式化数据透视表：
    • 行标签 = ，，，Name并关闭所有值的小计和总计。CodeCounter
    • 过滤计数器 = 清除 1，这将仅显示具有多个值的名称。
    • 过滤器名称 = 您感兴趣的任何特定露营地名称。

过滤枢轴

答案2

假设您的输入单元格是 A1，输出单元格是 A3（用于名称）和 B3:B..（用于输出范围）（B 范围的末尾一直向下到您希望看到的最大结果数）。

对于 A3，输入公式=A1。

对于 B3，输入公式

  =IFERROR(INDEX($N$1:$N$99,SMALL(IF($J$1:$J$99=$A$3,ROW($N$1:$N$99)-ROW($N$1)+1),ROWS($N$1:$N1))),"")

这是一个数组公式，需要使用Control- Shift-Enter组合键输入。然后您可以将其复制到输出范围的底部。

请注意，该公式是针对最多 99 行的输入列表进行硬编码的。您可以将其更改为所需的任何长度。虽然可以引用整个列（J:J 和 N:N），但这可能会对性能造成影响，您可能希望避免这种情况。

公式如何运作

从内向外，该公式首先将要执行查找的姓名（单元格 A3）与完整姓名列表（J1:J99 范围内最多 99 个姓名）进行比较。该比较显示在下方函数分解的第 6 行中。

该比较的产物是一个数组，其中匹配的值为 True，不匹配的值为 False，例如 {False, False, False, False, True, True, False，... 等}。

然后，将该数组与可以视为名称列表的“行号”的数组进行比较：{1、2、3、4、5、6、... 99}。此比较由公式表第 6-8 行中的 IF 语句完成。

比较是逐个元素进行的。如果名称比较数组的元素等于 True，则 IF 返回其对应的行号；如果元素等于 False，则 IF 返回 FALSE。使用上面的两个示例数组，IF 语句的结果将是 {False, False, False, False, 4, 5, False, ...}。

匹配函数的元素

继续，SMALL 函数（从函数大纲的第 8 行开始）用于从 IF 中获取此新数组的第 k 个最小元素。在这种情况下，“k”由表达式 ROWS($N$1:$N1) 提供，当整个公式从第 1 行向下复制到第 99 行时，它将简单地从 1 计数到 99（ROWS($N$1:$N1) = 1、ROWS($N$1:$N2) = 2，依此类推）。

因此，SMALL 将首先找到 IF 生成的数组的最小元素，忽略为 False 的元素。换句话说，它将返回被比较的名称与查找名称列表中的名称匹配的第一个行号。在我们的示例中，即数字 4，如下表第 6 列所示。

总结步骤使用查找值上的 INDEX 来获取与已计算的行号相对应的元素。在本例中，问题的颜色列表示例中的第 4 项是“黄色”。（IFERROR 确保当公式无法找到匹配项时显示空白。）

这是完整公式第一次复制产生的结果。当将其复制到下一个单元格时，计算中唯一发生变化的是 SMALL 函数的“k”值，该值前进到 2。找到的第二小行号是 5，得出的结果是“白色”。

在此处输入图片描述

相关内容