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

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

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

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

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

Ниже появится линия, указывающая на источники.

вы можете указать информацию несколькими способами.

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

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

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

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

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

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

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

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

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

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

Убедитесь, что вы установили флажок ниже.

Вы получите такой дизайн.

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

= КОСВЕННО («Таблица1 [Город]»)

  

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

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

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

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

= Cell_address

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

= D1

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

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

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

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

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

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

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

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

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

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

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

= КОСВЕННО (LA9)

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

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

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

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

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

Функция OFFSET возвращает ссылку на диапазон, который смещен от первой ячейки на указанное количество строк и столбцов: = OFFSET (начало; вниз; вправо; size_in_lines; size_in_columns)

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

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

Мультивыбор

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

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

Private Sub Worksheet_Change (ByVal Target As Range) При ошибке Возобновить Далее, если не пересечь (Target, Range («C2: F2»)) Ничего E Target.Cells.Count = 1 Тогда Application.EnableEvents = False Если Len (Target.Offset (1, 0)) = 0 Тогда Target.Offset (1, 0) = Target Else Target.End (xlDown) .Offset (1, 0) = Target End If Target.ClearContents Application.EnableEvents = True End If End Sub

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

Если не пересекается (Target, Range («E7»)) Is Nothing E Target.Cells.Count = 1 Then

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

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

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

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

Private Sub Worksheet_Change (ByVal Target As Range) При ошибке Возобновить Далее Если не пересечь (Target, Range («E7»)) Ничего E Target.Cells.Count = 1 Тогда Application.EnableEvents = False newVal = Целевое приложение.Отменить oldval = Target If Len (oldval) 0 And oldval newVal Then Target = Target & «,» & newVal Else Target = newVal End If Len (newVal) = 0 Тогда Target.ClearContents Application.EnableEvents = True End If End Sub

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

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