Fórmula genérica
=UNIQUE(FILTER(data,COUNTIF(data,data)>n))
Resumo
Para extrair uma lista de valores únicos de um conjunto de dados, filtrados por contagem ou ocorrência, você pode usar UNIQUE com FILTER e aplicar critérios com a função COUNTIF. No exemplo mostrado, a fórmula em D5 é:
=UNIQUE(FILTER(data,COUNTIF(data,data)>1))
que produz os 3 valores únicos que aparecem mais de uma vez no intervalo nomeado "dados" (B5: B16).
Observação: neste exemplo, estamos extraindo uma lista exclusiva de valores que aparecem mais de uma vez. Em outras palavras, estamos criando uma lista de duplicatas :) A linguagem é um tanto confusa.
Explicação
Este exemplo usa a função UNIQUE junto com a função FILTER. Você pode ver um exemplo mais básico aqui.
O truque neste caso é aplicar critérios à função FILTER para permitir apenas valores com base na contagem de ocorrências. Trabalhando de dentro para fora, isso é feito com CONT.SE e a função FILTER aqui:
FILTER(data,COUNTIF(data,data)>1)
O resultado de COUNTIF é uma matriz de contagens como esta:
(3;1;3;3;2;1;1;3;1;2;3;3)
que são verificados com a comparação lógica> 1 para produzir uma matriz ou valores TRUE / FALSE:
(TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE;TRUE;TRUE)
Observe que TRUE corresponde a valores nos dados que aparecem mais de uma vez. Essa matriz é retornada para FILTER como o argumento de inclusão , usado para filtrar os dados. FILTER retorna outra matriz como resultado:
("red";"green";"green";"blue";"red";"blue";"red";"green")
Esta matriz é retornada diretamente para a função UNIQUE como o argumento da matriz . Aviso dos 12 valores originais, apenas 8 sobreviveram.
UNIQUE então remove duplicatas e retorna a matriz final:
("red";"green";"blue")
Se os valores em B5: B16 mudarem, a saída será atualizada imediatamente.
Contagem> 2
A fórmula em F5, que lista as cores que aparecem pelo menos 2 vezes nos dados de origem, é:
=UNIQUE(FILTER(data,COUNTIF(data,data)>2))
Faixa de fonte dinâmica
Como os dados (B5: B15) são um intervalo com nome normal, eles não serão redimensionados se os dados forem adicionados ou excluídos. Para usar um intervalo dinâmico que será redimensionado automaticamente quando necessário, você pode usar uma Tabela do Excel ou criar um intervalo nomeado dinâmico com uma fórmula.
