Antes de mais nada, gostaria de pedir desculpas aos estatísticos se utilizar definições inadequadas ao assunto, afinal, embora o tema me interesse bastante, não tenho formação na área.
A curva de distribuição normal, de Gauss ou em sino é a representação da distribuição de frequência dos dados de um fenômeno. É aplicável aos mais diversos campos do conhecimento (demografia, epidemiologia, controle de qualidade, etc). A interpretação da sua forma nos indica que a maior parte dos dados observáveis concentra-se em torno da média da população/amostra e à medida que nos afastamos deste valor, os dados se tornam cada vez menos frequentes. Como a curva é simétrica, temos que a queda nesta frequência se comporta de forma similar tanto para valores maiores quanto menores que a média.
O nosso objetivo será criar um gráfico como este aqui:
Histograma x Curva de Gauss
Este tipo de gráfico, permite-nos comparar visualmente a distribuição dos dados que estamos estudando, frente àquela que seria obtida caso a distribuição fosse exatamente igual à prevista matematicamente pela Curva de Gauss.
O Excel não possui nenhuma ferramenta que permita gerar a curva diretamente, mas é possível construí-la com o auxílio de funções. No nosso exemplo utilizaremos a função DIST.NORMP.N (Para versões anteriores à 2010, a função a ser utilizada é DISTNORMP).
Esta função permite calcular qual a densidade de probabilidade (frequência relativa) dos dados para um determinado valor da variável padronizada (Z). Para aqueles não familiarizados com esta terminologia, a variável z é calculada obtendo-se a diferença entre um dado valor e a média do conjunto de dados e, em seguida, dividindo-se o valor obtido pelo desvio padrão dos dados. (Z = (X-Média)/(Desvio Padrão)).
Vamos ao exemplo:
Iremos trabalhar com o conjunto de dados a seguir no qual já destacamos os valores mínimo e máximo:
Conjunto de dados
Para que seja possível gerar o histograma de frequências, é necessário agrupar os dados em conjuntos chamados Classes.
Vamos agrupar os dados em grupos de 3, iniciando-se no valor mínimo até o valor máximo.
Desta forma, ficaremos com as seguintes classes:
Classes de dados
Os rótulos L Inf e L Sup, significam, respectivamente, Limite Inferior e Limite Superior da Classe.
Agora, temos que determinar quantos dados há em cada classe.
Em outras palavras, para cada uma das classes temos que contar quantos valores são maiores ou iguais ao L Inf e menores ou iguais ao L Sup.
Utilizaremos a função CONT.SES, inserindo em N2 a fórmula: =CONT.SES($A$2:$J$21;”>=”&L2;$A$2:$J$21;”<=”&M2), e em seguida copiando-a para o intervalo N3:N9.
Fórmula para calcular frequências
Essas já são informações suficientes para construir o histograma, mas vamos transformar essas frequências, em frequências relativas.
Para isso basta dividir a frequência pelo quantidade de dados analisados. Observe como isso é possível editando as fórmulas do intervalo N2:N9
Calculando frequências relativas
Agora iremos calcular os valores calculados para a Curva de Gauss.
Para isso precisaremos, da média e do desvio padrão do conjunto de dados e do ponto médio de cada classe para podermos calcular o valor da variável Z para cada classe.
Nesta etapa, iremos inserir a fórmula para cálculo da média em L12 e copiá-la para o intervalo L13:L20.
Calculando o ponto médio de cada classe
Este cálculo chama-se padronização e pode ser calculado pela função do Excel chamada PADRONIZAR.
Este cálculo será realizado no intervalo M13:M20:
Padronizando a variável Xi
OBS:
1. O cálculo da média e do desvio padrão foram realizados diretamente em cada fórmula do intervalo. Nada impede, no entanto, de inserirmos o cálculo numa célula auxiliar.
2. A fórmula utilizada para calcular o desvio padrão foi DESVPAD.A. Para aqueles que utilizem versões anteriores à 2010, a função a ser utilizada é DESVPAD.
E para concluir os nossos cálculos, iremos determinar o valor da densidade de probabilidade prevista pela Curva de Gauss, inserindo as fórmulas no intervalo N13:N20, utilizando a função DIST.NORMP.N.
Calculando a densidade de probabilidade
Observem que foi utilizado o parâmetro FALSO na fórmula. Desta forma é calculada a densidade de probabilidade para o valor informado. Se for utilizado o parâmetro VERDADEIRO, será calculada a densidade acumulada até o ponto.
Agora que todos os cálculos foram realizados, vamos organizar os resultados para gerar o gráfico, simplesmente movendo-os para outro ponto da planilha.
Rearranjando os resultados
Agora na coluna P iremos inserir os valores que serão usados como rótulos do gráfico. Uma fórmula simples para colocar numa única célula os limites inferior e superior da cada classe. Vamos alterar também as denominações em Q1 e R1, respectivamente para Calc e Real.
Acertando os últimos detalhes para construir o gráfico.
Selecionando o intervalo P1:P9, vamos inserir um gráfico de colunas:
Inserindo gráfico de colunas
Clicando com o botão direito na coluna Calc, vamos selecionar a opção Alterar tipo de gráfico.
Alterando o tipo de gráfico
E vamos modificá-lo para um gráfico de linhas:
Modificando para gráfico de linhas
Clicando com o botão direito na linha, vamos formatá-la:
Formatando linha do gráfico
Na opção Estilo de Linha, vamos marcar Linha suavizada.
Criando uma linha suavizada
Agora iremos alterar um dos parâmetros das colunas, clicando com o botão direito e selecionando Formatar série de dados.
Formatando colunas
Em Opções de série, vamos definir a Largura do espaçamento, para 0%.
Configurando o espaçamento entre as colunas
E, finalmente, obtemos o resultado desejado:
Gráfico concluído
A partir deste ponto, podemos modificar mais parâmetros, formatando o gráfico de acordo com o estilo desejado.
Deixo disponível o arquivo com os dados, cálculos e o gráfico para download:Histograma x Curva de Gauss
In Excel We Trust
Usuários do Excel 
this is really nice…
Your article is excellent. Your writing style and solid information is interesting and smart. I agree with a lot of your views. Thank you for your dedication to quality content.
Muito bom! Obrigado!
bom
Olá! Gostaria de saber qual a função equivalente a “DIST.NORMP.N” no Excel 2007.
Tentei as funções “DIST.NORM” e “DIST.NORMP” mas obtive valores diferentes do seu exemplo.
Muito obrigado.
Ok, descobri a resposta. Para o equivalente ao cálculo de densidade de probabilidade pela fórmula “DIST.NORMP.N” no Excel 2007 basta utilizar a fórmula “DIST.NORM(X;MÉDIA;DESVIO PADRÃO;CUMULATIVO)”, considerando X igual a variável aleatória Z (X=Z), a média igual a 0 (μ = 0), o desvio padrão igual a 1 (σ = 1) e o valor lógico igual a “FALSO” (CUMULATIVO = FALSO). Por favor, me confirmem se isto está correto ou se existe um modo mais simples.
Novamente, muito obrigado.
Passei pelo mesmo problema e sua solução foi correta. Deu certo comigo. Obrigado! Abração.
Obrigada pelo post!
Foi super útil, obrigada.
O arquivo não está mais disponível? Não consigo baixar.
Olá, não entendi como inseriu Linf e Lsup>
Olá, meu caro.
Sua dúvida é como chegamos aos valores de LInf e LSup?
Ola meu caro! muito boa publicação.
Posso estar enganado, mas o mais correto seria você usar:
“DIST.NORM(X;MÉDIA;DESVIO PADRÃO;CUMULATIVO)”, considerando X igual ao ponto médio da classe (4, 7, 10 etc..) , a média igual a 14,6; o desvio padrão igual a 3,9924 e CUMULATIVO = FALSO.
Após isso você multiplica o valor encontrado pelo “tamanho da classe”, que nesse caso é 3. Perceba que assim, a soma das “frequências calculadas” tenderá a 1, assim como as somas das “frequências reais” é 1. A integral da curva tem que ser 1.
Assim terá um ajuste correto, uma curva mais bem ajustada e com integral 1. Veja que no seu ajuste, a curva ficou em sua maior parte consideravelmente acima das colunas dos histograma. Isto por que você usou a DIST. NORM P que usa um desvio padrão 1, que não é condizente com a distribuição.
Um abraço.
Obrigado pelo comentário.
Observe que para os cálculos utilizando DIST.NORM.P foram utilizados os valores da variável modificada Zi (Zi = (Xi-Média)/DesvPadrão).
A distribuição de dados de Zi tem média zero e desvio padrão 1.
Desta forma o quociente dos valores calculados por DIST.NORM e DIST.NORM.P é constante e igual ao DesvPadrão.
Ou seja se optarmos por calcular a distribuição utilizando os valores de Zi, devemos utilizar DIST.NORM.P ao passo que teremos que utilizar DIST.NORM se formos calcular as frequências com base nos valores de Xi.
Verifique também que o tamanho da classe é 2 (LimSupi – LimInfi).
[ ]s
Boa tarde, pessoal.
Material bastante bom e interesante.
Mas não consigo localizar a função padronizar, isto para o cálculo de Zi. Tentei inserir padrão e também não aparece.
Estou usando o Windows, com a versão 10.
Por favor, me auxiliem.
Olá, meu caro. Qual a versão do Excel com que trabalha e qual a linguagem de instalação?
Estou usando o Microsoft Office 2010.
Português do Brasil ou de Portugal? Ou está em outro idioma?
Português (Portugal)
Esquema de teclado: Activado
Verificação Linguística: Instalado
Por incrível que pareça há diferenças entre as traduções das funções para as versões de português do Brasil e de Portugal. Segundo a pesquisa que fiz, a função original STANDARDIZE foi traduzida para o Brasil como PADRONIZAR e para Portugal como NORMALIZAR.
Ok, muito obrigado pelo auxílio.
Já consegui finalizar, inclusive o gráfico.
Não tem de quê.
Bom dia, excelente trabalho.
Preciso mostrar no gráfico a representação e o valor da média da coluna “Calc” através de um linha vertical que saia do eixo x e vá até o pico da curva.
Um grande abraço e sucesso.
A forma mais fácil é adicionar uma terceira série de dados como colunas, zerando os valores de Z diferentes de zero e em Z=0 atribuir o valor da distribuição. Depois pode ajustar a largura da coluna de forma que fique apenas como um traço. [ ]s
Muito bom. Parabéns
Obrigado!
Boa noite, tentei fazer um gráfico com os dados q tenho, mas percebo q ela não vai formar um gráfico de distribuição normal…os dados parecem estar “acumulados” em uma parte do gráfico…unicaudal…não tenho certeza..mas essa forma de fazer tbm se aplica para este tipo de gráfico?
Depende do grau de distorção dos seus dados em relação à distribuição de Gauss