Запросы (query) в Google Docs, как инструмент для профессиональной разработки отчетов и приложений
Многие пользуются сводными таблицами в Эксель и им приходится сталкиваться со следующими недостатками:
Когда ты немного разберешься, то можешь перейти на следующий уровень: самообучение. Мне повезло, и я смог быстро обучиться премудростям запросов благодаря этому ресурсу, где собраны все штуки по SQL. Будь внимателен, некоторые из них не работают в Googgle Docs. Но и того, что включено в функционал, тебе должно с головой хватить.
Если ты хочешь получить основные фишки и трюки, которые узнал я, то предлагаю тебе их просмотреть на странице, посвященной теме запросов в группе Вконтакте.
- таблицы обновляется вручную. После того, как данные в исходной таблице изменились, информация в сводной таблице уже неверна!
- невозможно сделать прямую ссылку на ячейку из сводной таблицы. То есть нельзя подключить данные их нее в другие расчеты
- Негибкость расчетов самой сводной таблицы. Другими словами, если я хочу сделать что-либо посложнее простого выбора полей и суммы по ним, мне тяжело будет настроить и получить то, что я хочу.
В этой статье ты увидишь, как все эти проблемы решаются посредством всего оной формулы в табличном редакторе Google Docs! Она позволяет тебе:
- Самому руководить выборкой данных
- Сортировать
- Выбирать часть данных по любым критериям и условиям
- Группировать и суммировать, рассчитывать агрегированные показатели (сумма, среднее)
- Располагать данные по столбцам
- Можно даже добавлять собственные колонки, которых не было в исходнике!
- Получить динамически обновляемую сводную таблицу
- Она не требует обновления. Она моментально обновляет цифры и данные при изменении в исходной таблице
- Она сама определяет необходимое количество сток и столбцов
- На нее можно делать ссылки в формулах и новые запросы!
↑ на этом примере показан простенький запрос с условием. Ты легко научишься делать любые выборки данных при помощи всего одной формулы!
Запрос в таблице G-Docs -- это простая формула, которая автоматически размножается вниз и вправо в зависимости от текста запроса. Процесс знакомства с запросами состоит из 2-х этапов:
- изучение языка SQL, на котором они работают. То есть изучение самих запросов
- построение собственных запросов.
Как быстро научиться пользоваться запросами query в Google Docs
Язык SQL очень прост и похож на обычный язык общения. Немного знания английского тебе не помешает, и ты поймешь:
SELECT (выбрать) “колонки, которые ты хочешь видеть”
WHERE (где) “твой фильтр колонок и данных”
GROUP BY (группировать) “если ты хочешь просуммировать данные”
ORDER BY (сортировать) “если ты хочешь сортировать результаты”
Создать свой первый запрос и научиться азам тебе поможет это видео:
↑ Все примеры, которые используются в этом видео, качай здесьКогда ты немного разберешься, то можешь перейти на следующий уровень: самообучение. Мне повезло, и я смог быстро обучиться премудростям запросов благодаря этому ресурсу, где собраны все штуки по SQL. Будь внимателен, некоторые из них не работают в Googgle Docs. Но и того, что включено в функционал, тебе должно с головой хватить.
Если ты хочешь получить основные фишки и трюки, которые узнал я, то предлагаю тебе их просмотреть на странице, посвященной теме запросов в группе Вконтакте.
Спасибо, попробую что-нибудь сваять. Максим, если уж ты изучаешь google docs, можно тебе задать вопрос по этому поводу? Я два дня уже лажу по инету с целью найти какой-нибудь облачный сервис, который можно использовать для простейшего учета движения матералов по складам. Есть небольшая организация, имеющая некоторые материальные ценности, находящиеся в разных местах и у разных ответственных лиц. Нужно сваять что-то, чтобы вести контроль над всем этим барахлом... т.е. кто, кому, когда выдал, в каком состоянии, когда вернул и в каком состоянии. Притом это должно быть доступно в разных местах определенным лицам. Чувствую, что лучшим вариантом был бы гугловский сервис, но проблема в том, что должны быть две независимых базы данных, связанных между собой: 1 база - карточки самих материалов. 2 база - журнал выдачи, который связан с определенными карточками. Можно ли это организовать? Очень надеюсь на ваш ответ...
ОтветитьУдалитьДля решения складского учета все же лучше специальная программа, например "Мой склад". Если делаешь в доках, сразу готовь правильную базу товаров, наподобие этого примера: https://docs.google.com/spreadsheets/d/1p_JW1rdFXyd9ApOJOGo96iAsI344K7a3hprZK0Dub_c/edit#gid=0
УдалитьВ другом доке делаешь журнал движений товаров, подтягивая данные о товаре через его код. Тут поможет формула importrange, которая осуществляет импорт из одного дока в другой. Так ты добьешься того, что каждое ответсвенное лицо будет видеть ту информацию, за которую оно ответственно.
Добрый день! Спасибо, интересная статья!
ОтветитьУдалитьПодскажите, пожалуйста, можно ли запрос использовать в следующем случае:
— есть база данных (3 столбца: год, страна, ввп)
— есть таблица (по строкам — годы, в столбцах — названия стран)
Хочется с помощью формулы получать значение ввп из базы данных, которое соответствует строке и столбцу.
Пример по ссылке:
https://docs.google.com/spreadsheets/d/1D2NXiI9DRqgoi3smUP6aJoVPEV-vsFnQvPYpQdCfm7Y/edit?usp=sharing
У меня что-то не работает.. Буду очень благодарен за помощь.
Добрый день!
УдалитьПопробуйте так:
https://docs.google.com/spreadsheets/d/1NVZc_A7QFnZ5yX8KQqI8VcelwTLS8VJURYfHr6_ktQY/edit#gid=539461952
Спасибо! Отличное решение.
УдалитьЗдравствуйте Макс! Спасибо за статью! Подскажите, можно ли в запросе, в фильтре where указать не значение, а дать ссылку на ячейку, содержащую функцию, к примеру today(). Пытался сделать запрос, чтобы он автоматически при открытии показывал данные из базы на текущую дату, что-то типа select A, B, C... where A = N1. В ячейке N1 значение =today(). Ничего не выходит. Очень надеюсь на Вашу помощь. С уважением, Михаил С.
ОтветитьУдалитьДобрый день! Михаил, возможно, не выходит из-за специфики работы с датой в Доках. Посмотрите этот пример:
Удалитьhttps://docs.google.com/spreadsheets/d/1kIEvHgftqPBQZ_ymMJpkdf4mXohSan0bWdTzhFnoNno/edit?usp=sharing
Надеюсь, поможет.
Спасибо огромное! Все действительно работает. У меня просто явные пробелы с синтаксисом. Если Вы знаете, может подскажете какую-нибудь книжку (желательно на русском) где бы это все расписывалось поподробней? Еще раз спасибо за помощь и удачи Вам.
УдалитьМихаил, на русском, к сожалению, не знаю. Сам учился азам на этом сайте:
Удалитьhttp://www.w3schools.com/sql/sql_select.asp
Возможности SQL в Доках ограничены, и не все функции срабатывают. Поэтому нужно быть готовым к тому, что не все получится.
добрый вечер!
ОтветитьУдалитьпытаюсь сделать такой запрос, а он выдает ошибку:
=query(importrange("0AkBKGiwipq7ddFIzNDF4bHBUMTNMQ3F3VXJrN2VUUWc#gid=16","data!A:R"),"select * where N contains 'mano'")
а если запрашиваю в самой таблице (без importramge)
=query(A:R;"select A,B,J,K where N contains 'mano' ")
то запрос работает.
что делать?
Добрый!
Удалитьвместо букв необходимо использовать Col1, Col2, Col3 и так далее:
=query(importrange("0AkBKGiwipq7ddFIzNDF4bHBUMTNMQ3F3VXJrN2VUUWc#gid=16","data!A:R"),"select * where Col19 contains 'mano'")
Добрый день!Подскажите пожалуйста,как объединить эти две формулы в одну рабочую с помощью SQL?если можно наглядным примером, просто с теорией в практику этот importrange вообще не получается внедрить(.
ОтветитьУдалить=SUM(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1O1DFUqpsf5MNXJVXPFg9Nul5sRgpAUyGm9n-6rWcmDY/edit#gid=0"; "Лист1!C4:C6")) и =SUMIF(D4:D6; "1"; C4:C6).
по отдельности все работает, а вместе нет.С SQL самому без наглядного примера не получается совладать.
=SUM(query(IMPORTRANGE("https://docs.google.com/spreadsheets/d/1WyqFWHEnkKD0wuh0IZ80QrboHaEOJkQ8IquBDzQSVlM/edit#gid=0"; "Лист1!C4:C6");"Select Col1 where Col1 = 1"))
УдалитьСпасибо.Этот запрос понятен тут мы выбираем столбец Col 1 и суммируем в нем все числа с необходимым значением(в данном случае значение 1 ).У меня не получается более сложный вариант.Попробую описать проблему.
УдалитьЕсть 2 разных таблицы.В первой список покупателей,а во второй товар с суммой которую они заплатили.Мне в первой нужно получить сумму из второй табл,но с отбором по продавцу, который им его продал.В google таблицах есть функция для суммирования в диапазоне с условием (SUMIF),но она не работает совместно с функцией импорта (IMPORTRANGE),Вот ссылки на примеры таблиц.https://docs.google.com/spreadsheets/d/1TZ0jWzh6o6AmprDrkUT3n-rUqDSgkHmrRdkPpHWEFuA/edit#gid=0 и https://docs.google.com/spreadsheets/d/1T-p02ENPoEZRWuf4PrmiYHV_L4E4yR3TXKZc2e_pSQk/edit#gid=0
Что то вроде этого =SUM(QUERY(FILTER(A1:C9;C1:C9=2);"select Col1 were Col3 = 2")) только вместе с (IMPORTRANGE),хотя и эта то не работает(.
УдалитьВ общем я хочу получить сумму из определенного диапазона ячеек находящихся в другой таблице, напротив которых стоит определенный критерий (цифра к примеру 1,2 или 3...)
УдалитьСпасибо еще раз часть задачи я решил,но остались вопросы вот что у меня получилось
Удалить=SUM(QUERY(IMPORTRANGE(E6;E7);"select Col1 where Col3 = 2") а вопрос вот какой
Как выставить более точный диапазон Cal1 и Cal2, то есть фильтр в этрх столбцах на диапазон ячеек (к примеру Cal1 выбирать в диапазоне с A1 по A 25 , а не весь столбик)
Как будто все варианты вокруг решения. Допустим, нам нужно получить сумму по колонке С, а в колонке В, А находятся требуемые критерии. Тогда решением будет формула типа:
Удалить= SUM(QUERY( IMPORTRANGE... ;"select Col3 where Col1 = 1 and Col2 = 2"))
такая конструкция обязана сработать.
да вы правы я после того как отписал последний вопрос решил эту задачу,но интересна была сама по себе функция фильтра.
УдалитьЯ как видите именно после вашего примера начал проникаться логикой работы.Кстати спасибо за оперативные подсказки,ответы и Примеры.Надеюсь я не слишком Вас обременяю.
Возник такой вопрос по ходу он косвенный и я пока что в нем особо не разбирался, но думаю скоро возможно понадобиться применить подобные инструменты.Суть в том ,чтобы сравнивать одну таблицу (не все к примеру 2 -3 столбца) с другой на предмет выявления соответствия значений (цифр) в моем случае и помечать их определенным знаком или цветом выделения.Пока не пробовал,но если у вас с вашим опытом есть какие либо подсказки,то с удовольствием использую их в дальнейшем познавание SQL и Google.
Да кстати я попозже подготовлю несколько примеров,с ссылками на таблицы (более понятными. Для вашего блога ).Я думаю это решение многих заинтересует.Дело в том,что я долго искал решение и нашел его именно отчасти у Вас и частично еще в одном блоге.Если Вам такие примеры,знания и общительность объединить,то цены бы такому ресурсу не было бы.
УдалитьИ еще вопрос в запросе который меня так терзал в конечном результате выдается одно число суммы в нужном диапазоне(с ссылкой на ячейки в которых формулы и запросы),а можно ли сделать так, чтобы была возможность развертывания импортируемых сумм запроса?
УдалитьВторой блог Максим это кстати ваш же блог)
УдалитьДобрый день!
УдалитьДля сравнения двух таблиц по нескольким критериям нужно сравнивать их по уникальному ключу. Например, описание строки в колонках А, В, С, в колонке D -- цифры, которые необходимо сравнить. Если нет уникального ключа строки, то его можно создать, сцепив А, В, С:
= A & B & C
Тогда их можно сравнивать по сцепке.
Чтобы подкрашивать, поищите статьи про Условное форматирование (Condition Formattting).
Если будут примеры для блога, приму, спасибо.
Чтобы разворачивать суммы импорта по группам, нужно избавиться от функции SUM, она всегда выдает только один результат.
= SUM(QUERY( IMPORTRANGE... ;"select Col3 where Col1 = 1 and Col2 = 2"))
переделываем в
=QUERY( IMPORTRANGE... ;"select Col1, sum(Col3) where Col1 = 1 and Col2 = 2 group by Col1")
Нюанс состоит в том, что развернутую сумму можно получить только в запросе вместе с колонкой или колонками, по которым необходима сумма.
Успехов!
Добрый день Максим.Такой вопрос.
УдалитьКак в этом запросе установить условие при котором в запрос не попадут пустые строки.
select Col1 where Col3 is not null and Col3 = 3.Пробовал так но не выходит.Просто не хочу чтобы выскакивало N/A при отсутствии значений в ячейке.
Точнее ошибку он выдает если нет хотя бы одного бы одного значения которое бы удовлетворяло условие,а я хочу чтобы в этом случае выдавал 0.
ОтветитьУдалитьПочему бы не использовать простую формулу IFERROR?
УдалитьСтранно я об этом даже не подумал Спасибо! это действительно решает этот нюанс.-)
УдалитьКстати сегодня столкнулся с одним нюансом,но не понял по чему так происходит.Когда я ввел выражение (select Col1 where Col3 = 1 and Col2 = "Петров") в другой таблице (она более старая),то мне выкинули ошибку я заменил (Col3 = 1,.....) на (B = 1,.....), а ( "Петров") на ( 'Петров') и все заработало в чем разница? И почему не воспринимается (Col1,...) ?
УдалитьНе удалось интерпретировать query string. Подробности: Параметр 2 в функции QUERY:NO_COLUMNR ,это что за ошибка вроде все правильно (SELECT R WHERE Q = 1 and I = 'Аксенова Елена Александровна')?
УдалитьДобрый день Максим.Возник такой вопрос.С импортом данных разобрался импортировал и сделал
ОтветитьУдалитьнужную грепировку вот (select Col5, Col8, Col368, Col369, Col370, Col371, Col372 where Col368 >= 0 ORDER BY Col8), но ка в этот запрос добавить сумму в конце каждого столбика Col368-Col372 с условием по одному из столбцов? Может писать запрос в запросе или это можно более компактно сделать.Сам попробовав пока глухо.
Кстати Максим заметил несколько нюансов в этих таблицах Вы о них не упоминали, но возможно,как и мне кому то пригодятся.
ОтветитьУдалитьВо первых простой запрос пишется, как с нумерацией (Col1,Col2......), так и с (A,B.....),а вот если использовать (IMPORTRANGE), то только (Col1,Col2......) при этом диапазон указывается в (A,B....)
Во вторых, если есть нужда выгрузки в xls запроса, то естественно нужно добавить (IMPORTRANGE) иначе в xls получается некорректная ссылка.
И в третьих при вычислении в любых массивах ни запрос ни формула в большинстве случаев не видит скрытых строк (я долго голову ломал) почему в таблице суммируются не все значения ячеек, а во втором случае вообще импортировались данные как текст а не число,при этом как не настраивал форматирование и другие пляски с бубном ни чего не помогало.В результате очистил все значения исходной таблице помогло.Потом под правами админа смотрел историю и нашел причины)).
Кстати Максим я заметил, что запрос который я привел выше пустые строки не попадают не смотря на то, что там нет ( is not null).
УдалитьЭтот комментарий был удален автором.
ОтветитьУдалитьДобрый день, Kate!
Удалитьв случае -1 доки автоматически определяют, сколько строк из выбранного диапазона являются заголовком. Он, наверное, думает: раз это заголовок, то брать из него данные не нужно. Предполагаю, что Гугл неправильно сам определяет количество заголовков (при параметре -1). В случае со значением 1 он должен брать все строки выбранного диапазона, кроме 1-ой, так как будет считать ее заголовком. Если же этот параметр будет 0, то Гугл возьмет все строки во внимание.
Конкретнее смогу ответить, если дадите пример.
спасибо, с этим разобралась). Второй момент: в диапазоне-исходнике у ячеек есть ссылки, при выводе их как результата после выполнения запроса ссылки пропадают. Как можно с этим бороться?
Удалитьссылки пропадают -- значит вместо них остается простой текст?
УдалитьЕсли сами ссылки были сделаны при помощи формулы HYPERLINK, то ничего не поделаешь -- даст на выходе один текст.
Но можно в исходных данных дать саму ссылку, типа http://www.doconomist.net/2014/01/query-googgle-docs.html. Её запрос должен прочитать и вернуть тоже как ссылку.
да, ссылки. которые оформлены как ссылки остаются, а от гипер ссылок остается только текст. Это Вы из личного опыта знаете, или есть где-то в документации об этом?
ОтветитьУдалитьПро документацию не уверен, но можете поискать. Это выходит из самой логики запроса SQL -- любой запрос возвращает текст, который был в исходной таблице. Ссылки же являются объектами. Но я, конечно, провел эксперимент, чтобы убедиться.
УдалитьХотя было бы прикольно запрашивать еще и объекты.
Например, в Доках можно вставлять картинки формулой:
=IMAGE("http://icons.iconarchive.com/icons/hopstarter/scrap/256/My-Pictures-icon.png")
Такую картинку запрос тоже не подтянет, увы(
Хотя, функция фильтра (например, =FILTER(A:A;B:B=2)) вытаскивает картинку, что стало для меня приятным удивлением. И ссылку она тоже вытягивает! Может, в вашем случае и простым фильтром можно воспользоваться? Это бы помогло.
кстати да, спасибо за идею, поищу как в фильтрах условия указывать
ОтветитьУдалитьесли интересно, внутри функции фильтр условия можно соединять по типу AND / OR
ОтветитьУдалить=Filter(Contacts!A3:Q,(Contacts!C3:C=B2) * (Contacts!F3:F=B3)) - AND
=Filter(Contacts!A3:Q,(Contacts!C3:C=B2) + (Contacts!F3:F=B3)) - OR
B2, B3 это поле, в которых указывается параметр для сортировки
Интересно, про OR не знал.
УдалитьМаксим Здравствуйте! вот какой вопрос.
ОтветитьУдалитьНаписал благодаря Вам в результате такой запрос (select Col5, Col8, Col368, Col369, Col370, Col371, Col372 where Col368 is not null ORDER BY Col8 label Col5 'ФИО')запрос работает.Он отбирает мне данные из другой таблички и выстраивает их в нужном порядке (конкретнее по организации),но у меня не получается добавить в него суммирование данных в столбцах.Пример могу позже выложить.Его я и хотел вам предложить в качестве примера для начинающих только доделать самому так и не вышло-).
Суммирование данных можно сделать только по столбцам, где есть числа. Для вашего примера, пусть это будут столбцы 372 и 368. Тогда запрос будет выглядеть так:
Удалитьselect Col5, Col8, Col368, Col369, Col370, sum(Col371), sum(Col372) where Col368 is not null GROUP BY Col5, Col8, Col368, Col369, Col370 ORDER BY Col8 label Col5 'ФИО'
По всем столбцам, не участвующим в суммировании необходимо прописать условие группировки GROUP BY. По столбцам суммирования нужно прописать функцию Суммы. Вообще еще можно сделать такие операции:
SUM - сумма
AVG - среднее
MIN - минимум
MAX - максимум
и, кажется еще можно делать математические операции:
sum(Col371) / (Col372) - поделить суммы
sum(Col371) + (Col372) - сложить суммы
и прочее
Помимо этого можно прописать условие по агрегатам. Для этого в текст запроса, кажется после группировки, нужно добавить условие:
HAVING sum(Col371) >= 1000
Пример выше заставит запрос выдать только те результаты, где сумма по колонке больше либо равна 1000.
Пишу по памяти, если что-то не так, прошу меня поправить.
Спасибо Максим я попробую, если что, то получится отпишу и постараюсь сформировать готовый пример, потом добавлю его тем, что спрашивал раньше(это суммирование из одной табл. в другую по условию,оно кстати тоже получилось ,но пока что не получается суммировать несколько столбцов по условию(один могу а вот с двумя отказывается) ),но это другая тема.
УдалитьЕще раз спасибо за ответ.
Прописать то я прописал, вот только общей суммы не получилось почему то ни где,а ведь если я не ошибаюсь должна была получится в конце каждого столбца.Ни как пример не доделаю,попробую может сегоддня посидеть и завтра.Под конец года работой завалили.Кстати спасибо у меня вообще запрос не работал пока вы не описали, что нужно(По всем столбцам, не участвующим в суммировании необходимо прописать условие группировки GROUP BY. По столбцам суммирования нужно прописать функцию Суммы)
УдалитьМаксим еще раз здравствуйте!Вот ссылка (https://docs.google.com/spreadsheets/d/1U0X6sZ1JMvy1u2AQ9HbGsGtBA0dljxgTA9yx7Dd8fMg/edit#gid=1334830522).Суть такова,что в результате мне нужно увидеть не только информацию в закладке отчет,но и общие суммы по столбцам после каждой фирмы.И это собственно и не выходит.Я думаю Вам тут понятно все, а я потихоньку буду подключать еще несколько табл.Они работают. Там используется (IMPORTRANGE)между файлами в качестве удаленного рабочего стола и пару интересных отчетов.Думаю можно сделать один хороший пример разбитый по пунктам)
ОтветитьУдалитьДобрый день, Zizle Zizlov!
УдалитьСпасибо за пример, это действительно интересная задача.
Предлагаю такое решение:
https://docs.google.com/spreadsheets/d/16RJam2m6s1Ci96qQcG9_KhlFXFIE8Z0_YAR6MBe6TJo/edit#gid=1334830522
На листе "суммы" прописан дополнительный запрос с суммами, на листе "отчет" данные в "запрос" сперва собираются из двух листов, а потом уже происходит по ним запрос.
Добрый вечер Максим!Огромное Спасибо за предложенное Вами решение!
УдалитьИнтересное решение Вы фактически вывели меня из тупика, но есть несколько вопросов.
1.Почему именно так (where C > 0 and A <> '' group by B label 'итого' '' а не (is not null)(хотя сам пока не попробовал).
2.Я как и говорил опробовать не успел идеи, но зачем плодить листы=это ведь должно только перегружать таблицу(поправите если я не прав).
Я предполагаю,что есть возможность все это объединить на одном листе при необходимости скрыть ненужные строки или создать запрос в запросе,но вот вопрос-какой из этих вариантов даст меньшую загрузку таблиц.Я пока что попробую что нибудь придумать и подключить, как и обещал к примеру еще одну таблицу.Ну и жду от Вас ответов или Ваших предположений по вопросам.
Кстати я думаю, что наверняка есть формула аналог (IMPORTRANGE),для решения мой задачи(поищу).
P.S.
Пробовал сам также сделать(label Col1 'ФИО', sum(Col3) 'Остаток', sum(Col4) 'Приход', sum(Col5) 'Расход')
почему то получалось обозвать только один столбец-)
буду смотреть где ошибался.
1. is not null неправильно считает, если были формулы т.к. null программа воспринимает как ничего в принципе, если в ячейке есть формула, которая выдает пустой текст (""), то ячейка уже не является null (ничем), так как в ней есть формула. Поэтому здесь срабатывает только конструкция не равно пустоте (<> '').
Удалить2. можно решить технически проще, если вспомогательный запрос разместить под основной таблицей на первом же листе. Но это не годится в случае динамически изменяемых диапазонов. Если, например, количество фирм или фамилий увеличится, то таблицу придется каждый раз переделывать. А мой способ позволяет минимизировать настройки в будущем. Еще предлагаю суммы на первом листе вывести НАД таблицей, а не под ней. Тогда, опять-таки, таблицу можно будет сделать динамической по размеру и не настраивать ее каждый раз по новой. Это все является базовыми принципами построения всяких автоматических отчетов внутри таблиц, будь то Эксель, Доки, не важно. А количество листов не должно влиять на размер файла и его быстродействие.
Но в любом случае вы вольны сделать расчеты на свое усмотрение. Сказанное выше не является догмой, это лишь мои замечания из личного опыта.
Приветствую Максим!
УдалитьСпасибо за ваши замечания и разъяснения(мотаю на УС).
Я сам не плохо понимаю в компах и ПО и вот с чем столкнулся недавно.
Хочу поделится вдруг поможет Вам или кому либо.
Сами таблицы в "Облаке" у нас достаточно большие и нагруженные формулами.На компах до того, как я пришел все работали через браузер Google,что логично,но таблицы стали часто вылетать и плохо обновляться,они перешли на Яндекс,и несколько месяцев назад тоже самое стало происходить и с ним.за 10-15 мин. табл могла вылететь раз 10.Поразмыслив посмотрел, что с ПО и обновами.Оказалось,что ПО и Обновы не ставились 2 года.Установил и обновил все необходимое ПО и Обновы,и вот интересно теперь, если выходить с Яндекса,то все глючит,но если грузить с Google браузера,то все идеально.
P.S. Это конечно не по теме запросов,но у товарища была таже самая проблема я ему посоветовал свое решение и помогло.Решил,что стоит и с Вами поделится.Вдруг кто нибудь голову ломает и постоянно матерится на вылеты и "Оппаньки"-)
Странно адаптировал ваш пример под себя вот такую ошибку выдал (Параметр 2 в функции QUERY:NO_COLUMNCol5)в примере это (Col1 в таблице клиенты )можете описать по шагам, что делает запрос.Возможно я его не правильно понимаю(читаю).
УдалитьМаксим здравствуйте!
УдалитьВот эта часть запроса понятна (select Col1, Col2, sum(Col3), sum(Col4), sum(Col5) where Col3 > 0 and Col1 <>)тут он делает выборку данных из первой (основной)таблицы,(") это я так понял разделяет запросы, а вот в этой части( GROUP BY Col1, Col2 ORDER BY Col2, sum(Col3)) я не могу понять на какие столбцы в каких таблицах он ссылается.
P.S. (label Col1 'ФИО', sum(Col3) 'Остаток', sum(Col4) 'Приход', sum(Col5) 'Расход')тут мы просто именуем столбцы.
Максим Здравствуйте!
УдалитьС тем, как работает запрос я разобрался (вроде), но для его работы нужно, чтобы номера и данные столбцов одной таблицы совпадали с аналогичными в другой.
Дело в том что номера в первой таблицы ежедневно меняются, а вот во второй неизменны эту проблему можно решить?Или проще сделать два запроса на оддельном листе и вывести их в одну таблицу на другом?
Максим, добрый день.
ОтветитьУдалитьподскажите плз как создать гипер ссылку в гугл таблице на соеднюю закладку в документе, на конкретную ячейку. чтоб при нажатии на ссылку меня перекидывало на нужную закладку и на конкретную ячейку в ней.
Добрый день!
УдалитьЭто вообще очень занимательный вопрос.У меня есть один пример:
https://docs.google.com/spreadsheets/d/1_0hHC8kaSEYhTTlDXuHceJVxJaH_TaEsEEIPy-FzMXs/edit?usp=sharing
Добрый день, Максим! У меня есть таблица с 2 столбцами
ОтветитьУдалить1) Дата время звонка в формате 08.04.2016 16:10:14
2) Длительность.
Подскажите, возможно ли запросом суммировать длительность звонков за день?
Добрый день!
УдалитьМожно, попробуйте так:
=QUERY(A:B;"select toDate(A), sum(B) where B > 0 group by toDate(A)")
Ссылка на пример:
https://docs.google.com/spreadsheets/d/1ucTXSx5yeiKAwc4LU6jSe4XgN2oDXNcD7k2rkOhRBww/edit#gid=0
И ссылка на источник моих знаний, справку Гугла:
https://developers.google.com/chart/interactive/docs/querylanguage#scalar-functions
Добрый день, не могу разобраться с функцией query.
ОтветитьУдалитьЗадача следующая:
Необходимо перенести данные из одной таблицы в другую, учитывая критерии суммирования в первой таблицы.
Вроде бы механизм понятен:
=sumifs(query(importrange (...);...);
Но вопрос встал, как сопоставить данные наименования колонки a и номенклатуры в SQL.
Т.е. col1 = (вот тут непонятно какой столбец имеется ввиду) Буду премного благодарен если сможете как-то оповестить vk.com/ussper если кто-либо сможет помочь.
Col1, Col2, Col3 ... это 1-ый, 2-ой и 3-ий столбцы из исходных данных. По имени поля в запросе к ним обращаться нельзя.
УдалитьЯ обычно отдельно выписываю себе имена столбцов и потом нахожу их позицию в исходных данных при помощи функции match.
Есть 2 таблицы, в разных файлов, в первой и второй есть Ф.И.О. и второй столбец со значениями Х. Как реализовать функцию (importrange) которая со второй таблицы брала бы значения Х и вставляла их в первую таблицу соответствующему полю Ф.И.О..
ОтветитьУдалитьТо есть сравнивается Ф.И.О. двух разних таблиц, если они равны, то со второй таблицы берется значение Х соотвествующей строки и вставляется в первую.
Этот комментарий был удален автором.
УдалитьЭтот комментарий был удален автором.
Удалить=vlookup ( ФИО_из_первой_таблицы , importrange ( файл, лист_ФИО_Х_из_второй) ,2,)
УдалитьДобрый день!
ОтветитьУдалитьЯ пытаюсь объединить функцией query данные из нескольких листов (=query({'Дамир'!A18:AD;'Асхат'!A18:AD};"SELECT * WHERE AD = 'да'")). но выдает ошибку (Не удалось интерпретировать query string. Подробности: Параметр 2 в функции QUERY:NO_COLUMN: AD). где я сделал ошибку?
Добрый день!
УдалитьВы хорошо придумали, массивы это отличный способ объединять листы.
Но ключ здесь в том, что {} превращают диапазон в массив. Текст ошибки является и подсказкой, как ее решить: QUERY:NO_COLUMN: AD значит, в массиве не найдена колонка AD.
Правильно: "SELECT * WHERE Col32 = 'да'"
здесь 32 -- это номер колонки. Колонка AD не знаю под каким номером идет, пользуюсь формулой COLUMN для определения номера.
Здравствуйте!
ОтветитьУдалитьЕсть =query(importrange(url_таблицы; диапазон);"select * where Col1 contains 'msft'"), можно ли сделать так чтобы вместо msft была ссылка на ячейку из текущего листа. В итоге нужно, чтобы обычный пользователь вводил значение в определенную ячейку, и потом получал результат согласно запросу.
Здравствуйте,
Удалить"select * where Col1 contains '" &A1& "'"
Класс, работает. Спасибо, очень помогли)
УдалитьДобрый день! Могли бы помочь, пожалуйста. Была у меня функция, которая нормально работала
ОтветитьУдалить=query('2014_2020'!A32:XH1190;"
Select F, H, IC, ID, IE, IF, IG, IH, IK, IL, IM, IN, IO, IP, IS, IT, IU, IV, IW, IX, JA, JB, JC, JD, JE, JF, JI, JJ, JK, JL, JM, JN, JQ, JR, JS, JT, JU, JV, JY, JZ, KA, KB, KC, KD, KG, KH, KI, KJ, KK, KL, KO, KP, KQ, KR, KS, KT, KW, KX, KY, KZ, LA, LB, LE, LF, LG, LH, LI, LJ, LM, LN, LO, LP, LQ, LR, LY, LZ, MA, MB, MC, MD
WHERE
(H = 'AM' )")
Мне ее понадобилось поменять в связи с рокировкой столбцов:
=query('2014_2020'!A32:KP1118;"
Select F, H, I, J, K, L, M, N, O, P, Q, R, S, T, U, V, W, X, Y, Z, AA, AB, AC, AD, AE, AF, AG, AH, AI, AJ, AK, AL, AM, AN, AO, AP, AQ, AR, AS, AT, AU, AV, AW, AX, AY, AZ, BA, BB, BC, BD, BE, BF, BG, BH, BI, BJ, BK, BL, BM, BN, BO, BP, BQ, BR, BS, BT, BU, BV, BW, BX, BY, BZ, CA, CB
WHERE
(H = 'AM' )")
В следствии чего начало выдавать ошибку:
Не удалось интерпретировать query string. Подробности: Параметр 2 в функции QUERY:PARSE_ERROR: Encountered "BY" at line 2, column 268. Was expecting one of: "true" ... "false" ... "date" ... "timeofday" ... "datetime" ... "timestamp" ... "min" ... "max" ... "avg" ... "count" ... "sum" ... "no_values" ... "no_format" ... "is" ... "null" ... "year" ... "month" ... "day" ... "hour" ... "minute" ... "second" ... "millisecond" ... "with" ... "contains" ... "starts" ... "ends" ... "matches" ... "like" ... "now" ... "dateDiff" ... "quarter" ... "lower" ... "upper" ... "dayOfWeek" ... "toDate" ... ... ... ... ... ... "(" ... "-" ...
Помогите, пожалуйста!
Добрый день! Слишком много столбцов, вы наткнулись на волшебный столбец BY, а текст этого столбца является специальным словом в запросах "group by"
УдалитьВыходы 2:
Избавиться от столбца BY, вместо него взять другой.
Использовать массивы: =query({'2014_2020'!A32:XH1190};"select Col5, Col6, Col7...")
Второй способ намного выгоднее, использует номера колонок вместо букв.
Огромное спасибо!) С натсупающими праздниками)
УдалитьДобрый день еще раз.
УдалитьСделала формулу как вы и советовали с Col - но возникла другая ошибка.
Заглавная строка "заглатывает" первые три строки после заголовков из первого листа.
Сам запрос:
=query({'2018'!A33:CL1034}; "
Select Col9, Col6, Col8, Col11, Col12, Col13, Col14, Col15, Col16, Col17, Col18, Col19, Col20, Col21, Col22, Col23, Col24, Col25, Col26, Col27, Col28, Col29, Col30, Col31, Col32, Col33, Col34, Col35, Col36, Col37, Col38, Col39, Col40, Col41, Col42, Col43, Col44, Col45, Col46, Col47, Col48, Col49, Col50, Col51, Col52, Col53, Col54, Col55, Col56, Col57, Col58, Col59, Col60, Col61, Col62, Col63, Col64, Col65, Col66, Col67, Col68, Col69, Col70, Col71, Col72, Col73, Col74, Col75, Col76, Col77, Col78, Col79, Col80, Col81, Col82, Col83, Col84, Col85, Col86, Col87, Col88
WHERE
(Col8 = 'AM' )")
При этом, заголовок выгружаемой Col8 выглядит так: ГЕО UA BY UA - то есть, он захватил в заголовок первые 3 строки следующие за заголовком в основной таблице
Попробуйте указать в формуле количество строк заголовка:
Удалить=query(data, query, 1)
Эта единица в конце должна поправить ситуацию.
Если не поможет, значит редкий случай какой-то, мне нужен будет пример вашего файла.
Вот так имеете в виду?
Удалить=query({'2018'!A33:CL1034}; "
Select Col9, Col6, Col8, Col11, Col12, Col13, Col14, Col15, Col16, Col17, Col18, Col19, Col20, Col21, Col22, Col23, Col24, Col25, Col26, Col27, Col28, Col29, Col30, Col31, Col32, Col33, Col34, Col35, Col36, Col37, Col38, Col39, Col40, Col41, Col42, Col43, Col44, Col45, Col46, Col47, Col48, Col49, Col50, Col51, Col52, Col53, Col54, Col55, Col56, Col57, Col58, Col59, Col60, Col61, Col62, Col63, Col64, Col65, Col66, Col67, Col68, Col69, Col70, Col71, Col72, Col73, Col74, Col75, Col76, Col77, Col78, Col79, Col80, Col81, Col82, Col83, Col84, Col85, Col86, Col87, Col88
WHERE
(Col8 = 'AM' ),
1")
Только вместо запятой в вашем случае точка с запятой ; это разделитель аргументов функции. Я привел пример с запятой по привычке (в американской версии запятая).
УдалитьДобрый день еще раз)) Надеюсь на Вашу помощь.
ОтветитьУдалитьНужно вывести в рамках одной таблицы значения по разным критериям. На примере: есть таблица с типами еды местами, где ее можно купить. Мне надо вывести всю еду, которая продается в баре и одновременно все места, где продается пицца.
Сам запрос:
=query(FOOD!A2:F3480;"
Select A, B, F, E
WHERE
(E = 'bar'
OR
F = 'pizza')
")
Добрый день!
УдалитьЯ бы сделал 2 запроса, один для всей еды в баре, другой -- для мест где пицца. Ваш запрос выглядит ОК.
Мой запрос выдает только результат по пицце просто(
УдалитьПопробуйте упростить запрос, разбить его на 2 куска:
УдалитьSelect A, B, F, E
WHERE
E = 'bar'
и отдельно:
Select A, B, F, E
WHERE
F = 'pizza'
Посмотрите, что выходит. Данные тоже неплохо проверить, сделать фильтр руками, посмотреть что выдает. Может быть, опечатка. Мало ли?
Совет: упрощать формулу, пока не станет ясно почему не работает.
Добрый день веду учет компании в гугл таблицах
ОтветитьУдалитьЗанимаемся доставкой ланчей. Пробую автоматизировать систему сбора и обработки данных:
Склад - Тех карта - Закупка - Склад
Не могу вот пока найти опорную формулу чтобы зацепила смотрите есть:
список продукции и его типы пример:
Имя Помидор - тип зелень
http://prntscr.com/i0b91e
Вот пытаюсь настроить чтобы данные обменивались по имени. Название везде совпадают
что самое просто не могу сделать это чтобы подтягивались другие данные.
Например есть вот тех карта
http://prntscr.com/i0b9qm
в ней морковка вот как сделать так чтоб в поле type оно подтянуло данные из склада? и она стало овощем ?)
вот уже 2 день пытаюсь найти как это реализовать.
Заранее благодарю за помощь !
QUERY(H1:Z,"select O where "sum(K:K)"> 0") Синтаксическая ошибка в формуле. Подскажите что не так какой знак не проставлен
ОтветитьУдалитьДобрый день.
ОтветитьУдалитьМне нужно выбрать все непустые строки.
Использую запрос: query(A3:F;"select E where F is null")
В столбце F у меня содержится разная информация: либо дата, либо текст.
Данный запрос отсеивает строки с датами, но пропускает строки с текстом. Как исправить?
Заранее благодарю.
*выбрать все пустые строки
УдалитьАлександр, добрый день.
УдалитьЗапрос умеет читать только один тип данных.
Можно все в текст конвертировать:
query(arrayformula(to_text(A3:F));"select Col5 where Col6 is null")
query(arrayformula(to_text(A3:F));"select Col5 where Col6 is null and Col6 <> ''")
УдалитьДобрый день. Подскажите, пожалуйста, прописываю фильтр с помощью кляузы
ОтветитьУдалитьWHERE
(F>'10') но формула почему то видит 10 как 1 и показывает всё что больше 1. Как исправить данную ошибку. Спасибо.
Добрый день.
УдалитьКогда работаем с числом, то без кавычки:
WHERE
(F>10)
Там не просто число, а результат с расчетов формулы. Когда убираю кавычки вообще выдает #Н/Д
УдалитьДобрый день.
ОтветитьУдалитьОткрыл для себя query в GooglDocs - любопытствую и наслаждаюсь ) Отличный инструмент.
Однако столкнулся с, наверняка, смешной для спецов проблемой.
Осмелюсь спросить ))
Итак есть 2 строковых поля, хочу через select получить в результате одно строковое поле.
И потихоньку схожу с ума от того что не получается. С sql работаю каждый день на работе, знаком :)
ни "+", ни"||", ни "&" не проходит....
Методом тыка, натыкал следующее
=QUERY(A:C;"select '"&CONCAT("""qw""";"""fff""")&"',C")
В итоге сумел таки "склеить" в результате запросом 2 строковых значения в одно.
НО! :( как в такой конструкции обратиться к текущему значению полей в запросе?
Предварительно делать третее результирующее поле можно, но не хочется.
Есть-ли волшебный синтаксис могущий таки воткнуть в найденное заклинание именно просто столбцы А и В?
Чтоб типа
=QUERY(A:C;"select '"&CONCAT(.....тут что-то.....A.....тут что-то.....;.....тут что-то.....B.....тут что-то.....)&"',C")
Вместо ".....тут что-то....." вставлял уже кучу всего, пока безрезультатно :(
Буду крайне благодарен если подскажете )) и одним сумасшедшим будет меньше этом мире )))
Добрый день!
УдалитьПока, к сожалению, нельзя:
https://developers.google.com/chart/interactive/docs/querylanguage
Можно пробовать сочетать формулы query + filter:
=query(filter({A:C, A:A & B:B}, A:A <> "") , "select Col1, Col2, Col3, Col4")
Непонятно почему подписалось как "Unknow", сори :(
ОтветитьУдалитьДобрый день! Подскажите, пожалуйста, можно ли в момент импорта отсортировать по определенному столбцу (дата)?
ОтветитьУдалить=QUERY(IMPORTRANGE("ссылка";"сверка Внешние!A1:P");" select * order by 'Дата поставки на склад' DESC")
Импорт происходит, а сортировка нет.
Спасибо!
EvgenK, добрый день!
Удалить=QUERY(IMPORTRANGE("ссылка";"сверка Внешние!A1:P");" select * order by 'Дата поставки на склад' DESC")
не сработает.
Попробуйте:
=QUERY(IMPORTRANGE("ссылка";"сверка Внешние!A1:P");" select * order by Col1 DESC")
Вместо Col1 поставьте свой номер колонки в источнике.
проблема не в том. Импорт происходит и при названии колонки и при номере. Но в любом случае не сортируется по данной колонке. Подозреваю потому, что происходит цепочка импортов и запросов. Импорт идет из таблицы, которая то же сама QUERY с другого отдельного листа импорта.
Удалить'Дата поставки на склад' — создаст колонку с повторением имени "Дата поставки на склад", по имени колонок нельзя выбирать.
Удалить" select * order by Col1 DESC" должен сортировать.
Нужен пример файла, чтобы разобраться.
Делаю простой запрос выборки по дате:
ОтветитьУдалить=query(windows!"SELECT A WHERE P=toDate(now())")
сегодня срабатывает а уже завтра не работает,
пока не зайду в ячейку и не зделаю любые изминения
нажимаю ентер и все работает
почему-то не видит изменения даты
данные для запроса беру из нескольких
ячеек соединением содержимого
так делаю, чтобы отслеживать перемещения
нужных столбцов
=query(windows!A4:S;D4)
=JOIN("";"SELECT ";K6;" WHERE ";K7;"=";K8;)
Добрый день, Максим!
ОтветитьУдалитьПодскажите как решить эту задачу
Еще раз добрый день !
ОтветитьУдалитьПомогло !
Написал вопрос и ответ сам пришел
в голову ))
Привет!
ОтветитьУдалитьПодскажите, пожалуйста, почему в функции после выбора диапазона открываются кавычки, и внутри этих кавычек меняется синтаксис и логика написания формул.
Вот как тут: ' " &A1& " '
Почему мы просто не можем напрямую обратится к ячейке, а заносим ее в формулу вот таким образом ' " & ?
Спасибо!
Здраствуйте,
ОтветитьУдалитьВоспользувалась єтой функцией =СУММ(QUERY( IMPORTRANGE(""; "08_18!AB1:AG"); "select Col2 where Col1='August' and Col6 = 'EU1001'")), все хорошо, но как зделать что б параметр EU1001 определялся по даных в текущей таблице/колонке, тоесть у меня в таблице есть колонка, где есть много таких параметров и по них хочу зделать суму з других табиц, как зделать суму, что б каждый раз не прописывать это вручную, тем более если параметр подвижный, сегодня в этой ячейке написано EU1001, завтра EU1002 и тд.
Добрый день! Есть файл с выставленными Счетами и Оплатами. В столбце Оплаты могут быть как числовые значения, так и пустая ячейка. Как заставить запрос определять пустую ячейку как числовое значение "ноль"? Иначе запрос не выполняется, а выполняется только при ручном выставлении нуля. Пример запроса
ОтветитьУдалить"select D, C, E - F where A is not null and E > 0 and F - E < 0"
где F - столбец с оплатами, может быть пустым, может быть с числом.
Спасибо!
Добрый вечер!
ОтветитьУдалитьМне надо чтобы из списка суммировались значения по 2 параметрам:
- наименование затрат
- месяц оплаты
Не знаю как для формулы =SUMIFS('Реестр Кред задолженности'!$B:$B;'Реестр Кред задолженности'!$C:$C;C$4;'Реестр Кред задолженности'!$D:$D;$B9) в столбце даты автоматически переводился формат (дд.мм.ггг) в номер месяца для суммирования
по номеру месяца и наименованию затрат?
Добрый день, Максим. Есть задача на один лист вывести данные из 10-20 однотипных листов. Есть вариант в формуле 20 раз не писать IMPORTRANGE с сылками на каждую таблицу, а задать своего рода массив ссылок, который будет прописан в ячейках. Например в столбце А - ссылки на таблицы, а в столбце В- ссылки на диапазоны.
ОтветитьУдалитьЗдравствуйте, у меня есть 10+баз и мне нужно перенести данные в 1 док по фильтрации допустим Аутрич, но формула постоянно выдает ошибки и при вызове функций она просто не видит столбцы, даже элементарно через SELECT, когда я ввожу отдельный столбец который я хочу видеть в этой таблице она говорит что не видит такого столбца, какого бы столбца я не вводила, а вот с * все работает ок, что мне делать?
ОтветитьУдалить=QUERY(
{IMPORTRANGE("1YcUGYaxGxIioGzwoa5L9l6L2J-jANqliZahHhQbrii8";"Лист1!D1:F50");
IMPORTRANGE("18rLbbLCo7neDcD-OFOCfM3QivLx1JkfhCSeiz0mZlTM";"Лист1!D1:F50")};
"SELECT F")