Связанные выпадающие списки: легко и быстро
Вставка
- Опубліковано 28 вер 2024
- Обычно, чтобы создать связанные выпадающие списки (когда содержимое одного списка зависит от того, что выбрано ранее), требуется создать несколько именованных диапазонов, использовать формулы, которые обычному пользователю могут показаться сложными, а потом при добавлении в созданные списки новых данных, следить, чтобы диапазоны "захватили" эти данные.
Это занимает время, приводит к ошибкам и доставляет немало хлопот.
В моем методе используется всего одна простая формула (правда, тоже непривычная обычным пользователям и используется она 2 раза) и форматированная таблица Excel. Зато, вы получите взаимозависимые списки и будете пользоваться ими по принципу "сделал и забыл". Вам не нужно будет переживать о том, что "слетит" диапазон, и не нужно будет переделывать всю архитектуру исходных данных. Просто, один раз сделаете и будете пользоваться.
Присоединяйтесь к нам в социальных сетях и на сайте:
Статьи и примеры файлов Power BI и Excel www.finalytics...
Вконтакте finalytics
Facebook / finalytics
Telegram t.me/finalytic...
Записаться на курсы www.finalytics...
Убил несколько часов чтобы найти что-то толковое! Перепробывал кучу способов и все в пустую.
У Вас все быстро, и понятно.
СПАСИБО!
+botman41 Спасибо за отзыв!
Рад, что смог помочь )
Спасибо, за материал и за подачу.Очень полезно. Сам недавно искал возможность составления таких списков для подготовки и заполнения справочника статей БДР и БДДС. Просто и понятно. Спасибо за ваш труд
Станислав, огромное Вам спасибо! Очень долго не мог найти адекватный метод создания связанных списков! Вы меня выручали!
Где же Вы раньше были когда я создавал свою таблицу :) я уже полгода пользуюсь таблицей с "километровыми" формулами
Спасибо вам огромное!!! Святой вы человек! 😇 Мне надо было сделать выпадающие динамические списки 3х уровней (я не очень продвинутый юзер). Я три дня перебирала все инструкции, смотрела видео, пробовала разные способы и НИЧЕГО не помогало! Потому что никто не сказал, что надо прописать ссылку &$A2&. Вы меня просто спасли! Спасибо вам огромное! 🙏💗
Потрясающе Станислав. Впрочем как всегда. Просто прекрасная информативность.
великолепно.и намного проще,чем другие примеры,даже спустя столько лет.спасибо
Хороший метод. Смущает только наличие лишних пустых строчек в зависимом выпадающем списке. При более муторном подходе (именование диапазонов и применение той же ДВССЫЛ) результат хоть и без "пустых вариантов", но без автоматического расширения таблицы.
Формулу нужно дописать ЕСЛИ пусто, то ничего не показывать...
Действительно интересно.
Я как раз раньше шёл по неправильному пути.
Теперь буду работать ещё быстрее.
жиза , друг))
Спасибо за весьма полезный урок! Щас еще попробую поиграться с формулой ЕСЛИ, чтобы не отображались ненужные пустые ячейки.
Спасибо за видео) Сделали все качественно, местами даже смешно) Продолжайте в том же духе!
Станислав, спасибо. Все понятно разъяснено
Очень полезное, простое решение! Спасибо!
Минусы конкуренты ставят чтоли? Показаный метод единственны, что мне помог. Остальные загючили. Лайк!
Минусы ставят те кто понимает насколько это кривая система обучения и что например данный способ вообще не работает в случае повторяющихся значений в ячейках.
Замечательное видео, хороший пример, но остаются вопросы.
Например, можно убрать выбор _статей_ напротив пустых ячеек _элементов_ через "если":
=ЕСЛИ($G3"";ДВССЫЛ("ИСТОЧНИК["&$G3&"]");"")
Но что делать с количеством _элементов_?
Если в _статье_ из _элементов_ у нас пусть будет 20 заполненных вариантов, в то время как в других _статьях_, где пунктов реальных 2-3 - тоже будут предлагаться 20 вариантов, из которых 17-18 будут пустыми.. как-то не комильфо..
Может я что-то не понимаю и это легко исправить?
Спасибо. Очень полезный урок.
Спасибо всем за комментарии и отзывы!
Надеюсь, вам это все будет полезным )
Станислав, спасибо!
Искал другие решения по связанным выпадающим спискам, Ваше решение самое простое и эффективное!
Захар Ганиев Спасибо )
Роберт Гильмуллин увидел два вопроса:
1. Как сделать 3-х уровневый список
2. Как организовать просмотр данных в динамике
Ответы в том же порядке:
1. Создаете список из 3-х корпусов - без пробелов, начинающихся не с числа. Затем создайте 3 форматированных таблицы и присвойте им имена корпусов. Далее все как в видео, только вначале создаем первый список, где будет выбираться корпус.
2. Данные нужно собирать в одну базу и для просмотра использовать сводную таблицу.
Будем!Спасибо за ориентиры.
добрый день! спасибо за видео, не могли бы вы уточнить как нужно модифицировать формулу =ДВССЫЛ("Источник["&$G3&"]") формулой ЕСЛИ, для того чтобы из выпадающих списков пропали пустые ячейки...
Станислав огромное спасибо.
Спасибо большое. Оказывается сложные вещи делаются так легко :))
Спасибо за видео, очень полезное! Только единственно вопрос, как сделать так, чтобы не отображались пустые строки в выпадающем меню, а остались только с информацией?
Отличная идея, грамотный подход.
форматируемая таблица в Книге, есть нечто иное как объект в VBA, если я не ошибаюсь конечно. Для очень слабых пользователей, было бы не лишним прикрепить маленький файл-примерчик. Большое спасибо за урок!
спасибо большое. очень полезная информация.
Благодарю за видео! Очень полезно! Подскажите, а возможно ли сделать по такому принципу 3х уровневые списки ?
Количество уровней не важно.
Действительно легко и быстро! )
Именно Ваш ролик взяла за основу для своей работы. Спасибо!
Подскажите пжл, был ли у Вас опыт совмещения выпадающих списков и функции "Если" ?
Так вышло что мне необходимо составить выпадающий список 4х уровней, но на 3м ур-не не все значения уникальны.
Логически эта задача должна решаться с помощью ф-ции "Если", но либо практики не хватает либо эти 2е ф-ции не совместимы.
Заранее спасибо за ответ !
Самый лучший способ. Спасибо.
Я в шоке..! Как я далек от возможности Excel. Мне поставили задачу свести все в один файл...доходы расходы, продажи. Теперь благодаря Вашему видео- это будет сделать интересно! Спасибо огромное за урок! Все это можно выполнить в Excel2007...?
Да, этот инструмент работает в Excel 2007.
Только нужно внимательно отнестись к ссылкам на форматированную таблицу, они могут в некоторых случаях отличаться от 2010.
Все остальное также.
Человеческое спасибо
Спасибо большое! Подписался и Лайк!
Станислав, огромное спасибо за урок, просто и понятно. Но как всегда бывает, "а человеку все мало")))) У меня вопрос. А если мне нужна трехуровневая связь? Например, если бы в вашем списке было бы еще дальнейшее расширение как то "красное дерево": "дуб", "сосна" и тд и ткани: "шелк", "лен" и тп и тп. Возможно ли составить такие списки? И как? Ну очень нужно :) Спасибо большое заранее
3-х уровневая связь... я бы присвоил каждой таблице тогда имя - это был бы первый уровень...
Не проверял - говорю общие свои мысли...
Как основа понятное видео. Спасибо. Остались вопросы. Непонятно как в распространить выпадающий список не на группу выделенных ячеек в столбце "G" (как в примере видео), а сразу на весь столбец. Соответственно и зависимый список сразу на весь столбец "H". Для вариантов когда создают "бесконечную" таблицу с неизвестным, на момент проектирования и создания таблиц, количеством заполненных, в будущем, срок. Пользователь сам не всегда знает сколько данных будет введено. Если провести аналогию с форматированием таблицы, в таблицу, так со столбцами "G" и "H" диапазон с выпадающими списками должен автоматически расширятся. Сколько смотрел примеров создания зависимых списков не нашёл соответствующего примера. Логично,таблицу "Источник", как справочник, вынести на отдельный лист, и может скрыть его... Станислав, может у вас есть пример создания связанных списков с условиями которые описал выше?
Здравствуйте Станислав.
Спасибо за видео, очень полезная. У меня есть вопрос, который задавал Arzu Mamedov 7 месяцев назад
А если таблица на другой странице? У меня он заголовки как источник не принимает(
В очередной раз спасибо. Перешерстил инет и только у Вас нашел простой, превосходно работающий вариант!
Класс!
генниально ! я б пошла к вам на курсы.
Гениально =) Спасибо!!
А когда и где проходят Ваши семинары.
Благодарю
Добрый времени суток. Интересный и довольно поучительный урок, Спасибо пригодился ))). Вопрос: с использованием той же методики (Двссыл), каким способом возможно сделать поиск в этих ячейках автономным, т.е. при вводе цифр или букв показывал найденные значения. буду очень благодарен. Спасибо
Станислав все очень подробно и быстро, огромное спасибо за видео. Остался один вопрос как сделать связанные выпадающие динамические списки?
"Динамические", это вы что имеете в виду?
Станислав Салостей Чтобы при смене главного списка автоматически менялось содержимое второго списка. Видео есть у одного автора но секрет не раскрывает.
Станислав, здравствуйте. Такой вопрос: например, даны 3 диапазона цен на перечень товаров. Всех клиентов разности по этим диапазонам. В основной таблице есть выпадающий список "наименование товара" и зависимая графа "цена". Можно ли сделать так, чтобы при выборе определенного клиента и отправленного товара, поставлялись цены из нужного диапазона цен?
А у меня такой вопрос: Если есть несколько одноуровневых столбцов по которым надо делать выборку - как быть? можно ли обойтись без VBA? Пример - создание реестра писем на стройплощадке. Есть входящие есть исходящие со своей нумерацией. Так же дата адресат тема ответственный от организации за вопрос и по каждому элементу нужно иметь возможность найти письмо и связанные с ним письма желательно просто по выпадающему списку из нужного столбца... решаю сейчас такую задачу... склоняюсь к тому, что придется писать макрос. а то и целую отдельную подпрограмму, работающую со своей отдельной БД на скрытом листе.
Подскажите как через =ЕСЛИ убрать пустые ячейки?
Я уже всю голову сломал
Очень доступно
Спасибо
Подскажите как делать связанные списки в таблицах гугл докс? Может кто то знает ссылку на видеоурок?
Скажите, а как Вы делаете выпадающее меню, графики для презентаций, про которые вы сказали и показали в начале видео?
Спасибо большое вам за это видео. да и я столкнулся с такой проблемой "пустые ячейки", почему то сам эксель не правильно реагирует это и это потому что в окне выбран чек бокс "игнорировать пустые ячейки". Тут думаю что самой майкрософт надо разбираться. И ещё, как можно добавить столбцы для дальнейшей фильтрации? спрашиваю так как у меня их 9 столбцов
Здорово!
А как соединить несколько таблиц в одну, где в одном столбце фамилии, а в другом числа? Но фамилии могут повторяться и нужно повторы вместе с числами объединить?
Доброго времени суток.
Спасибо за ролик, держите лайк. Подскажите пожалуйста, почему связанные списки нельзя сделать цифровые?
одна моя знакомая говорила , "Лучше просто , чем умно "
(и сейчас говорит , если что)
Четко
Здравствуйте Станислав. Подскажите пожалуйста как осуществить следующую задачу:
Я создал выпадающий список с помощью вкладки "разработчик". В списке указаны наименования товаров.
Мне нужно чтобы при выборе наименования товара, в соседней ячейке отображалась его стоимость.
Иными словами, ячейка с текстом должна быть равна значению ячейки с числом. Как осилить?
+Nano Diod
Присоединяю свой голос к вопросу. Именно поиск этой опции навёл меня на данное видео.
+Nano Diod
+Ave Salom
Если я правильно понял, использован элемент управления?
Тогда так: он выдает в ячейке к которой он привязан порядковый номер выбранной позиции в исходном списке.
Для извлечения нужных данных используйте формулу ИНДЕКС. В ней сделайте ссылку на столбец и через точку с запятой - ссылку на ячейку, привязанную к элементу управления.
Подробнее про эту формулу можно посмотреть в этом видео:
ua-cam.com/video/50hDZAVXwTM/v-deo.html
Очень здорово! А можно сделать 3 уровневый зависимый список?
ответ плз на етот вопрос.
Как многоуровневый список сделать?
У меня не получаетмся так сделать, пишет что "a list source must be a delimited list , or a reference to single column or row"
Непонятно, но здорово!
Большое спасибо
А как быть если, список зависит не от одного значения,а от 2-х и более.
Я пытаюсь решить проблему учета данных для животноводства. Например у меня 3 корпуса. Каждый корпус обслуживает 3 специалиста, а за специалистами закреплено разное поголовье обслуживания. Причем в динамике поголовье одних специалистов переходит к другим.А очень бы хотелось узнать побстрее какие виды мероприятий были проведены с конкретнм животным. Если будет ориентир на решение-спасибо
Первый уровень - присвоить имя таблице. Второй и третий - как в видео.
Скажите, а как Вы делаете выпадающее меню, графики для презентаций, про которые вы сказали и показали в начале видео?
Это анимация по триггеру, если коротко.
Здравствуйте, скажите пожалуйста как создавать ячейку-список Категории и под категории. И как связать под категорию с какой-то ячейкой. Например для меню ресторана: Соки(Категория)-Любимый(Под категория). Выбрал среди всего Соки и на ячейке справа появляется список(под категорий) всего что относится к сокам и правее цена.
Может ВПР использовать
все отлично, один только вопрос: как сделать чтобы можно было вводить с клавиатуры значения в колонки ЭЛЕМЕНТ и СТАТЬЯ, а эксель чтобы дописывал значение если бы находил его в нужных диапазонах таблицы??? фуф, надеюсь понятно спросил...
+Dart Veider спросили понятно, но такой штуки нет.
Excel умеет делать такую автоподстановку на основе данных из ячеек выше, а вот из другого диапазона - не умеет.
Как сделать так ,чтобы из выпадающего списко можно выбирать несколько пунктов
Я вот сейчас работаю над связанными списками, и попал на это видео.
Возможно я чего-то не понимаю, но у меня вопрос.
Таблица с шапкой: Клиент - Расчетный счет. У одного клиента могут быть разные счета, т.е. вид будет следующий:
Клиент Счет
Иванов 123
Иванов 221
Иванов 223
Как в этом случае, используя ваш метод, легко сделать связанный выпадающий список?
Клиенты будут в шапке. т.е.:
Иванов Петров
123 654
321 789
456 и т.д.
Правда, таблица получится длинной.
у меня в проверке данных не ставится = и я не могу прописать формулу, почему так происходит?
все уже получилось, нужно отдельно присваивать имя и только потом вставлять таблицу
Добрый день!
Спасибо большое за видео! Очень полезно.
Не могу найти способ решить один момент с выпадающим списком.
Есть ячейка связанная со списком значений (это база клиентов и значений много). Как сделать так, чтобы при вводе первых букв фамилии открывался список с удовлетворяющими запрос совпадениями?
Буду безмерно благодарна за подсказку!!
К сожалению такой безмакросной "заполнялки" нет (кроме автоподстановки в колонке). Можно использовать элементы ActiveX, но их нужно "оживлять" через редактор VBA и они не поддерживаются в 64-битном Excel.
Станислав Салостей Спасибо! Попробую написать макрос.
Как то же самое сделать в Гугл Докс ?
udali google docs, zatem, skachai excel, i bolshe ne ispolzuite google docs. esli u vas est ehse numbers, toje nemedlenno udalite.
как с вами связаться?
супер!!
Дорогой Станислав, Спасибо за ваши видео . Очень полезно .
У меня в процессе обучения по ним возник вопрос, как сделать так , что бы в зависимости от значения выбранного из выпадающего списка, менялось содержимое таблицы. Например , Есть таблица товаров(она активная или живая что ли), меняется она в зависимости от того что я выбера в находящемся над ней выпадащем списке , например выбрал фрукты, в таблице отобразились только фрукты. Похоже по смылсу на фильт только через выпадающий список.
Если есть вариант как это сделать, буду очень признателен если поделитесь.
Спасибо.
А в обратную сторону? сам попробую ... Спасибо Станислав
В обратную сторону это: справа укрупнение слева детализация, или выбрал детальную информацию, а потому укрупненную?
Если первое, то тоже самое.
Если второе, то все проще - нужно выбирать только детализацию, а верхнее (группирующее) значение подтягивать формулой Индекс + Поискпоз или ВПР. Как работают такие формулы смотрите в этом видео: ua-cam.com/video/50hDZAVXwTM/v-deo.html
Молодец
режим ссылок включен, и таблица отформатирована, а вот 1 пункт...попробуем.
Не видно вводимые формулы, а на слух - не понятно. Увеличьте экран с вводимыми формулами.
До этого момента было всё понятно.
Потом - путаница.
Нам по работе требуется работать с гос реестрами, и ни как не получается заголовки из номеров сделать, например такие;23194-03, 23194-07 и 48220-11, как это реализовать не подскажите? На всякий случай почта: ttifh@mail.ru
А что именно не получается?
по времени с 5:05 , у Вас: материалы, зарплата, амортизация - требуется поменять на 23194-03, 23194-07 и 48220-11, у меня не получается. Если пишу буквы то все нормально, столбцу присваивается имя "материалы", а заменить его на 23194-03 не получается. цифры и минус не воспринимается даже если ячейка отформатирована как текстовая.
Не смог повторить ошибку - у меня все срабатывает... Какая у вас версия Excel (какой год)? Как вариант, попробуйте ставить вначале в ячейке перед набором цифр знак апострофа '
После нажатия на Enter его видно не будет, но значение станет однозначно текстовым.
2003 год, но не могу это все делать на более новых выпусках, пришел приказ (мы ФБУ) переходить на ОС РОСА (Linux) там скорее всего будет LibreOfis его возможности мне не известны так что делаю все пока в 2003 его он точно понимает.
Wow... 2003... Там таких нормальных форматированных таблиц нет... И у меня нет возможности это проверить. Попробуйте ставить апостроф или ввести какой-то текст/букву
Вторая формула выдает ошибку(((🤔 что не так?
Что-то неправильно написано? Скобочка не там?
@@salosteysv завтра еще раз проверю.. Вроде я все правильно написала..
А вот и нет))) сейчас посмотрела еще внимательно.. Я упустила второй знак амперсанда.. Думаю завтра все получится..должен получиться, так как ваш метод мне понравился больше..
👍👏👏👏Супер.. Спасибо.. Ваш метод и быстрее и короче.. А главное не надо ставить знак _ если заголовок из двух слов, например.. Что и надо было..
Учимся дальше)))) какая всё-же интересная программа ехсеl.. Видимо возможности у неё безграничны..
я один вижу название файла вверху "Звязанные списки"?
+PRO_in_ALL
Я тоже увидел. Неловко как-то... спасибо что обратили внимание ))
hahahahha
а если у меня материал в колонке, можно как нибудь зделать тоже самое? www.screencast.com/t/00yS9fvqb
нашёл решение здесь. Спасибо ua-cam.com/video/rUgW65pIYqo/v-deo.html
Ядерная физика отдыхает(...
а я все с поверпоинтом вожусь
Очень много того, что делать не надо. лучше вкратце и по делу.
Ну, да )
Однако хочется рассказать побольше, потому так и получилось.
Но в целом очень полезная информация. я долго искал.
как-то витьевато и путано...
+Наталья Тк, видимо это по Фрейду - не хочу расставаться с секретами, поэтому речь забивается "оговорочками" )))
можно перезаписать
+Наташа Vital
Странно. На мой взгляд всё доходчиво и внятно. Гораздо проще, чем на офф сайте, это уж точно.
Гениально)))
Спасибо большое
Спасибо за видео!
Хотелось бы посмотреть как сделать два зависимых выпадающих списка с автоматическим добавлением данных и автосортировкой в Google Таблицах.
Очень полезная информация на доступном языке. Спасибо большое.
Удивительно, потрясающе, блеск!
Спасибо, Станислав!
Делайте еще ролики!
Pro trick : you can watch movies at flixzone. Been using them for watching all kinds of movies these days.
@Mohamed Anthony Yea, I have been watching on Flixzone} for months myself :)
@Mohamed Anthony Yea, been using flixzone} for years myself :D
Огромное спасибо Станислав это очень хороший ролик!!!
Ты Бог Екселя. Спасибо за видео . Очень помогло
Спасибо )
но все-таки до бога Excel мне еще далеко-далеко )
@@salosteysv интересно , кто тогда Бог Excel_я
Это гениально! Давно искал.
Супер! Спасибо!
А если таблица на другой странице? У меня он заголовки как источник не принимает(
+Arzu Mamedov значит у вас Excel 2007 года.
Заголовки нужно задавать как новый диапазон через Диспетчер имён на вкладке Формулы.
Это в Excel 2007 года. Там нужно в диспетчере имен создавать диапазон.
Шикарно! Спасибо!🤝
Подскажите любой возможный способ убрать из выпадающих списков пустые значения. Возможно другой метод создания самого выпадающего списка
Напишите диапазон с формулой ЕСЛИ()
Если значение пустое - ничего не выводить.
Станислав Салостей
А по подробней можно?
Спасибо большое! очень полезное и простое объяснение столь сложных вещей! у меня всё получилось! но у меня есть вопрос и поосложнее. В одной закладке у меня разработан бланк заявления от работника. В другой закладке - таблица с его персональными данными: Имя, Фамилия, год рождения, в каком отделе работает, кем и табельный номер. Раньше умным человеком было сделано так, что вбивая в бланк заявления табельный номер, сразу подтягивались и остальные персональные данные и заявление оказывалось готовым за несколько секунд! Только это умный человек меня предупреждал, чтоб закрывая файл, я ни в коем случае его не сохраняла. И в один день я забыла и сохранила..... и всё пропало!!! Научите пожалуйста восстановить утраченное! Буду оооочень благодарна Вам! Я у кого не спрашиваю, никто не знает как мне помочь.
О прекрасное видео! Черти сколько времени промаялась с другими такими инструкциями, а с этой наконец всё получилось! Большое спасибо!))
Волшебник!!! Честное слово!!!
+Маркетинг Шымкент, спасибо!
Спасибо большое , видио очень помогло мне , избавило меня от многих проблем. продолжай в том же духе ))
ОГРОМНОЕ СПАСИБО!
+Галина Милованова пожалуйста )
Спасибо большое! очень помогли!
Ставлю равно,выделяю шапку, а как у вас название таблицы не выдает,а только диапазон ячеек через :
+Павел Ретвер
Если таблица отформатирована (она же отформатирована как таблица?), но формулы ставятся как обычные то причины могут быть следующие:
1. Вы выделяете несколько ячеек, а потом нажимаете "="
2. Или у вас отключен режим ссылок на форматированные таблицы. Нужно зайти в Параметры → Формулы и там проверить галочку Использовать имена таблиц в формулах. Галочка должна быть нажата.