EXCEL

Prévia do material em texto

Aula 1 
Importação dos Dados
EXCEL AVANÇADO APLICADO 
AO CONTROLE
Unidade 1 
Operações Iniciais com os Dados
Instituto Serzedello Corrêa
RESPONSABILIDADE PELO CONTEÚDO
Tribunal de Contas da União
Secretaria Geral da Presidência
Instituto Serzedello Corrêa
2ª Diretoria de Desenvolvimento de Competências 
Serviço de Planejamento e Projetos Educacionais
SUPERVISÃO
Pedro Koshino
CONTEUDISTA
Helbert de Sousa Arruda
TRATAMENTO PEDAGÓGICO
Flávio Sposto Pompeo
RESPONSABILIDADE EDITORIAL
Tribunal de Contas da União
Secretaria Geral da Presidência
Instituto Serzedello Corrêa
Centro de Documentação
Editora do TCU
PROJETO GRÁFICO
Ismael Soares Miguel
Paulo Prudêncio Soares Brandão Filho
Vivian Campelo Fernandes
DIAGRAMAÇÃO
Vanessa Vieira
Brasil. Tribunal de Contas da União.
Excel Avançado Aplicado ao Controle / Tribunal de Contas da União ; 
conteudista: Helbert de Sousa Arruda. – Brasília : TCU, Instituto Serzedello 
Corrêa, 2012.
25 p. : il.
Conteúdo: Unidade 1: Operações iniciais com os dados. Aula 1: Importação 
dos dados.
Curso realizado no período de 22/05 a 19/06/2012 no Ambiente
Virtual de Educação Corporativa do Tribunal de Contas da União.
1. Importação de dados. 2. Normalização de dados. 3. Manipulação de 
dados. 4. Análise de dados. I. Título.
Ficha catalográfica elaborada pela Biblioteca Ministro Ruben Rosa
© Copyright 2012, Tribunal de Contas de União 
<www.tcu.gov.br>
Permite-se a reprodução desta publicação, 
em parte ou no todo, sem alteração do conteúdo, 
desde que citada a fonte e sem fins comerciais.
[ 3 ]Unidade 1 - Aula 1 - Importação dos Dados
Aula 1 – Importação dos Dados
Quais os principais tipos de dados aceitos pelo Excel?
Como proceder à importação dos dados?
Como navegar nos dados e selecioná-los?
Como copiar e movimentar os dados pela planilha?
Iniciamos nossos estudos pelas operações iniciais, abordando a 
respeito dos dados. Esta apresentação inicial faz-se necessária, pois um 
conjunto de dados, seja digitado ou importado, requer procedimentos 
adequados fazendo com que o processo de manipulação torne o trabalho 
mais fácil e direto.
Para facilitar o estudo, esta aula está organizada da seguinte forma:
Ao final desta aula, esperamos que você tenha condições de:
•	 conhecer quais os tipos de dados aceitos pelo Excel;
•	 executar os procedimentos de importação de dados;
•	 saber navegar pela planilha e selecionar as suas células;
•	 copiar e movimentar os dados pela planilha.
Pronto para começar? Então, vamos!
Aula 1 – Importação dos Dados ��������������������������������������������������������������������������������3
Introdução �������������������������������������������������������������������������������������������������������������������������4
1. Tipos de dados ���������������������������������������������������������������������������������������������������������������5
2. Importação de dados �������������������������������������������������������������������������������������������������9
3. Navegação pela planilha ������������������������������������������������������������������������������������� 15
4. Seleção de intervalos ������������������������������������������������������������������������������������������� 17
5. Cópia e movimentação de dados ����������������������������������������������������������������������� 20
Síntese �������������������������������������������������������������������������������������������������������������������������������� 24
Referências bibliográficas ����������������������������������������������������������������������������������� 25
[ 4 ] EXCEL AVANÇADO APLICADO AO CONTROLE
Introdução 
A versão do Microsoft Excel 2003 já foi um importante produto, 
segundo a opinião da maioria dos especialistas. Já o Microsoft Excel 
2007, por sua vez, eleva o padrão deste programa a um novo nível. Essa 
versão atualiza as tarefas comuns de planilhas e aprimora sensivelmente 
a potencialidade e a flexibilidade do programa. Quer seja uma tarefa 
simples, quer complexa, o Microsoft Excel 2007 derruba as barreiras 
entre a suposição e os dados.
A primeira surpresa dos usuários ao abrir o Microsoft Excel 2007 
é a ausência das barras de menus e de ferramentas, visto que foram 
substituídas pela Faixa de Opções. O programa possui uma nova 
interface orientada a resultados, novos recursos de fórmulas e um meio 
muito mais rápido para criar gráficos.
Os botões que aparecem automaticamente na planilha ajudam o 
usuário com tarefas, como a verificação de erros em fórmulas, as opções 
de correção automática, colagem, preenchimento automático e inserção. 
Com um clique em um botão, pode-se escolher opções relacionadas à 
sua tarefa sem sair da planilha ou das células em que está trabalhando.
A nova interface de utilizador orientada para os resultados facilita o 
trabalho no Microsoft Excel 2007. Os comandos e as funcionalidades até 
aqui escondidos em complexos menus e barras de ferramentas são agora 
fáceis de encontrar em separadores orientados por tarefas com grupos 
lógicos de comandos e funcionalidades. Muitas caixas de diálogo foram 
substituídas por galerias de lista pendente com as opções disponíveis e 
sugestões descritivas ou foi disponibilizada pré-visualização de exemplos 
que o ajuda a escolher a opção certa.
O programa apresenta as ferramentas mais adequadas para 
concretizar a tarefa, seja qual for a atividade a ser executada na nova 
interface, como formatar ou analisar dados, por exemplo. O Microsoft 
Excel 2007, que daqui por diante será chamado apenas de Excel 2007, 
possui várias guias, grupos e comandos para realizar cada uma dessas 
tarefas.
[ 5 ]Unidade 1 - Aula 1 - Importação dos Dados
1. Tipos de dados
Por meio do Excel 2007, é possível trabalhar com três formatos de 
dados nas planilhas. São eles:
•	 Valores Constantes: um valor constante é um tipo de dado 
digitado diretamente na célula; sendo que pode estar em formato 
de número, incluindo datas, horas, moedas, porcentagens, 
frações, notação científica ou em formato de texto. Os valores 
são constantes e não podem ser alterados a menos que a célula 
seja selecionada e seus dados editados. Exemplos: 43, Código, 
29/12/2011, 8:43 etc.
•	 Fórmulas: uma fórmula é uma sequência de valores, referências 
de célula, nomes ou operadores que produzam um novo valor 
a partir dos valores existentes. As fórmulas sempre começam 
com um sinal de igual (=). O valor resultante de uma fórmula 
altera-se quando outros valores relacionados da planilha são 
modificados. Exemplos: =A1+A2, =C4*10%, =D3/G5 etc.
•	 Funções: as funções seguem a mesma linha de trabalho 
das fórmulas com a diferença de que estas possuem nomes 
predefinidos e seus respectivos parâmetros, fazendo com que 
determinadas operações sejam executadas de maneira simples e 
direta. Exemplos: =SOMA(A1:A5); =HOJE(); =MÉDIA(B3:G7) 
etc.
No Excel 2007, pode-se inserir três tipos básicos de constantes: 
número, data e hora, e texto:
Número
Para digitar um número como valor constante, selecione uma célula 
e digite o número. Os números podem incluir caracteres numéricos (de 
0 a 9) e qualquer um dos caracteres especiais como, por exemplo, sinal de 
mais, de menos, parênteses, vírgula, barra, cifrão e sinal de porcentagem.
Se uma entrada consistir em qualquer caractere diferente dos 
caracteres especiais apresentados, o Excel 2007 interpretará como texto. 
Um exemplo de entrada válida em uma célula seria rua dos Milagres nº 
173, CR 170.
•	 Pode-se incluir pontos nos números e vírgula, como em 
1.000.000,00;
[ 6 ] EXCEL AVANÇADO APLICADO AO CONTROLE
•	 uma vírgula numa entrada numérica indica um valor decimal;
•	 os sinais de adição digitados antes dos números são ignorados;
•	 coloque um sinal de subtração antes dos números negativos ou 
coloque-os entre parênteses.Ao criar uma nova planilha, todas as células utilizarão o formato de 
número Geral. Sempre que possível, o Excel 2007 atribui automaticamente 
o formato de número correto para a sua entrada. Por exemplo, quando 
se digita um número com o R$ antes ou um sinal de porcentagem após, 
o programa altera automaticamente o formato da célula de Geral para 
Monetário ou Porcentagem.
Os números digitados são alinhados automaticamente pela direita 
da célula e para incluir um número numa fórmula, basta digitá-lo após a 
inserção do sinal de igual.
Nas fórmulas, não se pode usar parênteses para indicar números 
negativos, pontos para separar milhares e nem cifrões ($) antes dos 
números. Se for digitado um sinal de porcentagem (%) depois de um 
número, o Excel 2007 irá interpretá-lo como operador de porcentagem 
e armazená-lo-á como parte da fórmula. O operador de porcentagem 
atuará sobre o número anterior quando a fórmula for calculada.
Texto
Um texto se compõe de letras ou qualquer combinação de números 
e letras. Qualquer conjunto de caracteres digitados em uma célula que 
não for interpretado pelo Excel 2007 como número, fórmula, data, hora, 
valor lógico ou valor de erro será interpretado como texto. Quando o 
texto é digitado, os caracteres são alinhados automaticamente à esquerda 
na célula.
Para digitar um texto, selecione uma célula e digite-o. Uma célula 
aceita até 32.767 caracteres e pode-se inclusive formatar os caracteres 
individualmente dentro de uma célula.
Data e Hora
O Excel 2007 utiliza o relógio de 12 horas, para isso basta digitar 
“am” ou “pm”, por exemplo, 3:00 PM. Pode-se também digitar as letras 
“a” ou “p” em vez de “am” ou “pm”; deixando um espaço entre a hora e a 
letra, a menos que queira digitar “am” ou “pm”, assim, o Excel 2007 exibe 
a hora utilizando o relógio de 24 horas, por exemplo, 15:00.
[ 7 ]Unidade 1 - Aula 1 - Importação dos Dados
É possível digitar data e hora na mesma célula, bastando para isso, 
inserir um espaço entre elas, lembrando que, para digitar datas, usa-se a 
barra (/) ou o hífen (-).
Embora possam ser exibidas datas e horas em diversos formatos 
padrão, o Excel 2007 armazena todas as datas como números seriais e 
todas as horas como frações decimais. Sendo datas e horas tratadas como 
números, elas podem ser adicionadas, subtraídas e incluídas em outros 
cálculos. As datas e horas podem ser vistas como formato de números 
seriais ou como frações decimais utilizando-se o recurso de formatação 
de números.
Não se pode digitar números em formato de data ou de hora 
diretamente numa fórmula, pois seria preciso inserir a data e a hora 
em formato de texto, entre aspas, assim, serão convertidas no número 
correspondente quando calcular a fórmula. Veja o exemplo de uma 
operação com datas a seguir: a fórmula =”12/5/11”-”5/3/11” no formato 
DD/MM/AA apresentaria como resultado 68.
Exercício: Módulo01_Aula01_Exe01
1. Abra o programa Excel 2007;
2. altere a largura da coluna B para 18,00 (observe o valor sendo 
alterado na caixa de informação de largura);
Atenção!
3. na célula B2 digite o valor 387, pressione ENTER e observe que o 
conteúdo foi alinhado automaticamente para a direita da célula;
4. na célula B3 digite o texto TCU, pressione ENTER e observe 
que o conteúdo foi alinhado automaticamente para a esquerda 
da célula;
[ 8 ] EXCEL AVANÇADO APLICADO AO CONTROLE
5. na célula B4 digite o valor R$ 456. Após pressionar a tecla 
ENTER, volte com a seta do teclado para a célula B4 e note 
que o conteúdo foi automaticamente formatado como Moeda 
(esta informação é exibida no grupo Número presente na guia 
Início);
6. na célula B5 digite a data 15/09/2011, pressione ENTER e 
observe que o conteúdo foi alinhado automaticamente para a 
direita da célula (significando que é possível realizar operações 
matemáticas com as datas);
7. na célula B6 digite o valor 45, pressione ENTER e na célula B7 
digite a fórmula =B5+B6. Observe que a resposta inserida na 
célula B7 é a data 30/10/2011, ou seja, somou-se 45 dias à data 
inicial presente na célula B5;
8. na célula B8 digite a hora 10:37, na célula B9 digite a hora 8:52 
e na célula B10 digite a fórmula =B8-B9. Observe que surge o 
valor 01:45 significando que essa é a diferença de tempo entre 
as horas digitadas;
9. vá até a célula C2 e digite o valor (83), dentro dos parênteses 
mesmo, pressione ENTER e observe que o valor é alterado 
automaticamente para -83;
10. na célula C3 digite a fórmula =2^3-5+3*4*10%/2 e tente 
identificar a sequência dos cálculos e porque o resultado desta 
expressão resultou 3,6.
11. ao final, salve o arquivo com o nome Mod01_Aula01_Exe01.
xlsx na sua pasta Meus Documentos e feche o Excel 2007.
[ 9 ]Unidade 1 - Aula 1 - Importação dos Dados
2. Importação de dados
O Excel 2007 permite importar dados de um arquivo texto para uma 
planilha. Esse procedimento pode ser efetivado mediante a utilização do 
comando Abrir ou pelo Assistente de Importação que examina o arquivo 
original o qual cederá os dados a serem importados e auxiliará na escolha 
da forma mais adequada ao resultado pretendido.
Existem três formatos de arquivo de texto usados com mais frequência 
em importações para planilhas do Excel 2007. São eles:
•	 arquivos de texto com largura fixa (.txt), nos quais o caractere 
TAB (código de caractere ASCII 009) geralmente separa cada 
campo de texto;
•	 arquivos de texto (.txt) com delimitadores que separam os 
campos, podendo apresentar-se com caracteres diferentes, como: 
ponto e vírgula, barra, traço etc;
•	 arquivos de texto com valores separados por vírgulas (.csv), nos 
quais o caractere vírgula (,) geralmente separa cada campo de 
texto.
E de acordo com o site de suporte da Microsoft referente ao Excel 
2007, os outros formatos aceitos são os seguintes: No momento em que Excel 
2007 executa a operação 
de abertura de um arquivo 
(.csv), ele utiliza as 
configurações atuais de 
formato de dados padrão 
para interpretar como 
importar cada coluna 
de dados. Se quisermos 
ter mais flexibilidade na 
conversão destas colunas 
em diferentes formatos 
de dados, é recomendável 
utilizar o Assistente de 
Importação de Texto.
FORMATO EXTENSÃO DESCRIÇÃO
Pasta de trabalho do Excel .xlsx
O formato de arquivo padrão com base 
em XML do Office Excel 2007 não pode 
armazenar o código de macro do Microsoft 
VBA (Visual Basic for Applications) ou 
planilhas de macro do Microsoft Office 
Excel 4.0 (.xlm).
Pasta de trabalho do Excel 
(código)
.xlsm
O formato de arquivo do Office Excel 
2007, baseado em XML e habilitado por 
macro, armazena código de macro VBA ou 
planilhas de macro do Excel 4.0 (.xlm).
Pasta de Trabalho Binária 
do Excel .xlsb
O formato de arquivo binário do Office 
Excel 2007 (BIFF12).
Modelo .xltx
O formato de arquivo padrão do Office 
Excel 2007 para um modelo do Excel não 
pode armazenar código de macro VBA ou 
planilhas de macro do Excel 4.0 (.xlm).
[ 10 ] EXCEL AVANÇADO APLICADO AO CONTROLE
Modelo (código) .xltm
O formato de arquivo padrão do Office 
Excel 2007 habilitado por macro para 
um modelo do Excel armazena código de 
macro VBA ou planilhas de macro do Excel 
4.0 (.xlm).
Pasta de Trabalho do Excel 
97- Excel 2003
.xls O formato de arquivo binário do Excel 97 - 
Excel 2003 (BIFF8).
Modelo do Excel 97- Excel 
2003
.xlt
O formato de arquivo binário do Excel 97 
- Excel 2003 (BIFF8) para um modelo do 
Excel.
Pasta de trabalho do 
Microsoft Excel 5.0/95
.xls O formato de arquivo binário do Excel 
5.0/95 (BIFF5).
XML Spreadsheet 2003 .xml Formato de arquivo do XML Spreadsheet 
2003 (XMLSS).
XML Data .xml Formato do XML Data.
Suplementos do Excel .xlam
O suplemento do Office Excel 2007 
baseado em XML e habilitado por macro, 
um programa de suplemento que éprojetado para executar código adicional, 
fornece suporte ao uso de projetos VBA e 
de planilhas de macro do Excel 4.0 (.xlm).
Suplemento do Excel 97-
2003
.xla
O Suplemento do Excel 97-2003, um 
programa complementar que foi projetado 
para executar código adicional, oferece 
suporte para o uso de projetos do VBA.
Pasta de Trabalho do Excel 
4.0 .xlw
Um formato de arquivo do Excel 4.0 que 
salva apenas planilhas, planilhas de gráfico 
e planilhas de macro. Você pode abrir 
uma pasta de trabalho nesse formato de 
arquivo no Office Excel 2007, mas não 
pode salvar um arquivo do Excel nesse 
formato de arquivo.
[ 11 ]Unidade 1 - Aula 1 - Importação dos Dados
Exercício: Módulo01_Aula01_Exe02
1. Abra o programa Excel 2007;
2. posicionado na célula A1 da Plan1, selecione a guia Dados, em 
seguida, no grupo Obter Dados Externos, escolha o botão De 
Texto;
3. aponte para a sua pasta Meus Documentos e escolha o arquivo 
Entidades_MDS.csv;
4. clique no botão Importar;
O Excel 2007 abre automaticamente o Assistente de Importação de 
Texto (Etapa 1 de 3). Nesta primeira tela, é possível escolher o formato 
de origem do arquivo .csv (Delimitado ou Largura Fixa), a linha inicial 
de importação e a origem do arquivo.
[ 12 ] EXCEL AVANÇADO APLICADO AO CONTROLE
5. em nosso caso, apenas clique no botão Avançar, pois as 
configurações padrão desta primeira etapa satisfazem as nossas 
necessidades;
6. wna tela da etapa 2, altere o delimitador de Tabulação para 
Vírgula. As demais opções não necessitam de alteração;
[ 13 ]Unidade 1 - Aula 1 - Importação dos Dados
7. em seguida clique novamente no botão Avançar;
Nesta etapa 3, é possível definir o formato de cada coluna de dados 
do arquivo importado clicando em cada uma delas e escolhendo o 
respectivo formato.
Pode também ignorar a importação de uma coluna específica e 
definir/alterar os caracteres de formatação de valores como ponto para 
milhares e vírgula para decimais, por meio do botão Avançado.
8. ao final clique no botão Concluir;
Para finalizar a operação, escolha a posição inicial em que os dados 
do arquivo (.csv) serão descarregados.
[ 14 ] EXCEL AVANÇADO APLICADO AO CONTROLE
9. aceite a célula A1 como início da área de importação dos dados 
e clique no botão OK;
Pronto! Os dados do arquivo (.csv) foram importados para uma 
planilha do Excel 2007 utilizando-se o Assistente de Importação de 
Texto. Para finalizar, basta salvar o arquivo no formato Excel 2007, ou 
seja, na extensão (.xlsx).
10. clique no Botão Office, escolha a opção Salvar e em seguida 
aponte para a pasta Meus Documentos e dê o nome Mod01_
Aula01_Exe02.xlsx.
[ 15 ]Unidade 1 - Aula 1 - Importação dos Dados
3. Navegação pela planilha
Ao trabalhar com uma planilha eletrônica que possua 
uma quantidade considerável de dados, sempre é necessário nos 
movimentarmos para um determinado local de forma rápida. O Excel 
2007 permite movimentar-se pela planilha de várias maneiras diferentes 
por intermédio de técnicas que incluem o teclado e o mouse, bem como 
botões presentes na tela.
Essas técnicas devem ser abordadas pelo simples fato de que as 
planilhas, quando pequenas, tendem a crescer gradativamente, ou seja, 
à medida que as planilhas vão sendo aprimoradas, elas crescem e assim 
surge a necessidade de uma movimentação mais rápida entre os vários 
pontos.
Observe a tabela abaixo com as principais técnicas de movimentação 
pela planilha:
TECLA(S) AÇÃO DE MOVIMENTAÇÃO
Setas de direção do teclado Move o cursor uma célula acima, abaixo, à esquerda ou à direita, conforme a seta de direção pressionada.
CTRL + Seta de direção do 
teclado
Move o cursor para as extremidades da planilha ativa, ou 
seja, para um dos quatro cantos da planilha conforme a 
seta de direção pressionada em conjunto com a tecla CTRL.
HOME Move o cursor para o início da linha atual.
END
Tecla END, seguida de uma tecla de direção qualquer, tem 
o mesmo efeito que a combinação das teclas CTRL + seta 
de direção apresentada acima.
CTRL + HOME Move o cursor para a célula A1.
CTRL + END Move o cursor para a extremidade inferior direita da 
planilha ativa.
PGDN Move uma tela para baixo.
PGUP Move uma tela para cima.
CTRL + PGDN Move para a próxima planilha.
CTRL + PGUP Move para a planilha anterior.
F5 Abre a caixa de Ir Para, permitindo que o usuário digite o 
endereço específico da célula a qual deseja deslocar-se.
[ 16 ] EXCEL AVANÇADO APLICADO AO CONTROLE
Exercício: Módulo01_Aula01_Exe03
Agora vamos aprender a utilizar algumas técnicas de navegação na 
planilha de dados importada no exercício anterior.
1. Caso o programa Excel 2007 não esteja aberto, abra-o;
2. abra o arquivo Mod01_Aula01_Exe02.xlsx (caso não esteja 
aberto);
3. observe que você é posicionado inicialmente na célula A1;
4. pressione CTRL + seta para direita. Observe que o cursor é 
deslocado para a última coluna, ou seja, para a célula H1;
5. agora pressione CTRL + seta para baixo. Observe que o cursor 
é deslocado para a última linha desta coluna, ou seja, para a 
célula H71;
6. para voltar à célula A1, pressione as teclas CTRL + HOME;
7. para deslocar-se para a outra extremidade da planilha ativa 
diretamente, ou seja, para a última linha e a última coluna, 
basta pressionar as teclas CTRL + END;
8. pressionando somente a tecla HOME, o cursor é deslocado 
para a primeira coluna preservando a linha atual;
9. pressione a tecla PGDN sucessivas vezes para “pular” para as 
próximas telas da planilha;
10. pressionando a tecla PGUP várias vezes, o cursor desloca-se no 
sentido contrário, ou seja, “pula” para as telas anteriores;
11. pressione CTRL + HOME para retornar à célula A1;
12. pressione CTRL + PGDN e observe que você foi deslocado para 
a Plan2 (fazendo isso seguidas vezes, o Excel pula sempre para 
a próxima planilha até atingir a última);
13. pressione CTRL + UP para retornar uma planilha de cada vez;
14. pressione a tecla de função F5;
Atenção!
[ 17 ]Unidade 1 - Aula 1 - Importação dos Dados
15. na caixa Ir Para, digite a referência FT37498, pressione ENTER 
e observe que o cursor é deslocado para esta célula;
16. pressione CTRL + HOME para retornar à célula A1 e salve o 
arquivo.
4. Seleção de intervalos
Uma seleção consiste de itens destacados, que na tela serão afetados 
pelo próximo comando ou ação. Uma seleção de planilha pode ser um 
intervalo de célula, um objeto, um conjunto de planilhas, caracteres da 
barra de fórmulas etc.
Existem dois tipos de seleção de células em uma planilha:
•	 Intervalo adjacente: é uma seleção sequencial, em que todas 
as células do intervalo selecionado encontram-se interligadas, 
seja por linha, coluna ou ambas.
•	 Intervalo não-adjacente: é uma seleção múltipla de células ou 
intervalos de células que não estão interligados uns aos outros.
Para selecionar um conjunto de células em uma planilha com o 
mouse, use as ações apresentadas na tabela a seguir:
Após selecionar um 
intervalo adjacente ou 
não-adjacente, poderá 
ser usada a tecla TAB para 
mover a célula ativa dentro 
da seleção
[ 18 ] EXCEL AVANÇADO APLICADO AO CONTROLE
SELEÇÃO PROCEDIMENTO
Uma única célula Pressione o botão do mouse sobre a célula desejada.
Um intervalo adjacente Arraste o mouse para as células desejadas, em qualquer 
sentido.
Uma linha ou uma coluna 
inteira
Pressione o botão do mouse sobre o cabeçalho da linha ou 
da coluna.
Múltiplas linhas ou colunas 
em sequência Arraste para os cabeçalhos das linhas ou das colunas.
Uma planilha inteira
Pressione o botão do mouse sobre o retângulo cinza que 
fica à esquerda do cabeçalho da coluna A e acima do 
cabeçalho da linha 1.
Um intervalo não-
adjacente
Arraste o mouse criando a primeira seleção e em seguida 
mantenha pressionada atecla CTRL conforme for 
arrastando para as outras seleções.
Exercício: Módulo01_Aula01_Exe04
Vamos aprender a utilizar algumas técnicas de seleção de células 
com o mouse na planilha de dados importada no exercício 02.
1. Caso o programa Excel 2007 não esteja aberto, abra-o;
2. abra o arquivo Mod01_Aula01_Exe02.xlsx (caso não esteja 
aberto);
3. observe que você é posicionado inicialmente na célula A1;
4. clique no título da coluna D para selecionar toda esta coluna;
5. agora clique no título da linha 7 para selecionar toda a linha 
(observe que a seleção anterior é perdida);
6. clique no título da coluna B e arraste até o da coluna E para 
selecionar todas estas colunas;
7. pressione as teclas CTRL + HOME para deslocar o cursor até a 
célula A1 e desativar a seleção atual;
8. clique no retângulo cinza que fica à esquerda do cabeçalho da 
coluna A e acima do cabeçalho da linha 1 para selecionar toda 
a planilha;
Atenção!
[ 19 ]Unidade 1 - Aula 1 - Importação dos Dados
PARA PRESSIONE
Estender a seleção SHIFT + setas de direção.
Estender a seleção em um 
intervalo CTRL + SHIFT + setas de direção.
Estender a seleção até o 
início da linha SHIFT + HOME.
Estender a seleção até o 
fim da linha
SHIFT + END.
Selecionar linha inteira SHIFT + Barra de espaço.
Selecionar coluna inteira CRTL + Barra de espaço.
Selecionar intervalo não-
adjacente SHIFT + F8 ao final de cada intervalo selecionado.
Selecionar um intervalo 
específico
F5 e em seguida digitar o endereço do intervalo.
Selecionar planilhas
Em sequência: tecla SHIFT pressionada e clique na aba da 
última planilha desejada.
Alternadas: tecla CTRL pressionada e clique nas abas das 
demais planilhas.
Selecionar o intervalo de 
uma planilha ativa
CTRL + T (se o cursor estiver fora da planilha ativa, o 
pressionamento das teclas selecionará a planilha inteira, 
incluindo as células em branco fora desta área).
9. pressione novamente as teclas CTRL + HOME desativar a 
seleção atual e deslocar o cursor para a célula A1;
10. aponte o mouse para a célula B4 e arraste até a célula B10. 
Agora mantenha a tecla CTRL pressionada, aponte o mouse 
para a célula E13 e arraste até a célula F19. Observe que os dois 
intervalos são selecionados simultaneamente;
11. clique em qualquer célula com o mouse para desativar as 
seleções;
12. agora treine as demais seleções apresentadas na tabela anterior.
O Excel 2007 permite selecionar também um conjunto de células 
pelo teclado, facilitando assim a vida de quem não trabalha com o mouse 
ou dos que preferem selecionar células pelo teclado. A tabela a seguir 
exibe algumas das formas disponíveis de seleção pelo teclado:
[ 20 ] EXCEL AVANÇADO APLICADO AO CONTROLE
Exercício: Módulo01_Aula01_Exe05
Agora vamos aprender a utilizar algumas técnicas de seleção de 
células com o teclado na planilha de dados importada no exercício 02.
1. Abra o arquivo Mod01_Aula01_Exe02.xlsx (caso não esteja 
aberto);
2. estando posicionado na célula A1, mantenha a tecla SHIFT 
pressionada enquanto aperta e solta a tecla de seta para baixo 
várias vezes até que a seleção atinja a célula A8;
3. ainda com a tecla SHIFT pressionada, agora aperte e solte 
várias vezes a tecla de seta para a direita até que a seleção atinja 
a célula D8;
4. desfaça a seleção e leve o cursor até a célula E5, pressione 
simultaneamente as teclas CTRL + Barra de Espaço e observe 
que toda a coluna E foi selecionada;
5. desfaça a seleção, mantenha o cursor em qualquer célula que 
possua conteúdo, ao final pressione simultaneamente as teclas 
CTRL + T e observe que toda a área da planilha ativa foi 
selecionada, deixando sem seleção todas as células fora desta 
área (para ver este resultado, recorra às barras de rolagem 
horizontal ou vertical até que a planilhas se desloque até as 
células vazias);
6. para desfazer a seleção e se deslocar para a célula A1, pressione 
simultaneamente as teclas CTRL + HOME;
7. agora treine as demais seleções apresentadas na tabela anterior.
5. Cópia e movimentação de dados
Eventualmente, são inseridas informações em locais errados na 
planilha, para corrigir este tipo de problema não é necessário apagar a 
informação e redigitá-la no local correto, basta mover a informação ou o 
conjunto para o devido local.
A diferença básica entre este recurso dos aplicativos do Windows e 
do Excel 2007 é se for movimentada ou copiada uma informação de uma 
[ 21 ]Unidade 1 - Aula 1 - Importação dos Dados
célula que seja uma fórmula, não serão colados os mesmos resultados 
da seleção anterior e sim o resultado dos novos elementos com que a 
fórmula se relaciona. Veja o exemplo abaixo:
Nas respectivas células, encontram-se as seguintes informações:
A1 = 3 B1 = 4 C1 = A fórmula =A1+B1 que resulta 7.
Se for copiada a fórmula que está em C1 para a célula C2 e que 
existam os valores nas células abaixo, a fórmula terá o seguinte efeito:
A2 = 5 B2 = 8 C2 = A fórmula será alterada para =A2+B2 
resultando 13.
Para copiar uma informação utilizando o realçado, posicione o 
ponteiro do mouse no canto inferior direito da borda do realçado até 
que este ponteiro se torne um sinal de “mais” (+) escuro e então arraste 
(pode-se também dar um duplo clique) a seleção no sentido desejado. 
Para ter uma melhor ideia sobre este processo, observe a figura a seguir:
Os botões Copiar e Colar, 
presentes no grupo Área 
de Transferência, da guia 
Início, também podem ser 
utilizados para realizar 
esta operação.
Já a operação de movimentação é bastante simples. As células ou 
seleção que contenham as informações a serem movidas devem ser 
previamente selecionadas e logo após executa-se o processo de arraste. 
Para executar esta operação, o mouse deve transformar-se em uma seta 
assim que for posicionado sobre a borda da seleção ou da célula.
[ 22 ] EXCEL AVANÇADO APLICADO AO CONTROLE
A movimentação pode ser considerada uma das maiores vantagens 
existentes no Excel 2007, pois como há um relacionamento de células 
por meio de fórmulas, estas não perdem a referência ao serem movidas 
para outra parte da planilha. Foi tomado o devido cuidado por parte 
da Microsoft para que essa operação não se tornasse um caos durante a 
preparação de planilhas.
Exercício: Módulo01_Aula01_Exe06
Agora vamos aprender a utilizar algumas técnicas de seleção de 
células com o teclado na planilha de dados importada no exercício 02.
1. Abra o arquivo Mod01_Aula01_Exe06.xlsx que se encontra na 
pasta Meus Documentos de seu computador;
2. coloque o cursor na célula F2, digite a seguinte fórmula: 
=E2*25% e pressione ENTER (usamos como cálculo neste 
exemplo, que o valor da contrapartida em relação ao valor do 
convênio seja de 25%);
Os botões Recortar e Colar, 
presentes no grupo Área 
de Transferência, da guia 
Início, também podem ser 
utilizados para realizar esta 
operação.
3. volte à célula F2 e aponte o mouse na alça de cópia e clique duas 
vezes (pode-se também obter a cópia arrastando até a célula 
F16);
Observe que o Excel 2007 preencheu todas as células abaixo com a 
mesma fórmula mantendo as respectivas referências.
[ 23 ]Unidade 1 - Aula 1 - Importação dos Dados
4. desloque o cursor até a célula G2 e digite a fórmula =E2-
F2 para obter a diferença entre os valores do convênio e da 
contrapartida;
5. execute o mesmo procedimento anterior para copiar as fórmulas 
até a célula G16;
6. vá até a célula I2 e digite a fórmula =E2-H2, em seguida copie 
esta fórmula para as demais linhas da mesma maneira que foi 
executado nos itens acima.
A planilha agora está completa. Acesse o botão Office e selecione a 
opção Salvar para guardar essas novas modificações realizadas no arquivo.
[ 24 ] EXCEL AVANÇADO APLICADO AO CONTROLE
Síntese
Nesta aula, conhecemos os tiposde dados aceitos pelo Excel 2007 
bem como os formatos de arquivos que podem ser importados para 
suas planilhas. Percebemos que após serem importados, esses dados 
apresentam-se de maneira tal nas planilhas que forçam os seus usuários 
a utilizarem técnicas que facilitem sua manipulação.
Também estudamos que o Excel 2007 possui a execução dos 
recursos de recortar, copiar e colar, tanto por intermédio dos botões 
tradicionais, como também por ações executadas pelo mouse.
[ 25 ]Unidade 1 - Aula 1 - Importação dos Dados
Referências bibliográficas 
Microsoft. Novidades no Microsoft Office Excel 2007. Novidades na 
versão 2007. Disponível em: <http://office.microsoft.com/pt-pt/get-
started-with-2007/novidades-no-microsoft-office-excel-2007-HA01007 
3873.aspx>. Acesso em 19 de fevereiro de 2012.
Microsoft. Serviço de Suporte ao Excel 2007. Importando dados. 
Disponível em: <http://office.microsoft.com/pt-br/excel-help/importar-
ou-exportar-arquivos-de-texto-HP010099725.aspx>. Acesso em 20 de 
fevereiro de 2012.
Microsoft. Serviço de Suporte ao Excel 2007. Importar ou exportar 
arquivos de texto. Disponível em: <http://office.microsoft.com/pt-br/
excel-help/importar-ou-exportar-arquivos-de-texto-HP010099725.
aspx>. Acesso em 21 de fevereiro de 2012.
Aula 2 
Ajustes dos Dados
EXCEL AVANÇADO APLICADO 
AO CONTROLE
Unidade 1 
Operações Iniciais com os Dados
Instituto Serzedello Corrêa
RESPONSABILIDADE PELO CONTEÚDO
Tribunal de Contas da União
Secretaria Geral da Presidência
Instituto Serzedello Corrêa
2ª Diretoria de Desenvolvimento de Competências 
Serviço de Planejamento e Projetos Educacionais
SUPERVISÃO
Pedro Koshino
CONTEUDISTA
Helbert de Sousa Arruda
TRATAMENTO PEDAGÓGICO
Flávio Sposto Pompeo
RESPONSABILIDADE EDITORIAL
Tribunal de Contas da União
Secretaria Geral da Presidência
Instituto Serzedello Corrêa
Centro de Documentação
Editora do TCU
PROJETO GRÁFICO
Ismael Soares Miguel
Paulo Prudêncio Soares Brandão Filho
Vivian Campelo Fernandes
DIAGRAMAÇÃO
Vanessa Vieira
Brasil. Tribunal de Contas da União.
Excel Avançado Aplicado ao Controle / Tribunal de Contas da União ; 
conteudista: Helbert de Sousa Arruda. – Brasília : TCU, Instituto Serzedello 
Corrêa, 2012.
23 p. : il.
Conteúdo: Unidade 1: Operações iniciais com os dados. Aula 2: Ajustes dos 
dados.
Curso realizado no período de 22/05 a 19/06/2012 no Ambiente
Virtual de Educação Corporativa do Tribunal de Contas da União.
1. Importação de dados. 2. Normalização de dados. 3. Manipulação de 
dados. 4. Análise de dados. I. Título.
Ficha catalográfica elaborada pela Biblioteca Ministro Ruben Rosa
© Copyright 2012, Tribunal de Contas de União 
<www.tcu.gov.br>
Permite-se a reprodução desta publicação, 
em parte ou no todo, sem alteração do conteúdo, 
desde que citada a fonte e sem fins comerciais.
[ 3 ]Unidade 1 - Aula 2 - Ajuste dos Dados
Aula 2 – Ajuste dos Dados
Qual a utilidade da colagem especial?
Como dividir as planilhas em painéis?
Como trabalhar com grupos de planilhas?
Quais as opções de classificação das células?
Iniciamos nossos estudos pelas operações iniciais com os dados. 
Esta apresentação inicial faz-se necessária, pois um conjunto de dados, 
seja digitado ou importado, requer procedimentos adequados fazendo 
com que o processo de manipulação torne o trabalho mais fácil e direto.
Para facilitar o estudo, esta aula está organizada da seguinte forma:
Ao final desta aula, esperamos que você tenha condições de:
•	 conhecer a utilidade da opção de colagem especial;
•	 saber como dividir uma planilha grande em painéis;
•	 trabalhar com dados em diversas planilhas;
•	 executar o processo de classificação do conteúdo das células.
Pronto para começar? Então, vamos!
Aula 2 – Ajuste dos Dados �������������������������������������������������������������������������������������������3
1. Colagem especial ���������������������������������������������������������������������������������������������������������4
2. Divisão e congelamento de painéis ���������������������������������������������������������������� 10
3. Múltiplas planilhas ����������������������������������������������������������������������������������������������� 13
4. Classificação de dados ����������������������������������������������������������������������������������������� 17
Síntese �������������������������������������������������������������������������������������������������������������������������������� 22
Referências bibliográficas ����������������������������������������������������������������������������������� 23
[ 4 ] EXCEL AVANÇADO APLICADO AO CONTROLE
1. Colagem especial
Um dos recursos mais utilizados atualmente pelos programas 
de computador é o de Copiar, Recortar e Colar. Estes três comandos 
permitem que os usuários economizem tempo em suas tarefas, 
realizando, com extrema facilidade, operações de cópia e movimentação 
de elementos.
•	 Copiar  Colar (ou CTRL+C e CTRL+V): permite que 
determinados elementos sejam copiados de um local de origem 
para um destino qualquer.
•	 Recortar  Colar (ou CTRL+X e CTRL+V): já essa 
combinação permite que elementos sejam movidos de um local 
(origem) para outro (destino) de maneira rápida e direta.
A combinação desses comandos executam inúmeras tarefas que 
facilitam a integração de determinados aplicativos. No Excel 2007, bem 
como em outros programas, existe um comando denominado Colar 
Especial que permite dar mais praticidade às operações de edição.
No Excel 2007, selecionamos uma ou mais células que se deseja 
utilizar e em seguida copiamos o conteúdo para a área de transferência. 
Ao final, deve-se clicar na parte inferior do botão Colar, presente no 
grupo Área de Transferência da guia Início, em seguida na opção Colar 
Especial ou por meio do atalho de teclado, CTRL + ALT + V. Isto fará 
com que a caixa de diálogo Colar Especial seja apresentada na tela.
[ 5 ]Unidade 1 - Aula 2 - Ajuste dos Dados
As opções existentes no grupo Colar são:
OPÇÃO AÇÃO
Tudo Cola todo o conteúdo utilizando a mesma formatação.
Fórmulas Cola somente a fórmula utilizada no trecho copiado.
Valores Cola apenas os valores.
Formatos Cola apenas a formatação do trecho copiado.
Comentários Cola os comentários anexados à célula.
Validação Cola as regras de validação das células copiadas.
Todos usando tema de 
origem
Cola todo o conteúdo com a formatação e o tema de 
origem.
Tudo, exceto bordas Cola todo o conteúdo com exceção das bordas.
Larguras e colunas Cola as larguras ou intervalos entre as colunas.
Fórmulas e formatos de 
número Cola somente as fórmulas e a formatação dos números.
Valores e formatos de 
número Cola os valores e a formatação dos números.
[ 6 ] EXCEL AVANÇADO APLICADO AO CONTROLE
As opções existentes no grupo Operação são:
OPÇÃO AÇÃO
Nenhum Cola a opção sem efetuar qualquer operação.
Adicionar Adiciona os valores copiados aos das células de destino.
Subtrair Subtrai os valores de destino das células copiadas.
Multiplicar Multiplica os valores copiados com os de destino.
Dividir Divide os valores da área de colagem pelo copiados.
Na parte inferior da caixa de diálogo Colar Especial ainda se 
encontram as seguintes opções de colagem:
•	 A opção Ignorar em Branco, presente na caixa de diálogo 
Colar Especial, evita a substituição de células sem nenhum 
dado da área de colagem;
•	 para transportar os valores das colunas para as linhas e vice-
versa há a opção Transpor;
•	 por intermédio da opção Colar Vínculo, o Excel 2007 também 
permite fazer com que os dados colados mantenham uma 
interligação com as células copiadas em tempo real para que 
estas sejam atualizadas.
Vamos exercitar um pouco? Vamos lá!
Exercício: Módulo01_Aula02_Exe01
1. Abra o programa Excel 2007;
2. abra o arquivo Mod01_Aula02_Exe01.xlsxpresente na pasta 
Meus Documentos do seu computador;
3. na célula E18 digite o valor 2 e pressione ENTER;
4. volte à célula E18 e pressione as teclas CTRL + C para copiar o 
conteúdo;
5. faça uma seleção das células E2 até E16;
[ 7 ]Unidade 1 - Aula 2 - Ajuste dos Dados
6. agora acesse a opção Colar Especial presente no grupo Área de 
Transferência da guia Início;
7. Selecione a opção Multiplicação e clique no botão OK.
Observe que os valores do intervalo E2:E16 foram multiplicados 
por 2.
Agora vamos diminuir em 35.000,00 todos os valores deste mesmo 
intervalo, utilizando agora a operação Subtração.
8. Substitua o valor 2, na célula E18, pelo valor 35000;
9. após a troca do valor, posicionado na célula E18, pressione as 
teclas CTRL + C;
10. agora selecione o intervalo E2:E16;
11. acesse novamente o recurso Colar Especial, escolha a operação 
Subtração e clique em OK.
[ 8 ] EXCEL AVANÇADO APLICADO AO CONTROLE
Observe que todos os valores do intervalo E2:E16 foram subtraídos 
em 35.000,00.
12. apague o conteúdo da célula E18;
Agora copiaremos o conteúdo de algumas células que possuem 
fórmulas e transformaremos em valores fixos, desvinculando-se assim 
das células de origem.
13. Selecione o intervalo G2:G16 (intervalo esse que possui apenas 
fórmulas);
14. pressione as teclas CTRL + C para copiar o conteúdo para a 
área de transferência;
15. agora clique na célula I2 e, em seguida, pressione simultaneamente as 
teclas CTRL + ALT + V para abrir a caixa de diálogo Colar Especial;
[ 9 ]Unidade 1 - Aula 2 - Ajuste dos Dados
16. selecione a opção Valores e clique no botão OK;
17. clique em qualquer uma das células coladas e observe que o 
valor não é mais resultante de uma fórmula e sim um valor fixo;
Agora vamos executar uma transposição de dados de uma linha 
para uma coluna.
18. Selecione o intervalo A1:G1;
19. pressione as teclas CTRL + C e em seguida clique na célula B18;
20. agora acesse a opção Colar Especial presente no grupo Área de 
Transferência da guia Início;
21. selecione a opção Transpor e observe que os dados copiados 
em linha foram transportados para coluna;
22. salve o arquivo.
É possível tanto transpor 
dados de linha para coluna 
como de coluna para linha, 
bastando para isso escolher 
o mesmo comando Transpor, 
presente na caixa de 
diálogo Colar Especial.
[ 10 ] EXCEL AVANÇADO APLICADO AO CONTROLE
2. Divisão e congelamento de painéis
Em uma planilha grande e complexa, com inúmeras células 
preenchidas com dados, às vezes, navegar por sua área torna-se um 
enorme desafio. E de fato, quando uma planilha é rolada para baixo, os 
rótulos de coluna e linha não permanecem visíveis, fazendo com que a 
referência correta dos dados torne-se um problema.
Para este inconveniente, o Excel 2007 possui dois recursos que 
auxiliam os usuários na navegação pela planilha e no relacionamento 
vertical e horizontal dos dados. São eles:
•	 Dividir painéis: permite a divisão da planilha em quadros 
(painéis) com movimentações independentes, facilitando a 
visualização simultânea de partes distintas da planilha. Podemos, 
por exemplo, visualizar a linha 15 e a linha 2.843 ao mesmo 
tempo na tela.
•	 Congelar painéis: ao usarmos o comando de congelamento de 
painéis do Excel 2007, torna-se possível fazer com que linhas e 
colunas específicas permaneçam visíveis enquanto rolamos pela 
planilha.
Exercício: Módulo01_Aula02_Exe02
1. Abra o programa Excel 2007;
2. faça a importação do arquivo Entidades_MDS(pipe).txt, 
presente na pasta Meus Documentos, utilizando com opção de 
delimitadores o símbolo Pipe (|), conhecido com barra vertical;
3. após a importação dos dados do arquivo (.txt), coloque o cursor 
na célula C17, acesse a guia Exibição e clique na opção Dividir, 
presente no grupo Janela;
Uma planilha pode ser 
dividida em até quatro 
painéis e podem ser 
“congeladas” tanto 
linhas como colunas 
simultaneamente.
Atenção!
[ 11 ]Unidade 1 - Aula 2 - Ajuste dos Dados
4. observe que a planilha foi dividida em quatro painéis a partir 
da posição do cursor;
5. a posição das divisões destes painéis pode ser alterada 
simplesmente apontando o mouse na linha ou coluna divisória 
do painel e arrastando para cima, para baixo, esquerda ou direita;
Estes painéis possuem movimentos independentes, permitindo 
que o usuário clique em qualquer um deles e movimente o cursor com 
o teclado para qualquer direção e assim conseguindo visualizar partes da 
planilha que antes se encontravam distantes. Assim,
6. clique em qualquer um dos painéis e tente executar movimentações 
para uma determinada direção e observe o resultado;
7. para excluir dois dos painéis, clique duas vezes na linha divisória 
(vertical ou horizontal) e assim permanecerão visíveis somente 
dois painéis (ainda com movimentações independentes);
8. clique novamente no botão Dividir para retornar à forma 
tradicional de apresentação da planilha, ou seja, sem a exibição 
dos painéis.
Outro recurso de visualização de planilhas e o congelamento de 
painéis. Vamos executá-lo!
9. Clique na célula B2, na guia Exibição escolha o botão Congelar 
Painéis, em seguida escolha a opção Congelar Painéis;
[ 12 ] EXCEL AVANÇADO APLICADO AO CONTROLE
10. isto faz com que a coluna à esquerda e a linha acima do cursor 
fiquem fixas (é possível ver uma linha fina escura demarcando 
esta área fixa);
11. agora role a tela da planilha tentando ultrapassar o limite 
inferior e o direito e observe que as linhas “congeladas” não 
acompanham o deslocamento imposto;
12. para desativar o congelamento destes painéis, basta clicar 
novamente na opção Congelar Painéis e selecionar a opção 
Descongelar Painéis;
13. pode-se também congelar apenas a linha acima ou a coluna à 
esquerda da posição do cursor. Para isto existe uma opção para 
cada situação;
14. salve o arquivo na pasta Meus Documentos com o nome 
Mod01_Aula02_Exe02.xlsx.
[ 13 ]Unidade 1 - Aula 2 - Ajuste dos Dados
3. Múltiplas planilhas
As pastas de trabalho proporcionam um meio de organizar muitas 
planilhas em um mesmo arquivo. Uma pasta de trabalho é uma coleção 
de várias páginas de planilha que possuem o mesmo número de colunas 
e linhas que a primeira e, opcionalmente, pode-se criar planilhas 
exclusivas para gráfico. Cada página de planilha é uma grade formada 
por colunas e linhas distribuídas na tela de maneira tal que o usuário 
possa relacionar, horizontal e verticalmente, informações 
Cada pasta de trabalho é gravada como se fosse um arquivo, sendo 
que, o nome de arquivo padrão para a primeira pasta de trabalho é 
Pasta1. Há três utilizações principais para fazer uso da pasta de trabalho:
•	 dividir uma planilha grande em partes menores, ou seja, em 
páginas separadas;
•	 reunir dados relacionados logicamente no mesmo arquivo;
•	 consolidar planilhas de formato semelhante em um mesmo 
arquivo.
Divisão de Planilha
Se estiver trabalhando com uma planilha que possua uma grande 
quantidade de dados no Excel 2007, pode-se tornar o trabalho muito 
mais fácil se a planilha for dividida em partes separadas em cada página 
da pasta de trabalho.
Para chegar a uma página específica, deve-se clicar na aba de página 
(isso se torna mais fácil do que movimentar-se entre as diversas partes 
de uma única planilha de tamanho maior), que fica na parte inferior da 
tela. E também, quando se escreve uma fórmula que faz referência às 
células de outra página, o nome da página aparece na fórmula, ficando 
fácil perceber que está sendo realizada uma referência.
Reunião de Dados Relacionados
Em vez de gravar um orçamento, um cronograma, um inventário 
de estoque ou outras informações correlatas em diferentes arquivos do 
disco, pode-se transformá-los em páginas separadas da mesma pasta de 
trabalho.Com isso só será necessário lembrar o nome de um arquivo e 
não de vários.
[ 14 ] EXCEL AVANÇADO APLICADO AO CONTROLE
Consolidação de Dados
Se estiver trabalhando com dados que seguem certo gabarito ou 
apresentação, as pastas de trabalho proporcionam maneiras eficientes 
de digitar e formatar os dados, agrupando as páginas antes de digitar 
informações padrões para títulos de colunas ou, antes de realizar 
mudanças de formato, acelerando assim o seu trabalho.
No Excel 2007, cada planilha tem as configurações independentes, 
ou seja, os ajustes de formatação, configuração de impressão são 
definidos para cada planilha dentro da pasta de trabalho. Isso tem as 
suas vantagens ao permitir que o usuário defina separadamente todas as 
alterações e ajustes feitos em uma determinada planilha sem que afete o 
padrão de outra planilha que não necessariamente requeira das mesmas 
configurações e formatos.
Exercício: Módulo01_Aula02_Exe03
Agora vamos aprender a trabalhar com um grupo de planilhas.
1. Caso o programa Excel 2007 não esteja aberto, abra-o;
2. clique no ícone de inserção de nova planilha (presente após a 
aba Plan3). Assim é criada a planilha Plan4;
3. clique duas vezes na aba da Plan1 (isto faz escurecer sua 
identificação) e digite o termo Janeiro;
4. faça o mesmo com as demais planilhas, fazendo com que 
elas sejam renomeadas para Fevereiro, Março e Total, 
respectivamente;
5. clique com o botão da direita do mouse sobre a aba Total, 
escolha a opção Mover ou Copiar, na caixa de diálogo que 
surge, na área Antes da Planilha, escolha a opção (mover para 
o final) e por fim, ative a caixa de verificação Criar uma Cópia 
e clique em OK;
[ 15 ]Unidade 1 - Aula 2 - Ajuste dos Dados
6. observe que a planilha Total foi duplicada e sua cópia recebeu 
o nome de Total(2), pois o Excel 2007 não aceita planilhas com 
o mesmo nome;
7. para eliminar esta cópia, clique com o botão da direita do mouse 
sobre a aba Total(2), escolha a opção Excluir;
Como assim? O Excel 2007 não pede confirmação?
Não se preocupe, pois apesar de ter eliminado a planilha, o Excel 
2007 inteligentemente sabe que a planilha encontrava-se vazia, mas 
isso não acontece com planilhas que tenham dados. A confirmação de 
exclusão é apresentada. Veja a seguir:
[ 16 ] EXCEL AVANÇADO APLICADO AO CONTROLE
Para colorir as abas das planilhas (ou grupos de planilhas) façamos 
o seguinte:
8. clique na aba Janeiro, mantenha pressionada a tecla SHIFT e 
em seguida dê um clique na aba Março;
9. observe que as três planilhas agora ficaram com as abas na cor 
branca, significando que fazem parte de um grupo;
10. clique com o botão da direita do mouse sobre a aba Janeiro e 
escolha a opção Cor da Guia e escolha a cor laranja;
11. clique na aba Total para desfazer o grupo e observe que as 
planilhas dos meses ficaram com a cor laranja;
Por último, para mover uma planilha:
12. aponte para a aba Total e arraste-a para a esquerda da aba 
Janeiro e ao final solte o botão do mouse.
Pronto, a planilha mudou de posição.
13. as planilhas podem ainda ser ocultadas ou reexibidas mediante 
o clique na opção correspondente no menu das planilhas;
14. feche o arquivo sem salvar.
[ 17 ]Unidade 1 - Aula 2 - Ajuste dos Dados
4. Classificação de dados
Utilizando o Excel 2007 é possível colocar uma lista de nomes de 
pessoas em ordem alfabética, organizar uma lista de níveis de categoria 
de produtos do mais alto para o mais baixo ou até mesmo organizar 
linhas por cores ou ícones. A classificação de dados permite uma melhor 
visualização dos dados e também compreendê-los de modo mais rápido 
e melhor, efetuar uma organização, bem como localizar dados desejados 
de maneira mais clara.
Podemos classificar dados por texto (crescente ou 
descrescentemente), números (dos menores valores para os maiores 
ou vice-versa) e datas e horas (ordem cronológica) em uma ou mais 
colunas. É possivel também classificar dados por uma determinada lista 
ou por formato, incluindo a cor da célula, a cor da fonte ou o conjunto 
de ícones.
IMPORTANTE
Apesar de a maioria das operações de classificação serem executadas por coluna, o Excel 
2007 também aceita que essas sejam executadas por linhas.
Dependendo de qual a célula esteja selecionada atualmente, o 
menu de classificação adapta-se ao conteúdo:
•	 Célula com Texto: classificação alfabética dos dados (Classificar 
de A a Z ou de Z a A).
[ 18 ] EXCEL AVANÇADO APLICADO AO CONTROLE
•	 Célula com Número: classificação numérica dos dados 
(Classificar do Menor para o Maior e do Maior para o Menor).
•	 Célula com Data/Hora: classificação cronológica dos dados 
(Classificar do Mais Antigo para o Mais Novo e do Mais 
Novo para o Mais Antigo).
Caso o usuário tenha formatado um intervalo de células com cor 
da célula ou cor de texto, de forma manual ou por intermédio do recurso 
de Formatação Condicional, este poderá classificar o intervalo pelas 
cores utilizadas, bem como por um conjunto de ícones que o Excel 2007 
disponibiliza para análise de dados.
[ 19 ]Unidade 1 - Aula 2 - Ajuste dos Dados
A seguir, realizaremos um exercício que permitirá utilizar estes 
recursos de classificação de dados na planilha.
Exercício: Módulo01_Aula02_Exe04
Vamos aprender a utilizar alguns recursos de classificação de dados 
em uma planilha específica.
1. Caso o programa Excel 2007 não esteja aberto, abra-o;
2. abra o arquivo Classificacao.xlsx presente na sua pasta Meus 
Documentos;
3. clique na célula C2, em seguida escolha a opção Classificação 
de A a Z presente no botão Classificar e Filtrar da guia Início;
Atenção!
4. observe que a planilha foi classificada crescentemente pela 
coluna C e todos os outros dados (das outras colunas) 
acompanharam esta classificação;
Vamos agora classificar decrescentemente dados de uma planilha 
em que o conteúdo da coluna sejam datas.
5. Clique em qualquer célula pertencente à coluna F (Data de 
Protocolo);
6. selecione o botão Classificar e Filtrar e, em seguida, escolha a 
opção Classificar do Mais Novo para o Mais Antigo;
7. observe que agora a planilha foi classificada cronologicamente 
de forma decrescente;
[ 20 ] EXCEL AVANÇADO APLICADO AO CONTROLE
E quando a classificação desejada não é alfabética, cronológica 
ou numérica? Podemos realizar uma classificação pela formatação da 
planilha? A resposta é Sim.
Vamos realizá-la!
8. Observe que, propositalmente, a planilha possui na coluna 
E (Processo), os textos das células formatados em cores 
diferentes;
9. clique então em qualquer célula desta coluna, clique no 
botão Classificar e Agrupar e escolha a opção Personalizar 
Classificação;
10. na caixa Coluna escolha a coluna Processo, na caixa Classificar 
Em selecione a opção Cor da Fonte e na caixa Ordem escolha a 
cor vermelha (Na parte superior);
11. clique no botão Adicionar Nível e nele, escolha as mesmas 
opções acima, porém a cor verde;
12. agora adicione um novo nível e para ele escolha também as 
mesmas opções, porém a cor azul;
[ 21 ]Unidade 1 - Aula 2 - Ajuste dos Dados
13. ao final clique em OK;
14. observe que dessa vez a classificação acompanhou não o 
conteúdo das células e sim a formatação delas, ou seja, 
primeiramente as células que estão em vermelho, em seguidas 
as, em verde e por último as, em azul;
15. salve o arquivo.
Desafio: E agora, como 
podemos classificar a 
planilha crescentemente 
pelos números dos 
processos (coluna E) dentro 
de cada grupo de cores? 
Tente realizar esta tarefa.
[ 22 ] EXCEL AVANÇADO APLICADO AO CONTROLE
Síntese
Nesta aula, conhecemos o recurso de colagem especial que permite 
agilizar determinadas tarefas nas planilhas, fornecendo métodos que 
auxiliem na cópia de elementos, sejam eles na mesma planilha ou em 
outra qualquer.
Também executamosos recursos de divisão e congelamento de 
painéis que trazem um desempenho maior na hora de verificar dados 
em planilhas de tamanhos consideráveis.
Por último, vimos que o Excel 2007 fornece recursos para 
se trabalhar com múltiplas planilhas e, também, permite executar 
procedimentos diversos de classificação de dados.
[ 23 ]Unidade 1 - Aula 2 - Ajuste dos Dados
Referências bibliográficas 
Express Training. Dicas para trabalhar com grupo de planilhas. Disponível 
em: <http://expresstraining.com.br/index.php?page=article&id=314>. 
Acesso em 24 de fevereiro de 2012.
Microsoft. Serviço de Suporte ao Excel 2007. Importar ou exportar 
arquivos de texto. Disponível em: <http://office.microsoft.com/
pt-br/excel-help/classificar-dados-em-um-intervalo-ou-tabela-
HP010073947.aspx?CTT=3>. Acesso em 23 de fevereiro de 2012.
Tecmundo. Descubra e aprenda tudo sobre tecnologia. Disponível em: 
<http://www.tecmundo.com.br/789-excel-colagem-especial-aprenda-a-
usar.htm>. Acesso em 23 de fevereiro de 2012.
Aula 1 
Operações com Funções
EXCEL AVANÇADO APLICADO 
AO CONTROLE
Unidade 2 
Normalização dos Dados
Instituto Serzedello Corrêa
RESPONSABILIDADE PELO CONTEÚDO
Tribunal de Contas da União
Secretaria Geral da Presidência
Instituto Serzedello Corrêa
2ª Diretoria de Desenvolvimento de Competências 
Serviço de Planejamento e Projetos Educacionais
SUPERVISÃO
Pedro Koshino
CONTEUDISTA
Helbert de Sousa Arruda
TRATAMENTO PEDAGÓGICO
Flávio Sposto Pompeo
RESPONSABILIDADE EDITORIAL
Tribunal de Contas da União
Secretaria Geral da Presidência
Instituto Serzedello Corrêa
Centro de Documentação
Editora do TCU
PROJETO GRÁFICO
Ismael Soares Miguel
Paulo Prudêncio Soares Brandão Filho
Vivian Campelo Fernandes
DIAGRAMAÇÃO
Vanessa Vieira
Brasil. Tribunal de Contas da União.
Excel Avançado Aplicado ao Controle / Tribunal de Contas da União ; 
conteudista: Helbert de Sousa Arruda. – Brasília : TCU, Instituto Serzedello 
Corrêa, 2012.
19 p. : il.
Conteúdo: Unidade 2: Normalização dos dados. Aula 1: Operações com 
funções.
Curso realizado no período de 22/05 a 19/06/2012 no Ambiente
Virtual de Educação Corporativa do Tribunal de Contas da União.
1. Importação de dados. 2. Normalização de dados. 3. Manipulação de 
dados. 4. Análise de dados. I. Título.
Ficha catalográfica elaborada pela Biblioteca Ministro Ruben Rosa
© Copyright 2012, Tribunal de Contas de União 
<www.tcu.gov.br>
Permite-se a reprodução desta publicação, 
em parte ou no todo, sem alteração do conteúdo, 
desde que citada a fonte e sem fins comerciais.
[ 3 ]Unidade 2 - Aula 1 - Operações com Funções
Aula 1 – Operações com funções
Para que servem as funções?
Como é a sintaxe das funções?
Como utilizar funções de ajuste de números e textos?
Qual a utilidade das operações Localizar/Substituir?
Iniciamos nossos estudos pela apresentação das características 
básicas das funções do Excel 2007 e a demonstração de algumas delas. 
Para complementar este estudo, mostraremos como utilizar de maneira 
eficaz os recursos de localização e substituição de dados na planilha por 
meio dos comandos próprios para esta tarefa.
Para facilitar o estudo, esta aula está organizada da seguinte forma:
Ao final desta aula, esperamos que você tenha condições de:
•	 conhecer a sintaxe e as características das funções;
•	 conhecer e utilizar algumas funções de ajustes de números;
•	 conhecer e utilizar algumas funções de manipulação de texto;
•	 executar o processo de localização e substituição automática de 
dados na planilha.
Pronto para começar? Então, vamos!
Aula 1 – Operações com funções �����������������������������������������������������������������������������3
1. Utilização de funções ����������������������������������������������������������������������������������������������4
2. Funções de ajuste de números �����������������������������������������������������������������������������7
3. Funções de manipulação de texto ���������������������������������������������������������������������9
4. Localizar e substituir dados ���������������������������������������������������������������������������� 13
Síntese �������������������������������������������������������������������������������������������������������������������������������� 18
Referências bibliográficas ����������������������������������������������������������������������������������� 19
[ 4 ] EXCEL AVANÇADO APLICADO AO CONTROLE
1. Utilização de funções
Uma função é uma fórmula especial predefinida, que toma um 
valor ou valores, executa uma operação e produz um valor ou valores. As 
funções podem ser usadas isoladamente ou como bloco de construção 
de outras fórmulas. O uso de funções simplifica e reproduz as fórmulas 
em planilhas, especialmente aquelas que realizam cálculos extensos e 
complexos. Por exemplo, em vez de digitar a fórmula =A1+A2+A3+A4, 
pode-se usar uma função para calcular o intervalo entre as células A1 e 
A4.
As funções predefinidas de planilhas do Excel 2007 servem para 
executar cálculos padronizados de planilha. Os valores fornecidos a 
uma função para executar operações são denominados argumentos da 
função, os valores que a função produz são chamados de resultados e a 
sequência de caracteres utilizados denomina-se sintaxe, sendo que todas 
as funções possuem a mesma sintaxe básica.
Sintaxe: =NomeDaFunção(Argumentos)
•	 Nome Da Função: cada função do Excel possui um nome 
específico, podendo ser digitado em letras maiúsculas ou 
minúsculas.
•	 Argumentos: os argumentos das funções são os dados inseridos 
para serem trabalhados pela função. Estes argumentos podem 
ser números, datas, textos, fórmulas, células, intervalos etc.
As funções estão divididas nas seguintes categorias:
CATEGORIA DESCRIÇÃO
Cubo Possui funções que manipulam dados de procedimentos 
armazenados em banco de dados existente.
Banco de Dados
Possui funções que auxiliam na manipulação de dados 
de uma base existente na planilha, permitindo realizar 
análises em registros, tais como média, desvio padrão e 
outros.
Engenharia Possui funções que realizam conversões e cálculos com números decimais, hexadecimais e logaritmos.
Financeiras
Possui funções que manipulam taxas, valores de 
depreciação, pagamento e outras funções similares à 
calculadora científica e financeira.
[ 5 ]Unidade 2 - Aula 1 - Operações com Funções
Informação
Possui funções que manipulam e retornam expressões de 
informações referentes aos dados manipulados em uma ou 
mais células, como tipo de erro, conteúdo e tipo de célula.
Lógicas Possui funções que retornam valores lógicos ou booleanos 
(verdadeiro/falso) e outros tipos.
Procura e Referência Possui funções que permitem pesquisar dados e referências em um conjunto de células ou listas.
Matemáticas e 
Trigonométricas
Possui funções que permitem arredondar valores exibidos 
em cálculos, seno e cossenos e cálculo raiz quadrada.
Estatísticas
Possui funções que permitem realizar análise de dados, 
como médias, desvios, variância, etc.
Texto
Possui funções que permitem manipular células com 
conteúdo de texto, tais como procurar a substituição de 
dados por outro e conversões para maiúsculas, minúsculas 
e valores.
Data e Hora
Possui funções que manipulam datas e horas, realizando 
conversões e localização de ano, mês, dia da semana e 
minutos.
Suplemento e Automação Possui funções que visam atualizar vínculos, bibliotecas e procedimentos de consulta a banco de dados.
O Excel 2007 possui um recurso conhecido como Assistente de 
Função. Este recurso permite que seja localizada e inserida uma função 
selecionada na caixa de diálogo Inserir Função que aparece assim que é 
selecionado o botão. Nesta caixa, são listadas todas as funções do Excel 
2007 com os respectivos argumentos, exemplos, categorias, sintaxes, etc.Para acessar este recurso, basta clicar sobre o botão Inserir Função, 
presente na guia Fórmula. Desta maneira, surge na tela a caixa de diálogo 
chamada Inserir Função, como é apresentada na figura a seguir:
[ 6 ] EXCEL AVANÇADO APLICADO AO CONTROLE
•	 Selecione uma categoria: permite a seleção de subconjuntos das 
funções disponíveis conforme a tabela exibida anteriormente. Se 
for selecionada a categoria Todas nesta caixa, todas as funções 
disponíveis serão listadas e se for selecionada qualquer outra 
categoria, apenas as funções que pertençam a ela serão listadas.
•	 Selecione uma função: lista todas as funções predefinidas e 
personalizadas disponíveis na categoria selecionada e também 
as funções de macro predefinidas e personalizadas se uma folha 
de macro estiver ativa.
Vamos conhecer na prática o uso de algumas funções básicas e o 
uso do Assistente!? Vamos lá!
Exercício: Módulo02_Aula01_Exe01
1. Abra o programa Excel 2007;
2. importe o arquivo Convenios_Funcoes(csv).csv para um novo 
arquivo a partir da célula A1;
[ 7 ]Unidade 2 - Aula 1 - Operações com Funções
3. coloque o cursor na célula F22 e digite a função =SOMA(F2:F21) 
para obter o somatório de toda a coluna F;
4. coloque o cursor na célula F23 e digite a função =MÉDIA(F2:F21) 
para obter a média aritmética dos dados da coluna F;
5. respectivamente, digite nas células F24 e F25, digite as funções 
=MÍNIMO(F2:F21) e =MÁXIMO(F2:F21) para obter o menor 
e maior presente no intervalo;
6. para saber a quantidade de elementos presente no intervalo, 
digite a função =CONT.VALORES(F2:F21) na célula F26;
7. na célula D22 digite a função =HOJE() para inserir a data do 
computador na respectiva célula;
8. na célula D23 digite a função =AGORA(). Esta função insere 
além da data do computador também a hora;
Essas são algumas das funções básicas existentes no Excel 2007. Ao 
longo das demais aulas, iremos verificar outras funções com aplicações 
específicas.
9. Salve o arquivo com o nome Mod02_Aula01_Exe01.xlsx na sua 
pasta Meus Documentos.
2. Funções de ajuste de números
O Excel 2007 possui algumas funções que têm como objetivo o ajuste 
de números inseridos como conteúdos de células. Conhecer estas funções 
torna-se interessante, pois os dados numéricos importados de outros 
formatos de arquivos precisam eventualmente ser adaptados ao contexto 
atual de uma determinada planilha.
As funções de ajuste de números são
=ABS()
Esta função retorna o valor absoluto de um número. O valor absoluto 
de um número é o próprio número sem o respectivo sinal.
Sintaxe: =ABS(número)
=ARRED()
[ 8 ] EXCEL AVANÇADO APLICADO AO CONTROLE
Esta função tem como objetivo arredondar um determinado número 
até uma quantidade especificada de dígitos.
Sintaxe: =ARRED(número; número_dígitos)
=INT()
Esta função executa a extração da parte inteira de um número 
fracionário qualquer.
Sintaxe: =INT(número_fracionário)
=TRUNCAR()
Esta função trunca um número para um inteiro removendo a parte 
fracionária de um número.
Sintaxe: =TRUNCAR(número_fracionário; núm_dígitos)
Agora, vamos conhecer na prática o uso dessas funções acima? 
Vamos lá!
Exercício: Módulo02_Aula01_Exe02
1. Abra o programa Excel 2007;
2. na célula B2 digite o valor 20 e na célula B3 digite o valor 30;
3. na célula B4 digite a fórmula =B2-B3. Observe que o resultado 
é -10;
Quando o sinal negativo não houver importância para o resultado 
do cálculo, ou seja, quando este puder ser desprezado, você deverá usar a 
função =ABS() para esta tarefa. Vamos então utilizá-la!
4. Posicione o cursor na célula B4 novamente, pressione a tecla F2 
para editar a fórmula e altere-a para =ABS(B2-B3) e observe que 
o resultado não mais apresenta o sinal negativo;
5. digite o valor 345,327468 na célula C2;
6. na célula C3 digite a função =ARRED(C2;4) e observe que o 
resultado desta expressão retornou o valor 345,3275, pois foi 
Atenção!
[ 9 ]Unidade 2 - Aula 1 - Operações com Funções
requisitado o arredondamento na quarta casa decimal e, como 
o valor da quinta casa decimal é 6, o valor foi arredondado para 
cima;
7. altere o valor digitado na célula C2 na quinta casa decimal para 
4, ou seja, o valor agora ficará 345,327448;
Observe que o valor obtido pela função (célula C3) preservou a 
quarta casa decimal, ou seja, ficou em 345,3274. Isso significa que valores 
de 0 a 4 a função preserva-o e de 5 a 9 a função arredonda-o para cima.
8. Na célula C4 digite a função =INT(C2) para que o Excel 
2007 assuma como resultado apenas a parte inteira do valor, 
desprezando toda a parte decimal;
9. deixe a célula C2 novamente com o valor 345,327468;
10. digite, na célula D3, a função =TRUNCAR(C2;4) e observe que 
esta função apenas “corta” o número na posição indicada não 
levando em consideração o número posterior;
11. comparando as funções ARRED e TRUNCAR, a primeira 
retorna o valor 345,3275 e a segunda 345,3274;
12. assim verificamos o funcionamento de algumas funções de ajuste 
de números;
13. salve o arquivo com o nome Mod02_Aula01_Exe02.xlsx na pasta 
Meus Documentos.
3. Funções de manipulação de texto
O Excel 2007 possui também algumas funções que permitem o 
ajuste e manipulação de conteúdos em formato texto. Dessa maneira, 
torna-se possível tratar células que possuam texto, aplicando-se recursos 
de conversão de letra, extração de partes do texto etc.
As funções de ajustes e manipulação de textos são
=TEXTO()
Esta função permite converter um valor para texto em um formato 
de número específico.
[ 10 ] EXCEL AVANÇADO APLICADO AO CONTROLE
Sintaxe: =TEXTO(valor;formato_texto)
Alguns formatos:
CARACTERES RESULTADO
dd Exibe o dia como um número com zero à esquerda quando apropriado.
ddd Exibe o dia como uma abreviação (Dom a Sáb).
dddd Exibe o dia como um nome completo (Domingo a Sábado).
mm Exibe o mês como um número com zero à esquerda quando apropriado.
mmmm Exibe o mês como um nome completo (Janeiro a Dezembro).
aa Exibe o ano como um número de dois dígitos.
aaaa Exibe o ano como um número de quatro dígitos.
hh
Exibe a hora como um número com zero à esquerda quando 
apropriado. Se o formato contiver AM ou PM, a hora será 
indicada com base no relógio de 12 horas. Caso contrário, a 
hora será indicada com base no relógio de 24 horas.
ss
Exibe o segundo como um número com zero à esquerda 
quando apropriado. Se você deseja exibir frações de 
segundo, use um formato de número que se assemelhe a 
h:mm:ss.00.
=DIREITA()
Esta função retorna o último caractere ou caracteres em uma 
sequência de caracteres de texto com base no número de caracteres 
especificado pelo usuário.
Sintaxe: =DIREITA(texto;núm_caracteres)
=ESQUERDA()
Esta função, similar à função =Direita(), retorna o primeiro 
caractere ou caracteres em uma sequência de caracteres de texto baseado 
no número de caracteres especificado pelo usuário.
Sintaxe: =ESQUERDA(texto;núm_caracteres)
=EXT.TEXTO()
Retorna um número específico de caracteres da sequência de 
caracteres texto, começando na posição especificada, com base no 
número de caracteres especificado.
[ 11 ]Unidade 2 - Aula 1 - Operações com Funções
Sintaxe: =EXT.TEXTO(texto;pos_inicial;núm_caracteres)
Agora vamos conhecer na prática o uso das funções acima? Vamos lá!
Exercício: Módulo02_Aula01_Exe03
Agora vamos aprender a trabalhar com um grupo de planilhas.
1. Caso o programa Excel 2007 não esteja aberto, abra-o;
2. abra o arquivo Extracoes.xlsx presente na pasta Meus 
Documentos do seu computador;
Observe que esta planilha possui vários dados truncados presentes 
na coluna A. Vamos utilizar algumas funções apresentadas há pouco 
para extrair dados.
3. Posicione o cursor na célula C2 e digite a função 
=ESQUERDA(A2;10), pressione ENTER e observe que a data 
presentena parte esquerda da célula A2 foi toda extraída para 
esta célula;
4. volte à célula C2 e copie o conteúdo da célula até a célula C101;
5. agora examinaremos o dia da semana de cada data presente na 
coluna D;
6. posicione o cursor na célula D2, digite a função 
=TEXTO(C2;”dddd”), pressione ENTER e observe que a célula 
agora exibe o dia da semana referente à data da célula D2;
7. copie a célula D2 para as demais células desta coluna até a 
célula D101;
[ 12 ] EXCEL AVANÇADO APLICADO AO CONTROLE
Agora vamos extrair os números de CNPJ embutidos nos dados 
presentes na coluna A.
8. Posicione o cursor na célula E2, digite a função 
=DIREITA(A2;18), pressione ENTER e observe que o número 
do CNPJ da primeira célula da coluna A foi extraído do seu 
conteúdo;
9. agora copie o conteúdo desta célula até a célula E101;
Observe que agora em todas as células da coluna E encontram-se 
os CNPJ de forma separada dos dados da coluna A. Mas poderíamos 
retirar desse número, os caracteres ponto, barra e traço? Vamos realizar 
esta tarefa?
10. posicione o cursor na célula F2 e digite a seguinte combinação 
de funções:
=ESQUERDA(E2;2) & EXT.TEXTO(E2;4;3) & EXT.
TEXTO(E2;8;3) & EXT.TEXTO(E2;12;4) & DIREITA(E2;2)
11. pressione ENTER e observe que na célula F2 agora temos o 
número do primeiro CNPJ sem os respectivos caracteres, ou 
seja, preservamos somente os números;
12. agora copie o conteúdo desta célula até a célula F101;
13. agora para desvincularmos estes conteúdos obtidos com as 
[ 13 ]Unidade 2 - Aula 1 - Operações com Funções
funções da coluna A, executaremos o processo de cópia e, em 
seguida, a colagem especial (escolhendo a opção Valores).
14. salve o arquivo.
4. Localizar e substituir dados
As planilhas do Excel 2007 podem conter mais de um milhão de 
linhas de dados, portanto é pouco provável que tenhamos tempo para 
percorrer uma planilha inteira linha por linha para localizar um dado.
Podemos localizar dados específicos em uma planilha do Excel 
2007 utilizando a caixa de diálogo Localizar e Substituir, que tem 
duas guias(uma denominada Localizar; a outra, Substituir) as quais 
permitem procurar células com determinados valores.
A guia Localizar procura dados que especificamos; a guia 
Substituir substitui um determinado valor por outro.
IMPORTANTE
O botão Opções, presente na caixa de diálogo Localizar e Substituir, expande a quantidade 
de opções de pesquisa que podem ser utilizadas nas localizações.
O botão Opções abre novos itens de localização, a saber:
•	 Para procurar dados em uma planilha ou em uma pasta de 
trabalho inteira, na caixa devemos selecionar Planilha ou Pasta 
de Trabalho.
•	 Para procurar dados em linhas ou colunas específicas, na caixa 
Pesquisar, devemos clicar em Por Linhas ou Por Colunas.
[ 14 ] EXCEL AVANÇADO APLICADO AO CONTROLE
Atenção!
•	 Para procurar dados com detalhes específicos, na caixa 
Examinar, devemos clicar em Fórmulas, Valores ou 
Comentários.
•	 Para procurar dados que diferenciam maiúsculas de minúsculas, 
devemos marcar a caixa de seleção Diferenciar maiúsculas de 
minúsculas.
•	 Para procurar células que contenham apenas os caracteres que 
digitados na caixa Localizar, devemos marcar a caixa de seleção 
Coincidir conteúdo da célula inteira.
A seguir, realizaremos um exercício que permitirá utilizar esses 
recursos de localização e substituição de dados na planilha.
Exercício: Módulo02_Aula01_Exe04
Aprenderemos a utilizar os dois recursos em uma determinada 
planilha que possua muitos dados.
1. Caso o programa Excel 2007 não esteja aberto, abra-o;
2. abra o arquivo Loc_Sub.xlsx presente na pasta Meus 
Documentos;
3. execute os devidos procedimentos (mostrados no capítulo 
anterior) para extrair de todas as células da coluna A, o termo 
“RESPONSÁVEL:” à esquerda e “TRÂNSITO EM JULGADO” 
à direita;
[ 15 ]Unidade 2 - Aula 1 - Operações com Funções
4. após esta “limpeza” dos dados, posicione o cursor na célula A1 
e pressione as teclas CTRL + L para abrir a caixa de diálogo 
Localizar e Substituir;
5. vamos encontrar todas as ocorrências do sobrenome “Silva” 
presente nesta planilha, para isso, devemos digitar o termo 
Silva na caixa Localizar e pressionar o botão Localizar Tudo;
6. observe que o Excel 2007 localizou todas as ocorrências e listou-
as na parte de baixo da caixa de diálogo;
7. para localizar a próxima ocorrência basta clicar no botão 
Localizar Próximo e assim sucessivamente;
8. caso queira “pular” em uma ocorrência específica, basta clicar 
na respectiva linha da lista;
9. clique no botão Fechar.
Caso desejássemos efetuar a transposição dos dados da coluna A 
para as demais colunas com o comando Texto para Colunas, teríamos 
um pequeno inconveniente, pois deveríamos utilizar como delimitador 
o Espaço (que separa os três dados: Nome, CPF e Processo), porém os 
nomes das pessoas também possuem espaço. Observe o que aconteceria:
[ 16 ] EXCEL AVANÇADO APLICADO AO CONTROLE
Como proceder então, para que isto não ocorra? Vamos lá!
10. Posicione o cursor na célula A1, abra o recurso de Localizar e 
Substituir, e então altere para a guia Substituir;
11. nas caixas abaixo digite as seguintes informações:
•	 Localizar: “CPF”.
•	 Substituir por: ”;”.
12. clique no botão Substituir Tudo e observe que todas as palavras 
“CPF:” foram trocadas por ponto-e-vírgula;
13. faça o mesmo trocando as palavras “PROCESSO:” também por 
ponto-e-vírgula;
Agora você pode utilizar o comando Texto para Colunas 
escolhendo o símbolo ponto-e-vírgula como sendo o delimitador das 
colunas. Faça isso!
[ 17 ]Unidade 2 - Aula 1 - Operações com Funções
Observe que ao final a planilha deverá ficar como abaixo:
14. salve o arquivo..
[ 18 ] EXCEL AVANÇADO APLICADO AO CONTROLE
Síntese
Nesta aula, conhecemos a definição, características e sintaxe das 
funções do Excel 2007 e percebemos as vantagens proporcionadas pelo 
uso deste recurso.
Também aprofundamos o conhecimento em dois grupos de funções 
do Excel 2007, ou seja, nas funções de ajuste de números e manipulação 
de texto.
E por fim, adquirimos experiência na utilização dos recursos 
de localizar e substituir, fazendo com que operações complexas de 
localização de dados em uma planilha fossem realizadas com extrema 
facilidade.
[ 19 ]Unidade 2 - Aula 1 - Operações com Funções
Referências bibliográficas 
ComputerDicas. Categorias de funções no Excel. Disponível em: 
<http://www.computerdicas.com.br/2011/05/categorias-de-funcoes-
no-excel-2010.html>. Acesso em 27 de fevereiro de 2012.
COX, F.; FRYE C.; LAMBERT, M.D; PREPPERNEAU, J.; MURRAY, K. 
Microsoft Office System 2007 – Passo a passo. Porto Alegre: Artmed, 
2008.
Microsoft. Serviço de Suporte ao Excel 2007. Localizar ou substituir texto 
ou números em uma planilha. Disponível em: <http://office.microsoft.
com/pt-br/excel-help/localizar-ou-substituir-texto-ou-numeros-em-
uma-planilha-HP001216390.aspx>. Acesso em 27 de fevereiro de 2012.
Aula 2 
Transposições e Operações Diversas
EXCEL AVANÇADO APLICADO 
AO CONTROLE
Unidade 2 
Normalização dos Dados
Instituto Serzedello Corrêa
RESPONSABILIDADE PELO CONTEÚDO
Tribunal de Contas da União
Secretaria Geral da Presidência
Instituto Serzedello Corrêa
2ª Diretoria de Desenvolvimento de Competências 
Serviço de Planejamento e Projetos Educacionais
SUPERVISÃO
Pedro Koshino
CONTEUDISTA
Helbert de Sousa Arruda
TRATAMENTO PEDAGÓGICO
Flávio Sposto Pompeo
RESPONSABILIDADE EDITORIAL
Tribunal de Contas da União
Secretaria Geral da Presidência
Instituto Serzedello Corrêa
Centro de Documentação
Editora do TCU
PROJETO GRÁFICO
Ismael Soares Miguel
Paulo Prudêncio Soares Brandão Filho
Vivian Campelo Fernandes
DIAGRAMAÇÃO
Vanessa Vieira
Brasil. Tribunal de Contas da União.
Excel Avançado Aplicadoao Controle / Tribunal de Contas da União ; 
conteudista: Helbert de Sousa Arruda. – Brasília : TCU, Instituto Serzedello 
Corrêa, 2012.
24 p. : il.
Conteúdo: Unidade 2: Normalização dos dados. Aula 2: Transposições e 
operações diversas.
Curso realizado no período de 22/05 a 19/06/2012 no Ambiente
Virtual de Educação Corporativa do Tribunal de Contas da União.
1. Importação de dados. 2. Normalização de dados. 3. Manipulação de 
dados. 4. Análise de dados. I. Título.
Ficha catalográfica elaborada pela Biblioteca Ministro Ruben Rosa
© Copyright 2012, Tribunal de Contas de União 
<www.tcu.gov.br>
Permite-se a reprodução desta publicação, 
em parte ou no todo, sem alteração do conteúdo, 
desde que citada a fonte e sem fins comerciais.
[ 3 ]Unidade 2 - Aula 2 - Transposições e Operações Diversas
Aula 2 – Transposições e operações diversas
Como executar as operações de transposição de dados?
 Como utilizar funções de manipulação de data?
Como funciona a formatação de números?
Como proceder para remover dados duplicados na planilha?
Iniciamos nossos estudos pela apresentação das operações de 
transposição de dados na planilha. Também serão exibidas algumas 
funções de manipulação de elementos de data, suas características e 
sintaxes, bem como as opções de formatação de números para auxiliar 
na forma de apresentação deste tipo de dado. E para finalizar este estudo, 
mostraremos como executar o procedimento de eliminação de dados 
duplicados por intermédio de comando existente no Excel 2007.
Para facilitar o estudo, esta aula está organizada da seguinte forma:
Ao final desta aula, esperamos que você tenha condições de:
•	 utilizar os recursos de transposição de dados na planilha;
•	 conhecer a sintaxe e as características de algumas das funções 
da categoria Data/Hora;
•	 conhecer e utilizar as formatações de números;
•	 executar o processo de remoção automática de dados duplicados 
na planilha.
Pronto para começar? Então, vamos!
Aula 2 – Transposições e operações diversas �������������������������������������������������3
1. Transposição de dados���������������������������������������������������������������������������������������������4
2. Funções de manipulação de data ��������������������������������������������������������������������� 11
3. Formatação de números ��������������������������������������������������������������������������������������� 15
4. Remoção automática de dados duplicados ����������������������������������������������� 20
Síntese �������������������������������������������������������������������������������������������������������������������������������� 23
Referências bibliográficas ����������������������������������������������������������������������������������� 24
[ 4 ] EXCEL AVANÇADO APLICADO AO CONTROLE
1. Transposição de dados
Caso os dados de uma determinada planilha estejam inseridos em 
colunas ou linhas e desejarmos reorganizá-los em linhas ou colunas, 
poderemos transpor rápida e automaticamente esses dados de uma 
situação para outra.
Outra operação permite separar diversos dados presentes em 
uma determinada célula, em células separadas para que eventualmente 
sofram operações de forma individual.
Transpor
Esta operação pode ser realizada tanto pela utilização da opção de 
colagem, denominada Transpor (observe a figura), presente na caixa de 
diálogo Colar Especial, como também pela função matricial chamada 
=TRANSPOR(matriz).
Como podemos verificar, a caixa de diálogo Colar especial possui 
um item denominado Transpor.
Se a função =TRANSPOR() 
não for inserida como 
uma função de matriz, 
a fórmula terá um 
resultado inesperado. 
Para que a função seja 
inserida como função de 
matriz, deve-se após a 
digitação, pressionar F2 
e, em seguida, pressionar 
simultaneamente as teclas 
CTRL+SHIFT+ENTER.
Para exemplificar estas duas operações (com a opção e com a 
função), execute os próximos exercícios propostos.
Vamos iniciar!
[ 5 ]Unidade 2 - Aula 2 - Transposições e Operações Diversas
Exercício: Módulo02_Aula02_Exe01
1. Abra o programa Excel 2007;
2. abra o arquivo Mod02_Aula02_Exe01.xls presente na pasta 
Meus Documentos do seu computador;
3. observe que este arquivo possui uma planilha com 4 linhas e 5 
colunas, no qual os meses estão dispostos nas linhas e os anos 
nas colunas;
4. selecione o intervalo B2:F5 e em seguida pressione as teclas 
CTRL + C para copiar os dados para a área de transferência;
5. posicione o cursor na célula B8;
6. agora acesse a caixa de diálogo Colar Especial e escolha o 
comando Transpor;
7. clique no botão OK;
8. observe que os dados copiados foram alocados a partir da célula 
B8 e as referências invertidas de linha para coluna e vice-versa;
[ 6 ] EXCEL AVANÇADO APLICADO AO CONTROLE
9. assim sendo, a planilha de baixo ficou com cinco linhas e quatro 
colunas (ao contrário da planilha original);
10. salve o arquivo.
Agora executaremos a mesma operação de transposição, porém 
utilizando a função =TRANSPOR() que tem uma matriz como único 
argumento.
11. No mesmo arquivo, passe para a Plan2 e observe que temos 
novamente a planilha original (com 4 linhas e 5 colunas);
12. posicione o cursor na célula B8 desta Plan2, selecione até a 
célula E12;
13. digite a seguinte função: =TRANSPOR(B2:F5)
14. pressione simultaneamente as teclas CTRL + SHIFT + ENTER 
para que esta função seja inserida como uma matriz;
[ 7 ]Unidade 2 - Aula 2 - Transposições e Operações Diversas
15. pronto, executamos o mesmo procedimento anterior, mas dessa 
vez utilizamos uma função e não o comando Transpor;
16. salve a planilha.
Viu como estas duas operações são fáceis!
Texto para colunas
Agora falaremos sobre outro comando que realiza a transposição 
de dados, mas dessa vez o processo afeta o conteúdo presente em uma 
célula específica.
Podemos utilizar o Assistente de Conversão de Texto para 
Colunas, presente na guia Dados, a fim de separar simples conteúdos 
das células, dependendo dos dados, é possível dividir o conteúdo das 
células com base num delimitador, como, por exemplo, um espaço ou 
uma vírgula, ou com base numa localização específica de quebra de 
coluna nos dados em questão.
Agora vamos conhecer na prática o uso dessa opção? Vamos lá!
Exercício: Módulo02_Aula02_Exe02
1. Abra o programa Excel 2007;
2. abra o arquivo Sancionadas(xlsx).xlsx presente na pasta Meus 
Documentos do seu computador;
3. observe que existem vários dados truncados (separados por 
espaço) na coluna A;
Atenção!
[ 8 ] EXCEL AVANÇADO APLICADO AO CONTROLE
Vamos então executar o procedimento de desmembramento destes 
dados de uma única coluna para várias. Para isso devemos executar o 
comando Texto para Colunas.
4. selecione toda a coluna A dando um clique no título desta coluna;
5. acesse a guia Dados, e no grupo Ferramentas de Dados, dê um 
clique no botão Texto para Colunas;
6. surge na tela o Assistente para Conversão de Texto em Colunas;
[ 9 ]Unidade 2 - Aula 2 - Transposições e Operações Diversas
7. alterne a opção Tipo de Dados Originais para Delimitado e 
clique em Avançar;
8. desative a opção Tabulação e ative a opção Espaço. Clique em 
Avançar;
[ 10 ] EXCEL AVANÇADO APLICADO AO CONTROLE
9. para cada coluna de dados defina o tipo de dados da coluna. Os 
tipos são os seguintes:
•	 DataInicSancao: Data (DMA).
•	 DataFinalSancao: Data (DMA).
•	 TipoSancao: Texto.
•	 UF: Texto.
•	 CPF_CNPJ: Texto.
10. ao final clique no botão Concluir;
11. observe que os dados da coluna A foram desmembrados em 
outras colunas;
12. faça o ajuste de todas as colunas e observe o resultado;
[ 11 ]Unidade 2 - Aula 2 - Transposições e Operações Diversas
13. salve o arquivo.
2. Funções de manipulação de data
O Excel 2007possui algumas funções que têm como objetivo a 
manipulação de elementos que compõem datas, como por exemplo, 
dia, mês e ano. Conhecer essas funções permite ao usuário formular 
contextos que trabalhem com data e assim definir uma melhoria no 
aspecto dos trabalhos que envolvam elementos de ordem cronológica.
[ 12 ] EXCEL AVANÇADO APLICADO AO CONTROLE
As funções de manipulação de data são
=DIA()
Essa função retorna o dia de uma data representado por um número 
de série. O dia é dado como um inteiro que varia de 1 a 31.
Sintaxe: =DIA(núm_série)
=MÊS()
Essa função retorna o mês de uma data representado por um 
número de série. O mês é fornecido como um inteiro, variando de 1 
(janeiro) a 12 (dezembro).
Sintaxe: =MÊS(núm_série)
=ANO()
Essa função retorna o ano correspondente a uma data. O ano é 
retornado como um inteiro no intervalo de 1900-9999.
Sintaxe: =ANO(núm_série)
=DIATRABALHO()
Esta função retorna um número que representa uma data que é o 
número indicado de dias úteis antes ou após uma data (a data inicial). 
Os dias úteis excluem fins de semana e quaisquer datas identificadas 
como feriados. Use a função DIATRABALHO() para excluir os fins de 
semana ou feriados ao calcular as datas de vencimento de fatura, horas 
de entrega esperadas ou o número de dias de trabalho executado.
Se essa função não estiver disponível e retornar o erro #NOME?, 
instale e carregue o suplemento Ferramentas de Análise.
Sintaxe: =DIATRABALHO (data_inicial; dias; [feriados])
Agora vamos conhecer na prática o uso dessas funções acima? 
Vamos lá!
[ 13 ]Unidade 2 - Aula 2 - Transposições e Operações Diversas
Exercício: Módulo02_Aula02_Exe03
1. Abra o programa Excel 2007;
2. abra o arquivo de planilha salvo no exercício anterior chamado 
Sancionadas(xlsx).xlsx;
3. agora vamos separar os elementos de data da coluna 
DataInicSancao em colunas diferentes;
4. digite na célula G1 a palavra Dia, na célula H1 a palavra Mês e 
na célula I2 a palavra Ano;
5. posicione o cursor na célula G2 e digite =DIA(A2) para extrair 
o dia da respectiva data;
6. pressione ENTER e observe que a célula G2 assume o número 
15, correspondente ao dia da respectiva data;
7. na célula H2 digite =MÊS(A2) e na célula I2 digite =ANO(A2). 
As células assumem os valores 2 e 2011, respectivamente;
8. agora selecione o intervalo G2:I2, aponte para a alça de cópia e 
arraste até a linha 800 para copiar as fórmulas;
[ 14 ] EXCEL AVANÇADO APLICADO AO CONTROLE
9. observe que agora os elementos Dia, Mês e Ano encontram-se 
em colunas separadas;
10. salve o arquivo.
Agora descobriremos, por meio da função DIATRABALHO(), 
uma data final, em função de um número de dias úteis a partir de uma 
data inicial, incluindo uma lista de feriados.
1. Abra o programa Excel 2007;
2. abra o arquivo DiaTrabalho(xlsx).xlsx;
3. na célula C4 digite a seguinte função:
=DIATRABALHO(C2;C3;C6:C8)
4. o resultado é a data 26/01/2012 (caso não esteja no formato de 
data, acesse a opção de formato Data Abreviada);
5. isso significa que, 15 dias úteis a partir da data 02/01/2012, 
descontando os 3 feriados, a data resultante é 26/01/2012;
6. para confirmação do resultado, faça-o utilizando um calendário 
e conte o número de dias “pulando” os sábados, domingos e os 
dias discriminados dos feriados.
7. salve o arquivo.
[ 15 ]Unidade 2 - Aula 2 - Transposições e Operações Diversas
3. Formatação de números
Este recurso determina a maneira que as informações serão 
exibidas nas células selecionadas. É possível usar um dos formatos de 
número predefinidos ou criar formatos personalizados, utilizando a guia 
Número da caixa de diálogo Formatar Células acessando a lista drop-
down do grupo Número na faixa de opções Início.
•	 Categoria: lista vários formatos de número, data e hora. 
Quando se seleciona uma categoria, o quadro exibe os formatos 
predefinidos e personalizados para essa categoria.
•	 Exemplo: exibe um exemplo do formato selecionado na caixa 
Exemplo.
•	 Casas decimais: permite a escolha do número de casas decimais 
para um determinado número fracionário.
•	 Símbolo: define o símbolo monetário para o número.
•	 Números negativos: define o formato para os números 
negativos.
[ 16 ] EXCEL AVANÇADO APLICADO AO CONTROLE
O Excel 2007 fornece vários formatos de número internos, mas, se 
eles não atenderem às nossas necessidades, podemos personalizar um. 
Para obter mais informações sobre como alterar os códigos de formato de 
número, talvez seja necessário revermos as diretrizes de personalização 
de um formato de número antes de iniciar.
Não é possível excluir um formato de número interno, mas pode-
se excluir um formato de número personalizado quando o usuário não 
precisar mais dele.
Diretrizes de personalização de números
Para criar um formato de número personalizado, devemos começar 
selecionando um dos formatos de número internos como ponto de 
partida. Em seguida, alteramos qualquer uma das seções de código desse 
formato.
Um formato de número pode ter até quatro seções de código, 
separadas por ponto-e-vírgulas. Essas seções de código definem o formato 
para números positivos, números negativos, zeros e texto, nessa ordem.
<POSITIVO>;<NEGATIVO>;<ZERO>;<TEXTO>
Por exemplo, use essas seções de código para criar o seguinte 
formato personalizado:
[Azul]#.##0,00_);[Vermelho](#,##0,00);0,00;”vendas “@
Veja alguns exemplos na tabela a seguir:
ORIGINAL PERSONALIZADO CÓDIGO
12345,59 1234,60 ####,#
8,9 8,900 # 0,000
0,631 0,6 0,#
12
1234,568
12,0
1234,57
#,0#
44,398
102,65
2,8
44,398
102,65
2,8
(com decimais alinhados)
???,???
5,25
5,3
5 1/4
5 3/10 
(com frações alinhadas)
# ???/???
[ 17 ]Unidade 2 - Aula 2 - Transposições e Operações Diversas
Agora vamos exercitar!
Exercício: Módulo02_Aula02_Exe04
Agora aprenderemos a trabalhar com formatação de números.
1. Caso o programa Excel 2007 não esteja aberto, abra-o;
2. vamos importar dados de um arquivo “.CSV” e para isso, acesse 
a guia Dados e escolha a opção De Texto;
3. aponte para o arquivo Convenios_Ministerios(csv).csv 
presente na sua pasta Meus Documentos e clique no botão 
Importar;
4. abrir-se-á a tela do Assistente de importação de texto;
Atenção!
5. conforme visto na Aula 01, defina as seguintes opções de 
importação:
•	 Tipo de campo: Delimitado.
•	 Delimitadores: Ponto e vírgula.
•	 Posição inicial: célula A1.
6. ao final clique no botão OK;
[ 18 ] EXCEL AVANÇADO APLICADO AO CONTROLE
7. observe os dados importados na planilha, pois iremos adaptar 
alguns formatos numéricos dela;
8. presumindo que todos os dados da coluna A devessem possuir 
o formato XXX-XXX, selecione o intervalo A2:A101 e acesse a 
opção de Formato de células;
9. selecione a opção Mais Formatos de Número e em seguida o 
item Personalizado;
10. na caixa Tipo, digite a sequência 000-000;
[ 19 ]Unidade 2 - Aula 2 - Transposições e Operações Diversas
11. ao final clique em OK e observe que todos os números desta 
coluna A ficaram com o formato XXX-XXX, ou seja, com um 
traço separando dois blocos de três números;
Agora formataremos os dados da coluna C. Presumindo que todos 
os dados desta coluna devam ficar com 4 dígitos, ou seja, inclua o zero à 
esquerda dos números que possuírem apenas 3 dígitos, faça o seguinte:
12. selecione o intervalo C2:C101, acesse a caixa de formatos de 
números como realizado anteriormente;
13. digite 0000 na caixa tipo e clique em OK;
Pronto! Todos os dados desta coluna ficaram padronizados com 4 
dígitos;
14. Agora corrigiremos a aparência dos números presentes na 
coluna L, para isso, selecione o intervalo L2:L101;
[ 20 ] EXCEL AVANÇADO APLICADO AO CONTROLE
15. acesse a caixa de formatos de número e digite na caixa Tipoo 
número zero 14 vezes;
Pronto! A apresentação dos códigos dos Convenentes foi 
padronizada em 14 dígitos;
16. salve o arquivo na pasta Meus Documentos com o nome 
Mod02_Aula02_Exe04.xlsx.
4. Remoção automática de dados duplicados
Na maioria das vezes, quando importamos dados de outro formato 
de arquivo (geralmente originados de um banco de dados), uma parte 
dos registros se repete e de certa forma desejamos eliminá-los para que 
permaneça apenas uma amostra de cada.
Esta operação pode ser realizada automaticamente por intermédio 
do botão Remover Duplicatas presente no grupo Ferramentas de 
Dados da guia Dados.
[ 21 ]Unidade 2 - Aula 2 - Transposições e Operações Diversas
Quando removemos valores duplicados, apenas os valores no 
intervalo de células ou na tabela são afetados. Quaisquer outros valores 
fora do intervalo de células ou da tabela não são alterados ou movidos.
Como estamos excluindo dados permanentemente, convém 
copiarmos o intervalo de células ou a tabela original para outra planilha 
ou pasta de trabalho antes de removermos os valores duplicados.
IMPORTANTE
O botão Opções, presente na caixa de diálogo Localizar e Substituir, expande a quantidade 
de opções de pesquisa que podem ser utilizadas nas localizações.
A seguir, realizaremos um exercício que permitirá utilizar este 
recurso de remoção de dados duplicados em uma planilha.
Exercício: Módulo02_Aula02_Exe05
Vamos realizar o exercício então?
1. Caso o programa Excel 2007 não esteja aberto, abra-o;
2. abra o arquivo Duplicadas.xlsx presente na pasta Meus 
Documentos do seu computador;
3. execute o procedimento de transposição de dados 
(desmembramento) da coluna A por meio do comando Texto 
para Colunas apresentado no capítulo 1 desta aula;
4. após o desmembramento da coluna A em várias, realize o ajuste 
das larguras de todas as colunas e centralize seus dados;
[ 22 ] EXCEL AVANÇADO APLICADO AO CONTROLE
5. selecione as colunas A até E, acesse a guia Dados e escolha o 
botão Remover Duplicatas presente do grupo Ferramentas 
de Dados. Assim, surge na tela a caixa de diálogo Remover 
Duplicatas;
6. como todas as opções desta caixa encontram-se devidamente 
corretas, basta clicar no botão OK;
7. assim, uma mensagem informativa avisa a quantidade de linhas 
removidas e a as remanescentes;
8. clique em OK para fechá-la e percorra a planilha para verificar 
que realmente a linhas de dados (excetuando-se a do cabeçalho) 
diminuíram de 100 para 72;
9. salve o arquivo.
[ 23 ]Unidade 2 - Aula 2 - Transposições e Operações Diversas
Síntese
Aprendemos nesta aula que é possível efetuar transposições de 
dados entre linhas e colunas (e vice-versa), bem como dividir conteúdos 
de uma célula em várias outras.
Conhecemos a definição, características e sintaxe de algumas 
funções de manipulação de datas do Excel 2007 e percebemos as 
vantagens proporcionadas pelo uso delas.
Vimos que a formatação de números pode tornar-se um 
procedimento atraente e usual quando necessitamos de adaptações em 
suas formas de apresentação.
Também nesta aula, executamos a operação de remoção de 
dados duplicados, fazendo com que planilhas com muitos dados sejam 
ajustadas com extrema rapidez e versatilidade.
[ 24 ] EXCEL AVANÇADO APLICADO AO CONTROLE
Referências bibliográficas 
COX, F.; FRYE C.; LAMBERT, M.D; PREPPERNEAU, J.; MURRAY, K. 
Microsoft Office System 2007 – Passo a passo. Porto Alegre: Artmed, 
2008.
Microsoft. Serviço de Suporte ao Excel 2007. Criar ou excluir um 
formato de número personalizado. Disponível em: <http://office.
microsoft.com/pt-br/excel-help/criar-ou-excluir-um-formato-de-
numero-personalizado-HP001216503.aspx>. Acesso em 28 de fevereiro 
de 2012.
Microsoft. Serviço de Suporte ao Excel 2007. Dividir nomes utilizando o 
Assistente de Conversão de Texto para Colunas. Disponível em: <http://
office.microsoft.com/pt-pt/excel-help/dividir-nomes-utilizando-o-
assistente-de-conversao-de-texto-para-colunas-HA010102340.aspx>. 
Acesso em 28 de fevereiro de 2012.
Microsoft. Serviço de Suporte ao Excel 2007. Reorganizar (transpor) 
dados de colunas para linhas ou vice-versa. Disponível em: <http://
office.microsoft.com/pt-br/excel-help/reorganizar-transpor-dados-de-
colunas-para-linhas-ou-vice-versa-HP010224502.aspx>. Acesso em 28 
de fevereiro de 2012.
Aula 1 
Fórmulas
EXCEL AVANÇADO APLICADO 
AO CONTROLE
Unidade 3 
Manipulação dos Dados
Instituto Serzedello Corrêa
RESPONSABILIDADE PELO CONTEÚDO
Tribunal de Contas da União
Secretaria Geral da Presidência
Instituto Serzedello Corrêa
2ª Diretoria de Desenvolvimento de Competências 
Serviço de Planejamento e Projetos Educacionais
SUPERVISÃO
Pedro Koshino
CONTEUDISTA
Helbert de Sousa Arruda
TRATAMENTO PEDAGÓGICO
Flávio Sposto Pompeo
RESPONSABILIDADE EDITORIAL
Tribunal de Contas da União
Secretaria Geral da Presidência
Instituto Serzedello Corrêa
Centro de Documentação
Editora do TCU
PROJETO GRÁFICO
Ismael Soares Miguel
Paulo Prudêncio Soares Brandão Filho
Vivian Campelo Fernandes
DIAGRAMAÇÃO
Vanessa Vieira
Brasil. Tribunal de Contas da União.
Excel Avançado Aplicado ao Controle / Tribunal de Contas da União ; 
conteudista: Helbert de Sousa Arruda. – Brasília : TCU, Instituto Serzedello 
Corrêa, 2012.
17 p. : il.
Conteúdo: Unidade 3: Manipulação dos dados. Aula 1: Fórmulas.
Curso realizado no período de 22/05 a 19/06/2012 no Ambiente
Virtual de Educação Corporativa do Tribunal de Contas da União.
1. Importação de dados. 2. Normalização de dados. 3. Manipulação de 
dados. 4. Análise de dados. I. Título.
Ficha catalográfica elaborada pela Biblioteca Ministro Ruben Rosa
© Copyright 2012, Tribunal de Contas de União 
<www.tcu.gov.br>
Permite-se a reprodução desta publicação, 
em parte ou no todo, sem alteração do conteúdo, 
desde que citada a fonte e sem fins comerciais.
[ 3 ]Unidade 3 - Aula 1 - Fórmulas
Aula 1 – Fórmulas
Quais as características e as aplicações das fórmulas?
Quais os tipos de operadores?
Como trabalhar as operações matemáticas e de data/hora?
Como fazer referências a outras planilhas/arquivos?
Iniciamos nossos estudos pela abordagem das operações com 
fórmulas no Excel 2007, exibindo assim sua sintaxe, características e 
aplicações tradicionais em cálculos nas planilhas. Também evidenciamos 
o uso de fórmulas incluindo referências externas, como por exemplo, 
outras planilhas e arquivos.
Para facilitar o estudo, esta aula está organizada da seguinte forma:
Ao final desta aula, esperamos que você tenha condições de:
•	 conhecer a sintaxe e as características das fórmulas;
•	 executar operações matemáticas com datas e horas;
•	 conhecer as categorias e os respectivos operadores;
•	 elabora fórmulas que façam referência a elementos externos à 
planilha.
Pronto para começar? Então, vamos!
Aula 1 – Fórmulas ������������������������������������������������������������������������������������������������������������3
1. Uso de fórmulas e seus operadores �����������������������������������������������������������������4
2. Operações com datas e horas �������������������������������������������������������������������������������8
3. Referências em planilhas ����������������������������������������������������������������������������������� 12
Síntese �������������������������������������������������������������������������������������������������������������������������������� 16
Referências bibliográficas ����������������������������������������������������������������������������������� 17
[ 4 ] EXCEL AVANÇADO APLICADO AO CONTROLE
1. Uso de fórmulas e seus operadores
O uso de fórmulas pode ajudar-nos a analisar a consistência dos 
dados em uma planilha. Por meio de uma fórmula é possível executar 
operações como adição, multiplicação, comparação de valores da 
planilhaetc. Sempre devemos usar uma fórmula quando desejarmos 
incluir valores calculados em uma planilha de dados.
Uma fórmula pode incluir qualquer um dos seguintes elementos: 
operadores, referências de células, valores, funções de planilha e nomes. 
Para incluirmos fórmulas em uma determinada célula da planilha, 
devemos digitar uma combinação desses elementos na barra de fórmulas.
Pode-se pensar em uma fórmula como um lado de uma equação 
cujo resultado é mostrado nas células. Uma fórmula sempre é iniciada 
com um sinal de igual (=). Veja o exemplo abaixo:
Usam-se os operadores para especificar a operação a ser realizada 
nos operandos (valores em ambos os lados do operador) contidos na 
fórmula. O Excel 2007 possui quatro categorias de operadores.
São elas:
Operadores Aritméticos 
Estes operadores executam operações básicas, combinam e 
produzem resultados numéricos:
OPERADOR DESCRIÇÃO
+ Adição
- Subtração
* Multiplicação
/ Divisão
^ Exponenciação
% Porcentagem
Referência da célula
Operador de adição
Operador de divisão
Constantes numéricas
=(B4+25)/100
[ 5 ]Unidade 3 - Aula 1 - Fórmulas
OPERADOR DESCRIÇÃO
&
Conecta ou concatena dois valores de texto para produzir 
um valor de texto contínuo; se um operando for uma 
referência de células e a célula referenciada contiver uma 
fórmula, este operador juntará o valor produzido pela 
fórmula na célula referenciada como valor texto.
“ Define o comportamento de um determinado conteúdo da célula como valor literal.
OPERADOR DESCRIÇÃO
: Define um intervalo de células (da primeira até a última incluindo as intermediárias)
; Define a alternação de referências de células e separação de argumentos de funções.
! Este operador une informações entre planilhas diferentes.
OPERADOR DESCRIÇÃO
= Igual a
> Maior que
< Menor que
>= Maior ou igual a
<= Menor ou igual a
<> Diferente de
Operadores de Texto
Este tipo de operador une duas ou mais informações de texto 
em um único valor de texto combinado (útil no uso de funções que 
produzam texto). A outra utilização deste tipo é a definição de conteúdos 
em formato texto para serem utilizados em combinação nas fórmulas.
Operadores de Comparação
Este tipo de operador compara dois valores e produz o valor lógico 
VERDADEIRO ou FALSO. Por exemplo, a fórmula =Receita<550 produz 
o valor VERDADEIRO se a Receita for menor que R$ 550,00. Se o valor 
for maior que R$ 550,00, esta fórmula produzirá o valor lógico FALSO.
Operadores de Referência
Este tipo de operador combina duas células em uma única referência de 
junção formando um intervalo (adjacente ou não-adjacente). Também é 
possível efetuar uma referência à outra planilha da mesma pasta de trabalho.
[ 6 ] EXCEL AVANÇADO APLICADO AO CONTROLE
No trabalho de manipulação de fórmulas, deve-se observar a 
ordem de precedência dos operadores, pois esta ordem de avaliação 
dos operadores, presentes em uma mesma fórmula faz com que o 
resultado seja diferente. Portanto é necessário tomar o devido cuidado 
na colocação de tais operadores na fórmula, de tal maneira, que seja 
realizado o cálculo na sequência desejada.
A ordem de precedência é a seguinte:
OPERADOR DESCRIÇÃO
! União de múltiplas planilhas
: Intervalo adjacente de células
; Intervalo não-adjacente de células
% Porcentagem
^ Exponenciação
* e / Multiplicação e Divisão
+ e - Adição e Subtração
& Junção de texto
= < > <= >= <> Comparações
Se a fórmula contiver mais de um operador com a mesma 
prioridade, por exemplo, vários operadores de comparação, o Excel 2007 
avalia os operadores da esquerda para direita.
Agora vamos exercitar algumas fórmulas!
Exercício: Módulo03_Aula01_Exe01
1. Abra o programa Excel 2007;
2. abra o arquivo Mod03_Aula01_Exe01.xlsx presente na pasta 
Meus Documentos do seu computador;
3. na célula D2 digite a fórmula: =B2+C2 para obter a soma dos 
dois valores;
4. na célula D3 digite a fórmula: =B3-C3 para obter a subtração 
dos dois valores;
5. na célula D4 digite a fórmula: =B4*C4 para obter a multiplicação 
dos dois valores;
6. na célula D5 digite a fórmula: =B5/C5 para obter a divisão dos 
dois valores;
Se desejarmos alterar 
a ordem de avaliação, 
devemos utilizar os 
parênteses para agrupar 
expressões em sua fórmula. 
O Excel 2007 primeiro 
calcula as expressões entre 
parênteses e, em seguida, 
utiliza estes resultados 
para calcular o restante da 
fórmula.
Atenção!
[ 7 ]Unidade 3 - Aula 1 - Fórmulas
7. na célula D6 digite a fórmula: =B6^C6 para elevar o primeiro 
valor à potência do segundo valor;
8. na célula D7 digite a fórmula: =B7*C7% para obter a 
porcentagem do primeiro valor em relação ao segundo;
9. na célula D8 digite a fórmula: =B8&” “&C8 para unir o conteúdo 
das duas células separadas por um espaço;
Observe se obteve os mesmos resultados abaixo:
10. posicione o cursor na célula C18 e digite a função 
=SOMA(C12:C17) para obter a soma de todo o intervalo de 
valores acima da célula;
11. na célula C19, digite a função =SOMA(C12;C15;C17) para 
somar apenas as células referenciadas;
Verifique se os resultados foram os mesmos abaixo:
[ 8 ] EXCEL AVANÇADO APLICADO AO CONTROLE
12. caso negativo, refaça a(s) função(ões) errada(as), caso positivo, 
apenas salve o arquivo;
13. passe para a Plan2, coloque o cursor na célula D2 e digite a 
fórmula =A2=C2;
14. na célula D3 digite a fórmula =A3>C3;
15. na célula D4 digite a fórmula =A4<C4;
16. na célula D5 digite a fórmula =A5>=C5;
17. na célula D6 digite a fórmula =A6<=C6;
18. na célula D7 digite a fórmula =A7<>C7;
Verifique se os resultados foram os mesmos abaixo:
19. caso negativo, refaça a(s) fórmula(s) errada(as), caso positivo, 
apenas salve o arquivo. 
2. Operações com datas e horas
Dentre as inúmeras possibilidades de cálculo que o Excel 2007 
torna possível, uma muito interessante é a capacidade de realizar 
operações automatizadas com datas e horas, com base nas informações 
que inserimos na planilha.
O Excel 2007 permite a execução de operações matemáticas com 
conteúdos tanto em formato de data como em horas. Por exemplo, é muito 
útil fazer uma espécie de folha de ponto para calcular a remuneração de 
funcionários que recebem por hora trabalhada, a partir dos horários de 
chegada e saída na empresa.
[ 9 ]Unidade 3 - Aula 1 - Fórmulas
Então vamos exercitar essas operações com datas e horas?
Exercício: Módulo03_Aula01_Exe02
1. Abra o programa Excel 2007;
Com uma nova planilha na tela, 
2. digite os seguintes dados em suas respectivas células:
Para descobrirmos a quantidade de dias entre as datas 02/09/2010 
e 09/06/2010, 
3. posicione o cursor na célula B6 e digite a seguinte fórmula:
=B3-B2
Observe que o resultado foi 85, informando que entre as duas datas 
há um período de 85 dias.
Para descobrimos a quantidade de meses entre as mesmas datas 
acima, 
4. digite a seguinte fórmula na célula B7:
=MÊS(B3)-MÊS(B2)
Observe que o resultado foi 3.
Agora posicione o cursor na célula B8 e vamos descobrir a 
quantidade de meses entre duas datas, mas as que possuem mais de um 
ano de diferença. 
[ 10 ] EXCEL AVANÇADO APLICADO AO CONTROLE
5. Digite a seguinte fórmula:
Após pressionar o ENTER observe que a resposta será 15, ou seja, 
entre a data 10/12/2011 e 02/09/2010 existe a diferença de 15 meses.
Desafio: 
6. digite as datas 15/09/2007 e 23/11/2011 em duas células 
diferentes e ao final descubra (utilizando uma fórmula) a 
quantidade de anos entre estas duas datas.
Agora realizaremos uma operação matemática entre horas de um 
mesmo dia:
7. mude para a planilha Plan2 e digite os seguintes dados nas 
respectivas células:
Para descobrirmos a quantidade de horas líquidas trabalhadas no 
dia, 
8. posicione o cursor na célula C8 e digitea seguinte fórmula:
[ 11 ]Unidade 3 - Aula 1 - Fórmulas
9. Observe a resposta 07:31 informando a quantidade de horas 
líquidas trabalhadas no dia.
Agora vamos descobrir uma data específica, mediante outra de 
origem, somada a um número determinado de dias.
Acesse a Plan2, 
10. digite na célula B2 a data 13/01/2011 e na célula B3 digite o 
número 74 (que é o número de dias desejado):
Posicionado na célula B4, 
11. digite a seguinte fórmula:
=B2+B3
Observe que é obtida a data 28/03/2011 como resposta.
12. Salve o arquivo na pasta Meus Documentos de seu computador 
com o nome Mod03_Aula01_Exe02.xlsx.
[ 12 ] EXCEL AVANÇADO APLICADO AO CONTROLE
3. Referências em planilhas
As pastas de trabalho proporcionam um meio de organizar muitas 
planilhas em um mesmo arquivo. Uma pasta de trabalho é uma coleção 
de várias páginas de planilha que possuem o mesmo número de colunas 
e linhas que a primeira e, opcionalmente, pode-se criar planilhas 
exclusivas para um determinado gráfico.
Cada página de planilha é uma grade formada por colunas e 
linhas distribuídas na tela de maneira tal que o usuário possa relacionar 
informações horizontal e verticalmente, seja em referências internas 
(mesma planilha) ou externas (outras planilhas e/ou arquivos).
Estas referências externas são comumente denominadas 
“Referências 3D” e são utilizadas para realizar cálculos com valores 
referenciados entre diferentes planilhas.
E por que 3D? Pelo simples fato de que esta referência estende a 
ligação de linhas e colunas à outra dimensão (a de profundidade) que são 
as planilhas que compõem uma determinada pasta de trabalho do Excel.
O ponto de exclamação (!) é o operador responsável pela interligação 
das fórmulas às células de outras planilhas e/ou arquivos.
Vamos exercitar estas referências externas agora!?
Exercício: Módulo03_Aula01_Exe03
Agora aprenderemos a elaborar fórmulas que realizem referências 
externas em outras planilhas.
1. Abra o arquivo Mod03_Aula01_Exe03.xlsx que se encontra na 
pasta Meus Documentos de seu computador;
2. observe que este arquivo possui três planilhas: Valor, Pago e Saldo;
Vamos subtrair os valores pagos dos valores dos convênios e colocar 
o resultado na planilha de saldo. Para isso, coloque o cursor na célula E2 
Atenção!
[ 13 ]Unidade 3 - Aula 1 - Fórmulas
da planilha Saldo.
3. Digite o sinal de igual (=), em seguida dê um clique na aba da 
planilha Valor e por fim clique na célula E2 desta planilha;
A primeira parte da fórmula está pronta. Para continuar,
4. digite o sinal de subtração (-), em seguida dê um clique na aba 
da planilha Pago e por fim clique na célula E2 desta planilha;
5. para finalizar a subtração basta pressionar ENTER;
Observe se você obteve o resultado abaixo na célula E2 da planilha Saldo;
Agora posicione o cursor novamente na célula E2 desta planilha, 
6. pressione as teclas CTRL + C. 
Para finalizar, 
7. selecione o intervalo E3:E16, acesse a caixa de diálogo Colar 
Especial e selecione a opção Fórmulas.
[ 14 ] EXCEL AVANÇADO APLICADO AO CONTROLE
8. clique em OK para copiar a primeira fórmula para as demais 
células da coluna;
9. pressione a tecla ESC para eliminar o processo de cópia (o 
tracejado animado presente na célula E2);
10. clique na célula E17;
Agora totalizaremos, em profundidade, os intervalos E2:E16 de 
todas as planilhas;
11. pressione a tecla SHIFT e clique na aba da planilha Valor 
para fazer um grupo com as três planilhas (isto fará com que 
qualquer operação realizada, afetará as três simultaneamente);
12. observe o termo [Grupo] na barra de título do arquivo, após o 
seu nome;
13. selecione a opção Soma no menu AutoSoma presente no grupo 
Edição da guia Início;
14. após ter sido inserida a função Soma, basta pressionar a tecla 
ENTER para confirmar a inserção da função nas três planilhas 
ao mesmo tempo;
15. clique na aba das demais planilhas para verificar como a função 
foi inserida em todas elas ao mesmo tempo;
16. salve o arquivo.
Para realizar esta operação entre planilhas presentes em arquivos 
diferentes, basta abrir os dois arquivos (ou mais) ao mesmo tempo e 
executar o mesmo procedimento, porém alternando de arquivo na barra 
de tarefas do Windows.
[ 15 ]Unidade 3 - Aula 1 - Fórmulas
Vamos realizar esta tarefa em um exemplo bem simples? Então, 
vamos lá!
1. Com o arquivo anterior ainda aberto, clique no botão Novo 
para que um novo arquivo de planilha seja aberto;
2. observe, na barra de tarefas, dois botões referentes ao Excel 
2007, indicando que os dois arquivos encontram-se abertos;
3. aponte para a célula B2 da Plan1 do novo arquivo aberto e 
posicione o cursor nesta célula;
Iremos subtrair o valor total pelo total pago, presente nas células E17 
das planilhas Valor e Pago e armazenaremos o resultado na respectiva 
célula citada acima.
4. digite o sinal de igual (=), em seguida clique no botão da barra 
de tarefas do Windows correspondente ao arquivo Mod03_
Aula01_Exe03.xlsx e por fim selecione a célula E17 da planilha 
Valor;
5. digite o sinal de subtração (-), em seguida clique na aba da 
planilha Pago e depois na célula E17 desta planilha;
6. pressione ENTER e observe que o resultado desta subtração é 
apresentado neste novo arquivo;
7. observe a fórmula inserida neste processo:
=[Mod03_Aula_01_Exe03.xlsx]Valor!$E$17-[Mod03_
Aula_01_Exe03.xlsx]Pago!$E$17
8. o nome do arquivo foi inserido entre colchetes para informar 
que o conteúdo desta fórmula faz referência a um endereço 
externo;
9. não há a necessidade de salvar este novo arquivo. Saia sem 
salvar.
[ 16 ] EXCEL AVANÇADO APLICADO AO CONTROLE
Síntese
Nesta aula conhecemos a sintaxe e as características das fórmulas, 
bem com as categorias de operadores e a utilização deles em exemplos 
práticos. Percebemos que as datas e horas também podem ser 
referenciadas em fórmulas como forma de obter uma quantidade líquida 
de dias, meses, anos, horas ou minutos.
Também estudamos que o Excel 2007 permite elaborar fórmulas 
que possuam referências externas, sejam elas em outras planilhas de 
uma mesma pasta ou até mesmo em outros arquivos.
[ 17 ]Unidade 3 - Aula 1 - Fórmulas
Referências bibliográficas 
PETRACIOLI, F., PC World. Artigo: Dicas para fazer cálculos usando 
datas e horas no Excel. Disponível em: <http://pcworld.uol.com.br/
dicas/2008/07/04/dicas-para-fazer-calculos-usando-datas-e-horas-no-
excel/>. Acesso em 29 de fevereiro de 2012.
SÓ TUTORIAL. Referências 3D. Disponível em: <http://www.sotutorial.
com/index.php/tutoriais-excel/excel-027-referencias-3d/>.Acesso em 
29 de fevereiro de 2012.
Aula 2 
Manipulação de Nomes e Textos
EXCEL AVANÇADO APLICADO 
AO CONTROLE
Unidade 3 
Manipulação dos Dados
Instituto Serzedello Corrêa
RESPONSABILIDADE PELO CONTEÚDO
Tribunal de Contas da União
Secretaria Geral da Presidência
Instituto Serzedello Corrêa
2ª Diretoria de Desenvolvimento de Competências 
Serviço de Planejamento e Projetos Educacionais
SUPERVISÃO
Pedro Koshino
CONTEUDISTA
Helbert de Sousa Arruda
TRATAMENTO PEDAGÓGICO
Flávio Sposto Pompeo
RESPONSABILIDADE EDITORIAL
Tribunal de Contas da União
Secretaria Geral da Presidência
Instituto Serzedello Corrêa
Centro de Documentação
Editora do TCU
PROJETO GRÁFICO
Ismael Soares Miguel
Paulo Prudêncio Soares Brandão Filho
Vivian Campelo Fernandes
DIAGRAMAÇÃO
Vanessa Vieira
Brasil. Tribunal de Contas da União.
Excel Avançado Aplicado ao Controle / Tribunal de Contas da União ; 
conteudista: Helbert de Sousa Arruda. – Brasília : TCU, Instituto Serzedello 
Corrêa, 2012.
26 p. : il.
Conteúdo: Unidade 3: Manipulação dos dados. Aula 2: Manipulação de 
nomes e textos.
Curso realizado no período de 22/05 a 19/06/2012 no Ambiente
Virtual de Educação Corporativa do Tribunal de Contas da União.1. Importação de dados. 2. Normalização de dados. 3. Manipulação de 
dados. 4. Análise de dados. I. Título.
Ficha catalográfica elaborada pela Biblioteca Ministro Ruben Rosa
© Copyright 2012, Tribunal de Contas de União 
<www.tcu.gov.br>
Permite-se a reprodução desta publicação, 
em parte ou no todo, sem alteração do conteúdo, 
desde que citada a fonte e sem fins comerciais.
[ 3 ]Unidade 3 - Aula 2 - Manipulação de Nomes e Textos
Aula 2 – Manipulação de nomes e textos
Como gerenciar nomes em células?
Como trabalhar com funções de transformação de texto?
Quais as funções de manipulação de texto?
Como utilizar o recurso de formatação condicional?
Iniciamos nossos estudos pelas operações iniciais com os dados. 
Esta apresentação inicial faz-se necessária, pois um conjunto de dados, 
seja digitado ou importado, requer procedimentos adequados, que façam 
com que o processo de manipulação torne o trabalho mais fácil e direto.
Para facilitar o estudo, esta aula está organizada da seguinte forma:
Para facilitar o estudo, esta aula está organizada da seguinte forma:
Ao final desta aula, esperamos que você tenha condições de:
•	 conhecer o gerenciamento de nomes em células;
•	 saber como utilizar as funções de transformação de texto;
•	 trabalhar com as funções de manipulação de texto;
•	 executar o recurso formatação condicional.
Pronto para começar? Então, vamos!
Aula 2 – Manipulação de nomes e textos �����������������������������������������������������������3
1. Gerenciamento de nomes ����������������������������������������������������������������������������������������4
2. Funções de transformação de texto ���������������������������������������������������������������8
3. Funções de manipulação de texto ������������������������������������������������������������������ 11
4. Formatação condicional �������������������������������������������������������������������������������������� 15
Síntese �������������������������������������������������������������������������������������������������������������������������������� 25
Referências bibliográficas ����������������������������������������������������������������������������������� 26
[ 4 ] EXCEL AVANÇADO APLICADO AO CONTROLE
1. Gerenciamento de nomes
O Excel 2007 permite que sejam utilizados rótulos de colunas e 
linhas em uma planilha para que o usuário consiga referenciar-se às 
células que os compõem. É também possível criar nomes descritivos 
para representar células, intervalos, fórmulas, bem como constantes. 
Esses rótulos podem ser utilizados em fórmulas que se refiram aos dados 
da mesma planilha. Caso o usuário queira representar um determinado 
intervalo em outra planilha, este pode usar um nome para tal operação.
A definição de um nome em uma determinada fórmula é utilizada 
para facilitar a compreensão do objetivo da fórmula. Por exemplo, a 
fórmula =SOMA(Taxas) talvez seja mais fácil de identificar do que 
SOMA(B2:B7).
Deve-se levar em consideração que os nomes estão disponíveis 
em qualquer planilha. Por exemplo, se o nome VendasMes referir-se ao 
intervalo D4:D12 na primeira planilha de uma pasta de trabalho, dessa 
forma o usuário poderá usar o nome VendasMes em qualquer outra 
planilha da mesma pasta de trabalho para referir-se ao intervalo D4:D12 
na primeira planilha.
Por outro lado, também é permitido o uso de nomes para 
representar fórmulas ou constantes. Por exemplo, pode utilizar o nome 
TaxaAdm para representar o valor da taxa de administração (como 2,5 
por cento) aplicado às transações de vendas.
E finalmente, também se pode criar um vínculo a um nome definido 
em outra pasta de trabalho ou um nome que se refira a células em outra 
[ 5 ]Unidade 3 - Aula 2 - Manipulação de Nomes e Textos
pasta de trabalho. Tomemos como base a fórmula SOMA(Janeiro.
xlsx!Vendas) que refere-se ao intervalo nomeado Vendas pertencente à 
pasta de trabalho chamada Janeiro.
Quais são as diretrizes para nomes?
•	 Caracteres válidos: o primeiro caractere de um nome deve 
ser uma letra, um caractere de sublinhado (_) ou uma barra 
invertida (\). Os caracteres restantes do nome podem ser letras, 
números, pontos e caracteres de sublinhado.
•	 Referências de célula não permitidas: os nomes não podem 
ser iguais a uma referência de célula, como Z$100 ou L1C1.
•	 Espaços não são válidos: espaços não são permitidos como 
parte de um nome. Use o caractere de sublinhado (_) e o ponto 
(.) como separadores de palavras; por exemplo, Imposto_
Sobre_Vendas ou Primeiro.Trimestre.
•	 Comprimento do nome: um nome pode conter até 255 caracteres.
•	 Diferenciação de maiúsculas e minúsculas: os nomes podem 
conter letras maiúsculas e minúsculas. O Excel não diferencia 
maiúsculas de minúsculas em nomes. Por exemplo, se o usuário 
criou o nome Vendas e, em seguida, criar outro nome chamado 
VENDAS na mesma pasta de trabalho, o Excel solicitará a 
escolha de um nome exclusivo.
Para associar um nome a uma célula ou a uma região, execute os 
passos a seguir:
Exercício: Módulo03_Aula02_Exe01
1. Abra o programa Excel 2007;
2. abra o arquivo Nomes.xlsx presente na pasta Meus Documentos 
do seu computador;
3. observe que este arquivo possui várias colunas e um conjunto 
de dados que vai até a linha 12.056;
4. dê um clique na célula D5 e em seguida efetue a classificação 
dos dados da planilha de forma decrescente por esta coluna;
Por padrão, os nomes usam 
referências absolutas de 
célula (referência de célula 
absoluta: em uma fórmula, 
o endereço exato de uma 
célula, independentemente 
da posição da célula que 
contém a fórmula, em que 
esta referência de célula 
absoluta tem a forma 
$C$L).
Atenção!
[ 6 ] EXCEL AVANÇADO APLICADO AO CONTROLE
Observe que agora as situações dos convênios estão agrupadas 
e que existe um grupo em sequência denominado INADIMPLENTE 
(intervalo D32:D66).
5. Selecione esse intervalo;
6. selecione a opção Definir Nome no grupo Nomes Definidos 
da guia Fórmulas;
7. Surge então a caixa de diálogo Novo Nome sugerindo o nome 
INADIMPLENTE para o intervalo selecionado (“Convenios” 
é o nome da planilha); na célula D3 digite a fórmula: =B3-C3 
para obter a subtração dos dois valores;
[ 7 ]Unidade 3 - Aula 2 - Manipulação de Nomes e Textos
8. deixe as opções como estão e clique em OK;
Agora vamos contabilizar o percentual de convênios inadimplentes 
em relação a todos os convênios, utilizando na fórmula, o nome que foi 
dado ao grupo.
9. posicione o cursor na célula G2 e digite a seguinte fórmula:
10. observe que o valor resultante é 0 (zero);
11. altere o formato da célula para porcentagem e aumente o 
número de casas decimais para verificar o real valor;
12. observe que utilizamos o nome INADIMPLENTE em nossa 
fórmula e o Excel 2007 aceitou-o sem resultar um erro;
13. salve o arquivo.
Desafio: Definindo um nome para o valor do convênio (coluna 
W) e outro para a localidade do responsável (coluna S) igual a RIO 
BRANCO – AC (não se esqueça de classificar por esta coluna para 
obter o grupo), crie uma fórmula para descobrir a média de valores 
dos convênios especificamente para esta localidade. Lembre-se de que 
devem ser usados, na fórmula, os nomes definidos e não os endereços 
dos intervalos das células.
[ 8 ] EXCEL AVANÇADO APLICADO AO CONTROLE
2. Funções de transformação de texto
Em uma planilha grande e complexa, com inúmeras células 
preenchidas com dados do tipo Texto, eventualmente se deseja, 
de certa forma, ajustar estes conteúdos, seja transformando de 
maiúscula para minúscula e vice-versa, ou até mesmo somente a 
primeira maiúscula.
Para este trabalho, o Excel 2007 conta com algumas funções que 
permitem o usuário efetuar estas adaptações. São elas:
=MAIÚSCULA()
Esta função converte o conteúdo de uma determinada célula para 
letrasmaiúsculas. O parâmetro (texto) pode ser uma referência ou uma 
cadeia de texto.
Sintaxe: =MAIÚSCULA(texto)
=MINÚSCULA()
Esta função, ao contrário da função anterior, converte o conteúdo 
de uma determinada célula para letras minúsculas. O parâmetro (texto) 
pode ser uma referência ou uma cadeia de texto.
Sintaxe: =MINÚSCULA(texto)
=PRI.MAIÚSCULA()
[ 9 ]Unidade 3 - Aula 2 - Manipulação de Nomes e Textos
Esta função aplica a primeira letra de uma cadeia de texto em 
maiúscula e todas as outras letras do texto depois de qualquer caractere 
diferente de uma letra. Converte todas as outras letras para minúsculas.
Sintaxe: =PRI.MAIÚSCULA(texto)
Vamos exercitar!
Exercício: Módulo03_Aula02_Exe02
1. Abra o programa Excel 2007;
2. posicione o cursor na célula B2 e digite a seguinte função:
=TEXTO(HOJE();“dddd”)
3. pressione ENTER e observe que surgiu o dia da semana por 
extenso nesta célula (não necessariamente o mesmo que o da 
figura abaixo);
Observe que o retorno desta função foi o dia da semana por 
extenso em letras minúsculas. Como poderíamos alterar estas letras 
para maiúsculas? Vamos lá?!
4. Posicione novamente o cursor na célula B2, pressione a tecla 
F2 para editar a fórmula e inclua a função MAIÚSCULA() da 
seguinte maneira:
[ 10 ] EXCEL AVANÇADO APLICADO AO CONTROLE
5. ao pressionar ENTER, verifique que agora as letras alteraram-
se para maiúsculas;
6. agora, altere a função MAIÚSCULA() para PRI.MAIÚSCULA() 
conforme abaixo e observe o resultado;
7. observe que somente as primeiras letras ficaram em maiúsculas;
Agora, vamos verificar o funcionamento da função MINÚSCULA(), 
para tanto,
8. posicione o cursor na célula B3, digite o valor 5 e pressione 
ENTER;
9. na célula B4, digite a função =ÉIMPAR(B3) e observe que o 
Excel 2007 retorna a palavra VERDADEIRO para indicar que 
realmente o número naquela célula é ímpar;
Para transformar este texto para letras minúsculas,
10. edite a fórmula da seguinte maneira:
11. salve o arquivo com o nome Mod03_Aula02_Exe02.xlsx.
[ 11 ]Unidade 3 - Aula 2 - Manipulação de Nomes e Textos
3. Funções de manipulação de texto
No mesmo grupo de funções do capítulo anterior, existe um 
conjunto de funções que tem a finalidade de efetuar a manipulação 
de texto (bem como de outros caracteres, porém menos utilizados) de 
maneira que estes possam ser concatenados, ajustados e até mesmo 
substituídos por outras sequências de caracteres.
Para estas operações, o Excel 2007 possui algumas funções que 
permitem o usuário efetuar tais manipulações. São elas:
=CONCATENAR()
Agrupa várias sequências de caracteres de texto em uma única 
sequência de caracteres de texto. Esta função tem o mesmo propósito 
que o caractere “&”, que também permite efetuar concatenações.
Sintaxe: =CONCATENAR(texto1; texto2; texto3;...)
=NÚM.CARACT()
Esta função retorna o número de caracteres em uma sequência de 
caracteres de texto referenciada no argumento.
Sintaxe: =NÚM.CARACT(texto)
=MUDAR()
[ 12 ] EXCEL AVANÇADO APLICADO AO CONTROLE
Atenção!
Substitui parte de uma sequência de caracteres de texto, com base 
no número de caracteres especificado, por uma sequência diferente.
Sintaxe: =MUDAR(texto_antigo;núm_inicial;núm_caract;novo_
texto)
=PROCURAR()
Localiza uma sequência de caracteres de texto (texto_procurado) 
em outra sequência (no_texto) e retorna o número da posição inicial de 
texto_procurado a partir do primeiro caractere de no_texto. Também, 
pode-se usar a função LOCALIZAR para encontrar uma sequência 
de caracteres de texto em outra, mas ao contrário de LOCALIZAR, 
PROCURAR diferencia maiúsculas e minúsculas e não permite 
caracteres curinga.
Sintaxe: =PROCURAR(texto_procurado;no_texto;núm_inicial)
=SUBSTITUIR()
Esta função coloca novo_texto no lugar de texto_antigo em uma 
cadeia de texto. Use SUBSTITUIR quando quiser substituir texto 
específico em uma cadeia de texto; use MUDAR quando quiser substituir 
qualquer texto que ocorra em um local específico de uma cadeia de texto.
Sintaxe: =SUBSTITUIR(texto, texto_antigo, novo_texto, [núm_
da_ocorrência])
Exercício: Módulo03_Aula02_Exe03
Agora vamos aprender a trabalhar com estas funções!
1. Caso o programa Excel 2007 não esteja aberto, abra-o;
2. abra o arquivo Textos.xlsx presente na pasta Meus Documentos 
de seu computador;
Observe que este arquivo possui duas planilhas: Textos1 e Textos2. 
Posicione-se na célula H2 da planilha Textos1 e vamos, em seguida, 
concatenar dados de algumas células.
[ 13 ]Unidade 3 - Aula 2 - Manipulação de Nomes e Textos
3. Digite a seguinte expressão e pressione ENTER:
Obs: a inclusão das aspas nos argumentos da função foi para que os 
dados ficassem com um espaço entre si.
4. Observe que os dados foram unidos em uma célula somente;
5. copie o conteúdo da célula H2 até a célula H151;
6. posicione o cursor na célula G2, centralize o conteúdo e em 
seguida digite a função que contará o número de caracteres que 
constam em uma célula.
A função é: =NÚM.CARACT(H2). 
7. pressione ENTER ao final;
Observe que a função descobriu que a célula H2 possui 60 
caracteres. 
8. Copie o conteúdo desta célula G2 até a célula G151 para 
sabermos o número de caracteres de cada célula da coluna H;
[ 14 ] EXCEL AVANÇADO APLICADO AO CONTROLE
Agora vamos efetuar algumas localizações e alterações de conteúdo 
utilizando funções específicas para tal tarefa.
Inicialmente descobriremos em que posição encontra-se o símbolo 
da barra no número do processo. Vamos lá!
9. Passe para a planilha Textos2, posicione o cursor na célula E2 e 
digite a seguinte expressão:
10. a função retorna o número 13 informando que o símbolo da 
barra encontra-se na 13º posição;
Agora que já sabemos que o símbolo da barra fica na 13ª posição, 
alteraremos esse caractere por um traço.
11. Posicione o cursor na célula F2, digite a seguinte expressão:
12. pressione ENTER;
Com isso, o número do processo foi preservado e a barra foi trocada 
por um traço.
13. Copie o conteúdo desta célula F2 até a célula F151;
14. observe que agora todos os números de processo possuem um 
traço no lugar da barra.
[ 15 ]Unidade 3 - Aula 2 - Manipulação de Nomes e Textos
Agora veja como é fácil trocar o ponto que se encontra na 6ª posição 
pelo traço (a barra que está na 13ª posição retorna à sua origem).
15. Vá até a célula E2 e digite o número 6 no lugar do número 13. 
16. salve o arquivo.
Viu como foi fácil!
4. Formatação condicional
A formatação condicional ajuda responder visualmente a questões 
específicas sobre os dados. É possível aplicar a formatação condicional a 
um intervalo de células, a uma tabela do Excel 2007 ou até mesmo a um 
relatório de Tabela Dinâmica.
Além disso, ela ajuda responder a determinadas questões ao 
facilitar o destaque de células ou intervalos de células, a ênfase de valores 
não usuais e a visualização de dados usando barras de dados, escalas de 
cores e conjuntos de ícones.
Um formato condicional altera a aparência de um intervalo de células 
com base em condições (ou critérios). Caso a condição seja verdadeira, 
o intervalo de células será formatado com base nessa condição; se a 
[ 16 ] EXCEL AVANÇADO APLICADO AO CONTROLE
condição for falsa, o intervalo de células não será formatado com base 
nessa condição.
Os conjuntos de formatações e ícones que a Formatação 
Condicional pode apresentar são os seguintes:
Barras de Dados
Escalas de Cor
[ 17 ]Unidade 3 - Aula 2 - Manipulação de Nomes e Textos
Conjuntos de Ícones
Conclui-se, então, que o recurso de Formatação Condicional é 
uma operação que permite ao usuário selecionar as células que deseja 
adicionar, alterar ou remover formatação condicional (um formato, 
como sombreamento de célula e cor de fonte, que o Excel 2007 aplica 
automaticamentea células se uma condição especificada for verdadeira).
A caixa de diálogo Nova Regra de Formatação permite que 
sejam definidos todos os parâmetros para construção das formatações 
conforme as condições definidas.
[ 18 ] EXCEL AVANÇADO APLICADO AO CONTROLE
ATENÇÃO
Pode-se utilizar a formatação condicional em células referidas em outra planilha 
pertencente à mesma pasta de trabalho, porém esse recurso não se encontra disponível 
entre pastas de trabalho.
A seguir, realizaremos um exercício que permitirá utilizar esses 
recursos de formatação condicional na planilha.
Exercício: Módulo03_Aula02_Exe04
Vamos aprender a utilizar alguns recursos de formatação 
condicional em uma planilha específica.
1. Caso o programa Excel 2007 não esteja aberto, abra-o;
2. abra o arquivo FC.xlsx que se encontra na pasta Meus 
documentos;
3. na célula H1 digite o texto MÊS ANÁLISE e pule para a célula 
H2;
A condição é a seguinte: Se o município for da UF igual a SP o mês 
deverá ser Março, para a UF igual a RJ deverá ser Abril e para as demais 
UFs deverá ser MG. Vamos lá!
[ 19 ]Unidade 3 - Aula 2 - Manipulação de Nomes e Textos
4. Digite a seguinte expressão:
5. após a digitação da expressão na célula H2, copie-a até a célula 
H151;
Observe que o Excel 2007 se encarregou de distribuir os municípios 
nos meses conforme as respectivas UFs. Vamos aplicar a formatação 
condicional da seguinte maneira: para o mês de Março, a cor do texto 
vermelha, para o mês de Abril a cor verde e para o mês de Maio a cor 
azul.
6. Selecione o intervalo H2:H151 e em seguida acesse a opção 
Formatação Condicional presente na guia Início e por fim a 
opção Nova Regra;
[ 20 ] EXCEL AVANÇADO APLICADO AO CONTROLE
7. na caixa de diálogo da nova regra escolha na caixa Tipo de 
Regra, a opção Formatar Apenas Células que Contenham;
8. em seguida escolha na Descrição da Regra, o item Texto 
Específico e escreva o termo Março na caixa de texto;
[ 21 ]Unidade 3 - Aula 2 - Manipulação de Nomes e Textos
9. clique no botão Formatar Células e escolha a cor de texto 
vermelha;
10. clique em OK e de volta à caixa da nova regra, clique também 
em OK. Observe que a primeira regra foi concluída;
[ 22 ] EXCEL AVANÇADO APLICADO AO CONTROLE
11. clique no botão Nova Regra e realize a mesma tarefa para os 
meses de Abril e Maio com suas respectivas cores;
12. as regras devem fica assim:
13. Ao final clique em OK e observe que os meses possuem as 
respectivas cores;
Trabalharemos agora com ícones na coluna de valores para verificar 
como o recurso se comporta neste caso. Veja como é fácil!
14. Selecione o intervalo F2:F151, em seguida escolha a opção 
Formatação Condicional na guia Início;
[ 23 ]Unidade 3 - Aula 2 - Manipulação de Nomes e Textos
15. escolha a opção Conjuntos de Ícones e passe o mouse nas 
várias opções para verificar como eles se comportam;
[ 24 ] EXCEL AVANÇADO APLICADO AO CONTROLE
16. caso o usuário deseje alterar as regras estabelecidas pelos 
conjuntos de ícones, basta clicar na opção Mais Regras e 
escolher as novas definições;
17. ao final salve o arquivo.
[ 25 ]Unidade 3 - Aula 2 - Manipulação de Nomes e Textos
Síntese
Nesta aula, conhecemos o recurso de gerenciamento de nomes, 
que permite agilizar determinadas tarefas nas planilhas, fornecendo 
métodos que auxiliem na manipulação de dados nas fórmulas.
Também executamos trabalhos utilizando funções que ajustam 
e manipulam conteúdos do tipo texto em células, fazendo com que 
operações complexas sejam realizadas com mais facilidade.
Por último, vimos que o Excel 2007 fornece o recurso de formatação 
condicional que permite ao usuário obter formatos diversos mediante 
elaborações de expressões que retornem verdadeiro ou falso.
[ 26 ] EXCEL AVANÇADO APLICADO AO CONTROLE
Referências bibliográficas 
Microsoft. Serviço de Suporte ao Excel 2007. Definir e usar nomes em 
fórmulas. Disponível em: <http://office.microsoft.com/pt-br/excel-
help/definir-e-usar-nomes-em-formulas-HA010147120.aspx?CTT=3>. 
Acesso em 07 de março de 2012.
Microsoft. Serviço de Suporte ao Excel 2007. Adicionar, alterar, 
localizar ou limpar formatos condicionais. Disponível em: <http://office.
microsoft.com/pt-br/excel-help/adicionar-alterar-localizar-ou-limpar-
formatos-condicionais-HP010342157.aspx>. Acesso em 08 de março de 
2012.
Aula 3 
Vínculos e Funções de Análise
EXCEL AVANÇADO APLICADO 
AO CONTROLE
Unidade 3 
Manipulação dos Dados
Instituto Serzedello Corrêa
RESPONSABILIDADE PELO CONTEÚDO
Tribunal de Contas da União
Secretaria Geral da Presidência
Instituto Serzedello Corrêa
2ª Diretoria de Desenvolvimento de Competências 
Serviço de Planejamento e Projetos Educacionais
SUPERVISÃO
Pedro Koshino
CONTEUDISTA
Helbert de Sousa Arruda
TRATAMENTO PEDAGÓGICO
Flávio Sposto Pompeo
RESPONSABILIDADE EDITORIAL
Tribunal de Contas da União
Secretaria Geral da Presidência
Instituto Serzedello Corrêa
Centro de Documentação
Editora do TCU
PROJETO GRÁFICO
Ismael Soares Miguel
Paulo Prudêncio Soares Brandão Filho
Vivian Campelo Fernandes
DIAGRAMAÇÃO
Vanessa Vieira
Brasil. Tribunal de Contas da União.
Excel Avançado Aplicado ao Controle / Tribunal de Contas da União ; 
conteudista: Helbert de Sousa Arruda. – Brasília : TCU, Instituto Serzedello 
Corrêa, 2012.
19 p. : il.
Conteúdo: Unidade 3: Manipulação dos dados. Aula 3: Vínculos e funções 
de análise.
Curso realizado no período de 22/05 a 19/06/2012 no Ambiente
Virtual de Educação Corporativa do Tribunal de Contas da União.
1. Importação de dados. 2. Normalização de dados. 3. Manipulação de 
dados. 4. Análise de dados. I. Título.
Ficha catalográfica elaborada pela Biblioteca Ministro Ruben Rosa
© Copyright 2012, Tribunal de Contas de União 
<www.tcu.gov.br>
Permite-se a reprodução desta publicação, 
em parte ou no todo, sem alteração do conteúdo, 
desde que citada a fonte e sem fins comerciais.
[ 3 ]Unidade 3 - Aula 3 - Vínculos e Funções de Análise
Aula 3 – Vínculos e funções de análise
Para que servem os vínculos externos?
Como utilizar funções de análises numéricas?
Como utilizar as funções de análises condicionais?
Iniciamos nossos estudos pela apresentação dos procedimentos 
para criação de vínculos externos no Excel 2007 e uma demonstração 
prática. Para complementar este estudo, mostraremos como utilizar 
a função de análise de frequência de dados, de análises numéricas e 
condicionais.
A fim de facilitar o estudo, esta aula está organizada da seguinte 
forma:
Ao final desta aula, esperamos que você tenha condições de:
•	 conhecer os procedimentos para criar vínculos externos;
•	 conhecer e utilizar a função de análise de frequência de dados;
•	 conhecer e utilizar as funções de análises numéricas;
•	 conhecer e utilizar as funções de análises condicionais.
Pronto para começar? Então, vamos!
Aula 3 – Vínculos e funções de análise ���������������������������������������������������������������3
1. Vínculos externos ������������������������������������������������������������������������������������������������������4
2. Análise de frequência de dados �������������������������������������������������������������������������8
3. Funções de análise numérica ���������������������������������������������������������������������������� 12
4. Funções de análise condicional ���������������������������������������������������������������������� 15
Síntese �������������������������������������������������������������������������������������������������������������������������������� 18
Referências bibliográficas ����������������������������������������������������������������������������������� 19
[ 4 ] EXCEL AVANÇADO APLICADO AO CONTROLE
1. Vínculos externos
Um vínculo externo (também chamada de link) é uma referência a 
umacélula ou um intervalo em uma planilha em outra pasta de trabalho 
do Excel ou uma referência a um nome definido em outra pasta de 
trabalho. Desta forma, pode-se criar uma referência ao intervalo de 
células específico, a um nome definido do intervalo de células ou definir 
um nome para a referência externa.
Em que situações as referências externas podem ser utilizadas de 
forma eficiente?
SITUAÇÃO DESCRIÇÃO
Mesclar dados de várias 
pastas de trabalho
É possível 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, não será necessário 
alterar manualmente a pasta de trabalho de resumo.
Criar exibições diferentes 
de seus dados
Pode-se inserir todos os dados em uma ou mais pastas 
de trabalhos de origem e criar uma pasta de trabalho de 
relatório que contenha referências externas apenas para 
os dados pertinentes.
Simplificar modelos 
complexos e grandes
Ao dividir um modelo complicado em uma sequência 
de pastas de trabalho interdependentes, o usuário 
poderá trabalhar no modelo sem abrir todas as planilhas 
relacionadas. As pastas de trabalho menores são mais 
fáceis de se alterar, não necessitam de muita memória e 
abrem, salvam e calculam com mais rapidez.
Qual é a aparência de uma referência externa para 
outra pasta de trabalho?
Fórmulas com referências externas a outras pastas de trabalho são 
exibidas de duas maneiras, dependendo de a pasta de trabalho de origem 
( aquela que fornece dados para uma fórmula ) estar aberta ou fechada.
Quando uma origem estiver aberta, a referência externa incluirá 
o nome da pasta de trabalho entre colchetes ([ ]), seguido do nome da 
planilha, um ponto de exclamação (!) e as células das quais a fórmula 
depende. Por exemplo, a fórmula a seguir adiciona as células C10:C25 a 
partir da pasta de trabalho com o nome Orçamento.xls
=SOMA([Orçamento.xlsx]Anual!C10:C25)
[ 5 ]Unidade 3 - Aula 3 - Vínculos e Funções de Análise
Conheceremos na prática o uso destas referências externas. Vamos lá!?
Exercício: Módulo03_Aula03_Exe01
1. Abra o programa Excel 2007;
2. abra o arquivo Link.xlsx que se encontra na pasta Meus 
Documentos do seu computador;
3. observe que esta planilha possui uma série de colunas referentes 
a Entidades.
Imagine que quiséssemos obter somente alguns dados desta 
planilha a fim de colocá-los em outra, com os dados interligados, para 
que as atualizações entre eles sejam realizadas de forma automática.
Bom, para isso é necessário executar o processo de criação de 
vínculos. Vamos lá?
4. Abra um novo arquivo, mas deixe o arquivo Link.xlsx ainda 
aberto;
5. obteremos somente as colunas C e D da planilha presente no 
arquivo Link.xlsx;
6. no novo arquivo, posicione o cursor na célula A1, digite apenas 
o sinal de igual e, em seguida, com o mouse, mude para o 
arquivo Link.xlsx na barra de tarefas do Windows e clique na 
célula C1. 
7. ao final, pressione ENTER;
8. observe a expressão que foi inserida na célula A1 do novo 
arquivo:
[ 6 ] EXCEL AVANÇADO APLICADO AO CONTROLE
Foi criado o vínculo entre a célula A1 e C1 dos arquivos, porém 
a referência está no formato absoluto. Para conseguirmos replicar este 
vínculo para as demais células, retiraremos os dois símbolos de cifrão 
($) da referência.
9. Após a retirada dos cifrões, copie a fórmula até a célula A71. 
Observe o resultado:
10. adapte a largura da coluna A ao conteúdo;
11. faça o mesmo procedimento interligando a célula B1 do novo 
arquivo à célula D1 do arquivo Link.xlsx;
12. retire, em seguida, a referência absoluta;
13. copie a fórmula até a célula B71.
Agora, faremos o último vínculo por intermédio de uma fórmula 
matemática, obtendo a metade do valor da planilha do arquivo Link.
xlsx.
[ 7 ]Unidade 3 - Aula 3 - Vínculos e Funções de Análise
14. Posicione o cursor na célula C1 no novo arquivo e digite o 
termo VALORES, ao final pule para a célula C2;
15. digite apenas o sinal de igual e, em seguida, com auxílio do 
mouse, mude para o arquivo Link.xlsx na barra de tarefas do 
Windows e clique na célula E2;
16. antes de finalizar, digite na fórmula o símbolo da barra (divisão) 
e, na sequência, o número 2; 
17. ao final, pressione ENTER, para obter a metade do valor que 
está no arquivo Link.xlsx.
Proceda da mesma maneira que as colunas anteriores. 
18. a referência absoluta e copie a fórmula até a célula C71;
19. salve o novo arquivo com o nome Link2.xlsx na pasta Meus 
Documentos;
Para verificar e editar os links entre as pastas de trabalho, é 
necessário acessar a caixa de diálogo Editar Vínculos, que pode ser 
acessada na guia Dados e opção Editar Link.
[ 8 ] EXCEL AVANÇADO APLICADO AO CONTROLE
20. Observe a caixa de diálogo Editar Vínculos e respectivas opções 
de edição e manutenção de vínculos entre planilhas.
2. Análise de frequência de dados
Antes de apresentar a função =FREQUÊNCIA() do Excel 2007, 
torna-se necessário conhecer como basicamente funciona a distribuição 
de frequência. Portanto, eis a primeira pergunta:
O que é uma distribuição de frequência?
É um método de agrupamento de dados divididos em classes com 
o intuito de fornecer a quantidade (e/ou a percentagem) de dados em 
cada uma das classes. Dessa maneira, pode-se resumir e visualizar um 
conjunto de dados sem a necessidade de análise dos valores individuais.
Basicamente, uma distribuição de frequência pode ser absoluta ou 
relativa, podendo ser representada no formato de tabelas ou gráficos.
Frequência Absoluta
É o número de vezes que um determinado valor de uma variável 
aparece dentro da amostra, ou seja, é a quantidade de elementos que se 
referencia a um grupo específico.
[ 9 ]Unidade 3 - Aula 3 - Vínculos e Funções de Análise
Frequência Relativa
Conforme o site Wikipédia e em Estatística, denomina-se 
frequência relativa o resultado obtido da divisão entre a frequência - o 
valor que é observado na população - e a quantidade de elementos da 
população. Geralmente é apresentada na forma de percentagem.
Eis um exemplo:
Uma planilha que contenha uma lista de pessoas e seus respectivos 
Estados de origem.
Pessoa/Estado
Pedro: Alagoas Bruna: Acre Anete: Alagoas Paulo: Acre Célio: Goiás Manoel: Paraná
Carlos: Paraná Fred: Goiás Sérgio: Pará Gilson: Paraná Rui: Pará Cláudia: Goiás
Antônio: Pará Márcio: Goiás Marcelo: Paraná Ana: Piauí Geraldo: Goiás Rita: Alagoas
Pedro: Alagoas Alícia: Pernambuco Meira: Paraná Flávio: Acre Lia: Paraná Fabiano: Pernambuco
Classes
Frequência 
Absoluta
Frequência 
Relativa
Acre 3 12,5%
Alagoas 4 16,7%
Goiás 5 20,8%
Pará 3 12,5%
Paraná 6 25,0%
Pernambuco 2 8,3%
Piauí 1 4,2%
Total 24 100,0%
Esta planilha distribuída em classes ficaria desta maneira:
Observe que os dados foram agrupados por Estado e contabilizados 
em quantidades por classe e porcentagens em relação ao total geral de 
pessoas. A função FREQUÊNCIA() é utilizada para obter informações a 
partir de dados presentes em planilhas deste tipo.
=FREQUÊNCIA()
Esta função retorna distribuição da frequência, ou seja, a frequência 
com que os valores ocorrem em um intervalo de valores e, em seguida, 
retorna uma matriz vertical de números.
Sintaxe: =FREQUÊNCIA(matriz_dados;matriz_binária)
[ 10 ] EXCEL AVANÇADO APLICADO AO CONTROLE
IMPORTANTE
Como o retorno da função FREQUÊNCIA() é uma matriz, esta deve ser inserida como uma 
fórmula matricial, ou seja, com a combinação das teclas CTRL+SHIFT+ENTER.
Agora vamos conhecer na prática o uso dessa função acima? Vamos lá!
Exercício: Módulo03_Aula03_Exe02
1. Abra o programa Excel 2007;
2. abra o arquivo Frequencia.xlsx presente na pasta Meus 
Documentos;
3. observe que esta planilha possui duas colunasde dados, sendo 
a UF e seus respectivos valores;
Atenção!
Vamos descobrir o menor valor e o maior valor presente neste 
intervalo de valores presentes na coluna C.
4. Digite a função =MÁXIMO(C3:C72) na célula F2;
5. digite a função =MÍNIMO(C3:C72) na célula F3;
[ 11 ]Unidade 3 - Aula 3 - Vínculos e Funções de Análise
Agora vamos descobrir a quantidade de valores em cada uma das 
classes existentes na planilha (previamente digitadas).
6. Agora vá até a célula I6 e digite a seguinte função:
=FREQUÊNCIA(C3:C72;H6:H10)
7. pressione ENTER e observe que resultou o valor 10;
8. selecione o intervalo I6:I10 e pressione a tecla F2 para entrar no 
modo de edição;
9. em seguida, após a seleção, pressione simultaneamente as teclas 
CTRL+SHIFT+ENTER para que a fórmula seja reinserida, 
porém em forma de matriz (você verá que a fórmula ficará 
entre Chaves);
10. analise os dados e verifique que o Excel 2007 encontrou a 
quantidade de valores em cada uma das classes;
11. pronto, salve o arquivo.
[ 12 ] EXCEL AVANÇADO APLICADO AO CONTROLE
3. Funções de análise numérica
O Excel 2007 possui também algumas funções que permitem 
o ajuste e manipulação de conteúdos em formato numérico. Dessa 
maneira, torna-se possível analisar células que possuam números, 
aplicando-se recursos de aleatoriedade, mais frequência e destaque de 
k-ésimo número maior ou menor, etc.
As funções de ajustes e manipulação de número são
=ALEATÓRIO()
Esta função permite retornar um número aleatório real maior ou 
igual a 0 e menor que 1 distribuído uniformemente. Um novo número 
aleatório real é retornado toda vez que a planilha é calculada.
Sintaxe: =ALEATÓRIO()
=ALEATÓRIOENTRE()
Esta função permite retornar um número aleatório inteiro entre 
dois números previamente especificados. Um novo número aleatório 
inteiro é retornado toda vez que a planilha é calculada.
Sintaxe: =ALEATÓRIOENTRE(núm_inicial;núm_final)
=MODO.ÚNICO()
Esta função retorna o valor que ocorre com mais frequência em 
um intervalo de dados ou matriz.
Sintaxe: =MODO.ÚNICO(núm1;[núm2];...)
=MAIOR()
Esta função retorna o k-ésimo maior valor de um intervalo de 
dados. Uma aplicação prática para esta função é selecionar um valor de 
acordo com a posição relativa.
Sintaxe: =MAIOR(matriz;k)
=MENOR()
[ 13 ]Unidade 3 - Aula 3 - Vínculos e Funções de Análise
Esta função, ao contrário da função =Maior(), retorna o k-ésimo 
menor valor de um intervalo de dados.
Sintaxe: =MENOR(matriz;k)
Agora vamos conhecer na prática o uso das funções acima!?!? 
Vamos lá!!
Exercício: Módulo03_Aula03_Exe03
1. Caso o programa Excel 2007 não esteja aberto, abra-o;
2. no novo arquivo, clique na célula B2 e digite a função 
=ALEATÓRIO() e pressione ENTER;
3. observe que foi inserido um número qualquer entre 0 e 0,99;
4. se quiser obter um número qualquer acima deste valor, basta 
multiplicá-lo por outro número. Exemplo:
5. após pressionar ENTER, copie esta fórmula até a célula B10;
6. para alterar o número, basta pressionar a tecla F9 ou executar 
qualquer operação que recalcule a planilha;
Para criar números aleatórios entre dois números, utilize a função 
ALEATÓRIOENTRE(). Vamos ver como funciona!
7. Clique na célula C2 e digite a seguinte função:
=ALEATÓRIOENTRE(15;22).
8. pressione ENTER e observe que o Excel 2007 inseriu um 
número entre 15 e 22;
9. copie esta expressão até a célula C25;
[ 14 ] EXCEL AVANÇADO APLICADO AO CONTROLE
Agora vamos descobrir o número que mais aparece nesta listagem, 
o segundo maior elemento e o penúltimo elemento. Vamos lá!
10. Nas respectivas células, digite as seguintes funções:
•	 Célula E3: =MODO.ÚNICO(C2:C25)
•	 Célula E4: =MAIOR(C2:C25;2)
•	 Célula E5: =MENOR(C2:C25;2)
Obs: a cada atualização que os dados da planilha sofrem, os valores 
se modificam para se adaptarem à nova realidade.
Nas funções MAIOR e 
MENOR, quando um número 
se repete, este também 
participa da verificação 
para definição do k-ésimo 
elemento pesquisado.
[ 15 ]Unidade 3 - Aula 3 - Vínculos e Funções de Análise
11. Salve o arquivo na pasta Meus Documentos com o nome 
Mod03_Aula03_Exe03.xlsx.
4. Funções de análise condicional
Dentre os vários grupos de funções, o Excel 2007 possui um 
conjunto bastante útil para levantamento de informações mediante uma 
ou mais condições. Estas funções foram concebidas para executarem 
determinadas tarefas toda a vez que uma condição específica é atendida.
Essas funções são:
=CONT.SE()
Esta função percorre um intervalo especificado e quantifica 
o número de células não vazias que porventura corresponda a um 
determinado critério pré-estabelecido. Pode-se utilizar a função CONT.
SES() caso o critério não seja único.
Sintaxe: =CONT.SE(intervalo;critérios)
=SOMASE()
Esta função também percorre um determinado intervalo e soma o 
conteúdo de outro intervalo correspondente conforme um critério pré-
estabelecido. Pode-se utilizar a função SOMASES() caso o critério não 
seja único.
Sintaxe: =SOMASE(intervalo;critérios;[intervalo_soma])
=MÉDIASE()
Da mesma maneira que a função SOMASE(), esta também percorre 
um determinado intervalo e calcula a média aritmética de outro intervalo 
correspondente conforme um critério pré-estabelecido. Pode-se utilizar 
a função MÉDIASES() caso o número de critérios seja maior que um.
Sintaxe: =MÉDIASE(intervalo;critérios;[intervalo_média])
A seguir, realizaremos um exercício que permitirá utilizar funções 
condicionais em uma planilha específica.
[ 16 ] EXCEL AVANÇADO APLICADO AO CONTROLE
Exercício: Módulo03_Aula03_Exe04
Vamos aprender a utilizar as funções apresentadas!
1. Caso o programa Excel 2007 não esteja aberto, abra-o;
2. abra o arquivo Funcoes_SE.xlsx que se encontra na pasta Meus 
Documentos do seu computador;
3. observe que este arquivo possui uma série de colunas e uma 
área para análise dos dados nas colunas H e I;
Atenção!
4. na célula I2 já está inicialmente inserido a UF igual a SP para 
aplicação inicial das funções;
5. na célula I3 digite a seguinte função:
=CONT.SE(C2:C151;I2)
6. a resposta é 37;
7. na célula I4 digite a seguinte função:
=SOMASE(C2:C151;I2;F2:F151)
8. a resposta é 36.105.698,00:
[ 17 ]Unidade 3 - Aula 3 - Vínculos e Funções de Análise
9. na célula I5 digite a seguinte função:
=MÉDIASE(C2:C151;I2;F2:F151)
10. a resposta é 975.829,68:
11. agora fique alterando a UF para outras e verificar os novos 
resultados das funções;
12. salve o arquivo.
Desafio1: na célula I7, utilizando a função CONT.SES(), informe 
o resultado da quantidade de uma UF específica e que ao mesmo tempo 
possui a SITUAÇÃO ATUAL igual a Mec.
Desafio2: na célula I8, defina a quantidade de processos que possua 
o ano 2003 após a barra, independente dos dois últimos números do 
processo.
[ 18 ] EXCEL AVANÇADO APLICADO AO CONTROLE
Síntese
Nesta aula, conhecemos o recurso de gerenciamento de vínculos 
externos que permite agilizar determinadas tarefas nas planilhas, 
fornecendo métodos que auxiliem na interligação de dados nas fórmulas.
Também executamos um trabalho utilizando a função de 
distribuição de frequência para análise de dados divididos em classes.
Por último, conhecemos e utilizamos um conjunto de funções de 
análise condicional, lógicas, de informação de conteúdo e, por último, 
executaremos uma operação de pesquisa vertical ou horizontal de dados 
em uma determinada matriz a fim de obter o resultado correspondente.
[ 19 ]Unidade 3 - Aula 3 - Vínculos e Funções de Análise
Referências bibliográficas 
Microsoft. Serviço de Suporte ao Excel 2007. Criar uma referência 
externa (link) para um intervalo de células em outra pasta de trabalho. 
Disponível em: <http://office.microsoft.com/pt-br/excel-help/criar-
uma-referencia-externa-link-para-um-intervalo-de-celulas-em-outra-pasta-de-trabalho-HP010102338.aspx>. Acesso em 08 de março de 
2012.
Wikipédia. Frequência Relativa. Disponível em: <http://pt.wikipedia.
org/wiki/Frequencia_relativa>. Acesso em 08 de março de 2012.
Aula 4 
Funções Lógicas, de Informação e de 
Pesquisa
EXCEL AVANÇADO APLICADO 
AO CONTROLE
Unidade 3 
Manipulação dos Dados
Instituto Serzedello Corrêa
RESPONSABILIDADE PELO CONTEÚDO
Tribunal de Contas da União
Secretaria Geral da Presidência
Instituto Serzedello Corrêa
2ª Diretoria de Desenvolvimento de Competências 
Serviço de Planejamento e Projetos Educacionais
SUPERVISÃO
Pedro Koshino
CONTEUDISTA
Helbert de Sousa Arruda
TRATAMENTO PEDAGÓGICO
Flávio Sposto Pompeo
RESPONSABILIDADE EDITORIAL
Tribunal de Contas da União
Secretaria Geral da Presidência
Instituto Serzedello Corrêa
Centro de Documentação
Editora do TCU
PROJETO GRÁFICO
Ismael Soares Miguel
Paulo Prudêncio Soares Brandão Filho
Vivian Campelo Fernandes
DIAGRAMAÇÃO
Vanessa Vieira
Brasil. Tribunal de Contas da União.
Excel Avançado Aplicado ao Controle / Tribunal de Contas da União ; 
conteudista: Helbert de Sousa Arruda. – Brasília : TCU, Instituto Serzedello 
Corrêa, 2012.
17 p. : il.
Conteúdo: Unidade 3: Manipulação dos dados. Aula 4: Funções lógicas, de 
informação e de pesquisa.
Curso realizado no período de 22/05 a 19/06/2012 no Ambiente
Virtual de Educação Corporativa do Tribunal de Contas da União.
1. Importação de dados. 2. Normalização de dados. 3. Manipulação de 
dados. 4. Análise de dados. I. Título.
Ficha catalográfica elaborada pela Biblioteca Ministro Ruben Rosa
© Copyright 2012, Tribunal de Contas de União 
<www.tcu.gov.br>
Permite-se a reprodução desta publicação, 
em parte ou no todo, sem alteração do conteúdo, 
desde que citada a fonte e sem fins comerciais.
[ 3 ]Unidade 3 - Aula 4 - Funções Lógicas, de Informação e de Pesquisa
Aula 4 – Funções lógicas, de informação e de pesquisa
Como trabalhar com a função condicional?
Quais as funções lógicas e para que servem?
Como utilizar as funções de informação de conteúdo?
Como trabalhar com as funções de pesquisa?
Iniciamos nossos estudos pela apresentação da função condicional, 
que em conjunto, com as funções lógicas, também vistas nesta aula, 
ampliam as possibilidades fornecidas pelas fórmulas do Excel 2007. 
Isso se deve ao fato de que tais funções permitem o controle sobre os 
resultados, aplicando uma resposta para a situação verdadeira e outra 
para a falsa.
Ainda nesta aula, apresentaremos funções que forneçam, por meio 
de informações de conteúdo, a possibilidade de o usuário tratar o dado de 
uma maneira mais direta. E para completar, serão evidenciadas funções 
que promovam a pesquisa e o retorno de dados, mediante critérios de 
referência, previamente informados.
Para facilitar o estudo, esta aula está organizada da seguinte forma:
Para facilitar o estudo, esta aula está organizada da seguinte forma:
Ao final desta aula, esperamos que você tenha condições de:
•	 conhecer e aplicar a função condicional;
•	 saber como utilizar as funções lógicas e de informação de 
conteúdo;
•	 trabalhar com as funções de pesquisa e referência;
Pronto para começar? Então, vamos!
Aula 4 – Funções lógicas, de informação e de pesquisa �����������������������������3
1. Função condicional ���������������������������������������������������������������������������������������������������4
2. Funções lógicas ������������������������������������������������������������������������������������������������������������7
3. Funções de informação ��������������������������������������������������������������������������������������������9
4. Funções de pesquisa e referência ������������������������������������������������������������������ 11
Síntese �������������������������������������������������������������������������������������������������������������������������������� 16
Referências bibliográficas ����������������������������������������������������������������������������������� 17
[ 4 ] EXCEL AVANÇADO APLICADO AO CONTROLE
1. Função condicional
O teste lógico de uma condição desencadeia, basicamente, um 
resultado verdadeiro ou falso. Para aplicação desta tarefa, o Excel 2007 
possui a função SE que é utilizada para retornar um determinado 
resultado mediante um teste lógico referente ao valor contido em uma 
referência qualquer, seja uma célula, um intervalo, um texto entre outras.
=SE()
A função SE tem como objetivo retornar a um valor caso uma 
condição especificada avaliar como verdadeira e outro valor avaliar 
como Falso.
Sintaxe: =SE(teste_lógico;caso_verdadeiro;caso_falso)
Nessa sintaxe, os argumentos pertencentes à função têm os 
seguintes significados:
•	 Teste_lógico: este argumento verifica a condição estabelecida e 
que será testada pela função.
•	 Caso_verdadeiro: este argumento apresenta um resultado caso 
a condição estabelecida seja atendida.
•	 Caso_falso: este argumento apresenta um resultado caso a 
condição estabelecida não seja atendida.
Eis um exemplo prático:
Em uma planilha de pagamento, caso um vendedor atinja a meta 
de venda do mês, que é 25.000,000, este deverá ganhar 10% de Comissão 
sobre o valor de suas vendas no mês, do contrário ganhará apenas 5%.
Como este problema seria traduzido para o contexto do Excel 
2007? Vamos ver?!
[ 5 ]Unidade 3 - Aula 4 - Funções Lógicas, de Informação e de Pesquisa
Assim sendo o resultado (na célula D3) para o primeiro vendedor 
seria de 10% de comissão sobre a venda mensal, pois esse ultrapassou 
a meta estabelecida de 25.000,00, ou seja, a comissão deverá ser de 
R$ 2.650,00:
Agora, copie a fórmula para as demais linhas e o Excel 2007 se 
encarregará de verificar quais vendedores receberão 10% de comissão e 
quais receberão somente 5%.
O exemplo acima é uma típica situação que pode ser resolvida pelo 
Excel 2007 por intermédio da função SE. 
DICA
Caso a situação a ser resolvida envolva múltiplas condições, o Excel 2007 permitirá que 
seja feito um encadeamento de funções SE para possibilitar mais opções de resultado. A 
sintaxe ficará assim:
=SE(teste1;verdad1;SE(teste2;verdad2;falso))
Para aplicar o conhecimento adquirido nesta função, execute os 
passos a seguir:
[ 6 ] EXCEL AVANÇADO APLICADO AO CONTROLE
Exercício: Módulo03_Aula04_Exe01
1. Abra o programa Excel 2007;
2. abra o arquivo Funcoes_Logicas.xlsx que se encontra na pasta 
Meus Documentos;
Problema: Suponha que a Ação (coluna F) a ser inserida deva ser o 
termo “Análise” se o ano da DataFinalSancao (coluna B) for igual a 2012 
e “Descarte” caso seja outro ano qualquer.
Utilizaremos a função SE para realizar esta tarefa. Vamos lá!
3. posicione o cursor na célula F2 e digite a seguinte expressão:
=SE(ANO(B2)=2012;”Análise”;”Descarte”)
4. ao pressionar ENTER, observe que o primeiro resultado é o 
termo Análise pois o ano desta primeira data é igual a 2012;
5. Copie esta fórmula até a célula F101 para ver como o Excel 2007 
se comporta com as demais células desta coluna;
6. salve o arquivo.
[ 7 ]Unidade 3 - Aula 4 - Funções Lógicas, de Informação e de Pesquisa
2. Funções lógicas
As funções lógicas são o meio utilizado no Excel 2007 para que 
condições sejam testadas e, ao final, retornem verdadeiro se essas 
atenderem e falso se não atenderem.
Basicamente, as expressões podem conter situações cumulativas 
ou alternativas, sendo que para cada uma delas há uma resposta 
diferente.
Na lógica booleana, as situações são testadas e dependendo 
das expressões, resultam em valores verdadeiros ou falsos. Vamos 
conhecer uma tabela que exemplifica estes casos:
Valor1 Valor2 E OU
V V V V
V F F V
F V F V
F F F F
Resumo: para que a expressão lógica E retorne verdadeiro, é 
necessário que os valores Valor1 e Valor2 sejam verdadeiros, caso 
contrárioretorna falso. E para a expressão lógica OU, basta que pelo 
menos um dos valores, Valor1 ou Valor2, seja verdadeiro para que 
o resultado seja verdadeiro; somente se os dois forem falsos que a 
expressão OU retorna falso.
No Excel 2007, existem duas funções (na verdade são três, pois 
existe a função de negação, denominada NÃO) que são utilizadas 
para gerar estas aplicações. Vamos a elas!
=E()
Esta função retorna verdadeira somente se todos os argumentos 
forem verdadeiros; retornará falsa se um ou mais argumentos forem 
falsos.
Sintaxe: =E(lógico1;[lógico2]; [lógico3];...)
=OU()
Esta função retorna verdadeira se qualquer um dos argumentos 
for verdadeiros; ela retorna falsa somente se todos os argumentos 
forem falsos.
[ 8 ] EXCEL AVANÇADO APLICADO AO CONTROLE
Sintaxe: =OU(lógico1;[lógico2]; [lógico3];...)
=NÃO()
Esta função inverte o valor do argumento. Usa-se NÃO() 
quando quiser ter certeza de que um valor não é igual a outro valor 
determinado.
Sintaxe: =NÃO(lógico)
Agora, exercitaremos estas funções!
Exercício: Módulo03_Aula04_Exe02
1. Abra o programa Excel 2007;
2. caso não esteja aberto, abra o arquivo Funcoes_Logicas.xlsx 
utilizado no exercício anterior.
Agora, definiremos a prioridade (coluna G).
Problema: Se a quantidade de dias entre a DataFinalSancao e 
DataInicSancao for maior ou igual a 100 dias e que ao mesmo tempo o 
TipoSancao for igual a “Inidônea”, deve-se definir o termo “Alta” , caso 
contrário “Baixa”.
Utilizaremos a função SE conjugada com a função E. Vamos lá!
Para facilitar a análise da diferença de dias entre as duas datas, 
criaremos uma coluna para inserir estas quantidades.
3. Posicione o cursor na célula H1, digite a palavra “Dias” e na 
célula H2 digite a fórmula B2-A2. Ao final, copie esta fórmula 
até a célula H101;
Atenção!
[ 9 ]Unidade 3 - Aula 4 - Funções Lógicas, de Informação e de Pesquisa
4. agora na célula G2 digite a seguinte expressão:
=SE(E(B2-A2>=1000;C2=”Inidônea”);”Alta”;”Baixa”)
5. pressione ENTER e em seguida copie esta célula até o endereço 
G101 para verificar como os outros resultados se comportam;
6. salve o arquivo.
Desafio: na coluna I, com a função NÃO, defina uma negativa 
para a expressão criada na coluna G, ou seja, as células que contenham a 
palavra Alta passam a conter a palavra Baixa e vice-versa.
3. Funções de informação
No Excel 2007, este grupo de funções, chamado coletivamente de 
grupo de funções É, verifica o valor especificado e retorna verdadeiro ou 
falso, dependendo do resultado.
Essas funções geralmente são utilizadas em conjunto com funções 
condicionais para que os resultados da expressão sejam manipuláveis, ou 
seja, as funções em si, retornem apenas o texto “verdadeiro” ou “falso”, e 
se forem utilizadas em conjunto com a função SE passam a produzir um 
resultado definido pelo usuário.
Para estas operações de informação, o Excel 2007 possui algumas 
funções que permitam ao usuário efetuar tais operações. São elas:
=ÉERROS(valor)
Esta função produz um resultado verdadeiro se a expressão testada 
retornar a um erro qualquer, quer seja de referência, lógico, divisão por 
zero etc. A função SEERRO permite avaliar a expressão e se o resultado 
for um erro, ela permite definir o que deverá ser feito.
Sintaxe: =ÉERROS()
=ÉCÉL.VAZIA(valor)
A função ÉCÉL.VAZIA retorna o valor lógico verdadeiro se o argumento 
de valor for uma referência a uma célula vazia; caso contrário, ele retorna falso.
Sintaxe: =ÉCÉL.VAZIA()
[ 10 ] EXCEL AVANÇADO APLICADO AO CONTROLE
Exercício: Módulo03_Aula04_Exe03
Agora, aprenderemos a trabalhar com estas funções.
1. Caso o programa Excel 2007 não esteja aberto, abra-o;
2. abra o arquivo Funcoes_Infos.xlsx presente na pasta Meus 
Documentos;
Para a coluna LIBERAÇÃO deve-se inserir o termo “Pendência” se 
a célula do CNPJ estiver vazia, do contrário deverá ser inserido o termo 
“Imediata”.
3. coloque o cursor na célula D2 e digite a seguinte expressão:
4. pressione ENTER e, em seguida, copie esta fórmula para as 
demais linhas desta coluna até a célula D71;
Para a coluna N_PARCELAS deverá ser inserido o número 3 se a 
célula correspondente constar o conteúdo “Imediata” e para as células 
contendo o termo “Pendência” deverá ser inserido o número 0 (zero).
5. posicione o cursor na célula E2, digite a seguinte expressão e, 
em seguida, copie para as demais células da coluna E:
=SE(D2=”Imediata”;3;0)
Para a coluna VALOR_PARC1 deve-se dividir o valor do convênio 
pelo número de parcelas encontrado na fórmula anterior.
[ 11 ]Unidade 3 - Aula 4 - Funções Lógicas, de Informação e de Pesquisa
Ao copiar esta expressão para o restante da coluna, veremos que em 
algumas linhas aparecerão a mensagem de erro #DIV/0! (não existe valor 
divisível por zero), que na realidade informa apenas uma inconsistência 
na fórmula.;
Como podemos minimizar a apresentação desta expressão de erro 
para tornar a planilha mais elegante? Vamos ver!!
6. Retorne à célula F2 e altere a expressão para a seguinte:
=SE(ÉERROS(C2/E2);0;C2/E2)
Dessa maneira o erro é tratado. 
7. copie novamente esta expressão para as demais células da 
coluna;
8. salve o arquivo.
4. Funções de pesquisa e referência
Este grupo de funções localiza um valor na primeira coluna/linha 
de uma matriz de tabela e retorna um valor na mesma linha/coluna de 
outra linha/coluna na matriz da tabela.
A letra V em PROCV significa vertical, ou seja, a pesquisa é realizada 
no sentido vertical e o resultado é dado pela coluna correspodente. 
Utiliza-se a função PROCV quando os valores da comparação estiverem 
localizados em uma coluna à esquerda dos dados que o usuário deseja 
encontrar.
Já a função PROCH, sendo o H de horizontal, realiza a pesquisa 
no sentido horizontal e seu resultado é dado pela linha correspodente.
Observe a ilustração do funcionamento dessas duas funções:
[ 12 ] EXCEL AVANÇADO APLICADO AO CONTROLE
PROCV: (pesquisa verticalmente e retorna a coluna 
correspondente)
PROCH: (pesquisa horizontalmente e retorna a linha 
correspondente)
Observe as sintaxes das funções:
=PROCV(valor_proc, matriz, núm_coluna, [proc_intervalo])
=PROCH(valor_proc, matriz, núm_linha, [proc_intervalo])
[ 13 ]Unidade 3 - Aula 4 - Funções Lógicas, de Informação e de Pesquisa
ATENÇÃO
Se o argumento proc_intervalo for definido como verdadeiro, ou for omitido, os valores 
na primeira coluna de matriz deverão ser colocados em ordem ascendente; do contrário, 
PROCV poderá não retornar o valor correto.
A seguir, realizaremos um exercício que permitirá utilizar estes 
recursos de pesquisa na planilha.
Exercício: Módulo03_Aula04_Exe04
Aprenderemos criar um mecanismo de pesquisa de dados com a 
função PROCV.
1. Caso o programa Excel 2007 não esteja aberto, abra-o;
2. abra o arquivo Convenios_Procv.xlsx presente na pasta Meus 
Documentos;
Observe que esta planilha possui uma lista com vários dados a 
partir da linha 5. Esta vai ser a nossa matriz de dados.
3. posicione o cursor na célula A2 e digite o número de convênio 
319910, somente para termos um resultado inicial;
4. digite, na célula B2 a seguinte expressão:
=PROCV(A2;A6:G105;2;FALSO)
•	 A2: valor que está sendo procurado;
•	 A6:G105: intervalo da matriz onde os dados são pesquisados;
•	 2: em relação à matriz, deseja-se a 2ª coluna como resposta;
•	 FALSO: correspondência exata do valor procurado.
5. observe o resultado SÃO PAULO que é realmente o Estado 
correspondente ao convênio de número 319910;
[ 14 ] EXCEL AVANÇADO APLICADO AO CONTROLE
No entanto, temos um inconveniente em nossa pesquisa. Se 
digitarmos um número de convênio não existente, o Excel 2007 retornará 
uma mensagem de erro #N/D (Não disponível).
Como corrigiremos este problema? Alteraremos a expressão da 
seguinte maneira:=SEERRO(PROCV(A2;A6:G105;2;FALSO);”Não Encontrado”)
6. digite um número de convênio não existente para verificar o 
resultado;
Bom, como aproveitaremos esta primeira expressão e copiaremos 
para as demais células da linha 2, a fim de configurar algumas referências 
absolutas ($). Vamos fazer isso!
7. Altere a expressão da célula B2 para a seguinte forma:
=SEERRO(PROCV($A$2;$A$6:$G$105;2;FALSO);”Não 
Encontrado”)
8. após alterada a expressão, copie-a até a célula G2;
Não se preocupe se não foi apresentado o estado SÃO PAULO em 
todas as células, pois temos que trocar a referência da coluna da matriz 
em cada fórmula.
[ 15 ]Unidade 3 - Aula 4 - Funções Lógicas, de Informação e de Pesquisa
9. Pule para a célula C2 e altere o valor 2 (terceiro parâmetro da 
função) para 3; na célula D2 troque-o para 4, na E2 para 5, na 
F2 para 6 e por último na G2 para 7;
Pronto! Agora nosso mecanismo de pesquisa ficou pronto. 
10. Faça o teste com números válidos e inválidos de convênio;
11. ao final, salve o arquivo.
[ 16 ] EXCEL AVANÇADO APLICADO AO CONTROLE
Síntese
Nesta aula, aprendemos a trabalhar com as funções lógicas do 
Excel 2007 e obtivemos conhecimento de como ampliar as possibilidades 
fornecidas pelas fórmulas por meio da inserção da utilização de elementos 
condicionais.
Também conhecemos funções que permitem analisar conteúdos 
resultantes de células e de expressões e, assim, efetuar determinadas 
operações conforme estas respostas.
Por último, aprendemos a criar mecanismos de pesquisa mediante 
o uso de funções que realizam operações de pesquisa vertical ou 
horizontal de dados em uma determinada matriz.
[ 17 ]Unidade 3 - Aula 4 - Funções Lógicas, de Informação e de Pesquisa
Referências bibliográficas 
Microsoft. Serviço de Suporte ao Excel 2007. Funções lógicas. Disponível 
em: <http://office.microsoft.com/pt-br/excel-help/funcoes-logicas-
HP010079187.aspx>. Acesso em 09 de março de 2012.
Microsoft. Serviço de Suporte ao Excel 2007. Pesquisar valores em um 
intervalo. Disponível em: <http://office.microsoft.com/pt-br/excel-
help/pesquisar-valores-em-um-intervalo-HP005228952.aspx?CTT=3>. 
Acesso em 09 de março de 2012.
Aula 1 
Ferramentas de Agrupamento de 
Dados
EXCEL AVANÇADO APLICADO 
AO CONTROLE
Unidade 4 
Análise dos Dados
Instituto Serzedello Corrêa
RESPONSABILIDADE PELO CONTEÚDO
Tribunal de Contas da União
Secretaria Geral da Presidência
Instituto Serzedello Corrêa
2ª Diretoria de Desenvolvimento de Competências 
Serviço de Planejamento e Projetos Educacionais
SUPERVISÃO
Pedro Koshino
CONTEUDISTA
Helbert de Sousa Arruda
TRATAMENTO PEDAGÓGICO
Flávio Sposto Pompeo
RESPONSABILIDADE EDITORIAL
Tribunal de Contas da União
Secretaria Geral da Presidência
Instituto Serzedello Corrêa
Centro de Documentação
Editora do TCU
PROJETO GRÁFICO
Ismael Soares Miguel
Paulo Prudêncio Soares Brandão Filho
Vivian Campelo Fernandes
DIAGRAMAÇÃO
Vanessa Vieira
Brasil. Tribunal de Contas da União.
Excel Avançado Aplicado ao Controle / Tribunal de Contas da União ; 
conteudista: Helbert de Sousa Arruda. – Brasília : TCU, Instituto Serzedello 
Corrêa, 2012.
18 p. : il.
Conteúdo: Unidade 4: Análise dos dados. Aula 1: Ferramentas de 
agrupamento de dados.
Curso realizado no período de 22/05 a 19/06/2012 no Ambiente
Virtual de Educação Corporativa do Tribunal de Contas da União.
1. Importação de dados. 2. Normalização de dados. 3. Manipulação de 
dados. 4. Análise de dados. I. Título.
Ficha catalográfica elaborada pela Biblioteca Ministro Ruben Rosa
© Copyright 2012, Tribunal de Contas de União 
<www.tcu.gov.br>
Permite-se a reprodução desta publicação, 
em parte ou no todo, sem alteração do conteúdo, 
desde que citada a fonte e sem fins comerciais.
[ 3 ]Unidade 4 - Aula 1 - Ferramentas de Agrupamento de Dados
Aula 1 – Ferramentas de agrupamento de dados
Como executar as operações de consolidação de dados?
 Como utilizar os filtros de dados?
Como proceder às operações com subtotais?
Iniciaremos nossos estudos pela apresentação das operações de 
consolidação de dados na planilha. Também serão exibidas algumas 
operações de manipulação com filtragens de dados presentes na planilha. 
E para finalizar este estudo, mostraremos como proceder às rotinas de 
subtotalizações ofertadas pelo Excel 2007.
Para facilitar o estudo, esta aula está organizada da seguinte forma:
Ao final desta aula, esperamos que você tenha condições de:
•	 utilizar os recursos de consolidação de dados na planilha;
•	 conhecer e utilizar os mecanismos de filtragem de dados;
•	 executar o processo de subtotalizações de dados na planilha.
Pronto para começar? Então, vamos!
Aula 1 – Ferramentas de agrupamento de dados ������������������������������������������3
1. Consolidação de dados ��������������������������������������������������������������������������������������������4
2. Filtros de dados ����������������������������������������������������������������������������������������������������������8
3. Subtotais����������������������������������������������������������������������������������������������������������������������� 13
Síntese �������������������������������������������������������������������������������������������������������������������������������� 17
Referências bibliográficas ����������������������������������������������������������������������������������� 18
[ 4 ] EXCEL AVANÇADO APLICADO AO CONTROLE
1. Consolidação de dados
Em termos práticos, consolidação é uma forma de convergir 
vários elementos em um único local. A partir deste conceito, o Excel 
2007 possui uma ferramenta com o propósito de unir dados de várias 
planilhas em um só, interligando-as ou não.
Sendo assim, para resumir e relatar os resultados de planilhas 
separadas é possível consolidar dados de cada planilha separada em 
uma planilha principal. Estas planilhas podem estar na mesma pasta de 
trabalho que a planilha principal ou em outras pastas de trabalho. Ao 
consolidar dados, o usuário monta os dados para que possa atualizá-los 
e agregá-los com mais facilidade, em uma base regular ou ad hoc.
Se os dados a serem 
consolidados estiverem na 
mesma célula em outras 
planilhas, pode-se digitar 
uma fórmula com uma 
referência 3D que usa uma 
referência a um intervalo 
de nomes de planilha.
Exemplo: 
=SOMA(Jan:Jun!B4)
Por exemplo, se tiver uma planilha de valores de despesa de cada 
um dos escritórios regionais, é possível usar uma consolidação para 
combinar estes valores em uma planilha de despesas corporativas. Essa 
planilha principal pode conter totais e médias de venda e os produtos 
mais vendidos em toda a empresa.
Para exemplificar esta operação de consolidação de dados, execute 
o próximo exercício proposto.
Vamos iniciar!
[ 5 ]Unidade 4 - Aula 1 - Ferramentas de Agrupamento de Dados
Exercício: Módulo04_Aula01_Exe01
1. Abra o programa Excel 2007;
2. abra o arquivo Consolidacao.xlsx que se encontra na pasta 
Meus Documentos do seu computador;
Observe que este arquivo possui valores em três planilhas (anos 
2009, 2010 e 2011) para os 12 meses do ano e com todos os municípios 
do Brasil.
Como podemos unir o conteúdo destas três planilhas sem a 
necessidade de criar fórmulas para isso. A resposta é Consolidação.
Vamos executá-la!
3. Clique no ícone de criação de nova planilha (ao lado do nome 
da planilha 2011);
4. nesta nova planilha, clique na célula A1;
5. selecione a opção Consolidar no grupo Ferramentas de Dados 
da guia Dados;
[ 6 ] EXCEL AVANÇADO APLICADO AO CONTROLE
6. observe que é aberta a caixa de diálogo Consolidar;
7. você pode escolher qualquer uma das funções na caixa Função 
para agrupar os dados nesta planilha;
8. apesar de existirem várias, vamos trabalhar mesmo com a 
função SOMA, entãoescolha na caixa Função;
9. defina os intervalos das três planilhas que comporão a 
consolidação;
10. clique no ícone com a seta vermelha (ao lado do botão 
Procurar) para que a caixa de diálogo se retraia permitindo que 
escolhamos o primeiro intervalo;
[ 7 ]Unidade 4 - Aula 1 - Ferramentas de Agrupamento de Dados
11. pegue o mouse, clique na planilha 2009 e selecione todos os 
dados desta planilha a partir da coluna B, ou seja, o intervalo 
B1:N5562;
12. clique no ícone para expandir a caixa de diálogo novamente;
13. de volta à caixa Consolidar; clique no botão adicionar para 
armazenar o primeiro intervalo;
14. clique na guia da planilha 2010 e depois em Adicionar;
15. clique na guia da planilha 2011 e, em seguida Adicionar para 
guardar o terceiro intervalo;
16. para que a planilha de consolidação também possua os mesmos 
cabeçalhos de linha e coluna das planilhas dos anos, basta ativar 
as caixas de verificação Linha Superior e Coluna Esquerda;
Se quiser que os dados consolidados possuam vínculo com as 
planilhas de origem, basta ativar a opção Criar Vínculo com Dados de 
Origem.
17. ative esta opção e clique em OK;
[ 8 ] EXCEL AVANÇADO APLICADO AO CONTROLE
18. espere o resultado da consolidação e observe que o Excel 2007 
somou os valores das três planilhas permitindo que ainda sejam 
vistos os valores originais por intermédio de cliques nas alças 
de expansão (sinal de mais);
19. crie novas planilhas e realize outras consolidações utilizando 
somente alguns meses, ou alguns municípios entre outras;
20. ao final, salve o arquivo.
2. Filtros de dados
Planilhas de dados tendem a crescer de forma considerável e 
de certa forma passam a oferecer um maior trabalho no momento 
da manipulação e análise de dados. Por esta razão, o Excel 2007 
possui uma ferramenta que auxilia o usuário a destacar (filtrar) 
determinados dados para facilitar sua manipulação.
[ 9 ]Unidade 4 - Aula 1 - Ferramentas de Agrupamento de Dados
Mediante critérios pré-estabelecidos, as linhas exibem apenas 
os dados que atendem às condições especificadas. Depois que os 
dados são filtrados, pode-se copiar, localizar, editar, formatar, fazer 
gráfico e imprimir o subconjunto de dados filtrados sem reorganizá-
los nem movê-los.
A ferramenta permite que se filtre por mais de uma coluna. 
Os filtros são cumulativos, o que significa que cada filtro adicional 
baseia-se no filtro atual e ainda reduz o subconjunto de dados.
As opções de filtragem são
•	 filtrar para o menor ou maior número;
•	 mostrar/filtrar um intervalo para linhas que contenham 
texto específico;
•	 mostrar/filtrar células vazias ou não vazias;
•	 mostrar/filtrar números maiores ou menores que outro 
número;
•	 mostrar/filtrar um número igual ou diferente de outro 
número;
•	 mostrar/filtrar o início ou o fim de uma sequência de texto;
•	 mostrar/filtrar o número máximo ou mínimo por 
percentual.
No momento da filtragem de dados, linhas inteiras são ocultadas 
quando determinados valores de uma ou mais colunas não atendem aos 
critérios de filtragem. É possível filtrar por diversos conteúdos diferentes, 
como: valores numéricos ou texto, cor, células que tenham formatação 
de cores aplicada ao plano de fundo ou ao texto dessas células.
Agora vamos conhecer na prática o uso dessa ferramenta de 
filtragem de dados!?!? Vamos lá!!
Somente as 1000 primeiras 
entradas exclusivas na lista 
aparecem quando se clica 
na seta seta de campo.
[ 10 ] EXCEL AVANÇADO APLICADO AO CONTROLE
Exercício: Módulo04_Aula01_Exe02
1. Abra o programa Excel 2007;
2. abra o arquivo Filtros.xlsx presente na pasta Meus Documentos;
3. clique no botão Filtro no grupo Classificar e Filtrar presente 
na guia Dados;
Atenção!
4. observe que ao lado de cada título de coluna surgiu uma seta, 
clique na seta da coluna UF;
5. clique na opção Selecionar Tudo para desmarcar todas as UFs 
e em seguida marque somente as UFs da região Sudeste (ES, 
MG, RJ e SP);
6. clique em OK e observe que a listagem agora mostra apenas 
linhas destes quatro estados;
7. clique na seta da coluna SITUAÇÃO ATUAL e desmarque as 
opções Arquivado e Mec;
[ 11 ]Unidade 4 - Aula 1 - Ferramentas de Agrupamento de Dados
8. clique em OK e observe que a listagem agora mostra apenas 
linhas que possuam o processo na situação Em Análise;
9. por último, vá até a seta da coluna VALOR e escolha a opção 
Filtros de Número;
10. no menu ao lado, escolha a opção Está Entre;
11. selecione as opções conforme a caixa abaixo:
12. clique em OK e observe como a listagem mostra apenas registros 
que atendem os três critérios especificados;
13. para desfazer os três filtros, clique no botão Limpar presente 
no grupo Classificar e Filtrar;
[ 12 ] EXCEL AVANÇADO APLICADO AO CONTROLE
Agora efetuaremos mais uma filtragem. Dessa vez, filtraremos por 
uma parte do texto contido nas células. Vamos lá!
14. Clique na seta da coluna ENTIDADE e em seguida escolha a 
opção Filtro de Texto;
15. no submenu selecione a opção Contém;
16. na frente da caixa Contém escreva a palavra Beneficente e 
clique em OK;
17. observe que a listagem exibe apenas registros que possuam a 
palavra Beneficente;
18. para desfazer o recurso de filtro, clique novamente no botão 
Filtro e dessa forma as setas de todas as colunas desaparecem;
19. salve o arquivo.
[ 13 ]Unidade 4 - Aula 1 - Ferramentas de Agrupamento de Dados
3. Subtotais
Este comando fornece um subtotal em uma lista ou em um banco 
de dados. O comando, após acionado, insere linha de subtotais para 
qualquer coluna (campo).
De certa forma é mais fácil gerar uma lista com subtotais usando o 
comando e assim que esta lista de subtotais é criada, pode-se modificá-la 
editando a função SUBTOTAL().
Sintaxe: =SUBTOTAL(núm_função; ref1, ref2;...)
O parâmetro núm_função é um número de 1 a 11 (caso queira 
incluir valores ocultos) ou 101 a 111 (ignorando valores ocultos) que 
especifica qual função usar no cálculo de subtotais dentro de uma lista.
Caso o usuário queira editar a função inserida automaticamente 
pelo comando Formatar, basta conhecer os parâmetros da tabela abaixo 
para tal operação:
Núm_Função
(com inclusão de números 
ocultos)
Núm_Função
(sem inclusão de números ocultos)
FUNÇÃO
1 101 MÉDIA()
2 102 CONT.NÚM()
3 103 CONT.VALORES()
4 104 MÁXIMO()
5 105 MÍNIMO()
6 106 MULT()
7 107 DESVPAD()
8 108 DESVPADP()
9 109 SOMA()
10 110 VAR()
11 111 VARP()
[ 14 ] EXCEL AVANÇADO APLICADO AO CONTROLE
A função SUBTOTAL() ignora as linhas não incluídas no resultado 
de um filtro, independentemente de qual valor do parâmetro núm_
função seja utilizado.
IMPORTANTE
A função SUBTOTAL() foi projetada para trabalhar com colunas de dados ou intervalos 
verticais. Ela não foi projetada para linhas de dados nem intervalos horizontais. Por 
exemplo, quando o usuário subtotaliza um intervalo horizontal usando um núm_
função de 101 ou superior, como SUBTOTAL(109,D3:F3), ocultar uma coluna não 
afeta o subtotal. Porém, ocultar uma linha em um subtotal de um intervalo vertical 
afeta o subtotal.
Exercício: Módulo04_Aula01_Exe03
Agora, aprenderemos a trabalhar com a subtotalização mediante o 
comando e editar por intermédio da função:
1. Caso o programa Excel 2007 não esteja aberto, abra-o;
2. abra o arquivo Subtotais.xlsx presente na pasta Meus 
Documentos do seu computador;
Observe que esta planilha possui dados de vários municípios dos 
estados brasileiros para todos os meses do ano. Portanto, aplicaremos o 
recurso de subtotais para facilitar a análise dos dados.
3. como a planilha já se encontra classificada pela coluna UF, não 
há a necessidade de executar esta etapa. Então acesse a guia 
Dados, e no grupo Estrutura de Tópicos, escolha o botão 
Subtotal;
[ 15 ]Unidade4 - Aula 1 - Ferramentas de Agrupamento de Dados
4. surge a caixa de diálogo Subtotais;
5. escolha as seguintes opções nesta caixa:
•	 A cada alteração em: UF.
•	 Usar função: Soma.
•	 Adicionar subtotais a: marque a caixa de verificação de todos 
os estados.
As demais opções devem ser deixadas como estão.
6. clique em OK;
Observe que a planilha apresenta, à esquerda da tela, três níveis de 
visualização dos dados. Nível 1 para verificação do Total Geral, Nível 2 
para verificação das UFs e o Nível 3 para a verificação detalhada;
7. clique no nível 2 e verifique os totais de cada UF e o Total Geral;
8. observe que apareceu uma série de sinas de mais (+) na frente 
de cada UF. Ao clicar em algum deles, os respectivos dados 
daquela UF são apresentados;
[ 16 ] EXCEL AVANÇADO APLICADO AO CONTROLE
9. para recolher estes dados e retornar à visão anterior, basta clicar 
no sinal de menos (-) correspondente;
Agora, incluiremos um novo nível de subtotalização sem eliminar 
o já existente.
10. Clique novamente no botão Subtotal e na caixa de diálogo 
Subtotais execute as seguintes ações:
•	 A cada alteração em: UF.
•	 Usar função: Contagem.
•	 Adicionar subtotais a: marque apenas a caixa de verificação 
Município.
•	 Desmarque a opção Substituir Subtotais Atuais para que este 
novo nível seja adicionado ao painel de verificação.
11. clique no botão OK e observe que surge na tela o Nível 4 e ao 
mesmo tempo a visualização da quantidade de municípios de 
cada estado na coluna Município;
12. clicando no Nível 4 é possível verificar os municípios 
pertencentes a cada UF e logo abaixo o seu total e quantidade 
de municípios;
Agora, vamos desfazer os subtotais e voltar à planilha como a 
apresentada no início;
13. clique no botão Subtotal na guia Dados;
14. na caixa de diálogo Subtotais escolha o botão Remover Todos;
[ 17 ]Unidade 4 - Aula 1 - Ferramentas de Agrupamento de Dados
15. a planilha voltou ao estado original;
16. treine mais algumas situações e ao final salve o arquivo.
Síntese
Aprendemos, nesta aula, que é possível efetuar a consolidação de 
dados de várias planilhas, inserindo ou não a opção de vínculo.
Conhecemos o processo de filtragem de dados e as várias 
possibilidades de critérios de filtros.
Também, nesta aula, executamos a operação de subtotalização 
de dados, fazendo com que planilhas com muitos dados possam ser 
analisadas de forma mais sintética ou mais detalhada, conforme a 
escolha do usuário.
[ 18 ] EXCEL AVANÇADO APLICADO AO CONTROLE
Referências bibliográficas 
COX, F.; FRYE C.; LAMBERT, M.D; PREPPERNEAU, J.; MURRAY, K. 
Microsoft Office System 2007 – Passo a passo. Porto Alegre: Artmed, 
2008.
Microsoft. Serviço de Suporte ao Excel 2007. Consolidar dados em várias 
planilhas. Disponível em: <http://office.microsoft.com/pt-br/excel-help/
consolidar-dados-em-varias-planilhas-HP010095249.aspx>. Acesso em 
09 de março de 2012.
Microsoft. Serviço de Suporte ao Excel 2007. Subtotal. Disponível em: 
<http://office.microsoft.com/pt-br/excel-help/subtotal-HP005209288.
aspx>. Acesso em 13 de março de 2012.
Aula 2 
Bases de dados e Gráficos
EXCEL AVANÇADO APLICADO 
AO CONTROLE
Unidade 4 
Análise dos Dados
Instituto Serzedello Corrêa
RESPONSABILIDADE PELO CONTEÚDO
Tribunal de Contas da União
Secretaria Geral da Presidência
Instituto Serzedello Corrêa
2ª Diretoria de Desenvolvimento de Competências 
Serviço de Planejamento e Projetos Educacionais
SUPERVISÃO
Pedro Koshino
CONTEUDISTA
Helbert de Sousa Arruda
TRATAMENTO PEDAGÓGICO
Flávio Sposto Pompeo
RESPONSABILIDADE EDITORIAL
Tribunal de Contas da União
Secretaria Geral da Presidência
Instituto Serzedello Corrêa
Centro de Documentação
Editora do TCU
PROJETO GRÁFICO
Ismael Soares Miguel
Paulo Prudêncio Soares Brandão Filho
Vivian Campelo Fernandes
DIAGRAMAÇÃO
Vanessa Vieira
Brasil. Tribunal de Contas da União.
Excel Avançado Aplicado ao Controle / Tribunal de Contas da União ; 
conteudista: Helbert de Sousa Arruda. – Brasília : TCU, Instituto Serzedello 
Corrêa, 2012.
27 p. : il.
Conteúdo: Unidade 4: Análise dos dados. Aula 2: Bases de dados e gráficos.
Curso realizado no período de 22/05 a 19/06/2012 no Ambiente
Virtual de Educação Corporativa do Tribunal de Contas da União.
1. Importação de dados. 2. Normalização de dados. 3. Manipulação de 
dados. 4. Análise de dados. I. Título.
Ficha catalográfica elaborada pela Biblioteca Ministro Ruben Rosa
© Copyright 2012, Tribunal de Contas de União 
<www.tcu.gov.br>
Permite-se a reprodução desta publicação, 
em parte ou no todo, sem alteração do conteúdo, 
desde que citada a fonte e sem fins comerciais.
[ 3 ]Unidade 4 - Aula 2 - Bases de Dados e Gráficos
Aula 2 – Base de dados e gráficos
Como gerenciar bases de dados externas?
Qual a importância da demonstração gráfica?
Como trabalhar com gráficos?
Iniciamos nossos estudos pelas operações iniciais com as bases 
de dados externas. O Excel 2007 possui a flexibilidade de reconhecer 
vários tipos de dados tabulares, entre eles encontramos bancos de dados 
variados, tabelas, outros formatos de planilhas entre outros. Saber 
gerenciar esta tarefa de transferir esses dados para o formato do Excel 
2007 é de extrema importância, pois nem sempre nos depararemos com 
dados já no formato adequado.
Por outro lado, temos os gráficos que representam, de forma clara 
e direta, os dados numéricos inseridos em uma determinada planilha. 
Sendo assim, a exibição traz uma maneira rápida de realizar uma análise 
desses dados.
Para facilitar o estudo, esta aula está organizada da seguinte forma:
Ao final desta aula, esperamos que você tenha condições de:
•	 saber gerenciar dados de bases externas;
•	 transferir dados de outros formatos e manipulá-los no Excel 
2007;
•	 conceber e construir gráficos demonstrativos.
Pronto para começar? Então, vamos!
Aula 2 – Base de dados e gráficos ��������������������������������������������������������������������������3
1. Base de dados do Access ������������������������������������������������������������������������������������������4
2. Base de dados da web �����������������������������������������������������������������������������������������������8
3. Outras bases de dados ������������������������������������������������������������������������������������������ 11
4. Criação de gráficos ������������������������������������������������������������������������������������������������ 17
Síntese �������������������������������������������������������������������������������������������������������������������������������� 26
Referências bibliográficas ����������������������������������������������������������������������������������� 27
[ 4 ] EXCEL AVANÇADO APLICADO AO CONTROLE
1. Base de dados do Access
Frequentemente os usuários de computador desejam trabalhar 
com os dados do Access, porém em formato de planilha do Excel 2007, a 
fim de se beneficiar das vantagens que os recursos de análise e gráfico de 
dados oferecem, da flexibilidade no gerenciamento e layout dos dados 
ou das muitas funções não disponíveis no Access.
Dessa forma, a Microsoft disponibilizou diversas formas de 
transferir esses dados entre os dois programas. Vamos conhecer algumas:
•	 Transferência do Access para o Excel: podem-se copiar os 
dados da folha de dados do Access e colá-los em uma planilha 
do Excel, conectar-se a um banco de dados do Access a partir 
de uma planilha do Excel ou exportar os dados do Access para 
uma planilha do Excel.
•	 Transferência do Excel para o Access: podem-se copiar os 
dados de uma planilha do Excel e colá-los em uma folha de 
dados do Access, importar uma planilha do Excel para uma 
tabela do Access ou vincular uma planilha do Excel a partir de 
uma tabela do Access.
O Excel 2007 permite que sejarealizada uma conexão direta entre 
os dados do Access e a planilha, permitindo a interligação entre eles. Isto 
é possível devido à criação de um arquivo de conexão de dados do Office 
(.odc), para o banco de dados do Access e a consequente recuperação de 
todos os dados a partir de uma tabela ou consulta.
A maior vantagem em realizar a conexão aos dados do Access em 
vez de executar apenas a importação é que se torna possível analisar 
periodicamente esses dados no Excel 2007 sem a necessidade de copiar 
ou exportar repetidamente os dados do Access. Após efetuar a conexão 
aos dados, também se pode atualizar automaticamente as pastas de 
trabalho do Excel 2007 a partir do banco de dados original do Access 
sempre que este também for atualizado.
A principal forma de interligar estes dados entre os dois programas 
é por meio da opção Do Access, presente no grupo Obter Dados 
Externos, da guia Dados do Excel 2007.
Quando o usuário utiliza o 
Assistente para Exportação 
no Access, este pode 
exportar o objeto de banco 
de dados do Access da 
seguinte maneira: como 
uma tabela, consulta ou 
formulário, ou registros 
selecionados em um modo 
de exibição para uma 
planilha do Excel 2007.
[ 5 ]Unidade 4 - Aula 2 - Bases de Dados e Gráficos
Vamos descobrir como realizar esta tarefa!? Vamos lá!
Exercício: Módulo04_Aula02_Exe01
1. Abra o programa Excel 2007;
Nesta nova planilha, interligaremos uma tabela do banco de dados 
em Access chamado Northwind.accdb (banco de dados de teste que a 
Microsoft disponibiliza para os usuários);
2. acesse a guia Dados e clique no botão Do Access;
3. aponte para o arquivo Northwind.accdb que está presente na 
sua pasta Meus Documentos;
4. em seguida clique no botão Abrir;
5. na caixa de diálogo Selecionar Tabela, encontre a tabela 
chamada Clientes e clique no botão OK;
[ 6 ] EXCEL AVANÇADO APLICADO AO CONTROLE
6. na caixa de diálogo Importar Tabela, deixe selecionadas a 
opção Tabela e Na planilha Existente (célula =$A$1);
7. clique em OK;
8. agora salve o arquivo na pasta Meus Documentos com o nome 
Planilha_Access.xlsx;
Observe que na linha da Empresa C, consta o nome de Thomas 
Axen como Representante de Compras.
9. feche o arquivo da planilha;
10. abra o arquivo de banco de dados Northwind.accdb clicando 
duas vezes com o mouse sobre o nome do arquivo e pressionando 
a tecla SHIFT simultaneamente (este procedimento faz com 
que o banco de dados seja aberto no formato para edição);
11. habilite o conteúdo ativo;
[ 7 ]Unidade 4 - Aula 2 - Bases de Dados e Gráficos
12. na parte da esquerda desta tela encontra-se a lista de objetos 
do Access. Encontre a tabela chamada Clientes e dê um duplo 
clique para abri-la;
13. altere o nome de Thomas Axen para Monteiro Lobato;
14. feche o Access (não há a necessidade de salvar a alteração feita, 
pois esta é salva automaticamente);
15. abra novamente o Excel 2007 e, em seguida, o arquivo Planilha_
Access.xlsx;
Observe que na linha da Empresa C ainda consta o nome de 
Thomas Axen.
16. Acesse a guia Dados, e clique no botão Atualizar Tudo;
Pronto! A planilha atualizou-se com o banco de dados e agora 
estamos vendo o nome de Monteiro Lobato;
Viu como é fácil efetuar esta interligação entre planilhas Excel e 
bancos de dados Access?
Faça outras interligações entre planilhas e tabelas diferentes e 
obtenham mais experiência com este recurso de vínculos entre diferentes 
formatos de arquivos.
17. salve a planilha.
[ 8 ] EXCEL AVANÇADO APLICADO AO CONTROLE
2. Base de dados da web
Outro tipo de dado que o Excel 2007 permite conectar-se é o 
presente em páginas Web. Isto significa que é possível também criar 
ou executar uma consulta à Web para recuperar texto ou dados de 
uma página Web. 
Comumente, as páginas Web contêm dados que podem ser 
utilizados para análise pelo Excel 2007. Dependendo da situação 
desejada, o usuário pode também recuperar dados atualizáveis, ou 
seja, os dados no Excel estarão conectados automaticamente com os 
dados mais recentes da página Web, ou pode-se também recuperá-
los de uma página Web e mantê-los estáticos na planilha.
Para realizar a tarefa de obtenção de dados de páginas Web, 
o usuário deverá acessar a opção Da Web do grupo Obter Dados 
Externos, presente na guia Dados. 
DICA
Geralmente usa-se uma consulta à Web para recuperar dados atualizáveis 
armazenados na Intranet ou na Intranet, como uma única tabela, várias tabelas 
ou todo o texto de uma página da Web. Em seguida, realiza-se a análise dos dados 
utilizando os recursos existentes no Excel 2007.
As consultas à Web são especialmente úteis para recuperação de 
dados em tabelas ou áreas pré-formatadas. (As tabelas são definidas 
com a marca HTML <TABLE>. Em geral, as áreas pré-formatadas 
são definidas com a marca HTML <PRE>.) Os dados recuperados 
não contêm imagens, no formato .gif, por exemplo, e não incluem o 
conteúdo de scripts.
Vamos interligar dados de índice da bolsa de valores e cotação 
do dólar no portal MSN com nossa planilha de dados no Excel 2007.
[ 9 ]Unidade 4 - Aula 2 - Bases de Dados e Gráficos
Atenção!
Exercício: Módulo04_Aula01_Exe02
1. Abra o programa Excel 2007;
2. clique no botão Da Web, presente na guia Dados (grupo Obter 
Dados Externos);
3. acesse a seguinte página da internet na caixa de diálogo Nova 
Consulta à Web: 
br.msn.com
4. observe que na parte superior esquerda de algumas áreas são 
apresentadas algumas setas em um quadrado amarelo (esta 
seta indica que esta é uma área disponível para interligação de 
dados Web);
5. desça a barra de rolagem até que o quadro com o índice Ibovespa 
e a cotação de dólar e euro apareçam;
[ 10 ] EXCEL AVANÇADO APLICADO AO CONTROLE
6. observe a presença da seta no quadrado amarelo. Clique nesta 
seta para selecionar a área deste quadro; 
A seta do quadrado amarelo deu lugar à marca de “check” em 
um quadrado verde.
7. Clique no botão Importar;
8. na caixa de diálogo Importar Dados, escolha a célula A1 da 
planilha existente e clique em OK;
9. aguarde alguns segundo e ao final observe que os dados foram 
copiados para a planilha; 
[ 11 ]Unidade 4 - Aula 2 - Bases de Dados e Gráficos
Agora falta configurar alguns itens de atualização.
10. Clique no botão Propriedades;
11. faça os devidos ajustes de atualização, layout e formatação e por 
fim clique em OK; 
12. ao final, salve o arquivo com o nome Mod04_Aula02_Exe02.
xlsx. 
3. Outras bases de dados
Utilizando o Excel 2007 é possível também obter dados de outros 
tipos de base de dados, como por exemplo, do SQL Server, Microsoft 
Query, XML, serviços de análise entre outros. Vamos conhecer um de 
cada tipo de base de dados suportada pelo Excel 2007.
Do SQL Server
O SQL Server é um sistema gerenciador de banco de dados 
relacional completo projetado pela Microsoft para fornecer soluções de 
dados corporativos que requerem certo desempenho, disponibilidade, 
escalabilidade e segurança ideais. No Excel 2007, torna-se possível a 
conexão a um banco de dados do SQL Server. Esta opção encontra-se 
[ 12 ] EXCEL AVANÇADO APLICADO AO CONTROLE
na guia Dados, no grupo Obter Dados Externos, e em seguida na opção 
De Outras Fontes e por último em Do SQL Server.
Ao conectar-se a um banco de dados do SQL Server, abre-se o 
Assistente para Conexão de Dados exibindo as etapas de conexão. São 
elas:
•	 Conexão ao servidor do banco de dados: nesta etapa deverá ser 
definido o servidor e a maneira como logon deve ser realizado 
no servidor de banco de dados.
•	 Seleção do banco de dados e da tabela: deve-se especificar o 
banco de dados e a tabela ou consulta.
[ 13 ]Unidade 4 - Aula 2 - Bases de Dados e Gráficos
•	 Gravação do arquivo de dados e a conexão: nesta etapa 
especifica-se e descreve-se o arquivode conexão e realiza-se a 
pesquisa de frases para localizar o arquivo.
Da importação de dados XML
O Excel 2007 possui compatibilidade com dados importados de 
estruturas XML (Linguagem de Marcação Extensível). Estes dados podem 
ser advindos de outros bancos de dados e aplicativos, o mapeamento 
dos elementos XML de um esquema XML para células da planilha e a 
exportação dos dados XML revisados para interação com outros bancos 
de dados e aplicativos. 
A seguir, são relacionados os principais cenários que os recursos 
XML podem resolver:
•	 estender a funcionalidade dos modelos do Excel existentes 
mapeando elementos XML para células existentes. Isso facilitará 
a obtenção de dados XML para dentro e para fora dos modelos 
sem precisar recriá-los;
•	 usar os dados XML como entrada para os modelos de cálculo 
existentes mapeando elementos XML para planilhas existentes;
•	 importar arquivos de dados XML para uma nova pasta de 
trabalho;
•	 importar dados XML de um serviço Web para a planilha do 
Excel;
•	 exportar dados das células mapeadas para arquivos de dados 
XML independente de outros dados da pasta de trabalho.
[ 14 ] EXCEL AVANÇADO APLICADO AO CONTROLE
IMPORTANTE
Ao importar dados XML para uma planilha, devemos lembrar que há diversos métodos 
e comandos que podem ser utilizados na importação dos dados XML.
Do Microsoft Query
O Microsoft Query foi elaborado para conectar-se a fontes de dados 
externas, selecionar dados dessas fontes, importar os dados para uma 
planilha e atualizá-los, conforme o necessário para manter os dados da 
planilha sincronizados com os dados nas fontes externas.
É possível utilizar o Microsoft Query para recuperar dados de fontes 
externas, arquivos e dados de bancos de dados corporativos, fornecendo 
a comodidade de não ser mais necessário redigitar os dados a serem 
analisados no Excel 2007. Com ele, pode-se atualizar automaticamente 
os resumos e relatórios a partir do banco de dados original usado como 
fonte sempre que o banco de dados também for atualizado.
Quais os tipos de bancos de dados que podem ser acessados?
O Microsoft Query possui a capacidade de recuperar dados de vários 
tipos de bancos de dados, inclusive Microsoft Office Access, Microsoft 
SQL Server e Serviços OLAP do Microsoft SQL Server, bem como das 
pastas de trabalho do Excel 2007 e de arquivos de texto.
O Microsoft Office fornece drivers que podem ser usados para 
recuperar dados das seguintes fontes de dados:
•	 Serviços de Análise do Microsoft SQL Server (provedor OLAP)
•	 Microsoft Office Access
•	 dBASE
•	 Microsoft FoxPro
•	 Microsoft Office Excel
•	 Oracle
•	 Paradox
•	 Bancos de dados de arquivos de texto
[ 15 ]Unidade 4 - Aula 2 - Bases de Dados e Gráficos
DICA
O Microsoft Query permite selecionar as colunas dos dados desejados e importar 
apenas esses dados para o Excel 2007.
Atenção!
Vamos executar uma conexão com base de dados externa no 
formato XML!
Exercício: Módulo04_Aula02_Exe03
Realizaremos uma conexão com um arquivo XML e antes de 
iniciarmos o procedimento, vamos conhecer o arquivo (que possui 
poucos dados somente para exemplo nesta aula):
Esta é uma estrutura simples de um arquivo XML que contém 
dados de três empresas, sendo esses dados o Nome, Área de Atuação, 
Número do Projeto, Data de Conclusão e Valor.
Vamos importar esses dados para o Excel então!
1. Caso o programa Excel 2007 não esteja aberto, abra-o;
2. mantenha-se na célula A1 da Plan1, clique na guia Dados 
e escolha a opção De Outras Fontes, e por fim a opção Da 
Importação de Dados XML;
[ 16 ] EXCEL AVANÇADO APLICADO AO CONTROLE
3. na caixa Selecionar Fonte de Dados, aponte para a pasta Meus 
Documentos e escolha o arquivo Dados_Empresas.xml e clique 
no botão Abrir;
4. como este arquivo não possui um arquivo de esquema de 
configuração, o Excel 2007 emite um alerta informando que ele 
próprio criará um esquema;
5. apenas clique em OK;
6. na caixa de diálogo Importar Dados; observe se o local para 
iniciar a importação dos Dados está sendo a célula $A$1, caso 
esteja, clique em OK;
7. os dados no formato XML forma importados para a planilha 
no Excel 2007;
[ 17 ]Unidade 4 - Aula 2 - Bases de Dados e Gráficos
8. agora basta o usuário definir os filtros, formatações, atualizações 
etc;
9. salve o arquivo com o nome Mod04_Aula02_Exe03.xlsx na 
pasta Meus Documentos.
4. Criação de gráficos
Um gráfico é uma representação em forma gráfica de dados 
numéricos presentes na planilha. Em vista disso, o Excel 2007 oferece 
vários tipos de gráficos padrões, com uma média de seis variações para 
cada um dos tipos. Pode-se, ainda, escolher entre outros tipos utilizando-
se de gráficos personalizados.
Quando um gráfico é criado, o programa traça-o com base na forma 
do intervalo da planilha que contém os dados desejados, fundamentando-
se nos tipos de dados das linhas e colunas. Assim, em uma seleção da 
planilha, os valores são usados e apresentados no gráfico sob a forma 
de pontos de dados, representados por barras, linhas, colunas, fatias, 
pontos e outras formas que são conhecidas como marcadores de dados.
Os grupos de pontos de dados, ou marcadores de dados, que 
derivam de linhas ou colunas de uma única planilha são agrupados em 
sequências de dados. Cada sequência de dados é diferenciada por um 
único padrão ou cor, ou ambos.
Os gráficos podem ser criados utilizando-se o grupo Gráficos na 
faixa de opções Inserir para obter gráficos específicos ou pela lista drop-
down presente no mesmo grupo. Assim, é possível criar um gráfico em 
uma planilha de dados ou em uma planilha específica.
A criação de gráficos no Excel 2007 pode ser obtida em quatro 
etapas onde esses devem basear-se nos valores das células selecionadas, 
sendo que estas etapas devem ser cuidadosamente preenchidas para que 
se obtenha um resultado desejado: São elas:
Tipo e subtipo do gráfico
Nesta primeira etapa, é escolhido o tipo e subtipo do gráfico a ser 
criado. Na caixa de diálogo desta primeira etapa encontram-se vários 
tipos e subtipos de gráficos mediante a escolha feita inicialmente. Neste 
momento é permitida a escolha do tipo gráfico e a apresentação, variando 
entre gráficos bidimensionais e tridimensionais.
[ 18 ] EXCEL AVANÇADO APLICADO AO CONTROLE
A caixa de diálogo Inserir Gráfico, obtida por intermédio de um 
clique na lista drop-down do grupo Gráficos da faixa de opções Inserir é 
exibida a seguir:
Logo após a escolha do tipo e subtipo do gráfico deve-se clicar no 
botão OK e verificar o resultado na própria planilha de dados.
Intervalo de dados do gráfico
Nesta etapa é definido o intervalo e a sequência de dados que dará 
origem ao gráfico. Se os intervalos foram previamente selecionados, estes serão 
apresentados automaticamente na caixa Selecionar Fonte de Dados. Define-
se também, ao gráfico a sequência em séries ou categorias para apresentação 
visual dos dados. Para tanto, basta clicar no botão Selecionar Dados, no grupo 
Dados presente na nova faixa de opções que surgiu denominada Design.
[ 19 ]Unidade 4 - Aula 2 - Bases de Dados e Gráficos
Ao clicar no botão Adicionar, surge outra caixa de diálogo 
requisitando o nome e valor da série.
A figura a seguir exibe a caixa de diálogo referente à etapa de 
definição do intervalo de dados do gráfico.
As opções desta caixa são as seguintes:
•	 Nome da série: permite escolher qual a célula que representará 
o nome da série;
•	 Valores da série: permite a apresentação e alternação dos dados 
que dão origem aos valores do gráfico.
Layout do gráfico
Todas as formatações aplicáveis aos gráficos são encontradas 
nesta nova faixa de opções Layout. Ela é dividida em vários grupos para 
facilitar a utilização dos recursos.
No grupo Rótulos, existem as opções Título do Gráfico e Títulosdos Eixos em que são apresentados os locais em que devem ser escritos 
os títulos do gráfico, das categorias e séries, dependendo do gráfico 
escolhido na primeira etapa.
[ 20 ] EXCEL AVANÇADO APLICADO AO CONTROLE
No botão Títulos dos Eixos, são apresentadas opções para que estes 
sejam ou não apresentados títulos horizontais ou verticais na imagem 
final do gráfico.
No botão Linhas de Grade, pode-se escolher as linhas de grade 
para cada categoria apresentada no gráfico escolhido, ou seja, se as 
caixas de verificação desta guia estiverem desabilitadas, o gráfico não 
apresentará as linhas que ficam ao fundo da área.
[ 21 ]Unidade 4 - Aula 2 - Bases de Dados e Gráficos
No botão Legenda, há a alternativa de apresentar ou não uma 
legenda para o gráfico ou até escolher a localização dela na área do gráfico.
Os rótulos de dados são informações apresentadas como 
complemento da série dos dados presentes no gráfico, para isto, o botão 
Rótulos de Dados permite escolher tal definição.
No botão Tabela de Dados, é possível mostrar ou não duas 
informações no gráfico, a primeira seria a tabela de dados e a segunda os 
códigos da legenda.
[ 22 ] EXCEL AVANÇADO APLICADO AO CONTROLE
Ao serem configuradas todas as formatações ao gráfico utilizando 
as guias desta etapa, obtém-se uma apresentação mais refinada do gráfico 
inicialmente criado.
Local do gráfico
Após a criação e formatação do gráfico, recomenda-se definir o local 
onde ele será colocado. É possível colocar o gráfico no arquivo de duas 
maneiras diferentes; a primeira seria criar uma planilha específica para 
ele e a segunda, escolher uma planilha de dados para inseri–lo, fazendo 
com que ele seja embutido de forma flutuante, ou seja, deslocado-o 
dentro da planilha por meio do arraste do mouse. Para isto, é necessário 
clicar no botão Mover Gráfico que fica no grupo Local posicionado ao 
final da faixa de opções Design, da Ferramenta de Gráfico.
•	 Nova planilha: cria uma planilha específica para a colocação 
do gráfico;
•	 Objeto em: coloca o gráfico dentro de uma das planilhas de 
dados existente.
Após a escolha da opção adequada, deve-se clicar no botão OK 
para encerrar as principais formatações e concluir a criação do gráfico.
A seguir, realizaremos um exercício que permitirá utilizar estes 
recursos de criação de gráficos.
Exercício: Módulo04_Aula02_Exe04
Vamos aprender a criar um gráfico agora.
1. Caso o programa Excel 2007 não esteja aberto, abra-o;
2. abra o arquivo Graficos.xlsx;
[ 23 ]Unidade 4 - Aula 2 - Bases de Dados e Gráficos
Vamos criar um gráfico que apresente a evolução dos valores dos 
repasses ao longo dos três anos somente para os estados da região Norte.
3. selecione os seguintes intervalos de células (não se esqueça de 
pressionar a tecla CTRL para seleção não-adjacente):
A2:A3 ; A11 ; A20 ; C2:E3 ; C11:E11 e C20:E20
4. após a seleção, clique na guia Inserir e escolha o tipo de gráfico 
Colunas Agrupadas;
[ 24 ] EXCEL AVANÇADO APLICADO AO CONTROLE
Dessa forma o respectivo gráfico é inserido na área da planilha.
Agora vamos efetuar algumas adaptações para melhorar o aspecto 
do gráfico.
5. Com a área do gráfico selecionada, acesse a guia Layout;
6. escolha, em seguida, a opção Título do Gráfico e depois Acima 
do Gráfico;
7. surge uma área para a inserção do título do gráfico logo acima 
das barras. Digite a expressão “Repasses – Região Norte”;
8. escolha o botão Legenda e, em seguida, a opção Mostrar 
Legenda Abaixo;
[ 25 ]Unidade 4 - Aula 2 - Bases de Dados e Gráficos
9. observe que a legenda do gráfico foi deslocada para a parte de 
baixo do gráfico;
Agora vamos modificar a unidade de apresentação dos valores.
10. dê um duplo clique em qualquer número da escala (parte 
esquerda do gráfico) e na caixa de diálogo Formatar Eixo, 
escolha a opção Milhões na caixa Unidades de Exibição;
11. clique em Fechar para finalizar;
12. salve o arquivo.
Desafio: faça outro gráfico, agora com os dados dos estados da 
região Sudeste, porém somente com os anos 2009 e 2011. Boa sorte!
[ 26 ] EXCEL AVANÇADO APLICADO AO CONTROLE
Síntese
Nesta aula, conhecemos o recurso de gerenciamento de base de 
dados em Access e os mecanismos de integração.
Também executamos os recursos de transferência e conexão de 
dados entre o Excel 2007 e outras fontes, como por exemplo, o SQL 
Server, página Web e até mesmo no formato XML.
Por último, vimos que o Excel 2007 fornece o recurso de criação de 
gráficos demonstrativos e todo o conjunto de configurações necessárias 
para a apresentação em tela.
[ 27 ]Unidade 4 - Aula 2 - Bases de Dados e Gráficos
Referências bibliográficas 
Microsoft. Serviço de Suporte ao Excel 2007. Trocar (copiar, importar, 
exportar) dados entre o Excel e o Access. Disponível em: <http://office.
microsoft.com/pt-br/excel-help/trocar-copiar-importar-exportar-
dados-entre-o-excel-e-o-access-HA010096300.aspx>. Acesso em 13 de 
março de 2012.
Microsoft. Serviço de Suporte ao Excel 2007. Obter dados externos 
de uma página da Web. Disponível em: <http://office.microsoft.com/
pt-br/excel-help/obter-dados-externos-de-uma-pagina-da-web-
HA010218472.aspx>. Acesso em 14 de março de 2012.
Microsoft. Serviço de Suporte ao Excel 2007. Visão geral da conexão 
(importação) de dados. Disponível em: <http://office.microsoft.com/
pt-br/excel-help/visao-geral-da-conexao-importacao-de-dados-
HP010201710.aspx>. Acesso em 14 de março de 2012.
Microsoft. Serviço de Suporte ao Excel 2007. Usar o Microsoft Query 
para recuperar dados externos. Disponível em: <http://office.microsoft.
com/pt-br/excel-help/usar-o-microsoft-query-para-recuperar-dados-
externos-HA010099664.aspx>. Acesso em 14 de março de 2012.
Aula 3 
Tabelas Dinâmicas - Visão Geral
EXCEL AVANÇADO APLICADO 
AO CONTROLE
Unidade 4 
Análise dos Dados
Instituto Serzedello Corrêa
RESPONSABILIDADE PELO CONTEÚDO
Tribunal de Contas da União
Secretaria Geral da Presidência
Instituto Serzedello Corrêa
2ª Diretoria de Desenvolvimento de Competências 
Serviço de Planejamento e Projetos Educacionais
SUPERVISÃO
Pedro Koshino
CONTEUDISTA
Helbert de Sousa Arruda
TRATAMENTO PEDAGÓGICO
Flávio Sposto Pompeo
RESPONSABILIDADE EDITORIAL
Tribunal de Contas da União
Secretaria Geral da Presidência
Instituto Serzedello Corrêa
Centro de Documentação
Editora do TCU
PROJETO GRÁFICO
Ismael Soares Miguel
Paulo Prudêncio Soares Brandão Filho
Vivian Campelo Fernandes
DIAGRAMAÇÃO
Vanessa Vieira
Brasil. Tribunal de Contas da União.
Excel Avançado Aplicado ao Controle / Tribunal de Contas da União ; 
conteudista: Helbert de Sousa Arruda. – Brasília : TCU, Instituto Serzedello 
Corrêa, 2012.
27 p. : il.
Conteúdo: Unidade 4: Análise dos dados. Aula 3: Tabelas dinâmicas – visão 
geral.
Curso realizado no período de 22/05 a 19/06/2012 no Ambiente
Virtual de Educação Corporativa do Tribunal de Contas da União.
1. Importação de dados. 2. Normalização de dados. 3. Manipulação de 
dados. 4. Análise de dados. I. Título.
Ficha catalográfica elaborada pela Biblioteca Ministro Ruben Rosa
© Copyright 2012, Tribunal de Contas de União 
<www.tcu.gov.br>
Permite-se a reprodução desta publicação, 
em parte ou no todo, sem alteração do conteúdo, 
desde que citada a fonte e sem fins comerciais.
[ 3 ]Unidade 4 - Aula 3 - Tabelas Dinâmicas - Visão Geral
Aula 3 – Tabelas dinâmicas (Visão Geral)
Para que servem as tabelas dinâmicas?
Como utilizar seus recursos?
Principais características, aplicações e operações?
Nesta aula obteremos o conhecimento necessário para a concepção 
e implementação das tabelas dinâmicas. Iniciaremos nossos estudos pela 
apresentação dos procedimentos para elaboração das tabelas dentro de 
um contexto prático. E para complementar este estudo, mostraremos 
como utilizar e configurar seusprincipais recursos.
Para facilitar o estudo, esta aula está organizada da seguinte forma:
Ao final desta aula, esperamos que você tenha condições de:
•	 Conhecer os procedimentos para criação de relatórios de 
tabelas dinâmicas;
•	 Entender o processo de tomada de decisão e interpretação de 
dados;
•	 Conhecer os objetivos das tabelas dinâmicas;
•	 Saber executar os primeiros passos para criação do relatório;
•	 Conhecer as operações básicas com as tabelas dinâmicas.
Pronto para começar? Então, vamos!
Aula 3 – Tabelas dinâmicas (Visão Geral) ����������������������������������������������������������3
1. Noções básicas ��������������������������������������������������������������������������������������������������������������4
2. Primeiros passos ����������������������������������������������������������������������������������������������������������7
3. O acesso ����������������������������������������������������������������������������������������������������������������������������9
4. A configuração dos campos e da tabela ������������������������������������������������������ 11
5. Operações com as tabelas dinâmicas ������������������������������������������������������������ 17
Síntese �������������������������������������������������������������������������������������������������������������������������������� 26
Referências bibliográficas ����������������������������������������������������������������������������������� 27
[ 4 ] EXCEL AVANÇADO APLICADO AO CONTROLE
1. Noções básicas
Geralmente as planilhas corporativas possuem muitos dados, 
sendo que estes nem sempre informam detalhamente os reais propósitos, 
por exemplo: Sabe-se o que os números significam? Os dados existentes 
respondem a todas as perguntas? Entre outras.
Objetivos
Os relatórios de tabela dinâmica têm como objetivo ajudar o 
usuário a analisar os dados numéricos e responder a perguntas sobre 
eles. Em segundos, é possível verificar qual funcionário vendeu mais, em 
que setor e em que mês. É possível consultar qual dos trimestres foi mais 
lucrativo e também qual produto foi mais vendido etc.
Na realidade, os relatórios de tabela dinâmica fornecem formas 
diferentes de ver os dados com apenas alguns cliques do mouse, e dessa 
maneira, os dados são deslocados para outros lugares compondo novos 
significados.
Neste contexto de procura da melhor maneira de representar os 
dados a fim de torná-los mais fáceis de serem interpretados, segue-se o 
conceito de tomada de decisão. Embora os dados ali representados estejam 
completos e íntegros, há a necessidade de transformá-los em ações a serem 
tomadas, ou seja, em objetivos específicos a serem alcançados.
Tomada de decisão
Na enciclopédia eletrônica Wikipédia, tem-se o conceito de tomada 
de decisão como sendo o processo pelo qual são escolhidas algumas ou 
apenas uma entre muitas alternativas para as ações a serem realizadas.
Chiavenato (1997, p. 710) elucida ao definir decisão como “o 
processo de análise e escolha entre várias alternativas disponíveis do 
curso de ação que a pessoa deverá seguir”.
Tomada de decisão, segundo Oliveira (2004), nada mais é do que 
a conversão das informações em ação, assim sendo, decisão é a ação 
tomada com base na apreciação de informações. Decidir é recomendar 
entre vários caminhos alternativos que levam a determinado resultado.
As decisões são escolhas tomadas com base em propósitos, são 
ações orientadas para determinado objetivo e o alcance deste objetivo 
determina a eficiência do processo de tomada de decisão.
[ 5 ]Unidade 4 - Aula 3 - Tabelas Dinâmicas - Visão Geral
A decisão pode ser tomada a partir de probabilidades, possibilidades 
e/ou alternativas. Para toda ação existe uma reação e, portanto, são nas 
reações que são baseadas as decisões.
A decisão é mais do que a simples escolha entre alternativas, 
sendo necessário prever os efeitos futuros da escolha, considerando 
todos os reflexos possíveis que ela pode causar no momento presente 
e no futuro.
Modernamente entende-se que é impossível encontrar num processo 
de decisão a melhor alternativa, o que faz com que sejam buscadas as 
alternativas satisfatórias, ou seja, na prática o que se busca é a alternativa 
que, mesmo não sendo a melhor, leve para o alcance do objetivo da decisão.
Conforme estudos, a maioria das classificações das decisões é baseada 
em critérios de análise: atividade administrativa a qual se vincula a decisão; 
nível de importância dentro da organização; estruturação e previsibilidade.
a. Vinculação à atividade administrativa, que segundo Shimizu 
(2006) a decisão pode ser distinguida por nível de decisão:
•	 Nível Estratégico: em geral são as decisões para dois a cinco anos;
•	 Nível Tático: decisão para alguns meses a até dois anos;
•	 Nível Operacional: alguns dias ou alguns meses; e
•	 Nível de despacho ou liberação: decisão para algumas horas 
ou alguns dias.
b. Nível de importância dentro da organização:
•	 Altamente importantes;
•	 Importantes;
•	 Medianamente importantes;
•	 Pouco importantes;
•	 Não importantes.
c. Estruturação:
•	 Estruturadas;
[ 6 ] EXCEL AVANÇADO APLICADO AO CONTROLE
•	 Não-estruturadas.
d. Previsibilidade:
•	 Rotineiras ou cíclicas;
•	 Não rotineiras ou acíclicas;
•	 Inéditas.
O cenário em que as organizações estão inseridas se modifica 
constantemente, exercendo nestas, toda a sua influência. Diante disto, 
faz-se necessário permanente alerta por parte dos administradores, 
para percepção do que os ambientes interno e externo da organização 
indicam em relação às ameaças e oportunidades, para que as escolhas 
sejam feitas com base na realidade organizacional.
Interpretação dos dados
Então podemos concluir que o relatório de tabela dinâmica, através 
da interpretação de seus dados, subsidia a tomada de decisão.
Então como e para quê interpretar estes dados?
Imaginemos uma determinada planilha do Excel 2007 com 
informações sobre vendas com centenas ou milhares de linhas de dados. 
Neste caso, essa planilha apresenta, de forma descritiva, o registro 
dos dados das vendas, com os devidos nomes dos vendedores, datas, 
produtos vendidos, clientes, valores entre outros – isso tudo listados 
linha após linha e divididos em várias colunas.
Então surgem as perguntas:
•	 Como posso obter as informações gerenciais desta planilha?
•	 Como compreender todos esses dados?
•	 No geral, qual vendedor foi o mais eficiente?
•	 Qual deles vendeu mais em um determinado trimestre?
•	 Em que local foi realizado o maior número de vendas?
Com os relatórios de tabela dinâmica, todas essas perguntas 
podem ser respondidas com apenas alguns poucos cliques no mouse. 
[ 7 ]Unidade 4 - Aula 3 - Tabelas Dinâmicas - Visão Geral
O objetivo de um relatório de tabela dinâmica é transformar todos os 
dados em relatórios pequenos, concisos, que o informam exatamente o 
que se deseja conhecer.
2. Primeiros passos
Antes de iniciar o processo de elaboração de um relatório de tabela 
dinâmica, devem-se tomar alguns cuidados com relação à estrutura da 
planilha de dados e escalonar o resultado desejado.
O cuidado com a planilha de dados
Antes de começar a trabalhar com um relatório de tabela dinâmica, 
o usuário deverá analisar a estrutura da planilha do Excel 2007 para 
garantir que ela esteja bem preparada para gerar o relatório.
Ao criar um relatório de tabela dinâmica, cada coluna dos dados de 
origem se torna um campo que pode ser usado no relatório. Os campos 
resumem várias linhas de informações a partir dos dados de origem.
IMPORTANTE
Os nomes dos campos do relatório são originados dos títulos de cada coluna dos 
dados de origem. Deve ser verificado se cada coluna possui o nome na primeira linha 
da planilha nos dados de origem.
Caso exista alguma coluna sem sua identificação surgea seguinte 
mensagem de erro:
Vamos ver um exemplo de planilha válida!
Na planilha da imagem acima, os títulos de coluna CNPJ, Entidade, 
Cidade, UF e Processo, ao final, se tornarão os nomes de campo no 
momento da criação de um relatório de tabela dinâmica.
[ 8 ] EXCEL AVANÇADO APLICADO AO CONTROLE
O cuidado com a estrutura
As linhas restantes abaixo dos títulos devem conter os mesmos 
formatos de conteúdos na mesma coluna, ou seja, textos devem estar 
em uma coluna, números em outra coluna e datas em outra coluna. Em 
outras palavras, uma coluna que contém números não deve conter texto, 
e assim por diante.
E para que não haja erro de seleção de dados na elaboração do 
relatório, não deve haver colunas vazias na planilha de dados de origem, 
pois são necessárias que estas informações estejam de forma adjacente.
RECOMENDAÇÃO
Recomenda-se que não haja linhas vazias; por exemplo, linhas em branco que são 
usadas para separar um bloco de dados de outro devem ser removidas.
[ 9 ]Unidade 4 - Aula 3 - Tabelas Dinâmicas - Visão Geral
CORRETO
√
CORRETO
X
3. O acesso
Após a criação da planilha com seus respectivos dados ou até 
mesmo a importação de dados a partir de outro formato, deve-se 
assegurar que os dados estejam todos listados adjacentemente. Então, 
coloca-se o cursor em qualquer local dentro da área dos dados (isso 
incluirá todos os dados da planilha no relatório), ou deve-se selecionar 
apenas os dados que deseja usar no relatório.
Em seguida, na guia Inserir, no grupo Tabelas, deve-se clicar em 
Tabela Dinâmica.
Em seguida, clicar em Tabela Dinâmica novamente.
[ 10 ] EXCEL AVANÇADO APLICADO AO CONTROLE
A caixa de diálogo Criar Tabela Dinâmica é aberta.
Quando esta caixa de diálogo é aberta, automaticamente a opção 
Selecionar uma Tabela ou Intervalo encontra-se selecionada.
A caixa Tabela/Intervalo mostra o intervalo dos dados selecionados.
Pode-se inclusive escolher uma fonte de dados externa para gerar 
o relatório de tabela dinâmica.
O principal benefício da conexão com dados externos é a 
possibilidade de analisar periodicamente esses dados, no Microsoft 
Office Excel, sem copiar repetidamente os dados, o que é uma operação 
que pode levar tempo e está propensa a erros.
Depois de conectar-se a dados externos, torna-se possível atualizar 
automaticamente as pastas de trabalho do Excel 2007 a partir da fonte 
de dados original sempre que a fonte de dados for atualizada com novas 
informações.
[ 11 ]Unidade 4 - Aula 3 - Tabelas Dinâmicas - Visão Geral
Ao clicar no botão Escolher Conexão, surge a caixa de diálogo 
Conexões Existentes para efetuar a escolha de uma das conexões 
atualmente configuradas.
Ainda na caixa de diálogo Criar Tabela Dinâmica, existe a opção 
Nova Planilha (também selecionada automaticamente) para definir o 
local onde o relatório será colocado (pode-se clicar em Planilha Existente 
se não quiser colocar o relatório em uma nova planilha).
Ao final deve-se clicar no botão OK para finalmente gerar o 
relatório de tabela dinâmica com as configurações escolhidas.
4. A configuração dos campos e da tabela
Após a definição da área de dados e do local de inserção do relatório 
de tabela dinâmica, agora é clicar nos campos da tabela dinâmica que 
deseja para que os dados apareçam no relatorio na planilha.
Apresentação
À esquerda está a área de layout pronta para receber o relatório de 
tabela dinâmica, e à direita está a Lista de Campos de Tabela Dinâmica. 
Essa lista mostra os títulos de coluna dos dados de origem: cada título é 
um campo na lista.
[ 12 ] EXCEL AVANÇADO APLICADO AO CONTROLE
Adição de campos
Na lista de campos, devem ser marcadas as caixas de seleção ao 
lado dos campos que se deseja adicionar ao relatório.
Os campos são automaticamente adicionados ao relatório. 
[ 13 ]Unidade 4 - Aula 3 - Tabelas Dinâmicas - Visão Geral
Os campos que não são numéricos são adicionados à área Rótulos de 
Linha do relatório. Conforme adiciona-se mais campos não-numéricos, 
o Excel 2007 coloca-os dentro de campos que já existem no relatório 
de tabela dinâmica, criando uma hierarquia. Os campos numéricos são 
adicionados à direita.:
Opções da tabela dinâmica
Ao acessar as opções da tabela dinâmica (clicando com o botão 
da direita do mouse sobre qualquer célula da tabela), é possível alterar 
algumas configurações de sua planilha, como impressão, definições de 
exibição, layout, filtros e dados.
[ 14 ] EXCEL AVANÇADO APLICADO AO CONTROLE
As guias e opções da respectiva caixa de diálogo são:
a. Layout e formato
b. Totais e filtros
[ 15 ]Unidade 4 - Aula 3 - Tabelas Dinâmicas - Visão Geral
c. Exibição
d. Impressão
e. Dados
[ 16 ] EXCEL AVANÇADO APLICADO AO CONTROLE
f. Texto Alt.
Remoção de campos e da tabela
Para remover um campo do relatório, limpe a caixa de seleção ao 
lado do nome do campo na lista de campos.
Para efetuar a remoção de todos os campos de um relatório para 
que começar o processo novamente, basta clicar na guia Opções na 
Faixa de Opções em Ferramentas de Tabela Dinâmica, e em seguida, 
no grupo Ações, clicar na seta no botão Limpar e selecionar a opção 
Limpar Tudo.
[ 17 ]Unidade 4 - Aula 3 - Tabelas Dinâmicas - Visão Geral
Caso o usuário queira excluir o relatório inteiro, este deve clicar na 
guia Opções, no grupo Ações, clicar na seta em Selecionar e finalmente 
clicar na opção Tabela Dinâmica Inteira.
Por fim, deve-se pressionar a tecla DELETE.
5. Operações com as tabelas dinâmicas
A seguir são listadas as principais operações que podem ser 
realizadas com o relatório de tabela dinâmica.
Arrastar e soltar
Conforme a preferência do usuário, pode-se criar um relatório 
de tabela dinâmica usando o método arrastar e soltar, como podia ser 
realizado em versões anteriores do Excel.
Existem quatro caixas na parte inferior da Lista de Campos de 
Tabela Dinâmica. As caixas se chamam Filtro de Relatório, Rótulos de 
Linha, Rótulos de Coluna e Valores. Podem-se arrastar os campos para 
essa área.
[ 18 ] EXCEL AVANÇADO APLICADO AO CONTROLE
Normalmente, no Excel 2007, adicionam-se os campos ao layout 
do relatório a partir da lista de campos, marcando as caixas de seleção 
ao lado dos nomes de campo, ou clicando com o botão direito do mouse 
no campo e selecionando um local a partir de um menu.
Conforme esta operação com o botão direito do mouse é 
executada, os campos são automaticamente colocados no layout e eles 
são simultaneamente colocados na caixa apropriada na parte inferior da 
lista.
Classificação de campos
Para efeutar a classificação do relatório por um dos elementos de 
suas estrutura (linha ou coluna), deve-se clicar com o botão direito do 
mouse em uma célula no campo, no título da linha ou coluna que se 
deseja classificar. Ao final deve-se apontar para a opção Classificar e em 
seguida em uma opção. Por exemplo, Classificar do Maior para o Menor 
ou Mais Opções de Classificação.
Agrupamento de campo
O Excel 2007 permite agrupar manualmente os dados de um 
conjunto de linhas ou colunas para que seja efetuado um estudo 
consolidado em relação aos demais dados.
Para agrupar, deve-se selecionar o conjunto de células, clicar com o 
botão direito do mouse em uma das célula e clicar em Agrupar.
[ 19 ]Unidade 4 - Aula 3 - Tabelas Dinâmicas - Visão Geral
Para desagrupar, clica-se no termo Agrupar1, e em seguida, com o 
botão direito do mouse, seleciona-se Desagrupar.
Adição de filtro
Um filtro de relatório tem a finalidade de focalizar um subconjunto 
de dados no relatório, geralmente uma linha de produto, uma duração 
ou uma região geográfica.
Para inserir este recurso, na lista de campos da tabela dinâmica, 
deve-se clicar com o botão direito do mouse em um determinado nome 
de campo (de linha ou coluna) e selecionar a opçãoFiltrar seguida da 
subopção de filtragem.
[ 20 ] EXCEL AVANÇADO APLICADO AO CONTROLE
Para limpar o filtro, deve-se novamente pressionar o botão direito 
do mouse sobre o campo, escolher a opção Filtrar e finalmente a opção 
Limpar Filtro de [Campo].
Cálculos personalizados e funções de resumo
Nos relatórios de tabela dinâmica é possível utilizar cálculos 
personalizados e funções resumo no valor dos campos para combinar 
valores de dados de origem de base. Se as funções resumo e os cálculos 
personalizados não fornecerem os resultados desejados, ainda pode-se 
criar suas próprias fórmulas em campos calculados e itens calculados.
As principais funções de resumo são as seguintes:
•	 Soma: somatório de valores.
•	 Contagem: contagem de células preenchidas.
•	 Média: média aritmética.
•	 Máx: maior valor entre todos.
•	 Min: menor valor entre todos.
•	 Produto: multiplicação dos elementos (valores).
[ 21 ]Unidade 4 - Aula 3 - Tabelas Dinâmicas - Visão Geral
•	 Contar números: contagem de números.
•	 DesvPad: calcula o desvio padrão.
•	 DesvPadp: desvio padrão com base na população total.
•	 Var: calcula a variância.
•	 Varp: variância com base na população total.
Para acessar estas funções resumo, deve-se clicar com o botão 
direito do mouse sobre uma célula de valor da tabela dinâmica e escolher 
a opção Resumir Valores Por.
Já os principais cálculos personalizados são os seguintes:
FUNÇÃO RESULTADO
Diferente de Exibe os valores como a diferença em relação ao valor do Item base no Campo base.
% de Exibe os valores como uma porcentagem do valor do Item base no Campo base.
% Diferença de Exibe os valores como a diferença percentual em relação ao valor do Item base no Campo base.
Soma acumulada em Exibe o valor de itens sucessivos no Campo base como uma soma acumulada.
% da linha Exibe o valor em cada linha ou categoria como uma porcentagem do total da linha ou categoria.
% da coluna Exibe todos os valores em cada coluna ou sequência como uma porcentagem do total da coluna ou sequência.
% do total Exibe os valores como uma porcentagem do total geral de todos os dados ou pontos de dados no relatório.
Índice
Calcula valores da seguinte maneira:
((valor na célula) x (Total geral)) / ((Total geral da linha) x 
(Total geral da coluna))
[ 22 ] EXCEL AVANÇADO APLICADO AO CONTROLE
Para acessar estas funções resumo, deve-se clicar com o botão 
direito do mouse sobre uma célula de valor da tabela dinâmica e escolher 
a opção Mostrar Valores Como.
Campos calculados
Nas fórmulas criadas para campos calculados e itens calculados, 
pode-se usar operadores e expressões da mesma maneira que em outras 
fórmulas de planilha.
É possível usar constantes e referir-se a dados do relatório mas 
não se pode usar referências de células, nomes, funções de planilha que 
exigem referências de célula ou nomes definidos como argumentos, nem 
pode usar funções de matriz.
Para inserir um campo calculado, deve-se posicionar na célula 
dentro da tabela dinâmica, selecionar a guia Opções, no grupo Cálculos 
escolher a opção Campos, Itens e Conjuntos e por fim a opção Campo 
Calculado.
[ 23 ]Unidade 4 - Aula 3 - Tabelas Dinâmicas - Visão Geral
Na caixa de diálogo Inserir Campos Calculado deve-se digitar 
o nome do campo, a fórmula e em seguida clicar no botão Adicionar. 
Dessa maneira o campo criado é inserido na lista de Campos desta tela.
Em seguida, deve-se clicar no botão Inserir Campo e ao final 
Em OK. Dessa maneira é criado um novo campo a partir do cálculo 
elaborado na caixa de diálogo.
Alteração de estrutura
Ao dinamizar um relatório de tabela dinâmica, o usuário transpõe 
o modo de exibição vertical ou horizontal de um campo, movendo as 
linhas para a área de coluna ou movendo as colunas para a área de linha.
Com o clique na seta presente no canto direito do campo que se 
deseja alterar o posicionamento, aponta-se para Mover e seleciona-se 
Mover para Rótulos de Coluna ou vice-versa.
[ 24 ] EXCEL AVANÇADO APLICADO AO CONTROLE
Movimentação para outro local
O Excel 2007 permite que o relatório de tabela dinâmica seja movido 
para outro local na pasta. Para isto, deve-se clicar na guia Opções na 
Faixa de Opções em Ferramentas de Tabela Dinâmica. No grupo Ações, 
clique em Mover Tabela Dinâmica.
A caixa de diálogo Mover Tabela Dinâmica é aberta.
Na opção “Escolha onde deseja que o relatório de tabela dinâmica 
seja colocado”, seleciona-se Nova Planilha, ou na caixa Local de Planilha 
Existente, digita-se a primeira célula no intervalo de células onde se 
deseja colocar o relatório de tabela dinâmica.
[ 25 ]Unidade 4 - Aula 3 - Tabelas Dinâmicas - Visão Geral
Ao final, clica-se em OK.
[ 26 ] EXCEL AVANÇADO APLICADO AO CONTROLE
Síntese
Nesta aula conhecemos e aprendemos a utilizar os recursos 
existentes no relatório de tabela dinâmica.
Conhecemos também, os cuidados que devem ser mantidos 
antes da criação deste tipo de relatório, bem como as características, 
operações e configurações que tornam a apresentação e utilização da 
tabela dinâmica um recurso mais eficiente e produtivo.
[ 27 ]Unidade 4 - Aula 3 - Tabelas Dinâmicas - Visão Geral
Referências bibliográficas 
CHIAVENATO, Idalberto. Introdução à Teoria da Administração. 5 ed. 
São Paulo: Makron Books, 1997
Microsoft. Serviço de Suporte ao Excel 2007. Tabela dinâmica:Introdução 
aos relatórios de tabela dinâmica no Excel 2007. Disponível em: 
<http://office.microsoft.com/pt-br/training/visao-geral-RZ010205886.
aspx?section=1>. Acesso em 20 de março de 2012.
Microsoft. Serviço de Suporte ao Excel 2007. Conectar a (Importar) 
dados externos. Disponível em: <http://office.microsoft.com/pt-br/
excel-help/conectar-a-importar-dados-externos-HP010089898.aspx>. 
Acesso em 21 de março de 2012.
OLIVEIRA, D. P. R. Sistemas de informações gerenciais: estratégias, 
táticas, operacionais. 9 ed. São Paulo: Atlas, 2004.
Um toque de motivação. Tomada de decisão nas organizações. 
Disponível em: <http://www.umtoquedemotivacao.com/administracao/
tomadas-de-decisao-nas-organizacoes/>. Acesso em 20 de março de 
2012.
Wikipédia. Tomada de Decisão. Disponível em: <http://pt.wikipedia.
org/wiki/Tomada_de_decisão>. Acesso em 20 de março de 2012.
Aula 4 
Tabelas e Gráficos Dinâmicos na 
Prática
EXCEL AVANÇADO APLICADO 
AO CONTROLE
Unidade 4 
Análise dos Dados
Instituto Serzedello Corrêa
RESPONSABILIDADE PELO CONTEÚDO
Tribunal de Contas da União
Secretaria Geral da Presidência
Instituto Serzedello Corrêa
2ª Diretoria de Desenvolvimento de Competências 
Serviço de Planejamento e Projetos Educacionais
SUPERVISÃO
Pedro Koshino
CONTEUDISTA
Helbert de Sousa Arruda
TRATAMENTO PEDAGÓGICO
Flávio Sposto Pompeo
RESPONSABILIDADE EDITORIAL
Tribunal de Contas da União
Secretaria Geral da Presidência
Instituto Serzedello Corrêa
Centro de Documentação
Editora do TCU
PROJETO GRÁFICO
Ismael Soares Miguel
Paulo Prudêncio Soares Brandão Filho
Vivian Campelo Fernandes
DIAGRAMAÇÃO
Vanessa Vieira
Brasil. Tribunal de Contas da União.
Excel Avançado Aplicado ao Controle / Tribunal de Contas da União ; 
conteudista: Helbert de Sousa Arruda. – Brasília : TCU, Instituto Serzedello 
Corrêa, 2012.
22 p. : il.
Conteúdo: Unidade 4: Análise dos dados. Aula 4: Tabelas e gráficos dinâmicos 
na prática.
Curso realizado no período de 22/05 a 19/06/2012 no Ambiente
Virtual de Educação Corporativa do Tribunal de Contas da União.
1. Importação de dados. 2. Normalização de dados. 3. Manipulação de 
dados. 4. Análise de dados. I. Título.
Ficha catalográfica elaborada pela Biblioteca Ministro Ruben Rosa
© Copyright 2012, Tribunal de Contas de União 
<www.tcu.gov.br>
Permite-se a reprodução desta publicação, 
em parte ou no todo, sem alteração do conteúdo, 
desde que citada a fonte e sem finscomerciais.
[ 3 ]Unidade 4 - Aula 4 - Tabelas e Gráficos Dinâmicos na Prática
Aula 4 – Tabela e gráficos dinâmicos na prática
Como trabalhar com o relatório de tabela dinâmica?
Quais os recursos dos gráficos dinâmicos?
Como utilizar os gráficos dinâmicos?
Iniciaremos nossos estudos pela prática na elaboração de relatórios 
de tabelas dinâmicas, bem como a utilização dos recursos principais. 
Recursos esses evidenciados na aula anterior (Aula 3).
Ainda nesta aula, apresentaremos os principais recursos, as 
características básicas e as operações que podem ser executadas com os 
gráficos dinâmicos.
Para facilitar o estudo, esta aula está organizada da seguinte forma:
Ao final desta aula, esperamos que você tenha condições de:
•	 saber como utilizar os relatórios de tabela dinâmica;
•	 conhecer e trabalhar com os gráficos dinâmicos.
Pronto para começar? Então, vamos!
Aula 4 – Tabela e gráficos dinâmicos na prática ������������������������������������������3
1. Relatório de tabela dinâmica na prática �����������������������������������������������������4
2. Gráficos dinâmicos�������������������������������������������������������������������������������������������������� 12
Síntese �������������������������������������������������������������������������������������������������������������������������������� 21
Referências bibliográficas ����������������������������������������������������������������������������������� 22
[ 4 ] EXCEL AVANÇADO APLICADO AO CONTROLE
1. Relatório de tabela dinâmica na prática
Conforme evidenciado na aula anterior sobre a elaboração de 
relatórios de tabelas dinâmicas, são muitos os recursos existentes e para 
que o conhecimento sobre esse assunto seja efetivamente consolidado, 
esta aula fornecerá suporte necessário para a criação de relatórios 
baseados em situações reais, acompanhado de um teor prático e com 
fundamentos bem aprofundados.
Para aplicar o conhecimento adquirido na aula 3 deste módulo 4, 
execute os passos a seguir:
Exercício: Módulo04_Aula04_Exe01
1. Abra o programa Excel 2007;
2. abra o arquivo TD.xlsx presente na pasta Meus Documentos 
do seu computador;
3. acesse a guia Inserir e em seguida a opção Tabela Dinâmica 
no grupo Tabelas;
4. na caixa de diálogo Criar Tabela Dinâmica, verifique se o 
intervalo de dados corresponde à Plan1!$A$1:$G$121;
[ 5 ]Unidade 4 - Aula 4 - Tabelas e Gráficos Dinâmicos na Prática
5. caso o intervalo esteja correto, clique em OK (caso contrário, 
verifique se a planilha possui alguma interrupção de linha ou 
coluna);
6. surge então a área do relatório da tabela dinâmica (à esquerda) 
e a lista de campos da tabela dinâmica (à direita);
7. ative os campos UF, Data, Situação Atual e Valor;
8. observe que, ao selecionar, os três primeiros campos foram 
inseridos na área Rótulo de Linha e o último (Valor) foi 
inserido na área Valores. Ao mesmo tempo a tabela dinâmica 
foi montada na área da planilha;
[ 6 ] EXCEL AVANÇADO APLICADO AO CONTROLE
Agora vamos distribuir dois destes três primeiros campos pelas 
outras áreas.
9. Arraste o campo Data para a área Rótulo de Coluna e o campo 
Situação Atual para a área Filtro de Relatório;
10. as áreas devem ficar da seguinte forma (observe como isto se 
reflete na tabela dinâmica):
Agora vamos modificar o nome do campo Valor e definir os valores 
da tabela dinâmica no formato Monetário.
11. Clique na seta que fica ao lado do campo Soma de Valor e em 
seguida na opção Configurações do Campo de Valor;
[ 7 ]Unidade 4 - Aula 4 - Tabelas e Gráficos Dinâmicos na Prática
12. Em seguida, modifique o nome do campo para Valores dos 
Contratos na caixa Nome Personalizado;
13. clique no botão Formato do Número;
14. escolha,em seguida, a opção Moeda e defina as seguintes opções 
ao lado:
15. ao final clique em OK nas duas telas;
Agora vamos filtrar a tabela pelo campo de filtro de relatório 
Situação Atual a fim de exibir somente os dados cuja situação seja Saúde.
16. clique na seta ao lado do campo Situação Atual, escolha a 
opção Mec e clique em OK;
[ 8 ] EXCEL AVANÇADO APLICADO AO CONTROLE
17. observe que os dados da tabela sofreram uma diminuição 
considerável. Isto aconteceu pelo fato de que a situação atual 
Mec é uma das que menos existe na tabela original;
18. para que possamos verificar quais cidades correspondam aos 
valores dos Estados listados, basta ativar o campo Cidade na 
caixa de lista de campos;
19. observe o resultado na área de tabela dinâmica;
[ 9 ]Unidade 4 - Aula 4 - Tabelas e Gráficos Dinâmicos na Prática
Agora definiremos a quantidade de contratos por UF (em todas as 
datas e situações);
20. clique em qualquer ponto da tabela dinâmica com o botão 
direito do mouse;
21. escolha a opção de menu chamada Resumir Dados Por e em 
seguida escolha a opção Contagem;
22. observe que toda a tabela agora exibe o número de contratos 
por Cidade;
[ 10 ] EXCEL AVANÇADO APLICADO AO CONTROLE
Vamos modificar o formato de número retirando as casas decimais. 
23. clique com o botão da direita em qualquer parte da tabela, 
24. selecione a opção Configurações do Campo de Valor e na 
caixa de diálogo clique no botão Formato do Número;
25. na caixa de diálogo Formatar Células deixe as opções da 
seguinte maneira e clique em OK nas duas telas:
26. na lista de campos desmarque o campo Cidade;
[ 11 ]Unidade 4 - Aula 4 - Tabelas e Gráficos Dinâmicos na Prática
27. por último, desfaça o filtro de relatório Situação Atual para a 
opção Tudo e em seguida clique em OK;
28. a tabela dinâmica, agora, apresenta a quantidade e total de 
contratos por UF em cada uma das datas listadas;
29. salve o arquivo.
Agora tente outras opções. Boa sorte!
[ 12 ] EXCEL AVANÇADO APLICADO AO CONTROLE
2. Gráficos dinâmicos
Conjuntamente, um gráfico dinâmico associa uma representação 
gráfica dos dados em um relatório de tabela dinâmica. O gráfico dinâmico 
é interativo, ou seja, da mesma forma que se pode manipular campos 
em uma tabela dinâmica para obtenção automática de representações 
de dados, este elemento também proporciona o recurso de lidar com a 
representação gerenciável dos gráficos.
Quando um gráfico dinâmico é criado, o painel Filtro de Gráfico 
Dinâmico é exibido, dessa maneira, pode-se usá-lo para classificar e 
filtrar os dados subjacentes do gráfico dinâmico. As alterações realizadas 
no layout e nos dados do campo no relatório de tabela dinâmica associado 
refletem-se imediatamente no layout nos dados do gráfico dinâmico.
O gráfico dinâmico exibe um conjunto de itens como séries de 
dados, categorias, marcadores de dados e eixos, exatamente como os 
gráficos tradicionais. É possível também alterar o tipo de gráfico e 
outras opções como os títulos, o posicionamento da legenda, os rótulos 
de dados, o local do gráfico etc.
[ 13 ]Unidade 4 - Aula 4 - Tabelas e Gráficos Dinâmicos na Prática
OBSERVAÇÃO
É possível criar automaticamente um relatório de gráfico dinâmico ao criar primeiro 
um relatório de tabela dinâmica, ou criar um relatório de gráfico dinâmico a partir de 
um relatório de tabela dinâmica existente.
Passos iniciais
Certifique-se de que o relatório da tabela dinâmica tem pelo menos 
um campo de linha, para se tornar o campo de categoria no relatório de 
gráfico dinâmico e um campo de coluna para se tornar o campo de série. 
Se o relatório da tabela dinâmica estiver em formato recuado, mova pelo 
menos um campo para a área de coluna antes de criar o gráfico.
Estruturação
No assistente de tabela dinâmica e gráfico dinâmico, especifique 
o tipo de dados de origem a ser usado e defina as opções para como os 
dados serão utilizados. Em seguida, crie o layout do relatório de gráfico 
dinâmico de maneira semelhante à de um relatório de tabela dinâmica. 
Se a pasta de trabalho não contiverum relatório de tabela dinâmica, o 
Excel 2007 criará uma quando o relatório de gráfico dinâmico for criado. 
Assim, quando esse relatório for alterado, o relatório da tabela dinâmica 
associado será alterado e vice-versa.
Personalização
Em seguida, altera-se o tipo de gráfico e outras opções – como os 
títulos, o local da legenda, os rótulos de dados, o local do gráfico e assim 
por diante – usando o assistente de gráfico e os comandos do menu gráfico.
Campos de página
O uso de campos de página é uma forma conveniente de resumir e 
enfatizar rapidamente um subconjunto de dados sem precisar modificar 
as informações de série e categoria. Por exemplo, se o usuário estiver 
fazendo uma apresentação, poderá clicar na opção (Tudo) no filtro de 
relatório para mostrar os elementos referentes a todos os dados e, depois, 
enfatizar dados específicos, clicando em um dado de cada vez.
Os filtros de relatório têm o mesmo layout de categoria e série para 
os diferentes dados, para que os dados de cada elemento possam ser 
comparados facilmente. Além disso, ao permitir que se recupere um 
filtro de cada vez de um grande conjunto de dados, os campos de filtro 
conservam memória quando seu gráfico usa dados de origem externa.
[ 14 ] EXCEL AVANÇADO APLICADO AO CONTROLE
Partes dos gráficos dinâmicos
Os relatórios de gráfico dinâmico possuem alguns elementos 
especializados como os filtros, séries, categorias e eixos dos gráficos 
normais do Excel 2007. Veja a descrição de cada um deles:
•	 Filtro do Relatório: um campo que se usa para filtrar dados 
por itens específicos. No exemplo, o filtro Região exibe dados 
de todas as regiões. Para exibir dados de uma única região (ou 
várias, se for selecionada a opção Selecionar Vários Itens), deve-
se clicar na seta suspensa ao lado do termo Região e selecionar 
a(s) região(ões).
[ 15 ]Unidade 4 - Aula 4 - Tabelas e Gráficos Dinâmicos na Prática
•	 Campo de Eixos (Categorias): um campo dos dados de 
origem de base que fornece valores para serem comparados ou 
medidos. No exemplo, corresponde ao campo Mês, que pode 
ser classificado ou filtrado de diversas maneiras.
•	 Campo de Legenda (Série): um campo que se atribui a uma 
orientação de série em um relatório de gráfico dinâmico. Os 
itens no campo fornecem as séries de dados individuais. No 
exemplo, Vendedor é um campo de série com dois itens: João 
e Maria.
[ 16 ] EXCEL AVANÇADO APLICADO AO CONTROLE
•	 Valores: os itens representam as entradas exclusivas em um 
campo e aparecem nas listas suspensas dos campos de página, 
campos de categoria e campos de série. No exemplo, Janeiro, 
Fevereiro e Março são itens no campo de categoria Mês, 
enquanto João e Maria são itens no campo de série Vendedor.
IMPORTANTE
Quando gráfico dinâmico é criado a partir de um relatório da tabela dinâmica, o layout 
do relatório de gráfico dinâmico – ou seja, a posição de seus campos – é determinado 
inicialmente pelo layout do relatório da tabela dinâmica.
Agora vamos exercitar estes recursos dos gráficos dinâmicos 
apresentados!
Exercício: Módulo04_Aula04_Exe02
1. Abra o programa Excel 2007;
2. abra o arquivo TD.xlsx utilizado no exercício anterior (Exe01) 
e elimine a planilha Plan4 que possui a tabela dinâmica criada. 
Para isto, clique com o botão da direita do mouse sobre a guia 
desta planilha e escolha a opção Excluir;
Atenção!
[ 17 ]Unidade 4 - Aula 4 - Tabelas e Gráficos Dinâmicos na Prática
3. na caixa de confirmação, clique em Excluir;
Vamos construir um gráfico dinâmico com os dados presentes 
nesta planilha.
4. Com o cursor em qualquer ponto dentro dos dados presentes 
na planilha Plan1, clique na guia Inserir e em seguida escolha 
a opção Gráfico Dinâmico;
5. na caixa de diálogo Criar Tabela Dinâmica com Gráfico 
Dinâmico apenas clique em OK;
6. surgem as seguintes áreas na tela:
[ 18 ] EXCEL AVANÇADO APLICADO AO CONTROLE
7. selecione os campos UF, Situação Atual e Valor e observe 
como as duas áreas devem ficar;
8. arraste o campo Situação Atual para área Filtro de Relatório e 
observe o gráfico resultante e as demais áreas;
9. acesse o filtro de relatório na tabela dinâmica e escolha a opção 
Mec como Situação Atual e clique em OK;
[ 19 ]Unidade 4 - Aula 4 - Tabelas e Gráficos Dinâmicos na Prática
10. observe que as barras do gráfico adaptaram-se para o novo 
dado selecionado acima;
Agora, alteraremos o tipo do gráfico.
11. Com o gráfico selecionado, selecione a guia Design e em 
seguida escolha o botão Alterar Tipo do Gráfico presente no 
grupo Tipo;
12. na caixa Alterar Tipo do Gráfico, escolha o tipo Pizza e o 
subtipo 3D. 
13. ao final clique em OK;
[ 20 ] EXCEL AVANÇADO APLICADO AO CONTROLE
Observe o resultado.
Agora demonstraremos outra análise gráfica para evidenciar a 
versatilidade que o gráfico dinâmico proporciona aos usuários.
14. Agora, troque de área os campos Situação Atual e UF arrastando 
um para a área do outro e observe a alteração no gráfico;
15. observe a nova apresentação do gráfico;
16. tente outras opções de gráfico dinâmico e ao final salve o 
arquivo.
[ 21 ]Unidade 4 - Aula 4 - Tabelas e Gráficos Dinâmicos na Prática
Síntese
Nesta aula, conhecemos e aprendemos, na prática, a operação 
de criação de relatório de tabelas dinâmica, com todos os recursos, 
evidenciados na aula 3 deste módulo 4.
Também conhecemos e utilizamos o conjunto de mecanismos para 
elaboração de gráficos dinâmicos, quer seja de forma independente, quer 
seja associado ao relatório de tabela dinâmica.
[ 22 ] EXCEL AVANÇADO APLICADO AO CONTROLE
Referências bibliográficas 
Infofranco. Tabela Dinâmica com Gráfico. Autor: Evandro. Disponível 
em:<http://infofranco.com.br/site/excel/tabela-dinamica-com-
grafico/>. Acesso em 23 de março de 2012.
Microsoft. Serviço de Suporte ao Excel 2007. Visão geral dos relatórios 
de Tabela Dinâmica e de Gráfico Dinâmico. Disponível em: <http://
office.microsoft.com/pt-br/excel-help/visao-geral-dos-relatorios-de-
tabela-dinamica-e-de-grafico-dinamico-HP010177384.aspx>. Acesso 
em 26 de março de 2012.
Microsoft. Serviço de Suporte ao Excel 2003. Sobre os relatórios de 
gráfico dinâmico. Disponível em: <http://office.microsoft.com/pt-br/
excel-help/sobre-os-relatorios-de-grafico-dinamico-HP005249774.
aspx>. Acesso em 23 de março de 2012.
	Aula 1 – Importação dos Dados
	Introdução 
	1. Tipos de dados
	2. Importação de dados
	3. Navegação pela planilha
	4. Seleção de intervalos
	5. Cópia e movimentação de dados
	Síntese
	Referências bibliográficas 
	Aula 1 - Importação dos Dados 3 
	Introdução 4 
	1. Tipos de dados 5 
	2. Importação de dados 9 
	3. Navegação pela planilha 15 
	4. Seleção de intervalos 17 
	5. Cópia e movimentação de dados 20 
	Síntese 24 
	Referências bibliográficas 25 
	Excel2007_av_Unidade_1_Aula_2.pdf
	Aula 2 – Ajuste dos Dados
	1. Colagem especial
	2. Divisão e congelamento de painéis
	3. Múltiplas planilhas
	4. Classificação de dados
	Síntese
	Referências bibliográficas 
	Excel2007_av_Unidade_2_Aula_1.pdf
	Aula 1 – Operações com funções
	1. Utilização de funções
	2. Funções de ajuste de números
	3. Funções de manipulação de texto
	4. Localizar e substituir dados
	Síntese
	Referências bibliográficas 
	Excel2007_av_Unidade_2_Aula_2.pdf
	Aula 2 – Transposições e operações diversas
	1. Transposição de dados
	2. Funções de manipulação de data
	3. Formatação de números
	4. Remoção automática de dados duplicados
	Síntese
	Referências bibliográficas 
	Excel2007_av_Unidade_3_Aula_1.pdf
	Aula 1 – Fórmulas
	1. Uso de fórmulas e seus operadores
	2. Operações com datas e horas
	3. Referências em planilhas
	Síntese
	Referências bibliográficas 
	Excel2007_av_Unidade_3_Aula_2.pdf
	Aula 2 – Manipulação de nomes e textos
	1. Gerenciamento de nomes
	2. Funções de transformação de texto3. Funções de manipulação de texto
	4. Formatação condicional
	Síntese
	Referências bibliográficas 
	Excel2007_av_Unidade_3_Aula_3.pdf
	Aula 3 – Vínculos e funções de análise
	1. Vínculos externos
	2. Análise de frequência de dados
	3. Funções de análise numérica
	4. Funções de análise condicional
	Síntese
	Referências bibliográficas 
	Excel2007_av_Unidade_3_Aula_4.pdf
	Aula 4 – Funções lógicas, de informação e de pesquisa
	1. Função condicional
	2. Funções lógicas
	3. Funções de informação
	4. Funções de pesquisa e referência
	Síntese
	Referências bibliográficas 
	Excel2007_av_Unidade_4_Aula_1.pdf
	Aula 1 – Ferramentas de agrupamento de dados
	1. Consolidação de dados
	2. Filtros de dados
	3. Subtotais
	Síntese
	Referências bibliográficas 
	Excel2007_av_Unidade_4_Aula_2.pdf
	Aula 2 – Base de dados e gráficos
	1. Base de dados do Access
	2. Base de dados da web
	3. Outras bases de dados
	4. Criação de gráficos
	Síntese
	Referências bibliográficas 
	Excel2007_av_Unidade_4_Aula_3.pdf
	Aula 3 – Tabelas dinâmicas (Visão Geral)
	1. Noções básicas
	2. Primeiros passos
	3. O acesso
	4. A configuração dos campos e da tabela
	5. Operações com as tabelas dinâmicas
	Síntese
	Referências bibliográficas 
	Excel2007_av_Unidade_4_Aula_4.pdf
	Aula 4 – Tabela e gráficos dinâmicos na prática
	1. Relatório de tabela dinâmica na prática
	2. Gráficos dinâmicos
	Síntese
	Referências bibliográficas