Fórmula genérica
=IF(logical_test,"P","")
Resumo
Para exibir uma marca de seleção se um valor for "permitido" com base em uma lista existente de valores permitidos, você pode usar uma fórmula baseada na função IF junto com a função CONT.SE. No exemplo mostrado, a fórmula em C5 é:
=IF(COUNTIF(allowed,B5),"✓","")
onde permitido é o intervalo nomeado E5: E9.
Explicação
Esta fórmula é um bom exemplo de aninhamento de uma função dentro de outra. Basicamente, esta fórmula usa a função IF configurada para retornar uma marca de seleção (✓) quando um teste lógico retorna TRUE:
=IF(logical_test,"✓","")
Se o teste retornar FALSO, a fórmula retornará uma string vazia (""). Para o teste lógico, estamos usando a função CONT.SE assim:
COUNTIF(allowed,B5)
COUNTIF conta as ocorrências do valor em B5 no intervalo nomeado permitido (E5: E9). Isso pode parecer "ao contrário" para você, mas se você pensar sobre isso, faz sentido. Se o valor em B5 for encontrado na lista de valores permitidos, CONT.SE retornará um número positivo (neste caso 1). Caso contrário, CONT.SE retornará zero. O Excel avaliará qualquer número diferente de zero como VERDADEIRO, então isso funciona perfeitamente como o teste lógico para IF.
IF só retornará TRUE se o valor for encontrado na lista de permitidos e, se for o caso, o resultado final será uma marca de seleção (✓). Se o valor não for encontrado na lista permitida, CONT.SE retorna zero, que é avaliado como FALSO. Nesse caso, o resultado final é uma string vazia (""), que não exibe nada.
Com valores fixos
O exemplo acima mostra os valores permitidos em um intervalo de células, mas os valores permitidos também podem ser codificados nas fórmulas como uma constante de matriz como esta:
=IF(COUNTIF(("red","blue","green"),B5),"✓","")
Caractere de marca de verificação (✓)
Inserir um caractere de marca de seleção no Excel pode ser surpreendentemente desafiador e você encontrará muitos artigos na internet explicando várias abordagens. A maneira mais fácil de obter o caractere de marca de seleção (✓) usado nesta fórmula no Excel é simplesmente copiá-lo e colá-lo. Se você estiver copiando desta página da web, cole na barra de fórmulas para evitar arrastar em uma formatação indesejada. Você também pode copiar e colar diretamente da planilha anexada.
Se você tiver problemas para copiar e colar, tente esta variação. O caractere em si é Unicode 2713 (U + 2713), e também pode ser inserido no Excel com a função UNICHAR como esta:
=UNICHAR(10003) // returns "✓"
Portanto, a fórmula original pode ser escrita assim:
=IF(COUNTIF(allowed,B5),UNICHAR(10003),"")
Nota: a função UNICHAR foi introduzida no Excel 2013.
Estendendo a fórmula
A ideia básica desta fórmula pode ser estendida de muitas maneiras inteligentes. Por exemplo, a captura de tela abaixo mostra uma fórmula que retorna uma marca de seleção apenas quando todas as pontuações dos testes são pelo menos 65:
A fórmula em G5 é:
=IF(NOT(COUNTIF(B5:F5,"<65")),"✓","")
A função NOT inverte o resultado de CONT.SE. Se você achar isso confuso, pode alternativamente reestruturar a fórmula IF assim:
=IF(COUNTIF(B5:F5,"<65"),"","✓")
Na versão da fórmula, a lógica é mais semelhante à fórmula original acima. No entanto, movemos a marca de seleção para o argumento valor_se_falso, portanto, a marca de seleção aparecerá apenas se a contagem de CONT.SE for zero. Em outras palavras, a marca de seleção aparecerá apenas quando nenhum valor inferior a 65 for encontrado.
Observação: você também pode usar a formatação condicional para destacar entradas válidas ou inválidas e a validação de dados para restringir a entrada para permitir apenas dados válidos.
