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

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

Элементарный способ

Есть самый простой вариант справиться с поставленной задачей. Вы делаете правый клик мышкой по ячейке под столбиком с данными для вызова контекстного меню. Тут ищем пункт «Выбрать из раскрывающегося списка». Это же действие легко выполнить комбинацией Alt + стрелочка вниз .

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

Традиционный способ

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

Вставка/Имя/Присвоить (Excel 2003)

В свежих версиях (2007, 2010, 2013, 2016) переходите:

Формулы/Определенные имена/Диспетчер имен/Создать

• 
• Вписываете любое наименование, тапаете ОК .
• 
• Затем выделяете ячейки (или несколько), куда планируете вставить перечень заданных полей. В меню переходите:

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

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

Как в Экселе сделать выпадающий список: пользуемся управленческими методами

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

• во-первых, ищите раздел «Разработчик» (Эксель 2007/2010), в других выпусках активируете самостоятельно через параметры «Настроить ленту»;

• во-вторых, переходите на раздел, кликаете «Вставить»;
• в-третьих, выбираете «Поле…» и жмете по значку;
• рисуете прямоугольник;

• делаете правый клик по нему и жмете Формат объекта;

• ищите «Формировать…», выделяете нужные границы;
• отмечаете ту область, куда хотите установить порядковый номер элемента в перечне;
• жмете ОК.

Как создать в Экселе выпадающее меню: пользуемся элементами ActiveX

Действия аналогичны вышеописанным, но ищем «Поле с ActiveX».

Здесь основные отличия в том, что спецэлемент ActiveX может быть в 2 вариантах — режим отладки, который позволяет менять параметры, и — режим ввода, получится сделать только выборку сведений из него. Поменять режим можно клавишей Режим Конструктора в разделе Разработчик. С помощью этого способа вы можете настроить цвет, шрифт и выполнить оперативный поиск.

Дополнительные возможности

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

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

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

Для второй активируем окошко проверки сведений, но в пункте «Источник» пишем «=ДВССЫЛ» и адрес 1-й ячейки. Все получилось.

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

Источник: https://life-v.ru/how-to-make-excel-drop-down-list/

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

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

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

Простой способ

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

1. Перейдите на первую пустую клетку после вашего списка.

1. Сделайте правый клик. Затем выберите указанный пункт.

1. В результате этого появится следующий список.

1. Для перехода по нему достаточно нажать на горячие клавиши Alt+↓.

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

1. Затем для выбора можно использовать только стрелочки (↓ и ↑). Для того чтобы вставить нужный продукт (в нашем случае), достаточно нажать на клавишу Enter.

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

Обратите внимание на то, что этот метод не работает, если вы выберите клетку, выше которой нет никакой информации.

Стандартный

В этом случае необходимо:

1. Выделить нужные ячейки. Перейти на вкладку «Формулы». Нажать на кнопку «Определенные имена». Выбрать пункт «Диспетчер имён».

1. Затем кликнуть на «Создать».

1. Далее нужно будет указать желаемое имя (нельзя использовать символ тире или пробел). В графе диапазон произойдет автозаполнение, поскольку нужные ячейки были выделены в самом начале. Для сохранения нажмите на «OK».

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

1. Нажмите на «Тип данных». Необходимо задать значение «Список».

1. Вследствие этого появится поле «Источник». Кликните туда.

1. Затем выделите нужные ячейки. Ранее созданное имя автоматически подставится. Для продолжения нажимаем на «OK».

1. Благодаря этим действиям вы увидите вот такой элемент.

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

Как включить режим разработчика

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

1. Перейдите в раздел «Параметры».

1. Откройте категорию «Настроить ленту». Затем поставьте галочку напротив пункта «Разработчик». Для сохранения информации кликните на «OK».

Элементы управления

Для создания списков при помощи этого инструмента, необходимо сделать следующее:

1. Выделите свою таблицу данных. Перейдите на вкладку «Разработчик». Кликните на иконку «Вставить». Нажмите на указанный элемент.

1. Также изменится иконка указателя.

1. Выделите какой-нибудь прямоугольник. Именно таких размеров и будет ваша будущая кнопка. Её необязательно делать слишком большой. В нашем случае это только пример.

1. После этого сделайте правый клик мышкой по этому элементу. Затем выберите пункт «Формат объекта».

1. В окне «Форматирование объекта» необходимо:
   • Указать диапазон значений для формирования списка.
   • Выбрать ячейку, в которую будет выводиться результат.
   • Указать количество строк будущего списка.
   • Нажать на «OK» для сохранения.

1. Кликните на этот элемент. После этого вы увидите варианты для выбора.

1. Вследствие этого вы увидите какое-нибудь число. 1 – соответствует первому слову, а 2 – второму. То есть в этой ячейке выводится лишь порядковый номер выбранного слова.

ActiveX

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

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

1. Обратите внимание на то, что у вас включится режим конструктора. Кроме этого, изменится внешний вид указателя.

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

1. Кликните на указанную иконку.

1. Благодаря этому в правой части экрана появится окно «Properties», в котором вы сможете изменить различные настройки для выбранного элемента.

При этом значения и свойства будут меняться в зависимости от того, какой элемент будет активен в данный момент. Здесь вы сможете изменить всё, даже шрифт.

1. В поле «ListFilRange» укажите диапазон ячеек, в котором находятся ваши данные для будущего списка. Заполнение данных должно быть очень аккуратным. Достаточно указать одну неправильную букву, и вы увидите ошибку.

1. Далее необходимо кликнуть правой кнопкой мыши по созданному элементу. Выберите «Объект Combobox». Затем – «Edit».

1. Благодаря этим действиям вы увидите, что внешний вид объекта стал другим. Исчезнет возможность изменения размера.

1. Теперь вы можете спокойно выбрать что-нибудь из этого списка.

1. Для завершения необходимо отключить «Режим конструктора». После этого книга примет стандартный внешний вид.

1. Также необходимо закрыть окно свойств.

Убрать объекты ActiveX довольно просто.

1. Перейдите на вкладку «Разработчик».
2. Активируйте «Режим конструктора».

  Округление в Эксель

1. Нажмите на горячую клавишу Delete.
2. И всё сразу же исчезнет.

Связанные списки

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

1. Создайте какую-нибудь похожую таблицу. Главное условие – нужно добавить для каждого пункта несколько дополнительных вариантов выбора.

1. Затем выделите первую строку. Не целиком, а только возможные варианты. Вызовите контекстное меню при помощи правого клика. Выберите пункт «Присвоить имя…».

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

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

1. В этом окне необходимо выбрать пункт «Список».

1. Затем кликнуть на поле «Источник» и выбрать нужный диапазон ячеек.

1. Для сохранения используйте кнопку «OK».

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

В графе «Тип данных» снова указываем «Список». В поле источник укажите следующую формулу.

=ДВССЫЛ(B11)

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

1. Обязательно сохраните все внесенные изменения.

После нажатия на «OK» вы увидите ошибку источника данных. Ничего страшного тут нет. Кликните на «Да».

Дело в том, что в данный момент в клетке «Товар» у нас ничего не выбрано. Как только там будет какое-нибудь слово, нужный список автоматически загрузится.

1. Выберите что-нибудь из предлагаемых товаров.

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

1. Попробуйте указать что-то другое из вашего ассортимента. И вы увидите, что список сразу же изменится. Это очень удобно, поскольку ситуации, когда второе «меню» зависит от первого, бывают очень часто.

Связанные с поиском списки

В вышеописанном методе нужно было создавать дополнительные «имена» для каждого пункта. Но можно сделать и иначе. Представьте, что у вас на листе расположено огромное количество данных. Кроме этого, эта информация может меняться, дополняться или удаляться. Как быть в этом случае? Вручную каждый раз всё настраивать заново – очень плохая затея.

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

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

  Как пронумеровать строки в таблице Эксель

1. Выберите любую клетку. Перейдите на вкладку «Данные» и нажмите на инструмент «Проверка данных».

1. Выбираем нужный тип данных. В источнике указываем необходимый диапазон. Сохраняем при помощи кнопки «OK».

1. Кликните на другую ячейку и повторите описанные ранее действия по вызову такого же окна.

1. Указываем точно такой же тип данных, но в источнике на этот раз указываем следующую формулу.

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

После сохранения появится ошибка о том, что источник пустой. Нажимаем на кнопку «Да».

1. Выберите что-нибудь из предлагаемых вариантов.

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

1. Попробуйте выбрать другой город – список изменится автоматически.

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

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

Мультивыбор

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

1. Сделайте правый клик по названию листа, на котором расположена таблица и будущий выпадающий список.

1. В контекстном меню выберите пункт «Просмотреть код».

1. В появившемся окне вбейте следующий код.

Private Sub Worksheet_Change(ByVal Target As Range)

On Error Resume Next

If Not Intersect(Target, Range(«B11»)) Is Nothing And Target.Cells.Count = 1 Then

• Application.EnableEvents = False
• NewSelectWord = Target
• Application.Undo
• BeforeWord = Target
• If Len(BeforeWord) 0 And BeforeWord NewSelectWord Then
• Target = Target & «,» & NewSelectWord
• Else
• Target = NewSelectWord
• End If
• If Len(NewSelectWord) = 0 Then Target.ClearContents
• Application.EnableEvents = True
• End If
• End Sub

1. Затем вернитесь к книге и выберите что-нибудь.

1. Повторите это действие еще раз.

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

1. Для очистки этой клетки нужно использовать клавишу Delete.

Во всех остальных случаях значения будут только накапливаться.

Заключение

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

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

Если у вас отсутствует вкладка «Разработчик», значит, вы её неправильно активировали или же упустили этот момент из виду.

Видеоинструкция

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

Источник: https://os-helper.ru/excel/kak-sdelat-vypadayushhij-spisok.html

Создание списка в Excel

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

 Списки для сортировки и заполнения

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

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

Изменять параметры такого списка можно в меню списки, которое открывается в основных параметрах Excel. Находим кнопку «Изменить списки» и нажимаем ее.

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

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

Создание выпадающих списков

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

Создание выпадающего списка с источником на этом же листе

Чтобы пользоваться выпадающим списком нужно сначала этот список создать. Создаем список на этом же (открытом) листе и проверяем правильность написания его составляющих. Затем выделяем ячейку или несколько ячеек, куда будем вставлять эти данные, открываем окошко «Проверка вводимых значений», которое открывается по прохождении следующего пути: данные/работа с данными/проверка данных.

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

Создание выпадающего списка с источником на другом листе

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

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

Выполняем это так: создаем список данных на одном листе, затем выделяем его и присваиваем ему имя, кликаем последовательно вкладка формулы/присвоить имя (в разделе определенные имена), в открывшемся окне задаем имя списка. Если, предварительно мы не выделили список, то задаем диапазон его ячеек.

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

Как скрыть лист с источником списка

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

Таким образом Excel представляет большие возможности по созданию списков, даже по сравнению с текстовым процессором Word Office.

Источник: https://compone.ru/sozdanie-spiska-v-excel

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

С помощью настраиваемых списков в Microsoft Excel на Mac очень легко и удобно вводить данные. Один из вариантов – раскрывающиеся списки. Создавать их можно с помощью нескольких простых шагов.

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

Как добавить список в ячейку

Откройте документ в Microsoft Excel, в который хотите добавить раскрывающийся список.

1) Введите пункты списка на листе. Вы можете использовать чистый лист или пустое место на заполненном.

• 2) Выберите ячейку, из которой будет раскрываться список.
• 3) На панели сверху выберите вкладку Данные и кликните Проверка данных.
• 4) Далее в строке Разрешить выберите Список.
• 5) Нажмите на поле Источник и выделите ячейки со всеми пунктами списка.

1. 6) Нажмите клавишу Enter.
2. 7) Нажмите OK.
3. Всплывшее окно исчезнет, и вы увидите одну ячейку с раскрывающимся списком.

Как добавить список в колонку или строку

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

Затем проделайте шаги, перечисленные выше, начиная со второго с вкладки Данные и нажатия кнопки Проверка данных.

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

Источник: https://IT-here.ru/instruktsii/kak-sozdavat-raskryvayushhiesya-spiski-v-excel-na-mac/

Динамические выпадающие списки Excel

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

Говоря о динамических выпадающих списках, подразумевается 2 варианта:

Растущий выпадающий список

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

Рассмотрим пример создания такого списка.

На любом листе размещается перечень будущих пунктов. Далее стандартным образом настраивается правило проверки данных: Вкладка «Данные» -> Область «Работа с данными» -> Кнопка «Проверка данных» -> Тип данных «Список». Для источника необходимо создать следующую формулу:

=СМЕЩ($A$1;;;СЧЁТЗ($A:$A)), где:

• $A$1 – ячейка со значением первого пункта списка;
• $A:$A – столбец с перечнем всех пунктов списка.

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

Связанные (зависимые) выпадающие списки

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

В качестве примера рассмотрим создание зависимых выпадающих списков с перечнем товаров. Сначала определим категории имеющейся продукции (это будет главный список):

• Бытовая_техника (пробел в названии специально заменен нижним подчеркиванием, т.к. диспетчер имен, который будет использован далее, не принимает символ пробела);
• Электроника;
• Мебель.

1. Теперь для каждого значения категории создается свой список, после чего ему присваивается имя в диспетчере имен (вкладка «Формулы» -> Область «Определенные имена») согласно этому значению.
2. 
3. В завершении задаем для ячеек созданные нами связанные выпадающие списки с помощью проверки данных (вкладка «Данные» -> Область «Работа с данными»).
4. Для главного списка источником достаточно указать ссылку на диапазон, а для зависимых указывается формула:
5. =ДВССЫЛ($A$1), где:

• $A$1 – ячейка с главным списком.

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

Источник: http://office-menu.ru/uroki-excel/16-vopros-otvet/71-dinamicheskie-vypadayushchie-spiski-excel

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

• Ранее в публикациях рассказывалось о том, как создается выпадающий список в ячейках для упрощения внесения данных.
  Ссылка на описания метода создания связанного выпадающего списка ниже:
• Как сделать связанный выпадающий список в «Эксель», зависящий от значения в соседней ячейке.
• В данной публикации описана процедура создания выпадающих списков, которые записывают в ячейки по нескольку значений.

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

Для этого необходимо:

• Войти во вкладку «Данные»;
• Выбрать опцию «Проверка данных»;
• Выбрать «Список»;
• Указать диапазон, из которого будет выбираться выпадающий список или создать список прямо в появившемся поле через знак «;».

После этой процедуры следует записать макрос в документ

Для записи макроса следует:

• Открыть вкладку «Разработчик» ( Если вкладка отключена, включите ее в разделе Файл=> Параметры=> Настройка Ленты);

• Во вкладке «Разработчик» выбрать кнопку «Просмотр кода»;
• В открывшееся окно записать макрос;

Давайте рассмотрим несколько макросов с выпадающими списками.

Первый макрос со смещением списка в сторону (горизонтально)

Текст макроса:

Private Sub Worksheet_Change(ByVal Target As Range)

On Error Resume Next
If Not Intersect(Target, Range(«B2:B10»)) Is Nothing And Target.Cells.Count = 1 Then
Application.EnableEvents = False
If Len(Target.Offset(0, 1)) = 0 Then
Target.Offset(0, 1) = Target
Else
Target.End(xlToRight).Offset(0, 1) = Target
End If
Target.ClearContents
Application.EnableEvents = True
End If
End Sub
Необходимо обратить внимание, что в строке :

If Not Intersect(Target, Range(«B1:B10»)) Is Nothing And Target.Cells.Count = 1 Then

Значения («B1:B10»)— это диапазон в пределах которого будет работать выпадающий список.
Аналогичным образом можно создать выпадающий список со смещением вниз и выпадающий список, записывающий в ячейку несколько значений через знак табуляции или пробел.

Источник: http://RuExcel.ru/421-2/

Создаем в Excel выпадающий список с автоматическим добавлением отсутствующих элементов | Сводные таблицы Excel 2010

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

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

Шаг 1. Создаем именованный диапазон

Сначала создадим именованный диапазон, указывающий на заполненные именами ячейки в столбце А – сколько бы имен в списке ни находилось. Для этого жмем на вкладке Формулы (Formulas) кнопку Диспетчер имен (Name Manager) и затем Создать (New).

Вводим имя диапазона (допустим, People) и в строку Ссылка (Reference) вводим следующую формулу: =СМЕЩ(Лист1!$A$1;0;0;СЧЁТЗ(Лист1!$A$1:$A$24);1). в английской версии Excel это будет: =OFFSET(Лист1!$A$1;0;0;COUNTA(Лист1!$A$1:$A$24);1). Эта формула ссылается на все заполненные ячейки в столбце А, начиная с А1 и вниз до конца – до последнего имени.

Шаг 2. Создаем выпадающий список в ячейке

Выделяем ячейку D2 и жмем на вкладке Данные (Data) кнопку Проверка данных (Data Validation).

Далее выбираем из выпадающего списка Тип данных (Allow) позицию Список (List) и вводим в строку Источник (Source) ссылку на созданный на шаге 1 именованный диапазон (не забудьте перед именем диапазона поставить знак равенства!):

Не забудьте перед именем диапазона поставить знак равенства!

Чтобы Excel позволил нам в будущем ввести в список и новые имена, снимем галочки на вкладках Сообщение для ввода (Input Message) и Сообщение об ошибке (Error Alert) и нажмем ОК.

Теперь у нас есть выпадающий список в ячейке D2.

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

Шаг 3. Добавляем простой макрос

Щелкаем правой кнопкой мыши по ярлычку нашего листа и выбираем Исходный текст (View Source). Откроется модуль листа в редакторе Visual Basic, куда надо ввести такой код:

1 2 3 4 5 6 7 8 9 10 11 12 13

Private Sub Worksheet_Change(ByVal Target As Range) Dim lReply As Long If Target.Cells.Count > 1 Then Exit Sub If Target.Address = «$D$2» Then If IsEmpty(Target) Then Exit Sub If WorksheetFunction.CountIf(Range(«People»), Target) = 0 Then lReply = MsgBox(«Добавить введенное имя » & Target & » в выпадающий список?», vbYesNo + vbQuestion) If lReply = vbYes Then Range(«People»).Cells(Range(«People»).Rows.Count + 1, 1) = Target End If End If End If End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
Dim lReply As Long If Target.Cells.Count > 1 Then Exit Sub If Target.Address = «$D$2» Then If IsEmpty(Target) Then Exit Sub If WorksheetFunction.

CountIf(Range(«People»), Target) = 0 Then lReply = MsgBox(«Добавить введенное имя » & Target & » в выпадающий список?», vbYesNo + vbQuestion) If lReply = vbYes Then Range(«People»).Cells(Range(«People»).Rows.

Count + 1, 1) = Target End If End If End If
End Sub

Если ваш выпадающий список находится не в ячейке D2 или вы назвали диапазон с именами не People, а как-то еще, то подправьте эти параметры в макросе на свои. Всё! Теперь при попытке ввести новое имя в ячейку D2 Excel будет спрашивать и при утвердительном ответе пользователя автоматически добавлять новое имя к списку в столбце А и в выпадающий список в ячейку D2.

Excel будет спрашивать и при утвердительном ответе пользователя автоматически добавлять новое имя

Источник: http://pivot-table.ru/sozdaem-v-excel-vypadayushhij-spisok-s-avtomaticheskim-dobavleniem-otsutstvuyushhix-elementov.html

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

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

 

Выпадающий список как валидация (вариант 1):

Будем использовать поле с валидацией. Шаги такие:

1. Выделяем одну ячейку на листе, куда следует поместить выпадающий список с элементами.
2. Переходим на вкладку «DATA» и нажимаем кнопку «Data Validation» -> «Data Validation…».
3. Будем работать с первой вкладкой «Settings», предназначение остальных вкладок можно узнать в статье. Из списка «Allow» выбираем значение «List», в появившемся поле «Source» указываем диапазон всех возможных вариантов (список значений должен размещаться в 1-ом столбце или 1-ой строке), проще всего это сделать нажав пиктограму  и обычным способом на листе выделив смежные ячейки.
4. Убедитесь, что переключатель «In-cell dropdown» активен, нажимаем «OK».

Как это выглядит:

Особенности такого варианта:

• Выпадающий список вписывается по размерах в одну ячейку.
• Если ячейки не выделена то визуального различия с другими обычными ячейками нет.
• Поведение списка при попытке ввода несуществующего значения можно задать самостоятельно на вкладке «Error Alert» меню «Data Validation…». Можно не показывать ошибок, можно предупреждать про неверные значения или совсем запретить их ввод.
• Формат файла не меняется и документ можно сохранять с расширением .xlsx.
• Не используется связная ячейка для результата, в текущую ячейку записывается именно то, что выбрали из списка.

 

Выпадающий список из Form Controls (вариант 2):

Больше возможностей можно получить создав список из элементов Form Controls, для этого нужна вкладка «Developer» («Разработчик»). Этот вариант отличается от предыдущего результатом работы.

Шаги для создания выпадающего списка:

1. На вкладке «Developer» («Разработчик») в блоке инструментов «Controls» нажимаем кнопку «Insert».
2. В появившемся окне из верхней части с шапкой «Form Controls» выбираем элемент «Combo Box (Form Control)».
3. Теперь на листе левой кнопкой мыши рисуем прямоугольник такого размера, как должен быть наш выпадающий список. Сам графический элемент после этого будет готов. Теперь укажем набор возможных значений для него.
4. На только что созданном элементе нажимаем правой кнопкой мыши, выбираем последний пункт меню «Format Control» и переходим на последнюю вкладку «Control», если она не открылась по-умолчанию.
5. В поле «Input range» нужно указать диапазон смежных ячеек с вариантами выбора для списка, в поле «Cell link» указываем одну связную ячейку (зачем это написано ниже) и «Drop down lines» это количество видимых элементов при нажатии на выпадающий список.
6. Когда все поля указаны нажимаем «OK» и размещаем наш элемент в нужное место на листе с правильными размерами.

Как это выглядит:

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

Существует связная ячейка, мы указывали её в шаге 5 при создании выше, куда записывается текущий номер значения из списка (в примере выше: январь — 1, февраль — 2…). Теперь когда мы выбираем другое значение из списка в связной ячейке меняется активный номер, и мы можем реагировать на эти изменения формулами.

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

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

Особенности этого варианта:

• Выпадающий список отображается на отдельном слое поверх листа и закрывает видимость ячеек.
• Внешний вид элемента нельзя изменить, меняются только размеры.
• Не поддерживается ручной ввод значений, выбрать можно только значения из списка либо сбросить полностью активное значение.
• Используется связная ячейка для вывода результата. Выводится порядковый номер элемента в списке (последовательность начинается с 1) а не выбранное строковое значение.
• Элементу можно назначить 1 обработчик в виде макроса. Он сработает при выборе другого значения из списка.
• Если используется макрос для элемента, то файл должен сохраняться с расширением .xlsm.

Выпадающий список из ActiveX Controls (вариант 3):

Тип элемента, у которого много настроек и много возможностей. Детально про события читайте в статье, про разницу элементов Form и ActiveX другая статья.

Шаги создания:

1. На вкладке «Developer» («Разработчик») в блоке инструментов «Controls» нажимаем кнопку «Insert».
2. В появившемся окне из верхней части с шапкой «ActiveX Controls» выбираем элемент «Combo Box (ActiveX Control).
3. Теперь на листе левой кнопкой мыши рисуем прямоугольник такого размера, как должен быть наш выпадающий список. Сам графический элемент после этого будет готов. Теперь укажем набор возможных значений для него.
4. Убеждаемся, что переключатель «Design Mode» активирован.
5. Нажимаем кнопку «Properties» на той же панели управления.
6. Отметим несколько полезных свойств, которые можно изменить на своё усмотрение:
   • BackColor — цвет фона элемента.
   • BackStyle — режим фона:
     • fmBackStyleTransparent — фон прозрачный, видно ячейки позади.
     • fmBackStyleOpaque — цвет фона указанный свойством BackColor.
   • BorderColor — цвет рамки вокруг элемента.
   • BorderStyle — режим рамки элемента:
     • fmBorderStyleNone — рамка отсутствует.
     • fmBorderStyleSingle — рамка вокруг элемента в 1 пиксель.
   • Font — шрифт значений и слов.
   • ForeColor — цвет текста.
   • LinkedCell — связная ячейка для вывода активного значения на лист.
   • ListFillRange — диапазон смежных ячеек с возможными вариантами выбора в списке.
   • MatchEntry — действие при ручном вводе символов:
     • fmMatchEntryFirstLetter — каждый введённый символ рассматривается как первая буква возможных значений и подбираются вероятные значения автоматически.
     • fmMatchEntryComplete — оценивается полное совпадение введённого текста с вариантами выбора и предлагается наиболее вероятное значение.
     • fmMatchEntryNone — совпадения со всеми значениями не проводится.
   • ShowDropButtonWhen — когда отображается справа кнопка выпадающего списка:
     • fmShowDropButtonWhenNever — никогда не отображается.
     • fmShowDropButtonWhenFocus — только когда фокус приложения расположен на элементе графического интерфейса.
     • fmShowDropButtonWhenAlways — отображается всегда, по-умолчанию.
   • SpecialEffect — различные эффекты (выпуклость, выдавливание…)
   • Style — режим ввода произвольных значений:
     • fmStyleDropDownCombo — комбинированный вариант, когда пользователь может вручную печатать в поле значение.
     • fmStyleDropDownList — вручную вводить значения нельзя, можно выбрать только из списка.
7. Много разных свойств можно выбрать по вкусу, когда настройки закончили закрываем окно «Properties», выключаем режим «Design Mode» и уже можем работать с выпадающим списком. 

Если установить свойства «Linked Cell» вместе с «ListFillRange» получится выпадающий список аналогичный варианту 2.

Как это выглядит:

Особенности этого варианта:

• Возможность управления внешним видом элемента.
• Выпадающий список отображается на отдельном слое поверх листа и закрывает видимость ячеек.
• Можно вводить пользовательские символы при соответствующей установке свойства «Style».
• Доступно 15 обработчиков на разные события выпадающего списка. Детальнее в статье.
• Не обязательно указывать связную ячейку, вместо неё можно использовать обработчики событий.
• При использовании макросов формат файла при сохранении должен быть .xlsm.

Источник: https://sahe.ucoz.ru/publ/vba/create_combobox/4-1-0-32