Создание второго выпадающего списка существенно сложней

Первое, что нужно сделать, это в любом свободном месте создать таблицу из двух столбцов. В первом столбце будет бренд, во втором столбце будет ОС. Причем, важно, значения в столбце бренд должны быть упорядочены.

Такая таблица создана на листе 1 в диапазоне B50:Q66. Смысл этой таблицы в том, чтобы задать все возможные сочетания бренда и операционной системы в соответствии с исходными данными (лист1 A1:Q26).

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

Основная проблема здесь - как заполнить поле «Источник». Нужно создать формулу, которая в соответствии с выбранным брендом, т.е. со значением ячейки C3 листа 2, возвращала бы диапазон ячеек, содержащих ОС для этого бренда.

Т.е., например, если в C3 у нас НТС, нам нужен диапазон ячеек C54:C56, т.к. в именно в этих ячейках ОС для НТС.

Формула получилась такая:

=СМЕЩ(Лист1!$B$50;ПОИСКПОЗ(Лист2!$C$3;Лист1!$B$50:$B$66;0)-1;1;СЧЁТЕСЛИ(Лист1!$B$50:$B$66;Лист2!$C$3);1)

Примечание. Не надо пытаться понять работу формулы, просто глядя на нее, это очень трудно.

Для того, чтобы разобраться в ее работе, можно сделать следующее:

- в ячейке С3 листа 2 выбрать НТС

- выделить диапазон ячеек на листе 2, например, N2:N7

- ввести формулу в строку формул

- вводить формулу нужно как формулу массива, т.е. через Ctrl+Shift+Enter

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

После этого нужно нажать на кнопку f_x рядом со строкой формул. Должно появиться такое окошко

Из этой картинки уже можно понять смысл формулы:

- поиск нужного диапазона всегда начинается с ячейки B50 листа1,

- есть смещение по строкам вниз Смещ_по_строкам, в данном случае оно равно 4,

- есть смещение по столбцам вправо Смещ_по_столбцам, всегда равно 1,

- высота диапазона, в данном случае равна 3,

- ширина всегда равна 1.

Рассмотрим, что это дает.

От ячейки B50 листа 2 четыре строки вниз – это ячейка B54.

От B54 один столбец вправо – это С54.

От С54 – высота 3, ширина 1 – это C54:C56.

Получили как раз что нужно - диапазон с ОС для HTC.

Как рассчитывается смещение по строкам:

ПОИСКПОЗ(Лист2!$C$3;Лист1!$B$50:$B$66;0)-1

Вот в этой части ПОИСКПОЗ(Лист2!$C$3;Лист1!$B$50:$B$66;0) ищется позиция значения ячейки C3 листа 2 в диапазоне B50:B66 листа 1. Для НТС это будет B54, т.е. пятая позиция.

Но нам нужна не позиция, а смещение по строкам, т.е. по сути, разница между B54 (или другой найденной ячейкой, если выбран другой бренд) и B50. Поэтому всегда из найденной позиции вычитаем единицу.

Как рассчитывается высота:

СЧЁТЕСЛИ(Лист1!$B$50:$B$66;Лист2!$C$3)

Тут просто – сколько раз НТС (значение ячейки C3 листа 2) встречается в диапазоне B50:B66 листа 1 – три раза.

Ввести полученную формулу в поле «Источник» «Проверки данных» не получится по тем же самым причинам – выпадающий список на одном листе, данные для него на другом.

В этом случае воспользуемся диспетчером имен. На ленте Формулы >> Диспетчер имен.

Нажмем на кнопку «создать», в поле имя введем «ПоискОС», а формулу введем в поле «диапазон». Нажмем ОК. Должно получится так

Теперь можно создать второй выпадающий список.

Выбираем ячейку F3, Данные >> Проверка данных >> Тип данных - список >> Источник:

=ПоискОС

Поиск по сайту: