Как в excel изменить раскрывающийся список. Добавление и удаление элементов раскрывающегося списка

Выпадающий перечень в Excel с помощью инструментов либо макросов

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

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

Создание раскрывающегося списка

Путь: меню «Данные» – инструмент «Проверка данных» – вкладка «Параметры». Тип данных – «Список».

Как в excel изменить раскрывающийся список. Добавление и удаление элементов раскрывающегося списка

Ввести значения, из которых будет складываться выпадающий перечень, можно различными способами:

  1. Вручную через «точку-с-запятой» в поле «Источник».
  2. Как в excel изменить раскрывающийся список. Добавление и удаление элементов раскрывающегося списка

  3. Ввести значения заблаговременно. А в качестве источника указать спектр ячеек со списком.
  4. Как в excel изменить раскрывающийся список. Добавление и удаление элементов раскрывающегося списка

  5. Назначить имя для спектра значений и в поле источник вписать это имя.

Как в excel изменить раскрывающийся список. Добавление и удаление элементов раскрывающегося спискаКак в excel изменить раскрывающийся список. Добавление и удаление элементов раскрывающегося списка

Любой из вариантов даст таковой результат.



Выпадающий перечень в Excel с подстановкой данных

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

  1. Выделяем спектр для выпадающего перечня. В главном меню находим инструмент «Форматировать как таблицу».
  2. Как в excel изменить раскрывающийся список. Добавление и удаление элементов раскрывающегося списка

  3. Откроются стили. Избираем хоть какой. Для решения нашей задачки дизайн не имеет значения. Наличие заголовка (шапки) принципиально. В нашем примере это ячейка А1 со словом «Деревья». То есть необходимо выбрать стиль таблицы со строчкой заголовка. Получаем последующий вид диапазона:
  4. Как в excel изменить раскрывающийся список. Добавление и удаление элементов раскрывающегося списка

  5. Ставим курсор в ячейку, где будет находиться выпадающий перечень. Открываем характеристики инструмента «Проверка данных» (выше описан путь). В поле «Источник» прописываем такую функцию:

Как в excel изменить раскрывающийся список. Добавление и удаление элементов раскрывающегося списка

Протестируем. Вот наша таблица со перечнем на одном листе:

Как в excel изменить раскрывающийся список. Добавление и удаление элементов раскрывающегося списка

Добавим в таблицу новое значение «елка».

Как в excel изменить раскрывающийся список. Добавление и удаление элементов раскрывающегося списка

Теперь удалим значение «береза».

Как в excel изменить раскрывающийся список. Добавление и удаление элементов раскрывающегося списка

Осуществить загаданое нам посодействовала «умная таблица», которая легка «расширяется», меняется.

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

Как в excel изменить раскрывающийся список. Добавление и удаление элементов раскрывающегося списка

  1. Сформируем именованный спектр. Путь: «Формулы» – «Диспетчер имен» – «Создать». Вводим неповторимое заглавие спектра – ОК.
  2. Как в excel изменить раскрывающийся список. Добавление и удаление элементов раскрывающегося списка

  3. Создаем раскрывающийся перечень в хоть какой ячейке. Как это сделать, уже понятно. Источник – имя диапазона: =деревья.
  4. Снимаем галочки на вкладках «Сообщение для ввода», «Сообщение о ошибке». Ежели этого не сделать, Excel не дозволит нам вводить новейшие значения.
  5. Как в excel изменить раскрывающийся список. Добавление и удаление элементов раскрывающегося списка

  6. Вызываем редактор Visual Basic. Для этого щелкаем правой клавишей мыши по наименованию листа и перебегаем по вкладке «Исходный текст». Или сразу жмем клавиши Alt + F11. Копируем код (только вставьте свои параметры).
  7. PrivateSub Worksheet_Change(ByVal Target As Range)   Dim lReply AsLong   If Target.Cells.Count > 1 ThenExitSubIf Target.Address = "$C$2"ThenIf IsEmpty(Target) ThenExitSubIf WorksheetFunction.CountIf(Range("Деревья"), Target) = 0 Then lReply = MsgBox("Добавить введенное имя " & _ Target & " в выпадающий список?", vbYesNo + vbQuestion) If lReply = vbYes Then Range("Деревья").Cells(Range("Деревья").Rows.Count + 1, 1) = Target EndIfEndIfEndIfEndSub  

  8. Сохраняем, установив тип файла «с поддержкой макросов».
  9. Как в excel изменить раскрывающийся список. Добавление и удаление элементов раскрывающегося списка

  10. Переходим на лист со перечнем. Вкладка «Разработчик» – «Код» – «Макросы». Сочетание кнопок для скорого вызова – Alt + F8. Избираем необходимое имя. Жмем «Выполнить».

Как в excel изменить раскрывающийся список. Добавление и удаление элементов раскрывающегося списка

Когда мы введем в пустую ячейку выпадающего перечня новое наименование, покажется сообщение: «Добавить введенное имя баобаб в выпадающий список?».

Нажмем «Да» и добавиться еще одна строчка со значением «баобаб».

Выпадающий перечень в Excel с данными с другого листа/файла

Когда значения для выпадающего перечня размещены на другом листе либо в иной книжке, обычный метод не работает. Решить задачку можно с помощью функции ДВССЫЛ: она сформирует правильную ссылку на наружный источник информации.

  1. Делаем активной ячейку, куда желаем поместить раскрывающийся список.
  2. Открываем характеристики проверки данных. В поле «Источник» вводим формулу: =ДВССЫЛ(“[Список1.xlsx]Лист1!$A$1:$A$9”).
Читайте также  Самсунг перестал заряжаться от сети. Не заряжается телефон

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

Как сделать зависимые выпадающие списки

Возьмем три именованных диапазона:

Как в excel изменить раскрывающийся список. Добавление и удаление элементов раскрывающегося списка

Это обязательное условие. Выше описано, как сделать обыденный перечень именованным спектром (с помощью «Диспетчера имен»). Помним, что имя не может содержать пробелов и символов препинания.

  1. Создадим 1-ый выпадающий перечень, куда войдут наименования диапазонов.
  2. Как в excel изменить раскрывающийся список. Добавление и удаление элементов раскрывающегося списка

  3. Когда поставили курсор в поле «Источник», перебегаем на лист и выделяем попеременно нужные ячейки.
  4. Как в excel изменить раскрывающийся список. Добавление и удаление элементов раскрывающегося списка

  5. Теперь сделаем 2-ой раскрывающийся перечень. В нем должны отражаться те слова, которые соответствуют избранному в первом перечне наименованию. Ежели «Деревья», то «граб», «дуб» и т.д. Вводим в поле «Источник» функцию вида =ДВССЫЛ(E3). E3 – ячейка с именованием первого диапазона.
  6. Как в excel изменить раскрывающийся список. Добавление и удаление элементов раскрывающегося списка

    Выбор пары значений из выпадающего перечня Excel

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

    1. Создаем обычный перечень с помощью инструмента «Проверка данных». Добавляем в начальный код листа готовый макрос. Как это делать, описано выше. С его помощью справа от выпадающего перечня будут добавляться избранные значения.
    2. PrivateSub Worksheet_Change(ByVal Target As Range) OnErrorResumeNextIfNot Intersect(Target, Range("Е2:Е9")) IsNothingAnd Target.Cells.Count = 1 Then Application.EnableEvents = FalseIf Len(Target.Offset(0, 1)) = 0 Then Target.Offset(0, 1) = Target Else Target.End(xlToRight).Offset(0, 1) = Target EndIf Target.ClearContents Application.EnableEvents = TrueEndIfEndSub  

    3. Чтобы избранные значения показывались снизу, вставляем иной код обработчика.
    4. PrivateSub Worksheet_Change(ByVal Target As Range) OnErrorResumeNextIfNot Intersect(Target, Range("Н2:К2")) IsNothingAnd Target.Cells.Count = 1 Then Application.EnableEvents = FalseIf Len(Target.Offset(1, 0)) = 0 Then Target.Offset(1, 0) = Target Else Target.End(xlDown).Offset(1, 0) = Target EndIf Target.ClearContents Application.EnableEvents = TrueEndIfEndSub  

    5. Чтобы избираемые значения показывались в одной ячейке, разбитые хоть каким знаком препинания, применим таковой модуль.

    Не забываем поменять спектры на «свои». Списки создаем классическим методом. А всю остальную работу будут делать макросы.

    Выпадающий перечень с поиском

    1. На вкладке «Разработчик» находим инструмент «Вставить» – «ActiveX». Тут нам нужна клавиша «Поле со списком» (ориентируемся на всплывающие подсказки).
    2. Как в excel изменить раскрывающийся список. Добавление и удаление элементов раскрывающегося списка

    3. Щелкаем по значку – становится активным «Режим конструктора». Рисуем курсором (он становится «крестиком») маленький прямоугольник – место грядущего списка.
    4. Как в excel изменить раскрывающийся список. Добавление и удаление элементов раскрывающегося списка

    5. Жмем «Свойства» – раскрывается список настроек.
    6. Как в excel изменить раскрывающийся список. Добавление и удаление элементов раскрывающегося списка

    7. Вписываем спектр в строчку ListFillRange (руками). Ячейку, куда будет выводиться выбранное значение – в строчку LinkedCell. Для конфигурации шрифта и размера – Font.

    Скачать пример выпадающего списка

    При вводе первых букв с клавиатуры высвечиваются пригодные элементы. И это далековато не все приятные моменты данного инструмента. Тут можно настраивать зрительное представление инфы, указывать в качестве источника сходу два столбца.

5 методов сотворения выпадающего перечня в ячейке Excel

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

Как нам это может пригодиться?

Часто случается так, что в некий из колонок вашей таблицы необходимо вводить схожие повторяющиеся значения. К примеру, фамилии служащих, наименования продуктов. Что может случиться? Естественно, в первую очередь будут ошибки при вводе. Человечий фактор ведь никто не отменял. Чем нам сие грозит? К примеру, когда мы решим подсчитать, сколько заказов выполнил каждый из менеджеров, то окажется, что фамилий больше, чем служащих. Дальше придётся находить ошибки, исправлять их и вновь повторять расчет.

Читайте также  В какой программе можно перевернуть видео. Как перевернуть видео на компьютере на 90, 180, 270 градусов за 6 шагов

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

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

1 – Самый стремительный способ.

Как проще всего добавить выпадающий список? Всего один щелчок правой клавишей мыши по пустой клеточке под столбцом с данными, потом команда контекстного меню “Выберите из раскрывающегося списка” (Choose from drop-down list). А можно просто стать в необходимое место и надавить сочетание кнопок Alt+стрелка вниз. Покажется отсортированный список неповторимых ранее введенных значений.
Метод не работает, ежели нашу ячейку и столбец с записями отделяет хотя бы одна пустая строчка либо вы желаете ввести то, что еще не вводилось выше. На нашем примере это отлично видно.

2 – Используем меню.

Давайте разглядим маленькой пример, в котором нам необходимо повсевременно вводить в таблицу одни и те же наименования продуктов. Выпишите в столбик данные, которые мы будем применять (например, наименования товаров). В нашем примере – в спектр G2:G7.

Выделите ячейку таблицы (можно сходу несколько), в которых желаете применять ввод из заблаговременно определенного списка. Дальше в главном меню выберите на вкладке Данные – Проверка… (Data – Validation). Дальше нажмите пункт Тип данных (Allow) и выберите вариант Перечень (List). Поставьте курсор в поле Источник (Source) и вчеркните в него адреса с эталонными значениями частей – в нашем случае G2:G7. Рекомендуется также употреблять тут абсолютные ссылки (для их установки нажмите кнопку F4).

Бонусом тут идет возможность задать подсказку и сообщение о ошибке, ежели автоматом вставленное значение вы захотите поменять вручную. Для этого есть вкладки Подсказка по вводу (Input Message) и Сообщение о ошибке (Error Alert).

В качестве источника можно употреблять также и именованный спектр.

К примеру, спектру I2:I13, содержащему наименования месяцев, можно присвоить наименование “месяцы”. Потом имя можно ввести в поле “Источник”.

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

Но вы сможете и не применять спектры либо ссылки, а просто найти вероятные варианты прямо в поле “Источник”. К примеру, написать там –

Да;Нет

Используйте для разделения значений точку с запятой, запятую, или иной знак, установленный у вас в качестве разделителя частей. (Смотрите Панель управления – Часы и регион – Форматы – Дополнительно – Числа.)

3 – Создаем элемент управления.

Вставим на лист новейший объект – элемент управления “Поле со списком” с следующей привязкой его к данным на листе Excel. Делаем:

  1. Откройте вкладку Разраб (Developer). Ежели её не видно, то в Excel 2007 необходимо надавить клавишу Кабинет – Характеристики – флаг Показывать вкладку Разраб на ленте (Office Button – Options – Show Developer Tab in the Ribbon) либо в версии 2010–2013 щелкните правой клавишей мыши по ленте, выберите команду Настройка ленты (Customize Ribbon) и включите отображение вкладки Разраб (Developer) с помощью флага.
  2. Найдите подходящий значок посреди частей управления (см.рисунок ниже).

Вставив элемент управления на рабочий лист, щелкните по нему правой клавишей мышки и выберите в появившемся меню пункт “Формат объекта”. Дальше указываем спектр ячеек, в котором записаны допустимые значения для ввода. В поле “Связь с ячейкой” укажем, куда конкретно поместить итог. Принципиально учесть, что сиим результатом будет не само значение из указанного нами спектра, а лишь его порядковый номер.

Читайте также  Удалить ненужные программы с компьютера. Как удалить ненужные программы с компьютера

Но нам ведь нужен не этот номер, а соответственное ему слово. Используем функцию ИНДЕКС (INDEX в британском варианте). Она дозволяет отыскать в перечне значений одно из их соответственно его порядковому номеру. В качестве аргументов ИНДЕКС укажите спектр ячеек (F5:F11) и адресок с приобретенным порядковым номером (F2).

Формулу в F3 запишем, как показано на рисунке:

=ИНДЕКС(F5:F11;F2)

Как и в прошлом методе, тут возможны ссылки на остальные листы, на именованные диапазоны.

Обратите также внимание, что тут мы не привязаны ни к какой определенному месту таблицы. Таковым перечнем комфортно воспользоваться, так как его можно свободно “перетаскивать” мышкой в хоть какое комфортное место. Для этого на вкладке “Разработчик” необходимо активизировать режим конструктора.

4 – Элемент ActiveX

Действуем аналогично предыдущему способу, но избираем иконку чуток ниже – из раздела “Элементы ActiveX”.

Определяем список допустимых значений (1). Обратите внимание, что тут для показа можно выбирать сходу несколько колонок. Потом избираем адресок, по которому будет вставлена подходящая позиция из списка (2).Указываем количество столбцов, которые будут применены как начальные данные (3), и номер столбца, из которого будет происходить выбор для вставки на лист (4). Ежели укажете номер столбца 2, то в А5 будет вставлена не фамилия, а должность. Можно также указать количество строк, которое будет выведено в списке. По умолчанию – 8. Другие можно прокручивать мышкой (5).

Этот метод труднее предшествующего, но зато возвращает сходу значение, а не его номер. Потому необходимость в промежной ячейке и обработке ее при помощи ИНДЕКС – отпадает. Думаю, таковым перечнем воспользоваться еще удобнее.

5 – Перечень с автозаполнением

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

Вот как автозаполнение может смотреться на простом примере:

Способ 1. Укажите заранее большой источник.

Самая обычная и легкая хитрость. В начале действуем по обыкновенному методу действий: в меню избираем на вкладке Данные – Проверка … (Data – Validation). Из списка Тип данных (Allow) выберите вариант Перечень (List). Поставьте курсор в поле Источник (Source).  Зарезервируем в перечне набор с огромным запасом: к примеру, до 55-й строчки, хотя занято у нас лишь 7. Непременно не забудьте поставить галочку в чекбоксе “Игнорировать пустые …”. Тогда ваш “резерв” из пустых значений не будет для вас мешать.

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

Конечно, в качестве источника можно указать и весь столбец:

=$A:$A

Но обработка такового огромного количества ячеек может несколько замедлить вычисления.

Метод 2. Применяем именованный спектр.

Именованный спектр различается от обыденного тем, что ему присвоено определенное наименование. С ним еще проще работать, так как не необходимо вводить ссылку, а довольно просто указать его имя. Давайте разглядим маленький пример.

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

Выделим имеющийся в нашем распоряжении список имен A2:A10. Потом присвоим ему заглавие, заполнив поле “Имя”, находящееся левее строчки формул. Сделаем в С2 список значений. В качестве источника для него укажем выражение

=имя

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

Оставьте комментарий