Fórmula para determinar as opções disponíveis com base em pré-requisitos

Question

Exemplo: Uma planilha para um único aluno tem quatro colunas: Curso | Concluído? | Pré-requisitos | Disponível

A coluna "Curso" lista os nomes dos cursos. O "Concluído?" coluna indica um "sim" se o aluno tiver concluído o curso. A coluna "Pré-requisitos" contém uma lista de cursos delimitada por vírgulas que o aluno precisa ter concluído para poder fazer o curso na coluna "Curso" daquela linha; Todos os cursos que aparecem nesta coluna estão em alguma linha na coluna "Curso".

O objetivo é colocar uma fórmula na coluna "Disponível" que indique "sim" se cada um dos cursos na coluna "Pré-requisitos" tiver um "sim" na coluna "Concluído" para a linha desse curso. Se houvesse apenas um pré-requisito, isso seria um simples resumo. No entanto, como pode haver uma quantidade indeterminada de cursos listados aqui, preciso analisar os cursos e executar uma quantidade variável de vlookups com base no número de pré-requisitos.

Existe uma maneira fácil de fazer isso sem usar qualquer código VBA? Talvez usando um array com sumproduct?

    

Answer 1

Supondo que sua tabela está em, e. A1: D10 (com cabeçalhos na linha 1, de modo que Curso , Concluído? , Pré-requisitos e Disponível estão em A1, B1, C1 e D1 respectivamente), e também assumindo que as entradas na coluna Pré-requisitos são separadas pela combinação precisa de uma única vírgula seguida por um único espaço, insira essa fórmula em D2:

= SE (SUMPRODUCT (0+ (ISNUMBER (SEARCH ("," & $ A $ 2: $ A $ 10 & ",", "," & C2 & ","))), 0+ ( $ B $ 2: $ B $ 10="Sim")) = 1 + LEN (C2) -LEN (SUBSTITUTO (C2, ",", "")), "Sim", "Não")

Copie conforme necessário.

Atenciosamente