☰

在列表中查找一个值并返回所有多个对应的值

tag-icon • tag-icon microsoft-excel worksheet-function
在列表中查找一个值并返回所有多个对应的值

我的问题的基本答案如下：如何在列表中查找某个值并返回多个相应的值。但是，一旦输入公式，我需要向下拖动或复制/粘贴公式，才能获得其余的相应值。引用文章：

当您在后续单元格中输入或填写此公式时，该公式将返回后续对应的值

但是，我无法知道预期值的数量，因此无法知道要复制/粘贴公式到什么程度。一直复制并粘贴会导致 Excel 崩溃。

编辑 如果我的数据如下所示：

Tag | Loc | Time
---|---|----
NN | IN | 7
CD | OUT | 4
VB | OUT | 12
NN | OUT | 4
NN | IN | 2
NN | OUT | 6
VB | OUT | 23
VB | OUT | 4
VB | IN | 6

我想对每个标签执行 t 检验，比较 IN 和 OUT 的时间。假设使用 INDEX 公式，我可以提取所有时间，例如 NN 和 OUT。

答案1

我认为这里的问题是，该链接中给出的用于处理超出预期回报数量的行中公式结果的建议是简单地将公式包装在某种 IFERROR 子句中。

然而，这种设置极其低效，特别是当所讨论的数据集是动态的、可能不断扩大的数据集时。

重点是，如果你有如下设置：

=IFERROR([一些大型数组公式],"")

目的是向下复制足够数量的行以封装所有所需的返回，那么您将面临两个选择。

首先，您可以进行一些计算，以精确确定在任意给定时间内您将获得多少这样的回报，然后将此公式向下拖动到该行数。显然，这不是理想的选择，如果您有一个动态变化的数据集，那么就更不理想了。

其次，我们可以将公式复制到任意数量的行，这样即使我们的数据集在未来某个时间扩大，我们也能保证涵盖所有可能的回报，因此不必再担心它。

显然，第二种方法在实践中更可取。IFERROR 构造的问题（如果您使用的是 2003 年或更早的版本，并且必须使用重复的 IF(ISERROR 子句），则问题更加严重）在于，在公式被复制到的行中，超出了实际需要的范围，没有什么可以阻止大量资源密集型数组公式的不必要计算。

关键在于，在上述构造中，即使在包含我们最后预期返回的行之外的行中，Excel 仍然必须花费所有资源来计算数组公式部分，然后才能自行决定它是否确实是一个错误。

远远比这种空闲的 IFERROR 方法要好得多 - 遗憾的是，互联网上各种来源几乎都推荐使用这种方法 - 正如詹姆斯指出的那样，使用一个“辅助”单元格首先确定我们期望返回的行数，然后在公式中引用它。

因此，例如，如果发布的数据在 A1:C10（标题在第 1 行），并且我们将 NN 放在 E1 中，将 OUT 放在 F1 中，我们将首先在 G1 中输入一个非数组公式：

=SUMPRODUCT(0+(A2:A10=E1),0+(B2:B10=F1))

那么我们第一个选择的单元格中的**数组公式****将是：

=IF(ROWS($1:1)>$G$1,"",INDEX($C$2:$C$10,SMALL(IF($A$2:$A$10=$E$1,IF($B$2:$B$10=$F$1,ROW($C$2:$C$10)-MIN(ROW($C$2:$C$10))+1)),ROWS($1:1))))

并按要求抄录下来。

当然，我们仍然有一个问题，即要将此公式向下复制多远。尽管我们已经进行了计算来确定所需的行数，但我们仍然不想每次想要更新结果时都必须手动重新调整包含公式的单元格数量。这应该是一次性的初始工作。

我当然不建议复制到电子表格的最后。但是，只要可以选择一个足够大的上限，那么即使我们最终得到几千个包含公式的无关单元格，在性能方面也不会有太大影响。原因是，以及此设置与“懒惰” IFERROR 方法之间的巨大差异，这里的初始子句：

=IF(ROWS($1:1)>$G$1,""

意味着，在超出预期返回数量的行中，IF 子句返回 TRUE，因此返回空白。IF 函数的优点在于，如果传递给它的子句为 TRUE，则 FALSE 部分（此处为一个庞大且资源密集型的数组公式）甚至不会被考虑用于计算。

IFERROR 版本的情况完全不同，它不断地进行计算，而没有意识到它的计算是不必要的，而且会给资源带来负担。

问候

**数组公式的输入方式与“标准”公式不同。您不必直接按 ENTER，而是先按住 CTRL 和 SHIFT，然后按 ENTER。如果您操作正确，您会注意到 Excel 在公式周围放置了花括号 {}（但不要尝试自己手动插入这些括号）。

答案2

我会建议将数据格式化为表格（主页>格式化为表格），或者在数据上创建一个动态命名范围。这解决了不知道您将拥有多少数据的问题，因为范围/表格会随着新数据的添加而增长，但您仍然可以通过名称引用范围/表格。

tldr：格式化为表格，如果这是工作簿中的第一个表格，则该表格将被命名为“Table1”

我的建议是此时创建一​​个数据透视表（使用表/范围的名称作为源），并将“标签”放入行标签中，将“位置”放入列标签中，将“时间”放入值中。您将获得如下所示的数据：

. IN OUT CD 4 NN 9 10 VB 6 39

然后，您可以在右侧添加一个简单的公式，并向下拖动以计算 IN 和 OUT 之间的差异

相关内容