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

[@Sovabianka]

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

[@zaolog]

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

[@Archibald1995]

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

[@valeryv9146]

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

[@АдияДжумалиева]

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

[@ТатьянаР-л1г]

Николай, вы лучший в своем деле! Спасибо огромное!!!

[@Stas_Gutsal]

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

[@ОксанаЗюзина-ч5ц]

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

[@anastasiakempf]

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

[@forevermay8741]

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

[@MaestroWookie]

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

[@jollyjuln1054]

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

[@tashakim17kim63]

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

[@Denisgor_]

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

[@Syrok13]

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

[@Fireonair]

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

[@ANDREY_Korobov]

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

[@ТатьянаПухтина-ф3э]

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

[@Jangotrain777]

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

[@АнзорБуздов-ж9й]

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

[@РоманАндрианов-з6ю]

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

[@Serj8888]

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

[@sergeyanosov1192]

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

[@zabrus24]

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

[@Алина-ц5ч2м]

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

[@dikii3018]

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

[@Rice0987]

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

[@valknut85]

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

[@UC0LMA1CQ6b3s6nrBlO4]

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

[@MDunaev]

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

[@Fantv880]

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

[@user_Xo4y_Ha_PycckoM]

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

[@nikodimlad]

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

[@juliashu2490]

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

[@robrob9260]

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

[@rellanely5493]

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

[@asterklaster7575]

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

[@СергейПопрожук-ц9ж]

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

[@Баскака]

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

[@yurd563]

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

[@antonkharitonov9007]

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

[@michaelpavlov9493]

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

[@artynd]

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

[@sabina_jumazhanova]

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

[@iafj11]

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

[@mguptte]

Каждый бы так объяснял. Спасибо

[@ilnur17021992]

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

[@alexandershavleyko1313]

Требуем продолжения банкета!

[@maxim1976yn]

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

[@VoV4eK88]

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

[@L3nnyRU]

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

[@ВадимДмитренко-л3з]

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

[@ВадимДмитренко-л3з]

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

[@ВадимДмитренко-л3з]

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

[@РоманКазаков-ю4ф]

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

[@АлександрСветлый-м5д]

Как сделать так, чтобы у меня раскрывался список из конкретной ячейки? Пример есть таблица за доходами. В одной из строк доход , полученный за продажу фруктов - рядом записан Тотал. Нужно сделать так , чтобы из этой ячейки раскрывался список из чего состоит этот Тотал- яблоки = 5 рублей ; груши = 7 рублей и так далее. Суть - таблица из множества значений , где по каждой записан финальный Тотал. Но каждое значение состоит из других значений , которые тоже надо отобразить.

[@raisapogrebova2032]

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

[@Udaw05]

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

[@Raslambekov]

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

[@oleg_net]

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

[@Дмитрий-л9з1г]

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

[@diavolo-o]

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

[@АлександрСветлый-м5д]

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

[@lega1985]

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

[@sergeiserd730]

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

[@alonaisrael]

Спасибо 🙏🏻

[@СергейТретьяченко-ш1г]

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

[@innasabirova2539]

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

[@sergeypautov8086]

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

[@dmitriyvolkov9116]

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

[@marialeonova1802]

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

[@SHKIPPER989]

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

[@Anclrei]

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

[@danilyukmv]

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

[@dilyasidicova2306]

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

[@СтаниславШайсултанов-ы3ж]

Вопрос: после того, как в функцию СЧЕТЗ в качестве аргумента была вставлена скопированная формула с функцией СМЕЩ, почему не заменили цифру 10? Или тут ошибок в дальнейшем не будет при изменении диапазонов и цифра 10 не на что не влияет и не будет влиять?

[@BlackSniperVideo]

Думаю, это повод для ещё одного видео :)

[@alfasementeiralimitadaango1673]

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

[@mwatempest2223]

А можно ли как-то увеличить шрифт этого выпадающего списка? Либо возможность колёсиком крутить

[@MaxBulbov]

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

[@masterd6125]

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

[@user-xyuser99]

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

[@antonkharitonov9007]

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

[@МаркРайтер-ъ9ъ]

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

[@BroChe23]

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

[@fabrikadinar7592]

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

[@АртемСергеевич-ю1т]

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

[@Lub12358]

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

[@olyasoldatenko7269]

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

[@vladislavk9186]

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

[@user-pz5cy8dx9b]

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

[@u_ri4]

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

[@JuliaVenslavovich]

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

[@turshak_tv]

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

[@АртемГладышев-е8ю]

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

[@maxim1976yn]

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

[@almanu5408]

Спасибо!

[@VoV4eK88]

Скажите, а как сделать сокращение списка исполнителей в "выпадающем списке"?

[@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 ячейки, если значение из второй ячейки не найдено в заголовке таблицы, то выбирается диапазон для столбца, название которого совпадает со значением в первой ячейке. Все необходимые данные в таблице есть. Слишком длинная запись формулы? Если сделать дополнительную ячейку с номерами полученных заголовков из ЕОШИБКА(...) и вставить ссылку на неё, то формула вставляется без проблем

[@axeaxe7779]

Огонь

[@АлександрЕрмолаев-щ6т]

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

[@ДмитрийЛитвин-ю9р]

Работает!

[@ДмитрийГлущенко-г3х]

Гений!

[@alexanderstolbovoy1845]

У полученной формулы есть ряд проблем. 1) Не учтено что категорий может становиться больше. Нет динамического расширения вправо 2) Не учтено что товара может быть больше 10. Нет динамического расширения вниз.

[@МаргаритаБогомолова-к2и]

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

[@mr.jivchik2432]

А можно связать выпадающий список и фильтр по строкам? Вот это было бы огонь) мучился как-то и бросил)

[@planetaexcel]

Наверное, можно, но уж больно узкоспецифическая задача :)

[@mr.jivchik2432]

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