Como criar uma curva em sino no Excel

Uma curva em sino, também conhecida como distribuição normal, é o tipo mais comum de distribuição de uma variável. É aceito como uma distribuição que ocorre naturalmente em muitas situações. Em uma curva em sino, o ponto mais alto do arco define a média, que é o maior número de ocorrências de um elemento. A probabilidade de ocorrências diminui em ambos os lados da curva. Você pode usar uma curva em forma de sino para comparar desempenhos, excluindo extremos, ou definir suas expectativas pela possibilidade de um resultado ficar dentro de uma faixa à esquerda ou à direita do centro. Neste guia, mostraremos como criar uma curva em forma de sino no Excel com um cenário de caso de uso do mundo real como exemplo.

Baixar pasta de trabalho

Noções básicas

Tudo que você precisa é a média (média) e os valores de desvio padrão do seu conjunto de dados. Ambas as métricas podem ser calculadas no Excel usando as fórmulas abaixo.

Em consideração a esses dois valores, os valores normalmente distribuídos seguem estas regras:

1. A área total sob o curva é igual a 1 (100%)
2. O centro da curva do sino é a média do ponto de dados
3. (1-σ) Cerca de 68,2% da área sob a curva cai dentro de um desvio padrão (média ± desvio padrão)
4. (2-σ) Cerca de 95,5% da área sob a curva cai dentro de dois desvios padrão (média ± 2 * desvio padrão)
5. (3-σ) Cerca de 99,7% da área sob a curva cai dentro de três desvios padrão (Média ± 3 * Desvio Padrão)

Imagem da Universidade da Virgínia

Criando uma curva em forma de sino no Excel

Vamos pegar um exemplo comum e dizer que estamos analisando o resultado do exame É para uma classe de alunos. Estaremos usando uma curva em forma de sino para medir os resultados do exame para uma melhor comparação.

Começamos calculando as métricas para gerar dados normalmente distribuídos que irão gerar nossa curva. Precisamos calcular:

• Média (média) dos valores.
• Desvio padrão dos valores.
• Limites de 3 desvios padrão para antes e depois da média.
• Valor do intervalo para pontos de dados normalmente distribuídos. Isso também requer uma determinação dos pontos de intervalo. Você pode selecionar qualquer número, mas tenha isso em mente, mais intervalos significam mais precisão.

Métricas

Comece calculando a média e o desvio padrão dos dados. Você pode usar as funções AVEREAGE e STDEV.P para calcular os valores de média e desvio padrão, respectivamente.

A próxima etapa é o cálculo os 3 valores de desvio padrão para definir os valores mínimo e máximo para 99,7% dos dados.

Depois de definir os valores mínimo e máximo para nossa curva, precisamos gerar os intervalos . Os valores de intervalo serão a base para os valores normalmente distribuídos. Para calcular os intervalos, tudo o que você precisa fazer é dividir a área entre os valores mínimo e máximo pela contagem do intervalo. Neste exemplo, definimos isso como 20, mas você pode usar um número maior para aumentar o número de pontos de dados.

Depois que o valor do intervalo é calculado, você pode gerar os pontos de dados. Para fazer isso, insira o valor mínimo em uma célula. Em seguida, logo abaixo do valor mínimo, insira a fórmula para adicionar o valor do intervalo ao mínimo. Aqui, usamos referências de células (como J4) que ajudam a preencher os pontos de dados facilmente até o valor máximo.

A próxima etapa é calcular os valores normalmente distribuídos dos pontos de dados gerados. Você pode usar a função NORM.DIST do Excel para gerar esses valores.

Use os pontos de dados preenchidos como o primeiro argumento da função. Os valores da média e do desvio padrão são os próximos argumentos. Conclua a fórmula com um valor booleano FALSO para usar o tipo não cumulativo desta função.

Gráfico

Estamos quase terminando! Selecione os pontos de dados e os valores de distribuição normal e, em seguida, insira um gráfico de dispersão X-Y. Use a versão Disperso com linhas suaves para criar uma curva em forma de sino no Excel.

O gráfico pode parecer um pouco estranho primeiro.Vamos ver como você pode fazer com que pareça melhor.

Para alterar o título do gráfico, clique duas vezes no título e atualize o nome.

Em seguida, clique duas vezes no Eixo X e defina os valores mínimo e máximo no painel Opções do eixo para eliminar o espaço em branco em ambos os lados. Isso dará ao seu gráfico um formato de sino melhor. Definimos valores que estão um pouco fora do nosso conjunto de dados. Por exemplo, 66 – 100 para os valores 66,30 – 99,86.

Você pode aprimorar ainda mais seu gráfico adicionando os valores de desvio padrão .