QUERY + IMPORTRANGE пример использования в Google Таблицы

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

Функция IMPORTRANGE позволяет вам импортировать данные из другой таблицы по её URL-адресу (идентификатору таблицы) и указанию диапазона. Это удобно, если у вас есть данные, которые нужно использовать из другой таблицы.

Пример синтаксиса функции IMPORTRANGE в Google Таблицы:

=IMPORTRANGE("URL_адрес_другой_таблицы", "Лист!Диапазон")
URL_адрес_другой_таблицы - это URL-адрес таблицы, из которой вы хотите импортировать данные.
Лист!Диапазон - это конкретный лист и диапазон данных, который вы хотите импортировать.

Не забывайте разрешить доступ к импортированным данным, следуя инструкциям, которые появятся при первом использовании функции IMPORTRANGE.

Функция QUERY позволяет вам выполнять SQL-подобные запросы (язык запросов API визуализации Google) к данным в Google Таблицы. С его помощью вы можете фильтровать, сортировать и агрегировать данные в более удобном формате.

Пример синтаксиса функции QUERY в Google Таблицы:

=QUERY("Диапазон", "SQL_подобный_запрос")
Диапазон - это диапазон данных к которым вы применяете запрос.
SQL_подобный_запрос - это SQL-подобный запрос (язык запросов API визуализации Google), который определяет, какие данные извлекаются.

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

Таблица с данными для примера использования функций QUERY и IMPORTRANGE в Google Таблицы

Создаём новую таблицу и для начала, давайте эти данные ипортируем с помощью функции IMPORTRANGE. Для этого прописываем аргумент нашей функции:

=IMPORTRANGE("1b4ECXFiPJT5OIhqYpFVvjgusNMrbFYBUveyRpt0YwQE";"A:F")

Первый агрумент это идентификатор таблицы откуда мы ипортируем данные, второй аргумент это диапазон, который нам необходим. Ставим A:F, чтобы при добавлении новых данных в таблицу откуда мы импортируем, автоматически подгружали в таблицу куда мы импортируем.

Импорт данных с использованием функции IMPORTRANGE в Google Таблицы

Всё отработало, как надо, но у функции IMPORTRANGE, есть своим минусы. Все столбцы, которые мы импортируем должны идти по порядку, мы не можем выбрать столбцы A, C и D (Артикул, Цена, Кол-во), нам придется импортировать A, B, C и D и потом дополнительно их искать способ избавиться от столбца B.

В данному случаи нам на помощью приходит функция QUERY, с ёё помощью мы можем выбрать, только те столбцы, которые нам необходимы. Давайте выберем столбцы A, C и D. Для этого нашу функции IMPORTRANGE вкладываем в функцию QUERY и вторым аргументом прописываем SQL-подобный запрос (язык запросов API визуализации Google) и с помощью оператора SELECT перечисляем поля, которые нам необходимы. На простом языке запросы будет звучать следующим образом:

Из диапазона( получили с помощью IMPORTRANGE) ВЫБЕРИ Столбец1, Столбец3, Столбец4.

=QUERY(IMPORTRANGE("1b4ECXFiPJT5OIhqYpFVvjgusNMrbFYBUveyRpt0YwQE";"A:F");"SELECT Col1, Col3, Col4")

Простой пример использования функции QUERY с использование оператора SELECT в комбинации IMPORTRANGE в Google Таблицы

Мы с вами посмотрели на данные и поняли, что нас интересуют стороки, где Цена больше 5000. Для решения данной задачи нам поможет дополнительный оператор WHERE функции QUERY.

Наш новый запрос звучит следующим образом:

Из диапазона( получили с помощью IMPORTRANGE) ВЫБЕРИ Столбец1, Столбец3, Столбец4 ГДЕ Столбец3 > 5000

=QUERY(IMPORTRANGE("1b4ECXFiPJT5OIhqYpFVvjgusNMrbFYBUveyRpt0YwQE";"A:F");"SELECT Col1, Col3, Col4 WHERE Col3 > 5000")

Пример использования функции QUERY с использованием операторов SELECT и WHERE в комбинации IMPORTRANGE в Google Таблицы

Смотрим дальше на данные и понимаем, что нас не интересуют строки, где количество больше или равно 20.

Наш новый запрос звучит следующим образом:

Из диапазона( получили с помощью IMPORTRANGE) ВЫБЕРИ Столбец1, Столбец3, Столбец4 ГДЕ Столбец3 > 5000 AND Столбец3 >= 20

=QUERY(IMPORTRANGE("1b4ECXFiPJT5OIhqYpFVvjgusNMrbFYBUveyRpt0YwQE";"A:F");"SELECT Col1, Col3, Col4 WHERE Col3 > 5000 AND Col4 >= 20")

Пример использования функции QUERY с использованием операторов SELECT, WHERE и AND в комбинации IMPORTRANGE в Google Таблицы

У нас всё получилось, но тут приходим Гриша из соседнего отдела и говорит покажи мне сумму продажи по месяца. Вспоминаем нашу исходную таблицу откуда мы импортируем данные (самая первая картинка) и понимаем, что данные с названием месяца находятся в 6-м столбце. Чтобы получить сумму продаж нам необходимо использовать функцию SUM() язык запросов API визуализации Google и оператор GROUP BY.

Из диапазона( получили с помощью IMPORTRANGE) ВЫБЕРИ Столбец6, ПРОСУММИРУЙ(Столбец3) И СГРУППИРУЙ ПО Столбец6

=QUERY(IMPORTRANGE("1b4ECXFiPJT5OIhqYpFVvjgusNMrbFYBUveyRpt0YwQE";"A:F");"SELECT Col6, SUM(Col3) GROUP BY Col6")

Пример использования функции QUERY с использованием операторов SELECT и GROUP BY в комбинации IMPORTRANGE в Google Таблицы

У нас всё получилось, но только есть, какая-то непонятная пустая строка. Это из-за того что мы в IMPORTRANGE указали диапазон A:F, то есть выбрали столбцы целиком, чтобы при добавлнии данных, новые данные автоматом учитывались. Давайте избавимся от этой строки уже знакомым нам оператором WHERE.

Из диапазона( получили с помощью IMPORTRANGE) ВЫБЕРИ Столбец6, ПРОСУММИРУЙ(Столбец3) ГДЕ Столбец6 <> '' И СГРУППИРУЙ ПО Столбец6

=QUERY(IMPORTRANGE("1b4ECXFiPJT5OIhqYpFVvjgusNMrbFYBUveyRpt0YwQE";"A:F");"SELECT Col6, SUM(Col3) WHERE Col6 <> '' GROUP BY Col6")

Пример использования функции QUERY с использованием операторов SELECT, WHERE и GROUP BY в комбинации IMPORTRANGE в Google Таблицы

На друг Гриша на этом не остновился, он попросил полученные данные отсортировать во возрастанию суммы продаж. Это можно сделать с помощью фильтра, но давайте мы реализуем это с помощью функции QUERY и опреатора ORDER BY - ASC (по возрастанию), чтобы наши импортируемы данные сразу были отсортированные по возрастанию.

Из диапазона(получили с помощью IMPORTRANGE) ВЫБЕРИ Столбец6, ПРОСУММИРУЙ(Столбец3) ГДЕ Столбец6 <> '' СГРУППИРУЙ ПО Столбец6 и ОТСОРТИРУЙ Сумму продаж ПО ВОЗРАСТАНИЮ

=QUERY(IMPORTRANGE("1b4ECXFiPJT5OIhqYpFVvjgusNMrbFYBUveyRpt0YwQE";"A:F");"SELECT Col6, SUM(Col3) WHERE Col6 <> '' GROUP BY Col6 ORDER BY SUM(Col3) ASC")

Пример использования функции QUERY с использованием операторов SELECT, WHERE, GROUP BY и ORDER BY в комбинации IMPORTRANGE в Google Таблицы

Мы с вами разобрали основные моменты совместного использования функций QUERY и IMPORTRANGE в Google Таблицы, с которыми вы можите столкнуться на работе или повседневной жизни. Более подробно про SQL-подобный запрос (язык запросов API визуализации Google), которые использует функция QUERY, можно почитать на официальной странице Google справочник по языку запросов:

https://develop ers.google.com/chart/inte ractive/docs/querylanguage?hl=ru

Небольшое примечание, IMPORTRANGE работает, только с таблицами формата Google Таблицы, с Excel форматом функция работать не будет!

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