Как сделать фильтр в гугл таблицах
Обновлено: 07.07.2024
Функция FILTER Таблиц Гугл отображает только те строки или столбцы в диапазоне, которые соответствуют заданным условиям. Отличительной особенностью этой формулы является то, что она работает как со строками, так и с столбцами.
Примечание Это большой и подробный указатель на возможности формулы FILTER на русском языке. Все примеры доступны в Таблице, см. раздел Ссылки .
Описание
Функция FILTER универсальная и очень гибкая. Она очень быстрая и легко читается. Используя такие функции как REGEXMATCH и MATCH , можно имитировать работу SQL-подобных запросов типа like и выбора на основе списка. Это действительно удобно, когда требуется отобрать большое количество данных, не зная конечного числа аргументов в уловии отбора.
Для всех примеров будет использоваться следующий набор данных
Примеры
Колонка соответсвует конкретному значению
Данные содержат заданную подстроку
Вывести все имена, в которых есть буквосочетание ль
Данные начинаются с определенной строки
Вывести имена, которые начинаются на А
Данные не начинаются с определенной строки
Вывести имена не начинающиеся на А
Сравнение чисел
Вывести данные, числа которых меньше 2500
Числа содержат определенную цифру
Номер содержит цифру 2
Числа оканчиваются на определенную цифру
Номер оканчивается на цифру 2
Сравнение дат
Даты меньше 31.12.2019
Даты только определенного года
Только 2017 год
Даты только определенного месяца
Только август 2019
Отбор по счету (по очереди)
Выбрать каждую шестую строку
Выбрать каждую нечетную строку
Данные, которые находятся в заранее заданном списке
Выбрать данные, имена которых находятся в списке
Данные, которые соответствуют списку
Выбрать имена, которые находятся в списке. Список в I8:I12
Данные, которые отсутствуют в списке
Выбрать имена, которых нет в списке. Список в I15:I19
Данные, которые соответствуют “нечеткому” списку значений
Выбрать все, в поле Строка которых, есть последовательность символов “x” и, любая цифра или тире “-”
Вывести все данные, где поле Имя начинается с буквы из списка
Список находится в I9:I12
Пример выбора женских имен
Пример выбора мужских имен
Выборка с условием ИЛИ
Требуется отфильтровать данные в Дате которых указан 2018 год или Группа которых равна "B"
Бесконечные диапазоны
Бесконечные диапазоны в FILTER могут создавать некоторые неудобства, особенно, если в выборку попадает достаточно много пустых строк. Они просто занимают место в результирующей Таблице. Если на это место внести данные, то получим ошибку
Ошибка Массив не выведен, поскольку это привело бы к перезаписи данных в диапазоне.
Чтобы этого избежать, необходимо добавлять столько условий, сколько требуется для точной выборки только релевантных значений. Или использовать точные размеры диапазонов для выборки.
В примере ниже на листе Данные 1000 строк, из которых только 20 заполнены значениями. Вот результаты фильтров
В первой формуле учитываются и не выводятся пустые строки 'Данные'!A2:A<>""
Фильтр по колонкам
Фильтр по колонкам проверяет условие в горизонтальном массиве. Если значение условно истинное, то колонка выведется, иначе нет.
Жестко заданные колонки по номерам. Только вторая и шестая колонка
Колонки по определенному условию. Если имя колонки равно “Имя” или “Группа”
Google Sheets — онлайн-аналог Microsoft Excel. С ним можно работать и в офлайне, правда только из браузера Chrome. В этой статье мы научим вас пользоваться Гугл Таблицами и расскажем об их функционале.
Изучаем панель инструментов
Чтобы создать документ-таблицу в Google Sheets, достаточно нажать на блок с плюсом или выбрать один из предустановленных в сервисе шаблонов.
Вы окажетесь в созданном документе, над которым будет панель инструментов. Вот с ней мы сейчас и познакомимся. Всего на панели есть 12 блоков, в каждом из которых присутствует от одной до пяти опций. Левый крайний блок содержит инструменты:
В третьем блоке можно менять формат данных. Например, выбрать денежный, финансовый, валютный, процентный, временной и другие форматы. Здесь же можно сократить или увеличить число знаков после запятой.
Четвертый и пятый блоки позволяют выбирать шрифт и его размер.
Шестой блок содержит инструменты для форматирования шрифтовых начертаний и цветов:
- Полужирный.
- Курсив.
- Зачеркнутый.
- Цвет текста.
Кнопки седьмого блока применяются для форматирования ячеек таблицы: заливка цветом, границы или объединение ячеек.
В восьмом блоке располагаются инструменты для выравнивания текста, его переноса или поворота.
Девятый блок содержит 5 полезных опций:
- Вставить ссылку.
- Добавить комментарий.
- Сформировать диаграмму.
- Фильтровать.
- Применить формулу.
В десятом блоке находится кнопка для активации экранной клавиатуры и ввода текста в рукописном режиме.
Одиннадцатый блок выполнен в форме стрелочки вверх. Он позволяет скрывать заголовок таблицы, общее меню, настройки доступа и оставлять только панель инструментов.
Внизу таблицы есть дополнительная панель. Здесь создается новый лист или открывается список уже имеющихся листов. А при нажатии на стрелочку появляется меню, через которое можно создать копию листа, переименовать его, защитить и т.д.
Что можно делать с отдельными элементами в Google Таблицах
Если переместить элемент таблицы нужно на произвольное место, просто перетащите его. Выделите ячейку, строку или столбец, наведите указатель мыши на границу и дождитесь, пока курсор примет форму руки. Затем зажмите левую кнопку мыши и перетащите ячейку, строку или столбец туда, куда вам надо.
Сортировка и фильтрация данных
Сортировать информацию в таблице можно по алфавиту в прямом и обратном порядке. Если в таблице только числа, то они будут отсортированы по величине значения.
Активируйте фильтр, потом нажмите на его иконку в заголовке строки. Такой значок присваивается каждому столбцу.
Фильтрация от А до Я и наоборот работает аналогично сортировке. Здесь же можно выбрать различные условия для фильтра. Можно создавать несколько фильтров и накладывать их один на другой.
История изменений документов в Google Таблицах
Здесь можно отменить изменения и восстановить любой из вариантов. Конечно, при условии, что у вас есть доступ к редактированию документа.
В Google Таблицах есть еще немало опций, которые пригодятся любому. Это и формулы, и вставка диаграмм, и различные дополнения. Изучайте все возможности сервиса и делитесь своими впечатлениями с сообществом 1C-UMI в социальных сетях ― ВКонтакте и Facebook!
В Google Таблицах есть несколько отличных функций, которые могут помочь легко разрезать данные. Хотя в Google Таблицах есть встроенная функция фильтра, в вашем распоряжении также есть функция FILTER (ФИЛЬТР).
Функция FILTER , как следует из названия, позволит вам фильтровать набор данных на основе условия (или нескольких условий).
Например, если у вас есть список имен с названиями состояний и продажной стоимостью, вы можете использовать функцию FILTER в Google Таблицах, чтобы быстро получить все записи / назвать одно конкретное состояние (как показано ниже).
Одним из преимуществ использования функции FILTER по сравнению с обычным фильтром в Google Таблицах является то, что результаты функции FILTER являются динамическими. Если вы измените что-либо в исходных данных, полученные отфильтрованные данные автоматически обновятся.
Это делает функцию FILTER Google Таблиц отличным выбором при создании интерактивных отчетов или информационных панелей.
В этом руководстве я покажу вам, как работает функция ФИЛЬТР в Google Таблицах, а также расскажу о некоторых полезных примерах, которые можно использовать в повседневной работе.
Итак, давайте начнем с изучения синтаксиса этой функции.
Синтаксис функции фильтра Google Таблиц
Ниже приведен синтаксис функции FILTER:
FILTER (диапазон; условие1; [условие2;…]):
- диапазон : это диапазон ячеек, который вы хотите отфильтровать.
- условие1 : это столбцы / строка (соответствующие столбцу / строке набора данных), которая возвращает массив ИСТИНА / ЛОЖЬ. Он должен быть того же размера, что и диапазон
- [условие2] : это необязательный аргумент, который может быть вторым условием, которое вы проверяете в формуле. Это снова может быть столбец / строка (соответствующая столбцу / строке набора данных), которая возвращает массив ИСТИНА / ЛОЖЬ. Он должен быть того же размера, что и диапазон.
Когда вы используете несколько условий, те результаты, которые возвращают истину для обоих условий, будут отфильтрованы.
Если вам интересно, как это работает, просмотрите пару примеров (перечисленных ниже), и станет ясно, как использовать функцию ФИЛЬТР в Google Таблицах.
Пример 1 — Фильтрация данных на основе одного условия
Предположим, у вас есть набор данных, показанный ниже, и вы хотите быстро отфильтровать все записи, в которых название штата — Флорида.
Следующая формула сделает это:
В этом примере я жестко запрограммировал значение, но вы также можете иметь это значение в ячейке, а затем ссылаться на эту ячейку. Например, если у вас есть текст Флорида в ячейке H1, вы также можете использовать следующую формулу:
Несколько вещей, которые нужно знать о функции FILTER.
Функция ФИЛЬТР в Google Таблицах возвращает массив значений, который распространяется на соседние ячейки (это называется динамическим массивом). Чтобы это сработало, вам нужно убедиться, что соседние ячейки (куда будут помещены результаты) должны быть пустыми.
Результат массива не был расширен, потому что он перезаписал данные в F3
И как только вы удалите заполненную ячейку, которая мешает функции FILTER выдать результат, она автоматически заполнит диапазон результатом.
Кроме того, результатом формулы FILTER является массив, и вы можете изменить часть массива. Это означает, что вы не можете изменить или удалить одну ячейку (или пару ячеек) в результате. Вам придется удалить весь результат формулы. Чтобы удалить результат, вы можете выбрать ячейку, в которой вы ввели формулу, а затем нажать клавишу удаления.
Пример 2 — Фильтрация данных на основе нескольких условий (условие И)
Вы также можете использовать функцию FILTER для проверки нескольких условий таким образом, чтобы она возвращала только те записи, в которых выполняются оба условия.
Например, предположим, что у вас есть приведенный ниже набор данных и вы хотите отфильтровать все записи, в которых штат Флорида и стоимость продажи превышает 5000.
Вы можете сделать это, используя приведенную ниже формулу;
Приведенная выше формула проверяет наличие двух условий (где штат Флорида, а стоимость продажи превышает 5000) и возвращает все записи, соответствующие этим критериям.
Точно так же, если вы хотите, вы можете иметь несколько условий в одной формуле ФИЛЬТРА.
Пример 3 — Фильтрация записей на основе нескольких условий (условие ИЛИ)
В приведенном выше примере я проверил два условия и вернул результаты, в которых оба условия ИСТИНА.
Вы также можете проверить условие ИЛИ в формуле FILTER.
Например, предположим, что у вас есть набор данных, показанный ниже, и вы хотите получить все записи для Калифорнии и Айовы. Это означает, что условием должно быть состояние штата Калифорния или Айова (что делает это условием ИЛИ).
Следующая формула сделает это:
В приведенной выше формуле в условии используется оператор сложения, чтобы сначала проверить оба условия, а затем добавить результат каждого из них. Поскольку эти условия возвращают массив или ИСТИНА и ЛОЖЬ, вы можете добавить их (поскольку ИСТИНА равно 1, а ЛОЖЬ равно 0 в Google Таблицах).
Это даст вам 0 (или ЛОЖЬ), если оба условия не выполняются, 1, если выполнено одно из двух условий, и 2, если оба условия выполнены.
И тогда формула FILTER вернет все записи, в которых условия возвращают значение больше 0.
Пример 4 — Фильтр 3 или 5 лучших записей по значению
Вы также можете использовать функцию FILTER, чтобы быстро получить 3 или 5 первых (или любое количество записей, которое вы выберете).
Например, предположим, что у меня есть набор данных, показанный ниже, и я хочу быстро получить записи для трех основных значений продаж.
Следующая формула сделает это:
В приведенной выше формуле используется функция LARGE (НАИБОЛЬШИЙ), чтобы получить третье по величине значение в наборе данных. Это значение затем используется в условии, чтобы проверить, больше ли значения в столбце C этому значению или нет.
Это вернет все записи, соответствующие критериям, которые будут тремя верхними записями.
Если вы хотите получить три нижние записи, вы можете использовать приведенную ниже формулу ФИЛЬТРА:
Пример 5 — СОРТИРОВКА отфильтрованных данных (с использованием комбинации ФИЛЬТРА и СОРТИРОВКИ)
До сих пор все примеры формул FILTER, которые мы видели, будут фильтровать данные в том же порядке, в котором они встречаются в наборе данных.
Но что, если вы хотите получить отсортированный набор данных.
Например, предположим, что вы фильтруете 5 самых популярных записей, было бы более полезно отсортировать их в порядке убывания (наибольший вверху).
Ниже приведена формула, которая фильтрует данные и отображает их в порядке убывания:
В приведенной выше формуле используется та же формула FILTER, которую мы использовали в предыдущем примере, для получения трех верхних записей на основе продажной стоимости.
И поскольку я хотел, чтобы конечный результат был отсортирован в порядке убывания, я заключил функцию FILTER в функцию SORT. Функция SORT (СОРТИРОВКА) использует результат, полученный по формуле ФИЛЬТР, и сортирует его на основе третьего столбца (который имеет значение продаж).
Третий аргумент функции SORT — FALSE, он указывает, что я хочу получить окончательный результат в порядке убывания. Если вы оставите поле пустым (или сделаете его ИСТИННЫМ), результат будет в порядке возрастания.
Пример 6 — Фильтрация всех записей ЧЕТНЫХ чисел (или записей НЕЧЕТНЫХ чисел)
Это не такой распространенный вариант использования, но это то, что мне приходилось делать, когда я получаю свои данные от кого-то другого, из базы данных или веб-страниц.
Часто данные, которые вам нужны, будут только в чередующихся строках (или в каждой третьей / четвертой / пятой строке), и вам нужно будет избавиться от лишних строк, чтобы вы могли собрать все полезные данные вместе.
В таком случае вы можете использовать функцию FILTER, чтобы быстро отфильтровать и собрать все строки с четными номерами вместе (или все строки с нечетными номерами вместе). И вы также можете изменить формулу, чтобы фильтровать каждую третью, четвертую или n-ю строку в Google Таблицах.
Предположим, у вас есть набор данных, как показано ниже, и вы хотите отфильтровать все четные строки в этом наборе данных.
Ниже приведена формула, которая отфильтрует все четные строки:
В приведенной выше формуле используется функция (ROW) СТРОКА, чтобы получить номера строк для всех строк в наборе данных. Затем он вычитает из него 1, поскольку наш набор данных начинается со второй строки и далее.
Теперь он использует функцию MOD для проверки следующего условия — MOD (ROW (A2: A11) –1,2) = 0)
Это вернет ИСТИНУ для всех строк с четными номерами и ЛОЖЬ для всех строк с нечетными номерами. И этот массив ИСТИНА и ЛОЖЬ используется функцией ФИЛЬТР для извлечения записей.
Точно так же, если вам нужны только пронумерованные записи ODD, вы можете использовать следующую формулу:
А если вы хотите отфильтровать каждую третью строку, вы можете использовать следующую формулу в Google Таблицах:
Вот некоторые из примеров, когда функция ФИЛЬТРА в Google Таблицах может сэкономить время в реальном времени. А когда вы комбинируете её с другими формулами, вы можете многое сделать с ней.
Докономист — блог, посвященный бизнесу, финансам и аналитике. Этот блог идеален для частных предпринимателей, экономистов, финансистов, менеджеров и всех, кто работает в электронных таблицах Excel и Google Docs.
Замечательная функция Фильтра (FILTER) в таблицах Гугла (Google Spreadsheets)
- Получить ссылку
- Электронная почта
- Другие приложения
У меня есть табличка Гугла, где я провожу различные тесты формул, доступных редактору. Ты можешь найти ее по ссылке.
Перейдя в эту таблицу, ты обнаружишь несколько примеров использования функции FILTER
Синтаксис ее предельно прост, вот что я скопировал из справки:
Столбец или строка, которые содержат истинные и ложные значения, соответствующие первой строке или первому столбцу в диапазоне, либо формула массива для оценки истинного или ложного значения.
Дополнительные строки или столбцы, содержащие логические значения TRUE или FALSE. Они указывают на то, пройдет ли строка или столбец через фильтр. Вместо логических значений можно ввести формулу массива. Все условия должны касаться либо только столбцов, либо только строк. Смешанные условия работать не будут.
И / ИЛИ
Условий может быть много. Плюс, как мне недавно показали, могут быть условия ИЛИ, если написать так:
то эта запись означает, что необходимо выбрать все из диапазона [А:С], где [В:В] равняется пяти ИЛИ в колонке [С:С] есть слово "ок!". По-моему, это замечательное свойство функции.
Подтяни картинки и ссылки
Еще для меня сюрпризом была возможность подтягивать картинки и ссылки. Этого не умеют запросы QUERY.
Для начала разберем, что в Доках можно вставить картинку в ячейку по ее ссылке. Это замечательно, потому что это тоже делается при помощи функции, например:
= IMAGE ( "https://upload.wikimedia.org/wikipedia/commons/3/3e/Vista-folder_home.svg" )
такая функция вставит картинку из Викимедии в твою книгу Гугл.
И. функция фильтра умеет перетягивать картинки, а так же ссылки, созданные формулой HYPERLINK. Вот наглядный результат:
- Получить ссылку
- Электронная почта
- Другие приложения
Комментарии
Связанные выпадающие списки в табличках Google
Ты получишь возможность сделать в табличках Google свои связанные выпадающие списки: Всего в несколько простых шагов. Шаг 1. Скачай себе файл с примером Перейдя по ссылкам ниже, ты получишь доступ к готовым файлам: Базовая версия (описана в видео ) Новая версия — доработанная и улучшенная. Совет. Эти файлы открыты для просмотра, но не для редактирования. Поэтому сразу создай свою копию: меню Файл → Создать копию . Далее об этом я расскажу подробнее. Сравни 2 версии скрипта: Базовая версия Новая версия средняя оценка пользователей ★★★☆☆ - 3,4 ★★★★★ - 4,7 оценить оценить Среднее время выполнения в секундах 0,3 0,4 бесплатно и не для продажи Легко увеличить к-во связанных выпадающих списков автоматически заполняет единственное значение Работает с дублями Не нужно сортировать исходную таблицу Работает с числами и с текстом Работает с дробными десятичными числами функция "умного"
Запросы (query) в Google Docs, как инструмент для профессиональной разработки отчетов и приложений
Многие пользуются сводными таблицами в Эксель и им приходится сталкиваться со следующими недостатками: таблицы обновляется вручную. После того, как данные в исходной таблице изменились, информация в сводной таблице уже неверна! невозможно сделать прямую ссылку на ячейку из сводной таблицы. То есть нельзя подключить данные их нее в другие расчеты Негибкость расчетов самой сводной таблицы. Другими словами, если я хочу сделать что-либо посложнее простого выбора полей и суммы по ним, мне тяжело будет настроить и получить то, что я хочу. В этой статье ты увидишь, как все эти проблемы решаются посредством всего оной формулы в табличном редакторе Google Docs! Она позволяет тебе: Самому руководить выборкой данных Сортировать Выбирать часть данных по любым критериям и условиям Группировать и суммировать, рассчитывать агрегированные показатели (сумма, среднее) Располагать данные по столбцам Можно даже добавлять собственные колонки, которых не было в исходнике! Получить ди
Читайте также: