Выпадающий список в Excel с поиском
ฝัง
- เผยแพร่เมื่อ 9 ก.ค. 2021
- В этом видеоуроке рассматривается создание выпадающего списка с возможностью поиска элементов. Представленный подход базируется исключительно на функциях и применим во всех версиях Excel.
Файл из видео для упражнения: drive.google.com/file/d/11Iw0...
Хочешь поддержать мой канал? Это можно сделать с помощью функции «Спонсорство»:
/ @bilyalkhassenov
Билял, меня восхищает не только Ваше знание Excel, но и Ваша подача материала (скорость, содержание), а также Богатый Русский Язык! Респект!
ООоообалденно! Я уже знаю где применю знания из этого отличного урока в своей работе.
Жму руку автору канала!!!
Самый огромный минус этого метода, заключается в том, что для нескольких ячеек необходимо дублировать списки (( как за частую бывает, что необходим целый столбец с выпадающим списком, что к сожалению этот способ уже невозможно применить...хотя возможно попробовать закинуть обе выполняющие столбцы в диспетчер имён, создать не абсолютную ссылку на ячейку, тогда при копировании значений, так же будут изменятся показатели для каждой ячейки, при этом диапазон списка будет один, но в этом случае прийдется работать уже как с обычными ссылками. Может и получится. И ещё одно, я сумел с помощью формул преодолеть самый главный недостаток ВПР отображения первого подходящего значения в списке. А именно в результат поиска отобразились все удовлетворяющие записи к тому же искать можно по нескольким значениям, но есть большое но которое я не смог преодолеть это быстродействие, а так же если в одном из критериев высше есть параметр, по которому нет данных тогда поиск значений которые следуют под ни не будут искатся (о расширенном фильтре я зная, хочу увидеть возможность как по другому можно реализовать такое). Заранее спасибо если прочтете сообщение, хотя оно длинное его вряд ли хоть кто то прочтёт. Хорошего вам времени суток)))
Тоже самое нужно выпадающие списки для целого столбца с поиском и этот метод не катит, для каждого списка нужен свой источник дублер. Как решить данный вопрос не придумал.
@@kompozitor21 у самого сейчас такая же проблема.
Евгений вы нашли решение данной проблемы. Нужно тоже выпадающий список с поиском для сметы.
Ну, вот. Делала два дня таблицы. И оказывается работы не уменьшилось😱 Хорошо, что вы этот коммент написали,а то ещё б дня три копалась в поисках выхода🤭
@@kompozitor21 мне пришлось создавать (а потом скрывать) целый лист для формирования выпадающих списков для различных документов. Но в этом есть и большой плюс: это позволяет отслеживать процессы их формирования!
В общем дошёл до того, что при заполнении карточек-таблиц с помощью выпадающих списков количество ФИО в последующих списках убывает по мере их использования. По исчерпанию записей в выпадающем списке выдаётся текст "Сотрудники закончились!" 😁
Спасибо огромное за урок, Билял! С интересом смотрю все Ваши видео
Это круто!)) Спасибо, что находишь мотивацию к записи своих обучающих видео! С большим уважением и удовольствием к твоему творчеству!👍😃
Интересный вариант. Разбор формул вообще шикарный спасибо 🔥🔥🔥
Даже и не знаю как выразить благодарность. Сегодня моя жизнь радикально изменилась и стала прекраснее. Жму руку!
Спасибо огромное. Теперь знаю как решить одну из задач, которая стояла передо мной ранее, но не хватало знаний как это сделать. Благодаря этому уроку, смогу внедрить в свои скрипты, улучшающие производительность и эффективность.
Хитро придумано! Билял, благодарю! Это гениально!
Подробный и разборчивый урок. Наконец-то я могу не искать каждый раз из выпадающего списка нужное слово, ведь поиск мне облегчает и ускоряет работу. За столько времени именно ваш урок помог решить мне эту проблему. Спасибо за ваш труд и терпение)
Здравствуйте, Анастасия!
Подскажите пожалуйста как это применяете? Каждый раз копирует значение из динамического выпадающего списка? Можно ли это сделать на весь столбец для автозаполнения???
Ролик шикарный, тема актуальная, и вообще, Билял - молодец!
По поводу функции СМЕЩ для создания автоинкремента. Возможно, возможно... Но можно и попроще, я думаю. Во всяком случае сам давно уже пользуюсь чем-то типа: =МАКС($A$1:A1)+1, без всяких смещений. В таком виде эта формула также корректно растягивается вниз, выдавая упорядоченную нумерацию, даже после удаления строк из списка
Спасибо Вам за очень большой проделанный труд, а особенно за то, что правильно и последовательно все это объяснили!!!
За видео спасибо! Но как уже многие писали ниже, одна поисковая ячейка никакой практической пользы не несёт, как правило нужно целый столбец с возможностью поиска и автоподстановкой, одно поле может быть полезно когда ты сам для себя пытаешься выделить что-то из например картотеки чего-то
Как всегда, все твои уроки на высоте.
Не понимаю тех, кто ставит дизлайки.
Как всегда все ясно и понятно!!!
Спасибо за видео. На днях просили сделать такую таблицу, а я предложила только фильтр, вникать было некогда. Теперь знаю как это сделать еще удобнее.
Гениально. Я думал на ютубе один только маг по excel'ю - Павлов. Ан нет, есть еще и Хасенов.
Спасибо огромное за Ваш труд.
Век живи, век учись! Спасибо!
спасибо . класс. все по делу . четко и понятно ... без лишних пред Разговоров
Супер! Спасибо! Вроде, формулы несложные, но сам бы не додумался...
Классно. Реалезую на работе такое
Спасибо большое 🙌🙌🙌💡
Круто! Полезно! Спасибо
Это гениально! Я даже не догадывался об истинной мощи екселя)
Спасибо, все четко и доступно
Спасибо Биляль, как всегда!
Большое спасибо. Попробовал сделать все в одной таблице, т.е. фильтрация - не отдельная таблица. В этом случае при вводе нового товара не нужно вручную добавлять ячейки в фильтрацию - они добавляются автоматически.
Привет из Туркменистана 👍 спасибо за урок
апупеть просто, это взрыв мозга. Очень круто
Мега круто, спасибо большое
Отличное видео!!!
Вопрос частный - в каком редакторе Вы монтируете видео?
Спасибо,Билял! Очень позновательное видео! Смотрю все новые ваши видео и считаю эту информацию очень ценной! Хочу подкинуть тему для будущих видео и узнать методы, которыми пользуетесь вы в данном вопросе. Парсинг данных с сайтов ( например Post, Get запросы), а так же автоматизация этих процессов стандартными средствами VBA и с использованием Selen. Надеюсь, такой контент будет интересен и другим вашим подписчикам. Еще раз, спасибо, за Ваш труд!
Здравствуйте, Александр! Всегда пожалуйста, рад что канал Вам нравится! 😊
По поводу темы - она и вправду очень интересная. Пока что для стягивания данных с интернета применяю лишь Power Query, но на самом деле я уже думал снять плейлист по вебскрейпингу, возможно, с помощью Python. Так как самому еще нужно считай всё в этой области изучать, выпуск подобного плейлиста вероятно слегка затянется. Но в планах это есть.
Да и про Power Query рано или поздно собираюсь выпустить плейлист. В общем, план примерно такой .
Хорошего Вам дня! 😊
@@BilyalKhassenov кстати, парсить информацию с сайтов умеют гугл-таблицы. :)
СУПЕР-Круто!!! и СУПЕР-понятно!!!
Спасибо, большое!
Спасибо Вам большое!
Класс! Просто класс!
Супер!!!! Спасибо!!!!💪💪💪
Это гениально 🥇
Очень круто. Я с третьей попытки сделала. Непонятно было от других авторов. И тем более текстом. Спасибо
А на несколько строк я так понимаю не работает. Мне нужно что бы такой фильтр был в 50 ячейках, в каждой нужно выбрать разные товары с одинакового списка, как это реализовать можно ?
Вместо ВПР использовал ПРОСМОТРХ. А так все отлично. Спасибо.
Спасибо большое тебе!
Спасибо большое!
Спасибо! Уже вставляю в рабочий документ!
Чел, ты лучший!!!
Пример ради примера. Не применимо это на практике. На практике нужно добавлять строку и в добавленной строке уже искать, а не в одном и том же окошке всегда. В любом случае информация очень полезная, спасибо большое!
подскажите пожалуйста, как добавить такие выпадающие списки к нескольким строкам или, например, таблице, чтобы в каждой ячейке выпадал такой список?
Всего-навсего, чтобы добавить 1 функцию, нужно потратить дофига времени.
Это же прекрасно!
Мне больше интересно, когда таких записей больше 25000, то не будет ли все это чудо тормозить при открывании списка и поиска в нем?
Круто! Очень полезно. Но сложно. :)))
Супер!
Бро, ты крут!
Очень функционально
Оч круто, понятно отлично! Единственное что не работает по всему столбцу поиск после протяжки формулы..только в первой строке и все. Не могу никак настроить правильно..
отлично!
Ништяк. А можешь показать как это сделать с помощью VBA в Userform?
Билял, большое вам спасибо. Наконец -то я смог сделать, что хотел. А возможно ли сделать так, чтобы выпадающий список работал и на следующей строке таблицы? Поясню. есть накладная куда нужно заносить несколько товаров построчно из одной базы. Я получил выпадающий список для одной строки, а таких нужно несколько.
Очень приятный голос! Прирожденный учитель. Или диктор?
Спасибо, круто объяснили. Вот как теперь для много ечеек сразу сделать такой выпадающий список?
Добрый день! Билял, как настроить двух и трехуровневый выпадающий список, на основе, допустим, первого выбранного значения сделать в следующем столбце выпадающий список по подгруппе, например, в первом выбрал группу овощи, а во втором столбце выпадающий список потянул значения для выбора только из подгруппы овощей?
красавчик!👍
то что нужно обнял и лайкнул ) список это хорошо но как выводить вместе со списком значения ? например наименование а рядом цена.
Здравствуйте, спасибо за урок. А как сделать так чтобы из ячейки поиска по нажатию (предположим enter) значения записывались в новую колонку или столбец?
Подскажите какой вариант лучше использовать, если у меня 10 справочников они же таблицы (он обновляемый и подгружаемый другими пользователями в свои учетные таблицы) от 100 до 1000 значений? У каждого пользователя сделать выбор из справочника с поиском.
Спасибо большое всё получилось. Но в моём случае есть нюанс, а именно большое количество повторяющихся значений, от них можно как-то избавиться, и выводить только уникальные?
Добрый день. Подскажите пожалуйста, как сделать поиск по листам книги в ВБА с одной формой заполнения для всех листов (такая структура нужна для оределенной формы отчета). Спасибо.
Добрый день! Спасибо! Отличное видео! Отлично работает для первого значения. А если в таблице несколько значений? Тогда как быть дальше всё ламается. Мой вопрос понятен? Всем спасибо за ответ
А есть ли у вас видео, где результат из выпадающего списка с поиском выдавал значение в соседней ячейке , например цену? Спасибо
ВПР
Добрый день, прочитал все комментарии и везде наблюдается один и тот же "как применить этот поиск для любого количества строк в столбце из одного списка". Сложилось так, что я много времени потратил впустую реализуя этот метод, когда все сделал выяснилось, что практического применения этот способ не имеет.
Просто берем, в списке, нажимаем Ctrl + F и вуаля, мы нашли что искали, зачем тогда все эти формулы длинные?
Буду очень признателен если кто поможет, как все-таки это применить на любом кол-ве строк из одного списка, наверняка у кого-то есть готовое решение. Заранее благодарю!)
Здравствуйте спасибо вам огромное! Скажите есть ли способ решения задачи я веду статистику по хоккею есть столбец Счёт в одной ячейке условно 3 ниже 4 и так 900+ значений (хочу подсчитать количество точных счетов какой играл сколько раз)
Очень полезное видео, спасибо!
Единственное, у меня образовалась проблема, ячейка с Выбором товара не превращается в список, хоть и происходит фильтрация, список не образуется, как это решить?
Добрый день, как Вы вверху сделали строка 1 и строка 2. - + , где добавили (составили) сам список элементов?
Огромная благодарность! Это гениально!
P.S. Жаль, правда, не подходит для столбца со списками. Может есть какое-то решение?
В офисе 365 и в 2021 слава Богу всё уже намного лучше)
Не совсем понимаю зачем создавать две таблицы можно сделать в одной просто рядом добавив столбец фильтрации цикла вроде бы нету. Я бы исправил ещё выпадающий список так как когда вписал три буквы вариантов 1 или 2 а выпадающий с ползунком так как тянет и пустые значения в таблице, поэтому я бы исправил в данных задав функцию СМЕЩ в связке с итогами подсчет не пустых значений в столбце. Тогда выпадающий будет автоматом подстраивать размер окна если он меньше 8 позиций. 👍
Классно, быстро и понятно. А если к моему списку принадлежит информация с 3 столбцами, как захватить и ее, чтобы выпадала вся информация о том товаре? Спасибо вам за полезные видео.
Добрый день! Подскажите, пожалуйста, как сделать фильтр с другого листа? Двссыл не работает.
а можно ли с помощью формул например выпадающий список например класса?
например пишешь 1а и выскакивает список всего 1а класса,
с помощью впр так невозможно сделать, там выпадает только 1 значение.
Спасибо, помогли очень!!! Есть вопрос, если выпадающий список с поиском на другом листе, то как указать ссылку через функцию двссыл?
Super.
Добрый день, а подскажите возможно ли сделать выпадающий список с поиском работающий на весь столбец? (не отдельно прописывая формулы для каждой ячейки
все супер, но это все на 1 ячейку для списка товаров. а если мне надо тысячи ячеек с списком товаров или каких либо наименований. то такои вариант уже не подходит? как мне указать в нумерации все те ячейки в которых будет "список товаров"?
Здравствуйте! подскажите, можно ли сделать двссыл чтобы работал и во второй и тд строке, а то получается я связала в первой строке ячейку 2 с ячейкой 1, далее во второй строке опять идет ссылка ячейки 2 на ячейку 1, НО только из первой строки, а не из второй, что я делаю не так?
пишу формулу поиск, заполняю . Эксель пишет there's problem with this formula и все. на сайтах пишут что программа не понимает что хочу сделать поэтому ничего нельзя сделать. как так?
А как сделать общий поиск имея в одной книге 2 и более листа которые содержат разные таблицы (разные по строению). Пример: имеем 4 разные базы данных работников разных предприятий, и нужно сделать один поисковый запрос который проверит все эти базы и выведет результат. Каждая база на отдельном листе. Все совпадения которые могут быть (или не быть) должны отображаться в результате запроса. При этом строение баз могут отличатся, где-то фио разбито на три столбца, где то фио одним столбцом. Но в конечном результате при запросе фамилии будет проводится поиск сразу во всех листах и выдаваться результат, не только саму найденную фамилию, а и все содержимое строк где есть данная фамилия. Что-то на подобии выпадающего списка с поиском, но не на одном листе, а общий для всей книги, но и с разным строением таблиц...
Супер тема. Но не совсем то, что мне нужно. Хотя ооооочень полезно.
Можете сказать как сделать так, что бы такая строка была в каждой строчке. Т.е. у меня таблица где в первом столбце я пишу дату, а во втором столбце пишу поставщика из списка. Так этот список мне нужно сделать выпадающий с поиском.
Чтобы преобразовать булевое значение ЛОЖЬ или ИСТИНА в 0 или 1 необязательно использовать функцию ЕСЛИ. Достаточно умножить на 1
Добрый день, подскажите пожалуйста, возможно ли "скопировать" ячейку - выбор товара на другой лист, чтобы на нем работал поиск? если да-то как это сделать?. нужно чтобы выпадающий список и поиск работал на другом листе таблицы
Добрый день. Возможно ли реализовать этот способ без использования "умных" таблиц, например, при помощи динамически именованных диапазонов? "Умные" таблицы использовать лучше, если файл сделан для себя, а не передавать другим пользователям, т. к. другие пользователи, возможно, не зная про "умные" таблицы, "сломают" их на раз-два.
Можно
Привет хорошое видео спасибо. А может вып. Список быть не в одной ячейке а вниз на несколько?
Здравствуйте. Подскажите как сделать выпадающий список на 1 листе а список товаров на 2 листе. Я когда делаю =двсл("фильтрация") у меня вместо списка товара выходит только слово фильтрация
спасибр
Здравствуйте! Можно ли сделать более одной выпадающей ячейки с поисковиком? Я хочу создать файл, в котором заполняется параметры техники (этот список будет выпадающим), чтобы при заполнении работала эта функция "Поиска" и в последующих заполняемых ячейках
Интересный вопрос, я бы то же хотел получить на него ответ
Можно, но прийдется на каждую новую ячейку с таким выпадающим меню (кстати, это не поиск, а предфильтрация!) создавать по паре своих доп. колонок в умных таблицах. Или же доп. использовать макрос на VBA -- одно из решений приведено в комментариях выше, хотя оно и неоптимальное, т.к. если уж использовать VBA, то логичнее и эффективнее весь алгоритм фильтрации делать на нем, без лишних умных таблиц и мудреных формул. Но это уже требует некоторых программистких навыков и знаний.
Познавательно, спасибо! А есть ли возможность выпадения одного из списков по условию значения в соседней ячейке? Подстатьи только к определенной выбранной статье
конечно искать связанные выпадающие списки
А почему фильтрацию не сделать в той же таблице?
Замечательный урок! Благодарю вас, Билял. А как сделать выпадающий список не в одной ячейке, а в таблице? Например, мне нужен список ФИО ответственных за реализацию мероприятий. При этом ответственных может быть 50, а мероприятий несколько сотен.
Такой хитрый выпадающий список с предварительной фильтраций значений (а не поиском, как почему-то назвал это автор) распространить на много ячеек невозможно. Прийдется либо для каждой новой ячейки со списком создавать в умных таблицах справа по паре доп. колонок с жесткой привязкой к своей исходной ячейке, либо использовать совсем другие решения и возможности Excel: скрипты на VBA или новейшие фичи Excel 365 / 2021.
Если же вас интересуют просто выпадающие списки без фильтрации, то их настройки легко копируются (меню "Специальная вставка") или задаются сразу для выделенной группы ячеек или даже всего столбца/строки через тот же инструмент "Проверка ввода".
Добрый день. Подскажите пожалуйста как сделать , чтобы список распространялся на столбец.
Гений! я 2 недели голову ломаю как сделать тот же функционал со смещением, в итоге Эксель ругается)
Добрый день. А как сделать, чтобы этот же функционал работал и для ячеек B4, B5, и т.д. По этому примеру последующие ячейки работают с диапазоном из B3 ?
Добрый день! Спасибо, у меня всё получилось) А как сделать множество таких ячеек "выберите товар"? Очень надо, подскажите...
Спасибо, очень интересный ролик. Подскажите, как выпадающий список продолжить(чтобы каждая последующая ячейка также имела выпадающий список с поиском и на неё не влиял отсутствие\наличие товаров в предыдущей ячейке?
Да, тоже хотелось бы ответ на этот вопрос. Например для заполнения например накладной, чтоб по первым буквам вносить данные из номенклатуры! И так далее в каждой строке!