Como calcular percentis no Excel

Como calcular percentis no Excel

O Excel tem duas funções principais pra calcular percentis: PERCENTILE.INC() e PERCENTILE.EXC().

Falando sério, também tem a terceira função —PERCENTILE()— que era usada nas versões antigas do software (Excel 2007 e anteriores) e agora é mantida por motivos de compatibilidade. Essa função antiga funciona da mesma forma que PERCENTILE.INC().

Neste tutorial, vamos focar nas funções mais recentes —PERCENTILE.INC() e PERCENTILE.EXC().

Função PERCENTILE.INC() do Excel

Sintaxe

PERCENTILE.INC(array, k)

onde:

• array—o conjunto ou intervalo de dados para o qual o percentil é calculado,

• k—o valor percentual na faixa de 0 a 1, inclusive.

Os dois argumentos são necessários.

A função PERCENTILE.INC() retorna o k-ésimo percentil dos valores em um intervalo, onde k está no intervalo de 0 a 1, incluindo os valores 0 e 1. Ou seja, essa função inclui o primeiro e o último valores da matriz.

Algoritmo

Nos bastidores, a função Excel PERCENTILE.INC() faz o seguinte:

1. Ordena os valores da matriz fornecida em ordem crescente.
2. Determina o valor percentual 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 faz uma interpolação entre os valores mais próximos da matriz.

Exemplo

Digamos que a gente queira calcular o percentil 30 para uma matriz de números inteiros de 1 a 10, usando a função do Excel PERCENTILE.INC(). Aqui está como será a nossa fórmula:

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

Abaixo está o resultado do nosso cálculo:

Possíveis erros

• #NUM!— quando a matriz fornecida estiver vazia, ou k < 0, ou k > 1.

• #VALUE!—quando k não é numérico.

Função PERCENTILE.EXC() do Excel

Sintaxe

PERCENTILE.EXC(array, k)

onde:

• array—o conjunto ou intervalo de dados para o qual o percentil é calculado,

• k—o valor percentual no intervalo de 0 a 1, excluindo o 1.

Os dois argumentos são necessários.

A função PERCENTILE.EXC() retorna o k-ésimo percentil dos valores em um intervalo, onde 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() só define percentis para probabilidades estritamente entre 0 e 1. Isso quer dizer que ele não calcula os valores para o mínimo absoluto (percentil 0) ou máximo (percentil 100), embora todos os pontos de dados ainda sejam usados na interpolação.

Algoritmo

Nos bastidores, a função Excel PERCENTILE.EXC() faz o seguinte:

1. Ordena os valores da matriz fornecida em ordem crescente.
2. Determina o valor percentual 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 faz uma interpolação entre os valores mais próximos da matriz.

Exemplo

Digamos que a gente queira calcular o percentil 30 para uma matriz de números inteiros de 1 a 10, inclusive, usando a função do Excel PERCENTILE.EXC(). Aqui está como será a nossa fórmula:

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

Abaixo está o resultado do nosso cálculo:

Possíveis erros

• #NUM!— quando a matriz fornecida estiver vazia, ou k ≤ 1/(N+1), ou k ≥ N/(N+1).

• #VALUE!—quando k não é numérico.

Pra aprender ou dar uma atualizada em outras funções populares do Excel, dá uma olhada nesse tutorial: As 15 fórmulas básicas do Excel que todo mundo precisa saber.

PERCENTILE.INC VS. PERCENTILE.EXC no Excel

Até agora, dá pra ver claramente várias semelhanças entre as funções do Excel PERCENTILE.INC() e PERCENTILE.EXC(). Mas, também tem diferenças claras entre elas que fazem com que sejam duas funções diferentes e acabam tendo resultados diferentes. Vamos recapitular como as funções PERCENTILE.INC() e PERCENTILE.EXC() no Excel são parecidas e como elas diferem.

Semelhanças:

1. Sintaxe da função.

2. Lógica geral do algoritmo (exceto a fórmula usada para calcular o valor percentual).

3. Erros possíveis (#NUM! quando a matriz está vazia ou k está fora do 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), excluindo.

2. Tratando os valores de uma matriz:

• PERCENTILE.INC()—inclui o primeiro e o último valores.

• PERCENTILE.EXC()—não define percentis nos primeiros e últimos valores (0% e 100%), mas ainda inclui todos os dados na sua interpolação.

3. Fórmula para calcular o valor percentual:

• PERCENTILE.INC()—k(N-1) + 1.

• PERCENTILE.EXC()—k(N+1).

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

A tabela acima mostra que a função PERCENTILE.EXC() não está definida para valores de k dentro ou fora do seu intervalo válido (k ≤ 1/(N+1) ou k ≥ N/(N+1)). Isso não é um problema. Isso mostra a definição matemática de percentis exclusivos. As pequenas diferenças entre os resultados de .INC() e .EXC() perto dos extremos vêm de como cada função interpola as posições percentuais.

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

Na maioria dos casos, faz mais sentido escolher a função do Excel “ PERCENTILE.INC() ”, que também combina com as fórmulas padrão usadas para calcular percentis em muitas linguagens de programação, incluindo Python e R.

Mas, se a gente quiser evitar definir percentis bem nas extremidades do conjunto de dados, a função PERCENTILE.EXC() é a escolha certa. Não vai calcular os resultados para o percentil 0 ou 100, mas ainda usa todos os pontos de dados ao interpolar entre as classificações.

É importante ter dados limpos e representativos para extrair insights comerciais valiosos a partir deles. O curso Preparação de dados no Excel e a Folha de dicas para manipulação de dados no Excel são recursos úteis para aprender a preparar seus dados de forma eficiente para análises posteriores.

Conclusão

A gente falou sobre a teoria estatística essencial por trás dos percentis, como eles são definidos, as principais áreas em que são usados e as duas funções principais para calcular percentis no Excel, incluindo a sintaxe, os algoritmos, exemplos de cálculos e possíveis erros.

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

Para aproveitar todo o potencial do Excel, considere fazer um programa básico de Excel, que vai te ajudar a aprender, por meio de exercícios práticos, os recursos e fórmulas básicos e avançados usados para preparar, visualizar e analisar dados no Excel.