Análises com Fórmulas e Gráficos no Excel

 Observação importante:

 

Esta postagem é de nível avançado com a utilização de recursos: Fórmulas, Gerenciador de Nomes, Gráfico, Validação de Dados e Formatação Condicional.

Para um bom entendimento, sugiro bastante atenção na leitura da postagem e na análise do exemplo que pode ser baixado no link (final na postagem).

 

Analisar as informações em uma tabela é a parte mais importante pois, de nada adianta ter informações e não tirar proveito das mesmas.

 

Estas análises podem ser feitas de muitas maneiras com a utilização de muitos recursos sempre alienados a uma boa criatividade.

 

A tabela abaixo simula os dados de uma pesquisa feita em determinados estados. Nesta pesquisa, foram feitas algumas perguntas: Estado (UF), estado civil, sexo, moradia (aluguel ou casa própria), se a pessoa possui ou não automóvel, se é fumante ou não, a escolaridade e se possui filhos (quantidades).

 

Podemos notar que não é possível analisar as informações simplesmente por observar a tabela. Para isso, é importante que seja feita uma tabulação (consolidação) dos dados coletados.

Mas, como podemos analisar os dados desta base de dados? Quantas tabelas e/ou gráficos precisaremos criar? Bem, tudo depende do que sabemos fazer e de nossa criatividade.

A tabulação dos dados coletados será feita com a utilização de algumas fórmulas. Cito algumas que serão utilizadas: DESLOC, CORRESP, CONT.SE, CONT.VALORES.

A nossa tabela de dados possui 8 colunas com algumas linhas de informações. Normalmente quando precisamos analisar todas as informações de uma tabela com esta quantidade de colunas (ou mais colunas) elaboramos uma tabela com algumas colunas ou até, mais do que uma tabela. Se desejamos analisar os dados graficamente observando valores e/ou percentuais, normalmente criamos alguns gráficos.

Nosso objetivo é tabular (ou consolidar) todas estas informações em uma única tabela e um único gráfico com uma opção de seleção para alterar entre as 8 colunas. A primeira fórmula que vamos criar através do 'Gerenciador de Nomes' (guia Fórmulas) é a mais importante pois, através dela, teremos a opção de selecionar a coluna desejada que será mostrada na tabela de tabulação e no gráfico de barras. No Gerenciador de Nomes vamos criar o nome 'Origem' que conterá algumas funções formando assim uma fórmula conforme vemos na figura abaixo.

 

Fórmula 'Origem':

• =DESLOC(Base!$A$1;1;CORRESP(Dados!$L$1;Base!$A$1:$J$1;0)-1;CONT.VALORES(DESLOC(Base!$A$1;1;CORRESP(Dados!$L$1;Base!$A$1:$J$1;0)-1;10;1));1)

Com esta fórmula, é possível selecionarmos a coluna desejada para ser demonstrada na tabela de tabulação e no gráfico. Note que o nome 'Origem' (que contém uma fórmula com algumas funções do Excel) está contida na fórmula a seguir que se encontra na célula 'L2':

• =SE(ÉERROS(DESLOC(Origem;0;0;1;1));"";DESLOC(Origem;0;0;1;1))

Note que o número destacado (0) é a única coisa a ser alterada nas células das linhas seguintes: 'L3' até 'L7' (com os números 1 à 5 respectivamente.

Na fórmula acima, utilizei mais uma vez a função DESLOC para mostrar mais uma vez em um exemplo visto ser uma função muito útil. Podemos porém, utilizar uma fórmula bem mais simples no lugar da mencionada acima.

• =PROCH($L$1;Base!$A$1:$J$7;LIN();FALSO)

A fórmula na célula 'M2' faz a contagem dos itens referente ao critério contido na célula 'L2'.

• =CONT.SE(DESLOC($A$1;1;CORRESP($L$1;$A$1:$J$1;0)-1;30;1);L2)

Na célula 'N2', a fómula faz o cálculo da participação percentual que cada valor da coluna 'M' tem sobre o total dos itens consolidados.

• =M2/SOMA($M$2:$M$7)

Além das fórmulas utilizadas para a elaboração da tabela onde os dados serão tabulados, utilizei a Formatação Condicional para destacar as bordas das linhas (quando utilizadas).

Por fim, os dados tabulados são demonstrados em um gráfico de barras (tanto em valores como em percentuais). Os dados demonstrados no gráfico podem ser facilmente alterados, isto é, podemos visualizar o que desejarmos apenas selecionando o tipo da informação (célula 'L1'): estado (UF), estado civil, sexo, moradia, carro, fumante, escolaridades e filhos.

Para criação do Gráfico foram criados dois Nomes (no Gerenciador de Nomes) que contém algumas fórmulas.

Nome Descrição (fará a seleção dos nomes dos rótulos do eixo horizontal (Categorias):

• =DESLOC(Dados!$L$1;1;0;CONT.SE(Dados!$M$2:$M$7;">"&0);1)

Nome Valores (fará a seleção dos valores referente a Descrição):

• =DESLOC(Dados!$L$1;1;Dados!$O$1;CONT.SE(Dados!$M$2:$M$7;">"&0);1)

Observação: A célula mencionada acima (Dados!$O$1) está é o local onde os objetos 'Contagem' e 'Part. %' estão vinculados para que o gráfico possa alternar entre mostrar a contagem dos valores ou a participação percentual.

Para selecionar os dados para a criação do gráfico, utilizamos estes dois nomes (Descrição e Valores). O gráfico terá as funcionalidades conforme mencionado e com mais algumas formatações, pode ficar com a aparência da figura abaixo. 

Se gostou desta postagem, utilize-a fazendo os ajustes necessários as suas necessidades e repasse a seus amigos. Se desejar divulgue o endereço para que outras pessoas possam ver as explicações de como criar/trabalhar com este exemplo.

Se ficou alguma dúvida, favor postar comentário.

Download: DashBoard - 2011 - Tabela e Gráfico

Até a próxima.