Como criar uma curva de sino no Excel

uma curva de sino, também conhecida como distribuição normal, é o tipo mais comum de distribuição para uma variável. É aceito como uma distribuição que ocorre naturalmente em muitas situações. Em uma curva de 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ência diminui em ambos os lados da curva. Você pode usar uma curva de sino para comparar desempenhos excluindo extremos ou definir suas expectativas pela possibilidade de que um resultado esteja dentro de um intervalo à esquerda ou à direita do centro. Neste guia, mostraremos como criar uma curva 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.

Para calcular a média (média) =MÉDIA(dados)
Para calcular o desvio-padrão =DESVPAD.P(dados)

Em consideração destes dois valores, normalmente distribuídos valores, siga estas regras:

  1. A área total sob a curva é igual a 1 (100%)
  2. O centro da curva é a média dos dados de ponto de
  3. (1-σ) Sobre 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

Criar uma curva em forma de sino em Excel

Vamos dar um exemplo comum, e dizer que estamos analisando resultados de exame para uma classe de alunos. Usaremos uma curva de sino para medir os resultados dos exames para uma melhor comparação.

começamos calculando as métricas para gerar um dado distribuído normal que gerará nossa curva. Precisamos calcular:

  • Média (Média) dos valores.
  • desvio padrão dos valores.
  • 3-limites de desvio padrão para a média antes e depois.
  • 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.
uma nota rápida antes de mergulhar nas fórmulas. Usamos intervalos nomeados em vez de referências de células para facilitar a leitura das fórmulas. Você pode encontrar mais detalhes sobre intervalos nomeados aqui: Excel Intervalos Nomeados

Métricas

Começar por calcular a média e o desvio padrão dos dados. Você pode usar o AVEREAGE e STDEV.P funciona para calcular os valores de média e desvio padrão, respectivamente.

curva de sino no excel

o próximo passo é calcular OS 3 valores de desvio padrão para definir os valores mínimo e máximo para os 99,7% dos dados.

os 3 valores de desvio padrão podem exceder o conjunto de dados real. Este é um comportamento normal que pode ocorrer com frequência com conjuntos de dados menores.

curva do sino no excel

Mínimo = 83.23 – 3 * 5.54 = 66.61

Máximo = 83.23 – 3 * 5.54 = 99.86

Depois de definir os valores mínimo e máximo para a 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 de intervalos. Neste exemplo, definimos isso para 20, mas você pode usar um número maior para aumentar o número de pontos de dados.

valor do Intervalo de = (99.85 – 66.60) / 20 = 1.66

uma Vez 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.

o próximo passo é calcular os valores normalmente distribuídos a partir dos pontos de dados gerados. Você pode usar a norma do Excel.Função DIST para gerar esses valores.

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

Gráfico

Estamos quase acabando! Selecione os pontos de dados e os valores normais de distribuição e insira um gráfico de dispersão X-Y. Use a versão dispersa com linhas suaves para criar uma curva de sino no Excel.

o gráfico pode parecer um pouco desligado 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 valores mínimos e máximos no painel Opções do eixo para eliminar o espaço em branco de ambos os lados. Isso dará ao seu gráfico uma forma de Sino melhor. Definimos valores que estão um pouco fora do nosso conjunto de dados. Por exemplo, 66-100 para valores 66.30-99.86.

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

para obter mais informações sobre gráficos de dispersão, consulte Gráfico de dispersão no Excel

curva de sino no excel

Leave a Reply

Deixe uma resposta

O seu endereço de email não será publicado.