Como Fazer um Histograma no Excel e Todos os Detalhes

Você precisa fazer um histograma no Excel para aquele seu experimento de física 1 da faculdade? você lembrou dele enquanto analisava dados da empresa e achou que ele ficaria legal em um relatório? rsrs,

Não importa o motivo. Neste artigo você irá aprender tudo que precisa sobre histograma, como:

• O que é um histograma e quais informações tirar dele
• Como organizar seus dados para construir o histograma no Excel
• Como fazer um histograma no Excel 2016
• Como fazer um histograma no Excel 2013, 2010 e anteriores
• Como adicionar uma curva de distribuição normal ao seu histograma
• Quando utilizar um histograma

E com exemplos e passo-a-passo, vamos lá!

O que é um Histograma no Excel?

O histograma é um gráfico que nos permite identificar por inspeção a distribuição da frequência de uma série de dados. Por isso, através dele podemos identificar se a distribuição dos dados é normal, se tem alguma assimetria ou descontinuidade, como mais de uma tendência de média, e etc.

Por exemplo, imaginemos que estamos realizando um ensaio de partida em 1.048.576 equipamentos. Quando um equipamento falha vou lá no Excel e anoto quantas partidas o equipamento aguentou antes de falhar e vou para o próximo.

Pronto, já tenho minha série de dados. Apresentando como um histograma no Excel ela fica assim:

Podemos observar por inspeção que, em média, o equipamento que testamos costuma falhar por volta de 45-55 partidas. A distribuição da série de dados é parece ser normal com um desvio padrão lá na casa de 10 partidas.

Então podemos concluir que a chance de termos uma falha em equipamento entre 0-30 partidas é menor que 0,003%!! Assim como é a chance de um equipamento resistir 70 ou mais partidas sem falha.

Talvez este gráfico de distribuição refresque sua memória.

Se você não entendeu muito sobre o que expliquei no parágrafo anterior não se preocupe. Este conhecimento é lá de modelos probabilísticos. Eu pretendo, no futuro, escrever sobre isso aqui no blog, enquanto não escrevi cheque aqui neste link que tem uma explicação bem legal do assunto.

Como Escolher Quantas Classes Devem Ter um Histograma no Excel?

Fazer o histograma no Excel não é nenhum problema. A dificuldade é que a ferramenta vai te perguntar: qual o intervalo de cada classe (ou bloco, como o Excel chama) para sua série de dados? Aí você fica travado por não saber quais são os critérios para definir esse parâmetro.

O histograma da figura acima foi feito com os mesmos dados do da seção anterior. Não ficou muito bom, não é mesmo?

O problema é que não escolhemos bem quantas classes (ou blocos) o histograma deve ter.

O segredo para determinar o número de classes é usando a regra de Sturges.

A regra de Sturges diz que para uma série de dados de tamanho n, a quantidade de classes do histograma deve ser:

Traduzindo para o nosso bom Excel ficamos assim:

Para você copiar: =LOG10(CONT.NÚM(SEUS_DADOS))*3,3+1

A função LOG10 é a função que calcula o logarítmo base 10 do valor que passamos.

A função CONT.NÚM conta o número de entradas que nossa série de dados tem.

Logo a quantidade de classes ou blocos deve ser 21, arredondando.

Como Calcular o Tamanho do Intervalo entre as Barras do Histograma no Excel?

Já encontramos na seção anterior que devemos ter 21 classes.

Agora para encontrar qual o tamanho do intervalo entre as barras do histograma temos de primeiro encontrar qual o valor mínimo e máximo da nossa série de dados, e descobrir qual a distância entre esses valores. Essa distância divido pelo tamanho do intervalo nos dará o tamanho de cada classe.

Caso você esteja tratando 1.048.576 dados, como eu nesse exemplo, vamos usar o Excel para calcular tudo e facilitar nossas vidas:

Encontramos que o tamanho de classe é 5!

A função MÁXIMO encontra o valor máximo que nossa série de dados tem.

A função MÍNIMO encontra o valor mínimo da nossa série de dados.

A função ARRED aceita dois parâmetros; o primeiro é o valor que iremos arredondar e o segundo é o número de casas decimais que gostaríamos.

Para você copiar: =ARRED((MÁXIMO(SEUS_DADOS)-MÍNIMO(SEUS_DADOS))/QUANTIDADE_DE_CLASSES;0)

Então nossas classes (ou blocos) serão [0-5], [5-10] … [100-105]!!

A base que expliquei até aqui é necessária para elaborar o histograma no Excel, pois estas são basicamente as informações que o Excel pede na hora de elaborar o gráfico.

Nas próximas seções explico como elaborar o histograma nas diferentes versões do Excel.

Como Fazer um Histograma no Excel 2016

O Excel na versão 2016 adicionou no menu de ferramentas, lá na seção de gráficos, a opção padrão para criar histograma. Nas versões anteriores, como vou explicar na próxima seção, você precisa fazer alguns malabarismos para habilitar a opção de criar um histograma.

Suponha que estamos usando os dados das seções anteriores (aquele sobre 1.048.576 testes de partida).

Para fazer o histograma no Excel 2016 siga os seguintes passos:

1. Selecione seus dados, no meu caso seria toda a coluna A
   
2. Na barra de ferramentas do Excel selecione “Inserir”, então selecione um dos separadores que terá o nome “Gráfico”. Lá procure por “Histograma”, então selecione a primeira opção
   

Tchantchantchantchannnn!! Pronto O.O

Nem precisamos nos preocupar com separação de classes e intervalos delas, né?

Bem, não é bem assim. Recomendo que você formate seu gráfico de acordo com as regras que expliquei na seção anterior. O motivo é meio óbvio. É para facilitar a visualização das informações.

Como Formatar o Histograma no Excel 2016

A formatação que considero relevante explicar está relacionada aos eixos do histograma. Clique no eixo do gráfico e selecione “Formatar eixo horizontal…”

As opções que destaquei fazem o seguinte:

1. Por Categoria: Essa opção é utilizada quando sua série de dados tem rótulos no formato de texto. Por exemplo, digamos que estivéssemos testando, ao invés de um único equipamento de uma única marca, equipamentos de marcas diferentes. Então teríamos a contagem da frequência agrupada por categoria, que no nosso exemplo seriam as diferentes marcas. Isto descaracteriza um pouco a ideia de histograma, mas é uma facilidade.
2. Largura do compartimento: Nessa opção escolhemos o tamanho do bloco. Poderíamos colocar como calculamos, o valor 5
3. Número de compartimento: O Excel é tão bonzinho que só de preenchermos a largura do bloco, ou compartimento, ou classe (o Excel vai mudando o nome que ele dá ao longo das versões, rsrs) ele preenche o número de compartimentos automaticamente. A ordem inversa pode ser feita, então o Excel calcularia automaticamente a largura do compartimento.
4. Compartimento de estouro: Esse seria o bloco mais à direita do histograma para eventos que não se encaixaram em blocos anteriores
5. Compartimento de estouro  negativo: Esse seria o bloco mais à esquerda do histograma para eventos que não se encaixarão nos blocos a seguir

Para exemplificar, formatei da seguinte maneira para ficar como aprendemos:

Como Fazer um Histograma no Excel 2013, 2010, 2007 e anteriores

O método que vou explicar aqui funciona também com o Excel 2016, entretanto, como vimos na seção anterior, a partir da versão 2016 o Excel tem um atalho mais fácil para criar histogramas.

Nas versões anteriores ao Excel 2016 podemos criar na força bruta um histograma com gráfico de barras, já que descobrimos a quantidade de classes e seu tamanho no começo do artigo. Mas esta não é a maneira mais fácil.

O jeito mais simples é habilitando o suplemento “Ferramentas de Análise” no Excel.

Como Habilitar Ferramentas de Análise no Excel

Os passos para habilitar as “Ferramentas de Análise” no Excel são às seguintes:

1. Na barra de ferramentas do Excel clique na seta para baixo e no menu selecione “Mais Comandos…”
   
2. Uma nova janela irá aparecer. Nela selecione “Suplementos” à sua esquerda
3. Então clique com o botão esquerdo, na janela à direita, na linha “Ferramentas de Análise”
4. Clique no botão “Ir….”
   
5. Uma nova janela irá aparecer, cheque se “Ferramentas de Análise” está selecionada
6. Clique em OK
   

Pronto! Inserimos no Excel um novo atalho na aba “Dados”

Como Fazer Histograma no Excel com Ferramentas de Análise

Os passos para fazer o histograma no Excel com o suplemento ferramentas de análise são os seguintes:

1. Na barra de ferramentas do Excel navegue até a aba “Dados”, então selecione “Análise de Dados”. Uma janela deve aparecer como abaixo:
   
2. Selecione Histograma e clique OK
3. Selecione no “Intervalo de Entrada” sua série de dados
4. Selecione no “Intervalo do bloco” as suas classes, assim como calculamos no começo do artigo. Lembre que os valores tem de ser numéricos, mais a frente alteramos os rótulos para [0-5], [5-10] e etc
5. Marque o “Intervalo de saída” e escolha uma célula para que os resultados sejam apresentados na mesma planilha
6. Não esqueça de marcar “Resultado do gráfico” para que o histograma seja criado!
   

Se você fez tudo direitinho o histograma deve aparecer assim:

Como Formatar o Histograma no Excel Feito com Ferramentas de Análise

Caso você queira alterar a formatação para ficar com as barras mais grossas, faça o seguinte:

1. Clique sobre uma das barras com o botão direito e selecione “Formatar Série de Dados…”
2. No menu “Opções de Série” inseri em “Largura do Espaçamento” o valor zero
   
3. No mesmo menu selecione “Cor de Borda”. Marque as opções “Linha sólida” e selecione uma cor que contraste com seu histograma
   

Pronto, o seu histograma deve ter ficado como apresentei no início do artigo, com as barras juntinhas.

Para formatar os rótulos do histograma basta alterar a coluna “Bloco” dos dados gerados automaticamente. Sugiro o padrão: [0-5], [5-10] … [100-105] para ter uma ideia de qual o tamanho do intervalo.

Como Fazer um Histograma no Excel com Curva da Distribuição Normal

Essa parte aqui funciona em qualquer Excel.

É interessante sobrepormos ao histograma uma curva de distribuição normal para ser possível observamos se nossos dados compartilham de suas características.

A teoria nos diz que eventos probabilísticos naturais tendem a se aproximar de uma distribuição normal. Logo, se estamos analisando um evento aleatório, digamos, natural, é para ter uma distribuição similar. De cara, se seu histograma estiver muito assimétrico, já podemos identificar fenômenos mais específicos.

O exemplo que vou apresentar é utilizando os mesmos dados das seções anteriores.

Os passos para elaborar a curva de distribuição normal são os seguintes:

1. Calcular a média dos seus dados, portanto utilize a função =MÉDIA(SEUS_DADOS)
2. Calcular o desvio padrão, portanto utilize a função =DESV.PAD.A(SEUS_DADOS)
3. Calcular a distribuição normal para as 21 classes (ou blocos). Para tanto utilizaremos a fórmula =DIST.NORM.N(BLOCOS;MÉDIA;DESV_PADRÃO;FALSO)
   
4. Como estamos contando a frequência de ocorrências devemos multiplicar os valores da distribuição normal por =CONT.NÚM(SEUS_DADOS)/SOMA(DIST_NORMAL) para “normalizar” ao contrário ao contrário nossa distribuição normal, ou seja deixa-la na mesma escala dos dados de frequência. Os resultados devem ficar assim:
   
5. Na coluna G temos =DIST_NORMAL*NORMA, onde NORMA é o valor calculado no passo anterior
6. Faça seu histograma de acordo com sua versão do Excel, explicado nas seções anteriores
7. Clique sobre o histograma com o botão direito e selecione “Selecionar Dados…”
8. No menu selecione “Adicionar” e, em “Valores da série”, escolha a coluna da distribuição “normalizada” que criamos no passo anterior. Clique OK
   
9. Para alterar de barras vermelhas para uma linha, clique sobre as colunas vermelhas com o botão direito e selecione “Alterar Tipo de Gráfico de série”, então selecione o gráfico de linhas
   

Pronto! Ufa.

O resultado é uma boa aproximação para identificar uma distribuição gaussiana (normal) na sua série de dados.

Quando utilizar o Histograma?

Quando você não tem muita certeza do que fazer com uma série de dados muito grande em uma tabela, você pode usar o histograma para organizar e apresentar seus dados de forma amigável. O histograma permite identificar graficamente os principais indicadores estatísticos de um conjunto de dados de maneira visual, então fica fácil identificar valores como média, desvio padrão, forma de distribuição e etc. Por exemplo, se temos uma meta de frequência é fácil identificar se estamos acima ou abaixo dela.

De maneira geral o histograma é apropriado nas seguintes situações:

• Comunicar visualmente um resultado. O histograma é uma ferramenta muito boa para apresentar resultados por apresentar de forma clara e simples os eventos com maior frequência na sua série de dados
• Resumir em forma gráfica um grande conjunto de dados. Imagine o experimento que realizamos durante este artigo. São mais de um milhão de entradas de experimento. Portanto, não há gráfico que apresente com maiores detalhes as informações que precisávamos, que não seja um histograma.
• Ferramenta de auxílio na tomada de decisão. Se estamos falando de um relatório ou dashboard, em que a ideia é apresentar a frequência de um evento, o histograma não pode faltar!
• Identificar se seus indicadores de frequência estão dentro dos limites especificados. Voltando ainda ao exemplo do artigo. Caso nossa meta fosse que o equipamento suportasse pelo menos 30 partidas. Maravilha! pela característica da nossa distribuição, identificada pelo histograma, já sabemos que em mais de 99% dos equipamentos testados estamos dentro da meta.

Eu escrevi um artigo sobre dicas para fazer seu dashboard aqui e na categoria gráficos (aqui) tem bastante informação, caso esteja montando seu relatório e precise de ajuda. Não deixe de conferir!

Conclusão

O histograma é uma ferramenta excelente para análise estatística de dados. Eu acho bacana utilizar como recurso de pré-avaliação de dados, sabe? Só para termos uma ideia de com o que estamos lidando. O Excel nas versões anteriores a 2016 deixa o recurso um pouco obscuro para utilização, mas a partir da versão 2016 o uso é simples pelo atalho de gráficos.

O que eu aconselho é o mesmo que escrevo em todos os artigos sobre gráficos. Porque estou escolhendo este gráfico? Qual informação é a que eu gostaria de passar ao meu leitor?

Essas duas perguntas são as fundamentais para fazer um bom gráfico para seu relatório ou dashboard e com certeza vão te ajudar a decidir sobre a hora certa de usar um histograma no Excel.

Eu sei que não é comum ler um artigo deste, como tutorial, e repetir para aprender, mas recomendo fortemente que você tome coragem de repetir todo o passo-a-passo. Eu penso que praticar é a melhor maneira de aprender e, embora pareça simples, é preciso se acostumar a navegar entre as telas e fórmulas do Excel para que no futuro você possa se tornar um usuário avançado. Então, que tal praticar outros gráficos de artigos que já escrevi!

• Como Fazer um Gráfico de Gantt no Excel 2010, 2013 e 2016
• Como Fazer Gráfico de Linha no Excel
• Como Fazer Gráfico de Pareto no Excel
• Como Fazer Gráfico de Cascata no Excel
• Como Fazer Gráfico de Velocímetro no Excel
• Como Fazer Grafico de Pizza no Excel e Por que NUNCA Usa-lo
• Como Fazer um Gráfico de Área no Excel
• Como Fazer um Gráfico de Barras com Metas no Excel

Quando você estiver dominando os principais gráficos do Excel você estará pronto para elaborar lindas interfaces para visualização de dados para seus usuários. Portanto, o próximo passo será ler este artigo aqui:

• Como Fazer um Dashboard no Excel

Para estar sempre atualizado continue lendo sobre gráficos no Excel aqui ou mais dicas e truques no Excel aqui.