按数组过滤范围 [英] Filter a range by array

问题描述

我有一个Google电子表格，其中包含2012年欧洲足球锦标赛的球队及其得分:

I have a Google Spreadsheet containing the teams of the UEFA EURO 2012, and their scores:

Team     Points  Goals scored
------   ------  ------------
Germany    6          3
Croatia    3          3
Ireland    0          1
...       ...        ...

现在，我想过滤该列表，以便结果仅包含所涉及团队的一部分.具体来说，我希望结果列表中仅包含德国，荷兰，葡萄牙，意大利，英格兰，法国，西班牙和<克罗地亚> em 的团队.

Now I want to filter that list, so that the result contains only a subset of the teams involved. Specifically, I want the resulting list to contain only the teams Germany, Netherlands, Portugal, Italy, England, France, Spain and Croatia.

我知道我可以使用FILTER函数从表中提取单个值.因此，我可能会写一个像=FILTER(A2:C; A2:A = 'Germany' OR A2:A = 'Netherlands' OR A2:A = 'Portugal' OR ...)这样的FILTER表达式，但是我想避免这种情况，因为团队列表是动态的.

I know I can use the FILTER function to extract a single value from the table. Thus, I could probably write a FILTER expression like =FILTER(A2:C; A2:A = 'Germany' OR A2:A = 'Netherlands' OR A2:A = 'Portugal' OR ...) but I would like to avoid this, as the list of teams is sort of dynamic.

所以问题是:如何按范围值(而不是单个值)过滤表?

So the question is: How can I filter the table by a range of values - not just a single value?

推荐答案

对于像我一样偶然发现此线程的答案寻求者，请参见此

For answer-seekers who stumble onto this thread as I did, see this Google product forum page, where both Yogi and ahab present solutions to the question of how to filter a range of data by another range of data.

如果A3:C包含要过滤的UEFA EURO 2012数据范围，并且D3:D包含要过滤的球队列表，则E3 ...

If A3:C contains the range of UEFA EURO 2012 data to be filtered, and D3:D contains the list of teams by which to filter, then E3 ...

=FILTER(A3:C, MATCH(A3:A, D3:D,0))

或

=FILTER(A3:C, COUNTIF(D3:D, A3:A))

相反，如果您要按D3:D中列出的不团队进行过滤，则E3 ...

Conversely, if you'd like to filter by teams not listed in D3:D, then E3...

=FILTER(A3:C, ISNA(MATCH(A3:A, D3:D,0)))

或

=FILTER(A3:C, NOT(COUNTIF(D3:D, A3:A)))

这是一个示例电子表格这些功能的有效性.

Here's an example spreadsheet I've made to demonstrate these functions' effectiveness.

这篇关于按数组过滤范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！