Imaginemos o seguinte cenário: temos uma tabela com quase 30 mil linhas, onde cada linha é uma venda realizada. Temos "N" produtos sendo vendidos a "N" clientes das mais variadas regiões do país, ao longo de diversos anos.
Então o diretor solicita que você faça um quadro que resuma a quantidade de vendas dos clientes x produtos, de um determinado período. Você, com todo o seu esforço consegue ao final do dia gerar esse quadro aí, ele olha o quadro diz que "pensando bem, quero que seja o valor total por estado/clientes x categoria, e altera também o período. Lá vai você novamente, mais um dia de trabalho, e já começa a temer que ele volte a ser criativo no dia seguinte.
Seria muito bom se tivéssemos um mecanismo de gerar estes quadros dinamicamente? Uma forma mais inteligente de efetuar cruzamentos de dados? Eis aqui o recurso Tabela Dinâmica. Vamos ver como funciona...
Conceitos
Tabela dinâmica é um recurso disponível no Excel desde a década de 90, extremamente poderoso, mas ainda desconhecido pela maioria. Uma tabela dinâmica segue, basicamente, a seguinte regra estrutural:- FATO (VALORES)
- Analisamos um ou mais FATOS em uma tabela dinâmica. um fato pode ser um total da venda, pode ser a quantidade vendida, enfim, pode ser qualquer elemento numérico que queiramos analisar.
- MÉTRICA
- Um fato é analisado em alguma métrica. Podemos analisar com SOMA, MÉDIA, MÁXIMOS, MÍNIMOS, DESVIOS, enfim, cada operação matemática ou estatística irá configurar uma métrica do fato
- DIMENSÃO (EIXOS X,Y)
- Quando analisamos fato em uma métrica, por exemplo SOMA DOS VALORES TOTAIS (no caso, SOMA é a métrica e VALOR TOTAL é o fato), podemos fazer essa análise sob pontos de vista. Cada ponto de vista é uma dimensão. Por exemplo, queremos a SOMA DOS VALORES TOTAIS, por ESTADO e por PRODUTO. Estado e produto são dimensões.
- As dimensões podem ser colocadas em linhas ou em colunas
- FILTRO
- Depois de criada a tabela, podemos eleger alguns campos como FILTROS e dar ainda mais dinâmica à nossa tabela. Os dados da tabela serão apresentados conforme o que estiver selecionado nos filtros. Por exemplo: se desejarmos que a nossa tabela dinâmica seja filtrada por ESTADO, basta colocar esta coluna em FILTRO. A partir daí, o escolhermos um estado, a tabela apresentará apenas registros do estado escolhido.
Criando a nossa tabela dinâmica
No artigo "Excel: Entendendo e manipulando corretamente uma lista" , terminamos com uma tabela pronta. Uma tabela onde relacionamos vendas ao longo de alguns anos de uma série de produtos a alguns clientes de cidades e estados variados. Nossa lista tem aproximadamente 27 mil linhas, e veremos que, apesar de relativamente extensa, é muito simples de ser trabalhada usando tabela dinâmica. Quem desejar, pode realizar os exercícios NA PRÁTICA, CLICANDO AQUI PARA BAIXAR A TABELA DE ORIGEM. Abaixo ilustramos a nossa tabela de origem:
Tente acompanhar nosso roteiro.
1 - Clique em qualquer célula da tabela, no nosso caso, observe que a célula C9 está selecionada. Pode ser qualquer uma, desde que esteja dentro da tabela.
2 - No menu INSERIR, clique em TABELA DINÂMICA
3 - A tela de criação da tabela dinâmica será apresentada. Notem que o INTERVALO DE ORIGEM já veio devidamente preenchido com a informação TABELA1. Isso porque o Excel já detectou automaticamente a lista.
4 - Marque a opção ADICIONAR ESTES DADOS AO MODELO DE DADOS. Esse passo é importante para que possamos potencializar nossa tabela dinâmica quanto formos estudar SEGMENTAÇÃO DE DADOS.
5 - Como LOCAL DESTINO, podemos escolher colocar a tabela em uma planilha já existente, na PLAN2, por exemplo, ou deixar que o Excel crie uma nova. No nosso caso, escolhemos PLAN2, célula A3.
Notem abaixo que em nossa PLAN2 agora tem um objeto inserido. É a tabela dinâmica. Agora precisaremos definir FATO (com sua métrica), DIMENSÕES e FILTROS (se desejarmos). Observem na figura abaixo que, à direita, temos uma caixa e nela faremos as definições de nossa tabela. Essa caixa tem uma LISTA DE CAMPOS (acima) e quatro outros campos: FILTROS, LINHAS, COLUNAS e VALORES.
Definindo nossos fatos
Queremos aqui analisar totais do campo VALORPRODUTO.1 - Arrastem para o campo VALORES a coluna VALORPRODUTO. Notem que a nossa tabela dinâmica já está dando a primeira informação, que é a SOMA DE TODOS OS CAMPOS VALORPRODUTO
2 - Agora, vamos formatar o valor para que tenha 2 casas decimais e separador de milhar. Cliquem no campo, dentro da área de VALORES, e em seguida cliquem em CONFIGURAÇÕES DO CAMPO DE VALOR:
3 - A caixa abaixo será apresentada. Troquem o nome, em NOME PERSONALIZADO, para TOTAL. Notem que nesta caixa podemos alterar o tipo de cálculo (MÉTRICA) Se quisermos MÉDIA ao invés de SOMA basta selecionar a função desejada. Agora cliquem em FORMATO DO NÚMERO:
4 - Escolham em CATEGORIA o grupo NÚMERO e, ao lado, marquem USAR SEPARADOR DE 1000 e escolham na lista qual o tratamento desejam para números negativos:
Definindo as dimensões
Agora, iremos começar a analisar o nosso fato por alguns ângulos diferentes.1 - Arrastem NOMEPRODUTO para o campo LINHAS conforme demonstrado na ilustração abaixo. Assim, teremos o mesmo total, porém, agora com quebras por PRODUTO:
2 - Agora arrastem a cidade para o campo COLUNAS e veja o que acontece:
Temos agora o TOTAL por PRODUTO X CIDADE. Notem a facilidade que estamos tendo para totalizar a nossa lista.
UTILIZANDO FILTROS
Desejamos agora dar a quem estiver consultado a nossa tabela a possibilidade de filtrar os dados. Podemos ter N colunas no campo FILTROS.1 - Arrastem a coluna ESTADO para o campo filtros.
O campo estado agora aparece na parte esquerda superior de nossa tabela. Abrindo o campo, conforme abaixo, vemos que o Excel já está listando ali todos os estados disponíveis nos dados de origem.
Podemos escolher um deles, todos ou, ativando a opção SELECIONAR VÁRIOS ITENS, marcar alguns e deixar outros de fora.
Abaixo está a nossa tabela dinâmica filtrada com os dados de MG e SP apenas.
Agora apenas com RJ.
E agora, novamente com todos os estados selecionados no filtro.
Agora voltem ao posicionamento anterior e coloquem, antes de NOMEPRODUTO, a coluna CATEGORIA. Vejam abaixo:
Agora apliquem um formato, via DESIGN / FORMATAR COMO TABELA. Neste exercício escolhemos o formato marcado na imagem abaixo:
Nossa tabela dinâmica está pronta. Salvem o exercício e brinquem um pouco mais com combinações de linhas x colunas antes de prosseguir.
DrillDown (detalhamento dos dados)
Pode trás dos valores da tabela, sabemos que existem N linhas que foram totalizadas. Por exemplo, no quadro abaixo, temos a célula B13 como o valor de 4.170. Se desejarmos saber quais foram as vendas de ABRIDOR DE LATAS em BELO HORIZONTE, que somadas deram 4.170,00, basta darmos um DUPLO-CLIQUE sobre o valor que desejamos detalhar.
Abaixo podemos notar que o Excel criou uma nova planilha (PLAN4, no caso), e nela listou todos os registros que originaram aquele total. É uma planilha como outra qualquer e podemos deletá-la, se desejarmos, sem que estejamos apagando os dados de origem.
Atualização da Tabela Dinâmica
Quando os dados da origem são alterados, novas vendas, alteração de quantidades, linhas deletadas, enfim, se a origem mudar, temos que fazer com que essa mudança seja refletida na tabela dinâmica.
Para exemplificar, na tabela de ORIGEM, alteramos a QUANTIDADE e o VALORPRODUTO de uma das vendas para 1000 e 1000 respectivamente.
Voltamos para a nossa tabela dinâmica e, clicando sobre a tabela (qualquer célula dentro dela) e em seguida ATUALIZAR, fazemos com que a tabela seja recalculada, já considerando os novos valores.
Outra forma muito utilizada de atualização é configurar a tabela dinâmica para que seja atualizada automaticamente sempre que a pasta for aberta. Para isso, clicamos sobre a tabela (qualquer célula dentro dela) e em seguida entramos em OPÇÕES DA TABELA DINÂMICA.
Agora marcamos ATUALIZAR DADOS AO ABRIR O ARQUIVO.
Pronto. Já temos a nossa primeira tabela dinâmica.
Em nossos próximos artigos, seguiremos neste mesmo assunto, explorando os seguintes pontos:
- Gráficos Dinâmicos
- Segmentação de Dados
Se desejar este exercício concluído, CLIQUE AQUI.
Estamos aqui, à disposição, aceitando sugestões para novos artigos.
Contem conosco!
Curta a nossa página
Julho/2014
GILBERTO GOMES
ExcelLine - Office World
Google+ +gilberto gomes
Leiam também outros artigos em NOSSO BLOG.
Aproveitem e conheçam o site da nossa empresa: www.excelline.net.
Contem conosco!
Curta a nossa página
Julho/2014
GILBERTO GOMES
ExcelLine - Office World
Google+ +gilberto gomes
