Как изменить выпадающий список в excel

Как редактировать выпадающий список в Microsoft Excel

Выпадающие списки в Excel упрощают ввод данных, но иногда вам потребуется изменить этот список. Вы можете добавлять или удалять элементы из раскрывающегося списка независимо от того, как вы его создали.

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

Изменить раскрывающийся список из таблицы

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

В ОТНОШЕНИИ: Как создать и использовать таблицу в Microsoft Excel

Чтобы добавить элемент, перейдите к последней строке столбца, нажмите Enter или Return, введите новый элемент списка, затем снова нажмите Enter или Return.

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

Чтобы удалить элемент, щелкните правой кнопкой мыши и выберите «Удалить» > «Строки таблицы». Это удаляет элемент из массива и из списка.

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

Изменить раскрывающийся список из диапазона ячеек

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

В ОТНОШЕНИИ: Как назвать диапазон ячеек в Excel

Добавить элемент в диапазон ячеек

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

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

В поле «Источник» обновите ссылки на ячейки, чтобы включить дополнения, или перетащите новый диапазон ячеек на лист. Нажмите «ОК», чтобы применить изменение.

При желании установите флажок внизу, если вы используете список в нескольких местах и ​​хотите обновлять его везде.

Добавить элемент в именованный диапазон

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

Перейдите на вкладку «Формулы» и выберите «Диспетчер имен» в разделе «Определенные имена» на ленте.

Когда откроется диспетчер имен, выберите именованный диапазон и обновите ссылки на ячейки в поле «Относится к» внизу. Вы можете вручную настроить ссылки на ячейки или просто перетащить их на свой лист. Нажмите на галочку слева от этого поля, чтобы сохранить изменения, и нажмите «Закрыть».

Ваш раскрывающийся список автоматически обновляется, чтобы включить новый элемент списка.

Удалить элемент из диапазона

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

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

Чтобы удалить элемент из списка в диапазоне ячеек, щелкните правой кнопкой мыши и выберите «Удалить».

При появлении запроса выберите «Сдвинуть ячейки вверх» и нажмите «ОК».

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

Изменить раскрывающийся список вручную

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

В ОТНОШЕНИИ: Как ограничить ввод данных в Excel с проверкой данных

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

В поле Источник добавьте в список новые элементы списка или удалите ненужные. Нажмите «ОК», и ваш список будет обновлен.

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

Tremplin Numérique

Написание Tremplin Numérique, французское веб-агентство. Наши авторы ежедневно и бесплатно предоставляют вам последние технологические и цифровые новости во Франции и во всем мире.

Как настроить зависимые выпадающие списки в MS Excel, используя СМЕЩ и СУММПРОИЗВ

В этой статье мы продемонстрирует простой подход по настройке выпадающего списка, зависящего от другого выпадающего списка. Например, мы выбираем страну в ячейке F1 и это изменяет список городов, доступных для выбора в ячейке F2, как показано на Рисунке 1.

Предположим, что мы уже настроили выпадающий список для страны, ссылающийся на диапазон A1:C1, тогда мы можем настроить список городов, используя формулу ниже, где:

• СМЕЩ возвращает диапазон для зависимого выпадающего списка
• A2 фиксирует начальную ячейку для функции СМЕЩ
• 0 говорит функции СМЕЩ, что вертикального смещения нет
• ПОИСКПОЗ(F1;A1:C1;0)-1 говорит функции СМЕЩ на сколько столбцов нужно сместиться вправо от начальной ячейки A2
• СУММПРОИЗВ((F1=A1:C1)*(A2:C3<>«»)) сообщает функции СМЕЩ количество непустых ячеек (A2:C3<>«») в выбранном столбце (F1=A1:C1)

=СМЕЩ(A2 ;0 ;ПОИСКПОЗ(F1;A1:C1;0)-1 ;СУММПРОИЗВ((F1=A1:C1)*(A2:C3<>«»)))

Рисунок 2 демонстрирует зависимый список городов, когда в ячейке для страны выбрана Украина, где:

• (F1=A1:C1) – это массив
• (A2:C3<>«») – это массив
• (F1=A1:C1)*(A2:C3<>«») – это массив , поскольку произведение ЛОЖЬ*ЛОЖЬ или ЛОЖЬ*ИСТИНА равно 0, тогда как произведение ИСТИНА*ИСТИНА равно 1
• Функция СУММПРОИЗВ возвращает сумму массива , равную 1 в нашем случае

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

Выпадающий список в Excel

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

Как сделать выпадающий список в Excel

Как сделать выпадающий список в Excel 2010 или 2016 с помощью одной командой на панели инструментов? На вкладке «Данные» в разделе «Работа с данными» найдите кнопку «Проверка данных». Нажмите на нее и выберите первый пункт.

Откроется окно. Во вкладке «Параметры» в выпадающем разделе «Тип данных» выберите «Список».

Снизу появится строка для указания источников.

Указывать информацию можно по-разному.

• Ручной ввод
  Введите перечень через точку с запятой.
  
• Выбор диапазона значений с листа Excel
  Для этого начните выделять ячейки мышью.
  
  Как отпустите – окно снова станет нормальным, а в строке появятся адреса.
  
• Создание выпадающего списка в Excel с подстановкой данных

Сначала назначим имя. Для этого создайте на любом листе такую таблицу.

Выделите ее и нажмите правую кнопку мыши. Щелкните по команде «Присвоить имя».

Введите имя в строку сверху.

Вызовите окно «Проверка данных» и в поле «Источник» укажите имя, поставив перед ним знак «=».

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

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

Подстановка динамических данных Excel

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

Выделите его и на вкладке «Главная» выберите любой стиль таблицы.

Обязательно поставьте галочку внизу.

Вы получите такое оформление.

Создайте активный элемент, как было описано выше. В качестве источника введите формулу

=ДВССЫЛ("Таблица1[Города]")

Чтобы узнать имя таблицы, перейдите на вкладку «Конструктор» и посмотрите его. Можете поменять имя на любое другое.

Функция ДВССЫЛ создает ссылку на ячейку или диапазон. Теперь ваш элемент в ячейке привязан к массиву данных.

Попробуем увеличить количество городов.

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

=Адрес_ячейки

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

Как убрать (удалить) выпадающий список в Excel

Откройте окно настройки выпадающего списка и выберите «Любое значение» в разделе «Тип данных».

Ненужный элемент исчезнет.

Зависимые элементы

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

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

Это будет название города.

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

Поэтому переименуем эти города, поставив нижнее подчеркивание.

Первый элемент в ячейке A9 создаем обычным образом.

А во втором пропишем формулу:

=ДВССЫЛ(A9)

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

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

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

Для второго перечня нужно ввести формулу:

=СМЕЩ($A$1;ПОИСКПОЗ($E$6;$A:$A;0)-1;1;СЧЁТЕСЛИ($A:$A;$E$6);1)

Функция СМЕЩ возвращает ссылку на диапазон, который смещен относительно первой ячейки на определенное число строк и столбцов:=СМЕЩ(начало; вниз; вправо; размер_в_строках; размер_в_столбцах)

ПОИСКПОЗ возвращает номер ячейки с выбранным в первом списке (E6) городом в указанной области SA:$A.
СЧЕТЕСЛИ считает количество совпадений в диапазоне со значением в указанной ячейке (E6).

Мы получили связанные выпадающие списки в Excel с условием на совпадение и поиском диапазона для него.

Мультивыбор

Часто нам необходимо получить несколько значений из набора данных. Можно вывести их в разные ячейки, а можно объединить в одну. В любом случае необходим макрос.
Нажмите на ярлыке листа внизу правую кнопку мыши и выберите команду «Просмотреть код».

Откроется окно разработчика. В него надо вставить следующий алгоритм.

Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range("C2:F2")) Is Nothing And Target.Cells.Count = 1 Then Application.EnableEvents = False If Len(Target.Offset(1, 0)) = 0 Then Target.Offset(1, 0) = Target Else Target.End(xlDown).Offset(1, 0) = Target End If Target.ClearContents Application.EnableEvents = True End If End Sub

Обратите внимание, что в строке

If Not Intersect(Target, Range("E7")) Is Nothing And Target.Cells.Count = 1 Then

Следует проставить адрес ячейки со списком. У нас это будет E7.

Вернитесь на лист Excel и создайте в ячейке E7 список.

При выборе значения будут появляться под ним.

Следующий код позволит накапливать значения в ячейке.

Private Sub Worksheet_Change(ByVal Target As Range) On Error Resume Next If Not Intersect(Target, Range("E7")) Is Nothing And Target.Cells.Count = 1 Then Application.EnableEvents = False newVal = Target Application.Undo oldval = Target If Len(oldval) <> 0 And oldval <> newVal Then Target = Target & "," & newVal Else Target = newVal End If If Len(newVal) = 0 Then Target.ClearContents Application.EnableEvents = True End If End Sub

Как только Вы переведете указатель на другую ячейку, Вы увидите перечень выбранных городов. Для создания объединенных ячеек в Excel прочитайте эту статью.

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

Выпадающий список в Excel 2010-2013

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

Чтобы создать выпадающий список, на отдельном листе книги или на свободном месте исходного листа создайте строку или столбец с данными без пустых ячеек, выделите его и в поле «Имя» введите название выделенного списка и нажмите клавишу Enter (Рис. 1).

Рис. 1. Список данных для выпадающего списка

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

Рис. 2. Место для вставки выпадающего списка

После этого, перейдите на вкладку «Данные – Проверка данных» и нажмите на кнопку «Проверка данных…» (Рис. 3).

Рис. 3. Меню «Проверка данных»

В открывшемся окне в поле «Тип данных» выберите «Список». В поле «Источник» введите название списка, который подготовили ранее. Убедитесь, что перед ссылкой на список стоит знак равенства и нажмите клавишу «ОК» (Рис. 4).

Рис. 4. Проверка вводимых значений

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

Теперь, при выборе ячейки, для которой создавался список, справа от нее появится кнопка с треугольником внутри, нажав на которую перед Вами появится созданный выпадающий список (Рис. 5).

Рис. 5. Работа выпадающего списка

Прочтите также:

1. Создание выпадающего списка значений в ячейках Excel 2007
2. Разбиение данных по столбцам в Microsoft Excel 2007
3. Группировка ячеек в Excel 2003
4. Транспонирование данных из строки в столбец в Excel