Как сделать связанные выпадающие списки в Excel одной формулой

Еще один способ создать связанные выпадающие списки в Excel на основе неотсортированного списка с помощью функций СМЕЩ и ПОИСКПОЗ.
Скачать пример www.planetaexcel.ru/technique...
Мои книги planetaexcel.ru/books/
Мои онлайн-курсы www.planetaexcel.ru/learning/
Моя надстройка PLEX (+150 новых функций для вашего Excel) planetaexcel.ru/plex/
Twitter / planeta_excel
Facebook / planetaexcel
Instagram / planetaexcel

[@zaolog]

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

[@tashakim17kim63]

Какое-то волшебство...Хогвардс отдыхает

[@Sovabianka]

Космос!!! Каждый ролик - жемчужина. Спасибо, Николай, за Ваш труд и за наше просвещение! )

[@user-ch3bo9hn5k]

Огромное спасибо, что делитесь знаниями!

[@user-uj9hi7qr5d]

Спасибо Вам что бесплатно даёте знания это дорогого стоит!

[@valeryv9146]

Очередной раскрытый фокус :-) от Николая. Николай, Спасибо!!!

[@user-yf5sb6lj6h]

Николай подскажите у меня старая версия . нажала на enter. выдает знач. Что тогда нажимать?

[@Archibald1995]

Вы лучший ! Я всегда знал что это можно сделать , но никогда не понимал как и не мог найти . Спасибо вам !

[@user-cx7ly7nm9j]

Как приятно Вас слушать. Спасибо Вам.

[@Stas_Gutsal]

спасибо за видео Николай, очень крутой приём показали, жду с нетерпением новые видео :)

[@user-cn3hr6ee7b]

Просто супер! Спасибо вам огромное, какой же эксель крутой инструмент а с вами он становится только полезнее ❤

[@MaestroWookie]

Николай, огромное вам спасибо! Делал многоуровневый план счетов для управленческого учета, как раз такой метод и пригодился!)

[@ANDREY_Korobov]

Спасибо! Четко, коротко, всё по делу. Впрочем, как и всегда.

[@Denisgor_]

Ну это же охринеть, как круто! 🔥🔥🔥

[@forevermay8741]

Спасибо вам большое! Наконец-то кто-то понятно и доступно объяснил. Решила весомый рабочий вопрос

[@Serj8888]

Всегда рад, когда появляется новое видео )

[@UC0LMA1CQ6b3s6nrBlO4]

Спасибо большое за контент! Я Ваш фанат! Творчиских успехов!

[@Fantv880]

Спасибо за объяснение функции "СМЕЩ"!

[@user-cz1qn5lp9i]

ОГОНЬ !!! то что надо. Благодарочка АВтору !

[@user-wn7mv5gp7r]

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

[@yurd563]

Большое спасибо за видеоурок!

[@nikodimlad]

Супер! Просто, понятно и быстро

[@user_Xo4y_Ha_PycckoM]

спасибо, очень доступно излагаете материал

[@ilnur17021992]

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

[@rellanely5493]

Спасибо за уроки по Экселю

[@jollyjuln1054]

Воистину маг и волшебник!

[@robrob9260]

Вау!!!! Нет слов, как круто!! Спасибо!!

[@user-yf2hv6vm2b]

Супер. огромное ВАм СПАСИБО, за предоставленный контент

[@Unforgiven92]

Супер! Я вам в личку на планет-эксел об этом как раз просил урок сделать. Спасибо.

[@user-ub4iz3nw8n]

Сначало лайк а потом просмотр. Спасибо за полезное видео.

[@dmitriypapushin6119]

Просто супер, как раз искал как мне сделать зависимость в таблице для выбора соотношения процессора и оперативной памяти.

[@zabrus24]

Вот опять как будто мысли прочитали)) Очень полезный инструмент.

[@maxim1976yn]

Супер, как и всегда.

[@MDunaev]

Спасибо-преспасибо, дрогой Николай!

[@antonkharitonov9007]

Спасибо большое за видео)

[@dikii3018]

Быстро, понятно, сэкономил пол часа размышлений. Респект.

[@user-yo5wj7lx4u]

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

[@asterklaster7575]

круто. это именно то что я искал. спасибо.

[@juliashu2490]

Гениально! Я уже знаю, где применить!!!

[@michaelpavlov9493]

класс, то что доктор прописал. спасибо!

[@Rice8520]

В апреле как раз делал такие списки, но делал это через CHOOSE. :) Так как список разделов был очень длинный и не хотелось разбивать его по проектам. Поэтому это был сплошной длинный столбец. Но он, к сожалению, был не динамический, поэтому обновление планировалось макросом. Но до этого дело не дошло, файл оказался слишком перегруженным, так как за месяц собиралось очень много информации, а надо было собрать в нём информацию за год. Поэтому файл благополучно ушёл в архив. Разве что когда-нибудь дойдут руки переделать его в более гибкий вариант по вашему сценарию. :) Как всегда, спасибо за урок!

[@Raslambekov]

Все работает спасибо очень круто

[@valknut85]

Николай, здравствуйте. Я с огромным удовольствием смотрю ваши видеоматериалы. Когда заходишь на сайт душа радуется от нормальной человеческой организацииэ. Вот уроки вот реклама, заходи и смотри. И тем сильнее диссонанс с тем, что происходит у Вас на форуме. Адовище из 90х. У вас флеймят сами модераторы. Достаточно ткнуть на профиль, перейти в сообщения и две трети из них это едкие замечания в третьем лице об авторе темы. В 2021ом Встречая сайт и контент такого качества ожидаешь увидеть форум уровня Stack Overflow, Autodesk, на худой конец Reddit. На обращение в духе "вспылил был не прав, удалите флейм из темы" следуют ответы дословно: "Почистите свой срач в трех своих темах - модераторы после этого почистят тему от лишних разговоров."Вот ещё ряд примеров. "Думайте... А надумаете создавать тему, не прикрепляйте рабочий файл - потрудитесь подготовить НЕБОЛЬШОЙ ПРИМЕР""Михаил Булгаков, посмотрите на свое "произведение". Зачем так копировать? Зачем здесь вообще цитирование?"Исправьте сообщение и думайте перед тем, как нажать кнопку цитирования""Большое спасибо сердобольным помощникам за то, что добавили работы по чистке темы от тупого копирования..." Серьёзно? Что за нагловато-менторский тон? Мягко говоря, такое поведение в современном обществе неприемлемо. Николай, я тоже индивидуальный предпрениматель, у меня тоже есть клиенты и сотрудники. Да не в таких масштабах. Но я всегда предполагал, что кредо любой просветительской деятельности - быть лучшим во всем. Это я молчу о том, что форум является частью поддержки которая нужна для того чтобы продаваемый продукт продавался хорошо. Чтобы можно было сказать "а решение своего вопроса вы можете спросить на нашем форуме" а про себя добавить "но вы там .. чего найдете потому что вас захейтят". Ах да. вишенка на торте от Вашего модератора"Тема может быть полезна не только Вам. Рассказать о решении не хотите?"Не захочет. Никто не захочет. Очень надеюсь на ваше внимание к данному вопросу.

[@raisapogrebova2032]

красавчик! Огромное спасибо!

[@iafj11]

Спасибо!!! Я как раз думал как решить похожую задачу.

[@user-kk4vg7jo9d]

Николай здравствуйте! С гуугл таблицами работаете? Если работаете сделайте пожалуйста пару видео уроков.

[@marialeonova1802]

Супер! Спасибо!

[@L3nnyRU]

Автору респект

[@innasabirova2539]

большое вам спасибо!

[@alonaisrael]

Спасибо 🙏🏻

[@sergeypautov8086]

О, да. Великолепно!

[@dmitriyvolkov9116]

Крутяк. Спасибо большое 🤝

[@diavolo-o]

просто нет слов: Вау!)

[@SHKIPPER989]

Что сказать, супер!)

[@sergeyanosov1192]

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

[@artynd]

Но если в СЧЁТЗ высота выбранного диапазона стоит 10, функция всё равно не будет считать больше 10 строк. Я бы присвоил каждому диапазону имя и в функции бы ссылался на именованный диапазон CЧЁТЗ(ДВССЫЛ(А2)), но тогда вся эта формула не имеет большого смысла, ибо можно просто ссылаться на именованный диапазон в проверке данных.

[@focusgroup_ss]

Добрый день! помогите пжл) У меня все получилось, но вот протянуть формулу вниз никак не выходит, вставляю формулу в проверку данных и все равно только где задавала работает, может кто знает что делать?

[@almanu5408]

Спасибо!

[@user-yt1qj8tm7w]

Супер контент

[@user-xv9ve8ey1f]

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

[@lega1985]

Добрый день! Большое Вам спасибо.за это видео. Подскажите, если видео где в третьем столбце отображалось б значение в зависимости от значений в 1м и 2м столбце. Спасибо.

[@user-be3ps7xc6b]

Работает!

[@user-wh3io5ry5w]

красавчик

[@user-nn2yr1oy9s]

Гений!

[@user-eq6lu8ez2r]

Познавательно

[@Udaw05]

Какой там Хогвардс , берите выше -андронный коллайдер !!! Спасибо большое , Николай !

[@user-lw8gc3en5m]

Супер! Николай, подскажите, как сделать так же, но со ссылкой на другой лист?

[@axeaxe7779]

Огонь

[@user-yn8ze6fw7k]

Супер

[@spongebobtt8864]

Здравствуйте, Николай! Спасибо за очень познавательные видео. Попробовал сделать чуть более сложный выбор для зависимого списка, но формула в ячейке работает, выбранные данные показывает, а в проверку данных в строку "Источник" просто не вставляется. В чем может быть причина? Помогите разобраться, пожалуйста. Формула выглядит так: =СМЕЩ(ДВССЫЛ("ДопН1[[#Заголовки];[Тип1]]");1;ЕСЛИОШИБКА(ПОИСКПОЗ(C13;ДопН1_Заголовок;0);ПОИСКПОЗ(B13;ДопН1_Заголовок;0))-1;СЧЁТЗ(СМЕЩ(ДВССЫЛ("ДопН1[[#Заголовки];[Тип1]]");1;ЕСЛИОШИБКА(ПОИСКПОЗ(C13;ДопН1_Заголовок;0);ПОИСКПОЗ(B13;ДопН1_Заголовок;0))-1;10;1));1). Смысл в ЕСЛИОШИБКА: есть 2 ячейки, если значение из второй ячейки не найдено в заголовке таблицы, то выбирается диапазон для столбца, название которого совпадает со значением в первой ячейке. Все необходимые данные в таблице есть. Слишком длинная запись формулы? Если сделать дополнительную ячейку с номерами полученных заголовков из ЕОШИБКА(...) и вставить ссылку на неё, то формула вставляется без проблем

[@sergeiserd730]

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

[@smr801]

спасибо

[@vanhelsing6501]

класс

[@antonkharitonov9007]

Даёшь связь выпадающим спискам!

[@masterd6125]

Добрый день! Может быть подскажите, если в списках есть/будут повторы, то искать (поискпоз выдаст) только первое/верхнее значение и если да, то как лучше в этом случае поступать? Спасибо.

[@olegn.4237]

Отличное решение, только следует понимать, что СЧЁТЗ() не подсчитает количество строк больше 10, поэтому следует во вложенную в СЧЁТЗ формулу СМЕЩ закладывать заведомо большое количество строк, которое бы превышало возможный в будущем размер справочника.

[@MaxBulbov]

Николай как у вас заказать разработку и сопровождение нашего проекта в части Эксель?

[@VoV4eK88]

а на 7:23 ведь то число 10 (смещение вниз с запасом), которое писали от балды, так и остаётся. А если у нас список выйдет за пределы 10, то разве основная формула расширит диапазон?

[@user-xyuser99]

Николай, вопрос что делать если первый список/справочник на вашем примере - овощи фрукты зелень, содержит больше чем одно слово.

[@BroChe23]

А как сделать если всплывающий список должен сортировать по двум критериям, например: 1 столбец «название операции» 2 столбец «стадия» 3 столбец «номер помещения» Нужно чтобы в другой вкладке при выборе помещения в одном столбце в следующем отображались стадии которые привязаны к этим помещениям и в еще следующем столбце всплывали названия операций по этим помещениям исходя из стадии?

[@vladislavk9186]

Добрый день Николай. Спасибо за Ваши видео. Возник вопрос, ответ на который я не смог найти в интернете. Написал Вам на почту, но ответ не получил. Возможно ушло в спам, т.к. я там прикреплял файл. При переводе таблиц из сметных программ в Excel выгружаются столбцы с ячейками, имеющими горизонтальную черту. Суть задачи в том, что нужно сослаться формулой на эти ячейки, чтобы формула забирала значение либо из числителя, либо из знаменателя (если он есть). Если это невозможно, как вариант думал вытащить эти значения в соседние столбцы (в один столбец числа из числителя, в другой из знаменателя) и уже потом на них ссылаться формулой, но не могу найти решение, как это сделать. Спасибо за помощь. Извиняюсь, что вопрос не по теме видео.

[@kolizir]

Топ 👍

[@user-yn9dl6tv5u]

Супер гуд

[@danilyukmv]

Добрый день! Подскажите, пожалуйста, есть ли возможность сделать так, чтобы при выборе главного условия , все остальные условия ( в случае несовпадения) исчезали из ячеек. Моя проблема в том , что при изменении изначального ( главного) критерия , все остальные критерии оставляют за собой те же названия , что и при другом главном критерии, но при этом сами списки высвечиваются правильно. Возможно ли сделать так , чтобы это все обновлялось автоматически и/или несовпадающие критерии просто удалялись?

[@Anclrei]

На этом же примере можно было продемонстрировать использование лямбда функции. Красивше получилось бы.

[@user-hm3qs1mn4k]

В хозяйстве пригодится

[@alfasementeiralimitadaango1673]

Добрый день. А как шагнуть на еще один уровень и скажем выбрать сорт петрушки?

[@bimbom1982]

А подскажите пожалуйста, как сделать выпадающий список, если исходными данными для него является таблица примерно такого вида: фрукты > яблоки > красные фрукты > яблоки > зеленые овощи > перцы > сладкие овощи > перцы > острые Просто у меня в таком виде выпадающий список содержит несколько значений "фрукты", несколько "овощи" и т.д. Хотелось бы избавиться от дубликатов.

[@nasharussian4003]

👍👍👍👍👍

[@user-wm4qm7gx7w]

Офигенно. У меня офис 2013. Подумал, что удобно будет и мне адаптировать такое под себя. Но вот напоролся на нежданчик. Во строке выбора диапазона для списка моя матрёшка даёт ошибку (Вам не нужна формула?), хотя в ячейке рядом, через ct+sh+en, вроди бы, работает - выдаёт первый пункт из нужного списка и при замене инфы в первом выпадающем списке, моя динамическая ячейка выдаёт новую, коректную инфу. Все отличия от примера в уроке: 1. Вся нужная мне инфа висит в отдельном листе, в одной умной таблице. 2. Добавил новый столбец с инфой, по которой мне удобно будет групировать данные. 3. Сгрупировал их в отдельную умную таблицу с помощью другого урока про динамические масивы (индекс-наименьший-строка, добавив ещё левый символ, но это не играет роли, так как данные мне выдаёт верно, а функция из выпадающего списка (на другом листе) берет нанные уже из этой умной таблицы) 4. Ну и ещё в "поискпоз" первым аргументом у меня стоит "левсимв", что тоже никак влиять не должно. Есть подозрение, что причина в информации, желательной к выпаданию во втором списке, и.к. она состоит иногда из цыфр, иногда из букв, но вот если это все провернуть на листе-источнике, то все работает как хотелось бы на остальных листах. Но все равно пробовал это все добро перевести в текстовый с помощью ctrl+1 и с помощью апострофа. Результату било 0, только подпортил таблички на остальных листах, которые так же берут инфу с многострадального источника. Если кто-то понял весь этот бред выше, и есть идеи, спасайте. 🥴

[@user-wm4qm7gx7w]

Походу, проблема заключается в ссылках на ячейки из другого листа в формате: Источник[[#Заголовки];[2]:[E]] В динамической ячейке оно ещё кое как обрабатывается, а как источник для выпадающего списка не катит.

[@user-wm4qm7gx7w]

Так и было. Всё дело в формате ссылок. Перевёл все в формат "$А$1:$С$1" и все заработало. Ну и ещё убрал умную таблицу с этих данных, смысла в ней нету. Но! Такой забавный момент. В моем случае, до конца второго выпадающего списка все равно остаются пустые ячейки, т.к. у меня данные для второго списка берутся из динамического диапазона, в котором на определённое (с запасом) количество ячеек прописана формула, при отсутствие которой диапазон не будет пополнят я новыми данными, а только обновится в алфавитном порядке. Возможно ещё стоит попробовать убрать их из списка с помощью ЕСЛИ, указав один из аргументов "" Но так как я очень доволен, что все это работает, хоть и колхозно, то на пока что сойдёт. СПАСИБО ЗА ПОДСКАЗКИ. ВАШИ ВИДЕО ОЧЕНЬ ХОРОШИ!

[@nazardmytryshyn5477]

👍👍👍👍

[@user-pi9gi4ri1t]

Колдунство!

[@user-hs5pd6yp2i]

Как сделать выпадающий список по следующим пар-рам: например я делаю отчет по расходам компании и одна из главных статей «зарплата». Как сделать так , чтобы когда в отчете я нажимал на графу «зарплата» у меня раскрывался список с именами работников и суммы их з/п. И также нажимая на «стрелочку» список скрывался и мы можем видеть изначальную главную таблицу, где после зарплаты идут другие графы - амортизация, страховка, транспорт.расходы и так далее.

[@user-om3xr5io5s]

Будет ли это работать если овощи, фрукты, зелень, буду записаны не в строчку, а в столбец? То есть у меня уже есть база данных *Категория* заполненная овощи, фрукты, зелень и рядом будет уже заполненный столбец с товаром, как это было в уроке *Связанные выпадающие списки в Excel* способ три. Для меня это база данных из которой я потом хочу вытягивать информацию в такую же таблицу *Категория*, *Товары*, но допустим на другом листе Excel. То есть я так же создаю Категория и Товар. В категории выбираю Фрукты к примеру и дальше подтягивается список. У меня это получилось реализовать только на одну строку, все ниже следующие строки так же почему то ссылаются на первую строку.

[@turshak_tv]

Если Вдруг категория перемещается всегда надо будет пересортировать данный

[@maxim1976yn]

Знаю, где применить, или как минимум, предложить такое.

[@sergesb2651]

Спасибо, ваши видео всегда интересны. Собираетесь ли вы создать урок по "слиянию" (Word+Excel) при печати? Причём, если с печатью писем или отдельных однотипных документов всё понятно, а, вот, про печать "КАТАЛОГОВ" - вообще - никаких данных, даже в help'e Word'a! Я имею в виду: как напечатать КАТАЛОГ (из "Ворда"), где на каждой странице печатается какая-то информация в заданном формате, типа, "Название", "Краткое описание", "Полное описание", "Бла-бла-бла", "и т.д. и т. п." + . Тексты для разных полей берутся из таблицы Excel, а откуда и как брать картинки - вот это и есть основной мой вопрос. Если не трудно, сделайте такой урок, пожалуйста.

[@sergesb2651]

А вот и "дополнительный" вопрос: "Как снять ограничение на количество знаков в тексте, который при слиянии берётся из ячейки Excel'a"? Почему-то в печатаемый текст вставляется не более 255 символов, остальное "режется". Есть ли какой-то простой способ снять ограничение на длину текста или придётся дробить строку и затем склеивать её из нескольких строк?

[@user-vp6bh3ff6z]

​@@sergesb2651Есть способ без дробления, просто в первую строку данных нужно вставить более 255 символов, и данная проблема решиться.

[@dilyasidicova2306]

Николай, здравствуйте. Как можно с вами связаться?

[@user-ms3lf2pv9i]

Автор, спасибо! Вопрос: Как сделать так, чтобы в уже заполненной таблице "Категория - Товар" автоматически обновить данные согласно изменениям в "Справочнике". Например, был в "Справочнике" в колонке "Овощи" элемент "Агурец", потом заполнили полностью таблицу "Категория-Товар" данными из Справочника. Потом увидели, что правильно "Огурец", меняем в справочнике название "Агурец" на "Огурец", а данные в таблице Категория-Товар остаются прежними с "Агурцом". Как выйти из такой ситуации? Спасибо. P.s. Данные из Списка передаются в рабочую ячейку по значению, а не по ссылке? (С учетом, что дополнительно подключен через VBA мультивыбор значений ..."Огурец, помидор, кабачок...")

[@JuliaVenslavovich]

подскажите пож, а как потом скопировать эту формулу вниз в столбце товар?

[@olyasoldatenko7269]

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

[@user-pz5cy8dx9b]

Отличный инструмент, НО у меня не получилось. Тогда полностью скопировал пример с видео и все равно в столбике ТОВАР получил ошибку. В чем может быть причина? Формат ячеек имеет значение?

[@u_ri4]

какая ошибка? #ЗНАЧ?