If you can't read please download the document
Upload
gcms
View
43.751
Download
29
Embed Size (px)
DESCRIPTION
Notas de aula em Excel Avançado.
Citation preview
2. Reviso 3. Introduo (1)
4. Planilha eletrnica
5. Os valores em uma planilha podem ser automaticamente calculados a partir de outros valores 6. Introduo (2)
7. Calcular impostos 8. Elaborar oramento 9. Registrar notas de alunos 10. Acompanhar a venda de produtos 11. Controlar cronograma 12. Criar grficos 13. Extrair informaes de dados 14. ... 15. Introduo (3)
16. Introduo (4) 17. Introduo (5)
18. Uma planilha composta por vrias clulas, onde os dados podero ser inseridos
19. As colunas so dispostas verticalmente, e identificadas por uma letra 20. As linhas so dispostas horizontalmente, e identificadas por um nmero 21. Introduo (6)
Por exemplo...
Qualquer ao realizada na clula selecionada, que se apresenta destacada com uma borda 22. Introduo (7)
23. Microsoft Office -> Microsoft Office Excel 2003 24. Iniciar -> Executar
25. Introduo (8)
Inserir
26. (na barra de frmulas) ou Enter Apagar
... 27. Introduo (9)
28. Barra de frmulas Formatao
29. F ormatar ->C lulas... 30. Exerccio (1)
31. A partir das notas dos alunos, calcularemos a soma destas, e em seguida a mdia de cada aluno 32. Exerccio (2)
33. Redimensionar linha (ttulos) 34. Redimensionar coluna (Nomes) 35. Formatar fonte (ttulo: Negrito, Arial 16pt) 36. Formatar borda 37. Formatar nmero (notas: nmero, 1 casa decimal) 38. Formatar alinhamento
39. Exerccio (3)
Total das Notas 40. Mdia 41. Ala de preenchimento
42. Clique duplo 43. Exerccio (4)
44. SE Remover colunas 45. Formatao condicional 46. Resultado:
47. Referncias (1)
48. Ao utilizar uma referncia em uma funo ou frmula que espera um valor, utilizado o valor existente na clula da referncia 49. Exemplo:
50. = A1 + A3 -> 23 51. = SOMA(4;A2) -> 13 52. = MDIA(A1:A3) -> 10,7 A 1 15 2 9 3 8 53. Referncias (2)
54. interessante evitar o uso de valores fixos nas frmulas, preferindo o uso de referncias 55. No Excel existem trs tipos de referncias
56. Absoluta (Com nome) 57. Mista 58. Referncias (3)
59. e esta frmula copiada ou preenchida... 60. esta referncia relativizada 61. Referncias (4)
Copiar & Colar 62. Referncias (5)
Ala de Preenchimento 63. Referncias (6)
64. e esta frmula copiada ou preenchida... 65. esta refernciano alterada 66. Referncias (7)
67. Referncias (8)
Copiar & Colar Obs: Este apenas um exemplo do comportamento das referncias absolutas. No faz sentido o uso de referncias absolutas em uma tabela desta natureza, onde o comportamento natural uma frmula distinta para cada linha 68. Referncias (9)
http://tinyurl.com/tabnotas2 69. Referncias (10)
http://tools.assembla.com/svn/gustavo/cepss/absoluta.xls 70. Referncias (11)
71. Caixa de nome 72. I nserir ->N ome ->D efinir... 73. Aps definir um nome, ele pode ser usado diretamente na frmula! 74. Referncias (12)
75. Referncias (13)
76. O elemento absoluto marcado com um $ (cifro) 77. Exemplo:
78. B$2 -> Ao copiar, a linha se manter a mesma ecoluna ser relativizada 79. Referncias (14)
80. diferentes tipos de informao so separadas entre vrias planilhas 81. comum que os valores em uma planilha sejam calculados a partir de dados em outra planilha 82. Para utilizar uma clula em uma outra planilha necessrio indicar o nome da planilha antes 83. Referncias (15)
Plan1 Plan2 Referncia:Plan2!A2 Formato:NomeDaPlanilha!Endereco 84. Referncias (16)
85. Permite o clculo de valores a partir de dados em outros arquivos 86. Os valores podem ser atualizados quando o arquivo aberto 87. Tambm possvel atualizar utilizando a opoE ditar -> Vnc u los Formato: 'Caminho[NomeArquivo.xls]'NomePlanilha!Endereco Exemplo: ='C:[teste.xls]Plan1'!$A$1 88. Referncias (17)
89. Fazer o mesmo movendo a tabela para outra pasta de trabalho 90. Formatao Condicional (1)
91. A formatao aplicada clula com o valor Usando frmulas
92. As referncias utilizadas na frmula so relativizadas 93. Referencias mistas 94. Funes Aninhadas (1)
95. Referncias para clulas contendo valores 96. Exemplo:
97. MDIA(B3:E3) 98. SE(F3>=7;APROVADO;REPROVADO) 99. SE(D3Tambm pode receber...
101. Uma funo passada para a outra, como se fosse um valor ou clula 102. O Excel calcula esta funo, e passa o resultado para a outra funo Exemplo: 103. SE(E3A2 maiorNo maior A 1 15 2 9 3 8 Qual o resultado desta frmula? =SE( condicao ; valor_se_verdadeiro ; valor_se_falso ) 1 2 3 121. Expresso Condicional (8) A1>A2 maiorNo maior A 1 15 2 9 3 8 Qual o resultado desta frmula? Resposta: maior =SE( condicao ; valor_se_verdadeiro ; valor_se_falso ) 1 2 3 122. Expresso Condicional (9) A1>A2+A3 10 A 1 15 2 9 3 8 E desta frmula? =SE( condicao ; valor_se_verdadeiro ; valor_se_falso ) 1 2 3 123. Expresso Condicional (10) A1>A2+A3 10 A 1 15 2 9 3 8 E desta frmula? Resposta:0 =SE( condicao ; valor_se_verdadeiro ; valor_se_falso ) 1 2 3 124. Expresso Condicional (11) A1>A2+A3 1SE(A3+1=A2;2;0) A 1 15 2 9 3 8 E desta frmula? =SE( condicao ; valor_se_verdadeiro ; valor_se_falso ) 1 2 3 125. Expresso Condicional (12) A1>A2+A3 1SE(A3+1=A2;2;0) A 1 15 2 9 3 8 E desta frmula? Resposta:2 =SE( condicao ; valor_se_verdadeiro ; valor_se_falso ) 1 2 3 126. Expresso Condicional (13)
http://tinyurl.com/excel-condicional A 1 15 2 9 3 8 4 Rodas dentadas 5 Acessrios 127. Sites
128. http://blog.dicasdeexcel.com/ 129. Anlise de Dados 130. Listas (1) Cabealhos http://tinyurl.com/dados-xls 131. Listas (2)
132. O Excel identifica estes cabealhos, e permite trabalhar com diretamente com o nome doscampos Operaes aplicveis
133. Filtros 134. Classificao 135. Subtotais 136. Funes de banco de dados 137. Listas (3)
138. D ados -> For m ulrio Cria nova linha Localizar linha Alterar campos As frmulas existentes so inseridas automaticamente 139. Listas (4)
140. MenuD ados -> C l assificar Campos do cabealho 141. Listas (5) Classificao 142. Listas (4)
144. Total de vendas por filial 145. 146. Listas (5)
147. Exerccio
148. Qtd: 1 149. Valor: R$ 1,00 150. Filial: Goinia Ordenar por filial 151. Utilizar a funo subtotais para exibir o valor total das vendas por filial 152. Remover os subtotais 153. Ordenar por produto 154. Utilizar a funo subtotais para exibir o valor total das vendas por produto 155. Funes Condicionais (1) CONT.SE( intervalo ; critrios ) Calcula o nmero de clulas em um intervalo que corresponde a determinados critrios. 156. Funes Condicionais (2)
157. Exemplos:
158. 1500 162. Funes Condicionais (3)
163. Funes Condicionais (4) SOMASE( intervalo pesquisa ; critrios ; intervalo soma )Realiza a soma de valores que correspondem a determinados critrios Exemplo: Calcular a soma os salrios de um departamento Calcular a soma das vendas em uma filial 164. Funes Condicionais (5)
165. Funes Condicionais (6)
166. Funes de Banco de Dados (1)
167. BDMDIA 168. BDCONTAR 169. BDEXTRAIR 170. BDMX 171. BDMN 172. BDMLTIPL 173. BDSOMA 174. Funes de Banco de Dados (2)
175. Funes de Banco de Dados (3)
A1:C8 176. Funes de Banco de Dados (4)
177. Campo onde ser realizado a operao (SOMA, MDIA,CONTAR, MULTIPL) Salario 178. Funes de Banco de Dados (5)
179. Similar a uma lista
180. Critrios para cada campo F1:G2 181. Funes de Banco de Dados (6)
182. Funes de Banco de Dados (7)
183. http://tinyurl.com/bd-excel Calcule a mdia salarial do funcionrios com idade >= 30 anos
184. Funes de Banco de Dados (8) 185. Funes de Banco de Dados (9)
186. Exemplo:
187. Salrio Lista -> Criar lista... 196. Exibir -> Barra de ferramentas -> Lista 197. Lista definida (2) Linha de insero 198. Validao (1)
200. Idade acima de 18 anos
201. Validao (2)
202. Validao (3)
203. Validao (4) Aviso
Validao Mensagem 204. Validao (5)
205. Validao (6)
206. Validao (6)
207. Validao (7)
208. Validao (8) 209. Validao (9)
http://tinyurl.com/validacao-duplicados 210. Proteo
211. Gravao Planilhas
Pastas 212. Apresentao de Dados 213. Formatao Personalizada (1)
214. CPF 215. CNPJ 216. Conta Corrente 217. Datas 218. 219. Formatao Personalizada (2)
220. Formatao Personalizada (3)
221. Aps os dois nmeros mais a direita existir um ponto 222. 123=>1.23 223. 1=>.1 224. 12=>.12 225. 12345=>123.45 226. 123456=>1234.56 227. Formatao Personalizada (4)
228. O digito s exibido se significativo
229. Zero esquerda 230. Zero direita aps vrgula (o formato contm vrgula) Exemplo:
231. Exibir no mximo 3 nmeros aps a vrgula 232. Formatao Personalizada (5)
233. Se o nmero possui menos dgitos que o formato, estes dgitos so exibidos como 0 234. Exemplo:
235. SEMPRE exibir 3 nmeros aps a vrgula, e no mnimo 4 nmeros antes 236. Formatao Personalizada (6)
237. Se o nmero possui menos dgitos que o formato, estes dgitos so exibidos como 0 238. Exemplo:
239. SEMPRE exibir 3 nmeros aps a vrgula, e no mnimo 4 nmeros antes 240. Formatao Personalizada (7)
241. Utilizados para formatar datas 242. Exemplo:
243. dd/mm => 29/03 244. dd/mmm => 29/mar 245. dd/mm/aaaa => 29/03/2009 246. mm/aaaa => 03/2009 247. mmm/aaaa => mar/2009 248. aaaa-mm-dd => 2009-03-29 249. Formatao Personalizada (7)
Formato geral:
Cores 250. Condies 251. Formatao Personalizada (5)
252. O valor da clula o mesmo independente de sua formatao 253. Estilos e Modelos (1)
254. Podem ser cadastrados, modificados, e excludos 255. Exemplo:
256. Fonte Arial; Negrito 257. Padres: Sombreado Caminho:
258. Estilos e Modelos (2)
259. O Excel permite a criao de arquivos de modelos e sua utilizao para criao de novas pastas 260. Exemplo:
261. Fluxo de caixa 262. Ficha de ponto 263. Estilos e Modelos (3)
264. E selecionar no campo tipo a opo Modelo (*.xlt) 265. Estilos e Modelos (4)
266. E na guiaGeralselecionar o modelo desejado 267. Estilos e Modelos (5)
268. http://tools.assembla.com/svn/gustavo/cepss/diario.doc
269. Mantenha as frmulas e demais configuraes intactas 270. Utilize a opoA rquivo -> Salvarc omo... para criar um modelo 271. Tente criar uma nova pasta a partir deste arquivo. 272. Preencha alguns valores e verifique se as frmulas esto sendo calculadas corretamente 273. Funes de Procura (1)
274. Funes de Procura (2) Exemplo: Procurar o estado de uma cidade 275. Funes de Procura (4)
Campos preenchidos atravs da funo de procura 276. Funes de Procura (5)
PROCV( valor_procurado ; tabela ; ndice ; intervalo )
277. Funes de Procura (6)
BUSCA INTERVALOS Primeira coluna 278. Funes de Procura (7)
PROCH( valor_procurado ; tabela ; ndice ; intervalo ) Realiza a procura nas linhas databela , neste caso, ondicerepresenta o nmero da linha 279. Funes de Procura (8)
280. http://tinyurl.com/cidades-xls 281. http://tinyurl.com/proch-xls 282. Funes de Procura (9)
283. Funes de Procura (10)
284. Dividir e Congelar Painis
285. Consolidao de Dados (1)
286. Consolidao de Dados (2)
287. MDIA 288. CONTAGEM 289. Posio/Categoria 290. Tabela dinmica (1)
291. Utilizado para analisar grandes quantidades de dados
292. Ou 293. http://tools.assembla.com/svn/gustavo/cepss/tabeladinamica.xls 294. Tabela dinmica (2)
295. Tabela dinmica (3)
296. Como identificar o meio de entrega mais utilizado para vendas no Brasil? 297. Como identificar o ms com maior volume de vendas? 298. ... 299. Tabela dinmica (4)
Produto 2 Produto 3 .... Filial 1Filial 2.... Produto 1 300. Tabela dinmica (5)
Produto 1 Produto 2 Produto 3 ... Vendedor 1 Vendedor 2 .... Filial 1 Filial 2 ... 301. Tabela dinmica (6)
302. Itens calculados 303. Analise de Metas
304. Solver Assume that youre in the market for a new home and you know that you can afford $1,800 per month in mortgage payments. You also know that a lender can issue a fixed-rate mortgage loan for 6.50 percent, based on an 80 percentloan-to-value(that is, a 20 percentdown payment ). The question is What is the maximum purchase price I can handle? In other words, what value in cell C4 causes the formula in cell C11 to result in $1,800? You could plug values into cell C4 until C11 displays $1,800; however, Excel can determine the answer much more efficiently. 305. Funes Financeiras (1)
306. Permite:
307. Calcular a taxa de juros embutida em um produto 308. Calcular o retorno de um investimento 309. Calcular o nmero de parcelas necessrias para pagar um valor 310. Funes Financeiras (2)
NPER
PGTO
VF
VP
311. Funes Financeiras (3)
312. Pretendo financiar um imvel de R$80.000,00 com prestaes mensais de R$ 500,00 ao ms, durante 10 anos. Qual o valor da entrada necessria? 313. Funes Financeiras (4)
314. Funes Financeiras (5)
315. Funes (1)
316. HOJE() 317. ANO(data) 318. MES(data) 319. DIA(data) 320. HORA(horario) 321. MINUTO(horario) 322. Funes (2)
323. M meses entre as datas. 324. D dias entre as datas. 325. ... 326. Funes (3)
327. ARREDONDAR.PARA.BAIXO(num;num_digitos) 328. AREDONDAR.PARA.CIMA(num;num_digitos) 329. PAR(num) 330. MPAR(num 331. Funes (4)
332. LIN(ref) 333. COL(ref) 334. Outros temas
335. Comentrios 336. Macros 337. Endereo Este documento pode ser encontrado nos seguintes endereos http://tools.assembla.com/svn/gustavo/cepss/aulas.odp http://tools.assembla.com/svn/gustavo/cepss/aulas.pdf