Рассказываем, как работать с данными и фильтровать их в таблицах по заданным условиям — тем, которые вам нужны.
Привет! Меня зовут Александр, я работаю в отделе пользовательских исследований. Раньше ни один мой рабочий день не проходил без Google Таблиц, теперь продолжаю использовать инструмент только в личных целях. В статье объясню, как и какие формулы использовать в таблицах, чтобы работать с большими объемами данных.
Еще больше полезных инструкций — в Академии Selectel.
Варианты работы с данными в таблицах
Представим, что у вас есть таблица, в которую вы собираете результаты опроса. Опрос все еще активен: новые ответы приходят в таблицу и меняют ее, но предварительные результаты нужны вам уже сейчас. К тому же хочется, чтобы формулы продолжили работать дальше — до окончания опроса. Есть несколько способов решить эту задачу.
Формула QUERY — ключ к поиску нужных данных
QUERY — это формула, написанная на языке запросов. Вы пишете обычную формулу в Google Таблицах (стандартно, начиная со знака “=”), а внутри нее используется язык, похожий на SQL.
Пример:
=QUERY(A1:D6;"select C, A, B order by B desc")
Первая часть =QUERY(A1:D6 — это просто функция и выбранный диапазон данных. Вторая часть — тот самый язык запросов. В приведенном примере используются операторы select (выбрать), order by (сортировать по) и ключевое слово desc (обратное направление сортировки).
Покажу на конкретном кейсе. Допустим, вам необходимо создать связанную таблицу с другим порядком столбцов. Данные должны отсортироваться по убыванию значения «Дата регистрации», причем сама дата отображаться не должна.
Применим формулу:
=QUERY(A1:D6;"select D, A, C order by B desc")
Формула вывела таблицу с тремя из четырех имеющихся столбцов: D — «Тип лица», A — «Клиент» и C — «Продукт». Здесь данные отсортированы по столбцу B в обратном порядке — по убыванию. Чтобы отсортировать по возрастанию, нужно удалить desc.
В контексте задачи про опрос и постоянное изменение таблицы, выделение фиксированного диапазона A1:D6 не подходит. Будут учитываться только изменения в области A1:D6. Та же формула выведет такой результат:
Если будет указан диапазон A:D, а данные будут отсортированы по умолчанию (т. е. по возрастанию), получим следующий результат:
Это произошло, потому что были учтены пустые строки, в которых нет данных. Если ключевое слово desc отсутствует, то по умолчанию таблица сортируется по возрастанию. Так как пустая ячейка — наименьшее возможное значение, то оно выводится формулой =QUERY(A:D;»select В, A, С order by B») в первую очередь.
Чтобы избежать этого, необходимо не учитывать пустые строки в исходной таблице. Этого возможно добиться двумя способами. Первый — просто удалить пустые строки. Новые строки с данными будут добавляться автоматически в конец таблицы. Второй — использовать фильтрацию данных.
Пример:
=QUERY(A:D;"select D, A, C where A is not null order by B")
Совет! Используйте защищенные диапазоны, которые нельзя редактировать — это обезопасит вас от случайного удаления данных.
Справка. Защищенные ячейки, листы и диапазоны позволяют запретить другим пользователям редактировать данные. Для этого нажмите Данные > Защитить листы и диапазоны. Выберите Добавить лист или диапазон.
Фильтрация данных
Часто в опросах присутствуют необязательные вопросы. Респондент может пропустить их и оставить без ответа. Чтобы отфильтровать только тех респондентов, кто ответил на вопрос, необходимо использовать ключевое слово WHERE и оператор is not со значением null (полный список ключевых слов — в документации). Так как же работает WHERE?
WHERE
WHERE — оператор в запросе QUERY, который используется для фильтрации строк по заданным условиям.
Он позволяет отфильтровать строки на основе условий, таких как равенство, больше или меньше, и другие логические операторы. WHERE фильтрует строки, которые соответствуют указанным критериям. Если условия не соблюдаются, строки исключаются из результата.
Например, с помощью вот этой формулы была выведена таблица только с теми клиентами, кто ответил на вопрос про опыт использования провайдеров (строки, в которых значение в столбце D было ненулевым, то есть содержащим ответ).
=QUERY(A:D;"select A, C, D where D is not null")
Вот что мы получили:
AND и OR
AND и OR — логические операторы, используемые в QUERY для комбинирования условий. Они позволяют создавать сложные условия фильтрации, комбинируя несколько критериев.
AND требует выполнения всех условий, а OR требует выполнения хотя бы одного из условий.
Можно использовать несколько операторов, объединяя их в один запрос. Например, если вы хотите посмотреть список клиентов, которые пользуются облаком Selectel и одновременно с этим не имеют опыта использования других провайдеров — примените следующую формулу:
=QUERY(A:D;"select A, C, D where D is not null and C = 'Облако Selectel'")
Операторы сравнения
Вместе с «И» и «ИЛИ» используется не только =. Можно подключать и другие операторы:
- больше >
- меньше <
- больше или равно >=
- меньше или равно <=
- не равно !=
Представим, что к таблице выше вы подгрузили данные о выручке за последний месяц. Получили следующее:
Задача: оставить только тех клиентов, кто пользуется облаком Selectel и имеет выручку за последний месяц больше или равную 10 000 ₽. Воспользуемся формулой:
=QUERY(A:D;"select * where C = 'Облако Selectel' and D >=10000")
Разберем эту формулу:
- select * означает то, что в исходной таблице будут выведены все столбцы;
- where C = ‘Облако Selectel’ — первое условие: клиент пользуется только Облаком Selectel;
- and — объединяет первое условие со вторым;
- D >=10000 — второе условие: выручка за последний месяц больше или равна 10 000 ₽.
А если коллега тоже использует вашу таблицу? Допустим, он пришел, чтобы отфильтровать всех клиентов, выручка которых за последний месяц была больше 50 000 ₽. Но он не знаком с тем, как работает ваша формула. Для этого случая пригодятся внешние ячейки.
Внешние ячейки
Внешние ячейки — любые пустые ячейки, в которых не указаны данные. Их можно использовать в QUERY-запросах для динамического задания параметров фильтрации и сортировки. Ячейки позволяют изменять параметры запроса, не редактируя саму формулу.
Значения из внешних ячеек передаются в QUERY-запрос, и формула обновляется автоматически при изменении содержимого ячеек.
Предположим, вам нужно создать динамическую (интерактивную) таблицу для коллег, которые бы могли отфильтровать данные по условию с произвольным значением. Как из примера выше, когда коллеге нужны все клиенты с выручкой более 50 000 ₽. Для этого нужно просто выбрать ячейку вне диапазона исходной таблицы (в нашем случае, это любая ячейка вне диапазона A:D), в которую каждый, у кого есть доступ к вашему документу, сможет вписать необходимое значение.
Сделаем это с ячейкой F2:
=QUERY(A:D;"select * where D > "&F2)
Примечание. В зависимости от того, какой формат данных вы используете во внешней ячейке, отличается синтаксис формулы. Для числового формата это “&[ячейка], а для текстового ‘“&[ячейка]&”’. Его мы разберем дальше.
Внешние ячейки удобно использовать с раскрывающимися списками, чтобы быстро фильтровать таблицы. Например, в таблице ниже ячейка D1 содержит список из названий продуктов: облако Selectel, выделенные серверы, объектное хранилище и т. д. Чтобы использовать внешнюю ячейку D1, следует добавить ее в формулу QUERY, используя синтаксис ‘»&[ячейка]&»‘.
Пример:
=QUERY(A:C;"select * where C = '"&D1&"'")
Формула выше позволяет отфильтровать только тех клиентов, кто использовал выделенные серверы, так как это значение ячейки D1.
В примере выше логический оператор = ищет строгое соответствие данному значению ячейки. Если в ячейке D1 содержалось бы значение «Выделенные» или «Выделенные серверы», то формула бы не вернула ничего, так как оператор чувствителен к регистру.
Для нестрогого поиска по тексту следует использовать операторы CONTAINS и LIKE.
CONTAINS и LIKE
Предположим, что вы решили изменить значение ячейки D1 и вписали туда «Облако», чтобы найти клиентов, которые его использовали. Результат будет такой:
В этом вам помогут операторы CONTAINS и LIKE, которые используются для фильтрации данных в запросах QUERY. Эти операторы позволяют отфильтровать строки по различным критериям, таким как наличие подстроки или соответствие шаблону.
CONTAINS ищет строки, содержащие заданную подстроку, а LIKE используется для поиска по шаблону с использованием подстановочных знаков.
Пример:
=QUERY(A:C;"select * where C contains '"&D1&"'")
Знаки подстановки
Подстановочные знаки, такие как % и _, используются вместе с оператором LIKE для создания шаблонов поиска. Они позволяют искать строки, соответствующие частичному совпадению или определенному шаблону. Знак % соответствует любой последовательности символов, а _соответствует любому одному символу.
Использование LIKE вместе со знаками подстановки выручит в ситуации с «бэкапами» и «фидбеком» (они же «бекапы» и «фидбэк»).
Простой пример: два клиента оставили фидбек про бэкапы. Этой формулой можно вывести обе строки вне зависимости от написания первой гласной буквы в слове «бэкап»:
=QUERY(A:D;"select * where D like '%б_кап%'")
А вот эта формула выведет одно конкретное написание, заданное условием:
=QUERY(A:D;"select * where D contains 'бекап'")
Посмотрим вывод формулы:
=QUERY(A:D;"select * where D like '%б_кап%'")
Преимущества использования QUERY для фильтрации данных:
- исходная таблица не изменяется,
- сохраняется структура данных,
- данные обновляются автоматически,
- нет необходимости применять фильтры, которые скрывают часть данных.
После того, как данные отфильтрованы и отсортированы, необходимо их агрегировать, то есть сгруппировать.
Хотите узнать, как с помощью QUERY посчитать среднюю выручку за период или найти количество пользователей определенного продукта? Читайте полную версию материала в Академии Selectel.
