Verifique os valores encontrados em um intervalo do Excel, mas não em outro

Question

Eu tenho dois intervalos nomeados, entrada do usuário Profiles (intervalo vertical de uma coluna) e% pré-determinadaProfileNames (intervalo horizontal de uma linha). Eu tenho uma etapa de validação de dados na minha ferramenta e gostaria de verificar se alguma entrada de valores para Profiles não foi encontrada em ProfileNames . Eu estou tentando descobrir uma maneira de fazer isso em uma fórmula, mas eu parecia estar perplexo.

Atualmente, isso é obtido da seguinte forma: cada célula em ProfileNames é seu próprio nome ( Profile1 , Profile2 , etc.). Então esta fórmula é usada:

COUNTIFS(Profiles,"<>"&Profile1, Profiles,"<>"&Profile2, ...)

com algo maior que 0, acionando um pop-up. Eu gostaria de passar por isso, já que eventualmente haverá mais de 200 perfis para verificar e essa fórmula é difícil de controlar. Preciso de uma fórmula para substituir a anterior, que detectará as células em Profiles que não correspondem a pelo menos uma célula em ProfileNames .

Dados da amostra:

Cooling | Heating | Cooking 1 .5 .75

A primeira linha acima (Arrefecimento: Cozinhar) é ProfileNames "Arrefecimento" é Profile1 , Aquecimento é Profile2 , etc. Esta coluna é Profiles :

Cooling Cooling Cooking Heating Heating

Estou tentando evitar o uso de Profile1 etc, pois haverá mais de 200 deles e a fórmula usada agora poderá se expandir para exceder o limite máximo de caracteres.

    

Answer 1

Você pode usar uma fórmula de matriz para verificar. Digite

=SUM((Profiles = ProfileNames) * 1)

em uma célula, mas ao invés de usar enter aperte control-shift-enter. O resultado é o número de entradas em Profiles que correspondem a uma entrada em ProfileNames . Portanto, você sabe que, se houver, 5 entradas em Profiles e a soma é 5, todas são incluídas em ProfileNames . Se a soma for menor que 5, uma das entradas em Profiles não está correta.

Explicação: Profiles = ProfileNames cria uma matriz de TRUE s e FALSE s correspondente à verificação de cada linha em Profiles em relação a cada coluna em ProfileNames . O * 1 transforma essa matriz em uma matriz de números e SUM os resume.