47
0757 - Folha de cálculo - funcionalidades avançadas Conteúdo Detalhe da UFCD........................................................... 3 Objectivos...............................................................3 Conteúdos................................................................3 Introdução ao Excel....................................................... 4 A Tela do Excel..........................................................4 Barras e Menus............................................................ 5 Conceitos Básicos do Excel................................................ 8 Componentes do Excel.....................................................8 Intervalo de Células....................................................10 Formas do Ponteiro do Mouse.............................................10 Formatação da Planilha................................................... 12 Alteração de Fontes e Alinhamento de Texto..............................12 Envolvendo Células com uma Moldura....................................13 Fórmulas................................................................. 15 Copiando Fórmulas.......................................................18 Intervalo de Células....................................................18 Referências Fixas e Relativas............................................ 20 Funções.................................................................. 21 Funções Matemáticas.....................................................21 Função Soma...........................................................21 1- Quando o módulo fizer parte de um curso identificar o curso, sempre que o módulo estiver codificado iniciar a designação pelo código M.PF.14.01 FORMADOR (A): Filipa Joaquim

Manual Folha de Calculo

Embed Size (px)

Citation preview

Contedo

3Detalhe da UFCD

3Objectivos

3Contedos

4Introduo ao Excel

4A Tela do Excel

5Barras e Menus

8Conceitos Bsicos do Excel

8Componentes do Excel

10Intervalo de Clulas

10Formas do Ponteiro do Mouse

12Formatao da Planilha

12Alterao de Fontes e Alinhamento de Texto

13Envolvendo Clulas com uma Moldura

15Frmulas

18Copiando Frmulas

18Intervalo de Clulas

20Referncias Fixas e Relativas

21Funes

21Funes Matemticas

21Funo Soma

23Funo Mult

23Funo Raiz

23Funo Int

24Funo Abs

25Funes de Data e Hora

31Funes de Texto

31Funo Concatenar

31Funo Num.Caract

32Funo Esquerda e Direita

33Funes Lgicas

33Funo Se

35Reviso Funo Lgica

36GRFICOS

38Impresso

40Exerccio de Reviso

Detalhe da UFCD

0757 - Folha de clculo - funcionalidades avanadas

Carga Horria: 25 horas

Objectivos

Executar ligaes entre mltiplas folhas de clculo.

Efetuar a anlise de dados.

Automatizar aes atravs da utilizao de macros.

ContedosMltiplas folhas de clculo

Mltiplas folhas

Reunio de folhas de clculo

Ligao entre folhasResumo de dados

Insero de subtotais

Destaques

Relatrios Anlise de dados

Anlise de dados em tabelas e listas

- Criao, ordenao e filtragem de dados

- Formulrios

Criao e formatao de uma tabela dinmica

Utilizao de totais e subtotais

Frmulas em tabelas dinmicas

Elaborao de grficosMacros

Macros pr-definidas

Macros de personalizao das barras de ferramentas

Criao e gravao de uma macro

Atribuio de uma macro a um boto

Execuo de uma macroIntroduo ao ExcelO Excel considerado um software de planilha eletrnica ou de clculos. Com o Excel se pode fazer desde simples clculos, at clculos mais avanados como financeiros, matemticos, lgicos, estatsticos, etc. Pode-se fazer desde controle de gastos da sua casa, at controle de estoque ou fluxo de caixa de uma empresa.Este material apresenta opes bsicas e avanadas, lembrando esta apostila servir como referncia para a maioria dos comandos e opes do Excel, portanto use-a e cuide bem, pois ir auxili-lo por um bom caminho.A Tela do Excel

Barra de Acesso RpidoBarra de Ttulo

Barra de MenuBarra de FrmulasClula AtivaPlanilhas

Barra de StatusBarra de Zoom

Barras e MenusBarra de Menu esta barra exibe os nomes dos menus de aplicaes permitindo utilizar os vrios recursos que o Excel:

Barra de Ferramentas Padro um conjunto de botes que permite agilizar as operaes mais utilizadas do Excel (Esta barra aparecia at a verso 2003)

, na verso 2007 estas opes so acessadas atravs do coneA Barra de Ferramentas Formatao que aparecia at a verso 2003, agora esta no menu INICIO

Barra de Frmulas usada para inserir ou editar dados em clulas de planilhas ou em grficos. Para incluir dados, selecione uma clula, digite os dados e selecione com o mouse a caixa de entrada da barra de frmula () ou pressione ENTER. Para editar dados, selecione a barra de frmula com o mouse ou pressione F2. Em seguida, digite as alteraes e selecione com o mouse a caixa de entrada, ou pressione ENTER. Para cancelar as alteraes, pressione o boto do mouse sobre a caixa de cancelamento da barra de frmula (X) ou pressione ESC. A seguir so mostradas a Caixa de Nomes e a Barra de Frmulas com seus respectivos botes para que se possa conhecer o ponto em deve ser clicado o mouse para a devida operao desejada.:

Barra de FrmulasBarra de Status esta barra est localizada na parte inferior da tela do Excel onde so exibidas as informaes sobre o comando atualmente selecionado e o estado atual da rea de trabalho. O lado direito da barra de status mostra a barra de Zoom :

Para definir o que ser apresentado na Barra de Status, basta clicar com o boto da direita do mouse nesta barra e marcar os itens que deseja apresentar.Menu do Excel 2007

Conceitos Bsicos do ExcelComponentes do ExcelPasta : denominada PASTA todo arquivo que for criado neste software Excel. Tudo que for criado e posteriormente ser um arquivo, porm considerado uma PASTA.Planilha: Uma planilha considerada a parte onde ser executado todo o trabalho por isso esta fundamental, se no temos planilha no podemos criar qualquer calculo que seja ento nos dar o entender que em uma pasta contem planilhas e, que cada planilha possui no total de 16.777.216 clulas.Podemos localizar uma planilha atravs dos nomes que elas receberam inicialmente PLAN1, PLAN2...Na parte inferior da rea de Trabalho, que recebe o nome de guia de planilhas.

OBS: Dentro de uma planilha esto contidas as colunas, linhas e clulas.Coluna: o espaamento entre dois traos na vertical. As colunas do Excel so representadas em letras de acordo coma a ordem alfabtica crescente sendo que a ordem vai de A at IV, e tem no total de 256 colunas em cada planilha.Linha: o espaamento entre dois traos na horizontal. As linhas de uma planilha so representadas em nmeros, formam um total de 65.536 linhas e esto localizadas na parte vertical esquerda da planilha.Clula: As clulas so formadas atravs da interseco cruzamento de uma coluna com uma linha e, cada clula tem um endereo nome que mostrado na caixa de nomes que se encontra na Barra de Frmulas. Multiplicando as colunas pelas linhas vamos obter o total de clulas que 16.777.216.Clula Ativa a clula exibida com uma borda em negrito indicando que a ela est selecionada e onde os prximos dados digitados sero inseridos ou o prximo comando escolhido ser aplicado. Se for selecionada mais de uma clula ao mesmo tempo, a primeira ser a clula ativa e as outras sero destacadas na cor escura. Observe a figura :

ClulaAtiva

Assim, oExcel constitudo de colunas (na vertical) e linhas(na horizontal). As colunas so

identificadas porletrase iniciam pela letra A, seguindo alfabeticamenteat Z, a partir da inicia-se uma

combinao de coluna com linha para definir o endereo da clula.

A coluna final a IV, o que constitui um total de 256 colunas. E as linhas, so identificadas por nmeros e iniciam pelo nmero 1, seguindo de forma crescente at 65.536.O ponto de encontro (interseo) entre uma linha e uma coluna forma a chamada clula (a caixa). A interseo entre a coluna C e a Linha 4 forma a clula C4, como mostra o exemplo abaixo :

Uma clula pode conter nmeros, texto ou frmulas. A possibilidade de usar frmulas o que diferencia um programa de planilha de uma calculadora. Quando colocamos uma frmula em uma clula, dizemos que o contedo dessa clula deve ser calculado em funo dos valores contidos em outras clulas.Quando abrimos o Excel, j aparece um desenho bsico de planilha na tela. Precisamos, ento, organizar as informaes em linhas e colunas e determinar uma regio para cada tipo de informao. No layout, apenas definimos onde cada informao ser colocada, mas ainda no a digitamos.Exemplo:

Intervalo de ClulasQuando se trabalha com uma planilha, muitas vezes depara-se com a necessidade de tratar um trecho ou uma determinada regio de maneira diferente do restante da planilha. Um intervalo de clulas uma regio da planilha selecionada a fim de permitir que se trabalhe, edite, formate e modifique mais de uma clula ao mesmo tempo. O intervalo de clulas reconhecido como o conjunto de clulas que fica entre a clula do canto superior esquerdo e a do canto inferior direito. Observe a figura:

Formas do Ponteiro do MouseQuando o ponteiro do mouse movimentado ao longo da janela do Excel 2000, este se transforma a fim de indicar o que acontecer se for dado um clique com o mouse naquela rea da janela. Enquanto o ponteiro do mouse estiver sobre a planilha na janela do documento, ele ser apresentado como um sinal de mais (+). Dentro da barra de frmulas, o ponteiro do mouse ter a forma de uma viga ( I ), criada para posicionar um ponto de insero com preciso entre dois caracteres. Dentro da barra de ferramentas e da barra de menu, a forma do ponteiro um seta. A tabela a seguir ilustra os perfis do ponteiro que, muito provavelmente, sero encontrados.

PerfilPosioSobre as clulas da planilhaDentro da barra de frmula e dentro da caixa de texto na extremidade esquerda da barra de ferramentasSobre a barra de ttulos, botes na barra de ferramentas, barra de menu e barras de rolagem, do lado esquerdo da barra de frmulas e sobre as bordas das clulas da planilhaNo limite de um cabealho de coluna ou de linha (para redimensionamento)Sobre a ala de preenchimento no canto inferior direito da clula ativaFormatao da PlanilhaA barra de formatao concentra os principais elementos de formatao. Seus botes e caixas de seleo dividem-se em grupos lgicos.Alterao de Fontes e Alinhamento de Texto

Altera a fonte do TextoAltera o tamanho do TextoAltera a cor de fundo da clulaDiminuir o nmero

Formato Moedade casa decimais

Altera a cor do Texto

Formato PercentualAumentar o nmero

de casa decimais

Separador de Milhares

Envolvendo Clulas com uma MolduraO EXCEL permite envolver uma ou mais clulas selecionadas com 11 tipos diferentes de bordas ou molduras. A aplicao da moldura ou borda bastante simples e consiste em selecionar as clulas e pressionar o boto Tipos de moldura para aplicar a moldura previamente selecionada ou a seta ao seu lado para escolher um dos tipos disponveis.

A guia Alinhamento, possui vrias opes de alinhamento nos sentidos horizontal e vertical.

ParteTodoDiferena

Percentual

201004

504007

FrmulasUma Frmula uma seqncia de valores, operadores, referncias a clulas e funes pr-definidas. Esta seqncia est contida numa clula e produz um valor.Exemplo

=B3 + C3EXERCCIOSelecionar o intervalo de C3 at D7 e formatar como moeda Salvar com o nome Exerccio 01EXERCCIO

Selecionar o intervalo de C3 at D7 e formatar com estilo de moeda Salvar com o nome Exerccio 02

Sinais OperacionaisSinal de + = Adio Sinal de = Subtrao Sinal de ^ = Potenciao

Sinal de * = Multiplicao Sinal de / = DivisoSinal de % = PorcentagemCopiando FrmulasCalcular o primeiro item e depois copiar a frmula para os demais. Para isto, posicione o cursor no canto inferior direito (ala de preenchimento) da clula a ser copiada, neste caso a clula F2, que o cursor ficar como o formato apresentado na figura acima, e arrast-la para baixo at a ltima clula da nota final do ltimo aluno.

DICA : Em vez de arrastar a ala de preenchimento, d clique duplo, o Excel ir preencher as demais linhas automaticamente.Intervalo de ClulasAo definir um intervalo de clulas, importante verificar a diferena de usar ; e : B2:E4 Indica que esta buscando valores (selecionando

valores) no intervalo de B2 at E4 . Inclusive B2 e E4.Observe o exemplo ao ladoB2;E4Indica que esta buscando os valores de B2 e E4

.No incluindo os valores entre B2 e E4Observe o exemplo ao ladoReferncias Fixas e RelativasComo padro, o EXCEL copia as frmulas ajustando-as relativamente sua posio de origem. Prem, em muitos casos, necessrio realizar a cpia de frmulas que faam referncias a clulas especficas e que no podem ter suas referncias ajustadas pois causaro erros nas frmulas.Essa situao relativamente comum e exige do usurio um certo planejamento das frmulas durante sua criao, visto que a cpia das clulas sempre ser feita da mesma forma. O Excel permite "travar" as referncias a uma determinada clula de forma que, mesmo sendo copiada para outras localidades, a frmula sempre far meno clula original. Em situaes como essa, onde uma clula deve ter sua referncia fixada dentro de uma frmula, o usurio deve acrescentar um smbolo especial a ela para avisar o EXCEL de que aquela clula no deve ter sua referncia alterada.FIXANDO REFERNCIAS EM FRMULASO endereo de uma clula uma referncia sua localizao dentro da planilha. Como padro, as referncias a clulas que so criadas com o formato letra+nmero so consideradas referncias relativas.Alm de uma referncia do tipo relativa, que permite a sua alterao durante a cpia, o Excel permite criar referncias absolutas (fixas) ou mistas. Uma referncia absoluta no muda nem a linha nem a coluna da clula especificada. Uma referncia mista pode fixar apenas a linha ou coluna da clula permitindo o seu ajuste parcial.Para fixar uma linha ou coluna de uma referncia de clula, deve ser especificado o smbolo cifro imediatamente antes da letra, se o usurio desejar fixar a referncia coluna, ou antes do nmero, se quiser fixar a linha. Veja os exemplos possveis de combinaes.$D$4 Fixa a linha e a coluna$D4 Fixa apenas a coluna, permitindo a variao da linha D$4 Fixa apenas a linha, permitindo a variao da coluna D4 No fixa linha nem colunaExemplo

FunesSo comandos mais compactos e rpidos para se executar frmulas. Com elas possvel fazer operaes complexas com uma nica frmula. As funes so agrupadas em categorias, para ficar mais fcil a sua localizao. As funes tambm facilitam o trabalho com planilhas especializadas.Um engenheiro pode utilizar funes matemticas para calcular a resistncia de um material. Um contador usar funes financeiras para elaborar o balano de uma empresa.Algumas categorias de funes Funes financeiras: calcula juros, rendimento de aplicaes, depreciao de ativos etc. Funes matemticas e trigonomtricas: calcula raiz quadrada, fatorial, seno, tangente etc. Funes estatsticas: calcula a mdia de valores, valores mximos e mnimos de uma lista, desvio padro, distribuies etc. Funes lgicas: compara clulas e apresentar valores que no podem ser calculados com frmulas tradicionais. Funes MatemticasFuno SomaEsta uma funo matemtica que soma todos os nmeros em um intervalo de clulas. Exemplo:

1. Selecione as clulas C3 at C8

2. Clique no cone AutoSoma . Assim aparecer a soma dos valores na clula C9

Exerccio

1. Apresentar a SOMA dos valores na clula C7

Clicar na clula C7 e clicar no cone AutoSoma Selecionar o intervalo com os valores que devem ser somados (selecionar de B3 at D5) e ENTER 2. Observe a sintaxe da funo na clula C7 : = SOMA(B3:D5)

ExerccioObserve que:

- Os dados das colunas B, C, D e E estocentralizados. - Os dados esto na fonte Comics Sans MS. - As clulas B2 e C2 esto com a cor de preenchimento verde claro.- As clulas B7, C10, D9 e E8 esto com a cor de preenchimento amarelo claro.Apresentar a SOMA de cada coluna Clicar na clula B7 e clicar no cone AutoSoma . Verificar se a soma apresentada o intervalo de B4 a B6 Clicar na clula C10 e clicar no cone AutoSoma . Verificar se a soma do intervalo de C4 at C9 Clicar na clula D9 e clicar no cone AutoSoma . Verificar se a soma do intervalo de D4 at . Clicar na clula E8 clicar no cone AutoSoma . Verificar se a soma apresentada do intervalo de E4 at E7Apresentar na clula C2 a soma TOTAL de todas as somatrias parciais. Selecionar a clula C2 e clicar no cone da Com auxilio da teclar CTRL, selecionar as clulas que devem ser somadas: B7, C10, D9 e E8 e ENTER Funo Mult Esta uma funo matemtica que multiplica todos os nmeros dados como argumentos. Exemplo: Funo Raiz Esta uma funo matemtica que retorna a raiz quadrada de um nmero.Funo Int Esta uma funo matemtica que arredonda um nmero. Exemplo: Funo Abs Esta uma funo matemtica que retorna o valor absoluto de um nmero. Exemplo: Funes de Data e HoraExcel possui funes que permite trabalhar clculo com datas. HOJE( ) : retorna a data atual do sistema.

AGORA ( ) : retorna a data e a hora do sistema.

DIA.DA.SEMANA (argumento 1) : retorna o dia que representa o dia da semana ( 1= domingo; 2= segunda... ) EXEMPLO:

Exerccio1.Criar a planilha abaixo e salvar o arquivo como Exerccio 04 dentro da pasta CURSO.

= HOJE ( )= C7 * E7=F14= SOMA ( F7 : F13 )

=C17 / C15

2.Resultado final

Altura da linha de 20Altura da linha de 30Exerccio1.Criar a planilha abaixo e salvar o arquivo como Exerccio 05 dentro da pasta CURSO.

= $C$4 * C72.Resultado Final

Exerccio3.Criar a planilha abaixo e salvar o arquivo como Exerccio 06 dentro da pasta CURSO.

= HOJE ( )= D9 * F9= $C$6 * E13= SOMA ( G9 : G19 )4.Resultado final

Funes de TextoFuno ConcatenarEsta funo permite agrupar caracteres de um texto, ou seja, agrupa vrios itens de texto num nico item de texto. O operador "&" pode ser usado no lugar de CONCATENAR.Parmetros solicitados: So os textos que deseja unir

-Funo Num.CaractEsta funo retorna o nmero de caracteres de uma cadeia de textoParmetros solicitados: solicitado apenas o texto

Funo Esquerda e DireitaA funo ESQUERDA retorna os caracteres mais esquerda de um valor de texto.A funo DIREITA retorna os caracteres mais direita de um valor de texto.Parmetros solicitados:Funo DIREITA

Selecionar o Texto

Definir o nmero de caracteres que deseja extrair.

Funes LgicasFuno SeEsta funo verifica se uma funo foi satisfeita e retorna um valor se for VERDADEIRO e retorna um outro valor se for FALSO.Parmetros solicitados: Teste Lgico: um valor ou expresso que ser avaliada como VERDADEIRO ou FALSO

Valor se verdadeiro: o valor retornado caso o TESTE LGICO for VERDADEIRO

Valor se falso: o valor retornado caso o TESTE LGICO for FALSO

ExemploClassificar a altura como ALTA para altura maior que 1,70 e BAIXA para altura menor ou igual a 1,70.

OU

Exerccio 1TOTAL = soma dos alunos de jan + fev + mar + maiAVALIAO = quantidade BAIXA para TOTAL menor ou igual a 50 = quantidade BOA para TOTAL maior que 50

Reviso Funo Lgica

GRFICOSExpressar nmeros em forma de grficos uma das caractersticas mais atraentes das planilhas eletrnicas. Em muitos casos, um grfico pode sintetizar todo um conceito ou dar uma idia precisa e instantnea sobre um determinado assunto que possivelmente exigiria a leitura atenta de muitas linhas e colunas de nmeros da planilha.O EXCEL possui uma grande variedade de tipos de grficos que podem representar os dados por meio de dezenas de formatos em duas e trs dimenses.

O EXCEL possibilita editar o Grfico, atravs de caixas de dilogo. Dessa forma, basta dar um clique no boto da direita do mouse para abrir um menu e escolher a opo que deve ser modificada. Veja o exemplo a seguir:

ImpressoPara obter uma impresso perfeita da planilha necessrio algumas definies. Primeiramente necessrio definir a rea de impresso.Para isto basta selecionar rea da planilha que deseja imprimir e atravs do Menu Na verso 2003

Menu Arquivo rea de Impresso Definir rea de Impresso. interessante que antes de imprimir verifique a Configurao da pgina.

Nesta caixa de Dilogo possvel configurar Tamanho da pgina

Tamanho das Margens,

Na verso 2003Esta configurao feita atravs do Menu Arquivo Configurao de Pgina

Exerccio de RevisoReviso 01 Renomear a planilha como DESPESAS

Preencher todos os TOTAIS utilizando uma frmula % em Relao a Despesa Total para calcular o percentual do total de cada despesa no quadrimestre em relao ao total GERAL. Formatar esta coluna para apresentar formato de porcentual com duas casas decimais. Na clula H2 digitar uma funo para colocar uma data e hora sendo sempre alterado pelo computador. Exemplo : funo AGORA() Criar grficos dentro da MESMA planilha para demonstrar os gastos

Reviso 02Renomear a planilha para PEDIDO e criar a estrutura abaixo:

= D7 * E7= F7 + F7 * $C$4Reviso 03Renomear a planilha para Veculos e criar a estrutura abaixo:Obs: apresentar os valores em moeda.

Altura da linha de 30= D4 * 10%= D4 E4Total do preo a vistaMdia do preo a vistaSalvar o Arquivo como Reviso 01 Usar formatao Condicional de Barras na Coluna Entrada e na Coluna Restante Reviso 04Renomear a planilha para Tabela de Preos criar a estrutura abaixo:

1- Quando o mdulo fizer parte de um curso identificar o curso, sempre que o mdulo estiver codificado iniciar a designao pelo cdigo

M.PF.14.01