Excel_avancado_-VBA_Tecnicas_profissiona

Prévia do material em texto

Excel avançado - VBA 
Técnicas profissionais de uso dos poderosos recursos do excel ! 
 
 
 
 
 
 
 
 
 
 
 
 
Formatação de planilhas 
 
Gráficos 
 
Validação de dados 
 
Filtragem de dados 
 
Fórmulas avançadas 
 
Automatização de tarefas usando Macros 
 
Programação em VBA 
 
E muito mais... 
 
 
 
DEIVIS FONTES 
 
 
 
Excel Avançado – Deivis Fontes 
 2
 
 
 
 
 
 
 
 
 
 
 
Excel Avançado – Deivis Fontes 
 3
 
 
Capítulo 1 - Formatação e gráficos 
 
Conhecendo o ambiente de trabalho do Excel 
 
Partindo do principio que o leitor deste livro já deve ter um conhecimento básico do Microsoft 
Office Excel, a versão utilizada será o Office 2003, mas os conhecimentos adquiridos podem 
ser usados em outras versões com algumas adaptações. Para acessar o Excel: clique no menu 
Iniciar >> Programas( ou todos os programas) >> Microsoft Office 2003 >> Microsoft 
Office Excel 2003, lembrando que isso pode variar conforme a versão do programa. 
Área de trabalho do Excel 
 
 
 
 
Vejamos agora as partes da área de trabalho do Excel: 
• Pasta de trabalho: 
• Barra de Menu: Menus para acessar as opções e recursos do programa, como salvar, 
formatar, abrir, etc... 
• Barra de ferramentas: Um atalho para as opções mais usadas, como Novo, Abrir, 
Salvar, etc... 
• Barra de formatação: aqui estão as principais ferramentas para formatação da planilha 
de cálculos. 
• Colunas: O Excel divide a sua área de trabalho Verticalmente em colunas, contando 
com letras, no total são 256 colunas. 
• Linhas: A área de trabalho também é dividida em linhas no sentido Horizontal, usando 
números para contagem, no total são 65.536 linhas. 
• Células: Uma célula é o encontro de uma coluna com uma linha, ou seja, os quadrinhos 
da planilha. 
 
 
Célula 
 
Excel Avançado – Deivis Fontes 
 4
 
 
 
 
Formatando as células 
 
Para formatar as células de uma planilha o caminho mais fácil é usar os atalhos da barra de 
formatação, onde é possível alterar letra, cor de fundo e bordas. 
 
 
 
 
Para ter mais opções de formatação acesse o menu Formatar >> Células , ou as teclas de 
atalho Ctrl + 1. 
 
 
Vejamos as opções desta janela: 
 
• Número: Permite definir um formato para o valor da célula, como por exemplo, datas 
por extenso, o usuário pode digitar a data neste formato: 01/01/1980 e quando sair da 
célula vai ficar a data por extenso: Segunda-feira, 01 de janeiro de 1980. 
 
 
Excel Avançado – Deivis Fontes 
 5
 
 
 
 
Alinhamento: Para mostrar o texto em duas linhas dentro de uma célula, é necessário 
formatar esta para que quebre o texto automaticamente. Siga os passos: 
1. Digite um texto em uma célula que ultrapasse o seu limite na coluna, mas observe que o 
texto mesmo passando por cima da célula do lado continua na célula em que foi 
digitado, selecione esta célula. 
2. Clique no menu: Formatar >> Células... 
3. Na janela que abrir escolha a guia Alinhamento 
4. Em Alinhamento do texto escolha: 
a. Horizontal: Centro 
b. Vertical: Centro 
5. Em Controle de texto marque a caixa de seleção: Quebrar texto automaticamente 
6. Clique no botão OK 
 
 
 
 
• Orientação: Podemos definir o texto na vertical, horizontal ou em diagonal, alterando os 
valores da caixa de seleção Graus. 
 
 
 
 
 
 
 
 
 
 
Excel Avançado – Deivis Fontes 
 6
 
 
• Bordas: Nesta opção podemos escolher o estilo da linha, cor, em que parte da célula a 
borda deve aparecer e as predefinições do contorno. 
 
 
 
• Padrões: Preenchimento da célula pode ser uma cor ou um padrão de textura, para 
escolher um padrão clique na caixa de seleção Padrão e escolha um dos padrões 
disponíveis. 
 
 
 
 
 
 
 
Excel Avançado – Deivis Fontes 
 7
 
 
• Proteção: Esta opção funciona apenas se a planilha estiver protegida com uma senha o 
que veremos mais adiante 
 
 
 
 
Ocultar linhas ou colunas 
 
Em muitas situações pode ser necessário ocultar uma linha ou coluna, por exemplo, em uma 
planilha de produtos na célula A1 inserimos o percentual de ICMS, para ter mais espaço visível 
na tela podemos ocultar a linha 1 onde encontra-se o valor do ICMS. Para isso clique no menu 
Formatar >> Linha >> Ocultar, siga o mesmo procedimento para as colunas escolhendo 
Coluna no menu Formatar 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel Avançado – Deivis Fontes 
 8
 
 
Mostrar linhas ou colunas 
 
Para reexibir colunas ou linhas ocultas digite uma célula da linha ou coluna desejada na caixa 
de nome e clique no menu Formatar >> Linha >> Reexibir. 
 
 
 
 
 
 
 
 
 
 
 
 
 
Formatação condicional 
 
Formatação condicional é um recurso que permite definir a formatação de uma célula de 
acordo com o seu conteúdo, por exemplo, se o total de ganhos de uma empresa for maior que 
zero a célula vai ficar com o preenchimento azul e caso contrário ficará vermelho. 
Para usar este recurso siga os passos: 
1. Selecione a célula desejada 
2. Clique no menu Formatar >> formatação Condicional... 
3. Na janela que abrir escolha o valor da célula é 
4. Ao lado escolha a condição do valor, este caso Menor ou igual a 
5. No próximo campo digite o valor que irá fazer a comparação no nosso exemplo 0 (zero) 
6. Clique no botão formatar... 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel Avançado – Deivis Fontes 
 9
 
 
 
7. E na janela que surgir clique na guia padrões para escolher a cor do preenchimento 
 
 
 
 
8. Agora falta adicionar a segunda condição que se o valor for maior ou igual a zero, 
para isso basta clicar no botão Adicionar >> e seguir os mesmos passos mostrados 
anteriormente. 
 
 
 
Para excluir uma ou mais formatações clique no botão excluir e escolha qual condição 
deseja excluir. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel Avançado – Deivis Fontes 
 10
 
 
 
 
 
Casas Decimais 
 
Muitas vezes ao realizar um cálculo de multiplicação ou divisão o 
resultado pode conter muitas casas decimais, tornando seu resultado 
um pouco confuso, para resolver isso definiremos a quantidade de 
casas decimais de uma célula usando os botões da barra de 
formatação. 
Para acrescentar casas decimais a um número clique no botão: 
 
 
 
Gráficos 
 
Para mostrar as informações da planilha em forma de gráficos siga os passos: 
 
1. Selecione o intervalo de células que será 
usado para exibir as informações no gráfico, 
para selecionar colunas aleatórias pressione a 
tecla Ctrl enquanto seleciona as células 
desejadas (ex.: células da coluna A e da 
coluna C) 
2. Clique no menu Inserir >> Gráfico 
3. Siga os passos do assistente de gráficos 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel Avançado – Deivis Fontes 
 11
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel Avançado – Deivis Fontes 
 12
 
Alterando Gráficos 
 
Para alterar as partes do gráfico clique com o botão direito do mouse na seção do gráfico que 
você deseja alterar e escolha a opção desejada, no exemplo abaixo Formatar série de dados, 
para alterar as colunas de dados. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel Avançado – Deivis Fontes 
 13
 
 
 
Capítulo 2 – Trabalhando com dados 
 
Importando dados de outros programas 
 
Com o Excel é possível importar dados de outro programa ou banco de um banco de dados 
este recurso pode ser muito útil para obter cadastros de clientes de uma empresa que usa o 
banco de dados Access, onde estão cadastrados os clientes desta empresa. 
Para importar os dados clique no menu Dados >> Importar dados externos >> Importar 
dados 
 
 
 
Procure pela pasta onde o banco de dados esta salvo 
 
 
 
 
 
 
Excel Avançado – Deivis Fontes 
 14
 
Escolha o Tipo de arquivo, caso não saiba escolha os tipos disponíveis até que seja mostrado 
algum arquivo na pasta onde esta seu banco de dados 
 
Clique no botão Abrir e será mostradaas tabelas do banco de dados, escolha uma tabela e 
clique em OK 
 
 
Agora você pode escolher onde deseja inserir estes dados: na mesma planiha ou em uma nova 
planilha 
 
 
 
 
 
 
 
 
 
 
 
 
Excel Avançado – Deivis Fontes 
 15
 
Validação de dados 
 
Quando desenvolvemos uma planilha é interessante validar os dados antes que sejam 
inseridos na planilha, para evitar erros como datas fora do período pré-determinado, definir 
uma quantidade máxima de caracteres em uma célula e muitas outras opções. 
 
Para acessar este recurso do Excel: 
1. Clique no Menu Dados >> Validação... 
2. As Guias disponíveis são: 
a. Configurações: Configurar o que e como será validado o valor 
b. Mensagem de Entrada: Exibe uma mensagem quando o usuário entrar em uma 
célula com validação de dados 
c. Alerta de Erro: Exibe uma mensagem quando o valor inserido não é válido 
 
 
Opções de Configurações 
 
Veja as opções de configuração dos 
dados, na caixa de opões Permitir 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Opções disponíveis: 
 
• Qualquer valor: Permite inserir qualquer valor. 
• Número inteiro: Apenas números inteiros 
• Decimal: Apenas números decimais (0 – 9) 
• Lista: Permite escolher os valores de um grupo de células na planilha para inserir em 
uma célula 
• Data: Valida datas 
• Hora: Valida Horas 
• Comprimento de texto: Permite definir a quantidade de caracteres que uma célula 
pode conter. 
• Personalizado: Permite escolher uma fórmula para validar os dados, não permitindo 
que esta fórmula seja alterada. 
 
 
Excel Avançado – Deivis Fontes 
 16
 
 
Validação de dados na prática 
 
Abra o Excel e crie uma planilha com as colunas: 
A. Produto 
B. Quant. 
C. Data 
D. Valor 
E. Total 
 
Preencha com os valores abaixo. 
 
 
 
 
 
 
Número Inteiro 
 
Vamos validar as células referentes a quantidade (B2 e B3), esta planilha controla um pequeno 
estoque de produtos de informática, as células referentes à quantidade podem ser do tipo 
inteiro, já que não existe 1,5 mouse, para validar estes dados selecione as células B2 e B3 e 
abra a janela de validação (Dados >> Validação...). 
 
 
1. Configurações 
2. Permitir: Número Inteiro 
3. Dados: Maior ou igual a 
4. Mínimo: 0 (zero) 
 
Obs.: Para não permitir valores em 
branco desmarque a caixa de 
seleção Ignorar em branco 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel Avançado – Deivis Fontes 
 17
 
 
 
 
 
Mensagem de entrada 
 
Para mostrar uma mensagem 
quando o usuário clicar na célula 
com validação escolha a guia 
Mensagem de entrada e marque a 
caixa de seleção: Mostrar 
mensagem de entrada ao selecionar 
a célula. 
Defina como Título: Quantidade e a 
Mensagem de entrada digite: Insira 
a quantidade do produto em 
números inteiros. 
 
 
Alerta de erro 
 
Esta opção mostra uma mensagem 
quando o valor inserido não é válido 
1. Escolha um estilo neste caso 
Parar 
2. Título: Valor inválido 
3. Mensagem de erro: A 
quantidade do produto deve 
ser um número inteiro como 
1, 2, 20... 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel Avançado – Deivis Fontes 
 18
 
 
 
 
Estilos da mensagem de erro: 
 
A opção Estilo permite alterar o ícone e os botões da mensagem de erro, os estilos disponíveis 
são: Parar, Aviso e Informações, cada um deles mostra diferentes opções quando o valor 
inserido não é válido. 
 
Veja as opções, ao inserir uma letra na célula referente á quantidade. 
 
1. Parar: 
 Ao clicar no botão 
repetir o usuário pode 
digitar o valor correto 
novamente e o botão 
cancelar permite 
manter o valor que 
estava antes. 
 
 
2. Aviso: 
 
Mostra um aviso perguntando 
se o usuário deseja continuar 
(Sim / Não) ou cancelar, caso 
escolha Sim será ignorada a 
validação e o valor poderá 
ser inserido mesmo que não 
seja válido. 
 
 
 
3. Informações: 
Ao Clicar no botão OK 
a validação é ignorada 
e o valor pode ser 
inserido mesmo que 
seja inválido. 
 
 
 
 
 
 
 
 
 
 
Excel Avançado – Deivis Fontes 
 19
 
 
Validar a data 
 
 
Selecione as células referentes a data e na janela de 
validação em Configurações escolha Permitir >> Data. 
 
 
 
 
 
 
 
Dados: Escolha a data de ínicio e Término, os valores podem ser uma célula da planilha 
permitindo maior flexibilidade para alterar as datas futuramente. 
 
 
 
Configure as mensagens de entrada e de Erro, conforme suas preferências. 
 
 
 
 
 
 
 
 
 
 
Excel Avançado – Deivis Fontes 
 20
 
 
Filtros 
 
Em planilhas com muito conteúdo fica difícil separar as informações por grupos, para resolver o 
Excel permite filtrar as informações da planilha permitindo que sejam visualizadas apenas as 
células referentes ao critério usado no filtro tornando assim mais fácil a consulta aos dados da 
planilha. Para usar o filtro selecione a célula desejada e clique no menu Dados >> Filtrar >> 
Auto filtro, note que esta opção do menu ativa o filtro para desativar siga os mesmos passos 
desmarcando o auto filtro do menu. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Escolha a opção Personalizar: 
 
 
 
 
 
 
 
 
 
 
 
 
Defina os parâmetros do filtro personalizado 
 
Dica: Se quiser “resetar” o filtro clique no menu: Dados >> Filtrar e desmarque a opção 
Auto Filtro. 
 
Excel Avançado – Deivis Fontes 
 21
 
 
 
Capítulo 3 – Protegendo e Compartilhando 
pastas de trabalho 
 
 
Proteção de planilhas 
 
O Excel permite proteger as planilhas com senha, evitando assim que uma pessoa não 
autorizada altere os valores ou modifique suas fórmulas, este recurso é muito útil em empresas 
onde algumas informações devem ser protegidas. 
Veja como proteger uma planilha: 
1. Abra uma planilha que você deseja proteger com senha. 
2. Clique no Menu: Ferramentas >> Proteger >> Proteger Planilha... 
3. Na janela que surgir digite uma senha e escolha o que você deseja permitir marcando as 
caixas de seleção referentes ao recurso selecionado. 
4. Repita sua senha 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Ao tentar editar qualquer célula será exibida uma mensagem de erro. 
 
 
 
 
 
 
 
 
 
 
Excel Avançado – Deivis Fontes 
 22
 
 
Para desbloquear basta acessar o menu Ferramentas >> Proteger >> Desproteger 
Planilha... e digitar a senha. 
 
 
 
 
 
 
 
 
 
Para permitir a edição de determinadas células mesmo com a planilha protegida por senha, 
selecione as células que você deseja permitir a edição e clique no menu Formatar >> Células 
escolha a guia Proteção e desmarque a caixa de seleção Bloqueadas isso permite que as 
células selecionas possam ser editadas enquanto o resto da planilha fica protegida. 
 
Excel Avançado – Deivis Fontes 
 23
 
 
Compartilhando Planilhas na rede 
 
Antes de compartilhar uma planilha na rede você deve criar uma pasta compartilhada para 
salvar sua planilha neste local, para criar uma pasta compartilhada siga os passos abaixo: 
 
Clique com o botão direito do mouse na pasta que você deseja compartilhar e escolha a opção 
Compartilhamento e segurança ... 
 
 
 
Na janela que surgir marque a caixa de seleção 
Compartilhar esta pasta na rede e Permitir que os 
usuários da rede alterem meus arquivos, clique em OK 
para aplicar as alterações. 
Quando mais de um usuário abrir a mesma planilha o 
segundo usuário receberá uma mensagem de erro 
informando que uma planilha já esta aberta e esta cópia será 
aberta apenas como leitura. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel Avançado – Deivis Fontes 
 24
 
Permitindo que mais de um usuário trabalhe na mesma planilha 
 
Para permitir que mais de um usuário abra a mesma planilha, salve a planilha desejada e 
clique no menu Ferramentas >> Compartilhar pasta de trabalho... 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Na guia avançadas configure como na imagem abaixo: 
 
 
 Assim a planilha será salva e atualizada a 
cada 5 minutos. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel Avançado – Deivis Fontes 
 25
 
 
 
Capítulo 4 - Fórmulas 
 
Fórmulasdo Excel 
É possível fazer cálculos matemáticos com o Excel por meio de Fórmulas, baseadas no 
conteúdo de uma ou mais células ou em valores previamente determinados na fórmula. 
Antes de começar a fazer uma fórmulas leve em consideração que: 
• Toda fórmula inicia com o sinal = (igual) 
• Para calcular valores do conteúdo entre duas células use : (dois pontos) 
• Para calcular valores aleatórios entre células separes as células com ; (ponto e virgula) 
• As referências dos exemplos como =A1+A2, são apenas ilustrativas perceba que A1 
deve ser alterado para a célula correspondente ao valor que deseja calcular. Por 
exemplo, se na célula B2 tem o valor gasto com telefone e na célula C2 Tem o valor 
gasto com água então a fórmula ficaria assim: =B2+C2, para exibir os gastos com água 
e telefone. 
 
Operações básicas: 
Operação Fórmula Observações 
Adição =A1+A2 Soma o valor da célula A1 + A2 
Subtração =A1-A2 Subtrai o valor da célula A1 + A2 
Multiplicação =A1*A2 Multiplica o valor da célula A1 + A2 
Divisão =A1/A2 Divide o valor da célula A1 + A2 
Somar valores entre várias 
células 
=SOMA(A1:A10) Esta fórmula soma todos os valores que 
estão entre as células A1 e A10. 
Cálculos estatísticos 
Operação Fórmula Observações 
Média entre seqüência de 
valores 
=MEDIA(A1:A10) Calcula a média entre as células A1 e 
A10 
Média com dois ou mais 
valores 
=MEDIA(A1:A10;A15) Calcula a média com as células A1, A10, 
A15 
Maior valor =MAXIMO(A1:A10) Pode usar o sinal ; (ponto e Vírgula) 
para calcular valores selecionados 
Menor valor =MINIMO(A1:A10) Pode usar o sinal ; (ponto e Vírgula) 
para calcular valores selecionados 
Contar células =CONT.NÚM(A1:A10) Funciona apenas com valores 
numéricos. 
Obs.: o sinal : (dois pontos) calcula os valores que estão entre uma célula inicial e outra final, 
no entanto o sinal ; (ponto e Vírgula) calcula apenas os valores escolhidos que estão entre o 
sinal ; (ponto e Vírgula). 
Excel Avançado – Deivis Fontes 
 26
 
Outras fórmulas 
 
Nome Fórmula Observações 
Raiz Quadrada =Raiz(A1) 
Potência =A1^2 O número 2 indica o grau de exponênciação se 
quiser calcular ao cubo use 3 
 
 
Data e hora 
 
Fórmula Observações 
=HOJE() Mostra a data atual de acordo com o relógio do sistema 
=AGORA() Subtrai o valor da célula A1 + A2 
 
 
Operadores 
 
Soma + 
Subtração – 
Multiplicação * 
Divisão / 
Diferente de <> 
Maior > 
Menor < 
Potência ^ 
 
 
 
Nomes das células 
 
Em planilhas mais complexas, é recomendável nomear as células que serão usadas para 
calcular os resultados, para nomear as células: 
1. Selecione as células que serão nomeadas 
2. Clique no menu Inserir >> Nome >> Definir ... 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel Avançado – Deivis Fontes 
 27
 
Digite o nome para as células 
 
 
 
 
 
 
 
 
 
 
 
 
 
Clique na caixa de texto Refere-se a: e selecione as células que deseja nomear 
 
 
 
Clique no botão Adicionar e depois em OK. 
 
Para Calcular basta usar o nome da célula como, por exemplo, a célula A1 tem Quantidade de 
um produto e a célula B1 tem o valor de venda deste produto e estão nomeadas assim: 
A1 – Qnt 
B1 – ValorCompra 
 
Para saber o total use a fórmula: 
=Qnt * ValorCompra 
 
 
 
 
 
 
Excel Avançado – Deivis Fontes 
 28
 
 
Fórmula condicional no Excel 
 
O Excel permite o uso de fórmulas condicionais, possibilitando o preenchimento automático de 
uma célula, de acordo com o valor de outra célula relacionada, como por exemplo, as médias 
de um aluno, se o valor da célula correspondente a média for maior ou igual a 7 o resultado 
será Aprovado e caso contrário Reprovado, evitando assim que o usuário tenha que 
comparar as médias e digitar manualmente a situação do aluno. 
Lógica da fórmula: 
 
Veja o exemplo na planilha, onde temos a célula A1 com o número 1 e na célula do lado 
usamos uma fórmula que se o valor da célula A1 for igual a 1 será exibido Um e caso contrário 
não será mostrado nada. 
 
Acompanhe o seguinte raciocínio: 
Fórmula: 
=SE(A1=1;"Um";" ") 
Analisando: 
Se A1 for igual a 1 então Um caso contrário em branco 
 
Observações 
• Primeiro indicamos o tipo de fórmula, =SE(teste lógico) 
• Depois a condição A1=1 
• O Ponto-e-vírgula separa a condição do resultado 
• Caso o valor da célula A1 for igual a 1, temos então o primeiro resultado Um 
• E caso contrário fica em branco 
• Os resultados devem ser separados por Aspas " 
 
Cálculo de média escolar: 
=SE(A1>=7;"Aprovado";"Reprovado") 
Se o valor da célula A1 for maior ou igual a 7 então Aprovado, caso contrário Reprovado 
 
 
 
 
 
 
 
 
Excel Avançado – Deivis Fontes 
 29
 
Condição com Texto 
 
Até agora vimos fórmulas envolvendo células com números, mas podemos usar também usar 
células que contenham palavras, veja o exemplo: 
=SE(A3="deivis";"Fontes";"Outro Nome") 
Neste caso é necessário colocar a condição entre Aspas "deivis". 
 
 
 
Usando mais de uma condição 
 
Em uma planilha para controle de funcionários por setores de uma empresa temos uma célula 
com o nome do funcionário outra com o código do cargo e outra que mostra o setor em que o 
funcionário trabalha. 
 
 
veja a fórmula: 
=SE(B3=1;"Desenvolvimento";SE(B3=2;"Direção";SE(B3=3;"Outros"))) 
Agora basta adaptar conforme suas necessidades, lembrando que no final devem ser 
acrescentadas a quantidade de parênteses para “fechar a fórmula de acordo com os que foram 
abertos na fórmula. 
 
Concatenar 
 
Concatenar para o Excel significa juntar células umas as outras ou células com um texto 
predefinido, como por exemplo, em uma planilha onde temos em uma célula nome e em outra 
o endereço usamos a seguinte fórmula para juntar o nome e o endereço: 
 
=CONCATENAR(A2;B2) 
 
 
Excel Avançado – Deivis Fontes 
 30
 
 
Para usar um texto pré-determinado, coloque o texto entre aspas como no exemplo: 
 
=CONCATENAR(A2;”Seu texto”) 
 
 
 
 
 
Outra forma de concatenar é usar o caractere & entre uma célula e outra. 
Ex.: 
=A1&B1 
 
Para inserir espaço, palavra ou número no resultado use as aspas 
=A1&”texto”&B1 
 
 
Vinculo com célula de outra planilha 
 
É possível vincular dados das células de outra planilha, para que quando o valor for alterado na 
planilha de origem a que recebe o vinculo atualiza os valores automaticamente, por exemplo, 
digite um valor na célula A1 em Plan2 e na célula A2 da Plan1 digite a seguinte fórmula: 
=Plan2!A1. 
 
 
Deixar apenas a primeira letra Maiúscula 
 
Durante o uso de planilhas, muitos usuários esquecem a tecla Caps Loock ativada assim tudo 
que for digitado fica em maiúsculo, isso ocupa um espaço maior nas colunas da planilha e em 
alguns casos a deixa com uma aparência feia, para corrigir isso, na célula que você deseja 
mostrar com a primeira letra maiúscula, use a seguinte fórmula: 
Onde A1 deve ser substituída pela célula que contem o valor a ser corrigido. 
 
=PRI.MAIUSCULA(A1) 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel Avançado – Deivis Fontes 
 31
 
 
Procura automática 
 
Em muitos casos pode ser útil que o Excel preencha automaticamente os dados a aprtir de 
uma referência, como, por exemplo uma planilha de vendas onde temos uma planilha com os 
produtos e outra com as vendas, para evitar erros podemos usar a fórmula PROCV que 
preenche automaticamente os valores de acordo com o código do produto. Para entender 
melhor vamos á prática: 
 
Crie uma planilha como mostra a imagem abaixo: 
 
 
 
 
Renomeie a Plan2 como Orçamento 
 
Preencha com os dados da imagem abaixo: 
 
 
[ 
 
 
Excel Avançado – Deivis Fontes 
 32
 
Na célula A1 o usuário deve digitar o código do produto e nas células B2 e C2 deve aparecer 
automaticamente a descrição do produto e o seu valor. 
 
Para isso use a fórmula: 
 
=PROCV(A2;Produtos!A2:C10;2) 
 
Que traduzindo teremos 
=PROCV(célula onde o valor deve ser procurado;Intervalo onde procurar;Número 
correspondente à coluna cujo valor deve ser exibido) 
 
Vamos analisar cada parte destafórmula: 
 
• =PROCV: este é o nome da fórmula que deve ser seguido do parêntese 
• A2: é a célula onde digitaremos o código do produto, ou seja, o que a fórmula deve 
procurar. 
• Produtos!A2:A10: é o intervalo de células no qual o valor digitado em A2 deve ser 
procurado, neste caso este valor está entre as células A2 e A10 da planilha Produtos 
• 2: este é o número que indica em qual coluna deverá ser pego o valor a ser exibido, no 
caso da coluna C seria 3, coluna D 4 e assim por diante. 
• Os sinais de ponto e vírgula separam as partes da fórmula. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel Avançado – Deivis Fontes 
 33
 
 
 
 
Capítulo 5 – Tabelas dinâmicas 
 
Criando Tabelas dinâmicas 
 
Tabelas dinâmicas é um recurso do Excel que permite organizar os dados em planilhas muito 
extensas, permitindo acesso rápido e fácil aos dados que você precisa. Veja como criar uma 
tabela dinâmica: 
 
Abra uma planilha com muitos dados e clique no 
menu Dados >> Relatório de tabela e gráfico 
dinâmico... 
 
 
 
 
 
 
 
 
 
 
Escolha as opções : 
Banco de dados ou lista do microsoft office excel 
Tabela dinâmica 
 
 
Clique em avançar 
Excel Avançado – Deivis Fontes 
 34
 
 
Selecione as células que serão usadas e escolha avançar > 
 
 
 
Escolha onde você quer inserir o0 relatório de tabela dinâmica 
 
 
 
Clique em concluir 
 
Arraste os itens para as partes do relatório que você preferir 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel Avançado – Deivis Fontes 
 35
 
 
Capítulo 6 – Macros 
 
 
O Excel permite automatizar algumas tarefas com o uso de macros, que podem ser gravadas 
automaticamente ou escritas usando alguns comandos da linguagem Visual Basic baseada na 
linguagem Basic desenvolvida pela Microsoft. 
 
Para gravar macros siga os passos: 
 
Abra uma planilha e clique no menu Ferramentas >> Macro >> Gravar nova Macro... 
 
 
 
Digite um nome para esta macro, caso deseje pode definir um atalho de teclado para a macro 
pressionando a tecla Ctrl, também é possíel escolher um local para salvar esta macro. Clique 
em OK para iniciar a gravação. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel Avançado – Deivis Fontes 
 36
 
 
Selecione algumas células e mescle, isso será a ação desta macro poderia ser outra ação 
qualquer, como imprimir uma planilha. 
 
 
 
 
 
 
 
 
 
 
Para parar a gravação clique no botão Parar Gravação 
 
 
 
 
 
 
 
 
 
 
Para ver as macros que foram criadas Clique em Ferramentas >> Macro >> Macros, ou 
pressione Alt + F8 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel Avançado – Deivis Fontes 
 37
 
 
Para executar uma macro clique na macro e em seguida clique no botão executar 
 
 
 
 
 
Editando Macros 
 
Pressione Alt + F8 e clique na macro que deseja editar e clique no botão Editar. 
Será aberto o editor de macros, onde você poderá usar comandos da linguagem visual basic 
para alterar suas macros, mas isso veremos no próximo capitulo sobre VBA a linguagem de 
programação do Excel. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel Avançado – Deivis Fontes 
 38
 
 
Editando uma macro 
 
Veja a seguir o exemplo de uma macro que seleciona as células A1 até D1 
 
Sub Macro1() 
 Range("A1:D1").Select 
End Sub 
 
A linha Range("A1:D1").Select refere-se ao intervalo de células A1 até D1, para alterar este 
intervalo basta trocar os valores A1:D1, se desejar selecionar células fora de uma seqüência 
use a vírgula. 
 
Sub Macro1() 
 Range("A1,A3,A5").Select 
End Sub 
 
 
 
 
Segurança 
 
Ao abrir uma planilha que contém macros em outro computador ou até mesmo no próprio computador 
onde a planilha foi elaborada pode acontecer do Excel bloquear as macros, sendo necessário alterar 
algumas configurações de segurança do Excel. Isso foi implantado na versão 2003 do Office para evitar 
os vírus de macro que desconfiguravam os aplicativos do pacote Office. 
 
Com a planilha aberta clique no menu Ferramentas >> Macro >> Segurança... 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel Avançado – Deivis Fontes 
 39
 
Deixe os níveis de segurança como Baixo assim suas macros poderão ser executadas sem problemas, se 
preferir altere o nível de segurança para Alto ou Muito alto depois. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Alternando entre as planilhas 
 
Vamos criar uma macro simples que alterna entre as planilhas, apenas clicando em botões, para isso 
precisamos da barra de ferramentas de caixa de controle, que por padrão não é exibida no Excel, para 
exibir esta barra clique no menu Exibir >> Barras de ferramentas e marque a opção Caixa de ferram. 
De controle. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel Avançado – Deivis Fontes 
 40
 
 
Escolha o item Botão de comando e desenhe um botão na planilha 
 
 
 
 
 
 
 
 
 
 
 
 
 
Clique com o botão direito do mouse neste Botão de controle e escolha Propriedades. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Altere as seguintes opções: 
• Name – Nome do botão usado pelo Excel, não pode conter 
espaços e use um nome sugestivo como BtnPlanilha2. 
• Caption – Texto que aparece no botão 
• Mouse Icon – cursor do mouse que vai aparecer para este 
botão 
 
Dica: 
Você pode alterar as outras opções para descobrir 
 o que cada uma faz ! 
 
 
 
 
 
 
 
 
 
 
 
 
Excel Avançado – Deivis Fontes 
 41
 
Feitas as devidas alterações na aparência deste botão vamos agora 
inserir os comandos para que quando este botão for clicado passe para 
a planilha 2, para isso clique com o botão direito do mouse neste 
botão e escolha Exibir código. 
 
 
 
 
 
 
 
 
 
 
No editor do VBA insira o código entre Private Sub CommandButton1_Click() e End Sub 
 
Worksheets(2).Activate 
 
Deve ficar assim: 
 
Private Sub CommandButton1_Click() 
Worksheets(2).Activate 
End Sub 
 
Volte para a planilha do Excel e clique no botão para ver o resultado, talvez seja necessário sair do modo 
Design clicando na opção da caixa de ferramentas de controle. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel Avançado – Deivis Fontes 
 42
 
 
 
Capítulo 7 – Programação em VBA 
 
 
VBA 
VBA é a sigla para Visual Basic for Applications, um ambiente de programação voltado para os 
programas da família Office, usando como base a linguagem Visual Basic, criada pela Microsoft. 
Para acessar o ambiente de programação do VBA, primeiro abra o Excel, depois vá no menu 
Ferramentas >> Macros >> Editor do Visual Basic ou pelo atalho de teclado Alt + F11. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel Avançado – Deivis Fontes 
 43
 
 
Itens área de trabalho do VBA: 
1. Project Explorer - onde você pode ver todos componentes do seu projeto como planilhas plan1, 
plan2, etc. 
2. Janela de Propriedades - Janela onde você pode ver e alterar as propriedades dos componentes 
do projeto, por exemplo, a propriedade Name altera o nome de um componente. 
3. Formulário - Tela do programa onde podemos colocar botões, caixas de texto, etc. 
4. Caixa de ferramentas - Local onde encontramos os controles necessários para o projeto como 
botões, caixas de texto, etc. 
Para alterar as propriedades de um componente você deve primeiro selecioná-lo, bastando para isso clicar 
sobre o objeto desejado e Pressionar F4 caso a janela de propriedades não estiver aparecendo. 
Algumas propriedades comuns: 
• Name - Nome usado para orientar o programador na hora de escrever o código 
• Caption - Título que aparece no componente, comum em componentes tipo botão e formulário 
• Text - Exibe o texto definido nesta propriedade em uma caixa de texto 
• Font - Define o tipo de fonte (letra) a ser utilizada 
• Visible - Visibilidade do componente, True(verdadeiro)ou False(falso) 
• Enabled - Define se o componente está habilitado, True(verdadeiro) ou desabilitado False(falso) 
Estas são apenas algumas propriedades de uso geral, pois as propriedades podem variar conforme o 
componente e a situação. 
 
Executando uma aplicação 
 
Para executar sua aplicação pressione F5 ou clique no botão Executar da barra de ferramentas, para parar 
clique no botão Redefinir ou feche sua aplicação 
 
 
 
 
Módulos 
 
Os módulos são as macros criadas e podem ser atribuídos a um ou mais componentes da planilha 
economizando assim linhas de código repetidas. 
 
Formulários 
 
Formulários são janelas que podem ser usadas para diversas funções dentro de uma planilha, dentro de 
um formulário podemos inserir os controles como botões, caixas de texto, etc. Para inserir um formulário 
clique no menu Inserir >> User Form, note que também é possível inserir módulos a partir desse menu: 
 
 
 
Excel Avançado – Deivis Fontes 
 44
 
Controles comuns 
 
Vamos criar um aplicativo de exemplo onde o usuário deve digitar seu nome em uma caixa de texto e 
clicar em um botão para mostrar seu nome em um rótulo. 
 
No editor do VBA insira um formulário e neste formulário coloque os seguintes componentes: 
1 Rótulo 
1 Caixa de texto 
2 Botões 
 
 
Altere as propriedades: 
 
Rotulo: 
Caption: em branco 
 
Caixa de Texto: 
Name TxtNome 
Text: em branco 
 
CommandButton1: 
Caption: OK 
 
CommandButton2 
Caption: Sair 
 
Vamos agora ao código 
 
Dê um duplo clique no botão OK e insira o código que vai mostrar seu nome no rótulo para isso usaremos 
as propriedades do rótulo e da caixa de texto: 
 
Private Sub CommandButton1_Click() 
Label1.Caption = TxtNome.Text 
End Sub 
 
Onde: 
Label1.Caption - é o rótulo com a propriedade Caption onde mostra o texto neste rótulo 
TxtNome.Text – é a caixa de texto com a propriedade Text que será mostrada no rótulo 
 
No código do botão OK digite End, isso fecha o formulário 
 
Para chamar esse formulário através de um botão de comando use o código: 
UserForm1.Show 
 
Onde UserForm1 é o nome do formulário, definido na propriedade Name 
 
 
 
 
 
Excel Avançado – Deivis Fontes 
 45
 
Comentando o código 
 
Comentários ajudam a entender melhor o código os comentários devem ser iniciados com apóstrofos 
Veja 
 
‘mostra a letra A na caixa de texto 
 
TextBox1.Text = “A” 
 
Variáveis 
 
 
As variáveis armazenam um valor na memória do computador que poderá ser usado posteriormente para 
cálculos ou exibir uma mensagem, devem ser declaradas com o comando Dim Nome_da_variavel As 
Tipo_de_Variavel 
 
Tipos de Variáveis 
Boolean 
• As variáveis Boolean são armazenadas como números de 16 bits (2 bytes), mas só podem ser True 
ou False. 
• As variáveis Currency são armazenadas como números de 64 bits (8 bytes) em um formato de 
número inteiro, em escala de 10.000 para fornecer um número de ponto fixo com 15 dígitos à 
esquerda da vírgula decimal e 4 dígitos à direita. 
• Essa representação fornece um intervalo de -922.337.203.685.477,5808 até 
922.337.203.685.477,5807. 
Currency 
• O tipo de dados Currency é útil para cálculos que envolvem dinheiro e cálculos de ponto fixo, nos 
quais a precisão é especialmente importante. 
Date 
• As variáveis Date são armazenadas como números IEEE de ponto flutuante de 64 bits (8 bytes) 
que representam as datas que variam de 1 de janeiro de 100 a 31 de dezembro de 9999 e as horas 
de 0:00:00 a 23:59:59. Qualquer valor literal de data reconhecível pode ser atribuído a variáveis 
Date. Os literais date devem estar entre sinais (#), por exemplo, #1 de janeiro de 1993# ou #1 jan 
93#. 
• As variáveis Date exibem as datas de acordo com o formato abreviado de data reconhecido por 
seu computador. As horas são exibidas de acordo com o formato de hora (12 ou 24 horas) 
reconhecido por seu computador. 
• Quando outros tipos numéricos são convertidos em Date, os valores à esquerda do decimal 
representam informações de data, enquanto os valores à direita do decimal representam horas. 
Meia-noite é 0 e meio-dia é 0,5. Números inteiros negativos representam datas anteriores a 30 de 
dezembro de 1899. 
 
Excel Avançado – Deivis Fontes 
 46
 
String 
• Os códigos para caracteres String variam de 0 a 255. 
• Os primeiros 128 caracteres (de 0 a 127) do conjunto de caracteres correspondem às letras e aos 
símbolos de um teclado padrão norte-americano e são iguais àqueles definidos pelo conjunto de 
caracteres ASCII. 
• Os 128 caracteres (de 128 a 255) seguintes representam caracteres especiais, como letras de 
alfabetos internacionais, acentos, símbolos de moeda e frações. 
 
 
Byte 
• As variáveis Byte são armazenadas como números de 8 bits (1 byte), sem sinal, únicos, que 
variam em valor de 0 a 255. 
• O tipo de dados Byte é útil para conter dados binários. 
Decimal 
• As variáveis Decimal são armazenadas como números inteiros, sinalizados, de 96 bits (12 bytes), 
em escala por uma potência de 10 variável. 
• O fator de escala potência de 10 especifica o número de dígitos à direita da vírgula decimal e varia 
de 0 a 28. 
• Com uma escala de 0 (sem casas decimais), o maior valor possível é +/-
79.228.162.514.264.337.593.543.950.335. 
• Com 28 casas decimais, o maior valor é +/-7,9228162514264337593543950335 e o menor valor, 
diferente de zero, é +/-0,0000000000000000000000000001. 
IMPORTANTE: Atualmente, o tipo de dados Decimal pode ser usado somente dentro de uma 
Variant, ou seja, você não pode declarar uma variável como sendo do tipo Decimal. No entanto, 
você pode criar uma Variant com o subtipo Decimal usando a função CDec. 
Double 
• As variáveis Double (vírgula flutuante de dupla precisão) são armazenadas como números IEEE 
de vírgula flutuante de 64 bits (8 bytes), com valor no intervalo de -1,79769313486232E308 a -
4,94065645841247E-324 para valores negativos e de 4,94065645841247E-324 a 
1,79769313486232E308 para valores positivos. 
• O caractere de declaração de tipo para Double é o sinal #. 
Integer 
• As variáveis Integer são armazenadas como números de 16 bits (2 bytes) com valor no intervalo 
de -32.768 a 32.767. 
• O caractere de declaração de tipo para Integer é o sinal de porcentagem (%). 
• As variáveis Integer também podem ser usadas para representar valores enumerados. 
• Um valor enumerado pode conter um conjunto finito de números inteiros exclusivos, cada um 
com um significado especial no contexto em que é usado. 
Excel Avançado – Deivis Fontes 
 47
 
 
Para entender as variáveis vamos desenvolver uma calculadora, que pode ser muito útil caso o usuário 
queira fazer um cálculo simples e rápido sem ter que usar fórmulas. 
No editor do VBA clique no menu Inserir >> UserForm, para inserir um formulário que será a tela da 
calculadora, neste UserForm insira os seguintes componentes: 
• 3 TextBox 
• 4 Botões 
• 1 Rótulo 
Organize os componentes e altere as propriedades para que fiquem parecidos com a imagem abaixo: 
 
 
Clique 2 vezes no botão com o sinal de soma (+) e digite o seguinte código, entre Private Sub 
CommandButton1_Click() e End Sub : 
 
Dim x, y As Double 
x = TextBox1.Text 
y = TextBox2.Text 
 
TextBox3.Text = x + y 
 
Comentando o código: 
Dim x, y As Double - variáveis mostra que serão utilizados dois números X e Y 
 
x = TextBox1.Text 
y = TextBox2.Text 
mostra qual componente vai receber cada variável 
 
TextBox3.Text = x + y 
calcula no TextBox3 a soma de X+Y 
 
 
Excel Avançado – Deivis Fontes 
 48
 
Repita o mesmo procedimento para os outros botões lembrando de trocar o sinal do operador no código 
de cada botão. 
 
Depois de pronta crie um botão de comando no Excel, clique duas vezes nele e digite o código para 
chamar a calculadora 
FrmCalculadora.Show 
note que FrmCalculadora é o nome que foi definido para o UserForm 
 
 
 
Caixa de mensagemAs caixas de mensagem podem ser usadas para exibir informações na forma de avisos, seu uso é bem 
simples, veja o código a seguir: 
 
Private Sub CommandButton1_Click() 
MsgBox ("ola") 
End Sub 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
Excel Avançado – Deivis Fontes 
 49
 
Capítulo 8 – Guia de consulta rápida 
 
Fórmulas 
 
Fórmula Ação 
=A1+A2 Soma 2 valores 
=SOMA(A1:A10) Soma o intervalo de células entre A1 e A10 
=A1-A2 Subtração 
=A1*A2 Multiplicação 
=A1/A2 Divisão 
=MEDIA(A1:A10) Calcula a média de valores no intervalo entre A1 e A10 
=Mínimo(A1:A10) Mostra o menor valor entre o intervalo A1 e A10 
=MAXIMO(A1:A10) Mostra o maior valor entre o intervalo A1 e A10 
=SE(A1<= 7;”Aprovado”;”Reprovado) Fórmula lógica testa se o valor da célula A1 for menor ou 
igual a 7 mostra como resultado Aprovado, caso contrário 
Reprovado 
=SE(A1<>"";A1*5;"") Testa se o valor da célula A1 for diferente de em branco (o 
sinal “” indica espaço em branco), então calcula A1*5, caso 
contrário mostra a célula em branco 
=CONT.NUM(A1:A10) Conta o numero de células no intervalo entre A1 e A10, 
funciona apenas com calores numéricos (Números em geral, 
data, hora e valores monetários). 
=Raiz(A1) Calcula a raiz quadrada do valor contido na célula A1 
=A1^2 Calcula a potência do valor contido na célula A1 elevado a 2 
=CONCATENAR(A1;A2) Junta o valor das células A1 e A2 
=CONCATENAR(A1;” “;A2) Junta o valor das células A1 e A2 deixando um espaço em 
branco entre as duas 
=Plan2!A1 Mostra o valor da célula A1 da planilha Plan2 
=PRI.MAIUSCULA(A1) Mostra a primeira letra maiúscula 
=PROCV(A2;PRODUTOS!A2:A10;2 Procura um valor, onde: 
PROCV – formula 
A2 – Célula onde digita o código a ser procurado 
Produtos!A2:A10 – intervalo de células onde o código será 
procurado 
2 – refe-se à coluna B neste caso indicada pelo número 2 
 
VBA 
 
Worksheets(2).Activate Ativa e exibe a planilha 2 seguindo a ordem qu 
elas estão na pasta de trabalho 
Workbooks.Open(“C:\Balanço.xls”) Abre a planilha Balanço salva no disco C:\ 
Worksheets.Add Adiciona uma nova planilha 
Range(“A1”).Activate Ativa uma célula 
Range("A1").Value = "ABC" Define o valor ABC na célula A1 
UserForm1.Show Mostra um formulário 
End Fecha um formulário 
TextBox1.Text Propriedade Text da caixa de texto 
Label1.caption Propriedade Caption que mostra o texto do rótulo