Google таблицы.Функция Query.Динамические фильтры.Часть 2. Урок 26.
ฝัง
- เผยแพร่เมื่อ 14 ต.ค. 2024
- Вторая часть серии видео уроков по функции Query. В этом уроке мы продолжаем изучать возможности гугл таблиц, как пользоваться функцией Query в гугл таблицах, мы изучим как
фильтровать таблицу по динамическим данным которые выбираете вы, а так же как фильтровать значения в таблице по определенной дате или между ними.
В уроке присутствует новая функция TEXT о которой я вам не рассказывал, но думаю проблем с ней у вас не возникнет, так как по ходу видео становится понятно как она работает.
Смотрите до конца, будет интересно.
Станьте спонсором канала,. Подробнее:
/ @stmsolution
💰 Поддержать монетой:
🥇Bitcoin - bc1q5ve3p8zmcm6z9tapts6padpmnvv572tsujukad
📲 Через кнопку "Спасибо"
👍 Или станьте спонсором канала!
Ссылка на таблицу - goo.gl/R1G3jh
Кто хочет побаловаться с ней, просто создайте себе копию.
Поддержи автора канала пальцем в верх и подпиской это для
меня лучший стимул продолжать развиваться.
Мой паблик в Telegram - t.me/stmsolution
Буду рад видеть вас в моей группе ВК - public1...
Twitter - / stm_solution
Мой сайт stmsolution.ru
Еще больше видео на моем канале / @stmsolution
Супер, спасибо большое. Все четко и понятно, без воды. Успехов Вам!!!
Сижу и от восторга захватывает дух.
Тот момент когда ты так давно работаешь с гугл таблицами и понимаешь насколько ты чайник🤣😂.
Огромное спасибо!
Пожалуйста, еще видео о Query
всего знать невозможно. Там дальше вроде были еще. Не помню))
Спасибо Вам больше. Вы просто помогли решить серьезные вопросы в моей работе. И сократить большое затраченное время
Рад, что полезен
Спасиииибо! Очень нужная информация про && , не знал как решить проблему до видео.
Рад помочь
Спасибо огромное. Очень познавательно и очень помогло в работе. Удачи и успехов
Спасибо
Добрый день,делаю таблицу с продажами,ваши видео очень многое ускорили и помогли,но хотелось бы узнать следующее:Как выявить прогноз по продажам и как найти сумму сколько продаж нужно сделать в день что бы идти на 100 %.Удачи и по больше подписчиков
Автор, я взахлеб просмотрел 21 урок.... остальные обязательно досмотрю на днях! я думал что я хорошо разбираюсь в таблицах ))) теперь я понял что вообще не шарю!!! спасибо тебе большое! я для себя уже увидел несколько фишек, о которых даже не догадывался. ))))
Спасибо что смотрите
@@stmsolution не могу дойти до формулы никак (((( суть такая: у меня таблица по расчету стоимости секционных ворот. Есть вводные данные после замера: Ширина и высота. В таблице на пересечении параметров цена. Все в таблице Эксель. Сложно объяснить - могу показать, если отравишь почту. сможешь помочь?
@@АлександрПетрович-ъ5м mitra654491@gmail.com
@@stmsolution открыл доступ редактора ))))
там все описал
Написал вам на почту, свяжитесь со мной в телеграм
Подскажите пожалуйста. Можно ли ещё в этой функции сделать сортировку по алфавиту от а до Я по фамилиям. И также вставлять как видео сортировку по дате. Спасибо. В ваших видео все подробно об'ясняете о все понятно.
Order by A desc, B asc . что то типо такого, подставьте свои столбцы и виды сортировок
Спасибо, решил одну мою проблему
Вот прям помог! Спасибо!
На здоровье!
Круто! Спасибо за этот ролик!
спасибо что смотрите
очень хорошие уроки, спасибо! очень познавательная информация, подписалась . я новичок, если можно подскажите: есть таблица- в первом столбце дата, в другом столбце заказы, в третьем сумма от заказов. в один день их может быть несколько и прописать их надо отдельно т.к. стоимость разная т. е. в первом столбце , например будет несколько строк 01.01.20 и так далее. необходимо прописать формулу сумму заказов , например, за весь январь, предусмотреть сколько строк вписывать в формуле невозможно, т.к. дальше идут февраль, март и т.д. как отсортировать по дате с 01.01.20 по 31.01.20
вам может пригодиться функция sumif, query,filter. Все уроки есть на канале, гляньте в плейлисте это одни из первых уроков. th-cam.com/video/fVe7zNulhQI/w-d-xo.html
Здравствуйте! Спасибо за Ваши уроки, не знаю, что бы делал без них, однозначно лайк)
Если не затруднит, у меня большой вопрос:
Уже который день мучаюсь с 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. Так как массив может не начинаться с столбца А. В любом случае дайте ссылку на таблицу посмотрим.
огонь! очень помогла инфа как ссылаться на ячейку (через закрытие кавычек и &)
Рад помочь, смотрите и другие мои видео до конца, там много всяких фишек
@@stmsolutionну этим надо пользоваться чтобы в памяти оставалось. У меня довольно нетипичная задача была. Я в видеомикшер подтягиваю данные для титрования трансляций спортивного мероприятия. Нужно очень быстро в режиме реального времени вытягивать данные с БД
Здравствуйте, подскажите, пожалуйста как сделать чтобы работала с динамическим фильтром ситуация WHERE F contains 'Вертолетом' ? если у меня в одной ячейке F через запятую перечислено несколько значений (например Вертолетом, Ногами), надо чтобы эта строка попадала в фильтр 'Вертолетом'.
Прекрасный урок, странно что так мало просмотров и подписчиков
так никто ж не лайкает и не подписывается, а главное никто не делится такими видео с друзьями))Кому еще таблицы то нужны))
Здравствуйте, Станислав, как можно группировать и сортировать данные по другим таблицам-справочникам ( в случае, когда нужно сортировать на по названиям, а по специально созданному столбцу-индексу)? Такого понятия как схема данных для гугл таблиц не существует, как для экселевских таблиц?
Возможно именованные диапазоны помогут, если я правильно понял вопрос
Добрый день. Скажите пожалуйста, можно ли вместо названия листа поставить в Query ссылку на ячейку, в которой пользователь выберет нужный лист? Спасибо.
💥
Добрый день, спасибо большое, освоила таблицы только с ваших уроков. Помогите пожалуйста, как построить формулу при расчете плана на день, если значение от 0-1 , чтобы проставлял 1 , а не 0?
Вам допоможе функція "ROUNDDOWN" заокруглення до меншого числа
Отличный урок! Подскажите пжст, а как можно сделать так, чтобы одновременно, по одному столбцу фильтровалась по двум и более признакам!? К примеру, одновременно отфильтровывалось по "Вертолетам" и "Ногами"?
Or
Добрый день. С помощью какой функции можно суммировать данные за определенную дату и разделить их ещё по двум показателям . Две кассы грубо говоря
Той же query на сколько понял вопрос.
@@stmsolution пример таблица 1 : столбец Е ( касса1) (касса2) . Столбец U( сумма оплаты по каждой кассе своя . Столбец V - Дата оплаты . Мне необходимо перенести в другую таблицу данные по каждой дате отдельно кассы 1 и отдельно кассы 2 . ….. надеюсь понятно объяснила….. ну как смогла . Помогите пожалуйста
Можно ли сделать как-то, чтобы была возможность вывода данных по одному значению из выпадающего списка или , если я хочу просмотреть сразу по всем значения не только «ногами»? Заранее 😉
Не совсем понял вопрос.
STM Solution, вот есть у нас выпадающий список, там идет несколько значений, например : города, села, страны, и я могу выбрать только Одно из этих значений «Села», У меня в таблице будет показываться информация только по селам, а если мне нужна информация по городам , селам, странам , чтобы вывелись данные в таблице сразу по трем этим параметрам одновременно, так я не могу сделать. И вот вопрос , можно ли сделать чтобы выбирать все значения соазу из выпадающего списка?
Нет, так не выйдет, только в разные ячейки, или если хотите используйте функцию query там можно сразу по всем городам вывести и сгруппировать
Order by по-моему и столбец с городами
STM Solution , спасибо, попробую
Спасибо за видео. А по датам как сделать чтоб найденная информация выдавалась от более ранней к поздней по найденным датам?
Sort by
а если надо работать с двумя наборами данных? Навскидку, например с одного набора данных взять два столбца, а третий столбец выбрать с второго набора данных исходя с какого-то условия.
Буду признателен за ответ. Ваши видео очень познавательны.
Можно попробовать две функции query в массиве, то есть {query (данные из первых двух столбцов)/query(данные из другой таблицы 3 столбца)}
@@stmsolution я сделал иначе, написал квери для каждой ячейки новой таблицы ограничив результат одной записью, данные из первой скопировал.
Прелесть Гугл таблиц как раз в том что задачу можно решить разными способами
Сделал домашнюю бух-ю, данные вводятся с гугл-формы (в анкете поля доход, расход - числовые, статья, источник - перечисляемые). В соседнем листе запили удачный запрос, который выводит за один раз всю интересующую меня аналитику
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 вопрос есть решение в этом видео th-cam.com/video/4xral33SzAo/w-d-xo.html .. Вам необходимо провести фильтрацию по непустым значениям функции filter, not, isblank
По 1 надо смотреть формулу. Кидайте ссылку на таблицу и будем смотреть.
@@stmsolution Ссылку на емейл могу отправить, там вся моя чёрная бухгалтерия :) Только напишите куда отправить. Или черканите мне на renatius собака жмаил ком
По ситуации - полёт нормальный, проблему с isnull не решил, приходится лупить ноль, тогда считает правильно.
В конце месяца допишу вычисление сальдо по каждому кошельку, и введу статью "Небаланс", чтобы в конце месяца сверить что по факту в кошельках и на картах и зафиксировать ошибку, на сколько не бьётся.
Самое главное - это вполне рабочий и удобный способ совместного (с женой) ведения семейного бюджета, функционал я создаю для себя, учёт не внапряг вести. Мне очень нравится.
@@renatiusbe можно в личку, тоже бы вел такую бухгалтерию
Скажите, а как сделать, чтобы выводились только те строки, где в строке С товар встречается 2 и более раза, а те строки, где товар был один раз, не выводить? Например, если была дрель 1 раз, то ее вообще не выводить.
А как сделать чтоб еще со всеми критериям вашими, выводилось не все Брюс Уилис, а один и сумма общая по ним, также Бэтмен один и сумма по закупкам общая?
Добрый день, спасибо за урок. Все очень понятно и наглядно. Подскажите пожалуйста, у меня таблица, при вводе функции, формирует массив в 1 строчку, а не построчно. Подскажите пожалуйста как это можно поправить?
Надо более подробная инфа, скиньте функцию и ссылку на таблицу, будем смотреть
docs.google.com/spreadsheets/d/1h-Nijjl0a6nFAd8nvjOZWo9VQ6cr1DWa-VNvFZPyJnQ/edit?usp=sharing
Функцию брал из Вашего видео, поэтому опишу задачу. Дано 2 вкладки PLAY BOOK и Текущие позиции. Нужно, чтобы данные с листа PLAY BOOK со столбцов (AI по AQ) выводились на вкладку ТЕКУЩИЕ ПОЗИЦИИ. Получается, что выводятся все пустые строчки вместе с нужными. Хочется, что бы был список, без разрывов.
Спасибо за видео!!!
Вопрос. Можно ли через 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)''")}
Отличные уроки спасибо. + Лайк). Скажите как сделать итог, который выводится таблицей, так, чтобы клетки были с контурами?
Вы имеете в виду сетку сделать?
@@stmsolution , да сетку, автоматически. А то меняя параметры, меняется и количество строк.
Надо пробовать через условное форматирование...
Скажите, есть ли в гугл таблицах функционал наподобие срезов и временной шкалы сводных таблиц в Эксель. Вот что-то в сводных таблицах на Гугл не нашел, а ведь это такая удобная вещь.
сводные таблицы есть и здесь, если вы об этом
Правильно ли я понимаю что прежде чем привести ячейку i3 к формату дат ее необходимо изначально привести в текстовому формату date' "TEXT(i3;"yyyy-mm-dd"), а привести ее
из числового формата сразу формат дат невозможно.
Да, верно, query воспринимает буквально данные в исходнике, а исходник у даты это число
@@stmsolution Спасибо
У вас прекрасные уроки! Они очень помогли подскажите в 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 спасибо вы спасли меня от потерянного зря времени))))
Полезные примеры.
А как сделать так, чтобы в скрипте вместо явного указания листа (Лист 7) подставить название листа, возвращаемое функцией Session.getUser()?
spreadsheet.getRange('\Лист 7\'!B6:F6').copyTo(spreadsheet.getActiveRange(), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
Вот в таком виде:
spreadsheet.getRange('\???Session.getUser()???\'!B6:F6').copyTo(...
В уроке 26 на листе 3 ошибка: в запросе стоит неправильное наименование листа.
Добрый день, Станислав
Подскажите пожалуйста, сталкивались ли вы со следующей проблемой :
в справке developers.google.com/chart/interactive/docs/querylanguage#Language_Syntax
указано, что при формировании запроса можно использовать заголовок столбца, а не его буквенное обозначение (А, B, AZ и проч), достаточно лишь использовать обратные кавычки, однако, таблица мне настойчиво выдает ошибку что такого столбца не нашла.. Не помогает и игры с третьим параметром функции Query.
Знаете ли вы разгадку, Станислав? Для моего проекта с большим количеством запросов использовать буквенные обозначения крайне неудобно и чревато, при последующем изменении количества и порядка столбцов. Спасибо
Надо писать скрипты и адаптировать функцию под именно названия
Привет у меня снова есть задачка ))) существует ли такая формула которая самостоятельно может проходить по созданным ссылкам в таблице ? И в идеале далее уже на странице куда прошли подтверждать действие. Сложно все выразить словами если есть возможность связаться с вами голосом : тел, скайп , дискор? Бил бы очень признателен.
docs.google.com/spreadsheets/d/1fh8GeP5FMRc-s5cJCnK7oRCoLfN4AL-VymsU6ubdBRk/edit?usp=sharing
В вашем случае я так понимаю ,надо использовать скрипты
Пишите , mitra654491@gmail.com, но не обещаю что что то получится
Полезно в данный урок запихнуть сортировку по дате, те прописать в функции - "order by A desc"
Я стараюсь много в один урок не запихивать, так как ифо много и происходит каша в голове, лучше отдельным уроком.
Используйте функцию SORT
Добрый день!
Я наверно Вам уже надоел =)
Но хочется из Ваших уроков максимум вытащить
Сделал фильтр один в один по Вашему уроку, но оказалось, что если в одной из раскрывающихся строк пусто, то выдает ошибку, а хочется чтобы он в случае пустой строки считал все данные
Присоединяюсь к вопросу! А еще интересно как сделать так, чтобы была возможность выбрать и машиной и руками, т. е. несколько условий
Аналогичная вопрос
Привет, Станислав! Я новенький и залип на твои уроки. Возникла практическая необходимость автоматизировать одну простую задачу и я до сих пор не могу решить как.
Вот у нас есть 3 столбца данных (допустим, их 150 строк). Задача в том, чтоб при переносе на другой лист эти столбцы формировались в столбцы по 14 строк (то есть как бы блоками по 3 столбца на 14 строк). И так, вправо, пока не закончатся эти 150 строк.
Это нужно потом для вывода на принтер в альбомном режиме. Помоги, плз. Спасибо заранее!
Query pivot
STM Solution жаль, но пока не смог разобраться, как это сделать(
Посмотрите про query и про transpose ещё
Подскажите пожалуйста как взять данные не с другого листа, а из другого файла . Как правильно прописать 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
Большая просьба! Подскажите если есть возможность. Не хватает понимания как использовать функцию qwery (данные это импортируемые из другой таблицы с помощью IMPORTRANGE), и применять такие динамические фильтры как на видео. Пишет ошибку #ЗНАЧ!
Что именно вы пишите в формуле, без формулы тяжело определить что вы делаете не так?
@@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: "(" ... "(" ... "(" ... "(" ...
Как вы по календарику выбираете дату в ячейку?
добрый день можете привести пример онлайн записи пациентов
Это очень размытый вопрос, конкретнее пожалуйста. Запись можно вообще через Гугл формы делать.
Добрый день! я недавно начал свое знакомство с гугл таблицами и может вам это покажется, чем то слишком легким, но я никак не могу понять и гугл не находит ,как сделать в одной ячейке текст и маленькую(с курсор) картинку справа, я нагуглил функцию =IMAGE но если, перед ней текст-картинка пропадает. подскажите, если не сложно, как это реализовать🙏
Никак, вы для ячейки прописали формулу, а
текст её ломает. Только в отдельную ячейку.
support.google.com/docs/answer/3093333
Оно?
По image вот мой урок th-cam.com/video/6h6nDIqKQXY/w-d-xo.html
Подскажите, как написать вложенный запрос.
Объясните подробнее, что это значит
Кажется я единственный у кого возникла ошибка Не удалось интерпретировать query string. Подробности: Параметр 2 в функции QUERY:PARSE_ERROR: Encountered " "E "" at line 1, column 26. Was expecting one of: "(" ... "(" ... "(" ... "(" ...
Перепроверил всё раз 10, всё так же как на видео, но ошибка.
Надо смотреть формулу
Вопрос по функции.
Есть задача, которая делается с 1 по 4 число. У задачи есть дата старта и дата окончания. Мне нужно выставить фильтр какие задачи делаются в период со 2 по 3е число. Используя метод показанный в видео, задача отображается только в том случае, если я выставляю дату начала 1 число а дату окончания 4е числоyadi.sk/i/1vbXF27eL04CjA , yadi.sk/i/hnISqXjADQwccg
Соответственно надо сделать так, чтобы выбирая любое число в период с 1 по 4е, чтобы отображалась задача
Можете подсказать как написать where По UNIQUE
Более подробно вопрос сформулируйте. Не понял что вы хотите
=QUERY('Лист2'!A1:Q13;"select A,O where(UNIQUE'"&'Лист2'!A1:A13&"')")
нужно чтоб это работало ))
Уберите все что после where и само where тоже поместите функцию unique перед query , unique(query ())
А куда вы пропали?
Туда же куда и миллионы Украинцев((( Пока что нет вдохновения и желания что либо записывать, может когда все закончится, возобновлю, но пока нет.Но комменты я стараюсь читать и отвечать по возможности)) так что пишите ))
=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')")
Отлично