[Fórmulas Matriciais] Classificar uma lista dinamicamente

Quero mostrar o poder do uso de fórmulas matriciais através de 4 formas de classificar uma lista seja de nomes ou números. Imagine qualquer uma lista com 24 nomes no intervalo A2:A25, vamos classificar dinamicamente estes nomes de quatro maneiras:

  1. Classificação crescente sem repetição
  2. Classificação decrescente sem repetição
  3. Classificação crescente com repetição
  4. Classificação decrescente com repetição

Veja a lista e o resultado esperado:

Excelmaniacos - Classificar dinamicamente 1

Fazer isso com uso de uma única fórmula para cada classificação só é possível graças ao recurso de fórmulas matriciais, que possibilita um processamento de múltiplos cálculos dentro de uma única célula resultado em matrizes que podem ser trabalhadas com outras funções como MAIOR, MENOR, SOMA, MÉDIA, CORRESP, ÍNDICE, etc.

Este é um campo bem complexo para entender, porém recomendo o estudo destas fórmulas, pois ao dominá-las seu nível de Excel sofrerá um grande incremento!

Neste post não vou explicar cada fórmula, só quero disponibilizar o arquivo para servir de referência para estudo e para aplicações em outros desenvolvimentos. Essa classificação dinâmica pode ser utilizada para construção de relatórios, criar uma origem para validação por lista suspensa, e tudo mais que sua imaginação permitir.

Só cuidado que exige-se mais processamento do pc ao trabalhar com fórmulas matriciais, por isso quanto maior a base de dados na qual a fórmula estiver se referenciando, mais lenta ficará a planilha, então pondere bem a aplicação deste recurso.

Veja as solução para estas classificações abaixo e baixe o arquivo e explore a ferramenta de “Avaliar Fórmula” na aba de fórmulas para ver passo a passo o processamento da fórmula.

1. Classificação crescente sem repetição

=SEERRO(DESLOC($A$1;CORRESP(MAIOR(CONT.SE($A$2:$A$25;”>”&$A$2:$A$25)*(CONT.SE(INDIRETO(“A2:A”&LIN($A$2:$A$25));$A$2:$A$25)=1);SE(LIN(A1)>SOMA(SE(
FREQÜÊNCIA(SE(NÚM.CARACT($A$2:$A$25)>0;CORRESP($A$2:$A$25;$A$2:$A$25;0);””);
SE(NÚM.CARACT($A$2:$A$25)>0;CORRESP($A$2:$A$25;$A$2:$A$25;0);””))>0;1));-1;LIN(A1)));
CONT.SE($A$2:$A$25;”>”&$A$2:$A$25)-($A$2:$A$25=””);0);0);””)

2. Classificação decrescente sem repetição

=SEERRO(DESLOC($A$1;CORRESP(MENOR(CONT.SE($A$2:$A$25;”>”&$A$2:$A$25)*
(CONT.SE(INDIRETO(“A2:A”&LIN($A$2:$A$25));$A$2:$A$25)=1)+SE(CONT.SE(INDIRETO(“A2:A”&
LIN($A$2:$A$25));$A$2:$A$25)=1;0;999999999999999);SE(LIN(A1)>SOMA(SE(FREQÜÊNCIA(
SE(NÚM.CARACT($A$2:$A$25)>0;CORRESP($A$2:$A$25;$A$2:$A$25;0);””);SE(NÚM.CARACT($A$2:$A$25)>
0;CORRESP($A$2:$A$25;$A$2:$A$25;0);””))>0;1));-1;LIN(A1)));CONT.SE($A$2:$A$25;”>”&$A$2:$A$25)
-($A$2:$A$25=””);0);0);””)

3. Classificação crescente com repetição

=SEERRO(DESLOC($A$1;CORRESP(MAIOR(CONT.SE($A$2:$A$25;”>”&$A$2:$A$25)-
($A$2:$A$25=””);LIN(1:1));CONT.SE($A$2:$A$25;”>”&$A$2:$A$25)-SE(($A$2:$A$25=””);-2;0);0);0);””)

4. Classificação decrescente com repetição

=SEERRO(DESLOC($A$1;CORRESP(MENOR(CONT.SE($A$2:$A$25;”>”&$A$2:$A$25)+SE(($A$2:$A$25=””);
CONT.VALORES($A$2:$A$25);0);LIN(1:1));CONT.SE($A$2:$A$25;”>”&$A$2:$A$25)-($A$2:$A$25=””);0);0);””)

Estas fórmulas estão bem complexas e todas devem ser inseridas com CRTL+SHIFT+ENTER, pois desenvolvi de forma que desconsidere células vazios no meio da lista. Não é tão fácil atender esta condição, mas as fórmulas acima fazem isso.

Baixe o modelo: Excelmaniacos Classificar listas dinâmicamente por fórmulas matriciais

Ate!

Anúncios

2 comentários sobre “[Fórmulas Matriciais] Classificar uma lista dinamicamente

  1. Ola Rafael, vim parar aqui no seu blog atraves da minha postagem no Forum da Microsoft.
    Sou muito grato pelas ajudas quem proporcionado para mim,muito obrigado.

    Parabens pelo blog que esta muito bem feito e desejo que tenha sucessos.

    Experimentei as formulas matriciais acima e vi que e bem consistente e funcionam muito bem, porem para a minha finalidade, que tenho que acrescentar e eliminar linhas, vao dar alguns problemas.
    Para verificar se serviria para o meu caso, fiz o seguinte: coloquei a coluna Lista ao lado da coluna Classificar e copiei a formula ate onde termina a linha da coluna Lista e utilizei a Tabela para que a Lista ficasse dinamica enquanto acrescento mais nomes no fim da Lista e funcionou perfeitamente.No meu caso preciso de inserir linhas no meio da lista tambem, e para isso nao teve nenhum problema, porem quando tenho que eliminar linhas, as formulas matricias comecam a apresentar resultados inesperados.
    Se voce fizer uma que tambem aceitasse eliminacao de linhas, a utilizacao das formulas teriam uma aplicacao mais amplas, penso eu.

    Ja que voce agora tem um blog, fica uma sugestao minha de fazer um tema de Como Estruturar uma Formula para a sua Necessidade. Voce poderia comecar explicando como planejar a obtencao do resultado desejado , o que precisa, conhecimentos e detalhes de cada funcoes , planos , modo de exprimir etc…Por exemplo, em vez de pegar um lapis e escrever num papel em branco,porque em alguns casos nao deixar tudo preto e escrever com borracha???….e o caso que nos ja discutimos sobre como formatar com bordas de linhas grossas se na formatacao de celulas nao tem essa opcao?, entao deixa tudo com linha grossa e formata com linha fina,nao eh(la no forum da microsoft onde tem essa postagem esta na espera um pergunta que fiz para voce, se puder responder agradeceria).
    Outra coisa que acho que os profissionais da area usam (nao sou profissional da area) e dividir 1/1 =1, 1/0= erro equivale tambem dividir 1/verdade=1, 1/ falso= erro–> se der erro coloca um branco…..
    Estou propondo isso, porque acho que tem muita gente como eu que faz perguntas nos forum e recebe uma resposta como …para esse caso use essa caixa preta…..ops legal funcionou…obrigado…..num belo dia deixa de funcionar e nao sabe porque……Tem um velho proverbio que diz, em vez de dar um peixe para um necessitado, ensine ele a pescar que ele nunca mais vai passar necessidade…..

    Tadao

    Curtir

  2. Quando tem uma celulas vazias na coluna lista, na coluna Clasificar fica compactado eliminando as celulas vazias,mas se tiver celulas na coluna lista com formulas que retornam vazios, na coluna Classificar nao reconhece e aparece os vazios, como mudar a formula para que atenda essas condicoes tambem?

    Tadao

    Curtir

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s