Apostila Excel Avançado 2009

7/17/2019 Apostila Excel Avançado 2009

http://slidepdf.com/reader/full/apostila-excel-avancado-2009 1/46

SENAC Apostila de Excel - Fórmulas Avançadas

SUMÁRIO

Exercícios de Recordação...................................................................................................... 4

peradores de C!lculos em "órmulas.....................................................................................#

Funç$es %erais.......................................................................................................................&

ARRE'................................................................................................................................... &

(N).........................................................................................................................................&

Funç$es Condicionais.............................................................................................................&

CN).SE................................................................................................................................ &SE...........................................................................................................................................*

E............................................................................................................................................. *

+..........................................................................................................................................,

SE Concatenado..................................................................................................................

S/ASE..............................................................................................................................

Funç$es para procurar um valor especí"ico em uma lista de valores...................................

0RC1..................................................................................................................................

0RC2.................................................................................................................................3

Funç$es "inanceiras e lóicas...............................................................................................4

0%)....................................................................................................................................4

10.........................................................................................................................................5

Funç$es de an!lises e estimativas "inanceiras.....................................................................5

Função Atinir /eta..............................................................................................................5

Relacionamento de planil6as................................................................................................#

S+7))A8...........................................................................................................................*

Su9total com "iltro................................................................................................................. *

1alidação de dados...............................................................................................................:

0roteer pastas de tra9al6os e planil6as..............................................................................:5

)a9ela 'in;mica....................................................................................................................:,

Formul!rio.............................................................................................................................4

/acro.................................................................................................................................... 43

Re"erencia 7i9lior!"ica........................................................................................................44

:




EXERCÍCIOS DE RECORDAÇÃO

EXERCÍCIO 01:

< '(%()E= FR/A)E= RES0N'A e SA81E no dis>uete a planil6a a9aixo de acordo com asorientaç$es a9aixo?OBSERVAÇÃO: Não diite os valores orientados a9aixo= os mesmos serão encontrados por meiode "órmula.

TOTAL DE PONTOS: Função S/AMÉDIA FINAL: Função /@'(A

CONCEITO: Função 0RC1RESULTADO: Função SE. Sa9endo >ue a mdia para aprovação &=.MAIOR NOTA GERAL: Função /B(/MENOR NOTA GERAL: Função /DN(/

4




EXERCÍCIO 02:

< '(%()E= FR/A)E= RES0N'A e SA81E no dis>uete a planil6a a9aixo de acordo com asorientaç$es a9aixo?OBSERVAÇÃO: Não diite os valores orientados a9aixo= os mesmos serão encontrados por meiode "órmula.

EXERCÍCIO 03:

< '(%()E= FR/A)E= RES0N'A e SA81E no dis>uete a planil6a a9aixo de acordo com asorientaç$es a9aixo?OBSERVAÇÃO: Não diite os valores orientados ao lado= os mesmos serão encontrados por

meio de "órmula.

5




OPERADORES DE CÁLCULOS EM FÓRMULAS

uatro tipos de operadores são sím9olos >ue identi"icam o tipo de c!lculo.0ara e"etuar operaç$es matem!ticas 9!sicas.

O!"#$%"!& #"'()*('+%&O!"#$%" A"'()*('+% S',-'.'+#$%

Adição

- Su9traçãoG /ultiplicaçãoH 'ivisão

I 0orcentaemJ Exponenciação

0ara comparar 3 valores e o9ter como resultado os valores lóicos de verdadeiro ou "alsos.

O!"#$%"!& $! +%)#"#/%O!"#$%" $! +%)#"#/% S',-'.'+#$%

K (ual aL /aior do >ue

M /enor do >ueLK /aior ou iual aMK /enor ou iual aML 'i"erente

+se o sím9olo para concatenar 3 se>OPncias de caracteres.

O!"#$%" $! +%-+#(!-#/% $! (!(%O!"#$%" $! (!(% S',-'.'+#$%

+ne dois valores para produQir um valor de texto contínuo

Com9ine intervalos de clulas para c!lculos com os seuintes operadores.

O!"#$%"!& $! "!.!"-+'#

#




O!"#$%" $! "!.!"-+'# S',-'.'+#$%? perador de intervalo= >ue produQ uma re"erPncia a todas as

clulas entre duas re"erPncias= incluindo as duas re"erPncias perador de união= >ue com9ina diversas re"erPncias em uma

re"erPncia.

FUNÇES GERAIS

ARREDONDAR 4 ARRED

Arredonda um nmero at uma >uantidade especi"icada de díitos.

S'-(#!: ARRED5-6)7-6)8$9,'(%&N;) o nmero >ue se deseTa arredondar.

N;)8$9,'(%&

Especi"ica o nmero de díitos para o >ual vocP deseTa arredondar Num.L Num ser! arredondado para o nmero especi"icado de casas decimaisK Num ser! arredondado para o inteiro mais próximoM Num ser! arredondado para a es>uerda da vírula decimal

Exemplos? ARRE'U3=5 V iual a 3=3 ARRE'U3=4, V iual a 3= ARRE'U-=4&5 3V iual a -=4* ARRE'U3=5 -V iual a 3

ARREDONDAR PARA O INTEIRO MAIS PRÓXIMO 4 INT

Arredonda um nmero para 9aixo at o nmero inteiro mais próximo.

S'-(#! : INT5-6)N;) o nmero real >ue se deseTa arredondar para 9aixo at um inteiro.

Exemplos? (N)U*=,V iual a * (N)U-*=,V iual a <,

FUNÇES CONDICIONAIS

CONT<SE: para contar o nmero de ocorrPncias de uma se>OPncia de texto ou de um nmerodentro de um intervalo de clulas.SE: para >ue uma "órmula retorne um valor entre duas opç$es de valores com 9ase em umacondição.SOMASE: para calcular uma soma 9aseada em uma se>OPncia de caracteres de texto ou em umnmero contido em um intervalo.

CONDICIONAL 4 CONT<SE

&




Calcula o nmero de clulas não vaQias em um intervalo >ue corresponde a determinadoscritrios.S'-(#!: CONT<SE 5'-(!"=#>%7+"'(*"'%&I-(!"=#>%

o intervalo de clulas no >ual se deseTa contar clulas não vaQias.

C"'(*"'%& o critrio na "orma de um nmero= expressão ou texto >ue de"ine >uais clulasserão contadas.

Exemplos?Supon6amos >ue A?A4 conten6a WmaçãsW= WlaranTasW= WpPrasW= WmaçãsW= respectivamente.CN).SE UA?A4WmaçãsWV iual a 3Supon6amos >ue 7:?7# conten6a :3= 54= &5= *#= respectivamente.CN).SEU'?'4WL55WV iual a 3

A 7 C ' E F /açãs :33 8aranTas 54: 0Pras &54 /açãs *#5 KCN).SEUA?A4X/açãsXV KCN).SEU'?'4WL55WV

CONDICIONAL 4 SERetorna um valor se uma condição >ue vocP especi"icou avaliar como 1ER'A'E(R e um

outro valor se "or avaliado como FA8S.S'-(#!: SE5(!&(!8>?,'+%7=#>%"8&!8=!"$#$!'"%7=#>%"8&!8.#>&%T!&(!8>?,'+% >ual>uer valor ou expressão >ue possa ser avaliado como

1ER'A'E(R ou FA8S.V#>%"8&!8=!"$#$!'"% o valor retornado se testeYlóico "or 1ER'A'E(R.

1alorYseYverdadeiro pode ser outra "órmula.V#>%"8&!8.#>&% o valor retornado se testeYlóico "or FA8S. 1alorYseY"also pode ser

outra "órmula.

• At sete "unç$es SE podem ser anin6adas ou concatenadas como arumentosvalorYseYverdadeiro e valorYseY"also para construir testes mais ela9orados.

• uando os arumentos valorYseYverdadeiro e valorYseY"also são avaliados= SE retorna ovalor >ue "oi retornado por estas instruç$es.

Exemplos? A mdia para aprovação de um aluno maior ou iual a &=. A clula da "órmula rece9e Aprovado=caso contr!rio Reprovado. KSEU(3LK&WAprovadoWWReprovadoWV

A 7 C ' E F % 2 ( NZ Nome Z 7im 3Z 7im :Z 7im 4Z 7im

)otal de0ontos

/diaFinal

Situação

3 Ana 0ereira Silva *=4 &=: *= #=& :=5 &=#: Aprovado

: 3 Ant[nio liveira 5=3 5= 4=* 5= 3= 5= Reprovado

CONDICIONAL 4 E

*




Retornar! 1ER'A'E(R se todos os arumentos "orem verdadeiros retornar! FA8S se um oumais arumentos "orem "alsos.

S'-(#!: EUlóicolóico3 ...V 8óico lóico3... < São de a : condiç$es >ue vocP deseTatestar e >ue podem ser 1ER'A'E(R ou FA8S.

Coment!rios

s arumentos devem ser avaliados para valores lóicos= como 1ER'A'E(R ou FA8S= oudevem ser matriQes ou re"erPncias >ue contPm valores lóicos.Se um arumento de uma matriQ ou re"erPncia contiver texto ou clulas vaQias= esses valoresserão inorados.

A 7 C ' E F Alunos (dade Altura Situação3 \oão 33 =&3 KEU73L5C3LK=&V: 0edro 4 =#* FA8S4 8aura * =* 1ER'A'E(R

No exemplo acima= o aluno deve possuir os dois critrios? (dade UL 5V e Altura ULK=&V para >ueseTa apto para ser verdadeiro.

CONDICIONAL 4 OU

Retorna 1ER'A'E(R se >ual>uer arumento "or 1ER'A'E(R retorna FA8S se todos osarumentos "orem FA8SS.

S'-(#!+Ulóicolóico3...V 8óicolóico3=... são de uma a : condiç$es >ue vocP deseTa testar e>ue podem resultar em 1ER'A'E(R ou FA8S.

Coment!rios

s arumentos devem ser avaliados como valores lóicos= como 1ER'A'E(R ou FA8S= ouem matriQes ou re"erPncias >ue conten6am valores lóicos.Se um arumento de uma matriQ ou re"erPncia contiver texto ou clulas vaQias= esses valoresserão inorados.Se o intervalo especi"icado não contiver valores lóicos= + retornar! o valor de erro ]1A8R .̂1ocP pode usar uma "órmula de matriQ + para veri"icar se um valor ocorre em uma matriQ. 0arainserir uma "órmula de matriQ= pressione C)R8S2(F)EN)ER.

A 7 C ' E F Alunos (dade Altura Situação3 \oão 33 =&3 K+U73L5C3LK=&V: 0edro 4 =#* FA8S

4 8aura * =* 1ER'A'E(R

,




No exemplo acima o aluno dever! possuir um dos dois critrios? (dade UL 5V e Altura ULK=&Vpara >ue seTa apto para ser verdadeiro.0erce9eu a di"erença entre as "unç$es E e +_

NOTA: As "unção E ou + podem ser utiliQada Tuntas com a "unção SE para o 1alor 1erdadeiro eo 1alor Falso possa ser utiliQado. 1eTa o exemplo a9aixo para a "unção E?

A 7 C ' E F Alunos (dade Altura Situação3 \oão 33 =&3 KSEUEU73L5C3LK=&V ̀AptoXX(naptoXV: 0edro 4 =#* (napto4 8aura * =* Apto

No exemplo acima= o aluno deve possuir os dois critrios? (dade UL 5V e Altura ULK=&V para >ue

seTa apto ou inapto para ser apto.

0ara a "unção +?

A 7 C ' E F Alunos (dade Altura Situação3 \oão 33 =&3 KSEU+U73L5C3LK=&V ̀AptoXX(naptoXV: )iao # =#* Apto4 8aura * =* Apto

No exemplo acima o aluno dever! possuir um dos dois critrios? (dade UL 5V e Altura ULK=&Vpara >ue seTa apto.

CONDICIONAL 4 SE CONCATENADO

@ a "unção SE sendo utiliQada com mais de uma condição para 1erdadeiro ou Falso.

Exemplo?

0ara o primeiro aluno= na clula RES+8)A' a "órmula a seuinte?

KSEU%5KWExcelenteWSEU%5LK,WtimoWSEU%5LK*W7omWSEU%5L&WReularWW(nsu"icienteWVVVV

CONDICIONAL 4 SOMASE




Adiciona as clulas especi"icadas por um determinado critrio.

S'-(#!: SOMASE5'-(!"=#>%7+"'(*"'%&7'-(!"=#>%8&%)#I-(!"=#>% o intervalo de clulas >ue se deseTa calcular.

C"'(*"'%&são os critrios na "orma de um nmero= expressão ou texto= >ue de"ine >uaisclulas serão adicionadas.

I-(!"=#>%8&%)#

são as clulas >ue serão realmente somadas. As clulas em intervaloYsomasão somadas somente se suas clulas correspondentes em intervalocoincidirem com os critrios estipulados. Se intervaloYsoma "or omitido= asclulas em intervalo serão somadas.

Exemplo?Supon6amos >ue= de acordo com a planil6a a9aixo= vocP deseTe sa9er o resumo de cada item da

coluna < Exemplo? uanto "oi vendido somente na reião Norte_ u o resumo mensal de marçoou o total de vendas e"etuadas por 0aulo_0ara o resumo da Reião= seria? KS/ASEU74?733WnorteW'4?'33VResumo /ensal? KS/ASEUA4?A33WmarW'4?'33V

A 7 C ' E F %

ADUBOS E FERTILI@ANTES3:4 M& R!,'% V!-$!$%" ;#-('$#$! R!&;)% %" R!,'%5 Tan Norte 0aulo 55 Norte 3.,5# Tan Nordeste 0edro *3 Nordeste 3&.*5*

& Tan Centro-oeste biliam *,35 Centro-este :.,&** Tan Sudeste \aime &3 Sudeste :.5,,, Tan Sul Srio #:*, Sul 3&.&** "ev Norte 0aulo 55& "ev Nordeste 0aulo #4# R!&;)% M!-&#>3 "ev Centro-este Srio &*:5 \an :#.335: "ev Sudeste biliam ,: Fev :&.#*4 "ev Sul \aime &,54 /ar :,.#45 mar Norte 0aulo 4,44 A9r 3#.,5# mar Nordeste 0edro #5,*& mar Centro-este Srio &, R!&;)% %" =!-$!$%" *

mar Sudeste \aime &544 0aulo 33.433, mar Sul biliam :445 biliam :.#&3 a9r Norte Ricardo 4,44 \aime :.3533 a9r Nordeste Ricardo #5,* Srio 3.:::33 a9r Centro-este Santos &, Ricardo .5433: a9r Sudeste \aime &544 0edro 4.&,,34 Santos &.,35 T%(#> !) , :,.3*

PROCURAR UM VALOR ESPECÍFICO EM UMA LISTA DE VALORES

PROCV




8ocaliQa um valor na primeira coluna es>uerda de uma ta9ela e retorna um valor namesma lin6a de uma coluna especi"icada na ta9ela.

S'-(#!: PROCV5=#>%"8"%+;"#$%7)#("'8(#!>#7-6)89-$'+!8+%>;-#7"%+;"#"8'-(!"=#>%

=#>%"8"%+;"#$% o valor a ser localiQado na primeira coluna da matriQ. 1alorYprocuradopode ser um valor= uma re"erPncia ou uma se>OPncia de caracteres de texto.

)#("'8(#!>#

a ta9ela de in"ormaç$es em >ue os dados são procurados. +se umare"erPncia para um intervalo ou nome de intervalo= tal como 7anco de dadosou 8ista.• Se procurarYintervalo "or 1ER'A'E(R= os valores na primeira coluna

de matriQYta9ela deverão ser colocados em ordem ascendente? ...= -3=-= = = 3= ... = A-= FA8S= 1ER'A'E(R caso contr!rio= 0RC1 podenão retornar o valor correto. Se procurarYintervalo "or FA8S=matriQYta9ela não precisar! ser ordenada.

• 1ocP pode colocar os valores em ordem ascendente escol6endo o

comando C>#&&'.'+#" no menu D#$%& e selecionando C"!&+!-(!.• s valores na primeira coluna de matriQYta9ela podem ser texto=

nmeros ou valores lóicos.• )extos em maisculas e minsculas são e>uivalentes.

-6)89-$'+!8+%>;-#

o nmero da coluna em matriQYta9ela a partir do >ual o valor correspondente deve ser retornado. +m nmYíndiceYcoluna de retornar! ovalor na primeira coluna em matriQYta9ela um nmYíndiceYcoluna de 3retornar! o valor na seunda coluna em matriQYta9ela= e assim por diante.Se nmYíndiceYcoluna "or menor do >ue = 0RC1 retornar! o valor de erro]1A8R^ se nmYíndiceYcoluna "or maior do >ue o nmero de colunas emmatriQYta9ela= 0RC1 retornar! o valor de erro ]REF^.

"%+;"#"8'-(!"=#>%

um valor lóico >ue especi"ica se vocP >uer >ue 0RC1 encontre a

correspondPncia exata ou uma correspondPncia aproximada. Se1ER'A'E(R ou omitida= uma correspondPncia aproximada retornadaem outras palavras= se uma correspondPncia exata não "or encontrada= ovalor maior mais próximo >ue menor >ue o valorYprocurado retornado.Se FA8S= 0RC1 encontrar! uma correspondPncia exata. Se nen6umacorrespondPncia "or encontrada= o valor de erro ]NH' retornado.

Exemplos? Na planil6a a9aixo= onde as clulas \#?3 denominado (ntervalo?

3




PROC

8ocaliQa um valor especí"ico na lin6a superior de uma ta9ela ou matriQ de valores e retornao valor na mesma coluna de uma lin6a especi"icada na ta9ela ou matriQ.

S'-(#!: PROC5=#>%"8"%+;"#$%7)#("'8(#!>#7-6)89-$'+!8>'-7"%+;"#"8'-(!"=#>%

:




Exemplo?

Note a di"erença entre a "unção 0RC1 e a 0RC2.

4




FUNÇES FINANCEIRAS E LÓGICAS

CALCULAR O VALOR DE UMA PRESTAÇÃO INICIAL PGTO

Retorna o paamento periódico de umaanuidade de acordo com paamentosconstantes e com uma taxa de Turosconstante.

S'-(#!: PGTO5(##7-!"7=7=.7('%• paamento retornado por 0%) inclui o principal e os Turos e não inclui taxas= paamentos

e reserva ou tari"as= s veQes associados a emprstimos.

Exemplos?Supon6a >ue vocP >ueira economiQar Rf 5.= em * anos economiQando uma

mesma >uantia todo mPs= vocP pode utiliQar 0%) para determinar >uanto vocP deveeconomiQar. Se considerar >ue ser! capaQ de o9ter #I de Turos em suas economias= vocP poder!

usar 0%) para determinar o >uanto ter! >ue economiQar durante o mPs?

0%)U#IH3 *G3 V 5V iual a -f3,=*Se >uiser colocar f3,=* em uma poupança a #I todo o mPs durante * anos= vocP ter! f5..

Calcule a planil6a a9aixo para sa9er >ual o valor mensal desta prestação?

A 7 C ' E F 0roduto? )elevisão3 1alor )elevisão? &5=: \uro /ensal? 3I4 0restação? 35 1alor da 0restação? K-0%)U7:7473V

5




RETORNA O VALOR PRESENTE DE UM INVESTIMENTO VP

valor presente o valor total correspondente ao valor atual de uma srie de paamentos"uturos. 0or exemplo= >uando vocP pede din6eiro emprestado= o valor do emprstimo o valor presente para >uem empresta.

S'-(#!: VP5(##7-!"7,(%7=.7('%

T##

a taxa de Turos por período. 0or exemplo= se vocP o9tiver um emprstimo para um carrocom uma taxa de Turos de I ao ano e "iQer paamentos mensais= a sua taxa de Turosmensal ser! IH3= ou =*:I. 1ocP deve inserir IH3= ou =*:I= ou =*:= na"órmula como taxa.

N!" o nmero total de períodos de paamento de uma anuidade. 0or exemplo= se vocPo9tiver um emprstimo de >uatro anos e "iQer paamentos mensais= o emprstimo ter!4G3 Uou 4*V períodos. 1ocP deve inserir 4* na "órmula para nper.

P,(%

o paamento "eito a cada período e não pode mudar durante a viPncia da anuidade.%eralmente= pto inclui o principal e os Turos= e não 6! outras tari"as ou taxas. 0or exemplo= os paamentos mensais por um emprstimo para o carro de Rf . de>uatro anos a 3I são Rf 3#:=::. 1ocP deve inserir -3#:=:: na "órmula como pto. Septo "or omitido= vocP dever! incluir o arumento v".

V.

o valor "uturo= ou um saldo de caixa= >ue vocP deseTa o9ter depois do ltimo paamento.Se v" "or omitido= ser! considerado Uo valor "uturo de determinado emprstimo= por exemplo= V. 0or exemplo= se >uiser economiQar Rf 5. para paar um proTetoespecial em * anos= então Rf 5. o valor "uturo. 1ocP pode então calcular a taxa de Turos e determinar >uanto dever! economiQar a cada mPs. Se v" "or omitido= vocP dever!incluir o arumento pto.

T'% o nmero ou e indica as datas de vencimento.

Exemplos?Supon6amos >ue vocP realiQou um emprstimo no valor de Rf *= em * meses= com Turos

de 3=5I ao mPs. Calcule >ual ser! o valor total pao. A 7 C ' E F

Emprstimo? *=3 0eríodo? *: \uro /ensal? 3=5I4 1alor )otal? K-10U7:737V

USAR FUNÇES ANÁLISES E ESTIMATIVAS FINANCEIRAS

. No menu Ferramentas= cli>ue em Suplementos.Se o suplemento >ue vocP deseTa usar não estiver listado na caixa Suplementos disponíveis=cli>ue em 0rocurar e= em seuida= localiQe o suplemento. Se o suplemento não estiver instaladoem seu computador= vocP poder! instal!-lo.

#




3. Na caixa Suplementos disponíveis= mar>ue a caixa de seleção posicionada ao lado dosuplemento >ue vocP deseTa carrear.

FUNÇÃO ATINGIR META

@ um mtodo para encontrar o valor de entrada >ue uma "órmula precisa para produQir umdeterminado resultado. Ao executar atinir metas o Excel varia o valor contido em uma clula >uevocP especi"ica= at >ue uma "órmula dependente da>uela clula produQa o resultado deseTado.+se o comando atinir metas >uando vocP deseTar localiQar um valor especí"ico para umadeterminada clula aTustando o valor de apenas uma outra clula. 0or exemplo= atinir a meta1alor da 0restação de maneira >ue= >ual seria o valor da televisão= se a min6a condição depaamento da prestação "osse de apenas Rf #*=_

A 7 C ' E F

0roduto? )elevisão3 1alor )elevisão? &5=: \uro /ensal? 3I4 0restação? 35 1alor da 0restação? Rf &=,3

1! at o menu Ferramentas H Atinir /eta= aparecer! aseuinte caixa= onde?

1 D!.'-'" +*>;>#: a clula onde ser! o valor proposto= nocaso= onde ser! o Rf #*= >ue posso paar de prestação.2 4 P#"# V#>%": o valor deseTado >ue a clula de"inida ir!tornar-se.3 4 A>(!"-#-$% +*>;>#: a clula >ue ser! modi"icada=nesse caso ? >ual ser! o valor da televisão para >ue eupaue apenas Rf #*= mensalmente_

RELACIONAMENTO DE PLANILAS

@ a maneira pela >ual no Excel vocP relaciona= lia= une duas ou mais planil6as entre si= demaneira >ue >uando uma alterada= todas relacionadas a ela so"rem alteração.

&




1eTa na "iura acima >ue 6! * planil6as nesta pasta de tra9al6o e uma delas RES+/.Na planil6a RES+/ os dados da planil6a C+RSS= >ue vamos c6am!-la de planil6a matriQ="oram transportados e "icaram relacionadas nela. 1eTa como "icou a "órmula.

C%)% .#!" ;) "!>#+'%-#)!-(% -;)# >#-'>#

1H MANEIRA:• 1eri"icar na planil6a matriQ >ual o endereço da clula >ue ser! relacionado Uclula C3V• (r at a planil6a de destino U0lanil6a RES+/V• Na planil6a RES+/= diitar o sinal de iualdade na clula >ue ser! exi9ido o valor= diitar o

nome da planil6a e con"irmar o nome. A con"irmação "eita com o sím9olo de exclamação epor "inal o endereço da clula= resumindo= a "órmula "icar! assim? KC+RSS^C3

2H MANEIRA:• Na planil6a RES+/= diitar o sinal de iualdade• Clicar na planil6a matriQ UC+RSSV e clicar na clula >ue se deseTa relacionar

• 0ressionar a tecla EN)ER para >ue volte para a planil6a RES+/ "aQendo o relacionamento.

*




NOTA: Claro >ue a 3g maneira mais "!cil e pr!tica= mas com a primeira maneira vocP conseueentender o por>uP da "órmula e como corriir em caso de erros.

SUBTOTAL

M%&("#" %& S;(%(#'& +%) F'>("%

Retorna um su9total em uma lista ou em um 9anco de dados. @ eralmente mais "!cil criar uma lista com su9totais usando o comando Su9totais Umenu 'adosV. Assim >ue a lista desu9totais "or criada= vocP poder! modi"ic!-la editando a "unção S+7))A8.

S'-(#!: SUBTOTAL5-6)8.;-/%7"!.17"!.27<<<N;)8.;-/% @ o nmero de a >ue especi"ica >ual "unção usar no c!lculo de su9totais

dentro de uma lista."!.17"!.27<<< são intervalos ou re"erPncias de a 3, para os >uais vocP deseTa o su9total.

R!.!"-+'# F;-/% R!.!"-+'# F;-/% R!.!"-+'# F;-/% /dia 5 /ínimo , Soma3 Cont.Num # /ult 1ar : Cont.1alores & 'esvpad 1arp4 /!ximo * 'esvpadp

• A "unção S+7))A8 vai inorar >uais>uer lin6as ocultas >ue resultem de uma lista "iltrada.(sso importante >uando vocP deseTa o9ter o su9total apenas dos dados visíveis >ue resultamde uma lista "iltrada por vocP.

EXEMPLO DE SUBTOTAL COM FILTRO

,




C#"#+(!"9&('+#& $! ;)# B#&! $! D#$%&

Neste capítulo ser! demonstrado como "aQer uso do recurso de 9ase de dados do Excel. +ma9ase de dados um conTunto de in"ormaç$es arupadas e oraniQadas so9 um critriopreesta9elecido= cuTos dados poderão ser classi"icados= extraídos= alterados ou apaados. 'esta"orma= ser! possível manipular reistros= tra9al6ar com "unç$es especí"icas para 9anco de dadose "aQer uso de "iltros.0ara "aQer uso de uma estrutura de 9ase de dados no /icroso"t Excel= ser! necess!rio considerar trPs aspectos importantes?

- +ma 9ase de dados ser! uma ta9ela retanular.3 - As colunas dessa ta9ela serão consideradas como os campos de in"ormaç$es.: - As lin6as serão consideradas os reistros de uma ta9ela.

)odo 9anco= ou 9ase de dados em /icroso"t Excel= ter! de ser de"inido no "ormato de uma ta9elaretanular com as colunas indicando os campos em >ue cada campo UcolunaV dever! possuir umnome U1endedor= departamento= 'ados 1enda= 0roduto e 1alorV indicando seu contedo e aslin6as indicando os reistros em >ue cada reistro Ulin6aV dever! conter uma in"ormaçãocorrespondente ao nome do campo.s nomes dos campos deverão ser de"inidos sempre como rótulos. Caso vocP deseTe colocar onome de campo como nmero= não se es>ueça de diitar antes do nmero um pre"ixo de rótulo?apóstro"o UhV.\amais utiliQe dois campos com o mesmo nome. Caso vocP ten6a uma necessidade nessesentido= utiliQe nomes seriados= como? FONE1= FONE2= FONE3= VALOR1= VALOR2= VALOR3 eassim por diante.Não utiliQe tam9m nomes de campos >ue se pareçam com endereços de clulas= como? A1=

AX3= IVJ000= pois isto pode erar con"usão na interpretação dos dados >ue vocP provavelmentee"etuar! posteriormente. Adinste seue a relação completa dos reistros >ue serão utiliQados e por sua veQ deverão ser diitados como apresentados. 8em9rando >ue os rótulos? V!-$!$%" = D!#"(#)!-(%= D#(#V!-$#= P"%$;(% e V#>%" deverão ser diitados respectivamente nas clulas A2= B2= C2= D2 e E2?.

3




T#!>#1

Após ter diitado as in"ormaç$es para a 9ase de dados= rave-a com o nome Relatório de1endas. 0rocure deixar visualmente sua planil6a semel6ante "iura a9aixo.

R!>#(?"'%& $! ;)# B#&! $! D#$%&

Existem umas "ormas de e"etuar pes>uisas em uma 9ase de dados por meio da "iltraem dealumas condiç$es. 0ara "aQer uso deste recurso necess!rio estar com a 9ase de dadosselecionada. 0ortanto= selecione a "aixa de clulas A2:E2K e em seuida execute o comando do/enu? D#$%&HF'>("#" HA;(%F'>("%<

Ser! então apresentado ao lado direito do nomede cada campo UcolunaV um 9otão do tipo $"%$%-= indicado na "iura acima.0ara desliar a apresentação dos 9ot$es executenovamente o comando? D#$%&F'>("#"A;(%F'>("%< A partir deste momento= o recurso de "iltraempode ser utiliQado. 'P um cli>ue so9re o 9otãodrop-don do campo D!#"(#)!-(%= indicado na"iura a9aixo.

3




Figura – Apresentação do botão drop-down

no campo Departamento.

Selecione o departamento Ó('+# e o9serve a"orma de extração de dados >ue a planil6amostra. A Fiura mostra o resultado destaseleção.

0ara retornar a tela para a "orma anterior= cli>uenovamente o 9otão drop-don do campoD!#"(#)!-(% e selecione a opção UT;$%V.

0ara mais um teste= selecione somente osreistros do vendedor M#"'# I#!> >uevendeu os produtos Ó+;>%& $! G"#;. AFiura mostra o resultado desta pes>uisa.'epois retorne os campos V!-$!$%" eP"%$;(% para a de"inição UT;$%V.0erce9a >ue= desta "orma= possível o9ter resposta para a perunta? uais vendedores

venderam Micros e também Disquetes? Selecione no 9otãodrop-don do campo P"%$;(%= a opção UP!"&%-#>'#"<<<V= ser!apresentada a Caixa de 'i!loo P!"&%-#>'#" A;(%.'>("%= como

mostra ao lado.

Figura – Apresentação do resultado da pesquisa com dois campos

Figura – Caixa de diálogo Personaliar Auto!iltro.

Neste momento= diite= no primeiro campo da !rea de seleção P"%$;(%= o nome M'+"% 5utiliQe osinal de asterisco para considerar os demais caracteresV= selecione a condição 9oleana O; e

33




depois proceda ao preenc6imento do seundo campo diitando o nome D'&;!(!= veri"i>ue se acondição de pes>uisa * ',;#> # est! ativada. A "iura mostra como dever! "icar a tela. Cli>ue O.

Figura – Caixa de Diálogo Personaliar

AutoFiltro com os dados a serem pesquisados.

A "iura mostra o resultado desta pes>uisa.

Figura –Caixa de diálogo Personaliar AutoFiltro com os dados a serem

pesquisados" usando o operador boleano #.

1eTa tam9m a Fiura

E("#(%& $! ;)# B#&! $! D#$%&

Aora= a pes>uisa ser! e"etuada com a "inalidade de extrair os dados para uma outra !rea daplanil6a. 0ara isso= presico ser criada uma !rea de critrio= >ue dever! possuir os mesmos

campos da lista.0ara isso= selecione a "aixa de clulas A2:E2K e execute o comando do menu? E$'(#"C%'#" =depois posicione o cursor na clula G3 e execute o comando? E$'(#"C%>#" .

0osicione o cursor na clula G2 e diite o título Brea de Critrio= como se mostra na Fiura.@ preciso de"inir a !rea >ue rece9er! a extração dos dados= então selecione novamente a "aixa declulas A2:E2= execute o comando? E$'(#"C%'#"<E posicione o cursor na clula G10 executando o comando? E$'(#"C%>#"<0osicione em seuida o cursor na clula GQ e diite Á"!# $! E("#/%. A Fiura mostra comodever! estar a aparPncia da tela.

3:




Figura – Apresentação da de!inição da área de extração.

Estando de"inidas as duas !reas? a de critrio e a de extração= execute o comando? 'adosHFiltrarHFiltro avançado...

Ser! então apresentada a Caixa de 'i!loo Filtro avançado UFiuraV= indicando a selação !rea da9ase de dados. Neste momento= selecione a opção? Copiar para outro local= no campo?(ntervalo de critrios diite o endereço da !rea de critrio= no caso? f%f:?ff4 Uou selecionea re"erida !rea com o mouseV= e para o campo Copiar para= diite o endereço da !rea deextração? f%f?ff Uou selecione a re"erida !rea com o mouseV= em seuida cli>ue .

As Fiuras acima mostram respectivamente a Caixa de 'i!loo preenc6ida e seu resultado.

34

Figura – Apresentação da

Caixa de Diálogo !iltro

A$ançado

Figura – apresentação da

Caixa de Diálogo !iltro

a$ançado com opç%es de

crit&rio e extração.




Figura – Apresentação do resultado da extração

0erce9a duas coisas? a primeira= para a !rea de critrio "oram de"inidas duas lin6as= sendo umacom o nome dos campos e outra para a in"ormação dos dados a serem pes>uisados.

como esta lin6a est! em 9ranco= a extração ocorreu para todos os reistros. Supondo >ue vocP>ueira pes>uisar todas as ocorrPncias de vendas e"etuadas pelo vendedor André Luiz, diite naclula G o rótulo A-$"* L;' indicado na "iura= em seuida execute o comando?D#$%&F'>("#"F'>("% #=#-/#$%<<<

Selecione a opção C%'#" #"# %;("% >%+#> e cli>ue O< A Fiura mostra o resultado destapes>uisa.

Figura – Apresentação do r'tulo a ser pesquisado.

Este tipo de pes>uisa tam9m aceita os conceitos dos operadores 9ooleanos E ! OU. 0araexecutar o conceito do operador `EX= os dados devem ser diitados na mesma lin6a. 'iite naclula o rótulo M'+"% con"orme mostra a Fiura. em seuida execute o comando?D#$%&F'>("#"F'>("% #=#-/#$%<<<

Selecione C%'#" #"# %;("% >%+#> e cli>ue O. A Fiura mostra o resultado deste tipo depes>uisa.

35




0ara executar pes>uisa do tipo OU= por exemplo= listar os produtos vendidos por Andr 8uiQ ou\os Auusto= posicione o cursor na clula GK: e diite o rótulo %&* A;,;&(%= não es>ueça delimpar a clula : A .',;"# )%&("# +%)% $!=!" .'+#" # (!># $% M'+"%&%.( E+!>< $!%'&!!+;(! % +%)#-$%: D#$%&F'>("#"F'>("% #=#-/#$%

Selecione C%'#" #"# %;("% >%+#> para o I-(!"=#>% $! +"'(*"'%&= selecione a "aixa de clulasWGW3:WWK= cli>ue O< A Fiura mostra o resultado deste tipo de pes>uisa.

Figura – Apresentação dos r'tulos a serem pesquisados como condição (#)

Figura – Apresentação do resultado da extração ap's a pesquisa do $endedor Andr& *ui e +os& augusto – Condição ,.

Obs.: Quando se desejar usar condições do tipo ‘OU”, é necessário que você estabeleça aislin!as para alternar entre os eleentos que ser"o pesquisados.

Note >ue com o uso de "iltros avançados= ainda não exempli"icado como tra9al6ar com os camposnumricos Uvalores e datasV= para >ue o tra9al6o possa ocorrer de "orma e"iciente= necess!riocriar um campo suplementar denominado +#)% +#>+;>#$% %; critrio calculado= >ue poder! ser usado em apoio a >ual>uer outro campo numrico da sua lista. No exemplo apresentado= estecampo servir! para selecionar in"ormaç$es dos campos V#>%" ! D#$%& V!-$#< As in"ormaç$es selecionadas poderão ser extraídas usando-se o conceito de K Uiual aV= L Umaior >ueV= M Umenor >ueV= LK Umaior ou iual aV= MK Umenor ou iual aV= ML Udi"erente deV e tam9m

3#




possível cercar "aixas de valores= como? listar todos os valores entre 5 e , listar todas asvendas e"etuadas em utu9ro e assim por diante.0ara "aQer um teste desta possi9ilidade= posicione o cursor na clula L3 e diite o rótuloC#>+;>#$%= indicando na Fiura=. Se deseTar= copie par esta clula o "ormato das clulas >ueestão do lado es>uerdo.

Figura – Apresentação da de!inição do campo calculado.

Estando com o novo campo de"inido= 9asta em seuida esta9elecer as "órmulas lóicas >ue serãoutiliQadas para a avaliação dos critrios de pes>uisa. >uando uma "órmula lóica esta9elcida= esta retorna como resultado a indicação 1ER'A'E(R ou FA8S e dever!sempre ser re"erencial ao primeiro reistro da lista não importando a condição a ser utiliQada.Não se es>ueça de limpar as clulas %4? e %5.

0or exemplo= imaine >ue vocP necessita extrair todos os reistros cuTas vendas ten6am sidosuperiores a 5. 0osicione o cursor na clula L: e diite a "órmula lóica? E3Y000Ser! o9tido como retorno o valor FALSO= indicando >ue o primeiro reistro da lista est! "ora docritrio esta9elecido. 7asta aora e"etuar a extração para o9ter todos os reistros >ue satis"açama condição esta9elecida no campo calculado. Execute o comando? D#$%&F'>("#"F'>("%#=#-/#$%<<<Selecione a opção C%'#" #"# %;("% >%+#> != no campo I-(!"=#>% $! +"'(*"'%&? diite o endereçof%f:?f8f4 Ueste endereço considera o campo calculado como parte do critrioV= cli>ue O. AFiura mostra o resultado da extração dentro do critrio esta9elecido= 9em como a apresentaçãoda "órmula lóica com seu valor de retorno.

3&




Figura – Apresentação do resultado da extração e a apresentação da !'rmula l'gica.

Caso vocP >ueira e"etuar a extração tomando por 9ase "aixa de valores= poder! utiliQar as "unç$esKE e KOU. 0or exemplo= extrair todas as vendas entre , e 5. 0osicione o cursor na clula L?e diite a "órmula? E 5E3YQ07E3ZK00Em seuida execute? D#$%&F'>("#"F'>("% #=#-/#$%<<<

Selecione a opção C%'#" #"# %;("% >%+#> e cli>ue O. a "iura mostra o resultado deste critriode extração.

Figura – Apresentação do resultado da extração com duas condiç%es tipo (#)

1eTa um exemplo com a "unção OU. Ser! necess!rio o9ter a extração das vendas >ue "ormain"eriores a 3 e superiores a &= inclusive. 0osicione o cursor na clula L? e diite a "órmula?OU5E3Z2007E3Y[000Em seuida execute o comando? D#$%&F'>("#"F'>("% #=#-/#$%<<<

3*




Figura – Apresentação do resultado da extração com duas condiç%es (,).

Com relação s extraç$es a serem executadas com campos >ue possuam datas= ser! necess!rioutiliQar "unç$es >ue manipulem este tipo de dados= como? DIA M\S ANO ! DATA< 0or exemplo= extair todos os reistros de vendas e"etuadas no mPs de Tul6o de um ano >ual>uer.0osicione o cursor na clula L? e diite a "órmula lóica? K/Ps UC3V K &

Execute? D#$%&F'>("#"F'>("% #=#-/#$%<<<Selecione a opção C%'#" #"# %;("% >%+#> e cli>ue O< A "iura apresenta o resultado destaextração.

Figura – apresentação do resultado da extração e!etuada com uma determinada data para qualquer ano.

3,




Caso vocP >ueira cercar a apresentação de reistro de um determinado mPs= por exmplo= todasas vendas executadas em aosto de ,,,= na clula 84? diite a "órmula lóica?E5C3YDATA5QQ7J717 +3ZDATA5QQ7J731

Execute? D#$%&F'>("#"F'>("% #=#-/#$%<<<

Selecione a opção C%'#" #"# %;("% >%+#> e cli>ue O. A "iura apresenta o resultado destaextração. A "unção KDATA tra9al6a com os arumentos internos? #-%7 )&7 $'#:DATA5#-%7)&7$'#

Figura -Apresentação do resultado da extração de um determinado ms e ano.

A partir deste momento= vocP tem em suas mãos a 9ase de "uncionamento de toda a estrutura da9ase de dados dentro do microso"t excel= desde a manipulação simples de uma lista at umamanipulação mais avançada com uso de "unç$es mais especí"icas. Resta somente começar o >ue"aQ a opção S%)!-(! "!,'&("%& !+>;&'=%& da Caixa de 'i!loo F'>("% #=#-/#$%. desta "orma possível o9ter uma lista de extração sem reistros repetidos= >uando estes são exatamente iuais=

evitando a apresentação de in"ormaç$es duplicadas.

O U&% $! S;(%(#'&

0ara tra9al6ar com o recurso de S;(%(#'& imprescindível manter a 9ase de dados ordenada=seundo a ordem em >ue se deseTa o9ter um su9total. Neste caso= a 9ase de dados dever! estar ordenada pelos nomes dos vendedores.após a ordenação al"a9tica pelo campo >ue ser! o mais importante= manten6a o cursor posicionado dentro da !rea da 9ase de dados. em seuida execute o comando?D#$%&S;(%(#'&<<<

Ser! então apresentada a Caixa de di!loo Su9totais= indicada na "iura.

:




Figura – Caixa de diálogo Subtotais

Neste momento, dê um clique no botão O# . Observe como ficará a aresenta!ão das informa!"es base de dados, na figura abaixo.

Figura – Planil/a com os subtotais

ati$ados.

9serve >ue es>uerdaapareceram trPs novos9ot$es numerados como 12 ! 3. 0elas próximas"iuras ser! mostrado o>ue acontece se apertar

cada um deles e seusrespectivos resultados?

# figura aresenta a forma de visuali$a!ão obtida or meio do uso do botão n%mero $.

# figura aresenta a forma de visuali$a!ão obtida or meio do uso do botão n%mero %.

A "iura apresenta a "orma de visualiQação o9tida por meio do uso do 9otão nmero := >uepermite voltar situação oriinal.

:




0ara >ue seTam des"eitos os su9totais de uma planil6a= 9asta estar com o cursor posicionadodentro da !rea de 9ase de dados e= em seuida= executar o comando?D#$%&S;(%(#'&<<<R!)%=!" (%$%&

F;-/]!& $! B#&! $! D#$%&

Anteriormente "oram utiliQadas "unç$es como KS/A= K/@'(A= K/B(/= K/DN(/ entreoutras. Nesta parte serão estudadas "unç$es exclusivas para a manipulação de 9ase de dados. As "unç$es desta cateoria são idPnticas s demais "unç$es= tendo como di"erença o pre"ixo`BD. +ma "unção de 9ase de dados possui como sintaxe?K7'"unçãoU9ancocampocritrioV= em >ue?Função? Ser! uma das "unç$es K7'S/A= K7'/@'(A= K7'/A= K7'/(N entre outras..7anco? Ser! a "axia de clulas ou o nome de uma "axia >ue conten6a o endereço do 9anco de

dados e conten6a o nome do campo.Campo? Ser! o nmero da coluna >ue contm o campo a ser calculado. apreimeira coluna sempreser! UumV= a seunda 3 UdoisV e assim por diante. este poder! ser um nmero inteiro positivo apartir de um= ou o endereço de uma clula ou "órmula >ue retorne como resultado o nmeroUinteiro positivoV de deslocamento de uma coluna.Critrio? Ser! a "aixa de clulas >ue contm o critrio de procura= sendo no mínimo duas lin6as. aprimeira lin6a da "aixa de critrio dever! ter os nomes exatos dos campos da 9ase de dados. apartir da seunda lin6a= dever! ser in"ormado um ou mais critrios como condição para selecionar os dados a serem utiliQados pela "unção.Em seuida posicione o cursor na clula 73,?= diite a "unção? BDSOMA5A2:E2K7KG3:nde A2:E2K o intervalo da 9ase de dados= 5 a coluna V#>%" e G3: a !rea de extração da9ase de dados.

Repita os mesmos procedimentos para as demais "unç$es. Aora >ue todas as "unç$es "oram de"inidas= 9asta >ue vocP in"orme na !rea de critrio >ualin"ormação deseTa calcular. 0or exemplo? uanto vendeu o vendedor \oão Carlos_ 0osicione ocursor na clula G? e diite %% C#">%&. resultado imediato= o9serve.

Figura – 0ela da planil/a com cálculos executados com !unç%es de banco de dados para o $endedor +oão Carlos.

:3




VALIDAÇÃO DE DADOS

s dados diitados nas clulas devem estar dentro das reras de validação erada paraa>uela clula.

• Selecionar a clula deseTada Uclula C:V.• Clicar no menu 'ados e escol6er 1alidação• Na pal6eta Con"iuraç$es= em 0ermitir escol6a a opção 8ista• Em Fonte ou riem= selecionar a lista de validação= no caso do /odelo= o intervalo A#?A,

0erce9a >ue a tela de validaçãoencol6e para >ue vocP possaselecionar a !rea. 0ressione atecla EN)ER após a seleçãopara >ue retorne ao normal edepois no 9otão .

1eTa na "iura ao lado como "ica uma clula >uando

usada validação Upermitindo listaV= um drop-donUmenu suspensoV aparece ao lado da clula paraescol6a de dados?

::




EXERCÍCIO: 'iite= "ormate= responda= con"orme as orientaç$es ao lado e salve-a no micro edis>uete.

A planil6a acima respondida mostra uma operação de compra para o modelo 2onda R.

1eTa a planil6a a9aixo outra utilidade da pal6eta Con"iuraç$es? nesta planil6a de compra o valor mínimo de prestaç$es de # parcelas e o m!ximo 3.

1eTa a mensaem exi9ida caso vocP diite um valor di"erente dos permitidos.

:4




Na seunda pal6eta= /ensaem de Entrada vocP pode orientar o usu!rio so9re as restriç$es. Aredação para o campo título e a mensaem de entrada "ica a seu critrio e na clula aplicada"icar! aparecendo um coment!rio.

Na terceira pal6eta Alerta de erro vocP pode criar Tanela de mensaens no luar de exi9ição decoment!rio so9re as restriç$es. A redação para o campo título e a mensaem pode ser a mesma=com um detal6e= se utiliQe esta pal6eta= vocP pode apaar a redação da pal6eta anterior. 1ocPver! por>ue.2! trPs opç$es de estilo para essa pal6eta? 0arar= Aviso e (n"ormaç$es.

4 E&('>% P#"#": na caixa de mensaem apenas duas opç$es? Repetir Upara adicionar outro valorV eCancelar Uvolta ao valor >ue estavaV.

4 E&('>% A='&%: na caixa de mensaem são exi9idas trPs opç$es? Sim Uo valor diitado aceitoV=Não Uo valor diitado não aceitoV e Cancelar Uvolta ao valor >ue estavaV.

:5




4 E&('>% I-.%")#/]!&: na caixa de mensaem são exi9idas duas opç$es? Uo valor diitado aceitoV e Cancelar Uvolta ao valor >ue estavaV.

EXERCÍCIO: 'iite= "ormate= responda= con"orme as orientaç$es ao lado e salve-a no micro edis>uete.

:#




PROTEGER PASTAS DE TRABALOS E PLANILAS

L')'(#" # !''/% ! # !$'/% $! ;)# >#-'># '-$'='$;#>

. Selecione a planil6a >ue so"rer! proteção3. )ire a seleção das clulas >ue vocP deseTa poder alterar depois >ue proteer a planil6a.:. )ire a seleção dos o9Tetos r!"icos >ue vocP deseTa poder alterar depois >ue proteer aplanil6a.4. culte >uais>uer "órmulas >ue vocP não deseTa >ue "i>uem visíveis.5. No menu Ferramentas= aponte para 0roteer e cli>ue em 0roteer planil6a.#. 0ara impedir alteraç$es nas clulas das planil6as ou nos dados e outros itens em r!"icos= epara impedir a exi9ição de lin6as= colunas e "órmulas ocultas= mar>ue a caixa de seleçãoContedo. 0ara impedir alteraç$es nos o9Tetos r!"icos de planil6as ou r!"icos= mar>ue a caixa

de seleção 9Tetos. 0ara impedir alteraç$es nas de"iniç$es de cen!rios em uma planil6a= mar>uea caixa de seleção Cen!rios.&. 0ara impedir >ue outros usu!rios removam a proteção da planil6a= diite uma sen6a cli>ue em

:&




e= em seuida= rediite a sen6a na caixa de di!loo Con"irmar sen6a. As sen6as coincidemmaisculas e minsculas. 'iite a sen6a exatamente como deseTa inseri-la= incluindo letrasmaisculas e minsculas*. 0ara proteer as clulas deve-se?• Selecionar a clula ou um intervalo de clulas

• Em Formatar= escol6er Clulas= clicar na uia 0roteção e ativar )ravada para não deixar alterar dados numa planil6a eHou culta para não mostrar as "órmulas utiliQadas.

• Seuir os passos da proteção de planil6a

Se vocP atri9uir uma sen6a= deve copi!-la e uard!-la em local seuro. Se perder a sen6a=não ter! acesso aos elementos proteidos na planil6a.

P"%(!,!-$% +%-(!6$% +%-("# #>(!"#/%

1eTa o exemplo a9aixo?

0erce9eu no campo total a "órmula utiliQada para encontrar o valor_ K':HE:

1amos )ravar a planil6a para >ue não 6aTa alteraç$es nos dados. 1eTa a "iura a9aixo?

1ocP pode escol6er >ual ação ser! 9lo>ueada para >ue não 6aTa alteraç$es= depois diite umasen6a e a rediite= como nas "iuras a9aixo?

:*




1eTa na "iura ao lado >ue aluns itens nas 9arras de "erramentas 0adrão e Formatação "icamdesa9ilitados Uexemplos? nerito= mesclar e centraliQar= des"aQer= autosoma= etc.V

Caso alum deseTe alterar o contedo de sua planil6a=veTa a mensaem >ue ser! exi9ida?

0ara >ue retorne ao normal= desproteTa sua planil6a= diitando corretamente a sen6a?

O+;>(#-$% .?");>#& $! ;)# >#-'>#

Com a planil6a desproteida "aça o camin6o con"orme as "iuras a9aixo?

:,




0roteTa novamente a planil6a= con"orme visto anteriormente. 1eTa a "iura a9aixo?

1eTa >ue a clula ativa ado T%(#> e >ue a "órmulautiliQada não vista na 9arrade "órmulas= e assim com>ual>uer outra "órmulaAPENAS nesta planil6a.

S%>'+'(#" ;)# &!-# #"# #"'" ;)# #&(# $! ("##>% 5A";'=%

. No menu Ar>uivo= cli>ue em Salvar como.3. No menu Ferramentas= cli>ue em pç$eserais.:. Na caixa Sen6a de proteção= diite uma sen6ae cli>ue em .

As sen6as di"erenciam-se entremaisculas= minsculas= numricas oual"anumricas. A >uantidade m!xima de 5caracteres.

4. Rediite a sen6a.5. Cli>ue no 9otão Salvar.

4




#. 0ara a9rir o ar>uivo= diite a sen6a exatamente como criada.

TABELAS DIN^MICAS1< I-("%$;/%

As ta9elas din;micas= podem ser de"inidas da seuinte "orma? `São ta9elas interativa >ueresumem elevadas >uantidades de dados= usando a estrutura e mtodos de c!lculoespeci"icadosX.`@ uma "erramenta de an!lise de dados >ue produQ o resumo de reistros num "ormato ta9ular

concisoX.3. >ue se pode "aQer recorrendo a )a9elas 'in;micas3. Analisar dados rapidamente As )' permitem oraniQar dados e calcular in"ormação resumida utiliQando cateoriasUcamposV e "unç$es resumo Usoma= mdia= etcV.

P#&&%& #"# +"'#" T#!># D'-_)'+#

4




• &ados

'elat(rio de )abelas e *rá+icos &inicos - executa o assistente de tabelas e gráficos din&micos, cu'os assos

se descreve seguidamente(• assos do assistente)

$/ *secificar o tio dos dados origem, que odem ser)+ ista ou -ase de dados do excel(+ Origem externa / quer0 1 alica!ão de acesso a dados que encontra e visuali$a dados guardados em tabelasexternas, antes de devolver dados ao excel ara 234(+ 5ntervalos m%ltilos de consolida!ão / ermite combinar e resumir dados de m%ltilos intervalos de diferentesdocumentos, com cabe!al6os em lin6as e em colunas(+ 3ados de outra 23.

43




Surge o esquema da tabela din&mica na r7ria fol6a, ara onde se odem arrastar os camos existentes na 'anela82abela din&mica9, com o mesmo efeito que o asso anterior. 'esultado da 0eleç"o 1+etuada para criar a)abela.

F%");>"'%

O :icrosoft *xcel trabal6a com diversos tios de formulários.;ocê ode utili$ar os formulários fornecidos com o *xcel arainserir dados em intervalos, listas ou em outros bancos dedados. 2amb<m ode criar formulários ara imrimi+los ou usá+los online, incluindo formulários abertos no *xcel e os

formulários colocados em áginas da =eb. >ara caturar eorgani$ar os dados nos formulários online, você ode usar uma asta de trabal6o do *xcel, outro rograma ou um banco dedados.

Formulários internos ara dados do *xcel >ara obter intervalos ou listas em lanil6as do *xcel, você ode exibir umformulário de dados que ermite inserir novos dados, encontrar lin6as com base no conte%do das c<lulas, atuali$ar os dados eexcluir lin6as do intervalo ou da lista.

Criando seu r7rio formulário no *xcel ;ocê ode criar formulários do *xcel ara imrimi+los ou usá+los on+

line. Os formulários on+line odem incluir controles 1controle) um ob'eto da interface gráfica do usuário, comouma caixa de texto, uma caixa de sele!ão, uma barra de rolagem ou um botão de comando, que ermite a usuárioscontrolar o rograma. ;ocê usa controles ara exibir dados ou o!"es, executar uma a!ão ou facilitar a leitura dainterface do usuário.4, or exemlo, bot"es de o!ão e listas susensas. 2amb<m ode roteger um formulário on+line de modo que somente certas c<lulas fiquem dison?veis ara entrada de dados, al<m de validar os dados aracertificar+se de que os usuários s7 insiram os tios de dados válidos ara o formulário. @ oss?vel disonibili$ar formulários on+line a artir do *xcel, de áginas da =eb ou de rogramas :icrosoft ;isual -asic for #licativos1;-#4 1;-#) uma versão de linguagem macro do :icrosoft ;isual -asic usada ara rogramar alicativos do=indoAs e inclu?da em vários alicativos da :icrosoft.4.

4:

http://appendpopup%28this%2C%27xldeflist_1%27%29/


http://appendpopup%28this%2C%27ofcontrol_2%27%29/




http://appendpopup%28this%2C%27ofvba_3%27%29/













O UE É UMA MACRO`Bma macro < um equeno rograma que cont<m uma lista de instru!"es a reali$ar no *xcel. Como sendo umasa!"es de oera!"es, uma macro ode executar um con'unto de tarefas atrav<s de um %nico rocedimento o qual ode ser invocado raidamente. #s instru!"es que formam o coro da macro são escritas num c7digo r7rio araque o comutador as ossa entender, essa linguagem < designada or ;-# – ;isual -asic for #lications.

CRIAR MACROS*xistem duas ossibilidades de cria!ão de macros) #trav<s do ravador de :acros

Btili$ando o editor e rogramando em ;isual -asic for #lications

Gravar uma MacroD. 2erraentas 3 4acro 3 *ravar nova 4acro. O *xcel exibirá a caixa de diálogo da abaixo )

E. O noe da 4acro + será constitu?do or um con'unto de caracteres que identificarão a:acro e a sua funcionalidade.

. )ecla de atal!o) < comosto or uma combina!ão de teclas que oderão ser utili$adas ara executar uma macro.

G. 5ra6enar a acro e) *sta asta de trabal6o or arma$enar a macro numa das seguintesalternativas) sendo que cada uma corresonde a macros de &mbito diferente. #ssim deverão arma$enar amacro na o!ão que ela este'a activa semre que o documento estiver aberto, e ara que ela este'aarma$enada no r7rio fic6eiro não correndo o risco de ficar arma$enada nas :acrosdo *xcel.

H. &escriç"o, introdu$a um comentário I fun!ão – este oderá ser imortante ara que não se'aesquecido o resectivo ob'etivo e funcionalidade.

44




J. Clique em OK ara iniciar a grava!ão da macro – neste momento será exibida uma barra de grava!ãosemel6ante I da figura E, e o ?cone do rato será transformado numa cassete, indicando o estado degrava!ão.

L. Na -arra de rava!ão, existem dois bot"es) >arar grava!ão e Meferencia Melativa – O botão de >arar grava!ão termina a grava!ão da macro, o botão de Meferência Melativa Meferência seleciona o modo degrava!ão da macro – se < feito com base em referências relativas ou referências absolutas.

7ota: 2ome aten!ão ao asso que dá quando está gravando ois tudo será registrado, o que significaque quando for executar a macro, esses rocedimentos serão efetuados.

45




REFER\NCIA BIBLIOGRÁFICA

ATuda do /icroso"t Excel 0= /icroso"t.

Apostila de )reinamento Excel Avançado. 'ivisão de Serviços Comunidade - Centro de

Computação da +nicamp= com adaptaç$es pelos cola9oradores? %rson 8lis= Fa9io Auusto=

\os 1eia e Srio de \esus.SENAC-/A. Ncleo de Formação em (n"orm!tica.

4#




INTRODUÇÃO

Esta apostila tem o o9Tetivo de tra9al6ar nesta "icina de EXCEL 4

FÓRMULAS AVANÇADAS com alumas "órmulas avançadas como? "unç$es

erais= "unç$es condicionais= "unç$es "inanceiras e lóicas= procurar um valor

especí"ico em uma lista de valores= relacionamento de planil6as= Su9total=

1alidação e proteção de pastas de tra9al6o e planil6as.

N6+>!% $! F%")#/% !) I-.%")('+# 4 NFI

SENACMA

4&




Documents

Apostila Excel Avançado 2009