Формула для определения доступных опций на основе предпосылок

Question

Пример: электронная таблица для одного учащегося имеет четыре столбца: Курс | Завершено?| Предпосылки | Доступные

В столбце "Курс" перечислены названия курсов. Завершено?В колонке указано "да", если студент закончил курс. Столбец "Предварительные условия" содержит список курсов, разделенных запятыми, которые студент должен пройти, чтобы иметь возможность пройти курс в столбце "Курс" в этой строке; все курсы, которые отображаются в этом столбце, находятся в некоторой строке в столбце "Курс".

Цель состоит в том, чтобы поместить формулу в столбец "Доступно", в которой будет указано "да", если для каждого курса в столбце "Предпосылки" в столбце "Завершено" для строки этого курса указано "да". Если бы была только одна предпосылка, это был бы простой vlookup. Однако, поскольку здесь может быть неопределенное количество курсов, мне нужно проанализировать курсы и выполнить переменное количество просмотров в зависимости от количества предварительных условий.

Есть ли простой способ сделать это без использования кода VBA? Возможно использование массива с sumproduct?

Answer 1

Предполагая, что ваша таблица находится, например, в A1:D10 (с заголовками в строке 1, так что Course, Completed?, Prerequisites и Available находятся в A1, B1, C1 и D1 соответственно), а также предполагая, что записи в столбце Prerequisites являются каждым разделив точную комбинацию одной запятой, за которой следует один пробел, затем введите эту формулу в D2:

= IF(SUMPRODUCT(0+(ISNUMBER(ПОИСК ("," & $ A $ 2:$ A $ 10 & ",", "," & C2 & ","))), 0+($ B $ 2:$ B $ 10 = "Да"))= 1+LEN (С2)-LEN (ПОДСТАВИТЬ (С2, "", "")), "Да", "Нет")

Скопируйте при необходимости.

С уважением