Выпадающие списки в Excel
Приветствую всех и особенно своих подписчиков! Давно не писал, потому что работал вместо начальника и за себя, не было свободного времени на написание поста. Теперь дальше всё пойдёт в прежнем ритме.
Рад видеть в нашем тематическом сообществе интересные и полезные посты авторов @Petrov210217, @navferty, @zoodes, @FelixJanssen, @zoixnet, @kda2495, @avvacat, продолжайте в том же духе))
Итак, по просьбе @LordTachanka выкладываю пост, как создать выпадающие списки разными способами.
1 способ
В пустой ячейке под списком нажимаем сочетание клавиш «Alt+стрелка вниз», либо в меню правой кнопки мыши «Выбрать из раскрывающегося списка» и выбираем ранее введённые значения, которые будут отсортированы в алфавитном порядке.
Способ работает даже если в списке имеются пустые ячейки. Также при добавлении новых значений они автоматически попадают в выпадающий список. Способ удобен если в столбце часто вводятся одинаковые названия.
2 способ
Создаём список с данными из которых будет сформирован выпадающий список. Выделяем диапазон, где будет отображаться выпадающий список, затем на вкладке «Данные» – «Проверка данных» – «Тип данных» выбираем «Список». Далее ставим курсор в поле «Источник», куда указываем диапазон с образцовыми значениями списка:
Плюсом является также то, что можно задать уведомление об ошибке при вводе товара, отсутствующего в списке:
В результате будет выходить такое сообщение:
Удалить выпадающий список можно выбрав в «Тип данных» – «Любое значение».
С помощью макроса (с возможностью добавления новых элементов)
Допустим, что у нас есть список с именами и таблица, куда их нужно вносить.
Мы сделаем выпадающий список, автоматически добавляющий новые фамилии к списку и наоборот, в общем будет двухсторонняя связь.
Выделяем весь список (ячейки A1:A7) и нажимаем Ctrl+T или Главная - Форматировать как таблицу, чтобы создать "умную" таблицу:
Теперь создадим именованный диапазон, указывающий на заполненные именами ячейки в нашем списке. Для этого выделим в списке уже только имена без шапки (ячейки A2:A7) и в левой части строки формул (там будет имя таблицы) введём имя для нашего диапазона (например Легенды):
После переименования таблицы нажимаем Enter, в результате слово «Легенды» исчезнет, но диапазон будет создан.
Проверить диапазон можно на вкладке Формулы - Диспетчер имен:
Таким образом, при дописывании новых имен к списку будет расширяться наша "умная" Таблица3, а за ней и наш именованный диапазон Легенды.
Теперь создаём выпадающий список в ячейке
Выделяем зелёные ячейки и жмём на вкладке "Данные" кнопку "Проверка данных".
Далее выбираем из выпадающего списка "Тип данных" позицию "Список" и вводим в строку" Источник" ссылку на созданный именованный диапазон =Легенды:
Чтобы Excel позволил нам в будущем ввести в список новые имена, снимем галочки на вкладках "Сообщение для ввода" и "Сообщение об ошибке" и жмём ОК. Выпадающий список готов!
Причем, если, например, вручную дописать новое имя в список в столбце А, то оно автоматически появится в выпадающем списке в любой из зелёных ячеек, поскольку имена берутся из динамического диапазона Легенды:
Последний штрих, вставим в нашу книгу простой макрос, который будет отслеживать ввод в зелёные ячейки и при вводе новых людей добавлять их к списку.
Щёлкаем правой кнопкой мыши по ярлыку нашего листа и выбираем "Просмотреть код". В модуле листа в редакторе Visual Basic, скопируйте код (не забудьте заменить название диапазона и ячейки ввода, также можно изменить вопрос при добавлении:
Private Sub Worksheet_Change(ByVal Target As Range)
Set p = Range("Легенды")
If Target.Cells.Count > 1 Then Exit Sub
If IsEmpty(Target) Then Exit Sub
If Not Intersect(Target, Range("D2:D11")) Is Nothing Then
If WorksheetFunction.CountIf(p, Target) = 0 Then
r = MsgBox("Добавить новое имя в справочник?", vbYesNo)
If r = vbYes Then p.Cells(p.Rows.Count + 1) = Target
End If
End If
End Sub
Теперь при попытке ввести новое имя в любую из зелёных ячеек Excel будет спрашивать:
При утвердительном ответе, имя автоматически будет добавлено к списку и в выпадающий список.
MS, Libreoffice & Google docs
719 постов15K подписчика
Правила сообщества
1. Не нарушать правила Пикабу
2. Публиковать посты соответствующие тематике сообщества
3. Проявлять уважение к пользователям
4. Не допускается публикация постов с вопросами, ответы на которые легко найти с помощью любого поискового сайта.
По интересующим вопросам можно обратиться к автору поста схожей тематики, либо к пользователям в комментариях
Важно - сообщество призвано помочь, а не постебаться над постами авторов! Помните, не все обладают 100 процентными знаниями и навыками работы с Office. Хотя вы и можете написать, что вы знали об описываемом приёме раньше, пост неинтересный и т.п. и т.д., просьба воздержаться от подобных комментариев, вместо этого предложите способ лучше, либо дополните его своей полезной информацией и вам будут благодарны пользователи.
Утверждения вроде "пост - отстой", это оскорбление автора и будет наказываться баном.