Histograma no Excel com Curva da Distribuição Normal

Olá eu preparei este vídeo para explicar como construir histogramas combinando essas barras verticais e também a distribuição normal correspondente ao conjunto de dados então eu vou utilizar uma amra que eu gerei aleatoriamente e vou mostrar como que faz um gráfico como esse na verdade aqui já tá tudo resolvido então eu preparei uma outra aba aqui em que a gente não tem quase nada feito para mostrar para vocês como que a gente consegue chegar naquele gráfico Na verdade eu vou fazer em duas fases primeiro eu vou mostrar como que faz o histograma só com as barras

tá o histograma tradicional e depois vou mostrar como que faz o histograma com as barras e a distribuição normal que é um processo completamente diferente Bom vamos lá primeiro eu gostaria de mostrar aqui os dados então eu coloquei aqui 110 observações aleatórias tá então são 110 dados que eu utilizei aqui na verdade eu usei um gerador de números aleatórios mas poderia ser qualquer coisa por exemplo a gente tá num processo e a gente tá pesando os produtos que estão saindo daquele processo pra gente fazer o o histograma a gente precisa dessas informações primeiro a gente

precisa calcular o menor valor do da amostra para isso a gente vai usar a função mínimo aí a gente seleciona todos os dados para selecionar todos os dados como são muitos é só clicar aqui no primeiro e apertar contrl Shift para baixo aí seleciona tudo aí a gente fecha dá o enter E aí o cursor volta lá para cima mesma coisa pro maior valor então a gente vai usar a função máximo Então a gente seleciona o mesmo conjunto de dados aperta Enter e a gente tem aqui o maior valor e o menor valor para calcular

a média Então a gente vai usar a função média e o intervalo é o mesmo então só para não ter que fazer o o cont control shift para baixo de novo Vamos só copiar esse intervalo e a gente usa aqui a média cola o intervalo B3 a B12 e calcula a média desse conjunto de dados tamanho Eu já falei para vocês né que são 110 valores mas eu gostaria de automatizar essa planilha Então eu queria contar quantos valores tem aqui para isso é só a gente colocar igual a cont pon num e o intervalo ou

seja ele vai contar quantos números tem nesse intervalo essa fórmula ela é interessante porque se por algum acaso algum desses valores fossem errados por exemplo um asterisco mostrando que não foi feita a medição ou então escrito alguma mensagem de erro só ia contar os que são números bom agora a gente entra na parte do histograma a gente precisa determinar Quantas classes o nosso histograma vai ter então assim geralmente é utilizado entre 5 e 20 classes só que a gente não pode usar 20 classes ou muitas classes se a gente tem poucos dados porque senão os

nossos dados não vão ser suficientes para dar uma forma legal pro histograma para fazer isso a gente poderia estimar uma ideia pra gente ter uma ideia de Quantas classes a gente pode ter essa ideia é igual a raiz quadrada de n se eu tirar raiz quadrada de n ou seja 110 eu vou descobrir que eu tenho que ter aí entre 10 e 11 classes Geralmente eu arredondo esse valor para baixo Então a gente vai utilizar 10 classes como eu tenho 10 classes eu preciso calcular um incremento para mostrar qual é a largura de cada uma

dessas classes a estratégia que eu vou usar é a seguinte como o meu mínimo é 467,5 e o máximo É 47,99 eu vou calcular a diferença entre o mínimo e o máximo e vou dividir em 10 classes então o nosso incremento vai ser assim ó igual ao máximo menos o mínimo dividido por classes que aqui são 10 Então esse é o nosso incremento tá aqui com várias casas decimais porque na verdade esses números aqui eu arredondei eles eles para duas casas decimais eles têm na verdade infinitas casas decimais também vai ser importante lá pra frente

a gente saber o desvio padrão do nosso conjunto de dados para isso a gente usa a função desvpad P A que é o desvio o padrão amostral do nosso intervalo esse outro incremento chamar aqui de incremento do tá a gente vai usar depois quando a gente for desenhar a curva da distribuição normal agora a gente precisa fazer o histograma primeiro a gente precisa fazer as classes Então são 10 classes né V colocar aqui 1 2 vamos arrastar até o 10 são 10 classes aqui eu vou colocar Qual que é o corte da classe e aqui

a frequência que é a quantidade de elementos que caem dentro dada classe bom o corte pra primeira classe A gente vai ter que fazer assim ó vai ser igual ao valor mínimo mais o número da classe que é um vezes o incremento Isso quer dizer o seguinte como aqui tá 46 473,6 quer dizer que todos os valores que forem menores ou iguais a 473,6 v003 vão cair dentro dessa classe vão ser contabilizados como a classe um a gente não pode usar o valor mínimo como a classe um por quê Porque como ele é o mínimo

a gente não vai ter nenhum valor menor ou igual a ele tá então aliás vai ter só um igual Mas não vai ter nenhum valor antes então não faz sentido a gente considerar ele como a primeira classe Então a gente vai colocar aqui a primeira classe a segunda classe A gente vai utilizar o mesmo raciocínio só que em vez de multiplicar por um Vai Multiplicar por dois então eu posso copiar essa fórmula aqui somente travando o mínimo e o incremento e replico a fórmula então aqui a gente tem todos os cortes vou tirar as casas

decimais para ficar mais interessante Tá e agora a gente tem que fazer a parte mais complexa até agora que é o cálculo da frequência ou seja para cada um desses valores eu quero saber quantos eu tenho menores ou iguais ao valor da classe e maiores que o valor da classe anterior então é uma função que ela não gera resposta só em uma célula ela vai gerar resposta em uma série de células então o primeiro passo é a gente selecionar as células que vão receber o a resposta aí a gente aperta F2 e escreve igual a

frequência Essa é a função que conta a frequência são dois parâmetros a matriz de dados que a gente vai copiar a gente vai selecionar ela aqui tá o B3 ao b112 ponto e vírgula e o segundo parâmetro que é essa Matriz bim esse bim não tem nada com binário tá esse Bin vem da palavra em inglês Bim que significa compartimento ou seja as classes do histograma Então as nossas classes estão aqui ó a gente fecha e como a gente quer aplicar essa resposta da frequência para todas as células Eu Não Posso apertar enter eu tenho

que apertar control shift enter bom agora que eu apliquei cont control shift enter a gente tem todas as contagens de valores que estão em cada uma das classes se a gente selecionar tudo aqui dá para ver aqui emai ó que a soma é 110 ou seja todos os meus 110 valores estão distribuídos aqui entre as classes S vou fazer uma formatação inha aqui para deixar mais bonitinho colocar aqui o as colunas salvar agora a gente poderia fazer com esses dados que a com essa análise que a gente já fez já dá para fazer o histograma

na sua versão mais básica é para fazer isso é só selecionar essa coluna vem em inserir aí a gente insere um gráfico de coluna e aqui a gente já tem o nosso histograma quase pronto colocar ele aqui do lado algumas coisas que a gente poderia melhorar nele né primeiro aqui embaixo tá 1 2 3 até o 10 às vezes é interessante em vez de ter do um ao 10 eu ter esses valores de corte para eu saber qual valor corresponde essas contagens para fazer isso é só clicar com o botão direito aí a gente vem

aqui em selecionar dados aqui ó tem essa opção rótulos do eixo horizontal então a gente clica em editar e seleciona os valores que a gente quer que apareça ali embaixo na hora que eu der ok vocês vão ver que os rótulos do eixo horizontal foram modificados uma outra coisa que é legal fazer vou até arrastar um pouquinho para cá agora é a gente aumentar a largura dessas colunas então eu cliquei duas vezes em cima das colunas e aparece essa essas opções às vezes vai aparecer outras opções dependendo do Excel que vocês estiverem usando às vezes

pode já aparecer com essa de padrão tá se se aparecer essa como padrão Ou essa é só a gente clicar nessa aqui que é a que a gente quer tá com largura e do espaçamento de 219 por. eu considero isso muito eu não gosto das colunas muito fininhas então a gente poderia utilizar por exemplo 50 e as nossas colunas ficam mais largas se eu utilizar zero elas vão ficar sem espaçamento Mas eu particularmente não gosto disso eu gosto de usar entre 30 e 50 Então vou usar 30 aqui bom então aqui a gente tem o

nosso histograma básico tá o histograma com as colunas cada uma dessas colunas cor correspondem a contagem ou a frequência de ocorrências na minha amostra para essa classe para fazer o histograma junto com a distribuição normal eu preciso começar do zero tá eu preciso apagar esse daqui porque a estratégia é completamente diferente então primeiro né a gente vai fazer o seguinte como no nosso gráfico a gente tem duas séries de dados Então a gente tem a série que vai ter as colunas e a série da distribuição normal primeira coisa que eu vou fazer é criar aqui

a série da distribuição normal para isso a gente precisa calcular o valor da função densidade de massa para diversos pontos e depois utilizar o diagrama de correlação de dispersão para fazer uma linha em cima desses pontos tá Então olha só Qual que é a estratégia para ficar legal é importante usar pelo menos 100 pontos então vou colocar aqui pontos Vou colocar aqui um valor e vou colocar fdm que é função densidade massa da distribuição normal Então os pontos eu vou colocar aqui 100 vou arrastar aqui até criar 100 pontos centralizar tudo aqui também e os

valores como é que vai ser o raciocínio para esses valores para calcular esses valores imagina que a gente tem o mínimo e o máximo Então entre o mínimo e o máximo eu quero ter vários incrementos né ou seja 100 pontos para eu poder calcular a função densidade de massa Então vamos utilizar a mesma estratégia que a gente usou no histograma só que agora é com 100 pontos como a gente tem 100 pontos em vez de 10 a gente precisa de um outro incremento por isso que tem esse incremento dois aqui Então é só a gente

calcular a diferença entre o maior e o menor e dividir agora por 100 vamos lá então agora a gente pode calcular os valores para depois a gente determinar o fdm esse primeiro valor vai ser o mínimo mais o número do ponto vezes o incremento Então esse é o nosso primeiro valor como a gente viu no caso das classes é só a gente travar o mínimo e o incremento e replicar essa fórmula para todos os pontos então agora eu tenho uma série de dados que vai ser o meu eixo X no gráfico de dispersão que varia

do mínimo ao máximo com incrementos que totalizam 100 pontos agora a gente precisa calcular a função densidade massa para fazer isso a gente vai usar uma função que se chama dist pnorm PN essa função ela recebe quatro parâmetros primeiro é o x ou seja o valor que eu tô interessado que nesse caso tá aqui na na célula L3 tá a média da distribuição que tá aqui O desvio padrão da distribuição que tá aqui e essa última opção que é importantíssima que a gente pode colocar ou verdadeiro ou falso verdadeira é a distribuição cumulativa e o

falso é a função de probabilidade de massa a gente vai usar o falso bom eu preciso replicar essa função também para todos os valores para isso dar certo eu vou travar aqui a média eu não travo o x tá porque o x eu quero que ele varie vou travar a média e eu desvio padrão e agora eu posso replicar esse valor para todos aliás essa fórmula para todos os valores é normal esses valores vão ser bem pequenos mesmo tá Inclusive a gente vai ter um problema de escala depois por conta disso né porque os valores

são muito pequenos agora a gente pode fazer o nosso histograma combinado tá prestem bastante atenção nessa etapa então primeiro a gente vai fazer o seguinte eu vou inserir vou selecionar o corte a frequência e vou inserir um diagrama de dispersão Vamos colocar desse tipo aqui ó então percebam que ele não tá com Barras porque na verdade ele ele faz ele faz a correlação entre as duas variáveis e depois faz uma interpolação desses pontos pra gente conseguir colocar as barras aqui aliás antes de colocar as barras vamos fazer o seguinte vamos colocar também a nossa distribuição

normal Então a gente vai clicar aqui também com o botão direito selecionar dados e a gente vai adicionar mais uma série nesse mesmo gráfico então a gente clica em adicionar Vamos dar um nome para essa série vamos chamar ela de fpm porque é função de probabilidade massa pode colocar qualquer nome aqui tá aí a gente seleciona o valor x que é são esses valores aqui usei o shift baixo fazer isso e aqui eu seleciono os valores Y que vai ser que vão ser Essas funções aqui esses resultados da função de probabilidade de massa na hora

que eu der ok Olha o que que vai acontecer D Ok de novo Vai aparecer como se fosse uma linha aqui por porque esses valores são muito pequenos quando a gente compar com essa escala aqui eles out escala a gente pode clicar duas vezes em cima deles vai aparecer essa opção se tiver aqui né a gente clica em eixo secundário Então olha o que que aconteceu agora a gente tem essa nossa outra série plotada com uma outra escala tá então vamos lá pra gente poder agora trabalhar com isso daqui vamos primeiro pegar a série do

histograma e transformar ela em colunas tá para fazer isso é um pouquinho complexo porque isso aqui é um diagrama de dispersão Então vou arrastar um pouquinho para cá aí a gente vai fazer assim ó depois que eu selecionar ó eu soltei de novo vou clicar aqui em cima dessa série A gente vem aqui em design adicionar elemento gráfico e barras de erros nas barras de erros a gente vai aqui em mais opções de barras de erros Ótimo então aqui a gente vai fazer o seguinte vai ser uma barra de erro para menos sem legenda e

com porcentagem 100% então a gente tá quase lá olha só eu desenhei essas linhas que parecem Barras bem finas para transformar isso em Barras a gente vem aqui coloca a largura da linha Vamos colocar 20 e e muda a cor para ficar mais parecido com o outro histograma que a gente fez então o que que eu fiz eu peguei aquelas linhas Eu aumentei a largura delas e mudei a cor coloquei 20 já foi suficiente vamos clicar aqui fora Estamos quase lá que que falta fazer né falta a gente mudar esse gráfico aqui então eu cliquei

na série do histograma clico com o botão direito aí a gente pode vir e alterar o tipo de gráfico dessa série então aqui embaixo a gente vai a gente tá mexendo com a frequência né que é do histograma e eu vou mudar para esse tipo aqui sem a as linhas e dou ok então o que aconteceu aquelas linhas sumiram mas ainda ficou essa bolinha aqui essa bolinha aqui na verdade é o marcador então só a gente clicar duas vezes em cima dela vem aqui nessa opção marcador opções de marcador e nenhum na verdade acabou dando

errado porque eu acabei selecionando só um deles né então vou fazer de novo tá na hora que eu clico aqui vem nessa opção marcador opções de marcador nenhum todos eles somem ou seja agora o nosso histograma tá aqui certinho com as barras nem na verdade aqui são as colunas e falta uma última coisa que é o mudar o tipo dessa série aqui que é a série da nossa distribuição normal então a gente Clica com o botão direito alterar o tipo de gráfico e aqui no fpm a gente vai utilizar essa opção aqui de linhas suaves

dou ok e aqui a gente tem o nosso gráfico só para finalizar vou modificar aqui eu vou aumentar a largura para 2 pontos me tá e a gente finaliza a construção do nosso gráfico que é o histograma de barras juntamente com a distribuição normal tá espero que vocês tenham entendido até o próximo vídeo