Выплывающий список: для чего нужен и как сделать / Skillbox Media

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

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

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

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

Содержание

• Выпадающий список через контекстное меню
• Создание списка с применением инструментов разработчика
• Связанный список
• Заключение

Выпадающий список через контекстное меню

Смотрите также: “Как из Экселя перенести таблицу в Ворд”

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

1. Во вспомогательной таблице пишем перечень всех наименований – каждый с новой строки в отдельной ячейке. В итоге должен получиться один столбец с заполненными данными.
2. Затем отмечаем все эти ячейки, нажимаем в любом месте отмеченного диапазона правой кнопкой мыши и в открывшемся списке кликаем по функции “Присвоить имя..”.
3. На экране появится окно “Создание имени”. Называем список так, как хочется, но с  условием – первым символом должна быть буква, также не допускается использование определенных символов. Здесь же предусмотрена возможность добавления списку примечания в соответствующем текстовом поле. По готовности нажимаем OK.
4. Переключаемся во вкладку “Данные” в основном окне программы. Отмечаем группу ячеек, для которых хотим задать выбор из нашего списка и нажимаем на значок “Проверка данных” в подразделе “Работа с данными”.
5. На экране появится окно “Проверка вводимых значений”. Находясь во вкладке “Параметры” в типе данных останавливаемся на опции “Список”. В текстовом поле “Источник” пишем знак “равно” (“=”) и название только что созданного списка. В нашем случае – “=Наименование”. Нажимаем OK.
6. Все готово. Справа от каждой ячейки выбранного диапазона появится небольшой значок со стрелкой вниз, нажав на которую можно открыть перечень наименований, который мы заранее составили. Щелкнув по нужному варианту из списка, он сразу же будет вставлен в ячейку. Кроме того, значение в ячейке теперь может соответствовать только наименованию из списка, что исключит любые возможные опечатки.

Создание списка с применением инструментов разработчика

Смотрите также: “Как отобразить скрытые столбцы в Экселе”

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

1. В первую очередь, эти инструменты нужно найти и активировать, так как по умолчанию они выключены. Переходим в меню “Файл”.
2. В перечне слева находим в самом низу пункт “Параметры” и щелкаем по нему.
3. Переходим в раздел “Настроить ленту” и в области “Основные вкладки” ставим галочку напротив пункта “Разработчик”. Инструменты разработчика будут добавлены на ленту программы. Кликаем OK, чтобы сохранить настройки.
4. Теперь в программе есть новая вкладка под названием “Разработчик”. Через нее мы и будем работать. Сначала создаем столбец с элементами, которые будут источниками значений для нашего выпадающего списка.
5. Переключаемся во вкладу “Разработчик”. В подразделе “Элементы управления” нажимаем на кнопку “Вставить”. В открывшемся перечне в блоке функций “Элементы ActiveX” кликаем по значку “Поле со списком”.
6. Далее нажимаем на нужную ячейку, после чего появится окно со списком. Настраиваем его размеры по границам ячейки. Если список выделен мышкой, на панели инструментов будет активен “Режим конструктора”. Нажимаем на кнопку “Свойства”, чтобы продолжить настройку списка.
7. В открывшихся параметрах находим строку “ListFillRange”. В столбце рядом  через двоеточие пишем координаты диапазона ячеек, составляющих наш ранее созданный список. Закрываем окно с параметрами, щелкнув на крестик.
8. Затем кликаем правой кнопкой мыши по окну списка, далее – по пункту “Объект ComboBox” и выбираем “Edit”.
9. В результате мы получаем выпадающий список с заранее определенным перечнем.
10. Чтобы вставить его в несколько ячеек, наводим курсор  на правый нижний угол ячейки со списком, и как только он поменяет вид на крестик, зажимаем левую кнопку мыши и тянем вниз до самой нижней строки, в которой нам нужен подобный список.

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

У пользователей также есть возможность создавать и более сложные взаимозависимые списки (связанные). Это значит, что список в одной ячейке будет зависеть от того, какое значение мы выбрали в другой. Например, в единицах измерения товара мы можем задать килограммы или литры. Если вы выберем в первой ячейке кефир, во второй на выбор будет предложено два варианта – литры или миллилитры. А если в первую ячейки мы остановимся на яблоках, во второй у нас будет выбор из килограммов или граммов.

1. Для этого нужно подготовить как минимум три столбца. В первом будут заполнены наименования товаров, а во втором и третьем – их возможные единицы измерения. Столбцов с возможными вариациями единиц измерения может быть и больше.
2. Сначала создаем один общий список для всех наименований продуктов, выделив все строки столбца “Наименование”, через контекстное меню выделенного диапазона.
3. Задаем ему имя, например, “Питание”.
4. Затем таким же образом формируем отдельные списки для каждого продукта с соответствующими единицами измерения. Для большей наглядности возьмем в качестве примера первую позицию – “Лук”. Отмечаем ячейки, содержащие все единицы измерения для этого продукта, через контекстное меню присваиваем имя, которое полностью должно совпадать с наименованием. Таким же образом создаем отдельные списки для всех остальных продуктов в нашем перечне.
5. После этого вставляем общий список с продуктами в верхнюю ячейку первого столбца основной таблицы – как и в описанном выше примере, через кнопку “Проверка данных” (вкладка “Данные”).
6. В качестве источника указываем “=Питание” (согласно нашему названию).
7. Затем кликаем по верхней ячейке столбца с единицами измерения, также заходим в окно проверки данных и в источнике указываем формулу “=ДВССЫЛ(A2)“, где A2 – номер ячейки с соответствующим продуктом.
8. Списки готовы. Осталось его только растянуть их все строки таблицы, как для столбца A, так и для столбца B.

Заключение

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

Смотрите также: “Деление в Экселе: формула, как сделать”

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

12:00ЛикбезТехнологии

Лайфхакер собрал семь способов: от простых к сложным.

Поделиться

0

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

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

Поставьте курсор в следующую пустую ячейку диапазона. В нашем примере это B7.

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

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

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

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

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

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

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

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

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

Для начала создайте именованный диапазон. Для этого выделите ячейки с будущими пунктами выпадающего списка и озаглавьте, вписав имя в поле с названиями ячеек, а затем нажмите Enter. Обязательное условие: первым символом должна быть буква, нельзя использовать пробелы (ставьте вместо них «_»).

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

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

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

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

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

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

Выберите «Список», в качестве источника укажите такую формулу: =ДВССЫЛ("[имя_файла]имя_листа!диапазон") То есть внутри кавычек должно быть название файла с данными для списка в квадратных скобках, потом без пробела название листа, а затем так же без пробела восклицательный знак и обозначение диапазона. В нашем примере будет так: =ДВССЫЛ("[Книга1]Лист1!A3:A9")

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

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

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

Выделите диапазон с элементами списка и их категорией, а затем на вкладке «Главная» кликните «Форматировать как таблицу».

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

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

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

Укажите «Список» как тип данных, а для источника введите такую формулу: =ДВССЫЛ("имя_таблицы") В нашем примере будет так: =ДВССЫЛ("Двери")

Список сразу же заработает и будет обновляться при удалении и добавлении элементов.

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

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

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

Кликните «Настроить ленту», затем включите пункт «Разработчик» и нажмите OK.

На вкладке «Разработчик» кликните «Вставить» и выберите «Поле со списком» из элементов ActiveX.

«Нарисуйте» с помощью курсора ячейку списка, после чего кликните по ней правой кнопкой мыши и выберите «Свойства».

Найдите строку ListFillRange, введите в неё диапазон значений для списка и нажмите Enter. В нашем примере это B6:B9.

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

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

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

Подготовьте столбцы с данными: в шапках пункты первого, родительского списка, а под ними пункты связанного с ним второго. Выделите первый столбец с шапкой и на вкладке «Главная» кликните «Форматировать как таблицу».

Выберите любой из стилей, а затем в появившемся окошке включите опцию «Таблица с заголовками» и нажмите OK.

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

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

Укажите в качестве типа «Список», а в качестве источника выделите шапки столбцов с категориями, которые станут пунктами родительского перечня.

Далее установите курсор в ячейку, в которой будет второй список, кликните «Проверка данных», выберите вариант «Список», а в поле «Источник» впишите =ДВССЫЛ и адрес ячейки родительского списка в скобках. То есть =ДВССЫЛ(h4) для нашего примера.

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

А если указать «Наушники» — будут отображены соответствующие названия аксессуаров из одноимённого столбца.

Читайте также 🧐

• 10 формул в Excel, которые облегчат вам жизнь
• Как сделать диаграмму в Excel
• Как сделать или убрать разрыв страницы в Word и Excel
• Как в Excel закрепить область при прокрутке
• 12 простых приёмов для ускоренной работы в Excel

Раскрывающиеся списки Excel — проверка данных

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

• Что такое проверка данных?
• Видео: Создание раскрывающегося списка в ячейке
• Видео: использование списка на другом листе

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

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

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

Как создать раскрывающийся список С помощью проверки данных вы можете создать раскрывающийся список параметров в клетка. Есть 3 простых шага: 1. Создайте таблицу элементов ИЛИ создайте список 2. Назовите список 3. Создайте раскрывающийся список Примечание : Проверка данных не является надежной. Его можно обойти вставив данные в ячейку или выбрав «Очистить» > «Очистить все», на вкладке Главная ленты. 1. Создайте таблицу элементов Самый простой способ создать и поддерживать список опций — это введите их в рабочий лист. Вы можете сделать это на листе, который будет содержать раскрывающихся списков или на другом листе. В этом примере list будет храниться на листе с именем Lists . ПРИМЕЧАНИЕ. Список проверки данных может отображать 32 767 элементов из список на рабочем листе. Введите заголовок для списка — Сотрудники в этом примере Непосредственно под ячейкой заголовка в один столбец введите записи, которые вы хотите видеть в раскрывающемся списке. Не оставляйте пустым ячейки между записями. Выберите ячейку в списке и на вкладке ленты «Вставка» щелкните Стол Добавьте галочку в «Моя таблица имеет заголовки» и нажмите ХОРОШО Таблица теперь является именованной таблицей Excel.

2. Назовите список Далее вы создадите именованный диапазон, не включающий заголовок ячейка в таблице. Этот именованный диапазон будет динамическим — он будет корректировать автоматически, если элементы добавляются в список или удаляются из него. Вы можете использовать диспетчер имен, как показано в видео, или использовать поле имени. Оба метода описаны ниже. Метод Name Box быстрее, но вы не можете добавить комментарий. Использовать Диспетчер имен Этот метод показан на видео. Вы можете установить имя, область действия и комментарии. Выберите одну из ячеек в списке имен сотрудников. На ленте щелкните вкладку «Формулы» и в группе «Определенные имена» щелкните «Определить имя». В диалоговом окне «Новое имя» введите имя из одного слова для выбранного диапазона, например ЭмпИмена Оставить область как рабочую книгу Щелкните в поле «Ссылка на» (на рабочем листе будет выделена ячейка, которая в данный момент указана в поле «Ссылка на») Чтобы выбрать все имена в списке, наведите курсор на верхнюю часть ячейки заголовка (B1) и, когда появится черная стрелка, щелкните, чтобы выбрать все имена без ячейки заголовка. (Обязательно щелкните, когда стрелка находится в ячейке B1, а не в кнопке заголовка столбца B) Имя таблицы и имя столбца появятся в поле «Ссылки на»: =Table1[Employees] Нажмите OK, чтобы завершить имя. Используйте поле имени Это быстрый способ назвать диапазон ячеек. Щелкните в верхней части ячейки заголовка, чтобы выбрать все ячейки в списке (заголовок не будет выделен). Щелкните в поле Имя слева от строки формул Введите имя из одного слова для выбранного диапазона ячеек, например EmpNames, а затем нажмите клавишу Enter , чтобы завершить имя. ПРИМЕЧАНИЕ. После того, как вы нажмите Enter, имя исчезнет, ​​и появится имя таблицы в поле имени.

3. Создайте раскрывающийся список Теперь, когда вы создали именованный диапазон, вы можете использовать его для создания выпадающий список в одной или нескольких ячейках Выберите ячейки, в которых вы хотите выпадающий список На вкладке «Данные» ленты в группе «Инструменты данных» нажмите кнопку «Проверка данных». Когда откроется диалоговое окно «Проверка данных», перейдите на вкладку «Настройки». Сначала щелкните в поле Разрешить Затем в раскрывающемся списке Разрешить выберите Список Совет: используйте мышь, чтобы выбрать элемент в списке, или используйте стрелку вниз на клавиатуре Щелкните в поле Источник и введите знак равенства, и список имя, например: =EmpNames ИЛИ, чтобы использовать сочетание клавиш, нажмите клавишу F3, чтобы увидеть список имен Затем щелкните имя, чтобы выбрать его, и нажмите ОК Необязательно: перейдите на вкладку «Входящее сообщение» и создайте собственное сообщение с советами по вводу действительного элемента . Необязательно: перейдите на вкладку «Предупреждение об ошибке» и создайте собственное сообщение об ошибке, чтобы помочь людям исправить любые недопустимые записи Нажмите кнопку «ОК», чтобы закрыть диалоговое окно «Проверка данных». Щелкните одну из ячеек и щелкните стрелку раскрывающегося списка. Щелкните элемент в раскрывающемся списке, чтобы ввести его в ячейку.

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

Использовать список с разделителями Вместо обращения к списку элементов на рабочем листе вы можете введите список в поле Источник, разделив его запятыми. Например: Да, Нет, Возможно Примечания : Этот метод проверки данных чувствителен к регистру — если пользователь вводит YES, появится предупреждение об ошибке. Символы пробела можно вводить до или после допустимых элементов и сообщение об ошибке не отображается, т.е. » Да » было бы разрешено. (Спасибо Питеру за этот совет.)

Разрешить записи, отсутствующие в раскрывающемся списке Когда вы добавляете проверку данных в ячейку, автоматически включается функция предупреждения об ошибке. Это не позволяет пользователям вводить неверные данные в ячейку. В некоторых рабочих листах, где есть раскрывающийся список допустимых элементов, вы можете также разрешить ввод других элементов. Например: В форме заказа на рабочем листе покажите краткий раскрывающийся список названий основных продуктов, чтобы их было легко вводить Но разрешить людям вводить названия новых продуктов в ячейку, когда это необходимо Выключить оповещение об ошибке Самый быстрый способ разрешить пользователям вводить элементы, которых нет в списке, — это изменить настройку предупреждения об ошибке проверки данных. Чтобы изменить этот параметр, выполните следующие действия: Выберите ячейки рабочего листа, для которых вы хотите отключить оповещение об ошибке На ленте щелкните вкладку Данные и щелкните Проверка данных В окне «Настройки проверки данных» щелкните значок Предупреждение об ошибке вкладка Снять галочку с Показать предупреждение об ошибке после ввода неверных данных Другие параметры предупреждения об ошибке проверки данных Существуют и другие параметры предупреждения об ошибке, если вы не хотите полностью отключать этот параметр. Для получения подробной информации о настройке предупреждений об ошибках и других сообщений проверки данных перейдите на страницу «Показать сообщения проверки данных». Например, вы можете показать предупреждающее сообщение и спросить, хотят ли они продолжить запись. Или просто показать информационное сообщение, напоминающее им, что элемент отсутствует в раскрывающемся списке допустимых записей.

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

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

Дополнительные руководства

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

Индексная страница проверки данных

Изменить название продукта на код

Зависимые раскрывающиеся списки видео

Hide Используемые элементы в выпадении

Список из другой рабочей книги

Примеры критериев данных

Дата проверки данных

Советы по проверке данных

В валидации данных со сокаметром

. Списки вниз

Примеры критериев проверки данных

 

Последнее обновление: 9 ноября 2022 г., 14:19

как создавать, редактировать, копировать и удалять

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

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

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

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

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

• Раскрывающийся список из значений, разделенных запятыми
• Выпадающий список из именованного диапазона
• Список проверки данных из таблицы
• Выпадающий список из диапазона ячеек
• Динамическое раскрывающееся меню

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

Это самый быстрый способ добавить раскрывающийся список во все версии Excel 2010 — Excel 365.

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

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

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

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

На ленте Excel перейдите на вкладку Данные > группа Работа с данными и щелкните Проверка данных .

3. Введите элементы списка и выберите параметры.

В окне Data Validation на вкладке Settings выполните следующие действия:

• В поле Разрешить выберите Список .
• В поле Источник введите элементы, которые должны отображаться в раскрывающемся меню, разделенные запятой (с пробелами или без них).
• Убедитесь, что флажок раскрывающегося списка в ячейке установлен; в противном случае стрелка раскрывающегося списка не появится рядом с ячейкой.
• Установите или снимите флажок Игнорировать пустые в зависимости от того, как вы хотите обрабатывать пустые ячейки.
• Нажмите OK, и все готово!

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

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

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

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

1. Введите элементы раскрывающегося списка.

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

Например, давайте создадим раскрывающийся список ингредиентов для ваших любимых рецептов:

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

2.

Создайте именованный диапазон.

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

3. Примените проверку данных.

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

• В поле Разрешить выберите Список .
• В поле Источник введите имя, которое вы дали своему диапазону, поставив перед ним знак равенства, например =Ingredients .
• Убедитесь, что флажок раскрывающегося списка в ячейке установлен.
• Нажмите OK.

Если список источников содержит более 8 элементов, в раскрывающемся списке появится полоса прокрутки, подобная этой:

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

Создать раскрывающийся список из таблицы Excel

Вместо использования обычного именованного диапазона вы можете преобразовать свои данные в полнофункциональную таблицу Excel ( Вставка > Таблица или Ctrl + T), а затем создать список проверки данных из этого стол. Почему вы можете захотеть использовать таблицу? В первую очередь потому, что он позволяет создать расширяемый динамический раскрывающийся список , который автоматически обновляется при добавлении или удалении элементов из таблицы.

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

1. Выберите ячейку, в которую вы хотите вставить раскрывающийся список.
2. Откройте диалоговое окно Проверка данных .
3. Выберите Список в раскрывающемся списке Разрешить .
4. В новом поле Источник введите формулу, относящуюся к определенному столбцу в таблице, не включая ячейку заголовка. Для этого используйте функцию ДВССЫЛ со структурированной ссылкой, подобной этой:

   = ДВССЫЛ ("Имя_таблицы[имя_столбца]")

5. Когда закончите, нажмите OK .

В этом примере мы создаем раскрывающийся список из столбца с именем Ингредиенты в Таблице 1:

= ДВССЫЛ ("Таблица 1 [Ингредиенты]")

Вставка раскрывающегося списка в Excel из диапазона ячеек

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

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

Создать динамический (автоматически обновляемый) раскрывающийся список Excel

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

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

1. Вы начинаете с создания обычного раскрывающегося списка на основе именованного диапазона, как описано выше.
2. На шаге 2 при создании имени вы помещаете следующую формулу в поле Относится к .

   =СМЕЩЕНИЕ(Лист1!$A$1,0,0,СЧЁТ(Лист1!$A:$A),1)

   Где:

   • Sheet1 — имя листа
   • A — столбец, в котором расположены элементы вашего выпадающего списка
   • $A$1 — ячейка, содержащая первый элемент списка

Как видите, формула состоит из двух функций Excel — СМЕЩ и СЧЁТ. Функция COUNTA подсчитывает все непустые значения в указанном столбце. СМЕЩ принимает это число и возвращает ссылку на диапазон, включающий только непустые ячейки, начиная с первой ячейки, указанной в формуле.

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

Как работает эта формула

В Microsoft Excel функция СМЕЩ(ссылка, строки, столбцы, [высота], [ширина]) используется для возврата ссылки на диапазон, состоящий из указанного количества строк и столбцов. Чтобы заставить его возвращать динамический, т.е. постоянно меняющийся диапазон, мы указываем следующие аргументы:

• ссылка — ячейка $A$1 в Sheet1, которая является первым пунктом вашего выпадающего списка;
• строки и столбцы равны 0, потому что вы не хотите сдвигать возвращаемый диапазон ни по вертикали, ни по горизонтали;
• высота — количество непустых ячеек в столбце А, возвращаемое функцией СЧЁТЗ;
• ширина — 1, т.е. один столбец.

Как создать раскрывающийся список из другой книги

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

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

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

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

1. Создайте именованный диапазон для исходного списка.

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

2. Создайте именованную ссылку в основной книге.

Откройте книгу, в которой вы хотите разместить раскрывающийся список, и создайте имя, которое ссылается на ваш исходный список. В этом примере заполненная ссылка имеет вид =SourceBook.xlsx!Source_list

Примечание. Вы должны заключить имя книги в апострофы (‘), если оно содержит пробелы. Например: ='Source Book.xlsx'!Source_list

3.

Применить проверку данных

В основной книге выберите ячейки для раскрывающегося списка, щелкните Данные > Проверка данных и введите имя, которое вы созданный на шаге 2 в поле Source .

Динамический выпадающий список из другой книги

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

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

Проверка данных Excel не работает

Параметр проверки данных недоступен или отключен? Это может произойти по нескольким причинам:

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

Дополнительные параметры раскрывающегося списка Excel

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

Отображение сообщения при нажатии на ячейку с раскрывающимся списком

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

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

Результат в Excel будет выглядеть примерно так:

Разрешить пользователям вводить свои собственные данные в поле со списком

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

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

1. В Диалоговое окно «Проверка данных » (вкладка «Данные » > «Проверка данных ») перейдите на вкладку «Предупреждение об ошибке ».
2. Установите флажок «Показывать предупреждение об ошибке после ввода недопустимых данных », если вы хотите отображать предупреждение, когда пользователь пытается ввести некоторые данные, которых нет в раскрывающемся меню. Если вы не хотите показывать какие-либо сообщения, снимите этот флажок.
3. Чтобы отобразить предупреждающее сообщение, выберите один из вариантов в поле Стиль и введите заголовок и сообщение. Либо Информация или Предупреждение позволит пользователям ввести свой собственный текст в поле со списком.
   • Информационное сообщение рекомендуется, если ваши пользователи часто вводят собственные варианты.
   • Сообщение Предупреждение побуждает пользователей выбирать элемент из раскрывающегося списка, а не вводить свои собственные данные, хотя и не запрещает ввод пользовательских данных.
   • Стоп (по умолчанию) запретит пользователям вводить какие-либо данные, которых нет в раскрывающемся списке Excel.

   А вот как может выглядеть настроенное вами предупреждающее сообщение в Excel:
   

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

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

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

Чтобы скопировать раскрывающийся список без текущего выбора , используйте функцию «Специальная вставка», чтобы скопировать только правило проверки данных.

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

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

• Редактирование раскрывающегося списка, разделенного запятыми
• Изменение раскрывающегося меню на основе диапазона ячеек
• Редактирование выпадающего списка из именованного диапазона

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

Если вы создали раскрывающийся список, разделенный запятыми, выполните следующие действия:

1. Выберите ячейку или ячейки, которые ссылаются на ваш список проверки данных Excel, т. е. ячейки, содержащие раскрывающийся список, который вы хотите изменить.
2. Щелкните Проверка данных (лента Excel > вкладка Данные).
3. Удалите или введите новые элементы в поле Источник .
4. Нажмите OK, чтобы сохранить изменения и закрыть окно проверки данных Excel .
   

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

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

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

1. Перейдите к электронной таблице, содержащей элементы, которые отображаются в раскрывающемся списке, и отредактируйте список нужным вам образом.
2. Выберите ячейку или ячейки, содержащие раскрывающийся список.
3. Щелкните Проверка данных на вкладке Данные .
4. В окне Excel Проверка данных на вкладке Параметры измените ссылки на ячейки в поле Источник. Вы можете отредактировать их вручную или нажать кнопку Свернуть диалоговое окно значок.
5. Нажмите кнопку OK , чтобы сохранить изменения и закрыть окно.
   

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

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

1. Добавить или удалить элементы в именованном диапазоне.
   Откройте рабочий лист, содержащий именованный диапазон, удалите или введите новые записи. Не забудьте расположить элементы в том порядке, в котором вы хотите, чтобы они отображались в раскрывающемся списке Excel.
2. Изменить ссылку на именованный диапазон.
   • На ленте Excel перейдите на вкладку Формулы > Диспетчер имен . Либо нажмите Ctrl + F3, чтобы открыть окно диспетчера имен .
   • В окне Name Manager выберите именованный диапазон, который вы хотите обновить.
   • Измените ссылку в поле Относится к , щелкнув значок Свернуть диалоговое окно и выбрав все записи в раскрывающемся списке.
   • Нажмите кнопку Закрыть , а затем в появившемся сообщении подтверждения нажмите Да , чтобы сохранить изменения.

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

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

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

• Удаление раскрывающегося меню из выбранных ячеек
• Удаление выпадающего списка из всех ячеек текущего листа

Удаление раскрывающегося меню из выбранных ячеек

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

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

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

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

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

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

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