Apostilas de VBA para download

Olá Pessoal!

Estou passando por aqui só para avisar que disponibilizei na barra lateral do blog três apostilas de VBA para download.

Criei essas apostilas quando trabalhava na diretoria de Controladoria da Oi, para dar aula de VBA para algumas pessoas da minha equipe.

Usando os ensinamentos dessas apostilas, vocês conseguirão otimizar bastante o Excel, criando planilhas bem interessantes!

Espero que esse material seja de grande utilidade, principalmente para aqueles que estão iniciando no mundo do VBA!!!!

 

Abs, Dr. Excel

Teclas de Atalho no Excel

Oi Pessoal!

Segue abaixo uma lista com alguns dos atalhos que considero importantes para usuários avançados de Excel, como o pessoal de Controladoria e Finanças!

Parece besteira, mas a troca do mouse pelo teclado faz muita diferença, principalmente para quem utiliza o Excel diariamente nas suas oito horas de trabalho. Você realmente consegue produzir mais em menos tempo. Mas é preciso ter esses atalhos na ponta da língua, ou melhor, na ponta dos dedos!

Essa é uma das coisas que as versões 2007 e 2010 do Excel apresentaram de melhoria em relação à versão 2003. A forma como você acessa os menus/ comandos a partir do teclado. Nessas duas últimas versões, reparem que ao selecionar a tecla Alt os menus do Excel ficam identificados por letras. Então, basta ir clicando nas letras até encontrar o comando que você precisa.

Bom, como os comandos do Excel podem ser acessados conforme citei acima, procurei incluir nessa lista alguns atalhos que considero indispensáveis e que não são acessados pelo formato acima (tecla Alt + letra de acesso ao menu desejado).

Abrir arquivo CTRL+A

Colar CTRL+V

Copiar CTRL+C

Desfazer CTRL+Z

Exibir Auto-filtro da coluna atual ALT+seta para baixo

Fechar Auto-filtro da coluna atual Esc

Fechar janela CTRL+W

Fechar o Excel Alt+F4

Formatar células CTRL+1

Imprimir CTRL+P

Inserir Gráfico F11

Inserir linhas ou colunas SHIFT+CTRL+ +

Inserir planilha SHIFT+F11

Ir para F5

Ir para o final da planilha CTRL+End

Ir para o início da planilha CTRL+Home

Localizar e substituir CTRL+F

Mover para a planilha anterior CTRL+PgUp

Mover para a próxima planilha CTRL+PgDn

Nova pasta de trabalho CTRL+O

Recortar CTRL+X

Selecionar a planilha atual e a anterior SHIFT+CTRL+PgUp

Selecionar a planilha atual e a seguinte SHIFT+CTRL+PgDn

Selecionar as células que contêm fórmulas com referência direta à célula ativa CTRL+]

Selecionar as células que contêm fórmulas com referência direta ou indireta à célula ativa CTRL+SHIFT+}

Selecionar as células visíveis na seleção atual ALT+;

Selecionar até a última célula usada CTRL+SHIFT+End

Selecionar até o início da linha SHIFT+Home

Selecionar até o início da planilha CTRL+SHIFT+Home

Selecionar até última célula não vazia na mesma linha ou coluna CTRL+SHIFT+tecla de direção

Selecionar célula a célula SHIFT+Seta

Selecionar coluna ativa CTRL + barra de espaços

Selecionar linha ativa SHIFT + barra de espaços

Selecionar tudo CTRL+T

Selecionar uma tela para baixo SHIFT+PgDn

Selecionar uma tela para cima SHIFT+PgUp

Gostaria de destacar o atalho Alt+; (Selecionar as células visíveis na seleção atual). Acho esse atalho uma das melhores ferramentas para incluirmos fórmulas, alterarmos formatações etc. em apenas algumas células de uma determinada seleção.

Abs, Dr. E

P.S. Gostaria de pedir desculpas, mas infelizmente não estou conseguindo responder todos os e-mails com dúvidas que venho recebendo. Por isso, peço que não usem o espaço de comentários do post para enviar dúvidas.

Análise de Resultados! (fórmulas: Indireto, SomarProduto e SomaSe)

Oi Pessoal!

Recebi um comentário no post Índice+Corresp (PROCV mais eficiente!), enviado pelo Rafael Ovelha, falando sobre a fórmula Indireto. Após pesquisar melhor sobre essa fórmula em alguns fóruns, resolvi escrever esse post.

Criei uma planilha usando as fórmulas Indireto, SomarProduto e SomaSe, onde montei uma tabela resumo de despesas por período, com classificação por natureza (tipo de despesa). Quem fizer o download (vide * abaixo) do arquivo vai perceber que se trata de uma tabela simples, onde procurei demonstrar, de forma clara e objetiva, como criar planilhas inteligentes para análise/ controle de resultados.

É claro que podemos elaborar algo bem mais completo e complexo, entretanto, meu principal objetivo aqui no blog é dar uma ideia resumida de Excel avançado e VBA, com exemplos simples, mas que reflitam as atividades do pessoal de Controladoria/ Finanças voltadas para planejamento e controle de orçamento.

Então, vou deixar sempre a parte do “completo e complexo” por conta da criatividade e necessidade individual de cada um dos leitores que acompanham o blog!

* Vocês podem efetuar o download da planilha de apoio clicando aqui!

Bom, vamos começar pela fórmula SomaSe, que na minha opinião é a mais fácil das três.

Usando a tabela abaixo como exemplo, podemos obter o valor total das despesas classificadas como ESCRITÓRIO (na coluna B) no mês de fevereiro (coluna D), escrevendo o seguinte:

=SOMASE(B:B;”ESCRITÓRIO”;D:D)


Nesta expressão, pedimos ao Excel que efetue a soma de todos os valores da coluna D, que foram classificados como “ESCRITÓRIO” na coluna B. Em outras palavras, é como se a fórmula percorre-se toda a coluna B, desde a primeira linha, e considerasse na soma todas as despesas com escritório.

Neste exemplo, o resultado será 8.300, correspondente a soma dos seguintes valores: 5.000 + 1.500 + 1.200 + 600.

Agora vamos entender como funciona a fórmula SomarProduto, calculando o total de despesas com COMISSÕES nos meses de janeiro e fevereiro, conforme figura abaixo.

Para isso, devemos escrever o seguinte:

=SOMARPRODUTO(C1:E1;C9:E9)

Como o próprio nome sugere, a fórmula SomarProduto efetua a soma dos produtos dos intervalos de células que a compõe. Simplificando, podemos dizer que o Excel multiplica cada célula do primeiro range por cada célula do segundo range (células que estejam na mesma ordem nos dois intervalos) e, no final, soma todos os resultados das multiplicações. Assim, podemos concluir que, necessariamente, os dois ranges da fórmula devem possuir o mesmo tamanho.

Para este exemplo, a fórmula acima é equivalente à seguinte expressão matemática:

1×1.500 + 1×100 + 0x1.500 = 1.600

Agora sim, vamos falar da fórmula Indireto!

Quando procurei por essa fórmula nas funções do Excel, encontrei o seguinte texto descritivo: “Retorna uma referência indicada por um valor de texto”.

A partir daí, tive a ideia de usar esta fórmula para montar uma tabela comparativa de despesas por período, vinculando a escolha do mês de análise à posição (coluna) deste mês na planilha.

Exemplificando, como podem reparar na tabela abaixo, o mês de fevereiro encontra-se na coluna G. Então, para obtermos a soma de todos os valores desta coluna, basta escrevermos =SOMA(G:G).

Mas como efetuar uma soma dinâmica, onde poderemos alterar o mês de análise sem precisar alterar a fórmula de soma?

É nesta parte que entra a fórmula Indireto

O que fiz foi vincular cada um dos meses a uma posição (coluna na planilha) e incluir esta referência na soma dos valores, usando a fórmula Indireto. Com isso, a fórmula ficou da seguinte forma: =SOMA(INDIRETO(W7)). Onde o valor da célula W7 representa o range com os valores que queremos somar (G:G).

Obs: reparem que para alterar o valor da célula W7, usei a fórmula Procv, vinculando o mês selecionado a coluna onde estão as despesas desse mês. (para entender melhor como funciona a fórmula Procv, procure por esta fórmula na pesquisa do blog!

Para deixar a fórmula mais inteligente, troquei a fórmula Soma por SomaSe, criando um resumo das despesas por classificação. Além disso, usei a fórmula SomarProduto para calcular o total acumulado do período na coluna R, ou seja, a soma de janeiro até o mês selecionado.

Espero que esse post ajude bastante, principalmente o pessoal de Controladoria, na hora de montar análises para as apresentações mensais de resultado!

Abs, Dr. E

P.S. Gostaria de pedir desculpas, mas infelizmente não estou conseguindo responder todos os e-mails com dúvidas que venho recebendo. Por isso, peço que não usem o espaço de comentários do post para enviar dúvidas.

Índice+Corresp (PROCV mais eficiente!)

Oi Pessoal!

Neste post vou ensinar como a combinação das fórmulas ÍNDICE e CORRESP pode substituir a fórmula PROCV, fazendo buscas mais eficientes!

Essa combinação cria uma fórmula do tipo Procura e Referência, que é capaz de retornar resultados inclusive de células à esquerda da coluna onde efetuamos uma busca. O que não é possível com a PROCV, que só consegue mostrar valores de células à direita da coluna da busca.

Bom, primeiro vou explicar o funcionamento de cada uma das fórmulas separadamente, usando a tabela abaixo como exemplo.

Fórmula ÍNDICE

Retorna o valor da célula na interseção de uma linha com uma coluna em um dado intervalo de células.

Para a tabela acima, escrevendo a fórmula =ÍNDICE(A1:D5;2;3), obteremos como resultado o valor 31, correspondente a interseção da linha 2 com a coluna 3 dentro do range A1:D5.

Fórmula CORRESP

Retorna a posição de um item em uma determinada coluna.

Escrevendo a fórmula =CORRESP(“Débora”;B:B;0), o Excel mostrará o valor 4, correspondente a posição do nome Débora na coluna B.

Segue a composição da fórmula CORRESP para melhor entendimento.

Corresp(valor procurado; range de busca; tipo de correspondência)

Valor procurado: Débora

Range de busca: B:B

Tipo de correspondência: (no caso que estamos estudando sempre usaremos 0)

0 – correspondência exata

1 – é maior do que

-1 – é menor do que

Agora, vamos ver como funciona a combinação dessas fórmulas.

Como exemplo, devemos escrever a seguinte expressão para encontrar a idade da Débora:

=ÍNDICE(A1:D5;CORRESP(“Débora“;B:B;0);3).

Nesta expressão, estamos pedindo ao Excel que mostre o valor da terceira coluna do range A1:D5, quando encontrar o nome Débora na coluna B. Como resultado obteremos o valor 29.

É claro que obteríamos o mesmo resultado com a fórmula PROCV, escrevendo: =PROCV(“Débora”;B2:C5;2;0), mas se quiséssemos encontrar algum valor da coluna A, anterior a coluna de busca (coluna B), não seria possível usando a PROCV.

Aqui entra a substituição da PROCV para fazermos buscas mais eficientes…

Conforme falei no início do post, diferentemente da PROCV, a combinação ÍNDICE + CORRESP consegue mostra como resultado valores contidos em colunas à esquerda da coluna onde estamos efetuando a busca.

Assim, na mesma tabela, podemos obter o valor da coluna A relacionado ao nome Débora. Para isso, basta escrever a seguinte expressão:

=ÍNDICE(A1:D5;CORRESP(“Débora”;B:B;0);1)

Reparem que apenas trocando o 3 pelo 1 no final da expressão (coluna de referência do resultado desejado), teremos Ciências Sociais como resultado!

Abs, Dr. E

P.S. Gostaria de pedir desculpas, mas infelizmente não estou conseguindo responder todos os e-mails com dúvidas que venho recebendo. Por isso, peço que não usem o espaço de comentários do post para enviar dúvidas.

Combinação de gráficos – criando diferencial

Oi Pessoal,

Neste post vou falar de um gráfico bem simples, mas capaz de criar diferencial em suas apresentações!

Quando montamos apresentações, para facilitar o entendimento e prender a atenção do público, temos que focar na clareza e objetividade das informações que serão mostradas. Isso vale também na hora de criar gráficos no Excel para incluir nessas apresentações. Não adianta ser um expert em gráficos se você não consegue utilizar esse conhecimento para atender da melhor forma as expectativas do seu público-alvo.

Então, antes de criarmos um gráfico, acho importante sempre refletirmos sobre as seguintes questões:

Qual tipo de gráfico está mais alinhado ao meu objetivo? O que estou pretendendo: fazer uma comparação, mostrar uma distribuição, uma composição, mostrar tendências/ evoluções ou relacionamentos? Qual o tamanho da linha de tempo que vou usar? Quantos anos, meses, dias pretende mostrar/ comparar?

Agora vou tentar mostrar como é simples combinar dois gráficos num único, facilitando o entendimento do que você quer transmitir.

Como exemplo, vou usar a tabela abaixo para comparar as despesas mensais de uma empresa durante três exercícios (2008, 2009 e 2010).

Montei três gráficos, conforme figuras a seguir. Repare que o primeiro gráfico é do tipo Linhas com Marcadores, o segundo é do tipo Colunas Agrupadas e o terceiro é uma combinação dos dois anteriores.

Essa simples combinação é uma forma de facilitar o entendimento do gráfico por não exigir que você prenda sua atenção visual, única e exclusivamente, as cores relacionadas a cada ano (2008, 2009 e 2010).

Esse último gráfico combinado é bem interessante para compararmos, por exemplo, um período de despesas realizadas contra dois anos de planejamento. Neste exemplo específico, 2008 corresponde às despesas realizadas do ano anterior, 2009 ao forecast do ano atual e 2010 ao orçamento do próximo ano. Pensando bem, apesar de termos três períodos distintos no gráfico, nosso objetivo é efetuar uma comparação realizado vs planejado. Reforçando, onde 2008 representa o realizado e 2009/ 2010 o planejado. Por isso, usei o tipo Linha com Marcadores para destacar o realizado dos demais exercícios, que continuaram como colunas.

Para alterar uma das séries de dados, neste exemplo a do ano de 2008, basta seguir os seguintes passos:

Selecione uma das colunas desta série, conforme figura abaixo, clique na guia Design, clique no botão Alterar Tipo de Gráfico e logo após escolha o tipo Linha com Marcadores.

Buscando sempre uma boa impressão, formatando o gráfico acima, poderíamos ter o resultado abaixo.  Esse é um exemplo muito usado pela área de Controladoria para análise de Resultados!

Abs, Dr. E

P.S. Gostaria de pedir desculpas, mas infelizmente não estou conseguindo responder todos os e-mails com dúvidas que venho recebendo. Por isso, peço que não usem o espaço de comentários do post para enviar dúvidas.

Macro – definição e gravação

Oi pessoal!

Conforme prometido no post anterior, vou tentar explicar o que é Macro e dar um exemplo de gravação através do Excel.

A Macro, ou sub-rotina, é uma sequência de comandos e funções armazenados no VBA que pode ser criada, por exemplo, para automatizar uma tarefa que seria executada várias vezes no Excel. O que ajuda você a ganhar tempo e diminuir os erros que poderia cometer ao executar as mesmas tarefas repetidas vezes, já que o sistema fica encarregado de executar essa sequência de tarefas sempre na ordem correta e sem pular nenhuma etapa. É claro que devemos ter muito cuidado ao criar uma Macro para ter certeza que esta contempla todas as etapas do processo. Isso que dizer que a integridade das informações geradas depende principalmente do planejamento que o usuário faz antes de gravar a Macro, para certificar-se que todas as etapas foram contempladas.

 

Como exemplo, vamos supor que seguidamente precisemos formatar uma célula com Negrito, Itálico, fonte Verdana de tamanho 13 e cor vermelha. Ao invés de executar todos os comandos de formatação em cada célula, podemos criar uma Macro que aplique todos esses comandos num intervalo de células selecionado pelo usuário.

 

A Macro pode ser gravada etapa a etapa para realizar uma tarefa, mas antes de gravá-la, devemos planejar as etapas e os comandos que devem ser executados. Cometendo-se erros durante a gravação, as correções que também forem efetuadas no momento serão gravadas em seqüência. Então, lembrem-se da importância do planejamento do processo que citei no início do post!

Podemos executar uma Macro através do caminho Exibição -> Macros -> Exibir Macros, através de um botão específico, de uma combinação de teclas, de um botão de uma barra de ferramentas, a partir de um atalho no teclado, de um objeto gráfico em uma planilha etc. Porém com com exceção da primeira opção, que já existe na Barra de Menu do Excel, todas as outras dependem de programação VBA.

 

Agora vou dar um exemplo de gravação de Macro para vocês entenderem melhor como funciona na prática.

 

Vamos lá!

 

A Macro deverá formatar a célula atual com Negrito, cor de fonte vermelha e fundo amarelo. Gravar a Macro com o nome NegritoVermAmarelo.

1. Abra uma nova pasta no Excel.

2. Clique na célula A1.

3. Selecione o comando Exibição -> Macros -> Gravar Macro

 

4. Será exibida a janela Gravar macro, conforme figura abaixo.

5. No campo Nome da macro digite: NegritoVermAmarelo

6. No campo Tecla de atalho digite L. Observe que o Excel troca para Ctrl+Shift+L. Isso acontece porque a combinação Ctrl+L já deve estar associada a algum comando do Excel. Com isso estamos associando a combinação Ctrl+Shift+L a Macro NegritoVermAmarelo, ou seja, cada vez que quisermos executar essa Macro basta pressionar Ctrl+Shift+L.

7. No campo Descrição podemos registrar qualquer informação referente à Macro.

8. Clique em OK, que a gravação da Macro será iniciada e todos os comandos executados durante a gravação farão parte da Macro.

9. A barra (  ) é exibida na planilha do Excel e é utilizada para parar a gravação da Macro, que também pode ser finalizada através do caminho Ferramentas -> Macro -> Parar gravação.

10. Clique no botão (  ) para aplicar Negrito.

11. Na lista de Cores da fonte (  ) selecione Vermelho.

12. Na lista de Cores de fundo (  ) selecione Amarelo.

13. Clique no botão (  ) para encerrar a gravação da Macro.

 

 

Para executar a Macro NegritoVermAmarelo siga os passos abaixo.

 

1. Clique na Célula C7.

2. Pressione Ctrl+Shift+L.

3. A Macro FormataVermAmarelo será executada e as formatações definidas no código (Negrito, fonte vermelha e fundo amarelo) serão automaticamente aplicadas a Célula C7.

Obs: a Macro também pode ser executada através do comando Exibição -> Macros -> Exibir Macros (neste ponto você deve selecionar a macro na lista e clicar em “Executar”.

Abs, Dr. E

P.S. Gostaria de pedir desculpas, mas infelizmente não estou conseguindo responder todos os e-mails com dúvidas que venho recebendo. Por isso, peço que não usem o espaço de comentários do post para enviar dúvidas.

PROCV – dicas_01

Oi Galera,

Resolvi escrever esse post para dar algumas dicas sobre a fórmula procv.

Vamos lá.

Dica 1 – No início, quando comecei a usar a procv, aconteceu de a fórmula não encontrar um registro qualquer, mesmo tendo certeza que, pelo menos visualmente, o respectivo registro encontrava-se no range da busca. Bom, mais tarde acabei descobrindo que temos que tomar muito cuidado com a formação das células onde estamos trabalhando. Principalmente quando falamos de campos numéricos, pois é vital que o registro procurado tenha a mesma formatação do range de referência da fórmula para que o excel identifique a equivalência entre os valores e mostre o resultado corretamente.

Dica 2 – a procv finaliza a busca assim que encontra a primeira ocorrência do registro procurado no range de referência. Então, quando quisermos obter como resultado a soma dos valores de um determinado registro que aparece repetidas vezes em um intervalo qualquuer de células, sugiro utilizar a fórmula somase (veja posts sobre esta fórmula aqui no blog).

Dica 3 – A procv sempre efetua a busca na primeira coluna do range selecionado, só sendo capaz de mostrar como resultado células à direita desta coluna, conforme coluna de referência informada na fórmula. Isso quer dizer que: no exemplo de fórmula =procv(A2; B:D;2;0) o excel buscará o valor da célula A2 no range B:D, trazendo como resultado o valor da coluna C (coluna 2 a contar da coluna B) relacionado ao valor de A2.

Bom, por enquanto esses são os pequenos detalhes que lembrei da fórmula procv que nos dão o maior trabalho quando surgem. Lembrando que mais algum ponto, prometo incluir um novo post de título PROCV – Dicas_02.

Abs, Dr. E

P.S. Gostaria de pedir desculpas, mas infelizmente não estou conseguindo responder todos os e-mails com dúvidas que venho recebendo. Por isso, peço que não usem o espaço de comentários do post para enviar dúvidas.