Excel Intermedio

Prévia do material em texto

1
Curso: Excel 
Intermédio
Formador: Carlos Maia
2
ProgramaPrograma parapara o o MMóódulodulo ExcelExcel
� Revisão dos conceitos básicos de Excel
•Operações com ficheiros
•Manipulação de folhas (sheet)
•Comandos de edição
•Fórmulas
•Formatações
•Funções
•Gráficos
•Impressão
3
ProgramaPrograma parapara o o MMóódulodulo ExcelExcel
� Ordenar dados
� Utilização de Subtotais
� Listas 
� Formulários
� Utilização de filtros
� Utilização das funções de procura: VLOOKUP; 
HLOOKUP
4
ProgramaPrograma parapara o o MMóódulodulo ExcelExcel
� Pivot Tables
� Projecções de dados
� Utilização de Cenários
� Utilização do Solver (introdução)
� Utilização da função “If”
� Utilização de Macros (introdução)
5
RevisãoRevisão dos dos conceitosconceitos bbáásicossicos no MS no MS 
ExcelExcel
6
Os Os ElementosElementos queque constituemconstituem o o ecrãecrã
Menu Box
Menu Bar
Control Menu Box
Scrool Bar 
Vertical (elevador)
Scrool Bar Horizontal
Botões de 
dimensionamento
Célula
7
A A utilizautilizaççãoão dos menusdos menus
As reticências
depois do nome do 
comando, significa
que estas opções
apresentarão no 
ecrã uma caixa de 
dialogo.
O triângulo à direita das opções de Menu indica
que estes abrem sub-menus.
8
CriaCriaççãoão de de documentosdocumentos
9
GravarGravar ((GuardarGuardar) ) documentosdocumentos
Pasta que armazenará
o documento
Nome do 
documento
Formato do
documento
Guardar o documento
com as opções
pretendidas.
10
GravarGravar documentosdocumentos parapara a Interneta Internet
Esta opção permite
duma forma rápida,
gravar documentos
em HTML, de forma
a tornar possível a sua
divulgação na Internet.
11
TiposTipos de de formatoformato parapara o o DocumentoDocumento
Lista de 
formatos
12
O O ““LivroLivro”” de de DocumentosDocumentos ((booksbooks))
Menu de
contexto
Nome da folha
13
AberturaAbertura de de DocumentosDocumentos
Tipo de 
documento
(formato)
Pasta que contem
o documento
Nome do 
documento
14
OperadoresOperadores
•Tipo de Operadores
•Operadores aritméticos
+ Adição
- Subtracção
* Multiplicação
/ Divisão
% Percentagem
^ Exponenciação
•Operadores de texto
& Concatenação
15
OperadoresOperadores
•Operadores lógicos
= igual
< Menor
<= Menor ou igual
> Maior
>= Maior ou igual
<> diferente
•Operadores de Referência
: Operador de conjunto
; Operador de União
16
ExemplosExemplos
Operadores Aritméticos
=2+3 Soma o valor 2 com valor 3
=2%*5000 Calcula 2% de 5000
=2^3 Calcula 2 elevado a 3
Operadores de Texto
=“bar”&”co” O resultado será barco
17
ExemplosExemplos
Operadores Lógicos
=2>3 O resultado será FALSE (falso) na medida
em que a expressão é falsa. 
O valor 2 é inferior ao 3
Operadores de Referência
=SUM(a2:c2)
18
PrecedênciaPrecedência dos dos OperadoresOperadores
1 % Percentagem
2 ^ Exponenciação
3 * e / Multiplicação e Divisão
4 + e - Soma e Subtracção
Precedência Símbolo Nome
19
ModosModos de de SelecSelecççãoão
Utilizando a
tecla CTRL
Clique sobre
a letra
Seleccionar
a area
20
TiposTipos de de ReferênciaReferência
Referências Relativas
ColunaLinha
•Sintaxe
A6
Façamos uma cópia, da célula A6 para a célula C6 com o objectivo
de posicionar o valor noutra célula.
Nota-se uma
alteração
relativamente
às referências
Resultado
21
TiposTipos de de ReferênciaReferência
Referências Absolutas
•Sintaxe
$A$2
Façamos uma cópia, da célula A6 para a célula C6 com o objectivo
de posicionar o valor noutra célula.
Resultado
Nota-se uma
manutenção
relativamente
às referências
22
TiposTipos de de ReferênciaReferência
Referências Mistas
Este tipo de referência permite conjugar e relacionar os
dois tipos anteriores, consoante as necessidades
do utilizador.
Neste caso apenas DEPENDERÃO da posição da
posição da fórmula, as referências NÃO precedidas
do símbolo $ (dolar)
Exemplo:
A$2
$A2
23
DemonstraDemonstraççãoão da da utilizautilizaççãoão
de de referênciasreferências MistasMistas
24
TiposTipos de de ReferênciaReferência
Referências a outras folhas (sheet) do trabalho (workspace)
Sintaxe
[nome da folha]![célula]
Nome da 
folha (sheet) Célula
Iva!B2
Por esta razão a célula fixada
terá como referência:
25
FormataFormataççõesões de de LinhasLinhas e e ColunasColunas
Demasiado texto
para o tamanho
da coluna
Aumentar a largura
da coluna com o rato
Aumentar a largura
das linhas e colunas
Aumentar a largura
da coluna com o rato
Aumentar a largura
das linhas e colunas
26
FormataFormataççãoão de de CCéélulaslulas
27
FormataFormataççãoão de de CCéélulaslulas
Tipos de Letra
Fonte
Estilo
Tamanho
28
FormataFormataççãoão de de CCéélulaslulas
Alinhamento
29
FormataFormataççãoão de de CCéélulaslulas
Contornos
(borders)
Estilo da
linha
Cor
Local a
colocar o border
30
FormataFormataççãoão de de CCéélulaslulas
Sombreados
Padrões
31
DemonstraDemonstraççãoão da da 
utilizautilizaççãoão de de formatosformatos
32
ImpressãoImpressão
Impressora
Número de
cópias
Tipo de 
impressão
Área a 
imprimir
33
PrevisãoPrevisão da da ImpressãoImpressão
Configurações Margens
34
ConfiguraConfiguraççãoão da da PPááginagina
Folha na horizontal
Ajuste de escalaFolha na vertical
35
ConfiguraConfiguraççãoão das das margensmargens
36
ConfiguraConfiguraççãoão do do cabecabeççalhoalho e e rodaprodapéé
Cabeçalhos pre-definidos
Rodapés pré-definidos
Alterar o cabecalho
Alterar o rodapé
Previsão do cabeçalho
Previsão do rodapé
37
FFóórmulasrmulas de de CCáálculolculo
Fórmulas Simples:
Fórmula
1ª hipótese 2ª hipótese
Fórmula
38
FFóórmulasrmulas de de CCáálculolculo
Aparentemente as fórmulas parecem ter o mesmo efeito, 
uma vez que o resultado é igual, mas na realidade elas
SÃO COMPLETAMENTE DIFERENTES.
Na primeira hipótese caso modifique os valores que se 
encontram nas células da linha, o resultado PERMANECE
intacto.
Na segunda hipótese se modificar um dos valores que se 
encontram nas células da linha, o resultado
é automaticamente modificado.
39
FFóórmulasrmulas de de CCáálculolculo
Fórmulas mais complexas:
Noção de Range -
Intervalo (conjunto) de células.
Ex: A1+B2+C2
A2:C2
De A2 até C2
Lê-se
40
DemonstraDemonstraççãoão da da utilizautilizaççãoão
de de ffóórmulasrmulas
41
FunFunççõesões
= SUM(2;3;4;5)
Nome da função
separador
Sintaxe
Nome_da_função(argumento; argumento2;…)
Exemplo
Argumentos da função
42
AlgumasAlgumas FunFunççõesões
Average Média
Min Minimo
Max Máximo
Count Contar
Countif Contar se
Sum Somar
Sumif Somar se
Power Exponênciação
Today Hoje
Date Data
Now Agora
Nome Resultado
43
DemonstraDemonstraççãoão da da utilizautilizaççãoão
de de funfunççõesões
44
CriarCriar ffóórmulasrmulas complexascomplexas atravatravééss da da 
opopççãoão Function WizardFunction Wizard
Wizard - Assistente para construção de fórmulas.
Chamada do 
Function Wizard
45
UtilizaUtilizaççãoão do do Function WizardFunction Wizard
Funções
Sintaxe da função seleccionada
Tipos
46
CalculoCalculo da da mméédiadia usandousando o o WizardWizard
47
DemonstraDemonstraççãoão da da 
utilizautilizaççãoão do do Function Function 
WizardWizard
48
GrGrááficosficos
1. Seleccionar a 
área que contém
a informação
para o gráfico.
2. Chamar o
“chart wizard”
3. Seguir os passos
do “chart Wizard”
“Chartwizard”
49
A A utilizautilizaççãoão do do ““Chart WizardChart Wizard””
Seleccionar o tipo de gráfico
50
Verificar / Identificar a gama de valores
A A utilizautilizaççãoão do do ““Chart WizardChart Wizard””
51
A A utilizautilizaççãoão do do ““Chart WizardChart Wizard””
Titulo do gráfico
Etiqueta para o 
eixo do X
Etiqueta para o 
eixo do Y
52
As opções para a colocação da legenda
A A utilizautilizaççãoão do do ““Chart WizardChart Wizard””
53
A A utilizautilizaççãoão do do ““Chart WizardChart Wizard””
As etiquetas no gráfico (data labels)
54
A A utilizautilizaççãoão do do ““Chart WizardChart Wizard””
A localização do gráfico
55
O O grgrááficofico comocomo objectoobjecto
56
FormataFormataççãoão de de grgrááficosficos
Depois do gráfico elaborado, pode ser alterada a sua
aparência, formatando os vários elementos que o
constituem.
Estas operações são possíveis de várias hipóteses, no 
entanto, a mais fácil é um duplo clique no elemento em causa.
57
DemonstraDemonstraççãoão da da 
criacriaçção/formataão/formataççãoão de de 
grgrááficosficos
58
Trabalhar com dados em tabelasTrabalhar com dados em tabelas
� Ordenação de dados
59
CritCritéério de ordenario de ordenaççãoão
1º critério de ordenação
2º critério de ordenação
3º critério de ordenação
60
OrientaOrientaçção da Ordenaão da Ordenaççãoão
� O Excel permite alterar a 
orientação da ordenação, 
isto é, escolher entre 
ordenação por linha ou 
coluna
� Data�Sort...... (Options)
61
CriaCriaçção de ão de SubtotaisSubtotais
� Em Excel é possível criar, de forma automática, 
subtotais de valores em listas sem recorrer à
criação de fórmulas;
� Opção: Data�Subtotals
62
CriaCriaçção de ão de SubtotaisSubtotais
Indicadores
63
Remover Remover SubtotaisSubtotais
Remover Subtotais
64
Listas e FormulListas e Formuláários de Dadosrios de Dados
� Por definição uma base de dados é uma colecção 
de informação organizada
� Uma base de dados é constituída, entre outros 
elementos, por tabelas, com registos e campos
� Em Excel, uma lista numa folha pode servir como 
uma base de dados
� Entende-se por Lista um conjunto de múltiplas 
linhas, com tipos de dados semelhantes
� Em Excel, as linhas servem de registos e as 
colunas de campos
65
TabelasTabelas
Campo
Registo
Nome do campo
As funcionalidades para tratar dados estão disponAs funcionalidades para tratar dados estão disponííveis no menu veis no menu ““DATADATA””
66
CriaCriaçção de formulão de formulááriosrios
� Para criar um formulário é necessário:
• Seleccionar a informação que pretendemos tratar através de 
formulários;
• Escolher no menu “Data” a opção “Form”
� Utilização do formulário
• A utilização do formulário é possível através dos botões 
disponíveis na caixa de dialogo respeitante.
67
AplicaAplicaçção de Formulão de Formulááriosrios
Data Data �������� FormForm
68
IntroduIntroduçção de dados no formulão de dados no formulááriorio
Fazer um clique no 
botão “new” e preencher 
a informação para os campos
As alterações efectuadas 
reflectem-se na lista que 
originou o formulário
69
NavegaNavegaçção num formulão num formulááriorio
Registo anterior
Registo Seguinte
(respectivamente)
70
EliminaEliminaçção de registosão de registos
Seleccionar o registo a 
eliminar e fazer um 
clique no botão Delete
71
Consultar dados no FormulConsultar dados no Formulááriorio
1- Fazer um clique no botão “Criteria”
2- Colocar o critério pretendido
3- Premir “Enter”
Na escolha do critério
podem utilizar-se os 
operadores lógicos
<; >; >=; <=; <>
72
UtilizaUtilizaçção de Filtrosão de Filtros
� A utilização de filtros aplica-se quando 
pretendemos esconder temporariamente 
informação;
� Quando se aplicam filtros o Excel esconde todos os 
valores que não respeitem o critério estabelecido;
� Podemos utilizar filtros mais ou menos elaborados;
� Existem filtros automáticos, contudo é possível 
elaborar os nossos próprios filtros;
73
UtilizaUtilizaçção de Filtrosão de Filtros
Opção: Data - Filter
74
Filtros automFiltros automááticosticos
Aparecem setas
Com as setas podemos escolher o critério a aplicar.
75
CriaCriaçção de filtros personalizadosão de filtros personalizados
Criar o critério pretendido
Podemos criar filtros com 
dois critérios de comparação 
utilizando and/or
76
UtilizaUtilizaçção de funão de funçções de procuraões de procura
� O Excel dispõe de 2 funções que permitem 
procurar valores relacionáveis;
• PROCV (VLOOKUP) – procura um valor na coluna mais à
esquerda, e retorna o valor da linha, presente na coluna que 
especificamos.
• PROCH (HLOOKUP) – procura um valor comparável com a linha
� Exemplo de aplicação
• Determinação da nota de 1 aluno em determinada disciplina
77
VLOOKUP & HLOOKUPVLOOKUP & HLOOKUP
� Sintaxe das funções:
•VLOOKUP(Lookup value; Table array; Column index number)
•HLOOKUP(Lookup value; Table array; Row index number)
Onde:
• Lookup value – É o valor que a função procura na primeira 
coluna/linha superior da tabela;
• Table array – É o intervalo de células que contém a tabela com os 
valores;
• Column index number / Row index number – É a coluna/linha da 
Table array ónde constam os valores que estamos interessados. 
78
ExemplosExemplos
Ver Formúlas
79
Exemplos VLOOKUPExemplos VLOOKUP
Ordenar previamente a tabela 
para não haver erros (neste caso)
80
Exemplos HLOOKUPExemplos HLOOKUP
Com FALSE não é
necessário 
ordenar
previmente a tabela
81
FunFunçções de Base de Dadosões de Base de Dados
� Dcount – Similar à função count
� Dcounta - Similar à função counta (não nulos)
� Dsum - Similar à função sum
� Daverage - Similar à função average
� Etc...
82
ExemplosExemplos
83
Tabelas DinâmicasTabelas Dinâmicas
� As tabelas dinâmicas permitem 
confrontar/analisar informação de acordo com as 
especificações colocadas;
� Os campos que serão confrontados são escolhidos 
durante o processo de criação da tabela;
84
CriaCriaçção de tabelas dinâmicasão de tabelas dinâmicas
DATA - PIVOT TABLE
Passo 1Passo 1
85
Passo 2Passo 2
� Indicação do intervalo que contém a informação
Passo 3Passo 3
� Escolha do posicionamento da tabela pivot
86
ConstruConstruçção da tabelaão da tabela
Apenas é necessário fazer drag-and-drop para os locais onde se 
pretende o campo
87
Criar um gráfico
Estilos pré-definidos
Actualizar dados
88
89
Fazer projecFazer projecçções sobre dadosões sobre dados
� Utilização da função PMT()
90
Exemplo IExemplo I
91
Exemplo IIExemplo II
92
CriaCriaçção de cenão de cenááriosrios
� A utilização de cenários permite atribuir nomes a 
um conjunto de valores, que podem ser 
utilizados, para efectuar uma série de operações 
numa folha;
� Passos para criar um cenário
1. Seleccionar a primeira célula a alterar com base no cenário;
2. Escrever um valor que se pretende incluir no cenário;
3. Seleccionar o intervalo de células que contém os valores que 
se pretendem alterar com base no cenário;
93
CriaCriaçção de cenão de cenááriosrios
94
CriaCriaçção de cenão de cenááriosrios
Invocar o sumário
Escolher o tipo 
de relatório
95
ResultadoResultado
96
UtilizaUtilizaçção do Solverão do Solver
� O “Solver” utiliza-se quando se pretende 
encontrar a melhor solução para um problema, 
considerando um conjunto de restrições 
específicas;
� Uma restrição é um limite imposto.
� Se o “Solver” não estiver visível ir a:
• Tools�Add-ins�”SolverAdd-in”
• Confirmar com “OK”
97
Resolver o problema com o Resolver o problema com o ““SolverSolver””
� Seleccionar: Tools�Solver
� Escolher a célula onde será colocado o resultado
� Definir as condições;
� Definir as restrições;
� Fazer um clique em “solver”
98
Exemplo de utilizaExemplo de utilizaçção do Solverão do Solver
� Considere-se a situação de um agregado familiar 
onde:
• Vencimento 1000 Euros
� Despesas
• Desp. Familiares
• Desp. Alimentação
• Desp. Transporte
• Saldo
99
CondiCondiççõesões
� Problema: Qual o máximo que é possível dispender
para a prestação da casa?
� Condições:
• O saldo mensal do agregado familiar deverá ser >= 100 euros
• A prestação da habitação deverá ser <= 40% * vencimento
100
SoluSoluççãoão
101
102
UtilizaUtilizaçção de Auditoriasão de Auditorias
� As ferramentas de Auditing permitem fazer uma 
auditoria da folha Excel;
� Opção: Tools�Auditing
103
Auditoria de fAuditoria de fóórmulasrmulas
104
Proteger folhas de cProteger folhas de cáálculo e Livroslculo e Livros
� A protecção efectua-se mediante a colocação de 
passwords. É possível:
• Definir uma password para evitar um acesso não autorizado;
• Definir uma password para evitar um alterações não 
autorizadas;
• Abrir um livro (workbook) só
para leitura.
105
Proteger folhas de cProteger folhas de cáálculo e Livroslculo e Livros
Protecção da Folha Protecção do livro
Protecção da partilha
Desprotecção da Folha
106
UtilizaUtilizaçção da Funão da Funçção ão ““IfIf””
� A função “if” (Se) utiliza-se para testar conteúdos 
das células e determinar se as células cumprem 
com determinadas condições;
� É possível utilizar as funções OR (ou) e AND (e) em 
conjunção com a If;
� Sintaxe:
• If (teste lógico; Valor se verdadeiro; Valor se Falso)
� Exemplo:
• If (Media>9,5; Aprovado; Reprovado)
107
UtilizaUtilizaçção do ão do ““IfIf””
108
UtilizaUtilizaçção do ão do ““AndAnd””
109
Trabalhar com MacrosTrabalhar com Macros
� A utilização de Macros permite automatizar um 
conjunto de acções
� A forma de utilização é semelhante à do Word
110
Criar uma MacroCriar uma Macro
Nome da Macro
Terminar a gravação 
da Macro
111
Seleccionar/Invocar uma MacroSeleccionar/Invocar uma Macro
112
Associar uma Macro a um botão e Associar uma Macro a um botão e 
coloccolocáá--la na barra de Tarefasla na barra de Tarefas
� É possível associar a Macro a um botão para mais 
facilmente a invocar;
� Pode-se colocar o botão na barra de tarefas
� Opção: Menu View�Customize
113
� Passos:
1. Invocar a opção de Menu View�Toolbars �Customize
2. Fazer um clique no separador “Commands”
3. Fazer um clique em “Macros” na lista “Categories”
4. Fazer um clique no botão “Custom Button”
5. Arrastar a imagem genérica para a barra de tarefas
6. Fazer um clique em “Modify Selection”
7. Seleccionar a Macro pretendida na lista “Assign Macro” e OK
Associar uma Macro a um botão e Associar uma Macro a um botão e 
coloccolocáá--la na barra de Tarefasla na barra de Tarefas
114
1. Invocar a opção de 
Menu View � Toolbars
2. Fazer um clique no 
separador “Commands”
Associar uma Macro a um botão e Associar uma Macro a um botão e 
coloccolocáá--la na barra de Tarefasla na barra de Tarefas
115
Adicionar uma Macro a um botão e Adicionar uma Macro a um botão e 
coloccolocáá--la na barra de Tarefasla na barra de Tarefas
3 e 4. Fazer um clique em 
“Macros” na lista “Categories”
116
Adicionar uma Macro a um botão e Adicionar uma Macro a um botão e 
coloccolocáá--la na barra de Tarefasla na barra de Tarefas
5. Arrastar a 
imagem 
genérica
para a barra de 
tarefas
117
Adicionar uma Macro a um botão e Adicionar uma Macro a um botão e 
coloccolocáá--la na barra de Tarefasla na barra de Tarefas
7. Seleccionar a 
Macro pretendida 
na lista “Macro 
Name”
6. Fazer um clique em “Modify Selection” e “Assign Macro”
118