0161-Construindo uma Curva de Gauss no Excel

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

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

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

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

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

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

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

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

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

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.

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

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

Alterando o tipo de gráfico

E vamos modificá-lo para um gráfico de linhas:

Modificando para 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

Formatando linha do gráfico

Na opção Estilo de Linha, vamos marcar Linha suavizada.

Criando uma 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

Formatando colunas

Em Opções de série, vamos definir a Largura do espaçamento, para 0%.

Configurando o espaçamento entre as colunas

Configurando o espaçamento entre as colunas

E, finalmente, obtemos o resultado desejado:

Gráfico concluído

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

Anúncios

In Excel We Trust

Marcado com: , , , , , ,
Publicado em Nível: Básico
26 comentários em “0161-Construindo uma Curva de Gauss no Excel
  1. bbom disse:

    this is really nice…

  2. eyeuser disse:

    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.

  3. minarefilho@gmail.com disse:

    Muito bom! Obrigado!

  4. brunorsc disse:

    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.

  5. brunorsc disse:

    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.

  6. Ana disse:

    Foi super útil, obrigada.

  7. Douglas Lage disse:

    O arquivo não está mais disponível? Não consigo baixar.

  8. dorival disse:

    Olá, não entendi como inseriu Linf e Lsup>

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

  10. Helder disse:

    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.

  11. ALBERTO PIRES FARIAS disse:

    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.

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

  13. Celso disse:

    Muito bom. Parabéns

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s

%d blogueiros gostam disto: