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

Виктор Бухтеев

40K

Обсудить

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

Вариант 1: Группировка существующего списка

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

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

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

  2. Перейдите на вкладку «‎Данные» и выберите инструмент «‎Проверка данных».

  3. В новом окне в качестве типа данных укажите «‎Список», отыскав соответствующий вариант из выпадающего меню.

  4. В качестве источника задайте те самые ячейки, выделив их левой кнопкой мыши. Нажмите «‎ОК» для применения настроек.

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

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

Комьюнити теперь в Телеграм

Подпишитесь и будьте в курсе последних IT-новостей

Подписаться

Вариант 2: Ручное добавление элементов списка

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

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

  2. Выберите в качестве типа данных список, а в «‎Источник» перечислите значения, которые желаете в него добавить.

    Используйте «‎;», чтобы отделить одно значение от другого.

  3. Подтвердите изменения и вернитесь к таблице. Теперь при развертывании списка вы сможете выбрать один из добавленных вариантов.

Вариант 3: Выбор массива с именем

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

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

  2. Измените имя массива на любое, начинающееся с буквы.

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

  4. В качестве источника укажите созданный массив, написав его название после

    «‎=».

  5. В итоге у вас должен получиться перечень, куда входят все значения массива.

Вариант 4: Создание выпадающего списка из таблицы

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

  1. Выделите все ячейки, включая название столбца, и на главной вкладке нажмите кнопку «‎Сформировать как таблицу».

  2. Выберите подходящий для вас тип оформления.

  3. При появлении окна с расположением данных просто нажмите Enter, поскольку диапазон выбран предварительно.

  4. Вы автоматически окажетесь на вкладке «‎Конструктор таблицы», где можете изменить ее имя на любое удобное, начинающееся с буквы.  

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

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

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

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

Личный опыт

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

Рекомендуем

Выпадающий список в ячейке листа

5767 02.10.2012 Скачать пример

Видео

 У кого мало времени и нужно быстро ухватить суть — смотрим обучающее видео:

Кому интересны подробности и нюансы всех описанных способов — дальше по тексту.

Способ 1. Примитивный

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

Выбрать из раскрывающегося списка (Choose from drop-down list) или нажать сочетание клавиш ALT+стрелка вниз. Способ не работает, если ячейку и столбец с данными отделяет хотя бы одна пустая строка или вам нужен товар, который еще ни разу не вводился выше:

Способ 2.

Стандартный
  1. Выделите ячейки с данными, которые должны попасть в выпадающий список (например, наименованиями товаров).
  2. Если у вас Excel 2003 или старше — выберите в меню Вставка — Имя — Присвоить (Insert — Name — Define), если Excel 2007 или новее — откройте вкладку Формулы (Formulas) и воспользуйтесь кнопкой Диспетчер имен (Name Manager)
    , затем Создать. Введите имя (можно любое, но обязательно без пробелов и начать с буквы!) для выделенного диапазона (например Товары). Нажмите ОК.
  3. Выделите ячейки (можно сразу несколько), в которых хотите получить выпадающий список и выберите в меню (на вкладке) Данные — Проверка (Data — Validation). Из выпадающего списка Тип данных (Allow) выберите вариант Список (List) и введите в строчку Источник (Source) знак равенства и имя диапазона (т.е. =Товары).

Нажмите ОК.

Все! Наслаждайтесь!

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

Способ 3. Элемент управления

Этот способ представляет собой вставку на лист нового объекта — элемента управления «поле со списком» с последующей привязкой его к диапазонам на листе. Для этого:

  1. В Excel 2007/2010 откройте вкладку
    Разработчик (Developer)    . В более ранних версиях — панель инструментов Формы (Forms) через меню Вид — Панели инструментов — Формы (View — Toolbars — Forms). Если этой вкладки не видно, то нажмите кнопку Офис — Параметры Excel — флажок Отображать вкладку Разработчик на ленте (Office Button — Excel Options — Show Developer Tab in the Ribbon)
  2. Найдите значок выпадающего списка среди элементов управления форм (не ActiveX!). Ориентируйтесь по всплывающим подсказкам — Поле со списком:

    Щелкните по значку и нарисуйте небольшой горизонтальный прямоугольник — будущий список.
  3. Щелкните по нарисованному списку правой кнопкой мыши и выберите команду Формат объекта (Format control). В появившемся диалоговом окне задайте
    • Формировать список по диапазону — выделите ячейки с наименованиями товаров, которые должны попасть в список
    • Связь с ячейкой — укажите ячейку куда нужно выводить порядковый номер выбранного пользователем элемента.
    • Количество строк списка — сколько строк показывать в выпадающем списке. По умолчанию — 8, но можно больше, чего не позволяет предыдущий способ.

После нажатия на ОК списком можно пользоваться.

Чтобы вместо порядкового номера элемента выводилось его название можно дополнительно использовать функцию ИНДЕКС (INDEX), которая умеет выводить содержимое нужной по счету ячейки из диапазона:

Способ 4.

Элемент ActiveX

Этот способ частично напоминает предыдущий. Основное отличие в том, что на лист добавляется не элемент управления, а элемент ActiveX «Поле со списком» из раскрывающегося набора под кнопкой Вставить (Insert) с вкладки Разработчик (Developer):

Механизм добавления тот же — выбираем объект из списка и рисуем его на листе. А вот дальше начинаются серьезные отличия от предыдущего способа.

Во-первых, созданный выпадающий ActiveX список может находится в двух принципиально разных состояниях — режиме отладки, когда можно настраивать его параметры и свойства, двигать его по листу и менять размеры и — режиме ввода, когда единственное, что можно — выбирать из него данные. Переключение между этими режимами происходит с помощью кнопки Режим Конструктора (Design Mode) на вкладке Разработчик (Developer):

Если эта кнопка нажата, то мы можем настраивать параметры выпадающего списка, нажав соседнюю кнопку Свойства (Properties), которая откроет окно со списком всех возможных настроек для выделенного объекта:

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

  • ListFillRange — диапазон ячеек, откуда берутся данные для списка. Выделить мышью диапазон он не даст, надо просто вписать его руками с клавиатуры (например, Лист2!A1:A5)
  • LinkedCell — связанная ячейка, куда будет выводиться выбранный из списка элемент
  • ListRows — количество отображаемых строк
  • Font — шрифт, размер, начертание (курсив, подчеркивание и т.д. кроме цвета)
  • ForeColor и BackColor — цвет текста и фона, соответственно

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

При использовании этого способа, также возможно указывать в качестве ListFillRange не только одномерные диапазоны. Можно, например задать диапазон из двух столбцов и нескольких строк, указав дополнительно, что выводить нужно два столбца (свойство ColumnCount=2). Тогда можно получить весьма привлекательные результаты, окупающие все потраченные на дополнительные настройки усилия:

 

Итоговая сравнительная таблица всех способов

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

Ссылки по теме:

  • Выпадающий список с данными из другого файла
  • Создание зависимых выпадающих списков
  • Автоматическое создание выпадающих списков надстройкой PLEX
  • Выбор фото из выпадающего списка
  • Автоматическое удаление уже использованных элементов из выпадающего списка
  • Выпадающий список с автоматическим добавлением новых элементов

Видео: создание раскрывающихся списков и управление ими

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

Обучение Эксель 2013.

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

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

Выпадающие списки

  • Выпадающие списки
    видео
  • Настройки выпадающего списка
    видео
  • Ввод и сообщения об ошибках
    видео
  • Управление раскрывающимися списками
    видео

Следующий: Создайте сводную таблицу и проанализируйте свои данные

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

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

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

  1. Выберите ячейки, которые вы хотите содержать списки.

  2. На ленте щелкните ДАННЫЕ > Проверка данных .

  3. В диалоговом окне установите Разрешить на Список .

  4. Щелкните Source , введите текст или числа (разделенные запятыми, для списка с разделителями-запятыми), которые вы хотите добавить в раскрывающийся список, и щелкните OK .

Хотите больше?

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

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

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

Блокируйте ячейки, чтобы защитить их

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

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

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

На ленте щелкните вкладку ДАННЫЕ и щелкните Проверка данных .

В диалоговом окне установите Разрешить на Список .

Нажмите Источник .

В этом примере мы используем список, разделенный запятыми.

Текст или числа, которые мы вводим в поле Источник , разделяются запятыми.

И нажмите OK . Ячейки теперь имеют раскрывающийся список.

Далее, Настройки выпадающего списка .

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

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

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

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

  • Чтобы добавить элемент, перейдите в конец списка и введите новый элемент.

  • Чтобы удалить элемент, нажмите Удалить .

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

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

  2. Выполните любое из следующих действий:

    • Чтобы добавить элемент, перейдите в конец списка и введите новый элемент.

    • Чтобы удалить элемент, нажмите Удалить .

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

  3. org/ListItem»>

    Перейти к Формулы > Диспетчер имен .

  4. В поле Name Manager щелкните именованный диапазон, который вы хотите обновить.

  5. Щелкните поле Относится к , а затем на листе выберите все ячейки, содержащие записи для раскрывающегося списка.

  6. Щелкните Закрыть , а затем щелкните Да , чтобы сохранить изменения.

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

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

  2. Выполните любое из следующих действий:

    • Чтобы добавить элемент, перейдите в конец списка и введите новый элемент.

    • Чтобы удалить элемент, нажмите Удалить .

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

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

  4. Перейти к Данные > Проверка данных .

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

  6. org/ListItem»>

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

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

  2. Перейти к Данные > Проверка данных .

  3. На вкладке Settings щелкните поле Source , а затем при необходимости измените элементы списка. Каждый элемент должен быть разделен запятой без пробелов между ними, например: Да, Нет, Возможно .

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

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

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

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

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

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

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

  • Чтобы добавить элемент, перейдите в конец списка и введите новый элемент.

  • Чтобы удалить элемент, нажмите Удалить .

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

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

  2. Выполните любое из следующих действий:

    • Чтобы добавить элемент, перейдите в конец списка и введите новый элемент.

    • Чтобы удалить элемент, нажмите Удалить .

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

  3. Перейти к Формулы > Диспетчер имен .

  4. В поле Name Manager щелкните именованный диапазон, который вы хотите обновить.

  5. Щелкните поле Относится к , а затем на листе выберите все ячейки, содержащие записи для раскрывающегося списка.

  6. Щелкните Закрыть , а затем щелкните Да , чтобы сохранить изменения.

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

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

  2. Выполните любое из следующих действий:

    • Чтобы добавить элемент, перейдите в конец списка и введите новый элемент.

    • org/ListItem»>

      Чтобы удалить элемент, нажмите Удалить .

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

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

  4. Перейти к Данные > Проверка данных .

  5. org/ListItem»>

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

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

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

  2. Перейти к Данные > Проверка данных .

  3. На вкладке Settings щелкните поле Source , а затем при необходимости измените элементы списка. Каждый элемент должен быть разделен запятой без пробелов между ними, например: Да, Нет, Возможно .

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

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

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

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

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

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

  1. Выберите ячейки с раскрывающимся списком.

  2. Перейти к Данные > Проверка данных .

  3. На вкладке Параметры щелкните поле Источник . Затем выполните одно из следующих действий:

    • Если поле Источник содержит раскрывающиеся записи, разделенные запятыми, введите новые записи или удалите те, которые вам не нужны. Когда вы закончите, каждая запись должна быть разделена запятой без пробелов. Например: Фрукты, Овощи, Мясо, Гастроном .

    • org/ListItem»>

      Если поле Источник содержит ссылку на диапазон ячеек (например, =$A$2:$A$5 ), нажмите Отменить , а затем добавить или удалить записи из этих ячеек. В этом примере вы должны добавить или удалить записи в ячейках с A2 по A5. Если список записей окажется длиннее или короче исходного диапазона, вернитесь на вкладку Settings и удалите то, что находится в поле Source . Затем щелкните и перетащите, чтобы выбрать новый диапазон, содержащий записи.

    • Если поле «Источник» содержит именованный диапазон, например «Отделы», вам необходимо изменить сам диапазон с помощью настольной версии Excel.

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

No related posts.