[Перевод] Как настроить зависимые выпадающие списки в MS Excel, используя СМЕЩ и СУММПРОИЗВ
В этой статье мы продемонстрирует простой подход по настройке выпадающего списка, зависящего от другого выпадающего списка. Например, мы выбираем страну в ячейке F1 и это изменяет список городов, доступных для выбора в ячейке F2, как показано на Рисунке 1.
Рисунок 1. Выбор города в стране
Предположим, что мы уже настроили выпадающий список для страны, ссылающийся на диапазон A1: C1, тогда мы можем настроить список городов, используя формулу ниже, где:
СМЕЩ возвращает диапазон для зависимого выпадающего списка
A2 фиксирует начальную ячейку для функции СМЕЩ
0 говорит функции СМЕЩ, что вертикального смещения нет
ПОИСКПОЗ (F1; A1: C1;0)-1 говорит функции СМЕЩ на сколько столбцов нужно сместиться вправо от начальной ячейки A2
СУММПРОИЗВ ((F1=A1: C1)*(A2: C3<>»)) сообщает функции СМЕЩ количество непустых ячеек (A2: C3<>») в выбранном столбце (F1=A1: C1)
=СМЕЩ (A2;0; ПОИСКПОЗ (F1; A1: C1;0)-1; СУММПРОИЗВ ((F1=A1: C1)*(A2: C3<>»))) |
Рисунок 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 в нашем случае
Рисунок 2. Выбор города в Украине
Продемонстрированный подход по настройке зависимых выпадающих списков является наиболее простым и наглядным из всех возможных.
