Коллеги, добрый день! Мы приступаем к изучению функции QUERY (у нее нет названия на русском), которой посвятим сразу несколько уроков. Она этого, безусловно, заслуживает, не зря в одной известной статье ее называют "всемогущей" функцией. Она действительно решает очень много задач по обработке данных в Таблицах и является их неоспоримым преимуществом по сравнению с Excel. Ведь она позволяет строить запросы к данным, похожие на SQL запросы, и извлекать из них нужную информацию, группируя данные, сортируя их, отбирая по ряду условий и так далее. Давайте начнем с синтаксиса функции, а затем рассмотрим примеры ее применения и научимся работать с ней на практике. Итак, функция QUERY позволяет сделать выборку нужных строк из таблицы с помощью SQL запроса и сортировать их. Синтаксис функции следующий. Первый аргумент — это данные. Затем запрос и необязательный аргумент, заголовки. Рассмотрим их. Данные — это исходный диапазон, который будет обрабатываться, некая база данных, таблица с информацией, из которой мы будем формировать выборку. Запрос — это текстовое значение, это запрос на языке API визуализации Google. Это язык запросов, похожий на SQL, со своими правилами, все из которых мы обсудим. Он указывается в кавычках с соблюдением ряда условий, которым мы будем учиться. Третий аргумент необязательный, это заголовки. Это количество строк с заголовками в исходном диапазоне. По умолчанию он равен минус одному. Это означает, что функция будет определять количество строк с заголовками автоматически. В большинстве случаев используется единица просто потому, что во многих таблицах у нас, как правило, одна строка заголовков, или же пользователи предпочитают автоматически определять, то есть минус единица. Иногда заголовки выгружать вообще не нужно, тогда указывается ноль в последнем аргументе QUERY. Если же в таблице две строки заголовков, такие многоуровневые заголовки, это бывает, указывается третий аргумент, равный двойке. Давайте посмотрим на несколько примеров из практики, в которых используется функция QUERY. Первый пример — это из моего опыта работы в издательстве "МИФ". Это построение автоматическое топа продаж товаров с выбором категории, например, книги по бизнесу или настольные игры, или что-то еще, в выпадающем списке. То есть пользователь только выбирает категорию, и топ продаж формируется автоматически, причем все это без каких-либо скриптов, макросов, с помощью этой функции QUERY. Другой пример — это автоматическая фильтрация реестра платежей с выбором даты, столбца и порядка сортировки и количества выводимых платежей. То есть пользователь только меняет эти параметры в ячейках, функция QUERY делает все остальное, формирует выборку. Наконец, можно с помощью нее создавать своего рода сводные таблицы, анализировать данные в разных разрезах, как в этом примере автоматически формируется сумма по тематикам товаров, продаваемых из таблицы. На этом слайде вы видите далеко не полный список вариантов применения функции QUERY. Она действительно позволяет решать 80 процентов задач по обработке данных в Goolge Таблицах. Это могут быть и списки открытых сделок в файле вашей CRM системы, в которой вы ведете учет взаимодействий с клиентами. Их можно выводить по определенным датам, суммам, клиентам и другим параметрам. Можно автоматически формировать список товаров-неликвидов, исходя из остатков и продаж, строить топы, как я уже показывал на примере издательства "МИФ", проводить анализ данных по дате и времени, например, проанализировать посещение сайта по часам или дням и так далее. Во всех ситуациях, когда нужна выборка данных и их группировка и сортировка из любой базы данных (из таблицы, из отчета, из какой-то выгрузки), используется функция QUERY. Давайте посмотрим на несколько примеров из практики, в которых используется функция QUERY. Начнем мы с рассмотрения такого списка сотрудников, в котором указана фамилия, указан отдел и, наконец, оклад, и будем обрабатывать его с помощью функции QUERY. Итак, как мы обсудили, три аргумента: данные, запрос и заголовки. Данные — это, собственно говоря, та таблица, из которой мы хотим получить какую-то информацию, как-то ее обработать, написать к ней запрос. Обратите внимание, что можно ссылаться на обычный диапазон, а можно также ссылаться на открытый, то есть не указывать последнюю строку или просто указать столбцы. Вот таким образом: "А:С". Почему это важно? Потому что, если исходный диапазон будет пополняться, то есть будут добавляться сотрудники, будут добавляться новые строки, и вы не знаете заранее, сколько в диапазоне исходном будет данных, сколько строк, можно его пополнять, и результат вычисления функции QUERY будет тоже обновляться автоматически вместе с обновлением исходных данных. Для этого даже не нужно, как в Excel в случае со свободными таблицами, нажимать на какие-то клавиши "Обновить", все происходит автоматически. Итак, наши данные — это столбцы А:С. Также мы могли бы указать диапазон, например, без заголовков А2:С открытый и так далее. Запрос, как я говорил, указывается в кавычках, это текстовое значение, и начинается он всегда со слова "select". Вообще, запрос состоит из нескольких таких слов, как "select", так называемых кляуз, ключевых слов, и "select" — единственное обязательное. "Select" будет определять какие столбцы из исходного диапазона мы выгружаем. Если указать "select пробел звездочка", то это все данные из исходного диапазона. Мы видим, что выгрузилась вся информация, все три столбца, но вы также можете указывать столбцы конкретные и в любом порядке. Например, А:В — это выгрузка первых двух столбцов из исходного диапазона. Можно и поменять порядок. То есть сначала выгрузить С, потом А, а потом В и получить информацию в обратном порядке. Как я говорил, если исходная информация изменится, результат вычисления QUERY тоже будет меняться. Обратите внимание, что столбцы обязательно указываются заглавными буквами латинского алфавита, иначе работать функция не будет, то есть если мы укажем маленькую а, тут же будет ошибка. При этом кляузы, например, слово "select" можно указывать любыми буквами, на это функция не реагирует, такой у нее синтаксис. Итак, поскольку это, собственно говоря, функция, результат ее работы будет обновляться автоматически при обновлении исходных данных. Это большой плюс, который мы уже обсудили. Давайте теперь обсудим вторую кляузу этой функции, называется она "order by", это сортировка. Снова будем выгружать "select звездочка" все три столбца, но давайте теперь добавим сортировку. Как это формируется? Мы указываем через пробел после конструкции "select", то есть "select звездочка" или "select столбцы", и затем через пробел будет следовать "order by". И в "order by" нужно указать номер столбца, по которому идет сортировка. Например, если мы хотим сортировать по уровню заработной платы, нужно написать "order by С", потому что заработная плата в столбце С. И далее через пробел после столбца нужно указать порядок сортировки: desc, или descending, это по убыванию, asc, или ascending, это по возрастанию. Таким образом, вот такой запрос "select звездочка order by С asc" стал бы возвращать данные по возрастанию. Единственное, что здесь нужно указать последнюю строку, потому что в сортировку попали все строки ниже, пустые. Мы потом обсудим, как этого избегать и исключать пустые строки из выборки, но это будет уже чуть позже с помощью другой кляузы. Итак, это сортировка по возрастанию заработной платы. Если мы поменяем на "desc" (descending), то это будет по убыванию. Так же можно сортировать и по другим столбцам, например, по А, по алфавитному порядку или обратному алфавитному порядку. Коллеги, также обращу внимание, что сортировка может быть и нескольких уровней. Например, сначала по отделам, по алфавиту или в обратном алфавитном порядке, а потом в рамках каждого отдела по, например, возрастанию заработной платы. Например, если мы хотим по отделам в алфавитном порядке, мы указываем "order by В asc" (ascending), а потом через запятую нужно указать другой столбец С, например, по убыванию, вот таким образом. Что мы видим? Сначала идет с сортировка по отделам по алфавиту, и внутри каждого отдела сортировка уже идет по столбцу С "Оклад". Итак, коллеги, мы обсудили синтаксис функции QUERY и первые две кляузы этой функции ("select" и "order by"), которые позволяют, соответственно, выбирать отдельные столбцы и сортировать выборку. В следующих видео мы рассмотрим все остальные кляузы, которые позволят в полной мере овладеть функционалом этой мощной функции.
