Excel цветной выпадающий список. Создание выпадающего списка в ячейке

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

Несколько наиболее распространенных типов выпадающих списков, которые можно создать в программе Excel:

1. С функцией мультивыбора;
2. С наполнением;
3. С добавлением новых элементов;
4. С выпадающими фото;
5. Другие типы.

Сделать список в Эксель с мультивыбором

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

Рассмотрим подробнее все основные и самые распространенные типы, и процесс их создание на практике.

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

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

Чтобы создать такой, следуйте инструкции:

• Выделите ячейки. Если посмотреть на рисунок, то выделять нужно начиная с C2 и заканчивая C5;
• Найдите вкладку «Данные», которая расположена на главной панели инструментов в окне программы. Затем нажмите на клавишу проверки данных, как показано на рисунке ниже;

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

Пр имер заполнения:

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

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

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

Стандартный перечень с наполнением позволит вам автоматизировать процесс ввода информации. При нажатии на ее, появится выбор ее возможных значений.

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

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

С их помощью можно легко и быстро форматировать необходимые вам виды списков с наполнением:

• Выделите необходимые ячейки и нажмите в главной вкладке на клавишу «Форматировать как таблицу»;

Если Вам приходится работать с книгой Excel, в которой постоянно фигурирует один и тот же список данных, или может быть Вы просто не хотите множество раз использовать действие Копировать > Вставить , то было бы очень удобно иметь заранее настроенный и сохранённый список, чтобы Excel мог помочь Вам в работе. Использование настраиваемых списков – это хорошее решение для данной ситуации. Далее я покажу, как создать настраиваемый список в Excel.

Создаем пользовательский список в Excel

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

В этот момент я бы также запустил проверку орфографии, это делается простым нажатием F7 .

Затем я выделил эту область, нажал на кнопку Office и в нижней части открывшегося меню щелкнул по Excel Options (Параметры Excel).

В разделе Popular (Общие) Вы найдёте пункт Create lists for use in sorts and fills sequences (Создавать списки для сортировки и заполнения) – кликните по Edit Custom Lists (Изменить списки).

Если Вы работаете в Excel 2010, то Вам нужен другой маршрут. Откройте вкладку File (Файл) и нажмите Options (Параметры). Затем пролистайте вниз, найдите кнопку Edit Custom Lists (Изменить списки).

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

Создаем список с нуля

У меня есть два способа, как создать настраиваемый список. Я могу создать его вручную, введя каждое значение в поле List entries (Элементы списка) и нажав Add (Добавить). Если Вы выберете этот путь, то столкнетесь с некоторыми ограничениями. Поле List entries (Элементы списка) позволяет ввести не более 255 символов. Будьте внимательны к тому, из скольких символов состоит каждая Ваша запись!

Подсказка: Если вы планируете вводить список в поле List entries (Элементы списка) вручную, не ставьте лишние пробелы между элементами. Если пробелы будут стоять до или после элемента, то Microsoft их просто не станет учитывать, а если между словами одного элемента, то все они будут сохранены.

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

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

Помните, у нас было ограничение на количество символов, которое можно ввести в поле List entries (Элементы списка)? Только не при импорте! Теперь максимальный размер списка где-то 2000 символов! Нажмите ОК , чтобы закрыть окно с параметрами списка, и ещё раз ОК , чтобы закрыть окно параметров Excel.

Для пользовательского списка можно импортировать только текстовые значения. Если Вам нужно создать настраиваемый список с календарными датами или числами, то придётся использовать поле List entries (Элементы списка).

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

Использование пользовательских списков в Excel

Итак, наш настраиваемый список готов к использованию. Выделите ячейку и введите с клавиатуры любой элемент этого списка. Нажмите на маркер автозаполнения (небольшой квадратик в правом нижнем углу ячейки), и потяните за него, чтобы продолжить последовательность. Чуть правее я вписал “Monday”, затем нажал на маркер и потянул вправо, Excel заполнил ячейки автоматически.

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

Сортировка по пользовательскому списку

Одна из приятных возможностей, которую дает работа с настраиваемыми списками, – это возможность упорядочить данные по любому из списков, сохранённых на Вашем компьютере. Кликните по одному или нескольким столбцам, затем нажмите Sort & Filter (Сортировка и фильтр), раскройте выпадающее меню Order (Порядок), кликните Custom List (Настраиваемый список) и выберите список, по которому желаете выполнить сортировку.

Сортировка может включать более одного столбца. Если Вы хотите добавить ещё один уровень и упорядочить сначала по месяцам, а затем по номеру счёта, то можете кликнуть Add Level (Добавить уровень) и определить, каким образом должны отображаться данные. Когда закончите, нажмите ОК . Теперь информация упорядочена по выбранному списку!

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

В данной статье рассмотрим, как создать раскрывающийся список в excel 2007. Возьмем пример, когда нам нужно в ячейке выбрать заданные значения от 1 до 5 из выпадающего списка. Создаем сам список и выделяем его левой кнопкой мыши. Кликаем правой кнопкой мыши в выделенной области и выбираем пункт Имя диапазона .

В открывшемся окошке в поле Имя вводим название нашего списка, назовем Значение . В поле Область из выпадающего списка выберем Книга (либо номер Листа к которому желаете применить список). Жмем Ок.

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

Список создан. Теперь применим этот список к ячейке.

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

В поле Источник ставим знак «равно» и пишем название, которое присвоили списку. Список называется «Значение». Соответственно запись должна быть как показано на рисунке ниже.

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

Можно создать , минуя присвоение названия списка. То есть:

1. создаем сам список;
2. переходим в Ленте на вкладку Данные , жмем кнопку Проверка данных;
3. в открывшемся окошке, во вкладке Параметры в поле Тип данных выбираем Список;
4. в поле Источник кликаем левой кнопкой мыши для активации данного поля. Далее выделяем ячейки образующие список;
5. жмем Ок.

Все, раскрывающийся список в excel 2007 готов.

ПРЕДСТАВЬТЕ СИТУАЦИЮ: Мы хотим создать в Excel небольшую табличку, где можно выбрать страну и соответствующий ей город. При этом с помощью выпадающих списков, необходимо ограничить доступные пользователям варианты стран и городов, из которых они могут выбирать. В первой ячейке мы сделаем выбор страны, а во второй будут доступны только принадлежащие выбранной стране города. Думаю, это понятно?

Итак, давайте начнём наш простой пример с того, как можно создать связанный (или зависимый) выпадающий список в Excel? В ячейке B1 мы будем выбирать страну, а в ячейке B2 – принадлежащий ей город, как на примере:

Для начала нужно создать базу данных. На втором листе я занес список стран, которые хочу дать пользователям на выбор в первом раскрывающемся списке, а в соседнем столбце указал числовой индекс, который соответствует одному из списков городов. Списки городов располагаются правее в столбцах D , F и H . Так, например, рядом с France стоит индекс 2 , который соответствует списку городов 2 . Позже Вы увидите, как этот индекс будет использован.

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

Мы будем использовать именованные диапазоны и сделаем так, чтобы эти связанные выпадающие списки работали во всех версиях Excel. Следующий шаг – создать именованные диапазоны для наших списков. На вкладке Formulas (Формулы) есть команда Name Manager (Диспетчер имён). Нажав на нее, откроется диалоговое окно Name Manager (Диспетчер имён).

Нажмите кнопку New (Создать), чтобы добавить новый именованный диапазон. Откроется диалоговое окно New Name (Создание имени).

В поле Name (Имя) введите имя Country для нашего первого именованного диапазона, а в поле Refers to (Диапазон) выберите тот, в котором хранится список стран:

Sheet3!$A$3:$A$5

Имена диапазонам, содержащим города, можно присвоить точно таким же образом.

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

Откроется диалоговое окно Data Validation (Проверка вводимых значений).

Мы хотим дать пользователю на выбор список вариантов, поэтому в поле Allow (Тип данных) выберите List (Список). Это активирует поле Source (Источник), где необходимо указать имя диапазона со странами. Введите в этом поле “=Country” и жмите ОК . Теперь нам нужно сделать второй раскрывающийся список, чтобы пользователи могли выбрать город. Мы поместим этот раскрывающийся список в ячейку B2 . А теперь внимание – фокус! Нам нужно проверить содержимое ячейки с названием страны (ячейка B1), чтобы получить индекс соответствующий базе данных с городами. Если пользователь выберет Portugal , то мы должны обратиться к базе с индексом 3 , в которой хранятся названия городов Португалии. Мы воспользуемся функцией ВПР (VLOOKUP) для поиска значения из ячейки B1 в таблице с названиями стран. После того как индекс будет известен, мы выберем список, который станет источником данных для нашего второго выпадающего списка. Для этого напишем такую формулу:

CHOOSE(VLOOKUP(B1,Sheet3!$A$3:$B$5,2,FALSE),England,France,Portugal)
=ВЫБОР(ВПР(B1;Sheet3!$A$3:$B$5;2;ЛОЖЬ);England;France;Portugal)

Что же делает эта формула? Она ищет значение из ячейки B1 в списке стран и возвращает соответствующий индекс, который затем использует функция CHOOSE (ВЫБОР), чтобы выбрать 1-й, 2-й или 3-й именованный диапазон.

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

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

Способ 1: создаем дополнительный список

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

Шаг 1: подготавливаем данные

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

Если вы не хотите, чтобы таблица с данными находилась на том листе, что и основная, вы можете создать ее на отдельном листе. Роли это не сыграет.

Шаг 2: вводим имя диапазона

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

1. Выделите ячейки, в которых находятся в данном случае наименования товаров.
2. Нажмите правой кнопкой мыши (ПКМ) по выделению.
3. Выберите из меню опцию «Присвоить имя».
4. В появившемся окне в поле «Имя» введите название диапазона. Оно может быть абсолютно любым.
5. Нажмите «ОК».

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

Шаг 3: делаем выпадающий список

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

1. Выделите нужный диапазон ячеек, в котором будут располагаться выпадающие списки.
2. Перейдите на вкладку «Данные».
3. В группе инструментов «Работа с данными» нажмите по кнопке «Проверка данных».
4. В появившемся окне на вкладке «Параметры» выберите из выпадающего списка «Тип данных» значение «Список».
5. Введите в поле «Источник» название ранее созданного диапазона ячеек, предварительно поставив знак равенства. В нашем случае - «=Продукты».
6. Нажмите «ОК».

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

Способ 2: создание выпадающего списка через меню «Разработчика»

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

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

Шаг 1: включаем меню «Разработчика»

Итак, в первую очередь необходимо активировать меню «Разработчика», так как по умолчанию его нет среди прочих вкладок.

1. Нажмите по кнопке «Файл».
2. Нажмите по кнопке «Параметры».
3. В появившемся одноименном окне перейдите в раздел «Настройка ленты».
4. В области «Основные вкладки» установите отметку напротив пункта «Разработчик».
5. Нажмите «ОК».

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

Шаг 2: вставляем выпадающий список

Нужно создать непосредственно сам элемент «Выпадающий список». Для этого:

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

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

Шаг 3: задаем необходимые параметры

Чтобы добавить в выпадающий список пункты, необходимо:

1. На панели инструментов нажать по кнопке «Режим конструктора».
2. Затем нажать кнопку «Свойства элемента управления», располагающуюся рядом.
3. В появившемся окне со свойствами в графе ListFillRange введите диапазон ячеек, в котором находятся пункты будущего выпадающего списка.
4. Теперь нажмите ПКМ по выпадающему списку и в меню выберите «Объект ComboBox», а в подменю Edit.

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

Способ 3: создание связанного списка

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

Шаг 1: создаем дополнительный список

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

Шаг 2: Связываем первый список со вторым

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

1. Установите курсор в той ячейке, где будет располагаться второй список.
2. Откройте окно «Проверки вводимых значений», нажав на вкладке «Данные» по кнопке «Проверка данных».
3. В появившемся окне на вкладке «Параметры» выберите из выпадающего списка «Тип данных» пункт «Список».
4. В поле для ввода «Источник» введите формулу «ДВССЫЛ», ссылающуюся на первый список. В данном случае она будет выглядеть следующим образом: «=ДВССЫЛ($B3)».
5. Нажмите «ОК».

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

Заключение

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