Основы Excel: работаем с выпадающим списком. Пошаговая инструкция со скриншотами

Выпадающий список в Excel позволяет выбирать значение ячейки таблицы из перечня, подготовленного заранее.
Опция пригодится, когда нужно много раз вводить повторяющиеся параметры. Например, фамилии сотрудников в графике рабочих смен или наименования товаров в прайсе. Во-первых, с выпадающими списками не придётся вводить одни и те же данные несколько раз. Во-вторых, уменьшится вероятность опечаток.
Разберёмся на примере с каталогом авто, как сделать выпадающие списки. У нас есть перечень автомобилей. Нужно заполнить столбцы с их характеристиками: тип коробки передач, тип привода и положение руля. Значения будут повторяться, поэтому выпадающие списки ускорят заполнение таблицы. Можно заполнить её полностью в два шага: сначала создаём значения выпадающего списка, потом выбираем нужные.

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

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

Переходим на вкладку «Данные» и кликаем по кнопке «Проверка данных».

Открывается окно «Проверка данных». На вкладке «Параметры» в поле «Разрешить» выбираем пункт «Список».

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

На листе с данными для списка выделяем столбец с нужными параметрами. В нашем случае значения столбца «Коробка передач» — автомат, механика, вариатор.

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

Шаг 2
Заполняем таблицу готовыми данными
Когда сохраните выпадающий список, Excel перенесёт вас на первый лист с характеристиками авто. Справа от первой ячейки столбца с коробками передач появилась стрелочка. Если кликнуть по ней, появится список со значениями.

Кликаем на каждую ячейку столбца, справа от неё появляется стрелочка — выбираем нужное значение. Так проходим до конца таблицы.

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

Если в эту таблицу добавить больше строк (автомобилей), не обязательно создавать новые выпадающие списки для них. Можно протянуть вниз значения уже созданных:
- Очистим значения последней заполненной строки таблицы. Это нужно, чтобы протянулись пустые ячейки с выпадающими списками, а не уже выбранные значения.
- Выделим одновременно три столбца и в правом нижнем углу крайнего столбца найдём значок плюса.
- Захватим его и растянем вниз на все автомобили, добавленные в таблицу.

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

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

- Ввести значения заранее. А в качестве источника указать диапазон ячеек со списком.

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

- Откроются стили. Выбираем любой. Для решения нашей задачи дизайн не имеет значения. Наличие заголовка (шапки) важно. В нашем примере это ячейка А1 со словом «Деревья». То есть нужно выбрать стиль таблицы со строкой заголовка. Получаем следующий вид диапазона:

- Ставим курсор в ячейку, где будет находиться выпадающий список. Открываем параметры инструмента «Проверка данных» (выше описан путь). В поле «Источник» прописываем такую функцию:
Протестируем. Вот наша таблица со списком на одном листе:

Добавим в таблицу новое значение «елка».

Теперь удалим значение «береза».

Осуществить задуманное нам помогла «умная таблица», которая легка «расширяется», меняется.
Теперь сделаем так, чтобы можно было вводить новые значения прямо в ячейку с этим списком. И данные автоматически добавлялись в диапазон.
- Сформируем именованный диапазон. Путь: «Формулы» — «Диспетчер имен» — «Создать». Вводим уникальное название диапазона – ОК.

- Создаем раскрывающийся список в любой ячейке. Как это сделать, уже известно. Источник – имя диапазона: =деревья.
- Снимаем галочки на вкладках «Сообщение для ввода», «Сообщение об ошибке». Если этого не сделать, Excel не позволит нам вводить новые значения.

- Вызываем редактор Visual Basic. Для этого щелкаем правой кнопкой мыши по названию листа и переходим по вкладке «Исходный текст». Либо одновременно нажимаем клавиши Alt + F11. Копируем код (только вставьте свои параметры).
- Сохраняем, установив тип файла «с поддержкой макросов».

- Переходим на лист со списком. Вкладка «Разработчик» — «Код» — «Макросы». Сочетание клавиш для быстрого вызова – Alt + F8. Выбираем нужное имя. Нажимаем «Выполнить».
Когда мы введем в пустую ячейку выпадающего списка новое наименование, появится сообщение: «Добавить введенное имя баобаб в выпадающий список?».
Нажмем «Да» и добавиться еще одна строка со значением «баобаб».
Выпадающий список в Excel с данными с другого листа/файла
Когда значения для выпадающего списка расположены на другом листе или в другой книге, стандартный способ не работает. Решить задачу можно с помощью функции ДВССЫЛ: она сформирует правильную ссылку на внешний источник информации.
- Делаем активной ячейку, куда хотим поместить раскрывающийся список.
- Открываем параметры проверки данных. В поле «Источник» вводим формулу: =ДВССЫЛ(“[Список1.xlsx]Лист1!$A$1:$A$9”).
Имя файла, из которого берется информация для списка, заключено в квадратные скобки. Этот файл должен быть открыт. Если книга с нужными значениями находится в другой папке, нужно указывать путь полностью.
Как сделать зависимые выпадающие списки
Возьмем три именованных диапазона:

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

- Когда поставили курсор в поле «Источник», переходим на лист и выделяем попеременно нужные ячейки.

- Теперь создадим второй раскрывающийся список. В нем должны отражаться те слова, которые соответствуют выбранному в первом списке названию. Если «Деревья», то «граб», «дуб» и т.д. Вводим в поле «Источник» функцию вида =ДВССЫЛ(E3). E3 – ячейка с именем первого диапазона.
Выбор нескольких значений из выпадающего списка Excel
Бывает, когда из раскрывающегося списка необходимо выбрать сразу несколько элементов. Рассмотрим пути реализации задачи.
- Создаем стандартный список с помощью инструмента «Проверка данных». Добавляем в исходный код листа готовый макрос. Как это делать, описано выше. С его помощью справа от выпадающего списка будут добавляться выбранные значения.
- Чтобы выбранные значения показывались снизу, вставляем другой код обработчика.
- Чтобы выбираемые значения отображались в одной ячейке, разделенные любым знаком препинания, применим такой модуль.
Не забываем менять диапазоны на «свои». Списки создаем классическим способом. А всю остальную работу будут делать макросы.
Выпадающий список с поиском
- На вкладке «Разработчик» находим инструмент «Вставить» – «ActiveX». Здесь нам нужна кнопка «Поле со списком» (ориентируемся на всплывающие подсказки).

- Щелкаем по значку – становится активным «Режим конструктора». Рисуем курсором (он становится «крестиком») небольшой прямоугольник – место будущего списка.

- Жмем «Свойства» – открывается перечень настроек.

- Вписываем диапазон в строку ListFillRange (руками). Ячейку, куда будет выводиться выбранное значение – в строку LinkedCell. Для изменения шрифта и размера – Font.
При вводе первых букв с клавиатуры высвечиваются подходящие элементы. И это далеко не все приятные моменты данного инструмента. Здесь можно настраивать визуальное представление информации, указывать в качестве источника сразу два столбца.
Как редактировать выпадающий список в Excel? — Шаги по редактированию выпадающего списка

Выпадающий список Excel очень полезен для просмотра большого количества данных в небольшой области. Они также позволяют добавлять ограничения для пользователей, чтобы они не могли вносить какие-либо дополнительные изменения, кроме рекомендованных. Возможны ситуации, когда вы, будучи источником, захотите обновить содержимое выпадающих списков. У вас могут возникнуть проблемы, если вы не понимаете, откуда Excel получает значения для выпадающего списка. Если вы знаете источник списка Excel, вы можете обновить его и быстро в соответствии с требованиями. В этой статье основное внимание уделяется тому, как можно редактировать / обновлять раскрывающиеся списки Excel с помощью инструментов проверки данных Excel.
Примеры для редактирования выпадающего списка в Excel
Вот следующие примеры, которые помогут вам редактировать выпадающий список в Excel
Вы можете скачать этот Как редактировать шаблон Excel из выпадающего списка здесь — Как редактировать шаблон Excel из выпадающего списка
Пример № 1 — Редактирование списка через запятую
Раскрывающийся список, разделенный запятыми, — это список, в котором вы определяли список переменных вручную, используя запятую для их разделения. Предположим, у вас есть вопросы к человеку, который пришел на собеседование в вашу компанию, на эти вопросы есть полярные ответы (да / нет / может быть). Вы можете увидеть изображение ниже для лучшего обобщения.

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

У вас есть настройки, отмеченные как показано на рисунке ниже:

Предположим, мы хотели изменить исходное значение с «Может быть» на «Не уверен». Вы можете сделать то же самое, выполнив следующие шаги:
- В вашей электронной таблице Excel выберите все ячейки, которые имеют ссылку на раскрывающийся список (в этом примере выберите все строки в столбце «Ответы»), т. Е. Все ячейки, которые имеют раскрывающийся список, применены, и вы хотите редактировать.

- В ленте Excel выберите вкладку «Данные»> «Проверка данных».

- Откроется окно проверки данных.

- Добавить или удалить значения списка под источником. Вы можете удалить их все и определить новый набор значений. В этом случае вы заменили значение списка «Может быть» на «Не уверен» и нажмите «ОК». Это сохранит изменения в списке значений и закроет окно проверки данных.

- Вы можете видеть изменения, обновленные в списке под изображением ниже.

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

Если вы хотите редактировать раскрывающиеся списки с именованными диапазонами, то вам просто нужно обновить элементы в вашем диапазоне и изменить ссылку на именованный диапазон в Name Manager (если добавлено несколько новых записей). Раскрывающийся список, связанный с именованным диапазоном, будет автоматически обновляться в зависимости от внесенных вами изменений.
Следуйте инструкциям ниже:
- Перейдите к электронной таблице, в которой есть ваши именованные диапазоны. Обновлены данные диапазона (либо удалите несколько записей, либо добавьте несколько записей вместо предыдущих).

- Из ленты Excel перейдите на вкладку Формула> Диспетчер имен (или нажмите одновременно клавиши Ctrl + F3).

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

- Вы видите, что выпадающий список, связанный с вашим именованным диапазоном (столбец имен сотрудников), обновлен (Kofi заменен на Lalit, а Scharlet заменен на Suhana соответственно).

Пример № 3 — Редактирование выпадающего списка на основе диапазона таблицы
Что если данные, которые вы используете в качестве диапазона для создания раскрывающегося списка? Можно ли обновить / отредактировать такие данные?
Однако не существует традиционного способа редактирования раскрывающегося списка на основе диапазона таблицы. Причина этого в том, что когда мы используем Table в качестве источника / диапазона раскрывающегося списка, диапазон фиксируется в Name Manager .
Вы не можете редактировать диапазон там. Однако, поскольку это таблица, всякий раз, когда вы добавляете записи в таблицу, диапазон автоматически расширяется до последней строки таблицы.
Таким образом, редактирование раскрывающегося списка с таблицей в качестве источника удобнее для редактирования. Просто добавьте элементы в таблицу, и диапазон будет автоматически обновлен.
Смотрите пример ниже:

Столбец A содержит таблицу с именами сотрудников в качестве входных данных.
Вы можете увидеть выпадающий список, который мы создали, предоставив таблицу Имена сотрудников в качестве источника. Когда вы добавляете запись в конце таблицы (например, Mohan), раскрывающийся список автоматически обновляется, и вам не нужно обновлять его вручную (как это было в случае с разделителями-запятыми и именованным диапазоном). вниз список). Это дополнительное преимущество раскрывающегося списка, созданного с использованием таблицы в качестве источника.
Смотрите вывод ниже:

Это было из этой статьи.
Что нужно помнить о выпадающем списке в Excel
- Раскрывающиеся списки Excel, основанные на именованных диапазонах, предоставляют имя раскрывающемуся диапазону, которое можно использовать при создании раскрывающегося списка в качестве ссылки, вместо того, чтобы давать полную ссылку на ячейку (которая содержит ваши ранжированные данные). Тем не менее, также возможно создать раскрывающийся список без указания имени для диапазона.
- Раскрывающийся список, основанный на диапазонах таблиц, является лучшим источником для создания раскрывающегося списка, поскольку он позволяет автоматически обновлять раскрывающийся список, как только мы добавляем записи в таблицу данных. Какое преимущество не видно ни в каком другом методе.
Рекомендуемые статьи
Это было руководство для выпадающего списка в Excel. Здесь мы обсудили, как редактировать выпадающий список в Excel вместе с примерами и загружаемым шаблоном Excel. Вы также можете посмотреть следующие статьи, чтобы узнать больше —
Как редактировать раскрывающийся список в Excel (с пошаговыми инструкциями)
Как профессионал, вы можете создавать электронные таблицы Excel с различными интерактивными функциями, включая раскрывающиеся списки, для повышения эффективности на рабочем месте. Хотя редактирование раскрывающегося списка просто, если вы создали его на основе таблицы Excel, это может быть сложнее, если вы использовали альтернативный метод. Если вы разработали раскрывающийся список на основе диапазона ячеек, именованного диапазона или записей, введенных вручную, вам может быть интересно, как его эффективно редактировать.
В этой статье мы обсудим, почему вам может понадобиться изменить раскрывающийся список, предоставим пошаговые инструкции по редактированию различных типов раскрывающихся списков и дадим несколько советов, которые помогут вам оставаться организованным при внесении этих изменений.
Зачем редактировать раскрывающийся список в Excel?
Раскрывающийся список — прекрасный инструмент, который многие профессионалы используют для создания форм, которые коллеги могут заполнить или разработать простые для понимания инструкции. Хотя использование этой функции может улучшить совместную работу и оптимизировать процессы, вы можете время от времени обновлять элементы, которые вы включаете в раскрывающийся список, чтобы они оставались актуальными. Например, если вы создаете электронную таблицу Excel для отслеживания проектов, над которыми работает ваша команда, у вас может быть специальный столбец, в котором члены команды могут указать, для какого клиента они завершили проект, чтобы вы могли фильтровать эти данные по имени клиента.
Программы для Windows, мобильные приложения, игры — ВСЁ БЕСПЛАТНО, в нашем закрытом телеграмм канале — Подписывайтесь:)
Вместо того, чтобы члены команды вручную вводили имя клиента, вы можете создать раскрывающееся меню, которое позволит им выбрать клиента из уже существующего списка. Это может предотвратить орфографические ошибки, улучшить организацию и упростить заполнение формы. Со временем вы можете закончить работу с некоторыми клиентами и начать работать с новыми клиентами. Научившись редактировать созданный вами раскрывающийся список, вы сможете добавлять и удалять клиентов по мере необходимости. Это означает, что ваша команда может продолжать использовать ту же электронную таблицу Excel для совместной работы.
Как изменить диапазон ячеек в Excel
Вот несколько шагов, которые вы можете выполнить, чтобы изменить диапазон ячеек в Excel:
Откройте электронную таблицу Excel, с которой вы хотите работать, а затем перейдите к конкретному рабочему листу, содержащему элементы в вашем списке.
Нажмите на ячейки в вашем списке, чтобы добавить или удалить элементы.
Затем щелкните конкретную ячейку, в которой находится раскрывающийся список.
Щелкните вкладку меню «Данные» на панели инструментов в верхней части экрана, чтобы открыть представление данных. Если у вас уже открыто это представление, вкладка «Данные» может быть выделена или подчеркнута.
Открыв окно просмотра данных, нажмите кнопку «Проверка данных» в верхней части экрана. Обычно это появляется между кнопкой «Удалить дубликаты» и кнопкой «Объединить».
В появившемся раскрывающемся меню выберите «Проверка данных».
В открывшемся всплывающем окне нажмите на вкладку «Настройки».
В текстовом поле под словом «Источник» настройте диапазон ячеек раскрывающегося списка. Вы можете либо расширить диапазон ячеек, включив в него новые данные, которые вы ввели на листе, либо уменьшить диапазон ячеек, чтобы исключить данные, которые вы удалили из него.
Нажмите кнопку «ОК», чтобы обновить раскрывающийся список и сохранить изменения.
Как редактировать именованный диапазон в Excel
Вот несколько шагов, которые вы можете выполнить, чтобы изменить именованный диапазон в Excel:
Откройте электронную таблицу Excel, с которой вы хотите работать, а затем перейдите к конкретному рабочему листу, содержащему элементы в вашем списке.
Нажмите на ячейки в вашем списке, чтобы добавить или удалить элементы.
Выберите вкладку меню «Формулы» на панели инструментов в верхней части экрана, чтобы открыть представление формул. Если у вас уже открыто это представление, вкладка «Формулы» может быть выделена или подчеркнута.
Затем нажмите кнопку «Диспетчер имен» в верхней части экрана. Эта кнопка обычно появляется справа от кнопки «Дополнительные функции».
В появившемся всплывающем окне выберите именованный диапазон, на котором основан раскрывающийся список. Затем обновите формулу «Ссылки», чтобы включить ячейки, которые вы хотите добавить, или исключить ячейки, которые вы хотите удалить.
Нажмите кнопку «Закрыть», чтобы обновить раскрывающийся список. Затем нажмите кнопку «Да», чтобы подтвердить изменения.
Как отредактировать введенный вручную список в Excel
Вот несколько шагов, которые вы можете выполнить, чтобы отредактировать вручную введенный список в Excel:
Откройте электронную таблицу Excel, с которой вы хотите работать, а затем перейдите к конкретному рабочему листу, содержащему элементы в вашем списке.
Нажмите на ячейки в вашем списке, чтобы добавить или удалить элементы.
Затем щелкните конкретную ячейку, в которой находится раскрывающийся список.
Щелкните вкладку меню «Данные» на панели инструментов в верхней части экрана, чтобы открыть представление данных. Если у вас уже открыто это представление, вкладка «Данные» может быть выделена или подчеркнута.
Открыв окно просмотра данных, нажмите кнопку «Проверка данных» в верхней части экрана. Обычно это появляется между кнопкой «Удалить дубликаты» и кнопкой «Объединить».
В появившемся раскрывающемся меню выберите «Проверка данных».
В открывшемся всплывающем окне нажмите на вкладку «Настройки».
В текстовом поле под словом «Источник» добавьте или удалите нужные элементы списка. Убедитесь, что каждый элемент в вашем списке разделен запятой.
Нажмите кнопку «ОК», чтобы обновить раскрывающийся список и сохранить изменения.
Советы по редактированию раскрывающихся списков в Excel
Вот несколько дополнительных советов, которые помогут вам оставаться организованным и повысить эффективность при редактировании раскрывающихся списков в Excel:
Используйте рисовальщик форматов. Сделайте свои раскрывающиеся списки заметными, используя инструмент форматирования, чтобы применить определенный стиль к этим ячейкам и упростить их идентификацию для ваших коллег. Вы можете выбрать полужирный или курсивный шрифт или изменить цвет фона, чтобы сделать электронную таблицу визуально привлекательной.
Изучите шаблоны Excel. Существует множество доступных шаблонов Excel, которые помогут вам быстро создать электронную таблицу с раскрывающимися списками. Это может сэкономить ваше время и убедиться, что ваше форматирование выглядит профессионально.
Сохраняйте обновления по ходу. Если вы вносите несколько изменений в электронную таблицу, сохраняйте файл Excel каждые несколько минут, чтобы защитить свою работу. Вы можете одновременно нажать клавиши «Control» и «S», чтобы быстро сохранить свой прогресс.
Обратите внимание, что ни один из продуктов или компаний, упомянутых в этой статье, не связан с компанией Indeed.