Dando continuidade a série de postagens sobre a elaboração de DashBoards, estou apresentando aqui a solução para as questões levantadas na postagem anterior (DashBoards: Começando a Entender ...):
- Quais são as 06 dezenas que mais vezes foram sorteadas em todos os concursos que estão na base;
- Quais são as 06 dezenas que menos vezes foram sorteadas em todos os concursos que estão na base;
- Qual o percentual que as dezenas que mais e/ou menos vezes foram sorteadas, representa sobre as outras dezenas;
- Quais as dezenas que mais foram sorteadas: pares ou ímpares? E qual o percentual que cada uma representa?
Como mencionado na postagem anterior, estou utilizando como banco de dados, alguns dos resultados dos concursos da MegaSena! (mais precisamente 1260 concursos conforme vemos na figura ao lado).
Através dos diversos recursos disponíveis no Excel e, com um pouco de criatividade, podemos facilmente extrair qualquer informação desejada e disponível em nossa base de dados.
Contagem das dezenas:
Para iniciar, é interessante inserir uma nova planilha para consolidar/extrair os dados num local separado da base de dados. Podemos inserir na coluna 'A' (desta nova planilha) todas as dezenas que compõe uma cartela da megasena (01 a 60).
Na coluna 'B', podemos inserir uma fórmula para determinar o número de vezes que cada dezena foi sorteada. A fórmula abaixo, está inserida na célula 'B4' (figura ao lado).
Explicando a fórmula:
- Esta fórmula (Com as funções: Cont.se, Desloc, Aleatório) faz uma contagem das dezenas que estão na base de dados. No exemplo da fórmula, na figura acima (endereço/célula 'B4'), a função Cont.se faz uma procura na base de dados e retorna o número de vezes que a dezena '01' foi sorteada (em todos os 1260 concursos que a nossa base possui).
- A função Desloc substituiu o endereçamento convencional ('Sorteios MegaSena'!B2:G1261 - que poderia ter sido utilizado) pois mais a frente ela será necessária. Da forma como a função está, ela faz referência ao mesmo endereço que o endereço convencional faria.
- A função 'Aleatório()/1000' que faz parte da função (que na verdade é uma certa 'gambiarra') foi inserida para que quando um número estiver com a contagem igual a outro, ou a outros, um número insignificante é adicionado para que haja uma diferença entre as duas ou mais contagens. A função Aleatório faz um sorteio (a cada atualização da planilha) de um número decimal sempre maior do que 0 (zero) e menor do que 1 (um). Dividindo este número por '1000', teremos um número insignificante que será adicionado que fará com que nunca aconteça de ter dois ou mais números com as quantidades exatamente iguais. Isto será útil para fazer a verificação das dezenas que foram mais ou menos vezes sorteadas.
Com isso, teremos na coluna 'B', as quantidades que cada uma das 60 dezenas foram sorteadas.
Dezenas que mais vezes foram sorteadas:
Para que possamos extrair, por exemplo, as seis dezenas que mais vezes foram sorteadas, demonstrar as quantidades que estas dezenas saíram e a participação percentual de cada dezena, devemos utilizar algumas funções, conforme representado na figura ao lado.
- A 1ª informação a ser extraída é a quantidade (Qtde = coluna 'E'). Para isso, utilizamos a função 'Maior'. Esta função informa um valor de acordo com a sua posição relativa. Em nosso exemplo, será retornado o 1°, 2°, 3°, 4°, 5° e 6° maior valor. A função na célula 'E6' é a seguinte: MAIOR($B$4:$B$63;1).
- Com base na 1ª informação, podemos extrair o número a que esta informação representa (Ordem = coluna 'D'). Em nosso exemplo, a dezena que mais vezes foi sorteada (152 vezes) foi: '05'. Através das funções 'Desloc' e 'Corresp', conseguimos extrair esta informação: DESLOC($A$3;CORRESP(E6;$B$4:$B$63;0);0;1;1). Em postagens anteriores ( Funções de Procura no Excel 2007: Desloc + Corresp / Funções Corresp e Desloc: Similares ao Procv / entre outras ...) a utilização das funções Desloc e Corresp são explicadas e possuem link para download com exemplos.
- A 'Part.%' (coluna 'F') da quantidade que cada dezena representa sobre a quantidadde total das 06 dezenas é calculada com uma simples divisão: Quantidade da Dezena sobre a quantidade total das 06 dezenas.
Dezenas que menos vezes foram sorteadas:
Da mesma forma como extraímos as seis dezenas que mais vezes foram sorteadas, podemos extrair as dezenas que menos vezes foram sorteadas. A única alteração a ser feita é trocarmos a função 'Maior' pela função 'Menor'. O restante das fórmulas das 03 colunas ('D', 'E' e 'F') são exatamente iguais.
Dezenas que mais foram sorteadas: pares ou ímpares?
Para extrair a quantidade que as dezenas pares e ímpares foram sorteadas, utilizamos, a principio, três funções: Soma, ÉPar e ÉImpar. Como vemos na figura abaixo, criamos na coluna 'I' uma fórmula para calcular as dezenas pares (ÉPar) e na coluna 'J', para calcular as dezenas ímpares (ÉImpar). Para finalizar, fazemos a soma de todos os valores de todas as linhas na coluna 'I' para as dezenas pares e da coluna "J' para as dezenas ímpares.
Com isso, teremos a quantidade de vezes que as dezenas pares foram sorteadas e a quantidade de dezenas ímpares.
Podemos criar gráficos com base nestes números e então, ter uma clara visão do que estes números representam. Podemos criar diversos tipos de gráficos e mais, fazer com que eles percorram a área de dados sem termos que mudar a seleção de dados. Através do Gerenciador de Nomes (Guia Fórmulas), funções (como a função Desloc) e alguns objetos podemos fazer com que a seleção de dados possa ser alterada de forma automática.
No link abaixo, pode ser feito o download do arquivo de exemplo. Nele há diversos outros recursos que explicarei em outra oportunidade.
Se desejarem, podem fazer comentários referente a esta postagem, sugerindo algo ou até mesmo questionando algo que não tenha ficado claro. Também pode ser enviado email para maiores esclarecimentos.
Download: DashBoard MegaSena
Confira também:
Compartilhe ....
BONS ESTUDOS!
PROFESSOR FERRARI OFICIAL
EXCELMAX SOLUÇÕES
.jpg)
Postagens
Muitoooooooooo Número. Quanta coisa, ou informação, podemos extrair de bases de dados que muitas vezes nem imaginamos. Esta postagem está excelente.
ResponderExcluirSolanoS.
Por incrível que pareça não consegui fazer o gráfico mudar de quantidade para percentual com a célula validada. Toda vez que vou acrescentar a coluna dos valores percentuais e acrescenta mais uma barra no gráfico. Qual é o segredo ? Abraço a todos
ResponderExcluir