0123-Estatísticas com os n maiores/menores valores

Alguma vez já precisou determinar a média dos cinco maiores valores de um conjunto de dados? Ou a soma dos três menores valores?
Vejamos como realizar estas operações utilizando o Excel.
Esta é a série de dados que utilizaremos:

Primeiramente vamos realizar o cálculo da média dos cinco maiores valores. Para retornar os cinco maiores valores utilizaremos a função MAIOR. Desta forma =MAIOR($A$2:$A$21;3)  retorna o 3º maior valor do conjunto (91). Para retornar os cinco maiores valores de uma só vez podemos utilizar a fórmula =MAIOR($A$2:$A$21;{1;2;3;4;5}), cujo resultado é {96;93;91; 87;86}. Como o seu resultado é uma matriz, temos que pressionar CTRL+SFHT+ENTER, para inserir a fórmula como uma fórmula matricial.

Tendo obtido o subconjunto desejado, podemos calcular a média utilizando a fórmula acima como argumento: =MÉDIA(MAIOR($A$2:$A$25;{1;2;3;4;5})). O resultado obtido é 90,6.

Embora a fórmula acima atenda perfeitamente ao seu objetivo, podemos modificá-la para torná-la mais flexível. Se quisermos, ao invés de cinco valores, buscarmos dez ou quatro, seria necessário editar a fórmula, aumentando ou reduzindo a constante matricial dos n valores. A versão a seguir é uma alternativa que se ajusta automaticamente com base no parâmetro informado pelo usuário na célula  D12:

=MÉDIA(MAIOR($A$2:$A$25;LIN(INDIRETO(“1:”&$D$12)))).

Seguindo raciocínio análogo, podemos calcular a soma dos três menores valores: =SOMA(MENOR($A$2:$A$25;LIN(INDIRETO(“1:”&$D$24)))), sendo que em D24 será o informado o tamanho do subconjunto a ser obtido.

Veja aqui o arquivo com as fórmulas: Maiores e Menores

In Excel We Trust

Marcado com: , , , , , ,
Publicado em Nível: Avançado

Deixe uma resposta

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

Visitas dos usuários
  • 1,964,110

Clique para assinar este blog e receber notificações de novos artigos por email.

Junte-se a 1.476 outros seguidores

Aumente sua interação

Siga-nos do twitter (@usuariosdoexcel).

Acesse o nosso fórum e compartilhe as suas dúvidas.

Curta nossa página no Facebook.

Junte-se ao grupo do LinkedIn.

Visite-nos utilizando o seu smartphone ou tablet.

Recomende este Blog
Visualizar notícias
Site monitorado:
Real Time Web Analytics

Clicky

%d blogueiros gostam disto: