Как настроить зависимые выпадающие списки в MS Excel, используя СМЕЩ и СУММПРОИЗВ

В этой статье мы продемонстрирует простой подход по настройке выпадающего списка, зависящего от другого выпадающего списка. Например, мы выбираем страну в ячейке F1 и это изменяет список городов, доступных для выбора в ячейке F2, как показано на Рисунке 1.

Предположим, что мы уже настроили выпадающий список для страны, ссылающийся на диапазон A1:C1, тогда мы можем настроить список городов, используя формулу ниже, где:

• СМЕЩ возвращает диапазон для зависимого выпадающего списка

• A2 фиксирует начальную ячейку для функции СМЕЩ

• 0 говорит функции СМЕЩ, что вертикального смещения нет

• ПОИСКПОЗ(F1;A1:C1;0)-1 говорит функции СМЕЩ на сколько столбцов нужно сместиться вправо от начальной ячейки A2

• СУММПРОИЗВ((F1=A1:C1)*(A2:C3<>»»)) сообщает функции СМЕЩ количество непустых ячеек (A2:C3<>»») в выбранном столбце (F1=A1:C1)

Рисунок 2 демонстрирует зависимый список городов, когда в ячейке для страны выбрана Украина, где:

• (F1=A1:C1) – это массив {ЛОЖЬ;ИСТИНА;ЛОЖЬ}

• (A2:C3<>»») – это массив {ЛОЖЬ;ИСТИНА;ИСТИНА:ЛОЖЬ;ЛОЖЬ;ИСТИНА}

• (F1=A1:C1)*(A2:C3<>»») – это массив {0;1;0:0;0;0}, поскольку произведение ЛОЖЬ*ЛОЖЬ или ЛОЖЬ*ИСТИНА равно 0, тогда как произведение ИСТИНА*ИСТИНА равно 1

• Функция СУММПРОИЗВ возвращает сумму массива {0;1;0:0;0;0}, равную 1 в нашем случае

Продемонстрированный подход по настройке зависимых выпадающих списков является наиболее простым и наглядным из всех возможных.