Compare os conteúdos da lista no excel

Question

Então aqui está minha situação: Eu estou criando uma planilha onde eu tenho 9 agrupamentos de 3 listas onde os itens escolhidos estão todos contidos em listas feitas via validação de dados. O que eu preciso fazer é adicionar uma pontuação para cada um desses grupos de 3, onde se as 3 escolhas feitas a partir da lista de validação de dados são únicas das escolhas feitas em 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 de 1, pois as opções são únicas. No grupo 2 você tem laranja, amarelo e rosa - dando-lhe um valor de 1 também porque as escolhas são únicas. Então, e assim por diante, no entanto, digamos que no grupo 9 você tem magenta, vermelho e roxo - este grupo vai marcar 0, pois não é completamente único (roxo foi escolhido no grupo 1). Note que as escolhas são feitas em momentos diferentes, e pontuadas nesses intervalos separados - então, onde o grupo 2 só se compara ao grupo 1, o grupo 3 se compara ao grupo 1 & 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 de #VALUE! em vez de 1 ou 0. Gostaria de saber se preciso fazer cada célula individualmente, portanto, se o conteúdo da célula B1 não for igual ao conteúdo em A1, A2, ou A3, em seguida, verifique o conteúdo da célula B2 e se eles não são iguais ao conteúdo das células A1, A2 ou A3, em seguida, verifique o conteúdo da célula B3 e se eles não são iguais ao conteúdo de A1: A3, em seguida, o O valor dessa lista é 1. Se eu tiver que fazer isso individualmente, alguma idéia de como eu daria 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 o windows 10. Agradeço antecipadamente por qualquer ajuda.

    

Answer 1

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 em B1 ocorre no intervalo A1:A3 , você pode escrever

=COUNTIF(A1:A3,B1)

que você pode transformar em um resultado booleano comparando com 0 (significando B1 não está em A1:A3 )

=COUNTIF(A1:A3,B1)=0

Mas você também precisa verificar se B2 e B3 não ocorrem em A1:A3 . Você pode usar a função AND() para isso:

=AND(COUNTIF(A1:A3,B1)=0,COUNTIF(A1:A3,B2)=0,COUNTIF(A1:A3,B3)=0)

que retornará True se nenhum dos B1 , B2 e B3 ocorrer em A1:A3 . Para nos permitir copiar essa função através de 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 - > %código%. Quando copiamos a fórmula para a direita, o intervalo aumentará para $A1:A3 , $A1:B3 e assim por diante para finalmente $A1:C3 para a nona coluna

A fórmula acima retorna um resultado booleano, $A1:I3 ou True . Você indicou que precisa de 1 ou 0, o que é feito facilmente com a adição de uma função False em torno da fórmula anterior.

Agora podemos escrever a fórmula para a célula IF() as

=IF(AND(COUNTIF($A1:A3,B1)=0,COUNTIF($A1:A3,B2)=0,COUNTIF($A1:A3,B3)=0),1,0)

copie para as células B4

Já que pela sua definição, cada grupo de 3 é único um ao outro, nenhuma fórmula é necessária em C4:I4 , você pode simplesmente configurá-lo para 1.

Atualizar

Nota! Células vazias em um intervalo como A4 resultam em 1. Você não especificou, mas talvez queira que um grupo com valores ausentes retorne 0. Você pode fazer isso adicionando um quarto termo à função C1:C3 : AND() . A entrada em COUNTBLANK((B1:B3)=0 seria 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 as células B4