ВВЕРХ

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

YouLibreCalc for Excel logo

Описание

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


Способ 1. Функция INDIRECT

Этот прием основан на применении функции INDIRECT, которая умеет делать одну простую вещь – преобразовывать содержимое любой указанной ячейки в адрес диапазона, понимаемой Excel. То есть, если в ячейке лежит текст А1 ", то функция выдаст в результате ссылку на ячейку А1 . Если в ячейке лежит слово " Auto ", то функция выдаст ссылку на именуемый диапазон с именем Auto и т.д.

Возьмем, например, вот такой список моделей автомобилей Toyota, Ford и Nissan :

Выделим весь список моделей Тойоты (из ячейки А2 и вниз до конца списка) и дадим этому диапазону имя Toyota в меню Вставка - Имя - Присвоить (Insert - Name - Define) . Затем повторим то же самое со списками Ford и Nissan , задав соответственно имена диапазонов Ford и Nissan .

При предоставлении имен помните о том, что имена диапазонов в Excel не должны содержать пробелов, знаков препинания и начинаться обязательно с буквы. Поэтому если бы в одной из марок автомобилей присутствовал бы пробел (например Ssang Yong), то его пришлось бы заменить в ячейке и в имени диапазона на нижнее подчеркивание (т.е. Ssang_Yong).

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

Теперь создадим второй выпадающий список, в котором будут отображаться модели выбранной в первом списке марки. Точно так же, как в предыдущем случае, выделите пустую ячейку и откройте меню Данные - Проверка - Далее Список . В поле Источник нужно будет ввести следующую формулу:

=INDIRECT(F3)

Где:

Все. После нажатия на ОК содержимое второго списка будет выбираться по имени диапазона, выбранного в первом списке.

Минусы такого способа:

Способ 2. Список соответствий (OFFSET и MATCH)

Этот способ требует наличия отсортированного списка соответствий марка-модель следующего вида:

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

А вот для зависимого перечня моделей придется сделать именуемый спектр с функцией OFFSET, который будет динамически ссылаться лишь на ячейки моделей определенной марки. Для этого:

=OFFSET( $A$1 ; MATCH($G$7;$A:$A;0)-1 ; 1 ; COUNTIF($A:$A;$G$7) ; 1 )

Ссылки должны быть абсолютными (со знаками $ ). После нажатия Enter в формулу будут автоматически добавлены имена листов.

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

=OFFSET( начальная_ячейка ; сдвиг_вниз ; сдвиг_вправо ; размер_диапазона_в_строках ; размер_диапазона_в_столбцах )

Таким образом:

В результате должно получиться что-то вроде этого:

Осталось добавить выпадающий список на основе созданной формулы в ячейку G8 . Для этого:

Статьи по теме: