Então aqui está a minha situação: estou criando uma planilha onde tenho 9 agrupamentos de 3 listas onde os itens escolhidos estão todos contidos em listas feitas via validação de dados. O que preciso fazer é adicionar uma pontuação a cada um desses grupos de 3, onde se as 3 escolhas feitas na lista de validação de dados forem únicas das escolhas feitas nos agrupamentos anteriores de 3 (Observe que os agrupamentos de 3 nunca serão os mesmas escolhas, elas sempre serão exclusivas uma da outra).
Basicamente, digamos que no grupo 1 você tenha azul, verde e roxo - dando o valor 1 porque as opções são únicas. No grupo 2 você tem laranja, amarelo e rosa - atribuindo o valor 1 também porque as opções são únicas. E assim por diante, porém, digamos que no grupo 9 você tenha magenta, vermelho e roxo - este grupo receberá pontuação 0, pois não é completamente único (roxo foi escolhido no grupo 1). Observe que as escolhas são feitas em momentos diferentes e pontuadas nesses intervalos separados - portanto, onde o grupo 2 se compara apenas ao grupo 1, o grupo 3 se compara aos grupos 1 e 2, e assim por diante.
Eu tentei uma fórmula semelhante a:
=IF(NOT(B1:B3=A1:A3),1,0)
No entanto, a célula exibiu uma pontuação #VALUE!em vez de 1 ou 0. Estou me perguntando se preciso fazer cada célula individualmente, portanto, se o conteúdo da célula B1 não for igual ao conteúdo de A1, A2 ou A3, verifique o conteúdo da célula B2 e se não forem iguais ao conteúdo das células A1, A2 ou A3, verifique o conteúdo da célula B3 e se não forem iguais ao conteúdo de A1:A3, o valor dessa lista é 1 Se eu tiver que fazer isso individualmente, alguma idéia de como eu formularia a fórmula? Seria algo como:
=IF(NOT(A1:A3=B1,NOT(A1:A3=B2,NOT(A1:A3=B3),1,0)))
É possível fazer isso no Excel?
Estou usando a versão mais atual do Excel (office 365) com windows 10. Agradecemos antecipadamente por qualquer ajuda.
Responder1
Passo a passo (para apoiar qualquer nível de futuros leitores)
Use a função COUNTIF()para saber se um intervalo contém um valor. IOW, para verificar se o valor B1ocorre em range A1:A3, você pode escrever
=COUNTIF(A1:A3,B1)
que você pode transformar em um resultado booleano comparando com 0 (o significado B1não está em A1:A3)
=COUNTIF(A1:A3,B1)=0
Mas você também precisa verificar isso B2e B3não ocorrer em A1:A3. Você pode usar a AND()função para isso:
=AND(COUNTIF(A1:A3,B1)=0,COUNTIF(A1:A3,B2)=0,COUNTIF(A1:A3,B3)=0)
que retornará True se nenhum deles B1e B2ocorrer B3em A1:A3. Para nos permitir copiar essa função em uma linha e ajustar o intervalo que será verificado, alteramos a referência à primeira célula para uma referência de coluna fixa, A1:A3-> $A1:A3. Quando copiamos a fórmula para a direita, o intervalo crescerá para $A1:B3e $A1:C3assim por diante até finalmente $A1:I3a nona coluna
A fórmula acima retorna um resultado booleano, Trueou False. Você indicou que precisa de 1 ou 0, o que é feito facilmente com a adição de uma IF()função em torno da fórmula anterior.
Agora podemos escrever a fórmula na célula B4como
=IF(AND(COUNTIF($A1:A3,B1)=0,COUNTIF($A1:A3,B2)=0,COUNTIF($A1:A3,B3)=0),1,0)
depois copie para as célulasC4:I4
Como, pela sua definição, cada grupo de 3 é único, nenhuma fórmula é necessária A4, você pode simplesmente defini-la como 1.
Atualizar
Observação! Células vazias em um intervalo como C1:C3resultado em 1. Você não especificou isso, mas pode querer que um grupo com valores ausentes retorne 0. Você pode fazer isso adicionando um quarto termo à AND()função: COUNTBLANK((B1:B3)=0. A entrada B4seria então
=IF(AND(COUNTIF($A1:A3,B1)=0,COUNTIF($A1:A3,B2)=0,COUNTIF($A1:A3,B3)=0,COUNTBLANK(B1:B3)=0),1,0)
antes de copiar para célulasC4:I4