Como criar uma curva de sino no Excel

As curvas de distribuição gaussianas, comumente conhecidas como curvas de sino, são gráficos de distribuição normal que auxiliam na análise de variância em conjuntos de dados. Em uma curva de sino, o ponto mais alto (que também é a média) representa o evento com maior probabilidade de ocorrer, enquanto o restante dos eventos é distribuído de maneira simétrica em relação à média.

Desde a classificação relativa dos alunos e a criação de sistemas de avaliação competitivos até a previsão de retornos, as curvas de sino têm uma ampla gama de aplicações. Aqui, vamos orientá-lo no processo de criação de uma curva de sino no Excel.

O básico da criação de uma curva de sino no Excel

Para entender como criar uma curva de sino no Excel, vamos supor que você seja um professor de história que precise avaliar os alunos com base em seu desempenho em um teste. Suponha que a turma tenha 16 alunos com as seguintes notas:

Agora, antes de criar a curva de sino de qualquer conjunto de dados, você precisa calcular:

• Significa – o valor médio do conjunto de dados (fornece o centro da curva)
• Desvio padrão – mede o quanto os pontos de dados estão dispersos em relação à média (dá a propagação da curva)

Encontrando a média

Você pode usar funções internas no Excel para calcular estatísticas básicas, como média, desvio padrão, porcentagem, etc. Para encontrar a média, use a função MÉDIA no Excel:

Modelo =MÉDIA(B2:B16) para encontrar a média da folha de notas dada acima. Você notará que dá um valor de 53,93.

Se você deseja um valor de número inteiro, o que geralmente deseja, pode usar a função ROUND. Para isso, digite:

=ROUND(AVERAGE(B2:B16),0)

Agora a média se torna 54.

Encontrando o Desvio Padrão

O Excel mostra duas fórmulas para o desvio padrão:

• STDEV.P é usado quando você sabe que seus dados estão completos, ou seja, é uma população.
• STDEV.S é usado quando seus dados estão incompletos, ou seja, você tem uma amostra de uma população.

Em estatística, as pessoas geralmente escolhem amostras de uma população, então STEV.S é normalmente usado. Como você tem os dados completos, ou seja, as notas de todos os alunos da turma, usaremos STDEV.P. Para obter o desvio padrão da folha de marca fornecida, digite:

=STDEV.P(B2:B16)

Você receberá 27.755. Se você quiser seu valor em números inteiros, basta arredondar digitando:

=ROUND(STDEV.P(B2:B16),0)

Você terá 28.

Classificando os dados em ordem crescente

Para você criar a forma de sino para seu gráfico de distribuição normal, os dados precisam estar em ordem crescente. Se seus dados não estiverem em ordem crescente (como em nosso exemplo), basta selecionar todos os valores (marcas de teste) em seu conjunto de dados, vá para o Classificar e filtrar no painel superior e selecione Ordernar ascendente.

Como fazer uma curva de sino no Excel

Agora que você tem o desvio padrão e a média (média), é hora de calcular a distribuição normal dos valores fornecidos. Assim que tivermos isso, teremos tudo o que precisamos para criar nossa curva de sino usando a opção de gráfico de dispersão do Excel. Vamos primeiro encontrar a distribuição normal de todos os valores dentro do conjunto de dados:

1. Encontrando a Distribuição Normal

É hora de calcular a distribuição normal dos pontos de dados. No Excel, você pode encontrar a distribuição normal usando o NORM.DIST função, que requer as seguintes variáveis:

• X é o ponto de dados para o qual você deseja calcular a distribuição normal.
• Significa é a média dos dados fornecidos (já calculados).
• Desvio padrão é o desvio padrão dos dados fornecidos (já calculados).
• Cumulativo é o valor lógico usado para especificar o tipo de distribuição necessário.

Para calcular a distribuição normal das pontuações de nossos testes:

1. Modelo =NORM.DIST( em uma nova célula (célula C2 no nosso caso.)
2. Insira os valores necessários com vírgulas entre os valores conforme mostrado na sintaxe.

3. Para x, digite B2que fornece o primeiro ponto de dados, ou seja, 12.
4. Para média, digite D2que fornece a média de nossos dados, e pressione F4. o F4 bloqueia o valor da média representada pelo cifrão ($D$2) para que, ao copiar esta fórmula para valores diferentes em nossos dados, o valor da média permaneça o mesmo.
5. Para desvio padrão, insira E2 para obter o desvio padrão e bloquear seu valor pressionando F4.
6. Por último, digite FALSO no lugar do valor lógico cumulativo e feche os parênteses. Digitando FALSOvocê obtém a função de densidade de probabilidade normal (PDF.) Observação: Você precisa digitar VERDADE para obter uma função de distribuição normal cumulativa (CDF).
7. Depois de obter a distribuição normal para o primeiro valor, basta arrastá-lo para obter a distribuição normal para os valores restantes do seu conjunto de dados.

2. Criando um gráfico de dispersão

Agora que você tem os pontos de dados e a distribuição normal, você tem tudo o que é necessário para criar sua curva de sino. Para isso, você deve fazer um gráfico de dispersão no Excel seguindo as etapas abaixo:

1. Selecione o conjunto de dados (notas dos alunos) e sua distribuição normal.
2. Vamos para Inserir > Diagrama de dispersão.
3. Selecionar Dispersão com linhas suaves.

4. E você tem sua curva de sino.

3. Personalizando a curva do sino

Já temos nossa curva de sino, mas podemos torná-la um pouco melhor personalizando-a. Primeiramente, vamos atualizar o título da nossa curva clicando duas vezes no título e inserindo o título desejado. (Você pode modificar o tipo de fonte, tamanho da fonte e posicionamento do título, entre outras coisas.)

Você pode remover o Série1 escrito na parte inferior de sua curva de sino, desligando o Lenda alternar. Para dar uma forma melhor à sua curva de sino, você pode definir os valores máximo e mínimo de seu gráfico de sino. Para isso, clique duas vezes no eixo x e você obterá a guia Opções do eixo, de onde poderá fazer as alterações necessárias.

Usando curvas de sino no Excel

Agora que você tem sua curva de sino, que mostra dados de distribuição perspicazes, você pode usá-la para avaliar seus alunos. Você pode aplicar o mesmo processo para criar uma curva de sino para qualquer dado.

Embora uma curva de sino forneça a probabilidade de um determinado ponto de dados em seu conjunto de dados, existem vários outros gráficos que você pode criar no Excel para encontrar outras informações interessantes sobre seu conjunto de dados.