问题描述
我有一个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?
推荐答案
对于像我一样偶然发现此线程的答案寻求者，请参见此 Google产品论坛页面，其中Yogi和ahab都提出了有关如何过滤的问题的解决方案一个数据范围与另一个数据范围.
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.
这篇关于按数组过滤范围的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！