Как сделать ячейку с выпадающим списком в excel 2013?

    Добрый день уважаемый читатель!

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

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

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

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

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

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

   И поэтапно создадим выпадающий список в одной ячейке:

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

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

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

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

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

    Не забудьте поблагодарить автора!

Человеческий прогресс основан на желании каждого человека жить не по средствам

Сэмюэл Батлер, философ

Была ли статья полезной? Делитесь ссылкой с друзьями, пишите в Твиттере или ставьте лайк!

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

Чтобы создать раскрывающийся список, на отдельном листе книги или на пустом месте исходного листа создайте строку или столбец с данными без пустых ячеек, выберите его и в поле «Имя» введите имя выбранный список и нажмите Enter (рис. 1).

Рис. 1. Список данных для раскрывающегося списка

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

(рис. 2).

Рис. 2. Поместите, чтобы вставить раскрывающийся список

Затем перейдите на вкладку «Данные — Проверка данных» и нажмите кнопку «Проверка данных…» (рис. 3).

Рис. 3. Меню «Проверить данные»

В открывшемся окне в поле «Тип данных» выберите «Список». В поле «Источник» введите название списка, который вы подготовили ранее. Убедитесь, что перед ссылкой на список стоит знак равенства, и нажмите кнопку «ОК» (рис. 4).

Рис. 4. Проверка введенных значений

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

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

Рис. 5. Как работает раскрывающийся список

Видео

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

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

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

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

Способ 2. Стандартный

1. Выберите ячейки с данными, которые нужно включить в раскрывающийся список (например, названия продуктов).
2. Если у вас Excel 2003 или более ранняя версия, выберите «Вставить» — «Имя» — «Определить» в меню, в Excel 2007 или более поздней версии откройте вкладку «Формулы» и нажмите кнопку «Диспетчер имен», а затем — «Создать». Введите имя (возможно любое имя, но оно обязательно без пробелов и начинается с буквы!) Для выбранного диапазона (например, Товары). Щелкните ОК.
3. Выделите ячейки (их может быть несколько одновременно), для которых вы хотите получить раскрывающийся список, и выберите «Данные — Проверка» в меню (во вкладке). В раскрывающемся списке «Разрешить» выберите параметр «Список» и введите знак равенства и имя диапазона (например, «Продукты») в строке «Источник).

Щелкните ОК.

Все! Повеселись!

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

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

Этот метод заключается в том, чтобы вставить новый объект на лист, элемент управления «поле со списком», а затем привязать его к диапазонам на листе. Из-за этого:

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

   Щелкните значок и нарисуйте небольшой горизонтальный прямоугольник — будущий список.

3. Щелкните нарисованный список правой кнопкой мыши и выберите «Управление форматом». В появившемся диалоговом окне установите

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

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

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

Способ 4. Элемент ActiveX

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

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

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

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

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

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

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

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

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

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

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

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

Способ 1 — горячие клавиши и раскрывающийся список в excel

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

Тот же пункт меню можно запустить с помощью комбинации клавиш Alt + Стрелка вниз, и программа автоматически предложит значения ячеек, которые вы ранее заполнили данными в раскрывающемся списке. На изображении ниже программа предложила 4 варианта заполнения (в Excel не отображаются повторяющиеся данные). Единственное условие для работы этого инструмента — отсутствие пустых ячеек между ячейкой, в которую вы вводите данные из списка, и самим списком.

Использование сочетаний клавиш для раскрытия раскрывающегося списка данных

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

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

Способ 2 — самый удобный, простой и наиболее гибкий

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

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

   Создание набора данных для списка

2. В окне «Создать имя» укажите имя для вашего списка (это имя будет далее использоваться в формуле замены). Имя должно быть без пробелов и начинаться с буквы.

   Введите имя для набора данных

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

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

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

Чтобы создать проверку входного значения, введите имя ранее созданного списка

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

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

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

Способ 3 — как в excel сделать выпадающий список с использованием ActiveX

Чтобы использовать этот метод, у вас должна быть включена вкладка «РАЗРАБОТЧИК». По умолчанию эта вкладка отсутствует. Чтобы включить его:

1. Нажмите «Файл» в верхнем левом углу приложения.
2. Выберите «Параметры» и щелкните по нему.
3. В окне «Параметры Excel» на вкладке «Настроить ленту» установите флажок рядом с вкладкой «Разработчик».

Включение вкладки РАЗРАБОТЧИК

Теперь у вас должна быть возможность использовать инструмент Combo Box Tool (элемент управления ActiveX). На вкладке РАЗРАБОТЧИК нажмите кнопку «Вставить» и найдите кнопку «Поле со списком (элемент управления ActiveX)» в элементах управления ActiveX. Нажмите здесь.

Нарисуйте данный объект в раскрывающемся списке Excel в той ячейке, где раскрывающийся список нужен.

Теперь вам нужно настроить этот элемент. Для этого активируйте «Режим дизайна» и нажмите кнопку «Свойства». Вы должны открыть окно свойств).

В открытом окне «Свойства» щелкните созданный ранее элемент Combo Box. Список свойств содержит множество параметров для настройки, и после их просмотра вы можете настроить многие из них, от представления списка до специальных свойств этого объекта.

Но на этапе создания нас интересуют только три основных:

1. ListFillRange — указывает диапазон ячеек, из которого будут взяты значения для раскрывающегося списка. В моем примере я указал два столбца (A2: B7 — я покажу вам, как его использовать дальше). Если требуется только одно значение, отображается A2: A7.
2. ListRows — количество данных в раскрывающемся списке. Элемент управления ActiveX отличается от первого метода тем, что можно указать большой объем данных.
3. ColumnCount — указывает, сколько столбцов данных указать в раскрывающемся списке.

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

Как видите, мы получили выпадающий список в Excel с заменой данных из второго столбца данными «Поставщик».