Перейти к содержимому

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

  • автор:

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

Выпадающий список в Microsoft Excel

​Смотрите также​- количество отображаемых​ элемента выводилось его​ трюком для таких​Выбрать из раскрывающегося списка​Private Sub Worksheet_Change(ByVal​ может содержать пробелов​ F11. Копируем код​ это имя.​ и отключить, просто​ использовать и более​Сами списки этого типа​

​ с индексом​ Откроется диалоговое окно​

Создание дополнительного списка

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

​ списков является создание​(Choose from drop-down list)​ Target As Range)​ и знаков препинания.​ (только вставьте свои​Любой из вариантов даст​ убрав галочку с​ быстрое решение. Например,​ предполагают широкую область​3​New Name​ создать связанный (или​ ему параметры. Например,​ будет появляться список​ Microsoft Excel в​

tablitsa-zagotovka-i-spisok-v-microsoft-excel

​Font​ использовать функцию​ связанных выпадающих списков​или нажать сочетание​On Error Resume​Создадим первый выпадающий список,​ параметры).Private Sub Worksheet_Change(ByVal​

Присвоение имени в Microsoft Excel

​ такой результат.​ пункта его показа.​ нам нужно ввести​ применения. В одной​, в которой хранятся​(Создание имени).​ зависимый) выпадающий список​ при выборе в​ параметров, среди которых​ таблицах с повторяющимися​- шрифт, размер,​ИНДЕКС (INDEX)​

Создание имени в Microsoft Excel

​ (когда содержимое одного​ клавиш​ Next​ куда войдут названия​ Target As Range)​​ На вкладке об​ все значения в​

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

​ ячейке можно вводить​ названия городов Португалии.​В поле​ в Excel? В​ списке продуктов картофеля,​ можно выбрать любой​ данными, очень удобно​ начертание (курсив, подчеркивание​, которая умеет выводить​ списка меняется в​ALT+стрелка вниз​If Not Intersect(Target,​

Параметры вводимых значений в Microsoft Excel

​ диапазонов.​ Dim lReply As​Необходимо сделать раскрывающийся список​ ошибке можно ввести​ ячейках: начиная с​ не одно значение,​ Мы воспользуемся функцией​Name​ ячейке​

Выпадающий список в программе Microsoft Excel

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

​ предлагается выбрать как​ для добавления в​ использовать выпадающий список.​ и т.д. кроме​ содержимое нужной по​ зависимости от выбора​. Способ не работает,​ Range(«C2:C5»)) Is Nothing​Когда поставили курсор в​ Long If Target.Cells.Count​ со значениями из​ предупреждение о неправильности​ A2 и заканчивая​ как это делается​

Переход в параметры Microsoft Excel

​ВПР​(Имя) введите имя​B1​ меры измерения килограммы​ ячейку.​ С его помощью​

Включение режима разработчика в Microsoft Excel

​ цвета)​ счету ячейки из​ в другом).​ если ячейку и​ And Target.Cells.Count =​ поле «Источник», переходим​ > 1 Then​ динамического диапазона. Если​ вводимых данных. Это​ A4. В строке​ по умолчанию, а​(VLOOKUP) для поиска​Country​мы будем выбирать​

Выбор поля со списком в Microsoft Excel

​ и граммы, а​Второй способ предполагает создание​ можно просто выбирать​ForeColor​ диапазона:​

Форма списка в Microsoft Excel

​Этот способ представляет собой​ столбец с данными​ 1 Then​ на лист и​

Переход в свойства элемента управления в Microsoft Excel

​ Exit Sub If​ вносятся изменения в​ пригодится при отсутствии​ указания источника ставим​ несколько.​ значения из ячейки​для нашего первого​

Свойства элемента управления в Microsoft Excel

​ страну, а в​ при выборе масла​ выпадающего списка с​ нужные параметры из​и​

Редактирование в Microsoft Excel

​Этот способ частично напоминает​ вставку на лист​

Выпадающий список в приложении Microsoft Excel

​ отделяет хотя бы​Application.EnableEvents = False​ выделяем попеременно нужные​ Target.Address = «$C$2″​ имеющийся диапазон (добавляются​ жестких условий контроля​ курсор. А затем​

Протягивание выпадающего списка в Microsoft Excel

Связанные списки

​Вариантов ввода может быть​B1​ именованного диапазона, а​ ячейке​ растительного – литры​ помощью инструментов разработчика,​ сформированного меню. Давайте​BackColor​ предыдущий. Основное отличие​ нового объекта -​ одна пустая строка​newVal = Target​ ячейки.​ Then If IsEmpty(Target)​ или удаляются данные),​ значений или в​ просто выделяем нужный​

​ два:​в таблице с​ в поле​B2​ и миллилитры.​ а именно с​

Таблицы в Microsoft Excel

​ выясним, как сделать​- цвет текста​ в том, что​ элемента управления «поле​ или вам нужен​

Присваивание имени в Microsoft Excel

​Application.Undo​Теперь создадим второй раскрывающийся​ Then Exit Sub​ они автоматически отражаются​ случае их частичного​

Ввод данных в Microsoft Excel

​ диапазон. Программа сама​Только значения из созданного​ названиями стран. После​Refers to​– принадлежащий ей​Прежде всего, подготовим таблицу,​ использованием ActiveX. По​

Ввод данных для второй ячейки в Microsoft Excel

​ раскрывающийся список различными​

Список создан в Microsoft Excel

​ и фона, соответственно​ на лист добавляется​ со списком» с​ товар, который еще​oldval = Target​ список. В нем​ If WorksheetFunction.CountIf(Range(«Деревья»), Target)​ в раскрывающемся списке.​

Таблица создана в Microsoft Excel

​ поставит знак равенства,​ списка.​ того как индекс​(Диапазон) выберите тот,​ город, как на​ где будут располагаться​ умолчанию, функции инструментов​ способами.​Большим и жирным плюсом​ не элемент управления,​ последующей привязкой его​ ни разу не​If Len(oldval) <>​

​ должны отражаться те​

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

​ = 0 Then​​Выделяем диапазон для выпадающего​Как видно из всего​ но пропишет значение​Значения из списка и​ будет известен, мы​ в котором хранится​ примере:​ выпадающие списки, и​ разработчика отсутствуют, поэтому​Скачать последнюю версию​ этого способа является​ а элемент ActiveX​ к диапазонам на​ вводился выше:​ 0 And oldval​ слова, которые соответствуют​ lReply = MsgBox(«Добавить​ списка. В главном​

​ вышесказанного, проблема того,​ не в виде​ любые другие данные.​ выберем список, который​ список стран:​Для начала нужно создать​ отдельно сделаем списки​​ нам, прежде всего,​​ Excel​ возможность быстрого перехода​»Поле со списком»​​ листе. Для этого:​​Выделите ячейки с данными,​ <> newVal Then​ выбранному в первом​

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

​ введенное имя «​ меню находим инструмент​ как в Excel​ «(A2:A4)», как это​Итак, начинаем работу с​ станет источником данных​=Sheet3!$A$3:$A$5​ базу данных. На​ с наименованием продуктов​ нужно будет их​Самым удобным, и одновременно​ к нужному элементу​из раскрывающегося набора​В Excel 2007/2010 откройте​​ которые должны попасть​​Target = Target​​ списке названию. Если​​ & _ Target​​ «Форматировать как таблицу».​​ сделать выпадающие списки,​ делается в соответствующей​​ Excel. Создать выпадающий​​ для нашего второго​​Нажмите​​ втором листе я​ и мер измерения.​​ включить. Для этого,​​ наиболее функциональным способом​ в списке при​ под кнопкой​

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

​ вкладку​ в выпадающий список​ & «,» &​ «Деревья», то «граб»,​ & » в​Откроются стили. Выбираем любой.​ разрешается не так​ строке формул, а​ список здесь достаточно​ выпадающего списка. Для​ОК​ занес список стран,​Присваиваем каждому из списков​

​ переходим во вкладку​ создания выпадающего списка,​ вводе первых букв​Вставить (Insert)​Разработчик (Developer)​ (например, наименованиями товаров).​ newVal​ «дуб» и т.д.​ выпадающий список?», vbYesNo​ Для решения нашей​​ уж и сложно.​​ в варианте «=$A$2:$A$4».​​ просто. Но сначала​​ этого напишем такую​, чтобы сохранить и​ которые хочу дать​​ именованный диапазон, как​​ «Файл» программы Excel,​

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

​ является метод, основанный​​ с клавиатуры(!), чего​​с вкладки​. В более ранних​Если у вас Excel​​Else​​ Вводим в поле​

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

​ + vbQuestion) If​​ задачи дизайн не​​ Вопрос в другом:​​Метод действительно хорош своей​​ необходимо выполнить несколько​ формулу:​ закрыть диалоговое окно.​​ пользователям на выбор​​ это мы уже​ а затем кликаем​ на построении отдельного​

​Разработчик (Developer)​​ версиях — панель​​ 2003 или старше​Target = newVal​

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

​ «Источник» функцию вида​ lReply = vbYes​ имеет значения. Наличие​

​ какой метод использовать?​ быстротой вставки значений.​ предварительных действий, чтобы​=CHOOSE(VLOOKUP(B1,Sheet3!$A$3:$B$5,2,FALSE),England,France,Portugal)​Имена диапазонам, содержащим города,​​ в первом раскрывающемся​​ делали ранее с​ по надписи «Параметры».​ списка данных.​​ остальных способов. Приятным​​:​​ инструментов​​ — выберите в​End If​ =ДВССЫЛ(E3). E3 –​​ Then Range(«Деревья»).Cells(Range(«Деревья»).Rows.Count +​​ заголовка (шапки) важно.​

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

​ Если данных не​​ Но в качестве​​ в конечном итоге​

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

​=ВЫБОР(ВПР(B1;Sheet3!$A$3:$B$5;2;ЛОЖЬ);England;France;Portugal)​ можно присвоить точно​ списке, а в​ обычными выпадающими списками.​​В открывшемся окне переходим​​Прежде всего, делаем таблицу-заготовку,​​ моментом, также, является​​Механизм добавления тот же​Формы (Forms)​​ меню​​If Len(newVal) =​ ячейка с именем​ 1, 1) =​ В нашем примере​ много, подойдет простейший​​ самого главного недостатка​​ вся процедура свелась​Что же делает эта​ таким же образом.​ соседнем столбце указал​В первой ячейке создаём​ в подраздел «Настройка​ где собираемся использовать​​ возможность настройки визуального​​ — выбираем объект​через меню​Вставка — Имя -​ 0 Then Target.ClearContents​ первого диапазона.​ Target End If​ это ячейка А1​ вариант. Второй способ​ можно отметить невозможность​​ только к выбору​​ формула? Она ищет​Теперь мы можем создать​ числовой индекс, который​​ список точно таким​​ ленты», и ставим​ выпадающее меню, а​ представления (цветов, шрифтов​​ из списка и​​Вид — Панели инструментов​ Присвоить​​Application.EnableEvents = True​​Бывает, когда из раскрывающегося​ End If End​ со словом «Деревья».​ является более распространенным.​ использования диапазонов ячеек,​ способа ввода нужных​ значение из ячейки​ выпадающие списки в​ соответствует одному из​ же образом, как​

​ флажок напротив значения​
​ также делаем отдельным​

​ и т.д.)​ рисуем его на​ — Формы (View​​(Insert — Name -​​End If​ списка необходимо выбрать​ If End Sub​ То есть нужно​​ Рядовому пользователю он​​ находящихся на других​ данных.​B1​

​ тех ячейках, где​ списков городов. Списки​ делали это ранее,​

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

​ «Разработчик». Жмем на​ списком данные, которые​При использовании этого способа,​ листе. А вот​ — Toolbars -​​ Define),​​End Sub​ сразу несколько элементов.​Сохраняем, установив тип файла​ выбрать стиль таблицы​

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

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

​ также возможно указывать​ дальше начинаются серьезные​
​ Forms)​
​если Excel 2007​

​Не забываем менять диапазоны​

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

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

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

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

как в excel сделать выпадающие списки

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

  • ​ Эти данные можно​ListFillRange​
  • ​ способа.​ не видно, то​

​ откройте вкладку​ создаем классическим способом.​Создаем стандартный список с​ списком. Вкладка «Разработчик»​ диапазона:​ не ограничиваются только​ позволяющий решить проблему​ Как сделать в​ использует функция​(в ней мы​,​

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

​ данных, но в​ названием «Разработчик», куда​ размещать как на​не только одномерные​Во-первых, созданный выпадающий ActiveX​ нажмите кнопку​Формулы (Formulas)​ А всю остальную​ помощью инструмента «Проверка​ — «Код» -​Ставим курсор в ячейку,​ использованием простейших функций,​ того, как в​ Excel выпадающие списки​CHOOSE​ будем выбирать страну),​F​ графе «Источник» вводим​ мы и перемещаемся.​

excel создать выпадающий список

​ этом же листе​ диапазоны. Можно, например​ список может находится​Офис — Параметры Excel​и воспользуйтесь кнопкой​ работу будут делать​ данных». Добавляем в​ «Макросы». Сочетание клавиш​ где будет находиться​ здесь оптимальным станет​ Excel сделать выпадающие​ на основе стандартных​(ВЫБОР), чтобы выбрать​

Выпадающий список в Excel 2007: ручной ввод данных

​ откройте вкладку​и​ функцию «=ДВССЫЛ» и​ Чертим в Microsoft​ документа, так и​ задать диапазон из​ в двух принципиально​-​Диспетчер имен (Name Manager)​ макросы.​ исходный код листа​ для быстрого вызова​ выпадающий список. Открываем​ создание именованных списков​ списки. Сначала создается​ возможностей программы? Очень​ 1-й, 2-й или​Data​H​ адрес первой ячейки.​

Источник из диапазона

​ Excel список, который​ на другом, если​ двух столбцов и​ разных состояниях -​флажок​, затем​На вкладке «Разработчик» находим​ готовый макрос. Как​ – Alt +​ параметры инструмента «Проверка​ с последующим вводом​ сам диапазон с​ просто. Сначала выбираем​ 3-й именованный диапазон.​(Данные), нажмите​. Так, например, рядом​ Например, =ДВССЫЛ($B3).​ должен стать выпадающим​ вы не хотите,​

excel выпадающий список в ячейке

​ нескольких строк, указав​ режиме отладки, когда​Отображать вкладку Разработчик на​Создать​ инструмент «Вставить» –​ это делать, описано​ F8. Выбираем нужное​ данных» (выше описан​ диапазонов данных именно​

Ввод именованных диапазонов (списков)

​ присвоением соответствующего имени.​ одну ячейку или​Вот так будет выглядеть​Data Validation​ с​Как видим, список создан.​ меню. Затем, кликаем​ чтобы обе таблице​ дополнительно, что выводить​ можно настраивать его​ ленте (Office Button​. Введите имя (можно​ «ActiveX». Здесь нам​ выше. С его​ имя. Нажимаем «Выполнить».​ путь). В поле​ из них. Поскольку​ А после этого​ несколько, в которых​ наш второй раскрывающийся​(Проверка данных), а​France​Теперь, чтобы и нижние​ на Ленте на​ располагались визуально вместе.​ нужно два столбца​ параметры и свойства,​ — Excel Options​

выпадающий список в excel 2007

Управление параметрами списка

​ любое, но обязательно​ нужна кнопка «Поле​ помощью справа от​Когда мы введем в​ «Источник» прописываем такую​ в самом именованном​ в строке источника​ предполагается ввести какой-то​ список:​ затем в выпадающем​стоит индекс​ ячейки приобрели те​ значок «Вставить», и​Выделяем данные, которые планируем​ (свойство​ двигать его по​ — Show Developer​ без пробелов и​ со списком» (ориентируемся​ выпадающего списка будут​ пустую ячейку выпадающего​ функцию:​ списке изменение производится​ нужно будет просто​ диапазон значений, и​В результате мы получим​ меню выберите​2​ же свойства, как​ среди появившихся элементов​ занести в раскрывающийся​ColumnCount​ листу и менять​ Tab in the​ начать с буквы!)​

Заключение

​ на всплывающие подсказки).​ добавляться выбранные значения.Private​ списка новое наименование,​Протестируем. Вот наша таблица​ быстро и просто.​ ввести его название,​ правым кликом вызываем​ два связанных (или​Data Validation​, который соответствует списку​ и в предыдущий​ в группе «Элемент​ список. Кликаем правой​=2). Тогда можно получить​ размеры и -​ Ribbon)​ для выделенного диапазона​Щелкаем по значку –​ Sub Worksheet_Change(ByVal Target​ появится сообщение: «Добавить​ со списком на​ В общем, выбрать​ поставив перед ним​ контекстное меню. В​ зависимых) выпадающих списка.​(Проверка данных).​ городов​ раз, выделяем верхние​ ActiveX» выбираем «Поле​ кнопкой мыши, и​

​ весьма привлекательные результаты,​

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

​ режиме ввода, когда​Найдите значок выпадающего списка​ (например​ становится активным «Режим​ As Range) On​ введенное имя баобаб​ одном листе:​

​ для себя самый​ знак равенства. Такая​ нем используем сначала​ Если мы выбираем​Откроется диалоговое окно​2​ ячейки, и при​ со списком».​ в контекстном меню​ окупающие все потраченные​

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

​ единственное, что можно​ среди элементов управления​Товары​ конструктора». Рисуем курсором​ Error Resume Next​

Создание выпадающего списка.

​ в выпадающий список?».​Добавим в таблицу новое​ удобный способ можно​ методика позволяет создавать​

  1. ​ раздел «Данные», затем​ страну​Ввод значений.
  2. ​Data Validation​. Позже Вы увидите,​ нажатой клавише мышки​Кликаем по месту, где​Проверка вводимых значений.
  3. ​ выбираем пункт «Присвоить​ на дополнительные настройки​ — выбирать из​ форм (не ActiveX!).​

​). Нажмите​ (он становится «крестиком»)​

​ If Not Intersect(Target,​

Выпадающий список в Excel с подстановкой данных

​Нажмем «Да» и добавиться​ значение «елка».​ без проблем.​ списки с использованием​ – пункт «Проверка​France​(Проверка вводимых значений).​ как этот индекс​

  1. ​ «протаскиваем» вниз.​ должна быть ячейка​ имя…».​ усилия:​Форматировать как таблицу.
  2. ​ него данные. Переключение​ Ориентируйтесь по всплывающим​ОК​ небольшой прямоугольник –​ Range(«Е2:Е9»)) Is Nothing​ еще одна строка​Теперь удалим значение «береза».​Автор: Панькова Оксана Владимировна​ данных и значений,​ данных». И переходим​, в связанном списке​Мы хотим дать пользователю​ будет использован.​Выпадающий список.
  3. ​Всё, таблица создана.​ со списком. Как​Открывается форма создания имени.​Способ 1.​ между этими режимами​ подсказкам -​.​ место будущего списка.​

​ And Target.Cells.Count =​ со значением «баобаб».​Осуществить задуманное нам помогла​

Список и таблица.

​Под выпадающим списком понимается​ которые располагаются на​

Добавлено значение елка.

​ на вкладку параметров.​

Удалено значение береза.

​ у нас будут​ на выбор список​Если Вы работаете в​

​Мы разобрались, как сделать​ видите, форма списка​ В поле «Имя»​Примитивный​ происходит с помощью​Поле со списком​Выделите ячейки (можно сразу​

  1. ​Жмем «Свойства» – открывается​ 1 Then Application.EnableEvents​Когда значения для выпадающего​ «умная таблица», которая​ содержание в одной​Создание имени.
  2. ​ разных листах книги.​В ней имеется условие​ города только из​ вариантов, поэтому в​ Excel 2010, то​
  3. ​ выпадающий список в​ появилась.​ вписываем любое удобное​Способ 2.​ кнопки​:​ несколько), в которых​Сообщение об ошибке.
  4. ​ перечень настроек.​ = False If​ списка расположены на​ легка «расширяется», меняется.​ ячейке нескольких значений.​ И именно такой​ (критерий) проверки со​ Франции.​ поле​ можете создать лист-источник​ Экселе. В программе​Затем мы перемещаемся в​ наименование, по которому​Стандартный​Режим Конструктора (Design Mode)​Щелкните по значку​ хотите получить выпадающий​Вписываем диапазон в строку​ Len(Target.Offset(0, 1)) =​ другом листе или​Теперь сделаем так, чтобы​ Когда пользователь щелкает​ вариант в основном​ строкой типа данных,​Из этой статьи Вы​Allow​ в отдельной рабочей​ можно создавать, как​ «Режим конструктора». Жмем​ будем узнавать данный​Способ 3.​на вкладке​ и нарисуйте небольшой​
  5. ​ список и выберите​ ListFillRange (руками). Ячейку,​Сообщение об ошибке.
  6. ​ 0 Then Target.Offset(0,​ в другой книге,​ можно было вводить​ по стрелочке справа,​ применяется в версиях​ где и выбираем​ узнали, как можно​(Тип данных) выберите​

​ книге. Если же​ простые выпадающие списки,​ на кнопку «Свойства​ список. Но, это​Элемент управления​Разработчик (Developer)​

​ горизонтальный прямоугольник -​ в меню (на​ куда будет выводиться​

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

​ 1) = Target​ стандартный способ не​ новые значения прямо​ появляется определенный перечень.​ редактора Excel 2007​ значение «Список». В​ сделать простейшие связанные​List​ у Вас версия​ так и зависимые.​ элемента управления».​

  1. ​ наименование должно начинаться​Способ 4.​:​
  2. ​ будущий список.​ вкладке)​ выбранное значение –​

​ Else Target.End(xlToRight).Offset(0, 1)​ работает. Решить задачу​ в ячейку с​ Можно выбрать конкретное.​ и ниже, поскольку​ принципе, остальные поля​ выпадающие списки в​(Список). Это активирует​ Excel 2003 года,​ При этом, можно​

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

​Открывается окно свойств элемента​

Три именованных диапазона.

​ обязательно с буквы.​Элемент ActiveX​Если эта кнопка нажата,​Щелкните по нарисованному списку​Данные — Проверка (Data​ в строку LinkedCell.​ = Target End​ можно с помощью​

  1. ​ этим списком. И​Очень удобный инструмент Excel​ эти модификации не​Список диапазонов.
  2. ​ пока можно оставить​ Microsoft Excel. Вы​ поле​ и Вы планируете​ использовать различные методы​Таблица со списком.
  3. ​ управления. В графе​ Можно также вписать​Сложность​ то мы можем​ правой кнопкой мыши​ — Validation)​ Для изменения шрифта​ If Target.ClearContents Application.EnableEvents​ функции ДВССЫЛ: она​ данные автоматически добавлялись​ для проверки введенных​ умеют работать с​ без изменений. Чуть​ Второй раскрывающийся список.

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

​ можете взять этот​Source​ использовать именованный диапазон,​ создания. Выбор зависит​ «ListFillRange» вручную через​

  1. ​ примечание, но это​низкая​ настраивать параметры выпадающего​ и выберите команду​. Из выпадающего списка​ и размера –​ = True End​ сформирует правильную ссылку​ в диапазон.​ данных. Повысить комфорт​ диапазонами, в которых​ ниже расположена строка,​ простой пример и​(Источник), где необходимо​ то значения должны​ от конкретного предназначения​ двоеточие прописываем диапазон​ не обязательно. Жмем​средняя​ списка, нажав соседнюю​Формат объекта (Format control)​Тип данных (Allow)​ Font.​ If End Sub​ на внешний источник​Сформируем именованный диапазон. Путь:​
  2. ​ работы с данными​ указаны данные из​ в которой будет​ использовать его для​ указать имя диапазона​ находиться в той​ списка, целей его​ ячеек таблицы, данные​ на кнопку «OK».​высокая​ кнопку​. В появившемся диалоговом​выберите вариант​Скачать пример выпадающего списка​Чтобы выбранные значения показывались​ информации.​ «Формулы» — «Диспетчер​ позволяют возможности выпадающих​ разных листов.​
  3. ​ вводиться источник значений.​ решения реальных задач.​ со странами. Введите​ же книге, можно​ создания, области применения,​
    ​ которой будут формировать​Переходим во вкладку «Данные»​
    ​Возможность настройки шрифта, цвета​Свойства (Properties)​
    ​ окне задайте​Список (List)​При вводе первых букв​ снизу, вставляем другой​
    ​Делаем активной ячейку, куда​
    ​ имен» — «Создать».​
    ​ списков: подстановка данных,​
    ​Что касается управления списками,​
    ​ Вот он-то нас​Урок подготовлен для Вас​ в этом поле​
    ​ на другом листе.​ и т.д.​ пункты выпадающего списка.​
    ​ программы Microsoft Excel.​
    ​ и т.д.​
    ​, которая откроет окно​
    ​Формировать список по диапазону​и введите в​
    ​ с клавиатуры высвечиваются​
    ​ код обработчика.Private Sub​
    ​ хотим поместить раскрывающийся​

​ Вводим уникальное название​ отображение данных другого​ в редакторе Excel​ и интересует.​ командой сайта office-guru.ru​ «=Country» и жмите​

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

  1. ​Мы будем использовать именованные​Автор: Максим Тютюшев​Далее, кликаем по ячейке,​ Выделяем область таблицы,​нет​ со списком всех​Вставить ActiveX.
  2. ​- выделите ячейки​ строчку​ подходящие элементы. И​ Worksheet_Change(ByVal Target As​ список.​ диапазона – ОК.​Элемент ActiveX.
  3. ​ листа или файла,​ есть множество нужных​Свойства ActiveX.
  4. ​Начнем с ручного ввода​Источник: http://www.excel-user.com/2011/02/cascading-validation-lists.html​ОК​ диапазоны и сделаем​ПРЕДСТАВЬТЕ СИТУАЦИЮ:​ и в контекстном​ где собираемся применять​нет​

​ возможных настроек для​

​ с наименованиями товаров,​Источник (Source)​ это далеко не​ Range) On Error​Открываем параметры проверки данных.​Создаем раскрывающийся список в​ наличие функции поиска​ функций. Но самая,​ значений. Если говорить​Перевел: Антон Андронов​

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

Видео

​. Теперь нам нужно​ так, чтобы эти​Мы хотим создать​ меню последовательно переходим​

​ выпадающий список. Жмем​да​ выделенного объекта:​ которые должны попасть​

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

​знак равенства и​ все приятные моменты​ Resume Next If​ В поле «Источник»​ любой ячейке. Как​​ и зависимости.​ ​ пожалуй, интересная –​​ о том, как​Автор: Антон Андронов​​ сделать второй раскрывающийся​​ связанные выпадающие списки​ в Excel небольшую​ по пунктам «Объект​ на кнопку «Проверка​Количество отображаемых строк​Самые нужные и полезные​ в список​ имя диапазона (т.е.​ данного инструмента. Здесь​

Способ 2. Стандартный
  1. ​ Not Intersect(Target, Range(«Н2:К2»))​ вводим формулу: =ДВССЫЛ(“[Список1.xlsx]Лист1!$A$1:$A$9”).​ это сделать, уже​Путь: меню «Данные» -​
  2. ​ распространение параметров для​ в Excel сделать​Многие пользователи стандартного офисного​ список, чтобы пользователи​​ работали во всех​ табличку, где можно​​ ComboBox» и «Edit».​ данных», расположенную на​​всегда 8​ свойства, которые можно​Связь с ячейкой​​=Товары​​ можно настраивать визуальное​​ Is Nothing And​​Имя файла, из которого​​ известно. Источник –​​ инструмент «Проверка данных»​ ввода с одинаковыми​ выпадающие списки таким​ пакета от Microsoft​ могли выбрать город.​ версиях Excel. Следующий​​ выбрать страну и​​Выпадающий список в Microsoft​​ Ленте.​​любое​
  3. ​ и нужно настроить:​- укажите ячейку​).​ представление информации, указывать​ Target.Cells.Count = 1​ берется информация для​​ имя диапазона: =деревья.​ — вкладка «Параметры».​​ условиями. Применяется она​​ методом, решение состоит​​ явно недооценивают табличный​​ Мы поместим этот​​ шаг – создать​ соответствующий ей город.​​ Excel готов.​​Открывается окно проверки вводимых​Быстрый поиск элемента по​​ListFillRange​​ куда нужно выводить​

​Нажмите​​ в качестве источника​​ Then Application.EnableEvents =​

​ списка, заключено в​

​Снимаем галочки на вкладках​ Тип данных –​ в тех случаях,​ в том, чтобы​ редактор Excel. Выпадающий​ раскрывающийся список в​ именованные диапазоны для​ При этом с​Чтобы сделать и другие​ значений. Во вкладке​ первым буквам​- диапазон ячеек,​ порядковый номер выбранного​ОК​ сразу два столбца.​ False If Len(Target.Offset(1,​ квадратные скобки. Этот​ «Сообщение для ввода»,​

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

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

  1. ​ «Параметры» в поле​нет​​ откуда берутся данные​​ пользователем элемента.​.​У кого мало времени​​ 0)) = 0​​ файл должен быть​​ «Сообщение об ошибке».​Ввести значения, из которых​ имеется слишком много​ прописывать значения самостоятельно.​​ экономящий время, затрачиваемое​B2​ вкладке​​ необходимо ограничить доступные​​ списком, просто становимся​​ «Тип данных» выбираем​​нет​ для списка. Выделить​Количество строк списка​Все! Наслаждайтесь!​ и нужно быстро​ Then Target.Offset(1, 0)​
  2. ​ открыт. Если книга​ Если этого не​ будет складываться выпадающий​ проверок и их​ При этом стоит​​ на ввод или​​. А теперь внимание​

  • ​ с нужными значениями​​ сделать, Excel не​ список, можно разными​ условия нужно поменять.​ обратить внимание, что​
  • ​ поиск данных, в​​ – фокус! Нам​(Формулы) есть команда​ и городов, из​ край готовой ячейки,​
  • ​ поле «Источник» ставим​​Необходимость использования дополнительной функции​ не даст, надо​ показывать в выпадающем​ источника данных для​ смотрим обучающее видео:​ Target.End(xlDown).Offset(1, 0) =​ находится в другой​

​ позволит нам вводить​​ способами:​​ Для этого в​

​ для русскоязычной локализации​ нем создается за​ нужно проверить содержимое​Name Manager​​ которых они могут​​ нажимаем кнопку мыши,​ знак равно, и​ИНДЕКС​ просто вписать его​

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

​ списке. По умолчанию​ списка может выступать​Кому интересны подробности и​ Target End If​ папке, нужно указывать​ новые значения.​​Вручную через «точку-с-запятой» в​​ разделе проверки данных,​ вводить значения нужно​​ пару секунд, в​​ ячейки с названием​​(Диспетчер имён). Нажав​​ выбирать. В первой​

​ и протягиваем вниз.​ сразу без пробелов​нет​ руками с клавиатуры​ — 8, но​ и динамический именованный​ нюансы всех описанных​ Target.ClearContents Application.EnableEvents =​

​ путь полностью.​Вызываем редактор Visual Basic.​ поле «Источник».​ который использовался во​ через точку с​ отличие от Access,​ страны (ячейка B1),​ на нее, откроется​ ячейке мы сделаем​Также, в программе Excel​ пишем имя списка,​да​ (например, Лист2!A1:A5)​ можно больше, чего​ диапазон, например прайс-лист.​ способов — дальше​ True End If​​Возьмем три именованных диапазона:​​ Для этого щелкаем​​Ввести значения заранее. А​​ всех предыдущих методах,​

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

​ Тогда при дописывании​ по тексту.​ End Sub​

  • ​Это обязательное условие. Выше​​ правой кнопкой мыши​ в качестве источника​ обращаемся к вкладке​ задания применяется только​ сложнее. Ниже будут​ соответствующий базе данных​Name Manager​ во второй будут​
  • ​ выпадающие списки. Это​​ выше. Жмем на​Возможность создания связанных выпадающих​- связанная ячейка,​ способ.​
  • ​ новых товаров к​​Один щелчок правой кнопкой​Чтобы выбираемые значения отображались​
  • ​ описано, как сделать​​ по названию листа​ указать диапазон ячеек​ «Сообщения для ввода».​ запятая. Неудобство такого​
  • ​ приведены три основных​​ с городами. Если​​(Диспетчер имён).​​ доступны только принадлежащие​ такие списки, когда​

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

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

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

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

Ваш браузер не поддерживает видео. Установите Microsoft Silverlight, Adobe Flash Player или Internet Explorer 9.

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

Таблица Excel, используемая в качестве источника для списка проверки данных

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

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

Перейдите на вкладку Данные на ленте, а затем — Проверка данных.

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

На вкладке Параметры в поле Разрешить выберите Список.

Выберите в поле Источник , а затем выберите диапазон списка. В примере данные находятся на листе «Города» в диапазоне A2:A9. Обратите внимание на то, что строка заголовков отсутствует в диапазоне, так как она не является одним из вариантов, доступных для выбора.

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

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

Установите флажок в раскрывающемся списке В ячейке .

Перейдите на вкладку Входное сообщение .

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

Вкладка "Сообщение для ввода" в диалоговом окне "Проверка данных"

Перейдите на вкладку Оповещение об ошибке .

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

Параметры сообщения об ошибке для раскрывающегося списка проверки данных

Не знаете, какой вариант выбрать в поле Стиль ?

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

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

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

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

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

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

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

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

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

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

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

Выделите ячейки, для которых нужно ограничить ввод данных.

На вкладке Данные в разделе Сервис выберите Проверка данных или Проверка.

Меню "Данные" на панели инструментов Excel с выбранной кнопкой "Проверка данных"

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

Перейдите на вкладку Параметры , а затем во всплывающем меню Разрешить выберите Список.

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

Диалоговое окно свернется, чтобы было видно весь лист.

Нажмите клавишу RETURN или выберите Развернуть Кнопка "Развернуть" в диалоговом окне "Проверка данных", чтобы восстановить диалоговое окно, а затем нажмите кнопку ОК.

Можно также ввести значения, разделенные запятой, непосредственно в поле Источник .

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

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

См. также

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

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

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

Перейдите на вкладку Данные на ленте, а затем выберите Проверка данных.

На вкладке Параметры в поле Разрешить выберите Список.

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

Фрукты;Овощи;Зерновые культуры;Молочные продукты;Перекусы

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

Установите флажок в раскрывающемся списке В ячейке .

Перейдите на вкладку Входное сообщение .

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

Перейдите на вкладку Оповещение об ошибке .

Если вы хотите, чтобы сообщение отображалось, когда кто-то вводит что-то, чего нет в вашем списке, проверка флажок Показывать оповещение, выберите параметр в поле Тип и введите название и сообщение. Если вы не хотите, чтобы сообщение отображалось, снимите этот флажок.

Нажмите кнопку ОК.

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

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

5 способов создать выпадающий список в ячейке Excel

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

Зачем нужен выпадающий список?

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

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

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

1 — Самый быстрый способ создать выпадающий список.

Как проще всего добавить выпадающий список в таблицу Excel? Всего один щелчок правой кнопкой мыши по пустой клетке под столбцом с данными, затем команда контекстного меню «Выберите из раскрывающегося списка» (Choose from drop-down list). А можно просто стать в нужное место и нажать сочетание клавиш Alt+стрелка вниз . Появится отсортированный перечень уникальных ранее введенных значений.
Способ не работает, если нашу ячейку и столбец с записями отделяет хотя бы одна пустая строка или вы хотите ввести то, что еще не вводилось выше. На нашем примере это хорошо видно.

простой способ создания

2 — Используем меню.

Давайте рассмотрим небольшой пример, в котором нам нужно постоянно вводить в таблицу одни и те же наименования товаров. Выпишите в столбик данные, которые мы будем использовать (например, названия товаров). В нашем примере — в диапазон G2:G7.

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

заполнить через меню

Бонусом здесь идет возможность задать подсказку и сообщение об ошибке, если автоматически вставленное значение вы захотите изменить вручную. Для этого существуют вкладки Подсказка по вводу (Input Message) и Сообщение об ошибке (Error Alert).

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

создаем из именованного диапазона списки в excel

К примеру, диапазону I2:I13, содержащему названия месяцев, можно присвоить наименование «месяцы». Затем имя можно ввести в поле «Источник».

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

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

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

Используйте для разделения значений точку с запятой, запятую, либо другой символ, установленный у вас в качестве разделителя элементов. (Смотрите Панель управления — Часы и регион — Форматы — Дополнительно — Числа.)

3 — Создаем элемент управления.

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

  1. Откройте вкладку Разработчик (Developer). Если её не видно, то в Excel 2007 нужно нажать кнопку Офис – Параметры – флажок Отображать вкладку Разработчик на ленте (Office Button – Options – Show Developer Tab in the Ribbon) или в версии 2010–2013 и выше щелкните правой кнопкой мыши по ленте, выберите команду Настройка ленты (Customize Ribbon) и включите отображение вкладки Разработчик (Developer) с помощью флажка.
  2. Найдите нужный значок среди элементов управления (см.рисунок ниже).

получаем в ячейку номер позиции в списке

Но нам ведь нужен не этот номер, а соответствующее ему слово. Используем функцию ИНДЕКС (INDEX в английском варианте). Она позволяет найти в списке значений одно из них соответственно его порядковому номеру. В качестве аргументов ИНДЕКС укажите диапазон ячеек (F5:F11) и адрес с полученным порядковым номером (F2).

Формулу в F3 запишем, как показано на рисунке:

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

Обратите также внимание, что здесь мы не привязаны ни к какой конкретному месту таблицы. Таким списком Excel удобно пользоваться, поскольку его можно свободно «перетаскивать» мышкой в любое удобное место. Для этого на вкладке «Разработчик» нужно активизировать режим конструктора.

4 — Элемент ActiveX

Действуем аналогично предыдущему способу, но выбираем иконку чуть ниже — из раздела «Элементы ActiveX».

элемент ActiveX в раскрывающемся списке

Определяем перечень допустимых значений (1). Обратите внимание, что здесь для показа можно выбирать сразу несколько колонок. Затем выбираем адрес, по которому будет вставлена нужная позиция из перечня (2).Указываем количество столбцов, которые будут использованы как исходные данные (3), и номер столбца, из которого будет происходить выбор для вставки на лист (4). Если укажете номер столбца 2, то в А5 будет вставлена не фамилия, а должность. Можно также указать количество строк, которое будет выведено в перечне. По умолчанию — 8. Остальные можно прокручивать мышкой (5).

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

5 — Выпадающий список в Excel с автозаполнением

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

Вот как автозаполнение может выглядеть на простом примере:

Способ 1. Укажите заведомо большой источник значений для списка.

Самая простая и несложная хитрость. В начале действуем по обычному алгоритму действий: в меню выбираем на вкладке Данные – Проверка . (Data – Validation). Из перечня Тип данных (Allow) выберите вариант Список (List). Поставьте курсор в поле Источник (Source). Зарезервируем в списке набор с большим запасом: например, до 55-й строки, хотя занято у нас только 7. Обязательно не забудьте поставить галочку в чекбоксе «Игнорировать пустые . «. Тогда ваш «резерв» из пустых значений не будет вам мешать.

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

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

Конечно, в качестве источника можно указать и весь столбец:

Но обработка такого большого количества ячеек может несколько замедлить вычисления. Особенно в больших таблицах Excel.

Способ 2. Применяем именованный диапазон.

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

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

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

Так вы присвоите какое-то имя этому диапазону Excel.

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

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

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

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

А теперь давайте пойдем дальше и посмотрим, как можно работать с динамическим диапазоном, который автоматически подстраивается под вводимые значения.

Способ 3. Выпадающий список на основе «умной» таблицы Excel.

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

Любой набор значений в таблице может быть таким образом преобразован. Например, A1:A8. Выделите их мышкой. Затем преобразуйте в таблицу, используя меню Главная — Форматировать как таблицу (Home — Format as Table). Укажите, что в первой строке у вас находится название столбца. Это будет «шапка» вашей таблицы. Внешний вид может быть любым: это не более чем внешнее оформление и ни на что больше оно не влияет.

Как уже было сказано выше, «умная» таблица хороша для нас тем, что динамически меняет свои размеры при добавлении в нее информации. Если в строку ниже нее вписать что-либо, то она тут же присоединит к себе её. Таким образом, новые значения можно просто дописывать. К примеру, впишите в A9 слово «кокос», и таблица тут же расширится до 9 строк.

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

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

и не считает его формулой. Хотя в обычных выражениях на листе вашей рабочей книги это вполне будет работать. Эта конструкция обозначает ссылку на первый столбец. Но в поле «Источник» она почему-то игнорируется.

Чтобы использовать «умную таблицу» как источник, нам придется пойти на небольшую хитрость и воспользоваться функцией ДВССЫЛ (INDIRECT в английском варианте). Эта функция преобразует текстовую переменную в обычную ссылку.

добавляем в ячейку автоматически пополняемые списки

Формула теперь будет выглядеть следующим образом:

Таблица5 — имя, автоматически присвоенное «умной таблице». У вас оно может быть другим. На вкладке меню Конструктор (Design) можно изменить стандартное имя на свое (но без пробелов!). По нему мы сможем потом адресоваться к нашей таблице на любом листе книги.

«Продукт» — название нашего первого и единственного столбца, присвоено по его заголовку.

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

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

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

А вот еще полезная для вас информация:

Как сделать выпадающий список с выбором нескольких значений — В статье показано, как создать выпадающий список в Excel, который позволяет пользователям выбирать несколько значений с дубликатами или без них. Также рассмотрим, как разрешить множественный выбор только в отдельных строках, столбцах или ячейках, как это…
Как сделать зависимый выпадающий список в Excel? — Одной из наиболее полезных функций проверки данных является возможность создания выпадающего списка, который позволяет выбирать значение из предварительно определенного перечня. Но как только вы начнете применять это в своих таблицах,…
alt=»использование формул для выпадающего списка» width=»141″ height=»150″ /> Создаем выпадающий список в Excel при помощи формул — Задача: Создать выпадающий список в Excel таким образом, чтобы в него автоматически попадали все новые значения. Сделаем это при помощи формул, чтобы этот способ можно было использовать не только в…

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

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

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

Как сделать выпадающий список в Excel 2010 или 2016 с помощью одной командой на панели инструментов? На вкладке «Данные» в разделе «Работа с данными» найдите кнопку «Проверка данных». Нажмите на нее и выберите первый пункт.

Откроется окно. Во вкладке «Параметры» в выпадающем разделе «Тип данных» выберите «Список».

Снизу появится строка для указания источников.

Указывать информацию можно по-разному.

  • Ручной ввод
    Введите перечень через точку с запятой.
  • Выбор диапазона значений с листа Excel
    Для этого начните выделять ячейки мышью.

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

Сначала назначим имя. Для этого создайте на любом листе такую таблицу.

Выделите ее и нажмите правую кнопку мыши. Щелкните по команде «Присвоить имя».

Введите имя в строку сверху.

Вызовите окно «Проверка данных» и в поле «Источник» укажите имя, поставив перед ним знак «=».

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

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

Подстановка динамических данных Excel

Если Вы добавите какое-то значение в диапазон данных, которые подставляются в перечень, то в нем изменения не произойдет, пока вручную не будут указаны новые адреса. Чтобы связать диапазон и активный элемент, необходимо оформить первый как таблицу. Создайте вот такой массив.

Выделите его и на вкладке «Главная» выберите любой стиль таблицы.

Обязательно поставьте галочку внизу.

Вы получите такое оформление.

Создайте активный элемент, как было описано выше. В качестве источника введите формулу

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

Функция ДВССЫЛ создает ссылку на ячейку или диапазон. Теперь ваш элемент в ячейке привязан к массиву данных.

Попробуем увеличить количество городов.

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

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

Как убрать (удалить) выпадающий список в Excel

Откройте окно настройки выпадающего списка и выберите «Любое значение» в разделе «Тип данных».

Ненужный элемент исчезнет.

Зависимые элементы

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

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

Это будет название города.

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

Поэтому переименуем эти города, поставив нижнее подчеркивание.

Первый элемент в ячейке A9 создаем обычным образом.

А во втором пропишем формулу:


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

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

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

Для второго перечня нужно ввести формулу:

Функция СМЕЩ возвращает ссылку на диапазон, который смещен относительно первой ячейки на определенное число строк и столбцов:=СМЕЩ(начало; вниз; вправо; размер_в_строках; размер_в_столбцах)

ПОИСКПОЗ возвращает номер ячейки с выбранным в первом списке (E6) городом в указанной области SA:$A.
СЧЕТЕСЛИ считает количество совпадений в диапазоне со значением в указанной ячейке (E6).


Мы получили связанные выпадающие списки в Excel с условием на совпадение и поиском диапазона для него.

Мультивыбор

Часто нам необходимо получить несколько значений из набора данных. Можно вывести их в разные ячейки, а можно объединить в одну. В любом случае необходим макрос.
Нажмите на ярлыке листа внизу правую кнопку мыши и выберите команду «Просмотреть код».

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


Обратите внимание, что в строке

Следует проставить адрес ячейки со списком. У нас это будет E7.

Вернитесь на лист Excel и создайте в ячейке E7 список.

При выборе значения будут появляться под ним.

Следующий код позволит накапливать значения в ячейке.

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

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

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *