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

Описание

В 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)

Где:

F3 - адрес ячейки с первым выпадающим списком - замените на свой.

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

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

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

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

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

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

Дать имя диапазона D1:D3 (например Марки )
Выбрать на вкладке Данные (Data) команду Проверка данных (Data validation)
Выбрать из выпадающего списка вариант проверки Список (List) и указать в качестве Источники (Source) = Марки или просто выделить ячейки D1: D3 (если они на том же листе, где и список).

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

Нажмите Ctrl+F3 или воспользуйтесь кнопкой Диспетчер имен (Name manager) на вкладке Формулы (Formulas) . В версиях до 2003 года это была команда меню Вставка - Имя - Присвоить (Insert - Name - Define)
Создайте новый именуемый диапазон с любым именем (например Модели ) и в поле Линк (Reference) в нижней части окна введите руками следующую формулу:

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

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

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

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

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

начальная_ячейка - берем первую ячейку нашего списка, то есть А1 ;
сдвиг_вниз - нам считает функция MATCH, которая, попросту говоря, выдает порядковый номер ячейки с выбранной маркой (G7) в заданном диапазоне (столбце А );
сдвиг_вправо =1 , поэтому мы хотим сослаться на модели в соседнем столбце ( В );
размер_диапазона_в_строках - вычисляем с помощью функции COUNTIF, которая умеет подсчитать количество вхождений в списке (столбце А) нужных нам значений – марок авто ( G7 );
размер_диапазона_в_столбцах =1 Поэтому нам нужен один столбец с моделями.

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

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

Выделяем ячейку G8
Выбираем на вкладке Данные (Data) команду Проверка данных (Data validation) или в меню Данные - Проверка (Data - Validation)
Из выпадающего списка выбираем вариант проверки Список (List) и вводим в качестве Источники (Source) знак равен и имени нашего диапазона, т.е. = Модели .

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