Fórmulas Exel

Embed Size (px)

Citation preview

  • 1

    MICROSOFT EXCEL

    APOSTILA DE FRMULAS E FUNES

    Professores: Edson Roberto Rezende

    Jorge Alberto Frania

  • 2

    ndice

    Conceitos bsicos .............................................................................................. 3 Operadores..................................................................................................... 3 FRMULA DA SUBTRAO......................................................................... 3 FRMULA DA MULTIPLICAO .................................................................. 3 FRMULA DA DIVISO................................................................................. 4 PRECEDNCIA DE OPERADORES.............................................................. 4 ARREDONDAMENTO.................................................................................... 5 USANDO O AUTOPREENCHIMENTO PARA COPIAR FRMULAS. ........... 5 ENDEREOS ABSOLUTOS E RELATIVOS.................................................. 6

    ENDEREOS ABSOLUTOS ..................................................................................... 6 FRMULA DA PORCENTAGEM....................................................................... 8 FUNES.......................................................................................................... 9

    FUNO SOMA ............................................................................................. 9 Usando o assistente de funo..................................................................... 10 FUNO MXIMO ....................................................................................... 11 FUNO MNIMO ........................................................................................ 11 FUNO MDIA .......................................................................................... 11 FUNO DATA DE HOJE............................................................................ 12 ALGUMAS CONSIDERAES SOBRE DATAS: ........................................ 12 COMO DESCOBRIR QUANTOS DIAS EXISTEM ENTRE DUAS DATAS... 12 FUNO PARA DESCOBRIR O DIA DA SEMANA DE UMA DATA ........... 12

    USANDO A FUNO SE................................................................................. 13 CRIANDO UMA FUNO SE COMPOSTA................................................. 14

    USANDO A FUNO SE ATRAVS DO ASSISTENTE DE FUNO ........... 15 USANDO O ASSISTENTE PARA CRIAR UMA FUNO SE COMPOSTA. 15 OUTRO EXEMPLO PARA FUNO SE...................................................... 17 FRMULA DA CONDIO SE e E.............................................................. 17 FRMULA DA CONDIO SE e OU........................................................... 18 FRMULA DO CONT.SE............................................................................. 18 FRMULA DO CONTAR.VAZIO.................................................................. 18 FRMULA DO SOMASE.............................................................................. 19 FRMULA DO PROCV ................................................................................ 20 FRMULA DO SE VAZIO ............................................................................ 21

    FUNES FINANCEIRAS............................................................................... 22 FUNO IPGTO .......................................................................................... 22 FUNO PGTO ........................................................................................... 24 FUNO TAXA ............................................................................................ 25 FUNO VF................................................................................................. 25

    OUTRAS FUNES........................................................................................ 26 RAIZ ............................................................................................................. 26 POTNCIA ................................................................................................... 26 ARRED ......................................................................................................... 26 ARREDONDAR.PARA.BAIXO...................................................................... 26 ARREDONDAR.PARA.CIMA........................................................................ 26

  • 3

    Conceitos bsicos

    Operadores

    Para construir as frmulas do Excel, primeiro precisa-se conhecer os operadores matemticos e de igualdade. Veja-os a seguir:

    1. SINAIS DE OPERAES 2. SINAIS PARA CONDIO SINAL FUNO SINAL FUNO + SOMAR > MAIOR QUE - SUBTRAO < MENOR QUE * MULTIPLICAO DIFERENTE QUE / DIVISO >= MAIOR E IGUAL A % PORCENTAGEM

  • 4

    FRMULA DA DIVISO

    A frmula ocorre da mesma maneira que as duas anteriores. Voc s precisa trocar colocar o sinal para dividir (/).

    A B C 1 RENDA MEMBROS VALOR 2 25000 15 =A2/B2 3

    PRECEDNCIA DE OPERADORES

    Ao criar um clculo que possua mais de uma operao matemtica, deve-se levar em considerao a precedncia de operadores, ou seja, qual operao o Excel far primeiro.

    Veja a precedncia abaixo na tabela:

    1 RAIZ E POTENCIA 2 MULTIPLICAO E DIVISO 3 ADIO E SUBTRAO

    O Excel faz os clculos nesta ordem e no na ordem digitada. Por exemplo:

    =10+5*2 O resultado deste clculo 20, pois primeiro o Excel multiplica 5 por 2 e depois adiciona o 10.

    Caso queira que a adio seja feita primeiro, use os parnteses:

    =(10+5)*2 O resultado ser 30. primeiro ocorre a soma, 10+5, resultado 15 e depois a multiplicao por 2.

    Veja mais um exemplo:

    =(80-2)*4-(10+(40+10/2))

    Qual o resultado desta frmula ? Vamos resolver os parnteses. O mais interno primeiro.

    =(80-2)*4-(10+(40+10/2))

    1 divida 10 por 2 e some 40. resultado = 45

    2 some 10 ao 45. resultado = 55

    3: faa a subtrao. Resultado=78

    4 multiplique 78 por 4. depois subtria de 55 que o resultado do outro conjunto de parnteses.

  • 5

    Veja mais um exemplo:

    ARREDONDAMENTO

    Por padro o Excel arredonda os decimais terminados em 1,2,3 e 4 para baixo e os decimais terminados 5,6,7,8 e 9 para cima.

    Por exemplo:

    5,236 removendo uma casa decimal : 5,24 5,236 removendo duas casas decimais: 5,2

    Caso queira fazer um arredondamento diferente do padro use as funes ARREDONDAR.PARA.BAIXO e ARREDONDAR.PARA.CIMA que so explicadas nesta apostila.

    USANDO O AUTOPREENCHIMENTO PARA COPIAR FRMULAS. possvel usar o auto preenchimento para copiar qualquer frmula criada. Este processo economiza muito tempo e um dos principais recursos do Excel. Para isso basta criar a primeira frmula na clula desejada (normalmente a primeira da sua lista de valores), depois clique sobre ela e leve o ponteiro do mouse at o canto inferior direito. Observe que o cursor muda para uma cruz preta e fina. Clique e arraste para copiar para outros itens da lista.

    =10*4/2-(2*3)

    1: resolva a multiplicao.

    2 Faa a multiplicao e em seguida a diviso. Neste caso resolve-se a operao na ordem da esquerda pra direita, pois as duas operaes tem a mesma precedncia. O resultado subtraia do parnteses.

    Clique aqui e arraste para copiar a frmula.

  • 6

    ENDEREOS ABSOLUTOS E RELATIVOS

    Os endereos de clulas podem ser divididos em 2 tipos. Os endereos relativos como o nome diz so referenciados pela posio da clula dentro da planilha. Quando copiados para outras clulas estes endereos mudam para manter relao com sua posio original. Veja o exemplo:

    ENDEREOS ABSOLUTOS Este tipo de endereo no muda quando a frmula copiada para outra clula. Chamamos este procedimento de travar clulas. Para isso coloca-se um cifro ($) antes da letra e um antes do numero da clula. Por exemplo: $A$1. Use este endereo em valores de ndice, que sero usados para clculos em toda a tabela de dados e que normalmente ficam em separado na sua planilha.

    Digite esta frmula na clula D1

    Use o autopreenchimento aqui e copie para as demais clulas

    Veja que para esta clula todas as referencias mudaram aumentando o nmero da linha

    Para este clculo veja que o endereo do dlar no ir mudar pois est travado $B$2

  • 7

    Usando o auto preenchimento e copiando esta frmula para outras clulas veja como fica o clculo da linha de baixo:

    Veja aqui que o endereo no mudou. Continua como $B$2. Mudou apenas a clula do preo em Real

  • 8

    FRMULA DA PORCENTAGEM

    Primeiro entenda que: 100% 1 50% 0,5 10% 0,1 8% 0,08

    Ou seja, toda porcentagem equivale a um numero decimal. O excel no trabalha com porcentagens da mesma forma que a calculadora. Para somar uma porcentagem a um valor voc deve multiplicar o valor pela porcentagem e somar o valor em seguida.

    Veja o exemplo:

    A B C D 1 Produto Preo custo Acrscimo. Preo final 2 Mouse R$ 10,00 30% =B2*C2+B2 3

    Obs: Multiplicando-se B2*C2 obtm-se quanto 30% de 10, ou seja, 3. somando-se em seguida o valor do mouse > 10 tem-se o preo final

    Veja outro exemplo. Um cliente de sua loja, fez uma compra no valor de R$ 1.000,00 e voc deseja dar a ele um desconto de 8% em cima do valor da compra.

    A B C D 1 CLIENTE TCOMPRA DESCT. VL A PAGAR 2 Maria 1000 8% =B2-B2*C2 3

  • 9

    FUNES

    Em breves palavras, uma funo um comando que recebe uma informao, realiza um clculo e devolve uma resposta ao usurio.

    FUNO SOMA

    Ex: =SOMA(A1:A8) . A funo ir somar todos os valores que se encontram no endereo A1 at o endereo A8.

    Os dois pontos indicam at, ou seja, some de A1 at A8. A frmula ser sempre a mesma, s mudar os devidos endereos dos valores que voc deseja somar.

    Veja o outro exemplo: A B C D E 1 10 25 15 10 =SOMA(A1:D1) 2 3 4 5

    Neste exemplo estamos somando todos os valores do endereo A1 at o endereo D1. A frmula seria digitada como no exemplo, e ao teclar enter o valor apareceria. No caso a resposta seria 60.

    Outra maneira de voc somar utilizando o Boto da Autosoma. Veja o exemplo:

    Este o boto da AutoSoma. Para trabalhar com o boto da Autosoma voc deve fazer o seguinte: 1. Selecionar os valores que desejar somar. 2. Depois clique no Boto da Autosoma e ele mostrar o resultado. Veja mais um exemplo de Soma Agora voc deseja somar todos os valores dispostos nesta planilha usando uma nica frmula, desta vez voc ter que digitar a frmula.

    A B C D E 1 10 25 15 10 2 15 20 25 15 3 14 25 25 25 4 TOTAL =SOMA(A1:D3) 5 6 7 8

    Para fazer isto, s basta que voc digite o endereo inicial (em destaque) e tambm o endereo final(em destaque) Desta forma, voc est somando todos os valores numa nica frmula, o que chamamos de Somar Matrizes.

    Acompanhe mais um exemplo de Soma.

    Desta vez voc deseja somar nmeros dispostos de maneira alternada, ou seja, em endereos diferentes. Veja o exemplo:

    A B C D E 1 GUA LUZ GUA LUZ 2 150 35 75 55 3 4 TOTAL DA GUA =A2+C2 5 TOTAL DA LUZ =B2+D3

  • 10

    Voc desejar somar somente os valores de gua, ento, basta digitar o endereo de cada valor, ou seja, o endereo do primeiro valor + o endereo do 2 valor e assim sucessivamente. Lembre-se que sempre devo iniciar o clculo usando o sinal de igualdade.

    Usando o assistente de funo

    O assistente de funo auxilia na montagem de uma funo, colocando o nome da funo corretamente, abrindo e fechando o parnteses, alm de mostrar explicaes sobre a montagem e o resultado prvio do clculo. Para usar o assistente, clique no boto colar funo. Surgir ento o seguinte quadro:

    Escolha a categoria e a funo desejada e depois clique no OK. Caso queira uma explicao sobre o uso da funo, clique no boto ajuda ( ? ) que est no canto inferior esquerdo do quadro. Como exemplo vamos escolher a funo SOMA. Veja o quadro que aparece em seguida:

    No quadro acima ele sugere somar o intervalo que vai do A1 at o A5 na caixa Num1. na frente aparecem os valores deste intervalo (1,26,59,89,63). A caixa Num2 possvel colocar mais um intervalo de clulas, ou seja podemos somar vrios intervalos de clulas intercalados. Veja

  • 11

    tambm que o canto inferior esquerdo temos o resultado da frmula (238). Clique no OK para confirmar a soma.

    Neste exemplo portanto, bastou selecionar a rea que se deseja somar. Para outras funes basta tambm apenas selecionar a faixa de clulas. O processo o mesmo, apenas a funo que fornece outro tipo de resultado. So elas: MNIMO, MXIMO, e MDIA.

    Veja a seguir uma explicao destas funes:

    FUNO MXIMO

    Mostra o valor mximo de uma faixa de clulas. Exemplo: Suponha que desejasse saber qual a maior idade de crianas em uma tabela de dados. Veja a frmula no exemplo abaixo:

    A B C 1 IDADE 2 15 3 16 4 25 5 30 6 MAIOR IDADE: =MXIMO(A2:A5) 7

    Onde: (A2:A5) refere-se ao endereo dos valores onde voc deseja ver qual o maior valor. No caso a resposta seria 30. Faa como mostra o exemplo trocando apenas o endereo das clulas.

    FUNO MNIMO

    Mostra o valor mnimo de uma faixa de clulas. Exemplo: Suponha que desejasse saber qual o menor peso de crianas em uma tabela de dados. Veja a frmula no exemplo abaixo:

    A B C 1 PESO 2 15 3 16 4 25 5 30 6 MENOR IDADE: =MNIMO(A2:A5) 7

    FUNO MDIA

    Calcula a mdia de uma faixa de valores. Exemplo: Suponha que desejasse saber qual a mdia de idade numa tabela de dados abaixo:

    A B C 1 IDADE 2 15 3 16 4 25 5 30 6 MDIA IDADE =MDIA(A2:A5)

  • 12

    FUNO DATA DE HOJE

    Esta frmula insere a data automtica em uma planilha. Veja o exemplo

    A B C 1 Data =HOJE() 2 3

    Esta frmula digitada precisamente como esta. Voc s precisa colocar o cursor no local onde deseja que fique a data e digitar =HOJE() e ela colocar automaticamente a data do sistema.

    ALGUMAS CONSIDERAES SOBRE DATAS:

    Datas so na verdade nmeros decimais. O Excel assume como nmero 1 a data 01/01/1900. Para ver qual numero corresponde a uma determinada data, digite a mesma em uma clula e depois clique no boto Separador de milhares na barra de botes ou use o menu Formatar Clulas e transforme a data em nmero. Por exemplo 15/05/2003 corresponde ao nmero 37.756. Usando os nmero o Excel pode resolver clculos envolvendo datas.

    COMO DESCOBRIR QUANTOS DIAS EXISTEM ENTRE DUAS DATAS

    simples, basta digitar as datas nas clulas desejadas e depois subtrair a mais recente da mais velha. Veja o exemplo:

    A B C 1 Data inicial 10/02/2001 2 Data Final 15/10/2002 3 Dias =B2-B1

    Caso o resultado seja apresentado em forma de data, basta converte-lo para nmero.

    FUNO PARA DESCOBRIR O DIA DA SEMANA DE UMA DATA

    Atravs da funo texto podemos descobrir o dia da semana em que caiu uma determinada data. Veja o exemplo

    A B C 1 Data 10/02/2001 2 Dia da semana =TEXTO(B1;"ddd") 3

    Acima o formato ddd coloca o dia da semana com 3 letras (seg, ter, qua), colocando o formato como dddd ele apresenta o dia completo (segunda-feira, tera-feira...)

    Lembre-se que voc pode usar o assistente de funo para resolver qualquer frmula apresentada aqui.

  • 13

    USANDO A FUNO SE

    A funo SE do Excel sempre utilizada quando o valor de uma clula pode variar.

    A tarefa da funo SE comparar o valor da clula com um crtrio estabelecido e retornar dois resultados. Um se a comparao for verdadeira e outro se a comparao for falsa.

    Veja estas comparaes:

    5+2 > 8 comparao falsa 2*3 = 6 comparao verdadeira (2+8) * 2 > 5+6 comparao verdadeira.

    Estas comparaes so bem simples e conhecidas. No Excel funciona da mesma forma, mas comparamos uma clula (ou seja, seu contedo) com um determinado valor

    No exemplo a seguir o aluno reprovado caso sua mdia seja menor que 7. Sendo assim 7 o critrio para aprovao. Ento compara-se a mdia do aluno com 7. Se a comparao retornar verdadeiro, este estar aprovado, caso contrrio estar reprovado.

    A B C D E 1 Aluno 1 Bim. 2 Bim Mdia Resultado 2 Maria 4 6 5 Reprovado 3 Joo 6 9 7,5 Aprovado

    Nossa comparao ficaria assim para o 1 aluno:

    Mdia da Maria > 7 comparao falsa. Ento REPROVADA

    No momento a Maria tem mdia menor que 7, mas o valor da mdia pode variar no decorrer dos bimestres, fazendo com que a aluna seja aprovada. A funo SE trata ento as duas situaes. Quando a comparao for falsa e quando for verdadeira.

    A forma de montar a Funo a seguinte:

    =SE ( Comparao ; Resultado caso Verdadeiro ; Resultado caso Falso)

    A funo SE para este exemplo ficaria assim:

    = SE (D2 > 7 ; APROVADO ; REPROVADO)

    No exemplo acima o resultado da mdia da aluno Maria, REPROVADO, pois sua mdia menor do que 7 o que faz com que a comparao retorne falso. Para o aluno Joo, o resultado APROVADO, j que sua mdia maior que 7, fazendo com que a comparao seja verdadeira.

    MDIA Critrio

    Resultado se a comparao retornar

    Resultado se a comparao retornar

  • 14

    Obs: o sinal de ponto e vrgula ( ; ) obrigatrio na funo e serve para separar as 3 partes da mesma. Toda vez que usar um texto na em qualquer funo o mesmo deve vir dentro de aspas como o palavra aprovado e reprovado. Quando usar numeros ou endereos de clulas (p. ex: D2 ) no h a necessidade do aspas.

    CRIANDO UMA FUNO SE COMPOSTA.

    Quando o valor da clula pode conter mais de dois resultados necessrio criarmos a funo SE composta de mais outra.

    No exemplo a seguir o aluno que tiver mdia menor que 4 est reprovado, o aluno que tiver mdia maior que 7 estar aprovado, e o aluno que tiver um mdia entre 4 e 7 ficar de exame. Note portanto que podemos ter 3 situaes para o resultado das notas do aluno.

    necessrio ento criarmos duas funes SE, para realizar duas comparaes, sobrando um ultima alternativa para o retorno da resposta. Usando o exemplo da tabela de notas acima teramos:

    =SE ( D2 > 7; APROVADO ; SE(D2 < 4; REPROVADO ; EXAME ))

    No exemplo acima a primeira comparao , sendo verdadeira, retorna a resposta APROVADO para o aluno, caso seja falsa, ser realizada uma nova funo para comparar novamente a o valor da clula, pois ainda no podemos afirmar que o aluno est REPROVADO ou de EXAME. A segunda comparao retorna APROVADO caso a comparao de D2 < 4 seja verdadeira. E retorna EXAME caso a comparao seja falsa, pois se D2 no nem maior que 7 (1 comparao) e nem menor que 4 s pode estar entre 4 e 7, fazendo com que o aluno fique de exame.

    Comparao Resposta se a comparao for verdade

    nova funo SE caso a 1 retorne falso.

  • 15

    USANDO A FUNO SE ATRAVS DO ASSISTENTE DE FUNO

    possvel utilizarmos o assistente de funo SE atravs do assistente de funo.

    Para isso, devemos clicar no seguinte boto :

    Surgir ento uma janela com duas listas. Na lista da esquerda, escolha a categoria Lgica. Na lista da direita, escolha a funo SE e clique em OK. Surgir ento o seguinte quadro, dividindo a funo SE em 3 caixas, que correspondem s 3 partes da funo. Montando o exemplo anterior para aluna Maria no Assistente, este ficaria assim:

    O assistente cria facilidades pois reduz a possibilidade de erros de digitao na funo.

    USANDO O ASSISTENTE PARA CRIAR UMA FUNO SE COMPOSTA.

    Usaremos a seguir o mesmo exemplo para criar a funo SE.

    Acione a funo SE atravs do boto colar funo.

    Passe os seguintes dados para a funo:

    Para iniciar a nova funo, clique em seguida neste boto que fica na parte superior esquerda da janela, acima das letras das colunas.

    Clique aqui. Neste ponto devemos informar o inicio da nova funo SE

  • 16

    Ser exibida uma nova janela da funo, monte da seguinte forma:

    Clique em OK para confirmar.

  • 17

    OUTRO EXEMPLO PARA FUNO SE

    A B C 1 Funcionrio Salrio INSS 2 Jos R$ 850,00 3 Fernanda R$ 1210,00 4 Ana R$ 1550,00

    Neste exemplo precisamos criar uma funo se para determinar o desconto do INSS do funcionrio. Imagine uma tabela de desconto desta forma:

    Salrio at 900 desconto de 8% do salrio Salrio entre 901 e 1300 Desconto de 9% do Salrio Salrio acima de 1300 Desconto de 10% do Salrio.

    A funo neste caso, para o primeiro funcionrio ficaria assim:

    =SE (B2 < 900 ; B2*8% ; SE (B2 > 1300 ; B2*9% ; B2*10%))

    A primeira funo SE compara se o salrio (B2) menor que 900. Caso seja verdadeira a comparao o calculo realizado salrio * 8%. Caso a comparao seja falsa devemos realizar um outro SE para determinar se o salrio est entre 901 e 1300 ou acima de 1300. Neste caso a segunda funo SE compara se o Salrio maior do que 1300, sendo verdadeira, desconta-se 9%, sendo falsa entende-se que o Salrio est entre 901 e 1300, descontando 10%

    FRMULA DA CONDIO SE e E

    Agora voc tem uma planilha onde tem a idade e altura de seus alunos. Haver uma competio e somente aqueles que tem Idade Maior que 15 e Altura maior ou igual que 1,70 participaro da competio. Neste caso voc utilizar a condio SE e a condio E. Porque? simples, porque para o aluno participar ele dever possuir a idade maior que 15 e altura maior ou igual 1,70. As duas condies devem ser verdadeiras, caso uma seja falsa, ele no participar. Veja o exemplo:

    A B C D 1 ALUNO IDADE ALTURA SITUAO 2 Mrcio 22 1,72 =SE(E(B2>15;C2>=1,70);Competir;No Competir) 3 Joo 14 1,68 Onde: B2 refere-se ao endereo da idade >15 refere-se a condio, ou seja, se a idade for maior que 15 C2 refere-se ao endereo da altura >=1,70 refere-se a condio, ou seja, se a altura for maior ou igual a 1,70 Competir resposta se as duas condies forem verdadeiras. No Competir- resposta se caso as duas respostas no forem verdadeiras.

  • 18

    FRMULA DA CONDIO SE e OU

    Neste exemplo basta que uma condio seja verdadeira para que o aluno participe da condio.

    Veja o exemplo:

    A B C D 1 ALUNO IDADE ALTURA SITUAO 2 Mrcio 22 1,72 =SE(OU(B2>15;C2>=1,70);Competir;No Competir) 3 Joo 14 1,68

    FRMULA DO CONT.SE

    Agora voc possui uma planilha onde tem o nome dos alunos e as suas mdias. E voc desejasse agora saber quantos alunos tiraram mdias maior e igual a 9. Veja o exemplo:

    A B 1 ALUNO MDIAS 2 Joo 7 3 Maria 10 4 Mrcio 6 5 Dborah 8

    =CONT.SE(B2:B5;>=9)

    Onde: (B2:B5) refere-se ao endereo das clulas onde voc desejar contar. ; utiliza-se como parte da sintaxe para separar >=9 refere-se a condio, ou seja, esta frmula s ir contar as clulas que contm valores maiores ou igual a 9. Siga a sintaxe, substituindo apenas os endereos e a condio para contar. Depois das aspas voc digita a condio. Pode ser tambm texto, independente de texto ou valor, deve-se colocar entre as aspas. Exemplo: =CONT.SE(C2:C5;APROVADO) Neste exemplo ele contar apenas as clulas que contm a palavra Aprovado.

    FRMULA DO CONTAR.VAZIO

    Contar as clulas que esto vazias. Exemplo:

    Voc gostaria de saber quantos alunos esto sem a mdia A B 1 ALUNO MDIAS 2 Joo 3 Maria 10 4 Mrcio 5 Dborah 8 6 Marta 10 7 Andra

    =CONTAR.VAZIO(B2:B7) Onde: =CONTAR.VAZIO o nome da frmula (B2:B7) refere-se ao endereo das clulas

  • 19

    FRMULA DO SOMASE

    Soma um intervalo de clulas mediante a condio estabelecida

    Exemplo:

    Voc gostaria de soma as faturas que foram pagas. Ento voc tem uma planilha onde na coluna A voc coloca o nome do cliente, na coluna B o valor da fatura e na coluna C, a situao se foi paga ou no. Voc gostaria de somar somente as faturas que estivessem pagas, assim voc saberia o quanto j recebeu. Logo a frmula seria a seguinte:

    A B C 1 CLIENTE VALOR SITUAO 2 Bemol 150 PG 3 TV Lar 250 4 MS Casa 350 PG 5 tica Avenida 180 6 Marta 250 PG 7 Andra 190 PG Valor Recebido =SOMASE(C2:C7;PG;B2:B7)

    Onde: =SOMASE- o nome da frmula (C2:C7 refere-se ao endereo inicial e final de clulas onde voc digita a palavra PG, especificando se est paga ou no. PG o critrio para somar, ou seja, s somar se neste intervalo de clulas de C2 at C7, conter alguma palavra PG. O critrio dever sempre ser colocado entre aspas. B2:B7 refere-se ao intervalo de clulas onde ser somado, mediante a condio, ou seja, ele somar somente aqueles valores que na coluna C voc digitou PG.

  • 20

    FRMULA DO PROCV

    Procura um determinado valor numa Tabela Matriz.

    Suponhamos que voc tivesse uma planilha onde controla-se a entrada e a sada de clientes do seu hotel. Nela voc deseja colocar o Nome, Entrada, Saida, Classe e o Valor da Classe. Voc deseja que ao digitar o nome da Classe, automaticamente aparea o valor da Classe. Na verdade voc ter que criar 2(duas) planilhas: A 1 Primeira planilha chamaremos de Matriz Tabela, porque nela voc colocar o nome das Classe e o seu Valor A 2 Segunda planilha voc controlar a entrada e a sada do cliente. Veja o Exemplo:

    A B C D TABELA DE CLASSES 1 CLASSE VALOR 2 ALTA 55,00 3 MDIA 45,00 4 BAIXA 25,00 5 PRESIDENTE 180,00 6 COMUM 22,00

    Esta a Tabela Matriz

    7 8 Hspede Tipo Classe Valor-Diria 9 JOAO ALTA =PROCV(B9;$A$2:$B$6;2) 10 KARLA BAIXA 11 MRCIO MDIA 12 13 14

    Onde: =PROCV o nome da frmula

    (B9 refere-se ao endereo do valor procurado, ou seja, o tipo da classe que voc digitou

    $A$2:$B$6 refere-se ao endereo absoluto da Matriz Tabela, ou seja, o endereo da tabela onde contem os dados que voc procura, no caso, o valor das classes. O endereo da tabela matriz sempre deve ser absoluto, para que permanea para as demais clulas(ou seja os clientes)

    2 refere-se ao nmero do ndice de coluna, ou seja, o nmero da coluna onde est os dados que voc deseja que aparea, no caso, o valor da classe.

    Observao: Os dados da Tabela Matriz devem est em ordem crescente. Para fazer isso, selecione os dados e clique no boto abaixo para ordenar os dados.

  • 21

    FRMULA DO SE VAZIO

    Imagine agora que voc s deseja que aparea a resposta se caso, voc digita a classe, enquanto isso o campo classe dever ficar em branco. Neste caso voc juntar a frmula do SE com a do PROCV. Logo a frmula ficaria assim:

    A B C D TABELA DE CLASSES 1 CLASSE VALOR 2 ALTA 55,00 3 MDIA 45,00 4 BAIXA 25,00 5 PRESIDENTE 180,00 6 COMUM 22,00

    Esta a Tabela Matriz

    7 8 Hspede Tipo Classe Valor-Diria 9 JOAO ALTA =SE(B9=;;PROCV(B9;$A$2:$B$6;2)) 10 KARLA BAIXA 11 MRCIO MDIA 12 13 14

    =Se o nome da frmula B9=; ; - refere-se ao endereo da clula onde voc digita o nome da classe. Isto , se estiver vazio, ento deixe vazio.

    PROCV o nome da frmula

    (B9 refere-se ao endereo do valor procurado, ou seja, o tipo da classe que voc digitou

    $A$2:$B$6 refere-se ao endereo absoluto da Matriz Tabela, ou seja, o endereo da tabela onde contem os dados que voc procura, no caso, o valor das classes. O endereo da tabela matriz sempre deve ser absoluto, para que permanea para as demais clulas(ou seja os clientes)

    2 refere-se ao nmero do ndice de coluna, ou seja, o nmero da coluna onde est os dados que voc deseja que aparea, no caso, o valor da classe.

  • 22

    FUNES FINANCEIRAS

    As funes financeiras facilitam em muito o clculo de juros, valor de parcelas e retornos de investimentos. So muitas as funes financeiras aqui veremos algumas das mais comuns.

    Observaes: os valores das funes podem retornar um valor negativo, caso queira evitar isso coloque o argumento PGTO da funo em nmero negativo.

    FUNO IPGTO Esta funo retorna o pagamento de juros para um determinado perodo de investimento de acordo com pagamentos peridicos e constantes e com uma taxa de juros constante

    Vamos a um exemplo. Digite o titulo na clula A1 : Exercicio n. 1 - Clculo de juros simples. Digite nas clulas correspondentes: A3: Valor do emprstimo A4: juros ao ms A5: meses A7: Resultado: B3: 20.000,00 B4: 3% B5: 4 Sua planilha deve ficar assim:

    Selecione a clula B3 e clique no boto formato de moeda para aplicar o

    formato R$. Agora vamos criar a funo para calcular o valor dos juros: Clique na clula B7 e clique no boto colar funo Surgir o seguinte quadro:

  • 23

    Selecione a categoria Financeira e depois a funo IPGTO. clique em OK. O prximo quadro ser:

    Digite os seguintes valores nas caixas: Taxa: B4 Perodo: 1 Nper: B5 VP: -B3 Veja o que significa cada item: Taxa a taxa de juros por perodo. Perodo o perodo cujos juros se deseja saber e deve estar no intervalo entre 1 e nper. Nper o nmero total de perodos de pagamento em uma anuidade. Vp o valor presente, ou a quantia total atual correspondente a uma srie de pagamentos futuros. Repare que logo abaixo no quadro ser apresentado o resultado. clique em OK. O resultado ser de R$ 600,00

  • 24

    Este resultado o valor dos juros do primeiro ms. Para calcular o juros dos 4 meses, voc pode multiplicar este valor por 4 editando a clula que contm a funo (B7). Clique na referida clula. Repare a funo na barra de frmulas. Clique no fim da mesma e digite: *4. Tecle Enter para confirmar. Veja a figura a seguir.

    Pronto, o valor da clula B7 agora de R$ 2.400

    OBS: Para os exemplos a seguir tambm pode-se usar o Assistente de funo.

    FUNO PGTO

    Retorna o pagamento peridico de uma anuidade de acordo com pagamentos constantes e com uma taxa de juros constante.

    Sintaxe da funo

    PGTO(taxa;nper;vp;vf;tipo)

    Taxa a taxa de juros por perodo. Nper o nmero total de pagamentos pelo emprstimo. Vp o valor presenteo valor total presente de uma srie de pagamentos futuros. Vf o valor futuro, ou o saldo de caixa, que voc deseja obter depois do ltimo pagamento. Se vf for omitido, ser considerado 0 (o valor futuro de determinado emprstimo, por exemplo, 0). Tipo o nmero 0 ou 1 e indica as datas de vencimento. Indique 1 se o pagamento for efetuado no inicio do perodo e 0 se for efetuado no final do perodo.

    A frmula a seguir retorna o pagamento mensal por um emprstimo de R$1.000 a uma taxa anual de 20% que voc deve pagar em 12 meses:

    PGTO(20%/12; 12; -1000)

    Os valores de VF foi omitido pois no temos o valor final do total de pagamento.

    Veja o quadro a seguir:

  • 25

    FUNO TAXA

    Retorna a taxa de juros por perodo de uma anuidade.

    Sintaxe

    TAXA(nper;pgto;vp;vf;tipo)

    Exemplo

    Para calcular a taxa de um emprstimo de quatro anos de $8.000 com pagamentos mensais de $200:

    TAXA(48; -200; 8000)

    FUNO VF

    Retorna o valor futuro de um investimento de acordo com os pagamentos peridicos e constantes e com uma taxa de juros constante.

    Sintaxe

    VF(taxa;nper;pgto;vp;tipo)

    Exemplo

    Suponha que deseja aplicar um dinheiro na poupana e que esta rende juros fixos todo o ms. Voc acaba de abrir uma conta no banco e depositou de inicio R$ 1000,00. Todo inicio de ms ir depositar R$ 120,00 durante 18 meses com rendimento mensal de 1%. Quanto obter no final do perodo ? Veja a frmula a seguir.

  • 26

    =VF(1%;18;-120;-1000;1)

    O resultado ser R$ 3.573,45

    OUTRAS FUNES

    RAIZ

    Esta funo retorna a raiz quadrada de um nmero. Exemplo: RAIZ(16) resultado ser 4.

    POTNCIA Fornece o resultado de um nmero elevado a uma potncia. POTNCIA(nm;potncia) Exemplo: potncia(5;2) igual a 25

    ARRED

    Arredonda um nmero at uma quantidade especificada de dgitos. Sintaxe ARRED(nm;nm_dgitos) Exemplo: ARRED(12,236;1) resultado ser 12,2.

    ARREDONDAR.PARA.BAIXO

    Esta funo arredonda um numero sempre para baixo. Exemplo: Suponha que queira arredondar o numero 15,236 para baixo com duas casas decimais: =ARREDONDAR.PARA.BAIXO(15,236;2) o resultado ser: 15,23

    ARREDONDAR.PARA.CIMA

    Esta funo arredonda um numero sempre para cima Exemplo:

  • 27

    Suponha que queira arredondar o numero 15,234 para cima com duas casas decimais: =ARREDONDAR.PARA.CIMA(15,234;2) o resultado ser: 15,24