Наименование списка в excel. Создание выпадающего списка в Excel. Как настроить зависимые выпадающие списки в Excel с поиском

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

Из раскрывающегося списка несколькими кликами мыши можно ввести в назначенные ячейки нужную информацию. Широко применяются раскрывающиеся списки при написании расчетных программ в Excel.

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

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

Вариант №0 — «Элементарный».

Делая очередную запись в ячейку А9, при наборе первой буквы наименования профиля, например «Ш», Excel предлагает заполнить ячейку словом «Швеллер». После набора «Ш» достаточно нажать кнопку «Ввод» на клавиатуре – и слово будет введено в ячейку.

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

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

Вариант №1 — «Простейший».

Если активировать мышью ячейку А9, нажать сочетание клавиш «Alt» «↓», то появится раскрывающийся список, содержащий все ранее введенные в этом столбце значения. Остается лишь выбрать мышью нужную запись. Вместо набора вышеуказанного сочетания клавиш можно щелчком правой кнопки мыши вызвать контекстное меню и выбрать в нем пункт «Выбрать из раскрывающегося списка…». В результате увидим тот же выпадающий список.

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

Вариант №2 — «Простой».

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

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

1. Создаем список возможных значений, записав их в столбец по одному в ячейку. Допустим это перечень в ячейках А2…А8.

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

3. Выбираем в главном меню кнопку «Данные» – «Проверка…».

4. В выпавшем окне «Проверка вводимых значений» выбираем вкладку «Параметры».

5. В поле «Тип данных:» из раскрывающегося списка (подобного тому, который мы создаем) выбираем значение «Список».

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

7. Устанавливаем (если он не установлен по умолчанию) флажок «Список допустимых значений» и нажимаем кнопку «ОК».

Раскрывающийся список готов. Его можно скопировать как формулы в любое количество ячеек!

Вариант №3 — «Сложный».

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

Создадим раскрывающийся список этим способом.

1. Создаем список-справочник в ячейках А2…А8.

2. Выбираем в главном меню кнопку «Вид» – «Панели инструментов» – «Формы».

3. В появившейся панели «Формы» выбираем «Поле со списком» и рисуем его, например, в ячейке А9.

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

4. Щелкаем правой кнопкой мыши по нарисованному элементу и в появившемся контекстном меню выбираем «Формат объекта».

5. В выпавшем окне «Форматирование объекта» на вкладке «Элемент управления» заполняем поля в соответствии с рисунком, расположенном ниже и нажимаем «ОК».

6. Раскрывающийся список готов. Он выводит порядковый номер элемента списка в связанную ячейку В9. (Можете назначить любую удобную вам ячейку, не обязательно В9!)

Для вывода в какую-либо ячейку самого значения из списка-справочника применим функцию ИНДЕКС. Допустим, нам необходимо вывести значение в ячейку А9, расположенную под элементом «Поле со списком».

Для этого в ячейку А9 запишем формулу: =ИНДЕКС(A2:A8;В9)

Наглядный пример подробно рассмотрен в статье « ». Можно перейти по ссылке и ознакомиться.

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

Вариант №4 — «Самый сложный».

Для создания выпадающего списка в этом случае используется также элемент «Поле со списком», но панели инструментов «Элементы управления» (в MS Excel 2003). Это так называемые элементы ActiveX. Здесь все очень похоже внешне на вариант №3, но значительно шире возможности настройки и форматирования элемента.

1. Выбираем в главном меню кнопку «Вид» – «Панели инструментов» – «Элементы управления».

2. В появившейся панели «Элементы управления» выбираем «Поле со списком» и рисуем его в ячейке А9. Элемент ActiveX «Поле со списком» размещается не в самой ячейке, а сверху, накрывая ее!!!

3. Нажимаем кнопку «Свойства» на панели «Элементы управления» и в выпавшем окне «Properties» («Свойства») вручную вписываем диапазон исходных данных, адрес связанной ячейки (ячейки, куда будет введено выбранное значение) и количество отображаемых строк.

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

5. Отжимаем кнопку «Выход из режима конструктора» на панели «Элементы управления» и проверяем работу раскрывающегося списка. Все работает! Выбранное значение записано в ячейку А9, в нашем примере — под элементом «Поле со списком». Вообще связанной ячейкой может быть абсолютно любая кроме ячеек, где располагается базовый список.

Итоги.

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

На практике я чаще всего создаю раскрывающиеся списки в Excel, используя варианты №1 и №3, реже — вариант №2 и совсем редко — вариант №4, хотя именно он является, безусловно, самым гибким, предоставляющим самые широкие возможности.

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

Подписывайтесь на анонсы статей в окне, расположенном в конце каждой статьи или в окне вверху страницы и не забывайте подтверждать подписку кликом по ссылке в письме, которое придет к вам на указанную почту (может прийти в папку «Спам» — все зависит от настроек вашей почты)!!!

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

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

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

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

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

Выберите ячейку, в которую будете вставлять выпадающий список;
Переходим на вкладку ленты Данные ;
Выбираем команду Проверка данных ;
В выпадающем списке выбираем значение Список ;
В поле Источник указываем следующую формулу =ДВССЫЛ("Таблица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 - так как это ширина нашего столбца с подгруппами.

При заполнении таблички в редакторе Excel очень часто данные повторяются. К примеру, вы пишите наименование товара или ФИО работника. Сегодня мы поговорим о том, как в Эксель сделать выпадающий список, чтобы постоянно не вводить одно и то же, а просто выбирать нужное значение.

Есть самый простой вариант справиться с поставленной задачей. Вы делаете правый клик мышкой по ячейке под столбиком с данными для вызова контекстного меню. Тут ищем пункт «Выбрать из раскрывающегося списка». Это же действие легко выполнить комбинацией Alt + стрелочка вниз .

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

Традиционный способ

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

Вставка/Имя/Присвоить (Excel 2003)

В свежих версиях (2007, 2010, 2013, 2016) переходите:

Формулы/Определенные имена/Диспетчер имен/Создать

Вписываете любое наименование, тапаете ОК .

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

Данные/Тип данных/Список

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

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

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

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

во-первых, ищите раздел «Разработчик» (Эксель 2007/2010), в других выпусках активируете самостоятельно через параметры «Настроить ленту»;

во-вторых, переходите на раздел, кликаете «Вставить»;
в-третьих, выбираете «Поле…» и жмете по значку;
рисуете прямоугольник;

делаете правый клик по нему и жмете Формат объекта;

ищите «Формировать…», выделяете нужные границы;
отмечаете ту область, куда хотите установить порядковый номер элемента в перечне;
жмете ОК.

Как создать в Экселе выпадающее меню: пользуемся элементами ActiveX

Действия аналогичны вышеописанным, но ищем «Поле с ActiveX».

Здесь основные отличия в том, что спецэлемент ActiveX может быть в 2 вариантах - режим отладки, который позволяет менять параметры, и - режим ввода, получится сделать только выборку сведений из него. Поменять режим можно клавишей Режим Конструктора в разделе Разработчик . С помощью этого способа вы можете настроить цвет, шрифт и выполнить оперативный поиск.

Дополнительные возможности

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

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

Для второй активируем окошко проверки сведений, но в пункте «Источник» пишем «=ДВССЫЛ» и адрес 1-й ячейки. Все получилось.

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

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

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

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

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

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

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

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

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

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

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

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

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

Стандартный

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ActiveX

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

=ДВССЫЛ(B11)

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

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

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

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

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

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

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

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

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

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

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

ПРЕДСТАВЬТЕ СИТУАЦИЮ: Мы хотим создать в 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-й именованный диапазон.

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