Fórmula genérica
(=INDEX(list,MATCH(0,COUNTIF(uniques,list),0)))
Resumo
Para extrair apenas valores exclusivos de uma lista ou coluna, você pode usar uma fórmula de matriz baseada em INDEX, MATCH e COUNTIF. No exemplo mostrado, a fórmula em D5, copiada, é:
(=INDEX(list,MATCH(0,COUNTIF($D$4:D4,list),0)))
onde "lista" é o intervalo nomeado B5: B11.
Observação: esta é uma fórmula de matriz e deve ser inserida usando control + shift + enter.
Explicação
O núcleo desta fórmula é uma pesquisa básica com INDEX:
=INDEX(list,row)
Em outras palavras, dê a INDEX a lista e um número de linha, e INDEX recuperará um valor para adicionar à lista exclusiva.
O trabalho árduo é descobrir o número ROW para dar INDEX, de modo que obtenhamos apenas valores únicos. Isso é feito com MATCH e COUNTIF, e o truque principal está aqui:
COUNTIF($D$4:D4,list)
Aqui, COUNTIF conta quantas vezes os itens já na lista exclusiva aparecem na lista mestre, usando uma referência de expansão para o intervalo, $ D $ 4: D4.
Uma referência de expansão é absoluta de um lado e relativa do outro. Nesse caso, conforme a fórmula é copiada, a referência se expandirá para incluir mais linhas na lista exclusiva.
Observe que a referência começa em D4, uma linha acima da primeira entrada exclusiva, na lista exclusiva. Isso é intencional - queremos contar itens * já * na lista exclusiva e não podemos incluir a célula atual sem criar uma referência circular. Portanto, começamos na linha acima.
Importante: certifique-se de que o título da lista exclusiva não apareça na lista mestre.
Para os critérios em COUNTIF, estamos usando a própria lista mestre. Quando dados vários critérios, COUNTIF retornará vários resultados em uma matriz. Em cada nova linha, temos uma matriz diferente como esta:
(0;0;0;0;0;0;0) // row 5 (1;0;0;0;1;0;0) // row 6 (1;1;0;0;1;0;1) // row 7 (1;1;1;1;1;0;1) // row 8
Nota: CONT.SE lida com vários critérios com uma relação "OR" (ou seja, CONT.SE (intervalo, ("vermelho", "azul", "verde")) conta em vermelho, azul ou verde.
Agora temos as matrizes de que precisamos para encontrar as posições (números das linhas). Para isso, usamos MATCH, configurado para correspondência exata, para encontrar valores zero. Se colocarmos as matrizes criadas por COUNTIF acima em MATCH, aqui está o que obtemos:
MATCH(0,(0;0;0;0;0;0;0),0) // 1 (Joe) MATCH(0,(1;0;0;0;1;0;0),0) // 2 (Bob) MATCH(0,(1;1;0;0;1;0;1),0) // 3 (Sue) MATCH(0,(1;1;1;1;1;0;1),0) // 6 (Aya)
MATCH localiza itens procurando por uma contagem de zero (isto é, procurando por itens que ainda não aparecem na lista única). Isso funciona, porque MATCH sempre retorna a primeira correspondência quando há duplicatas.
Finalmente, as posições são alimentadas em INDEX como números de linha e INDEX retorna o nome naquela posição.
Versão sem matriz com LOOKUP
Você pode construir uma fórmula sem matriz para extrair itens exclusivos usando a função flexível LOOKUP:
=LOOKUP(2,1/(COUNTIF($D$4:D4,list)=0),list)
A construção da fórmula é semelhante à fórmula INDEX MATCH acima, mas LOOKUP pode lidar com a operação de matriz nativamente.
- COUNTIF retorna contagens de cada valor da "lista" no intervalo de expansão $ D $ 4: D4
- A comparação com zero cria uma matriz de valores TRUE e FALSE
- O número 1 é dividido pela matriz, criando uma matriz de erros 1s e # DIV / 0
- Este array se torna o lookup_vector dentro de LOOKUP
- O valor de pesquisa de 2 é maior do que qualquer valor em lookup_vector
- LOOKUP corresponderá ao último valor sem erro na matriz de pesquisa
- LOOKUP retorna o valor correspondente em result_vector, o intervalo nomeado "lista"
Extraia itens que aparecem apenas uma vez
A fórmula LOOKUP acima é fácil de estender com a lógica booleana. Para extrair uma lista de itens exclusivos que aparecem apenas uma vez nos dados de origem, você pode usar uma fórmula como esta:
=LOOKUP(2,1/((COUNTIF($D$4:D4,list)=0)*(COUNTIF(list,list)=1)),list)
A única adição é a segunda expressão CONT.SE:
COUNTIF(list,list)=1
Aqui, COUNTIF retorna uma matriz de contagens de itens como esta:
(2;2;2;2;2;1;2)
que são comparados a 1, resultando em uma matriz de valores VERDADEIRO / FALSO:
(FALSE;FALSE;FALSE;FALSE;FALSE;TRUE;FALSE)
que atuam como um "filtro" para restringir a saída a itens que ocorrem apenas uma vez nos dados de origem.
