Google Таблицы. Углубление в функции. Filter
Google Sheets – один из крутейших инструментов для работы с данными. Он позволяет обрабатывать данные в визуально понятном виде, именно поэтому у него такая огромная база поклонников, включающая в себя маркетологов, аналитиков (хотя большинство все равно выбирает более традиционный Excel), предпринимателей и студентов.
Гугл Таблицы, помимо работы с текстом (разделением и объединением), о которой писал ранее, позволяет фильтровать данные по заданным параметрам.
Для этого можно использовать две ключевые функции:
- Filter
- Query (но о ней в другой раз)
Функция FILTER
Функция FILTER применяется для фильтрации данных по определенным параметрам.
Она принимает в себя массив и находит соответствия значений с заранее заданным параметрам. Также функция Filter может принимать в себя и другие функции и использовать их значения.
Из чего состоит функция
=FILTER( диапазон ; условие 1 ; условие 2-n)
Диапозон – это массив данных с которым мы хотим работать. Он может быть выражен с виде диапазона (A1:V22) или отдельной функции, например importrange или .
Условие 1 – это условие при соблюдение которого строки будут показываться, как результат нашей функции.
Условия 2-n – это дополнительные условия, которые можно применить к диапозону. Являются не обязательными.
Сама по себе, функция Filter очень проста, как в настройке, так и в применении. Рассмотрим несколько примеров...
Примеры использования функции FILTER
Пример 1. Фильтр с одним условием
Предположим у нас есть таблица с делами, где мы отмечаем выполненные задачи.
И мы хотим вывести отдельный список дел, которые еще не завершены...другими словами, список незаконченных дел...
Действия
- Пропишем функцию | FILTER()
- Выбираем диапазон A2:B6 для нашей функции | FILTER(A2:B6 ; )
- Дополним функцию условием, что нам нужны строки, когда в колонке B стоит статус FALSE | FILTER(A2:B6 ; B2:B6 = FALSE)
- Получаем список задач
Итоговая функция =FILTER(A2:B6 ; B2:B6 = FALSE)
Задача выполнена, но немного улучшим функцию:
Мы получили фильтрованный диапазон, но мы и так понимаем, что это список с задачами, которые не выполнены.
Мы можем улучшить наш результат многими способами...зависит от исходных данных, например при наличии именованных диапазонов лучше подойдет функция INDEX, чтобы получить конкретную колонку, но можно сделать проще.
Улучшение
- Вместо диапазона с всей таблицей, возьмем только интересующую часть (колонку А) | FILTER(A2:A6)
- А условие оставим предыдущим | FILTER(A2:A6 ; B2:B6)
- И получим финальный список задач
Пример 2. Фильтр с двумя переменными условиями
У нас есть таблица с задачами по двум сотрудникам
Мы хотим сделать отдельную таблицу, чтобы можно было посмотреть выполненные и не выполненные задачи по отдельным сотрудникам.
Действия
- Создаем выпадающие списки для выбора сотрудника и статуса задачи
- Задаем значения выпадающего списка по сотрудникам по уникальным значениям из соответствующего диапозона
- Задаем значения выпадающего списка для статуса: Все, TRUE и FALSE
- Прописываем нашу функцию | FILTER()
- Выбираем нужный диапазон | FILTER(A2:B6)
- Прописываем первое условие, чтобы значения из колонки C соответствовали значению в ячейке с именем A12 | FILTER(A2:B6; C2:C6 = A12)
- Прописываем второе условие, чтобы учитывался статус задач и, чтобы, если мы хотим увидеть все статусы, не рушилась формула. | FILTER(A2:B6; C2:C6=A12; IF(B12="Все"; NOT(ISBLANK(A2:A6)); D2:D6=B12))
Итоговая формула =FILTER(A2:B6; C2:C6=A12; IF(B12="Все"; NOT(ISBLANK(A2:A6)); D2:D6=B12))
Задача решена!
О каких еще функциях вы бы хотели узнать больше? Или какие задачи у вас есть в GSheets, которые можно попробовать решить?
Буду рад пообщаться на любые вопросы тет-а-тет и в комментариях по направлениям:
- Управлению репутацией
- Разработке сайтов на Tilda
- Созданию презентаций
- Формированию репутационного отдела ин хаус
- Google Таблицы
классная статья. сам сталкивался на неделе с задаче по сложным фильтрам в гугл таблицах. но у меня использовалась функция Query.
но я решал задачу через нейросети) сам бы никогда не написал ту формулу что получилась) а нейросеть шмагла)
=QUERY('База'!A:K; "SELECT * WHERE " & CHAR(64 + MATCH("Подрубрика"; 'База'!1:1; 0)) & " matches '" & TEXTJOIN("|"; TRUE; 'Подрубрики'!B:B) & "'")
Я до сих пор не понимаю как она работает) но главное результат) ну и пару часов времени игр с нейросетью. но теперь под повторяющуюся задачу есть готовое решение)
Виталий, приветствую!
Спасибо за комментарий и огромное спасибо за такой интересный пример!
У вас в запросе, по сути, сказано:
"
Возьми из листа "База" колонки А–К, покажи мне все колонки и выведи строки, где значения из колонки "Подрубрика" соответствуют одному из значений на листе "Подрубрики" в колонке В.
"
Функция QUERY в целом считается всемогущей) и относится к профессиональным решениям, потому что для ее использования надо знать базовый SQL.
Очень симпатичное решение получилось. Оно будет подстраиваться под незначительные изменения в листах и работать стабильно и быстро.
В рамках темы статьи, предложу решение с помощью функции FILTER:
=FILTER('База'!A:K; MATCH([буква колонки с подрубрикой] (записать надо в таком виде: A:A) ; 'Подрубрики'!B:B; 0)
Но тут сразу появится проблема с названиями колонок, а ваше решение супер крутое.
А какой нейросеткой пользовались?
Как с помощью этой функции вывести строки, кроме тех, которые содержат значения "Яблочко"? Прочие возможные значения не известны
Андрей, по аналогии с примерами из статьи, нам нужно выбрать только те значения которые не являются или не содержат слово "Яблочко".
Если задача максимально тривиальная и строки содержат конкретно слово "яблочко" или како-либо другое, то можно просто =FILTER ( [диапозон]; NOT ([диапозон] = "Яблочко"))
Если строка именно содержит слово яблочко внутри контекста, то надо проверить строку на содержание регулярного выражения, это лучше сделать через REGEXMATCH, чтобы получить true или false
=FILTER ( [диапозон]; NOT (REGEXMATCH ([диапозон]; "(?i)яблочко")))