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

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

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

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

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

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

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

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

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

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

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

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

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

Связанные статьи

Похожие задачи
Прочитайте другие статьи, решающие похожие задачи в MS Excel. Это позволит Вам решать широкий класс подобных задач.
Средняя: 5 (1 оценка)