Apostila Excel 2010 Avançado

(1)

(2)

A

DMINISTRAÇÃO

R

EGIONAL DO

S

ENAC NO

E

STADO DE

S

ÃO

P

AULO

Gerência de Desenvolvimento

Claudio Luiz de Souza Silva

Coordenação Técnica

Richard Martelli

Apoio Técnico

Paulo Roberto Pereira Dias

Revisão Técnica

Núcleo Digital Prestação de Serviços em Informática Ltda.

© Senac São Paulo 2011

1ª. edição

Elaboração do material didático

Maria Silvia Mendonça de Barros

Edição e Produção

(3)

(4)

E

XCEL

2010

A

VANÇADO

(5)

(6)

Sumário

Capítulo 1 – Funções matemáticas, estatísticas, lógicas e manipulação de datas ... 1

FUNÇÕES MATEMÁTICAS ... 3

Atividade 1 – Utilizando funções matemáticas ... 3

Função SOMA ... 3

Função PRODUTO ... 4

Função SOMARPRODUTO ... 4

Função SOMASE ... 5

Função SOMASES ... 6

Atividade 2 – Utilizando Funções Estatísticas ... 9

Função MÁXIMO ... 10 Função MÍN ... 11 Função MÉDIA... 11 Função ORDEM.EQ ... 12 Função CONT.VALORES ... 12 Função CONTAR.VAZIO... 13 Função CONT.NÚM ... 13 Função CONT.SE ... 14 Função CONT.SES ... 14 Função MÉDIASE... 15 Função MÉDIASES ... 15

Atividade 3 – Utilizando Funções Lógicas ... 19

Função SE ... 19

Função E ... 20

Função OU ... 21

Função SEERRO ... 23

Atividade 4 – Manipulando Datas e Horas ... 25

Formatação de DATAS ... 25 FUNÇÕES DE DATA ... 29 Função HOJE ... 29 Função ANO ... 29 Função MÊS ... 29 Função DIA ... 29 Função DIA.DA.SEMANA ... 29 Função DIATRABALHO.INTL ... 31 Função DIATRABALHOTOTAL.INTL ... 33

(7)

Função HORA ... 37

Função MINUTO ... 37

Formatação CONDICIONAL ... 38

Exercício Proposto ... 40

Capítulo 2 – Funções de Pesquisa e Auditoria de Fórmulas ... 45

Funções de PESQUISA ... 47

Atividade 1 – Utilizando as funções PROCV e PROCH ... 47

Função PROCV ... 47

Função PROCH ... 49

Atividade 2 – Utilizando as funções CORRESP e ÍNDICE ... 51

Função CORRESP ... 51

Função ÍNDICE ... 52

AUDITORIA DE FÓRMULAS ... 53

Atividade 3 – Rastreando os precedentes de uma célula ... 53

Atividade 4 – Rastreando os dependentes de uma célula ... 55

Atividade 5 – Rastreando erros em fórmulas ... 57

Atividade 6 – Avaliando fórmulas ... 59

Atividade 7 – Mostrando fórmulas ... 61

Capítulo 3 – Funções de Texto ... 65

Funções de PESQUISA ... 67

Atividade 1 – Utilizando a função ARRUMAR ... 67

Função ARRUMAR ... 67

Atividade 2 – Utilizando as funções MAIÚSCULA, MINÚSCULA e PRI.MAIÚSCULA ... 69

Função MAIÚSCULA ... 69

Função MINÚSCULA ... 69

Função PRI.MAIÚSCULA ... 69

(8)

Função ESQUERDA ... 71

Função DIREITA ... 71

Função EXT.TEXTO ... 71

Capítulo 4 – Funções de Banco de Dados ... 75

Funções de BANCO DE DADOS ... 77

Atividade 1 – Utilizando as funções BDSOMA e BDMÉDIA ... 77

Função BDSOMA ... 77

Função BDMÉDIA ... 79

Atividade 2 – Utilizando as funções BDMÁX e BDMÍN ... 81

Função BDMÁX... 81

Função BDMÍN ... 81

Atividade 3 – Utilizando a função BDEXTRAIR ... 83

Função BDEXTRAIR ... 83

Atividade 4 – Utilizando a função BDCONTARA ... 87

Função BDCONTARA ... 87

Capítulo 5 – Importação de Dados para o Excel ... 89

Importação de Dados para o EXCEL ... 91

Atividade 1 – Importando arquivos texto para o Excel ... 91

Importando um arquivo texto ... 91

Atividade 2 – Importando arquivos de dados para o Excel ... 97

IMPORTANDO UM ARQUIVO DE DADOS ... 97

Atividade 3 – Importando base de dados como consulta para o Excel ... 99

Importando uma Tabela como Consulta ... 99

Atividade 4 – Vinculando dados do Excel no Access ... 103

Vinculando Dados do Excel no Access ... 103

(9)

Capítulo 6 – Filtro, Classificação e Filtro Avançado ... 107

Filtrando e classificando no Excel ... 109

Atividade 1 – Aplicar o AutoFiltro ... 109

Atividade 2 – Aplicar o filtro a mais de um campo ... 111

Atividade 3 – Aplicar filtro alternativo ... 113

Atividade 4 – Classificar com o AutoFiltro ... 115

Atividade 5 – Classificação Avançada ... 117

Atividade 6 – Classificação por lista personalizada ... 119

Atividade 7 – Classificação por cores ... 121

Atividade 8 – Filtro avançado ... 123

Atividade 9 – Filtro avançado com critérios múltiplos ... 127

Capítulo 7 – Visualização de Dados ... 129

Atividade 1 – Criação de Minigráficos ... 131

Atividade 2 – Alteração e formatação de Minigráficos ... 133

Atividade 3 – Capturar telas ... 135

Capítulo 8 – Tabela Dinâmica ... 137

Atividade 1 – Criação da Tabela Dinâmica ... 139

Atividade 2 – Atualização e Inclusão de Dados ... 141

Atividade 3 – Criação de outros níveis ... 143

(10)

Atividade 5 – Agrupar Itens ... 147

Atividade 6 – Aplicar Filtro de Relatório ... 149

Atividade 7 – Agrupar campos por períodos ... 151

Atividade 8 – Segmentação de Dados ... 153

Atividade 9 – Gráfico Dinâmico ... 155

Atividade 10 – Mostrar Páginas de Relatório ... 157

Capítulo 9 – Resumindo Dados ... 159

Atividade 1 – Consolidação de Dados ... 161

Atividade 2 – Consolidação de Dados com Vínculo ... 165

Atividade 3 – Estrutura de Tópicos ... 167

Atividade 4 – Subtotais ... 169

Atividade 5 – Cenários ... 173

Capítulo 10 – Validação de Dados ... 181

Atividade 1 – Criar regras de validação ... 183

Capítulo 11 – Utilizando pasta de trabalho em equipe ... 189

Trabalho em Equipe... 191

Atividade 1 – Proteção de planilha ... 191

(11)

Capítulo 12 – Simulações ... 201

Atividade 1 – Atingir Meta ... 203

Atividade 2 – Solver ... 205

Atividade 3 – Tabela de Dados ... 209

Atividade 4 – Inserir Tabela ... 213

Capítulo 13 – Macros Intrativas ... 219

Trabalhando com Macros ... 221

Atividade 1 – Criar macro ... 221

Atividade 2 – Criando macro com deslocamentos relativos ... 225

Atividade 3 – Execução e exclusão de macros ... 229

Capítulo 14 – Automação de Planilhas ... 237

Automação de Planilhas ... 239

Atividade 1 – Criar controles para escolha de dados ... 239

Atividade 2 – Criação da Macro Modelos ... 243

Atividade 3 – Criar controles complementares à pesquisa ... 245

Atividade 4 – Criar Critérios para a pesquisa ... 249

Atividade 5 – Criar macro Veículos ... 251

Atividade 6 – Controles para cálculos ... 253

(12)

A estrutura da apostila

Esta apostila está dividida em capítulos. Cada capítulo contém atividades práticas e informações teóricas sobre o software.

Para obter o melhor rendimento possível em seu estudo, evitando dúvidas ou erros, é importante que você:

• leia com atenção todos os itens da apostila, pois sempre encontrará informações úteis para a execução das atividades;

• conheça e respeite o significado dos símbolos colocados na margem esquerda de determinados parágrafos do texto, pois eles servem para orientar seu trabalho;

• faça apenas o que estiver indicado no item e só execute uma seqüência após ter lido a instrução do respectivo item.

Significado dos símbolos Início

Este símbolo sinaliza o início de uma atividade. Apesar de poder interromper seu estudo em qualquer etapa, é recomendável que você inicie e finalize uma atividade sem interrupção.

Dica

Quando este símbolo aparecer, você terá informações adicionais sobre o assunto, como dicas, atalhos e sugestões para facilitar o trabalho com o software. Sua leitura não é obrigatória para o desenvolvimento da atividade; mas lembre-se: quanto mais informações você tiver, melhor será o seu aproveitamento.

Observação

Este símbolo marca um texto com observações sobre o assunto tratado. Embora importante, sua leitura não é obrigatória, nem interfere diretamente na atividade em execução.

Atenção

Toda vez que se deparar com este símbolo, leia com muita atenção o texto que o acompanha, pois as informações que ele contém afetarão os passos seguintes da atividade. Isso evitará dúvidas posteriores.

Utilizando o CD

Para a execução das atividades dos capítulos, disponibilizamos os arquivos num CD que acompanha a apostila.

Para facilitar seu trabalho, faça uma cópia da pasta “PLANILHAS - ATIVIDADES” para sua Área de Trabalho. Esta pasta contém todos os arquivos necessários para a execução de suas atividades e dos exercícios propostos.

Além dela, há uma pasta chamada “PLANILHAS RESOLVIDAS”, que contém os arquivos finais das atividades e exercícios propostos para você possa comparar com as atividades executadas por você.

(13)

(14)

OBJETIVOS



Utilizar funções matemáticas do Excel.



Utilizar funções estatísticas do Excel.



Utilizar funções lógicas do Excel.



Utilizar funções de data e hora do Excel.



Formatar datas.

(15)

(16)

FUNÇÕES MATEMÁTICAS

O Excel 2010 oferece ferramentas avançadas que possibilitam a análise, o gerenciamento e o compartilhamento de informações de tal forma que a tomada de decisões para uma situação específica se torne mais eficiente.

É nesse contexto que neste curso você aprenderá algumas dessas funções e ferramentas, para que possa utilizar em suas planilhas aprimorando sua capacidade de analisar conjuntos de dados.

Atividade 1 – Utilizando funções matemáticas

Objetivos : • Utilizar as funções matemáticas do Excel 2010. Tarefas : • Abrir a pasta Vendas trimestre 1.xlsx.

• Somar o número de itens vendidos. • Calcular o preço total de cada produto. • Somar o valor total de vendas.

• Somar o valor vendido por filial.

• Somar o valor vendido por filial e por mês.

Função SOMA

A função SOMA tem como objetivo somar os valores numéricos contidos em uma faixa de células.

• Sintaxe:

SOMA(núm1;núm2;...)

• Argumentos: os argumentos núm1, núm2,... correspondem a valores, endereços de células

ou faixa de células que se deseja somar.

(17)

2. Você vai calcular o número de itens vendidos pelas filiais no 1º trimestre de 2010. Clique na célula D19

e digite:

= SOMA( 3. Com o mouse clicado selecione a região D4:D17 .

4. Pressione a tecla ENTER e você terá a seguinte fórmula na célula: = SOMA(D4:D17)

Uma outra forma de selecionar a função SOMA é através da ferramenta Ʃ que se encontra na

guia Página Inicial, grupo Edição. Se você escolher essa forma, não digite o sinal = antes de selecionar a função.

Função PRODUTO

A função PRODUTO tem como objetivo efetuar o produto entre os valores numéricos contidos em uma faixa de células.

• Sintaxe: PRODUTO(núm1;núm2;...)

• Argumentos: os argumentos núm1, núm2,... correspondem a valores, endereços de células ou

faixa de células que desejamos multiplicar.

1. Você vai calcular o valor total de cada item vendido pelas filiais no 1º trimestre de 2010. Clique na célula F4 e digite:

= PRODUTO( 2. Clique na célula D4, digite ; (ponto e vírgula) e clique na célula E4. 3. Pressione a tecla ENTER e você terá a seguinte fórmula na célula:

= PRODUTO(D4:E4)

No caso da atividade, o argumento D4:E4 refere-se ao intervalo de células D4 até E4, ou seja, serão considerados todos os valores entre essas duas células. Você pode, no entanto, substituir o argumento por

D4;E4, uma vez que não existe nenhuma célula entre D4 e E4.

4. Copie a fórmula para todos os itens da planilha.

Função SOMARPRODUTO

A função 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 células que desejamos

(18)

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 multiplicação de quantidade por preço unitário de cada produto. Clique na célula D20 e digite:

= SOMARPRODUTO( 2. Com o mouse selecione a faixa D4:D17

3. Digite ; (ponto e vírgula).

4. Com o mouse selecione a faixa E4:E17.

Você poderia indicar a fórmula 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 fórmula na célula: =SOMARPRODUTO(D4:D17;E4:E17) A operação realizada corresponde a somar a coluna Valor Total.

Função SOMASE

A função SOMASE tem como objetivo efetuar a soma dos valores indicados de acordo com um determinado critério ou condição.

• Sintaxe: SOMASE(intervalo; critérios; intervalo_soma)

• Argumentos:

o Intervalo: intervalo de células onde o critério será procurado;

o Critério: condição para definir quais valores serão somados. Esses critérios podem ser

número, expressão, referência de célula, texto ou função. Por exemplo: 2, “Ana”, “>100”, F5. O critério deve ser informado entre aspas se for um valor alfanumérico ou se a expressão utilizar operadores relacionais (>, <, >=, <=, <>);

o Intervalo_soma: intervalo de valores que serão somados. Se este argumento for omitido,

serão somadas as células especificadas no argumento intervalo (as mesmas células às quais os critérios são aplicados).

6. Você vai calcular o valor vendido pela filial de Vila Mariana. Clique na célula D21 e digite: = SOMASE(

7. Com o mouse selecione a faixa A4:A17

8. Digite ; (ponto e vírgula). 9. Digite “Vila Mariana”. 10. Digite ; (ponto e vírgula).

11. Com o mouse selecione a faixa F4:F17.

12. Pressione a tecla ENTER e você terá a seguinte fórmula na célula: =SOMASE(A4:A17;"Vila Mariana";F4:F17)

(19)

13. Na célula D22 calcule o valor vendido pela filial do Brooklin. Digite a fórmula: =SOMASE(A4:A17;"Brooklin";F4:F17)

14. Na célula D23 calcule o valor das vendas no mês de Janeiro. Observe que o critério “Janeiro” deve ser procurado na faixa B4:B17.

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

15. Nas células D24 e D25 calcule os valores vendidos para os meses de Fevereiro e Março.

Função SOMASES

A função SOMASES tem como objetivo efetuar a soma em um intervalo atendendo a vários critérios, que serão combinados.

• Sintaxe:

o SOMASES(intervalo_soma; intervalo_critério1; critério1; intervalo_critério2; critério2;...)

• Argumentos:

o Intervalo_soma: intervalo de valores que serão somados.

o Intervalo_critério1: intervalo de células onde o critério1 será procurado;

o Critério1: condição para definir quais valores serão somados. Esses critérios podem ser

número, expressão, referência de célula, texto ou função. Por exemplo: 2, “Ana”, “>100”, F5. O critério deve ser informado entre aspas se for um valor alfanumérico ou se a expressão utilizar operadores relacionais (>, <, >=, <=, <>);

o Critério2: condição para definir quais valores serão somados. Esses critérios podem ser

número, expressão, referência de célula, texto ou função. Por exemplo: 2, “Ana”, “>100”, F5. O critério deve ser informado entre aspas se for um valor alfanumérico ou se a expressão utilizar operadores relacionais (>, <, >=, <=, <>).

Você pode ter até 127 pares de intervalos/critérios.

1. Você vai calcular o valor vendido pela filial de Vila Mariana no mês de Fevereiro. Clique na célula D26 e digite:

= SOMASES( 2. Com o mouse selecione a faixa F4:F17

3. Digite ; (ponto e vírgula).

4. Com o mouse selecione a faixa A4:A17.

5. Digite ; (ponto e vírgula). 6. Digite “Vila Mariana”. 7. Digite ; (ponto e vírgula).

8. Com o mouse selecione a faixa B4:B17.

9. Digite ; (ponto e vírgula). 10. Digite “Fevereiro”.

(20)

11. Pressione a tecla ENTER e você terá a seguinte fórmula na célula:

=SOMASES(F4:F17;A4:A17;"Vila Mariana";B4:B17;"Fevereiro") 12. Na célula D27 calcule o valor vendido pela filial do Brooklin no mês de Janeiro.

(21)

(22)

Atividade 2 – Utilizando Funções Estatísticas

Objetivos : • Utilizar as funções estatísticas do Excel 2010. Tarefas : • Abrir a pasta Prêmio.xlsx.

• Calcular a maior e a menor venda entre os vendedores. • Calcular a média de vendas dos vendedores.

• Calcular o total de vendedores.

• Calcular o número de vendedores que não efetuaram vendas. • Calcular o número de vendedores que efetuaram vendas.

• Calcular o número de vendedores premiados com o primeiro lugar em vendas. • Calcular o total de vendedores por departamento.

• Calcular o número de vendedores premiados com o primeiro lugar em vendas por departamento.

• Calcular a média de vendas por departamento.

• Calcular a média de vendas pelos vendedores não premiados com o primeiro lugar por departamento.

Uma loja possui três departamentos de vendas: Informática, Eletrodomésticos e Livraria. Cada departamento tem a sua equipe de vendas. Com a finalidade de motivar os vendedores, a loja resolveu distribuir um prêmio 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 colocação de cada um levando em consideração a venda individual em relação ao conjunto de vendas dos vendedores.

• Mostrar a maior e a menor venda efetuada considerando todos os departamentos; • Calcular a média de vendas considerando todos os departamentos;

• Mostrar o número de vendedores da loja, o número de vendedores que não efetuaram vendas, o número de vendedores que efetuaram vendas e quantos ficaram em primeiro lugar em vendas; • Por departamento, calcular o número de vendedores, quantos ficaram em primeiro lugar em

vendas, calcular a média de vendas e a média de vendas dos vendedores não ganhadores do prêmio.

Para resolver essa situação você vai trabalhar com a pasta Prêmio.xlsx. Nessa pasta você encontrará duas planilhas: VENDAS e RELATÓRIO.

(23)

Função MÁXIMO

Essa função retorna o valor máximo entre um conjunto de valores numéricos informado.

• Sintaxe: MÁXIMO(núm1;núm2;...)

• Argumentos: os argumentos núm1, núm2,... correspondem aos valores numéricos cujo valor

máximo queremos determinar. Os argumentos podem ser números, nomes, matrizes ou referências que contenham números. Pode-se informar de 1 até 255 valores numéricos.

1. Abra a pasta Prêmio.xlsx.

2. Selecione a planilha RELATÓRIO e clique na célula B2. Nessa célula você deve calcular o valor máximo das vendas que estão na faixa C3:C22 da planilha VENDAS.

3. Digite:

= MÁXIMO(

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 fórmula na célula B2: =MÁXIMO(VENDAS!C3:C22)

(24)

Observe que, utilizando a faixa C3:C22 da planilha VENDAS e como a fórmula está sendo colocada na planilha RELATÓRIO, é colocado o nome da planilha seguido pelo ponto de exclamação na identificação da região informada.

Outra forma de selecionar a função MÁXIMO é através da ferramenta Ʃ que se encontra na

guia Página Inicial, grupo Edição. Se você escolher essa forma, não digite o sinal = antes de selecionar a função.

Função MÍN

Essa função retorna o valor mínimo entre um conjunto de valores numéricos informado.

• Sintaxe: MÍN(núm1;núm2;...)

• Argumentos: os argumentos núm1, núm2,... correspondem aos valores numéricos cujo valor

mínimo queremos determinar. Os argumentos podem ser números, nomes, matrizes ou referências que contenham números. Pode-se informar de 1 até 255 valores numéricos.

1. Selecione a planilha RELATÓRIO e clique na célula B3. Nessa célula você vai calcular o valor mínimo das vendas que estão na faixa C3:C22 da planilha VENDAS.

2. Digite:

= MÍN(

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 fórmula na célula B2: =MÍN(VENDAS!C3:C22)

Outra forma de selecionar a função MÍN é através da ferramenta Ʃ que se encontra na guia

Página Inicial, grupo Edição. Se você escolher essa forma, não digite o sinal = antes de selecionar a função.

Função MÉDIA

Essa função retorna a média aritmética entre um conjunto de valores numéricos informado.

• Sintaxe: MÉDIA(núm1;núm2;...)

• Argumentos: os argumentos núm1, núm2,... correspondem aos valores numéricos para os quais

desejamos calcular a média aritmética. Os argumentos podem ser números, nomes, matrizes ou referências que contenham números. Pode-se informar de 1 até 255 valores numéricos.

1. Selecione a planilha RELATÓRIO e clique na célula B4. Nessa célula você vai calcular a média aritmética das vendas que estão na faixa C3:C22 da planilha VENDAS.

2. Digite:

= MÉDIA(

(25)

4. Pressione a tecla ENTER e você terá a seguinte fórmula na célula B2: =MÉDIA(VENDAS!C3:C22)

Outra forma de selecionar a função MÉDIA é através da ferramenta Ʃ que se encontra na guia

Página Inicial, grupo Edição. Se você escolher essa forma, não digite o sinal = antes de selecionar a função.

Função ORDEM.EQ

Essa função retorna a posição de um número em uma lista de números.

• Sintaxe: ORDEM.EQ(número; referência; [ordem])

• Argumentos:

o Número: número cuja posição se quer encontrar em uma lista de números;

o Referência: lista de números onde o número informado será posicionado;

o Ordem: argumento opcional. Se for 0 ou omitido, o Excel posicionará o número

considerando a lista de números ordenada de forma descendente; se for qualquer valor diferente de zero, o Excel posicionará o número considerando a lista de números ordenada de forma ascendente.

A presença de números com a mesma posição irá interferir na ordem dos números subsequentes Por exemplo, em uma lista de números inteiros classificados em ordem crescente, se o número 5 aparecer duas vezes e tiver uma ordem de 2, então 6 teria uma ordem de 4 e nenhum número teria a ordem de 3. 5. Selecione a planilha VENDAS e clique na célula D3.

6. Você vai utilizar a função ORDEM.EQ para posicionar o valor da venda da célula C3 dentro da faixa de vendas C3:C22. Digite a fórmula:

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

Observe que a faixa de células C3:C22 foi fixada na fórmula, pois na cópia para as células de baixo essa faixa deve continuar sendo a mesma.

7. Pressione a tecla ENTER e copie a fórmula até a célula D22.

Não se preocupe com as informações de erro que aparecem nas células D8 e D17. Esse erro é devido a não existir um valor de venda nas células C8 e C17. Mais adiante, nesse mesmo capítulo, esse erro será tratado de forma que não apareça a mensagem.

Função CONT.VALORES

Essa função conta o número de células não vazias em um intervalo informado. O conjunto de células pode conter qualquer tipo de informação.

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

• Argumentos:

(26)

1. Você vai contar o número total de vendedores da loja. Para isso conte quantas células contém os nomes dos vendedores. Selecione a planilha RELATÓRIO e clique na célula B7.

2. Digite:

=CONT.VALORES(

3. Como os nomes dos vendedores estão na planilha VENDAS, selecione a planilha e, com o mouse clicado, selecione a faixa de células A3:A22.

4. Pressione a tecla ENTER e você terá a fórmula:

=CONT.VALORES(VENDAS!A3:A22)

Função CONTAR.VAZIO

Essa função conta o número de células vazias em um intervalo informado. Células que contenham

valor nulo não serão contadas.

• Sintaxe: CONTAR.VAZIO(intervalo)

• Argumento: o argumento intervalo representa a faixa de células que devem ser contadas.

1. Agora você vai contar quantos vendedores não efetuaram vendas, ou seja, quantas são as células da coluna VALOR DA VENDA que estão vazias. Selecione a planilha RELATÓRIO e clique na célula B8.

2. Digite:

=CONTAR.VAZIO(

3. Como os valores das vendas estão na planilha VENDAS, selecione a planilha e, com o mouse clicado, selecione a faixa de células C3:C22.

=CONTAR.VAZIO(VENDAS!C3:C22)

Função CONT.NÚM

Essa função conta o número de células que contêm números em um intervalo informado. Serão contadas as células que contenham números, datas ou números escritos entre aspas.

• Sintaxe: CONT.NÚM(intervalo1; [intervalo2]; ....)

• Argumentos:

o Intervalo1;[intervalo2];....: faixas de células que devem ser contadas.

1. Agora você vai contar quantos vendedores efetuaram vendas, ou seja, quantas são as células da coluna VALOR DA VENDA que estão preenchidas com números. Selecione a planilha RELATÓRIO e clique na célula B9.

2. Digite:

(27)

3. Como os valores das vendas estão na planilha VENDAS, selecione a planilha e, com o mouse clicado, selecione a faixa de células C3:C22.

=CONT.NÚM(VENDAS!C3:C22)

Função CONT.SE

Essa função conta o número de ocorrências de uma determinada condição em um intervalo de células informado.

• Sintaxe: CONT.SE(intervalo; critério)

• Argumentos:

o Intervalo: intervalo de células que será considerado;

o Critério: condição que será procurada no intervalo de células;

1. Você vai contar quantos são os vendedores ganhadores do prêmio, ou seja, aqueles que se encontram em primeiro lugar na colocação de vendas. Selecione a planilha RELATÓRIO e clique na célula B10.

2. Digite:

=CONT.SE(

3. Como você quer verificar se o vendedor está na classificação 1, o intervalo de células considerado deve ser o da coluna COLOCAÇÃO da planilha VENDAS. Selecione a planilha VENDAS e, com o mouse clicado, selecione o intervalo D3:D22.

4. Digite ; (ponto e vírgula). 5. Digite 1

6. Pressione a tecla ENTER e você terá a seguinte fórmula na célula: = CONT.SE(VENDAS!D3:D22;1)

7. Agora você vai contar quantos vendedores são do departamento de Informática. Para isso você deve contar o número de ocorrências da palavra Informática no intervalo de células B3:B22 da planilha

VENDAS. Clique na célula E2 da planilha RELATÓRIO.

8. Digite a fórmula:

= CONT.SE(VENDAS!B3:B22;"Informática")

Como a palavra Informática, que é o critério para a função, está na célula D1 da planilha RELATÓRIO, a fórmula também poderia ser escrita da seguinte forma:

= CONT.SE(VENDAS!B3:B22;RELATÓRIO!D1)

Função CONT.SES

Essa função aplica critérios a células em vários intervalos e conta o número de vezes que todos os critérios são verdadeiros.

(28)

• Sintaxe: CONT.SES(intervalo1; critério1;intervalo2;critério2;....)

• Argumentos:

o Intervalo1: intervalo de células onde será procurado o critério1;

o Critério1: condição que será procurada no intervalo de células intervalo1;

o Intervalo2: intervalo de células onde será procurado o critério2;

o Critério2: condição que será procurada no intervalo de células intervalo2;

Pode-se ter até 127 pares de intervalos/critérios.

1. Você vai calcular quantos são os vendedores ganhadores do prêmio do departamento de Informática. Veja que agora você precisa considerar duas condições: o vendedor está classificado como primeiro e trabalha no departamento de Informática. Selecione a planilha RELATÓRIO e clique na célula E3. 2. Digite:

=CONT.SES(

3. Você deve procurar pela palavra “Informática”. Selecione a planilha VENDAS e selecione o intervalo

B3:B22. Digite ; (ponto e vírgula) e, na planilha RELATÓRIO, clique na célula D1. Digite ; (ponto e

vírgula).

4. Agora você vai procurar pelo número 1 no intervalo D3:D22 (estamos procurando pelos primeiros colocados). Selecione a planilha VENDAS e o intervalo D3:D22. Digite ; (ponto e vírgula) e digite 1. Pressione a tecla ENTER e teremos a seguinte fórmula:

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

Função MÉDIASE

A função MÉDIASE tem como objetivo calcular a média aritmética dos valores indicados de acordo com um determinado critério ou condição.

• Sintaxe: MÉDIASE(intervalo; critérios; intervalo_média)

• Argumentos:

o Intervalo: intervalo de células onde o critério será procurado;

o Critério: condição para definir quais valores serão considerados para o cálculo da média;

o Intervalo_média: intervalo de células que será considerado para calcular a média.

1. Calcule agora a média de vendas para o departamento de Informática. Selecione a célula E4 da planilha

RELATÓRIO e digite a fórmula a seguir. Utilize o método de selecionar com o mouse, como foi feito

para as fórmulas anteriores, para construir a expressão.

= MÉDIASE(VENDAS!B3:B22;RELATÓRIO!D1;VENDAS!C3:C22)

Função MÉDIASES

A função MÉDIASES tem como objetivo calcular a média aritmética em um intervalo atendendo a vários critérios.

(29)

• Sintaxe:

• MÉDIASES(intervalo_média; intervalo_critério1; critério1; intervalo_critério2; critério2;...)

• Argumentos:

o Intervalo_média: intervalo de valores que serão considerados para o cálculo da média.

o Critério1: condição para definir quais valores serão considerados para o cálculo da média;

o Critério2: condição para definir quais valores serão considerados para o cálculo da média.

1. Calcular a média aritmética das vendas dos vendedores do departamento de Informática e que não são ganhadores do prêmio. Na célula E5 da planilha RELATÓRIO digite a fórmula:

= MÉDIASES(VENDAS!C3:C22;VENDAS!B3:B22;RELATÓRIO!D1;VENDAS!D3:D22;"<>1") Os critérios informados nessa fórmula são os seguintes:

• VENDAS!C3:C22 – intervalo de valores que serão considerados no cálculo da média;

• VENDAS!B3:B22 – intervalo onde será procurado o primeiro critério (Departamento);

• RELATÓRIO!D1 – endereço da célula que contém o primeiro critério (Informática);

• VENDAS!D3:D22 – intervalo onde será procurado o segundo critério (Colocação);

• “<>1” – segundo critério, vendedores que não estão na posição 1.

2. Preencha, utilizando o mesmo raciocínio utilizado para as informações do departamento de Informática, os quadros relativos aos departamentos de Eletrodomésticos e Livraria da planilha

RELATÓRIOS.

(30)

(31)

(32)

Atividade 3 – Utilizando Funções Lógicas

Objetivos : • Utilizar as funções lógicas do Excel 2010.

Tarefas : • Calcular o valor do prêmio para cada um dos vendedores ganhadores. • Atribuir um conceito de acordo com o valor das vendas de cada vendedor. • Distribuir um prêmio extra entre os vendedores que não alcançaram 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 Prêmio.xlsx, você ainda tem algumas tarefas a realizar:

• Distribuir o prêmio 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 prêmio extra aos vendedores que não se classificaram em primeiro lugar, mas que também se destacaram de acordo com alguns critérios estabelecidos.

Função SE

Essa função tem como objetivo efetuar testes condicionais com valores e fórmulas permitindo a escolha do que fazer de acordo com o resultado do teste, que pode ser Falso ou Verdadeiro.

• Sintaxe:

o SE(teste_lógico; valor_se_verdadeiro; valor_se_falso)

• Argumentos:

o Teste_lógico: condição que será avaliada, trazendo Falso ou Verdadeiro como resultado;

o Valor_se_verdadeiro: o que fazer se o resultado do teste_lógico for Verdadeiro;

o Valor_se_falso: o que fazer se o resultado do teste_lógico for Falso.

1. Selecione a planilha VENDAS da pasta Prêmios.xlsx.

Você deve verificar na célula E3 se o vendedor está em primeiro lugar na classificação e, se estiver, calcular e mostrar o seu prêmio. Se o vendedor não estiver em primeiro lugar, o valor que deve ser colocado na célula é zero. Lembre-se que o prêmio total deve ser dividido igualmente entre os vendedores ganhadores. O raciocínio que vamos seguir é o seguinte:

a) SE classificação = 1

b) ENTÃO dividir o valor do prêmio pelo número de ganhadores e mostrar na célula

c) SENÃO mostrar o valor 0 na célula

2. Clique na célula E3 e digite o seguinte:

(33)

Observe que na célula D3 está a classificação do vendedor, na célula B1 está valor do prêmio total e na célula B10 da planilha RELATÓRIO está o número de ganhadores.

3. Copie a fórmula para todos os vendedores. Não se preocupe com os erros apontados nas células E8 e E17. Mais adiante você vai modificar as fórmulas para tratar adequadamente esses erros.

4. Agora você vai colocar uma mensagem para cada vendedor de acordo com os seguintes critérios:

CONCEITO CONDIÇÃO

EXCELENTE Colocação 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 condições anteriores

Observe que existem quatro condições a serem verificadas. Isso não será possível através de uma única função SE. Nesse caso, você terá que aninhar funções SE para resolver o problema. Até 64 funções SE podem ser aninhadas no Excel 2010.

O raciocínio que você deve seguir é o seguinte:

SE colocação do vendedor = 1

ENTÃO atribuir conceito “EXCELENTE”

SENÃOSE valor das vendas maior do que a meta

ENTÃO atribuir conceito “ÓTIMO”

SENÃOSE valor das vendas igual à meta

ENTÃO atribuir conceito “BOM”

SENÃO atribuir conceito “PODE MELHORAR”

5. Clique na célula F3 e digite a seguinte fórmula:

= SE(D3=1;"EXCELENTE";SE(C3>$D$1;"ÓTIMO";SE(C3=$D$1;"BOM";"PODE MELHORAR"))) 6. Copie a fórmula para todos os vendedores.

7. Feche a pasta Premio.xlsx.

Função E

Essa função retorna o valor Verdadeiro se todos os seus argumentos forem verdadeiros. • Sintaxe:

o E(lógico1; lógico2;...)

• Argumentos:

o Lógico1: condição que será avaliada, trazendo Falso ou Verdadeiro como resultado;

(34)

Em uma função E você pode colocar até 255 argumentos, ou seja, pode-se ter até 255 condições lógicas para avaliar.

Função OU

Essa função retorna o valor Verdadeiro se pelo menos um de seus argumentos for verdadeiro. • Sintaxe:

o OU(lógico1; lógico2;...)

• Argumentos:

o Lógico1: condição que será avaliada, trazendo Falso ou Verdadeiro como resultado;

o Lógico2;...: condições que serão avaliadas, trazendo Falso ou Verdadeiro como resultado.

Em uma função OU você pode colocar até 255 argumentos, ou seja, pode-se ter até 255 condições lógicas para avaliar.

As funções E e OU são normalmente utilizadas para dar mais flexibilidade a outras funções que executam testes lógicos, como por exemplo, em conjunto com a função SE.

1. Abra a pasta Estado civil.xlsx. Você vai verificar a diferença dos resultados entre as funções E e OU.

2. Clique na célula D3. Nela deve-se mostrar o valor VERDADEIRO se a pessoa for solteira e sua idade for maior ou igual a 22 anos. Caso contrário, deverá ser mostrado o valor FALSO.

= E(B3="Solteiro(a)";C3>=22) 4. Copie a fórmula para todas as pessoas.

5. Clique na célula 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 contrário, deverá ser mostrado o valor FALSO.

(35)

= OU(B3="Solteiro(a)";C3>=22) 7. Copie a fórmula para todas as pessoas.

Note que no caso da função E, o resultado só é verdadeiro se as duas condições forem verdadeiras. No caso da função OU, o resultado é verdadeiro se pelo menos uma das condições for verdadeira, e somente será falso se as duas condições forem falsas.

8. Feche a pasta Estado civil.xlsx e abra a pasta Prêmio.xlsx.

9. A empresa resolveu premiar também os funcionários que obtiveram conceito ÓTIMO com uma viagem para Natal (RN) e os funcionários que venderam menos ou o valor da meta, mas que venderam mais do que R$ 300.000,00, com um jantar. Considere o seguinte raciocínio:

SE conceito = ÓTIMO

ENTÃO prêmio extra = Viagem para Natal

SENÃOSE vendas <= meta E vendas > 300000

ENTÃO prêmio extra = Jantar

10. Clique na célula G3 e digite o seguinte:

= SE(F3="ÓTIMO";"Viagem para Natal";SE(E(C3<=$D$1;C3>300000);"Jantar";""))

Observe que se nenhuma das condições for verdadeira, o valor “” será colocado na célula, ou seja, a célula ficará vazia para evitar que seja mostrada a mensagem FALSO.

(36)

Função SEERRO

Essa função tem como objetivo retornar um valor especificado se uma fórmula gerar um erro. Se não existir erro, a função retorna o resultado da fórmula.

• Sintaxe:

o SEERRO(fórmula; valor_se_erro)

• Argumentos:

o Fórmula: fórmula que se deseja verificar quanto a erros;

o Lógico2;...: valor a ser retornado se a fórmula gerar um erro.

Os seguintes tipos de erro são considerados: #N/D, #VALOR!, #REF!, #DIV/0!, #NÚM!, #NOME? ou #NULO!.

12. As fórmulas da coluna COLOCAÇÃO estão gerando erros nos casos em que a célula correspondente a venda está vazia. Como é um fato que pode acontecer, é aconselhável não mostrar a mensagem de erro gerada pelo Excel, mas sim armazenar na célula um valor mais adequado. Nesse caso, se existir o erro na célula, você deve deixá-la vazia, ou seja, armazenar “”. Clique na célula D3 e modifique a fórmula para:

=SEERRO(ORDEM.EQ(C3;$C$3:$C$22);"") Observe que todos os erros da planilha foram solucionados.

É necessário saber qual valor deve-se colocar na célula nos casos de erro para não ocasionar novos erros.

(37)

(38)

Atividade 4 – Manipulando Datas e Horas

Objetivos : • Formatar datas.

• Utilizar funções de datas e horas. • Efetuar cálculos com datas e horas.

Tarefas : • Formatar uma data para que seja mostrado o dia da semana. • Formatar uma data para que seja mostrado o mês.

• Formatar uma data para que seja mostrado o dia, mês e ano.

• Utilizar as funções de data para mostrar a data de hoje, o ano, dia e mês de uma data, o número do dia da semana.

• Projetar dias úteis a partir de uma data.

• Calcular o número de dias úteis entre duas datas. • Projetar dias corridos a partir de uma data.

• Calcular o número de dias corridos entre duas datas. • Calcular o número de horas e minutos entre dois horários. • Aplicar formatação condicional nas planilhas.

Em muitas planilhas é necessário o trabalho com datas e horas, efetuando cálculos e projeções. O Excel 2010 oferece várias funções e opções de formatação que podem ser aplicadas para as mais variadas necessidades.

Formatação de DATAS

(39)

2. Copie o conteúdo da coluna A para as demais colunas.

3. Na coluna B você vai mostrar o número serial que representa a data. Esse número é o número de dias transcorridos entre a data 01/01/1900 e a data considerada. Selecione o intervalo B2:B32.

4. Na guia Página Inicial, grupo Número, clique na seta do comando Formato de número.

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 Número da guia Página Inicial.

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

(40)

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 são formados por um algarismo são exibidos com um algarismo.

• DD dias que são formados por um algarismo são exibidos com dois algarismos, tendo o zero como inicial.

• DDD exibe o nome do dia de forma abreviada, através das três 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 mês por extenso. Selecione o intervalo D2:D32. 12. Clique na seta do grupo Número da guia Página Inicial.

13. Na guia Número, selecione a categoria Personalizado. 14. Apague o que está no campo Tipo e digite mmmm.

Para o caso de datas, considerando o mês da data, temos os seguintes formatos personalizados: • M meses que são formados por um algarismo são exibidos com um algarismo.

• MM meses que são formados por um algarismo são exibidos com dois algarismos, tendo o zero como inicial.

• MMM exibe o nome do mês de forma abreviada, através das três primeiras letras. • MMMM exibe o nome do mês por extenso.

(41)

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 espaço, vírgula, espaço

dd

número do dia

“ de “

coloca espaço, a palavra “de”, espaço

mmmm

nome do mês por extenso

“ de “

coloca espaço, a palavra “de”, espaço

aaa

número do ano

No caso do ano, a representação aa formata a data para que apareçam os dois últimos dígitos do ano e a representação aaa formata a data para que apareçam os quatro dígitos do ano.

(42)

Você deve ter em mente a diferença entre conteúdo e formato. Conteúdo é o dado armazenado na célula, e formato é a forma como ele está sendo mostrado. Quando você efetuar cálculos com células formatadas, é o conteúdo que será trabalhado, e não o formato.

FUNÇÕES DE DATA

Função HOJE

A função HOJE retorna a data de hoje formatada como data.

• Sintaxe: HOJE()

Essa função não possui argumentos. No entanto deve-se digitar os parênteses.

Função ANO

Essa função 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.

Função MÊS

Essa função retorna o número do mês da data informada. O mês é retornado como um inteiro no intervalo de 1 a 12.

• Sintaxe: MÊS(data_informada)

• Argumentos: data_informada: data que se deseja retornar o mês.

Função DIA

Essa função 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.

Função DIA.DA.SEMANA

Essa função retorna o número do dia da semana da data informada. O dia é retornado como um inteiro, variando de 1 (domingo) a 7 (sábado), por padrão.

• Sintaxe:

o DIA.DA.SEMANA(data_informada;tipo)

• Argumentos:

o data_informada: data que se deseja retornar o número do dia da semana.

o tipo: número que determina o tipo do valor retornado. Este argumento é opcional e pode ter os seguintes valores:

(43)

TIPO VALOR RETORNADO 1 ou omitido Números 1 (domingo) a 7 (sábado)

2 Números 1 (segunda-feira) a 7 (domingo) 3 Números 0 (segunda-feira) a 6 (domingo) 11 Números 1 (segunda-feira) a 7 (domingo) 12 Números 1 (terça-feira) a 7 (segunda-feira) 13 Números 1 (quarta-feira) a 7 (terça-feira) 14 Números 1 (quinta-feira) a 7 (quarta-feira) 15 Números 1 (sexta-feira) a 7 (quinta-feira) 16 Números 1 (sábado) a 7 (sexta-feira) 17 Números 1 (domingo) a 7 (sábado) 1. Abra a pasta “Datas Funções.xlsx”.

2. Clique na célula B1.

3. Coloque nessa célula a função para mostrar a data de hoje. Digite: = HOJE()

5. Coloque nessa célula a função para mostrar o dia da data de hoje. Digite: = DIA(B1)

7. Coloque nessa célula a função para mostrar o número do mês da data de hoje. Digite: = MÊS(B1)

(44)

9. Coloque nessa célula a função para mostrar o ano da data de hoje. Digite: = ANO(B1)

11. Coloque nessa célula a função para mostrar o número do dia da semana da data de hoje, considerando 1 como domingo e 7 como sábado. Digite:

= DIA.DA.SEMANA(B1)

Função DIATRABALHO.INTL

Essa função 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 período.

• 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: número 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 são considerados como pertencentes ao final de semana. Pode ter os seguintes valores:

FIM_DE_SEMANA DIAS DE FIM DE SEMANA 1 ou omitido Sábado, Domingo

2 Domingo, Segunda 3 Segunda-feira, Terça-feira 4 Terça-feira, Quarta-feira 5 Quarta-feira, Quinta-feira 6 Quinta-feira, Sexta-feira 7 Sexta-feira, Sábado 11 Apenas Domingo 12 Apenas Segunda-feira 13 Apenas Terça-feira 14 Apenas Quarta-feira 15 Apenas Quinta-feira

(45)

16 Apenas Sexta-feira

17 Apenas Sábado

• feriados: argumento opcional que é um conjunto de uma ou mais datas que serão excluídas do

calendário de dias úteis. 1. Abra a pasta Caixa.xlsx.

2. Nela você encontra duas planilhas: Lançamentos e Feriados. Na planilha Lançamentos você fará a projeção dos dias úteis para o mês de Abril/2010.

3. Clique na célula B3 e digite a data 01/04/2010.

4. Formate a célula B3 para que apareça somente o nome do mês.

5. Agora você vai projetar o primeiro dia útil a partir do último dia do mês de março. Essa data é o dia anterior da data 01/04/2010 que está armazenada na célula B3, ou seja, é o conteúdo da célula B3 – 1. Clique na célula 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 sábado e domingo. Logo, o parâmetro que deve ser informado para fim_de_semana é 1. Continue digitando 1;

(46)

8. Na planilha Feriados, na coluna A, estão os feriados do mês de abril de 2010. Selecione a planilha

Feriados e clique sobre a indicação da coluna A. Marque a coluna inteira para evitar refazer as fórmulas

caso seja inserido algum feriado não previsto. Pressione ENTER e terá a seguinte fórmula na célula: = DIATRABALHO.INTL(B3-1;1;1;Feriados!A:A)

9. Agora você vai calcular o próximo dia útil a partir da célula A6. Clique na célula A7 e digite a seguinte fórmula:

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

10. Copie a fórmula para as células abaixo e você terá uma lista de dias úteis no mês de abril de 2010.

11. Feche a pasta Caixa.xlsx.

Função DIATRABALHOTOTAL.INTL

Essa função retorna o número de dias úteis entre duas datas usando parâmetros para indicar quais e quantos dias são 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 são considerados como pertencentes ao final de semana. Pode ter os seguintes valores:

(47)

FIM_DE_SEMANA DIAS DE FIM DE SEMANA 1 ou omitido Sábado, Domingo

2 Domingo, Segunda 3 Segunda-feira, Terça-feira 4 Terça-feira, Quarta-feira 5 Quarta-feira, Quinta-feira 6 Quinta-feira, Sexta-feira 7 Sexta-feira, Sábado 11 Apenas Domingo 12 Apenas Segunda-feira 13 Apenas Terça-feira 14 Apenas Quarta-feira 15 Apenas Quinta-feira 16 Apenas Sexta-feira 17 Apenas Sábado

o feriados: argumento opcional que é um conjunto de uma ou mais datas que serão

excluídas do calendário de dias úteis.

Seu trabalho neste momento é calcular o salário que deve ser pago por uma empresa aos seus estagiários. Esse controle é efetuado através da planilha Pagamento Estagiarios.xlsx.

1. Abra a pasta Pagamento Estagiarios.xlsx.

2. O primeiro passo é calcular quantos são os dias de vigência do contrato, ou seja, quantos dias corridos existem entre a data de início e a data de término do contrato. Para saber quantos dias existem entre duas datas, basta subtrair a data inicial da data final. Dessa forma, selecione a célula F4 e digite:

= D4-C4 3. Copie a fórmula para todos os estagiários.

4. No entanto, o salário é pago considerando o número de dias a trabalhar no período, ou seja, o número de dias úteis do contrato, e não o número de dias corridos. Clique na célula G4 e digite a fórmula:

(48)

Onde:

C4 data inicial

D4 data final

1 final de semana formado por sábado e domingo

Feriados!A:A lista de feriados no ano de 2010

5. Copie a fórmula para todos os estagiários.

6. Agora calcule quanto cada estagiário deve receber, sabendo que o valor pago por hora está armazenado na célula C14. Clique na célula H4 e digite a fórmula:

= E4*G4*$C$14 7. Copie a fórmula para todos os estagiários.

8. A empresa percebeu que alguns contratos teriam que ser prorrogados. Foi então 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 cálculo das novas datas de término dos contratos para que o cálculo dos salários fique correto. Insira duas novas colunas na planilha antes da coluna Carga horária diária:

9. Digite os valores mostrados na coluna Prorrogação (dias). Se quando digitar o valor aparecer uma data, formate os valores como números.

(49)

10. Se você tiver uma data e somar um número a essa data, você terá a data correspondente à data inicial mais o número de dias somado. Portanto, para calcular a data efetiva de término do contrato, temos que adicionar o número de dias de prorrogação do contrato à data de término do contrato. Clique na célula F4 e digite a fórmula:

= D4+E4 11. Copie a fórmula para todos os estagiários.

12. Agora é necessário recalcular os dias de vigência do contrato e os dias a trabalhar no período, pois a data final do contrato é a nova data calculada, que se encontra na coluna Data de término efetiva. Refaça as fórmulas e confira o resultado:

13. Feche e pasta Pagamento Estagiários.xlsx.

14. Agora você vai efetuar cálculos 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 funcionário deve receber em função do tempo trabalhado nos dias úteis de fevereiro de 2010 e do valor que deve receber por hora.

(50)

16. Formate a célula para aparecer o nome do mês.

17. A partir da célula A5 preencha até a célula A23 com os dias úteis de fevereiro. Utilize o mesmo raciocínio do exercício anterior. Os feriados de fevereiro estão na planilha Feriados.

18. Copie os dados do intervalo A5:A23 para o intervalo B5:B23. Tenha o cuidado de copiar os valores, e não as fórmulas.

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 horário de entrada do horário de saída. Para isso, clique na célula E5 e digite:

= D5-C5 21. Copie a fórmula para todo o intervalo.

Função HORA

Essa função 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: horário que contém a hora que desejamos encontrar.

Função MINUTO

Essa função retorna os minutos de um valor de tempo. O valor retornado é um número inteiro que varia de 0 a 59.

• Sintaxe: MINUTO(valor_tempo)

• Argumento: valor_tempo: horário que contém os minutos que desejamos encontrar.

1. Calcule quantas horas foram trabalhadas em cada dia. Clique na célula F5 e digite a seguinte fórmula:

2. = HORA(E5)

3. Copie a fórmula para todos os dias. Se o valor para número de horas estiver aparecendo como um valor do tipo hora, formate para que seja mostrado como número.

4. Para calcular o número de minutos, além das horas cheias, clique na célula G5 e digite a seguinte fórmula:

5. = MINUTO(E5)

6. Copie a fórmula para todos os dias. Se o valor para número de minutos estiver aparecendo como um valor do tipo hora, formate para que seja mostrado como número.

7. Para calcular o valor a pagar você deve multiplicar o número de horas pelo valor por hora e somar com a multiplicação do número de minutos pelo valor por hora dividido por 60. Clique na célula I5 e digite a fórmula:

= F5*$I$2+G5*$I$2/60 8. Copie a fórmula para todos os dias.

(51)

Formatação CONDICIONAL

A formatação 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 célula para que os dados que satisfazem critérios 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 célula é uma data, e não o nome do dia da semana. Para obter o número do dia da semana da data armazenada, você deve utilizar a função DIA.DA.SEMANA vista anteriormente. Selecione o intervalo B5:B23.

2. Na guia Página Inicial, grupo Estilo, clique em Formatação Condicional e, no menu apresentado, selecione Nova regra.

3. Na janela exibida, selecione Usar uma fórmula para determinar quais células devem ser formatadas. 4. No campo Formatar valores em que essa fórmula é verdadeira, digite a fórmula:

(52)

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

Lembre-se que a função DIA.DA.SEMANA traz como retorno o número do dia da semana, de acordo com o tipo de semana indicado na função. 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 botão Formatar e escolha a cor vermelha e negrito. 6. Clique em OK e, novamente em OK.

(53)

Exercício Proposto

1. Abra a planilha Faculdade.xlsx.

Siga os procedimentos descritos para efetuar os cálculos: a) Selecione a planilha Boletim.

b) Calcule a média dos alunos. Utilize a função de tratamento de erros se for necessário em todos os cálculos que serão efetuados.

c) Preencha a coluna MENSAGEM MÉDIA considerando a seguinte condição: se a média for maior ou igual a 5, a mensagem deve ser "Média OK", caso contrário deve ser "Média abaixo do limite". d) Preencha a coluna MENSAGEM FALTAS considerando a seguinte condição: se o número de faltas

for menor ou igual a 25% das aulas dadas, a mensagem deve ser "Faltas OK", caso contrário deve ser "Faltas acima do limite".

e) Calcule o RANKING DE MÉDIAS classificando os alunos por média.

f) Preencha a coluna RESULTADO da seguinte forma: se a média está OK E faltas também OK, então colocar “Aprovado”, caso contrário 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 informações: número total de alunos, maior e menor média, número de aprovados e de reprovados, número de reprovados por média, por falta, por média e por falta, número de alunos que não entregaram o exercício, número de alunos que entregaram o exercício, média das médias dos alunos aprovados, média de faltas dos alunos com média OK e reprovados.

(54)

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 mês 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.

(55)

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

a) Calcular o Saldo Atual.

b) A coluna Situação deve ser preenchida da seguinte forma: se o cliente está com saldo positivo e a média 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 contrário deve ser apresentada a mensagem “Cheque Comum.

c) A coluna Limite do Cheque Especial deve ser preenchida da seguinte forma: se o cliente não tiver cheque especial, a célula deve ser preenchida com zero; se o cliente tiver Cheque Especial e se o total de créditos for menor ou igual a R$ 3.000,00, o valor do cheque especial será de R$ 2.000,00, caso contrário (cliente com cheque especial e o total de créditos maior do que R$ 3.000,00) o valor do cheque especial será de R$ 5.000,00.

d) Contar o número de clientes que receberam cheque especial.

(56)

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 Relatório contando o número de alunos do sexo feminino, masculino, matriculados em biologia, educação física, história, letras, matemática e química e o número de alunos com idade maior do que 50 anos, menor do que 25 anos, maior de 40 anos e maior do que 30 anos.

(57)

a) A coluna Comissão deve ser preenchida como sendo o valor da comissão sobre o valor total das vendas.

b) A coluna Salário Total deve ser preenchida com o valor da comissão mais o valor do salário mínimo se o vendedor atingiu um volume de vendas maior ou igual a R$ 10.000,00. Caso contrário, a célula deve ser preenchida com o valor do salário mínimo.

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

(58)

OBJETIVOS



Utilizar as funções de pesquisa PROCV, PROCH, CORRESP e

ÍNDICE.

(59)

(60)

Funções de PESQUISA

As funções de pesquisa do Excel são poderosas ferramentas que permitem a busca de dados de forma rápida, muitas vezes evitando o uso de várias funções SE aninhadas.

Atividade 1 – Utilizando as funções PROCV e PROCH

Objetivos : • Utilizar as funções PROCV e PROCH.

Tarefas : • Abrir a pasta Relatório de Vendas.xlsx.

• Através da utilização da função PROCV, preencher a planilha Relatório com os nomes dos funcionários, comissões, salário fixo e imposto.

• Calcular o valor a receber para cada funcionário.

• Através da utilização da função PROCH, preencher a coluna Classificação faixas com as devidas faixas.

Função PROCV

A função PROCV permite procurar por um valor na primeira coluna de um intervalo de células e trazer outro valor da mesma linha em outra coluna especificada.

• Sintaxe:

o PROCV(valor_procurado;intervalo_células;núm _coluna,[procurar_intervalo]) • Argumentos:

o valor_procurado: valor a ser procurado na primeira coluna do intervalo de células. Pode

ser um valor ou uma referência.

o intervalo_células: intervalo de células que contém os dados. Pode ser uma referência a um

intervalo ou um nome de intervalo. Os valores na primeira coluna de intervalo_células são os valores procurados por valor_procurado e podem ser texto, números ou valores lógicos. Não existe distinção entre maiúsculas e minúsculas.

o núm_coluna: número da coluna do intervalo de células que contém a informação que deve

ser retornada.

o procurar_intervalo: argumento opcional que corresponde a um valor lógico e que indica de

que forma o valor será procurado na primeira coluna do intervalo, se a correspondência deverá ser exata ou aproximada.

Se este valor for VERDADEIRO ou omitido, a correspondência se dará de forma exata ou aproximada. Neste caso, os valores da primeira coluna do intervalo deverão estar classificados em ordem ascendente, ou a função poderá retornar valores indevidos.

Se o valor do argumento for FALSO, a correspondência se dará de forma exata. Neste caso, os valores da primeira coluna do intervalo não precisam estar classificados e, se houver mais de um valor que coincida com o valor_procurado, será considerado o primeiro encontrado.

1. Abra a pasta Relatório de Vendas.xlsx. Selecione a planilha Relatório.

2. Preencha a coluna NOME DO FUNCIONÁRIO com os nomes dos funcionários, procurando-os na planilha Suporte. Clique na célula B2 e digite:

(61)

Você iniciou a digitação da função PROCV informando o primeiro argumento, que é o que se deseja procurar. No caso, você deve procurar pelo código do funcionário na planilha Suporte.

3. Selecione a planilha Suporte. Como o mouse clicado, selecione a região A2:B23. Essa região é a que contém o valor a ser procurado (o código do funcionário, que está na primeira coluna do intervalo) e o valor a ser retornado (o nome do funcionário, que está na segunda coluna do intervalo). A fórmula fica da seguinte forma:

= PROCV(A2; Suporte!$A$2:$B$23;

Esse argumento indica qual intervalo de células será considerado para a procura e retorno dos dados. Observe que o intervalo A2:B23 da planilha Suporte foi fixado, pois não pode ser modificado quando você copiar a fórmula para as células de baixo. Outra forma de evitar que os endereços sejam modificados na cópia é atribuir um nome ao intervalo.

4. Digite 2 e pressione a tecla ENTER. O parâmetro 2 indica que o valor que se deseja que retorne é o que está na coluna 2 do intervalo de células indicado.

= PROCV(A2;Suporte!$A$2:$B$23;2) 5. Copie a fórmula para todos os funcionários.

6. Agora calcule o valor da comissão, salário fixo e impostos para cada funcionário. Esses valores estão em

Suporte e dependem do valor da venda efetuada pelo funcionário. Atribua um nome à região da

planilha Suporte onde será procurado o valor da venda e retornados os valores desejados para facilitar a digitação das fórmulas. Selecione a planilha Suporte e selecione o intervalo E2:H7.

7. Na guia Fórmulas, grupo Nomes definidos selecione Definir Nome. Na janela apresentada digite

Valor_Vendas e clique em OK.

8. Você vai procurar pelo valor da venda na região Valor_Vendas e trazer o percentual de comissão. Esse percentual é obtido pela procura do valor da venda na região Valor_Vendas e trazendo o conteúdo da célula que está na coluna 2. O valor da comissão deve ser calculado como sendo o percentual de comissão multiplicado pelo valor da venda. Selecione a planilha Relatório. Clique na célula D2 e digite a seguinte fórmula:

= PROCV(C2;Valor_Vendas;2) * C2 9. Copie a fórmula para todos os vendedores.