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

2024 Автор: Abigail Brown | [email protected]. Последнее изменение: 2023-12-17 06:59

Функция ЗАПРОС позволяет извлекать информацию из диапазона или всего листа данных с помощью гибких команд запроса. Изучение того, как использовать функцию QUERY Google Sheets, дает вам доступ к мощному инструменту поиска.

Если вы когда-либо писали SQL-запросы для получения данных из базы данных, то вы узнаете функцию QUERY. Если у вас нет опыта работы с базами данных, функция ЗАПРОС очень проста в освоении.

Что такое функция QUERY?

У функции три основных параметра:

=ЗАПРОС(данные, запрос, заголовки)

Эти параметры довольно просты.

• Данные: Диапазон ячеек, содержащих исходные данные
• Запрос: оператор поиска, описывающий, как извлечь то, что вы хотите, из исходных данных
• Заголовки: необязательный аргумент, который позволяет объединить несколько заголовков в исходном диапазоне в один заголовок на целевом листе

Гибкость и мощность функции QUERY обусловлены аргументом Query, как вы увидите ниже.

Как создать простую формулу ЗАПРОСА

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

В следующих примерах используется статистика успеваемости SAT в средней школе США. В этом первом примере вы узнаете, как написать простую формулу ЗАПРОС, которая возвращает все средние школы и их данные, где «Нью-Йорк» стоит в названии школы.

1. Создать новый лист для размещения результатов запроса. В верхней левой ячейке введите =Query(. Когда вы это сделаете, вы увидите всплывающее окно с требуемыми аргументами, примером и полезной информацией о функции.

   

2. Далее, предполагая, что у вас есть исходные данные на Листе1, заполните функцию следующим образом:

   =Query(Sheet1!A1:F460, "ВЫБЕРИТЕ B, C, D, E, F, ГДЕ B LIKE '%New York%'")

   Эта формула включает следующие аргументы:

   • Диапазон ячеек: Диапазон данных от A1 до F460 на Листе 1
   • SELECT Statement: Оператор SELECT, который вызывает любые данные в столбцах B, C, D, E и F, где столбец B содержит текст со словом «Нью-Йорк». " в нем.

   

   Символ «%» - это подстановочный знак, который можно использовать для поиска частей строк или чисел в любом наборе данных. Если оставить «%» в начале строки, будет возвращено название любой школы, начинающееся с текста «Нью-Йорк».

3. Если вы хотите найти название конкретной школы из списка, вы можете набрать запрос:

   =Query(Sheet1!A1:F460, "ВЫБЕРИТЕ B, C, D, E, F, ГДЕ B='Средняя школа New York Harbour'")

   Использование оператора =находит точное совпадение и может использоваться для поиска совпадающего текста или чисел в любом столбце.

   

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

Используйте функцию QUERY с оператором сравнения

Операторы сравнения позволяют использовать функцию QUERY для фильтрации данных, которые не соответствуют условию.

У вас есть доступ ко всем следующим операторам в функции QUERY:

• =: Значения совпадают со значением поиска
• <: Значения меньше искомого значения
• >: Значения больше, чем искомое значение
• <=: Значения меньше или равны искомому значению
• >=: Значения больше или равны искомому значению
• и !=: Значение поиска и исходное значение не равны

Используя тот же пример данных SAT, приведенный выше, давайте посмотрим, как узнать, в каких школах средний балл по математике превышает 500 баллов.

1. В левой верхней ячейке чистого листа заполните функцию ЗАПРОС следующим образом:

   =Запрос(Лист1!A1:F460, "ВЫБЕРИТЕ B, C, D, E, F, ГДЕ E > 500")

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

   

2. Вы также можете использовать логические операторы, такие как AND и OR, для поиска нескольких условий. Например, чтобы получить баллы только для школ с более чем 600 тестируемыми и критическим средним значением от 400 до 600, вы должны ввести следующую функцию ЗАПРОС:

   =Запрос(Лист1!A1:F460, "ВЫБЕРИТЕ B, C, D, E, F, ГДЕ C > 600 И D > 400 И D < 600")

   

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

Расширенное использование функции QUERY

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

1. Использование GROUP в функции QUERY позволяет агрегировать значения в нескольких строках. Например, вы можете усреднить тестовые оценки для каждого учащегося, используя функцию ГРУППА. Для этого наберите:

   =Запрос(Лист1!A1:B24, "ВЫБРАТЬ A, СРЕДН.(B) СГРУППИРОВАТЬ ПО A")

   

2. Используя COUNT в функции QUERY, вы можете подсчитать количество школ со средним баллом по письму более 500, используя следующую функцию QUERY:

   =ЗАПРОС(Лист1!A2:F460, "ВЫБРАТЬ B, СЧИТАТЬ (F) СГРУППИРОВАТЬ ПО B")

   

3. Используя ORDER BY в функции QUERY, вы можете найти школы с максимальными средними баллами по математике и упорядочить список по этим баллам.

   =ЗАПРОС(Лист1!A2:F460, "ВЫБРАТЬ B, МАКС (E) СГРУППИРОВАТЬ ПО ГРАНИЦЕ ПО МАКС (E)")