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 2019.

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 de agrupamento dos dados 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 um formato de sino curva, 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.

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

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

Para plotar uma curva gaussiana, você precisa saber duas coisas:

• A média (também conhecida como medida padrão). Isso determina o centro da curva – que, por sua vez, caracteriza a posição da curva.
• O desvio padrão (DP) 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 o intervalo entre as notas dos exames de 53 e 85.

Quanto mais baixo o DP, mais alta a curva e menor 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 SD de distância da média, 95% dentro de dois SD e 99,7% dentro de três SD.

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

Primeiros passos

Para fins de ilustração, vamos supor que você tenha a pontuação de 200 alunos no teste e queira avaliá-los “em uma curva , ”O que significa que as notas dos alunos serão baseadas em seu desempenho relativo ao resto da classe:

Etapa # 1: Encontre a média.

Normalmente, você recebe a média e valores 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 abordar a média primeiro.

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

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:

Uma nota 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:

Etapa 2: Encontre o desvio padrão.

Um para baixo, um para ir. Felizmente, o Excel tem uma função especial para fazer todo o trabalho sujo de encontrar o desvio padrão para você:

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

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 de exame particular, enquanto os valores do eixo y dirão nos a probabilidade de um aluno obter essa pontuação no exame.

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

Vamos começar uma 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 tabela auxiliar.

1. Escolha qualquer célula vazia abaixo dos dados do gráfico (como E4) e digite “1”, o valor que define o primeiro intervalo.
2. Navegue até a guia Página inicial.
3. No grupo Edição, escolha “Preencher”.
4. Em “Série em, ”Selecione” Coluna “.
5. Para” Valor da etapa “, digite” 1 “. Este valor determina os incrementos que serão somados automaticamente até que o Excel alcance o último intervalo.
6. Para “Valor de parada”, digite “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 que vão de 2 a 150.

NOTA: Não esconda as células de dados originais como 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 de 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) de seu primeiro intervalo (E4):

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

Você bloqueia os valores médios e SD para que possa executar a fórmula sem esforço 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. Vá para a guia Inserir.
3. Clique no botão “Inserir Dispersão (X, Y) ou Gráfico de Bolhas”.
4. Escolha “Dispersão com Linhas Suaves”.

Etapa 6: configure a tabela de rótulos.

Tecnicamente, você tem sua curva em forma de sino. Mas seria difícil de ler, pois não tem dados que a 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 as pontuações z).

Para isso, configure outra tabela auxiliar da seguinte maneira:

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

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

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 de espelho:

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 como você deseja que marcadores de dados 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 “Selecionar dados”.

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

Destaque os respectivos intervalos de células da tabela auxiliar – I2: 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 de a 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 do gráfico e selecione “Alterar Tipo de gráfico. ”

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

1. Navegue até o Guia Combo.
2. Para a série “Série 2”, altere “Tipo de gráfico” para “Dispersão”.
   • Observação: certifique-se de que “Série1” permaneça como “Dispersão com linhas suaves”. Às vezes, o Excel muda quando você faz uma combinação. Certifique-se também de que “Série1” não seja 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 do mouse no eixo horizontal e selecione “Format Axis” no menu.

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

• Vá para a guia Opções de eixo.
• Defina o valor de Limites mínimos para “15”.
• Defina o valor de Limites máximos para “125.”

Você pode ajustar o intervalo de escala do eixo da maneira que achar melhor, mas como você conhece os intervalos 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.

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

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

1. Clique com o botão direito do mouse em qualquer rótulo de dados da série “Series2”.
2. Selecione “Formato Rótulos de dados. ”
3. No painel de tarefas, mude para a guia Opções de rótulo.
4. Marque a caixa” Valor X “.
5. Desmarque” Valor Y ”.
6. Em” Posição do rótulo ”, escolha” Acima “.

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 ajustar ao estilo do seu gráfico.

1. Clique com o botão direito do mouse em qualquer rótulo de dados da série “Series2”.
2. Clique o botão “Preencher”.
3. Escolha sua cor na paleta que aparece.

Além disso, r remova as bordas ao redor dos pontos:

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

Etapa 12: adicione linhas verticais (opcional).

Como um ajuste final, você pode adicione 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).
• Vá para a guia Inserir.
• Clique no botão “Formas”.
• Escolha “Linha”.

Mantenha pressionada a tecla “SHIFT” enquanto arrastando 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 em forma de sino aprimorada está 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!