AVANÇADO EXCEL

Prévia do material em texto

Universidade Regional de Blumenau 
 
 
 
 
Divisão de Tecnologia da Informação 
Seção de Apoio ao Usuário 
 
 
 
 
 
 
 
 
 
CURSO DE MICROSOFT 
 EXCEL 2010 AVANÇADO 
 
 
 
 
 
 
 
 
 
 
 
Gilson Klotz 
 
 
 
 
 
 2
Sumário 
 
Banco (Lista de Dados) ..................................................................................................... 4 
Classificação de Registros ................................................................................................. 5 
Listas de classificação personalizadas ............................................................................... 8 
Operadores Lógicos: “E” e “OU”...................................................................................... 9 
Filtrando dados ................................................................................................................ 10 
Autofiltro ..................................................................................................................... 10 
Personalizar AutoFiltro ............................................................................................... 11 
Filtro Avançado ........................................................................................................... 13 
Faixas de critérios ........................................................................................................ 15 
Critérios calculados ..................................................................................................... 16 
Importação de dados ........................................................................................................ 17 
Importação de arquivos do tipo texto: ......................................................................... 17 
Arquivos de texto no formato Tamanho Fixo ......................................................... 17 
Arquivos de texto no formato Delimitado ou com Delimitadores .......................... 18 
Importação de dados da Internet .................................................................................. 20 
Trabalhando com dados externos através do MS-Query ............................................. 22 
Criando uma consulta de dados através do assistente de consulta .......................... 22 
Interface do Microsoft Query .................................................................................. 24 
Operações de busca e troca.............................................................................................. 26 
Subtotais .......................................................................................................................... 27 
Funções de Banco de Dados ............................................................................................ 32 
Função BDMÉDIA ...................................................................................................... 32 
Função BDMÁX ......................................................................................................... 33 
Função BDMÍN ........................................................................................................... 33 
Função BDSOMA ....................................................................................................... 34 
Função CONTAR ........................................................................................................ 34 
Função BDCONTARA ............................................................................................... 34 
Função BDEXTRAIR ................................................................................................. 34 
Função CONT.SE ............................................................................................................ 35 
Funções SOMASE........................................................................................................... 36 
Funções Encadeadas ........................................................................................................ 37 
Função PROCV ............................................................................................................... 37 
Função SE ........................................................................................................................ 39 
Encadeando Funções ............................................................................................... 40 
Mais exemplos ......................................................................................................... 42 
Formatação Condicional .................................................................................................. 43 
Formulários...................................................................................................................... 45 
Adicionar o botão Formulário à Barra de Ferramentas de Acesso Rápido ................. 45 
Inserindo novos Dados ................................................................................................ 46 
Atualizando Dados ...................................................................................................... 47 
Excluindo dados .......................................................................................................... 47 
Localizando Registros em um banco ou lista de dados ............................................... 47 
Tabela Dinâmica .............................................................................................................. 49 
Gráfico Dinâmico ............................................................................................................ 51 
Consolidar........................................................................................................................ 52 
Funções de cálculo disponíveis para a consolidação de dados .................................... 53 
Vínculos ........................................................................................................................... 54 
Cenários ........................................................................................................................... 55 
 3
Suplementos .................................................................................................................... 56 
Solver ........................................................................................................................... 57 
Atingir Meta .................................................................................................................... 58 
Especificações do Excel 2010 ......................................................................................... 59 
Especificações e limites de cálculo ............................................................................. 60 
Especificações e limites de gráficos ........................................................................... 60 
Especificações e limites de relatório tabela ou gráfico dinâmicos ........................... 61 
Especificações e limites de pasta de trabalho compartilhada .................................. 61 
 
 4
BANCO (LISTA DE DADOS) 
 
Uma lista de dados pode ser uma planilha criada diretamente no Excel ou 
dados que foram obtidos a partir de uma fonte externa, tal como uma tabela de um 
banco de dados do Microsoft Access ou dados de um arquivo do tipo texto. 
Uma lista contém dados relacionados a um determinado assunto. Por 
exemplo, poderíamos ter uma lista com dados sobre os funcionários de uma empresa 
ou uma lista de pedidos efetuados em um determinado período. Uma lista é como se 
fosse uma tabela de um banco de dados onde cada coluna representa um campo da 
tabela e cada linha é um registro. 
 
Considere os seguintes detalhes sobre listas: 
• Cada coluna representa um campo; 
• Os rótulos de cada coluna representam os nomesdos campos; 
• Cada linha representa um registro; 
• A lista não deve conter linhas ou colunas em branco; 
 
Use somente uma lista em cada planilha. O ideal é que não exista nenhuma 
outra informação na planilha, a não ser os dados da lista.Algumas funções de planilha, 
tais como filtragem, podem não funcionar corretamente se houver outros dados além 
dos dados da própria lista; 
Cada coluna deve conter dados do mesmo tipo: texto, número, data, etc. Não 
insira dados de diferentes tipos, na mesma coluna; 
Caso tenha outros dados, além da lista, mantenha a lista separada dos 
demais dados. Para isso insira, pelo menos, uma linha e uma coluna em branco, entre 
a lista e os demais dados. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 5
CLASSIFICAÇÃO DE REGISTROS 
 
Quando se utiliza uma base de dados, geralmente há necessidades de que 
ela seja classificada em uma determinada ordem. Para que isso seja possível, o Excel 
possui a ferramenta classificar. 
Vamos a um exemplo prático: 
 
1. Abra o Microsoft Excel. 
2. Selecione o comando Arquivo -> Abrir. 
3. Navegue até a pasta C:\Excel Avançado. 
4. Selecione o arquivo Exemplo1. xls. 
5. Clique em Abrir. 
6. Será aberta uma planilha com dados de pedidos de diversas filiais de 
uma empresa. 
 
 
 
 6
7. Para classificar os dados da lista, não é necessário selecionar todos os 
registros da base de dados, apenas clicar em uma das células que faz 
parte da lista. 
8. Em seguida selecione o comando Dados -> Classificar. 
9. Observe que ao selecionar este comando, todas as linhas da lista são 
automaticamente marcadas com exceção da primeira linha que contém 
os rótulos das colunas. 
10. Será apresentada a janela de classificar 
 
Para classificar os elementos selecionados o Excel usa a seguinte ordem: 
 
• Células vazias 
• Números 
• Textos 
• Valores de erro 
• Valores lógicos 
 
11. Selecione em Classificar por a opção FILIAL e depois por PRODUTO. 
12. Nas duas opções deixe selecionada a ordem De A a Z. 
13. Clique em OK. 
 7
 
14. Observe que na primeira figura o primeira registro era da Filial SC, na 
segunda figura o primeiro registro é a Filial PR. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 8
LISTAS DE CLASSIFICAÇÃO PERSONALIZADAS 
 
Além de classificações padrões, tais como alfanumérica e número, é possível 
criar listas de classificação personalizadas. Por exemplo, se a classificação dos dados 
de vendas, tivesse que ser de acordo com a seguinte ordem de código da Filial: 
 
Vamos tomar como exemplo a planilha Exemplo1.xls para criar uma lista 
personalizada de classificação. 
 
15. Selecione o comando Arquivo -> Opções. 
16. Na esquerda selecione Avançado. Desça na rolagem e procure o botão 
Editar Listas Personalizadas 
17. Serão exibidas as listas já existentes, criadas automaticamente pelo 
Excel, conforme citado anteriormente. 
18. Para criar uma nova lista, digite os valores na ordem em que se deseja 
classificar, no campo “Entradas da lista”; uma entrada em cada linha, 
conforme indicado na figura: 
 
 
 
 
19. Após ter digitado os valores da lista, clique no botão Adicionar. A nova 
lista será criada e exibida no campo Listas Personalizadas, abaixo das 
listas já existentes. 
 9
20. Clique em OK. Clique em OK também na janela de Opções da 
Planilha. Você estará de volta á planilha. 
21. Agora vamos classificar a planilha com base nos valores da lista 
recém-criada. 
22. Para classificar a lista não é necessário selecionar todas as linhas da 
lista. Basta clicar em uma das células que fazem parte da lista. 
23. Em seguida selecione o comando Dados -> Classificar. Observe que 
ao selecionar este comando, todas as linhas da lista serão 
automaticamente marcadas, com exceção da primeira linha que 
contém os rótulos das colunas. 
24. Será exibida a janela Classificar. 
25. Na lista Classificar Por, selecione o campo Filial. Vamos classificar 
pelo campo Filial, pois os valores da lista personalizada são valores 
deste campo. 
26. Para informar ao Excel que, ao invés da ordem alfabética padrão, deve 
ser usado os valores da lista, selecione Lista Personalizada no campo 
Ordem. Selecione então a lista personalizada criada anteriormente. 
 
 
OPERADORES LÓGICOS: “E” E “OU” 
 
São operadores utilizados em critérios de pesquisa em uma lista de dados do 
Excel. 
Utilizamos o operador E sempre que precisamos definir dois ou mais critérios 
de pesquisa. 
Quando os critérios são unidos por um operador E, todos os critérios devem 
ser verdadeiros para que um registro seja relacionado. Por exemplo, se forem definidos 
 10
três critérios unidos por operadores E, os três critérios devem ser atendidos para que 
um registro seja selecionado. Mas, se dois dos critérios forem verdadeiros, e um for 
falso, o referido não será selecionado. 
Utilizamos o operador OU sempre que precisamos definir dois ou mais 
critérios de pesquisa. Quando os critérios são unidos por um operador OU, basta que 
um dos critérios seja verdadeiro para que um registro seja selecionado. Por exemplo, 
se forem definidos três critérios unidos por operadores OU e um dos critérios for 
verdadeiro, ou dois dos critérios forem verdadeiros, ou até mesmo se os três forem 
verdadeiros, o registro seria selecionado. Um registro somente não será selecionado 
se todos os critérios unidos pelo operador OU forem falsos para o referido registro. 
 
FILTRANDO DADOS 
 
Quando trabalhamos com listas muito extensas, com milhares de linhas, pode 
haver a necessidade de “filtrar” a lista, para que sejam exibidas somente as linhas 
onde um ou mais campos atendam determinados critérios. 
Filtrar é uma maneira rápida e prática para trabalhar apenas com um conjunto 
reduzido de linhas. Quando aplicarmos um filtro a uma lista, somente serão exibidas as 
linhas que atenderem aos critérios especificados. Uma vez removidos os filtros, todas 
as linhas voltarão a ser exibidas. 
 
AUTOFILTRO 
 
A opção AutoFiltro é uma maneira rápida e prática para aplicar critérios de 
filtragem á lista de dados. 
Quando usamos a função AutoFiltro, o título de cada coluna é transformado 
em uma lista com todos os valores possíveis para esta coluna. 
Vamos observar um exemplo prático: 
 
1. Abra o Excel. 
2. Selecione o comando Arquivo -> Abrir. 
3. Navegue até a pasta C:\Excel Avançado. 
4. Selecione o arquivo Exemplo1.xls. 
5. Clique em Abrir. 
 11
6. Será aberta uma planilha com uma lista com dados de pedidos 
referentes a diversas filiais de uma empresa. 
7. Para habilitar o recurso de AutoFiltro selecione o seguinte comando: 
Dados -> Filtro 
8. Observe que o cabeçalho de cada coluna é transformado em uma lista 
de valores. Nesta lista são exibidos, em ordem crescente, todos os 
valores possíveis para a referida coluna. 
9. Vamos iniciar filtrando apenas os pedidos para a filial de SC. Para fazer 
isso abra a lista de valores da coluna FILIAL e selecione SC, conforme 
indicado na Figura: 
 
10. Após selecionar SC na lista de FILIAL, os registros serão filtrados e 
exibidos apenas os pedidos onde o campo FILIAL é igual a SC. 
 
PERSONALIZAR AUTOF ILTRO 
 
É possível ainda, no recurso de Filtro, fazer a filtragem em campos numéricos 
especificando uma faixa de valores. 
Por exemplo, podemos filtrar os pedidos que possuem quantidades de peças 
vendidas maiores que 200, ou quantidades maiores que 100 e menores que 200, 
combinando com critérios para FILIAL. 
1. Selecione Dados -> Filtro. 
2. O cabeçalho de cada coluna é transformado em uma lista de valores. 
3. Vamos iniciar filtrando apenas os pedidos para a filial PR e comquantidades maiores do que 100. 
 12
4. Para isso, abra a lista de valores da coluna Filial e selecione PR. Feito 
isso será aplicado um filtro onde serão exibidos apenas os pedidos para 
o PR. 
5. Abra a lista de valores da coluna Qtde e selecione Filtros de Número. 
 
6. Será exibida a janela Personalizar AutoFiltro. 
7. Na janela Personalizar AutoFiltro pode-se definir condições 
personalizadas para faixas de valores como por exemplo, valores 
numéricos e faixas de datas. 
8. Existe uma lista de operadores pré-definidos, conforme indicado a 
seguir: 
 
9. Clique em OK. Observe que serão exibidas apenas as linhas para PR e 
com valores de quantidades maiores de 100. 
10. Agora vamos definir os pedidos da Filial de SC que tenham quantidades 
maiores que 100 e menores que 200, inclusive. 
11. Limpe os filtros escolhendo a opção Limpar Filtro... nos campos de 
coluna Qtde e Filial. 
 13
12. Agora defina o campo Filial para SC. Apresentará então todos os 
pedidos para esta filial. 
13. Abra a lista de valores da coluna Qtde e selecione Filtros de Número. 
 
 
 
F ILTRO AVANÇADO 
É utilizado para definição de critérios mais complexos.Com o comando Filtro 
Avançado é possível definir faixas de critérios, com as quase fica muito mais fácil 
alterar os valores dos critérios e, com isso, rapidamente obterem um novo conjunto de 
linhas, conjunto este que reflete as alterações feitas na faixa de critérios. 
Nesta opção também é possível extrair dados de uma lista de dados para 
outra área da planilha. Para isso é preciso criar uma área de critérios e uma área de 
extração, que devem possuir os mesmo campos da lista. 
Exemplo: 
 
1. Abra o Excel. 
2. Selecione o comando Arquivo ->Abrir 
3. Navegue até a pasta C:\Excel Avançado. 
4. Selecione o arquivo Exemplo2.xls. 
5. Clique em Abrir. 
6. Será aberta uma planilha com uma lista de produtos vendidos por filial. 
7. Selecione a faixa de células A1:C1 e execute o comando Copiar (Ctrl + C). 
8. Posicione o cursor na célula E2 e na célula E10, pressionando a tecla CTRL, 
para poder selecioná-las simultaneamente. 
9. Execute o comando Colar (Ctrl + V). 
10. Posicione o cursor na célula E1 e digite: Área de Critérios. Em seguida 
posicione o cursor na célula E9 e digite Área de Extração. 
 14
11. Na definição de critérios, será extraído apenas os produtos vendidos na Filial 
SP. 
12. Na célula E3, que se refere á coluna Filial, digite SP. 
13. Selecione qualquer célula da lista de dados original da planilha. 
14. Execute o comando Dados -> Avançado. 
 
15. Será apresentada a janela Filtro Avançado. 
 
 
 
16. Selecione a opção Copiar para outro local. 
17. No campo Intervalo da lista, o Excel por padrão descreve a lista de dados 
(desde que o cursor esteja posicionado em qualquer parte dela). 
18. No campo Intervalo de critérios digite o endereço da faixa de área de critério, 
neste exemplo, $E$2:$G$3 ou selecione a área com o mouse. 
19. No campo Copiar para digite o endereço na área de extração que será as 
células $E$10:$G$10 ou selecione a área com o mouse. 
 
 15
20. Clique no botão OK. 
21. Serão apresentados na área de extração os dados conforme os critérios, que 
são todos os produtos vendidos na filial SP. 
 
 
 
 
FAIXAS DE CRITÉRIOS 
 
Várias condições em uma só coluna 
 
 
 
Uma condição em duas ou mais colunas 
 
 
 
 
Uma condição em uma coluna ou outra 
 16
 
 
 
CRITÉRIOS CALCULADOS 
 
1. Abra o Excel. 
2. Selecione o comando Arquivo -> Abrir. 
3. Navegue até a pasta C:\Excel Avançado. 
4. Selecione o arquivo Exemplo3.xls. 
5. Clique em Abrir. 
6. Será aberta uma planilha com uma lista de vendas de produtos por 
Filial de uma empresa 
7. O primeiro passo é calcular o valor médio dos pedidos em uma célula 
fora da lista de pedidos. 
8. Na célula I2 digite a seguinte fórmula: =MÉDIA(E2:E145). 
9. Tecle Enter. 
10. Deverá obter um valor de R$3.443,29 que será a média de vendas. 
11. O valor da média calculada será usado como critério (na verdade uma 
referência absoluta a célula onde está o valor da média) para filtrar 
apenas os pedidos com valores acima da média. 
12. Na célula G1 digite o rótulo: Pedidos Acima da Média. Lembre que 
este rótulo não pode ser igual ao nome de uma das colunas da lista. 
13. Na célula G2 digite a seguinte fórmula: =E2>$I$2. A célula E2 é onde 
está o valor para o primeiro registro. Quando o Excel executar o filtro, 
ele iniciará pela célula E2 e irá comparar o valor desta célula com o 
valor da média calculado na célula I2. Se o valor for maior do que a 
média, o registro será selecionado, caso contrário o Excel irá para o 
próximo registro. 
 
 17
 
14. Selecione o comando Dados -> Filtrar -> Filtro Avançado e defina as 
faixas conforme indicado na figura. 
 
15. Clique em OK. 
16. Observe que serão exibidos apenas com valores acima de R$3.443,29, 
que é a média de vendas. 
17. Salve as alterações feitas na planilha Exemplo3.xls e feche o arquivo. 
IMPORTAÇÃO DE DADOS 
Existem situações nas quais os dados que precisamos analisar não estão no 
formato de uma planilha do Excel. Por exemplo, estes dados podem estar em um 
arquivo do tipo texto, ou podem estar em uma tabela do Microsoft Access. Nestas 
situações podemos importar estes dados para dentro de uma planilha do Excel, para 
então analisar as funcionalidades de análise de dados do Excel. 
 
IMPORTAÇÃO DE ARQUIVOS DO TIPO TEXTO: 
Existem dois formatos padrão quando se trata de arquivos do tipo texto: 
delimitado e tamanho fixo. 
Arquivos de texto no formato Tamanho Fixo 
 
Nos arquivos de texto em formato Tamanho Fixo cada campo começa e 
termina em uma determinada coluna e termina em uma posição fixa. 
 18
 
 
 
 
 
 
 
 
 
 
 
 
 
Arquivos de texto no formato Delimitado ou com Delimitadores 
Nos arquivos de texto em Formato Delimitado cada linha contém um registro 
completo, porém os campos são separados por um determinado “separador”, que no 
exemplo da Figura é o ponto-e-vírgula (;). Podemos ter diferentes separadores, sendo 
que um dos mais comuns é a vírgula. 
 
 
Exemplo de importação de um arquivo do tipo texto 
 
1. Abra o Excel. 
2. Selecione o comando Arquivo -> Abrir. 
3. Navegue até a pasta C:\Excel Avançado. 
4. Na linha Arquivos do tipo, selecione a opção Todos os Arquivos. 
5. Selecione o arquivo Exemplo03.txt e em seguida no botão Abrir. 
 19
6. Será aberto o assistente de importação. 
7. Na primeira etapa deve-se informar o tipo de dado. Neste exemplo 
usaremos a opção Delimitado. Por padrão a importação inicia na 
primeira linha. 
 
8. Clique em Avançar. 
9. Na segunda etapa do assistente deve-se informar qual o caracter 
utilizado com delimitador de campo. No exemplo, está sendo utilizado 
o ponto-e-vírgula. Marque a opção ponto e vírgula. 
 
 20
10. Clique em Avançar. 
11. Será então exibida a última etapa do assistente, onde irá ser definido o 
tipo de cada campo (Geral, Texto ou Data) ou escolher se deseja ou 
não importar algum campo. 
12. Clique em Concluir. Os dados serão importados para a planilha do 
Excel. 
 
13. Agora você deverá salvar este arquivo como uma planilha Excel. 
IMPORTAÇÃO DE DADOS DA INTERNET 
È possível criar uma consulta baseada nos dados de um site que possui uma 
série de indicadores como Índice de Poupança, Dólar, IPC, etc. Importá-los para o 
Excel e configurar o intervalo de atualização destes dados. 
1. Abra uma planilha nova. 
2. Selecione Dados -> Da Web 
3. Será aberta a janela Nova consulta à Web. 
4. No campo endereço, digite o seguinte endereço 
http://www.terra.com.br/invertia 
 21
 
5.Selecione o quadro amarelo referente aos índices econômicos que 
deseja importar. Clique no botão Importar na parte inferior da janela. 
6. Indique o local onde serão inseridos os dados importados clicando na 
célula A1. 
7. Veja como ficou a importação dos dados na planilha. 
 
 
 22
TRABALHANDO COM DADOS EXTERNOS ATRAVÉS DO MS-QUERY 
O Microsoft Query é uma ferramenta que facilita a conexão do Excel com 
fontes externas de dados. Por exemplo, pode-se utilizar o Microsoft Query para fazer 
uma pesquisa em tabelas de um banco de dados do Microsoft Access, especificar 
critérios de filtragem e ordenação e retornar o resultado da consulta para uma planilha 
do Excel. É possível ainda fazer cálculos, análises e formatações com os dados 
retornados pelo Microsoft Query. 
O MS-Query usa a fonte de dados para conectar-se ao Banco de Dados 
externo e mostra os dados disponíveis. Após criar a consulta e retornar dados ao 
Excel, o MS-Query recupera os dados e fornece á pasta de trabalho do Excel 
informações sobre a consulta e a fonte de dados para que você se conecte ao banco 
quando desejar atualizar os dados. 
Criando uma consulta de dados através do assistente de consulta 
 
1. Para definir a fonte de 
dados selecione o comando 
Dados -> De Outras 
Fontes -> Do Microsoft 
Query 
2. Será exibido a janela 
escolher fonte de dados 
3. O primeiro passo é informar ao Microsoft Query o formato em que se 
encontram os dados que serão acessados. No exemplo os dados 
estão no formato do Microsoft Access. Clique em Banco de Dados do 
MS Access 
4. Clique em Ok. 
 
 
 
 
 
 
 
 
 
 23
5. Será aberta a janela selecionar fonte de dados. Escolha o arquivo 
Vendas.mdb que está na pasta de arquivos do curso. 
6. Clique em Ok. 
7. O MS-Query exibe a lista de tabelas disponíveis no banco de dados 
Vendas.mdb. Com a fonte de dados já definida o Excel já sabe de 
onde buscar os dados que servirão como base para as consultas que 
depois serão exibidas na planilha. 
8. Esta é a janela assistente de consulta. No painel á esquerda selecione 
a tabela Dados_vendas e clique no sinal de + ao lado para expandir 
todos os campos que pertencem á ela. 
9. Escolha os campos FILIAL, PRODUTO e QUANTIDADE, 
selecionando-os e clicando na seta para a direita que existe no meio 
da janela. 
 
 
 
 
 
 
 
 
 
 
 
10. Clique no botão Avançar para seguir para a próxima etapa do 
assistente. 
11. Nesta etapa podem-se definir os filtros para a consulta. Escolheremos 
apenas os pedidos da filial SC. 
12. Clique no campo FILIAL. Note que ao lado irão aparecer as opções de 
filtro, então escolha o campo igual e SC. 
13. Clique em Avançar. 
14. Na próxima etapa pode-se selecionar até três campos para 
classificação. Escolha o campo Produto em ordem Crescente. 
15. Clique em Avançar para seguir para a etapa final do assistente. 
 24
16. Na janela seguinte, escolha Retornar dados ao Microsoft Excel. 
17. Para finalizar, na janela importar Dados selecione o modo Tabela e 
indique o local onde os dados serão colocados. 
18. Salve a planilha em C:\Excel Avançado com o nome Consulta Query. 
 
Interface do Microsoft Query 
 
A partir da consulta salva, iremos editar a mesma através do Microsoft Query, 
usando sua interface. Na interface iremos definir outros critérios de consulta, além dos 
já estabelecidos anteriormente através do assistente de consulta. 
 
1. Selecione Dados -> Conexões. 
2. Selecione Consulta de Banco de Dados do MS Access e depois 
Propriedades... 
3. Alterne para a aba Definição 
4. Clique no botão Editar Consulta... 
5. Na janela Assistente de Consulta – Escolher Colunas clique em 
Cancelar e dê um Sim na mensagem a seguir. 
6. Será aberta uma janela do editor do Microsoft Query. 
7. Na parte superior da janela se encontram as barras de Menu de 
comandos e a de Ferramentas. Logo após, as tabelas com a lista de 
campos de cada tabela. Para adicionar um campo à consulta basta dar 
um dique duplo no campo ou amastá-lo para o painel na parte de baixo 
da janela. Ao arrastar um campo, os cados do campo já serão 
exibidos. Cada campo é exibido em uma coluna separada no painel de 
baixo. 
8. Adicione O campo Valor Unitário. 
9. Defina os seguintes critérios: 
* Filial = ‘PR’ 
 
* Produto = ‘edredon solteiro’ 
 
É importante observar os seguintes detalhes: 
 
- Quando o critério for em um campo do tipo Texto, como no 
caso do Campo valor do critério (‘PR’) deverá vir entre 
apóstrofes. 
 25
- Quando o critério for em um campo do tipo Data, deverá vir 
dentro do sinal # 
 
10. O próximo passo é fechar o Microsoft Query, retornando estes dados 
para a planilha do Excel. Para isso utilize o comando: Arquivo -> 
Retornar dados ao Microsoft Excel. 
11. A consulta será exibida já com as alterações executadas. 
 
 26
OPERAÇÕES DE BUSCA E TROCA 
 
É possível utilizar operações de busca e troca de informações de dados. 
Seguem algumas opções: 
 
Localizar: Permite determinar a direção da pesquisa. Se ele vai ocorrer por 
linhas ou por colunas. Para inverter a direção da pesquisa, mantenha a tecla SHIFT 
pressionada ao clicar no botão OK. 
Coincidir maiúscula e minúscula: Permite efetuar a distinção entre letras 
maiúsculas e minúsculas ao pesquisar o texto informado no campo Localizar. 
Somente Células Inteiras: Permite efetuar a busca dos caracteres que 
coincidam exatamente e completamente com os caracteres informados no campo 
localizar. 
Localizar Próximo: Permite localizar a próxima ocorrência dos caracteres 
informados no campo Localizar. Se a tecla SHIFT estiver pressionada quando o clique 
for dado no botão Localizar Próximo, será possível localizar a ocorrência anterior. 
Substituir: Permite efetuar a substituição dos caracteres pesquisados na 
célula ativa pelos novos caracteres, e então localiza a próxima ocorrência dos 
caracteres pesquisados. A tecla SHIFT pode ser usada para inverter a direção da 
pesquisa. Para efetuar a substituição dos caracteres sem localizar a próxima célula, 
mantenha a tecla CTRL pressionada enquanto é dado um clique no botão Substituir. 
 
 27
SUBTOTAIS 
 
Uma funcionalidade bastante útil do Excel é a possibilidade de, rapidamente, 
criar subtotais para grupos de dados em uma planilha. Por exemplo, pode-se criar 
subtotais de vendas por vendedor, dentro de cada vendedor pode-se criar subtotais por 
departamento. 
A criação de subtotais é uma maneira rápida de comparar o desempenho de 
diferentes grupos. No exemplo, com o cálculo de subtotais por vendedor, poderá se 
comparar, facilmente o desempenho de cada vendedor. 
É muito importante observar que é imprescindível manter a base de dados 
classificada segundo a ordem em que se deseja obter um subtotal. 
 
1. Abra o arquivo Exemplo4.xls, iremos criar um subtotal por vendedores. 
2. Em primeiro lugar é preciso classificar a planilha em ordem crescente 
no campo Vendedor. 
3. Escolha o menu Dados -> Subtotal 
4. Será exibida a janela Subtotais. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
5. Esta caixa apresenta as seguintes opções de trabalho: 
• A cada alteração em: É o local em que se determina por qual campo 
será feito o subtotal. No nosso exemplo selecione o campo Vendedor. 
• Usar função: é a utilização da função estatística ou cálculo de subtotal 
que será usado em cada intervalo de subtotal. Por padrão é a Soma 
 28
mas, também pode ser contar o número de linhas, Máximo, Mínimo, 
Média, etc. Selecione a opção Soma. 
• Adicionar subtotais a : nesse local é determinado o campo no qual 
estão os valores onde serão efetuados os cálculos. No exemplo, 
queremos somar o total de vendas para cada funcionário. Selecione a 
colunaValor. 
• Substituir subtotais atuais: quando selecionado, serve para a 
substituir subtotais que já tenham sido feitos anteriormente. 
• Quebra de página entre grupos: é muito interessante esse recurso, 
pois a cada mudança de dados de um mesmo campo, ele 
automaticamente quebra a página, facilitando a impressão de um 
relatório por grupo de dados. Deixar desmarcada. 
• Resumir abaixo dos dados: é a colocação do total geral de todos os 
grupos abaixo deste; caso contrário, o total geral será colocado como a 
primeira informação da base de dados, abaixo somente dos nomes dos 
campos. 
• Remover todos: quando deseja remover os subtotais aplicados na 
base de dados. 
6. Clique em OK. 
7. O Excel insere uma estrutura de tópicos ao lado esquerdo da planilha. 
8. Observe que a estrutura de tópicos apresenta diferentes níveis: 1, 2 e 
3 . Por padrão são exibidos todos os registros e também os subtotais. 
Para visualizar apenas o subtotal por vendedor, dique no botão com o 
número 2. 
9. Para remover os subtotais obtidos, selecione o comando Dados -> -> 
Subtotais e clique na opção Remover Todos. 
É possível ainda através desse recurso, criar subtotais em dois ou mais 
campos, além de usar outros tipos de cálculos, sem ser a soma. 
Por exemplo, pode-se calcular o subtotal por departamento e em seguida por 
vendedor e em seguida calcular uma média de pedidos por vendedor. 
 
Para isso: 
 
1. Inicialmente classifique a lista de dados referentes aos campos 
Departamento e Vendedor, consecutivamente. 
2. Crie um subtotal de Soma por Departamento. 
3. Selecione o comando Dados -> Subtotal. 
 29
4. Defina as configurações indicadas na Figura: 
 
 
5. Clique em OK. 
6. Agora calcule os subtotais por Vendedor, mantendo os subtotais gerais 
por Departamento. 
7. Selecione o comando Dados -> Subtotal. 
8. Para que os subtotais por Departamento sejam mantidos é preciso 
desmarcar a Substituir subtotais atuais. Se esta opção estiver 
marcada, os subtotais por Departamento serão excluídos e serão 
mantidos apenas os subtotais por Vendedor. 
 
 30
10. Clique em OK. 
 
11. Agora iremos calcular a média de pedidos por vendedor. 
12. Selecione o comando Dados -> Subtotais e marque as opções 
indicadas na figura. Com esta configuração, teremos três subtotais: 
Totais por departamento, Total por vendedor e Média por Vendedor. 
 
13. Clique em OK. 
14. Será exibida a planilha conforme a figura abaixo: 
 31
 
15. Salve as alterações feitas na planilha Exemplo4.xls e feche o arquivo. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 32
FUNÇÕES DE BANCO DE DADOS 
 
O Microsoft Excel inclui 12 funções de planilha que analisam os dados 
armazenados em listas ou bancos de dados. Cada uma dessas funções, citadas 
genericamente como BDFunções, usa três argumentos: banco de dados, campo e 
critérios. Esses argumentos se referem aos intervalos de planilhas usados pela função. 
 
FUNÇÃO BDMÉDIA 
 
A função BDMÉDIA é utilizada para calcular a média dos valores em uma 
coluna de uma lista ou banco de dados que coincidirem com as condições 
especificadas. Ou seja, usa-se a função BDMÉDIA para calcular a média dos valores 
estes que coincidirem com um critério especificado. 
Sintaxe: 
BDMÉDIA(banco_dados;campo;critérios) 
 
Exemplo: 
 
1. Abra o Excel. 
2. Selecione o comando Arquivo -> Abrir 
3. Navegue até a pasta C:\Excel Avançado. 
4. Clique no arquivo Exemplo5.xls. 
5. Cilque em Abrir. 
6. Será aberta uma planilha com dados sobre quantidade de produtos 
vendidos por Filial em determinados meses. 
7. No exemplo iremos calcular a média de produtos vendidos, podendo 
ser através de um ou mais critérios. 
8. Inicialmente é preciso criar a área de critérios. Para isso selecione as 
células de A1:D1 que é a área de rótulos das colunas do banco. 
9. Copie esta área para as células G1:J1. 
10. Na célula F7 digite “Média de Produto Vendido”. 
11. Na celula G7 vamos digitar a função =BDMEDIA(A:D;3;G1:J2), onde 
A:D e o banco de o 3 é o campo: número da coluna na qual será 
 33
calculado, no exernplo é a quantidade de peças vendidas e G1:J2 é a 
área de critérios que será utilizada para determinar informações 
específicas. 
12. Após digitar a função tecie enter. 
13. Será calculada a média de produtos vendidos. 
14. Agora para analisar a média de um produto específico basta digitar o 
nome do produto na área de critérios abaixo do rótulo PRODUTO. 
 
FUNÇÃO BDMÁX 
 
Esta função é utilizada para retorna o maior número em uma coluna de uma 
lista ou banco de dados que coincidir com as condições especificadas por você. 
Sintaxe: 
BDMÁX(banco_dados;campo;critérios) 
 
1. Utilizando o arquivo anterior Exemplo5.xls 
2. Exclua os critérios utilizados anteriormente. 
3. Clique na célula F8. 
4. Digite “Maior Quantidade de Produto Vendido”. 
5. Na célula G8 digite a função =BDMÁX(A:D;3;G1:J2), onde A:D é o 
banco ou lista de dados, 3 é o campo, número de coluna na que será 
calculado e G1:J2 a área de critérios que será utilizada para determinar 
informações específicas. 
6. Tecle Enter. 
7. O resultado obtido será o registro que tem a maior quantidade de 
peças vendidas, independente de Filial, Produto ou Mês 
FUNÇÃO BDMÍN 
 
Esta função é utilizada para retornar o menor número em uma coluna de uma 
lista ou banco de dados que coincidir com as condições especificadas. 
Sintaxe: 
BDMÍN(banco_dados;campo;critérios) 
 
 34
FUNÇÃO BDSOMA 
 
Esta função é utilizada para somar os números em uma coluna de uma lista 
ou banco de dados que coincidirem com as condições especificadas. 
Sintaxe: 
BDSOMA(banco_dados;campo;critérios) 
 
FUNÇÃO CONTAR 
A função BDCONTAR é utilizada para contar as células contendo números em 
uma coluna de uma lista ou banco de dados que coincidirem com as condições 
especificadas por você. Ou seja, permite uma contagem condicional, contando 
condicional, contando apenas as células que atendem ao critério especificado. 
Sintaxe: 
BDCONTAR(banco_dados;campos;critérios) 
 
FUNÇÃO BDCONTARA 
Esta função é utilizada para contar todas as células não vazias em uma coluna 
de uma lista ou banco de dados que coincidirem com as condições especificadas por 
você. 
Sintaxe: 
BDCONTARA(banco_dados;campo;critérios) 
 
FUNÇÃO BDEXTRAIR 
 
Esta função é utilizada para extrair um único valor em uma coluna de uma lista 
ou banco de dados que coincidir com as condições especificadas por você. 
Sintaxe: 
BDEXTRAIR(banco_dados;campo;critérios) 
 
 
 
 
 
 35
FUNÇÃO CONT.SE 
 
Esta função calcula o número de células não vazias em um intervalo que 
corresponde a determinados critérios. 
Sintaxe: 
CONT.SE(intervalo;critérios) 
 
Intervalo: é p intervalo de células no qual se deseja contar células não vazias. 
Critérios: é o critério na forma de um número, expressão ou texto que define 
quais células serão contadas. 
 
Exemplo: 
 
1. Utilizando o arquivo Exemplo5.xls, clique na guia da plan2, que se 
encontra na parte inferior da planilha. 
2. Nesta planilha existe um banco ou lista de dados e uma área de 
pesquisa. 
3. Na primeira área de Pesquisa/Produto, ao especificar um determinado 
produto a quantidade de pedidos vendidos para este produto irá ser 
obtida. 
4. Para isso, a função CONT.SE será inserida na célula G4, que tem 
como rótulo N° de pedidos. 
5. Clique na célula G4 e digite a função =CONT.SE(A:D;G3), onde A:D é 
o intervalo do banco ou lista de dados na qual serão pesquisadas as 
informações e G3 é o critério, sendo o local(célula) aonde será 
inserido, no exemplo, o nome do produto. 
6. Digite na célula G3 o produto fronha. 
 
 
7. N
a
 
s
e
g36
unda área de pesquisa, Pesquisa/Mês, pode-se obter a quantidade de 
pedidos realizados em um determinado mês. 
8. Clique na célula G11 e digite a função =CONT.SE(A:D;G10), aonde 
A:D será o banco ou lista de dados e G10 o critério, ou seja, a célula 
aonde será inserido o mês que se deseja saber a informação. 
9. Digite na célula G10 o mês de novembro. 
FUNÇÕES SOMASE 
 
A função SOMASE faz uma soma condicional. È preciso fornecer três 
parâmetros para esta função, conforme descrito a seguir: 
Sintaxe: 
SOMASE(intervalo de pesquisa;critérios;intervalo de soma) 
 
Intervalo de critério: é o critério de células aonde será feita a pesquisa de 
acordo como critério especificado 
Critérios: são os critérios na forma de um número, expressão ou texto, que 
define quais células serão adicionadas. É importante salientar que, nos critérios da 
função SOMASE, não é possível usar a função E ou a função OU. 
Intervalo de soma: são as células que serão realmente somadas. As células 
em intervalo de soma são somadas somente se suas células correspondentes em 
intervalo de pesquisa coincidirem com os critérios estipulados. Se o intervalo de soma 
for omitido, as células em intervalo de pesquisa serão somadas, ou seja, a coluna de 
pesquisa é a mesma aonde os valores serão somados. 
 
Exemplo: 
 
1. Utilizando o arquivo Exemplo5.xls, na plan2, pode-se observar que 
existe na primeira área de pesquisa Pesquisa/Produto um campo 
aonde é possível obter o total de peças vendidas de acordo com o 
produto desejado. 
2. Clique na célula G5, que tem como rótulo Total de Peças. 
3. Digite a função =SOMASE(B:B;G3;C:C), onde B:B é o intervalo aonde 
será pesquisado o critério determinado, o G3 é a celula aonde será 
digitado o critério, no exemplo está fronha, e o C:C é o intervalo que 
será somado o critério. 
 37
4. Tecle Enter. 
 
5. Na segunda área de pesquisa, Pesquisa/Mês, pode-se obter o total de 
peças vendidas em um determinado mês. 
6. Clique na célula G12 e digite a função =SOMASE(D:D;G10;C:C), 
aonde D:D é o intervalo que será pesquisado o critério, o G10 o 
critério, neste caso o mês e o C:C é o intervalo que será somado 
conforme o critério. 
7. Tecle Enter. 
8. Salve as alterações feitas na planilha5.xls e feche o arquivo. 
 
Funções Encadeadas 
 
O Excel tem a possibilidade de trabalhar diversas funções dentro de uma 
mesma fórmula, dependendo da resposta que se deseja. 
Inicialmente veremos duas funções e em seguida trabalhar com as duas 
dentro de uma mesma fórmula. 
 
FUNÇÃO PROCV 
 
Esta função tem o objetivo de procurar um registro dentro de um banco ou 
lista de dados través de um critério. 
Sintaxe 
PROCV(valor_procurado;matriz_tabela;num_indice_coluna;procurar_intervalo) 
 
Valor_procurado: é a célula de referência para a busca no banco ou lista de 
dados 
 38
Matriz_tabela: é a intervalo do banco ou lista de dados aonde o critério será 
encontrado e a informação desejada buscada. 
Observação: é importante na hora de selecionar o intervalo, a coluna que 
possui rótulo seja selecionada por completo e não somente as linhas que contém 
registro. 
Num_índice_coluna: é o número da coluna que está a informação desejada. 
Sempre iniciará a contagem a partir da primeira coluna do banco. 
Procurar_intervalo: é um valor lógico aonde para encontrar a informação 
exata é preciso digitar FALSO, caso contrário ou seja, se digitar VERDADEIRO, a 
função quando não encontrar o registro igual irá trazer a informação mais próxima. 
Exemplo: 
 
1. Abra o Excel 
2. Selecione o comando Arquivo -> Abrir 
3. Navegue até a pasta C:\Excel Avançado. 
4. Selecione o arquivo Exemplo6.xls 
5. Clique em Abrir. 
6. Será aberto um banco ou lista de dados com informações de compra 
de clientes em uma loja e de sua situação de pagamento. 
7. Clique na célula A9 e digite um nome de cliente. 
8. Na coluna produto, insira nome do produto que o cliente comprou, 
neste caso Geladeira. 
9. Ao digitar geladeira no campo produto, seria possível que já fossse 
preenchido a informação do valor produto. 
10. Para que isso aconteça utilize a função PROCV, pois a mesma irá 
buscar em um banco ou lista de dados a informação que deseja 
através de um critério, que neste caso é geladeira. 
11. Clique na guia da planilha produto. 
12. Observe que nesta planilha existe um banco ou lista de com as 
informações de produto e seus respectivos preços. 
13. Volte para a guia Vendas e na célula C9 digite a função 
=PROCV(B9;Produtos!A:B;2;FALSO), aonde B9 é o valor procurado, 
ou seja, a célula aonde está sendo digitado o registro que servirá de 
referência para a busca da informação desejada. Produtos!A:B é a 
matriz tabela, ou seja, o intervalo do banco ou lista de dados. O 2 é o 
número do índice da coluna e o FALSO é para obter um dado exato sem 
aproximação. 
 39
14. Tecle Enter. 
 
 
 
FUNÇÃO SE 
 
Esta é uma função lógica que tem por finalidade analisar critérios que de 
acordo com a resposta, VERDADEIRO ou FALSO, aplica uma determinada ação. 
Sintaxe: 
SE(teste_lógico;valor_se_verdadeiro;valor_se_falso) 
Teste_lógico: é qualquer valor ou expressão que pode ser avaliada como 
verdadeiro ou falso. 
Valor_se_verdadeiro: é a ação aplicada caso verdadeiro ao teste lógico. 
Valor_se_falso: é a ação aplicada caso falso ao teste lógico. 
 
Exemplo: 
 
1. No arquivo Exemplo6.xls. 
2. Clique na célula D9. 
3. Digite uma data do vencimento. 
4. Clique em E9. 
5. Neste células terá que constar os dias em atraso do cliente caso, 
esteja em atraso. 
6. Para isso utilize a função SE aonde irá executa o seguinte teste lógico: 
se a data do vencimento for maior que a data atual (B3), então diminua 
a data atual di vencimento, caso contrário traga 0. 
7. Digite então a função = SE(B3>D9;B3-D9;0). 
8. Tecle Enter. 
 
 40
 
 
 
 
9. Observe que a data do vencimento é menor que a data atual, sendo 
então uma resposta verdadeira ao teste lógico e por isso a função 
aplicou a subtração da data atual do vencimento. 
10. Clique na célula F9. 
11. Nesta célula terá que constar a quantidade de dias antecipados a data 
do vencimento, caso houver. 
12. Neste caso a função SE executará o seguinte teste lógico: se a data do 
vencimento for menor do que a data atual então diminua a data atual 
do vencimento, caso contrário traqa 0. 
13. Digite a função =SE(B3<D9;D9-B3;0). 
 
14. Tecle Enter. 
 
Encadeando Funções 
 
Agora uniremos duas funções dentro de uma mesma fórmula: a função SE e a 
PROCV. Continuando em um exemplo prático, iremos utilizar o arquivo Exemplo6.xls. 
Nele será calculado o valor da multa ou valor de desconto para pagamentos feitos 
atrasados ou antecipados respectivamente. 
1. Clique na célula G9. 
2. Neta célula será calculado o valor da multa que o cliente irá pagar caso 
estiver em atraso. 
 41
3. Para isso a loja possui uma tabela de multa diária aplicada de acordo 
com a quantidade de dias em atraso do cliente, que se encontra na 
planilha multa_descontos: 
 
4. De acordo com a tabela a multa aplicado será: se o cliente estiver em 
atraso a mais de 15 dias terá 0,50% de multa ao dia e se estiver a 
menos de 16 dias será aplicada 0,30% diário. 
5. Digite então a fórmula 
=SE(E9>0;PROCV(E9;Multa_descontos!A4:B6;2);0)*C9*E9 
6. Tecle Enter. 
 
 
7. Clique na célula H9 
8. Nesta célula será calculado o valor de desconto recebido pelo cliente, 
caso pague adiantado. 
9. Para isso a loja possui uma janela de desconto diário aplicado de 
acordo com a quantidade de dias antecipados do cliente, que se 
encontra na planilha multa_descontos: 
10. De acordo com a tabela descontos aplicada será: se o cliente pagar 
adiantado mais de 15 dias terá 0,15% de desconto diárioe se estiver a 
menos de 16 dias será aplicado 0,10% diário. 
11. Digite então a fórmula 
=SE(F9>0;PROCV(F9;Multa_descontos!A9:B11;2);0)*C9*E9, que 
seguirá os mesmos moldes da fórmula anterior, apenas mudando os 
dias atrasados pelos dias antecipados. 
 42
 
12. Tecle Enter. 
 
Mais exemplos 
 
Utilizando a mesma planilha, será calculado agora, o total a pagar do cliente e 
sua situação. Para isso usaremos as operações de soma, subtração e a 
função SE. 
 
1. Clique na célula I9. 
2. Nesta célula será calculado qual o valor que o cliente irá pagar com a 
multa ou com o desconto. 
3. Digite a fórmula =C9+G9-H9, que consiste em o valor da compra (C9) 
mais o valor da multa (G9) menos o valor do desconto(H9). 
4. Tecle Enter. 
 
5. Clique na célula J9. Nesta célula será calculado a situação do cliente, 
se ele está em dia ou em atraso, dependendo da data de vencimento 
de sua dívida. 
6. Para isso utilize a função SE que executará o seguinte teste lógico: se 
a data atual for maior que a data do vencimento então traga a reposta 
“Em atraso”, caso contrário traga “Em dia”. 
7. Digite então a função =SE(B3>D9.”Em atraso”;”Em dia”). 
 43
8. Tecle Enter. 
 
9. Salve as alterações feitas na planilha Exemplo6.xls e feche o arquivo. 
 
Formatação Condicional 
 
A formatação condicional é um formato como, sombreamento de célula e cor 
de fonte, que o Microsoft Excel aplica automaticamente à células se uma condição 
especificada for verdadeira. 
Por exemplo, no arquivo Exemplo7.xls, existem células nas quais especifica a 
situação do cliente em relação ao pagamento. É possível, através da formatação 
condicional, que quando a célula estiver com o resultado “em atraso” a mesma será 
destacada por uma cor de fonte diferente das demais. 
 
1. Selecione as células nas quais será aplicada a formatação condicional, 
neste caso do J7 à J10. 
2. Selecione o comando Página Inicial -> Formatação Condicional -> 
Realçar Regras das Células 
3. Deve-se então selecionar a condição ou critério para que seja aplicada 
uma determinada formatação. 
 
 44
4. No exemplo utilize a opção É igual a... 
 
 
5. Na caixa aonde especifica a condição, insira as palavras Em atraso, 
que é a condição para uma formatação diferenciada das demais. 
6. Em seguida escolha o tipo de preenchimento desejado para as células 
do intervalo que atendam esta condição 
7. A condição total então ficará conforme a figura. 
8. Sendo assim, é possível também especificar uma formatação para as 
células que tiverem o valor “Em dia”. 
9. Repita os passos 2 até 6 e configure conforme a figura abaixo 
 
10. Clique em OK. 
11. Salve as alterações feitas na planilha Exemplo7.xls e feche o arquivo. 
 
 
 
 
 45
Formulários 
 
O Microsoft Excel trabalha com diversos tipos de formulários. É possível 
utilizar os formulários fornecidos como Excel para inserir dados em intervalos, listas ou 
em outros bancos de dados. Também pode-se criar formulários para imprimi-los ou 
usá-los on-line, incluindo formulários abertos no Excel e os formulários colocados em 
páginas da WEB. 
O que veremos são formulários internos para dados do Excel. Para obter 
intervalos ou listas em planilhas do Excel, pode-se exibir um formulário de dados que 
permite inserir novos dados, encontrar linhas com base no conteúdo das células, 
atualizar os dados e excluir linhas do intervalo ou da lista. 
Novo: inclui um novo registro dentro do banco ou lista de dados. 
Excluir: exclui o registro na qual está sendo exibido na janela. 
Restaurar: retorna uma alteração feita nos registros. 
Localizar anterior: posiciona no registro anterior ao qual se encontra no 
momento atual. 
Localizar próximo: posiciona no registro posterior ao qual se encontra no 
momento atual. 
Critério: visualiza na janela de formulário apenas os registros conforme o 
critério determinado. 
 
ADICIONAR O BOTÃO FORMULÁRIO À BARRA DE FERRAMENTAS DE 
ACESSO RÁPIDO 
1. Clique na seta ao lado da Barra de Ferramentas de Acesso Rápido e 
clique em Mais Comandos. 
 
2. Na caixa Escolher comandos em, clique em Todos os Comandos. 
3. Na caixa de listagem, selecione o botão Formulário e clique em 
Adicionar. 
4. O Botão Formulário será adicionado e estará disponível na Barra de 
Ferramentas de Acesso Rápido 
 
 46
INSERINDO NOVOS DADOS 
Exemplo: 
5. Abra o Excel. 
6. Selecione o comando Arquivo -> Abrir. 
7. Navegue até a pasta C:\Excel Avançado. 
8. Selecione o arquivo Exemplo7.xls 
9. Clique em Abrir. 
10. Será aberta uma planilha com dados de compras de clientes de uma 
loja que construímos no último exemplo, só que neste arquivo existem 
registros. 
11. Será inserido dados nesta lista ou banco de dados através da 
ferramenta formulário. 
12. Clique no botão Formulário adicionado anteriomente. 
13. A janela formulários terá o título da planilha que está em execução, 
que neste caso é Vendas. 
 
14. Ao lado esquerdo da tela se encontram os rótulos de cada coluna do 
banco ou lista de dados, com seus respectivos valores. Ao lado direito 
se encontram os botões de controle do formulário e o número do 
registro na qual está sendo exibido ao lado esquerdo. 
15. Clique no botão Novo. 
 
 47
16. Observe que apenas 3 campos estão disponíveis para preenchimento: 
cliente, produto e vencimento. Os outros campos, são ao que possuem 
fórmulas na qual são preenchidas automaticamente conforme os itens 
que forem inclusos nos campos disponíveis, pois uma depende da 
outra por estarem encadeadas. 
17. Insira respectivamente os seguintes dados nos campos cliente, produto 
e vencimento: Regina, DVD e 25/05/2005. 
18. Clique em Fechar. 
19. Confira que todos os campos foram preenchidos corretamente de 
acordo com o registro inserido através do formulário. 
 
ATUALIZANDO DADOS 
 
1. Clique no botão Formulário . 
2. Utilizando o botão Localizar próxima, posicione no terceiro registro. 
3. Troque o nome do cliente Renato pelo nome Marcio e clique em 
Fechar. 
 
EXCLUINDO DADOS 
 
1. Clique no botão Formulário . 
2. Utilizando o botão Localizar próxima, posicione-se no quinto registro. 
3. Clique no botão Excluir. 
4. O Excel solicitará uma confirmação de exclusão de registro, pois ao 
excluir através do formulário não há como desfazer a ação. 
5. Clique em fechar. 
 
LOCALIZANDO REGISTROS EM UM BANCO OU LISTA DE DADOS 
 
1. Clique no botão Formulário . 
2. Clique em Critérios. 
 48
3. Observe que nesta tela todos os rótulos estão disponíveis para que se 
possa através de qualquer campo, determinar critérios para busca de 
registros. 
4. Localize o registro do produto DVD. 
5. Selecione o campo que representa produto e digite DVD. 
6. Clique no botão Localizar próxima. 
7. O Excel irá trazer todos os registros que contém no campo produto o 
nome DVD, dentro da janela de formulário. 
8. Para navegar entre os registros encontrados utilizam-se os botões 
Localizar Próxima e Localizar Anterior. 
9. Para voltar a mostrar todos os registros do banco ou lista de dados, 
clique no botão Critérios e exclua o critério inserido. 
 
 
 
 
 
 
 
 
 
 
 49
TABELA DINÂMICA 
Um relatório de tabela dinâmica é uma tabela interativa que pode ser usada 
para resumir rapidamente grandes quantidades de dados. Pode-se girar suas linhas e 
colunas para ver resumos diferentes dos dados de origem, filtrar os dados por meio da 
exibição de páginas diferentes ou exibir os detalhes de áreas de interesse. 
Um relatório de tabela dinâmica é usado quando se deseja comparar totais 
relacionados, especialmente quando se tem uma longa lista de valores a serem 
resumidos e deseja comparar vários fatos sobre cada valor. Use relatóriode tabela 
dinâmica quando desejar que o Microsoft Excel faça a classificação, a subtotalização e 
a totalização de dados. 
Para criar um relatório de tabela dinâmica, usaremos o Assistente de Tabela 
Dinâmica e Gráfico Dinâmico como um guia para localizar e especificar os dados de 
origem que você desejar para analisar e criar a estrutura do relatório. Você pode, em 
seguida, usar a barra de ferramentas Tabela Dinâmica para organizar os dados dentro 
dessa estrutura. 
1. Abra o arquivo Exemplo8.xls 
2. Selecione Inserir -> Tabela Dinâmica 
 
 
 
 
 
 
 
 
 
 
3. Será aberto o Assistente de Tabela Dinâmica. Na primeira etapa do 
assistente deve-se informar qual a fonte de dados que será utilizada 
como base para a tabela dinâmica. No exemplo usaremos os dados da 
lista na própria planilha do Excel. Clique no botão OK para seguir para a 
próxima etapa 
4. Neste momento o Excel exibe uma planilha com as áreas bem definidas 
da tabela dinâmica e a barra de ferramentas Tabela dinâmica. Nesta 
 50
barra estão os campos da fonte de dados. Para construir a tabela 
dinâmica basta arrastar cada campo para a respectiva posição. 
5. Na figura abaixo temos tela inicial, onde os campos ainda não foram 
arrastados para as respectivas posições no relatório de tabela dinâmica. 
 
6. Arraste o campo Linha para a área onde está escrito: Solte campos de 
filtro de relatório aqui. 
7. Arraste o campo Produto para a área onde está escrito: Solte campos 
de linha aqui. 
8. Arraste o campo Filial para a área onde está escrito: Solte campos de 
coluna aqui. 
9. Arraste o campo Qtde para a área onde está escrito: Solte campos de 
valor aqui. 
 51
GRÁFICO DINÂMICO 
O gráfico é dito dinâmico porque, no próprio gráfico, podemos aplicar filtros e 
mover campos de posição de tal maneira que o gráfico é dinamicamente alterado para 
refletir os filtros e/ou alterações efetuadas. 
1. Selecione a plan1 do arquivo Exemplo8.xls que contém a tabela 
dinâmica criada anteriormente. 
2. Para criar o gráfico dinâmico clique em Inserir e depois escolha o 
modelo de gráfico de sua preferência. 
3. No exemplo está sendo utilizado o gráfico de colunas (Colunas 
agrupadas). 
 
 
 
 52
CONSOLIDAR 
O Recurso de Consolidação de dados permite que se façam cálculos 
consolidados a partir de múltiplas planilhas, múltiplas fontes externas de dados ou uma 
mescla entre planilhas do Excel e fontes externas. 
Se considerar o exemplo: Você recebe, mensalmente, uma planilha com os 
dados de vendas por região/produto. Ao final do ano você precisa criar uma planilha 
com o total anual de vendas. Vamos supor que os dados mensais estejam em uma 
planilha com o nome do mês. E que na célula E3, de cada planilha, tenhamos o total 
de vendas do produto X para a região Sul. Na célula E3 da planilha dos totais anuais, 
teria que ser utilizado a seguinte fórmula: 
=Jan!E3+Fev!E3+Mar!E3+Abr!E3+Mai!E3+Jun!E3+Jul!E3+Ago!E3 
 +Set!E3+Out!E3+Nov!E3+Dez!E3 
Agora imagine o trabalho para criar todas as fórmulas necessárias para o total 
anual, especialmente se a planilha for complexa, com diferentes cálculos. Com o 
recurso de consolidação podemos fazer com que o Excel crie todas as fórmulas 
necessárias, automaticamente. 
Um detalhe importante: “Para utilizarmos o recurso de consolidação, todas as 
planilhas a serem consolidadas devem ter a mesma estrutura, ou seja, o mesmo 
número de linhas e colunas, com os dados nas mesmas posições”. 
 
 
 
 
 
 53
FUNÇÕES DE CÁLCULO DISPONÍVEIS PARA A CONSOLIDAÇÃO DE 
DADOS 
 
 
 
 
 
 
 
 
 
 
 54
V ÍNCULOS 
Um vínculo é uma referência à outra pasta de trabalho, sendo às vezes 
chamada de referência externa. Também pode haver um vínculo com outro programa, 
sendo às vezes chamado de referência remota. Como os dados de outra pasta de 
trabalho ou de outro programa podem mudar e tornar os dados da sua pasta de 
trabalho desatualizados há opções para controlar a atualização dos vínculos. 
O vínculo é útil especialmente quando não é viável manter grandes modelos 
de planilha juntos na mesma pasta de trabalho. 
• Mesclar dados de várias pastas de trabalho: Você pode vincular 
pastas de trabalho de vários usuários ou departamentos e depois integrar 
os dados pertinentes em uma pasta de trabalho de resumo. Assim, 
quando as pastas de trabalho de origem forem alteradas, você não terá 
que alterar manualmente a pasta de trabalho de resumo. 
• Criar exibições diferentes de seus dados: Você pode inserir todos os 
dados em uma ou mais pastas de trabalho de origem e depois criar uma 
pasta de trabalho de relatório que contenha links somente com os dados 
pertinentes. 
• Modelos complexos e extremamente grandes: Ao dividir um modelo 
complicado em uma seqüência de pastas de trabalho interdependentes, 
você poderá trabalhar no modelo sem abrir todas as planilhas 
relacionadas. As pastas de trabalho menores são mais fáceis de alterar, 
não necessitam de muita memória e abrem, salvam e calculam com maior 
rapidez. 
 
 
 
 
 
 
 
 
 
 
 55
CENÁRIOS 
 
Um cenário é um conjunto de valores que o Excel salva e pode substituir 
automaticamente na planilha. Podem-se utilizar cenários para prever o resultado de um 
modelo de planilha. É possível ainda, criar e salvar diferentes grupos de valores em 
uma planilha e alternar para qualquer um desses novos cenários para exibir resultados 
diferentes. 
É possível criar vários cenários para um único modelo de simulação, aonde 
cada cenário apresenta um conjunto diferente de variáveis. 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 56
 
SUPLEMENTOS 
 
Quando instalado, o Microsoft Excel instala uma série de recursos padrão 
automaticamente. Porém, alguns recursos também importantes não serão inseridos de 
imediato. Eles estarão disponíveis apenas quando habilitados. 
Por exemplo, para ativar o suplemento Solver clique em Arquivo -> Opções -> 
Suplementos e selecione o Suplemento Solver. Clique no Botão Ir... 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 57
SOLVER 
É possível determinar os valores resultantes quando precisar alterar mais de 
uma célula usada em uma fórmula e tiver várias restrições para esses valores. O 
Solver ajusta os valores nas células especificadas para produzir o resultado desejado 
da fórmula. 
 
 
 
 
 
 
 
 
 
 
 
 
 58
ATINGIR META 
 
Com a ferramenta Atingir Meta, o Excel varia o valor em uma célula específica 
até uma fórmula dependente de aquela célula retornar o resultado desejado. 
O comando Atingir Meta utiliza um método iterativo para achar o valor que 
atenda a situação proposta. Uma das limitações desse comando é que ele somente 
pode ser utilizado para problemas de uma única variável. 
 
 
 
 
 
 
 
 
 
 
 
 59
ESPECIFICAÇÕES DO EXCEL 2010 
RECURSO LIMITE MÁXIMO 
Pastas de trabalho abertas Limitado pela memória disponível e pelos recursos do 
sistema 
Tamanho da planilha 1.048.576 linhas por 16.384 colunas 
Largura da coluna 255 caracteres 
Altura da linha 409 pontos 
Quebras de página 1.026 na horizontal e na vertical 
Número total de caracteres que uma célula 
pode conter 
32,767 caracteres 
Caracteres em um cabeçalho ou rodapé 255 
Planilhas em uma pasta de trabalho Limitado pela memória disponível e pelos recursos do 
sistema (o padrão é 3 folhas). 
Cores em uma pasta de trabalho 16 milhões de cores (32 bits com acesso total ao 
espectro de cores de 24 bits) 
Exibições nomeadas em uma pasta de 
trabalho 
Limitado pela memória disponível 
Formatos/estilos de célula exclusivos 64.000 
Estilos de preenchimento256 
Espessura e estilos de linha 256 
Tipos exclusivos de fonte 1.024 fontes globais disponíveis para uso; 512 por pasta 
de trabalho 
Formatos de número em uma pasta de 
trabalho 
Entre 200 e 250, dependendo da versão do idioma do 
Excel instalado 
Nomes em uma pasta de trabalho Limitado pela memória disponível 
Janelas em uma pasta de trabalho Limitado pela memória disponível 
Painéis em uma janela 4 
Planilhas vinculadas Limitado pela memória disponível 
Cenários Limitado pela memória disponível; um relatório 
resumido mostra somente os primeiro 251 cenários. 
Células variáveis em um cenário 32 
Células ajustáveis no suplemento Solver 200 
Intervalo de zoom 10 por cento a 400 por cento 
Relatórios Limitado pela memória disponível 
Referências classificadas 64 em uma única classificação; ilimitado quando usar 
classificações seqüenciais 
Níveis de desfazer 100 
Campos em um formulário de dados 32 
Parâmetros de pasta de trabalho 255 parâmetros por pasta de trabalho 
Itens exibidos nas listas suspensas do filtro 10.000 
Células não contíguas que podem ser 
selecionados 
2.147.483.648 células 
 60
Especificações e limites de cálculo 
RECURSO LIMITE MÁXIMO 
Precisão de número 15 dígitos 
Menor número negativo permitido -2,2251E-308 
Menor número positivo permitido 2,2251E-308 
Maior número positivo permitido 9,99999999999999E+307 
Maior número negativo permitido -9,99999999999999E+307 
Maior número positivo via fórmula permitido 1,7976931348623158e+308 
Maior número negativo via fórmula permitido -1,7976931348623158e+308 
Comprimento do conteúdo da fórmula 8,192 caracteres 
Comprimento interno da fórmula 16.384 bytes 
Iterações 32.767 
Matrizes de planilha Limitado pela memória disponível 
Intervalos selecionados 2.048 
Argumentos em uma função 255 
Níveis aninhados de funções 64 
Categorias de função definidas pelo usuário 255 
Número de funções de planilha disponíveis 341 
Tamanho do empilhamento operando 1.024 
Dependência de planilha cruzada 64.000 planilhas que podem se referir a 
outras planilhas 
Dependência de fórmula de matriz de planilha cruzada Limitado pela memória disponível 
Dependência de área Limitado pela memória disponível 
Dependência de área por planilha Limitado pela memória disponível 
Dependência em uma única célula 4 bilhões de fórmulas que podem depender 
de uma única célula 
Comprimento do conteúdo da célula vinculado a pastas de 
trabalho fechadas 
32.767 
Data mais antiga permitida para cálculo 1º de Janeiro de 1900 (1º de Janeiro de 
1904, se for usado o sistema de datas 1904) 
Data mais avançada permitida para cálculo 31 de dezembro de 9999 
Máximo de tempo que pode ser inserido 9999:59:59 
Especificações e limites de gráficos 
RECURSO LIMITE MÁXIMO 
Gráficos vinculados a uma planilha Limitado pela memória disponível 
Planilhas a que se refere um gráfico 255 
Seqüências de dados em um gráfico 255 
Pontos de dados em uma sequência de dados para gráficos 2D Limitado pela memória disponível 
Pontos de dados em uma sequência de dados para gráficos 3D Limitado pela memória disponível 
Pontos de dados para todas as sequências de dados em um gráfico Limitado pela memória disponível 
 61
 
Especificações e limites de relatório tabela ou gráfico dinâmicos 
RECURSO LIMITE MÁXIMO 
Relatórios de tabela dinâmica em uma planilha Limitado pela memória 
disponível 
Itens exclusivos por campo 1.048.576 
Campos de linha ou de coluna em um relatório de tabela dinâmica Limitado pela memória 
disponível 
Filtros de relatório em um relatório de tabela dinâmica 256 (pode ser limitado pela 
memória disponível) 
Campos de valor em um relatório de tabela dinâmica 256 
Fórmulas de item calculado em um relatório de tabela dinâmica Limitado pela memória 
disponível 
Filtros de relatório em um relatório de gráfico dinâmico 256 (pode ser limitado pela 
memória disponível) 
Campos de valor em um relatório de gráfico dinâmico 256 
Fórmulas de itens calculados em um relatório de gráfico dinâmico Limitado pela memória 
disponível 
Extensão do nome MDX para um item da Tabela Dinâmica 32.767 
Extensão de uma cadeia de caracteres relacional da Tabela Dinâmica 32.767 
Itens exibidos nas listas suspensas do filtro 10.000 
Especificações e limites de pasta de trabalho compartilhada 
RECURSO LIMITE MÁXIMO 
Usuários que podem abrir e compartilhar uma pasta de 
trabalho compartilhada ao mesmo tempo 
256 
Exibições personalizadas em uma pasta de trabalho 
compartilhada 
Limitado pela memória disponível 
Dias em que o histórico de alteração é mantido 32.767 (o padrão é 30 dias) 
Pastas de trabalho que podem ser mescladas ao mesmo 
tempo 
Limitado pela memória disponível 
Células que podem ser realçadas em uma pasta de trabalho 
compartilhada 
32.767 
Cores usadas para identificar alterações efetuadas por 
diferentes usuários quando está ativado o realce de alteração 
32 (cada usuário é identificado por uma 
cor separada; as alterações efetuadas pelo 
usuário atual são realçadas em azul 
marinho) 
Tabelas do Excel em uma pasta de trabalho compartilhada 0 (zero) 
 OBSERVAÇÃO Uma pasta de trabalho 
que contém uma ou mais tabelas do Excel 
não pode ser compartilhada.