Fórmula genérica
=MMULT(--(data>TRANSPOSE(data)),ROW(data)^0)
Resumo
Para classificar e extrair dinamicamente valores exclusivos de uma lista de dados, você pode usar uma fórmula de matriz para estabelecer uma classificação em uma coluna auxiliar e, em seguida, usar uma fórmula INDEX e MATCH especialmente construída para extrair valores exclusivos. No exemplo mostrado, a fórmula para estabelecer a classificação em C5: C13 é:
=IF(data="",ROWS(data),MMULT(--(data>TRANSPOSE(data)),ROW(data)^0))
onde "dados" é o intervalo nomeado B5: B13.
Observação: esta é uma fórmula de matriz de várias células, inserida com control + shift + enter.
Explicação
Nota: a ideia central desta fórmula é adaptada de um exemplo no excelente livro de Mike Girvin Control + Shift + Enter.
O exemplo mostrado usa várias fórmulas, que são descritas a seguir. Em um alto nível, a função MMULT é usada para calcular uma classificação numérica em uma coluna auxiliar (coluna C), e essa classificação é então usada por uma fórmula INDEX e MATCH na coluna G para extrair valores exclusivos.
Classificação de valores de dados
A função MMULT realiza a multiplicação da matriz e é usada para atribuir uma classificação numérica a cada valor. A primeira matriz é criada com a seguinte expressão:
--(data>TRANSPOSE(data))
Aqui, usamos a função TRANSPOSE para criar uma matriz horizontal de dados , e todos os valores são comparados uns com os outros. Em essência, cada valor é comparado com todos os outros valores para responder à pergunta "este valor é maior do que qualquer outro valor". Isso resulta em uma matriz bidimensional, 9 colunas x 9 linhas, preenchida com valores TRUE e FALSE. O duplo negativo (-) é usado para forçar os valores TRUE FALSE para 1s e zeros. Você pode visualizar a matriz resultante assim:
A matriz de 1s e zeros acima torna-se array1 dentro da função MMULT. Array2 é criado com esta expressão:
ROW(data)^0
Aqui, cada número de linha em "dados" é elevado à potência de zero para criar um array unidimensional, 1 coluna x 9 linhas, preenchido com o número 1. MMULT então retorna o produto da matriz dos dois arrays, que se tornam o valores vistos na coluna de classificação.
Recebemos todas as 9 classificações ao mesmo tempo em uma matriz, portanto, precisamos colocar os resultados em células diferentes de uma vez. Caso contrário, cada célula mostrará apenas o primeiro valor de classificação na matriz que é retornada.
Observação: esta é uma fórmula de matriz de várias células, inserida com control + shift + enter, no intervalo C5: C13.
Tratamento de células em branco
As células vazias são tratadas com esta parte da fórmula de classificação:
=IF(data="",ROWS(data)
Aqui, antes de executarmos o MMULT, verificamos se a célula atual em "dados" está em branco. Nesse caso, atribuímos um valor de classificação que é igual à contagem de linhas nos dados. Isso é feito para forçar as células em branco para o final da lista, onde podem ser facilmente excluídas posteriormente à medida que os valores exclusivos são extraídos (explicado abaixo).
Contando valores únicos
Para contar valores únicos nos dados, a fórmula em E5 é:
=SUM(--(FREQUENCY(rank,rank)>0))-(blank>0)
Como a fórmula de classificação acima atribui uma classificação numérica a cada valor, podemos usar a função FREQUENCY com SUM para contar valores únicos. Esta fórmula é explicada em detalhes aqui. Em seguida, subtraímos 1 do resultado se houver células vazias nos dados:
-(blank>0)
onde "em branco" é o intervalo nomeado E8 e contém esta fórmula:
=COUNTBLANK(data)
Essencialmente, reduzimos a contagem única em um se houver células em branco nos dados, uma vez que não as incluímos nos resultados. A contagem exclusiva na célula E5 é chamada de "exclusiva" (para contagem exclusiva) e é usada pela fórmula INDEX e MATCH para filtrar células em branco (descritas abaixo).
Extraindo valores únicos
Para extrair valores únicos, G5 contém a seguinte fórmula, copiada:
=IF(ROWS($G$5:G5)>unique,"",INDEX(data,MATCH(MIN(IF(ISNA(MATCH(data,$G$4:G4,0)),rank)),rank,0)))
Antes de executarmos a fórmula INDEX e MATCH, primeiro verificamos se a contagem de linha atual na área de extração é maior que a contagem única do intervalo nomeado "exclusivo" (E5):
=IF(ROWS($G$5:G5)>unique,"",
Se for assim, terminaremos de extrair os valores únicos e retornaremos uma string vazia (""). Caso contrário, executamos a fórmula de extração:
INDEX(data,MATCH(MIN(IF(ISNA(MATCH(data,$G$4:G4,0)),rank)),rank,0))
Observe que há duas funções MATCH aqui, uma dentro da outra. O MATCH interno usa um intervalo de expansão para uma matriz e o intervalo nomeado "dados" para o valor de pesquisa:
MATCH(data,$G$4:G4,0)
Observe que o intervalo de expansão começa na "linha acima", linha 4 no exemplo. O resultado do MATCH interno é uma matriz que, para cada valor nos dados, contém uma posição numérica (o valor já foi extraído) ou o erro # N / A (o valor ainda não foi extraído). Em seguida, usamos IF e ISNA para filtrar esses resultados e retornar o valor de classificação para todos os valores em "dados" ainda não extraídos:
IF(ISNA(results),rank))
Esta operação resulta em uma matriz, que é alimentada na função MIN para obter o "valor mínimo de classificação" para valores de dados ainda não extraídos. A função MIN retorna esse valor para o MATCH externo como um valor de pesquisa e o intervalo nomeado "classificação" como a matriz:
MATCH(min_not_extracted,rank)),rank,0)
Por fim, MATCH retorna a posição do valor de classificação mais baixo para INDEX como um número de linha e INDEX retorna o valor dos dados na linha atual do intervalo de extração.
