EXCEL: Utilizando Tabela Dinâmica

Olá pessoal! Antes de terminar os posts "Perfumarias", vamos mudar um pouco de assunto?

Vamos falar sobre tabela dinâmica! Mas porque o nome tabela dinâmica? Porque você pode mudar a configuração da tabela dinamicamente. Ou seja, para cada dado que você precisar focar / analisar, você pode modificar a forma como os dados serão apresentados. Então, vamos responder com esse post as seguintes perguntas:

Quando utilizar?

Quando você possuir uma tabela com inúmeros dados e precisar analisá-los fazendo agrupamentos.

Como criar?

Para criar uma tabela dinâmica é preciso que você já tenha uma tabela cheia com dados. No meu exemplo, eu vou utilizar uma planilha fictícia com 3 colunas: Item, Valor e Classificação. Já já, vou mostrar para vocês em detalhes como criar a Tabela Dinâmica.

Como interpretar?

A interpretação  vai depender do que você quer analisar e do tipo de agrupamento que fizer. Em linhas gerais, é possível (com o exemplo que estou dando) analisar quantos itens A estão classificados com classificação BOA, RUIM e REGULAR e quais os valores dos mesmos. Isso vai ajudar o fabricante dos itens a entender se um item classificado várias vezes como RUIM está mais caro que um ITEM com bastante classificações BOAS, por exemplo. Este é apenas um exemplo. Existem inúmeras aplicações para a tabela dinâmica.

Bom, vamos lá! Então, com a tabela fictícia acima, vamos criar a tabela dinâmica. Para isso, vá na seção Insert >> Pivot Table (ou Tabela Dinâmica):

 

A seguinte tela será apresentada:

Clique no botão com uma flechinha vermelha referente ao campo Table/Range e selecione todo o intervalo da sua planilha:

Agora selecione o local onde deseja colocar a sua planilha dinâmica. Para isso, selecione a opção Existing Worksheet e em Location digite Sheet2!$A$1. Ficará assim:

Clique novamente no botão com a setinha vermelha e clique OK. Agora vá em Sheet2 e você verá o seguinte lá:

Do lado esquerdo está onde os dados analisados serão apresentados. Do lado direito estão os campos para você começar a "brincar".

Agora, vamos analisar os dados. Vou clicar, no lado direito, em ITEM e arrastá-lo para Row Labels. Agora ficou assim:

Agora, vou arrastar a CLASSIFICAÇÃO para Row Filter e VALOR para Values. Ficará assim:

Agora, selecione as células com valor (B4 a B6) e formate como Moeda (Caso tenha dúvidas como fazer isso, acesse o artigo http://owsheet.blogspot.com.br/2014/09/excel-perfumaria-i-formatacao-rapida.html).

Agora, lá em cima em CLASSIFICAÇÃO, clique na flecha para abrir as opções:

Vão aparecer as opções assim:

Clique naquela que você quer analisar. Vamos começar analisando a BOA:

Agora, mude para RUIM:

Veja que o valor dos itens classificados como RUIM são maiores dos que os classificados como BOA. Pode ser que esteja acontecendo 2 coisas: Ou existem mais itens RUIM e a soma dos valores ficou maior que os de classificação BOA ou os itens RUIM são mais caros que os BOA. Vamos descobrir?

Arraste ITEM para Values:

Putz! A contagem de itens também ficou formatada como moeda. Selecione as células C4 a C6 clique no botão que é uma vírgula. Ele fica na sessão Home:

Pronto!

Agora, mude a classificação para BOA novamente:

Veja que a quantidade de itens é exatamente a mesma, porém os de classificação RUIM estão mais caros do que os de classificação BOA.

Podemos deixar mais evidente arrastando CLASSIFICAÇÃO para Row Labels:

Veja que para o item A, os de classificação BOA estão mais caros do que os RUIM, já que a quantidade de itens é a mesma. Com os itens B e C a mesma coisa acontece.

Podemos fazer uma outra análise: Quantos itens existem dentro de cada classificação. Para zerar a sua analise inicial, arraste tudo o que está em Row Filter, Column Labels, Row Labels e Value para cima, um a um.

Agora, arraste para Row Labels CLASSIFICAÇÃO e depois ITEM. Arraste o ITEM para Values também. Ficará assim:

Temos a mesma quantidade de itens classificados como BOA e RUIM.

Viu só como fica fácil analisar dados com a planilha dinâmica? Imagina como você faria essa análise sem a tabela dinâmica? Impossível!

Espero que tenha ficado mais claro para vocês como e quando se usa a tabela dinâmica. Não deixem de postar suas dúvidas e o que mais desejam saber sobre esse e outros assuntos.

Nos vemos no próximo post!

OBRIGADA!! Mais de 1000 Acessos!!

Gente, é com muito orgulho que anuncio que o blog teve mais de 1000 acessos desde a sua criação em final de Agosto/2014.

MUITO OBRIGADA A TODOS!!!

Não deixem de comentar suas dúvidas e postar aqui também (ou nas redes sociais) qual o assunto sobre o Excel ou pacote Office vocês gostariam de ver aqui no blog.

Essa semana terá novo post. Aguardem!!!

EXCEL: Perfumaria II

Olá pessoal! Vou publicar uma série de posts falando  sobre alguns pontos que são considerados "perfumaria" mas que fazem muita diferença na apresentação da planilha.

É importante que você seja capaz de elaborar uma planilha que todos os seus colegas de trabalho entendam e achem amigável. Não tem coisa pior do que você receber uma planilha complicada, que não é auto explicativa e, além de tudo, sem graça.

Os pontos que vou passar nessa série de posts para vocês são:

1. Formatação rápida (Merge Cells, Taxado, Moeda, Porcentagem e etc) - Já Falamos no post anterior. Acesse http://owsheet.blogspot.com.br/2014/09/excel-perfumaria-i-formatacao-rapida.html para relembrar.
2. Como a escolha de fontes e cores podem transformar sua planilha num lixo ou numa jóia - Vamos falar agora!!
3. Como o uso de um gráfico pode transformar sua planilha
4. Uma imagem fala mais do que mil palavras
5. Como não custa nada fazer um trabalho mais bonito e caprichado

Então vamos falar sobre o item B acima: Como a escolha de fontes e cores podem transformar sua planilha num lixo ou numa jóia.

Vamos usar o mesmo exemplo da planilha do post anterior, sem formatação. Mas antes, vocês lembram da planilha sem formatação?  Era assim:

Esse é o exemplo do LIXO! Nunca faça uma planilha assim, pelo amor de Deus!!

Nesse artigo, vou ensiná-los a deixá-la assim:

Bom, vamos começar centralizando o título. Selecione  B1 a G1 e clique em Mesclar e Centralizar (se estiver com dúvidas, acesse o artigo anterior http://owsheet.blogspot.com.br/2014/09/excel-perfumaria-i-formatacao-rapida.html

Detalhe: Tem que selecionar até o G1, pois vamos inserir uma coluna nova.

Coloque o Título em Negrito, Itálico e escolha a fonte Times New Roman tamanho 20.

Legal! Agora que o título já está centralizado, vamos adicionar a coluna nova.

Vá em G3 e digite "%Utilizada". Ficará assim:

 

Agora, como num toque de mágica, vamos formatar a planilha, colocar cor e dar vida a ela! Selecione B3 a G12. Sua seleção deverá ser assim:

A parte mais escurecida acima é a sua seleção. Agora, vá em Home (ou Página Inicial) e clique nesse botão:

Existem vários estilos prontos, mas eu vou usar esse aqui:

Pronto! Olha como fica:

Agora, para incrementar e ficar mais funcional, vamos inserir alguns dados mais abaixo, por exemplo, a partir de F14. A sua coluna F deverá ficar assim:

Resumindo: inclui informações como Receita Salário, Receita Vale, Total Receitas, Total Despesas (Salário), Total Despesas (Vale), Total Despesas e Saldo. Agora vou explicar cada uma dessas linhas:

Receita Salário (F14): Normalmente, quando se é registrado, recebemos 2 pagamentos no mês. Aqui nós deveremos colocar a receita líquida do pagamento feito no começo do mês.

Receita Vale (F15): Aqui devemos entrar com o valor do vale, normalmente pago no dia 20. Mas no meu caso, estou considerando dia 15.

Total Receitas (F16): Nesta célula colocaremos uma fórmula para somar as receitas de Salário e Vale. A fórmula usada será SUM ou SOMA, depende da linguagem do seu Excel.

Total Despesas (Salário) (F17): Aqui nós vamos somar todas as despesas que vencem entre o recebimento do salário e do vale. Ou seja, tudo aquilo que será pago com o dinheiro do começo do mês.

Total Despesas (Vale) (F18): Aqui nós vamos somar todas as despesas que vencem após o dia 20, dia do recebimento do Vale. No meu caso, dia 15.

Total Despesas (F19): Aqui vamos somar as despesas de salário e vale.

Saldo (F20): Aqui faremos uma subtração entre as células Total Receitas - Total Despesas.

Pronto, após essa rápida explicação, vamos inserir os dados correspondentes na coluna G. Sua coluna G ficará assim:

Acompanhando o que escrevemos na coluna F, vamos ter a equivalência na coluna G. Veja abaixo:

G14: R$ 2500,00. Este é o valor recebido no começo do mês, denominado antes como Receita Salário. No meu caso fictício, meu salário entrará dia 1.

G15: R$ 2000,00. Este é o valor recebido no dia 15, denominado antes como Receita Vale.

G16: Aqui temos a fórmula =SUM(G14: G15). Ou seja a soma das receitas. Se seu Excel estiver em português a formula será =SOMA(G14: G15)

G17: Aqui é um pouquinho mais difícil. Repare que na planilha existe uma coluna chamada Dia Vencimento. No meu caso, tudo que vencer entre dia 1 e 14 será pago com Salário e tudo o que vencer entre dia 15 e 30 será pago com Vale. Veja a minha planilha:

Seguindo essa lógica, devo colocar na G17 a seguinte fórmula: =E10+E9+E8+E6+E4. Ou seja, E4 é o aluguel que vence dia 10. E assim por diante.

G18: Seguindo a lógica da linha de cima, devo colocar na G18 a seguinte fórmula: =E12+E11+E7+E5. Repare que são os itens que vencem entre dia 15 e 30.

G19: Nesta célula devemos colocar a soma das despesas: =SUM(G17:G18)

G20: Na G20 colocaremos o saldo e, para calcular o saldo, devemos subtrair as despesas das receitas. Então a fórmula ficará assim: =G16-G19. Pronto! Agora vamos voltar para a perfumaria.

Para que as linhas fiquem intercaladas com cinza e branco, faça assim: Selecione na tabela maior uma célula em cinza. Pode ser a C8 (Seguro). Ai você clicará neste botão aqui:

Esse botão se chama "Pincel de Formatação". E o que ele faz? Bom ele copia somente a formatação da célula selecionada. Eu disse somente formatação, ele não copia os dados. Agora depois de ter pressionado esse botão, você seleciona a célula  F14. Isso irá copiar a formatação da célula. Faça a mesma coisa para as células F16, F18 e F20.

Para formatar as células G14 a G20, selecione a célula E5, pressione o botão Pincel de Formatação e selecione a célula G14. Faça a mesma coisa para as Células G16, G18 e G20. Sua tabela deverá ficar idêntica a minha, logo acima.

Agora, com isso feito, vamos colocar uma mini barra de status. A ideia aqui é visualizar quanto da sua receita está sendo utilizada para pagar os vários tipos de gastos. Para isso é preciso dividir o valor de cada gasto pelo valor total da receita.

Então, na célula G4, devemos colocar a seguinte fórmula: =E4/$G$16.

Você com certeza está se perguntando "Porque raios ela colocou o $ antes do G e do 16???". Isso serve apenas se você tem a intenção de copiar esta fórmula para outras células e deseja que todas as outras células fixem a G16. Se você não fizer isso, o resultado sairá errado.

No nosso caso, todos os valores na coluna E deverão ser sempre divididos pelo total da receita, que está na G16. Por isso fixamos a G16.

Com a fórmula pronta na G4, copie a célula G4, selecione G5 a G12, clique com o botão direito do mouse e você verá a opção "Opções de Colagem". Clique em Colar Especial. A seguinte tela será apresentada:

Selecione Fórmulas e dê OK. 

Pronto! A fórmula foi copiada para as células que você selecionou sem "bagunçar" a formatação. Agora transforme os valores de G4 a G12 em porcentagem e adicione as mini barras de status. Se você não se lembra mais como faz isso, veja o artigo http://owsheet.blogspot.com.br/2014/08/excel-criando-mini-barras-de-status.html

Bom gente, é isso! Ficou um pouquinho extenso, mas é porque não é fácil formatar planilha. E olha que fizemos da forma mais simples: usando formatações prontas do Excel.

Espero que tenham gostado do artigo. Nos vemos no próximo!

EXCEL: Perfumaria I (Formatação Rápida)

Olá pessoal! Vou publicar uma série de posts falando  sobre alguns pontos que são considerados "perfumaria" mas que fazem muita diferença na apresentação da planilha.

É importante que você seja capaz de elaborar uma planilha que todos os seus colegas de trabalho entendam e achem amigável. Não tem coisa pior do que você receber uma planilha complicada, que não é auto explicativa e, além de tudo, sem graça.

Os pontos que vou passar nessa série de posts para vocês são:

Perfumaria I - Formatação rápida (Merge Cells, Taxado, Moeda, Porcentagem e etc)

Perfumaria II - Como a escolha de fontes e cores pode transformar sua planilha num lixo ou numa joia

Perfumaria III - Como o uso de um gráfico pode transformar sua planilha

Bom, vamos começar então com o item A acima. Vou dar um exemplo agora de uma planilha sem formatação nenhuma:

Olha como fica ruim de trabalhar. Os números não tem formatação nenhuma. Não se sabe o que é título, onde estão os nomes dos campos. Enfim, uma zona!

Vamos começar o processo de transformação dessa planilha com algumas formatações rápidas. Veja como é simples. Primeiro o Título da Planilha "Planilha de Gastos Pessoais" precisa ficar centralizado. Então selecione de A1 a E1 e clique nesse botão:

Agora vai ficar assim:

Agora vamos formatar as células do campo Valor. Selecione de D4 a D12 e clique nesse botão:

Isso fará com que o campo fique formatado para moeda. Veja:

Legal! Agora  vamos ajustar a largura de todas as colunas de acordo com o tamanho do texto de cada uma. Clique com o mouse no canto superior esquerdo, que fica logo ao lado esquerdo da coluna A e logo acima da linha 1:

Repare que isso fez com que tudo ficasse selecionado. Agora posicione (apenas posicione, não clique em nada ainda) seu mouse para a divisão entre a coluna A e B. O cursor do mouse fica igual a esse pretinho ai em abaixo:

Quando o cursor ficar assim, ai você clica duas vezes. Pronto! Colunas ajustadas! Veja o resultado:

Agora, imaginem que a conta fixa Aluguel já foi paga. Você pode usar o tachado para criar um risco em cima da palavra.  Selecione de A4 ao E4 e clique nesse botão em vermelho:

A seguinte tela será aberta:

Selecione Strikethrough e clique em OK. Pronto!  As palavras ficaram riscadas. Veja:

Você também poderia formatar uma célula, ou um conjunto de células, para porcentagem. Assim todo o número que for inserido será considerado porcentagem automaticamente. Basta selecionar as células que deseja usar essa formatação e clicar num botãozinho com o símbolo %.

Pronto! Agora é só terminar com a perfumaria.

No próximo post  vou mostrar como a escolha de fontes e cores pode transformar sua planilha num lixo ou numa joia. Não percam!

EXCEL: Criando gráficos parte II

Olá pessoal! Vamos aproveitar que falamos recentemente sobre gráficos e vou passar para vocês um "macete" para  que as barras no gráfico fiquem com uma cor diferente quando está acima ou abaixo de uma meta. Visualmente, isso é muito legal, pois já dá a idéia do que está ruim e do que está bom. Bom, vamos lá!

Vamos utilizar a mesma planilha fictícia de vendas utilizada no artigo que falava sobre como criar gráficos.

Mas precisamos fazer algumas alterações na planilha. Veja como era:

 

  

Agora vejam como ficou com as modificações:

Ai você vai se perguntar: "Tá, mas que raios essa menina fez???". Vou explicar agora!

Eu inseri duas colunas: Acima da Meta e Abaixo da Meta. Vou explicar com calma para que você entenda direitinho.

Na coluna Acima da meta, eu usei uma fórmula chamada IF (em português, SE). A lógica é mais ou menos assim:

"Se a Venda for Maior ou Igual a Meta, mostra o valor da Venda. Senão, deixa em BRANCO". Transcrevendo isso para a linguagem do Excel, fica assim:

=IF(B3>=C3;B3;""), onde B3 é o valor da venda de janeiro e C3 é a meta de Janeiro.

Copia essa formula da linha 3 para as outras linhas até D14.

Agora, na coluna Abaixo da Meta, vamos colocar a formula com a seguinte lógica:

"Se a Venda for Menor ou Igual a Meta, Mostra o Valor da Venda. Senão, deixa em BRANCO. Novamente, transcrevendo essa lógica para a linguagem do Excel, fica assim:

=IF(B3<=C3;B3;""), onde B3 é onde está o valor da venda de janeiro e C3 é a meta de Janeiro.

Copia essa formula da linha 3 para as outras linhas até E14.

Agora repare bem: Quando o valor da venda é maior que a meta, ele está mostrando o valor na coluna Acima da Meta. Quando o valor da venda é abaixo da meta, ele mostra o valor na coluna Abaixo da Meta. Para jogarmos isso num gráfico, vá em Insert e na seção Graph, insira um gráfico 2D de barras. O gráfico inserido será totalmente estranho. Vamos corrigir agora. Clique com o botão direito em cima do gráfico e selecione Select Data. A tela a seguir será apresentada:

Clique em Add. Na tela que abrir, no campo Series Name clique no botão bem ao lado do campo e selecione D2. No campo Series values, clique no botão bem ao lado do campo e selecione D3 até D14.

Clique OK.

Faça a mesma coisa, mas agora selecionando os dados da coluna E:

E a mesma coisa para coluna C:

Click Ok. No gráfico, selecione a barra verde e clique com o botão direito do mouse. Selecione Change  Series Chart Type:

Selecione o gráfico de linha:

O segundo da esquerda para a direita.

Seu gráfico agora ficará assim:

Tá ficando bonito!! Perceba que aquelas barras que estão abaixo da linha verde estão vermelhas. As que estão acima estão azuis. Na verdade é isso, a história acaba aqui. Mas vamos incrementar mais esse gráfico! Selecione a barra azul e clique com o botão direito do mouse. Selecione Format Data Series:

Preencha como está na figura acima e clique em close. Clique em cima de onde está escrito Chart Tittle e digite "Resultado Vendas do Ano"

Pronto! Agora está pronto!

Viram como fica bem legal desse jeito? E é dinâmico, ou seja, ao digitar uma venda menor que a meta a barra automaticamente ficará vermelha!

Escrevi esse post com a ajuda desse artigo aqui:
http://guiadoexcel.com.br/grafico-com-cores-diferentes-para-abaixo-ou-acima-da-meta
Dá uma olhada nesse blog porque é ótimo também!

Até o próximo post!