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

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

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

Процесс создания списка

Для создания всплывающего меню кликните по пунктам меню по пути «Данные» – «Проверка данных». Откроется диалоговое окно, где нужно найти вкладку «Параметры» и нажать на нее, если она ранее не открывалась. Настроек у него много, но нам важен пункт «Тип данных». Из всех значений «Список» является правильным.

Количество способов внесения информации во всплывающий список достаточно велико.

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

   

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

   

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

   

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

На основании данных из списка

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

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

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

   

3. Найдите пункт «Тип данных» и измените значение на «Список».
   

   

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

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

С ручной записью данных

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

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

1. Нажмите на ячейку для просмотра списка.
2. Открываем «Данные» и там находим знакомый нам раздел «Проверка данных».
   

   

3. Снова выберите тип «Список».
   

   

4. Здесь нужно ввести «Да; Нет» в качестве источника. Мы видим, что информация вводится вручную с использованием точки с запятой для перечисления.

После нажатия ОК имеем следующий результат.

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

Создание раскрывающегося списка с помощью функции СМЕЩ

Помимо классического метода можно использовать функцию УДАЛЕНИЕдля создания выпадающих меню.

Давайте откроем лист.

Чтобы использовать функцию для выпадающего списка, вам необходимо сделать следующее:

1. Выберите интересующую ячейку, в которую вы хотите поместить будущий список.
2. Последовательно откройте вкладку «Данные» и окно «Проверка данных».
   

   

3. Сет-лист". Делается это так же, как и в предыдущих примерах. Наконец, используется следующая формула: =СМЕЩ(A$2$;0;0;5). Вводим его, где указаны ячейки, которые будут использоваться в качестве аргумента.

Затем программа создаст меню со списком фруктов.

Синтаксис для этого:

=СМЕЩ(ссылка,смещение_строки,смещение_столбца,[высота],[ширина])

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

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

В данном случае УДАЛЕНИЕ разрешено создавать всплывающее меню, расположенное в фиксированном диапазоне. Недостаток этого метода в том, что после добавления элемента вам придется редактировать формулу самостоятельно.

Для создания динамического списка с поддержкой ввода новой информации необходимо:

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

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

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

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

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

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

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

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

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

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

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

1. Создайте первый список с именами диапазонов.
   

   

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

   

3. Создайте второй список в зависимости от типа растения, которое выбрал человек. Альтернативно, если в первом списке указать деревья, то во втором списке будет информация «дуб, граб, каштан» и далее. Необходимо записать формулу в месте ввода источника данных =НЕПРЯМОЙ(E3). E3 – ячейка, содержащая имя диапазона 1.=ДВССЫЛ(E3). E3 – ячейка с названием списка 1.

Теперь все готово.

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

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

Private Sub Worksheet_Change (Цель ByVal как диапазон)

    On Error Resume Next

    Если Not Intersect(Target, Range(«Е2:Е9»)) — ничего и Target.Cells.Count = 1 Тогда

        Application.EnableEvents = False

        Если Len(Target.Offset(0, 1)) = 0 Тогда

            Цель.Смещение (0, 1) = Цель

        еще

            Target.End(xlToRight).Offset (0, 1) = Цель

        End If

        Target.ClearContents

        Application.EnableEvents = True

    End If

End Sub 

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

Private Sub Worksheet_Change (Цель ByVal как диапазон)

    On Error Resume Next

    Если Not Intersect(Target, Range(«Н2:К2»)) — ничего и Target.Cells.Count = 1 Тогда

        Application.EnableEvents = False

        Если Len(Target.Offset(1, 0)) = 0 Тогда

            Цель.Смещение (1, 0) = Цель

        еще

            Цель.Конец (xlDown).Смещение (1, 0) = Цель

        End If

        Target.ClearContents

        Application.EnableEvents = True

    End If

End Sub

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

Private Sub Worksheet_Change (Цель ByVal как диапазон)

    On Error Resume Next

    Если Not Intersect(Target, Range(«C2:C5»)) — ничего и Target.Cells.Count = 1 Тогда

        Application.EnableEvents = False

        новоеВал = Цель

        Приложение.Отменить

        oldval = Цель

        Если Len (oldval) <> 0 И oldval <> newVal Тогда

            Цель = Цель & «,» & newVal

        еще

            Цель = новое значение

        End If

        Если Len (newVal) = 0, то Target.ClearContents

        Application.EnableEvents = True

    End If

End Sub

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

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

В этом случае изначально необходимо использовать другой тип списка. Откроется вкладка «Разработчик», после чего нужно нажать или тапнуть (если экран сенсорный) по элементу «Вставка» — «ActiveX». Есть поле со списком. Вам будет предложено нарисовать этот список, после чего он будет добавлен в документ.

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

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

Также есть функция автоматической подстановки данных после их добавления в диапазон. Это легко сделать:

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

   

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

   

   

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

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

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

1. Выберите ячейку, в которой находится список.
2. Откройте диалоговое окно «Проверка данных».
   

   

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

   

   

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

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

Как скопировать выпадающий список?

Для копирования достаточно использовать комбинацию клавиш Ctrl+C и Ctrl+V. Так выпадающий список будет скопирован вместе с форматированием. Для удаления форматирования необходимо использовать специальную вставку (в контекстном меню эта опция появляется после копирования списка), где установлена ​​опция «условия по значениям».

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

Для выполнения этой задачи необходимо воспользоваться функцией «Выбрать группу ячеек» в группе «Найти и выделить».

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