Как сортировать и фильтровать данные в Гугл Таблице
Здравствуйте, друзья! Чаще всего Гугл Таблицы заполняют большим количеством данных и, в результате, найти нужные строки или столбцы, например, с похожими значениями, очень сложно. Но разработчики это предусмотрели и, так же, как и в Excel, добавили все необходимые функции, которые помогут решить данную проблему.
В этой статье мы это и будем рассматривать. Разберемся, как сделать сортировку строк в порядке возрастания или убывания, а если нужно оставить на виду только определенные данные, тогда лучше воспользоваться фильтром. О нем мы также поговорим и расскажу, чем отличается обычный фильтр от режима фильтрации в Google Таблице.
Как делается сортировка
Сортировку следует использовать, если нужно упорядочить данные в таблице. Например, расположить цифры от большего к меньшему значению, или, в зависимости от первой буквы, разместить слова в ячейках по алфавиту.
Выделите нужный диапазон, откройте вкладку «Данные» и выберите пункт «Сортировка диапазона».
Если выделили данные из таблицы вместе с заголовком, тогда отметьте галочкой «Данные со строкой заголовка». Дальше выберите по какому столбцу нужно сортировать. Например, отсортирую таблицу по Стоимости – это столбец В, значит его и выбираю. Затем отметьте маркером, как нужно все сделать по возрастанию или убыванию.
Здесь есть возможность «Добавить еще один столбец». Это нужно, если вы хотите отсортировать сначала по цене, а потом, к примеру, по наличию на складе. Жмем по кнопке «Добавить…», но уже выбираем сортировку по столбцу С.
Когда все сделаете, жмите «Сортировать».
После этого получите желаемый результат. В примере я отсортировала овощи по цене.
Чтобы снова вернуть все как было, нажмите стрелку «Отмена действий» вверху или комбинацию клавиш «Ctrl+Z».
Чтобы таблица изменялась правильно, нужно выделять все столбцы, а, например, не только В, если я хочу отсортировать по Стоимости. Потому что если выделить только В, то после сортировки данные в столбцах А и С не будут ему соответствовать, поскольку цифры встанут в порядке возрастания, а данные в ячейках А и С так и останутся на местах.
Как сделать фильтр
Фильтрацию данных можно использовать, когда введено их много, но оставить на данный момент нужно только определенные. Например, чтобы отобразились те товары, стоимость которых не превышает 130.
Выделяем блоки в нужном столбце вместе с заголовком таблицы, открываем вкладку «Данные» и жмем «Создать фильтр».
В ячейке с заголовком появится кнопка с полосками – она нужна, чтобы настроить фильтр. Нажмите на нее, чтобы открыть окно для установки параметров. Разберем сначала вариант «Фильтровать по условию».
Если у вас создана большая таблица и удобнее было бы, чтобы ее шапка всегда оставалась на виду, можете закрепить строки в Гугл Таблице.
В первом поле выберите подходящее условие: отобразить только незаполненные ячейки, текст в которых начинает или заканчивается на…, число меньше или равно указанному и прочее.
Во втором поле нужно ввести данные для выбранного условия. Например, посмотрим сколько у нас овощей, цена которых больше или равна 150. Затем жмите «ОК» и смотрите результат.
Оставленные после применения фильтра строки вы также можете отсортировать. В окне для установки параметров фильтра вверху есть два нужных пункта.
Если выберите вариант «Фильтровать по значению», то уберите галочки напротив тех пунктов, которые нужно скрыть, и жмите «ОК».
Кнопка «Выбрать все» позволит всё отметить галочками, а «Очистить» наоборот, уберет галочки напротив всех пунктов.
Фильтр по цвету
Если в вашей таблице есть ячейки с данными, закрашенные в определенный цвет, и вас интересует, как сделать фильтр по цвету в Гугл Таблице, тогда сначала нужно для каждого цвета определить его код, а к нему уже можно применить фильтр.
Встроенной формулы, которая бы нам подошла, нет, поэтому необходимо добавить пользовательскую функцию. Откройте вкладку «Инструменты» и выберите из списка «Редактор скриптов».
В новой вкладке браузера откроется следующее окно, для создания нового проекта. Удалите весь написанный код – выделите его и жмите «Delete».
Дальше в окно нужно вставить код, обведенный прямоугольником на скриншоте ниже. Сохраните изменения, нажав на дискету.
Придумайте имя для проекта и жмите «ОК». После этого, вкладку можете закрывать.
Удобнее всего выводить код цвета в столбце, находящимся рядом с закрашенными ячейками. Поэтому ставим курсор в D2 и пишем следующую формулу:
Здесь С2 – это адрес ячейки, залитой цветом, а Е1 – нужна для обновления данных (можете указать адрес любой другой, но знак доллара не убирайте), числа напишите любые.
Выделите D2 и протяните формулу вниз, потянув за правый нижний угол. «Loading» означает, что идет расчет.
Когда подсчет закончится и ячейках появится код цвета, соответствующий фону в соседнем блоке.
Теперь можно применять к ним фильтр. Выделяем весь диапазон.
Откройте вкладку «Данные». Если у вас уже есть в таблице фильтр, то его нужно отключить, чтобы появилась кнопочка «Создать…», которая нам нужна.
Для того, чтобы убрать фильтр в Гугл Таблице необходимо на упомянутой вкладке нажать кнопку «Отключить…».
Когда в заголовке появится кнопка, нажмите на нее. В окне параметров выберите «По значению» и оставьте галочки только напротив тех кодов, что соответствуют нужным цветам. Для применения жмите «ОК».
Вот теперь у нас остались в таблице только те строки, ячейки в которых залиты зеленым цветом.
Чтобы столбец с кодами не мешался, его можно сгруппировать. А когда он понадобится, достаточно будет просто развернуть его, нажав на знак «+».
Теперь расскажу, зачем нужна ячейка Е1. Она понадобится для пересчета. Например, у вас залиты цвета, вы прописали формулу и определились коды. Если нужно заменить некоторые цвета, то код автоматически не просчитается. Для этого удалите число из Е1 и нажмите «Enter». Теперь формула будет пересчитана. Когда снова поменяете цвет заливки, напишите что-то в Е1, например. 123, и кода опять поменяются. И делать так нужно постоянно, как меняете фон блоков.
Создание режима фильтрации
Мы рассмотрели, как создать фильтр, но подойти такое может не всем. Фильтр хорошо использовать, если нужно:
- Чтобы данные так и остались отфильтрованные.
- Другие пользователи, у кого есть доступ к таблице, тоже видели результат фильтрации.
Если же использовать его нужно просто для работы и после того, как вы закончите, все необходимо отключить, то лучше воспользоваться режимом фильтрации. Преимущества перед обычным фильтром у него следующие:
- Можно создать несколько режимов.
- Каждому из них придумать название.
- Возможность разным пользователям просматривать таблицу в разных режимах.
- Вы можете создать ссылку на отфильтрованную таблицу и отправить ее одному пользователю, а потом выбрать другой режим фильтрации и отправить ссылку уже другому человеку.
- Возможность создавать дубликаты режимов и немного изменять в них параметры.
- Если у вас нет прав на редактирование таблицы, но отфильтровать данные нужно, то будет создан временный режим.
Итак, фильтруйте данные в таблице, как нужно, и открывайте «Данные» – «Фильтры» – «Сохранить как…».
Поля слева и вверху станут серого цвета – значит мы находимся в режиме фильтрации. Давайте дадим таблице в таком виде определенное «Имя». Также будет указан «Диапазон», к которому применен фильтр.
Справа есть кнопка в виде шестеренки – это «Параметры». «Создать копию» – дублирование режима. Пригодится если хотите немного изменить текущий фильтр, но при этом старый оставить также нужно. «Удалить» – удаление режима. Чтобы выйти из данного режима и вернуться к нормальной таблице, просто нажмите на крестик справа.
Так можете создать сколько нужно режимов фильтрации, применяя фильтр к определенным данным таблицы. Чтобы посмотреть любые из отфильтрованных значений откройте вкладку «Данные» и выберите из сохраненного списка нужное название.
Отсортировать данные в таблице не сложно. С фильтром придется немного разобраться, особенно, если нужно отфильтровать по цвету ячейки или создать несколько режимов фильтрации. Но потратьте на это 20 минут и работать с Google Таблицей станет куда удобнее, если в ней будут оставаться только необходимые значения.
Фильтрация данных в Google Таблицах
Фильтрация данных в Google Таблицах
Фильтры
Google Таблицы позволяет применять к данным фильтры.
Благодаря фильтрам вы можете отфильтровать данные в вашей таблице, указав, какие именно сведения необходимо отобразить или наоборот скрыть, оставив на экране только подходящую информацию.
Для включения фильтров необходимо перейти в меню «Данные» — «Создать фильтр». При этом должны быть выделена любая ячейка внутри таблицы, которую вы собираетесь фильтровать.
После включения фильтра заголовки строк и столбцов будут подсвечены зеленым, а около каждого заголовка (первая строка) вашей таблице появится символ:
По нажатию на него вы сможете задать условия для фильтрации данных в вашей таблице.
В этом видео-уроке мы рассказали о фильтрации данных.
Фильтрация с помощью срезов
Инструмент «Срезы» позволяет фильтровать данные и представлять результаты в более удобном виде.
Для использования срезов необходимо перейти в меню Данные — Срез.
Срезы данных в Google Таблицах
Аналогичный инструмент существует в Excel.
Мы уже добавили данный инструмент в наши курсы по Google Таблицам.
Фильтрация по цвету
В Google Таблицах обновились инструменты фильтрации. Теперь отфильтровать данные можно с учетом цвета.
Для того, чтобы отфильтровать таблицу по цветам, предварительно необходимо включить фильтрацию: меню Данные — Создать фильтр.
Далее по клику на значок фильтрации в первой ячейке столбца необходимо выбрать пункт Фильтровать по цвету.
Доступна фильтрация по цвету заливки и по цвету текста, а также по отсутствию цвета (пункт Нет на картинке ниже).
Расписание ближайших групп:
Читайте также:
Соотношение русских и английских названий функций
Автор: Sreda31 · Published 20.02.2020 · Last modified 07.05.2022
Офлайн доступ к документам на Google Диске
Автор: Sreda31 · Published 09.06.2022
Установка обновлений Google Drive
Автор: Sreda31 · Published 27.09.2018 · Last modified 09.05.2022
Как использовать функцию ФИЛЬТР в Google Таблицах (примеры)
В Google Таблицах есть несколько отличных функций, которые могут помочь легко разрезать данные. Хотя в Google Таблицах есть встроенная функция фильтра, в вашем распоряжении также есть функция FILTER (ФИЛЬТР).
Функция FILTER , как следует из названия, позволит вам фильтровать набор данных на основе условия (или нескольких условий).
Например, если у вас есть список имен с названиями состояний и продажной стоимостью, вы можете использовать функцию FILTER в Google Таблицах, чтобы быстро получить все записи / назвать одно конкретное состояние (как показано ниже).
Одним из преимуществ использования функции FILTER по сравнению с обычным фильтром в Google Таблицах является то, что результаты функции FILTER являются динамическими. Если вы измените что-либо в исходных данных, полученные отфильтрованные данные автоматически обновятся.
Это делает функцию FILTER Google Таблиц отличным выбором при создании интерактивных отчетов или информационных панелей.
В этом руководстве я покажу вам, как работает функция ФИЛЬТР в Google Таблицах, а также расскажу о некоторых полезных примерах, которые можно использовать в повседневной работе.
Итак, давайте начнем с изучения синтаксиса этой функции.
Синтаксис функции фильтра Google Таблиц
Ниже приведен синтаксис функции FILTER:
FILTER (диапазон; условие1; [условие2;…]):
- диапазон : это диапазон ячеек, который вы хотите отфильтровать.
- условие1 : это столбцы / строка (соответствующие столбцу / строке набора данных), которая возвращает массив ИСТИНА / ЛОЖЬ. Он должен быть того же размера, что и диапазон
- [условие2] : это необязательный аргумент, который может быть вторым условием, которое вы проверяете в формуле. Это снова может быть столбец / строка (соответствующая столбцу / строке набора данных), которая возвращает массив ИСТИНА / ЛОЖЬ. Он должен быть того же размера, что и диапазон.
Когда вы используете несколько условий, те результаты, которые возвращают истину для обоих условий, будут отфильтрованы.
Если функция FILTER не может найти результат, соответствующий условию, она вернет ошибку # Н / Д.
Если вам интересно, как это работает, просмотрите пару примеров (перечисленных ниже), и станет ясно, как использовать функцию ФИЛЬТР в Google Таблицах.
Пример 1 — Фильтрация данных на основе одного условия
Предположим, у вас есть набор данных, показанный ниже, и вы хотите быстро отфильтровать все записи, в которых название штата — Флорида.
Следующая формула сделает это:
В приведенной выше формуле в качестве аргумента используется диапазон данных, а условие — B2: B11 = «Флорида». Это условие проверяет каждую ячейку в диапазоне B2: B11, и если значение равно Флориде, эта запись фильтруется, в противном случае — нет.
В этом примере я жестко запрограммировал значение, но вы также можете иметь это значение в ячейке, а затем ссылаться на эту ячейку. Например, если у вас есть текст Флорида в ячейке H1, вы также можете использовать следующую формулу:
Несколько вещей, которые нужно знать о функции FILTER.
Функция ФИЛЬТР в Google Таблицах возвращает массив значений, который распространяется на соседние ячейки (это называется динамическим массивом). Чтобы это сработало, вам нужно убедиться, что соседние ячейки (куда будут помещены результаты) должны быть пустыми.
Если какая-либо из ячеек не пуста, ваша формула вернет # ССЫЛКА! ошибка. Google Sheets также сообщает вам, почему выдает ошибку, показывая красный треугольник в правом верхнем углу ячейки, и когда вы наводите на него курсор, появляется сообщение:
Результат массива не был расширен, потому что он перезаписал данные в 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 Таблицах может сэкономить время в реальном времени. А когда вы комбинируете её с другими формулами, вы можете многое сделать с ней.
Применение фильтров для Google Таблиц
Гугл Таблицы позволяют организовывать Ваши данные, сортировать их по алфавиту и применять к ним фильтры, чтобы сузить разброс ненужных Вам данных и скрыть их из вида.
В этой статье Вы узнаете, как отфильтровать данные для отображения только нужной информации.
Создание фильтра
Для того, чтобы фильтр работал правильно, ваш лист должен содержать заголовок, чтобы определить имя каждого столбца.
1. Нажмите кнопку вид и наведите курсор мыши на кнопку закрепить . Выберите первую строку из появившегося меню.
2. Щелкните на ячейку, содержащую данные.
3. Щелкните кнопку Ф ильтр в меню Данные.
4. Появился выпадающий список со стрелкой в заголовке каждого столбца.
5. Щелкните стрелку выпадающего списка в столбце, которые Вы хотите фильтровать.
6. Нажмите очистить , чтобы убрать чеки от данных.
7. Выберите данные, которые Вы хотели бы фильтровать, затем нажмите кнопку ОК .
8. Данные будут отфильтрованы, временно скрывая любой контент, который не соответствует требованиям.
Применение нескольких фильтров
1. Щелкните стрелку выпадающего списка в столбцах, которые хотите фильтровать.
2. Проверьте или снимите флажки, в зависимости от данных, которые хотите фильтровать, затем нажмите ОК .
3. Новый фильтр будет применен.
Условное форматирование
Работать с большими объемами информации проще, если настроить правила условного форматирования. Цвет текста или фона в ячейках, строках и столбцах Гугл Таблиц будет меняться при вводе определенных данных. Для того, чтобы установить правила:
1. Выделите нужные ячейки.
2. Нажмите Формат и в открывшемся меню выберите условное форматирование .