Como calcular percentis no Excel

Como calcular percentis no Excel

O Excel oferece duas funções principais para calcular percentis: PERCENTILE.INC e PERCENTILE.EXC.

A rigor, há também a terceira função - PERCENTUAL - que era usada em versões antigas do software (Excel 2007 e anteriores) e agora é mantida para fins de compatibilidade. Essa função antiga funciona da mesma forma que a PERCENTILE.INC.

Neste tutorial, vamos nos concentrar nas funções mais recentes: PERCENTILE.INC e PERCENTILE.EXC.

Função do Excel PERCENTILE.INC

Sintaxe

PERCENTILE.INC(matriz, k)

onde:

• matriz - amatriz ou ointervalo de dados para o qual o percentil é calculado,
• k - ovalor do percentil no intervalo de 0 a 1, inclusive.

Ambos os argumentos são necessários.

A função PERCENTILE.INC retorna o k-ésimo percentil de valores em um intervalo, em que k está no intervalo de 0 a 1, incluindo os valores 0 e 1. Em outras palavras, essa função inclui o primeiro e o último valores da matriz.

Algoritmo

Em essência, a função PERCENTILE.INC do Excel executa as seguintes etapas:

1. Classifica os valores da matriz fornecida em ordem crescente.
2. Determina o valor do percentil com base no argumento k e no número N de pontos de dados na matriz usando a fórmula: k(N-1) + 1.
3. Se o resultado da etapa anterior não for um número inteiro, o algoritmo interpola entre os valores mais próximos da matriz.

Exemplo

Digamos que você queira calcular o 30º percentil de uma matriz de números inteiros de 1 a 10, inclusive, usando a função PERCENTILE.INC do Excel. Aqui está a aparência da nossa fórmula:

Observação: Embora os valores da matriz acima estejam ordenados para fins ilustrativos, na verdade não precisamos ordenar os valores antecipadamente, pois o algoritmo fará isso por nós nos bastidores.

Abaixo está o resultado de nosso cálculo:

Erros possíveis

• #NUM!- quando a matriz fornecida estiver vazia, ou k < 0, ou k > 1.
• #VALUE!-quando k não for numérico.

PERCENTILE.EXC Função do Excel

Sintaxe

PERCENTILE.EXC(matriz, k)

onde:

• matriz - amatriz ou ointervalo de dados para o qual o percentil é calculado,
• k-ovalor do percentil no intervalo de 0 a 1, exclusive.

Ambos os argumentos são necessários.

A função PERCENTILE.EXC retorna o k-ésimo percentil de valores em um intervalo, em que k está no intervalo de 0 a 1, excluindo os valores 0 e 1. Mais precisamente, para uma matriz com N pontos de dados, o valor de k deve satisfazer as duas condições a seguir:

1. K > 1/(N+1)
2. K < N/(N+1)

Simplificando, a função PERCENTILE.EXC exclui o primeiro e o último valores da matriz.

Algoritmo

Nos bastidores, a função PERCENTILE.EXC do Excel executa as seguintes etapas:

1. Classifica os valores da matriz fornecida em ordem crescente.
2. Determina o valor do percentil com base no argumento k e no número N de pontos de dados na matriz usando a fórmula: k(N+1).
3. Se o resultado da etapa anterior não for um número inteiro, o algoritmo interpola entre os valores mais próximos da matriz.

Exemplo

Digamos que você queira calcular o 30º percentil de uma matriz de números inteiros de 1 a 10, inclusive, usando a função PERCENTILE.EXC do Excel. Aqui está a aparência da nossa fórmula:

Observação: Embora os valores da matriz acima estejam ordenados para fins ilustrativos, na verdade não precisamos ordenar os valores antecipadamente, pois o algoritmo fará isso por nós nos bastidores.

Abaixo está o resultado de nosso cálculo:

Erros possíveis

• #NUM!- quando a matriz fornecida estiver vazia, ou k <= 1/(N+1), ou k >= N/(N+1).
• #VALUE!-quando k não for numérico.

Para aprender ou atualizar outras funções populares do Excel, confira este tutorial: As 15 fórmulas básicas do Excel que todo mundo precisa saber.

PERCENTILE.INC VS. PERCENTILE.EXC no Excel

Até agora, podemos ver claramente muitas semelhanças entre as funções PERCENTILE.INC e PERCENTILE.EXC do Excel. No entanto, há também diferenças distintas entre elas que as tornam duas funções distintas e resultam em resultados diferentes. Vamos recapitular como as funções PERCENTILE.INC e PERCENTILE.EXC no Excel são semelhantes e como elas diferem.

Semelhanças:

1. Sintaxe da função.
2. Lógica geral do algoritmo (exceto pela fórmula usada para determinar o valor do percentil).
3. Possíveis erros (#NUM! quando a matriz está vazia ou k está fora de seu intervalo possível e #VALUE! quando k não é numérico).

Diferenças:

1. O intervalo possível para o argumento k:
• PERCENTILE.INC - de 0 a 1, inclusive.
• PERCENTILE.EXC - de 1/(N+1) a N/(N+1), exclusivo.
2. Tratamento dos valores de uma matriz:
• PERCENTILE.INC-inclui o primeiro e o último valores.
• PERCENTILE.EXC-exclui o primeiro e o último valores.
3. Fórmula para determinar o valor do percentil:
• PERCENTILE.INC-k(N-1) + 1.
• PERCENTILE.EXC—k(N+1).

Vamos ver as funções PERCENTILE.INC e PERCENTILE.EXC do Excel em ação, aplicando-as na mesma matriz de números inteiros de 1 a 10, inclusive, e brincando com diferentes valores de k:

A tabela acima demonstra as limitações da função PERCENTILE.EXP quando se trata de calcular percentis para valores muito baixos ou muito altos de k (mais precisamente, quando k <= 1/(N+1) ou k >= N/(N+1)), bem como as diferenças entre pares nos resultados para cada valor de k que aumentam em direção aos valores extremos de k e podem ser explicadas pelas diferenças computacionais dos algoritmos subjacentes.

Por outro lado, devemos ter em mente que, na maioria predominante dos casos, valores muito baixos ou muito altos de k não têm valor especial para nós do ponto de vista estatístico. Portanto, essas limitações normalmente não são um problema. Além disso, na vida real, geralmente precisamos calcular percentis para matrizes muito maiores do que a do exemplo acima, o que atenua as diferenças de resultados.

Na maioria dos casos, faz mais sentido você optar pela função PERCENTILE.INC do Excel, que também corresponde às fórmulas padrão usadas para calcular percentis em muitas linguagens de programação, incluindo Python e R.

No entanto, se, por qualquer motivo, não confiarmos no primeiro e no último valores de nosso conjunto de dados, considerando-os estatisticamente não representativos, a função PERCENTILE.EXP pode ser uma escolha melhor, pois excluirá esses valores dos cálculos.

É importante que você tenha dados limpos e representativos para extrair insights comerciais valiosos. O curso Preparação de dados no Excel e a Folha de dicas de manipulação de dados no Excel são recursos úteis para que você aprenda a preparar seus dados de forma eficiente para análise posterior.

Conclusão

Discutimos a teoria estatística essencial por trás dos percentis, sua definição, as principais áreas de sua aplicação e as duas principais funções usadas para calcular percentis no Excel, incluindo sua sintaxe, algoritmos, exemplos de cálculo e possíveis erros.

Também comparamos as duas funções do Excel, descrevemos suas semelhanças e diferenças importantes, colocamos ambas as funções em ação na mesma matriz de dados para confrontar os resultados e aprendemos o melhor cenário para cada uma delas.

Para desbloquear todo o poder do Excel, considere fazer um curso de habilidades do Excel Fundamentals para iniciantes, que ajudará você a aprender, por meio de exercícios práticos, os recursos básicos e avançados e as fórmulas usadas para preparação, visualização e análise de dados no Excel.