Работа с VB проектом (12)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (63)
Разное (39)
Баги и глюки Excel (3)

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

Скачать файл, используемый в видеоуроке:

Статья помогла? Поделись ссылкой с друзьями! Видеоуроки

{"Bottom bar":{"textstyle":"static","textpositionstatic":"bottom","textautohide":true,"textpositionmarginstatic":0,"textpositiondynamic":"bottomleft","textpositionmarginleft":24,"textpositionmarginright":24,"textpositionmargintop":24,"textpositionmarginbottom":24,"texteffect":"slide","texteffecteasing":"easeOutCubic","texteffectduration":600,"texteffectslidedirection":"left","texteffectslidedistance":30,"texteffectdelay":500,"texteffectseparate":false,"texteffect1":"slide","texteffectslidedirection1":"right","texteffectslidedistance1":120,"texteffecteasing1":"easeOutCubic","texteffectduration1":600,"texteffectdelay1":1000,"texteffect2":"slide","texteffectslidedirection2":"right","texteffectslidedistance2":120,"texteffecteasing2":"easeOutCubic","texteffectduration2":600,"texteffectdelay2":1500,"textcss":"display:block; padding:12px; text-align:left;","textbgcss":"display:block; position:absolute; top:0px; left:0px; width:100%; height:100%; background-color:#333333; opacity:0.6; filter:alpha(opacity=60);","titlecss":"display:block; position:relative; font:bold 14px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff;","descriptioncss":"display:block; position:relative; font:12px \"Lucida Sans Unicode\",\"Lucida Grande\",sans-serif,Arial; color:#fff; margin-top:8px;","buttoncss":"display:block; position:relative; margin-top:8px;","texteffectresponsive":true,"texteffectresponsivesize":640,"titlecssresponsive":"font-size:12px;","descriptioncssresponsive":"display:none !important;","buttoncssresponsive":"","addgooglefonts":false,"googlefonts":"","textleftrightpercentforstatic":40}}

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Способ 1. Стандартный.

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

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

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

Выделяем будущую ячейку со списком

В Excel 2007 и выше данное окно вызывается через вкладку «Данные » -> «Проверка данных ».

Список в ячейке в MS Excell 2010

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

Указываем диапазон ячеек со значениями списка

Задаем конкретный тип вводимых значений, в нашем случае рассматриваем элемент «Список».

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

Вот какой результат получается в итоге.

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

Для того чтобы в поле «Источник» не задавать постоянно диапазон значений. Можно эти значения объединить в одну категорию, присвоить ей имя и писать это имя в данной графе.

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

1. Шаг 1 – выбираем меню – «Вставка»;
2. Шаг 2 – переходим в меню «Имя»;
3. Шаг 3 — открываем диалоговое окно «Присвоить».

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

Если у вас английская версия тогда так

1. Insert;
2. Name;
3. Define.

В случае, если работаете с седьмой офисной версией или более новее Excel 2007. Тогда вам в помощь вкладка «Формулы» – «Диспетчер имен» (Name Manager) , и выбираем создать. Выбор имени ничем не ограничен. Можете написать, например «Обзор».

Создание имени диапазона значений в Excel 2010

Указываем имя созданного диапазона

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

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

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

В этом способе рассмотрен вариант добавления нового объекта и привязка его к конкретному диапазону в екселе. Какие необходимо выполнить шаги:

Если у вас версия ексель 2007 года и позже, тогда выбираем меню Разработчик. Если версия ранняя тогда Вид — Панели инструментов – Формы.

Открываем панель элементов

В появившемся диалоговом окне элементов ActiveX, находим значок со списком «Поле со списком». Выносим элемент на страницу документа.

Формат элемента «Список»

В данном меню заносим диапазон значений и привязываем к ячейке данные.

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

В этом материале речь пойдет именно о заполнении таблицы. Здесь вы сможете узнать, как в Эксель сделать выпадающий список, который позволит значительно быстрее заполнять таблицу данными. Инструкция будет актуальна для Excel 2007, 2010, 2013 и 2016.

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

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

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

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

После чего нужно заполнить строку «Источник».

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

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

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

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

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

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

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

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

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

После выбора стиля, появится окно «Форматирование таблицы». Здесь нужно установить отметку напротив функции «Таблица с заголовком» и нажать «Ok».

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

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

Данный способ создания выпадающих списков с возможностью добавления новых строк был проверен на Excel 2010, но он также должен работать и в Excel 2007. Как и в более современных версиях Excel, например, Excel 2013 и Excel 2016.

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

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

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

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

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 - так как это ширина нашего столбца с подгруппами.