Cap 1.3 - Excel 2010 Avançado

Excel 2010 Avanado

Senac So Paulo 1

OBJETIVOS Utilizar funes matemticas do Excel. Utilizar funes estatsticas do Excel. Utilizar funes lgicas do Excel. Utilizar funes de data e hora do Excel. Formatar datas. Efetuar clculos com datas e horas.

Excel 2010 Avanado

2 Senac So Paulo

Excel 2010 Avanado

Senac So Paulo 3

FUNES MATEMTICAS O Excel 2010 oferece ferramentas avanadas que possibilitam a anlise, o gerenciamento e o compartilhamento de informaes de tal forma que a tomada de decises para uma situao especfica se torne mais eficiente.

nesse contexto que neste curso voc aprender algumas dessas funes e ferramentas, para que possa utilizar em suas planilhas aprimorando sua capacidade de analisar conjuntos de dados.

Atividade 1 Utilizando funes matemticas

Objetivos : Utilizar as funes matemticas do Excel 2010.

Tarefas : Abrir a pasta Vendas trimestre 1.xlsx.

Somar o nmero de itens vendidos.

Calcular o preo total de cada produto.

Somar o valor total de vendas.

Somar o valor vendido por filial.

Somar o valor vendido por filial e por ms.

Funo SOMA A funo SOMA tem como objetivo somar os valores numricos contidos em uma faixa de clulas.

Sintaxe: SOMA(nm1;nm2;.....)

Argumentos: os argumentos nm1, nm2,..... correspondem a valores, endereos de clulas ou faixa de clulas que se deseja somar.

1. Abra a pasta Vendas trimestre 1.xlsx.

Excel 2010 Avanado

4 Senac So Paulo

2. Voc vai calcular o nmero de itens vendidos pelas filiais no 1 trimestre de 2010. Clique na clula D19 e digite:

= SOMA(

3. Com o mouse clicado selecione a regio D4:D17 . 4. Pressione a tecla ENTER e voc ter a seguinte frmula na clula:

= SOMA(D4:D17)

Uma outra forma de selecionar a funo SOMA atravs da ferramenta que se encontra na guia Pgina Inicial, grupo Edio. Se voc escolher essa forma, no digite o sinal = antes de selecionar a funo.

Funo PRODUTO A funo PRODUTO tem como objetivo efetuar o produto entre os valores numricos contidos em uma faixa de clulas.

Sintaxe: PRODUTO(nm1;nm2;.....) Argumentos: os argumentos nm1, nm2,..... correspondem a valores, endereos de clulas ou

faixa de clulas que desejamos multiplicar.

1. Voc vai calcular o valor total de cada item vendido pelas filiais no 1 trimestre de 2010. Clique na clula F4 e digite:

= PRODUTO(

2. Clique na clula D4, digite ; (ponto e vrgula) e clique na clula E4. 3. Pressione a tecla ENTER e voc ter a seguinte frmula na clula:

= PRODUTO(D4:E4)

No caso da atividade, o argumento D4:E4 refere-se ao intervalo de clulas D4 at E4, ou seja, sero considerados todos os valores entre essas duas clulas. Voc pode, no entanto, substituir o argumento por D4;E4, uma vez que no existe nenhuma clula entre D4 e E4.

4. Copie a frmula para todos os itens da planilha.

Funo SOMARPRODUTO A funo SOMARPRODUTO tem como objetivo multiplicar os componentes das faixas de dados fornecidas e retornar a soma destes produtos.

Sintaxe: SOMAPRODUTO(faixa1;faixa2;.....) Argumentos: os argumentos faixa1, faixa2,..... correspondem a faixa de clulas que desejamos

multiplicar e depois somar.

Excel 2010 Avanado

Senac So Paulo 5

1. Voc vai calcular o valor total das vendas efetuadas pelas filiais no 1 trimestre de 2010. Na verdade ter de somar os resultados da multiplicao de quantidade por preo unitrio de cada produto. Clique na clula D20 e digite:

= SOMARPRODUTO(

2. Com o mouse selecione a faixa D4:D17 3. Digite ; (ponto e vrgula). 4. Com o mouse selecione a faixa E4:E17.

Voc poderia indicar a frmula selecionando a faixa D4:D17 e, pressionando a tecla CTRL, selecionar a faixa E4:E17. 5. Pressione a tecla ENTER e voc ter a seguinte frmula na clula:

=SOMARPRODUTO(D4:D17;E4:E17)

A operao realizada corresponde a somar a coluna Valor Total.

Funo SOMASE A funo SOMASE tem como objetivo efetuar a soma dos valores indicados de acordo com um determinado critrio ou condio.

Sintaxe: SOMASE(intervalo; critrios; intervalo_soma) Argumentos:

o Intervalo: intervalo de clulas onde o critrio ser procurado; o Critrio: condio para definir quais valores sero somados. Esses critrios podem ser

nmero, expresso, referncia de clula, texto ou funo. Por exemplo: 2, Ana, >100, F5. O critrio deve ser informado entre aspas se for um valor alfanumrico ou se a expresso utilizar operadores relacionais (>, =,

Excel 2010 Avanado

6 Senac So Paulo

13. Na clula D22 calcule o valor vendido pela filial do Brooklin. Digite a frmula: =SOMASE(A4:A17;"Brooklin";F4:F17)

14. Na clula D23 calcule o valor das vendas no ms de Janeiro. Observe que o critrio Janeiro deve ser procurado na faixa B4:B17.

=SOMASE(B4:B17;"Janeiro";F4:F17)

15. Nas clulas D24 e D25 calcule os valores vendidos para os meses de Fevereiro e Maro.

Funo SOMASES A funo SOMASES tem como objetivo efetuar a soma em um intervalo atendendo a vrios critrios, que sero combinados.

Sintaxe: o SOMASES(intervalo_soma; intervalo_critrio1; critrio1; intervalo_critrio2; critrio2;......)

Argumentos: o Intervalo_soma: intervalo de valores que sero somados. o Intervalo_critrio1: intervalo de clulas onde o critrio1 ser procurado; o Critrio1: condio para definir quais valores sero somados. Esses critrios podem ser

nmero, expresso, referncia de clula, texto ou funo. Por exemplo: 2, Ana, >100, F5. O critrio deve ser informado entre aspas se for um valor alfanumrico ou se a expresso utilizar operadores relacionais (>, =, 100, F5. O critrio deve ser informado entre aspas se for um valor alfanumrico ou se a expresso utilizar operadores relacionais (>, =,

Excel 2010 Avanado

Senac So Paulo 7

11. Pressione a tecla ENTER e voc ter a seguinte frmula na clula: =SOMASES(F4:F17;A4:A17;"Vila Mariana";B4:B17;"Fevereiro")

12. Na clula D27 calcule o valor vendido pela filial do Brooklin no ms de Janeiro.

13. Feche a pasta salvando as modificaes.

Excel 2010 Avanado

8 Senac So Paulo

ANOTAES

Excel 2010 Avanado

Senac So Paulo 9

Atividade 2 Utilizando Funes Estatsticas

Objetivos : Utilizar as funes estatsticas do Excel 2010.

Tarefas : Abrir a pasta Prmio.xlsx.

Calcular a maior e a menor venda entre os vendedores.

Calcular a mdia de vendas dos vendedores.

Calcular o total de vendedores.

Calcular o nmero de vendedores que no efetuaram vendas.

Calcular o nmero de vendedores que efetuaram vendas.

Calcular o nmero de vendedores premiados com o primeiro lugar em vendas.

Calcular o total de vendedores por departamento.

Calcular o nmero de vendedores premiados com o primeiro lugar em vendas por departamento.

Calcular a mdia de vendas por departamento.

Calcular a mdia de vendas pelos vendedores no premiados com o primeiro lugar por departamento.

Uma loja possui trs departamentos de vendas: Informtica, Eletrodomsticos e Livraria. Cada departamento tem a sua equipe de vendas. Com a finalidade de motivar os vendedores, a loja resolveu distribuir um prmio no valor de R$ 450.000,00 para o(s) vendedor(es) que ficou(aram) em primeiro lugar no volume de vendas.

O objetivo efetuar os seguintes controles:

Mostrar o ranking de vendas, ou seja, calcular a colocao de cada um levando em considerao a venda individual em relao ao conjunto de vendas dos vendedores.

Mostrar a maior e a menor venda efetuada considerando todos os departamentos; Calcular a mdia de vendas considerando todos os departamentos; Mostrar o nmero de vendedores da loja, o nmero de vendedores que no efetuaram vendas, o

nmero de vendedores que efetuaram vendas e quantos ficaram em primeiro lugar em vendas;

Por departamento, calcular o nmero de vendedores, quantos ficaram em primeiro lugar em vendas, calcular a mdia de vendas e a mdia de vendas dos vendedores no ganhadores do prmio.

Para resolver essa situao voc vai trabalhar com a pasta Prmio.xlsx. Nessa pasta voc encontrar duas planilhas: VENDAS e RELATRIO.

Excel 2010 Avanado

10 Senac So Paulo

Funo MXIMO Essa funo retorna o valor mximo entre um conjunto de valores numricos informado.

Sintaxe: MXIMO(nm1;nm2;.....) Argumentos: os argumentos nm1, nm2,..... correspondem aos valores numricos cujo valor

mximo queremos determinar. Os argumentos podem ser nmeros, nomes, matrizes ou referncias que contenham nmeros. Pode-se informar de 1 at 255 valores numricos.

1. Abra a pasta Prmio.xlsx. 2. Selecione a planilha RELATRIO e clique na clula B2. Nessa clula voc deve calcular o valor mximo

das vendas que esto na faixa C3:C22 da planilha VENDAS. 3. Digite:

= MXIMO(

4. Selecione a planilha VENDAS e, com o mouse clicado, selecione a faixa C3:C22. 5. Pressione a tecla ENTER e voc ter a seguinte frmula na clula B2:

=MXIMO(VENDAS!C3:C22)

Excel 2010 Avanado

Senac So Paulo 11

Observe que, utilizando a faixa C3:C22 da planilha VENDAS e como a frmula est sendo colocada na planilha RELATRIO, colocado o nome da planilha seguido pelo ponto de exclamao na identificao da regio informada.

Outra forma de selecionar a funo MXIMO atravs da ferramenta que se encontra na guia Pgina Inicial, grupo Edio. Se voc escolher essa forma, no digite o sinal = antes de selecionar a funo.

Funo MN Essa funo retorna o valor mnimo entre um conjunto de valores numricos informado.

Sintaxe: MN(nm1;nm2;.....) Argumentos: os argumentos nm1, nm2,..... correspondem aos valores numricos cujo valor

mnimo queremos determinar. Os argumentos podem ser nmeros, nomes, matrizes ou referncias que contenham nmeros. Pode-se informar de 1 at 255 valores numricos.

1. Selecione a planilha RELATRIO e clique na clula B3. Nessa clula voc vai calcular o valor mnimo das vendas que esto na faixa C3:C22 da planilha VENDAS.

2. Digite: = MN(

3. Selecione a planilha VENDAS e, com o mouse clicado, selecione a faixa C3:C22. 4. Pressione a tecla ENTER e voc ter a seguinte frmula na clula B2:

=MN(VENDAS!C3:C22)

Outra forma de selecionar a funo MN atravs da ferramenta que se encontra na guia Pgina Inicial, grupo Edio. Se voc escolher essa forma, no digite o sinal = antes de selecionar a funo.

Funo MDIA Essa funo retorna a mdia aritmtica entre um conjunto de valores numricos informado.

Sintaxe: MDIA(nm1;nm2;.....) Argumentos: os argumentos nm1, nm2,..... correspondem aos valores numricos para os quais

desejamos calcular a mdia aritmtica. Os argumentos podem ser nmeros, nomes, matrizes ou referncias que contenham nmeros. Pode-se informar de 1 at 255 valores numricos.

1. Selecione a planilha RELATRIO e clique na clula B4. Nessa clula voc vai calcular a mdia aritmtica das vendas que esto na faixa C3:C22 da planilha VENDAS.

2. Digite: = MDIA(

3. Selecione a planilha VENDAS e, com o mouse clicado, selecione a faixa C3:C22.

Excel 2010 Avanado

12 Senac So Paulo

4. Pressione a tecla ENTER e voc ter a seguinte frmula na clula B2: =MDIA(VENDAS!C3:C22)

Outra forma de selecionar a funo MDIA atravs da ferramenta que se encontra na guia Pgina Inicial, grupo Edio. Se voc escolher essa forma, no digite o sinal = antes de selecionar a funo.

Funo ORDEM.EQ Essa funo retorna a posio de um nmero em uma lista de nmeros.

Sintaxe: ORDEM.EQ(nmero; referncia; [ordem]) Argumentos:

o Nmero: nmero cuja posio se quer encontrar em uma lista de nmeros; o Referncia: lista de nmeros onde o nmero informado ser posicionado; o Ordem: argumento opcional. Se for 0 ou omitido, o Excel posicionar o nmero

considerando a lista de nmeros ordenada de forma descendente; se for qualquer valor diferente de zero, o Excel posicionar o nmero considerando a lista de nmeros ordenada de forma ascendente.

A presena de nmeros com a mesma posio ir interferir na ordem dos nmeros subsequentes Por exemplo, em uma lista de nmeros inteiros classificados em ordem crescente, se o nmero 5 aparecer duas vezes e tiver uma ordem de 2, ento 6 teria uma ordem de 4 e nenhum nmero teria a ordem de 3.

5. Selecione a planilha VENDAS e clique na clula D3. 6. Voc vai utilizar a funo ORDEM.EQ para posicionar o valor da venda da clula C3 dentro da faixa de

vendas C3:C22. Digite a frmula:

= ORDEM.EQ(C3;$C$3:$C$22)

Observe que a faixa de clulas C3:C22 foi fixada na frmula, pois na cpia para as clulas de baixo essa faixa deve continuar sendo a mesma.

7. Pressione a tecla ENTER e copie a frmula at a clula D22.

No se preocupe com as informaes de erro que aparecem nas clulas D8 e D17. Esse erro devido a no existir um valor de venda nas clulas C8 e C17. Mais adiante, nesse mesmo captulo, esse erro ser tratado de forma que no aparea a mensagem.

Funo CONT.VALORES Essa funo conta o nmero de clulas no vazias em um intervalo informado. O conjunto de clulas pode conter qualquer tipo de informao.

Sintaxe: CONT.VALORES(intervalo1; [intervalo2]; ....) Argumentos:

o Intervalo1;[intervalo2];....: faixas de clulas que devem ser contadas.

Excel 2010 Avanado

Senac So Paulo 13

1. Voc vai contar o nmero total de vendedores da loja. Para isso conte quantas clulas contm os nomes dos vendedores. Selecione a planilha RELATRIO e clique na clula B7.

2. Digite: =CONT.VALORES(

3. Como os nomes dos vendedores esto na planilha VENDAS, selecione a planilha e, com o mouse clicado, selecione a faixa de clulas A3:A22.

4. Pressione a tecla ENTER e voc ter a frmula: =CONT.VALORES(VENDAS!A3:A22)

Funo CONTAR.VAZIO Essa funo conta o nmero de clulas vazias em um intervalo informado. Clulas que contenham valor nulo no sero contadas.

Sintaxe: CONTAR.VAZIO(intervalo) Argumento: o argumento intervalo representa a faixa de clulas que devem ser contadas.

1. Agora voc vai contar quantos vendedores no efetuaram vendas, ou seja, quantas so as clulas da coluna VALOR DA VENDA que esto vazias. Selecione a planilha RELATRIO e clique na clula B8.

2. Digite: =CONTAR.VAZIO(

3. Como os valores das vendas esto na planilha VENDAS, selecione a planilha e, com o mouse clicado, selecione a faixa de clulas C3:C22.

4. Pressione a tecla ENTER e voc ter a frmula: =CONTAR.VAZIO(VENDAS!C3:C22)

Funo CONT.NM Essa funo conta o nmero de clulas que contm nmeros em um intervalo informado. Sero contadas as clulas que contenham nmeros, datas ou nmeros escritos entre aspas.

Sintaxe: CONT.NM(intervalo1; [intervalo2]; ....) Argumentos:

o Intervalo1;[intervalo2];....: faixas de clulas que devem ser contadas.

1. Agora voc vai contar quantos vendedores efetuaram vendas, ou seja, quantas so as clulas da coluna VALOR DA VENDA que esto preenchidas com nmeros. Selecione a planilha RELATRIO e clique na clula B9.

2. Digite: =CONT.NM(

Excel 2010 Avanado

14 Senac So Paulo

3. Como os valores das vendas esto na planilha VENDAS, selecione a planilha e, com o mouse clicado, selecione a faixa de clulas C3:C22.

4. Pressione a tecla ENTER e voc ter a frmula: =CONT.NM(VENDAS!C3:C22)

Funo CONT.SE Essa funo conta o nmero de ocorrncias de uma determinada condio em um intervalo de clulas informado.

Sintaxe: CONT.SE(intervalo; critrio) Argumentos:

o Intervalo: intervalo de clulas que ser considerado; o Critrio: condio que ser procurada no intervalo de clulas;

1. Voc vai contar quantos so os vendedores ganhadores do prmio, ou seja, aqueles que se encontram em primeiro lugar na colocao de vendas. Selecione a planilha RELATRIO e clique na clula B10.

2. Digite: =CONT.SE(

3. Como voc quer verificar se o vendedor est na classificao 1, o intervalo de clulas considerado deve ser o da coluna COLOCAO da planilha VENDAS. Selecione a planilha VENDAS e, com o mouse clicado, selecione o intervalo D3:D22.

4. Digite ; (ponto e vrgula). 5. Digite 1 6. Pressione a tecla ENTER e voc ter a seguinte frmula na clula:

= CONT.SE(VENDAS!D3:D22;1)

7. Agora voc vai contar quantos vendedores so do departamento de Informtica. Para isso voc deve contar o nmero de ocorrncias da palavra Informtica no intervalo de clulas B3:B22 da planilha VENDAS. Clique na clula E2 da planilha RELATRIO.

8. Digite a frmula: = CONT.SE(VENDAS!B3:B22;"Informtica")

Como a palavra Informtica, que o critrio para a funo, est na clula D1 da planilha RELATRIO, a frmula tambm poderia ser escrita da seguinte forma:

= CONT.SE(VENDAS!B3:B22;RELATRIO!D1)

Funo CONT.SES Essa funo aplica critrios a clulas em vrios intervalos e conta o nmero de vezes que todos os critrios so verdadeiros.

Excel 2010 Avanado

Senac So Paulo 15

Sintaxe: CONT.SES(intervalo1; critrio1;intervalo2;critrio2;....) Argumentos:

o Intervalo1: intervalo de clulas onde ser procurado o critrio1; o Critrio1: condio que ser procurada no intervalo de clulas intervalo1; o Intervalo2: intervalo de clulas onde ser procurado o critrio2; o Critrio2: condio que ser procurada no intervalo de clulas intervalo2;

Pode-se ter at 127 pares de intervalos/critrios.

1. Voc vai calcular quantos so os vendedores ganhadores do prmio do departamento de Informtica. Veja que agora voc precisa considerar duas condies: o vendedor est classificado como primeiro e trabalha no departamento de Informtica. Selecione a planilha RELATRIO e clique na clula E3.

2. Digite: =CONT.SES(

3. Voc deve procurar pela palavra Informtica. Selecione a planilha VENDAS e selecione o intervalo

B3:B22. Digite ; (ponto e vrgula) e, na planilha RELATRIO, clique na clula D1. Digite ; (ponto e vrgula).

4. Agora voc vai procurar pelo nmero 1 no intervalo D3:D22 (estamos procurando pelos primeiros colocados). Selecione a planilha VENDAS e o intervalo D3:D22. Digite ; (ponto e vrgula) e digite 1. Pressione a tecla ENTER e teremos a seguinte frmula:

= CONT.SES(VENDAS!B3:B22;RELATRIO!D1;VENDAS!D3:D22;1)

Funo MDIASE A funo MDIASE tem como objetivo calcular a mdia aritmtica dos valores indicados de acordo com um determinado critrio ou condio.

Sintaxe: MDIASE(intervalo; critrios; intervalo_mdia) Argumentos:

o Intervalo: intervalo de clulas onde o critrio ser procurado; o Critrio: condio para definir quais valores sero considerados para o clculo da mdia; o Intervalo_mdia: intervalo de clulas que ser considerado para calcular a mdia.

1. Calcule agora a mdia de vendas para o departamento de Informtica. Selecione a clula E4 da planilha RELATRIO e digite a frmula a seguir. Utilize o mtodo de selecionar com o mouse, como foi feito para as frmulas anteriores, para construir a expresso.

= MDIASE(VENDAS!B3:B22;RELATRIO!D1;VENDAS!C3:C22)

Funo MDIASES A funo MDIASES tem como objetivo calcular a mdia aritmtica em um intervalo atendendo a vrios critrios.

Excel 2010 Avanado

16 Senac So Paulo

Sintaxe: MDIASES(intervalo_mdia; intervalo_critrio1; critrio1; intervalo_critrio2; critrio2;......) Argumentos:

o Intervalo_mdia: intervalo de valores que sero considerados para o clculo da mdia. o Intervalo_critrio1: intervalo de clulas onde o critrio1 ser procurado; o Critrio1: condio para definir quais valores sero considerados para o clculo da mdia; o Intervalo_critrio2: intervalo de clulas onde o critrio2 ser procurado; o Critrio2: condio para definir quais valores sero considerados para o clculo da mdia.

1. Calcular a mdia aritmtica das vendas dos vendedores do departamento de Informtica e que no so

ganhadores do prmio. Na clula E5 da planilha RELATRIO digite a frmula: = MDIASES(VENDAS!C3:C22;VENDAS!B3:B22;RELATRIO!D1;VENDAS!D3:D22;"1")

Os critrios informados nessa frmula so os seguintes:

VENDAS!C3:C22 intervalo de valores que sero considerados no clculo da mdia; VENDAS!B3:B22 intervalo onde ser procurado o primeiro critrio (Departamento); RELATRIO!D1 endereo da clula que contm o primeiro critrio (Informtica); VENDAS!D3:D22 intervalo onde ser procurado o segundo critrio (Colocao); 1 segundo critrio, vendedores que no esto na posio 1.

2. Preencha, utilizando o mesmo raciocnio utilizado para as informaes do departamento de

Informtica, os quadros relativos aos departamentos de Eletrodomsticos e Livraria da planilha RELATRIOS.

3. No final voc dever ter o seguinte para as planilhas VENDAS e RELATRIO:

Excel 2010 Avanado

Senac So Paulo 17

Excel 2010 Avanado

18 Senac So Paulo

ANOTAES

Excel 2010 Avanado

Senac So Paulo 19

Atividade 3 Utilizando Funes Lgicas

Objetivos : Utilizar as funes lgicas do Excel 2010.

Tarefas : Calcular o valor do prmio para cada um dos vendedores ganhadores.

Atribuir um conceito de acordo com o valor das vendas de cada vendedor.

Distribuir um prmio extra entre os vendedores que no alcanaram o primeiro lugar de acordo com o valor de suas vendas.

Tratar adequadamente os erros que possam ocorrer em planilhas.

Para completar o preenchimento da planilha VENDAS da pasta Prmio.xlsx, voc ainda tem algumas tarefas a realizar:

Distribuir o prmio que a empresa oferece aos vendedores colocados em primeiro lugar; Atribuir um conceito a cada vendedor de acordo com o seu volume de vendas, comparando-o com

a meta a ser atingida individualmente;

Distribuir um prmio extra aos vendedores que no se classificaram em primeiro lugar, mas que tambm se destacaram de acordo com alguns critrios estabelecidos.

Funo SE Essa funo tem como objetivo efetuar testes condicionais com valores e frmulas permitindo a escolha do que fazer de acordo com o resultado do teste, que pode ser Falso ou Verdadeiro.

Sintaxe: o SE(teste_lgico; valor_se_verdadeiro; valor_se_falso)

Argumentos: o Teste_lgico: condio que ser avaliada, trazendo Falso ou Verdadeiro como resultado; o Valor_se_verdadeiro: o que fazer se o resultado do teste_lgico for Verdadeiro; o Valor_se_falso: o que fazer se o resultado do teste_lgico for Falso.

1. Selecione a planilha VENDAS da pasta Prmios.xlsx.

Voc deve verificar na clula E3 se o vendedor est em primeiro lugar na classificao e, se estiver, calcular e mostrar o seu prmio. Se o vendedor no estiver em primeiro lugar, o valor que deve ser colocado na clula zero. Lembre-se que o prmio total deve ser dividido igualmente entre os vendedores ganhadores.

O raciocnio que vamos seguir o seguinte:

a) SE classificao = 1 b) ENTO dividir o valor do prmio pelo nmero de ganhadores e mostrar na clula c) SENO mostrar o valor 0 na clula

2. Clique na clula E3 e digite o seguinte: = SE(D3=1;$B$1/RELATRIO!$B$10;0)

Excel 2010 Avanado

20 Senac So Paulo

Observe que na clula D3 est a classificao do vendedor, na clula B1 est valor do prmio total e na clula B10 da planilha RELATRIO est o nmero de ganhadores.

3. Copie a frmula para todos os vendedores. No se preocupe com os erros apontados nas clulas E8 e E17. Mais adiante voc vai modificar as frmulas para tratar adequadamente esses erros.

4. Agora voc vai colocar uma mensagem para cada vendedor de acordo com os seguintes critrios:

CONCEITO CONDIO

EXCELENTE Colocao do vendedor igual a 1

TIMO Valor das vendas do vendedor maior do que a meta

BOM Valor das vendas do vendedor igual meta

PODE MELHORAR Nenhuma das condies anteriores

Observe que existem quatro condies a serem verificadas. Isso no ser possvel atravs de uma nica funo SE. Nesse caso, voc ter que aninhar funes SE para resolver o problema. At 64 funes SE podem ser aninhadas no Excel 2010.

O raciocnio que voc deve seguir o seguinte:

SE colocao do vendedor = 1 ENTO atribuir conceito EXCELENTE SENO SE valor das vendas maior do que a meta ENTO atribuir conceito TIMO SENO SE valor das vendas igual meta ENTO atribuir conceito BOM SENO atribuir conceito PODE MELHORAR

5. Clique na clula F3 e digite a seguinte frmula: = SE(D3=1;"EXCELENTE";SE(C3>$D$1;"TIMO";SE(C3=$D$1;"BOM";"PODE MELHORAR")))

6. Copie a frmula para todos os vendedores. 7. Feche a pasta Premio.xlsx.

Funo E Essa funo retorna o valor Verdadeiro se todos os seus argumentos forem verdadeiros.

Sintaxe: o E(lgico1; lgico2;...)

Argumentos: o Lgico1: condio que ser avaliada, trazendo Falso ou Verdadeiro como resultado; o Lgico2;...: condies que sero avaliadas, trazendo Falso ou Verdadeiro como resultado.

Excel 2010 Avanado

Senac So Paulo 21

Em uma funo E voc pode colocar at 255 argumentos, ou seja, pode-se ter at 255 condies lgicas para avaliar.

Funo OU Essa funo retorna o valor Verdadeiro se pelo menos um de seus argumentos for verdadeiro.

Sintaxe: o OU(lgico1; lgico2;...)

Argumentos: o Lgico1: condio que ser avaliada, trazendo Falso ou Verdadeiro como resultado; o Lgico2;...: condies que sero avaliadas, trazendo Falso ou Verdadeiro como resultado.

Em uma funo OU voc pode colocar at 255 argumentos, ou seja, pode-se ter at 255 condies lgicas para avaliar.

As funes E e OU so normalmente utilizadas para dar mais flexibilidade a outras funes que executam testes lgicos, como por exemplo, em conjunto com a funo SE.

1. Abra a pasta Estado civil.xlsx. Voc vai verificar a diferena dos resultados entre as funes E e OU.

2. Clique na clula D3. Nela deve-se mostrar o valor VERDADEIRO se a pessoa for solteira e sua idade for maior ou igual a 22 anos. Caso contrrio, dever ser mostrado o valor FALSO.

3. Digite a frmula: = E(B3="Solteiro(a)";C3>=22)

4. Copie a frmula para todas as pessoas. 5. Clique na clula E3. Nela deve-se mostrar o valor VERDADEIRO se a pessoa for solteira ou se sua

idade for maior ou igual a 22 anos. Caso contrrio, dever ser mostrado o valor FALSO.

Excel 2010 Avanado

22 Senac So Paulo

6. Digite a frmula: = OU(B3="Solteiro(a)";C3>=22)

7. Copie a frmula para todas as pessoas.

Note que no caso da funo E, o resultado s verdadeiro se as duas condies forem verdadeiras. No caso da funo OU, o resultado verdadeiro se pelo menos uma das condies for verdadeira, e somente ser falso se as duas condies forem falsas.

8. Feche a pasta Estado civil.xlsx e abra a pasta Prmio.xlsx. 9. A empresa resolveu premiar tambm os funcionrios que obtiveram conceito TIMO com uma

viagem para Natal (RN) e os funcionrios que venderam menos ou o valor da meta, mas que venderam mais do que R$ 300.000,00, com um jantar. Considere o seguinte raciocnio:

SE conceito = TIMO ENTO prmio extra = Viagem para Natal SENO SE vendas 300000 ENTO prmio extra = Jantar

10. Clique na clula G3 e digite o seguinte:

= SE(F3="TIMO";"Viagem para Natal";SE(E(C3300000);"Jantar";""))

Observe que se nenhuma das condies for verdadeira, o valor ser colocado na clula, ou seja, a clula ficar vazia para evitar que seja mostrada a mensagem FALSO.

11. Copie a frmula para todos os vendedores.

Excel 2010 Avanado

Senac So Paulo 23

Funo SEERRO Essa funo tem como objetivo retornar um valor especificado se uma frmula gerar um erro. Se no existir erro, a funo retorna o resultado da frmula.

Sintaxe: o SEERRO(frmula; valor_se_erro)

Argumentos: o Frmula: frmula que se deseja verificar quanto a erros; o Lgico2;...: valor a ser retornado se a frmula gerar um erro.

Os seguintes tipos de erro so considerados: #N/D, #VALOR!, #REF!, #DIV/0!, #NM!, #NOME? ou #NULO!.

12. As frmulas da coluna COLOCAO esto gerando erros nos casos em que a clula correspondente a venda est vazia. Como um fato que pode acontecer, aconselhvel no mostrar a mensagem de erro gerada pelo Excel, mas sim armazenar na clula um valor mais adequado. Nesse caso, se existir o erro na clula, voc deve deix-la vazia, ou seja, armazenar . Clique na clula D3 e modifique a frmula para:

=SEERRO(ORDEM.EQ(C3;$C$3:$C$22);"")

Observe que todos os erros da planilha foram solucionados.

necessrio saber qual valor deve-se colocar na clula nos casos de erro para no ocasionar novos erros.

13. Feche a pasta Prmio.xlsx.

Excel 2010 Avanado

24 Senac So Paulo

ANOTAES

Excel 2010 Avanado

Senac So Paulo 25

Atividade 4 Manipulando Datas e Horas

Objetivos : Formatar datas.

Utilizar funes de datas e horas.

Efetuar clculos com datas e horas.

Tarefas : Formatar uma data para que seja mostrado o dia da semana.

Formatar uma data para que seja mostrado o ms.

Formatar uma data para que seja mostrado o dia, ms e ano.

Utilizar as funes de data para mostrar a data de hoje, o ano, dia e ms de uma data, o nmero do dia da semana.

Projetar dias teis a partir de uma data.

Calcular o nmero de dias teis entre duas datas.

Projetar dias corridos a partir de uma data.

Calcular o nmero de dias corridos entre duas datas.

Calcular o nmero de horas e minutos entre dois horrios.

Aplicar formatao condicional nas planilhas.

Em muitas planilhas necessrio o trabalho com datas e horas, efetuando clculos e projees. O Excel 2010 oferece vrias funes e opes de formatao que podem ser aplicadas para as mais variadas necessidades.

Formatao de DATAS 1. Abra a pasta Datas Formato.xlsx .

Excel 2010 Avanado

26 Senac So Paulo

2. Copie o contedo da coluna A para as demais colunas. 3. Na coluna B voc vai mostrar o nmero serial que representa a data. Esse nmero o nmero de dias

transcorridos entre a data 01/01/1900 e a data considerada. Selecione o intervalo B2:B32. 4. Na guia Pgina Inicial, grupo Nmero, clique na seta do comando Formato de nmero.

5. Selecione Geral. 6. Na coluna C voc vai mostrar o nome do dia da semana da data armazenada. Selecione o intervalo

C2:C32. 7. Clique na seta do grupo Nmero da guia Pgina Inicial.

8. Na guia Nmero, selecione a categoria Personalizado. 9. Apague o que est no campo Tipo e digite dddd.

Excel 2010 Avanado

Senac So Paulo 27

O que informado nesse campo determina o formato do dado selecionado. Para o caso de datas, considerando o dia da data, temos os seguintes formatos personalizados:

D dias que so formados por um algarismo so exibidos com um algarismo. DD dias que so formados por um algarismo so exibidos com dois algarismos, tendo o zero

como inicial.

DDD exibe o nome do dia de forma abreviada, atravs das trs primeiras letras. DDDD exibe o nome do dia por extenso.

10. Clique em OK. 11. Agora voc vai exibir na coluna D o nome do ms por extenso. Selecione o intervalo D2:D32. 12. Clique na seta do grupo Nmero da guia Pgina Inicial. 13. Na guia Nmero, selecione a categoria Personalizado. 14. Apague o que est no campo Tipo e digite mmmm.

Para o caso de datas, considerando o ms da data, temos os seguintes formatos personalizados:

M meses que so formados por um algarismo so exibidos com um algarismo. MM meses que so formados por um algarismo so exibidos com dois algarismos, tendo o

zero como inicial.

MMM exibe o nome do ms de forma abreviada, atravs das trs primeiras letras. MMMM exibe o nome do ms por extenso.

Excel 2010 Avanado

28 Senac So Paulo

15. Clique em OK.

Agora voc vai mostrar a data por extenso. Por exemplo: para a data 01/01/2010, dever ser mostrado sexta-feira, 01 de janeiro de 2010.

16. No campo Tipo digite o seguinte: dddd", "dd" de "mmmm" de "aaa

Onde:

dddd nome do dia da semana , coloca um espao, vrgula, espao dd nmero do dia de coloca espao, a palavra de, espao mmmm nome do ms por extenso de coloca espao, a palavra de, espao aaa nmero do ano

No caso do ano, a representao aa formata a data para que apaream os dois ltimos dgitos do ano e a representao aaa formata a data para que apaream os quatro dgitos do ano.

17. Clique em OK.

Excel 2010 Avanado

Senac So Paulo 29

Voc deve ter em mente a diferena entre contedo e formato. Contedo o dado armazenado na clula, e formato a forma como ele est sendo mostrado. Quando voc efetuar clculos com clulas formatadas, o contedo que ser trabalhado, e no o formato.

FUNES DE DATA Funo HOJE A funo HOJE retorna a data de hoje formatada como data.

Sintaxe: HOJE()

Essa funo no possui argumentos. No entanto deve-se digitar os parnteses.

Funo ANO Essa funo retorna o ano da data informada. O ano retornado como um inteiro no intervalo de 1900 a 9999.

Sintaxe: ANO(data_informada)

Argumentos: data_informada: data que se deseja retornar o ano.

Funo MS Essa funo retorna o nmero do ms da data informada. O ms retornado como um inteiro no intervalo de 1 a 12.

Sintaxe: MS(data_informada) Argumentos: data_informada: data que se deseja retornar o ms.

Funo DIA Essa funo retorna o dia da data informada. O dia retornado como um inteiro no intervalo de 1 a 31.

Sintaxe: DIA(data_informada) Argumentos: data_informada: data que se deseja retornar o dia.

Funo DIA.DA.SEMANA Essa funo retorna o nmero do dia da semana da data informada. O dia retornado como um inteiro, variando de 1 (domingo) a 7 (sbado), por padro.

Sintaxe: o DIA.DA.SEMANA(data_informada;tipo)

Argumentos: o data_informada: data que se deseja retornar o nmero do dia da semana. o tipo: nmero que determina o tipo do valor retornado. Este argumento opcional e pode

ter os seguintes valores:

Excel 2010 Avanado

30 Senac So Paulo

TIPO VALOR RETORNADO

1 ou omitido Nmeros 1 (domingo) a 7 (sbado)

2 Nmeros 1 (segunda-feira) a 7 (domingo)



12 Nmeros 1 (tera-feira) a 7 (segunda-feira)

13 Nmeros 1 (quarta-feira) a 7 (tera-feira)

14 Nmeros 1 (quinta-feira) a 7 (quarta-feira)

15 Nmeros 1 (sexta-feira) a 7 (quinta-feira)

16 Nmeros 1 (sbado) a 7 (sexta-feira)

17 Nmeros 1 (domingo) a 7 (sbado)

1. Abra a pasta Datas Funes.xlsx.

2. Clique na clula B1. 3. Coloque nessa clula a funo para mostrar a data de hoje. Digite:

= HOJE()

4. Clique na clula B2. 5. Coloque nessa clula a funo para mostrar o dia da data de hoje. Digite:

= DIA(B1)

6. Clique na clula B3. 7. Coloque nessa clula a funo para mostrar o nmero do ms da data de hoje. Digite:

= MS(B1)

8. Clique na clula B4.

Excel 2010 Avanado

Senac So Paulo 31

9. Coloque nessa clula a funo para mostrar o ano da data de hoje. Digite: = ANO(B1)

10. Clique na clula B5. 11. Coloque nessa clula a funo para mostrar o nmero do dia da semana da data de hoje, considerando

1 como domingo e 7 como sbado. Digite:

= DIA.DA.SEMANA(B1)

Funo DIATRABALHO.INTL Essa funo retorna os dias teis a projetar a partir de uma data informada, considerando como calculado o fim de semana e os feriados que possam ocorrer no perodo.

Sintaxe: o DIATRABALHO.INTL(data_inicial;dias;fim_de_semana;feriados)

Argumentos: o data_inicial: data a partir da qual se deseja projetar dias teis. o dias: nmero de dias teis antes ou depois da data inicial, isto , quando ocorrer o

primeiro dia til projetado a partir da data inicial. Um valor positivo gera uma data futura, um valor negativo gera uma data passada e um valor zero gera uma data inicial.

o fim_de_semana: argumento opcional. Indica que dias da semana so considerados como pertencentes ao final de semana. Pode ter os seguintes valores:

FIM_DE_SEMANA DIAS DE FIM DE SEMANA

1 ou omitido Sbado, Domingo

2 Domingo, Segunda

3 Segunda-feira, Tera-feira

4 Tera-feira, Quarta-feira

5 Quarta-feira, Quinta-feira

6 Quinta-feira, Sexta-feira

7 Sexta-feira, Sbado

11 Apenas Domingo

12 Apenas Segunda-feira

13 Apenas Tera-feira

14 Apenas Quarta-feira

15 Apenas Quinta-feira

Excel 2010 Avanado

32 Senac So Paulo

16 Apenas Sexta-feira

17 Apenas Sbado

feriados: argumento opcional que um conjunto de uma ou mais datas que sero excludas do calendrio de dias teis.

1. Abra a pasta Caixa.xlsx. 2. Nela voc encontra duas planilhas: Lanamentos e Feriados. Na planilha Lanamentos voc far a

projeo dos dias teis para o ms de Abril/2010.

3. Clique na clula B3 e digite a data 01/04/2010. 4. Formate a clula B3 para que aparea somente o nome do ms. 5. Agora voc vai projetar o primeiro dia til a partir do ltimo dia do ms de maro. Essa data o dia

anterior da data 01/04/2010 que est armazenada na clula B3, ou seja, o contedo da clula B3 1. Clique na clula A6 e digite:

= DIATRABALHO.INTL(B3-1;

6. Como se deseja obter o primeiro dia til a partir dessa data, digite 1; 7. Nesta atividade o final de semana composto por sbado e domingo. Logo, o parmetro que deve ser

informado para fim_de_semana 1. Continue digitando 1;

Excel 2010 Avanado

Senac So Paulo 33

8. Na planilha Feriados, na coluna A, esto os feriados do ms de abril de 2010. Selecione a planilha Feriados e clique sobre a indicao da coluna A. Marque a coluna inteira para evitar refazer as frmulas caso seja inserido algum feriado no previsto. Pressione ENTER e ter a seguinte frmula na clula:

= DIATRABALHO.INTL(B3-1;1;1;Feriados!A:A)

9. Agora voc vai calcular o prximo dia til a partir da clula A6. Clique na clula A7 e digite a seguinte frmula:

= DIATRABALHO.INTL(A6;1;1;Feriados!A:A)

10. Copie a frmula para as clulas abaixo e voc ter uma lista de dias teis no ms de abril de 2010.

11. Feche a pasta Caixa.xlsx.

Funo DIATRABALHOTOTAL.INTL Essa funo retorna o nmero de dias teis entre duas datas usando parmetros para indicar quais e quantos dias so dias de fim de semana, bem como feriados.

Sintaxe: o DIATRABALHO.INTL(data_inicial;data_final;fim_de_semana;feriados)

Argumentos: o data_inicial: data a partir da qual se deseja projetar dias teis. o data_final: data at a qual se deseja projetar dias teis. o fim_de_semana: argumento opcional. Indica que dias da semana so considerados

como pertencentes ao final de semana. Pode ter os seguintes valores:

Excel 2010 Avanado

34 Senac So Paulo

FIM_DE_SEMANA DIAS DE FIM DE SEMANA

1 ou omitido Sbado, Domingo

2 Domingo, Segunda

3 Segunda-feira, Tera-feira

4 Tera-feira, Quarta-feira

5 Quarta-feira, Quinta-feira

6 Quinta-feira, Sexta-feira

7 Sexta-feira, Sbado

11 Apenas Domingo

12 Apenas Segunda-feira

13 Apenas Tera-feira

14 Apenas Quarta-feira

15 Apenas Quinta-feira

16 Apenas Sexta-feira

17 Apenas Sbado

o feriados: argumento opcional que um conjunto de uma ou mais datas que sero

excludas do calendrio de dias teis.

Seu trabalho neste momento calcular o salrio que deve ser pago por uma empresa aos seus estagirios. Esse controle efetuado atravs da planilha Pagamento Estagiarios.xlsx.

1. Abra a pasta Pagamento Estagiarios.xlsx. 2. O primeiro passo calcular quantos so os dias de vigncia do contrato, ou seja, quantos dias corridos

existem entre a data de incio e a data de trmino do contrato. Para saber quantos dias existem entre duas datas, basta subtrair a data inicial da data final. Dessa forma, selecione a clula F4 e digite:

= D4-C4

3. Copie a frmula para todos os estagirios. 4. No entanto, o salrio pago considerando o nmero de dias a trabalhar no perodo, ou seja, o nmero

de dias teis do contrato, e no o nmero de dias corridos. Clique na clula G4 e digite a frmula: = DIATRABALHOTOTAL.INTL(C4;D4;1;Feriados!A:A)

Excel 2010 Avanado

Senac So Paulo 35

Onde:

C4 data inicial D4 data final 1 final de semana formado por sbado e domingo Feriados!A:A lista de feriados no ano de 2010

5. Copie a frmula para todos os estagirios. 6. Agora calcule quanto cada estagirio deve receber, sabendo que o valor pago por hora est

armazenado na clula C14. Clique na clula H4 e digite a frmula: = E4*G4*$C$14

7. Copie a frmula para todos os estagirios.

8. A empresa percebeu que alguns contratos teriam que ser prorrogados. Foi ento solicitado que uma nova coluna fosse inserida na planilha informando quantos dias corridos deveriam ser acrescidos aos contratos. Sua tarefa agora inserir essa coluna e efetuar o clculo das novas datas de trmino dos contratos para que o clculo dos salrios fique correto. Insira duas novas colunas na planilha antes da coluna Carga horria diria:

9. Digite os valores mostrados na coluna Prorrogao (dias). Se quando digitar o valor aparecer uma data, formate os valores como nmeros.

Excel 2010 Avanado

36 Senac So Paulo

10. Se voc tiver uma data e somar um nmero a essa data, voc ter a data correspondente data inicial mais o nmero de dias somado. Portanto, para calcular a data efetiva de trmino do contrato, temos que adicionar o nmero de dias de prorrogao do contrato data de trmino do contrato. Clique na clula F4 e digite a frmula:

= D4+E4

11. Copie a frmula para todos os estagirios. 12. Agora necessrio recalcular os dias de vigncia do contrato e os dias a trabalhar no perodo, pois a

data final do contrato a nova data calculada, que se encontra na coluna Data de trmino efetiva. Refaa as frmulas e confira o resultado:

13. Feche e pasta Pagamento Estagirios.xlsx. 14. Agora voc vai efetuar clculos com horas. Abra a pasta Atividades e horas.xlsx. Nessa pasta voc

encontra duas planilhas: Controle e Feriados. Na planilha Controle voc vai calcular quanto o funcionrio deve receber em funo do tempo trabalhado nos dias teis de fevereiro de 2010 e do valor que deve receber por hora.

15. Clique na clula B2 e digite: 01/02/2010.

Excel 2010 Avanado

Senac So Paulo 37

16. Formate a clula para aparecer o nome do ms. 17. A partir da clula A5 preencha at a clula A23 com os dias teis de fevereiro. Utilize o mesmo

raciocnio do exerccio anterior. Os feriados de fevereiro esto na planilha Feriados. 18. Copie os dados do intervalo A5:A23 para o intervalo B5:B23. Tenha o cuidado de copiar os valores, e

no as frmulas.

19. Formate o intervalo B5:B23 para que sejam mostrados os nomes dos dias da semana. 20. Calcule agora o tempo trabalhado em cada dia. Esse tempo calculado subtraindo o horrio de

entrada do horrio de sada. Para isso, clique na clula E5 e digite: = D5-C5

21. Copie a frmula para todo o intervalo.

Funo HORA Essa funo retorna a hora de um valor de tempo. A hora retornada como um inteiro, variando de 0 (12:00 A.M.) a 23 (11:00 P.M.).

Sintaxe: HORA(valor_tempo) Argumento: valor_tempo: horrio que contm a hora que desejamos encontrar.

Funo MINUTO Essa funo retorna os minutos de um valor de tempo. O valor retornado um nmero inteiro que varia de 0 a 59.

Sintaxe: MINUTO(valor_tempo) Argumento: valor_tempo: horrio que contm os minutos que desejamos encontrar.

1. Calcule quantas horas foram trabalhadas em cada dia. Clique na clula F5 e digite a seguinte frmula: 2. = HORA(E5) 3. Copie a frmula para todos os dias. Se o valor para nmero de horas estiver aparecendo como um

valor do tipo hora, formate para que seja mostrado como nmero.

4. Para calcular o nmero de minutos, alm das horas cheias, clique na clula G5 e digite a seguinte frmula:

5. = MINUTO(E5) 6. Copie a frmula para todos os dias. Se o valor para nmero de minutos estiver aparecendo como um

valor do tipo hora, formate para que seja mostrado como nmero.

7. Para calcular o valor a pagar voc deve multiplicar o nmero de horas pelo valor por hora e somar com a multiplicao do nmero de minutos pelo valor por hora dividido por 60. Clique na clula I5 e digite a frmula:

= F5*$I$2+G5*$I$2/60

8. Copie a frmula para todos os dias. 9. Totalize o valor a pagar na clula I24.

Excel 2010 Avanado

38 Senac So Paulo

Formatao CONDICIONAL A formatao condicional permite que os dados sejam mostrados na planilha de uma forma mais objetiva. Voc pode, por exemplo, modificar a cor da fonte ou da clula para que os dados que satisfazem critrios estabelecidos sejam exibidos.

1. Na coluna Dia da semana voc vai mostrar em vermelho todos os dias que forem segunda-feira. No entanto voc deve lembrar que o valor que est armazenado na clula uma data, e no o nome do dia da semana. Para obter o nmero do dia da semana da data armazenada, voc deve utilizar a funo DIA.DA.SEMANA vista anteriormente. Selecione o intervalo B5:B23.

2. Na guia Pgina Inicial, grupo Estilo, clique em Formatao Condicional e, no menu apresentado, selecione Nova regra.

3. Na janela exibida, selecione Usar uma frmula para determinar quais clulas devem ser formatadas. 4. No campo Formatar valores em que essa frmula verdadeira, digite a frmula:

Excel 2010 Avanado

Senac So Paulo 39

=DIA.DA.SEMANA(B5;1)=2

Lembre-se que a funo DIA.DA.SEMANA traz como retorno o nmero do dia da semana, de acordo com o tipo de semana indicado na funo. No caso o argumento para tipo foi passado como 1, ou seja, domingo o dia 1, segunda-feira o dia 2 e assim por diante.

5. Clique no boto Formatar e escolha a cor vermelha e negrito. 6. Clique em OK e, novamente em OK.

7. Feche a pasta Atividades e horas.xlsx.

Excel 2010 Avanado

40 Senac So Paulo

Exerccio Proposto

1. Abra a planilha Faculdade.xlsx.

Siga os procedimentos descritos para efetuar os clculos:

a) Selecione a planilha Boletim. b) Calcule a mdia dos alunos. Utilize a funo de tratamento de erros se for necessrio em todos os

clculos que sero efetuados.

c) Preencha a coluna MENSAGEM MDIA considerando a seguinte condio: se a mdia for maior ou igual a 5, a mensagem deve ser "Mdia OK", caso contrrio deve ser "Mdia abaixo do limite".

d) Preencha a coluna MENSAGEM FALTAS considerando a seguinte condio: se o nmero de faltas for menor ou igual a 25% das aulas dadas, a mensagem deve ser "Faltas OK", caso contrrio deve ser "Faltas acima do limite".

e) Calcule o RANKING DE MDIAS classificando os alunos por mdia. f) Preencha a coluna RESULTADO da seguinte forma: se a mdia est OK E faltas tambm OK, ento

colocar Aprovado, caso contrrio colocar Reprovado.

g) Formate condicionalmente a coluna RESULTADO colocando em vermelho os reprovados e em azul os aprovados.

h) Preencha a planilha Resumo com as informaes: nmero total de alunos, maior e menor mdia, nmero de aprovados e de reprovados, nmero de reprovados por mdia, por falta, por mdia e por falta, nmero de alunos que no entregaram o exerccio, nmero de alunos que entregaram o exerccio, mdia das mdias dos alunos aprovados, mdia de faltas dos alunos com mdia OK e reprovados.

Excel 2010 Avanado

Senac So Paulo 41

2. Abra a pasta Atendimento ao cliente.xlsx.

Nessa pasta voc encontrar as planilhas ATENDIMENTO e Feriados. A primeira planilha deve ser preenchida da seguinte forma:

a) Considere o ms de novembro. b) Preencha a coluna DATA com os dias teis de novembro. c) Calcule o tempo de atendimento de cada cliente. d) Calcule a data para retorno considerando o dia do atendimento e o prazo (em dias) para retorno. e) Calcule o prazo para retorno em dias teis, considerando a data do atendimento e a data para

retorno.

f) Utilize a planilha Feriados para informar os feriados do ano de 2010.

Excel 2010 Avanado

42 Senac So Paulo

3. Abra a pasta Banco.xlsx. Siga os seguintes procedimentos para efetuar os clculos na planilha:

a) Calcular o Saldo Atual. b) A coluna Situao deve ser preenchida da seguinte forma: se o cliente est com saldo positivo e a

mdia entre o saldo anterior e o saldo atual for superior ou igual a R$ 3.000,00, deve ser apresentada a mensagem Cheque Especial, caso contrrio deve ser apresentada a mensagem Cheque Comum.

c) A coluna Limite do Cheque Especial deve ser preenchida da seguinte forma: se o cliente no tiver cheque especial, a clula deve ser preenchida com zero; se o cliente tiver Cheque Especial e se o total de crditos for menor ou igual a R$ 3.000,00, o valor do cheque especial ser de R$ 2.000,00, caso contrrio (cliente com cheque especial e o total de crditos maior do que R$ 3.000,00) o valor do cheque especial ser de R$ 5.000,00.

d) Contar o nmero de clientes que receberam cheque especial.

4. Abra a pasta Idades e cursos.xlsx. Nessa pasta temos duas planilhas: Dados e Relatrio.

Excel 2010 Avanado

Senac So Paulo 43

a) Calcular a idade de cada aluno. Para isso considere o ano da data de hoje e o ano da data de nascimento do aluno.

b) Preencher a planilha Relatrio contando o nmero de alunos do sexo feminino, masculino, matriculados em biologia, educao fsica, histria, letras, matemtica e qumica e o nmero de alunos com idade maior do que 50 anos, menor do que 25 anos, maior de 40 anos e maior do que 30 anos.

5. Abra a pasta Vendedores.xlxs. Calcular o valor da Comisso e do Salrio Total devido aos vendedores.

Excel 2010 Avanado

44 Senac So Paulo

a) A coluna Comisso deve ser preenchida como sendo o valor da comisso sobre o valor total das vendas.

b) A coluna Salrio Total deve ser preenchida com o valor da comisso mais o valor do salrio mnimo se o vendedor atingiu um volume de vendas maior ou igual a R$ 10.000,00. Caso contrrio, a clula deve ser preenchida com o valor do salrio mnimo.

c) Calcular o total de vendas por funcionrio. d) Calcular o valor da maior e menor venda.

Documents

Cap 1.3 - Excel 2010 Avançado