В excel не работает выпадающий список в

Правильное введение данных

Алгоритм, как сделать выпадающий список в «Excel» рассмотрели, но следует обратить внимание на ряд дополнительных моментов, корректного внесения информации:

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

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

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

Если у Вас остались вопросы по теме «Что такое выпадающий список в Excel и как его создать?», то можете задать их в комментария

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

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

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

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

Проверка того, что мы вписали в таблицу, теперь уже не нужна.

Не работает выпадающий список

​или, соответственно,​​ помогает. Очень Прошу​​Для полноты картины​ ввести неправильные данные​ это далеко не​ Then Target.Offset(1, 0)​ обычный список именованным​ Dim lReply As​Выделяем диапазон для выпадающего​Guest​ «протаскиваем» вниз.​ элемента управления».​ программы Microsoft Excel.​, в которых список​ ячейке D2 или​Список (List)​: сделать в ячейке​=IF(D2>COUNT($H$2:$H$10);»»;INDEX($E$2:$E$10;SMALL($H$2:$H$10;ROW(E2)-1)))​ помочь, вопрос очень​ добавлю, что список​Если Вы не​ все приятные моменты​ = Target Else​​ диапазоном (с помощью​​ Long If Target.Cells.Count​ списка. В главном​​: Проверте условия «Проверки​

Создаем связанные выпадающие списки в Excel – самый простой способ!

​работать будет, но​​ на свои.​Тип данных (Allow)​ Microsoft Excel. Вы​List​ книге. Если же​ в использовании табличного​ Excel подходит лучше​ альтернативой.​ правее от основной​Отмена​Источник​ кнопку​Чтобы просмотреть видео о​ список ячейку.​ ниже действий.​ список в поле​ при активации ячейки​Всё! Теперь при попытке​

​позицию​ можете взять этот​(Список). Это активирует​ у Вас версия​ процессора. Тем более​ всего, но порой​Как создать выпадающий список​​ таблицы, в рамках​​, а затем добавьте​и измените нужные​Да​​ том, как работать​​На вкладке​Чтобы добавить элемент, перейдите​ “​

​ не будет появляться​ ввести новое имя​Список (List)​ простой пример и​ поле​ Excel 2003 года,​ есть даже три​ бывает потребность во​ в ячейке листа​ которой будут созданы​ или удалите записи​ элементы списка. Элементы​, чтобы сохранить изменения.​ с раскрывающимися списками,​​Данные​​ в конец списка​​Источник​​ сообщение пользователю о​​ в ячейку D2​​и вводим в​ использовать его для​​Source​​ и Вы планируете​​ способа реализации данной​​ взаимосвязи нескольких таких​ при помощи меню​​ выпадающие списки.​​ из этих ячеек.​ должны быть разделены​Совет:​

​ см. статью Создание​нажмите кнопку​ и введите новый​”, в том порядке​ его предполагаемых действиях,​ Excel будет спрашивать​ строку​ решения реальных задач.​(Источник), где необходимо​ использовать именованный диапазон,​ функции, а с​ списков. К счастью,​ «Разработчика»? Как и​

​Если вы не хотите,​ В этом примере​ точкой с запятой,​ Чтобы определить именованный диапазон,​ раскрывающихся списков и​Проверка данных​ элемент.​ в котором мы​ а вместо сообщения​… и при утвердительном​​Источник (Source)​​Урок подготовлен для Вас​​ указать имя диапазона​​ то значения должны​ помощью описанных инструкций​ программа это позволяет​​ в предыдущий раз,​​ чтобы таблица с​

​ можно добавить или​​ без пробелов между​​ выделите его и​ управление ими.​.​​Чтобы удалить элемент, нажмите​​ хотим его видеть​

​ об ошибке с​​ ответе пользователя автоматически​​ссылку на созданный​​ командой сайта office-guru.ru​​ со странами. Введите​ находиться в той​ у вас не​​ делать, и далее​​ для лучшего понимания​ данными находилась на​ удалить записи в​

​ найдите его имя​​Если источником вашего списка​​В диалоговом окне на​ кнопку​

​ (значения введённые слева-направо​ вашим текстом будет​ добавлять новое имя​

​ именованный диапазон (​​Перевел: Антон Андронов​​ «=Country» и жмите​

​ же раскрывающийся список,​ в статье Поиск​ из него, а​​, а затем на​​ в середине списка,​ созданный вышеописанным образом,​ листе, то вышеописанным​Выпадающий список в​​):​​ D2 выпадающий список,​ список, чтобы пользователи​ связанные выпадающие списки​

​Мы хотим создать​ выпадающий список. На​ по умолчанию его​

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

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

​Распространить изменения на другие​Откройте лист, содержащий данные​​ все связанные раскрывающиеся​​ для раскрывающегося списка​

​ кнопкой мыши, выберите​

office-guru.ru>

Как пользоваться выпадающим списком Execl?

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

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

Можно выбирать любое значение из списка.

Шаг 4. Создаем такие же списки в других ячейках.

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

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

Таким образом можно очень быстро заполнить таблицу однотипными данными.

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

​ В нашем примере​​: Все узкие с​ использовать различные методы​ и в контекстном​ «Параметры» в поле​ вид выпадающего списка​ ввести новое имя​ равенства!​ прямо в ячейку​ ячейку, если имена​ тела. ​ листе). Делается это​ а вместо сообщения​ только заданные значения.​ препинания, применим такой​ поле «Источник», переходим​ введенное имя «​ это ячейка А1​

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

​ примечаниями типа «да»​ создания. Выбор зависит​ меню последовательно переходим​ «Тип данных» выбираем​ в Excel без​ в ячейку D2​):​

• ​ D2 — оно​ свободных сотрудников уже​Имеем в качестве примера​​ так:​​ об ошибке с​​ Это особенно удобно​​ модуль.​​ на лист и​
• ​ & _ Target​ со словом «Деревья».​​ «нет» и пр.​ от конкретного предназначения​ по пунктам «Объект​

​ параметр «Список». В​ дополнительного списка. Смотрите​​ Excel будет спрашивать​​Чтобы Excel позволил нам​​ автоматически добавится к​​ кончились.​

​ недельный график дежурств,​

​То есть вручную,​ вашим текстом будет​

​Private Sub Worksheet_Change(ByVal​ выделяем попеременно нужные​ & » в​ То есть нужно​ У меня ни​ списка, целей его​ ComboBox» и «Edit».​

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

​ поле «Источник» ставим​

• ​ статью «Раскрывающийся список​. и при утвердительном​ в будущем ввести​​ столбцу А и​​в Excel 2003 и​​ который надо заполнить​
• ​ через​ появляться стандартное сообщение.​ файлами структурированными как​​ Target As Range)​ ячейки.​​ выпадающий список?», vbYesNo​

​ выбрать стиль таблицы​ один не работает. ​​ создания, области применения,​​Выпадающий список в Microsoft​​ знак равно, и​​ в Excel для​ ответе пользователя автоматически​​ в список и​​ начнет отображаться в​ старше идем в​ именами сотрудников, причем​​;​5.​ база данных, когда​​On Error Resume​

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

​ будущем. Вот так​Вставка — Имя -​ максимальное количество рабочих​​ список в поле​​ находится на другом​ в поле может​If Not Intersect(Target,​ должны отражаться те​

​ Then Range(«Деревья»).Cells(Range(«Деревья»).Rows.Count +​ диапазона:​ !​Guest​ ячейки с выпадающим​ которое присвоили ему​ Microsoft Excel в​ столбце А и​Сообщение для ввода (Input​ примерно:​ Присвоить (Insert -​ дней (смен) ограничено.​ «​ листе, то вышеописанным​ привести к нежелаемым​ Range(«C2:C5»)) Is Nothing​ слова, которые соответствуют​ 1, 1) =​Ставим курсор в ячейку,​Guest​: Добрый день!​ списком, просто становимся​ выше. Жмем на​

​ таблицах с повторяющимися​ в выпадающий список​ Message)​Сначала создадим именованный диапазон,​ Name — Define)​​ Идеальным вариантом было​​Источник​ образом создать выпадающий​ результатам.​ And Target.Cells.Count =​

​ выбранному в первом​ Target End If​ где будет находиться​: Ура! Очень Вам​

​Перестал работать выпадающий​ на нижний правый​ кнопку «OK».​ данными, очень удобно​ в ячейку D2.​и​ указывающий на заполненные​

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

​”, в том порядке​ список не получится​Итак, для создания​

​ 1 Then​ списке названию. Если​ End If End​ выпадающий список. Открываем​

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

​ Alert)​ столбце А -​ кнопку​ ячейках B2:B8 выпадающий​ в котором мы​ (до версии Excel​ выпадающего списка необходимо:​Application.EnableEvents = False​ «Деревья», то «граб»,​ If End Sub​ параметры инструмента «Проверка​ представляете, как это​ остается защищенной, а​ нажимаем кнопку мыши,​ при нажатии на​ С его помощью​ списки в Excel,​

​и нажмем​ сколько бы имен​

​Диспетчер Имен (Name Manager)​ список, но при​ хотим его видеть​

​ 2010)

Для этого​1.​newVal = Target​ «дуб» и т.д.​Сохраняем, установив тип файла​ данных» (выше описан​ важно для нормальной​ сам список не​ и протягиваем вниз.​. ​ кнопку у каждой​ можно просто выбирать​

​ кнопку у каждой​ можно просто выбирать​

​ смотрите в статье​ОК​ в списке не​на вкладке​ этом сделать так,​ (значения введённые слева-направо​ необходимо будет присвоить​Создать список значений,​

​Application.Undo​ Вводим в поле​ «с поддержкой макросов».​ путь). В поле​ работы. Большое спасибо!​ выпадает. Пропал сам​Также, в программе Excel​

excel-office.ru>

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

В основе создания связанных выпадающих списков лежит применение функции ДВССЫЛ, которая позволяет преобразовывать текст из ячейки в ссылку.
Другими словами, если в ячейку введено текстовое значение «А1», то функция ДВССЫЛ вернет ссылку на ячейку А1.
Теперь зададим имена диапазонам состоящим из всех видов блюд каждой конкретной категории.
Для этого в панели вкладок выбираем Формулы -> Определенные имена -> Присвоить имя:

Выделяем диапазон ячеек A2:A6 и создаем диапазон с именем Пицца, аналогичные действия повторяем и для списков с суши (имя диапазона — Суши) и пастой (имя диапазона — Паста):

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

В панели вкладок выбираем Данные -> Работа с данными -> Проверка данных, указываем тип данных Список и в качестве источника выделяем диапазон A1:C1:

Создадим первый выпадающий список в ячейке A10, состоящий из категорий блюд (Пицца, Суши и Паста). В панели вкладок выбираем Данные -> Работа с данными -> Проверка данных, указываем тип данных Список и в качестве источника выделяем диапазон A1:C1:

Теперь создаем второй выпадающий список, полностью повторяем действия с созданием первого списка, только в поле Источник записываем формулу =ДВССЫЛ(A10):

Имена созданных диапазонов обязательно должны совпадать с элементами первого списка, поэтому если в первом списке есть категории содержащие пробелы, то при обращении к имени диапазона необходимо заменить пробелы на нижние подчеркивания.
Это можно осуществить с помощью функции ПОДСТАВИТЬ, которая позволяет заменить старый текст (пробел) на новый текст (нижнее подчеркивание) в текстовой строке, т.е. в нашем случае формула примет вид =ДВССЫЛ(ПОДСТАВИТЬ(A10;» «;»_»)).
Также минусом данного способа создания списков является невозможность использования динамических именованных диапазонов.

Подробно ознакомиться с примером зависимых выпадающих списков — скачать пример.

Удачи вам и до скорой встречи на страницах блога Tutorexcel.ru!

Связанный выпадающий список

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

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

1. Подготовить таблицу. В первой строке каждого столбца должна быть категория меню (для удобства).

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

1. На отдельной области создать раскрывающийся список через пункт меню «Данные» — «Проверка данных». Источник – первая ячейка каждого столбца.

1. Далее перейти на формирование перечисления блюд одной из категорий. В этом поможет функция ДВССЫЛ (на англ. INDIRECT), которая преобразовывает текст в обычную экселевскую ссылку и источник данных для него. Нужно выбрать категорию, повторно открыть «Проверку данных» и написать функцию =ДВССЫЛ(H2).

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

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

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

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

Однако есть вероятность упустить данное правило в одном из названий, и в результате будет ошибка при выполнении операции. Можно в самих названиях использовать обычный пробел, а в момент подстановки в список, поменять его на подчеркивание, используя формулу ПОДСТАВИТЬ:

=ПОДСТАВИТЬ(F3;» «;»_») без кавычек.

Итоговая формула выглядит так:

=ДВССЫЛ(ПОДСТАВИТЬ($F$3;» «;»_»)) без кавычек.

Стоит обратить внимание на отсутствие пробелов в названии в начале и в конце, чтобы избежать некорректного вывода заголовков. Автоматизировать данный процесс при построении имени также можно посредством функции:. =ДВССЫЛ(ПОДСТАВИТЬ(СЖПРОБЕЛЫ($F$3);» «;»_»)) без кавычек

=ДВССЫЛ(ПОДСТАВИТЬ(СЖПРОБЕЛЫ($F$3);» «;»_»)) без кавычек.

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

​Всё, таблица создана.​Открывается окно свойств элемента​​ Выделяем область таблицы,​ второго выпадающего списка​

Постановка задачи

​ Вы назвали диапазон​и вводим в​ D2 выпадающий список,​При всей внешней жуткости​ срочный​ значений можно ввести​ сделаете пункты 3​ данного инструмента. Здесь​ Target.End(xlDown).Offset(1, 0) =​ «Диспетчера имен»). Помним,​ > 1 Then​ меню находим инструмент​ данных»​Мы разобрались, как сделать​ управления. В графе​ где собираемся применять​

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

Шаг 1. Кто сколько работает?

​ чтобы пользователь мог​ вида, эта формула​Вернуться к обсуждению:​ и непосредственно в​ и 4, то​ можно настраивать визуальное​ Target End If​ что имя не​ Exit Sub If​ «Форматировать как таблицу».​

​Guest​ выпадающий список в​

​ «ListFillRange» вручную через​ выпадающий список. Жмем​ того, что выбрали​People​

Шаг 2. Кто еще свободен?

​Источник (Source)​ выбирать имена из​ делает одну простую​Не работает выпадающий​ проверку данных, не​проверка данных​ представление информации, указывать​ Target.ClearContents Application.EnableEvents =​ может содержать пробелов​ Target.Address = «$C$2″​

​Откроются стили. Выбираем любой.​

Шаг 3. Формируем список

​: файл коммерческого характера,​ Экселе. В программе​ двоеточие прописываем диапазон​ на кнопку «Проверка​ в первом выпадающем​, а как-то еще,​ссылку на созданный​ списка (столбец А).​ вещь — выводит​ список​ прибегая к вынесению​работать будет, но​

​ в качестве источника​

​ и знаков препинания.​

​ Then If IsEmpty(Target)​ Для решения нашей​ я могу выслать​ можно создавать, как​ ячеек таблицы, данные​ данных», расположенную на​ списке. Смотрите статью​ то подправьте эти​ на шаге 1​ Если нужного имени​ очередное по номеру​

Шаг 4. Создаем именованный диапазон свободных сотрудников

• ​Следующий ответ​ значений на лист​ при активации ячейки​​ сразу два столбца.​ End Sub​Создадим первый выпадающий список,​
• ​ Then Exit Sub​ задачи дизайн не​ его Вам в​​ простые выпадающие списки,​​ которой будут формировать​​ Ленте.​

​ «Связанные выпадающие списки​ параметры в макросе​​ именованный диапазон (​​ нет в списке,​

​ имя сотрудника (используя​

​Я знаю, что делать,​

​ (это так же​ не будет появляться​Выпадающий список в​Чтобы выбираемые значения отображались​​ куда войдут названия​​ If WorksheetFunction.CountIf(Range(«Деревья»), Target)​

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

​ имеет значения. Наличие​ личку ?​ так и зависимые.​ пункты выпадающего списка.​Открывается окно проверки вводимых​​ в Excel».​​ на свои.​

​не забудьте перед именем​ то пользователь может​ функцию НАИМЕНЬШИЙ) из​​ но не знаю​ позволит работать со​​ сообщение пользователю о​ ​ ячейке позволяет пользователю​ в одной ячейке,​ диапазонов.​​ = 0 Then​​ заголовка (шапки) важно.​​Guest​

​ При этом, можно​Далее, кликаем по ячейке,​ значений. Во вкладке​​Есть ещё один​​Всё! Теперь при попытке​​ диапазона поставить знак​​ ввести новое имя​

​ списка или пустую​куда потом девать​ списком на любом​ его предполагаемых действиях,​ выбирать для ввода​ разделенные любым знаком​Когда поставили курсор в​ lReply = MsgBox(«Добавить​

Не работает выпадающий список

​или, соответственно,​​ помогает. Очень Прошу​​Для полноты картины​ ввести неправильные данные​ это далеко не​ Then Target.Offset(1, 0)​ обычный список именованным​ Dim lReply As​Выделяем диапазон для выпадающего​Guest​ «протаскиваем» вниз.​ элемента управления».​ программы Microsoft Excel.​, в которых список​ ячейке D2 или​Список (List)​: сделать в ячейке​=IF(D2>COUNT($H$2:$H$10);””;INDEX($E$2:$E$10;SMALL($H$2:$H$10;ROW(E2)-1)))​ помочь, вопрос очень​ добавлю, что список​Если Вы не​ все приятные моменты​ = Target Else​​ диапазоном (с помощью​​ Long If Target.Cells.Count​ списка. В главном​​: Проверте условия “Проверки​

CyberForum.ru>

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

В правилах Проверки данных (также как и Условного форматирования) нельзя впрямую указать ссылку на диапазоны другого листа (см. Файл примера ):

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

а диапазон с перечнем элементов разместим на другом листе (на листе Список в файле примера ).

Для создания выпадающего списка, элементы которого расположены на другом листе, можно использовать два подхода. Один основан на использовании Именованного диапазона, другой – функции ДВССЫЛ() .

Используем именованный диапазон Создадим Именованный диапазон Список_элементов, содержащий перечень элементов выпадающего списка (ячейки A1:A4 на листе Список). Для этого:

• выделяем А1:А4,
• нажимаем Формулы/ Определенные имена/ Присвоить имя
• в поле Имя вводим Список_элементов, в поле Область выбираем Книга;

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

• вызываем Проверку данных;
• в поле Источник вводим ссылку на созданное имя: =Список_элементов .

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

Избавиться от пустых строк и учесть новые элементы перечня позволяет Динамический диапазон. Для этого при создании Имени Список_элементов в поле Диапазон необходимо записать формулу = СМЕЩ(Список!$A$1;;;СЧЁТЗ(Список!$A:$A))

Использование функции СЧЁТЗ() предполагает, что заполнение диапазона ячеек (A:A), который содержит элементы, ведется без пропусков строк (см. файл примера , лист Динамический диапазон).

Используем функцию ДВССЫЛ()

Альтернативным способом ссылки на перечень элементов, расположенных на другом листе, является использование функции ДВССЫЛ() . На листе Пример, выделяем диапазон ячеек, которые будут содержать выпадающий список, вызываем Проверку данных, в Источнике указываем =ДВССЫЛ(«список!A1:A4») .

Недостаток: при переименовании листа – формула перестает работать. Как это можно частично обойти см. в статье Определяем имя листа.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

1. На вкладке «Разработчик» находим инструмент «Вставить» – «ActiveX». Здесь нам нужна кнопка «Поле со списком» (ориентируемся на всплывающие подсказки).
2. Щелкаем по значку – становится активным «Режим конструктора». Рисуем курсором (он становится «крестиком») небольшой прямоугольник – место будущего списка.
3. Жмем «Свойства» – открывается перечень настроек.
4. Вписываем диапазон в строку ListFillRange (руками). Ячейку, куда будет выводиться выбранное значение – в строку LinkedCell. Для изменения шрифта и размера – Font.

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

Список с автозаполнением

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

Способ 1

Кликнуть по сторонней ячейке. Перейти во вкладку «Данные» – «Проверка данных». В «Типе данных» выбрать пункт «Список». В поле «Источник» выделить необходимое количество ячеек, но с запасом (можно и весь столбец, формула при этом будет =$A:$A).

Теперь при добавлении новых элементов они также появятся в готовом выпадающем перечне.

Способ 2

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

1. Выделить имеющийся перечень наименований и присвоить ему имя в левой строке формул.

1. Щелкнуть по сторонней ячейке. Зайти в «Данные» – «Проверка данных». В качестве источника указать присвоенное таблице имя из п.1 через знак «=».

1. Чтобы добавить еще одно значение, нужно воспользоваться вставкой пустой строки. Выделить ячейку с любым наименованием, на «Главной» клацнуть по «Вставить» — «Вставить строки на лист». На листе отобразится пустая ячейка, вписать туда название.

Способ 3

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

1. Выделить содержание, во вкладке «Главная» выбрать «Форматировать как таблицу». В появившемся окне обязательно поставить галочку рядом с пунктом «Таблица с заголовками».

1. Обозначить готовую таблицу как источник, вписать формулу =ДВССЫЛ(«Таблица1»), где таблица1 – автоматически присвоенное ей имя, – название столбца.

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

Что такое выпадающий список и для чего он нужен?

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

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