Условие «соответствует» выполняет поиск по регулярному выражению. Например, этот запрос

выберите A, B, C, D, E, F, где A соответствует ‘.*ana’

При этом нам нужно будет поместить его в функцию ЗАПРОС Google Таблиц с помощью формат:

=query([range],»[SQL query]'»)

Дает нам:

Условие «нравится»

Условие «нравится» допускает два подстановочных знака, что позволяет уточнить поиск. Подстановочный знак «%» служит заполнителем для нуля или более символов любого типа, а подстановочный знак «_» служит заполнителем для одного символа, что позволяет указать длину ожидаемого результата в виде слова. В качестве примера мы сравним два подстановочных знака. Этот

выберите A, B, C, D, E, F, где A, например, «Индия%»

При этом нам нужно будет поместить его в функцию QUERY Google Таблиц в формате:

=query([range ],»[SQL query]'»)

В результате вы получите:

Хотя этот

выберите A, B, C, D, E, F, где A, например, «Индия_»

При этом нам нужно будет поместить его в функцию QUERY Google Sheets в формате:

=query([range],»[SQL query]'»)

В результате вы получите следующее:

Как видите, предыдущий запрос искал в столбце A слова, начинающиеся с «Индия», и перечислял их независимо от количества последующих символов. Последний запрос искал в столбце А слова, начинающиеся со слова «Индия», за которыми следует один символ. Поскольку ни одна строка не содержала слова в столбце A, удовлетворяющего такому условию.

Объединение двух или более отдельных ключевых слов

Если вы хотите объединить два или более отдельных ключевых слова в одном запросе, существует особый способ сделать это. В нашем примере мы хотим перечислить названия штатов США, происходящие из английского или испанского языков. Для этого напишем следующий запрос:

выберите A, B, C, D, E, где D содержит «испанский» или D содержит «английский».

=query([range],»[SQL query]'»)

И вот результат, который мы получим:

Здесь можно еще многое добавить…

…прежде чем мы сможем эффективно начать используя это для нашего анализа. Поиск по ключевым словам является важной частью анализа отзывов клиентов, который даст нам важные показатели, такие как уровень удовлетворенности и чистая оценка промоутера. Беглый взгляд на принципы поиска по ключевым словам покажет вам, насколько сложной и запутанной является обработка естественного языка.

Вместо того, чтобы изучать совершенно новую область только для того, чтобы узнать отзывы клиентов, подумайте о том, чтобы попробовать Lido . С помощью нескольких щелчков мыши на своем ноутбуке теперь вы можете получить доступ ко всем соответствующим показателям, не утруждая себя доступом к базам данных SQL ваших платформ электронной коммерции и последующим кодированием формул для их обработки. Пусть наша платформа сделает все за вас!

Изучите самую мощную функцию в Sheets

Функция запроса Google Sheets — это самая мощная и универсальная функция в Google Таблицах.

Он позволяет вам использовать команды данных для управления вашими данными в Google Sheets, и это невероятно универсальный и мощный .

Эта единственная функция выполняет работу многих других функций и может воспроизводить большую часть функций сводных таблиц.

Это видео — урок 14 из 30 моего бесплатного курса Google Sheets: Advanced Formulas 30 Day Challenge

Google Sheets QUERY Синтаксис функции

=QUERY(данные, запрос, [заголовки])

Требуется 3 аргумента:

1. диапазон данных, которые вы хотите проанализировать
2. запрос, который вы хотите выполнить, заключенный в кавычки
3. необязательное число, указывающее, сколько строк заголовков содержится в ваших данных

Вот пример функции ЗАПРОС:

=ЗАПРОС(A1:D234,"ВЫБЕРИТЕ B, D",1)

Диапазон данных в этом примере: A1:D234

Оператор запроса — это строка в кавычках, выделенная зеленым цветом. В этом случае он сообщает функции выбрать столбцы B и D из данных.

Третий аргумент — это число 1, которое сообщает функции, что исходные данные имели одну строку заголовка. Этот аргумент является необязательным и, если он опущен, будет автоматически определен Sheets.

Это одна из функций Google, которые недоступны в других инструментах для работы с электронными таблицами.

ЗАПРОС Функция Примечания

Ключевые слова не чувствительны к регистру, поэтому вы можете написать «ВЫБРАТЬ» или «выбрать», и оба варианта будут работать.

Однако буквы столбцов должны быть в верхнем регистре: A, B, C и т. д. иначе вы получите ошибку.

Ключевые слова должны стоять в следующем порядке (конечно, вам не обязательно использовать их все):

• выберите
• где
• группа по
• заказать по
• ограничение
• этикетка

Вы увидите примеры всех этих ключевых слов ниже.

Есть еще несколько ключевых слов, но они менее распространены. Полный список смотрите здесь.

Google Sheets QUERY Function Template

Нажмите здесь, чтобы открыть копию только для просмотра >>

Вы можете сделать копию: Файл > Создать копию…

Если вы не можете получить доступ к шаблону, он может быть из-за настроек Google Workspace вашей организации. Если вы щелкните ссылку правой кнопкой мыши и откроете ее в окне инкогнито, вы сможете ее увидеть.

Google Sheets QUERY Примеры функций

Если вы хотите следовать решениям, сделайте копию приведенного выше шаблона Google Sheet.

Вот как выглядят наши исходные данные:

В этом руководстве я использовал именованный диапазон для идентификации данных, что значительно упрощает и упрощает использование в функции ЗАПРОС. Не стесняйтесь также использовать именованный диапазон «страны», который уже существует в шаблоне.

Если вы не знакомы с именованными диапазонами, вот как их создавать:

Выберите диапазон данных и перейдите в меню:

Данные > Именованные диапазоны…

В правой части электронной таблицы появится новая панель. В первом поле ввода введите имя таблицы данных, чтобы к ней можно было легко обратиться.

SELECT All

Оператор SELECT * извлекает все столбцы из нашей таблицы данных.

В правой части таблицы (я использовал ячейку G1) введите следующую функцию QUERY Google Sheets, используя обозначение именованного диапазона:

=ЗАПРОС(страны,"SELECT *",1)

Примечания: если вы не хотите использовать именованные диапазоны, то это не проблема. Ваша формула ЗАПРОСА будет выглядеть следующим образом:

=ЗАПРОС(A1:D234,"ВЫБЕРИТЕ *",1)

В оставшейся части этой статьи я использовал именованный диапазон «страны», но вы можете продолжить используя вместо него ссылку на обычный диапазон A1:D234.

Результатом этого запроса снова является наша полная таблица, потому что SELECT * извлекает все столбцы из таблицы стран:

Вот это да! Вы написали свой первый ЗАПРОС! Похлопайте себя по спине.

SELECT Определенные столбцы

Что, если мы не хотим выбирать каждый столбец, а только определенные?

Измените функцию ЗАПРОС Google Таблиц, чтобы она читалась так:

= ЗАПРОС(страны,"ВЫБЕРИТЕ B, D",1)

На этот раз мы выбрали только столбцы B и D из исходного набора данных , поэтому наш вывод будет выглядеть так:

Важное примечание

Помните, наша функция ЗАПРОС находится в ячейке G1, поэтому вывод будет в столбцах G, H, I и т. д.

B и D внутри оператора выбора QUERY ссылаются на ссылки на столбцы в исходных данных.

Ключевое слово WHERE

Ключевое слово WHERE указывает условие, которое должно быть выполнено. Он фильтрует наши данные. Он идет после ключевого слова SELECT.

Измените функцию ЗАПРОС в Google Таблицах, чтобы выбирать только страны с населением более 100 миллионов человек:

= ЗАПРОС(страны,"ВЫБЕРИТЕ B, D, ГДЕ D > 100000000",1)

Наша выходная таблица:

Давайте посмотрим еще один пример ключевого слова WHERE, на этот раз выбрав только европейские страны. Измените формулу на:

=ЗАПРОС(страны,"ВЫБЕРИТЕ B, C, D, ГДЕ С = 'Европа'", 1)

Обратите внимание, что слово «Европа» заключено в одинарные кавычки. Сравните это с числовым примером, в котором число не заключалось в одинарные кавычки.

Теперь таблица результатов:

ORDER BY Keyword

Ключевое слово ORDER BY сортирует наши данные. Мы можем указать столбцы и направление (по возрастанию или по убыванию). Он идет после ключевых слов SELECT и WHERE.

Давайте отсортируем наши данные по населению от меньшего к большему. Измените формулу, добавив следующее ключевое слово ORDER BY, указав восходящее направление с помощью ASC:

=ЗАПРОС(страны,"SELECT B, C, D ORDER BY D ASC",1)

Выходная таблица:

Измените формулу ЗАПРОС, чтобы отсортировать данные по странам в порядке убывания, Z – A:

= ЗАПРОС(страны,"ВЫБЕРИТЕ B, C, ORDER BY B DESC", 1)

Таблица вывода:

Ключевое слово LIMIT

Ключевое слово LIMIT ограничивает количество возвращаемых результатов. Он идет после ключевых слов SELECT, WHERE и ORDER BY.

Давайте добавим в нашу формулу ключевое слово LIMIT и вернем только 10 результатов:

=ЗАПРОС(страны,"ВЫБЕРИТЕ B, C, D ORDER BY D ASC LIMIT 10",1)

Теперь это возвращает только 10 результатов из наших данных:

Арифметические функции

Мы может выполнять стандартные математические операции над числовыми столбцами.

Итак, давайте разберемся, какой процент от всего населения мира (7,16 миллиарда) приходится на каждую страну.

Мы собираемся разделить столбец населения на общее количество (7 162 119 434) и умножить на 100, чтобы вычислить проценты. Итак, изменим нашу формулу, чтобы она читалась:

=ЗАПРОС(страны,"ВЫБЕРИТЕ B, C, (D / 7162119434) * 100",1)

Я разделил значения в столбце D на общую численность населения (в скобках), а затем умножил на 100, чтобы получить процент.

Выходная таблица на этот раз:

Примечание. Я применил форматирование к выходному столбцу в Google Таблицах, чтобы отображались только 2 десятичных знака.

LABEL Ключевое слово

Этот заголовок для арифметического столбца довольно уродлив, верно? Ну, мы можем переименовать его, используя ключевое слово LABEL, которое стоит в конце оператора QUERY. Попробуйте это:

=ЗАПРОС(страны,"ВЫБЕРИТЕ B, C, (D/7162119434) * 100 LABEL (D/7162119434) * 100 'Процент'",1)

=ЗАПРОС(страны,"ВЫБЕРИТЕ B, C , (D / 7162119434) * 100 LABEL (D / 7162119434) * 100 'Процент' ",1)

Функции агрегирования

Мы можем использовать в наших расчетах другие функции, например, мин, макс и среднее.

Чтобы рассчитать минимальную, максимальную и среднюю численность населения в наборе данных вашей страны, используйте агрегатные функции в своем запросе следующим образом:

=ЗАПРОС(страны,"ВЫБЕРИТЕ максимальное (D), минимальное (D), среднее (D)", 1)

Выходные данные возвращают три значения — максимальное, минимальное и среднее заполнение набора данных, как показано ниже :

GROUP BY Ключевое слово

Хорошо, сделайте глубокий вдох. Это самая сложная концепция для понимания. Однако, если вы когда-либо использовали сводные таблицы в Google Таблицах (или Excel), то вас это должно устраивать.

Ключевое слово GROUP BY используется с агрегатными функциями для суммирования данных в группы, как это делает сводная таблица.

Подведем итоги по континентам и посчитаем, сколько стран на континенте. Измените формулу запроса, включив в нее ключевое слово GROUP BY, и используйте агрегатную функцию COUNT для подсчета количества стран следующим образом:

=ЗАПРОС(countries,"SELECT C, count(B) GROUP BY C",1)

Обратите внимание: каждый столбец в операторе SELECT (т. е. перед GROUP BY) должен быть либо агрегирован (например, counted, min, max), либо стоять после ключевого слова GROUP BY (например, столбец C в данном случае).

Результат этого запроса:

Давайте рассмотрим более сложный пример, включающий множество различных типов ключевых слов. Измените формулу, чтобы она выглядела следующим образом:

=ЗАПРОС(страны,"ВЫБЕРИТЕ C, количество(B), мин.(D), макс.(D), среднее(D) СГРУППИРОВАТЬ ПО КОРДЕРУ ПО ср.(D) DESC LIMIT 3" ,1)

Это может быть легче читать, разбитое на несколько строк:

=ЗАПРОС(страны,
"ВЫБЕРИТЕ C, количество(B), мин.(D), макс.(D), среднее(D)
ГРУППА ПО C
ПОРЯДОК ПО avg(D) DESC
LIMIT 3",1)

Это суммирует наши данные для каждого континента, сортирует их по наибольшему и наименьшему среднему населению и, наконец, ограничивает результаты только тремя верхними.

Результат этого запроса:

Advanced Google Sheets QUERY Function Techniques

Как добавить итоговую строку в ваши формулы запроса

Как использовать даты в качестве фильтров в ваших формулах запроса

Есть еще 4 ключевых слова, которые не были рассмотрены в этой статье: PIVOT, СМЕЩЕНИЕ, ФОРМАТ И ОПЦИИ

Кроме того, доступно больше функций для обработки данных, чем мы обсуждали выше.