Функция Query в Google Docs.

Всем привет.

 

И опять про MS Excel. Как, опять? Да вот, выдалась неделька).

 

Хорошая новость - SQL добрался до MS Excel! Нет,нет я не про PowerQuery или получение данных через ODBC. Я про обычный, но с оговоркой, Google Docs который Таблицы.

 

Можно уверенно заявить что функция Query выгодно отличает Google Таблицы от самого MS Excel.

 

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

 

Язык SQL очень прост и похож на обычный язык общения. Чуть познаний в Английском и вы уже знаете что:

• SELECT (выбрать) “колонки, которые ты хочешь видеть”
• WHERE (где) “твой фильтр колонок и данных”
• GROUP BY (группировать по) “если ты хочешь просуммировать данные”
• ORDER BY (сортировать по) “если ты хочешь сортировать результаты”

Синтаксис функции QUERY прост:
QUERY(данные; запрос; [заголовки])

где:
•данные - это диапазон ячеек, который будет служить источником данных для запроса;
•запрос - непосредственно текст SQL запроса;
•заголовки - необязательный аргумент, в котором вы можете указать, сколько первых строк массива содержат заголовки.

Любой SQL запрос состоит из отдельных блоков, которые часто называют кляузами(clause). В SQL для функции Query заложен синтаксис языка запросов API визуализации Google, который поддерживает следующие кляузы:

•select — перечисление полей, которые будут возвращены запросом;

•where — содержит перечень условий, с помощью которых будет отфильтрован массив данных, обрабатываемый запросом;

•group by — содержит перечень полей, по которым вы хотите группировать результат;

•pivot — помогает строить перекрестные таблицы, используя значение одного столбца в качестве названий столбцов финальной таблицы;

•order by — отвечает за сортировку результатов;

•limit — с помощью этой части запроса вы можете задать предел количеству строк, возвращаемых запросом;

•offset — с помощью этой кляузы вы можете задать число первых строк, которые не надо обрабатывать запросом;

•label — данная кляуза отвечает за название полей, возвращаемых запросом;

•format — отвечает за формат выводимых данных;

•options — дает возможность задавать дополнительные параметры вывода данных.

 

Очень подробно работа с Query освещена здесь.

Кстати если построить сводную таблицу с помощью кляузы pivot, то проблема обновления данных сводной таблицы с Query решается автоматически.

Естественно нас интересует возможность переноса данных из Excel в Google Таблицы и наоборот. Не все так радужно. Фирмы ведь разные. С импортом из Excel в Google Таблицы все более менее понятно. А в с экспортом из Google Таблицы в Excel есть ограничения.

 

Отсутствующие в Excel функции при экспорте превращаются в ЕСЛИОШИБКА (IFERROR), где в качестве первого аргумента будет запись вида __xludf.DUMMYFUNCTION (функция), которая и выдаст ошибку в Excel, а в качестве второго аргумента - то значение, которое возвращала эта функция в момент экспорта. К сожалению это касается и нашей функции Query. 

 

Такое вот неудобство про которое следует помнить.

Успехов вам c Query!