Аналитика

Всемогущая функция Query — подробное руководство

20719077
399

Думаю, все слышали о правиле Парето. В любой сфере 20% усилий дают 80% результата. Например, 20% своего гардероба вы носите 80% времени, 20% ваших клиентов приносят 80% дохода. Так же и в Google Таблицах: зная 20% существующих функций, вы сможете решить 80% всех возможных задач.

Я считаю Query одной из наиболее полезных функций Google Таблиц. Но в справке Google она описывается очень поверхностно, и вся мощь данной функции не раскрыта. При более детальном знакомстве становится ясно, что она способна заменить большую часть существующих функций.

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

Синтаксис QUERY

QUERY(данные; запрос; [заголовки])

Где:

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

Для максимального восприятия дальнейшей информации предлагаю открыть и скопировать себе следующую Google Таблицу. Для того, чтобы создать копию, воспользуйтесь меню «Файл» и выберите в нем пункт «Создать копию». Для того, чтобы создать копию, воспользуйтесь меню «Файл» и выберите в нем пункт «Создать копию» В доксе, копию которого вы только что создали, существует несколько листов. Лист DB - это база данных, к которой мы будет обращаться с помощью функции QUERY. Листы Level содержат примеры, которые мы будем рассматривать в этой статье. C каждым новым уровнем пример будет усложняться.

План SQL запроса в функции Query

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

  • select — перечисление полей, которые будут возвращены запросом;
  • where — содержит перечень условий, с помощью которых будет отфильтрован массив данных, обрабатываемый запросом;
  • group by — содержит перечень полей, по которым вы хотите группировать результат;
  • pivot — помогает строить перекрестные таблицы, используя значение одного столбца в качестве названий столбцов финальной таблицы;
  • order by — отвечает за сортировку результатов;
  • limit — с помощью этой части запроса вы можете задать предел количеству строк, возвращаемых запросом;
  • offset — с помощью этой кляузы вы можете задать число первых строк, которые не надо обрабатывать запросом;
  • label — данная кляуза отвечает за название полей, возвращаемых запросом;
  • format — отвечает за формат выводимых данных;
  • options — дает возможность задавать дополнительные параметры вывода данных.

Hello World для функции Query (Select)

Перейдем на лист Level_1 и посмотрим формулу в ячейке A1.

=query(DB!A1:L1143;"select * limit 100")

Часть формулы «DB!A1:L1143» отвечает за базу данных, с которой мы будем делать выборку. Вторая часть «select * limit 100» содержит непосредственно текст запроса. Символ «*» в данном случае означает возвращение всех полей, содержащихся в базе данных. С помощью «limit 100» мы ограничиваем вывод данных в 100 строк максимум. Это пример самого простого запроса. Мы выбрали 100 первых строк из базы данных. Это своего рода «Hello world» для функции Query.

Используем фильтры и сортировку (Where, Order by)

Переходим на лист Level_2. Выберем только некоторые нужные нам поля и зададим условия фильтрации и сортировки. Например, используем данные только по кампаниям Campaign_1 и Campaign_2 за период 22-25 октября 2015 года. Отсортируем их в порядке убывания по сумме сеансов. Для фильтра и сортировки в текст запроса необходимо добавить описание кляуз Where и Order. Для вывода в результирующую таблицу описанного выше примера нам понадобятся поля Campaign, Date и Sessions. Именно их и нужно перечислить в кляузе Select.

Обращение к полям базы данных осуществляется через названия столбцов рабочего листа, на котором располагается база данных.

В нашем случае данные, расположенные на листе DB, и обращение к определенным полям прописываются как название столбцов листа. Таким образом, нужные поля располагается в следующих столбцах:

  • поле Date — столбец A;
  • поле Campaign — столбец B;
  • поле Sessions — столбец G.

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

Select A, B, G

Далее в запросе идет кляуза Where. При написании запроса кляузы обязательно должны располагаться в таком порядке, в котором были описаны в первом разделе этой статьи. После объявления Where нам необходимо перечислить условия фильтрации. В данном случае мы фильтруем данные по названию кампании (Campaign) и дате (Date). Мы используем несколько условий фильтрации. В тексте запроса между всеми условиями должен стоять логический оператор OR или AND. Фильтрация по датам немного отличается от фильтрации по числовым и текстовым значениям, для ее применения необходимо использовать оператор Date. Часть запроса, отвечающая за фильтрацию данных, будет выглядеть так:

WHERE
(A >= date'2015-10-22'
AND A <= date'2015-10-25')
AND (B = 'Campaign_1'
OR B = 'Campaign_2')

Мы разбили с помощью скобок фильтрацию данных на две логических части: первая фильтрует по датам, вторая — по названию кампании. На данном этапе формула, описывающая данные, которые необходимо выбрать, и условия фильтрации данных, выглядит так:

=query(DB!A1:L1143;"
Select A, B, G
WHERE
(A >= date'2015-10-22'
AND A <= date'2015-10-25')
AND (B = 'Campaign_1'
OR B = 'Campaign_2')")

Вы можете скопировать ее и вставить, например, на новый лист документа, который используется в качестве примера в этом посте, и получите следующий результат: Формула, описывающая данные, которые необходимо выбрать, и условия фильтрации данных

Помимо обычных логических операторов (=, <, >) блок WHERE поддерживает дополнительные операторы фильтрации:

  • contains — проверяет содержание определённых символов в строке. Например, WHERE A contains ‘John’ вернёт в фильтр все значения из столбца A, в которых встречается John, например, John Adams, Long John Silver;
  • starts with — фильтрует значения по префиксу, то есть проверяет символы в начале строки. Например, starts with ‘en’ вернёт значения engineering и english;
  • ends with — фильтрует значения по окончанию строки. Например, строка ‘cowboy’ будет возвращена конструкцией «ends with ‘boy’» или «ends with ‘y’»;
  • matches — соответствует регулярному выражению. Например: where matches ‘.*ia’ вернёт значения India и Nigeria.
  • like — упрощённая версия регулярных выражений, проверяет соответствия строки заданному выражению с использованиям символов подстановки. На данный момент like поддерживает два символа подстановки: «%» означает любое количество любых символов в строке, и «_» — означает один любой символ. Например, «where name like ‘fre%’» будет соответствовать строкам ‘fre’, ‘fred’, и ‘freddy’.

Запрос уже отфильтровал данные за определенный период и оставил только нужные нам кампании. Остается только отсортировать результат по убыванию в зависимости от количества сеансов. Сортировка в данных запросах осуществляется традиционно для SQL с помощью кляузы Order by. По синтаксису она довольна простая: необходимо только перечислить поля, по которым требуется отсортировать результат, а также указать порядок сортировки. По умолчанию — порядок asc, то есть по возрастанию. Если укажете после название поле параметр desc, запрос вернет результат в порядке убывания указанных в кляузе Order by полей.

В нашем случае за фильтрацию будет отвечать строчка в тексте запроса:

Order by G desc

Соответственно, окончательный результат формулы на листе Level_2, решающий нужную нам задачу, выглядит так:

=query(DB!A1:L1143;"
SELECT A, B, G
WHERE (A &gt;= date'2015-10-22'
AND A &lt;= date'2015-10-25')
AND (B = 'Campaign_1'
OR B = 'Campaign_2')
ORDER BY G DESC")

Теперь вы умеете с помощью простейшего SQL синтаксиса и функции QUERY фильтровать и сортировать данные.

Кстати, советуем подписаться на нашу рассылку — мы еженедельно высылаем полезные подборки статей:

Агрегирующие функции, группировка данных и переименование столбцов (Group by, Label)

Переходим на лист Level_3 и усложняем задачу. В запросах вы можете не только делать выборки, но также проводить различные вычисления и агрегации данных. Для этого в SQL функции Query существует ряд агрегирующих функций и кляуза Group by. Агрегирующие функции:

Функция Описание Поддерживаемый тип данных Возвращаемый тип данных
avg() Возвращает среднее значение для группы Числовой Числовой
count() Возвращает количество значений в группе Любой Числовой
max() Возвращает максимальное значение для группы Любой Аналогичный полю, к которому применяется
min() Возвращает минимальное значение для группы Любой Аналогичный полю, к которому применяется
sum() Возвращает сумму значений в группе Числовой Числовой

Итак, давайте посчитаем данные по каждой кампании:

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

Для решения этой задачи нам понадобятся данные только из двух полей: Campaign (находится в столбце B) и Sessions (находится в столбце G). Все агрегирующие функции прописываются вместе со списком полей для вывода данных в кляузе Select. В случае применения агрегирующих функций все поля, к которым не применяется этот тип функций, являются группирующими полями. Их необходимо перечислить в кляузе Group by. Агрегирующие функции работают обязательно в паре с Group by. Описание кляузы Select будет следующим:

SELECT
B,
avg(G),
max(G),
min(G),
count(G),
sum(G)

Далее необходимо сгруппировать данные: в нашем случае требуется группировка только по одному полю Campaign, но вы можете осуществлять группировку по любому количеству столбцов.

Описание кляузы Group by очень простое:

GROUP BY B

В кляузе достаточно указать только столбец B, содержащий информацию о названии кампании. Потому что он единственный, к которому мы не применили никакой агрегирующей функции. Наша формула:

=query(DB!A1:L1143;"
SELECT
B,
avg(G),
max(G),
min(G),
count(G),
sum(G)
GROUP BY B")

Получим следующий результат: Отображение кляузы GROUP BY B В принципе, мы получили желаемый результат, но названия столбцов можно подкорректировать с помощью кляузы Label. Результат будет лучше отображаться, если мы отсортируем отчет по названию кампании. Описание кляузы Order by мы уже рассмотрели выше.

Для нужной сортировки достаточно добавить следующую строку после описания:

Order by B

Чтобы переименовать столбцы в таблице, которую возвращает запрос, необходимо добавить описание кляузы Label. Синтаксис достаточно прост: сначала указываете выводимый столбец или функцию, которая будет возвращать значения в результирующую таблицу, и далее в одинарных кавычках указываете нужное название.

Это будет выглядеть так:

B 'Кампания',
avg(G) 'Среднее',
max(G) 'Максимальное',
min(G) 'Минимальное',
count(G) 'Количество',
sum(G) 'Общая сумма'

Преобразованная формула:

=query(DB!A1:L1143;"
SELECT
B,
avg(G),
max(G),
min(G),
count(G),
sum(G)
GROUP BY B

ORDER BY B

LABEL
B 'Кампания',
avg(G) 'Среднее',
max(G) 'Максимальное',
min(G) 'Минимальное',
count(G) 'Количество',
sum(G) 'Общая сумма'")

А результат, возвращаемый формулой, выглядит так: Результат формулы "Order by B" Все поля названы соответствующим описанию кляузы Label образом. Последнее, что режет глаз в возвращаемой таблице, — формат, в котором выводятся данные в столбце «Среднее». Для корректировки форматов, выводимых запросом данных, требуется описать кляузу Format. Ее описание схоже с описанием Label, но вместо названия поля следует прописать маску вывода данных (также в одинарных кавычках).

Округлим числа в столбце «Среднее» до двух знаков после запятой. Для округления выводимых данных до двух знаков после запятой маска должна выглядеть как ‘0.00’.

Описание кляузы Format

FORMAT avg(G) ‘0.00’

Соответственно, окончательная формула на листе Level_3 выглядит так:

=query(DB!A1:L1143;
"SELECT
B,
avg(G),
max(G),
min(G),
count(G),
sum(G)
GROUP BY B

ORDER BY B

LABEL
B 'Кампания',
avg(G) 'Среднее',
max(G) 'Максимальное',
min(G) 'Минимальное',
count(G) 'Количество',
sum(G) 'Общая сумма'

FORMAT
avg(G) '0.00'")

В результате: Результат кляузы Format

Создание перекрестных таблиц (Pivot, скалярные функции)

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

Чтобы вычислить день недели, придется воспользоваться одной из множества скалярных функций. В нашей базе есть вся необходимая информация для расчета показателя отказов. Дальше достаточно просто применить арифметический оператор «Деление».

Скалярные функции

На момент написания статьи SQL в Google Таблицах поддерживает 14 скалярных функций.

Функция Описание
year() Возвращает номер года из «даты» или «даты и времени». Пример: year(date ‘2009-02-05’) вернет 2009. Запрашиваемые параметры: один параметр с типом дата или дата и время. Тип возвращаемых данных: число.
month() Возвращает номер месяца из «даты» или «даты и времени». Но в данном случае январь будет возвращать 0, феврваль 1 и так далее. Началом отсчета для номера месяца является 0. Пример: month(date ‘2009-02-05’) вернет 1. Чтобы функция вернула номер месяца в привычном виде к ее результату прибавьте 1, month(date "2009-02-05")+1 вернет 2. Запрашиваемые параметры: один параметр с типом дата или дата и время. Тип возвращаемых данных: число.
day() Возвращает номер дня в месяце из «даты» или «даты и времени». Пример: day(date ‘2009-02-05’) вернет 5. Запрашиваемые параметры: один параметр с типом дата или дата и время. Тип возвращаемых данных: число.
hour() Возвращает номер часа в дне из «даты и времени» или «времени». Пример: hour(timeofday ‘12:03:17') вернет 12. Запрашиваемые параметры: один параметр с типом время или дата и время. Тип возвращаемых данных: число.
minute() Возвращает номер минуты в часе из «даты и времени» или «времени». Пример: minute(timeofday ‘12:03:17') вернет 3. Запрашиваемые параметры: один параметр с типом время или дата и время. Тип возвращаемых данных: число.
second() Возвращает номер секунды в минуте из «даты и времени» или «времени». Пример: second(timeofday ‘12:03:17') вернет 17. Запрашиваемые параметры: один параметр с типом время или дата и время. Тип возвращаемых данных: число.
millisecond() Возвращает номер миллисекунды в секунде из «даты и времени» или «времени». Пример: millisecond(timeofday ‘12:03:17.123') вернет 123. Запрашиваемые параметры: один параметр с типом время или дата и время. Тип возвращаемых данных: число.
quarter() Возвращает номер квартала в году из «даты и времени» или «времени». Базовым значением или началом отсчета является 1, соответствено, для первого квартала функция вернет значение 1, для второго 2 и так далее. Пример: quarter(date ‘2009-02-05’) вернет 1. Запрашиваемые параметры: один параметр с типом дата или дата и время. Тип возвращаемых данных: число.
dayOfWeek() Возвращает номер дня недели в неделе из «даты» или «даты и времени». Началом недели считается воскресенье, для воскресенья функция вернет значение 1, для понедельника 2 и так далее. Пример: dayOfWeek(date ‘2015-11-10’) вернет 3, так как 10 ноября 2015 года - вторник. Запрашиваемые параметры: один параметр с типом дата или дата и время. Тип возвращаемых данных:число.
now() Возвращает текущую дату и время в часовом поясе GTM. Запрашиваемые параметры: не требует ввода параметров. Тип возвращаемых данных: дата и время.
dateDiff() Возвращает разницу в днях между двумя датами. Пример: dateDiff(date ‘2008-03-13’ , date ‘2008-02-12’) вернет 29, так как 10 ноября 2015 года вторник. Запрашиваемые параметры: два параметра с типом «дата» или «дата и время». Тип возвращаемых данных: число.
toDate Возвращает преобразованное в дату значение из «даты» или «даты и времени» или «числа». Пример:
  • toDate(date ‘2008-03-13’) вернет аналогичное значение в формате даты, ‘2008-03-13’.
  • toDate(dateTime‘2013-03-13 11:19:22’) вернет дату ‘2013-03-13’.
  • toDate(1234567890000) вернет дату ‘2009-02-13’.
Запрашиваемые параметры: один параметр с типом дата, дата и время или число. Тип возвращаемых данных: дата.
upper() Преобразует все значения в строке в верхний регистр. Пример: upper( ‘foo') вернет строку ‘FOO’. Запрашиваемые параметры: один параметр с текстовым типом данных. Тип возвращаемых данных: текст.
lower() Преобразует все значения в строке в нижний регистр. Пример: upper( ‘Bar') вернет строку ‘bar’. Запрашиваемые параметры: один параметр с текстовым типом данных. Тип возвращаемых данных: текст.

Арифметические операторы

Оператор Описание
+ Сложение нескольких числовых значений
- Разница между числовыми значениями
/ Деление числовых значений
* Умножение числовых значений

Для решения нашей задачи потребуется использовать скалярную функцию dayOfWeek для вычисления дня недели, а также арифметический оператор «/» для подсчета показателя отказов.

Давайте определим поля, которые будем использовать в запросе:

  1. Для вычисления дня недели нам потребуется данные поля Date в столбце A.
  2. Данные о типах устройств хранятся в поле Device category в столбце E.
  3. Для расчета показателя отказов потребуются данные полей Bounces и Sessions — в столбцах H и G.

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

SELECT dayOfWeek(A), sum(H)/sum(G)

Именно так будет выглядеть описание нужных нам полей. Теперь с помощью кляузы Group by сгруппируем строки по дням недели. Для этого допишем в запрос следующую строку:

GROUP BY dayOfWeek(A)

Уже видно, как меняется показатель отказов в зависимости от дня недели: Видно, как меняется показатель отказов в зависимости от дня недели Чтобы построить перекрестную таблицу, достаточно прописать кляузу Pivot с указанием столбца, значения из которого будут добавлены в виде столбцов. В нашем случае это столбец E, потому что именно он содержит информацию о типе устройств. Дописываем строку:

PIVOT E

Теперь запрос возвращает результат: Запрос возвращает результат Нам остается только добавить последние штрихи: изменить названия столбцов и формат чисел с помощью пунктов LABEL и FORMAT.

Окончательная формула на листе Level_4:

=query(DB!A1:L1143;"
SELECT dayOfWeek(A), sum(H)/sum(G)
GROUP BY dayOfWeek(A)
PIVOT E
LABEL
dayOfWeek(A) 'День недели',
sum(H)/sum(G)''
FORMAT sum(H)/sum(G) '0.00%'")

Получаем отчет: Окончательная формула на листе “Level_4” Строкой LABEL sum(H)/sum(G) ‘’ мы убрали из подписей столбца надпись, содержащую формулу расчета. Строкой FORMAT sum(H)/sum(G) ‘0.00%’ мы передали процентный формат показателю отказов в отчете. В целом, описанного в примерах выше синтаксиса вполне достаточно, чтобы начать активно использовать функцию QUERY, но в завершении статьи хочу показать еще несколько интересных приемов, которые можно взять на вооружение.

Импорт данных с помощью QUERY из другой Google Таблицы

С помощью QUERY вы можете использовать в качестве базы данных другую Google Таблицу. Это можно сделать с помощью сочетания функций ImportRange и QUERY. Я создал новую Google Таблицу, в которую продублировал данные с листа DB из приведенного в начале статьи документа. Чтобы в качестве базы данных использовать данные из другой Google таблицы, в качестве первого аргумента функции Query выступит импортируемый функцией ImportRange диапазон.

Разница в том, что при написании запроса к данным, импортируемым функцией ImportRange, вместо названия столбцов мы указываем их порядковый номер в возвращаемом функцией ImportRange диапазоне. На листе DataImport перепишем запрос, представленный в Level_4 таким образом, чтобы он обращался к данным, находящимся в новой таблице на листе DB_Transfer. Синтаксис функции ImportRange достаточно прост:

IMPORTRANGE(ключ, диапазон)

Где ключ — часть URL Google Таблицы:

ключ — часть URL Google Таблицы

А диапазон — это ссылка на лист и (простите за каламбур) диапазон. В нашем случае диапазоном будет DB_Transfer!A1:L1143. Формула ImportRange:

importrange(“1aBytZCYsZF0-3RozYviSrMqVLtqtb49yxY9KBgT4pVo”;”DB_Transfer!A1:L1143”)

Именно ее мы должны указать в качестве данных функции Query. Далее остается переписать запрос так, чтобы ссылаться на столбцы базы данных не по названию, а по порядковому номеру столбца. Определим, к каким столбцам мы обращались с помощью запроса на листе Level_4.

Название Содержание Наименование в таблице Порядковый номер
Date Дата A 1
Device type Тип устройства E 5
Sessions Количество сеансов G 7
Bounces Количество отказов H 8

Определим, к каким столбцам мы обращались с помощью запроса Текст запроса после замены названий столбцов на их порядковый номер:

SELECT dayOfWeek(Col1), sum(Col8)/sum(Col7)
GROUP BY dayOfWeek(Col1)
PIVOT Col5
LABEL
dayOfWeek(Col1) 'День недели',
sum(Col8)/sum(Col7) ''
FORMAT sum(Col8)/sum(Col7) '0.00%'

Как видите, текст запроса практически не изменился, но вместо столбца A мы теперь указываем Col1, вместо столбца E — Col5, вместо G — Col7 и вместо H, соответственно, Col8. Получаем формулу:

=query(IMPORTRANGE("1aBytZCYsZF0-3RozYviSrMqVLtqtb49yxY9KBgT4pVo";"DB_Transfer!A1:L1143");"
SELECT dayOfWeek(Col1), sum(Col8)/sum(Col7)
GROUP BY dayOfWeek(Col1)
PIVOT Col5
LABEL
dayOfWeek(Col1) 'День недели',
sum(Col8)/sum(Col7) ''
FORMAT sum(Col8)/sum(Col7) '0.00%'")

В качестве первого аргумента функции Query выступает функция ImportRange с ссылкой на ключ нужной Google таблицы, которую вы можете скопировать из URL Google Таблицы, и ссылки на диапазон, включающий название листа, а также первой и последней ячейки нужного диапазона.

Номера столбцов в запросе идут не со столбца A, а с того, который является первым в указанном вами диапазоне в функции ImportRange. Например, если бы в качестве импортируемого диапазона выступал DB_Transfer!C1:L1143, то данные из столбца C запрашивались ссылкой Col1, поскольку в импортируемом массиве этот столбец - первый.

Окончательную формулу в работе вы можете посмотреть на листе DataImport.

Строим запрос на основе объединения данных из нескольких таблиц с одинаковой структурой

Ещё одна довольно мощная возможность функции QUERY — построение запрос на основе нескольких массивов данных.

Единственное условие для объединения данных - одинаковая структура входящих таблиц.

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

Массив — это виртуальная таблица, которая содержит строки и столбцы.

Массив всегда описывается внутри фигурных скобок, при этом необходимо соблюдать следующую пунктуацию:

  • обратная косая черта «\» — разделяет столбцы. Например, {1 \ A}. Число 1 будет находится в правой левой ячейке массива, буква «A» в ячейке справа. Так мы описали диапазон, содержащий два столбца и одну строку.
  • точка с запятой «;» используется для перехода на следующую строку. Возьмем {1;A}. Этот массив будет состоять из одного столбца и двух строк, в первой строке будет содержаться значение 1, во второй строке буква «A».

Таким образом вы можете два и более диапазона описать в одном массиве, например:

=query({Table1!A1:B5; Table2!A1:B5; Table3!A1:B5};"SELECT * WHERE Col2 > 4")

В данном случае мы обращаемся с запросом к трём диапазонам данных, находящимся на разных листах, объединив их с помощью «;» в массив так, что вторая таблица становится продолжением первой, а третья таблица — продолжением второй.

Посмотрите этот пример по ссылке.

Запрос с динамическими параметрами

Синтаксис запросов в функции QUERY сложен для неподготовленного пользователя. Поэтому вы можете добавить на рабочий лист различные интерактивные элементы в виде выпадающего списка, созданного с помощью функции «Проверка данных». Запрос с динамическими параметрами А в тексте запроса — делать ссылки на ячейки, содержащие нужные данные. Например, мы можем динамически задать диапазон дат, который хотим вывести в динамическую таблицу, либо сделать возможность динамически добавлять и убирать различные поля результирующей таблицы. Посмотреть, как это выглядит наглядно, вы можете на листе DinamicQuery.

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

При использовании данного конструктора отчета для отображения обязательно должна быть выбрана хотя бы одна мера и одно измерение.

Укажите интересующий вас интервал дат в пределах от 24.09.2015 по 25.10.2015, поскольку данные, сгенерированные для тестовой базы и хранящиеся на листе DB, содержат только этот диапазон. Укажите интересующий вас интервал дат Далее в конструкторе отчетов вы можете изменить название полей и оно будет отображаться в финальной таблице. Также можете указать, какие поля требуется вывести в отчет. Еще раз напомню, что необходимо указать как минимум одну меру и одно измерение. В конструкторе отчетов вы можете изменить название полей Во время изменения каких-либо параметров отчет под конструктором будет изменяться динамически. Под конструктором динамически будет изменяться отчет в соответствии с указанными параметрами Формула, которая изменяет запрос в зависимости от настроенных параметров, выглядит так:

=query(DB!A1:L1143;"
Select "&amp;join(",";filter(C7:C11;B7:B11="Да"))&amp;"
WHERE
(A &gt;= date'"&amp;C2&amp;"-"&amp;D2&amp;"-"&amp;E2&amp;"'
AND A &lt;= date'"&amp;C3&amp;"-"&amp;D3&amp;"-"&amp;E3&amp;"')
GROUP BY "&amp;join(",";filter(C7:C11;B7:B11="Да";D7:D11="Измерение"))&amp;"
LABEL "&amp;join(",";filter(E7:E11;B7:B11="Да")))

На самом деле функция Query может разрастаться до размеров программы и динамически изменять выводимую информацию в зависимости от указанных на рабочем листе данных.

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

Готов отвечать на вопросы в комментариях :)

Обнаружили ошибку? Выделите ее и нажмите Ctrl + Enter.

Комментарии (53 )

  1. 0
    9 дней назад

    Привет! Есть задача. 

    Суть таблицы в том, что есть дата покупки + товар, таблица естественно больше и объёмнее по факту, обновляется ежедневно.

    Вопрос, как сделать, чтобы кверёй данные подтягивались только за сегодняшний день? 
    То есть без указания конкретной даты, именно с помощью условия. 

  2. 0
    10 дней назад

    Здравствуйте. Имеется проблема в части кляузы ORDER BY:

    У меня запрос с группировкой

    ={QUERY({'Progress report'!P:P,'Progress report'!Q:Q,'Progress report'!R:R,'Progress report'!S:S,'Progress report'!C:C,'Progress report'!G:G},

    "select Col1, Sum(Col2), max(Col3), sum(Col4) where (Col5='YES') and (Col6="&D4&") Group by Col1 Order by min(row(Co1)) 

    Label Col1 'DESCRIPTION',sum(Col2) 'HOURS', max(Col3) 'RATE',sum(Col4) 'AMOUNT'",0);

    {"","","TOTAL",sum(QUERY({'Progress report'!S:S,'Progress report'!C:C,'Progress report'!G:G}, "select sum(Col1) where (Col2='YES') and (Col3="&D4&")"),0 )};

    {"","","",""};{"Signature: __________________________________","","",""}}

    Мне нужно сделать сортировку, как в исходной таблице, то есть по min(row(Col1)) (раньше встречается в исходной таблице - значит раньше будет в результирующей таблице), однако запрос на этом выдает ошибку типа "Error In ARRAY_LITERAL, an Array Literal was missing values for one or more rows." 

    При этом сортировка вполне себе работает по Col1 или Sum(Col2), max(Col3), sum(Col4) 

    Я уже мозг сломала. Не подскажете вариант решения проблемы?

    • 0
      Natalya Sokolova
      9 дней назад

      Добрый день!

      функции row в принципе нет в языке запросов который используется внутри QUERY, поэтому ничего и не работает.

      Нужны примеры и доступы к доксу, чтобы лучше понять задачу.

      • 1
        Георгий Рябой
        9 дней назад

        Спасибо большое за участие! Я все же решила поставленную задачу путем добавления в исходной таблице столбца с "номером столбца" и включения его в запрос.

        Статья кстати классная.

  3. 0
    26 дней назад

     Дорого дня! не могу найти информацию в чем может быть проблема. 3 месяца все работало исправно. Ничего не менялось в формуле и не редактировалось. данные перестали подгружаться из разных таблц. даные в таблицах не дошли до 999. в чем может быть проблема, кто может подсказать? 

    Ошибка

    Не удалось интерпретировать query string. Подробности: Параметр 2 в функции QUERY:TypeError: Cannot read property 'get' of undefined

    • 0
      Alexandr Harchenko
      26 дней назад
      причем часть таблиц подгружается. не подгружаеться только определённые таблицы
      • 0
        Alexandr Harchenko
        25 дней назад

        Здравствуйте, Александр!

        Уточнил у Алексея Селезнева, здесь без ссылки на саму таблицу не разобраться. Так не очень ясно, в чем вообще дело.
  4. 0
    месяц назад

    1. Здравствуйте! Хочу описать задачу. Если сможете, подскажите, как её решить!

    2. Имеется онлайн таблица, в которую подразделения ( их 7) вносят на свой лист данные за каждый день. На каждую дату приходится 3 строчки (Заполнение строчек зависит от количества событий - их может быть от 1 до 3. Соответственно заполняются от 1 до 3 строчек (по 10 ячеек)).

    3. Сводную таблицу я построил. На указанное число в сводную таблицу заносятся заполненные строчки по подразделениям.

    4. Необходимо построить таблицу за интервал времени (например: неделя). Так, чтобы в интервальную таблицу заносились данные (текст) построчно. Например: В 1-м подразделении за неделю было 10 событий. Нужно сделать так, чтобы пустые строчки в датах не переносились в таблицу за интервал, а только заполненные.  Согласно условию (пункт 2) на каждую дату выделено 3 строчки. Но заполнена может быть одна, две или три строки. Необходимо, чтобы в таблицу за интервал попадали только заполненные строчки (10 ячеек в одной строчке).  Ниже были данные по второму подразделению, третьему и т.д.

    И всё это должно быть за интервал времени. (например: неделя) Чтобы можно было указать начальную и конечную дату.

    • 0
      Сергей Литвиненко
      месяц назад

      Добрый день.

      По описанию сложно понять как именно решить вашу задачу. Если возможно, покажите, пожалуйста, пример, как выглядят исходные данные и как должен выглядеть результат. Данные могут быть абсолютно любыми, главное понять их структуру на входе и как они должны выглядеть на выходе.

  5. 0
    2 месяца назад

    Добрый день!

    Для функции:

    =query(DB!A1:L1143;"SELECT

            B,

            D,

            avg(G),

            max(G),

            min(G),

            count(G),

            sum(G)

    GROUP BY B, D

    ORDER BY B

    LABEL

            B 'Кампания',

            D 'Город',

            avg(G) 'Среднее',

            max(G) 'Максимальное',

            min(G) 'Минимальное',

            count(G) 'Количество',

            sum(G) 'Общая сумма'

    FORMAT

            avg(G) '0.00'")

    нужно посчитать промежуточные итоги - вычислить sum(G) для каждой Кампании и отобразить значения либо под каждой группой (как в сводной таблице), либо в конце таблицы несколькими строками.

    Возможно ли это сделать "в лоб", если нет - в каком направлении искать решение?

    Спасибо!

  6. 0
    3 месяца назад

    Здравствуйте.

    Имеется таблица, где на листе "Молодняк" столбцы S:ФИО, T:Аккаунт

    Евдокимов Юрий Александрович| mapo.ardatov@gmail.com

    Евдокимов Юрий Александрович| mapo.ardatov@gmail.com

    Луконина Ольга Николаевна        |obor1989@gmail.com

    Луконина Ольга Николаевна        | obor1989@gmail.com

    Луконина Ольга Николаевна        | obor1989@gmail.com

    Федаев Андрей Владимирович    |fedayev403@gmail.com

    ..................................................................     ...................................................

    Конструкция  в макросе: spreadsheet.getCurrentCell().setFormula('=query(\'Молодняк\'!1:73;"select * where T=\'mapo.ardatov@gmail.com\'")');  выбирает из листа записи, в которых значения столбца T "Аккаунт" равны указанному: mapo.ardatov@gmail.com. Как сделать выборку по текущему аккаунту Session.getUser() ?

    Спасибо.

  7. 0
    4 месяца назад

    Здравствуйте! Спасибо за отличную статью. Подскажите, пожалуйста, как извлечь определенные данные из одной ячейки? Например, в ячейке несколько фамилий "Иванов, Сидоров". Необходимо извлечь только фамилию "Сидоров". Пробовал с помощью where contains 'Сидоров', но результат не тот.

    =QUERY (A:B;"SELECT A, B WHERE B contains 'Сидоров'")

    https://docs.google.com/spreadsheets/d/12Z7RDkunOyA6tgZ7KmeFFogVA5LLHRDQJZ_-yYqDw1w/edit?usp=sharing



    • 0
      Рустам Боприев
      4 месяца назад

      Добрый день.

      Ваша формула сработала правильно, вывела только те строки, в которых есть фамилия "Сидоров".

      Какой должен быть конечный результат работы формулы? И какая должна быть логика работы при одинаковых фамилиях, например Сидоров менеджер и Сидоров механик?

      • 0
        Андрей Коваль
        4 месяца назад

        Требуемый результат должен быть вида:

         | Менеджеры | Сидоров | 
         | Механики | Сидоров | 
         | Мойщики | Сидоров | 

        Нужно изъять из ячейки, где находится несколько фамилий ("Иванов, Петров, Сидоров") одну задаваемую фамилию и вывести в столбик данные по отдельности. После изъятия одной фамилии, делаем тоже самое со второй и тд. В итоге должна получится таблица вида:

         | Менеджеры | Сидоров | 
         | Механики | Сидоров | 
         | Мойщики | Сидоров | 
         | Менеджеры | Иванов | 
         | Механики | Иванов |  
         | Мойщики | Иванов | 

        Для понимания приведу другой пример. Есть автопарк, состоящий из 10 машин. По ним ежедневно проводится обслуживание: осмотр, заправка, мойка. В первый столбик с названием "Госномер" заносится номер машины, во второй столбик под названием "Вид обслуживания", заносятся работы, проведенные с машиной. Из этой таблицы нужно составить отчет с сортировкой по каждому виду обслуживания: какие машины мылись, какие заправлялись, какие осматривали, такого вида:

         | В585НЕ777 | Мойка | 
         | В896OT777 | Мойка | 
         | К225EE777 | Мойка | 
         | В585НЕ777 | Заправка | 
         | В896OT777 | Заправка |  
         | К225EE777 | Заправка | 
         | В896OT777 | Осмотр |   
         | К225EE777 | Осмотр | 

        Первоначальный вид таблицы-донора:

         | В585НЕ777 | Мойка, Заправка | 
         | В896OT777 | Мойка, Заправка, Осмотр | 
         | К225EE777 | Мойка, Заправка, Осмотр | 

        • 0
          Рустам Боприев
          4 месяца назад

          Добрый день, тут вам QUERY не поможет по той причине, что у вас изначально логически неправильно базовая таблица собирается, а QUERY не в состоянии из такого вида | К225EE777 | Мойка, Заправка, Осмотр |  привести в правильный. 

          Есть такая концепция хранения данных tidy data, она довольно проста, смысл её заключается в том, что данные надо хранить придерживаясь правила 1 строка = 1 наблюдение, 1 столбец = 1 свойство этого наблюдения, в общем вам изначально надо наполнять таблицу пот так. 


          Гос номер | Тип работ

          К225EE777 |Мойка

          К225EE777 |Заправка

          К225EE777 |Осмотр 


          Т.е. то в общем к какому виду вы и хотите её привести. Если вы будете базовую таблицу заполнять так то можно будет уже что то дальше делать, 


          К сожалению QUERY конечно очень мощная функция но такие фокусы делать не умеет, это разве что писать скрипт на языке например R, с использованием пакета tidyr.

          • 0
            Алексей Селезнёв
            3 месяца назад

            Алексей, добрый день и спасибо за ответ.

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

            Однако я получил-таки желаемый результат при помощи Query, но топорным способом. Сначала скопировал все строки, где было нужное слово при помощи contains, затем в следующем столбце при помощь новой QUERY изъял строки, где было следующее нужное слово и т.д. В итоге получил большую горизонтальную таблицу вида:

            Гос номер | Тип работ | Гос номер | Тип работ | Гос номер | Тип работ
            К225EE777 |Мойка        | К225EE777 |Осмотр     | К225EE777 |Заправка 
            К586EE777 |Мойка        | К586EE777 |Осмотр

            Затем вывел их в одну вертикальную таблицу (она и требовалась) с двумя столбцами при помощи фигурных скобок  ={QUERY (........); QUERY (............); QUERY (............)} и получил:

            Гос номер | Тип работ
            К225EE777 |Мойка
            К586EE777 |Мойка
            К225EE777 |Осмотр
            К586EE777 |Осмотр
            К225EE777 |Заправка


            Грубо, некрасиво, но задачу решил. Спасибо, что не оставляете вопросы без ответов.

  8. 0
    4 месяца назад

    Добрый день. Проблема следующая.

    =query('Статистика КЛ КРД'!A2:M575;"

    Select A, B, M, L, K

    WHERE

    (B = 'БН')

    and (M>0 or L>0)

    ORDER BY K

    ")

    Все работает замечательно, как только я ставлю диапазон больше, например, A2:M576 хотя бы, то в выгрузке пропадают все данные из столбца А, а остальные данные есть

    • 0
      Виталий Садетдинов
      4 месяца назад

      К сожаление с подобной проблемой никогда не сталкивался, по описанию похоже на баг в самой функции.

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

      • 0
        Алексей Селезнёв
        4 месяца назад
        Проблема уже решена, Данные брались из нескольких документов и видимо в каком-то из них ему не нравился формат ячейки и просто не выводил данные. Везде где можно было поменял формат и все заработало
  9. 0
    7 месяцев назад

    Спасибо.

    можно ли вместо , например, A и B использовать Date и Campaign?

    select Date, Campaign where Date = и т.д.?


  10. 0
    7 месяцев назад

    query(IMPORTRANGE) Если где то в ячейке стоит примечание то почему то данные не импотритуються с той ячейки просто отображается пустая ячейка. Есть какое то решение? 

  11. 0
    8 месяцев назад

    И еще раз добрый день. Вопрос следующий. Нужно при следующем запросе

    =query(DB!A2:K10000;"

    SELECT A, B, D, E, F, G, H, J, K

    WHERE

     J = I1")


    чтобы значение J брало из ячейки в текущей таблице (I1). 

    Возможно ли это? может где-то можно объявить переменную, присвоить ей значение ячейки в текущей таблице и в дальнейшем сделать запрос ссылаясь на эту переменную?

    • 0
      Артем Кабицкий
      8 месяцев назад

      WHERE J = '"&I1&"'

      Т.е. последовательно символы ' " & перед ссылкой на ячейку

      • 0
        Андрей Коваль
        8 месяцев назад

        Приветствую!

        А как сделать чтобы вместо '"&I1&"' тянуло диапазон условий?
        Например, есть перечень запросов + город и в соседнем столбце прописываю перечень из 15 минус-городов. И нужно вывести список ключей за исключением тех, в которых есть вхождения городов из второго столбца?
        Грубо говоря - отсеять минус слова, но не прописывать каждый раз новую ячейку с условием, а дать диапазон из которого нужно тянуть условия (вхождения), которые нужно отминусовать, при этом имея возможность докидывать или удалять минус-слова из столбца.
        Пробовал так, но не работает:
        =QUERY(A:B;"

        select A

        where not

        A contains ' "&B:B&" ' ")

        Какой синтаксис нужно использовать вместо "&B:B&" чтоб подтянуть минус-слова из столбца В?

  12. 0
    8 месяцев назад

    Добрый день. Очень информативная статья. Спасибо большое. Столкнулся с одной неприятной проблемой. Не работает выбор ячейки по тексту на кириллице. Даже в вашем примере пробовал английские названия менять на кириллицу - query ее просто не понимает. По числам и английским словам выборка работает. Для примера

    =query(DB!A2:K10000;"

    SELECT A, B, D, E, F, G, H, J, K

    WHERE

    J='мишуги 10'

    ")


    И ничего не выдает(

    • 0
      Артем Кабицкий
      8 месяцев назад

      Добрый день. Возможно у вас какая-то ошибка в синтаксисе или типах данных в таблице. Кириллица работает



      • 0
        Андрей Коваль
        8 месяцев назад
        Да. Вы были правы. Проблема была в формате ячеек. Там был выставлен автоматический форма (числа, текст). Как поменял на текстовый - запрос сразу стал работать.
  13. 0
    9 месяцев назад

    Привет!


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


    Вот как тут: ' " &A1& " '


    Почему мы просто не можем напрямую обратится к ячейке, а заносим ее в формулу вот таким образом ' " & ?


    Спасибо!


  14. 0
    год назад
    Добрый день Алексей. Спасибо за вашу стать, открыл для себя эту волшебную функцию query Подскажите пожалуйста, даже не знаю возможно ли это, но есть необходимость брать строку и в ней оставлять только те значения которые не равн нулю. т.е. в месяце 31 день но в строке циффры не каждый день будут и хотелось бы импортировать не всю строку а только те ячейки где есть числа. Вот как у ня сделано, но это не работает, он выдает в результате всю строку. =QUERY(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1_hF8OAD5kPQhZSwXtQbNH86SZuw7ba5zJZqZny6NS1M/edit#gid=2054868386";"Фев18!b2:ai2");"SELECT * WHERE Col7 > 0")
  15. 0
    год назад

    Добрый  вечер. Спасибо  огромное  за  статью - разобралась легко и быстро.  

    Вопрос  состоит в  следующем:  есть  8  листов  с  одинаковой  информацией. Часть  информации  с  этих 8  листов  собирается  в "Свод".  На  листе  "Свод"  необходимо  добавить  еще  дополнительную  информацию.  При внесении  на  любой  из  других  листов строки с внесенной информацией  не  двигаются вместе  с  собранной  из  других  листов  https://docs.google.com/spreadsheets/d/1AcvplTzyD8s-OeYvNNcmrjamjq3j1NcudnfHt_SXq6I/edit#gid=2065266902

    Как сделать  так, чтоб  внесенные комментарии  двигались  вместе  с  собранными  данными ? 

    Спасибо

  16. 0
    год назад

    Здравствуйте. Отличная статья! Помогла понять некоторые моменты. Тем не менее не смог решить одну, казалось бы, простую задачу. Нужно правильно сформировать запрос.

    Требуется из таблицы вида (Дата | Количество), содержащей количество по дням, собрать рядом аналогичную но с суммой по месяцам. То есть в первой таблице количество на каждый день, а мне надо показать сколько всего в каждом месяце.

    Пробовал такой запрос

    =QUERY(A1:B;"select A, sum(B) group by month(A) order by A")

    получаю ошибку

    Не удалось интерпретировать query string. Подробности: Параметр 2 в функции QUERY:ADD_COL_TO_GROUP_BY_OR_AGG: A

    Я понимаю что по указанному правилу он что-то там не может выбрать но не знаю как решить. Вероятно такой запрос вообще невозможен?

    Табличка с примером 

    https://docs.google.com/spreadsheets/d/1GrmYnvUIcnVRxuP3TvOmxJKml5wZo1m6kpvNlqDP4PM/edit?usp=sharing

    PS. Окончательной задачай является построение общего графика из нескольких таких таблиц. Быть может есть примеры готовые?

  17. 0
    год назад

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

    Необходимо сделать динамический фильтр с несколькими вариантами фильтрации. Это получилось сделать , но работает не совсем так как хотелось бы.


    Вот ссылка на рабочий файл 

    https://docs.google.com/spreadsheets/d/1ZdGbULGqDJXBcdAGvQ_Uy-RFeOnwqKk64H4MJzgHAvY/edit#gid=683542617


    Задача в том чтобы фильтровть результаты на оснвое данных в ячейках B2:B5.  Это Дата, Касса и Статья. Но мне не понятно как настроить фильтр чтобы мы могли при необходимости фильтровать не по всем параметрам, а по нескольким.

    К примеру, видеть все операции по   Касса 1  не зависимо от "Статьи" 

    или Статья "Сервисы"  и видеть все операции не зависимо от "Кассы".

    Формула задана в ячейку А16. 

    =QUERY('raw data'!A1:E2986;"select A,B,C,D,E
    WHERE
    (B >= date'"
    &C2&"-"&D2&"-"&E2&"'
    AND B <= date'"
    &C3&"-"&D3&"-"&E3&"'
    AND E='"
    &B4&"'

    AND C='"
    &B5&"')")


    То есть условия фильтра типа E='"&B4&"

    но если B4 пустая, то фильтр не применять.  


    Буду очень благодарен за помощь. Доступ открыл на почту  alsey.netpeak@gmail.com




    • 0
      Friend
      год назад

      Здравствуйте, возможность игнорирования фильтров вполне реализуема если добавить в список какое то значение которое будет отключать фильтр, к примеру я добавил в фильтре по статьям значение "Не фильтровать", далее в QUERY через функцию IF добавил проверку, если в ячейке B4 выбрано значение "Не фильтровать" то не добавлять в запрос фильтр по статье, в формуле это выглядит вот так:

      =QUERY('raw data'!A1:E2986;"select A,B,C,D,E
      WHERE
      (B >= date'"
      &C2&"-"&D2&"-"&E2&"'
      AND B <= date'"
      &C3&"-"&D3&"-"&E3&"'"&if(B4="Не фильтровать";"";"
      AND E='"
      &B4&"'")&"

      AND C='"
      &B5&"')")


      Вот часть которая проверяет выбрано ли значение Не фильтровать, if(B4="Не фильтровать";"";"AND E='"&B4&"'"), логика работы этой функции вполне простая.




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

  18. 0
    год назад
    Можете пояснить как делать Get запросы, просто какой нибудь пример, я ознакомился со всей статьей все классно описано, но как делать Get запросы не понял
    • 0
      Денис Макаров
      год назад

      Здравствуйте , не совсем понял при чём Get запросы к функции QUERY?

      Метод запроса POST предназначен для запроса, при котором веб-сервер принимает данные, заключённые в тело сообщения, для хранения. Он часто используется для загрузки файла или представления заполненной веб-формы. В отличие от него, метод HTTP GET предназначен для получения информации от сервера.


      В общем POST  и GET запросы используются для обмена информацией с сервером, к примеру через API интерфейс, функция QUERY не работает с http запросами.

  19. 0
    год назад

    Подскажите, пожалуйста, можно ли организовать суммирование по произведению ячеек двух диапазонов? Или все же придется добавлять дополнительный столбец в исходное полотно?

    =QUERY('Заказы'!A1:Q50000;
    "SELECT sum(I*J)
    PIVOT L"
    )

    Пытаюсь сделать это в такой форме, однако получаю QUERY:PARSE_ERROR: Encountered "*" at line 1, column 13. Was expecting: ")" Подозреваю, что синтаксис этой функции не позволяет перемножать данные

    • 0
      Aaz Potter
      год назад

      Здравствуйте, совершенно верно пока что QUERY не поддерживает такие операции, можно только через sum(I) * sum(J) но вернёт она несколько иной результат, поэтому пока пользуйтесь либо доп столбцом, либо надо использовать результат выполнения Query внутри другой Query которая будет как бы надзапросом.


      пример:

      =QUERY(QUERY(A1:Q50000; "SELECT I*J"); "SELECT SUM(Col1)")
      • 1
        Алексей Селезнёв
        год назад

        Большое спасибо за ответ и отдельное огромное спасибо за статью!

  20. 0
    год назад

    Спасибо за очень подробное руководство, но кажется я страдаю кривизной рук и не могу найти свою ошибку. Опишу задачу:

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

    Загрузка всей таблицы работает, но когда я пытаюсь задать параметр выгружать только те строчки где в колонке Y значение КМС у меня вылазит ошибка

    https://docs.google.com/spreadsheets/d/1Ojbo21I1B_x5o1SuDbPseotTF84zxNDm7EX87LPuU-M/edit?usp=sharing доступ на редактирование дал, помогите разобраться пожалуйста

    • 0
      Максим Юрьев
      год назад

      Добрый день Максим.

      Проблема в том, что вы внутри запроса используете двойные кавычки, и спредшитс не понимает почему вы две строки без каких либо разделителей и конкатенации используете, в общем внутри запроса всегда используйте только одинарные кавычки и всё заработает.

      Во вторых если в первом аргументе вы используете не ссылку на диапазон а массив (функция IMPORTRANGE возвращает именно массив) обращайтесь к столбцам не по названию (A,B,C ...) а по номеру например Col1, Col2, Col3 ...

      Правильная формула выглядит так: =query(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1Ojbo21I1B_x5o1SuDbPseotTF84zxNDm7EX87LPuU-M/edit";"A:Y"); "SELECT * WHERE (Col25='КМС')"; -1)



      • 1
        Алексей Селезнёв
        год назад
        Спасибо огромное) Вы просто упростили мне жизнь на год вперед ) Не придется раскопировать все это каждый день. Да я уже увидел вашу правку. Увидел в чем была проблема. Огромный плюс вам в карму )

Чтобы оставить комментарий, необходимо авторизироваться

Подписаться

на самую полезную рассылку по интернет-маркетингу

Самое

обсуждаемое популярное читаемое

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