Что такое список в excel. Как сделать сложный выпадающий список в excel? Видео — Связанные выпадающие списки: легко и быстро

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

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

Создаем раскрывающийся список в Экселе: способ первый

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

Заметьте, насколько легко пользоваться программой Excel – любые задачи решаются действительно в несколько кликов, достаточно потратить порядка пары минут на изучение функционала. Именно по этой причине MS Office, как пакет офисных программ, является самым продвинутым и популярным на рынке.

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

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

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

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

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

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

Итак, для создания выпадающего списка необходимо:

1. Создать список значений, которые будут предоставляться на выбор пользователю (в нашем примере это диапазон M1:M3 ), далее выбрать ячейку в которой будет выпадающий список (в нашем примере это ячейка К1 ), потом зайти во вкладку «Данные «, группа «Работа с данными «, кнопка «Проверка данных «

2. Выбираем «Тип данных » -«Список » и указываем диапазон списка

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

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

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

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

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

Для Excel версий ниже 2007 те же действия выглядят так:

Второй : воспользуйтесь Диспетчером имён (Excel версий выше 2003 — вкладка «Формулы » — группа «Определённые имена «), который в любой версии Excel вызывается сочетанием клавиш Ctrl+F3 .Какой бы способ Вы не выбрали в итоге Вы должны будете ввести имя (я назвал диапазон со списком list ) и адрес самого диапазона (в нашем примере это«2»!$A$1:$A$3 )

6. Теперь в ячейке с выпадающим списком укажите в поле «Источник» имя диапазона

7. Готово!

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

То есть вручную, через ; (точка с запятой) вводим список в поле «Источник «, в том порядке в котором мы хотим его видеть (значения введённые слева-направо будут отображаться в ячейке сверху вниз).

При всех своих плюсах выпадающий список, созданный вышеописанным образом, имеет один, но очень «жирный» минус: проверка данных работает только при непосредственном вводе значений с клавиатуры. Если Вы попытаетесь вставить в ячейку с проверкой данных значения из буфера обмена, т.е скопированные предварительно любым способом, то Вам это удастся. Более того, вставленное значение из буфера УДАЛИТ ПРОВЕРКУ ДАННЫХ И ВЫПАДАЮЩИЙ СПИСОК ИЗ ЯЧЕЙКИ, в которую вставили предварительно скопированное значение. Избежать этого штатными средствами Excel нельзя.

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

Первый способ создания двухуровнего списка

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

Теперь приступим к созданию первого выпадающего списка группы (в моем случае — список стран):

1. Выберите ячейку, в которую будете вставлять выпадающий список;
2. Переходим на вкладку ленты Данные ;
3. Выбираем команду Проверка данных ;
4. В выпадающем списке выбираем значение Список ;
5. В поле Источник указываем следующую формулу =ДВССЫЛ(«Таблица1[#Заголовки]») .

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

Осталось создать второй зависимый выпадающий список — список подгрупп.

Смело повторяем 4 первых пункта описанных выше. Источником в окне Проверка данных для второго выпадающего списка будет служить формула =ДВССЫЛ(«Таблица1[«&F2&»]») . Ячейка F2 в данном случае — значение первого выпадающего списка.

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

Второй способ создания двухуровнего списка

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

ВАЖНО! Перед созданием зависимого списка по подгруппам необходимо отсортировать исходную таблицу по первому столбцу (столбец с группой) далее будет понятно зачем это делается.

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

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

Рекомендация: удобно в качестве источника указывать именованный диапазон. Для его создания откройте Диспетчер имен со вкладки Формулы и присвойте имя диапазону с уникальными значениями.

Теперь самая сложная часть — указать в Источнике динамическую ссылку на диапазон со значениями второго выпадающего списка (списка подгрупп). Решать ее будем с помощью функции СМЕЩ(ссылка, смещ_по_строкам, смещ_по_столбцам, [высота], [ширина]) , которая возвращает ссылку на диапазон, отстоящий от ячейки или диапазона ячеек на заданное число строк и столбцов.

• Ссылка в нашем случае — $A$1 — верхний левый угол исходной таблицы;
• Смещ_по_строкам — ПОИСКПОЗ(F3;$A$1:$A$67;0)-1 — номер строки со значением искомой группы (в моем случае страны ячейка F3 ) минус единица;
• Cмещ_по_столбцам — 1 — так как нам необходим столбец с подгруппами (городами);
• [Высота] — СЧЁТЕСЛИ($A$1:$A$67;F3) — количество подгрупп в искомой группе (количество городов в стране F3 );
• [Ширина] — 1 — так как это ширина нашего столбца с подгруппами.

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

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

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

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

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

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

А делать выпадающий список в ячейке будем в несколько этапов:

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

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

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

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

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

Прогресс человечества основывается на желании каждого человека жить не по средствам
Сэмюэль Батлер, философ

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

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

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

1. Перейдите на первую пустую клетку после вашего списка.

1. Сделайте правый клик. Затем выберите указанный пункт.

1. В результате этого появится следующий список.

1. Для перехода по нему достаточно нажать на горячие клавиши Alt +↓ .

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

1. Затем для выбора можно использовать только стрелочки (↓ и ). Для того чтобы вставить нужный продукт (в нашем случае), достаточно нажать на клавишу Enter .

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

Обратите внимание на то, что этот метод не работает, если вы выберите клетку, выше которой нет никакой информации.

Стандартный

В этом случае необходимо:

1. Выделить нужные ячейки. Перейти на вкладку «Формулы». Нажать на кнопку «Определенные имена». Выбрать пункт «Диспетчер имён».

1. Затем кликнуть на «Создать».

1. Далее нужно будет указать желаемое имя (нельзя использовать символ тире или пробел). В графе диапазон произойдет автозаполнение, поскольку нужные ячейки были выделены в самом начале. Для сохранения нажмите на «OK».

1. Затем закройте это окно.

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

1. Нажмите на «Тип данных». Необходимо задать значение «Список».

1. Вследствие этого появится поле «Источник». Кликните туда.

1. Затем выделите нужные ячейки. Ранее созданное имя автоматически подставится. Для продолжения нажимаем на «OK».

1. Благодаря этим действиям вы увидите вот такой элемент.

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

Как включить режим разработчика

Для того чтобы использовать более продвинутые объекты, нужно использовать вкладку «Разработчик». По умолчанию она отключена. Для того чтобы её активировать, необходимо следовать следующей инструкции.

1. Нажмите на меню «Файл».

1. Перейдите в раздел «Параметры».

1. Откройте категорию «Настроить ленту». Затем поставьте галочку напротив пункта «Разработчик». Для сохранения информации кликните на «OK».

Элементы управления

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

1. Выделите свою таблицу данных. Перейдите на вкладку «Разработчик». Кликните на иконку «Вставить». Нажмите на указанный элемент.

1. Также изменится иконка указателя.

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

1. После этого сделайте правый клик мышкой по этому элементу. Затем выберите пункт «Формат объекта».

1. В окне «Форматирование объекта» необходимо:
   • Указать диапазон значений для формирования списка.
   • Выбрать ячейку, в которую будет выводиться результат.
   • Указать количество строк будущего списка.
   • Нажать на «OK» для сохранения.

1. Кликните на этот элемент. После этого вы увидите варианты для выбора.

1. Вследствие этого вы увидите какое-нибудь число. 1 – соответствует первому слову, а 2 – второму. То есть в этой ячейке выводится лишь порядковый номер выбранного слова.

ActiveX

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

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

1. Обратите внимание на то, что у вас включится режим конструктора. Кроме этого, изменится внешний вид указателя.

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

1. Кликните на указанную иконку.

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

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

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

1. Далее необходимо кликнуть правой кнопкой мыши по созданному элементу. Выберите «Объект Combobox». Затем – «Edit».

1. Благодаря этим действиям вы увидите, что внешний вид объекта стал другим. Исчезнет возможность изменения размера.

1. Теперь вы можете спокойно выбрать что-нибудь из этого списка.

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

1. Также необходимо закрыть окно свойств.

Убрать объекты ActiveX довольно просто.

1. Перейдите на вкладку «Разработчик».
2. Активируйте «Режим конструктора».

1. Кликните на этот объект.

1. Нажмите на горячую клавишу Delete .
2. И всё сразу же исчезнет.

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

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

1. Создайте какую-нибудь похожую таблицу. Главное условие – нужно добавить для каждого пункта несколько дополнительных вариантов выбора.

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

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

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

1. В этом окне необходимо выбрать пункт «Список».

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

1. Для сохранения используйте кнопку «OK».

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

В графе «Тип данных» снова указываем «Список». В поле источник укажите следующую формулу.

=ДВССЫЛ(B11)

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

1. Обязательно сохраните все внесенные изменения.

После нажатия на «OK» вы увидите ошибку источника данных. Ничего страшного тут нет. Кликните на «Да».

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

1. Выберите что-нибудь из предлагаемых товаров.

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

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

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

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

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

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

Благодарим за уделённое нам время!