Функция QUERY — одна из лучших и наиболее полезных функций в Google Таблицах. Если использовать весь свой потенциал, он может делать действительно удивительные вещи в Google Таблицах.

В этом уроке я объясню функцию QUERY Google Таблиц и покажу вам множество практических примеров того, как вы можете использовать ее в Google Sheets.

Прежде чем я перейду к синтаксису функции или примерам, позвольте мне попытаться объяснить полезность функции QUERY.

Когда использовать функцию QUERY в Google Таблицах

Функция QUERY позволяет получать определенные данные из табличного набора данных.

Например, предположим, что у вас есть огромные данные — скажем, данные о розничных продажах многих магазинов. Вы можете использовать функцию запроса в Google Таблицах, чтобы быстро получить следующие данные:

  • Все данные о продажах Магазина А.
  • Все данные о продажах товара A из магазина B
  • Все распродажи в январе месяце

Вы также можете использовать функцию запроса для извлечения подмножества данных из основного набора данных. Например, если в ваших данных 10 столбцов, вы можете использовать функцию QUERY в Google Таблицах, чтобы извлечь только 3 или 4 столбца, которые вам нужны.

Мы увидим, как все это сделать, на примерах.

Давайте сначала начнем с синтаксиса функции QUERY в Google Sheet.

Функция QUERY — Синтаксис

Вот синтаксис функции запроса в Google Таблицах:

QUERY(data, query, [headers])
  • data — это диапазон данных, в котором вы хотите выполнить запрос. Если взять пример данных о розничных продажах, это будет диапазон ячеек, содержащий все данные о продажах.
  • query — это выражение запроса, которое вы будете использовать для получения необходимых данных. Это выражение запроса написано на языке запросов API визуализации Google. Это также очень похоже на выражение запроса, используемое в SQL. Это всегда пишется в двойных кавычках. Но не беспокойтесь об этом. Я покажу вам, как создавать эти выражения позже в этом уроке.
  • [headers] — необязательный аргумент. Здесь вы указываете, сколько строк заголовков содержится в вашем наборе данных. Если этот параметр опущен, Google Таблицы угадывают его на основе содержимого набора данных.

Понимание выражений функций QUERY — с примерами

Лучший способ понять функцию QUERY в Google Таблицах — это узнать, как использовать выражения с набором данных.

Для этого урока я использую набор данных самых кассовых голливудских фильмов. Я получил за это из Википедии (конечно же!). Обратите внимание, что валовые сборы не скорректированы с учетом инфляции (так что не ужасайтесь, когда вы увидите в списке «Человек-паук 3» и «Судьба яростного»).

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

Набор данных содержит данные для 50 фильмов в 4 столбцах — рейтинг, название, общий сбор и год выпуска.

Первое, что я сделал бы с этим набором данных, — это создать для него именованный диапазон. Создание именованного диапазона позволяет мне легко обращаться к этим данным из других листов. Обратите внимание: если вы не создаете именованный диапазон, вам придется использовать ссылку на ячейку вместе с именем листа.

Например, вместо использования = Data! A1: D51 я могу использовать = MovieData (как мы увидим в примерах).

Теперь давайте углубимся и узнаем, какие интересные вещи можно делать с помощью функции запросов.

Пример 1. Извлечение некоторых столбцов из набора данных (предложение Select)

Если вы хотите извлечь часть набора данных из всего набора данных, вы можете использовать выражение SELECT.

Вот формула, которая извлечет рейтинг (в столбце A) и название фильма (в столбце B).

=QUERY(MovieData,"Select A, B")

Обратите внимание, что каждая функция QUERY должна содержать выражение Select. «SELECT» сообщает функции запроса, какие столбцы данных следует учитывать при извлечении данных.

Выражение Select и любое другое выражение в аргументе запроса всегда заключаются в двойные кавычки.

В этом случае мы указали, что столбцы A и B должны быть извлечены полностью.

Вот результат формулы:

Обратите внимание, что я извлек результат на другой лист.

Кроме того, формула будет возвращать только значения, но не форматирование.

Точно так же, если вы хотите получить названия фильмов и год выпуска, вы можете использовать следующую формулу:

=QUERY(MovieData,"Select B, D")

Пример 2 — Извлечение данных в возрастающем / убывающем порядке (условие порядка)

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

Например, в этом примере данных фильма давайте посмотрим, как запросить данные и получить столбцы для «Заголовок» и «Год выпуска», а затем отсортировать их в порядке убывания (так, чтобы фильмы, выпущенные последними, показывались первыми).

Вот формула для получения данных в порядке убывания:

=QUERY(MovieData,"Select B, D Order by D Desc")

В приведенной выше формуле, помимо предложения Select, есть новое предложение — Order .

Предложение Order указывает запросу на сортировку данных в порядке возрастания / убывания на основе указанного столбца (который в данном случае является столбцом D).

Если бы я хотел отсортировать данные в порядке возрастания, формула была бы такой:

=QUERY(MovieData,"Select B, D Order by D Asc")

Это позволит «Jurrasic Park» оказаться на вершине рейтинга, выпущенного в 1993 году.

 

Пример 3 — Извлечение и фильтрация данных с использованием условий 

До сих пор мы извлекали все столбцы с помощью функции запроса.

Теперь давайте посмотрим, как извлечь данные на основе условия.

Например, предположим, что я хочу извлечь данные только для тех фильмов, которые были выпущены после 2000 года.

Вот формула, которую я могу использовать для этого:

=QUERY(MovieData,"Select B, D where D>2000")

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

Это предложение «where», которое позволяет фильтровать данные.

Если вы хотите получить данные о фильмах, выпущенных после 2010 года или до 1995 года, вы можете использовать следующую формулу:

=QUERY(MovieData,"Select B, D where D>2010 or D<1995")

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

=QUERY(MovieData,"Select B, D where D>2010 or D<1995 order by D Asc")

Обратите внимание, что мы использовали все три рассмотренные выше предложения — Select, Order и Where.

Вот результат последней использованной нами формулы:

Предложение Where может быть полезно в тех случаях, когда у вас есть категория, которую вы хотите отфильтровать. Например, если у меня также есть жанр фильма в этом списке, и я хочу отфильтровать данные по жанру, я могу указать его с помощью предложения «Where».

Точно так же я могу фильтровать названия фильмов на основе общей коллекции. Например, достаньте все фильмы, которые собрали более миллиарда долларов.

Еще один отличный способ использовать предложение «Where» — это использовать его с раскрывающимися списками.

Например, если у меня есть жанры, я могу указать эти жанры в раскрывающемся списке в ячейке и использовать ссылку на ячейку с предложением where.

Пример 4 — Группировка данных с помощью функции запроса (предложение Group)

Вы также можете сгруппировать данные с помощью предложения Group в функции запроса.

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

=QUERY(MovieData,"Select D, Count(C) group by D")

Это даст результат, как показано ниже:

Если вы раньше работали со сводными таблицами Excel, вы можете увидеть, что выходные данные имеют аналогичную логику и формат.

Вы можете использовать предложение «Order» (как показано ниже), чтобы отсортировать данные и получить последний год вверху.

=QUERY(MovieData,"Select D, Count(C) group by D Order by D Desc")

Я надеюсь, что приведенные выше примеры помогут вам начать работу с функцией QUERY в Google Таблицах.

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