Фильтровать диапазон по массиву

У меня есть таблица Google, в которой указаны команды чемпионата Европы по футболу 2012 года и их результаты:

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

Теперь я хочу отфильтровать этот список, чтобы результат содержал только подмножество участвующих команд. В частности, я хочу, чтобы итоговый список содержал только команды Германии, Нидерландов, Португалии, Италии, Англии, Франции, Испании и Хорватии.

Я знаю, что могу использовать функцию FILTER для извлечения единственного значения из таблицы. Таким образом, я, вероятно, мог бы написать выражение FILTER, например =FILTER(A2:C; A2:A = 'Germany' OR A2:A = 'Netherlands' OR A2:A = 'Portugal' OR ...), но я бы хотел избежать этого, поскольку список команд является своего рода динамическим.

Возникает вопрос: как отфильтровать таблицу по диапазону значений, а не только по одному значению?


google-sheets filter google-sheets-formula
person Vidar S. Ramdal    schedule 14.06.2012    source источник
comment
Ответьте на аналогичный вопрос: stackoverflow.com/q/9128987/179529   -  person Guy    schedule 15.06.2012

Ответы (3)


arrow_upward
62
arrow_downward

Для тех, кто ищет ответы и наткнулся на эту тему, как и я, см. Это страница форума продукта Google, где и Yogi, и ahab представляют решения по вопросу о том, как отфильтровать диапазон данных по другому диапазону данных.

Если A3:C содержит диапазон данных УЕФА ЕВРО 2012, подлежащих фильтрации, а D3:D содержит список команд, по которым выполняется фильтрация, то E3 ...

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

or

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

«Положительные

И наоборот, если вы хотите отфильтровать команды, не перечисленные в D3:D, тогда _7 _...

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

or 

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

Отрицательные результаты фильтра

Вот пример электронной таблицы, которую я создал для демонстрации этих функций. эффективность.

person Greg    schedule 30.10.2014
comment
Я хотел бы отметить, что вы должны включить в свой ответ фактические детали, так как ссылки часто могут быть сломаны, что делает этот ответ не таким полезным в будущем. - person Douglas Gaskell; 07.06.2016
comment
Спасибо, Дуглас. Вы совершенно правы. Надеюсь, моего обновленного ответа будет достаточно. - person Greg; 12.06.2016

arrow_upward
4
arrow_downward

для тех, кому нужно использовать формулы Грега и бороться с несовпадением диапазона FILTER

=FILTER(A1:A, MATCH(A1:A, B1:B, 0))

=FILTER(A1:A, COUNTIF(B1:B, A1:A))

=FILTER(A1:A, ISNA(MATCH(A1:A, B1:B, 0)))

=FILTER(A1:A, NOT(COUNTIF(B1:B, A1:A)))

в случае, если вам нужно использовать формулу FILTER для возврата оценки между двумя диапазонами, и эти два диапазона имеют разный размер (например, когда они возвращаются из запроса) и не могут быть изменены для соответствия одинаковому размеру, и вы только что получили FILTER has mismatched range sizes. Expected row count: etc. ошибку , то это обходной путь:

для простоты предположим, что ваши диапазоны для фильтра: A1: A10 и B1: B8, вы можете использовать скобки массива {}, чтобы добавить две виртуальные строки в диапазоне B1:B8 для соответствия size A1:A10 с использованием REPT, где количество необходимых повторений должно быть рассчитано простым вычислением между начальными диапазонами.

тогда к этой формуле REPT нам нужно добавить +1 как исправление / отказоустойчивость (в случае, если разница между двумя начальными диапазонами равна 1), потому что REPT работает минимум с 2 повторениями. поэтому в некотором смысле нам нужно будет создать диапазон B1:B11 (от B1:B8), а позже мы просто удалим последнюю строку из диапазона, чтобы она была B1:B10 против A1:A10. мы будем использовать 2 уникальных символа для REPT

следующим шагом будет преобразование REPT в SPLIT и разделение на 2-й уникальный символ. затем (в зависимости от дальнейших потребностей) этот SPLIT необходимо обернуть в TRANSPOSE (потому что мы хотим сопоставить размер столбца с размером столбца), и последним шагом было бы обернуть его в QUERY и limit, чтобы снова вывести результат простой математикой COUNTA(A1:A10), чтобы обрезать последний реп. вместе это будет выглядеть так:

=FILTER(A1:A10, NOT(COUNTIF(QUERY({B1:B8; 
 TRANSPOSE(SPLIT(REPT("♂♀", COUNTA(A1:A10)-COUNTA(B1:B8)+1), "♀"))}, 
 "limit "&COUNTA(A1:A10), 0), A1:A10)))

person player0    schedule 20.02.2019

arrow_upward
0
arrow_downward

Некоторые возможные и разные подходы:

=FILTER(C5:C;REGEXMATCH(C5:C;"one|two|five"))

результат: любой текст, содержащий один, два, пять. Деликатный случай.

=FILTER(C5:C;REGEXMATCH(C5:C;JOIN("|";DC5:DC7)))

То же, что и предыдущее, но значения в списке: DC5: DC7

=FILTER(C5:C;REGEXMATCH(C5:C;"^("&JOIN("|";EE5:EE7)&")$"))

то же, что и предыдущий + совпадение всего текста

Предлагаемые ранее варианты:

=FILTER(C5:C;MATCH(C5:C;EG5:EG7;))

=FILTER(C5:C;countif(EK5:EK7;C5:C))

Результат без учета регистра.

То же, нет в списке

=FILTER(C5:C;ISNA(MATCH(C5:C;EF5:EF7;)))

Интересно: дает разницу между списками.

person Max Makhrov    schedule 28.05.2021