Google таблицы. Как пользоваться функцией Query. Урок 10.
Вставка
- Опубліковано 5 лип 2018
- Поддержи автора канала пальцем в верх и подпиской это для
меня лучший стимул продолжать развиваться.
В этом уроке вы узнаете как пользоваться функцией Query в гугл таблицах, функция очень многофункциональная
мы рассмотрим базовые ее понятия и принципы работы. Функция Query позволяет вытаскивать данные
с любыйх частей таблицы посредством SQL запросов.
Смотрите до конца, будет интересно.
Станьте спонсором канала,. Подробнее:
/ @stmsolution
💰 Поддержать монетой:
🥇Bitcoin - bc1q5ve3p8zmcm6z9tapts6padpmnvv572tsujukad
📲 Через кнопку "Спасибо"
👍 Или станьте спонсором канала!
Все уроки по Гугл таблицам в одном плейлисте - • Google таблицы. Как со...
Мой паблик в Telegram - t.me/stmsolution
Ссылка на таблицу - docs.google.com/spreadsheets/...
Кто хочет побаловаться с ней, просто создайте себе копию.
Буду рад видеть вас в моей группе ВК - public150463462
Twitter - / stm_solution
Мой сайт stmsolution.ru
Еще больше видео на моем канале / @stmsolution - Наука та технологія
![Функция =LET в Гугл таблицах! Новый уровень создания формул с ПЕРЕМЕННЫМИ! [ПОЛНЫЙ УРОК]](/img/n.gif)
Вы даже не представляете сколько пользы я получила от ваших уроков!!! я безумно вам благодарна!!! год! а то и больше я ломала голову, как выстроить учет на швейном производстве, ни одно из готовых решений - срм системы, программы учета складов не подходили, все слишком сложно и к тому же платно, теперь все считается - з/п, количество в наличии, полный учет склада - это потрясающе, благодарю от всей души, вы открыли мне дверь к дальнейшему развитию!!! Благодарю!!! Плакала от счастья!!!
Ну я б не стал такие диферамбы петь
Очень полезно! Всем советую к изучению. Спасибо вам.
доброе утро, спасибо Вам большое, правда очень бы хотелось изучить эту функцию подробнее и с разных ракурсов))) пожалуйста сделайте видео😊
Добрый день, не могу обещать в ближайшее время, так как много запланировано на данный момент других видео, а времени очень мало, основная работа забирает много времени и сил. Но в любом случае я вас услышал и приму во внимание.
@@stmsolutionможно ваш инстаграм или телеграмм канал?
Спасибо, классный урок!
Спасибо что смотрите;)
Спасибо за уроки
Классно! Спасибо!
Спасибо, очень интересно
Колдунство какое. Спс за видео, очень познавательно)
Рад помочь. Обращайтесь.
Чувак, це круто! Молодець!
Нашел более удобное решение.. сразу же на листе "Данные" в А2 - готовый сортированный суммированый результат:
=QUERY({'Месяц 1'!A2:D26;'Месяц 2'!A2:D26;'Месяц 3'!A2:D26};"select Col1,sum(Col2),sum(Col3) where Col1 is not null group by Col1") Если нужно в обратном порядке - в конце дописать: ...order by desc")
"Лист3" - совсем не нужен, и еще там была ошибка - у вас Мориарти не посчитался, в моем случае - все тут. Успехов! :-)
Годно, очень крутые ролики много нового находишь 👍
Спасибо, смотрите дальше, там много ещё))
@@stmsolution да, конечно))
Я только начал, хочу освоить с ними работу, иногда на работе требуется в заявках видел)))
Танос наторговал больше супермена, "веский повод для улыбки")
И тут он всех уделал)))
Спасибо
Действительно информация очень полезна в век быстрых технологий. Вопрос по ролику. А как парсить гугл таблицы для чтения на нескольких листах в книге и ловить изменения цены, товара и т.д.? На сколько я понимаю, так же через xPath? Ведь, как я понимаю Power Query не совсем подойдет или ошибаюсь? Спасибо
Спасибо за видео! Очень полезная информация!
А подскажите еще, что нужно сделать, чтобы в итоговой таблице добавлялись продавцы, которые были в первом и втором месяце, но не были в третьем?
С помощью квери вытащить список фамилий из трёх страниц за три месяца, прописать в квери после select, строку Where A is not null, чтобы квери пропускало пустые значения и потом к этой квери, в начало, дописать функцию Unique, чтобы отобразить только уникальные значения и убрать повторения. Можно так сделать, но в таком случае в списке будут все фамилии за 3 месяца.
Здравствуйте! Пожалуйста, сделайте еще урок или не один с применением функции Query!
Подскажите пожалуйста, какое слово использовать, когда нужно выбрать не колонку (Col), а строчку?
Молодец!)
Спасибо
Тарзан, халк, танос, супермен, отличные имена XD
Крутяк!
Добрый день! Спасибо за урок! Подскажите, можно ли с помощью макроса импортировать данные с одной Гугл таблицы в другую(не листы в одной таблицы)?
Можно, смотрите уроки по суриптам
И еще вопрос. Как перенести с помощью способа описанного в видео, не только значения, а и форматирование ячеек (цвет фона, размер и цвет шрифта)?
Может скопировать условное форматирование на диапазоны ячеек?
Добрый день. Очень ценная инфа. Спасибо. Помогите разобраться, как реализовать такую задачу. Есть 12 листов с одинаковыми по форме таблицами за каждый месяц, только содержимое - цифры разные. Нужно сделать на отдельном листе одну таблицу, где могут отображаться значения разных месяцев при выборе в отдельной ячейке определенного месяца из выпадающего списка (в котором есть возможность выбрать каждый месяц отдельно и сумма значений по квартально и за год). Т.е. выбрал январь и ниже в таблице подставились значения за январь со всеми форматированиями ячеек.
Как это можно реализовать?
форматирование работает только на конкретном листе одном. Для остального надо пробовать учить сскрипты и уже через них реализовывать ваши задумки
Реально запарила высплавающая картинка "подпишись" .
Учел это в дальнейших видео, сделал ее меньше и в углу.
Добрый день! Решил сделать общую базу расходов и из нее с помощью данной функции вытаскивать необходимые данные.
Подскажите можно ли в функцию "если" прописать раскрывающийся список(которые обычно вбиваем в проверке данных). К примеру в левом столбце выбираю "Менеджеры" и в правом столбце появляется раскрывающийся список с фамилиями.
Да, можно через фильтр со ссылкой на диапазон с выдачей фильтра.
@@stmsolution Не совсем понял. Формулу с фильтром вставить в проверку данных? Покажите, если есть время
docs.google.com/spreadsheets/d/1HK65_KBB_qXu6N0mCuz4c9Ddejstqdvvq4SQo-lUdnk/edit?usp=sharing
Сделал через фильтр, но привязка идет к одной ячейке, а так как эта таблица расходов и у меня там будет много строк подряд, то не получается маневр...
docs.google.com/spreadsheets/d/1HK65_KBB_qXu6N0mCuz4c9Ddejstqdvvq4SQo-lUdnk/edit?usp=sharing
@@user-gv8su3qq9p вы уже решили этот вопрос? Если решили то как?
Отличные уроки! Очень помог со многим но тут я застрял.
Как сделать фильтр по нескольким параметрам? То есть всё то же самое как в этом уроке но мне нужно чтоб ещё отфильтровывал по фамилии.
В первой ячейке ИМЯ, во второй ФАМИЛИЯ - и только если эти 2 параметра совпадают то делать посчёт. А то у меня к примеру 5 человек с именем Александр работают - и подсчёт делается всех вместе.
Пример формулы можно?
Filter(A:A;B:B= "Александр";C:C = "Сидоров")
Добрый день. Может подскажете. При очередном входе в гугл таблицу она открывается не в том месте, где закончил работу а в самом начале таблицы. Например заполнил очередную 586 строку и закрыл, а при следующем открытии экран на первой строке и чтобы заполнить очередную 587 строку, нужно вручную листать вниз.
Никак, перепрыгивайте через контрол + вниз
Помогите как сделать добавить в ячейку натуральные числа?
👌
Здравствуйте :) подскажите пожалуйста, как в гугл таблицах сделать из сводной таблицы - плоскую таблицу? В экселе нужна надстройка Power Query, а как это делать в гугле?
Не совсем понял,что значит плоскую?
@@stmsolution честно говоря сложно коротко описать, вот на этом видео есть инструкция как делать в экселе, а мне точно тоже самое нужно сделать в гугл таблицах :) ua-cam.com/video/LvlFqCczt_k/v-deo.html
здравствуйте! Спасибо за полезное видео! Скажите, пожалуйста, как использовать функцию Filter с Query? У меня импорт из нескольких листов и я хочу, чтобы в сводном листе была фильтрация по дате. Вот формула, она почему-то не срабатывает после добавления query: =QUERY(FILTER({'Точка'!A2:Q;'Карты Виктор'!A2:Q;'Карты Ирина'!A2:Q;'Иное'!A2:Q};НЕ(ЕПУСТО({'Точка'!A2:A;'Карты Виктор'!A2:A;'Карты Ирина'!A2:A;'Иное'!A2:A})));"select A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q where (B>=date'"&ТЕКСТ(Q4;"yyyy-MM-dd")&"' and B
Надо смотреть в таблице и что за ошибка
добрый день. Не могу разобраться как вывести данные с таблиц. Каждый день генерируется новая таблица. В ней много столбцов. но нужны только 2. это список участников и некоторое число от 0 до 10. далее мне нужна понедельная статистика. чтобы был один список участников(в который можно дополнить но не удалять строки) и 7 столбцов с числами. QUerry бы мне помогла выводить числа, но список участников постоянно меняется и.на уменьшение и на увеличение делая ввод чисел невозможным. они просто встанут не на свои места.
Учите скрипты и выдергивайте триггером каждый день все данные в отдельный лист а после фильтруйте
Только это урок не про среднее значение , а про общее значение продаж по списку работников оставшихся к третьему месяцу.
Вообще, урок про Query)))
Добрый день! Подскажите пожалуйста, после вывода данных перестают отображаться и функционировать гиперссылки, как это можно поправить ?
Только скриптами так как квери тянет текст , там нет чисел или ссылок, чисто текст
@@stmsolution понял, спасибо
Мини интернет магазин замути !!!
А как сделать на листе "Данные", чтобы "Товар" и "Сервис" были под "Продажи", как на листе Месяц 1 (как и на Месяц 2 и Месяц 3)? Я делал как на видео, но у вас тоже съехало всё на 3:01. А то у меня в строку Продавцы - Продажи Товар - Сервис. Обратите внимаение, что перенеслось не точь-в-точь.
Добрый день! Мне очень нравятся ваши уроки, пересмотрел все. Короткие, чёткие и понятным языком. Но у меня возник один вопрос по которому к сожалению у вас нет урока. Если вы знакомы с функцией importhml, то вы мне сможете помочь. Вопрос такой: у меня получается импортировать только на тех сайтах, в которые не надо входить, т.е. нет аккаунта. По понятнее, на сайтах в которые я захожу под своим логином и паролем, у меня не получается с него импортировать в гугл таблицу. Заранее спасибо за ответ!
Добрый день. Спасибо что смотрите. Importhtml мне знаком но из за ограниченности данной функции я не стал делать урок по ней(видимо зря), а готовлю более мощную функцию importxml, там все можно делать что и в первой, только вдобавок ещё куча возможностей импорта. По вашему вопросу надо смотреть. Так сразу и не знаю что ответить, адрес сайта вы указываете уже после того как вошли на сайт?
STM Solution с importxml тоже пробовал, не получилось. Копировал ссылку тогда, когда вошёл естественно, но все же не получилось. Буду очень рад если сделаете по этой теме урок. Интересно онлайн синхронизация сайтов с гугл таблицами.
Конкретно, что я пытаюсь сделать это синхронизация приложения otelms с гугл таблицами. Чтобы отчёт и таблицы по проживающим в отели переносились онлайн автоматически в гугл таблицы.
Сложный вопрос, надо разбираться, там не простой сайт а можно сказать веб приложение, это сложнее и при том они мне что то доступ не дают к своей системе.
Вопрос по IMPORTXML..
если вот так прописать "//a/@href", то выводит все ссылки из указанного интернет-адреса
вопрос такой: можно ли (и как) изменить вторую часть (эту - "//a/@href"), чтобы выводило только ссылки в имени которых есть "/id/" ??
Вам с определенным id надо?
Подскажите, как вывести данные через функцию query уже отсортированными по убыванию?
=QUERY($B$1:$L$36;"select (F) where B="&$N$2&" and C="&$O$2&"") Что нужно сделать, чтобы данные при работе данной формулы возвращались уже отсортированными по убыванию?
Order by
а почему невозможно удалить строку с ненужными данными в Query? Я жму Удалить строку или Очистить строку, но данные снова появляются. Толку от query тогда нет.
А можно этой функцией обращаться к таблицам гугл в других файлах? Как это будет выглядеть? Спасибо.
Query( importrange ("A:A);"")
@@stmsolution да, спасибо. А как вытащить данные из нескольких разных файлов?
Вот как в этом видео только надо будет вместо диапазонов importrange вставлять ua-cam.com/video/4xral33SzAo/v-deo.html .
Написала формулу Query точно так же, но у меня, почему-то, список отобразился не вниз друг за другом, а слево на право
значит не точно так же))
ПАСІБА
Как ты узнал что знак доллара стопит значения, где это узнавать ?
Если честно даже не вспомню, давно это было, где то в статьях по таблицам или в справочнике гугл, в основном я все там узнаю, но а так как и все гунлю то что меня интересует и читаю или смотрю. Тебе бы порекомендовал почитать справочник гугла. Там очень много доступно и По ятно написано.
STM Solution спасибо, делал бы ты дальше видео о таблицах, особенно изучений функций, очень полезно, бывает иногда не понятно, хочется простым языком на примере , а не терминами типа:"значения, аргументы,истина ложь) башка итак забивается)))))
Будем делать, если есть пожелания - пиши.
еще можно просто F4 нажать когда курсор в наименовании ячейки стоит, то он фиксирует и по строкам и по столбцам
Поскольку Microsoft Excel стал де-факто стандартом для электронных таблиц, то многие его функции, свойства и принципы используются в других электронных таблицах. Google Таблицы не стали исключением. Знак $ и клавиша F4 (в одном из комментариев про неё написано ) в Excel тоже так же используются.
А если агрегацию «{}» вложить в query?) типа query({диапазон1; диапазон2; диапазон3}), добавить удаление пустых строк и упорядочить... тогда совсем культурненько)
Да, в последующих видео такое есть, спасибо))
Хотим
Кто подскажет как использовать qury или IMPORTRANGE Так чтобы они игнорировали запрет на ввод данныйх в заполненные ячейки. Допустим мне не важна и не нужна инвормация которая написна ниже формулы, но она появляется и каждый раз вручную приходится удалять чтобы функция вывела список нужной инфы
Query импортирует данные на основании запроса, если вы скажете функции что бы она выводила данные с конкретными значениями остальное и не будет выводится
@@stmsolution не в этом дело, если ввести данные на месте тех что ввел квери, формула сломается и выдаст ошибку, а мне нужно чтоб она игнорировала это и перезарисывала то что введено в ручную на то что нужно
Не получится так
@@stmsolution Нашел выход из ситуации, подробно тут : www.excelworld.ru/forum/23-42241-1
без кнопки работает супер, с кнопкой слетает. Почему???
watched
Просто о сложном.
04/04/24
Кишинев+++
Добрий день! Підкажіть будь ласка як фільтрувати по декільком значенням в одному стовпчику. Наприклад в А:А в нас список продавців, у В:В успішність. Треба щоб показало тільки строки з продавцями Вова і Коля. Якщо можна, зразок формули. Дякую. Гарного дня!
П.С. пробував об'єднати умову оператором and():
filter(A:B;and(A:A="Вова";А:А="Коля"))
Але так не працює. Пробував вказати ці 2 імені через & - теж не працює.
Посмотрите 25 уроки по квери
@@stmsolution , Ви маєте на увазі "25 урок_И по квери"?
В уроці #25 йдеться про чекбокси, це я вмію. Уроки про Квері - ще раз прогляну. Відпишусь
@@tarasmops5545 да 26)) уроки перепутал))
@@stmsolution Ооо, з допомогою query частково вирішив, але Ви можливо не все сказали у відео. Якщо у функції прописати .....А='Вова' AND A='Коля'..... , то не відображає результати. Тому методом здогадки я замість AND поставив OR - і запрацювало. Але якщо в колонку С також помістити дати продажу, і спробувати фільтрувати по даті + імена одночасно декількох продавців з колонки А, то функція знову припиняє працювати, оскільки після where йде AND i OR. Методом вгадування знайшов таке рішення(додав дужки):
QUERY('Лист1'!1:1000;"select A, B, C where(С=date' "&text (D1;"yyyy-mm-dd")&" ' and (B='Вова' or B= 'Коля')")
Тобто..... and (B='Вова' or B= 'Коля') в мене запрацювало і показуються Вови і Колі тільки за певну дату (у формулі вона прописана в D1)
Якщо це прийнятний правопис - підтвердіть будь ласка
Зачем делать абсолютными ссылки на полностью выбранный столбец (А:А, В:В, на 8:35) , если формула протягивается по вертикали? Это излишне.
Да, вы правы, спасибо за замечания, но есть один момент возможно в будущем вам понадобится растянуть в бок и тогда это может вызвать трудности. То есть если подразумевается что данный диапазон не должен никуда сдвигаться то лучше его закрепить. Это в моем понимании так. Но тут как кто хочет. Вы со своей стороны тоже правы.
@@stmsolution в таком случае, для присвоения диапазону различных ипостасей абсолюта, предлагаю использовать последовательное нажатие клавиши F4. Особенно это выручает при абсолютировании ограниченных диапазонов типа А1:А5: первое нажатие F4 делает ссылку на диапазон абсолютной, второе не дает ссылке сдвигаться по вертикали, третье - по горизонтали.
Да, спасибо, где то в. Видео об этом тоже говорилось
а если Вася только в последнем месяце устроился , а до этого первую строчку занимал Петя ? Сортировка по алфавиту не поможет , если сортировать пару сотен строк проданного товара .
повторил всё до мелочей
а никто не сталкивался с ситуацией, когда query затягивает цифры, но игнорирует ячейки, если есть хоть одна буква?
Ну так буков быть и не должно, может быть ошибка
в другом месте точно такая же формула работает. а тут через select переносит строчки из указанной колонки. QUERY('Ответы на форму (1)'!A2:AX;"select B,C,J where(AX = '"&C1&"'and J>'""')"). а строчки, в которых встречается буквы переносит, но ячейки пустые. если в листе "ответы на форму(1)" ввести цифры - они тут же отображаются по месту размещения формулы query
Поставила скобки, все равно ошибка
Ищите в формуле ошибку
@@stmsolution docs.google.com/spreadsheets/d/1Ku9GAgP9qXm7bSjpjuPrNTPpftCdEfZUqLfQ2DN6wsw/edit#gid=748261749 как объединить данные сводных таблиц?
@@dftcm8 =QUERY({'Сводная таблица 1'!A1:V22;'Сводная таблица 2'!A1:V22};"SELECT Col1,Col22")
При работе с массивами ставится не А,В и т.д., а именно номер колонок. На здоровье
@@stmsolution спасибо Вам)
Обращайтесь
Музыка та зачем?
громко музыка
Понял, поправим в следующих видео.
музыка на ускоренном просмотре очень мешает
музыка слишком громкая
отвлекает
Когда это начали говорить уже 20 видосоа записал)) потом поправил))
Такое ощущение что на середине видео ты забыл о чём собственно оно. То что ты показал это даже не десятая часть возможностей функций query. Всё то что ты показал делается с помощью одной query функции, а ты как по мне не объяснил, а ещё больше запутал.
Посмотри 26 урок
Так и не понял о чем это видео
музыка раздражает
Учел это в дальнейших видео, сделал ее тише
Музыка очень мешает. Вообще не в тему
Дальше поправлено. Уменьшил громкость))
Очень плохо объяснил. Если применять твой пример на своих таблицах с данными во время просмотра, то каша получится.
Как есть...уже не поменяешь
где тут среднее то? видим только сумму. дай формулу на среднее,жлоб!
что за тупая всплывающая анимация?? для чего ?? зачем?? видео законно получает дис.
есть такое как, начинающий блогер, который учится на отзывах подписчиков, а в следующих видео исправляет эти недостатки. Если ты смотришь на анимации , а не на контент, который дается тебе бесплатно и максимально разжёвано, если ты считаешь что тут ничего полезного нет, то будь добр отпишись от канала и больше никогда сюда не заходи. Хочешь дать пару советов по установке анимаций и как и правильно делать, вставлять в видео, разрабатывать, милости прошу ко мне в личку. Твое мнение как и мнение многих было учтено в последущих видео (хотя остальные это выражали в более культурной форме). Но судя по отзыву вижу что ты не за полезным контентом сюда пришел, а просто поумничать, по этому еще раз прошу, больше не заходи сюда и не смотри ничего, раз ты смотриш на анимации.