В Google Таблицах есть несколько замечательных функций, которые помогут вам легко разделять и разделять данные. Хотя в Google Таблицах есть встроенная функция фильтра, в ее распоряжении также есть функция ФИЛЬТР).
Функция ФИЛЬТР, как следует из названия, позволит вам фильтровать набор данных на основе условия (или нескольких условий).
Например, если у вас есть список имен с названиями условий и продажной стоимостью, вы можете использовать функцию ФИЛЬТР в Google Таблицах, чтобы быстро получить все записи / назвать определенное условие (как показано ниже).
Одним из преимуществ использования функции ФИЛЬТР перед обычным фильтром в Google Таблицах является то, что результаты функции ФИЛЬТР являются динамическими. Если вы что-то измените в исходных данных, полученные отфильтрованные данные автоматически обновятся.
Это делает функцию ФИЛЬТРА в Google Таблицах отличным вариантом при создании интерактивных отчетов или информационных панелей.
Начнем с изучения синтаксиса этой функции.
Синтаксис функции фильтра Google Таблиц
Ниже приводится синтаксис функции ФИЛЬТР:
ФИЛЬТР (диапазон; условие1; [условие2;…]):
- [условие2] — это необязательный аргумент, который может быть вторым условием, которое вы проверяете в формуле. Опять же, это может быть столбец / строка (соответствующая столбцу / строке в наборе данных), которая возвращает массив ИСТИНА / ЛОЖЬ. Он должен быть того же размера, что и диапазон.
- condition1: это столбец / строка (соответствующая столбцу / строке в наборе данных), которая возвращает массив ИСТИНА / ЛОЖЬ. Должен быть того же размера, что и диапазон
- диапазон — это диапазон ячеек, который вы хотите отфильтровать.
Если вы используете несколько условий, результаты, которые возвращают истину для обоих условий, будут отфильтрованы.
Если функция ФИЛЬТР не может найти результат, соответствующий условию, она вернет ошибку # N / D.
Если вам интересно, как это работает, ознакомьтесь с парой примеров (перечисленных ниже), чтобы узнать, как использовать функцию ФИЛЬТР в Google Таблицах.
Пример 1 — Фильтрация данных на основе одного условия
Предположим, у вас есть набор данных, показанный ниже, и вы хотите быстро отфильтровать все записи, в которых название штата — Флорида.
Следующая формула:
= ФИЛЬТР (A2: C11, B2: B11 = «Флорида»)
В приведенной выше формуле в качестве аргумента используется диапазон данных, а условие — B2: B11 = Florida. Это условие проверяет каждую ячейку в диапазоне B2: B11, и если значение равно Флориде, эта запись фильтруется; иначе нет.
В этом примере я жестко запрограммировал значение, но вы также можете иметь это значение в ячейке, а затем ссылаться на эту ячейку. Например, если у вас есть текст Флорида в ячейке H1, вы также можете использовать следующую формулу:
= ФИЛЬТР (A2: C11; B2: B11 = H1)
Что нужно знать о функции ФИЛЬТР.
Функция ФИЛЬТР в Google Таблицах возвращает массив значений, распределенных по соседним ячейкам (это называется динамическим массивом). Чтобы это сработало, нужно убедиться, что соседние ячейки (куда будут помещены результаты) должны быть пустыми.
Если какая-либо из ячеек не пуста, ваша формула вернет ошибку #REF. Google Таблицы также расскажут, почему выдает ошибку при отображении красного треугольника в правом верхнем углу ячейки, а при наведении на него курсора появляется сообщение:
Результат массива не был расширен, потому что вы перезаписали данные в F3
И как только вы удалите заполненную ячейку, которая мешает функции FILTER вернуть результат, она автоматически заполнит диапазон результатом.
Кроме того, результатом формулы ФИЛЬТР является массив, и вы можете изменить часть массива. Это означает, что в результате вы не можете изменить или удалить ячейку (или пару ячеек). Вам нужно будет удалить из формулы весь результат. Чтобы удалить результат, вы можете выбрать ячейку, в которой вы ввели формулу, и затем нажать клавишу Delete.
Пример 2 — Фильтрация данных на основе нескольких условий (условие И)
Вы также можете использовать функцию ФИЛЬТР для проверки нескольких условий, чтобы она возвращала только те записи, в которых оба условия верны.
Например, предположим, что у вас есть набор данных ниже, и вы хотите отфильтровать все записи, в которых штат Флорида и стоимость продаж превышают 5000.
Вы можете сделать это, используя следующую формулу; = ФИЛЬТР (A2: C11, B2: B11 = «Флорида», C2: C11> 5000)
Вышеупомянутая формула проверяет два условия (где Флорида и цена продажи больше 5000) и возвращает все записи, которые соответствуют этим критериям.
Точно так же, если вы хотите, вы можете иметь несколько условий в формуле ФИЛЬТР.
Пример 3 — Фильтрация записей на основе нескольких условий (условие ИЛИ)
В приведенном выше примере я проверил два условия и вернул результаты, в которых оба условия ИСТИНА.
Вы также можете проверить условие ИЛИ в формуле ФИЛЬТР.
Например, предположим, что у вас есть набор данных, показанный ниже, и вы хотите получить все записи для Калифорнии и Айовы. Это означает, что условием должно быть состояние штата Калифорния или Айова (так что это условие ИЛИ).
Это сделает следующая формула: = ФИЛЬТР (A2: C11, (B2: B11 = «Калифорния») + (B2: B11 = «Айова»))
В приведенной выше формуле условие использует оператор сложения, чтобы сначала проверить оба условия, а затем добавить результат каждого из них. Поскольку эти условия возвращают массив или ИСТИНА и ЛОЖЬ, вы можете добавить их (поскольку ИСТИНА равно 1, а ЛОЖЬ равно 0 в Google Таблицах).
Это даст вам 0 (или ЛОЖЬ), если оба условия не выполняются, 1, если выполняется одно из двух условий, и 2, если выполняются оба условия.
И тогда формула ФИЛЬТР вернет все записи, в которых условия возвращают значение больше 0.
Пример 4 — Фильтр 3 или 5 лучших записей по значению
Вы также можете использовать функцию ФИЛЬТР, чтобы быстро получить 3 или 5 лучших (или любое количество записей, которое вы выберете).
Например, предположим, что у меня есть набор данных, показанный ниже, и я хочу быстро получить записи для трех основных значений продаж.
Это сделает следующая формула: = ФИЛЬТР (A2: C11, C2: C11> = LARGE (C2: C11,3))
В приведенной выше формуле используется функция BIG для получения третьего по величине значения в наборе данных. Затем это значение используется в условии, чтобы проверить, больше ли значение в столбце C этого значения.
Это вернет все записи, соответствующие критериям, которые будут первыми тремя записями.
Если вы хотите получить три нижние записи, вы можете использовать формулу ФИЛЬТРА ниже:
= ФИЛЬТР (A2: C11, C2: C11 Пример 5 — СОРТИРОВКА отфильтрованных данных (с использованием комбинации ФИЛЬТРА и СОРТИРОВКИ)
До сих пор все примеры формул FILTER, которые мы видели, будут фильтровать данные в том же порядке, в котором они отображаются в наборе данных.
Но что, если вы хотите получить упорядоченный набор данных.
Например, предположим, что вы фильтруете 5 самых популярных записей, было бы более полезно отсортировать их в порядке убывания (наибольший вверху).
Вот формула, которая фильтрует данные и отображает их в порядке убывания:
= СОРТИРОВАТЬ (ФИЛЬТР (A2: C11; C2: C11> = НАИБОЛЬШИЙ (C2: C11,3)); 3; ЛОЖЬ)
В приведенной выше формуле используется та же формула ФИЛЬТРА, которую мы использовали в предыдущем примере, чтобы получить первые три записи на основе продажной стоимости.
И поскольку я хотел, чтобы конечный результат был отсортирован в порядке убывания, я заключил функцию FILTER в функцию SORT. Функция СОРТИРОВКА берет результат формулы ФИЛЬТР и сортирует его на основе третьего столбца (который представляет собой продажи).
Третий аргумент функции SORT — FALSE, указывая, что я хочу получить окончательный результат в порядке убывания. Если вы оставите поле пустым (или сделаете его ИСТИННЫМ), результат будет в порядке возрастания.
Пример 6 — Фильтрация всех записей ЧЕТНЫХ чисел (или записей НЕЧЕТНЫХ чисел)
Это не такой распространенный вариант использования, но это то, что мне приходилось делать, когда я получал свои данные от кого-то еще, из базы данных или веб-страниц.
Часто данные, которые вам нужны, будут только в чередующихся строках (или в каждой третьей / четвертой / пятой строке), и вам нужно будет избавиться от лишних строк, чтобы собрать всю полезную нагрузку вместе.
В таком случае вы можете использовать функцию ФИЛЬТР, чтобы быстро отфильтровать и собрать все четные строки вместе (или все нечетные строки вместе). И вы также можете изменить формулу для фильтрации каждой третьей, четвертой или n-й строки в Google Таблицах.
Предположим, у вас есть набор данных, как показано ниже, и вы хотите отфильтровать все четные строки в этом наборе данных.
Ниже приведена формула, которая фильтрует все четные строки: = FILTER (A2: C11, MOD (ROW (A2: A11) -1,2) = 0)
В приведенной выше формуле используется функция (СТРОКА) СТРОКА, чтобы получить номера строк для всех строк в наборе данных. Затем он вычитает 1, поскольку наш набор данных начинается со второй строки.
Теперь используйте функцию MOD, чтобы проверить следующее условие: MOD (ROW (A2: A11) -1,2) = 0)
Это вернет ИСТИНА для всех четных строк и ЛОЖЬ для всех нечетных строк. И этот массив ИСТИНА и ЛОЖЬ используется функцией ФИЛЬТР для извлечения записей.
Точно так же, если вам нужны только пронумерованные записи ODD, вы можете использовать следующую формулу:
= ФИЛЬТР (A2: C11; MOD (СТРОКА (A2: A11) -1,2) = 1)
А если вы хотите отфильтровать каждую третью строку, вы можете использовать следующую формулу в Google Таблицах:
= ФИЛЬТР (A2: C11; MOD (строка (A2: A11) -1,3) = 0)
Вот несколько примеров того, как функция ФИЛЬТР в Google Таблицах может сэкономить время в реальном времени. И когда вы комбинируете его с другими формулами, вы можете многое с ним сделать.