Связанный список в EXCEL на основе элемента управления формы

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

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

Модифицируем файл примера из статьи Расширяемый связанный список . Сначала удалим содержимое ячеек A5:B22 на листе Таблица , в которые заносились данные с помощью Связанного списка . Под оставшимися заголовками Регион и Страна разместим два элемента управления форм Список .

СОВЕТ :Для того, чтобы разместить элемент управления Список точно по границам ячеек, воспользуйтесь клавишей ALT .

Теперь, свяжем эти списки, соответственно, с ячейками A 3 и B 3 .

Через Диспетчер имен ( Формулы/ Определенные имена/ Диспетчер имен ) изменим Именованную формулу Позиция . В поле Диапазон укажем формулу =таблица!$A$3 . В ячейке A3 теперь хранится номер позиции выбранного Региона , который используется для определения диапазона содержащего соответствующий перечень стран.

Выделив элемент Список, соответствующий Регионам , правой клавишей вызываем контекстное меню, Формат объекта …, вкладка Элемент управления , в поле Формировать список по диапазону вводим имя диапазона Регионы .

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

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

У Связанного списка , созданного на основе Элемента управления формы Список, имеется существенный недостаток. Проделаем следующие манипуляции:

• выберите регион Европа;
• выберите страну Испания (в ячейке B3 будет значение 5);
• выберите регион Африка ;
• в списке Страны выделится Конго (элемент №3),а в ячейке B3 будет по прежнему значение 5, как будто в списке стран выбран пятый элемент.

Ссылка на несуществующий элемент может стать источником ошибки. Безусловно, эту ошибку можно корректно обработать обычной формулой (если значение в ячейке B3 больше, чем число элементов в списке, то должно быть заменено позицией последнего существующего элемента). Эта простая формула =МИН(B3;ЧСТРОК(Страны)) помещена в ячейку С3 файла примера.