Excel2007. Avançado. ValériaPiccoliGonzales

Ex

c

el

2007

A

DMINISTRAÇÃO

R

EGIONAL DO

S

ENAC NO

E

STADO DE

S

ÃO

P

AULO

Gerência de Desenvolvimento

Sidney Zaganin Latorre

Coordenação Técnica

Richard Martelli

Apoio Técnico

Fábio Gomes Pereira

Revisão Técnica

Quéops Design S/C Ltda.

© Senac São Paulo 2007

1ª. edição

Elaboração do material didático

Valéria Piccoli Gonzales

Edição e Produção

E

XCEL

2007

-

A

VANÇADO

 

Senac São Paulo I

Sumário

Capítulo 1 - Importação de Dados para o Excel ... 1

Importando arquivos de texto ... 3

Atividade 1 – Importando arquivos de texto para o Excel ... 3

Importando arquivos de dados ... 11

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

Atividade 3 – Criando consultas de base de dados dentro do Excel ... 15

Vinculando dados do excel no Access ... 21

Atividade 4 – Vinculando dados do excel no Access ... 21

Capítulo 2 - Filtro, Classificação de Dados e Filtro Avançado ... 27

Filtro e Classificação de Dados ... 29

Atividade 1 – Conhecendo o filtro e a classificação ... 29

Atividade 2 – Classificação ... 35

Atividade 3 – Classificação com duas chaves ... 39

Atividade 4 – Classificação por cores ... 41

Filtro Avançado ... 45

Atividade 5 – Conhecendo o filtro avançado ... 45

Capítulo 3 - Funções de Texto e Bancos de Dados ... 53

Funções de Texto ... 55

Atividade 1 – Aplicando as funções de texto ... 55

Atividade 2 – Copiando fórmulas e planilhas ... 63

Funções de Banco de Dados ... 67

Atividade 3 – Conhecendo as funções de banco de dados ... 67

Capítulo 4 - Tabela Dinâmica e Consolidação de Dados ... 79

Tabela Dinâmica ... 81

Atividade 1 – Criando o relatório de tabela dinâmica ... 81

Atividade 2 – Navegando em nossa tabela dinâmica ... 89

Atividade 3 – Alterando o layout da tabela dinâmica ... 91

Atividade 4 – Mostrando o gráfico dinâmico a partir de uma tabela dinâmica ... 93

Atividade 5 – Inserindo e alterando informações ... 97

Atividade 6 – Usando mais de um campo como linha e arrumando os subtotais ... 103

Atividade 7 – Mostrando o campo página da tabela dinâmica separadamente ... 107

Atividade 8 – Agrupando dados de uma tabela dinâmica ... 109

Atividade 9 – Consolidando planilhas pelo menu ... 111

Atividade 10 – Estrutura de tópicos ...117

Atividade 11 – Criando subtotais ... 121

Capítulo 5 - Validação de Dados ... 131

Validação de Dados ... 133

Atividade 1 – Conhecendo a validação de dados ... 133

Atividade 2 – Conhecendo a guia mensagem de entrada e alerta de erro ... 143

Capítulo 6 - Funções Matemáticas, Estatísticas, Informação e Datas ... 149

Função Cont.se ... 151

Atividade 1 – Conhecendo a função Cont.se ... 151

Função SOMASE ... 159

Atividade 2 – Conhecendo a função SOMASE ... 159

Função Contar.vazio ... 163

Atividade 3 – Conhecendo a função Contar.vazio ... 163

Função Cont.num ... 167

Atividade 4 – Conhecendo a função Cont.num ... 167

Função Cont.valores ... 169

Atividade 5 – Função Cont.valores ... 169

Datas ... 171

Atividade 6 – Formatando datas ... 171

Atividade 7 – Projetando dias corridos de uma data ... 179

Atividade 8 – Projetando dias úteis de uma data ... 183

Atividade 9 – Formatação condicional e cálculo de horas ... 187

Capítulo 7 - Funções Lógicas e Condicionais ... 195

Função E ... 197

Atividade 1 – Conhecendo a Função E... 197

Atividade 2 – Conhecendo a Função OU ... 201

Função SE ... 203

Atividade 3 – Conhecendo a Função SE ... 203

Função SE – aninhada com SE e outras funções ... 207

Atividade 4 – Função Se aninhada com Se ... 207

Atividade 5 – Função Se aninhada com OU ... 211

Atividade 6 – Função Se aninhada com E ... 213

Capítulo 8 - Funções de Pesquisa e Auditoria de Fórmulas ... 215

Funções de Pesquisa ... 217

Função Procv e Proch ... 217

Atividade 1 – Usando Procv e Proch ... 217

Função Corresp ... 225

Senac São Paulo III

Função Índice ... 227

Atividade 3 – Usando Índice ... 227

Auditoria de fórmulas ... 229

Atividade 4 – Rastreando os precedentes de uma célula ... 229

Atividade 5 – Rastreando os dependentes de uma célula ... 233

Atividade 6 – Rastreando erro em uma célula ... 235

Capítulo 9 - Usando Pastas de Trabalho em Equipe, Análise de Dados e Simulações ... 237

Usando Pastas de Trabalho em Equipe ... 239

Atividade 1 – Trabalhador em equipe ... 239

Atingir Meta ... 245

Atividade 2 – Usando o recurso atingir meta ... 245

Solver ... 251

Atividade 3 – Conhecendo o recurso Solver ... 252

Capítulo 10 - Cenários ... 257

Cenários ... 259

Atividade 1 – Criando cenários ... 259

Atividade 2 – Exibindo e resumindo cenários ... 269

Atividade 3 – Mesclando cenários... 273

Capítulo 11 – Macros Interativas ... 277

Macros interativas ... 279

Atividade 1 –Criando macros ... 279

Atividade 2 – Criando macros ... 289

Atividade 3 – Atribuindo uma macro a um botão ... 293

Atividade 4 – Atribuindo uma macro a um botão na planilha ... 297

Anexo ... 301

Senac São Paulo 1

OBJETIVOS

ƒ Conhecer a forma de como o Excel recebe e interpreta arquivos

texto

ƒ Criar consultas à base de dados dentro do Excel

ƒ Conhecer a forma como o Access interpreta a planilha e

Senac São Paulo

Importando a

Para fazer a impor pois o Excel não a antes de iniciar o p Parece um tanto e em seus sistemas a outras versões. D relatórios para adm

Atividade 1 –

Objetivo : •I

Tarefa : •I

Nesta atividade vo nomes dos funcio localizada na cidad passo é importar e 1. Abra uma nova

Para quem está c , localizado n 2. Na barra de fe

3. Será exibida a j

arquivos de texto

rtação de arquivos de texto no Excel, é preciso s ceita extensões .doc ou .pdf. Portanto, faça a co processo de importação.

estranho tal importação, mas hoje em dia uma b a funcionalidade de exportar a base de dados para Desse forma a importação para o Excel facilita ministração e gerenciamento destas informações.

– Importando arquivos de texto para

Importar o arquivo base.txt para o Excel. Importar um arquivo de texto

ocê utilizará o arquivo base.txt disponível na past onários de todas as empresas de um grupo e de São Paulo. Para que você possa elaborar rel este arquivo para o Excel, e assim dar continuidad

a pasta de trabalho em branco:

chegando agora na versão 2007, para incluir uma na parte inferior da pasta de trabalho do Excel

rramentas dados, no grupo obter dados externos

janela a seguir:

3

salvar esses arquivos como txt, onversão dos arquivos para .txt boa parte das empresas possui a .txt, .mdb ou até mesmo em a cálculos e a elaboração de

a o Excel

ta Treinamento. Ele contém os empresarial, cuja matriz está latórios e consultas, o primeiro de às próximas etapas.

a planilha basta clicar no botão:

4. Na caixa Examinar:, localize a pasta treinamento, e dentro dela o arquivo base.txt. Clique sobre o arquivo para selecioná-lo.

Senac São Paulo 5

Nesta janela é possível se fazer as seguintes configurações:

• Delimitado: utilizado para separar as colunas usando como critério a vírgula, a tabulação ou algum outro delimitador.

• Largura Fixa: utilizado para especificar as colunas no lugar onde for necessário.

• Iniciar Importação na linha: permite escolher a partir de que linha do arquivo de texto a importação será feita. O padrão é linha 1.

• Origem do Arquivo: permite a escolha do sistema operacional e idioma. A melhor alternativa é deixar a padrão.

6. Mantenha os padrões mostrados na figura anterior, e clique em Avançar. Será exibida a janela a seguir:

No item Delimitadores você esc de outra. Por exemplo, se entre que cada palavra ficará em uma c

Como saber se o texto a se que é um símbolo de form dentro do Word, vá ao meu parágrafo, como mostrado a

Dessa forma, no Word, serão ex Outra opção bastante útil é a ativada, por exemplo, se existir esta opção não é acionada, se e dentro da base de dados. 7. Mantenha o delimitador Tab

como um só”.

8. Clique em avançar e será exi

Nesta etapa você pode especifi coluna na visualização de dados desejado. Você pode simplesme padrão, e tem a finalidade de co demais em texto.

9. Clique em Concluir e será ex

colhe os caracteres que serão utilizados para sep e duas palavras existir um tab (tabulação) para se

coluna.

er importado está usando tabulação como matação que sempre fica oculto? Abra o u Início, grupo parágrafo e pressione a tecla

a seguir:

xibidas as marcas de tabulação através do símbolo Considerar delimitadores consecutivos como u dois delimitadores seguidos será considero apen existir mais de um delimitador serão geradas co bulação e ative a opção “Considerar delimitado

bida a janela a seguir:

car o formato de dados de cada coluna, clican , e selecionando em “Formato dos dados da co ente clicar em concluir, pois a opção Geral é onverter valor numérico em número, valor de da

xibida a janela a seguir.

parar uma coluna pará-las, significa delimitador, já arquivo texto a de marcas de o: um só. Quando

nas um. Quando lunas em branco res consecutivos ndo em qualquer oluna” o formato selecionada por ata em data e os

Senac São Paulo 7

A janela Importar dados exibe a célula onde será iniciada a importação. Você pode clicar no botão ao lado da caixa para definir uma outra célula, ou ainda selecionar a opção “Na nova planilha” o que fará a importação numa planilha em branco.

10. Selecione a célula A1, caso não esteja selecionada, e clique em OK. O resultado deverá ser o mostrado a seguir:

Após a importação, qualquer alteração ou inclusão deverá ser feita dentro da base.txt, por ser o arquivo principal que deu origem à planilha da figura anterior.

11. Abra o arquivo base.txt no Bloco de Notas ou outro editor que melhor lhe convier, e faça as alterações a seguir:

Nome de Origem

Nome Alterado

Douglas Francisco Douglas Piccoli

Fernando Abreu Florêncio Flores

Thais Flores Tatiana Araújo

Camila Piccoli Adriana da Silva

Caso você tenha fechado o Ex arquivo no Excel a planilha já es 13. Na barra de ferramentas dad

janela a seguir:

Esta janela é aberta pois, podem que você confirma o arquivo em 14. Selecione o arquivo base.txt,

15. Salve a pasta de trabalho com e encerre o Excel.

16. Abra novamente o arquivo q

xcel antes de fazer as alterações no arquivo de t stará atualizada.

dos, no grupo conexões, clique em atualizar tudo

m haver várias importações em uma mesma pasta questão para atualização.

e clique em importar para prosseguir. Veja o res

m o nome de Base de dados, pois será usada nas e ue foi salvo, e verifique a mensagem a seguir:

texto, ao abrir o , e será exibida a

a, e é nesta janela sultado.

Senac São Paulo 9

17. Esse aviso aparece como uma forma de segurança. Ele lhe avisa que existe um vínculo externo caso não fosse você que estivesse utilizando o arquivo. Clique em opções e será exibida a tela a seguir.

18. Selecione Habilitar este conteúdo e clique em OK. Dessa forma você terá o vínculo disponível para eventuais atualizações.

Senac São Paulo

Importando a

Para fazer import access, salvos com dados , e vale a pe mesmo pacote do

Atividade 2 –

Objetivo : • Im Tarefa : • Im A seguir temos o Existem duas ma abordaremos a imp Agora que já comp 1. Abra uma nova

2. Vá a guia Dado

3. Dentro da opç

arquivos de dados

ações de arquivos de dados para o Excel, vam m a extensão .mdb, o procedimento é muito sem ena tentar, nosso foco de trabalho na atividade se

aplicativo Excel.

– Importando arquivos de dados par

mportar o arquivo controle de produtos.mdb para mportar um banco de dados para o Excel.

o arquivo controle de produtos.mdb, que você aneiras de importar uma base de dados pa

portação total da base de dados para o Excel. preendemos o que a atividade pede, vamos as eta

a pasta de trabalho em branco:

os, grupo Obter dados externos e clique na opção

ão clicada, aparecerá a janela a seguir:

11

mos utilizar arquivos feitos no melhante para outras bases de erá o access, por pertencer ao

ra o Excel

a o Excel.

ê irá importar para o Excel. ara o Excel, nesta atividade apas de desenvolvimento.

4. Selecione a base de dados Grupo Empresarial.mdb, que se encontra na pasta treinamento, e clique em abrir para prosseguir, e teremos a tela a seguir:

5. Nesta tela anterior, podemos escolher o modo que os dados serão mostrados, como também a partir de qual célula irá aparecer o resultado, deixe a opção padrão ativada, e clique em ok, confira o resultado a seguir:

6. Observe que o resultado já aparece formatado, este é um recurso padrão da versão 2007 do aplicativo.

Senac São Paulo

Na versão 2007

aparecerá a guia D7 para trocar a formatação da planilha, assim Design que te dará as possibilidades de formataçã

13

que o resultado for exibido, ão, conforme figura a seguir:

Senac São Paulo

Atividade 3 –

Objetivo : • co Tarefa : • A base de dados escolhendo somen satisfaçam a condi consulta que será c 1. Abra uma pasta

2. Vá a guia dado segue:

3. E você verá o m

4. Na tela anterio deverá ser sele

– Criando consultas de base de dado

Importar a base de dados Controle de Produto onsulta.

Importar arquivos de bancos de dados no modo Controle de Produtos.mdb será importada par nte alguns campos como: nome do produto, ção de pertencerem ao fornecedor Doces do B criada.

a de trabalho em branco:

os, no grupo Obter dados Externos, clique na o

menu seguinte:

or, clique na opção Do Microsoft Query, e você ecionar a opção: Banco de dados do MS Access, e

15

s dentro do Excel

os.mdb para o Excel, no modo consulta.

ra o Excel no modo consulta, fabricante, valor unitário que Brasil S/A, podem aparecer na

opção De outras Fontes, como

ê verá a janela a seguir, aonde e dê ok para prosseguir:

5. E teremos a janela a seguir:

O padrão de quando executarm visualizando Treinamento, pois demais do Windows, e a unidad 6. Selecione a base Controle d

seguir:

7. Na Janela Assistente de consu onde Produtos representa o todos os campos que compõ

mos esta ação é abrir a pasta meus documentos, s a pasta já foi selecionada, não esqueça que est de em questão tem que ser selecionada na caixa u de Produtos.mdb, e dê ok para prosseguir, voc

ulta, como você pode ver na figura anterior, apar o nome da tabela, clicando no sinal de mais, vo õe esta tabela, clique e confira com a imagem a se

, então você está ta tela difere das unidades.

ê terá a janela a

rece, , cê terá acesso a

Senac São Paulo 17

8. Assim poderá ser visualizado todos os campos que compõem a tabela produtos, existem algumas possibilidades nesta janela:

a) Se clicarmos no botão, , com o nome da tabela selecionada, todos os campos vão para o lado direito para serem utilizados na consulta.

b) Se clicarmos no botão, , com o nome de um dos campos selecionados, apenas o nome em questão vai para o lado direito para ser utilizado.

c) Se quando selecionarmos qualquer campo, clicarmos no botão, , o

conteúdo do campo selecionado, poderá ser visualizado, como segue:

d) Dando continuidade na atividade, selecione os campos propostos na atividade: Nome do Produto, Fabricante, Fornecedor e Valor Unitário, como segue:

10. Como no enunciado da atividade a restrição para consulta era apenas para o Fornecedor Doces do Brasil S/A, algumas adaptações serão necessárias, como na caixa coluna a filtrar, selecione Fornecedor, como segue:

11. Agora na caixa Fornecedor selecione o parâmetro igual, como segue:

12. Como é preciso exibir os dados relativos a Fornecedor seja Doces do Brasil S/A, selecione o nome em questão, como segue:

Senac São Paulo 19

14. Nesta janela poderá ou não ser escolhida uma ordem de classificação no resultado, no caso não será usado o nome do Fornecedor, pois será o mesmo conteúdo para todos os registros encontrados e não faria sentido, será optada pela classificação por nome do Produto, como segue:

15. Dê avançar e terá o resultado a seguir:

16. Clique em concluir e você terá a janela Importar dados a seguir, que permite escolher o tipo de apresentação de dados e a partir de qual célula o resultado será exibido, clique em ok:

17. Assim você visualizará o resultado seguinte:

18. É importante lembrar que a planilha que está sendo exibida no item anterior, foi gerada através de uma base de dados, então qualquer alteração que se deseja fazer, ou inclusão, deverá ser acessado o arquivo controle de produtos.mdb, dentro do Access e posteriormente retornando ao Excel na guia Design, no grupo dados de Tabela Externa, clique no botão Atualizar.

Senac São Paulo

Vinculando da

Nosso objetivo é vinculados no Acce no assunto, pois o

Atividade 4 –

Objetivo : • Vi Tarefa : • Vi

Temos uma planil Microsoft Access. 1. Abra o Aplicat Microsoft Acce

2. No menu do la teremos a jane

ados do Excel no Access

mostrar que existe a possibilidade de que os d ess, para tanto vamos falar o mínimo sobre Acc nosso foco é tratarmos sobre o Excel.

– Vinculando dados do Excel no Acc

incular dados do arquivo Access.xls no Access. incular dados de um arquivo do Excel dentro do A lha salva com o nome de Access.xls, que esta ivo Microsoft Access: Iniciar, opção Programas, ess, e você verá a tela seguinte:

ado direito, na janela Abrir Banco de Dados Rec ela a seguir:

21

ados digitados no Excel sejam ess para não nos estendermos

cess

Access.

aremos vinculando dentro do opção Microsoft Office, opção

3. Para que os arquivos feitos no Excel, sejam vistos nesta janela, vá a caixa Arquivos tipo, selecione todos os arquivos, caso contrário, somente os arquivos criados no Access serão mostrados, como segue:

4. Clique na pasta treinamento, e dentro dela selecione o arquivo Access.xls, e clique em abrir:

Senac São Paulo 23

6. Na janela anterior, você terá a possibilidade de escolher de qual planilha precisa exibir os dados dentro do Access, nesta atividade, os dados estão na plan 1, então para prosseguir clique em avançar, e você visualizará a janela a seguir:

7. Na janela anterior, você terá que especificar se a primeira linha é título da coluna ou dado, se você deixar a opção desativada a palavra nome, telefone, irão virar dados e não títulos de campos, selecione a opção como segue:

8. Observe que nome, telefone e os demais campos se transformam em rótulos das colunas, dê avançar e você terá a janela a seguir:

9. Na janela anterior, você tem a possibilidade de dar um nome para a planilha que acabou de vincular, digite Controle de RH, e selecione a opção concluir, assim você terá a janela a seguir:

10. Esta mensagem é informativa, avisa que a vinculação foi concluída, dê ok e você verá a janela a seguir:

Senac São Paulo 25

11. Observe que o nome Controle de Rh vem antecedido pelo símbolo do Excel, que avisa que esta tabela está vinculada a tabela feita no Excel.

12. Dê dois cliques no nome Controle de RH para que seu conteúdo seja visualizado, como segue:

13. Lembrando que a atividade concluída foi gerar um vínculo, então se faz necessário que qualquer alteração ou inclusão que seja feita, seja aplicada no Excel e atualizada dentro do Access, para tanto, vá a guia início, e no grupo Registros, você encontrará o botão Atualizar tudo.

Senac São Paulo 27

OBJETIVO

ƒ Conhecer o funcionamento do Auto Filtro, Filtro Avançado e

Classificação de Dados, como formas de consultas, navegação

na planilha e análise de dados.

Senac São Paulo

Filtro e Classi

Importante recurso uma melhor análise

Atividade 1 –

Objetivo : • U de Tarefa : • C co Nesta atividade, u consultas 1. Abra a planilha 2. Supondo que s Paulo, será pre planilha, desde Filtrar e clique Lembre-se que a alguma coluna qu não funcionará co 3. Você visualizar

ificação de Dados

o do Microsoft Excel, que nos permite a classific e de dados.

– Conhecendo o Filtro e a Classificaç

Utilizar o recurso Filtro e a Classificação de dado e dados.xls.

Conhecer o recurso Filtro e a Classificação de dad onsulta numa planilha.

usaremos o Filtro e a Classificação de dados pa a base de dados.xls que se encontra na pasta trein

seja preciso exibir apenas os funcionários que s eciso utilizar o recurso Filtro, para tanto selecione e que tenha conteúdo relativo a planilha, vá a guia

no botão Filtro, como segue:

a célula usada para chamar o filtro, para não term ue esteja completa, se for de alguma que esteja orretamente.

rá o resultado:

29

ação e filtragem de dados para

ção

os para navegar na planilha base dos como meio de navegação e

ara navegar na planilha e fazer namento, como segue:

se localizam na cidade de São e qualquer célula pertencente a

a dados, no grupo Classificar e

rmos problemas, deverá ser de a faltando dados, o modo filtro

4. A única coisa que difere a janela anterior da inicial, são os botões, em formato de seta que surgiram nas células referentes aos rótulos da coluna.

5. Como é preciso mostrar os dados referentes a cidade de São Paulo, clique na seta referente a cidade, como segue:

6. Na lista de opções que apareceu, temos o Nome de todas as cidades que se encontram na coluna, mesmo se a cidade se repetir, ela irá aparecer na lista do filtro uma única vez, clique em Selecionar tudo para desativar a opção e selecione a cidade de São Paulo, como segue:

Senac São Paulo 31

7. Dê ok e assim todos os dados referentes a São Paulo serão exibidos, confira com a janela seguinte:

Observe que a imagem de seta que havia em Cidade após o recurso filtro ser aplicado, foi trocada por uma imagem com o símbolo do filtro que identifica que naquela coluna o recurso filtro foi aplicado.

8. Assim que a opção ok é selecionada, uma informação muito útil aparece no canto inferior esquerdo da tela junto com o resultado do recurso filtro, é o retorno de quantos registros estão sendo mostrados de um total, como segue:

9. Se desejar classificar qualquer coluna e estiver ainda no modo filtro, basta clicar na seta do rótulo em questão e escolher a opção de Classificação que procura, de A a Z ou de Z a A. 10. Para tirar a seleção referente a cidade de São Paulo, clique na seta, do rótulo Cidade, célula

11. Deste modo todos os regist poderá ser visualizado a segu

Em qualquer momento que pr Classificar e Filtrar clique no bo

12. Outra forma de se aplicar o opção classificar e filtrar, com

tros serão mostrados, e você ainda estará no m uir:

recisar desativar o modo filtro, basta ir a guia da otão Filtro, como segue:

recurso filtro também é ir a guia Início, grupo ed mo segue:

modo filtro como

ados, e no grupo

Senac São Paulo 33

13. Quando você clicar na opção anterior irá aparecer o menu seguinte, então selecione a opção Filtro:

Aproveitando a tela anterior se desejar Classificar em Ordem Crescente alguma coluna, basta selecionar a opção Classificar de A a Z e caso for em Ordem Decrescente, basta selecionar a opção de Z a A, mas lembrando que se optar por este caminho o recurso será aplicado na coluna que estiver posicionado anterior ao acionamento da opção.

14. Outro modo de classificação é através da guia dados, grupo Classificar e Filtrar, assim teremos:

Para Classificarmos em Ordem Crescente Para Classificarmos em Ordem Decrescente

Senac São Paulo

Atividade 2 –

Objetivo : • C pe Continuando o a classificando uma classificar com um decrescente já con 1. Abra a planilha

2. Para uma melh e não pela ord célula seleciona clique na opçã classificação, as O procedimento Classificar e Filtra

– Classificação

lassificar a planilha projeto numero.xls com um ersonalizada.

assunto classificação; como você pôde acom coluna utilizando chave única; nesta atividade ma chave, criando uma ordem personalizada que

nhecida.

a projeto numero.xls:

hor organização de dados, vamos classificar o Mê dem crescente ou decrescente que você já conh ada da nossa planilha, desde que tenha algum dad ão classificar e filtrar, e no menu que irá ap ssim você terá a janela a seguir:

o proposto no item 2 pode ser feito também ar, clique no botão classificar, e você terá a janela

35

ma chave criando uma ordem

panhar na atividade anterior você verá a possibilidade de difere da ordem crescente ou

ês de Início por ordem de Mês, ece, para tanto, com qualquer do da planilha, vá a guia início e parecer selecione personalizar

através da guia dados, grupo a Classificar deste modo.

3. Como estamos classificando Mês de Início, como segue:

4. Na caixa Classificar em, personalizada, como segue:

5. Então você terá a janela segu

Como você pode notar na jane meses da planilha estão por exte

Vamos imaginar que um Geren funcionários da empresa por or Adicionar na tela anterior e col assim toda a vez que precisar de

por ordem de Mês, na caixa classificar por, se

deixe selecionado valores, e em ordem, se

uinte:

ela Listas Personalizadas, existem 4 listas já def nso, selecione a última opção e dê ok.

nte de Recursos Humanos, tenha a necessidade rdem de Hierarquia, ele poderá utilizar este recu olocando os cargos um a um na ordem de Hierar

desta lista ela estará pronta.

elecione a opção

elecione a lista

finidas, como os

e de classificar os urso, clicando em rquia que precisa

Senac São Paulo 37

6. Assim você visualizará a janela:

7. Dê ok para finalizar e confira com o resultado a seguir:

Senac São Paulo

Atividade 3 –

Objetivo : • U po Tarefa : • O Vamos nos organiz dos projetos da pla por mão de Obra, para tanto você est 1. Abra a planilha que pertença classificar, e vo 2. Observe na t permanecem, f a janela a segui 3. Como dentro selecione a op seguir:

– Classificação com duas chaves

tilizar a chave dupla para classificação na plan ossibilitar um melhor gerenciamento dos projetos Organizar os projetos com a utilização da chave du zar ainda com a classificação com a possibilidade

anilha utilizada na atividade anterior. Nesta ativid , dentro desta organização os projetos serão co tará utilizando nesta atividade a chave dupla para a utilizada na atividade anterior: projeto numero a planilha e vá a guia dados, grupo Classifica ocê terá a seguinte janela:

tela anterior que os últimos ajustes feitos faça a seguinte alteração, em Classificar por: alte r:

da classificação de mão de obra foi propost ção adicionar nível, e em depois por selecione P

39

nilha projeto numero.xls para s.

upla para classificação.

de um melhor gerenciamento dade você criará a organização olocados em ordem crescente,

classificação.

o.xls, selecione qualquer célula r e Filtrar e clique na opção

na atividade anterior, ainda re para Mão de Obra e confira

ta a classificação por projeto, Projeto e confira o resultado a

Se houver a necessidade de alt para subir um nível, ou a setinha 4. Dê ok para finalizar e verifiqu

Observe que a planilha foi segm desta segmentação, houve a class

terar a ordem dos níveis utilize as setinhas apont ha apontando para baixo para descer um nível.

ue o resultado a seguir:

mentada em duas outras: Não Qualificada e Qua sificação por ordem de projeto.

ntando para cima,

Senac São Paulo

Atividade 4 –

Objetivo : • C ou Tarefa : • C A versão 2007 che célula ou ícone, n pessoa anteriorme máxima, cinza, par classificar os projet 1. Abra a planilha

2. Vá a guia Dado seguir:

3. Clique em Adic

– Classificando por cores

lassificação dos projetos na planilha projeto core u ícone.

Classificar os projetos por prioridade máxima, por egou com muitas mudanças, e uma delas é a c nesta atividade, você vivenciará este recurso co ente, demarcou os projetos na planilha com as ra cuidado, e branca, para os que estiverem ok, e

tos por prioridade máxima, em seguida por cuida a projeto cores.xls, como segue:

os, grupo Classificar e Filtrar e clique na opção c

cionar Nível e preencha com os quesitos a seguir

41

es.xls pela cor da fonte, célula r cuidado e ok.

classificação pela cor da fonte, om a seguinte suposição, uma s cores preta, para prioridade então a missão nesta atividade é

ado e ok.

lassificar, e você terá a janela a

Para funcionar o recurso clas relacionado do contrário as cor 4. Selecione Adicionar Nível e p

Como estamos trabalhando co contrário não será possível a cla cinza, por conta do aplicativo e cores, teríamos que especificar 5. Dê ok e verifique o resultado

ssificar por cor da célula, é necessário que um res não irão aparecer e você não terá as opções. preencha com os quesitos a seguir:

om a classificação de cores, temos que descreve lassificação, observe que no exemplo em questão entender que a branca vem na seqüência, mas se r nível a nível.

o a seguir:

m campo esteja

er a ordem, caso o fomos até a cor e tivéssemos mais

Senac São Paulo 43

6. Observe que os dados são classificados juntamente com a cor, o que nas outras versões era impossível.

Senac São Paulo

Filtro Avança

O Filtro Avançado dados em uma nov

Atividade 5 –

Objetivo : • C cr Tarefas : • C • C • C • C em Nesta atividade, se aos critérios espec Portanto você terá 1. Abra a pasta ba

2. Navegue pela pelo motivo do Para que o recurs você escrever em poluindo, por exem critérios não apare cuja finalidade é ar 3. Para iniciar o r atividade, prec digitar os nom loja e cole na p

ado

o permite além de filtrar, recurso já visto na A va planilha, como veremos na atividade a seguir.

– Conhecendo o filtro Avançado

onhecer o recurso Filtro Avançado para selecion ritérios especificados na pasta base avançada.xls. Criar relatórios de funcionários que trabalhem na C Criar relatórios de funcionários que trabalhem em Criar relatórios de funcionários que trabalhem na C

Criar relatórios de funcionários que trabalhem na m São Paulo

erá utilizado o recurso Filtro Avançado, para sele cificados a seguir e colocá-los em uma planilha a p

á nesta atividade 4 relatórios para fazer

ase avançada.xls, que se encontra na pasta treinam

planilha e observe que embora tenha nome nas o espaço estar reservado previamente para o resu so filtro avançado possa ser utilizado, você prec m alguma planilha que já está direcionando p mplo, no caso de impressão desta planilha, teria ecessem na impressão, então por este motivo f

mazenar os critérios que serão utilizados. recurso filtro avançado os critérios necessários d cisam ser colocados na planilha critérios, para q es das lojas diferentes dos que se encontram na planilha critérios como segue:

45

Atividade 1 - Filtro, colocar os

nar os registros pertinentes aos Confeitaria Piccoli

3 Manos Mercados Cervejaria Piccoli

a Confeitaria Piccoli e residam

ecionar os registros pertinentes parte.

mento:

demais abas, elas estão vazias, ultado que será encontrado. cisará escrever os critérios, se para alguma finalidade, estará

que ser adaptado para que os foi inserida uma nova planilha, de acordo com o enunciado da que você não corra o risco de a planilha dados, copie a coluna

4. Para que as duplicidades sejam eliminadas, pela necessidade apenas de um nome de cada loja e não de vários, vá a guia dados, e no grupo Ferramentas de Dados, clique na opção Remover Duplicatas, então aparecerá a janela Remover duplicatas, clique em ok e verifique o resultado a seguir:

5. Como você está construindo critérios para o filtro avançado, precisará também dos rótulos a seguir:

Senac São Paulo 47

6. Como o primeiro item do enunciado é selecionar os funcionários que pertencem a loja Confeitaria Piccoli, vá a planilha Confeitaria Piccoli, aonde deverá ser exibido o resultado, e não aquela aonde estão os dados, como segue:

7. Vá a guia Dados, no grupo classificar e filtrar e selecione a opção Avançado, como segue:

8. Assim, você visualizará a janela:

9. Como o resultado deverá ser colocado em outra planilha, ative a opção Copiar para outro local, como segue:

10. Selecione o intervalo respect botão, , para que você po

11. Agora vá para a planilha Dado

12. Agora clique novamente no b

Outra maneira de selecionar o i antes de iniciarmos, vá a planil nome localizada abaixo das guia não pode ser de alguma célula, foi digitado o nome de geral, estivermos na janela Filtro Avan a palavra gera, e não clicarmos 13. O próximo passo é adiciona

botão, , para que você seguinte janela:

14. Vá para a planilha critérios e como consta na letra A do en

15. Clique novamente no botão,

tivo da lista em questão, para tanto clique ao lado ossa ter acesso a planilha Dados, a seguinte janela

os, e faça a seleção de A1 até H23, como segue:

botão, , para retornar a janela Filtro Avançado

intervalo da lista, porém sem sair da planilha Con ilha dados e selecione as células de A1 até H23, as de ferramentas, e escreva o nome que desejar ter espaços em branco ou caracteres especiais, n

veja: , lembrando que send

nçado, se optarmos por este caminho temos apen no botão para buscarmos as células da planil ar critérios, para tanto na caixa intervalo de cri possa ter acesso a planilha de critérios, e vo

selecione as células A1 e A2, referentes a Loja Co nunciado da atividade, como segue:

, para retornar a janela Filtro Avançado, com

o da caixa lista no a aparecerá: o, como segue: nfeitaria Piccoli, é 3, e vá a caixa de r, lembrando que na figura a seguir do assim quando enas que escrever

lha dados. térios, clique no ocê visualizará a

onfeitaria Piccoli,

Senac São Paulo 49

16. Agora é necessário especificar aonde os resultados serão exibidos, para tanto clique no botão, , localizado ao lado da caixa copiar para, e você visualizará a janela a seguir:

17. Assim para finalizar, você terá que escolher a partir de qual célula que gostaria de exibir o resultado, no caso escolha A1, por ser o padrão aonde todas as planilhas se iniciam, como segue:

18. Clique novamente no botão, , para retornar a janela Filtro Avançado, como segue:

19. Dê ok para finalizar e verifique o resultado a seguir:

20. Verifique que apenas os funcionários que trabalham na Confeitaria Piccoli são exibidos. 21. Repita o processo para as demais lojas e confira com os resultados a seguir:

a) Loja 3 Manos Mercados:

b) Loja Cervejaria Piccoli:

22. Agora precisamos listar os funcionários que trabalham na Confeitaria Piccoli e Residam em São Paulo, para tanto você precisa colocar mais este critério na planilha critérios, como você já tem o nome da loja Confeitaria Piccoli, coloque apenas a cidade, como segue:

23. Vá planilha Confeitaria x São Paulo e na guia dados clique no botão Avançado, e você visualizará a tela a seguir:

Senac São Paulo 51

24. Como é necessário colocar o resultado em outra planilha, selecione a opção copiar para outro local, como segue:

25. Clique no botão, , ao lado da caixa Intervalo da lista para poder selecionar os dados, e você terá a seguinte janela:

26. Vá para a planilha dados e faça a seleção a seguir:

27. Clique novamente no botão, , para retornar a janela Filtro Avançado, como segue:

28. Agora selecione os critérios, para tanto clique no botão, , localizado ao lado da caixa critérios e você visualizará a seguinte janela:

29. Vá para a planilha critério, e selecione o intervalo referente a célula A1 até B2, como segue:

31. Para escolher a partir de qual célula você estará exibindo o resultado, clique no botão, , localizado ao lado da caixa copiar para, como segue:

32. Selecione a célula A1, como segue:

33. Clique novamente no botão, , para retornar a janela Filtro Avançado, como segue:

Senac São Paulo 53

OBJETIVOS

ƒ Conhecer as funções de texto: Arrumar, Maiúscula,

Minúscula, Pri.Maiúscula, Ext.texto, Esquerda, Direita.

ƒ Conhecer as funções de banco de dados: Bdsoma, Bdmédia,

Senac São Paulo

Funções de Te

Imagine que você t exibido da maneir problemas como necessidade de se poderão facilitar importação dentro

Atividade 1 –

Objetivo : • A re Tarefa : • O pa ap os to • C • C • Pr • Pr • D ca ca 1. Abra a planilha

Observe que temo Original: aonde se Aplicação: como a poluição visual, a p

exto

tenha um banco de dados que importou para o ra esperada, por exemplo, letra minúscula no espaços em branco, que por ventura podem egmentar algum número, neste capítulo será ab a adaptação destes dados, principalmente dos o do Excel.

– Aplicando as funções de texto

Aplicar as funções de texto na planilha livrarialibé esultantes de uma importação que apresentou pro Os nomes dos livros da livraria libélula iniciam sem ara solucionar este tipo de problema, os espaç pagados, por conta se houver necessidade de clas s espaços em branco são considerados na classif odo o processo.

olocar inicial maiúscula no nome do autor. olocar o nome da Editora toda em letra maiúscul ré-venda deverá ser colocado em letra minúscula reço se manterá o mesmo.

Dividir o código em três partes, onde o 1º par, aracteres, 2º par, se refere aos dois caracteres do aracteres

a livrarialibélula.xls, que se encontra na pasta trein

os duas planilhas:

encontram os dados a serem trabalhados as funções precisam ser aplicadas em outras cé planilha aplicação foi reservada para esta finalidade

55

Excel e que nem sempre ele é lugar de maiúscula, e outros m aparecer, como também a bordado, algumas funções que

s resultados apresentados na

élula.xls para adaptar os dados oblemas.

mpre com espaços em branco, ços em branco terão que ser ssificação em ordem alfabética, ficação, comprometendo assim

la a.

, se refere aos dois primeiros o meio e 3º par os dois últimos

namento, como segue:

élulas, para não acarretar uma e.

2. Como a intenção é eliminar os espaços em branco, para tanto você utilizará a função arrumar, cuja sintaxe:

= arrumar(célula que se deseja desconsiderar os espaços em branco)

3. Prosseguindo com a atividade, em Original, você encontra os nomes dos livros com espaços em branco e terá que trazê-los para a planilha Aplicação, então na célula A4, da planilha aplicação, digite: =arrumar(

4. Observe que uma caixa de texto abre-se abaixo da função que você estará digitando, isso garante, que a digitação está correta, se por ventura, você esquecer da sintaxe, neste momento se você clicar no nome da função nesta caixa de texto, irá acessar diretamente a ajuda do aplicativo com esta função selecionada, veja:

Senac São Paulo 57

5. Dando prosseguimento, após digitar =Arrumar(, vá a planilha Original, e selecione a célula A4 dando enter para finalizar, como segue:

6. Para concluir arraste a função para as demais células e verifique o resultado a seguir:

7. Salve as alterações para prosseguir a atividade.

8. Agora o próximo item a ser trabalhado é colocar inicial maiúscula no nome do autor, para tanto, você utilizará a função Pri.Maiúscula cuja sintaxe é:

=Pri.Maiúscula(célula que se deseja aplicar o recurso)

9. Agora vá para a planilha Aplicação, e selecione a célula B4, então digite: =Pri.Maiúscula(, vá para a planilha Original e selecione a célula B4, dê enter para finalizar, como segue:

Note que a inicial maiúscula é ap 11. Salve as alterações para pross 12. No próximo item a ser trab

para tanto utilizará a função M = Maiúscula( célula que se de O nome da função deve ser d acento se você esquecer, agora te levar diretamente a ajuda, se 13. Agora vá para a planilha aplic original e selecione a célula C

Quando o texto for longo se qu selecione formatar, e no men aparecer, vá a guia alinhamento

licada até no sobrenome do autor seguir a atividade.

balhado, você irá colocar o nome da editora em Maiúscula cuja sintaxe é:

eseja aplicar o recurso)

digitado com ou sem acento? Indiferente o apl ra se precisar daquela caixa de texto com o nome e torna necessária a digitação correta, com o acen cação, e selecione a célula C4, e digite: = Maiúscu C4, e dê enter para finalizar, como segue:

uiser quebrá-lo dentro da célula, vá a guia início, n nu que irá aparecer, escolha formatar célula, n o, como segue: m letra Maiúscula, plicativo coloca o me da função para nto inclusive. ula(, vá a planilha no grupo células, na janela que irá

Senac São Paulo 59

Selecione a opção quebrar texto automaticamente, na caixa vertical, selecione centro, e dê ok para finalizar, veja o resultado a seguir:

14. Arraste a célula C4 para as demais células, e verifique o resultado a seguir:

16. No próximo item, você deverá colocar pré-venda em letra Minúscula, para tanto utilize a função Minúscula cuja sintaxe é:

= Minúscula( célula que se deseja aplicar o recurso)

17. Agora, vá a planilha aplicação e na célula G4, digite: =Minúscula(, vá a planilha Original e selecione a célula G4, dê enter para finalizar, como segue:

18. Arraste a célula G4 para as demais células e verifique o resultado a seguir:

19. Salve as alterações para prosseguir a atividade.

20. No próximo item, dentro da planilha aplicação, na coluna “d” você deverá colocar os dois primeiros caracteres que compõem o código localizado na coluna “d” da planilha Original, para tanto utilize a função esquerda cuja sintaxe é:

= esquerda( célula que se deseja aplicar o recurso; número de carateres a esquerda)

21. Agora vá a planilha Aplicação, selecione a célula D4 e digite, = Esquerda(, vá a planilha Original e selecione a célula D4, em seguida digite ; 2, por ser o número de caracteres que o enunciado pede que seja exibido, verifique a sintaxe da função, e o resultado obtido:

Senac São Paulo 61

22. Salve as alterações para prosseguir a atividade.

23. Ainda dentro do item anterior, na planilha aplicação, coluna “f” você deverá exibir os dois últimos caracteres que compõem o código localizado na coluna “d” da planilha Original, para tanto utilize a função direita, cuja sintaxe é:

= direita( célula que se deseja aplicar o recurso; número de carateres a direita)

24. Agora vá a planilha Aplicação, selecione a célula f4 e digite, = direita(, vá a planilha Original e selecione a célula D4, em seguida digite ; 2, por ser o número de caracteres que localizado a direita da célula em questão que deverá ser exibido, verifique a sintaxe da função, e o resultado obtido:

=DIREITA(Original!D4;2)

25. Salve as alterações para prosseguir a atividade.

26. Ainda dentro do item anterior, na planilha aplicação, coluna “E” você deverá exibir os dois caracteres do meio que compõem o código localizado na coluna “D” da planilha Original, para tanto você deverá utilizar a função ext.texto, cuja sintaxe é:

= Ext.texto( célula que se deseja aplicar o recurso; posição de início; quantidade de caracteres a partir da posição )

27. Agora vá a planilha Aplicação, selecione a célula e4 e digite, = ext.texto(, vá a planilha Original e selecione a célula D4, em seguida digite ; 3, por ser a partir do caracter 3 que precisa ser exibido, em seguida ; 2 por ser a partir da 3ª posição, 2 caracteres que precisão ser exibidos, verifique a sintaxe da função, e o resultado obtido:

28. Para copiar os preços para a planilha Aplicação, digite = na célula H4, vá a planilha Original e selecione a célula f4 e dê enter para finalizar, confira:

Senac São Paulo

Atividade 2 –

Objetivo : • C Os resultados da a é preciso que as c você irá vivenciar e 1. Com a planilha 2. Observe que a funções, agora aplicação, apen 3. Vá a planilha ap de Transferênc 4. Vá a planilha p clique na seta r 5. Selecione Cola valores e dê ok

– Copiando Fórmulas e planilhas

opiar Fórmulas e planilhas.

atividade anterior estão sendo mostrados na plani células não contenham fórmulas, ou até mesmo estas situações nesta atividade.

a da atividade anterior aberta: livrarialibélula.xls, c

apenas a coluna de preço contém números e as d a para se dar início ao processo, você precisa nas com os resultados das funções e não mais com

plicação, selecione do intervalo de A1 até H11, v cia, clique no símbolo copiar , ou tecle: CTR plan3, posicione-se na célula A1, na guia início, referente ao botão colar e você terá o seguinte m

ar Especial e você terá a tela seguinte, aonde vo k para finalizar:

63

ilha aplicação, embora as vezes que a planilha seja transposta, como segue:

emais colunas você encontrará rá de uma versão da planilha m as funções.

vá a guia Início, no grupo Área RL + c.

grupo Área de Transferência, menu:

6. Verifique o resultado a seguir:

7. A única diferença de plan3 para a planilha Aplicação, é que agora você terá o resultado da função e não mais a função:

8. Imagine agora que fosse necessário colocar os dados de plan3 em uma outra planilha, só que os dados que agora representam colunas, iriam representar linhas, parece complicado, no entanto é simples, insira uma nova planilha como segue:

9. Agora volte para a plan3 e selecionar os dados de A3 até h11, e vá a guia início, grupo Área de Transferência, selecione o botão copiar: , vá para Plan1 e posicione-se na célula A1, na guia Início, grupo Área de Transferência, clique na seta do botão colar e selecione a opção colar especial, assim você visualizará a janela seguinte:

Senac São Paulo 65

10. Selecione a opção transpor e dê ok para finalizar, acompanhe o resultado a seguir:

11. O que era coluna passa a ser representado em linha, como você pode observar na imagem anterior.

Senac São Paulo

Funções de Ba

Neste Capítulo, vo no formato de ban

Atividade 3 –

Objetivo: • C Tarefas : • C • C • C • C • C • C Nesta atividade co relatórios para con 1. Abra a pasta pr 2. Na planilha Co respectivos fab ainda uma más a seguir:

anco de Dados

ocê conhecerá algumas funções que são específic nco de dados, como você poderá vivenciar na ativ

– Conhecendo as funções de Banco d

riar relatórios com as das funções de banco de da Criar relatório com valor total de pagamentos de c riar relatório com média de pagamentos de cada riar relatório com maior valor pago a cada Forne riar relatório com menor valor pago a cada Forne riar relatório com nome do Produto de maior va riar relatório com nome do Produto de menor va om o auxílio das funções de banco de dados, voc nsolidar as informações necessárias.

rodutos.xls que se encontra na pasta treinamento

ontrole de Produtos, você encontrará uma list bricantes, fornecedor, e valores de pagamento, na

scara seguindo os critérios propostos no enuncia

67

cas para interagir com planilhas vidade passo a passo.

de Dados

ados. cada Fornecedor. a Fornecedor ecedor ecedor

alor de cada Fornecedor alor de cada Fornecedor cê terá a oportunidade de criar o, como segue:

tagem de produtos, com seus a planilha Relatório, encontrará ado, como você pode observar

3. Inicie pela coluna “B”, na qual deverá ser exibido as somas por Fornecedor, para tanto utilize a função bdsoma, cuja sintaxe é:

=bdsoma(banco de dados; rótulo da coluna em questão; critérios)

4. Observe na sintaxe da função, que existe o argumento critérios, então você precisará escrever os critérios segundo os itens propostos no enunciado, para tanto vá a planilha critérios e faça as adaptações necessárias:

5. Agora que você já tem os critérios especificados, vá a planilha Relatório, e na célula B4, digite: = Bdsoma(, como o primeiro argumento é o banco de dados, vá a planilha Controle de Produtos, e selecione o intervalo referente a A1 até D17, como segue:

Senac São Paulo 69

6. Digite ; em seguida, o rótulo da coluna que você utilizará como resultado, neste item como o objetivo é totalizar os pagamentos por Fornecedor, selecione o campo Total, célula D1, e para finalizar, vá a planilha critérios e selecione a célula A1 e A2, como segue:

7. Dê enter para finalizar, verifique a seguir a sintaxe da função e o resultado obtido: =BDSOMA('Controle de Produtos'!A1:D17;'Controle de Produtos'!D1;Critérios!A1:A2)

8. Repita o processo para os demais Fornecedores, não esqueça que para cada Fornecedor o critério tem que ser refeito, por conta de a cada célula ser abordado um novo Fornecedor. Confira a seguir a sintaxe da função e os resultados obtidos:

Fornecedor Doces do Brasil S/A:

=BDSOMA('Controle de Produtos'!A1:D17;'Controle de Produtos'!D1;Critérios!A4:A5) Fornecedor Flores Hipermercados

=BDSOMA('Controle de Produtos'!A1:D17;'Controle de Produtos'!D1;Critérios!A7:A8) Fornecedor 3 Manos Hipermercados

=BDSOMA('Controle de Produtos'!A1:D17;'Controle de Produtos'!D1;Critérios!A10:A11)

9. No próximo item do enunciado, você terá que calcular a média de valores pagos a cada Fornecedor, para tanto utilize a função bdmédia, cuja sintaxe é:

Senac São Paulo 71

10. Lembrando que para a função bdsoma a todo o momento, você precisou ir a planilha controle de produtos e selecionar os dados de A1 até D17, para poupar esta passagem, a função bdmédia será abordada com nomeação de células, para tanto vá a planilha controle de Produtos e selecione de A1 até D17, como segue:

11. Agora vá a caixa de nome e digite geral, como segue:

12. Lembrando que a média deve ser feita com base no Fornecedor, então você poderá aproveitar os mesmos critérios propostos para soma, não existe a necessidade de reescrevê-los. Prosseguindo com o enunciado, agora vá a planilha Relatório e selecione a célula C4, e

então ao digitar a função, é importante lembrar que já foi nomeado o intervalo que será utilizado na sintaxe de banco de banco de dados, então ao digitar a função utilize o nome geral e não será preciso selecionar o intervalo, assim você terá: =BdMédia(geral; agora vá a planilha Controle de Produtos e selecione o rótulo Total, na célula D1, em seguida vá paraa planilha critérios e selecione as células A1 e A2, dê enter para finalizar e confira a sintaxe e resultado a seguir:

=BDMÉDIA(geral;'Controle de Produtos'!D1;Critérios!A1:A2)

13. Faça a média para os demais fornecedores, e confira a função e resultados a seguir: Fornecedor Doces do Brasil S/A:

=BDMÉDIA(geral;'Controle de Produtos'!D1;Critérios!A4:A5) Fornecedor Flores Hipermercados

=BDMÉDIA(geral;'Controle de Produtos'!D1;Critérios!A7:A8) Fornecedor 3 Manos Hipermercados

=BDMÉDIA(geral;'Controle de Produtos'!D1;Critérios!A10:A11)

14. Agora você terá que calcular o maior valor pago de cada fornecedor, como ainda estamos abordando o mesmo critério, não será necessário fazer modificações na planilha critérios, você utilizará a função bdmáx, cuja sintaxe é:

=bdmáx(banco de dados; rótulo da coluna em questão; critérios)

15. Dando continuidade vá a planilha Relatório selecione a célula D4 e então digite: =bdmáx(geral; isto se deve ao fato de anteriormente você ter ido a planilha Controle de Produtos e ter renomeado o intervalo referente ao primeiro argumento da função, portanto não será necessário sair da planilha Relatório é só digitar o nome atribuído, vá para a planilha Controle de Produtos, selecione apenas o rótulo na coluna Total, no caso a Célula D1 em seguida vá a planilha critério e selecione as células A1 e A2, dê enter para finalizar e confira a sintaxe da função e resultado obtido:

Senac São Paulo 73

=BDMÁX(geral;'Controle de Produtos'!D1;Critérios!A1:A2)

16. Faça o maior valor para os demais Fornecedores e confira com a sintaxe da função e resultados obtidos:

Fornecedor Doces do Brasil S/A:

=BDMÁX(geral;'Controle de Produtos'!D1;Critérios!A4:A5) Fornecedor Flores Hipermercados

=BDMÁX(geral;'Controle de Produtos'!D1;Critérios!A7:A8) Fornecedor 3 Manos Hipermercados

=BDMÁX(geral;'Controle de Produtos'!D1;Critérios!A10:A11)

17. Vamos repetir o processo de máximo só que agora utilizando-se a função para descobrir o valor mínimo, como segue:

=bdmín(banco de dados; rótulo da coluna em questão; critérios)

18. Dando continuidade, vá a planilha Relatório, e selecione a célula F4, então digite: =Bdmín(geral, isto se deve ao fato de anteriormente você ter ido a planilha Controle de Produtos e ter renomeado o intervalo referente ao primeiro argumento da função, portanto não será necessário sair da planilha Relatório é só digitar o nome atribuído, vá a planilha Controle de Produtos e selecione o rótulo Total, ou seja a célula D1, digite ; vá a planilha critérios selecione as células A1 e A2, dê enter para finalizar e confira a função e resultado obtido:

19. Faça o menor valor para os demais fornecedores e confira a sintaxe da função e resultados obtidos:

Fornecedor Doces do Brasil S/A:

=BDMÍN(geral;'Controle de Produtos'!D1;Critérios!A4:A5) Fornecedor Flores Hipermercados

=BDMÍN(geral;'Controle de Produtos'!D1;Critérios!A7:A8) Fornecedor 3 Manos Hipermercados

=BDMÍN(geral;'Controle de Produtos'!D1;Critérios!A10:A11)

20. Dando continuidade você terá que identificar o nome do produto de cada Fornecedor que tenha o maior preço identificado anteriormente, para tanto será utilizado a função bdextrair, cuja sintaxe é:

= bdextrair(banco de dados; rótulo da coluna em questão; critérios)

21. Quanto ao critério, terá que ser feita uma adaptação, por conta de neste item ter que ser identificado o nome do produto de um determinado fornecedor e valor, então você terá que colocar os valores referentes ao preço máximo na planilha critérios, como poderá ver a seguir:

Senac São Paulo 75

Observe que na planilha Critérios está sendo utilizado Total e não Maior Valor, isto porque, como você está construindo a planilha critérios, o rótulo da coluna tem que ser o mesmo da planilha controle de produtos e nesta você encontrará total e não maior valor.

22. Dando continuidade, vá a planilha Relatório, selecione a célula E4, então digite: = Bdextrair(geral; isto se deve ao fato de anteriormente você ter ido a planilha Controle de Produtos e ter renomeado o intervalo referente ao primeiro argumento da função, portanto não será necessário sair da planilha Relatório é só digitar o nome atribuído, vá a planilha Controle de Produtos, e como agora você precisa do nome do produto como retorno da função, selecione a célula A1, referente a nome do produto, na seqüência, vá a planilha critérios e selecione a célula A1 até B2, dê enter para finalizar, verifique a função e resultado obtido:

=BDEXTRAIR(geral;'Controle de Produtos'!A1;Critérios!A1:B2)

23. Aplique a função Bdextrair para as demais células e confira a sintaxe da função e resultados obtidos:

Fornecedor Doces do Brasil S/A:

=BDEXTRAIR(geral;'Controle de Produtos'!A1;Critérios!A4:B5) Fornecedor Flores Hipermercados

=BDEXTRAIR(geral;'Controle de Produtos'!A1;Critérios!A7:B8) Fornecedor 3 Manos Hipermercados

Se o retorno da função bdextra dado procurado não foi encont 24. Dando continuidade agora vo

valor, para tanto será necessá

25. Vá a planilha Relatório, selec fato de anteriormente você intervalo referente ao prime planilha Relatório é só digita agora você precisa do nom referente a nome do produto E2, dê enter para finalizar, ve =BDEXTRAIR(geral;'Contro

26. Encontre os demais produtos Fornecedor Doces do Brasil =BDEXTRAIR(geral;'Contro Fornecedor Flores Hipermer =BDEXTRAIR(geral;'Contro Fornecedor 3 Manos Hiperm =BDEXTRAIR(geral;'Contro

air der algum tipo de erro tem que se avaliar duas trado na planilha, ou se ele existe em duplicidade

ocê irá descobrir os nomes dos produtos que po ário algumas as adaptações na planilha critérios:

ione a célula G4, então digite: = Bdextrair(geral ê ter ido a planilha Controle de Produtos e te eiro argumento da função, portanto não será ne

r o nome atribuído, vá a planilha Controle de Pr e do produto como retorno da função, selecio o, na seqüência, vá a planilha critérios e selecione erifique a função e resultado obtido:

ole de Produtos'!A1;Critérios!D1:E2)

s, e confira a sintaxe da função e resultados obtid S/A: ole de Produtos'!A1;Critérios!D4:E5) rcados ole de Produtos'!A1;Critérios!D7:E8) mercados ole de Produtos'!A1;Critérios!D10:E11) as hipóteses, ou o e na mesma. ossuem o menor l; isto se deve ao er renomeado o ecessário sair da rodutos, e como one a célula A1, e a célula D1 até

Senac São Paulo 77

27. Supondo que precise quantificar o número de produtos de cada fornecedor, para tanto você terá que fazer as adaptações necessárias na planilha Relatório, como segue:

28. Para a contagem de produtos por Fornecedor, você utilizará a função bdcontara, cuja sintaxe é:

= bdcontara(banco de dados; rótulo da coluna em questão; critérios)

29. Vá a planilha Relatório, selecione a célula H4 e digite: =Bdcontara(geral; isto se deve ao fato de anteriormente você ter ido a planilha Controle de Produtos e ter renomeado o intervalo referente ao primeiro argumento da função, portanto não será necessário sair da planilha Relatório é só digitar o nome atribuído, na seqüência, vá a planilha controle de produtos, como é preciso contar os produtos, selecione a célula A1, em seguida vá a planilha critérios, e selecione as células A1 e A2, dê enter para finalizar e verifique a função e resultados obtidos: =BDCONTARA(geral;'Controle de Produtos'!A1;Critérios!A1:A2)

30. Aplique a função para os demais fornecedores e verifique a sintaxe da função e resultados obtidos:

=BDCONTARA(geral;'Controle de Produtos'!A1;Critérios!A4:A5) Fornecedor Flores Hipermercados

=BDCONTARA(geral;'Controle de Produtos'!A1;Critérios!A7:A8) Fornecedor 3 Manos Hipermercados

Senac São Paulo 79

OBJETIVOS

ƒ Criar relatórios de consulta com referência a uma base de

dados, agrupando-os de forma significativa, e fazendo

interação com a planilha base.

ƒ Consolidação de informações de uma base de dados

ƒ Criar subtotais em uma planilha.

Senac São Paulo

Tabela dinâm

Utilizamos o recu consolidando por v gerar o relatório. Este recurso se to dados relativamen agrupar, totalizar o todas estas possibi

Atividade 1 –

Objetivo : • El

Tarefas : • C

A seguir é mostra grupo curso, você 1. Abra o arquivo Na tela anterior, v código de Matrícu Financeira e Saldo 2. Dando início a opção tabela d 3. Ao clicar na op

mica

rso Tabela dinâmica, para elaborar relatórios si valores, quantificando informações e outras opçõ orna extremamente útil para pessoas que possue nte grande, tanto em linhas quanto em colun ou até mesmo quantificar os dados que precisa,

lidades.

– Criando o Relatório de Tabela Din

laborar um relatório referente aos dados encontr Criar a opção de agrupar os dados do curso.

ada uma planilha referente ao controle de aluno terá o nome do aluno, período e valor do Saldo D o base tabela.xls que se encontra na pasta treinam

você tem o controle de alunos de uma escola, ne ula, Nome, Endereço, Bairro, data de Nascim Devedor.

ao relatório, conforme enunciado, vá a guia inser inâmica, como segue:

pção Tabela Dinâmica, você visualizará o menu a s

81

ignificativos, agrupando dados, ões que forem necessários para em planilhas, com uma base de nas, e dificilmente conseguiria

a tabela dinâmica nos oferece

nâmica

rados na tabela.

os de uma Escola. Dentro do Devedor.

mento, como segue:

le poderá observar os campos: mento, Ano, Período, Situação

rir, grupo tabelas e selecione a

4. Clique na opção Tabela Dinâmica, e você terá a janela a seguir:

Na tela anterior, você tem a opção de escolher uma tabela/intervalo, como também usar uma fonte de dados externa, nesta atividade, você utilizará a opção tabela/Intervalo, confira se o intervalo proposto realmente faz parte da tabela, caso o intervalo proposto não esteja contemplando os dados da planilha, clique no botão e refaça o intervalo.

5. Para prosseguir, dê ok e confira a janela a seguir:

Na tela anterior, no lado direito, você encontrará a janela Lista de Campos da Tabela dinâmica, aonde todos os campos que pertencem a seleção inicial, estão aparecendo, mas nem sempre é necessário o uso de todos os campos que nela aparecem, você tem a opção de escolher somente aqueles que contemplarem a necessidade proposta inicialmente, dentro desta janela você encontrará:

Filtro de Relatório: Numa visão macro, pode-se dizer que o Filtro de relatório teria a função de dividir a tabela em grupos grandes, ou supondo a idéia de ter que dividir a tabela em várias

Senac São Paulo planilhas, qual o considerado como Rótulo de Linha: subconjunto do gru Lembrando que a como linha da tab estão em linha. Rótulo de Coluna: agrupado, dentro d Lembrando que a exibida como Colu inicialmente estão Valores: quando co caracteres, ele qua 6. Para prossegui Nome, Período

7. Observe que, Aplicativo os c do layout da ta curso para Filtr

campo seria usado nesta ocasião? A respost o campo a ser utilizado em Filtro de Relatório.

Dentro do grupo escolhido em Filtro de Re upo referente ao Filtro de Relatório.

a informação Rótulo de linha, se refere a inform bela dinâmica, e nada tem a ver se os rótulos da t

Dentro do grupo escolhido em Filtro, o campo do grupo linha e relacionado com o grupo Filtro d informação Rótulo de Coluna, se refere a inform una da tabela dinâmica, e nada tem a ver se os

em linha ou coluna.

olocado campos numéricos ele calcula ou quantif antifica os dados, sempre relacionados aos grupos

ir, na janela lista de campos da Tabela dinâmica o e Saldo devedor e confira a janela a seguir:

assim que foram selecionados os campos prop colocou na janela rótulo de linha. Para prosseguir abela dinâmica, como o grupo macro do enuncia ro de Relatório, e confira com a janela a seguir:

83

ta a esta pergunta, deve ser elatório, Rótulo de Linha é o

mação que estará sendo exibida tabela selecionada inicialmente o selecionado para coluna, está

de Relatório.

mação que estará sendo como s rótulos da tabela selecionada fica e quando colocado campos s em questão.

a, selecione os campos: Curso,

postos conforme enunciado, o r, o próximo passo é a escolha

8. Agora, arraste o campo nome para Rótulos de Coluna, e confira:

Observe que a escolha não foi muito boa, pelo motivo do campo nome ter muita informação diferenciada, assim a tabela dinâmica estendeu-se na horizontal, o que dificulta a navegação e no caso de impressão dificultará muito o entendimento e análise dos dados.

9. Dando continuidade, retorne com o campo nome em Rótulos de linha e arraste o campo período em Rótulos de Coluna, confira a seguir: