Como criar uma curva de sino de distribuição normal no Excel

Este tutorial demonstrará como criar uma curva de sino de distribuição normal em todas as versões do Excel: 2007, 2010, 2013, 2016 e 2022.

Em estatística, uma curva em forma de sino (também conhecida como distribuição normal padrão ou curva gaussiana) é um gráfico simétrico que ilustra a tendência dos dados de se agruparem em torno de um valor central, ou média, em um determinado conjunto de dados.

O eixo y representa a probabilidade relativa de um determinado valor ocorrer no conjunto de dados, enquanto o eixo x plota os próprios valores no gráfico para criar uma curva em forma de sino, daí o nome.

O gráfico nos ajuda a analisar se um determinado valor faz parte da variação esperada ou é estatisticamente significativo e, portanto, deve ser examinado mais de perto.

Uma vez que o Excel não tem nenhuma solução incorporada a oferecer, você terá que plotar você mesmo. É por isso que desenvolvemos o complemento Chart Creator, uma ferramenta que permite criar gráficos Excel avançados com apenas alguns cliques.

Neste tutorial passo a passo, você aprenderá como criar uma curva de sino de distribuição normal no Excel desde o início:

Para traçar uma curva gaussiana, você precisa saber duas coisas:

• O significativo (também conhecido como medida padrão). Isso determina o centro da curva - que, por sua vez, caracteriza a posição da curva.
• O desvio padrão (SD) das medições. Isso define a propagação de seus dados na distribuição normal - ou em inglês simples, a largura da curva. Por exemplo, na curva do sino mostrada acima, um desvio padrão da média representa a faixa entre as notas do exame de 53 e 85.

Quanto menor o SD, mais alta a curva e menos seus dados serão espalhados e vice-versa.

Vale a pena mencionar a regra 68-95-99,7 que pode ser aplicada a qualquer curva de distribuição normal, o que significa que aproximadamente 68% dos seus dados serão colocados dentro de um DP de distância da média, 95% dentro de dois DP e 99,7% dentro três SD.

Agora que você sabe o essencial, vamos passar da teoria à prática.

Começando

Para fins de ilustração, vamos supor que você tenha as notas dos testes de 200 alunos e queira classificá-los "em uma curva", o que significa que as notas dos alunos serão baseadas em seu desempenho relativo para o resto da classe:

Etapa # 1: Encontre a média.

Normalmente, você recebe os valores médios e SD desde o início, mas se esse não for o caso, você pode calcular facilmente esses valores em apenas algumas etapas simples. Vamos lidar com o meio primeiro.

Como a média indica o valor médio de uma amostra ou população de dados, você pode encontrar sua medição padrão usando a função AVERAGE.

Digite a seguinte fórmula em qualquer célula vazia (F1 neste exemplo) ao lado de seus dados reais (colunas A e B) para calcular a média das pontuações do exame no conjunto de dados:

1	= MÉDIA (B2: B201)

Uma observação rápida: na maioria das vezes, pode ser necessário arredondar o resultado da fórmula. Para fazer isso, basta envolvê-lo na função ROUND da seguinte maneira:

1	= RODADA (MÉDIA (B2: B201), 0)

Etapa 2: Encontre o desvio padrão.

Um já foi, só falta um. Felizmente, o Excel tem uma função especial para fazer todo o trabalho sujo de encontrar o desvio padrão para você:

1	= STDEV.P (B2: B201)

Novamente, a fórmula escolhe todos os valores do intervalo de células especificado (B2: B201) e calcula seu desvio padrão - apenas não se esqueça de arredondar a saída também.

1	= ROUND (STDEV.P (B2: B201), 0)

Etapa # 3: Configure os valores do eixo x para a curva.

Basicamente, o gráfico constitui um grande número de intervalos (pense neles como etapas) unidos por uma linha para criar uma curva suave.

Em nosso caso, os valores do eixo x serão usados ​​para ilustrar uma pontuação específica no exame, enquanto os valores do eixo y nos dirão a probabilidade de um aluno obter essa pontuação no exame.

Tecnicamente, você pode incluir quantos intervalos desejar - você pode apagar facilmente os dados redundantes posteriormente, modificando a escala do eixo horizontal. Apenas certifique-se de escolher um intervalo que incorporará os três desvios-padrão.

Vamos começar a contagem em um (já que não há como um aluno obter uma nota negativa no exame) e ir até 150 - não importa realmente se é 150 ou 1500 - para configurar outra mesa auxiliar.

1. Escolha qualquer célula vazia abaixo dos dados do gráfico (como E4) e tipo “1,” o valor que define o primeiro intervalo.
2. Navegue até o Casa aba.
3. No Editando grupo, escolha “Preencher.”
4. Debaixo "Série em,”Selecione“Coluna.”
5. Para "Valor do passo," modelo “1.” Este valor determina os incrementos que serão somados automaticamente até que o Excel alcance o último intervalo.
6. Para "Valor de parada," modelo "150,” o valor que representa o último intervalo e clique em “OK.”

Milagrosamente, 149 células na coluna E (E5: E153) foram preenchidos com os valores de 2 a 150.

NOTA: Não esconda as células de dados originais, conforme mostrado nas imagens. Caso contrário, a técnica não funcionará.

Etapa 4: Calcule os valores de distribuição normal para cada valor do eixo x.

Agora, encontre os valores de distribuição normal - a probabilidade de um aluno obter uma determinada pontuação no exame representada por um valor específico do eixo x - para cada um dos intervalos. Felizmente para você, o Excel tem o burro de carga para fazer todos esses cálculos para você: a função DIST.NORM.

Digite a seguinte fórmula na célula à direita (F4) do seu primeiro intervalo (E4):

1	= DIST.NORM (E4, $ F $ 1, $ F $ 2, FALSO)

Aqui está a versão decodificada para ajudá-lo a se ajustar de acordo:

1	= DIST.NORM ([o primeiro intervalo], [a média (referência absoluta)], [o desvio padrão (referência absoluta), FALSO)

Você bloqueia os valores médios e SD para que possa executar sem esforço a fórmula para os intervalos restantes (E5: E153).

Agora, clique duas vezes na alça de preenchimento para copiar a fórmula para o resto das células (F5: F153).

Etapa 5: Crie um gráfico de dispersão com linhas suaves.

Finalmente, chegou a hora de construir a curva do sino:

1. Selecione qualquer valor na tabela auxiliar contendo os valores dos eixos xey (E4: F153).
2. Vou ao Inserir aba.
3. Clique no "Inserir dispersão (X, Y) ou gráfico de bolhas" botão.
4. Escolher "Espalhe com linhas suaves. ”

Etapa # 6: Configure a tabela de rótulos.

Tecnicamente, você tem sua curva de sino. Mas seria difícil de ler, pois não possui dados que o descrevam.

Vamos tornar a distribuição normal mais informativa adicionando os rótulos que ilustram todos os valores de desvio padrão abaixo e acima da média (você também pode usá-los para mostrar os escores z).

Para isso, configure ainda outra tabela auxiliar da seguinte maneira:

Primeiro, copie o valor médio (F1) ao lado da célula correspondente na coluna X-Value (I5).

Em seguida, calcule os valores de desvio padrão abaixo da média inserindo esta fórmula simples em célula I4:

1	= I5- $ F $ 2

Simplificando, a fórmula subtrai a soma dos valores de desvio padrão anteriores da média. Agora, arraste a alça de preenchimento para cima para copiar a fórmula nas duas células restantes (I2: I3).

Repita o mesmo processo para os desvios padrão acima da média usando a fórmula do espelho:

1	= I5 + $ F $ 2

Da mesma forma, execute a fórmula para as outras duas células (I7: I8).

Finalmente, preencha os valores do rótulo do eixo y (J2: J8) com zeros conforme deseja que os marcadores de dados sejam colocados no eixo horizontal.

Etapa 7: Insira os dados do rótulo no gráfico.

Agora, adicione todos os dados que você preparou. Clique com o botão direito no gráfico e escolha “Selecione Dados.”

Na caixa de diálogo que aparece, selecione “Adicionar.”

Destaque os respectivos intervalos de células da tabela auxiliarI2: I8 para "Valores da série X" e J2: J8 para "Valores da série Y ”-e clique em “OK.”

Etapa # 8: Altere o tipo de gráfico da série do rótulo.

Nossa próxima etapa é alterar o tipo de gráfico da série recém-adicionada para fazer os marcadores de dados aparecerem como pontos. Para fazer isso, clique com o botão direito no gráfico e selecione “Alterar o tipo de gráfico.”

Em seguida, projete um gráfico de combinação:

1. Navegue até o Combo aba.
2. Para Série “Series2,” mudança "Tipo de Gráfico" para "Dispersão.”
   • Nota: Certifique-se de que “Series1”Permanece como“Espalhe com linhas suaves. ” Às vezes, o Excel muda quando você faz um Combo Certifique-se também de que “Series1”Não é empurrado para o eixo secundário - a caixa de seleção ao lado do tipo de gráfico não deve ser marcada.
3. Clique em “OK.”

Etapa # 9: Modifique a escala do eixo horizontal.

Centralize o gráfico na curva do sino ajustando a escala do eixo horizontal. Clique com o botão direito no eixo horizontal e selecione “Formato de eixo”No menu.

Assim que o painel de tarefas aparecer, faça o seguinte:

• Vou ao Opções de eixo aba.
• Colocou o Limites Mínimos valor para “15.”
• Colocou o Limites máximos valor para “125.”

Você pode ajustar a faixa de escala do eixo da maneira que achar conveniente, mas como você conhece as faixas de desvio padrão, defina os valores de Limites um pouco longe de cada um de seus terceiros desvios padrão para mostrar a “cauda” da curva.

Etapa # 10: insira e posicione os rótulos de dados personalizados.

Conforme você aprimora seu gráfico, certifique-se de adicionar os rótulos de dados personalizados. Primeiro, clique com o botão direito em qualquer ponto que represente Série “Series2” e selecione “Adicionar rótulos de dados.”

Em seguida, substitua os rótulos padrão pelos que você configurou anteriormente e coloque-os acima dos marcadores de dados.

1. Clique com o botão direito em qualquer Série “Series2” etiqueta de dados.
2. Selecione “Formatar rótulos de dados.”
3. No painel de tarefas, mude para o Opções de etiqueta aba.
4. Verifica a "Valor X" caixa.
5. Desmarque o “Valor Y" caixa.
6. Debaixo "Posição da etiqueta," escolher "Acima de.”

Além disso, agora você pode remover as linhas de grade (clique com o botão direito nelas> Excluir).

Etapa 11: Recolorir os marcadores de dados (opcional).

Finalmente, recolorir os pontos para ajudá-los a se encaixar no estilo do gráfico.

1. Clique com o botão direito em qualquer Série “Series2” etiqueta de dados.
2. Clique no "Preencher" botão.
3. Escolha sua cor na paleta que aparece.

Além disso, remova as bordas em torno dos pontos:

1. Clique com o botão direito no mesmo marcador de dados novamente e selecione “Contorno.”
2. Escolher "Sem contorno.”

Etapa # 12: adicione linhas verticais (opcional).

Como um ajuste final, você pode adicionar linhas verticais ao gráfico para ajudar a enfatizar os valores SD.

• Selecione o gráfico do gráfico (dessa forma, as linhas serão inseridas diretamente no gráfico).
• Vou ao Inserir aba.
• Clique no "Formas" botão.
• Escolher "Linha.”

Segure o "MUDANÇA" enquanto arrasta o mouse para desenhar linhas perfeitamente verticais de cada ponto até onde cada linha encontra a curva do sino.

Altere o título do gráfico e sua curva de sino aprimorada estará pronta, mostrando seus valiosos dados de distribuição.

E é assim que você faz. Agora você pode escolher qualquer conjunto de dados e criar uma curva de sino de distribuição normal seguindo estas etapas simples!