Feeds:
Posts
Comentários

Posts Tagged ‘Avançado’

Oi amigos! De volta para ajudá-los mais um pouquinho… Muitos de vocês utilizam o Microsoft Excel e não conhecem as maravilhas de usar uma tabela dinâmica. Ou ainda, conhecem, usam o que alguns companheiros de trabalho já haviam criado, mas não tem a menor ideia de como criá-la.

Vocês vão ver alguns conceitos básicos muito importantes para vocês poderem criar suas próprias tabelas dinâmicas de forma simples, e fazer os ajustes de acordo com as suas necessidades para que você possa visualizar seus resultados da melhor maneira.

Caso queira, baixe aqui, a planilha de exemplo para este tópico.

Temos como exemplo esta planilha que possui 104 linhas de dados:

Utilizando apenas filtros, você até poderia resolver muitas coisas, mas será que você não teria muito trabalho para obter, por exemplo, o total vendido em Reais da Equipe Gama, para o Estado de MG, ou ainda quantas vendas do item Camisas foram realizadas pelo funcionário João?

Para estes e uma infinidade de outros casos que utilizamos o recurso de Tabelas Dinâmicas. Este assunto é tão vasto que tenho inclusive um treinamento só deste maravilhoso recurso. Clique aqui e saiba mais sobre este treinamento.

Bem, voltando ao assunto, Clique em qualquer ponto de sua tabela e vá em Inserir, Tabela Dinâmica.

Você pode clicar diretamente no botão, ou clicar na seta e escolher Tabela Dinâmica. Você terá a seguinte janela:

Como estou utilizando um Formato de Tabela, o nome desta tabela aparece como o Intervalo. Nada impediria de aparecer um intervalo de células neste campo. Tabelas Dinâmicas também podem ser geradas a partir de uma fonte de dados externa como outros arquivos Excel, Microsoft SQL Server, Microsoft Access, bancos de dados de arquivos de texto e até dados de um site da Web. No nosso caso, iremos utilizar a nossa tabela que foi automaticamente como Tabela 1.

Vamos colocar nossa Tabela Dinâmica em uma Nova Planilha, e depois pressionaremos Ok.

Agora é a hora de planejar de que maneira vamos exibir os resultados em nossa tabela dinâmica. Vou dar um pequeno exemplo inicial:

Calculando o total vendido por vendedor

Toda tabela dinâmica tem 4 áreas: Filtros de Relatório (antigo campo de página), Rótulos de Linha (Campo de Linha), Rótulos de Coluna (Campo de Coluna) e Valores (Área de Valores). Podemos utilizar 2 ou mais áreas para podermos analisar nossos dados. No primeiro exemplo, vamos selecionar o Vendedor, marcando a caixa. Repare que automaticamente ele vai ser colocado na área chamada Rótulos de Linha. Você também poderia realizar este processo clicando sobre o nome Vendedor e arrastando-o para a área abaixo da Lista de Campos, dentro do quadro Rótulos de Linha.

Teremos o seguinte resultado:

Olha que coisa interessante: mesmo que tenhamos nomes repetidos na nossa planilha principal, ele vai mostrar apenas 1 ocorrência do nome em nossa tabela dinâmica. Isso vai ser de grande importância quando nós formos para a próxima etapa.

Agora, dê um clique na caixa antes de Vendas, ou arraste o Campo Vendas para a área de Valores. Pronto! Você já tem o total em R$ vendido por cada vendedor. Fácil? Isso é tão mole quanto sopa de minhoca…

Agora vamos fazer como os bons cozinheiros, vamos arrumar bem o nosso prato. As vendas deveriam ser exibidas em um formato como o Contábil. Para fazer isso de uma maneira bem simples, faça o seguinte: Clique na seta que aparece ao lado de Soma de Vendas, dentro do quadro Valores. Agora escolha a última opção – Configurações do Campo de Valor.

Na próxima janela, na parte inferior da janela, você encontrará um botão que vai nos ajudar bastante, Formato do Número, que permitirá escolher o formato de exibição desejado para os nossos valores.

Escolheremos contábil e manteremos as outras configurações, clicando em Ok para confirmar.

Depois de pressionar Ok mais uma vez na janela Configurações de Campo de Valor, você terá o seguinte resultado:

Muito bem! Estamos melhorando. Mas, só utilizamos duas das quatro áreas da Tabela Dinâmica. Portanto, iremos fazer outro exemplo:

Exibindo o total vendido por Vendedor em cada Estado, paginando por Equipe

Primeiramente, vamos retornar à nossa tabela de dados. Quero ver se você realmente aprendeu a fazer uma tabela dinâmica. Repita os passos até você ter uma planilha somente com sua tabela dinâmica (Aba Inserir, Tabela Dinâmica, Ok).

Agora direcione os campos da seguinte maneira:

Equipe => Filtro de Relatório
Vendedor => Rótulos de Linha
Estado => Rótulos de Coluna
Vendas => Valores

Você vai me perguntar: E o produto? Você não é obrigado a usar todos os campos da tabela em uma Tabela Dinâmica. Você só deverá usar os campos que forem relevantes para a análise que você está realizando.

Teremos o seguinte resultado:

Nossa tabela agora exibe o total vendido por Estado. Faça o ajuste para exibir valores em Contábil.

Agora você vai brincar com essa Tabela Dinâmica. Neste ponto você verá porque ela tem este nome. Clique na Seta onde está aparecendo (Tudo) na Equipe de Vendas. Escolha qual Equipe você quer ver, por exemplo a Equipe Gama. Agora selecione apenas os Estados do RJ e de SP, para isso, clique na Seta ao lado de Rótulos de Coluna. Mais uma vez os valores foram alterados, certo? É esse dinamismo que faz este recurso ser um dos queridinhos do escritório, e por conta disso, você não pode de forma alguma não conhecer melhor sobre este assunto. Temos o seguinte resultado agora:

Foi muito prático, não? Você pode mudar à vontade os campos da Tabela. Aliás, vamos fazer isso juntos! Observe o Painel à direita do seu Microsoft Excel. Caso o Painel não esteja aparecendo, simplesmente clique sobre qualquer dado de sua Tabela Dinâmica.

Desmarque a caixa Vendedor e marque a Caixa Produto. Pronto! Sua tabela Dinâmica agora tem uma nova análise, você agora sabe quanto em Reais foi vendido de cada produto para o RJ e para SP.

Bem meus amigos, caso vocês tenham dúvidas, sugestões, pedidos de orçamentos, fiquem à vontade para entrar em contato. Espero que eu tenha ajudado vocês mais uma vez. Muito obrigado por todas as mensagens de carinho e apoio que tenho recebido. Valeu mesmo!

Forte abraço à todos e até a próxima!

Anúncios

Read Full Post »

Hallo!!!

Bem pessoal, agora que mesclei, não tem jeito… Vamos seguir com mais dicas avançadas: vou criar com vocês um gráfico condicional.

O que é um gráfico condicional? É um gráfico que muda de cor de acordo com um critério que você especificar. Por exemplo, você tem 3 estágios em uma linha de produção: Produção Baixa, Produção Esperada e Super Produção. Você gostaria de colocar estes estágios em três cores diferentes em um gráfico de colunas, com apenas 1 coluna e não três como normalmente seria…

Nosso problema é esse:

Gráfico condicional

Segue a planilha para você acompanhar comigo: Exemplo – Gráfico Condicional

Planilha salva? Vamos lá:

Crie três colunas onde vocês irão separar os valores: Produção Baixa, Produção Esperada e Super Produção. Você já sabe qual o critério de cada uma, então, precisamos dividir os valores de produção em suas colunas correspondentes:

Gráfico Condicional - Parte 2

Para Produção Baixa vamos fazer a seguinte função: =SE(B5<100000;B5;0), onde o valor de B5 aparecerá na coluna caso ele seja menor que 100.000 senão aparecerá 0.

Para Produção Esperada vamos fazer a seguinte função: =SE(E(B5>=100000;B5<=400000);B5;0), onde o valor de B5 aparecerá na coluna caso ele seja maior ou igual a 100.000 E menor ou igual a 400.000 senão aparecerá 0.

Para Super Produção vamos fazer a seguinte função: =SE(B5>400000;B5;0), onde o valor de B5 aparecerá na coluna caso ele seja maior que 400.000 senão aparecerá 0.

Não esqueça de arrastar as fórmulas para baixo!

Você terá o seguinte resultado:

Gráfico Condicional - Parte 3

Agora vamos montar o gráfico! Bem, no post Criando um gráfico rápido mostrei como vocês poderiam criar gráficos em um estalar de dedos.

A dica a seguir serve apenas para Office 2007 e 2010, galera mais velhinha, mande-me um e-mail que darei um apoio. Clique em uma área vazia da planilha e pressione ALT + F1, você terá um gráfico em branco.

Gráfico condicional - parte 4

Com o gráfico vazio inserido, vamos brincar um pouco! Selecione desde Super Produção até o último valor de sua coluna, copie e cole dentro do gráfico. Isso mesmo! Vai, tenta!

Agora clique na coluna azul que apareceu e lá no topo localize Ferramentas de Gráfico, Formatar.

Ferramentas de Gráfico, Formatar

Localize o botão Preenchimento da Forma e escolha a cor Verde, você pode também no grupo Estilos da Forma escolher um modelo verde pronto pré-configurado.

Seu gráfico ficará assim:

Gráfico Condicional

Agora repita o processo de copiar e colar para as outras duas colunas. Não esqueça de personalizar, ok? Lembre-se Produção Baixa em Vermelho, Produção Esperada em Azul e Super Produção em Verde.

Teremos este resultado ao final desta etapa:

Gráfico condicional - Quase Ok

Agora falta pouco, vamos aos ajustes finais:

Primeiro, iremos alterar os números referentes aos meses para o nome dos meses:

Clique no gráfico, vá em Ferramentas de Gráfico, Design e clique no botão Selecionar Dados. Em Rótulos do Eixo Horizontal (categorias) você deve clicar no botão Editar e depois selecione os meses das células A5 até A16 pressione Ok e depois Ok novamente e pronto, você terá este resultado:

Agora falta pouco para nosso Gráfico Condicional

Vamos aos “finalmentes”, clique com o botão direito sobre qualquer coluna deste gráfico e escolha Formatar Série de Dados… Na janela que se abre, simplesmente faça os seguintes ajustes:

Sobreposição de Séries: 100% – Isso fará com que as colunas fiquem uma sobre a outra, dando a impressão da troca de cor.

Largura do Espaçamento: Cerca de 20% – Isso fará com que as colunas fiquem mais largas dando um efeito muito interessante.

Este é nosso resultado final:

Gráfico Condicional 3 condições - Completo

Agora, experimente alterar o valor da produção em qualquer um dos meses para ver as colunas “mudando” de cor, e impressionando muita gente.

Malandramente, coloque o gráfico posicionado exatamente sobre a área onde você dividiu a informação. Ninguém precisa saber que tem um segredo ali atrás não é?

Solução - Gráfico Condicional

Lembro que com esta técnica, você pode criar gráficos condicionais com o número de cores que quiser. Tudo dependerá de quantas colunas de condições você criará.

Esta foi excelente, ou você esperava mais? Bem pessoal, comente, entre em contato e estarei pronto para postar mais e mais segredos por aqui. Ok? Abraços!

Auf Wiedersehen!

Read Full Post »

Olá Pessoal!

Vou começar a mesclar as coisas por aqui. Não posso apenas dar dicas muito básicas para vocês, desta vez, falarei para o pessoal mais avançado.

Quem aí já teve a necessidade de fazer um PROCV com 2 critérios ou mais? Vou mostrar um exemplo do que quero dizer:

2 Condições

Muitos ensinariam aqui que você deve criar uma coluna extra, concatenar as duas ou mais chaves desejadas para realizar o PROCV. Vou fazer diferente e tenho certeza que vocês vão adorar:

Primeiro, crie uma área onde você vai informar os critérios:

Área de critérios

Agora você precisa criar uma função que analise os critérios e a nossa tabelinha. Vamos lá?

Vamos trabalhar com duas funções que quando se juntam, vira uma festa: ÍNDICE E CORRESP. Na célula F4 digite =ÍNDICE(B2:B9;CORRESP(F2&F3;A2:A9&C2:C9;0)) e ao final pressione CRTL + SHIFT + ENTER para transformar esta função em uma matricial.

Índice e Corresp

Depois do CRTL + SHIFT + ENTER ficará assim:

Índice e Corresp 2

Vamos aos detalhes: A função ÍNDICE permite localizar um valor em uma matriz a partir de sua posição de linha e coluna. A matriz é de B2 até B9, você não sabe a linha onde a informação está, portanto, você precisa de algum recurso que permite localizar a posição do item ou no nosso caso itens que você pesquisa. Usaremos então CORRESP que vai pesquisar o conteúdo de F2 concatenado com F3, na matriz A2 até A9 concatenado com C2 até C9 com resultado exato (por isso o zero no final da função). Como não me interessa a coluna desejada, ignoro esta opção na minha função ÍNDICE. Pressiono CTRL + SHIFT + ENTER para gerar uma função matricial e pronto. Você acaba de encontrar o resultado da conctanação de dois critérios, sem usar PROCV.

Por essa você não esperava não é? Espero poder ajudar vocês em breve, mais e mais, comentem, divulguem, entrem em contato, pois este blog é para vocês.

Forte abraço! Auf Wiedersehen!

Read Full Post »