03 Excel 2000 Avançado

Prévia do material em texto

������
�
����	
���
����
���������
�
���	�������������
 
 
MS Excel 2000 Avançado 
Alexandre Dantas Página: 2 
ESCOLA TÉCNICA DE INFORMÁTICA
Sumário 
 
1. FUNÇÕES DE BANCO DE DADOS 3 
1.1. FUNÇÕES DE BD MAIS IMPORTANTES PARA O NOSSO CURSO 4 
2. FORMATAÇÃO CONDICIONAL 6 
3. SUBTOTAIS 7 
4. VALIDAÇÃO 9 
4.1. DEFININDO UMA VALIDAÇÃO DECIMAL 10 
4.2. AJUSTANDO UMA MENSAGEM DE ENTRADA 10 
4.3. DEFININDO O NÍVEL DE BLOQUEIO 11 
4.4. CONSOLIDAR 13 
5. ATINGIR META 15 
6. CENÁRIOS 16 
7. AUDITORIA 18 
8. SOLVER 18 
9. LISTAS 21 
9.1. VISUALIZANDO AS LISTAS EXISTENTES. 21 
9.2. ADICIONANDO UMA NOVA LISTA 22 
9.3. IMPORTANDO DADOS PARA FORMAR UMA NOVA LISTA. 22 
9.4. EXCLUINDO UMA LISTA 22 
10. MACROS 22 
10.1. CONSTRUINDO UMA MACRO: 23 
10.2. EXECUTANDO UMA MACRO 24 
10.3. ATRIBUINDO MACROS A FIGURAS 25 
10.4. CRIANDO UMA NOVA BARRA DE FERRAMENTAS 25 
10.5. ASSOCIANDO SUAS MACROS A BOTÕES DE BARRAS DE FERRAMENTAS. 27 
10.6. EDITANDO A IMAGEM DE BOTÃO 28 
10.7. COPIANDO E COLANDO A IMAGEM DE BOTÃO 28 
10.8. EXCLUINDO MACROS 29 
10.9. EXCLUÍDO BARRAS DE FERRAMENTAS 29 
11. VISUALIZANDO O CÓDIGO ESCRITO EM VISUAL BASIC APPLICATION29 
11.1. SEGURANÇA 29 
12. FILTRO AVANÇADO 30 
12.1. EXIBINDO TODOS OS REGISTROS 31 
13. TABELA DINÂMICA 32 
14. OUTRAS FUNÇÕES QUE VOCÊ PODE PRECISAR USAR NO PROJETO 35 
 
 
MS Excel 2000 Avançado 
Alexandre Dantas Página: 3 
ESCOLA TÉCNICA DE INFORMÁTICA
1. Funções de Banco de Dados 
São funções que tratam a tabela, área que contém títulos das colunas, como um banco de dados. Todas as 
funções de banco de dados, quando se referem a tabela, incluem seus títulos como parte da tabela, esse 
conceito se contrapõe as funções de procura e referencia já estudadas (PROC e PROCV). 
Para não ficar cansativo em todas as fórmulas se referenciar a tabela (com os títulos), aconselhamos a 
definir um nome para a tabela. 
Definindo um nome para a tabela – nomeando uma faixa de células 
Observe a planilha abaixo. Sua área de dados se estende até a linha 30 (que não está visível na figura). Para 
definir um nome para a área da tabela, siga os seguintes passos: 
 
 
1. Selecione a tabela inteira. Clique em qualquer célula da tabela e pressione CTRL + *. 
2. Clique no Menu Inserir – Nome – Definir. 
 
Clicar dentro da caixa de texto “Nomes na pasta de trabalho”, digite um nome para a área da tabela, por 
exemplo Vendas e clicar no botão Adicionar. Note que o nome Vendas aparece abaixo, dentro da caixa de 
listagem. Agora clique no botão OK. 
 
 
 
Observe na figura acima que o nome Vendas, recém criado, refere-se a área de A3:E30, da planilha 
Quadro de Vendas. 
 
 
MS Excel 2000 Avançado 
Alexandre Dantas Página: 4 
ESCOLA TÉCNICA DE INFORMÁTICA
1.1. Funções de BD mais importantes para o nosso curso 
• BDSoma – Soma células cujos registros satisfaçam a critérios previamente estabelecidos; 
• BDMáx – Extrai o maior valor de uma região cujos registros satisfaçam a critérios previamente 
estabelecidos; 
• BDMín - Extrai o menor valor de uma região cujos registros satisfaçam a critérios previamente 
estabelecidos; 
• BDMédia – Calcula a média de valores cujos registros satisfaçam a critérios previamente estabelecidos. 
• BDExtrair – Extrai uma informação de uma tabela, desde que os critérios previamente estabelecidos 
apontem um único registro como solução. 
• BDContar – Conta quantos registros possuem um certo campo com conteúdo numérico e que 
satisfaçam a critérios previamente estabelecidos. 
• DBContara – Conta quantos registros satisfazem os condições impostas por critérios previamente 
estabelecidos. 
Observe que todas de BD somente reconhecem os registros (linhas da tabela) que satisfaçam a condições 
impostas por critérios previamente estabelecidos. 
 
O que são critérios ? 
Uma ou mais condições que você especifica para limitar os registros incluídos no conjunto de resultados de 
uma consulta. Por exemplo, para definir que, usando a tabela Infótica – Relatório de Vendas como 
exemplo, somente sejam considerados os registros referentes ao vendedor André Luiz, veja como seria o 
critério: 
Vendedor 
André Luiz 
 
Isso mesmo, os critérios do Microsoft Excel são sempre de duas linhas, na primeira linha são especificados 
os títulos e na segunda o conteúdo a ser reconhecido. No exemplo abaixo somente serão reconhecidos os 
registros em que o vendedor seja José Luiz e o produto seja Micro Pentium II. 
 
Vendedor Produto 
André Luiz Micro Pentium II 
Sintaxe das funções de BD 
Uma das grandes vantagens das funções de BD é que a sintaxe de suas funções é a mesma, ou seja 
aprendendo a usar, por exemplo, a função BDSOMA, você estará aprendendo também a usar a função 
BDMÉDIA. 
As funções de BD possuem três argumentos, a saber: O primeiro é a tabela inteira, com seu respectivos 
títulos das colunas, o segundo é o título da coluna de referencia e o terceiro é a região onde você digitou os 
critérios. 
Para facilitar sua vida usaremos como exemplo, mais uma vez, a tabela Infótica – Relatório de Vendas. 
Digite os critérios em células livres na planilha atual, ou se quiser pode até inseri-los em outra planilha. No 
nosso exemplo dirigido digitaremos nosso critérios nas células G5:G6, como mostra a figura abaixo: 
 
 G 
5 Vendedor 
6 André Luiz 
 
 
 
MS Excel 2000 Avançado 
Alexandre Dantas Página: 5 
ESCOLA TÉCNICA DE INFORMÁTICA
Suponha também que você deseja calcular qual foi o total vendido pelo vendedor André Luiz. Digite numa 
célula livre qualquer a fórmula abaixo: 
=BDSoma(vendas;E3;G5:G6) 
Como já vimos, o primeiro parâmetro é a área da tabela com os títulos é nós nomeamos a planilha como 
vendas, poderíamos também especificar A3:E30. O segundo parâmetro especifica o título da coluna de 
referência, que no nosso exemplo é a coluna valor - o que queremos somar – poderíamos digitar a palavra 
valor, seria a mesma coisa. Porém aconselhamos que você sempre digite o endereço, para não correr o risco 
de escrever errado. O terceiro parâmetro especifica a faixa de critérios. 
Agora pense um pouco, o que seria mudado na fórmula se você quisesse somar as vendas de André Luiz 
porém só as vendas do dia 08/07/99. 
E aí, a que conclusão você chegou? 
Basta incluir mais uma coluna nos critérios, como mostra a figura abaixo e incluir a alteração na fórmula. 
 
 G H 
5 Vendedor Data Venda 
6 André Luiz 08/07/99 
 
=BDSoma(vendas;E3;G5:H6) 
Estão marcados de vermelho as alterações necessárias. 
 
Exercícios propostos 
Crie a tabela abaixo e responda as questões inserindo as funções de BD que achar necessário. 
 
Lojas Barateiras 
 
Data Produto Preço Unitário Quantidade Valor Vendedor 
01/01/99 Mesa 57,00 1 57,00 Ana 
01/01/99 Cama 100,00 2 200,00 Ana 
01/01/99 Geladeira 500,00 2 1.000,00 Ana 
01/01/99 Geladeira 500,00 1 500,00 Paula 
02/01/99 Fogão 199,00 3 597,00 Paula 
02/01/99 Mesa 57,00 2 114,00 Ana 
02/01/99 Mesa 57,00 2 114,00 Maria 
03/01/99 Mesa 57,00 1 57,00 Joana 
03/01/99 Mesa 57,00 3 171,00 Mércia 
04/01/99 Cama 100,00 4 400,00 Mércia 
04/01/99 Geladeira 500,00 2 1.000,00 Mércia 
04/01/99 Geladeira 500,00 1 500,00 Joana 
05/01/09 Geladeira 500,00 1 500,00 Ana 
 
Perguntas 
 
1) Quanto vendeu a vendedora ? 
 
2) Quantas vendas foram realizadas no dia ? 
 
3) Quanto ganhou de comissão o vendedor das vendas do produto ? 
 
4) Quanto foi vendido entre os dias (datas) e ? 
 
 
MS Excel 2000 Avançado 
Alexandre Dantas Página: 6 
ESCOLA TÉCNICA DE INFORMÁTICA
 
5) Quanto foi pago de comissão aos vendedores sobre as vendas do produto ? 
 
6) Quanto foi pago ao vendedor no período de ate ? 
 
 
2.Formatação condicional 
As vezes é necessário formatar uma ou mais células, de acordo com seu preenchimentoou de outras células. 
Por exemplo é possível fazer com que células que tenham valores inferiores a um certo limite, fiquem com a 
cor da fonte ou tenham um sombreamento diferente. 
 
 
 
Clique no menu Formatar e em seguida Formatação Condicional. Será apresentada a janela acima. Sob o 
título Condição 1, na caixa combinação, são apresentadas 2 (duas ) opções, a saber: “O valor da célula é” 
é usada quando queremos formatar a célula de acordo com o seu conteúdo e “A fórmula é” é usada quando 
queremos formatar uma célula de acordo com o conteúdo de outra. 
 
Veremos um exemplo de como formatar uma célula de acordo com seu conteúdo. 
 
 
 
Suponha que as células da coluna A, onde temos o nome dos vendedores, fiquem com a cor da fonte azul, 
quando seu conteúdo for igual à José Augusto. 
Passo a passo: 
1. Selecione as células da coluna A; 
2. Clique no menu formatar – formatação condicional; 
 
 
MS Excel 2000 Avançado 
Alexandre Dantas Página: 7 
ESCOLA TÉCNICA DE INFORMÁTICA
 
3. Certifique-se de que as opções estejam iguais as da tela acima e clique no botão formatar; 
 
4. Defina a cor azul e clique OK, fechando a janela de formatar células; 
5. Clique OK para confirmar a formatação condicional. 
 
Note que as células que já tiverem o seu conteúdo igual a José Augusto, já estão azuis. Para verificar com 
funciona, altere o nome de outro vendedor para José Augusto e veja que ela já fica também azul. 
3.Subtotais 
Esse recurso do Excel nos permite realizar totalizações em planilhas. Observe a planilha abaixo: 
Data Produto Preço Unitário Quantidade Valor total 
05/05/00 Cama 200,00 2 400,00 
05/05/00 Cadeira 70,00 1 70,00 
05/05/00 Mesa 490,00 2 980,00 
08/05/00 Cama 200,00 3 600,00 
08/05/00 Mesa 490,00 1 490,00 
08/05/00 Cadeira 70,00 2 140,00 
08/05/00 Mesa 490,00 3 1.470,00 
09/05/00 Cama 200,00 2 400,00 
09/05/00 Fogão 300,00 2 600,00 
10/05/00 Cadeira 70,00 2 140,00 
10/05/00 Fogão 300,00 1 300,00 
10/05/00 Cama 200,00 3 600,00 
10/05/00 Cama 200,00 4 800,00 
11/05/00 Cadeira 70,00 1 70,00 
11/05/00 Mesa 490,00 2 980,00 
11/05/00 Cama 200,00 2 400,00 
 
 
MS Excel 2000 Avançado 
Alexandre Dantas Página: 8 
ESCOLA TÉCNICA DE INFORMÁTICA
11/05/00 Fogão 300,00 2 600,00 
11/05/00 Cadeira 70,00 1 70,00 
 
Suponha que você queira gerar um relatório de vendas, subtotalizando por dia. 
Para resolver este problema é necessário classificar a tabela pela coluna em que você deseja agrupar. 
No nosso exemplo é necessário classificar a tabela por data, observe também que no exemplo acima a 
tabela já esta classificada, se não estivesse, seríamos obrigados a classifica-la. 
Muito bem, agora vamos usar o recurso subtotais. O primeiro passo é selecionar a tabela inteira (clique 
dentro da tabela e pressione CTRL + asterisco), feito isso clique no Menu Dados – Subtotais, será 
apresentada a tela abaixo: 
 
 
 
Na caixa de combinação “A cada alteração em:” são apresentados todos os títulos das colunas. Selecione 
Data, pois queremos que quando mudar a data ele totalize. 
Na caixa de combinação “Usar função:” são apresentados todas as funções disponíveis. Selecione Soma, 
pois queremos que sejam somados os valores. 
Na caixa de combinação “Adicionar subtotal a:” são apresentados todos os títulos das colunas. Selecione 
Valor total, pois queremos que a coluna somada seja ela. 
A caixa de seleção Substituir subtotais atuais deve ser ativada quando a tabela já estiver com algum 
subtotal e você quiser substituir os subtotais já apresentados pelos que você está fazendo agora. No nosso 
caso (1o. exemplo) deixe desativada. 
A caixa de seleção Quebra de página entre grupos deve ser ativada se quiser que o Excel coloque, 
automaticamente, uma quebra de página após uma mudança da coluna indicada na caixa de combinação A 
cada alteração em. No nosso exemplo quando mudar de data. 
A caixa de seleção Resumir abaixo dos dados deve ser ativada se quiser que os dados sejam totalizados 
abaixo de cada quebra e o total geral abaixo da tabela. O default é subtotalizar antes. 
Observe como ficou a nossa tabela após aplicação do recurso estudado. 
 
 
 
MS Excel 2000 Avançado 
Alexandre Dantas Página: 9 
ESCOLA TÉCNICA DE INFORMÁTICA
 
 
Note que do lado esquerdo da tabela, quando apresentada com subtotais, são apresentados símbolos – e +. 
Esses símbolos sevem para aplicar nos subtotais filtros de visualização (exibição). Clique no sinal de – 
(subtração) e observe que o conjunto de linhas associadas é ocultado e é apresentada apenas a linha de 
totalização correspondente ao conjunto. O sinal de adição volta a apresentar as linhas que estavam ocultas. 
 
4.Validação 
Permite que os dados digitados em células obedeçam a regras previamente estabelecidas. 
Imagine que você está preenchendo uma planilha de notas escolares, e que as notas devem ser sempre 
numéricas e devem variar entre 0 (zero) e 10 (dez). 
 
 
Aluno: Bruna de Oliveira Matrícula 220000671 CursoJornalismo 
 
Disciplina 1o.Exercício 2o.Exercício 3o.Exercício Total de Pontos Média 
Pesquisa de Campo 
Redação 
Português 
Linguística 
 
No exemplo acima, a área com sombreamento amarelo deve receber as notas das dos exercícios de cada 
disciplina. 
Durante a digitação é provável que o operador cometa um erro de digitação e coloque, por exemplo, uma 
nota 65 quando seria 6,5. Para resolver esse problema a garantir que as notas devem variar entre 0 e 10, 
inclusive as duas, proceda assim: Primeiro selecione a área correspondente. No nosso exemplo selecione a 
área amarela. Depois clique no Menu Dados – Validação. Será apresentada a tela a seguir: 
 
 
 
MS Excel 2000 Avançado 
Alexandre Dantas Página: 10 
ESCOLA TÉCNICA DE INFORMÁTICA
 
 
Note que a janela Validação de dados possui 3 (três) guias. Na primeira guia você especifica a validação 
em si. A segunda guia você define uma mensagem de entrada, ou seja, quando você clicar na célula, será 
apresentada uma mensagem de orientação. A terceira guia serve para que você defina o nível de bloqueio. 
Vamos deixar de papo-furado e começar logo a incrementar a validação necessária. 
Tendo selecionado a área em que desejamos colocar a validação e estando no Menu Dados – Validação, 
certifique-se de que a guia Configurações está em primeiro plano. 
4.1. Definindo uma validação Decimal 
Clique na caixa de combinação Permitir e escolha a opção Decimal, pois, as notas inseridas podem ser 
decimais, agora clique na caixa de combinação Dados e selecione Entre, pois, o valor de cada nota pode 
variar dentro de um intervalo. Por último clique nas caixas de texto Mínimo e Máximo, especificando os 
limites 0 (mínimo) e 10 (máximo). 
4.2. Ajustando uma mensagem de entrada 
 
 
 
 
 
MS Excel 2000 Avançado 
Alexandre Dantas Página: 11 
ESCOLA TÉCNICA DE INFORMÁTICA
Clique na guia Mensagem de Entrada e sob o título “Título:” digite um título para a mensagem de entrada 
e sob o título “Mensagem de entrada:” digite um texto para orientar o usuário. 
 
Orientação para a prática 
A configuração de uma mensagem de entrada só é utilizada na prática quando a validação é feita numa célula onde se 
recebe um parâmetro, ou coisa parecida. A colocação de mensagens de entrada em células próximas (região) numa 
tabela não fica muito boa, pois, a cada célula a ser preenchida a mensagem de entrada se repete se tornando cansativo. 
 
4.3. Definindo o nível de bloqueio 
 
 
 
Clique na guia Alerta deerro 
O Excel oferece 3 (três) níveis de bloqueio. Em todos eles você pode colocar um texto para ser lido pelo 
operador, quando for cometido um erro no preenchimento da célula. 
O primeiro deles e o mais “rígido” é o Estilo Parar. Esse nível inibe totalmente a transcrição de dados para 
célula se o valor estiver contrariando a validação. 
 
 
 
O segundo um pouco mais flexível. 
 
 
 
O terceiro dará apenas uma informação. 
 
 
MS Excel 2000 Avançado 
Alexandre Dantas Página: 12 
ESCOLA TÉCNICA DE INFORMÁTICA
 
 
 
Escolha o nível de bloqueio e coloque uma mensagem apropriada para cada situação. 
A validação de dados não serve apenas para números decimais, use validação para criticar o preenchimento 
de células com: números inteiros, Data, Hora, comprimento do texto e também com Listas e Fórmulas. 
 
 
 
Na sala de aula seu professor fará mais exemplos. Gostaria de enfatizar a validação de Lista. 
Veja o exemplo abaixo: 
 
Produtos Cama 
 Fogão 
 Geladeira 
 Mesa 
 Televisor 
 
Data Produto Preço Unitário Quantidade Valor total 
05/05/00 200,00 2 400,00 
05/05/00 70,00 1 70,00 
05/05/00 490,00 2 980,00 
08/05/00 200,00 3 600,00 
08/05/00 490,00 1 490,00 
08/05/00 70,00 2 140,00 
08/05/00 490,00 3 1.470,00 
09/05/00 200,00 2 400,00 
Observe que os nomes (descrição) dos produtos estão digitados na tabela. Para garantir que durante a 
digitação do nome do produto vendido não seja transcrito de forma errada, faremos uma validação de Lista. 
 
 
MS Excel 2000 Avançado 
Alexandre Dantas Página: 13 
ESCOLA TÉCNICA DE INFORMÁTICA
Para resolver esse problema, selecione na tabela a área onde os nomes dos produtos serão digitados (coluna 
B, abaixo do título Produto), feita a seleção, clique no Menu Dados – Validação, na guia Configurações 
selecione Permitir Lista e na caixa de texto Origem defina a área da tabela onde já estão digitados os nome 
de todos os produtos (lista). 
 
 
 
Depois de definir, opcionalmente, Mensagem de Entrada e Alerta de Erro, clique no botão OK e vamos 
testar. 
Clique numa das células onde foram inseridas validações de lista e note a presença de uma “setinha”, clique 
na setinha e veja a lista. Não é interessante! 
 
 
 
A origem de dados para a validação de listas só é permitida na mesma planilha, entretanto existem alguns 
truques para contornar esta limitação. Comente com seu professor que truques seriam esses. 
 
4.4. Consolidar 
Esse recurso é usado para consolidar, totalizando linhas com a mesma identificação entre planilhas e pastas. 
Vamos criar um exemplo para facilitar o entendimento. 
Estudo de caso 
 
 
MS Excel 2000 Avançado 
Alexandre Dantas Página: 14 
ESCOLA TÉCNICA DE INFORMÁTICA
O Ibratec repassa aos seus alunos alguns itens oriundos do pólo irradiador, órgão do Ibratec onde é feita a 
coordenação de todas as unidades. Para ajudar a funcionária Mirthes, responsável pela distribuição dos 
materiais às unidades, vamos construir uma pasta contendo as seguintes planilhas: Pólo-Irradiador, Boa 
Viagem, CDU, Graças, Piedade, JPessoa e Maceió. 
Observe o layout de cada planilha. 
 
Semanalmente as unidades remetem uma planilha conforme o modelo acima, via E-mail, para o Pólo-
Irradiador. A Srta. Mirthes de Montserrat, exímia conhecedora do Excel, usa o recurso consolidar para 
totalizar os pedidos por tipo, solicitar a confecção dos mesmos, e envia-los de volta a cada unidade. 
Supondo que a Srta. Mirthes resolva copiar cada planilha e colar dentro de uma mesma pasta (em planilhas 
diferentes) cada uma das tabelas remetidas pelas unidades. 
Resolvendo o problema 
Suponha que a Srta Mirthes foi fazer um estágio na Microsoft e você assumiu o lugar dela. 
A primeira coisa a ser feita é inserir uma nova planilha (vazia) e clicar na célula onde será construído o 
início da consolidação, feito isso clique no Menu Dados – Consolidar, será apresentada a tela abaixo: 
 
Na caixa de combinação “Função:” você deve especificar a função desejada, no nosso exemplo selecione 
Soma. 
A caixa de texto “Referência:” serve para você identificar as diversas planilhas a serem usadas na 
consolidação. Marque uma a uma, inclusive com os títulos, após selecionar cada uma, clique no botão 
ADICIONAR. Se os dados a serem consolidados estiverem em outra planilha, clique no botão 
PROCURAR, localize o arquivo e selecione nele a área de referencia. 
 
 
MS Excel 2000 Avançado 
Alexandre Dantas Página: 15 
ESCOLA TÉCNICA DE INFORMÁTICA
Caso tenha marcado uma área errada e já adicionada, identifique-a dentro da caixa de listagem “Todas as 
referências:” clicando sobre ela e clique no botão EXCLUIR. 
As caixas de seleção Linha superior e Coluna esquerda devem estar marcadas para que na montagem da 
tabela de consolidação esses títulos sejam exibidos. 
Observe agora como ficou construída a planilha consolidada: 
 
Totais dos Materiais a serem remetidos às unidades 
Referência: de 2 à 6 de maio de 2000 
 Preço Unitário Quantidade Valor 
Apostila de Word Avançado 45,00 19 142,50 
Bloco rascunho 3,00 17 17,00 
Kit Vestibular 40,00 15 300,00 
Apostila de Excel Básico 12,00 16 96,00 
Histórico Escolar (2a via) 20,00 3 30,00 
Recibo de pagamento - 2 - 
 
5.Atingir Meta 
Esse recurso tem o objetivo de fazer uma célula atingir um determinado valor, variando uma outra célula. 
Naturalmente, a célula que irá atingir determinado valor deve ter uma fórmula, e que a célula que irá variar 
deve fazer parte do cálculo. 
Vejamos no exemplo abaixo: 
 
Festa de Formatura do PFSU10 
 
Descrição das despesas Valor 
Aluguel do clube 300,00 
Placa comemorativa 350,00 
Recursos humanos 450,00 
Descartáveis 150,00 
Conjunto "Os Incríveis" 350,00 
Alimentação (carnes) 450,00 
Alimentação (acompanhamentos) 100,00 
Bebidas 500,00 
Brindes 350,00 
Total 3.000,00 
 
A célula que do valor total dos itens tem uma fórmula de soma, que é calculada somando todos os itens 
acima. Imagine que o durante o curso, os alunos juntaram através de cota, um montante em dinheiro 
equivalente a R$ 3.000,00 (três mil reais), e resolveram gastar tudo na festa de formatura. Depois de 
lançarem os valores das despesas, verificaram que houve sobre de dinheiro. O total das despesas não atingir 
o limite de R$ 3.000,00, e eles sabem que não pode haver sobra. Claro, com quem ficaria a sobra ? 
Para solucionar o problema, os alunos resolveram utilizar o recurso Atingir Meta do Excel e decidiram que 
o dinheiro que sobraria seria aplicado em Brindes. 
 
Solução do Atingir Meta 
Clique na célula que tem a fórmula de soma, clique no Menu Ferramentas – Atingir meta. 
 
 
MS Excel 2000 Avançado 
Alexandre Dantas Página: 16 
ESCOLA TÉCNICA DE INFORMÁTICA
 
Será apresentada a tela abaixo: 
 
Ajuste as informações solicitadas: 
� Definir célula: Indique a célula que tem o somatório, como estava na célula que tinha a fórmula 
ela já aparece correta. 
� Para valor: Defina o limite a ser atingido. No exemplo 3000. 
� Variando a célula: Indique a célula que tem (ou terá) o valor a ser gasto em brindes. 
Depois de pressionada a tecla OK, a célula somatório passou a ter R$ 3.000,00 e a célula que tem o valor 
dos brindes passou a ter R$ 350,00. 
As limitações do atingir meta são visíveis, a principal é que somente uma célula pode variar. Existe um 
recurso no Excel, o Solver, que é coisa de gente grande e que vai, com certeza, preencher suas expectativas 
em tudo que o atingir meta não resolveu. Veremos esse tópico mais adiante. 
6.Cenários 
Esse tópico é muito interessante porque permite que você crie situações para estudos de caso. 
Imagine que em uma determinada empresa, composta de 3 (três) diretores, houveuma reunião para decidir 
como seriam os gastos para o ano de 2001. Foi criada uma tabela no Excel para que cada diretor preencha 
as células de acordo com suas opinião pessoal. O problema é que depois que os dados são preenchidos por 
um diretor, eles devem ser impressos e depois apagados para outro diretor possa lançar seus dados. 
Resolveremos esse problema com ajuda do recurso Cenários. Guardaremos as respostas de cada diretor 
num cenário, esse cenário terá um nome, e quando nós quisermos voltar a ver as respostas de um dos 
diretores, e só mostrar o cenário que corresponde a suas respostas. 
A planilha abaixo foi concebida para receber as respostas dos diretores. 
 
 
 
MS Excel 2000 Avançado 
Alexandre Dantas Página: 17 
ESCOLA TÉCNICA DE INFORMÁTICA
Obs: O nome do diretor está na célula B12. 
Para usar o recurso, clique no Menu Ferramentas – Cenários, será apresentada uma tela informando que 
nenhum cenário foi definido, clique no botão ADICIONAR e será apresentada a tela abaixo: 
 
Clique na caixa de texto “Nome do cenário” e digite o nome do 1o. diretor. José Maria. Em seguida clique 
na caixa de texto “Células variáveis” e indique todas as células que serão preenchidas pelo 1o. diretor, 
inclusive o nome dele na célula B12. Clique no botão OK. Será apresentada agora uma tela onde você vai 
digitar os dados que correspondem as respostas do diretor (células variáveis). 
 
 
Após o preenchimento, clique no botão OK ou adicionar (adicionar um novo cenário). Faça isso para cada 
diretor. 
Após a criação dos cenários, você pode, através da tela Gerenciador de cenários, voltar a exibir os dados de 
um determinado cenário (mostrar), adicionar um novo cenário (adicionar), excluir um cenário que não quer 
mais (excluir), alterar dados de um cenário (editar), mesclar e resumir. 
 
 
 
MS Excel 2000 Avançado 
Alexandre Dantas Página: 18 
ESCOLA TÉCNICA DE INFORMÁTICA
 
7.Auditoria 
Algumas vezes você é surpreendido no Excel com fórmulas com erros, dependendo do tamanho da planilha 
é importante usar o recurso auditoria para rastrear erros em células antecedentes e precedentes, e assim 
tentar descobrir o porquê do erro e também saber quais as células que serão afetadas por esse erro. 
Para utilizar esse recurso, coloque o cursor na célula que tem o erro, clique no Menu Ferramentas – 
Auditoria, clique ativando a barra de ferramenta auditoria. 
 
 
Clicando nos botões rastrear antecedentes e precedentes são apresentadas setas coloridas indicando as 
células que originaram o erro e também para as células que dependem da célula que tem o erro. 
 
8.Solver 
O recurso Solver pode não estar disponível em seu computador. Clique no Menu Ferramentas e verifique se 
existe a opção Solver, se existir pode pular para o tópico O que é o Solver ?, caso contrário clique no 
Menu Ferramentas – Suplementos, e será apresentada a tela a seguir: 
 
 
 
 
MS Excel 2000 Avançado 
Alexandre Dantas Página: 19 
ESCOLA TÉCNICA DE INFORMÁTICA
Ative a caixa de seleção correspondente ao Solver e clique no botão OK para instala-lo. 
 
O que é o Solver ? 
Até mesmo os programadores mais experientes, que preferem dar soluções a seus problemas através de 
programação, curvam-se ao Solver. Enfim o Solver é um recurso do Excel que ajuda ao usuário a encontrar 
soluções para problemas usando algoritmos bem sofisticados. 
A pesquisa para encontrar a(s) melhor(es) solução(ões) segue dois padrões: 
Newton - Usa o método quase-Newton que geralmente exige mais memória e bem menos iterações do que 
o método gradiente Conjugado. Conjugado - Requer menos memória do que o método Newton, mas 
geralmente exige mais iterações para atingir determinado nível de precisão. Use esta opção quando houver 
um problema grande e a quantidade de memória disponível for uma preocupação, ou quando as várias 
iterações do processo de solução revelarem um progresso lento. 
O Método default é o Newton. 
Vamos montar uma situação para facilitar o entendimento do poderoso recurso solver. Observe a planilha 
abaixo: 
 
 
Os alunos do PFSU10 estão com um problema. Eles juntaram R$ 3.000,00 (três mil reais) durante todo o 
curso e querem gastar todo (o máximo possível) na sua festa de formatura. Eles já contabilizaram as 
despesas, exceto as despesas do item Brindes. Planejaram gastar todo dinheiro que sobrar em 3 (três) tipos 
de brindes, John Walker, Ovo de Páscoa e Caixas de disquetes. Inclusive já fizeram a cotação de preço 
desses brindes, só não sabem quantos brindes tem que comprar de cada um para aproveitar o dinheiro da 
melhor maneira possível. 
Entenda melhor a situação. Se eles comprarem 10 litros de John Walker e 3 caixas de disquetes, ainda vão 
restar R$ 6,00. Se eles comprarem 10 litros de John Walker e comprarem 1 Ovo de páscoa e 1 caixa de 
disquetes, ainda sobra dinheiro. Como resolver ? 
Aplicaremos o recurso Solver para encontrar a melhor resposta. 
Clique no Menu Ferramentas – Solver e configure a janela assim: 
 
 
MS Excel 2000 Avançado 
Alexandre Dantas Página: 20 
ESCOLA TÉCNICA DE INFORMÁTICA
 
A célula destino é a que tem o valor total, queremos que ela atinja o valor de R$ 3.000,00, as céulas que 
irão variar são as que têm as quantidades dos brindes (D21:D23), as restrições são as seguintes: as 
quantidades dos itens têm que ser maior do que zero D21:D23>=0 e tem que ser positivos 
D21:D23=número. 
Ele encontrará a melhor combinação possível de acordo com o algoritmo seguido. 
 
Festa de Formatura do PFSU10 
 
Valor arrecadado 3.000,00 
 
Descrição das despesas 
Item valor 
Aluguel do clube 300,00 
Placa comemorativa 350,00 
Recursos humanos 450,00 
Descartáveis 150,00 
Conjunto "Os Incríveis" 350,00 
Alimentação (carnes) 450,00 
Alimentação (acompanhamentos) 100,00 
Bebidas 500,00 
Brindes 350,00 
Total 3.000,00 
 
Brindes 
Produto Valor Unitário Quantidade Total 
John Walker 32,00 7 224,00 
Ovo de Pascoa nr 30 21,00 6 126,00 
Caixas de disquetes 8,00 0 - 
 
O Solver, após encontrar a solução oferece ainda relatórios de performance, permite que você mantenha a 
solução do Solver ou restaure os valores originais. Se quiser pode ainda salvar a solução como cenário. 
 
 
 
MS Excel 2000 Avançado 
Alexandre Dantas Página: 21 
ESCOLA TÉCNICA DE INFORMÁTICA
 
9.Listas 
Recurso incorporado pelo Excel para facilitar a digitação de listas comuns. 
Veja como funciona. 
Digite em qualquer célula do Excel a palavra Agosto. Agora aponte o mouse na alça de preenchimento da 
célula onde você digitou a palavra agosto e arraste até um certo ponto. Note que enquanto você arrasta o 
Excel apresenta o nome de outros meses. Quando você soltar o botão do mouse, a lista contendo o nome 
dos meses começando em Agosto, foi inserida das células de destino. 
 
Você deve estar pensando como é que o Excel sabe o nome dos meses subseqüentes. 
O Excel trás consigo algumas listas já prontas, tais como: Jan, Fev , Mar,.......Dez; Dom, Seg, Ter, 
Qua,.....Sab; e ainda com os dias da semana sem abreviar. 
9.1. Visualizando as listas existentes. 
Clique no Menu Ferramentas – Opções – e em seguida na guia Listas. Será apresentada a janela abaixo: 
 
 
 
 
 
MS Excel 2000 Avançado 
Alexandre Dantas Página: 22 
ESCOLA TÉCNICA DE INFORMÁTICA
9.2. Adicionando uma nova lista 
 
Clique na caixa de listagem Entradas da lista e digite os componentes da nova lista, use vírgula (ou 
ENTER) para separar os itens, quando a lista estiver completa, clique no botão Adicionar e em seguida 
OK. Para verificar se está funcionado, digite qualquer um dos componentes da lista e copie, arrastando, até 
onde desejar. 
 
9.3. Importando dados para formar uma nova lista. 
Asvezes você já tem a lista digitada na planilha e não teria sentido ter que re-digitar para inclui-la na lista. 
Clique no Menu Ferramentas – Opções – e em seguida na guia Listas. Agora clique dentro da objeto 
selecionar. 
 
 
 
Selecione na planilha as células que você deseja incluir na lista e clique no botão Importar. Pronto a nova 
lista já está criada e poderá ser usada em novas planilhas. 
 
9.4. Excluindo uma Lista 
 
Abra a guia a que se refere a figura principal desse assunto, clique na lista e clique no botão Excluir. 
 
10.Macros 
 
O que são Macros? 
São procedimentos editados (VBA) ou gravados, usados para automatizar tarefas executadas com 
maior freqüência. 
Uma macro é uma seqüência de comandos e funções armazenados em um módulo do Visual Basic (VBA) e 
pode ser executada sempre que você precisar executar a tarefa. Quando você grava uma macro, o Excel 
escreve as ações realizadas à medida que você executa uma seqüência de comandos em módulos que são 
anexados a uma pasta de trabalho. Em seguida, você executa a macro para repetir, ou "reproduzir", as 
ações. 
Antes de gravar uma macro, planeje as etapas e os comandos que você deseja que a macro execute. Se 
cometer um erro durante a gravação da macro, as correções feitas também são gravadas. 
Se você seguiu as etapas anteriores para gravar a macro de retorno automático de texto, o resultado pode 
parecer com este: 
Gerenciar suas macros Com o Editor do Visual Basic, você pode editar macros, copiar macros de um 
módulo para outro, copiar macros entre pastas de trabalho diferentes, renomear os módulos que armazenam 
as macros ou renomear as macros. 
Segurança da macro O Microsoft Excel 2000 fornece proteção contra vírus que podem ser transmitidos 
através das macros. Se você compartilha macros com outros usuários, você pode certificá-las com uma 
assinatura digital de forma que os outros usuários possam verificar que as macros são de origem confiável. 
Sempre que você abrir uma pasta de trabalho que contenha macros, poderá verificar a origem das macros 
antes de ativá-las. 
 
 
 
MS Excel 2000 Avançado 
Alexandre Dantas Página: 23 
ESCOLA TÉCNICA DE INFORMÁTICA
10.1. Construindo uma macro: 
 
1. Planejando as ações de uma macro. 
 
Suponha que queremos criar uma macro para simplesmente mudar de planilha. 
 
2. Gravando uma macro. 
 
Você pode fazer isso partindo do Menu Ferramentas – Macro – Gravar nova macro. 
 
 
 
Uma outra maneira é na barra de ferramentas Visual Basic, clicar no botão Gravar nova macro. 
 
 
 
3. Na janela Gravar macro você define: 
 
a. O nome da macro – O Excel sempre irá sugerir um nome genérico (macro1, macro2,....) , coloque 
um nome mais sugestivo. 
b. Apesar de poder definir uma tecla de atalho, combinando CTRL+ (Tecla), na prática isso é muito 
raro. 
c. Defina também o local de armazenamento da macro. Se você definir a pasta pessoal de macros, ela 
estará disponível em todas as pastas.(XLS). 
d. Na descrição (opcional) define um comentário a respeito das funções da macro que será gravada. 
 
 
 
 Depois de clicar no botão OK da janela de Gravar macro, note a presença de uma nova barra de 
ferramentas. 
 
 
 
MS Excel 2000 Avançado 
Alexandre Dantas Página: 24 
ESCOLA TÉCNICA DE INFORMÁTICA
 
 
Observação: O botão (parar gravação) só deve ser pressionado quando todas as ações forem efetuadas. 
 
4. Agora clique na guia que corresponde a planilha destino. 
 
 
 
5. Agora clique no botão Parar gravação. 
 
10.2. Executando uma macro 
As macros podem ser executadas das seguintes formas: 
 
1. Pressionando a combinação de teclas, CTRL+(tecla), definida no momento da gravação. 
2. No Menu Ferramentas – Macro – Macros. 
 
 
 
Note que na janela Macro, todas as macros gravadas (e também as codificadas em VBA) são apresentadas 
dentro da caixa de listagem, para executar uma delas e só clicar no seu nome e em seguida clicar no botão 
executar. 
 
A janela macros também é apresentada quando você clica no botão Executar macro, da barra de 
ferramentas Visual Basic. 
 
3. Clicando numa figura (imagem) que esteja associada a uma macro. Esta figura servirá como botão acionador da 
macro. Veremos no próximo tópico como fazer essa associação. 
 
4. Clicando num botão que esteja associado a uma macro, numa barra de ferramentas qualquer. 
 
5. Macros automáticas – No Excel existem macros que são executadas automaticamente quando uma determinada 
pasta (arquivo) é aberta ou fechada. Mais adiante saberemos mais a respeito dessas macros automáticas. 
 
 
MS Excel 2000 Avançado 
Alexandre Dantas Página: 25 
ESCOLA TÉCNICA DE INFORMÁTICA
10.3. Atribuindo macros a figuras 
1. Insira uma figura qualquer dentro da planilha. Essa figura poder até um botão da barra de ferramentas 
formulários ou uma autoforma da barra de ferramentas desenho. Alguns objetos, tal como o botão da barra de 
ferramentas de formulário, pede essa atribuição assim que é inserido na planilha, já em outros (maioria) dos 
objetos (figuras), para fazer a atribuição é necessário clicar com o botão direito e em seguida clicar na opção 
atribuir macro. 
 
 
 
2. Na janela acima, clique no nome da macro e em seguida clique no botão OK. 
 
Você vai notar que ao passar o ponteiro do mouse sobre a figura, ele se transformará em uma mãozinha. 
 
 
 
 
 
 
. 
 
 
10.4. Criando uma nova barra de ferramentas 
 
Clique no Menu Ferramentas – Personalizar – Guia Barra de ferramentas. Depois clique no botão 
Nova, digite o nome da nova barra de ferramentas e clique em OK. 
 
Note a presença da nova barra de ferramentas. Sugiro que você a mova para fora da janela atual. Coloque-a 
junto às demais barras de ferramentas. 
 
 
� 
 
 
MS Excel 2000 Avançado 
Alexandre Dantas Página: 26 
ESCOLA TÉCNICA DE INFORMÁTICA
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
MS Excel 2000 Avançado 
Alexandre Dantas Página: 27 
ESCOLA TÉCNICA DE INFORMÁTICA
10.5. Associando suas macros a botões de barras de ferramentas. 
Ainda com a janela Personalizar aberta, clique na guia Comandos e selecione a categoria Macros. 
 
 
 
 
 
Observe que as macros estão sob o título Comandos. Aponte o mouse e arraste até a barra de ferramentas 
desejada. Pode ser a nova ou até uma outra barra de ferramentas qualquer. 
 
 
 
 
 
Quando você arrasta até a barra de ferramentas, o ponteiro do mouse se transforma numa barra vertical, 
indicando o local exato onde será inserido o botão. 
 
 
 
 
 
 
MS Excel 2000 Avançado 
Alexandre Dantas Página: 28 
ESCOLA TÉCNICA DE INFORMÁTICA
 
Quando você solta o botão do mouse, a identificação da macro 
fica como mostra a figura acima. Aponte o mouse sobre essa 
identificação e clique com o botão direito do mouse. Será 
apresentada a figura ao lado: 
 
Clique em Estilo padrão e em seguida Alterar a imagem do 
botão. Serão apresentados vários tipos de ícones (desenhos) e 
você deve selecionar um deles para ficar representando a 
macro na barra de ferramentas. 
 
 
Para definir a dica que será apresentada quando o ponteiro do 
mouse estiver sobre o botão altere o parâmetro nome dessa 
tela. 
 
 
 
 
 
10.6. Editando a imagem de botão 
 
Clique em editando a imagem de botão e redesenhe a imagem já selecionada, fazendo alguma alteração. 
 
10.7. Copiando e colando a imagem de botão 
 
Esse recurso é interessante porque permite que você personalize seu botão usando qualquer figura que 
esteja na área de transferência. 
 
Vamos testar ? 
 
• Feche a janela Personalizar e volte à planilha. 
• Insira uma figura qualquer na planilha. A intenção é apenas copiá-la, jogando na área de transferência. 
• Selecione a figura e clique no botão copiar. 
• Volte a exibir a janela personalizar e aponte o mouse sobre o botão da barra de ferramentas que você está 
trabalhando. 
• Clique com o botão direito e em seguida clique em colar imagem de botão. 
 
 Não é incrível! 
 
Cuidado! 
 
Com esse recurso você poderá alterar a imagem de todos os botões de todasas barras de ferramentas de seu 
Excel, mas não faça isso indiscriminadamente, alterando os padrões normais, um operador menos capacitado 
ou até mesmo você, poderá sentir alguma dificuldade em usar um Excel com os botões mais comuns diferentes. 
 
 
 
MS Excel 2000 Avançado 
Alexandre Dantas Página: 29 
ESCOLA TÉCNICA DE INFORMÁTICA
10.8. Excluindo Macros 
Para excluir uma macro, clique no Menu Ferramentas – Macro – Macros, clique na macro a ser excluída 
e clique no botão Excluir. 
10.9. Excluído Barras de Ferramentas 
Faça aparecer a janela personalizar, clique na guia barra de ferramentas, selecione a barra a ser excluída e 
clique no botão Excluir. 
11.Visualizando o código escrito em Visual Basic Application 
 
Toda macro quando é gravada, é também escrita numa linguagem de programação chamada VBA. Observe 
o código referente a gravação de uma macro com as seguintes ações: 
 
1. Clique numa planilha qualquer (plan2). 
2. Clique na célula A10. 
 
Para visualizar o código VBA referente a gravação de uma macro, clique no Menu Ferramentas – Macro – 
Macros, clique na identificação da macro e depois clique no botão editar. 
A janela do editor do VBA será apresentada. Veja você mesmo. 
 
 
 
Para sair do Editor do Visual Basic Application, feche a janela. 
Excluindo uma Macro 
Para excluir uma macro você deve clique no Menu Ferramentas – Macro – Macros, clique na identificação 
da macro e depois clique no botão excluir. 
11.1. Segurança 
Como já comentamos no início desse assunto, Macros, as macros podem ser gravadas ou escritas numa 
linguagem de programação. Uma linguagem de programação possui recursos que podem ser usados para 
prejudicar a performance de seu sistema, como, por exemplo, fazendo com que seu arquivo não possa ser 
mais aberto, ou ainda alterar inadvertidamente a formatação total de seu documento. Esses códigos nocivos 
podem estar presentes num documento copiado inocentemente por você. São chamados vírus de macro. 
Para garantir que o seu Excel (Pasta pessoal.xls e outros arquivos) não será contaminado por essas pestes, o 
Excel 2000 foi implementando com um recurso que iremos comentar agora. 
 
 
 
MS Excel 2000 Avançado 
Alexandre Dantas Página: 30 
ESCOLA TÉCNICA DE INFORMÁTICA
 
 
Na barra de ferramentas Visual Basic, apresentada acima, aparece um título Segurança..., clicando nela é 
apresentada a janela abaixo: 
 
 
 
Como pode ser lido no próprio texto da janela, existem 3 (três) opções. A primeira opção ativa ou desativa 
automaticamente as macros dependendo de sua origem. A segunda opção faz com que, quando um arquivo 
do Excel aberto e ele possui macros, o Excel perguntará se as macros devem ser ativadas ou não. A terceira 
ativa automaticamente todas as macros sem perguntar nada ao usuário. 
 
12.Filtro Avançado 
Esse recurso é muito usado na geração de relatórios. A grande vantagem do filtro avançado é que ele usa 
critérios e pode então ser programado. 
 
Observe a planilha abaixo: 
 
Nr Seq Placa Data 
chegada
Hora 
chegada
Data saída Hora saída Tempo de 
permanência
Valor do 
serviço
1 HFE-3455 03/05/00 8:01 03/05/00 12:30 4:29 13,45 
2 HTT-0999 03/05/00 8:10 03/05/00 16:00 7:50 23,50 
3 HGM-2775 03/05/00 9:00 03/05/00 10:00 1:00 3,00 
4 HRE-8771 03/05/00 10:00 03/05/00 18:00 8:00 24,00 
5 KJL-5521 03/05/00 15:00 04/05/00 8:00 17:00 51,00 
6 KMN-4555 04/05/00 10:00 04/05/00 12:00 2:00 6,00 
7 HTT-0999 04/05/00 10:10 04/05/00 15:00 4:50 14,50 
8 KGM-2775 04/05/00 11:00 04/05/00 15:01 4:01 12,05 
9 HRE-6633 04/05/00 11:04 04/05/00 18:00 6:56 20,80 
10 HFN-2865 04/05/00 12:00 04/05/00 17:50 5:50 17,50 
 
A planilha acima equivale ao controle de veículos estacionados no Shopping Ibratec, com o passar do 
tempo a quantidade de lançamentos será tanto que uma análise visual não responderá a perguntas tais como: 
O veículo cuja placa é KGM-2775, já esteve estacionado no Shopping Ibratec? Em caso afirmativo dê-me 
um histórico desses registros. 
 
 
MS Excel 2000 Avançado 
Alexandre Dantas Página: 31 
ESCOLA TÉCNICA DE INFORMÁTICA
Você até que poderia usar um Autofiltro para responder a questão, porém teria que criar na clicar no filtro 
Placa e escolher dentre tantas a placa do veículo que lhe interessa. Uma outra maneira de resolver o 
problema é usar uma função de banco de dados (BDCONTARA) e um Filtro avançado, além de obter um 
relatório bem mais apresentável você oferecerá ao seu cliente (usuário) uma interface de consulta 
profissional. 
 
Vamos então resolver o problema 
Em algum lugar da planilha, onde não serão digitados dados, crie os critérios. 
 
 
 
O critério acima servirá tanto para a função de BD quanto para o filtro avançado. 
Após a criação dos critérios, selecione a planilha inteira e clique no Menu Dados – Filtro – Filtro avançado. 
Será apresentada a tela a seguir: 
 
 
 
Certifique-se de que a ação selecionada seja Filtrar a lista no local. note que o Intervalo da lista já 
aparece selecionado. Selecione o intervalo de critérios e clique OK. 
 
O recurso de filtro avançado fica excelente quando combinado com macros. 
 
12.1. Exibindo todos os registros 
 
Observe que quando um filtro está ativado, as linhas exibidas são apresentada da cor azul. Para voltar a 
exibir todos os registros, clique no Menu Dados – Filtro – Mostrar todos. 
 
 
 
 
MS Excel 2000 Avançado 
Alexandre Dantas Página: 32 
ESCOLA TÉCNICA DE INFORMÁTICA
 
 
 
 
 
Exercício proposto 
 
Crie uma interface de consulta para a planilha acima, lembre-se de que seu usuário é totalmente 
despreparado e não conhece bem o Excel. Proteja tudo e oriente-o da melhor maneira possível. Você deve 
solicitar a ele a placa do carro e quando ele digitar, informe-o se o veículo já esteve ou não estacionado e 
em caso afirmativo, quantas vezes. Ofereça um botão (Macro) para ele visualizar os registros de 
estacionamento do veículo cuja placa ele digitou. 
 
13.Tabela Dinâmica 
 
Esse recurso do Excel é muito importante porquê facilita muito a geração relatórios modificando o layout 
original da planilha. 
 
Observe a tabela abaixo. Ela guarda todos os registros de vendas da loja. 
 
Data Produto Preço Unitário Quantidade Valor Vendedor 
24/05/00 Cama 299,90 1 299,90José 
24/05/00 Mesa 399,99 2 799,98Marcelo 
24/05/00 Cadeira 59,00 3 177,00Francisco 
24/05/00 Cama 299,90 1 299,90José 
24/05/00 Mesa 399,99 2 799,98José 
24/05/00 Cadeira 59,00 4 236,00Marcelo 
24/05/00 Cama 299,90 2 599,80João Neto 
24/05/00 Mesa 399,99 1 399,99Francisco 
25/05/00 Cadeira 59,00 1 59,00José 
25/05/00 Cama 299,90 1 299,90Marcelo 
 
Agora o gerente da loja necessita de um relatório consolidando quantas unidades de cada produto cada 
vendedor vendeu. 
 
 
 
MS Excel 2000 Avançado 
Alexandre Dantas Página: 33 
ESCOLA TÉCNICA DE INFORMÁTICA
 
Soma de Quantidade Produto 
Vendedor Cadeira Cama Mesa Total Global 
Francisco 3 2 1 6 
João Neto 2 1 3 
José 2 2 2 6 
Marcelo 4 1 5 10 
Total Global 9 7 9 25 
 
Para obter o relatório acima, bem como outros que podem ser criados, apresentamos o recurso “Relatório 
de Tabela Dinâmica”. 
 
Para construir o produto acima, comece selecionando a tabela inteira, clique dentro da tabela e pressione 
CTRL + *. Com a planilha selecionada clique no Menu Dados – Relatório de Tabela e Gráficos Dinâmicos. 
O assistente assumirá o comando das ações de agora por diante. Na primeira etapa, confirma que os dados 
estão no Excel e escolha o relatório de tabela dinâmica. Depois é no clicar em Avançar. 
 
 
 
A segunda etapa pede a confirmação da área da tabela. Como ela já está selecionada e só clicar em 
Avançar. 
 
 
 
A terceira e última etapa serve apenas para que você escolha o local onde será apresentado o relatório de 
tabela dinâmica. Escolha como nova planilha e clique Concluir. 
 
 
 
MS Excel 2000 Avançado 
Alexandre Dantas Página: 34 
ESCOLA TÉCNICA DE INFORMÁTICAObserve bem a tela que será apresentada, pois é através dela que você monta seu relatório simplesmente 
arrastando as informações para construir o layout que quiser. 
 
 
 
Aponte o mouse nos títulos das colunas contemplados dentro da barra de ferramentas Tabela dinâmica e 
arraste até Solte os campos de página aqui. 
 
 
Depois que você arrastar todos os campos, desloque a barra de ferramentas até que ela fique junto das 
demais. Agora arraste Produtos até o título das colunas, arraste Vendedor até o título das linhas e arraste 
quantidade até o centro da planilha. Pronto já está pronta. Se quiser pode mudar tudo, ou ainda selecionar 
quais os produtos serão contabilizados. Pode fazer a mesma coisa com o vendedor. Isso não incrível! 
 
 
 
MS Excel 2000 Avançado 
Alexandre Dantas Página: 35 
ESCOLA TÉCNICA DE INFORMÁTICA
 
 
 
 
 
 
14.Outras funções que você pode precisar usar no projeto 
Somase – Adiciona as células especificadas por um determinado critério ou condição 
Sintaxe: =SOMASE(tabela sem os títulos; chave de localização; intervalo onde estão os valores a serem 
somados). 
Cont.se – Calcula o número de células não vazias em um intervalo que corresponde a uma determinada 
condição. 
Sintaxe: =CONT.SE (Intervalo; condição) 
Indireto – Retorna uma refer6encia indicada por um valor de texto. 
Sintaxe: =INDIRETO (Célula onde está o texto que deve valer uma referência) 
Ext.texto – Retorna um número específico de caracteres a partir de uma seqüência de caracterees de texto, 
com início na posição especificada. 
Sintaxe: =EXT.TEXTO( Cadeia de caracteres ou célula onde está a cadeira de caracteres; posição inicial na 
cadeia original; tamanho) 
Arred - Arredonda um número seguindo o padrão normal (para baixo ou para cima) até uma quantidade 
especificada de dígitos. 
Sintaxe: =ARRED(Célula onde está o valor ou o próprio valor;número de dígitos) 
Arredondar.para.cima – Arredonda um número para cima até uma quantidade especificada de dígitos. 
Sintaxe: =ARREDONDAR.PARA.CIMA(Célula onde está o valor ou o próprio valor;número de dígitos) 
Arredondar.para.baixo – Arredonda um número para cima até uma quantidade especificada de dígitos. 
Sintaxe: =ARREDONDAR.PARA.BAIXO(Célula onde está o valor ou o próprio valor;número de dígitos) 
Truncar - Trunca um número com a uma quantidade especificada de dígitos. 
Sintaxe: =TRUNCAR(Célula onde está o valor ou o próprio valor;número de dígitos) 
Procv - Retorna um dado em uma tabela. 
Sintaxe: =PROCV( chave a ser procurada;Tabela inteira sem os títulos;número de ordem seqüencial 
da coluna que contém o objeto de procura; FALSO/VERDADEIRO) 
 
 
MS Excel 2000 Avançado 
Alexandre Dantas Página: 36 
ESCOLA TÉCNICA DE INFORMÁTICA
Uma limitação da procv e que ela procura a chave na primeira coluna selecionada, ou seja para usar o 
procv a chave de procura tem que ser estar na primeira coluna da tabela. 
Proc - Retorna um dado em uma tabela. 
Sintaxe: = PROC( Chave a ser procurada; Vetor de procura; vetor de retorno) 
Existe também na forma de matriz, porém como vetor o seu poder aumenta, pois podemos localizar um 
dado na tabela partindo de uma outra coluna qualquer. Uma limitação dessa função é que ela exige que a 
planilha esteja classificada de forma crscente pela chave de procura. 
Cont.valores – Conta quantas células preenchidas existem no intervalo especificado. 
Sintaxe: =CONT.VALORES (Intervalo) 
SE - Retorna um valor se a condição avaliada for verdadeira e retorna outro valor se a condição avaliada 
for falsa. 
Sintaxe: =SE (Condição; valor se a condição for verdadeira;valor se a condição avaliada for falsa). 
As funções acima foram abordadas em sala de aula. Nesse ponto, gostaríamos apenas de ressaltar essas 
funções, que junto com as funções de Banco de Dados, são a base para a automação de planilhas.