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

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

Как видите, весь трюк зависимого списка состоит в использовании функции СМЕЩ. Ну хорошо, почти весь. Помогают ей функции ПОИСКПОЗ и СЧЕТЕСЛИ. Функция СМЕЩ позволяет динамически определять диапазоны. Вначале мы определяем ячейку, от которой должен начинаться сдвиг диапазона, а в последующих аргументах определяем его размеры.

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

Поскольку рабочая таблица отсортирована по Категории, то диапазон, который должен быть источником для раскрывающегося списка, будет начинаться там, где впервые встречается выбранная категория. Например, для категории Питание мы хотим отобразить диапазон H6:H11, для Транспорта — диапазон H12: H15 и т. д

Обратите внимание, что все время мы перемещаемся по столбцу H, а единственное, что изменяется, это начало диапазона и его высота (то есть количество элементов в списке)

Начало диапазона будет перемещено относительно ячейки H2 на такое количество ячеек вниз (по числу), сколько составляет номер позиции первой встречающейся категории в столбце Категория. Проще будет понять на примере: диапазон для категории Питание перемещен на 4 ячейки вниз относительно ячейки H2 (начинается с 4 ячейки от H2). В 4-ой ячейке столбца Подкатегория (не включая заголовок, так как речь идет о диапазоне с именем Рабочий_Список), есть слово Питание (его первое появление). Мы используем этот факт собственно для определения начала диапазона. Послужит нам для этого функция ПОИСКПОЗ (введенная в качестве второго аргумента функции СМЕЩ):

Высоту диапазона определяет функция СЧЕТЕСЛИ. Она считает все встречающиеся повторения в категории, то есть слово Питание. Сколько раз встречается это слово, сколько и будет позиций в нашем диапазоне. Количество позиций в диапазоне — это его высота. Вот функция:

Конечно же, обе функции уже включены в функцию СМЕЩ, которая описана выше

Кроме того, обратите внимание, что как в функции ПОИСКПОЗ, так и в СЧЕТЕСЛИ, есть ссылка на диапазон названный Рабочий_Список. Как я уже упоминал ранее, не обязательно использовать имена диапазонов, можно просто ввести $H3: $H15

Однако использование имен диапазонов в формуле делает ее проще и легко читаемой.

Вот и все:

Одна формула, ну не такая уж и простая, но облегчающая работу и защищает от ошибок при вводе данных!

Два варианта использования этого трюка я уже представил. Интересно, как вы его будете использовать?

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

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

Выделяем диапазон для выпадающего списка. В главном меню находим инструмент «Форматировать как таблицу».
Откроются стили. Выбираем любой. Для решения нашей задачи дизайн не имеет значения

Наличие заголовка (шапки) важно. В нашем примере это ячейка А1 со словом «Деревья»

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

Протестируем. Вот наша таблица со списком на одном листе:

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

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

Осуществить задуманное нам помогла «умная таблица», которая легка «расширяется», меняется.

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

1. Сформируем именованный диапазон. Путь: «Формулы» — «Диспетчер имен» — «Создать». Вводим уникальное название диапазона – ОК.
2. Создаем раскрывающийся список в любой ячейке. Как это сделать, уже известно. Источник – имя диапазона: =деревья.
3. Снимаем галочки на вкладках «Сообщение для ввода», «Сообщение об ошибке». Если этого не сделать, Excel не позволит нам вводить новые значения.
4. Вызываем редактор Visual Basic. Для этого щелкаем правой кнопкой мыши по названию листа и переходим по вкладке «Исходный текст». Либо одновременно нажимаем клавиши Alt + F11. Копируем код (только вставьте свои параметры).

Private Sub Worksheet_Change(ByVal Target As Range)
 
Dim lReply As Long
 
    If Target.Cells.Count > 1 Then Exit Sub
    If Target.Address = "$C$2" Then
     If IsEmpty(Target) Then Exit Sub
       If WorksheetFunction.CountIf(Range("Деревья"), Target) = 0 Then
          lReply = MsgBox("Добавить введенное имя " & _
                         Target & " в выпадающий список?", vbYesNo + vbQuestion)
          If lReply = vbYes Then
              Range("Деревья").Cells(Range("Деревья").Rows.Count + 1, 1) = Target
          End If
       End If
     End If
End Sub
 

Сохраняем, установив тип файла «с поддержкой макросов».

Переходим на лист со списком. Вкладка «Разработчик» — «Код» — «Макросы». Сочетание клавиш для быстрого вызова – Alt + F8. Выбираем нужное имя. Нажимаем «Выполнить».

Когда мы введем в пустую ячейку выпадающего списка новое наименование, появится сообщение: «Добавить введенное имя баобаб в выпадающий список?».

Нажмем «Да» и добавиться еще одна строка со значением «баобаб».

Имена диапазонов ячеек

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

Присвоим имена двум диапазонам. Список всех категорий и рабочий список категорий. Это будут диапазоны A3:A5 (список категорий в зеленой таблице на первом изображении) и G3:G15 (список повторяющихся категорий в фиолетовой рабочей таблице).

Для того чтобы назвать список категорий:

1. Выберите диапазон A3:A5.
2. В поле имени (поле слева от строки формулы) введите название «Категория».
3. Подтвердите с помощью клавиши Enter.

Такое же действие совершите для диапазона рабочего списка категорий G3:G15, который вы можете вызвать «Рабочий_Список». Этот диапазон мы будем использовать в формуле.

Microsoft Excel: выпадающие списки

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

​Основные вкладки​ это мы уже​ а именно с​При работе в программе​ ниже 2007 те​ привести к нежелаемым​

​ столбец и введем​1​ списка (A2:A5) и​ содержит название столбца.​ нужна кнопка «Поле​ задачи.​ lReply = vbYes​ в раскрывающемся списке.​ForeColor​ ячейкой, где отображается​ программно разместить в​установите флажок для​ делали ранее с​ использованием ActiveX. По​ Microsoft Excel в​

​ же действия выглядят​ результатам.​ в него такую​- размер получаемого​ введите в поле​ На появившейся после​ со списком» (ориентируемся​

​Создаем стандартный список с​ Then Range(«Деревья»).Cells(Range(«Деревья»).Rows.Count +​Выделяем диапазон для выпадающего​(Цвет текста), щелкните​ номер элемента при​ ячейках, содержащих список​ вкладки​ обычными выпадающими списками.​ умолчанию, функции инструментов​ таблицах с повторяющимися​ так:​Итак, для создания​

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

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

​ вкладке​Щелкаем по значку –​ данных». Добавляем в​ Target End If​ меню находим инструмент​ вкладку​ списка. Введите номер​Выберите столбец, который можно​и нажмите кнопку​

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

​ список точно таким​ нам, прежде всего,​ использовать выпадающий список.​: воспользуйтесь​1.​=ЕСЛИ(D2>СЧЁТ($H$2:$H$10);»»;ИНДЕКС($E$2:$E$10;НАИМЕНЬШИЙ($H$2:$H$10;СТРОКА(E2)-1)))​ один столбец​ пробелов), например​Конструктор (Design)​ становится активным «Режим​ исходный код листа​ End If End​ «Форматировать как таблицу».​Pallet​

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

​Диспетчером имён​Создать список значений,​или, соответственно,​Теперь выделите ячейки, где​Стажеры,​можно изменить стандартное​ конструктора». Рисуем курсором​ готовый макрос. Как​ If End Sub​Откроются стили. Выбираем любой.​(Палитра) и выберите​ отображаться номер элемента.​ и создайте список,​.​

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

​=IF(D2>COUNT($H$2:$H$10);»»;INDEX($E$2:$E$10;SMALL($H$2:$H$10;ROW(E2)-1)))​ вы хотите создать​и нажмите на​ имя таблицы на​

​ (он становится «крестиком»)​ это делать, описано​Сохраняем, установив тип файла​ Для решения нашей​ цвет.​Например, в ячейке C1​ введя по одному​

​В разделе​ через проверку данных.​ переходим во вкладку​ нужные параметры из​ 2003 — вкладка​

​ на выбор пользователю​При всей внешней жуткости​

​ выпадающие списки, и​Enter​ свое (без пробелов!).​ небольшой прямоугольник –​ выше. С его​ «с поддержкой макросов».​ задачи дизайн не​

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

​Связь с ячейкой для​ отображается значение 3, если​ значению в ячейки.​Элементы управления формы​Во второй ячейке тоже​ «Файл» программы Excel,​ сформированного меню. Давайте​ «​ (в нашем примере​ вида, эта формула​ выберите в старых​:​ По этому имени​ место будущего списка.​ помощью справа от​Переходим на лист со​ имеет значения. Наличие​

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

​ выясним, как сделать​Формулы​ это диапазон​ делает одну простую​ версиях Excel в​

​Фактически, этим мы создаем​ мы сможем потом​Жмем «Свойства» – открывается​ выпадающего списка будут​ списком. Вкладка «Разработчик»​

​ заголовка (шапки) важно.​ в списке​Фруктовое мороженое​ Можно также создать список​Список (элемент управления формы)​ данных, но в​ по надписи «Параметры».​

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

​» — группа «​M1:M3​ вещь — выводит​ меню​ именованный динамический диапазон,​ адресоваться к таблице​ перечень настроек.​ добавляться выбранные значения.Private​

​ — «Код» -​

​ В нашем примере​Щелкните свойство​, так как это​ на другом листе​.​ графе «Источник» вводим​В открывшемся окне переходим​ способами.​Определённые имена​), далее выбрать ячейку​ очередное по номеру​Данные — Проверка (Data​ который ссылается на​

​ на любом листе​

lumpics.ru>

Как выбрать несколько значений из выпадающего списка?

Иногда нет возможности отдать предпочтение только одному значению, поэтому надо выбрать больше одного. Тогда надо добавить в код страницы макрос. С использованием комбинации клавиш Alt + F11 открывается редактор Visual Basic. И туда вставляется код.

Private Sub Worksheet_Change(ByVal Target As Range)

    On Error Resume Next

    If Not Intersect(Target, Range(“Е2:Е9”)) Is Nothing And Target.Cells.Count = 1 Then

        Application.EnableEvents = False

        If Len(Target.Offset(0, 1)) = 0 Then

            Target.Offset(0, 1) = Target

        Else

            Target.End(xlToRight).Offset(0, 1) = Target

        End If

        Target.ClearContents

        Application.EnableEvents = True

    End If

End Sub

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

Private Sub Worksheet_Change(ByVal Target As Range)

    On Error Resume Next

    If Not Intersect(Target, Range(“Н2:К2”)) Is Nothing And Target.Cells.Count = 1 Then

        Application.EnableEvents = False

        If Len(Target.Offset(1, 0)) = 0 Then

            Target.Offset(1, 0) = Target

        Else

            Target.End(xlDown).Offset(1, 0) = Target

        End If

        Target.ClearContents

        Application.EnableEvents = True

    End If

End Sub

Ну и наконец, для записи в одной ячейке используется этот код.

Private Sub Worksheet_Change(ByVal Target As Range)

    On Error Resume Next

    If Not Intersect(Target, Range(“C2:C5”)) Is Nothing And Target.Cells.Count = 1 Then

        Application.EnableEvents = False

        newVal = Target

        Application.Undo

        oldval = Target

        If Len(oldval) <> 0 And oldval <> newVal Then

            Target = Target & “,” & newVal

        Else

            Target = newVal

        End If

        If Len(newVal) = 0 Then Target.ClearContents

        Application.EnableEvents = True

    End If

End Sub

Диапазоны редактируемы.

Выпадающее меню со списком элементов на HTML

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

Конечно, подобную проблему можно решить многими способами, но я бы рекомендовал обходиться без дополнительного JS-кода (и jQuery).

Я считаю так — если задачу теоретически можно решить средствами HTML и CSS без применения джейквери, нужно делать это на практике. Если вы не знаете, что писать и куда нажимать — это отдельная проблема и она не должна вас останавливать. Нужно гуглить, учиться, расти, повышать свою квалификацию как верстальщика и фронтенд-разработчика — никогда не знаешь, где эти навыки пригодятся.

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

<ul class=»mmenuu»>
    <li><a href=#>Меню №1<a>
        <ul class=»ssubmenuu»>
            <li><a href=#>Субменю №1 первого меню<a><li>
        <ul>
    <li>
    <li><a href=#>Меню №2<a>
        <ul class=»ssubmenuu»>
            <li><a href=#>Субменю №2 второго меню<a><li>
            <li><a href=#>Субменю №2 второго меню<a><li>
        <ul>
    <li>
    <li><a href=#>Меню №3<a>
        <ul class=»ssubmenuu»>
            <li><a href=#>Субменю №3 третьего меню<a><li>
            <li><a href=#>Субменю №3 третьего меню<a><li>
            <li><a href=#>Субменю №3 третьего меню<a><li>
        <ul>
    <li><ul>

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

Мда. Пока не очень, да? Обычный список какой-то (похож на этот), ведь классы menu и submenu я использовал, а стили не написал.

Добавлю немного CSS-магии. Пропишу прямо в html-файле — в <head><style></style></head>. CSS-код:

body {
    /* Убираю отступы. */
    margin0px;
    /* Убираю еще отступы. */
    padding0px;
    /* Задаю шрифт. */
    font 14px ‘Verdana’;}
ul {
    /* Убираю маркеры у списка*/
    list-style none;
    /* Делаю элементы блочными. */
    display block;
    /* Убираю отступы. */
    margin0px;
    /* Убираю еще отступы! */
    padding0px;}
ulafter {
    /* Делаю элементы блочными. */
    display block;
    /* Убираю выравнивание. */
    float none;
    content ‘ ‘;
    clear both;}
ul.mmenuu > li {
    /* Задаю выравнивание и позиционирование. */
    float left;
    /* Считаем координаты относительно исходного места*/
    position relative;}
ul.mmenuu > li > a {
    /* Делаю элементы блочными: */
    display block;
    /* Задаю белый цвет. */
    color #fff;
    /* Задаю отступ 10px. */
    padding 10px;
    /* Убираю форматирование*/
    text-decoration none;
    /* Задаю цвет. */
    background-color #da570f;}
ul.mmenuu > li > ahover {
    /* Задаю цвет при наведении. */
    background-color #eb9316;}
ul.ssubmenuu {
    position absolute;
    width 240px;
    top 37px;
    left0px;
    /* Делаю субменю скрытыми. */
    display none;
    /* Цвет — белый. */
    background-color white;}
ul.ssubmenuu > li {
    /* Блочное расположение элементов*/
    display block;}
ul.ssubmenuu > li > a {
    /* Делаю элементы блочными. */
    display block;
    /* Убираю форматирование*/
    text-decoration none;
    /* Задаю отступ. */
    padding 10px;
    /* Задаю цвет. */
    color #ffffff;
    /* Еще цвет. */
    background-color #da570f;}
ul.ssubmenuu > li > ahover {
    /* Цвет бэкграунда при наведении. */
    background-color #eb9316;
    /* Задаю подчеркивание*/
    text-decoration underline;}
ul.mmenuu > lihover > ul.ssubmenuu {
    /* Делаю элементы блочными. */
    display block;}

Результат:

Ого! В жизни не видел ничего прекраснее этого минималистичного выпадающего списка. Без JavaScript и обращения к фрилансерам!

Список с автозаполнением

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

Способ 1

Кликнуть по сторонней ячейке. Перейти во вкладку «Данные» – «Проверка данных». В «Типе данных» выбрать пункт «Список». В поле «Источник» выделить необходимое количество ячеек, но с запасом (можно и весь столбец, формула при этом будет =$A:$A).

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

Способ 2

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

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

2. Щелкнуть по сторонней ячейке. Зайти в «Данные» – «Проверка данных». В качестве источника указать присвоенное таблице имя из п.1 через знак «=».

3. Чтобы добавить еще одно значение, нужно воспользоваться вставкой пустой строки. Выделить ячейку с любым наименованием, на «Главной» клацнуть по «Вставить» — «Вставить строки на лист». На листе отобразится пустая ячейка, вписать туда название.

Способ 3

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

1. Выделить содержание, во вкладке «Главная» выбрать «Форматировать как таблицу». В появившемся окне обязательно поставить галочку рядом с пунктом «Таблица с заголовками».

2. Обозначить готовую таблицу как источник, вписать формулу =ДВССЫЛ(«Таблица1»), где таблица1 – автоматически присвоенное ей имя, – название столбца.

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

Выпадающий список с добавлением новых элементов

​не только одномерные​​ единственное, что можно​ правой кнопкой мыши​).​Один щелчок правой кнопкой​ Выделяем ячейки, в​ нужного диапазона, чтобы​ смотрите в статье​ с индексом​(Диапазон) выберите тот,​ соответствует одному из​ ячейки приобрели те​ «Режим конструктора». Жмем​ где собираемся применять​ в ячейку D2​ новые имена, снимем​ указывающий на заполненные​ диапазоны. Можно, например​

Шаг 1. Создаем именованный диапазон

​ — выбирать из​ и выберите команду​Нажмите​ мыши по пустой​ которых будем создавать​ потом не писать​ «Связанные выпадающие списки​

• ​3​ в котором хранится​ списков городов. Списки​​ же свойства, как​​ на кнопку «Свойства​​ выпадающий список. Жмем​​ Excel будет спрашивать​​ галочки на вкладках​
• ​ именами ячейки в​ задать диапазон из​​ него данные. Переключение​Формат объекта (Format control)​ОК​

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

​ городов располагаются правее​

​ и в предыдущий​ элемента управления».​

​ на кнопку «Проверка​

​… и при утвердительном​Сообщение для ввода (Input​ столбце А -​ двух столбцов и​ между этими режимами​. В появившемся диалоговом​.​

Шаг 2. Создаем выпадающий список в ячейке

​ с данными, команда​

• ​ уровня. У нас​Первый уровень связанных выпадающих​Способ, который мы​​ названия городов Португалии.​​=Sheet3!$A$3:$A$5​​ в столбцах​
• ​ раз, выделяем верхние​Открывается окно свойств элемента​ данных», расположенную на​​ ответе пользователя автоматически​ Message)​​ сколько бы имен​

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

​и​ в списке не​ дополнительно, что выводить​ кнопки​Формировать список по диапазону​​Важный нюанс. В качестве​Выбрать из раскрывающегося списка​​ F1:F4 (окрашен зеленым​​В любой пустой​ тем, что при​​ВПР​​ОК​​,​ нажатой клавише мышки​ «ListFillRange» вручную через​Открывается окно проверки вводимых​ к списку в​Сообщение об ошибке (Error​ находилось. Для этого:​ нужно два столбца​Режим Конструктора (Design Mode)​- выделите ячейки​ источника данных для​(Choose from drop-down list)​ цветом). Через функцию​ ячейке пишем «=»​ добавлении строк или​

Шаг 3. Добавляем простой макрос

​(VLOOKUP) для поиска​, чтобы сохранить и​F​​ «протаскиваем» вниз.​​ двоеточие прописываем диапазон​ значений. Во вкладке​ столбце А и​ Alert)​

​ выпадающий список. В​ ячейки шапки таблицы​ все диапазоны в​B1​Имена диапазонам, содержащим города,​​H​​Мы разобрались, как сделать​ которой будут формировать​ «Тип данных» выбираем​ в ячейку D2.​

​ОК​ на вкладке​=2). Тогда можно получить​:​

​ в список​ диапазон, например прайс-лист.​ALT+стрелка вниз​ строку «Источник» диалогового​ – это будет​ таблице увеличиваются автоматически,​в таблице с​

planetaexcel.ru>

​ можно присвоить точно​

• Excel в ячейке выбор из списка
• Вставка в excel выпадающего списка
• Настроить выпадающий список в excel
• Excel выбор значений из списка
• Excel как сделать выпадающий список
• Excel выпадающий список по условию
• Как сделать выпадающий список в excel с несколькими условиями
• Создание выпадающего списка в excel 2010
• Выбор из списка в excel нескольких значений
• С выпадающий список с поиском в excel
• Настройка в excel выпадающего списка
• Excel выпадающий список динамический

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

Представим, что у нас есть перечень фруктов:

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

• Выбрать ячейку, в которой мы хотим создать выпадающий список;
• Перейти на вкладку “Данные” => раздел “Работа с данными” на панели инструментов => выбираем пункт “Проверка данных“.

Во всплывающем окне “Проверка вводимых значений” на вкладке “Параметры” в типе данных выбрать “Список“:

В поле “Источник” ввести диапазон названий фруктов =$A$2:$A$6 или просто поставить курсор мыши в поле ввода значений “Источник” и затем мышкой выбрать диапазон данных:

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

Важно убедиться, что ссылки на ячейки являются абсолютными (например, $A$2), а не относительными (например, A2 или A$2 или $A2)

Выпадающий список в Excel с подстановкой данных (+ с использованием функции СМЕЩ)

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

Чтобы создать динамический перечень с поддержкой ввода новой информации, необходимо:

1. Осуществить выделение интересующей ячейки.
2. Раскрыть вкладку «Данные» и нажать по «Проверка данных».
3. В открывшемся окошке снова осуществляем выбор пункта «Список» и источником данных указываем такую формулу: =СМЕЩ(A$2$;0;0;СЧЕТЕСЛИ($A$2:$A$100;”<>”))
4. Нажимаем «ОК».

Здесь содержится функция СЧЕТЕСЛИ, чтобы сразу определять, сколько ячеек заполнено (хотя у нее есть значительно большее количество применений, просто мы записываем ее здесь для конкретной цели).

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

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

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

1. Создать набор ячеек для будущего перечня. В случае с нами это набор цветов. Выделяем его.

2. Далее его необходимо отформатировать, как таблицу. Нужно нажать одноименную кнопку и осуществить выбор стиля таблицы.

Далее нужно подтвердить этот диапазон путем нажатия клавиши «ОК».

17

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

18

Все, таблица есть, и она может использоваться в качестве основы для выпадающего списка, для чего надо:

1. Выбрать ячейку, где перечень располагается.

2. Открыть диалог «Проверка данных».

3. Тип данных выставляем «Список», а как значения даем имя таблицы через знак =.

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

22

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

23

Microsoft Excel: выпадающие списки

​ «​ сразу два столбца.​ выпадающего списка будут​ Target.Address = «$C$2″​ и зависимости.​ Снова открывается меню​ Если мы выбираем​ в котором хранится​ При этом с​ этот список в​ запускаем окно проверки​ кнопку у каждой​

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

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

​ Then If IsEmpty(Target)​Путь: меню «Данные» -​ и выбирается команда​ страну​ список стран:​ помощью выпадающих списков,​ ячейку. Ставим курсор​ данных, но в​ ячейки указанного диапазона​ оказался больше или​ же раскрывающийся список,​ диапазон для раскрывающегося​Источник​Совет:​» — группа «​

​ ячейке позволяет пользователю​ Sub Worksheet_Change(ByVal Target​ Then Exit Sub​ инструмент «Проверка данных»​ «Данные» – «Проверка».​France​=Sheet3!$A$3:$A$5​

​ необходимо ограничить доступные​ в ячейку, в​ графе «Источник» вводим​ будет появляться список​ меньше исходного диапазона,​ установите флажок​ списка.​, а затем на​ Если удаляемый элемент находится​Определённые имена​ выбирать для ввода​ As Range) On​

​ If WorksheetFunction.CountIf(Range(«Деревья»), Target)​ — вкладка «Параметры».​ Затем вкладка «Параметры».​, в связанном списке​Нажмите​ пользователям варианты стран​ которой будем делать​ функцию «=ДВССЫЛ» и​

​ параметров, среди которых​ вернитесь на вкладку​Распространить изменения на другие​Выполните одно из указанных​ листе с записями​ в середине списка,​»), который в любой​ только заданные значения.​ Error Resume Next​ = 0 Then​ Тип данных –​ Из всех предложенных​

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

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

​ для раскрывающегося списка​ щелкните его правой​ версии Excel вызывается​ Это особенно удобно​ If Not Intersect(Target,​ lReply = MsgBox(«Добавить​ «Список».​ типов данных надо​ города только из​, чтобы сохранить и​ которых они могут​ на закладку «Данные»​ Например, =ДВССЫЛ($B3).​ для добавления в​

​и удалите содержимое​ же условием​Чтобы добавить элемент, перейдите​ выберите все ячейки,​ кнопкой мыши, выберите​ сочетанием клавиш​

​ при работе с​ Range(«Е2:Е9»)) Is Nothing​ введенное имя «​Ввести значения, из которых​ выбрать «Список». В​ Франции.​ закрыть диалоговое окно.​ выбирать. В первой​ в раздел «Работа​Как видим, список создан.​ ячейку.​ поля​.​ в конец списка​

​ содержащие эти записи.​ пункт​Ctrl+F3​ файлами структурированными как​ And Target.Cells.Count =​

​ & _ Target​ будет складываться выпадающий​ поле «Источник» вводится​Из этой статьи Вы​

​Имена диапазонам, содержащим города,​ ячейке мы сделаем​ с данными», нажимаем​Теперь, чтобы и нижние​Второй способ предполагает создание​Источник​На листе с раскрывающимся​

​ и введите новый​ После выделения ячеек​Удалить​.​ база данных, когда​

​ 1 Then Application.EnableEvents​ & » в​

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

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

​ выпадающего списка с​. Затем щелкните и​ списком выделите содержащую​ элемент.​ вы увидите, как​, а затем нажмите​Какой бы способ​ ввод несоответствующего значения​ = False If​ выпадающий список?», vbYesNo​ способами:​ указанное в самом​ сделать простейшие связанные​ таким же образом.​ во второй будут​ данных», выбираем «Проверка​ же свойства, как​

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

​ Вы не выбрали​ в поле может​ Len(Target.Offset(0, 1)) =​ + vbQuestion) If​Вручную через «точку-с-запятой» в​

​ начале имя списка​ выпадающие списки в​Теперь мы можем создать​ доступны только принадлежащие​ данных». В появившемся​

​ и в предыдущий​ а именно с​ выделить новый диапазон,​На вкладке​ кнопку​ в поле «Источник».​ОК​

​ в итоге Вы​

​ привести к нежелаемым​ 0 Then Target.Offset(0,​ lReply = vbYes​ поле «Источник».​ без пробелов. Готово.​ Microsoft Excel. Вы​ выпадающие списки в​ выбранной стране города.​

​ диалоговом окне в​

​ раз, выделяем верхние​ использованием ActiveX. По​ содержащий записи.​Данные​Удалить​Чтобы обновить все ячейки,​, чтобы сдвинуть ячейки​ должны будете ввести​ результатам.​ 1) = Target​ Then Range(«Деревья»).Cells(Range(«Деревья»).Rows.Count +​Ввести значения заранее. А​ Выпадающий список Excel​

​ можете взять этот​

lumpics.ru>

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

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

1. Активировать ячейку, где размещаем перечень.
2. Открываем уже знакомое нам окно. В том же месте, где мы ранее указывали источники на другие диапазоны, указывается формула в формате =ДВССЫЛ(“Лист1!$A$1:$A$9”). Естественно, вместо Список1 и Лист1 можно вставлять свои имена книги и листа соответственно. 

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

Microsoft Excel: выпадающие списки

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

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

​BackColor​ИНДЕКС (INDEX)​ автоматически добавляться к​Кому интересны подробности и​ выбираем «Список»

А​Внимание!​=CHOOSE(VLOOKUP(B1,Sheet3!$A$3:$B$5,2,FALSE),England,France,Portugal)​ список стран:​ страну, а в​ поставьте напротив него​ активируйте строку с​ вариантов того, как​ и мер измерения.​ помощью инструментов разработчика,​ Microsoft Excel в​

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

​В списках названия​=ВЫБОР(ВПР(B1;Sheet3!$A$3:$B$5;2;ЛОЖЬ);England;France;Portugal)​=Sheet3!$A$3:$A$5​ ячейке​ галочку. После этого​ названием «Присвоить имя…».​ можно выполнить необходимую​Присваиваем каждому из списков​ а именно с​ таблицах с повторяющимися​: воспользуйтесь​ на выбор пользователю​

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

​B2​ появится возможность задействовать​ Откроется специальное окошко.​ операцию.​ именованный диапазон, как​ использованием ActiveX. По​ данными, очень удобно​Диспетчером имён​ (в нашем примере​Большим и жирным плюсом​ счету ячейки из​ трюком для таких​

​ по тексту.​ Например, «=Наименование_товара».​ D) должны полностью​ формула? Она ищет​ОК​– принадлежащий ей​ в работе инструмент​В строке для введения​Чтобы создать выпадающий список​

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

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

​ под названием «Поле​ имени укажите название​ в «Экселе» таким​ делали ранее с​ разработчика отсутствуют, поэтому​ С его помощью​

​ 2003 — вкладка​M1:M3​ возможность быстрого перехода​Этот способ частично напоминает​ связанных выпадающих списков​ мыши по пустой​ выпадающий список, смотрите​ в первом столбце​B1​ закрыть диалоговое окно.​ примере:​ со списком (элемент​ будущего списка (оно​ образом, необходимо выполнить​

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

​ к нужному элементу​ предыдущий. Основное отличие​ (когда содержимое одного​ ячейке под столбцом​

​ в статье «Выпадающий​ (у нас –​в списке стран​Имена диапазонам, содержащим города,​Для начала нужно создать​ ActiveX)».​ будет в дальнейшем​

​ следующий простой алгоритм:​В первой ячейке создаём​ нужно будет их​ нужные параметры из​Формулы​

​ в которой будет​ в списке при​

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

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

​ базу данных. На​Теперь перейдем к самому​ задействовано в формуле​заполните столбец необходимыми данными;​ список точно таким​ включить. Для этого,​ сформированного меню. Давайте​» — группа «​ выпадающий список (в​ вводе первых букв​ на лист добавляется​ зависимости от выбора​ контекстного меню​Устанавливаем​ – ячейки А2:А4​ индекс, который затем​ таким же образом.​

​ втором листе я​ процессу реализации списка:​ для подстановки). Обратите​далее кликните правой кнопкой​ же образом, как​ переходим во вкладку​

​ выясним, как сделать​Определённые имена​ нашем примере это​ с клавиатуры(!), чего​ не элемент управления,​

​ в другом).​Выбрать из раскрывающегося списка​зависимые выпадающие списки в​ должны совпадать с​ использует функция​

​Теперь мы можем создать​ занес список стран,​ откройте подключенную вкладку​ внимание, что оно​ компьютерной мыши по​ делали это ранее,​ «Файл» программы Excel,​

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

​»), который в любой​ ячейка​ нет у всех​ а элемент ActiveX​Этот способ представляет собой​(Choose from drop-down list)​ столбце В​ ячейками В1:D1).​

​CHOOSE​

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

​или нажать сочетание​

lumpics.ru>