Post on 05-Jul-2018
8/15/2019 Formulas Excel Augusto
1/17
1
MICROSOFT EXCEL
FÓRMULAS E FUNÇÕES
Conceitos básicos
Operadores
Para construir as fórmulas do Excel, primeiro precisa-se conhecer os operadores matemáticos
e de igualdade. Veja-os a seguir:
! SINAIS "E O#ERAÇÕES $! SINAIS #ARA CON"IÇ%O
Cria&'o de ()r*+,as-
Veja o exemplo a seguir:
A . C " Produto Vendas – an Vendas - !e" #otal$ $ouse %&& '&&/ (is)uete *&& +&&
Para calcular o total do exemplo acima de"emos criar uma fórmula de adio. Posicione ocursor na clula (/ e digite:
=B2+C2
#ecle E0#E ao terminar.
23045 !607890 29$41 26#47892 $65#3P53;4789 3 (3V32894 P9;E0#46E87 (3!EE0#E >6E75 $439 E 3
8/15/2019 Formulas Excel Augusto
2/17
/
?#oda fórmula sempre comea com sinal de igual para )ue o Excel entenda )ue um cálculo eno um texto )ual)uer )ue está digitando.
?0o se coloca o "alor na soma como, por exemplo, @%&&A'&& por)ue estes "alores podemsofrer alteraBes e o resultado no seria atualiCado, portanto "ocD teria )ue corrigir afórmula depois.
?6se sempre os endereos de clula / e ;/ como no exemploF. 4ssim alterando-se os"alores da clula o resultado será atualiCado automaticamente.
FÓRMULA "A SU.TRAÇ%O
0o exemplo aGaixo "ocD deseja saGer )ual o saldo lH)uido do os. Ento simples: asta )ue"ocD digite o endereo do 2alário ruto 9 o endereo do (esconto. Veja:.
4 ; E
1 !60; 256#9 (E2;#. 25 53>63(9/ os *&& 1+' @/-;/
%
FÓRMULA "A MULTI#LICAÇ%O
4gora a maneira como "ocD suGtraiu a mesma para multiplicar, será preciso apenas trocar osinal de suGtrao pelo o sinal de multiplicao 2F. Veja o exemplo.
4 ; E1 P9(6#9 V459 >640#. #9#45/ !eijo 1,'& '& @/I;/%
FÓRMULA "A "I:IS%O
4 fórmula ocorre da mesma maneira )ue as duas anteriores. VocD só precisa trocar colocar osinal para di"idir JF.
4 ;1 E0(4 $E$92 V459/ /'&&& 1' @4/J/%
#RECE";NCIA "E O#ERA"ORES
4o criar um cálculo )ue possua mais de uma operao matemática, de"e-se le"ar emconsiderao a precedDncia de operadores, ou seja, )ual operao o Excel fará primeiro.
Veja a precedDncia aGaixo na taGela:
< 43K E P9#E0;34$< $65#3P53;4789 E (3V3289/< 4(3789 E 26#4789
9 Excel faC os cálculos nesta ordem e no na ordem digitada. Por exemplo:
=10+5*2
9 resultado deste cálculo /&, pois primeiro o Excel multiplica ' por / e depois adiciona o 1&.
8/15/2019 Formulas Excel Augusto
3/17
=(80-2)*4-(10+(40+10/2))
º divida 10 por 21e some 40.resultado = 45
º some 10 ao 45.2resultado = 55
3º: a!a asu"tra!#o.
$esultado=%8
º multipli&ue %8 por 4.4depois su"traia de 55 &ue '
o resultado do outroouto de parteses.
=10*4/2-(2*3)
º: resolva a multiplia!#o.1
2 º ,a!a a multiplia!#o e em seuida a divis#o. esteaso resolve-se a opera!#o a ordem da es&uerda pradireita pois as duas opera!es tem a mesmapreedia. resultado su"traia do parteses.
%
;aso )ueira )ue a adio seja feita primeiro, use os parDnteses:
=(10+5)*2
9 resultado será %&. Primeiro ocorre a soma, 1&A', resultado 1' e depois a multiplicao por /.
Veja mais um exemplo:
@*&-/FIL-1&AL&A1&J/FF
>ual o resultado desta fórmulaMVamos resol"er os parDnteses. 9 mais interno primeiro.
Veja mais um exemplo:
USAN"O O AUTO#REENC=IMENTO #ARA CO#IAR FÓRMULAS!
8/15/2019 Formulas Excel Augusto
4/17
L
N possH"el usar o auto preenchimento para copiar )ual)uer fórmula criada. Esteprocesso economiCa muito tempo e um dos principais recursos do Excel. Para issoGasta criar a primeira fórmula na clula desejada normalmente a primeira da sua listade "aloresF, depois cli)ue soGre ela e le"e o ponteiro do mouse at o canto inferior direito. 9Gser"e )ue o cursor muda para uma cruC preta e fina. ;li)ue e arraste paracopiar para outros itens da lista.
EN"EREÇOS A.SOLUTOS E RELATI:OS
9s endereos de clulas podem ser di"ididos em / tipos. 9s endereos relati"os como o nomediC so referenciados pela posio da clula dentro da planilha. >uando copiados para outrasclulas estes endereos mudam para manter relao com sua posio original. Veja o exemplo:
EN"EREÇOS A.SOLUTOSEste tipo de endereo no muda )uando a fórmula copiada para outra clula. ;hamamoseste procedimento de tra"ar clulas. Para isso coloca-se um cifro OF antes da letra e um antesdo numero da clula. Por exemplo: O4O1.
;li)ue a)ui e arraste paracopiar a fórmula.
Veja )ue para esta clula todasas referencias mudaramaumentando o nmero da linha
(igite esta fórmulana clula (1
6se o autopreenchimento a)uie copie para as demaisclulas
8/15/2019 Formulas Excel Augusto
5/17
'
6se este endereo em "alores de Hndice, )ue sero usados para cálculos em toda a taGela dedados e )ue normalmente ficam em separado na sua planilha.
6sando o auto preenchimento e copiando esta fórmula para outras clulas "eja
como fica o cálculo da linha de Gaixo:
Para este cálculo "eja )ue oendereo do dólar no irámudar pois está tra"adoOO/
Veja a)ui )ue oendereo no mudou.;ontinua como OO/.$udou apenas aclula do preo emeal
8/15/2019 Formulas Excel Augusto
6/17
Q
FÓRMULA "A #ORCENTA>EM
Primeiro entenda )ue:1&&R 1
'&R &,'1&R &,1*R &,&*
9u seja, toda porcentagem e)ui"ale a um nmero decimal.9 excel no traGalha com porcentagens da mesma forma )ue a calculadora.Para somar uma porcentagem a um "alor "ocD de"e multiplicar o "alor pela porcentagem esomar o "alor em seguida.
Veja o exemplo:
4 ; (1 Produto Preo custo 4crscimo. Preo final/ $ouse O 1&,&& %&R @/I;/A/%
Obs: Multiplicando-se B2*C2 obtém-se uanto é !0" de 10# ou se$a# !% somando-se emse&uida o 'alo do mouse 10 tem-se o peo ,inal
Veja outro exemplo.6m cliente de sua loja, feC uma compra no "alor de O 1.&&&,&& e "ocD deseja dar a ele umdesconto de *R em cima do "alor da compra.
4 ; (1 ;53E0#E #;9$P4 (E2;#. V5 4 P4
8/15/2019 Formulas Excel Augusto
7/17
+
0este exemplo estamos somando todos os "alores do endereo 41 at o endereo(1. 4 fórmula seria digitada como no exemplo, e ao teclar enter o "alor apareceria. 0ocaso a resposta seria D!
9utra maneira de "ocD somar utiliCando o oto da 4utosoma. Veja o exemplo:
Este o Goto da 4uto2oma.Para traGalhar com o Goto da 4utosoma "ocD de"e faCer o seguinte:
1. 2elecionar os "alores )ue desejar somar./. (epois cli)ue no oto da 4utosoma e ele mostrará o resultado.
Veja mais um exemplo de 2oma 4gora "ocD deseja somar todos os "alores dispostos nesta planilha usando umanica fórmula, desta "eC "ocD terá )ue digitar a fórmula.
4 ; ( E
1 D /' 1' 1&/ 1' /& /' 1'% 1L /' /' $L #9#4 5 5SOMA@A-"/B'Q+*
Para faCer isto, só Gasta )ue "ocD digite o endereo inicial @e* desta+eB etamGm o endereo final e* desta+eB(esta forma, "ocD está somando todos os "alores numa nica fórmula, o )ue
chamamos de 2omar $atriCes.
4companhe mais um exemplo de 2oma.
(esta "eC "ocD deseja somar nmeros dispostos de maneira alternada, ou seja, emendereos diferentes. Veja o exemplo:
4 ; ( E1 S
8/15/2019 Formulas Excel Augusto
8/17
*
$ostra o "alor máximo de uma faixa de clulas.Exemplo: 2uponha )ue desejasse saGer )ual a maior idade de crianas em umataGela de dados. Veja a fórmula no exemplo aGaixo:
4 ;
1 3(4(E/ 1'% 1QL /'' %&Q $439 3(4(E: 5MGXIMO@A$-AB+
9nde:4/:4'F – refere-se ao endereo dos "alores onde "ocD deseja "er )ual o maior "alor. 0o caso a resposta seria %&. !aa como mostra o exemplo trocando apenas oendereo das clulas.
FUNÇ%O MHNIMO
$ostra o "alor mHnimo de uma faixa de clulas.Exemplo: 2uponha )ue desejasse saGer )ual o menor peso de crianas em umataGela de dados. Veja a fórmula no exemplo aGaixo:
4 ;1 PE29/ 1'% 1QL /'
' %&Q $E09 3(4(E: 5MHNIMO@A$-AB+
FUNÇ%O M"IA
;alcula a mdia de uma faixa de "alores.Exemplo: 2uponha )ue desejasse saGer )ual a mdia de idade numa taGela dedados aGaixo:
4 ;1 3(4(E/ 1'% 1QL /'' %&Q $N(34 3(4(E 5M"IA@A$-AB
FUNÇ%O "ATA "E =OJE
Esta fórmula insere a data automática em uma planilha. Veja o exemplo
4 ;1 (ata @U9EF
8/15/2019 Formulas Excel Augusto
9/17
/%
Esta fórmula digitada precisamente como estaW. VocD só precisa colocar o cursor no local onde deseja )ue fi)ue a data e digitar @U9EF e ela colocaráautomaticamente a data do sistema.
AL>UMAS CONSI"ERAÇÕES SO.RE "ATAS-
(atas so na "erdade nmeros decimais. 9 Excel assume como nmero 1 a data&1J&1J1&&. Para "er )ual numero corresponde a uma determinada data, digite amesma em uma clula e depois cli)ue no Goto epaado de mil.aes na Garra deGotBes ou use o menu !ormatar ;lulas e transforme a data em nmero.Por exemplo 1'J&'J/&&% corresponde ao nmero %+.+'Q.6sando os nmero o Excel pode resol"er cálculos en"ol"endo datas.
COMO "ESCO.RIR KUANTOS "IAS EXISTEM ENTRE "UAS "ATAS
N simples, Gasta digitar as datas nas clulas desejadas e depois suGtrair a maisrecente da mais "elha. Veja o exemplo:
4 ;1 (ata inicial 1&J&/J/&&1/ (ata !inal 1'J1&J/&&/% (ias @/-1
;aso o resultado seja apresentado em forma de data, Gasta con"erte-lo paranmero.
FUNÇ%O #ARA "ESCO.RIR O "IA "A SEMANA "E UMA "ATA
4tra"s da funo texto podemos descoGrir o dia da semana em )ue caiu umadeterminada data. Veja o exemplo
4 ;1 (ata 1&J&/J/&&1/ (ia da semana @#E=#91XYdddYF%
4cima o formato Zddd[ coloca o dia da semana com % letras seg, ter, )uaF,colocando o formato como Zdddd[ ele apresenta o dia completo segunda-feira,tera-feira...F
USAN"O A FUNÇ%O SE
4 funo 2E do Excel sempre utiliCada )uando o "alor de uma clula pode "ariar.
8/15/2019 Formulas Excel Augusto
10/17
= (2 % 6 79$; 6 7$9$;)
?rit'rio
$esultado se a?ompara!#oretorar
$esultado se a?ompara!#o retorar
1&
4 tarefa da funo 2E comparar o "alor da clula com um crHtrio estaGelecido eretornar dois resultados. 6m se a comparao for "erdadeira e outro se a comparaofor falsa.
Veja estas comparaBes:
'A/ \ * – compaa/o ,alsa/I% @ Q compaa/o 'edadeia/A*F I / \ 'AQ compaa/o'edadeia%
Estas comparaBes so Gem simples e conhecidas. 0o Excel funciona da mesmaforma, mas comparamos uma clula ou seja, seu contedoF com um determinado"alor
0o exemplo a seguir o aluno repro"ado caso sua mdia seja menor )ue +. 2endoassim + o critrio para apro"ao. Ento compara-se a mdia do aluno com +. 2e acomparao retornar "erdadeiro, este estará aproado caso contrário estaráreproado!
4 ; ( E
1 4luno 1T im. /T im $dia esultado
/ $aria L Q ' epro"ado
% oo Q +,' 4pro"ado
0ossa comparao ficaria assim para o 1T aluno:
$dia da $aria \ + compaa/o ,alsa% nt/o O344
0o momento a $aria tem mdia menor )ue +, mas o "alor da mdia pode "ariar nodecorrer dos Gimestres, faCendo com )ue a aluna seja apro"ada. 4 funo 2E trataento as duas situaBes. >uando a comparao for falsa e )uando for "erdadeira.
4 forma de montar a !uno a seguinte:
@2E ;omparao 6 esultado caso 3edadeio 6 esultado caso 7alsoF
4 funo 2E para este exemplo ficaria assim:
8/15/2019 Formulas Excel Augusto
11/17
= ( 2 %6 79$; 6 (2 @ 46 7$9$; 67Aoda 'e; ue usa um te9to na em ualue ,un/o o mesmo de'e 'i dentode aspas como o pala'a aprovado e reprovado. ?uando usa numeos ouendeeos de células (p% e9: 2 ) n/o .@ a necessidade do aspas%
CRIAN"O UMA FUNÇ%O SE COM#OSTA!
>uando o "alor da clula pode conter mais de dois resultados necessário criarmos a funo2E composta de mais outra.
0o exemplo a seguir o aluno )ue ti"er mdia menor )ue L está reproado o aluno
)ue ti"er mdia maior )ue + estará aproado, e o aluno )ue ti"er um mdia entre L e+ ficará de e?a*e! 0ote portanto )ue podemos ter % situaBes para o resultado dasnotas do aluno.
N necessário ento criarmos duas funBes 2E, para realiCar duas comparaBes,soGrando um ultima alternati"a para o retorno da resposta. 6sando o exemplo dataGela de notas acima terHamos:
0o exemplo acima a primeira comparao , sendo "erdadeira, retorna a respostaA#RO:A"O para o aluno, caso seja falsa, será realiCada uma no"a funo paracomparar no"amente a o "alor da clula, pois ainda no podemos afirmar )ue o alunoestá RE#RO:A"O ou de EXAME. 4 segunda comparao retorna A#RO:A"O casoa comparao de (/ ] L seja "erdadeira. E retorna EXAME caso a comparao sejafalsa, pois se (/ no nem maior )ue + 1^ comparaoF e nem menor )ue L sópode estar entre L e +, faCendo com )ue o aluno fi)ue de e?a*e!
8/15/2019 Formulas Excel Augusto
12/17
1/
FÓRMULA "O CONT!SE
4gora "ocD possui uma planilha onde tem o nome dos alunos e as suas mdias. E"ocD desejasse agora saGer )uantos alunos tiraram mdias maior e igual a . Vejao exemplo:
4 1 45609 $N(342/ oo +% $aria 1&L $árcio Q' (Gorah *
5CONT!SE@.$-.75PB
9nde:
/:'F – refere-se ao endereo das clulas onde "ocD desejar contar. utiliCa-se como parte da sintaxe para separarZ\@[ – refere-se a condio, ou seja, esta fórmula só irá contar as clulas )uecontDm "alores maiores ou igual a .2iga a sintaxe, suGstituindo apenas os endereos e a condio para contar.(epois das aspas "ocD digita a condio. Pode ser tamGm texto, independente detexto ou "alor, de"e-se colocar entre as aspas.Exemplo:@;90#.2E;/:;'X[4P9V4(9[F0este exemplo ele contará apenas as clulas )ue contDm a pala"ra 4pro"ado.
FÓRMULA "O SOMASE
2oma um inter"alo de clulas mediante a condio estaGelecida
Exemplo:
VocD gostaria de soma as faturas )ue foram pagas.Ento "ocD tem uma planilha onde na coluna 4 "ocD coloca o nome do cliente, nacoluna o "alor da fatura e na coluna ;, a situao se foi paga ou no.
VocD gostaria de somar somente as faturas )ue esti"essem pagas, assim "ocDsaGeria o )uanto já receGeu. 5ogo a fórmula seria a seguinte:
4 ;1 ;53E0#E V459 23#64789/ emol 1'& P<% #V 5ar /'&L $2 ;asa %'& P<' _tica 4"enida 1*&Q $arta /'& P<+ 4ndra 1& P<
Valor eceGido 5SOMASE@C$-CQ#>.$-.QB
9nde:@29$42E- o nome da fórmula
8/15/2019 Formulas Excel Augusto
13/17
1%
;/:;+ – refere-se ao endereo inicial e final de clulas onde "ocD digita a pala"raP
8/15/2019 Formulas Excel Augusto
14/17
ua plailCa deve Dar assim:
eleioe a 'lula E3 e li&ue o "ot#oormato de moedapara apliar o
ormato $F.ora vamos riar a u!#o para alular o valor dos uros:
1L
;li)ue na clula + e cli)ue no Goto cola ,un/o 2urgiráo seguinte )uadro:
2elecione a categoria !inanceira e depois a funo 3P
8/15/2019 Formulas Excel Augusto
15/17
1'
(igite os seguintes "alores nas caixas:#axa: BE PerHodo: 1 0per: B5 VP: -B! Veja o )ue significa cada item:Ta?a a taxa de juros por perHodo.
#erodo o perHodo cujos juros se deseja saGer e de"e estar no inter"alo entre1 e nper.Nper o nmero total de perHodos de pagamento em uma anuidade. :p o"alor presente, ou a )uantia total atual correspondente a uma srie depagamentos futuros.epare )ue logo aGaixo no )uadro será apresentado o resultado. cli)ue em 9`.9 resultado será de O Q&&,&&Este resultado o "alor dos juros do primeiro mDs. Para calcular o juros dos Lmeses, "ocD pode multiplicar este "alor por L editando a clula )ue contm afuno +F. ;li)ue na referida clula. epare a funo na Garra de fórmulas.;li)ue no fim da mesma e digite: *E% #ecle Enter para confirmar. Veja a figura a
seguir.
Pronto, o "alor da clula + agora de O /.L&&
FUNÇ%O #>TO
8/15/2019 Formulas Excel Augusto
16/17
1Q
etorna o pagamento periódico de uma anuidade de acordo com pagamentosconstantes e com uma taxa de juros constante.
2intaxe da funo
Pa9a a taxa de juros por perHodo.8pe o nmero total de pagamentos pelo emprstimo.3p o "alor presenteo "alor total presente de uma srie de pagamentosfuturos.3, o "alor futuro, ou o saldo de caixa, )ue "ocD deseja oGter depois do ltimopagamento. 2e "f for omitido, será considerado & o "alor futuro dedeterminado emprstimo, por exemplo, &F.>ipo o nmero & ou 1 e indica as datas de "encimento. 3ndi)ue 1 se o
pagamento for efetuado no inicio do perHodo e & se for efetuado no final doperHodo.
4 fórmula a seguir retorna o pagamento mensal por um emprstimo de O1.&&&a uma taxa anual de /&R )ue "ocD de"e pagar em 1/ meses:
P
8/15/2019 Formulas Excel Augusto
17/17
1+
2intaxe
#4=4nperXpgtoX"pX"fXtipoF
Exemplo
Para calcular a taxa de um emprstimo de )uatro anos de O*.&&& compagamentos mensais de O/&&:
#4=4L*X -/&&X *&&&F
FUNÇ%O :F
etorna o "alor futuro de um in"estimento de acordo com os pagamentosperiódicos e constantes e com uma taxa de juros constante.
2intaxe
V!taxaXnperXpgtoX"pXtipoF
Exemplo
2uponha )ue deseja aplicar um dinheiro na poupana e )ue esta rende jurosfixos todo o mDs. VocD acaGa de aGrir uma conta no Ganco e depositou de inicio
O 1&&&,&&. #odo inicio de mDs irá depositar O 1/&,&& durante 1* meses comrendimento mensal de 1R. >uanto oGterá no final do perHodo M Veja a fórmula aseguir.
@V!1RX1*X-1/&X-1&&&X1F
9 resultado será O %.'+%,L'