Tabela Dinâmica: Como fazer agrupamentos diferentes

Algumas vezes precisamos criar mais de uma tabela dinâmica no mesmo arquivo do Excel com os mesmos dados, porém com agrupamentos diferentes. Em uma tabela dinâmica podemos precisar observar os resultados agrupados mensalmente, enquanto em outra tabela dinâmica podemos observar os dados agrupados diariamente. Vamos analisar essa situação através de um exemplo.
Na figura a seguir temos uma relação de pagamentos efetuados por clientes com as datas e os valores das compras.
td1
Vamos inserir uma tabela dinâmica, onde na área de linha colocaremos as datas das compras e na área de dados os valores, observe que criamos a tabela dinâmica na planilha TD1.
td2
Agora vamos criar outra tabela dinâmica, com os mesmos campos da anterior, mas agrupadas por trimestres e inserida na planilha TD2:
td3
Até aqui parece que está tudo bem, entretanto se voltarmos para a planilha TD1 perceberemos que ela também foi agrupada em trimestres:
td4
Este é o problema, quando se agrupa uma tabela dinâmica, a outra recebe o mesmo agrupamento. Como fazer para termos dois agrupamentos diferentes no mesmo arquivo?
Há mais de uma maneira de se resolver essa situação. Apresentaremos aquela que consideramos a mais flexível. A ideia é nomear o intervalo de dados e relacionar as tabelas dinâmicas com esses novos nomes.
1. Selecione todo o intervalo de dados usado para elaborar a tabela dinâmica. Nomeie esse intervalo, por exemplo Dados1.
td5
2. Mantenha a seleção e nomeie o mesmo intervalo com outro nome, por exemplo Dados2.
td6

3. Vá até uma das tabelas dinâmicas e escolha a opção para alterar a fonte de dados.
td7

4. Digite um dos nomes criados anteriormente, por exemplo Dados1 e pressione OK.
td8

5. Faça o mesmo com a outra tabela dinâmica, só que use o outro nome para o intervalo de dados (Dados2).
6. Agora experimente alterar os agrupamentos e você verá que eles são independentes.

Convertendo data padrão americano (ano/mês/dia) para data no formato brasileiro (dia/mês/ano)

Confira o artigo sobre conversão de datas no Excel:

http://curso-de-excel-mpr.blogspot.com/2011/10/convertendo-data-padrao-americano.html

Clique aqui – Curso de Excel Avançado – MPR Informática

Teste de Excel

Qual curso de Excel é mais indicado para você?

Veja se você está preparado para atender o mercado.

O Excel é um programa muito utilizado pelas empresas e conhecer seus recursos de maneira abrangente permitirão organizar, planejar, analisar e apresentar as informações de forma organizada e com excelente aproveitamento do tempo.

Não perca seu tempo e solicite já uma avaliação para verificar o seu conhecimento.

Download do teste de Excel: Teste de Excel

Faça o download da avaliação e encaminhe para correção. Você receberá em seu e-mail a indicação de qual curso de Excel da MPR Informática é mais apropriado de acordo com o seu nível de conhecimento.

Clique aqui – Curso de Excel – MPR Informática

Função SomaSe

Para entender a função SomaSe clique no link abaixo para fazer o download do arquivo disponibilizado para testar o recurso.

http://mprinformatica.com.br/download/somase.xls

A função Somase possui três parametros que deverão ser inseridos na fórmula para que a mesma funcione corretamente.
1º parametro – intervalo: deverá ser selecionado a região de células onde sera analisado se o critério retornara true ou false.
2º parametro – critérios: deverá ser inserido o critério a ser analisado para que a soma ocorra.
3º parametro – intervalo_soma: deverá ser inserido o intervalo de células onde ocorrera a soma caso o critério seja atendido.

Ex.: no arquivo anexado desejamos saber qual o valor total das notas emitidas dia 02/01/2006.
Para não termos que ficar conferindo data por data para poder depois obter a soma basta usar a função Somase.

A Fórmula ficará assim:
=SOMASE(C5:C9;”02/01/2006″;F5:F9)

O intervalo selecionado no primeiro parametro contem as datas em que foram emitidas as notas promissórias.
o critério sera a data desejada. Obs.: o critério sempre vem entre aspas dupla.
e o intervalo_soma sera a região de células que contem os valores a serem somados.

No arquivo anexado tambem foi usado a soma simples para testar se o resultado obtido foi o correto.
basta usar a função soma e selecionar os valores em que a data seja igual a “01/02/2006”.

A Fórmula ficará assim:
=SOMA(F5;F7;F8)

na Planilha “Plan2” existe uma cópia da tabela usada como exemplo para que você possa testar o recurso.

Procura Vertical (PROCV) com imagens

A função de procura vertical (PROCV ou VLOOKUP em inglês) está entre as mais solicitadas pelos nossos alunos do Curso de Excel (http://www.mprinformatica.com.br/excelintermediario.html), isso se deve ao fato de frequentemente organizarmos informações em tabelas e precisarmos localizar e extrair algum dado específico.
A função PROCV extrai qualquer tipo de dado que esteja em uma célula, mas e se precisarmos extrair uma imagem? Ou seja, se quisermos procurar por um item e depois visualizar a imagem correspondente? Como exemplo, imagine que possuímos uma tabela que possua as informações de nossos clientes e como a tabela é extensa, ficaria muito prático se ao selecionarmos um cliente pudéssemos ver a foto desse cliente.
As células do Excel não armazenam nem exibem imagens, porém como as imagens são arquivos de computador, podemos armazenar o caminho das imagens nas células, ou seja a sua localização em disco, conforme mostrada na figura abaixo.

Colocamos esses dados numa planilha chamada Tabela. Nessa tabela simplificada, temos uma coluna Número, que pode ser o número de cadastro do cliente, o campo Nome – nome completo do cliente, e a coluna Caminho, que é a localização do arquivo de imagem com a foto do cliente. Neste exemplo, haverá uma pasta Imagem no drive C:, com todas as fotos dos clientes, o que torna bastante simples a organização e como os arquivos de imagem não foram incorporados na planilha do Excel, ela ficará muito leve.

Além da planilha com os dados onde serão feitas as pesquisas, criamos uma planilha chamada Principal, onde será efetuada a pesquisa, ou seja, onde usaremos a função PROCV. Ela terá a seguinte aparência.

onde:
> As células A1:A4 são apenas descrições
> A célula B1 é o local onde digitaremos o número do cliente, para que sejam exibidos o nome e a imagem do mesmo.
> A célula B2 possui uma simples busca pelo nome do clinte com a seguinte fórmula: =PROCV(Principal!B1;Tabela!$A$4:$C$9;2;FALSO)
> A célula B3 possui a busca pelo caminho da imagem. A fórmula é: =PROCV(Principal!B1;Tabela!$A$4:$C$9;3;FALSO)

Relembrando, as células do Excel não exibem imagens, então precisaremos de um recurso que faça isso. Escolhemos usar um controle ActiveX, para inseri-lo faça o seguinte:
– Ative a guia do Desenvolvedor (Botão do Office; Opções do Excel; ativar a caixa Mostrar guia Desenvolvedor na Faixa de Opções; OK.
– Na guia desenvolvedor, clique no botão Inserir (ícone de maleta com ferramentas) e clique sobre o controle Imagem (ActiveX);
– Agora desenhe um retângulo, arrastando o cursor do mouse de um vértice até o vértice oposto. O tamanho da imagem pode ser mudado depois através dos círculos em torno desse retângulo.

O local onde será exibida a imagem está pronto, veja o nosso exemplo na figura anterior. Esse controle precisa ser configurado, para isso clique no botão propriedades que está na guia Desenvolvedor. Altere as seguintes propriedades:
(Name) para imgfoto
PictureSizeMode para 1-fmPictureSizaModeStretch

Feche a janela de propriedades e desative o botão Modo de Design na guia Desenvolvedor.

A etapa final é elaborar um pequeno código em VBA para que a imagem seja atualizada sempre que um novo código for digitado. Para isso vá até o editor do VBA através da combinação Alt+F11 e dê um clique duplo na planilha Principal, conforme indicado na figura a seguir:

Do lado direito haverá uma grande área em branco para inserção de código, copie e cole o seguinte trecho:

Private Sub Worksheet_Change(ByVal Target As Range)
‘Lembrando que target é a referência para a célula modificada

‘verifica se a célula que contém o número do cliente foi modificada
‘Nesse caso é a célula B1
If Target.Row = 1 And Target.Column = 2 Then
‘Carrega imagem de acordo com o caminho indicado na procura vertical
‘A função LoadPicture carrega um caminho de imagem na propriedade Picture
‘O seu uso é necessário
imgfoto.Picture = LoadPicture(Range(“B3”).Value)
End If
End Sub

Feche o editor de VBA e salve a planilha habilitando-a para usar macros: Salvar Como e depois escolher a segunda opção “Pasta de Trabalho habilitada para Macro do Excel”.

Antes de experimentar o sistema, crie uma pasta com o nome Imagens, no drive C: (se for outro local, mude também o caminho na tabela) e copie os arquivos de imagem listados na tabela, como exemplo, eu disponibilizei para download um arquivo compactado com as imagens usadas no exemplo (imagens.zip)

Agora teste o sistema, digitando valores na célula B1 da planilha principal.
(Este exemplo pode ser baixado neste link procura vertical com imagens.xlsm)

Segundo Eixo de Valores

Para criar um gráfico com dois eixos de valores selecione o intervalo com o qual quer gerar o gráfico e aperte a tecla de função F11.

O gráfico foi gerado.

Clique com o botão direito na série de dados que se deseja colocar como eixo secundário no gráfico gerado e acione a opção Formatar Série de Dados.

Abrirá a janela Formatar Série de Dados, acione a opção Eixo Secundário.

Feche a janela Formatar Série de Dados.

Repare que a série selecionada fica a frente das outras séries e outro eixo de valores é adicionado ao gráfico.

Para uma melhor visualização vamos alterar o tipo de gráfico da série que ficou no segundo eixo secundário para linha.

Agora possuímos um gráfico com dois eixos de valores.

André Lanzillotta
Clique aqui – Curso de Excel Avançado – MPR Informática

Separando dados agrupados em colunas independentes

Imagine uma tabela que contenha o campo Endereço conforme mostra a ilustração abaixo:

O objetivo, é separar cada parte do endereço e obter um resultado assim:

Para obter o resultado acima, você pode proceder por etapas usando o recurso do Localizar/Substituir.

Inicialmente, deixe sempre a base original intacta, ou seja, a coluna com o endereço agrupado e copie para a coluna ao lado para realizar a edição.

1º – Isolando a primeira parte:
Com a coluna que será editada (que contém todo o endereço), acione o comando Localizar/Substituir e digite:

Localizar: R.*
Substituir por: R.

Faça a primeira varredura e você verá que os endereços com iniciam com R. já ficarão isolados.

Repita o procedimento para isolar Av.

Localizar: Av. *
Substituir por: Av.

2º – Isolando a segunda parte do endereço:

Copie novamente a base completa para outra coluna para realizar a substituição.
Acione o Localizar/Substituir e digite:

Localizar: R.
Substituir por:

Faça as substituições

==> esse procedimento irá retirar R. e Av. do endereço

Localizar: Av.
Substituir por:

*** Nessa etapa, copie a coluna para usá-la como referência e isolar os números posteriormente.

Continuando, para isolar o endereço, acione Localizar/Substituir.

Localizar: , *
Substituir por:

Faça as substituições

—————————————–

Última etapa: isolando os números

Acione Localizar/Substituir

Localizar: *,
Substituir por:

Faça as substituições. Ao término desses procedimentos, você obterá o resultado abaixo:

Clique aqui – Curso de Excel – MPR Informática