Google таблицы.Функция Query.Динамические фильтры.Часть 2. Урок 26.
Vložit
- čas přidán 7. 01. 2019
- Вторая часть серии видео уроков по функции Query. В этом уроке мы продолжаем изучать возможности гугл таблиц, как пользоваться функцией Query в гугл таблицах, мы изучим как
фильтровать таблицу по динамическим данным которые выбираете вы, а так же как фильтровать значения в таблице по определенной дате или между ними.
В уроке присутствует новая функция TEXT о которой я вам не рассказывал, но думаю проблем с ней у вас не возникнет, так как по ходу видео становится понятно как она работает.
Смотрите до конца, будет интересно.
Станьте спонсором канала,. Подробнее:
/ @stmsolution
💰 Поддержать монетой:
🥇Bitcoin - bc1q5ve3p8zmcm6z9tapts6padpmnvv572tsujukad
📲 Через кнопку "Спасибо"
👍 Или станьте спонсором канала!
Ссылка на таблицу - goo.gl/R1G3jh
Кто хочет побаловаться с ней, просто создайте себе копию.
Поддержи автора канала пальцем в верх и подпиской это для
меня лучший стимул продолжать развиваться.
Мой паблик в Telegram - t.me/stmsolution
Буду рад видеть вас в моей группе ВК - public150463462
Twitter - / stm_solution
Мой сайт stmsolution.ru
Еще больше видео на моем канале / @stmsolution - Věda a technologie
Сижу и от восторга захватывает дух.
Тот момент когда ты так давно работаешь с гугл таблицами и понимаешь насколько ты чайник🤣😂.
Огромное спасибо!
Пожалуйста, еще видео о Query
всего знать невозможно. Там дальше вроде были еще. Не помню))
Супер, спасибо большое. Все четко и понятно, без воды. Успехов Вам!!!
Спасибо Вам больше. Вы просто помогли решить серьезные вопросы в моей работе. И сократить большое затраченное время
Рад, что полезен
Спасиииибо! Очень нужная информация про && , не знал как решить проблему до видео.
Рад помочь
Спасибо огромное. Очень познавательно и очень помогло в работе. Удачи и успехов
Спасибо
Вот прям помог! Спасибо!
На здоровье!
Круто! Спасибо за этот ролик!
спасибо что смотрите
Спасибо, решил одну мою проблему
Добрый день,делаю таблицу с продажами,ваши видео очень многое ускорили и помогли,но хотелось бы узнать следующее:Как выявить прогноз по продажам и как найти сумму сколько продаж нужно сделать в день что бы идти на 100 %.Удачи и по больше подписчиков
Прекрасный урок, странно что так мало просмотров и подписчиков
так никто ж не лайкает и не подписывается, а главное никто не делится такими видео с друзьями))Кому еще таблицы то нужны))
Автор, я взахлеб просмотрел 21 урок.... остальные обязательно досмотрю на днях! я думал что я хорошо разбираюсь в таблицах ))) теперь я понял что вообще не шарю!!! спасибо тебе большое! я для себя уже увидел несколько фишек, о которых даже не догадывался. ))))
Спасибо что смотрите
@@stmsolution не могу дойти до формулы никак (((( суть такая: у меня таблица по расчету стоимости секционных ворот. Есть вводные данные после замера: Ширина и высота. В таблице на пересечении параметров цена. Все в таблице Эксель. Сложно объяснить - могу показать, если отравишь почту. сможешь помочь?
@@user-vg6xc4by9w mitra654491@gmail.com
@@stmsolution открыл доступ редактора ))))
там все описал
Написал вам на почту, свяжитесь со мной в телеграм
огонь! очень помогла инфа как ссылаться на ячейку (через закрытие кавычек и &)
Рад помочь, смотрите и другие мои видео до конца, там много всяких фишек
@@stmsolutionну этим надо пользоваться чтобы в памяти оставалось. У меня довольно нетипичная задача была. Я в видеомикшер подтягиваю данные для титрования трансляций спортивного мероприятия. Нужно очень быстро в режиме реального времени вытягивать данные с БД
Спасибо за видео. (см. почту)
Подскажите пожалуйста. Можно ли ещё в этой функции сделать сортировку по алфавиту от а до Я по фамилиям. И также вставлять как видео сортировку по дате. Спасибо. В ваших видео все подробно об'ясняете о все понятно.
Order by A desc, B asc . что то типо такого, подставьте свои столбцы и виды сортировок
💥
Здравствуйте! Спасибо за Ваши уроки, не знаю, что бы делал без них, однозначно лайк)
Если не затруднит, у меня большой вопрос:
Уже который день мучаюсь с query.
Если по порядку, то:
Занимаюсь ремонтом телефонов, решил немного автоматизировать бухгалтерию.
В качестве вводных значений есть две "базы данных":
Первая - каталог магазина, в котором я закупаю запчасти для телефонов (столбцы: наименования - цена - количество на складе).
Вторая - это пополняемый через гугл формы список клиентов (столбцы: дата записи, ФИО, модель телефона, вид неисправности и т.д.).
И всё не могу связать эти две "базы данных" между собой посредством query. Суть такая: хочу приходить в магазин запчастей и в таблицах смотреть, что мне нужно купить из запчастей, чтобы провести все ремонты за сегодня. Нужно чтобы на отдельном листе, из списка клиентов выписывались модели их устройств и их неисправности (если что - их не сильно много, порядка 30 штук типовых проблем) и, соответственно этим двум параметрам (модель и неисправность), подбирались запчасти из каталога и показывалось их наличие у меня.
Например: пришел клиент с IPHONE 7 с РАЗБИТЫМ ЭКРАНОМ. Следовательно, выдаётся КОЛИЧЕСТВО НОВЫХ ДИСПЛЕЕВ у меня на руках в данный момент ДЛЯ IPHONE 7.
Формирую отчёт с интересующей информацией следующей формулой:
=query(ARRAYFORMULA({QUERY('Список клиентов'!A1:O100;"select A,B,C,D,E,F,G,H,I,J,K,L,M,N,O"); QUERY('Каталог магазина'!P13:Q9029;"select P,Q,R")});"
Select "&join(",";filter(C7:C24;B7:B24="Да"))&"
WHERE
((A >= date'"&C2&"-"&D2&"-"&E2&"'
AND A
Вы в функции query импортируете массив в котором ищете колонки А,В и т.д., а надао искать Col1,Col2 ... Coln. Так как массив может не начинаться с столбца А. В любом случае дайте ссылку на таблицу посмотрим.
Добрый день!
Я наверно Вам уже надоел =)
Но хочется из Ваших уроков максимум вытащить
Сделал фильтр один в один по Вашему уроку, но оказалось, что если в одной из раскрывающихся строк пусто, то выдает ошибку, а хочется чтобы он в случае пустой строки считал все данные
Присоединяюсь к вопросу! А еще интересно как сделать так, чтобы была возможность выбрать и машиной и руками, т. е. несколько условий
Аналогичная вопрос
очень хорошие уроки, спасибо! очень познавательная информация, подписалась . я новичок, если можно подскажите: есть таблица- в первом столбце дата, в другом столбце заказы, в третьем сумма от заказов. в один день их может быть несколько и прописать их надо отдельно т.к. стоимость разная т. е. в первом столбце , например будет несколько строк 01.01.20 и так далее. необходимо прописать формулу сумму заказов , например, за весь январь, предусмотреть сколько строк вписывать в формуле невозможно, т.к. дальше идут февраль, март и т.д. как отсортировать по дате с 01.01.20 по 31.01.20
вам может пригодиться функция sumif, query,filter. Все уроки есть на канале, гляньте в плейлисте это одни из первых уроков. czcams.com/video/fVe7zNulhQI/video.html
Здравствуйте, подскажите, пожалуйста как сделать чтобы работала с динамическим фильтром ситуация WHERE F contains 'Вертолетом' ? если у меня в одной ячейке F через запятую перечислено несколько значений (например Вертолетом, Ногами), надо чтобы эта строка попадала в фильтр 'Вертолетом'.
Сделал домашнюю бух-ю, данные вводятся с гугл-формы (в анкете поля доход, расход - числовые, статья, источник - перечисляемые). В соседнем листе запили удачный запрос, который выводит за один раз всю интересующую меня аналитику
select E, sum(D)-sum(C) - статья дохода-расхода, и доход-расход
where not (A is null) - где метка времени заполнена (приходит автоматически из анкеты)
group by E - сгруппирована по статье
pivot month(A)+1 - раскинута по месяцам горизонтально (реально офигенно)
order by E - строковая сортировка по статье (например 01_Заплата, 99_Прочие расходы)
LABEL E 'Статья', sum(D)-sum(C) ' Фин.результат'
FORMAT sum(D)-sum(C) '0.00'
Остались 2 проблемы.
1.- FORMAT не работает. Ну не фатально, хотя досадно, ибо в доках - всё в порядке.
2. Тут посерьёзнее. Так как и расходы и доходы попадают в столбцы C и D, какой-то один из 2х столбцов пустой. null. И конструкция sum(D)-sum(C) в таких случаях выдаёт null, пусто. Чтобы она работала надо в исходной таблице пустые значения добивать нулём. Конструкции isnull, как в MS SQL тут нет, как интерпретировать "пустоту" нулём внутри select - я не нашёл.
Пытался в форме в числовые поля расхода и дохода поставить по-умолчанию 0, но значений по умолчанию там нет. Нули лупить в анкете или добивать их потом в результирующей таблице - совсем не айс.
Победа близка. Если интересно - файл вышлю, получилось просто и функционально.
2 вопрос есть решение в этом видео czcams.com/video/4xral33SzAo/video.html .. Вам необходимо провести фильтрацию по непустым значениям функции filter, not, isblank
По 1 надо смотреть формулу. Кидайте ссылку на таблицу и будем смотреть.
@@stmsolution Ссылку на емейл могу отправить, там вся моя чёрная бухгалтерия :) Только напишите куда отправить. Или черканите мне на renatius собака жмаил ком
По ситуации - полёт нормальный, проблему с isnull не решил, приходится лупить ноль, тогда считает правильно.
В конце месяца допишу вычисление сальдо по каждому кошельку, и введу статью "Небаланс", чтобы в конце месяца сверить что по факту в кошельках и на картах и зафиксировать ошибку, на сколько не бьётся.
Самое главное - это вполне рабочий и удобный способ совместного (с женой) ведения семейного бюджета, функционал я создаю для себя, учёт не внапряг вести. Мне очень нравится.
@@renatiusbe можно в личку, тоже бы вел такую бухгалтерию
Спасибо за видео. А по датам как сделать чтоб найденная информация выдавалась от более ранней к поздней по найденным датам?
Sort by
Здравствуйте, Станислав, как можно группировать и сортировать данные по другим таблицам-справочникам ( в случае, когда нужно сортировать на по названиям, а по специально созданному столбцу-индексу)? Такого понятия как схема данных для гугл таблиц не существует, как для экселевских таблиц?
Возможно именованные диапазоны помогут, если я правильно понял вопрос
Полезные примеры.
А как сделать так, чтобы в скрипте вместо явного указания листа (Лист 7) подставить название листа, возвращаемое функцией Session.getUser()?
spreadsheet.getRange('\Лист 7\'!B6:F6').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
Вот в таком виде:
spreadsheet.getRange('\???Session.getUser()???\'!B6:F6').copyTo(...
Отличные уроки спасибо. + Лайк). Скажите как сделать итог, который выводится таблицей, так, чтобы клетки были с контурами?
Вы имеете в виду сетку сделать?
@@stmsolution , да сетку, автоматически. А то меняя параметры, меняется и количество строк.
Надо пробовать через условное форматирование...
Спасибо за видео!!!
Вопрос. Можно ли через query парсить с другого листа (СВОДКА) изображения, вставленные в ячейки, которые, в свою очередь, парсятся ВПРом с третьего листа (ПРАЙС, к примеру)
Не, квери текст тянет
@@stmsolution спасибо за ответ! Еще часто бывает ошибка, что в пропадают некоторые колонки, почему?
Например. Колонка 1,2,3 - есть данные, 4 - нет данных, 5,6,7 - данные есть.
Код выглядит так:
={QUERY('СВОДКА'!A:L;"select B,C,D,E,F,G,H,I,J,K,L where(A=true) label (B)'',(C)'',(D)'',(E)'',(F)'',(G)'',(H)'',(I)'',(J)'',(K)'',(L)''");QUERY('ПОДВАЛ'!A:L;"select B,C,D,E,F,G,H,I,J,K,L where(A=true) label (B)'',(C)'',(D)'',(E)'',(F)'',(G)'',(H)'',(I)'',(J)'',(K)'',(L)''")}
Полезно в данный урок запихнуть сортировку по дате, те прописать в функции - "order by A desc"
Я стараюсь много в один урок не запихивать, так как ифо много и происходит каша в голове, лучше отдельным уроком.
Используйте функцию SORT
Добрый день, спасибо за урок. Все очень понятно и наглядно. Подскажите пожалуйста, у меня таблица, при вводе функции, формирует массив в 1 строчку, а не построчно. Подскажите пожалуйста как это можно поправить?
Надо более подробная инфа, скиньте функцию и ссылку на таблицу, будем смотреть
docs.google.com/spreadsheets/d/1h-Nijjl0a6nFAd8nvjOZWo9VQ6cr1DWa-VNvFZPyJnQ/edit?usp=sharing
Функцию брал из Вашего видео, поэтому опишу задачу. Дано 2 вкладки PLAY BOOK и Текущие позиции. Нужно, чтобы данные с листа PLAY BOOK со столбцов (AI по AQ) выводились на вкладку ТЕКУЩИЕ ПОЗИЦИИ. Получается, что выводятся все пустые строчки вместе с нужными. Хочется, что бы был список, без разрывов.
Добрый день. Скажите пожалуйста, можно ли вместо названия листа поставить в Query ссылку на ячейку, в которой пользователь выберет нужный лист? Спасибо.
А как сделать чтоб еще со всеми критериям вашими, выводилось не все Брюс Уилис, а один и сумма общая по ним, также Бэтмен один и сумма по закупкам общая?
Скажите, а как сделать, чтобы выводились только те строки, где в строке С товар встречается 2 и более раза, а те строки, где товар был один раз, не выводить? Например, если была дрель 1 раз, то ее вообще не выводить.
В уроке 26 на листе 3 ошибка: в запросе стоит неправильное наименование листа.
Отличный урок! Подскажите пжст, а как можно сделать так, чтобы одновременно, по одному столбцу фильтровалась по двум и более признакам!? К примеру, одновременно отфильтровывалось по "Вертолетам" и "Ногами"?
Or
Добрый день, спасибо большое, освоила таблицы только с ваших уроков. Помогите пожалуйста, как построить формулу при расчете плана на день, если значение от 0-1 , чтобы проставлял 1 , а не 0?
Вам допоможе функція "ROUNDDOWN" заокруглення до меншого числа
а если надо работать с двумя наборами данных? Навскидку, например с одного набора данных взять два столбца, а третий столбец выбрать с второго набора данных исходя с какого-то условия.
Буду признателен за ответ. Ваши видео очень познавательны.
Можно попробовать две функции query в массиве, то есть {query (данные из первых двух столбцов)/query(данные из другой таблицы 3 столбца)}
@@stmsolution я сделал иначе, написал квери для каждой ячейки новой таблицы ограничив результат одной записью, данные из первой скопировал.
Прелесть Гугл таблиц как раз в том что задачу можно решить разными способами
Можно ли сделать как-то, чтобы была возможность вывода данных по одному значению из выпадающего списка или , если я хочу просмотреть сразу по всем значения не только «ногами»? Заранее 😉
Не совсем понял вопрос.
STM Solution, вот есть у нас выпадающий список, там идет несколько значений, например : города, села, страны, и я могу выбрать только Одно из этих значений «Села», У меня в таблице будет показываться информация только по селам, а если мне нужна информация по городам , селам, странам , чтобы вывелись данные в таблице сразу по трем этим параметрам одновременно, так я не могу сделать. И вот вопрос , можно ли сделать чтобы выбирать все значения соазу из выпадающего списка?
Нет, так не выйдет, только в разные ячейки, или если хотите используйте функцию query там можно сразу по всем городам вывести и сгруппировать
Order by по-моему и столбец с городами
STM Solution , спасибо, попробую
Скажите, есть ли в гугл таблицах функционал наподобие срезов и временной шкалы сводных таблиц в Эксель. Вот что-то в сводных таблицах на Гугл не нашел, а ведь это такая удобная вещь.
сводные таблицы есть и здесь, если вы об этом
Привет у меня снова есть задачка ))) существует ли такая формула которая самостоятельно может проходить по созданным ссылкам в таблице ? И в идеале далее уже на странице куда прошли подтверждать действие. Сложно все выразить словами если есть возможность связаться с вами голосом : тел, скайп , дискор? Бил бы очень признателен.
docs.google.com/spreadsheets/d/1fh8GeP5FMRc-s5cJCnK7oRCoLfN4AL-VymsU6ubdBRk/edit?usp=sharing
В вашем случае я так понимаю ,надо использовать скрипты
Пишите , mitra654491@gmail.com, но не обещаю что что то получится
Привет, Станислав! Я новенький и залип на твои уроки. Возникла практическая необходимость автоматизировать одну простую задачу и я до сих пор не могу решить как.
Вот у нас есть 3 столбца данных (допустим, их 150 строк). Задача в том, чтоб при переносе на другой лист эти столбцы формировались в столбцы по 14 строк (то есть как бы блоками по 3 столбца на 14 строк). И так, вправо, пока не закончатся эти 150 строк.
Это нужно потом для вывода на принтер в альбомном режиме. Помоги, плз. Спасибо заранее!
Query pivot
STM Solution жаль, но пока не смог разобраться, как это сделать(
Посмотрите про query и про transpose ещё
=QUERY('Лист2'!A1:Q13;"select A,O where(UNIQUE'"&'Лист2'!A1:A13&"')")
нужно чтоб это работало ))
Уберите все что после where и само where тоже поместите функцию unique перед query , unique(query ())
Добрый день! я недавно начал свое знакомство с гугл таблицами и может вам это покажется, чем то слишком легким, но я никак не могу понять и гугл не находит ,как сделать в одной ячейке текст и маленькую(с курсор) картинку справа, я нагуглил функцию =IMAGE но если, перед ней текст-картинка пропадает. подскажите, если не сложно, как это реализовать🙏
Никак, вы для ячейки прописали формулу, а
текст её ломает. Только в отдельную ячейку.
support.google.com/docs/answer/3093333
Оно?
По image вот мой урок czcams.com/video/6h6nDIqKQXY/video.html
Большая просьба! Подскажите если есть возможность. Не хватает понимания как использовать функцию qwery (данные это импортируемые из другой таблицы с помощью IMPORTRANGE), и применять такие динамические фильтры как на видео. Пишет ошибку #ЗНАЧ!
Что именно вы пишите в формуле, без формулы тяжело определить что вы делаете не так?
@@stmsolution спасибо за обратную связь. Разобрался.
Вопрос по функции.
Есть задача, которая делается с 1 по 4 число. У задачи есть дата старта и дата окончания. Мне нужно выставить фильтр какие задачи делаются в период со 2 по 3е число. Используя метод показанный в видео, задача отображается только в том случае, если я выставляю дату начала 1 число а дату окончания 4е числоyadi.sk/i/1vbXF27eL04CjA , yadi.sk/i/hnISqXjADQwccg
Соответственно надо сделать так, чтобы выбирая любое число в период с 1 по 4е, чтобы отображалась задача
Добрый день. С помощью какой функции можно суммировать данные за определенную дату и разделить их ещё по двум показателям . Две кассы грубо говоря
Той же query на сколько понял вопрос.
@@stmsolution пример таблица 1 : столбец Е ( касса1) (касса2) . Столбец U( сумма оплаты по каждой кассе своя . Столбец V - Дата оплаты . Мне необходимо перенести в другую таблицу данные по каждой дате отдельно кассы 1 и отдельно кассы 2 . ….. надеюсь понятно объяснила….. ну как смогла . Помогите пожалуйста
Добрый день, Станислав
Подскажите пожалуйста, сталкивались ли вы со следующей проблемой :
в справке developers.google.com/chart/interactive/docs/querylanguage#Language_Syntax
указано, что при формировании запроса можно использовать заголовок столбца, а не его буквенное обозначение (А, B, AZ и проч), достаточно лишь использовать обратные кавычки, однако, таблица мне настойчиво выдает ошибку что такого столбца не нашла.. Не помогает и игры с третьим параметром функции Query.
Знаете ли вы разгадку, Станислав? Для моего проекта с большим количеством запросов использовать буквенные обозначения крайне неудобно и чревато, при последующем изменении количества и порядка столбцов. Спасибо
Надо писать скрипты и адаптировать функцию под именно названия
У вас прекрасные уроки! Они очень помогли подскажите в query если написать в where фильтр который будет отбирать из таблицы все даты старше сегодня по СЕГОДНЯ(), при этом даты в формате 22.04.2022. Все до чего я додумалась это в изначальной таблице сделать соседний столбец который 22.04.2022 приводит к yyyy-mm-dd и тогда запрос выглядит так QUERY('Счета'!A:G;"Select A,C,D,B,E where (G
Добрый, нет, не найдете по другому, только так как вы написали , только после G < надо дописать date . G< date &все остальное&
@@stmsolution спасибо вы спасли меня от потерянного зря времени))))
добрый день можете привести пример онлайн записи пациентов
Это очень размытый вопрос, конкретнее пожалуйста. Запись можно вообще через Гугл формы делать.
делаю один в один по видео и постоянно ошибки
=QUERY('work office(парс)'!1:3505;"select A,B,C where(B="&G2&" and C='"&H2&"')")
Не удалось интерпретировать query string. Подробности: Параметр 2 в функции QUERY:PARSE_ERROR: Encountered " "B "" at line 1, column 20. Was expecting one of: "(" ... "(" ... "(" ... "(" ...
Подскажите пожалуйста как взять данные не с другого листа, а из другого файла . Как правильно прописать importrange? Спасибо. Все сделать как в уроке, но данные взять из другой гугл таблицы. Ещё раз Спасибо.
Пропишите ссылку на таблицу и следующим аргументом пропишите путь к листу и диапазону, как если бы вы прописывали его в одной таблице и возьмите его в кавычки
@@stmsolution 1 формула ссылается на другой лист =QUERY('0297'!B7:L20,"select K,L,G,D,B where(G>=date'"&text(AA2,"yyyy-mm-dd")&"' and G=date'"&text(AA2,"yyyy-mm-dd")&"' and G
Как вы по календарику выбираете дату в ячейку?
Правильно ли я понимаю что прежде чем привести ячейку i3 к формату дат ее необходимо изначально привести в текстовому формату date' "TEXT(i3;"yyyy-mm-dd"), а привести ее
из числового формата сразу формат дат невозможно.
Да, верно, query воспринимает буквально данные в исходнике, а исходник у даты это число
@@stmsolution Спасибо
Подскажите, как написать вложенный запрос.
Объясните подробнее, что это значит
Кажется я единственный у кого возникла ошибка Не удалось интерпретировать query string. Подробности: Параметр 2 в функции QUERY:PARSE_ERROR: Encountered " "E "" at line 1, column 26. Was expecting one of: "(" ... "(" ... "(" ... "(" ...
Перепроверил всё раз 10, всё так же как на видео, но ошибка.
Надо смотреть формулу
Можете подсказать как написать where По UNIQUE
Более подробно вопрос сформулируйте. Не понял что вы хотите
А куда вы пропали?
Туда же куда и миллионы Украинцев((( Пока что нет вдохновения и желания что либо записывать, может когда все закончится, возобновлю, но пока нет.Но комменты я стараюсь читать и отвечать по возможности)) так что пишите ))
=QUERY('04.12.19'!A2:U300;"select A, B, C, S where (C="&H1&" and A='No info')") Не могу понять почему не работает(
надо смотреть на пример, возможно оба условия не соблюдаются. Так вроде все верно, без таблицы не разобраться
Ещё проверьте С у вас русская или английская, для формулы разница есть
@@stmsolution Спасибо за ответ! Почему-то заработало в таком виде =QUERY('04.12.19'!A2:J300;"select A, B, C where (C='"&H1&"' and A='Done')")
Отлично