Apostila Excel 2007 Senai

Embed Size (px)

Citation preview

  • 8/2/2019 Apostila Excel 2007 Senai

    1/53

    Excel 2007

    SERVIO NACIONAL DE APRENDIZAGEM INDUSTRIAL SENAI DR/MG

    ________________________________Pgina 1 de 53Excel 2007

    Excel 2007

  • 8/2/2019 Apostila Excel 2007 Senai

    2/53

    Excel 2007

    Presidente da FIEMGOlavo Machado Junior

    Gestor do SENAI

    Petrnio Machado Zica

    Diretor Regional do SENAI eSuperintendente de Conhecimento e TecnologiaLucio Jos de Figueiredo Sampaio

    Gerente de Educao e TecnologiaEdmar Fernando de Alcntara

    Elaborao / AnoAnderson Dias de Oliveira / 2010

    Unidade OperacionalCentro de Formao Profissional Orlando Chiarini

    ________________________________Pgina 2 de 53Excel 2007

  • 8/2/2019 Apostila Excel 2007 Senai

    3/53

    Excel 2007

    APRESENTAO

    Muda a forma de trabalhar, agir, sentir, pensar na chamada sociedade doconhecimento.Peter Drucker

    O ingresso na sociedade da informao exige mudanas profundas em todos osperfis profissionais, especialmente naqueles diretamente envolvidos na produo,coleta, disseminao e uso da informao.

    O SENAI, maior rede privada de educao profissional do pas, sabe disso, e,

    consciente do seu papel formativo, educa o trabalhador sob a gide do conceito dacompetncia: formar o profissional com responsabilidade no processoprodutivo, com iniciativa na resoluo de problemas, com conhecimentostcnicos aprofundados, flexibilidade e criatividade, empreendedorismo econscincia da necessidade de educao continuada.

    Vivemos numa sociedade da informao. O conhecimento, na sua rea tecnolgica,amplia-se e se multiplica a cada dia. Uma constante atualizao se faz necessria.Para o SENAI, cuidar do seu acervo bibliogrfico, da sua infovia, da conexo desuas escolas rede mundial de informaes internet - to importante quantozelar pela produo de material didtico.

    Isto porque, nos embates dirios, instrutores e alunos, nas diversas oficinas elaboratrios do SENAI, fazem com que as informaes, contidas nos materiaisdidticos, tomem sentido e se concretizem em mltiplos conhecimentos.

    O SENAI deseja, por meio dos diversos materiais didticos, aguar a suacuriosidade, responder s suas demandas de informaes e construir links entre osdiversos conhecimentos, to importantes para sua formao continuada !

    Gerncia de Educao e Tecnologia

    ________________________________Pgina 3 de 53Excel 2007

  • 8/2/2019 Apostila Excel 2007 Senai

    4/53

    Excel 2007

    SUMRIO

    1 FORMATAO E

    FUNES...........................................................................................

    05

    1.1 Formatao e guias............................................................................ 051.2 Uso da funo soma, multiplicao, diviso, subtrao e

    potenciao......................................................................................... 051.3 Copiar frmulas absolutas e relativas ................................................ 051.4 Funo SE (Frmulas condicionais)................................................... 06

    2 BANCO DE DADOS E TRATAMENTO DE DADOS...........................

    2.1 Banco de dados .................................................................................2.2 Vnculos com planilhas externas e arquivos.......................................

    2.3 PROCV

    2.4 PROCV/CONCATENAR

    2.5 Formatao condicional

    2.6 Nome em clulas

    2.7 SOMASE

    2.8 Comentrios em clulas

    3 FILTROS E TABELA DINMICA3.1 Uso do comando FILTRAR3.2 Funo SUBTOTAL no modo filtrar

    3.3 Classificao do banco de dados3.4 Subtotais

    3.5 Tabela Dinmica

    3.6 Formulrio

    3.7 Validao

    4 MACROS, CAIXAS E BOTES

    4.1 Macros

    4.2 Filtro Avanado

    ________________________________Pgina 4 de 53Excel 2007

  • 8/2/2019 Apostila Excel 2007 Senai

    5/53

    Excel 2007

    4.3 Caixa drop-down

    4.4 Botes de seleo

    5 FUNES AVANADAS

    5.1 Funes financeiras

    5.2 Funes estatsticas

    5.3 Funes diversas

    5.4 Grficos

    5.5 Vnculos com outros aplicativos

    5.6 Botes em macros

    ________________________________Pgina 5 de 53Excel 2007

  • 8/2/2019 Apostila Excel 2007 Senai

    6/53

    Excel 2007

    Antes de um piloto de avio fazer seu primeiro vo, ele precisa primeiramenteconhecer muito bem os controles e botes que o avio possui no seu painel, eledeve saber, por exemplo, qual boto ou controle ele usa para baixar o trem depouso. Usar o Excel, ou qualquer outro software, no muito diferente, para quevoc possa obter sucesso na sua utilizao voc precisa conhecer bem suasferramentas, atalhos, menus e recursos. Os prximos captulos iro lhe oferecer

    justamente isto, um conhecimento detalhado de cada Guia para que voc possadecolar com o Excel 2007 e ter um vo de sucesso. Porm, antes disso,estudaremos uma breve explicao sobre o porqu das Guias.

    Sem dvida, o maior diferencial que o Excel 2007 possui em relao as suasverses anteriores a substituio das Barras de Menus e Ferramentas pelas Guiasque foram organizadas em: Incio, Inserir, Layout da Pgina, Frmulas, Dados,Reviso, Exibio e Desenvolvedor. Comenta-se que a razo desta substituio foi ofato de que muitas ferramentas, opes e atalhos presentes nas verses maisantigas do Excel eram totalmente desconhecidos por seus usurios. Isso aconteciaporque, nessas verses, a Barra de Ferramentas era padronizada de acordo com afreqncia de uso destes recursos e o espao disponvel para visualizao, deixandoassim, vrias ferramentas, opes e atalhos ocultos aos usurios das verses 97,2000, XP e 2003 do Excel. Entretanto, com a utilizao de Guias tanto a visualizaoquanto a utilizao dos recursos acima citados tornaram-se extremamente maisfceis e prticos para os usurios do Excel 2007.

    ________________________________Pgina 6 de 53Excel 2007

  • 8/2/2019 Apostila Excel 2007 Senai

    7/53

    Excel 2007

    1.1 GUIA INCIO1.1 GUIA INCIO

    ________________________________Pgina 7 de 53Excel 2007

  • 8/2/2019 Apostila Excel 2007 Senai

    8/53

    Excel 2007

    GUIA INSERIRGUIA INSERIR

    ________________________________Pgina 8 de 53Excel 2007

  • 8/2/2019 Apostila Excel 2007 Senai

    9/53

    Excel 2007

    GUIA LAYOUT DA PGINAGUIA LAYOUT DA PGINA

    ________________________________Pgina 9 de 53Excel 2007

  • 8/2/2019 Apostila Excel 2007 Senai

    10/53

    Excel 2007

    GUIA FRMULASGUIA FRMULAS

    ________________________________Pgina 10 de 53Excel 2007

  • 8/2/2019 Apostila Excel 2007 Senai

    11/53

    Excel 2007

    GUIA DADOS

    ________________________________Pgina 11 de 53Excel 2007

  • 8/2/2019 Apostila Excel 2007 Senai

    12/53

    Excel 2007

    GUIA REVISO

    ________________________________Pgina 12 de 53Excel 2007

  • 8/2/2019 Apostila Excel 2007 Senai

    13/53

    Excel 2007

    GUIA EXIBIO

    ________________________________Pgina 13 de 53Excel 2007

  • 8/2/2019 Apostila Excel 2007 Senai

    14/53

    Excel 2007

    FORMATAES OPERACIONAIS

    Barra de status como calculadoraImagine que voc precise fazer vrios tipos de clculos como: mdia, mximo,

    mnimo e soma. Ao invs de criar vrias frmulas para obter estes clculos voc

    pode, simplesmente, usar a barra de status como uma calculadora. Para isso,voc s precisar dar alguns cliques para configur-la.

    1. Clique com o boto direito do mouse sobre a Barra de Status.

    2. Selecione todas as opes de clculos desejadas.

    3. Agora, toda vez que voc selecionar um intervalo de dados no Excel 2007, aBarra de Status efetuar diversos clculos automaticamente, simples e semfrmulas, funcionando como uma calculadora.

    Barra de Ferramentas de Acesso Rpido do Excel 2007

    1. Clique no boto com o smbolo do Office .

    2. Depois clique em Opes do Excel.

    3. Clique em Personalizar.

    ________________________________Pgina 14 de 53Excel 2007

  • 8/2/2019 Apostila Excel 2007 Senai

    15/53

    Excel 2007

    4. Clique na opo que deseja acionar rapidamente e depois no boto Adicionar

    5. O Atalho escolhido foi adicionado a sua Barra de Ferramentas de Acesso Rpidodo Excel 2007. s clicar nele para acion-lo.

    Formatao condicional

    1. Selecione as clulas nas quais voc deseja aplicar aformatao condicional.

    2. Clique em Incio e depois em Formatao Condicional

    Neste item podemos obter uma noo visual direta e instantnea doselementos dispostos na tabela. Este recurso funciona somente com nmeros.

    Inserindo dados de forma mais rpida em um formulrio

    1- Inicialmente insira na barra de acesso rpido o item FORMULRIO;

    2- O item formulrio dever aparecer na barra de acesso rpido, ento ao clicarem qualquer clula portadora de informao e clicar no cone formulrio,surgir uma janela como ilustra a figura abaixo:

    ________________________________Pgina 15 de 53Excel 2007

  • 8/2/2019 Apostila Excel 2007 Senai

    16/53

    Excel 2007

    3 Para inserir novos dados deve-se clicar em novo, e escrever o que se deseja,para mudar de campo necessrio a utilizao da tecla TAB, para efetivar ainsero basta apertar a tecla enter.

    Plano de fundo

    Para fins de personalizao da planilha podemos alterar seu plano de fundo, epara isso temos que clicar na guia Layout da Pgina, Plano de fundo, entodeve-se selecionar a figura desejada e clicar em OK.Ainda nesta guia temos os itens Linhas de Grade e Ttulos, sendo que sedesabilitados retiram as linhas de grade do Excel e as letras e nmerosindicadores do endereo da clula.

    Para uma formatao mais rpida, podemos selecionar a rea a ser modificada eclicar na mesma com o boto direito, uma barra de menus surgir, ento deve-seselecionar Formatar clulas..., uma janela, como se segue abaixo surgir:

    Nesta janela podemos configurar nossa planilha para trabalhar de forma afacilitar nossos clculos e frmulas, podemos alterar as grades, fontes, tamanho, corda letra, cor da clula, etc.Esta ser muito utilizada durante toda a apostila e no cotidiano do trabalho.

    ________________________________Pgina 16 de 53Excel 2007

  • 8/2/2019 Apostila Excel 2007 Senai

    17/53

    Excel 2007

    1.2 Uso da funo soma, multiplicao, diviso, subtrao e potenciao

    1.3 Copiar frmulas absolutas e relativas

    ________________________________Pgina 17 de 53Excel 2007

  • 8/2/2019 Apostila Excel 2007 Senai

    18/53

    Excel 2007

    Para fixar a coluna e a linha usa-se =$A$1*A2, pois assim quando copiamos estaclula para baixo ou para o lado no Excel, A1 permanecer fixo.

    Quando precisamos de copiar somente os valores, e o fazemos selecionandoos dados, clicando em copiar na guia acionada com o boto direito e depoisclicamos em colar, os itens se desconfiguram por completo, desde que sejam

    oriundos de frmulas, isto porque o Excel copia somente as frmulas e no osdados, assim o mesmo perde a referncia. Para sanar este problema podemoscolocar $ em todas as variveis, ou copiar normalmente atravs do menu acionadocom o boto direito, e quando a colar devemos escolher o item Colar especial, edepois Valores finalizando com OK.

    ________________________________Pgina 18 de 53Excel 2007

  • 8/2/2019 Apostila Excel 2007 Senai

    19/53

    Excel 2007

    1.4 Funo SE (Frmulas condicionais)

    ________________________________Pgina 19 de 53Excel 2007

  • 8/2/2019 Apostila Excel 2007 Senai

    20/53

    Excel 2007

    ________________________________Pgina 20 de 53Excel 2007

  • 8/2/2019 Apostila Excel 2007 Senai

    21/53

    Excel 2007

    Todas as demais frmulas condicionais fazem-se da mesma forma, abaixo seguemmais alguns exemplos:

    SOMASE = Soma as clulas determinadas por um critrio ou condio.A frmula =SOMASE(H2:H12;">10"), adiciona os valores contidos no intervalo deH2 a H12, porm somente os valores maiores que 10.

    CONT.SE = Conta o nmero de clulas no vazias que atendem a umadeterminada condio.

    A frmula =CONT.SE(G2:G12;"=v"), conta todas as clulas que contm o valor v nointervalo de G2 a G12. O valor pode ser substitudo por palavras ou nmeros.

    CONTAR.VAZIO = Conta um nmero de clulas vazias em determinadointervalo.

    A frmula =contar.vazio(A2:A20), conta as clulas vazias existentes no intervalo deA2 a A20. Devemos tomar certo cuidado para no selecionar clulas indesejadas,

    pois estas podem atrelar um resultado errneo a tabela.Esta funo bastante utilizada com as funes, concatenar, procv e proch, as quaisveremos mais adiante.

    MDIA = Calcula a mdia de determinado intervalo.Uma clula que apresente a frmula =MDIA(H2:H12), soma os valores do intervaloH2 a H12 e divide o resultado por 11, obtendo assim a mdia simples.Comumente utilizamos esta funo em planilhas que determinam desvio padro,varincia, etc.

    MXIMO = Retorna o valor mximo de um conjunto de argumentos.

    Se tenho em uma clula a frmula =mximo(A1;A2;A3;A4;A5), esta nos dar o valornumrico mais alto que encontrar no intervalo.

    MNIMO = Retorna o valor mnimo de um conjunto de argumentos.Assim como o MXIMO, esta funo tambm nos mostra um valor numrico, massendo este o menor encontrado no intervalo.

    EXERCCIOS

    Confeccione a tabela abaixo utilizando os recursos vistos at o momento,sendo que quaisquer valores alterados a planilha se atualiza.

    ________________________________Pgina 21 de 53Excel 2007

  • 8/2/2019 Apostila Excel 2007 Senai

    22/53

    Excel 2007

    Confeccione a tabela abaixo utilizando os recursos disponveis, sabendoque:1 Total = Soma das Notas2 Mdia = Mdia das notas3 Resultado = Se o aluno tm mdia acima de 6, deve-se exibir APR, se possuimdia menor que 40, deve-se exibir REP em cor vermelha, e se a mdia da notaestiver entre 40 e 60, deve-se escrever REC4 Prmio O programa deve escrever timo para o aluno que obteve mdia entre8,5 e 9,99999.5 Em Alunos aprovados, Alunos de recuperao e Alunos reprovados, deve-se

    exibir o nmero de alunos em cada situao.6 Formate a planilha de forma a parecer mais organizada.

    ________________________________Pgina 22 de 53Excel 2007

  • 8/2/2019 Apostila Excel 2007 Senai

    23/53

    Excel 2007

    2 BANCO DE DADOS E TRATAMENTO DE DADOS

    2.1 Banco de dados

    2.2 Vnculos com planilhas externas e arquivos

    ________________________________Pgina 23 de 53Excel 2007

  • 8/2/2019 Apostila Excel 2007 Senai

    24/53

    Excel 2007

    2.3 Funo PROCVA funo PROCV utilizada para buscar informaes automaticamente em

    um banco de dados tomando uma referncia primria.Para facilitar nossa compreenso, abra um documento novo do Microsoft Excel2007, e crie, em Plan2, o banco de dados abaixo.

    Vamos ento confeccionar agora em Plan1 um cadastro de pedidos onde aodigitarmos o Modelo, a planilha automaticamente buscar os dados de Descrio,Cor e Valor, ficando apenas o item QTD (quantidade) para ser preenchido, j quepodemos tambm automatizar o item Valor Total. Segue exemplo:

    ________________________________Pgina 24 de 53Excel 2007

  • 8/2/2019 Apostila Excel 2007 Senai

    25/53

    Excel 2007

    Buscamos ento as informaes na primeira planilha e completamos asegunda automaticamente, ou seja, toda vez que digitarmos um valor na guiamodelo do cadastro de pedidos, os valores sero buscados no banco de dados, paratanto, utilizamos a funo PROCV. PROC significa procurar, e o V significa vertical,ou melhor, uma funo que procura valores em colunas, podemos tambm ter o

    PROCH, que procura valores nas linhas, (PROC = procura, H = horizontal).O desenvolvimento da funo d-se da seguinte maneira:

    =PROCV(Valor procurado;Tabela Matriz;Nmero ndice de coluna;Critrio)

    Sendo: Valor procurado = o valor que queremos comparar, no nosso caso os dados

    contidos em Modelo. Tabela matriz = o banco de dados de onde retiraremos as informaes, que

    no exemplo em Plan2!A2:D10

    Nmero ndice de coluna = um nmero que indica qual informao buscarno banco de dados, no nosso estudo, por exemplo, temos uma rea de busca A2:D10, a coluna A corresponde ao nmero ndice 1, a coluna Bcorresponde ao nmero ndice 2, e assim sucessivamente.

    Critrio = 0 (exato) ou 1 (parecido) serve para que o Excel busqueinformaes exatas ou parecidas.

    Ento nossa frmula no cadastro de pedidos na clula B2 ficou da seguinte forma:

    =PROCV(A2;Plan2!A2:D10;2;0)

    Ao arrastarmos esta frmula para baixo e para o lado, devemos tomar cuidado com

    as adies de endereos indevidas, ou seja, se levarmos esta frmula at B3, amesma ficar assim:

    =PROCV(A3;Plan2!A3:D11;2;0)

    Note que o banco de dados mudou tambm seu valor, e isto pode gerar erros. Entofaz-se necessrio travar o endereo, ficando conforme exemplo abaixo:

    =PROCV(A3;Plan2!$A$2:$D$11;2;0)Tendo:

    ________________________________Pgina 25 de 53Excel 2007

  • 8/2/2019 Apostila Excel 2007 Senai

    26/53

    Excel 2007

    Exemplo das frmulas:

    Note ainda que um erro ocorreu, pois, se no lanarmos informao nenhuma emmodelo aparece #N/D na clula.

    EXERCCIO Desenvolva uma maneira de sanar o erro citado acima e demais, como que j aprendemos, pode-se usar frmulas para que o cadastro de pedidosaparente o exemplo abaixo:________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________________

    CONCATENAR

    A funo concatenar utilizada para ajuntar valores, para formar um terceiro valor,como segue o exemplo.

    Imaginemos que em uma fbrica de sapatos existam 4 tipos de sapatos diferentes,A, B, C e D, cada tipo em 2 tamanhos 38 e 41. Foi necessrio a criao de umcdigo para nomear estes sapatos, ento decidiu-se que o cdigo seria a juno domodelo e o tamanho.Utilizaremos comando concatenar para solucionar este problema, assim comomostra a figura abaixo:

    ________________________________Pgina 26 de 53Excel 2007

  • 8/2/2019 Apostila Excel 2007 Senai

    27/53

    Excel 2007

    A funo concatenar tambm pode serrepresentada pelo smbolo &, ento, como ilustroo exemplo, nas clulas da coluna C, ao invs deutilizar a frmula =concatenar(A2;B2), poderamossimplesmente escrever =A2&B2.

    PROCV E CONCATENAR

    Imaginemos que na fabrica de sapatos do exemplo anterior o banco de dados fosseo seguinte.

    E foi criado uma pequena planilha de consulta, da seguinte forma O vendedorescreve o modelo e o tamanho do sapato desejado e o programa busca porinformaes no banco de dados e retorna a cor e o preo do item em pesquisa.Note que neste caso fica mais fcil buscar a informao desejada utilizando ocomando concatenar adicionado do procv, da maneira descrita logo abaixo:

    Na clula C2 =PROCV(A2&B2;Plan4!A1:E9;2;0)e na clula D2 temos =PROCV(A2&B2;Plan4!A1:E9;5;0)

    PROTEGENDO CLULAS

    1. Selecione a planilha que deseja proteger.

    2. Para desbloquear clulas ou intervalos que deseja liberar para outros

    usurios alterarem, faa o seguinte:

    1.Selecione cada clula ou intervalo que deseja desbloquear.

    2.Na guia Incio, no grupo Clulas, clique em Formatar e, em seguida,

    clique em Formatar Clulas.

    ________________________________Pgina 27 de 53Excel 2007

    Banco de

    dados criado

    em Plan4

  • 8/2/2019 Apostila Excel 2007 Senai

    28/53

    Excel 2007

    3. Na guia Proteo, limpe a caixa de seleo Bloqueada e clique em OK.

    4. Para ocultar frmulas que voc no deseja que fiquem visveis, faa o

    seguinte:

    5. Na planilha, selecione as clulas que contm as frmulas que deseja

    ocultar.

    6. Na guia Incio, no grupo Clulas, clique em Formatar e, em seguida, clique

    em Formatar Clulas.

    7. Na guia Proteo, marque a caixa de seleo Oculta e clique em OK.

    8. Na guia Reviso, no grupo Alteraes, clique em Proteger Planilha.

    9. Na lista Permitir a todos os usurios desta pasta de trabalho, selecione oselementos que voc deseja que os usurios possam alterar.

    Nos passos acima protegemos clulas para que usurios no possam fazer

    modificaes nas mesmas, como por exemplo em frmulas.

    de extrema importncia desbloquear as celular que os usurios podem modificar,

    e travar e at mesmo ocultar as frmulas.

    ________________________________Pgina 28 de 53Excel 2007

  • 8/2/2019 Apostila Excel 2007 Senai

    29/53

    Excel 2007

    EXERCCIO

    Proteja as clulas e frmulas na planilha Cadastro de pedidos feita nos exemplos

    anteriores, liberando somente as clulas de utilizao dos usurios.

    VALIDAO DE DADOS

    Na GUIA DADOS, entre em validao de dados, a seguinte janela dever aparecer.

    Neste item podemos definir

    alguns padres para as clulas,

    como por exemplo impedir que o

    usurio digite nmeros fracionrios,

    o nmero mnimo e o mximo deletras que se pode escrever,

    podemos permitir somente a

    insero de datas ou horas, e at

    definir um pequeno menu com as

    opes que existem.

    Imaginemos que na clula L7 queremos somente digitar um valor superior a

    100, ento devemos selecionar Personalizado e inserir a frmula abaixo no

    campo.

    Podemos ainda inserir uma lista para que o usurio possa selecionar

    somente itens pr-definidos, fazemos isto clicando na guia Lista, e inserindo os

    valores que desejamos que apaream no menu no campo denominado fonte.

    ________________________________Pgina 29 de 53Excel 2007

  • 8/2/2019 Apostila Excel 2007 Senai

    30/53

    Excel 2007

    Neste campo Fonte devemos simplesmente colocar as condiesseparadas por ponto e vrgula.

    Na guia Mensagem de entrada podemos definir uma frase que o usurio lerquando selecionar a clula.Na guia Alerta de erro podemos definir cones e frases aps uma tentativa de

    insero de dados ilegais.

    EXERCCIO

    1 - Ainda sobre a planilha Cadastro de pedidos faa com que seja impossvellanar um valor errado na coluna modelo.

    2 Siga as instrues abaixo: Crie o banco de dados abaixo em uma planilha em branco e salve no seu

    PEN-DRIVE com o nome de BD.

    Crie agora um outro documento do Excel chamado Pedido de compra, e osalve em seu PEN-DRIVE.

    o

    Faa o seguinte documento seguindo as especificaes abaixo. Em Pedido nmero de aparecer um cdigo que seja a juno dototal da compra com a data e hora atual, o formato da data ehora no nmero do pedido deve ser DDMMAAAAHHMM.

    Assim que inserirmos um cdigo de produto na coluna cdigo,este mesmo deve buscar os demais itens em BD, sendo que ousurio pode somente lanar dados em Cdigo e QTD,todos os outros campos devem ser travados e com frmulasocultas. O programa deve dar menus de escolha aos usurios.

    Se o total de venda for at R$500,00 o frete de 10% do total,se as vendas forem de R$500,00 at R$1000,00 o frete de 5%

    ________________________________Pgina 30 de 53Excel 2007

  • 8/2/2019 Apostila Excel 2007 Senai

    31/53

    Excel 2007

    do valor total e se a compra for acima de R$1000,00 o frete grtis.

    Em compras acima de R$1500,00 o desconto de 7,5%. A data da entrega deve ser dez dias aps a data do pedido.

    ________________________________Pgina 31 de 53Excel 2007

  • 8/2/2019 Apostila Excel 2007 Senai

    32/53

    Excel 2007

    FILTROSUtilizamos o filtro para listar dados de uma determinada famlia, facilitando acompreenso. Vejamos o exemplo abaixo:

    Imaginemos que precisamos mostrar na tela apenas as informaes do cliente BeiraRio. Faz-se ento necessrio selecionar a GUIA DADOS,e selecionar a ferramenta

    filtro:

    Surgiro ento nas clulas referentesaos ttulos algumas guias drop down, quequando acionadas nos mostram o contedodas clulas daquela coluna, ento podemosselecionar o item desejado e trabalhar com omesmo.

    Devemos notar que surge no botodrop down um smbolo de filtro, o queindica que aquele campo j foi filtrado.

    possvel ainda personalizar o filtro, ou melhor, utilizar o filtro avanado. Comeste recurso podemos, por exemplo, inserir um critrio para que o Excel nos retorneum valor. Para tanto necessrio fazer o seguinte:

    Imaginemos que no banco de dados acima desejamos filtrar somente osTotais que forem acima de 10000,00. Primeiro temos que criar em uma clulaqualquer em branco o (s) critrio (s) que desejamos, e logo aps, clicar emAvanado, que se localiza tambm na GUIA DADOS logo ao lado do filtro. Umapequena janela surgir conforme ilustra a figura abaixo:

    ________________________________Pgina 32 de 53Excel 2007

  • 8/2/2019 Apostila Excel 2007 Senai

    33/53

    Excel 2007

    Podemos nesta opo, obter um filtro local, mas isto, apagaria parcialmente obanco de dados atual, ento pode-se selecionar a guia Copiar para outro local, queo Excel cria o filtro em local pr-selecionado.

    No campo Intervalo da lista selecionamos toda a rea que desejamos que ofiltro faa a busca, que neste caso foi todo o banco de dados.

    No campo Intervalo de critrios selecionamos as clulas que criamos como

    critrio de busca, que no caso acima compreende a informao abaixo do banco dedados circundada pela elipse. Chamamos esta informao adicional de planilha deapoio, nela inserimos o ttulo da guia que queremos filtrar e abaixo deste ttulo ocritrio.No campo Copiar para selecionamos uma clula ou rea de destino para as

    informaes filtradas.Caso existam informaes em duplicatas podemos selecionar a guia Somente

    registros exclusivos, sendo que este nos dar somente dados no repetidos.Clicando finalmente em OK, obteremos os dados abaixo:

    CLASSIFICAO DO BANCO DE DADOS comum que um banco de dados seja organizado por algum parmetro,

    ou por algum campo especifico, como por exemplo por ordem crescente de cliente.Na GUIA DADOS encontramos a ferramenta classificar, que pode ser

    utilizada para ordenar os dados de vrias formas diferentes em um banco de dados,

    dependendo claro, dos critrios utilizados.Para tanto devemos selecionar os dados a serem classificados ou clicar em uma dasclulas do banco de dados, pois assim que clicarmos em classificar o Excelselecionar automaticamente o Banco de informaes, como segue o exemploabaixo:

    ________________________________Pgina 33 de 53Excel 2007

  • 8/2/2019 Apostila Excel 2007 Senai

    34/53

    Excel 2007

    A janela acima dever aparecer quando a ferramenta Classificar foracionada, nota-se que no campo Classificar por podemos selecionar qualquerttulo, classificar por valores, ou formatao e colocar em ordem crescente oudecrescente.

    Nas verses mais antigas do Excel podamos realizar uma classificaosomente at trs critrios, nesta verso podemos definir quantas classificaesforem necessrias, basta clicar no boto Adicionar nvel, se no quisermos maisdeterminado critrio podemos exclu-lo, selecionando-o e clicando em Excluirnvel .

    No boto Opes podemos escolher se queremos diferenciar letras

    maisculas de minsculas e se a classificao deve ser vertical ou horizontal.

    SUBTOTAISNa GUIA DADOS,e ferramenta Subtotal, possvel subtotalizar um banco dedados desde que este esteja classificado corretamente. Por exemplo, se vamossubtotalizar por cliente, o banco de dados deve primeiro estar classificado porcliente, caso contrrio no ir funcionar. Se fizer um subtotal por cliente, quando oExcel encontrar um cliente diferente, ele subtotaliza o cliente anterior, e assimsucessivamente. Vejamos o exemplo:

    ________________________________Pgina 34 de 53Excel 2007

  • 8/2/2019 Apostila Excel 2007 Senai

    35/53

    Excel 2007

    Podemos observar que a coluna Cliente est classificada, ento atrelamos osubtotal, informando ao Excel que a cada alterao de cliente, ele deve gerar umresultado parcial de soma dos valores. Podemos adicionar um subtotal a todos osttulos, conforme a necessidade.

    Ao clicar em OK, obtivemos atabela ao lado. Nela temos a cadacliente um resultado parcial daquelemesmo, e ao final o total geral.Repare ainda que no cantoesquerdo da tabela temos osnmeros 1, 2 e 3, que compreendem

    a forma de ver a tabela, ou seja, aoclicar no nmero 1 temossimplesmente os ttulos da planilha eo Total Geral, ao clicar no nmero2 encontraremos uma tabelareduzida com os totais de cadacliente e o total geral, e j seclicarmos no boto 3, teremos atabela com os resultados parciais,totais e total geral, conforme ilustra afigura a lado.

    Para que retornemos a planilha como eraanteriormente ao subtotal, podemos clicar novamente no cone da ferramenta citadae quando a janela aparecer novamente, clicamos em REMOVER TODOS, destaforma retiramos todos os subtotais e a planilha volta ao normal.

    TABELA DINMICA

    A tabela dinmica bastante til quando queremos analisar dados em uma estruturadiferente da que temos no banco de dados. Por exemplo, para uma planilha decontas a pagar como o exemplo abaixo, onde as informaes esto lanadas uma

    ________________________________Pgina 35 de 53Excel 2007

  • 8/2/2019 Apostila Excel 2007 Senai

    36/53

    Excel 2007

    abaixo da outra, e queremos visualiz-la por colunas, ento podemos utilizar a tabeladinmica, na GUIA INSERIR.

    Ao clicar no cone correspondente a janela acima dever surgir, e devemosselecionar a rea, ou intervalo das informaes que queremos visualizar, podemosainda obter uma fonte de dados externa, escolhendo uma conexo na rede. Pode-setambm nesta janela definir se a tabela dinmica ser criada em um novo arquivonovo do Excel ou na mesma pasta de trabalho.

    Uma lista semelhante a figura acima faz-se real quando clicamos em OK, na

    guia a direita podemos selecionar quais itens desejamos visualizar na tabeladinmica, sendo que os mesmos ficam dispostos nos quatro campos localizados nolado inferior direito da tela.

    ________________________________Pgina 36 de 53Excel 2007

  • 8/2/2019 Apostila Excel 2007 Senai

    37/53

    Excel 2007

    Podemos clicar sobre as guias drop down nos campos inferiores e formatar acomposio da tabela, ao clicar com o boto direito um menu surge, e o mesmo nosd opes para movimentar os dados. Na tabela dinmica, em rtulos de linha temosum filtro para uma eventual necessidade de filtrar a informao, e se quisermosformatar a mesma, e a lista de campos da tabela dinmica no estiver maispresente, podemos clicar em qualquer parte da planilha que esta re-aparecer.

    No exemplo acima modificou-se a tabela dinmica para que apresentasse aData Pgto como rtulo de coluna.

    ________________________________Pgina 37 de 53Excel 2007

  • 8/2/2019 Apostila Excel 2007 Senai

    38/53

    Excel 2007

    MACROS

    Imaginemos que em uma planilha seja necessrio executar uma ao vriasvezes, por exemplo copiar um dado, gerar um relatrio, etc. Podemos ento utilizara macro, que por sua vez grava todas as aes que fazemos no Excel emdeterminado perodo, sendo que podemos execut-la sempre que necessrio.

    Para tanto devemos clicar na GUIA EXIBIO, ao final da barra deferramentas encontraremos o cone Macros, se acionado o programa nos exibir alista de macros j existentes no computador, sendo que podemos editar, criar ouexcluir uma macro, porm para criar uma macro neste ambiente as ferramentasdisponveis so em Visual Basic, os quais no abordaremos aqui. Ento podemosclicar na caixa drop down abaixo do referido cone, onde inicialmente, teremos duasopes: Exibir macros e Gravar macros...

    Podemos ento selecionar Gravar Macro...Primeiramente definimos

    um nome para a macro.Podemos ainda definir umahotkey, ou tecla de atalho para amacro, que sempre acompanhada de Ctrl.

    Na guia Armazenar macroem: podemos definir aondequeremos guardar esta macro,que geralmente na mesmapasta de trabalho como mostra oexemplo ao lado.

    Em Descrio podemos

    descrever as aes da macro,para facilitar a identificao.

    Ao clicar em OK, a macro j comea a ser gravada, ento basta executarmosmanualmente as aes que queremos que o Excel execute sozinho, que o mesmocomea a armazenar todas estas informaes.

    Assim que finalizarmos as aes atribudas a macro, podemos clicar na caixadrop down abaixo do cone Macro e clicar em Parar Gravao.Para executar as macros podemos clicar no cone Macro, sendo que a seguinteexibir a seguinte janela:

    Basta selecionarmos a macro e clicar

    em Executar para realizarmos a tarefagravada a macro. No boto Depurarpodemos observar o cdigo fonte da macro,exibido em VB (Visual Basic), em Editarpodemos re-configurar a macro pormtambm em VB. Podemos ainda excluir umamacro, selecionando-a e clicando emExcluir. No boto Opes podemos atribuiruma tecla de atalho para a macro e inseriruma descrio para a mesma.

    ________________________________Pgina 38 de 53Excel 2007

  • 8/2/2019 Apostila Excel 2007 Senai

    39/53

    Excel 2007

    Caso queira que a Macro seja executada quando a planilha for aberta, deve-se atrelar o nome macro em auto_open.

    Existem alguns problemas que podem ocorrer quando tentamos salvar oarquivo, pois agora temos uma macro para executar e necessrio ento salvar oprograma selecionando um tipo de arquivo habilitado para macro, e podemosescolher esta opo na janela referente ao salvamento no campo tipo.

    Pode ainda ocorrer que o Excel ao reabrir o documento com macro e tentarexecutar a mesma no o faa, nos mostrando a seguinte mensagem:

    O que significa que para a segurana do arquivo o Excel desabilitou asmacros para que uma pessoa mal intencionada ou desinformada modifique ouexecute macros indevidas. Para permitir que as macros estejam sempre aptas ao

    funcionamento devemos clicar no cone do Office, depois em Opes do Excel,posteriormente na guia Central de confiabilidade e por fim no boto deConfiguraes da central de confiabilidade. Aps este processo devemosselecionar a sub-guia Opes de macros, conforme ilustra a figura abaixo,selecionando finalmente a habilitao de macros e clicando em OK.

    INSERINDO BOTES

    Se necessitamos de executar uma macro sempre, fica mais trabalhoso clicarno cone e realizar a tarefa. Podemos ento inserir botes no Excel e atrel-los a

    alguma ao especfica ou propriamente dito, uma macro.Portanto necessitamos de inserir a ferramenta de insero de botes em nossa barrade ferramentas. Esta ferramenta pode ser encontrada na GUIA DESENVOLVEDOR,mas existe uma maneira mais fcil de localizar esta ferramenta. Clique no cone doOffice, no canto esquerdo superior, depois clique em opes do Excel,posteriormente clicando em Personalizar. Feito isto, selecione a ferramenta Boto(controle de formulrio), adicione o mesmo na barra de acesso rpido, agora umcone da respectiva ferramenta deve aparecer na barro de acesso rpido.

    Para inserir um boto devemos clicar na ferramenta citada, clicar e arrastar narea que queremos inserir o mesmo, assim que soltarmos o boto do mouse umajanela aparecer, conforme ilustra a figura abaixo:

    ________________________________Pgina 39 de 53Excel 2007

  • 8/2/2019 Apostila Excel 2007 Senai

    40/53

    Excel 2007

    Esta a janela de atribuio de macro, nela nos adicionamos uma macropreviamente criada ao boto que estamos inserindo, como exemplo, temos a macrolimpa, para adicion-la ao boto 6 ao lado, devemos selecionar respectiva macroe clicar em OK, nesta janela podemos ainda criar novas macros, e definir osarquivos que esta macro estar inserida no campo Macros em:.

    Podemos tambm formatar o boto que acabamos de inserir, formatando seunome, clicamos com o boto direito do mouse sobre o boto, selecionamos seutexto, clicando com o direito novamente e selecionando em um pequeno menu quesurgir o item Formatar controle.

    GRAFICOSPodemos criar grficos para que as informaes fiquem mais fceis decompreender, cliquemos ento na GUIA INSERIR, nela notaremos que temos vriostipos de grficos, como colunas, linhas, pizza, barra, rea, disperso, etc.

    Para criar no Excel um grfico bsico que voc possa modificar e formatarposteriormente, comece inserindo os dados do grfico em uma planilha. Em seguida,basta selecionar esses dados e escolher o tipo de grfico que deseja utilizar naFaixa de Opes da barra Grficos.

    O interessante desta verso do Excel que aoinserirmos um grfico, ou este ser selecionado

    ________________________________Pgina 40 de 53Excel 2007

  • 8/2/2019 Apostila Excel 2007 Senai

    41/53

    Excel 2007

    surge uma barra de ferramentas bastante completa, conforme podemos ver ailustrao abaixo:

    Nestas guias de tratamentos de planilhas podemos modificar facilmente oLayout dos grficos, ou seja podemos inserir ttulos de grficos e eixos, legenda,rtulos de dados, cores, tipos de grficos, etc.

    Informao tambm interessante deste grfico que podemos inserir linhasde tendncia, no caso dos grficos de linha, e ainda os respectivos erros e desviopadro.

    Resumindo, na caixa de ferramentas de grfico podemos, atravs das trsabas (Design, Layout e formatar) modificar QUALQUER item do grfico.

    Os dois exemplos de grficos acima so extremamente simples, masutilizando estas trs uteis ferramentas j citadas podemos profissionalizar umaplanilha e principalmente grficos.

    ________________________________Pgina 41 de 53Excel 2007

  • 8/2/2019 Apostila Excel 2007 Senai

    42/53

    Excel 2007

    Para formatar determinada rea do grfico necessitamos selecionar esta rea nocampo rea do grfico na figura acima.

    Aps formatar o grfico podemos salvar a configurao que fizemos, isto ,para evitar de o tempo todo re-configurar grficos, salvamos o nosso modelopersonalizado e o utilizamos sempre que necessrio. Primeiramente, deve-seformatar o grfico de acordo com a utilizao, uma vez que o grfico estiver prontopodemos clicar na guia Design e em seguida clicar em Salvar como modelo,escolher um nome para este layout e clicar em OK.

    Desta forma quando fizermos um grfico e necessitarmos de umaconfigurao padro, podemos, ainda na aba Design clicar em Alterar tipo degrfico, selecionando o modelo salvo desejado.

    EXERCCIOS

    1 - Na planilha de controle semanal de massas, crie alguns botes que assim queacionados, gerem os respectivos grficos, cada um em uma plan diferente,referentes a produo semanal de massas de todas as linhas. Faa o mesmoprocesso para os resultados gerais da produo, mas este deve ser um grfico comindicadores de desvio padro. O modelo dos grficos podem ser escolhidos avontade, mas todos devem ter todos os ttulos.

    2 Na planilha pedido de compras ainda temos um problema, sempre que

    realizamos um pedido, tm-se que apagar todos os dados antigos manualmente, crieuma forma simples de apagar estes dados.

    3 Monte a planilha abaixo em um documento em branco.

    - Complete a coluna Total- Crie, se possvel em outra planilha um

    subtotal desta planilha, classificada porfornecedor.- Aplique um tipo de filtro que me mostre

    apenas as compras acima de $160,00.

    - Crie um grfico para que tenhamosfeedback visual dos nossos maioresfornecedores, crie um grfico tipo pizza3d.Seria possvel criar macros para realizarestas tarefas? Se sim, execute.

    ________________________________Pgina 42 de 53Excel 2007

  • 8/2/2019 Apostila Excel 2007 Senai

    43/53

    Excel 2007

    FUNES AVANADAS

    DATAS E HORAS

    =DIAS360(data inicial;data final)A funo DIAS360 retorna o nmero de dias entre duas datas com base em

    um ano de 360 dias (doze meses de 30 dias). Use essa funo para ajudar noclculo de pagamentos, se o seu sistema contbil estiver baseado em doze mesesde 30 dias.

    =DIATRABALHOTOTAL(data inicial;data final;[feriados])Retorna o nmero de dias teis inteiros entre data_inicial e data_final. Os dias

    teis excluem os fins de semana e quaisquer datas identificadas em feriados. UseDIATRABALHOTOTAL para calcular os benefcios aos empregados que recebemcom base no nmero de dias trabalhados durante um perodo especfico.

    =DIATRABALHO(data inicial;dias;feriados)Retorna um nmero que representa uma data que o nmero indicado de

    dias teis antes ou aps uma data (a data inicial). Os dias teis excluem fins desemana e quaisquer datas identificadas como feriados. Use DIATRABALHO paraexcluir os fins de semana ou feriados ao calcular as datas de vencimento de fatura,horas de entrega esperadas ou o nmero de dias de trabalho executado.

    =AGORA()Retorna a data e a hora atuais.

    =HOJE()

    Retorna somente a data atual.

    FUNES FINACEIRAS

    O Excel possui mais de 50 frmulas financeiras, veremos abaixo as frmulas maisutilizadas.

    =PGTO(taxa;nper;vp;vf;tipo)Retorna o valor a ser pago mensalmente considerando os critrios abaixo.

    Taxa a taxa de juros por perodo (mensal).

    Nper o nmero total de pagamentos, ou perodo. Vp o valor presente o valor total presente de uma srie de pagamentos

    futuros. Vf o valor futuro, ou o saldo, que voc deseja obter depois do ltimo

    pagamento. Se vf for omitido, ser considerado 0 (o valor futuro dedeterminado emprstimo, por exemplo, 0).

    Tipo o nmero 0 ou 1 e indica as datas de vencimento, se 0 ou noespecificado utiliza o final do perodo para calcular a quantia final, e se 1calcula os valores considerando o incio do perodo.

    ________________________________Pgina 43 de 53Excel 2007

  • 8/2/2019 Apostila Excel 2007 Senai

    44/53

    Excel 2007

    Imagine que fizemos um emprstimo de R$10000,00, e queremos saber quantopagaremos por ms. Sabemos que a taxa de juros de 8% ao ano, e quepagaremos este valor em 10 meses. Podemos proceder da seguinte forma.

    Dividimos a taxa por doze para obtero valor mensal de juros, o resultado

    mostrado como negativo porque oExcel entende que como o valor um pagamento, este mesmo estsendo decrescido de nossa receita.

    EXERCCIO

    Queremos R$50000,00, e utilizaremos uma poupana onde o rendimento de 6%AA, e depositaremos esta quantia por 18 anos. Quanto devemos depositar por msnesta poupana para obter o valor pretendido?

    R:_____________________________

    =VF(taxa;nper;pgto;vp;tipo)Retorna o valor futuro de um investimento de acordo com os pagamentos

    peridicos e constantes e com uma taxa de juros constante. Taxa a taxa de juros por perodo. Nper o nmero total de perodos de pagamento em uma anuidade. Pgto o pagamento feito a cada perodo; no pode mudar durante a

    vigncia da anuidade. Geralmente, pgto contm o capital e os juros enenhuma outra tarifa ou taxas. Se pgto for omitido, voc dever incluiro argumento vp.

    Vp o valor presente ou a soma total correspondente ao valorpresente de uma srie de pagamentos futuros. Se vp for omitido, serconsiderado 0 (zero) e a incluso do argumento pgto ser obrigatria.

    Tipo o nmero 0 ou 1 e indica as datas de vencimento dospagamentos. Se tipo for omitido, ser considerado 0.

    Imaginemos que vamos iniciar uma poupana, sendo que a taxa de juros damesma de 6%AA, que ns depositaremos inicialmente R$500,00 e que a cadams depositaremos mais R$200,00. Quanto teramos ao final de 12 meses?

    =VF(6%/12;12;-200;-500;0)Colocamos ento a frmula com um sinal de no pgto e vp, porque estes

    valores foram pagos e no recebidos.

    EXERCCIO1 - Voc est fazendo uma poupana para custear a faculdade do seu filho, o

    valor inicial de R$0,00, voc depositar R$100,00 mensais durante 18 anos.Sabendo que a taxa de rendimento da poupana de 0,53%AM, quanto voc terao final do perodo?

    R:______________________________

    ________________________________Pgina 44 de 53Excel 2007

  • 8/2/2019 Apostila Excel 2007 Senai

    45/53

    Excel 2007

    2 E se os valores fossem os mesmos, alterando apenas a taxa de juros para11%AA?

    R:_______________________________

    =TAXA(nper;pgto;vp;vf;tipo;estimativa)Retorna a taxa de juros por perodo de uma anuidade. TAXA calculado por

    iterao e pode ter zero ou mais solues. Se os resultados sucessivos de TAXAno convergirem para 0,0000001 depois de 20 iteraes, TAXA retornar o valor deerro #NM!.

    Nper o nmero total de perodos de pagamento em uma anuidade. Pgto o pagamento feito em cada perodo e no pode mudar durante

    a vigncia da anuidade. Geralmente, pgto inclui o principal e os juros enenhuma outra taxa ou tributo. Se pgto for omitido, voc dever incluiro argumento vf.

    Vp o valor presente o valor total correspondente ao valor atual deuma srie de pagamentos futuros.

    Vf o valor futuro, ou o saldo, que voc deseja obter depois do ltimopagamento. Se vf for omitido, ser considerado 0 (o valor futuro de umemprstimo, por exemplo, 0).

    Tipo o nmero 0 ou 1 e indica as datas de vencimento. Estimativa a sua estimativa para a taxa.

    Imaginemos que emprestamos R$8000,00, e que pagaremos este em 4 anos, sendoum pagamento fixo mensal de R$200,00. Queremos conhecer a taxa de juros, entofaremos o seguinte.=TAXA(4*12;-200;8000)*12

    Com a frmula acima conhecemos ento a taxa de juros anual, por issomultiplicamos a frmula por 12. Se quisermos a taxa de juros mensal, no faz-se

    necessria esta multiplicao final.

    EXERCCIO - TESTEVou comprar um carro financiando totalmente. O valor do veculo de

    R$15000,00. A financeira A ofereceu-me 36 parcelas de R$491,08, e a financeiraB ofereceu-me 48 parcelas de R$409,90. As duas financeiras garantiram que ataxa de juros fixa independentemente do perodo. Eu quero pagar em 30 meses.Em qual financeira mais compensatrio o negcio?

    R:____________________________

    =NPER(taxa;pgto;vp;vf;tipo)Retorna o nmero de perodos para investimento de acordo com pagamentosconstantes e peridicos e uma taxa de juros constante.

    Taxa a taxa de juros por perodo. Pgto o pagamento feito em cada perodo; no pode mudar durante a

    vigncia da anuidade. Geralmente, pgto contm o capital e os juros, masnenhuma outra tarifa ou taxas.

    Vp o valor presente ou atual de uma srie de pagamentos futuros. Vf o valor futuro, ou o saldo, que voc deseja obter depois do ltimo

    pagamento. Se vf for omitido, ser considerado 0 (o valor futuro de umemprstimo, por exemplo, 0).

    ________________________________Pgina 45 de 53Excel 2007

  • 8/2/2019 Apostila Excel 2007 Senai

    46/53

    Excel 2007

    Tipo o nmero 0 ou 1 e indica as datas de vencimento.Imagine que se queira ajuntar R$10000,00, temos inicialmente R$1000,00 e

    por ms depositaremos mais R$100,00 a uma taxa de juros de 8%AA. Porquanto tempo deveremos depositar este dinheiro para obter o objetivo.

    =NPER(8%/12;-100;-1000;10000)Inserimos o sinal negativo a frente de Vp e Vf pois so valores que devem ser

    considerados como gastos.

    EXERCICIOSDetermine o perodo de um investimento de R$150,00 mensais, tendo um valorinicial de R$0,00 e uma taxa de juros de 12%AA. Sabe-se ainda que o valor finaldo investimento deve ser de R$18000,00.

    R:_______________________________

    FUNES ESTATSCAS

    =VAR(nm1;nm2;...)Estima a varincia com base em uma amostra.

    =VAR(123;124;123;125;130;132;120) 17,9

    =DESVPAD(nm1;nm2;...)

    Calcula o desvio padro a partir de uma amostra. O desvio padro umamedida do grau de disperso dos valores em relao ao valor mdio (a mdia).Nm1;nm2;... so de 1 a 255 argumentos numricos correspondentes a uma

    amostra de populao. Voc tambm pode usar uma nica matriz ou umareferncia a uma matriz em vez de argumentos separados por ponto-e-vrgula.

    Imaginemos o quadro abaixo com a relao de produo diria de latas:

    Calculamos o desvio inserindo =desvpad(B2:G2), assim o Excel nos retorna o valorreferido.

    FUNES DIVERSAS

    ARRED E TRUNCAR

    A funo ARRED arredonda o resultado de um clculo para quantas casas fornecessrio. Basta incluir na frmula esta funo, observe abaixo:=ARRED(B5*A2;3)Esta frmula significa que os valores das clulas B5 e A2 sero multiplicados, poremo resultado somente nos dar valores com trs casas decimais.

    ________________________________Pgina 46 de 53Excel 2007

  • 8/2/2019 Apostila Excel 2007 Senai

    47/53

    Excel 2007

    A funo TRUNCAR semelhante ao ARRED, porm ao passo que o arredarredonda os valores o truncar simplesmente ignora os valores, realizando o clculodesta forma, como segue o exemplo:=TRUNCAR(A5+B2;2)Na condio acima a funo somaria A5 e B2, feito isso nos daria um resultadosomente com duas casas decimais. O problema que em valores que necessitam

    de grande preciso, um erro grosseiro poderia ser gerado.

    EXERCCIO

    1 - Crie uma planilha com formatao prpria, mas que exiba campos para o usurioimputar Taxa de juros, mensalidade valor inicial e mensalidade. Esta planilha deveindicar o valor futuro. A guia taxa de juros deve conter uma lista drop down quecontenha os valores (6%, 8%, 10%, 12% e 14%) ao ano. Tambm em forma de listadevemos ter o perodo em 6, 12, 24, 36, 48, 60, 84 e 100 meses. Deve haver umboto para limpar a pesquisa.OBS.: todas as clulas devem trabalhar com duas casas decimais.

    2 Crie uma planilha que resolva sozinha o problema exposto em EXERCCIO TESTE na pgina 45.

    ________________________________Pgina 47 de 53Excel 2007

  • 8/2/2019 Apostila Excel 2007 Senai

    48/53

    Excel 2007

    ATINGIR META

    Esta funo muito til quando desejamos saber qual o valor inicial ideal paraobtermos um resultado j definido, considerando vrios critrios.

    Crie em uma planilha o exemplo ao lado.

    O item IR deve ser 7,5% do salrio nominal. O itemINSS deve ser 5% do salrio nominal e o itemConvnio mdico deve ser um valor fixo.Notamos que o salrio lquido o valor do salrionominal decrescido dos respectivos descontos.Ento pergunta-se, Para um salrio lquido de

    R$1200,00, qual deveria ser o salrio nominal?Para tanto devemos clicar na GUIA DADOS e posteriormente em teste de

    hipteses. Surgir ento um menu com trs opes, selecione a opo Atingirmeta....

    Uma pequena janela como a da figura aolado deve surgir, nela temos o campo, Definirclula, no qual devemos colocar o endereo daclula que queremos modificar o valor, que noexemplo acima seria B5, normal que ao clicarna clula o Excel a trave utilizando $. Depoisprecisamos informar qual seria o valor desejado,por isso colocamos 1200 no campo Para valor,e por fim devemos indicar ao atingir meta qualclula deve variar para este valor de 1200, queeste caso a clula B1. Clicamos ento em OK.

    Neste ponto o Excel calcular aquantia inicial e nos dar o resultado finalconsiderando as variaes, exibindo destaforma outra janela, nos mostrando o statusda ferramenta, em casos de planilhas muitograndes onde a ferramenta precisa analisarmuitos dados comum levar-se algunssegundos para a finalizao do clculo.

    Podemos agora clicar em OK, quenossa planilha manter os dadosdeterminados na tela.

    Nos itens Tabela de dados... eGerenciador de cenrios... que tambmencontramos em teste de hipteses podefazer-se o clculo contrrio do atingir meta,ou seja, enquanto o atingir meta utiliza um resultado para chegar a um valorprimrio, as outras duas ferramentas citadas fazem checar valores de entrada parachegar a um resultado, exatamente como uma frmula convencional faz.

    ________________________________Pgina 48 de 53Excel 2007

  • 8/2/2019 Apostila Excel 2007 Senai

    49/53

    Excel 2007

    SOLVER

    Primeiramente devemos inserir a ferramenta clicando no cone do Office, notopo esquerdo da tela, depois em Opes do Excel selecionando a guiaSUPLEMENTOS, neste ponto surgir uma lista onde deve-se localizar e selecionaro item Solver, clica-se ento em Ir.

    Uma janela como a ao lado dever surgir,selecione a ferramenta solver e clique emOK.O cone Solver aparecer na GUIADADOS.

    Esta ferramenta um atingir metas mais avanado. Imaginem que para oexemplo anterior (atingir metas) onde somente temos uma opo de clula varivel,no solver bem mais completo.

    Tomemos como princpio, ainda no exemplo anterior que o salrio lquidodever ser R$1200,00, mas o IR no pode ser maior que R$80,00. Com aferramenta solver possvel resolver este problema.

    Observe o exemplo abaixo:

    Clula de destino = deve ser a clula onde se quer aplicar a soluo. Valor de = o valor que desejamos no Salrio lquido. Clulas variveis = So IR, INSS e Convnio mdico, pode-se

    selecionar o intervalo. necessrio sempre selecionar TODAS asvariveis, pois o solver faz alguns ajustes nos demais valores onde noexistem restries.

    ________________________________Pgina 49 de 53Excel 2007

  • 8/2/2019 Apostila Excel 2007 Senai

    50/53

    Excel 2007

    Submeter as restries = Para inserirmos as restries temos que clicarem adicionar, uma janela surgir.

    Podemos clicarna clula que queremos submeter a restrio, e inserir os respectivos dados,clicando em OK.Se quisermos retirar uma restrio devemos selecionar a mesma e clicar em excluir.Assim que acionamos OK ao Adicionar Restrio podemos visualizar novamente ajanela de Parmetros do Solver, se as informaes estiverem corretas podemosclicar em Resolver.

    O Excel solucionar o problema e nos dar a janela abaixo, onde podemosmanter a soluo do solver ou voltar a tabela aos valores originais. Podemos aindaselecionar relatrios para exibio de resultados, selecionando-os no campoRelatrios e clicando em OK.

    Note nos resultados que para manter o IR de acordo com a restrio osolver ajustou INSS e Convnio mdico.

    Vejamos um outro exemplo: Uma empresa pode fabricar dois produtos (A e B). Na fabricao do produto 1 a empresa gasta nove horas-homem e trs horas-

    mquina. Na fabricao do produto 2 a empresa gasta uma hora-homem e uma hora-

    mquina. A empresa dispe de 18 horas-homem e 12 horas-mquina para um perodo

    de produo. Sabe-se que os lucros lquidos dos produtos so $4 e $1 respectivamente.

    Tendo esta informao acima pergunta-se.

    ________________________________Pgina 50 de 53Excel 2007

  • 8/2/2019 Apostila Excel 2007 Senai

    51/53

    Excel 2007

    Como podemos determinar a melhor maneira de produo, visando o maiorlucro possvel?

    Necessitamos construir uma planilha para melhor compreenso utilizando claro, osolver.

    Na planilha temos a coluna restries, que servem somente para quetenhamos noo visual de que temos somente 18HH e 12HM.Ainda na planilha temos, ao lado de restries uma coluna chamada LIMITE,nela temos as seguintes frmulas =(G18*G20)+(H18*H20) e =(G19*G20)+(H19*H20), que simplesmente so os valores mximos de tempo porproduo. Por exemplo, multiplica-se a HH pelo total de produo do produtoA e multiplica-se a HM tambm pelo total de produo de A

    J na janela do solver, temos: Em definir destino lanamos o valor que queremos atingir, que no nosso caso

    o total de lucro. Nesta clula ns colocamos uma frmula =(G20*4)+(H20*1), ou seja, multiplicamos a produo pelo valor do lucro. Selecionamos a guia `Mx para obtermos a maximizao de nossa soluo,

    que o Total lucro. Valor pode ser zero, pois queremos o mximo e no um valor mnimo. Em clulas variveis selecionamos as clulas que tero uma variao, que

    so neste caso, o total da produo. Estes valores aqui foram imputadosmanualmente, mas podem ser automatizados com a ajuda do procv, porexemplo.

    Adicionamos as restries que temos que so um tempo limitado a 18 horas eoutro limitado a 12 horas.

    ________________________________Pgina 51 de 53Excel 2007

  • 8/2/2019 Apostila Excel 2007 Senai

    52/53

    Excel 2007

    Podemos ainda clicar em opes e selecionar as guias Presumir modelolinear para que o resultado seja linear e no logartmico, e selecionamostambm Presumir no negativos para que o programa no nos retorne umvalor negativo. Clicamos ento em OK

    Ao clicar em OK o solver voltou a janela principal do mesmo, acionamos entoRESOLVER, e a imagem abaixo surgir.

    Isto significa que para o lucro ser o maior possvel devemos produzir umapea do produto A e 9 peas do produto B, observando ainda que o LIMITE detempo foi respeitado.

    EXERCCIO A empresa JM fabrica vrios produtos mas trs deles no apresentam

    resultados produtivos satisfatrios. Precisamos ento maximizar a produo. Na fabricao de talharine gasta-se 3 horas-homem e 3 horas-mquina.

    ________________________________Pgina 52 de 53Excel 2007

  • 8/2/2019 Apostila Excel 2007 Senai

    53/53

    Excel 2007

    Na fabricao de Espaguete gasta-se 2 hora-homem e 1 hora-mquina. Na fabricao de Parafuso gasta-se 1 hora-homem e 4 hora-mquina. A empresa dispe de 30 horas-homem e 20 horas-mquina para um perodo

    de produo. Sabe-se que os lucros lquidos dos produtos so R$10,00, R$8,00 e R$6,00

    respectivamente.Tendo estas especificaes de tempo, quanto deve-se produzir de cada massa paraobter o MAIOR lucro possvel?

    R:___________________________________________________________