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

Как сделать списки в Excel 2007

Для примера я создал список городов Московской области. Выделяем список и создаем именованный диапазон. Для этого после щелчка правой кнопки мыши выбираем в контекстном меню «Имя диапазона».

Задаем имя «Город_М_О» и жмем «ОК».

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


В появившемся окне выбираем тип данных «Список» и в поле «Источник» вводим «=Город_М_О», то есть заданное нами имя диапазона, который содержит список.


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


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

Как это сделать в Excel 2003

Здесь, чтобы присвоить имя диапазону нам потребуется зайти в меню «Вставка»


И окно присвоения имени выглядит немного по-другому.


Так же переходим в нужную нам ячейку и в меню «Данные» выбираем «Проверка». А открывшееся окно будет таким же, как и в Excel 2007.
Покоряйте Excel и до новых встреч!

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

И так есть несколько вариантов, как сделать выпадающие списки для работы в Microsoft Office Excel.

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

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

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

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

Введите данные в ячейки A1:A10, которые будут выступать в качестве источника для списка. В нашем примере мы ввели цифры, они появятся в выпадающем списке. Выделите ячейку (Например, E5), которая будет содержать в раскрывающийся список. Выберите меню «Данные» -> «Проверка данных», чтобы открыть диалоговое окно «Проверка вводимых значений».

3. На вкладке «Параметры» выберите параметр «Список» в раскрывающемся меню. Убедитесь, что установлены нужные флажки.

4. Затем, нажмите на кнопку. Появится следующее диалоговое окно.

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

6. Выпадающий список в Excel будет создан.

Если ваш список короткий, вы можете ввести элементы непосредственно в «Источник» на вкладке «Настройка» в диалоговом окне «Проверка вводимых значений». Отделите каждый пункт списка разделителями, указанными в региональных настройках.
Если список должен располагаться на другом листе, вы можете воспользоваться параметром «= List» перед указанием диапазона данных.
Как создать выпадающий список в Экселе на основе данных из перечня

Представим, что у нас есть перечень фруктов:
Как сделать выпадающий список в Excel

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

Перейти на вкладку “Данные” => раздел “Работа с данными” на панели инструментов => выбираем пункт “Проверка данных”.

В поле “Источник” ввести диапазон названий фруктов =$A$2:$A$6 или просто поставить курсор мыши в поле ввода значений “Источник” и затем мышкой выбрать диапазон данных:

Если вы хотите создать выпадающие списки в нескольких ячейках за раз, то выберите все ячейки, в которых вы хотите их создать, а затем выполните указанные выше действия. Важно убедиться, что ссылки на ячейки являются абсолютными (например, $A$2), а не относительными (например, A2 или A$2 или $A2).

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

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

Для этого нам потребуется:
Выбрать ячейку, в которой мы хотим создать выпадающий список;
Перейти на вкладку “Данные” => раздел “Работа с данными” на панели инструментов =>
Проверка данных в Excel

Во всплывающем окне “Проверка вводимых значений” на вкладке “Параметры” в типе данных выбрать “Список”:
Проверка вводимых значений в Excel

В поле “Источник” ввести значение “Да; Нет”.
Нажимаем “ОК”
Да - Нет

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

Если вы хотите одновременно создать выпадающий список в нескольких ячейках – выделите нужные ячейки и следуйте инструкциям выше.
Как создать раскрывающийся список в Эксель с помощью функции СМЕЩ

Наряду со способами описанными выше, вы также можете использовать формулу СМЕЩ для создания выпадающих списков.

Например, у нас есть список с перечнем фруктов:

Для того чтобы сделать выпадающий список с помощью формулы СМЕЩ необходимо сделать следующее:
Выбрать ячейку, в которой мы хотим создать выпадающий список;
Перейти на вкладку “Данные” => раздел “Работа с данными” на панели инструментов => выбрать пункт “Проверка данных”:
Проверка данных в Excel

Во всплывающем окне “Проверка вводимых значений” на вкладке “Параметры” в типе данных выбрать “Список”:
Проверка вводимых значений в Excel

В поле “Источник” ввести формулу: =СМЕЩ(A$2$;0;0;5)
Нажать “ОК”

Система создаст выпадающий список с перечнем фруктов.
Как эта формула работает?

На примере выше мы использовали формулу =СМЕЩ(ссылка;смещ_по_строкам;смещ_по_столбцам;[высота];[ширина]).
Эта функция содержит в себе пять аргументов. В аргументе “ссылка” (в примере $A$2) указывается с какой ячейки начинать смещение. В аргументах “смещ_по_строкам” и “смещ_по_столбцам” (в примере указано значение “0”) – на какое количество строк/столбцов нужно смещаться для отображения данных.

В аргументе “[высота]” указано значение “5”, которое обозначает высоту диапазона ячеек. Аргумент “[ширина]” мы не указываем, так как в нашем примере диапазон состоит из одной колонки.
Используя эту формулу, система возвращает вам в качестве данных для выпадающего списка диапазон ячеек, начинающийся с ячейки $A$2, состоящий из 5 ячеек.

Как сделать выпадающий список в Excel с подстановкой данных (с использованием функции СМЕЩ)

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

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

Перейти на вкладку “Данные” => раздел “Работа с данными” на панели инструментов => выбрать пункт “Проверка данных”;
Во всплывающем окне “Проверка вводимых значений” на вкладке “Параметры” в типе данных выбрать “Список”;
В поле “Источник” ввести формулу: =СМЕЩ(A$2$;0;0;СЧЕТЕСЛИ($A$2:$A$100;”<>”))
Нажать “ОК”

В этой формуле, в аргументе “[высота]” мы указываем в качестве аргумента, обозначающего высоту списка с данными – формулу СЧЕТЕСЛИ, которая рассчитывает в заданном диапазоне A2:A100 количество не пустых ячеек.

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

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

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

На панели инструментов нажимаем пункт “Форматировать как таблицу”:

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

Нажав клавишу “ОК” во всплывающем окне, подтверждаем выбранный диапазон ячеек:

Присваиваем имя таблице в правой верхней ячейке над колонкой “А”:

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

Перейти на вкладку “Данные” => раздел “Работа с данными” на панели инструментов => выбрать пункт “Проверка данных”:

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

В поле источник указываем =”название вашей таблицы”. В нашем случае мы ее назвали “Список”:
Поле источник автоматическая подстановка данных в выпадающий список Эксель

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

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

Таблица автоматически расширит свой диапазон данных. Выпадающий список соответственно пополнится новым значением из таблицы:
Автоматическая подстановка данных в выпадающий список эксель

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

В Excel есть возможность копировать созданные выпадающие списки. Например, в ячейке А1 у нас есть выпадающий список, который мы хотим скопировать в диапазон ячеек А2:А6.

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

выделите ячейки в диапазоне А2:А6, в которые вы хотите вставить выпадающий список;

нажмите сочетание клавиш на клавиатуре CTRL+V.
Так, вы скопируете выпадающий список, сохранив исходный формат списка (цвет, шрифт и.т.д). Если вы хотите скопировать/вставить выпадающий список без сохранения формата, то:
нажмите левой клавишей мыши на ячейку с выпадающим списком, который вы хотите скопировать;

нажмите сочетание клавиш на клавиатуре CTRL+C;
выберите ячейку, в которую вы хотите вставить выпадающий список;
нажмите правую кнопку мыши => вызовите выпадающее меню и нажмите “Специальная вставка”;
выпадающий список в excel

В появившемся окне в разделе “Вставить” выберите пункт “условия на значения”:

Нажмите “ОК”
После этого, Эксель скопирует только данные выпадающего списка, не сохраняя форматирование исходной ячейки.
Как выделить все ячейки, содержащие выпадающий список в Экселе

Иногда, сложно понять, какое количество ячеек в файле Excel содержат выпадающие списки. Есть простой способ отобразить их. Для этого:

Нажмите на вкладку “Главная” на Панели инструментов;
Нажмите “Найти и выделить” и выберите пункт “Выделить группу ячеек”:

В диалоговом окне выберите пункт “Проверка данных”. В этом поле есть возможность выбрать пункты “Всех” и “Этих же”. “Всех” позволит выделить все выпадающие списки на листе. Пункт “этих же” покажет выпадающие списки схожие по содержанию данных в выпадающем меню. В нашем случае мы выбираем “всех”:
Выпадающий список в Excel. Как найти все списки

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

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

Иногда нам требуется создать несколько выпадающих списков, причем, таким образом, чтобы, выбирая значения из первого списка, Excel определял какие данные отобразить во втором выпадающем списке.
Предположим, что у нас есть списки городов двух стран Россия и США:

Для создания зависимого выпадающего списка нам потребуется:
Создать два именованных диапазона для ячеек “A2:A5” с именем “Россия” и для ячеек “B2:B5” с названием “США”. Для этого нам нужно выделить весь диапазон данных для выпадающих списков:
зависимый выпадающий список в Excel

Перейти на вкладку “Формулы” => кликнуть в разделе “Определенные имена” на пункт “Создать из выделенного”:
Зависимые выпадающие списки в Excel

Во всплывающем окне “Создание имен из выделенного диапазона” поставьте галочку в пункт “в строке выше”. Сделав это, Excel создаст два именованных диапазона “Россия” и “США” со списками городов:
зависимый-выпадающий-список-в-excel

Нажмите “ОК”
В ячейке “D2” создайте выпадающий список для выбора стран “Россия” или “США”. Так, мы создадим первый выпадающий список, в котором пользователь сможет выбрать одну из двух стран.

Теперь, для создания зависимого выпадающего списка:
Выделите ячейку E2 (или любую другую ячейку, в которой вы хотите сделать зависимый выпадающий список);
Кликните по вкладке “Данные” => “Проверка данных”;
Во всплывающем окне “Проверка вводимых значений” на вкладке “Параметры” в типе данных выберите “Список”:
Проверка вводимых значений в Excel

Нажмите “ОК”

Теперь, если вы выберите в первом выпадающем списке страну “Россия”, то во втором выпадающем списке появятся только те города, которые относятся к этой стране. Также и в случае, когда выбираете “США” из первого выпадающего списка.

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

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

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

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

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

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

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

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

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

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

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

Стандартный

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

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

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

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

1. Затем закройте это окно.

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

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

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

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

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

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

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

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

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. Кликните на этот объект.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Target = NewSelectWord

If Len(NewSelectWord) = 0 Then Target.ClearContents

Application.EnableEvents = True

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

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

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

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

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

Заключение

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

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

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

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

Выпадающий список можно создать с помощью

В этой статье создадим Выпадающий список с помощью () с типом данных Список .

Выпадающий список можно сформировать по разному.

А. Простейший выпадающий список - ввод элементов списка непосредственно в поле Источник

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

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

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

Недостатки этого подхода: элементы списка легко потерять (например, удалив строку или столбец, содержащие ячейку B 1 ); не удобно вводить большое количество элементов. Подход годится для маленьких (3-5 значений) неизменных списков.
Преимущество : быстрота создания списка.

Б. Ввод элементов списка в диапазон (на том же листе, что и выпадающий список)

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

Предположим, что элементы списка шт;кг;кв.м;куб.м введены в ячейки диапазона A 1: A 4 , тогда поле Источник будет содержать =лист1!$A$1:$A$4

Преимущество : наглядность перечня элементов и простота его модификации. Подход годится для редко изменяющихся списков.
Недостатки : если добавляются новые элементы, то приходится вручную изменять ссылку на диапазон. Правда, в качестве источника можно определить сразу более широкий диапазон, например, A 1: A 100 . Но, тогда выпадающий список может содержать пустые строки (если, например, часть элементов была удалена или список только что был создан). Чтобы пустые строки исчезли необходимо сохранить файл.

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

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

B. Ввод элементов списка в диапазон (на любом листе)

Ввод элементов списка в диапазон ячеек, находящегося в другой книге

Если необходимо перенести диапазон с элементами выпадающего списка в другую книгу (например, в книгу Источник. xlsx ), то нужно сделать следующее:

• в книге Источник. xlsx создайте необходимый перечень элементов;
• в книге Источник.xlsx диапазону ячеек содержащему перечень элементов присвойте , например СписокВнеш;
• откройте книгу, в которой предполагается разместить ячейки с выпадающим списком;
• выделите нужный диапазон ячеек, вызовите инструмент , в поле Источник укажите =ДВССЫЛ("[Источник.xlsx]лист1!СписокВнеш") ;

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

Если нет желания присваивать имя диапазону в файле Источник.xlsx , то формулу нужно изменить на =ДВССЫЛ("[Источник.xlsx]лист1!$A$1:$A$4")

СОВЕТ:
Если на листе много ячеек с правилами Проверки данных , то можно использовать инструмент (Главная/ Найти и выделить/ Выделение группы ячеек ). Опция Проверка данных этого инструмента позволяет выделить ячейки, для которых проводится проверка допустимости данных (заданная с помощью команды Данные/ Работа с данными/ Проверка данных ). При выборе переключателя Всех будут выделены все такие ячейки. При выборе опции Этих же выделяются только те ячейки, для которых установлены те же правила проверки данных, что и для активной ячейки.

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

В EXCEL не предусмотрена регулировка размера шрифта Выпадающего списка . При большом количестве элементов имеет смысл список элементов и использовать дополнительную классификацию элементов (т.е. один выпадающий список разбить на 2 и более).

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