Связанные выпадающие списки в Excel 

Большое спасибо! Благодарности нет границ! Только у вас нашла нужную информацию!

Огpомное спасибо за ваши труды! Удачи, здоровья и благополучия Вам!

Спасибо огромное!! Несколько вечеров над задачей мучился!! С Вашим роликом за час все сделал. Удачи Вам!!

Обожаю Ваш сайт и подсказки! Спасибо, что Вы есть!

Я посмотрел данное видео и потратил 16 минут личного времени, и понимаю что теперь могу сэкономить своё и других людей, время обработки служебной информации. Вы один из лучших мастеров, в работе с данным инструментом!!!

Николай, благодарю за подробное описание. Очень полезная информация. Прошу описать как продлить (скопировать) в 3 способе ячейку "товар" так чтобы она не ссылалась на материнскую ячейку "Категория"

Огромное спасибо. Очень ценный урок. Воспользовался информацией из этого видео для решения своих задач. Очень помогло.

Николай, благодарю за ценную информацию! Очень помог третий способ. В него я ещё интегрировал нюанс второго способа, т.е. списки библиотеки перевести в таблицу, чтобы при поступлении новых данных (с последующей сортировкой) - они добавлялясь в "умный диапазон" и не приходилось делать корректировки

16 Минут данного видео, сохранят мне уйму времени, полагаю не только мне. Большое, человеческое спасибо!

Подача просто супер! Всё ясно и понятно, без лишних слов!!! 100℅ лайк и подписка!

Ой, так легко доносите информацию! ОГРОМНОЕ ВАМ СПАСИБО! ЖЕЛАЮ ВСЕХ БЛАГ!

Николай, спасибо вам за все без исключения видео. Искала решение одной проблемы и нашла его в одном из ваших роликов. И такое началось... :-) Мне так понравилась подача материала и приемы, которые не описывают в книгах, что я стала смотреть все видео подряд. Очень познавательно, но еще больше поучительно. Каюсь, без ваших подсказок я раньше делала некоторые расчеты часами. Теперь опасаюсь, работодатель может подумать, что мне нечем заняться на рабочем месте :-)))

Николай, большое Вам спасибо. Вы мне очень помогли. Без видео разобраться не получалось.

Спасибо!!! Очень хорошо вы объясняете, просто и доходчиво!

Шикарно. 3й способ прям хорош. Люблю такие замороченные вещи.

Нарвался на этот контент! Шарахнул инсайт! Отходил мин. 20! Николай ты реально крут! Подписался! Отправил ссылку Бухгалтеру! Огромное человеческое спасибо!

Николай спасибо большое. Очень полезная информация.

Большое спасибо за видео! Всё предельно понятно изложено!

Спасибо вам большое, вы очень облегчаете мою аналитическую работу)

Шикарно! В который раз убеждаюсь в полезности функции "ДВССЫЛ" ;)

Очень полезные уроки. Спасибо Вам большое.

Спасибо большое! Просто, наглядно и очень полезно!

Гениально. Спасибо, Николай.

Spasibo vam bolshoe, vashi vidoe uroki ochen iasnie, legkie dlia ponimania i vsio chetko oboznacheno. vsega polchaiu bolshuiu pozu !!!!! Super video ot Super Prepodovatelia!!!

ОГРОМНЕЙШЕЕ СПАСИБО!!! ИСКАЛА ИМЕННО ЭТУ ПОДСКАЗКУ!!! 🙏🏼🙏🏼🙏🏼

Отличные объяснения: четко, по делу, без воды, не скороговоркой. Был бы признателен, если бы появился видеоурок по следующей проблеме. Есть некий список, скажем: без ворот, откатные ворота, распашные ворота, складчатые ворота, роллет. Ниже есть выбор количества в штуках. Мне бы хотелоь, если выбор сделан "без ворот", то в графе количество появляется 0, во всех остальных случаях чтобы можно было ввести любое число.

Спасибо. Очень полезное информация!

Офигенно!! 👍 желаю добиться больших высот 🤟

Благодарю. Очень ценная информация.

Очень полезно. Спасибо.

Шикарно! всё получилось.

Николай, спасибо вам доступное объяснение. Хотелось бы узнать возможно ли на одном листе собрать информацию о тарифах курьерской службы (допустим на доставку чего-либо на разные расстояния) двух или трех компаний, анализа? Заранее благодарен.

Спасибо огромное, мы Вас любим))

Vse vashi video ochen poleznie i aktualnie, spasibo za vashi starania

Инструкции у всех примерно одинаковые, но ваш голос приятнее. Ищу только ваши лайфхаки по экселю)

Блестяще! Спасибо!

Спасибо. Очень полезное видео. Я подписался.

Спасибо, всё получилось!!

Николай, доброго дня! Спасибо за ваше видео. Хотелось бы узнать, можно ли в выпадающем списке подтянуть значение с прикрепленной ссылкой? То есть в исходной таблице значение с ссылкой на ютуб, а в выпадающем списке необходимо сохранить то же значение со ссылкой. Заранее спасибо

Спасибо То что искал :) BIG LIKE

Спасибо Николай, всё очень понятно. Не могли бы вы объяснить как продлить лист выпадающих свянных списков на другие строки с теми же данными.... На данный момент приходиться вручную вводить ссылки...

Очень подробные уроки, хотя такое редко встретишь. А как провернуть такое с элементами Active x? Чтобы следующее поле со списком выдавало в зависимости от выбора предыдущего

Николай, все круто, но еще бы юзал Ctrl-C /Ctrl-V - для переименования Таблиц и другие приемчики с коментами (которые обычно импользуешь в работе) - и было бы совсем ок! Еще желательно снизу видео писать названия функций на англ! и русском (кому - нужно) - чтобы видео можно было проще искать и вспоминать! Спасибо (не первый раз натыкаюсь на видео - пришлось Подписаться)!!! =)

Николай, здравствуйте. Прекрасное видео. Есть ли решения для многоуровневых списков с повторяющимися элементами?. на Вашем примере: фрукты/овощи, овощи (картофель, морковь) Картофель (красны, белый), белый (с глазками, и без глазков). Итоговая позиция: овощи, картофель, белый, без глазков - она уникальна (хорошо) А вот сами позиции нет. Пока решаю этот вопрос так: первые два столбика как у вас. Потом сцепляю результат сцепить(овощикартофель) ищу его, потом слеиваю следующий (овощикартофельбелый) и т.д. Есть ли решения без такого изврата:)))

помог, очень сильно) спасибо большое

Доброго время дня, Николай. Есть вопрос по выпадающим спискам: если таблица состоит из 3 и более столбцов применение "именованного" диапазона и формул СМЕЩ и ПОИСКПОЗ довольно таки долго и возможны ошибки. Какие возможны ещё варианты выборки данных? СПАСИБО за ответ

Спасибо! Все понятно)

ОГРОМНОЕ СПАСИБО!

Николай, спасибо! Подскажите есть ли решение, когда умная таблица состоит из нескольких столбцов, а для выпадающего списка необходимо выбрать только один?

1:48 Способ 1. Именованные диапазоны и функция ДВССЫЛ 5:11 Способ 2. Умные таблицы и функция ДВССЫЛ 9:12 Способ 3. Автоматическое связывание функциями СМЕЩ и ПОИСКПОЗ

Вижу Павла- лайк неглядя)

ПРОСТО СУПЕР... ТЫ МАГИСТР

Подписался, учусь. Пожалуйста не прекращайте помогать людям умнеть! Спасибо

Именно то, что искал!!!

Добрый день! Присоединяюсь к вопросу: Как нужно делать способ 3, если "желтое" поле выбора категорий и "зеленое" поле выбора товаров - расположено на одном листе, а таблица с исходными данными - на другом? Подскажите, пожалуйста.

Подписка и спасибо за помощь❤

1/2. «Сказ о том, как решить проблему в способах №.№ 1 и 2, когда значения в столбце Категория (первый выпадающий список) представляют собой набор слов, разделенных пробелом» В ролике этой информации нет, а в комментариях Николай дал решение этой проблемы, но как бы вскользь и между прочем. Мне же эта информация кажется достаточно важной и интересной, чтобы обратить на неё более пристальное внимание. Ну и вот, спешу порадовать таких же жирафов, до которых (как и до меня) не сразу дошло как ЭТО делать. Чтобы сразу, на конкретном образце, посмотреть как эта штука фунциклирует, скачайте файл-пример Николая и на листе «Умные таблицы» проделайте следующие манипуляции: 1. В ячейке H11 замените «Фрукты» на «Мои ну очень очень любимые фрукты ягоды», получив ту самую проблемную категорию товара, состоящую из нескольких слов и разделенную пробелами (пока только ими). Название столбца «Фрукты» в ячейке A3 не трогаем, так как оно нигде не используется и ни на что не влияет, но нам нужно изменить название данной умной таблицы, записав его сплошняком без пробелов или через нижнее подчеркивание: «МоиНуОченьОченьЛюбимыеФруктыЯгоды». или «Мои_ну_очень_очень_любимые_фрукты_ягоды». Первый вариант является более универсальным, поэтому настоятельно рекомендую использовать именно его. В чем заключается универсальность расскажу чуть ниже. Наконец, на последнем шаге выделяем ячейку K5, в которой расположен связанный выпадающий список, вызываем окно «Проверка вводимых значений» и в поле «Источник» вместо =ДВССЫЛ($H$5) пишем =ДВССЫЛ(ПОДСТАВИТЬ($H$5;" ";"")). Пустота в третьем аргументе функции ПОДСТАВИТЬ как раз и убирает все пробелы из ячейки с нашими любимыми фруктами. И даже если вы случайно поставили лишний пробел в начале ячейки, в конце ячейки или поставили два пробела между словами, то всё равно всё будет работать корректно. Усё! Можно пользовать. Когда знаешь - всё просто! (Вроде кто-то из великих любит так приговаривать))) Да, пока далеко не ушли от этой темы, надо бы еще раз подчеркнуть, что ссылаться на ячейку H5 целесообразней все-таки не в виде абсолютной ссылки $H$5, а в виде $H5. В этом случае связанный выпадающий список будет без проблем привычным нам образом протягиваться вниз по столбцу. 2. Теперь самую малость, буквально капелюшечку, усложним формат написания категории товара и отразим его в ячейке H11 следующим образом: «Мои ну, очень-очень любимые фрукты/ягоды». Кроме пробела у нас появились дополнительные три символа, которые нужно зачистить: запятая, тире и слэш. Название умной таблицы остается прежним, как в предыдущем пункте, а вот метод зачистки будет чуток усложнен. необходимо использовать несколько функций ПОДСТАВИТЬ, вложенных друг в друга: =ДВССЫЛ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ(ПОДСТАВИТЬ($H5;"/";"");"-";"");",";"");" ";"")). Таким вот нехитрым способом вы можете избавиться от любых неуставных символов в ваших категориях, при этом последовательность замен значения не имеет. Предполагаю, что поиск и удаление символов звездочка (*), вопросительный знак (?) (если, конечно, функция ПОДСТАВИТЬ вообще допускает использование подстановочных знаков), а также кавычек и других спец. символов в диалоговом окне «Проверка вводимых значений» осуществляется по тем же правилам, как если бы мы это делали просто в обычной ячейке на листе. Проверять как-то лениво, так что сами, сами. 3. Если же вы не воспользовались моим мудрым советом и записали имя таблицы через нижнее подчеркивание, то я вам не завидую. Возможные варианты развития событий: Вариант 1. Поскольку между словами «ну» и «очень» стоит запятая и пробел, то можно написать имя таблицы через два нижних подчеркивания: «Мои_ну__очень_очень_любимые_фрукты_ягоды». В этом случае мы просто без дополнительных премудростей прописываем нижнее подчеркивание в третьем аргументе функции ПОДСТАВИТЬ. ...

Спасибо Николай. Очень классно дается материал. И практично. Единственное но. Третий вариант сложный для работы, т.к. при добавлении новых позиций каждый раз придется пересортировывать таблицу

Волшебно

Это просто супер!

Добрый день! Подскажите пжл можно ли добавлять Категории/Товары (как во 2ом примере с умными табл) используя 3ий вариант ?

Большое спасибо!

Спасибо за видео

Очень хорошо!

очень полезные уроки. Спасибо Вам большое. Пожалуйста подскажите как сделать так, что бы,попробую объяснить) допустим, у меня два выпадающих списка и мне надо,что бы выбирая в них обоих данные в итоге, в третье ячейке, по этим данным был результат в соответстви с данными из обоих выпадающих списков???пожалуйста помогите) ЗАРАНЕЕ СПАСИБО! например: 2 столбца и 3 строки: в первой строке - предмет, во второй - ученик, в третьей- оценка; в первом столбце соответственно все эти три строки, во втором столбце раскрывающиеся списки с наименованиями предметов напротив предметов, напротив графы ученик соответственно раскрывающийся список с именами учеников, и оценка это как раз искомое значение. Как его найти, допустим,если я выберу предмет математика и ученика Иванова, в графе оценка должна появиться соответствующая оценка из исходной таблицы, какую формулу надо использовать, что бы оценка автоматически подставлялась в соответствии с двойной выборкой? )))

Супер наконец-то понял как это работает

Очень круто, большое спасибо. Но как формулу в лезёной ячейке продублировать на весь столбец, когдя тяну просто так то она привязывается только к первой категории а не соседней. С ув. Евгений

Спасибо огромное!

Аааа! Обыскался! Спасибо!

Скажите пожалуйста, как в таблице, отправленный с 1с в ехсел по реализации, можно посчитать сколько торговых точек сработали по наименованиям определенного товара?

Приветствую, Николай! Хочу поднять правую руку и спросить следующее: "Данный вопрос касается второго способа. Когда вы приводите в пример, категории товаров, то обязательно чтобы между ними был зазор в один столбец"?

Добрый день) Спасибо за видео. Подскажите, а есть возможность сделать вывод списка на основе двух параметров ( а не на одном, как показано видео. Например, есть группы "Фрукты" и "Овощи" . в "Фруктах" в ней категории "цитрусы" и "яблоки, груши". А в "цитрусы" список всех кто подходит (апельсины, мандарины и т.д.)

Николай а как-то возможно посчитать значение в зависимости от имени выпадающего списка? Т.е. есть выпадающий список имен, и есть отд столбец где значения должны пересчитываться каждый раз когда я выбираю какое то имя из списка. Спасибо

Спасибо, все понятно. Но есть вопрос. У нас в основной таблице поменялось наименование ячейки. Как сделать так, чтобы она изменилась и в других ячейках когда мы её выбирали из выпадающего списка?

Здравствуйте Николай. Пожалуйста подскажите , почему при создании выпадающего списка, в окне "Проверка вводимых значений ", в окне, где я должен выбрать "Источник", не могу перейти на другой лист. Когда ячейка для создания выпадающего списка и данные находятся на одном листе, создать выпадающий список получается , когда же пытаюсь брать данные с другого листа программа не пускает на другой лист?

Спасибо, Николай. От Вас много пользы. Не получается растянуть эти столбцы вниз (3 вариант). Зеленый столбец ссылается на верхнюю ячейку желтого столбца. Хотелось бы иеивидуальную связь ячеек в каждой строке. Скажите, пожплуйста, это возможно?

Сначала лайк потом просмотр.

Вообще крутяк!!!

Спасибо!

Здравствуйте Николай! Подписавшись на Вас, не пожалела, одним словом волшебник. Вопрос такого характера, как сделать связанный выпадающий список с автоматическим заполнением других ячеек. Например: есть справочник с названием тр/средств, есть справочник летняя и зимняя норма списания, есть справочник значений к летней и зимней норме для каждого авто, как сделать и связать выпадающие списки норм списания к авто, и при выборе летней или зимней нормы автоматически вставлялось значение к определенному авто.Спасибо

Николай, для третьего способа я не нашел в закладке формулы "диспечтер Имен". Пользуюсь офисом на МАС-OS. Не подскажите??

Николай, а про гугл таблицы такое можете сделать? Мне в облаке надо, многопользовательское. Я в обычном экселе самостоятельно реализовал что-то похожее на одно из ваших решений (там это просто, на мой взгляд), но в гугле подобный функционал сделан как-то тупее. Но вдруг я чего-то упустил.

Доброго времени суток, существуют ли способы примнения способа к динамической таблице?

Подскажите, пожалуйста, можно ли сделать так, чтобы из такого выпадающего списка выбирать не одну, а несколько строк (например, в категории Овощи надо в Товарах из всех указать только огурец, лук и перец, чтобы они втроем были видны в Товарах).

Николай, скажите пожалуйста как решить такую задачу: Мне нужно чтобы при выборе данных из списка (срок изготовления на выбор) подставлялась скидка (наценка) от общей суммы из ячейки. Есть ли видео как это можно сделать?

Добрый день, спасибо все подробно. Но у меня вопрос решение которого не нашел. Я научился делать зависимый выпадающий список в одной ячейке. Но мне нужно "протянуть" этот список вниз. Как это сделать? Категория - Товар Категория - Товар Категория - Товар

Николай, как сделать что бы в зелёной ячейке появлялся не выпадающий список, а полный список, например в жёлтом окне выбираем "овощи" и на зелёном поле видим все товары расписанные на 8 строк получается, 8 зелёных строк с наименованием. Спасибо за ответ.

Николай, у меня задача сделать связный выпадающий список, но из двух не зависимых списков. Т.е. к примеру если в овощах выбираешь КАРТОФЕЛЬ, то во фруктах должны выпадать только ЛИМОН и ДЫНЯ, а если выбираешь ПОМИДОР, то СЛИВА и КИВИ. Такое можно реализовать в MS Excel?

Здравствуйте. Можно ли в третьем способе убрать повторяющиеся значения из выпадающего списка?

Николай спасибо, понятное видео. У меня все получилось. Но есть вопрос. Как сделать третий способ если данные расположены на другом листе, не получается. Подскажите.

Отличный урок, спасибо, очень помогло. Но есть проблема, что при смене источника ( красной ячейки) значения в "зеленой ячейке" остаются от предыдущего источника. Существует возможность, чтобы при смене красной ячейки, позиция в зеленой вставала, например, на первую строчку списка нового источника?

У меня вопрос как прикрепить за каждой выбранной сточкой из списка цену товара или услуги и отобразить её в столбце на против в отдельной итоговой таблице . Заранее спасибо

Как всегда все доходчиво) Николай, а работает ли все это внутри умной таблицы? У меня вот такая ситуация таблицы с нужными списками на вспомогательном листе (всегда делаю его)...на нем если делать связанные списки все работает, НО как только пытаюсь сделать все тоже самое на другом листе внутри умной таблицы...ничего не работает....Есть столбец например Поставщик и вот на основе его выбора надо выбирать Товар и потом ВПРом цена подтягивается

Здравствуйте! А возможно применить автозаполнение к ячейке с выпадающим списком? Например, у меня в выпадающем списке 500 фамилий и если набирать начало фамилии, происходило бы автозаполнение

Спасибо)

Спасибо огромное, добрый человек. Сколько же часов я потратил на поиски этой функции

Большое спасибо за обучение! Подскажите пожалуйста, а как скопировать это на нижние ячейки? Если я потягиваю вниз, динамический выпадающий список уже не работает (использую 3й способ с формулами)

Добрый день. При добавлении какого-либо товара, получается, нужно " засовывать " в середину нужного списка, чтобы в именной формуле не менять диапазоны. А если новая категория, диапазоны в формуле надо все-таки подвинуть согласно новым границам, верно?

Здравствуйте Николай. Скажите пожалуйста как сделать так чтобы после выбора Категории - Товар в итоговую таблицу попадали только выбранные товары. Понятно что нужно использовать функцию ВПР или Поискпоз. Загвоздка в том, что данные по товаром находятся в разных таблицах. Как сделать привязку Категории к конкретной таблице, находятся которые на разных листах. Очень нужно.

Well done