Excel Avançado com fórmulas interessantes

Excel avançado com dicas interessantes

1. Curso: 
Excel 
Avançado
Formador: 
Carlos Maia

2. 2
Programa Programa
parapara
o o 
MM
óó
dulodulo
P
Formatações avançadas da folha de cálculo
Protecção de dados
Estilos de formatação
Modelos
P
Fórmulas e Funções
Fórmulas e funções avançadas (IF; Vlookup; Hlookup, etc...)
Definir e utilizar nomes de células
Auditoria de fórmulas
P
Séries de dados
Preenchimento automático de séries
P
Gestão de dados
Gerir dados
Filtros automáticos e avançados
Ordenação de dados
As tabelas dinâmicas
Os níveis de dados
A utilização de subtotais
A consolidação de dados

3. 3
Programa Programa
parapara
o o 
MM
óó
dulodulo
P
As técnicas de simulação Cenários
Atingir objectivo
O Solver
P
A criação de gráficos Formatação avançada de gráficos
Formatação de gráficos tridimensionais
P
Criar e utilizar macros
P
Os Formulários
P
Ferramentas de Revisão
P
As ligações do Excel com outras aplicações Importar texto do Word
Importar informação de uma base de dados
P
A integração do Excel com a Internet A transformação de documentos Excel em formato HTML 

4. Revisão Revisão
de de 
conceitos conceitos

5. 5
FunFun
çç
õesões
= SUM(2;3;4;5)
Nome da função
Argumentos da função
Caracterseparador
Sintaxe
Nome_da_função(
argumento; argumento2;…
)
Exemplo

6. 6
Algumas Algumas
FunFun
çç
õesões
Average Média
Min Minimo
Max Máximo
Count Contar
Countif Contarse
Sum Somar
Sumif Somarse
Power Exponênciação
Today Hoje
Date Data
Now AgoraNome Resultado

7. 7
Demonstra Demonstra
çç
ãoão
da da 
utiliza utiliza
çç
ãoão
de de 
funfun
çç
õesões

8. 8
Criar Criar
ff
óó
rmulas rmulas
complexas complexas
usando usando
o o 
Function Wizard Function Wizard
Wizard-Assistenteparaconstruçãode fórmulas.
Chamadado
Function Wizard

9. 9
Utiliza Utiliza
çç
ãoão
do do 
Function Wizard Function Wizard
Sintaxeda funçãoseleccionada
Categorias
de funções
Funções

10. 10
Calculo Calculo
da da 
mm
éé
diadia
usando usando
o o 
Wizard Wizard

11. 11
Demonstra Demonstra
çç
ãoão
da da 
utiliza utiliza
çç
ãoão
do do 
Function  Function 
Wizard Wizard

12. 12
Estilos Estilos

13. 13
Novo Estilo Novo Estilo
1.
Seleccionar a célula com o 
estilo criado
2.
Format -> Style 
3.
Escreverum nomeparao 
estilo
4.
ClicaremAdd
5.
Efectuaras modificações
necessárias
6.
OK

14. 14
Juntar Estilo de outra Folha Juntar Estilo de outra Folha
1.
Format -> Style 
2.
ClicaremMerge
3.
OK

15. 15
Botão de Estilo Botão de Estilo
P
Na barra de formatação, clicar com o botão direito e es colher a 
opção “customize”
P
Arrastar de seguida o botão para a barra de formatação.

16. 16
Estilos Estilos
P
Quero usar estilos que criei em outras folhas. 
Como posso usá-los?
•Abrir um livro onde pretendemos juntar os estilos
•Juntar os estilos (botão “merge”)
•Gravar como Template (*.xlt)

17. 17
Formata Formata
çç
ão de c ão de c
éé
lulas mediante  lulas mediante 
condi condi
çç
õesões
P
Objectivo: Formatar células mediante a 
verificação de condições (Format rConditional 
Formatting).
P
Exemplo: assinalar todas as notas inferiores ou 
iguais a 12 valores

18. 18
Resultado .... Resultado ....

19. 19
GrGr
áá
ficos ficos
1. Seleccionara
àreaquecontém
a informação
parao gráfico.
2. Chamaro
“chart wizard”
3. Seguirospassos
apresentados
Chart wizard

20. 20
A A 
utiliza utiliza
çç
ãoão
do do 
““
Chart Wizard Chart Wizard
””
Seleccionaro tipode gráfico

21. 21
Verificar/ Identificara gamade valores A A 
utiliza utiliza
çç
ãoão
do do 
““
Chart Wizard Chart Wizard
””

22. 22
A A 
utiliza utiliza
çç
ãoão
do do 
““
Chart Wizard Chart Wizard
””
Titulo do gráfico
Etiqueta parao
eixo do X
Etiqueta parao
eixo do Y

23. 23
As opçõesparaa colocaçãoda legenda
A A 
utiliza utiliza
çç
ãoão
do do 
““
Chart Wizard Chart Wizard
””

24. 24
A A 
utiliza utiliza
çç
ãoão
do do 
““
Chart Wizard Chart Wizard
””
As etiquetasno gráfico(data labels)

25. 25
A A 
utiliza utiliza
çç
ãoão
do do 
““
Chart Wizard Chart Wizard
””
A localizaçãodo gráfico

26. 26
O O 
grgr
áá
ficofico
comocomo
objecto objecto

27. 27
Formata Formata
çç
ãoão
de de 
grgr
áá
ficos ficos
Depoisdo gráficoelaboradopoder-se-áalterara sua
aparência, formatandoosvárioselementosqueo
constituem.
Háváriasmaneirasde aplicaralterações, no entanto, a mais
fáciléum duploclique no elementoemcausa.

28. 28
Modifica Modifica
çç
ões ao gr ões ao gr
áá
ficofico
P
Mudar tipo de gráfico
P
Mudar séries de dados

29. 29
Modifica Modifica
çç
ões ao gr ões ao gr
áá
ficofico
P
Adicionar dados
P
Ou com copiar e colar especial 
em cima do gráfico

30. 30
Modifica Modifica
çç
ões ao gr ões ao gr
áá
ficofico
P
Mostrar a segunda série

31. 31
Modifica Modifica
çç
ões ao gr ões ao gr
áá
ficofico
P
Mostrar valores em ordem inversa

32. 32
Modifica Modifica
çç
ões ao gr ões ao gr
áá
ficofico
P
Adicionar uma TrendLine

33. 33
Modifica Modifica
çç
ões ao gr ões ao gr
áá
ficofico
P
Adicionar uma imagem às séries

34. 34
GrGr
áá
ficos 3D ficos 3D
Domingo
Segunda
Terça
Quarta
Quinta
Sexta
Sábado
0
20
40
60
80
100
120
140
160
Series1 Series2

35. 35
Modificar Gr Modificar Gr
áá
ficos 3D ficos 3D
P
ModificarVisão
•Seleccionar as paredes

36. 36
Modificar Gr Modificar Gr
áá
ficos 3D ficos 3D
P
MudarSériesde dados
•Format -> Selected Data Series

37. 37
Demonstra Demonstra
çç
ãoão
da da 
criacria
çç
ão/formata ão/formata
çç
ãoão
de de 
grgr
áá
ficos ficos

38. 38
Trabalhar com dados em listas Trabalhar com dados em listas
P
Ordenação de dados

39. 39
CritCrit
éé
rio de ordena rio de ordena
çç
ãoão
1º critério de ordenação 2º critério de ordenação 3º critério de ordenação

40. 40
Orienta Orienta
çç
ão da Ordena ão da Ordena
çç
ãoão
P
O Excel permite alterar a 
orientação da ordenação, 
isto é, escolher entre 
ordenação por linha ou 
coluna
P
DatarSort...... (Options)

41. 41
SS
éé
ries de Preenchimento ries de Preenchimento

42. 42
Criar S Criar S
éé
ries de Preenchimento ries de Preenchimento
P
Seleccionar e arrastar com o botão direito do rato

43. 43
SS
éé
ries e Tendências ries e Tendências
P
Baseado no histórico
•Exemplo – Saber o crescimento das vendas nos próximos meses 
baseado nos últimos três

44. 44
Criar Listas / S Criar Listas / S
éé
riesries
P
Tools-> Options

45. 45
Definir nomes de c Definir nomes de c
éé
lulas / intervalos lulas / intervalos
OU

46. 46
Utilizar nomes de c Utilizar nomes de c
éé
lulas /  lulas / 
intervalos intervalos

47. 47
CriaCria
çç
ão de  ão de 
Subtotais Subtotais
P
Em Excel épossível criar, de forma automática, 
subtotaisde valores em listas sem recorrer à
criação de formulas;
P
Opção:  Data
r
Subtotals

48. 48
CriaCria
çç
ão de  ão de 
Subtotais Subtotais
Indicadores

49. 49
Remover  Remover 
Subtotais Subtotais
Remover Subtotais

50. 50
Listas e Formul Listas e Formul
áá
rios de Dados rios de Dados
P
Por definição, uma base de dados éuma colecção 
de informação organizada;
P
Uma base de dados éconstituída principalmente 
por tabelas, registos e Campos;
P
Em Excel, uma lista presente numa folha pode 
servir como uma base de dados; 
P
Entende-se por lista um conjunto de múltiplas 
linhas, com tipos de dados semelhantes;
P
Em Excel as linhas servem de registos e as colunas 
de campos;

51. 51
Tabelas Tabelas
RegistoNome do campo
As funcionalidades para tratar dados estão dispon As funcionalidades para tratar dados estão dispon
íí
veis no menu  veis no menu 
““
DATADATA
””
Campo

52. 52
CriaCria
çç
ão de formul ão de formul
áá
riosrios
P
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
”
P
Utilização do formulário
•A utilização do formulário é possível através dos b otões 
disponíveis na caixa de dialogo respectiva.

53. 53
Aplica Aplica
çç
ão de Formul ão de Formul
áá
riosrios
DataData
--
>>
FormForm

54. 54
Introdu Introdu
çç
ão de dados no  ão de dados no 
formul 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

55. 55
Navega Navega
çç
ão num formul ão num formul
áá
riorio
Registo anterior
Registo Seguinte
(respectivamente)

56. 56
Elimina Elimina
çç
ão de registos ão de registos
Seleccionar o registo a 
eliminar e fazer um 
clique no botão Delete

57. 57
Consultar dados no Formul Consultar 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
<; >; >=; <=; <>

58. 58
Utiliza Utiliza
çç
ão de Filtros ão de Filtros
P
A utilização de filtros aplica-se quando 
pretendemos esconder temporariamente 
informação;
P
Quando se aplicam filtros, o Excel esconde todos 
os valores que não respeitem o critério 
estabelecido;
P
Podemos utilizar filtros mais ou menos elaborados;
P
Existem filtros automáticos, contudo épossível 
elaborar os nossos próprios filtros;

59. 59
Utiliza Utiliza
çç
ão de Filtros ão de Filtros
Opção: 
Data - Filter

60. 60
Filtros autom Filtros autom
áá
ticos ticos
Aparecem setas
Através das setas podemos escolher o critério a aplicar.

61. 61
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

62. 62
Utiliza Utiliza
çç
ão de fun ão de fun
çç
ões de procura ões de procura
P
O Excel dispõe de 2 funções que permitem 
procurar valores relacionáveis;
•VLOOKUP (PROCV) – procura um valor na coluna mais à
esquerda, e retorna o valor da linha, presente na c oluna que 
especificamos.
•HLOOKUP (PROCH) – procura um valor comparável com a linha
P
Exemplo de aplicação
•Determinação da nota de 1 aluno em determinada disciplina

63. 63
VLOOKUP & HLOOKUP VLOOKUP & HLOOKUP
P
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 primeir a 
coluna/linha superior da tabela;
•Table array – É o intervalo de células que contém a tabel a com os 
valores;
•Column index number / Row index number – É a coluna/linha da 
Table array ónde constam os valores que estamos interessados. 

64. 64
Ver as f Ver as f
óó
rmulas nas c rmulas nas c
éé
lulas lulas
Ver Formúlas
Pode-se também usar a combinação de teclas Ctrl + Shift + ´

65. 65
Exemplos VLOOKUP Exemplos VLOOKUP
Neste caso, é necessário ordenar
previamente
a tabela para não haver erros

66. 66
Exemplos HLOOKUP Exemplos HLOOKUP
Com FALSE não é
necessário 
ordenar
previmente a tabela

67. 67
FunFun
çç
ões de Base de Dados ões de Base de Dados
P
Dcount–Similar àfunção count
P
Dcounta-Similar àfunção counta(não nulos)
P
Dsum-Similar àfunção sum
P
Daverage-Similar àfunção average
P
Etc...

68. 68
Algumas fun Algumas fun
çç
õesões

69. 69
Tabelas Dinâmicas Tabelas Dinâmicas
P
As tabelas dinâmicas permitem 
confrontar/analisar informação de acordo com as 
especificações colocadas;
P
Os campos que serão confrontados são escolhidos 
durante o processo de criação da tabela;

70. 70
CriaCria
çç
ão de tabelas dinâmicas ão de tabelas dinâmicas
DATA - PIVOT TABLE

71. 71
Passo 2 Passo 2
P
Indicação do intervalo que contém a informação
Passo 3 Passo 3
P
Escolha do posicionamento da tabela pivot

72. 72
Constru Constru
çç
ão da tabela ão da tabela
Apenas é necessário fazer drag-and-drop para os locais onde se 
pretende o campo

73. 73
Criar um gráfico
Estilos pré-definidos
Actualizar dados

74. 74

75. 75
Fazer projec Fazer projec
çç
ões sobre dados ões sobre dados
P
Utilização da função PMT()

76. 76
Exemplo I Exemplo I
NOTA
: A taxa apresentada é uma taxa ANUAL. 
Como estamos a calcular o valor da prestação 
mensal, temos que a dividir por 12 (nº de meses 
num ano)

77. 77
Exemplo I Exemplo I

78. 78
CriaCria
çç
ão de cen ão de cen
áá
riosrios
P
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;
P
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;

79. 79
CriaCria
çç
ão de cen ão de cen
áá
rios  rios 
--
Passo 1 Passo 1

80. 80
CriaCria
çç
ão de cen ão de cen
áá
rios ( rios (
contcont
))
Invocar o sumário
Escolher o tipo 
de relatório

81. 81
Resultado Resultado

82. 82
Utiliza Utiliza
çç
ão do Solver ão do Solver
P
O “Solver” utiliza-se quando se pretende encontrar a  melhor 
solução para um problema, considerando um conjunto de 
restrições específicas;
P
Uma restrição é um limite imposto.
P
Se o “Solver” não estiver visível:
•ToolsrAdd-insr”Solver Add-in”
•Confirmar com “OK”

83. 83
Resolver o problema com o  Resolver o problema com o 
““
Solver Solver
””
P
Seleccionar: ToolsrSolver
P
Escolher a célula onde serácolocado o resultado
P
Definir as condições;
P
Definir as restrições;
P
Fazer um clique em “solver”

84. 84
Exemplo de utiliza Exemplo de utiliza
çç
ão do Solver ão do Solver
P
Considere-se a situação de um agregado familiar 
onde:
• Vencimento 1000 Euros
P
Despesas
• Desp. Familiares
• Desp. Alimentação
• Desp. Transporte
• Saldo

85. 85
Condi Condi
çç
õesões
P
Problema: Qual o máximo que épossível dispender
para a prestação da casa?
P
Condições:
•O saldo mensal do agregado familiar deverá ser >= 100 euros
•A prestação da habitação deverá ser <= 40% * vencimento

86. 86
SoluSolu
çç
ãoão

87. 87

88. 88
Utiliza Utiliza
çç
ão de Auditorias ão de Auditorias
P
As ferramentas de Auditingpermitem fazer uma 
auditoria da folha Excel;
P
Opção: Tools
r
Auditing

89. 89

90. 90
Utiliza Utiliza
çç
ão da Fun ão da Fun
çç
ão ão 
““
IfIf
””
P
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;
P
Épossível utilizar as funções OR (ou) e AND (e) em 
conjunção com a If;
P
Sintaxe:
•If (teste lógico; Valor se verdadeiro; Valor se Fal so)
P
Exemplo:
•If (Media>9,5; Aprovado; Reprovado)

91. 91
Utiliza Utiliza
çç
ão do  ão do 
““
IfIf
””

92. 92
Utiliza Utiliza
çç
ão do  ão do 
““
AndAnd
””

93. 93
Trabalhar com Macros Trabalhar com Macros
P
A utilização de Macros permite automatizar um 
conjunto de acções;
P
A forma de utilização ésemelhante àdo Word; 

94. 94
Criar uma Macro Criar uma Macro
Nome da Macro
Terminar a gravação 
da Macro

95. 95
Seleccionar/Invocar uma Macro Seleccionar/Invocar uma Macro

96. 96
Adicionar uma Macro a um botão  Adicionar uma Macro a um botão 
e coloc e coloc
áá
--
la na barra de Tarefas la na barra de Tarefas
P
Épossível associar a Macro a um botão para mais 
facilmente a invocar;
P
Étambém possível colocar o botão na barra de 
tarefas
P
Opção: Menu  ViewrCustomize

97. 97
Adicionar uma Macro a um botão  Adicionar uma Macro a um botão 
e coloc e coloc
áá
--
la na barra de Tarefas la na barra de Tarefas
P
Passos: 1. Invocar a opção de Menu   ViewrToolbarsrCustomize
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 Ma cro” e OK

98. 98
Adicionar uma Macro a um botão e  Adicionar uma Macro a um botão e 
coloc coloc
áá
--
la na barra de Tarefas la na barra de Tarefas
1- Invocar a opção de 
Menu   Viewr
Toolbars
2 - Fazer um clique no 
separador “Commands”

99. 99
Adicionar uma Macro a um botão  Adicionar uma Macro a um botão 
e coloc e coloc
áá
--
la na barra de Tarefas la na barra de Tarefas
3 e 4 –Fazer um clique em 
“Macros”na lista “Categories”

100. 100
Adicionar uma Macro a um botão  Adicionar uma Macro a um botão 
e coloc e coloc
áá
--
la na barra de Tarefas la na barra de Tarefas
5 - Arrastar a 
imagem 
genérica
para a barra de 
tarefas

101. 101
Adicionar uma Macro a um botão  Adicionar uma Macro a um botão 
e coloc e coloc
áá
--
la na barra de Tarefas la na barra de Tarefas
7 - Seleccionar a 
Macro pretendida 
na lista “Macro 
Name”
6 - Fazer um clique em “Modify Selection” e “Assign Mac ro”

102. 102
Gravar Gravar
documentos documentos
parapara
a a 
Internet Internet
Estaopçãopermite
dumaforma rápida,
gravardocumentos
emHTML, de forma
a tornarpossívela sua
divulgaçãonaInternet.

103. 103
Abrir Modelos Abrir Modelos

104. 104
Criar Modelos Criar Modelos

105. 105
Formul Formul
áá
riosrios
P
Definir a estrutura 1. Planear o formulário
2. Definir o esqueleto
3. Adicionar interactividade
4. Proteger o formulário
5. Distribuir como modelo ou documento
• Mail
• Rede
P
Preencher e guardar informações de um 
formulário 

106. 106
Formul Formul
áá
riosrios

107. 107
Adicionar Controles aos Formul Adicionar Controles aos Formul
áá
riosrios

108. 108
Adicionar Controles aos Formul Adicionar Controles aos Formul
áá
riosrios

109. 109
Adicionar Controles aos Formul Adicionar Controles aos Formul
áá
riosrios

110. 110
Adicionar Controles aos Formul Adicionar Controles aos Formul
áá
riosrios

111. 111
Adicionar Controles aos Formul Adicionar Controles aos Formul
áá
riosrios
P
Adicionar IFs

112. 112
Adicionar Controles aos Formul Adicionar Controles aos Formul
áá
riosrios

113. 113
Adicionar Controles aos Gr Adicionar Controles aos Gr
áá
ficos ficos

114. 114
Adicionar Controles aos Gr Adicionar Controles aos Gr
áá
ficos ficos

115. 115
Adicionar Controles aos Gr Adicionar Controles aos Gr
áá
ficos ficos

116. 116
Adicionar Controles aos Gr Adicionar Controles aos Gr
áá
ficos ficos

117. 117
Adicionar Outros Controles Adicionar Outros Controles

118. 118
Proteger folhas de c Proteger folhas de c
áá
lculo e Livros lculo e Livros
P
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 leitua.

119. 119
Protecção da FolhaProtecção do livro
Protecção da partilha
Desprotecção da Folha
Proteger folhas de c Proteger folhas de c
áá
lculo e Livros lculo e Livros

120. 120
Trabalho Cooperativo Trabalho Cooperativo
P
Revisão
•Comentários
•Tracked Changes/Registo de Alterações

121. 121
Coment Coment
áá
riosrios
P
View–Toolbars-Reviewing

122. 122
Inserir Coment Inserir Coment
áá
riorio Novo 
Comentário

123. 123
Coment Coment
áá
rios de M rios de M
úú
ltiplos Revisores ltiplos Revisores

124. 124
Coment Coment
áá
rios de M rios de M
úú
ltiplos Revisores ltiplos Revisores

125. 125
Pesquisa de Informa Pesquisa de Informa
çç
ão nos  ão nos 
Coment Coment
áá
riosrios

126. 126
Imprimir Informa Imprimir Informa
çç
ão de Coment ão de Coment
áá
riosrios

127. 127
Registo de Altera Registo de Altera
çç
õesões
Ligar 
Registo de 
Alterações

128. 128
Registo de Altera Registo de Altera
çç
õesões
Desligar 
Registo de 
Alterações

129. 129
Aceitar / Rejeitar Altera Aceitar / Rejeitar Altera
çç
õesões
P
Tools -> Track Changes -> Accept or Reject 
Changes

130. 130
Envio para Revisão Envio para Revisão

131. 131
As liga As liga
çç
ões do Excel com as  ões do Excel com as 
restantes aplica restantes aplica
çç
ões do Office  ões do Office 

132. 132