Часто при работе с Гугл таблицами приходится постоянно переформатировать таблицы, объединять, делать некие выжимки, сортировки и т.д.
Для этих целей стоит использовать функционал функции Query
Эта функция, собственно, значительно отличается от других встроенных функций. Аргументы здесь указываются (пишутся) на специальном языке "запросов" SQL.
Давайте, вместе с вами, рассмотрим основные примеры использования запросов Query
Итак, у нас есть исходная таблица:
Синтаксис функции Query выглядит следующим образом:
QUERY(данные; запрос; [заголовки])
данные - это диапазон с данными, над которыми нужно выполнить действия
запрос - непосредственно команда, написанная на языке запросов SQL? а именно то, что нужно сделать с данными
заголовки - это не обязательный аргумент, который подсказывает программе, что в выделенном диапазоне данных есть строки (именно в этом параметре мы указываем сколько таких строк), которые являются не данными, а заголовками таблицы.
Если рассматривать наш пример, то мы будем использовать диапазон A1:E7, при этом у нас будет 1 строка с заголовками.
Мы рассмотрим основные операции на языке SQL, а именно:
SELECT - Позволяет выбрать и вывести на экран определенные столбцы, нужные именно вам.
WHERE - задает условия для строк, т.е. какому условию должны удовлетворять строки, чтобы попасть в выборку
GROUP BY - группирует строки при наличии агрегации (сумма, количество и т.д.)
ORDER BY - задает условия сортировки строк
LABEL - позволяет присвоить новые имена (заголовки) столбцов в итоговой таблице
Данные операции можно использовать по отдельности или комбинировать, но важно соблюдать порядок следования этих операций
Операции указываются в двойных кавычках ""
SELECT - выбрать столбцы
Обычно используем два варианта этой операции:
либо "*" (звездочка) - все столбцы, либо перечисляем через запятую те столбцы, которые нам нужны. Название столбцов соответствует названию этих столбцов на рабочем поле активной книги (A, B, С и тд)? но можно указать нужные столбцы, так сказать, по счёту, например Col1, Col2 и т.д.
Давайте выведем на экран таблицу, которая получится из нашей исходной, но будет содержать только столбцы: фамилия, должность и оклад.
Формула будет выглядеть следующим образом:
=QUERY(A1:E7; "select A, D, E")
Т.к. мы не производим никаких сортировок, то нам не обязательно указывать, что первая строка у нас является заголовком, но при желании можно это указать,
=QUERY(A1:E7; "select A, D, E"; 1)
Столбцы можно переставлять местами, так, как нужно в итоговой таблице. Например, если нам нужно чтобы первым столбцом шла должность, то следует написать такую формулу:
=QUERY(A1:E7; "select D, A, E")
Мы просто поменяли столбцы местами в запросе и получили уже другой результат:
Если в итоговой таблице нам нужно получить все столбы, то формула будет выглядеть следующим образом:
=QUERY(A1:E7; "select *")
Объединение таблиц
Очень часто возникает необходимость объединить несколько таблиц в одну.
Давайте рассмотрим пример, где у нас на разных листах "Отдел1" и "Отдел2" находятся разные таблицы, которые нужно объединить и поместить полученную таблицу на лист "Свод"
Соответственно на листе "Свод" мы вводим нашу формулу:
=QUERY({'Отдел1'!A1:E10;'Отдел2'!A1:E10}; "select *")
Обратите внимание, что перечисляемые диапазоны взяты в фигурные скобки. Диапазоны разделяются точкой с запятой, поэтому это может неверно интерпретироваться функцией - после точки с запятой она ждет ввода команды а не очередной диапазон, поэтому берем все наши диапазоны в фигурные скобки.
Вот, что у нас получится
Сразу бросается в глаза, что у нас задвоились заголовки, т.к. они были в каждой таблице, а мы не указали, что у нас есть заголовки. Давайте исправим:
=QUERY({'Отдел1'!A1:E10;'Отдел2'!A2:E10}; "select *";-1)
Мы исправили следующим образом - в первом диапазоне мы берем все, вместе с заголовком и указываем, что первая строка это заголовок. Но даже если бы мы не указывали, то ничего страшного не произошло бы ровно до того момента, пока мы не стали бы сортировать наши строки.
А вот во втором диапазоне мы не включаем первую строку в выборку. указываем диапазон со второй строки
Обратите внимание, что в нашем примере мы берем все данные, т.е. все столбцы, а вот если нам нужно взять только некоторые, например Фамилию, должность и оклад, а в разных исходных таблицах столбцы могут называться по разному. В одной тафлице фамилия это А, в другой это С и т.д. Вот в этом случае мы можем использовать указание столбцов только "по счету". Например:
=QUERY({'Отдел1'!A1:E10;'Отдел2'!A2:E10}; "select Col1, Col4, Col5";-1)
Еще один момент, который может раздражать - это наличие пустых строк. Если у нас исходные таблицы уже окончательные и туда не будут добавляться строки, то при указании диапазона можно взять только заполненные строки, но на практике такое встречается крайне редко. Обычно в таблицы все время что-то добавляется и сколько туда ещё впихнут строк не всегда известно заранее, а всё время перебивать и править формулы очень лениво. Поэтому обычно я в формулах беру диапазоны с большим запасом, но это, к сожалению, приводит к появлению пустых строк.
Как от этого избавиться? Для этого есть команда Where, которая позволяет нам настроить фильтрацию нужных нам строк
WHERE - выборка (условие)
Для того, чтобы убрать пустые строки будем использовать фильтр - Вывести только не пустые строки. Т.е. вывести только строки в которых, например, фамилия не пустое значение.
=QUERY({'Отдел1'!A1:E10;'Отдел2'!A2:E10}; "select Col1, Col4, Col5 where Col1 <> ''";-1)
здесь мы добавили в нашу формулу условие
where Col1 <> ''
т.е. колонка один не равна пустому значению. Значение указывается в одинарных кавычках (')
Вот что получается в результате:
Условия отбора можно объединять, используя оператор and.
Например давайте добавим еще одно условие, например мы должны вывести список только Менеджеров. Т.е. нужно добавить условие, что значения в колонке 4 исходной таблицы (Col4) равна 'Менеджер' (Условие пишем в одинарных кавычках)
=QUERY({'Отдел1'!A1:E10;'Отдел2'!A2:E10}; "select Col1, Col4, Col5 where Col1 <> '' and Col4 = 'Менеджер'";-1)
Вот результат:
Следует отметить, что значение может быть равно (идентично), а может содержать. В этом случае вместо знака "=" (равно) используем оператор LIKE
А вот как поступить, если нам нужно отсортировать сотрудников, у которых оклад более указанного, например в ячейке Е1?
=QUERY({'Отдел1'!A1:E10;'Отдел2'!A2:E10}; "select Col1, Col4, Col5 where Col1 <> '' and Col5 >"&E1&"";-1)
Обратите внимание на то, как указана ссылка на ячейку. Ссылка на ячейку заключается в двойные кавычки и амперсанд (&) - "&E1&"
Еще один существенный момент - не запутайтесь в кавычках. Весь текст команд SQL тоже пишется в двойных кавычках. Очень часто забывают закрывающие кавычки у команды написанной на SQL.
Вот результат:
GROUP BY - СГРУППИРОВАТЬ
С помощью этой команды мы можем группировать значения по определенному столбцу и вычислят агрегатные функции над значениями в каждой группе
Агрегатные функции
SUM - сумма
MIN - минимальное значение
MAX - максимальное значение
AVG - среднее значение
COUNT - Количество значений
Давайте посчитаем сколько человек каждой должности у нас работает:
=QUERY({'Отдел1'!A1:E10;'Отдел2'!A2:E10}; "select Col4, COUNT(Col4) group by Col4")
В разделе select мы перечисляем столбцы, которые нам нужны Col4 - это столбец который содержит название должностей, далее мы используем агрегатирующую функцию COUNT (количество) и считаем количество вхождений каждой записи и это выводится во второй столбец, параллельно с этим мы группируем по значению столбца 4
Вот что получается:
Теперь нам нужно убрать пустые строки. Мы можем воспользоваться схемой, как это делали раньше, либо дописать следующую команду - where Col4 is not null
Получится так
=QUERY({'Отдел1'!A1:E10;'Отдел2'!A2:E10}; "select Col4, COUNT(Col4) where Col4 is not null group by Col4")
В нашем новом столбце появился какой-то кривой заголовок. Т.е. к старому названию заголовка добавилось название агрегатирующей функции count? что смотрится очень не красиво. Поменять название любого столбца позволяет команда Label
LABEL - Заголовок
Функция достаточно проста. Синтаксис у нее следующий
label НомерСтолбца 'Новое название в одинарных кавычках'
У нашего примера новый заголовок нужен новому столбцу. Его не было в старой таблице, поэтому у нас будет следующая конструкция
label COUNT(Col4) 'Количество сотрудников'
Здесь COUNT(Col4) - это номер нашего нового
Итого получаем
=QUERY({'Отдел1'!A1:E10;'Отдел2'!A2:E10}; "select Col4, COUNT(Col4) where Col4 is not null group by Col4 label COUNT(Col4) 'Количество сотрудников'")
Получаем:
ORDER BY - Сортировка
Эта команда позволяет отсортировать данные. Давайте выведем всю нашу объединенную таблицу, за вычетом пустых строк
=QUERY({'Отдел1'!A1:E10;'Отдел2'!A2:E10}; "select * where Col1 is not null")
А теперь отсортируем сотрудников по фамилиям по возрастанию
=QUERY({'Отдел1'!A1:E10;'Отдел2'!A2:E10}; "select * where Col1 is not null order by Col1")
мы добавили команду order by Col1 - сортировать по столбцу 1
Можно добавить
ASC - для сортировки по возрастанию (по умолчанию)
DESC - для сортировки по убыванию
Например команда
=QUERY({'Отдел1'!A1:E10;'Отдел2'!A2:E10}; "select * where Col1 is not null order by Col1 desc")
отсортирует список по убыванию
Мы с Вами разобрали основные способы применения функции QUERY/ Если Интересны другие моменты применения этой функции, пишите в комментариях, обязательно разберем
Если Вам понравилась статья, ставьте лайк и подписывайтесь на канал.
Все остальные уроки по Гугл таблицам Вы можете посмотреть, посетив страницу с содержанием:
Без рекламы и только про Гугл таблицы можно почитать здесь:
