Baixe o app para aproveitar ainda mais
Prévia do material em texto
sĂůĠƌŝĂ�WŝĐĐŽůŝ�'ŽŶnjĂůĞƐ� �džĐĞů�ϮϬϬϳ �ǀĂŶĕĂĚŽ ADMINISTRAÇÃO REGIONAL DO SENAC NO ESTADO DE SÃO PAULO 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 Quéops Design S/C Ltda. EXCEL 2007 - AVANÇADO 2007 Excel 2007 - Avanç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 SubTotais .............. ................................................................................................................... .121 Excel 2007 - Avançado II Senac São Paulo 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 – Projetandodias ú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 Atividade 2 – Usando Corresp ............................................................................................. 225 Excel 2007 - Avançado 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 Exercícios complementares ....................................................................................................... 303 Excel 2007 - Avançado IV Senac São Paulo Excel 2007 Avançado 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 formata seus campos, gerando o vínculo Excel 2007 Avançado 2 Senac São Paulo Excel 2007 Avançado 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 primeirode às próximas etapas. a planilha basta clicar no botão: s, clique na opção De Texto. Excel 2007 Avançado 4 Senac São Paulo 4. Na caixa Examinar:, localize a pasta treinamento, e dentro dela o arquivo base.txt. Clique sobre o arquivo para selecioná-lo. 5. Clique no botão importar, e será exibida a janela a seguir: Excel 2007 Avançado 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: 6 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 Excel 2 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. 2007 Avançado Senac São Paulo 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 Excel 2007 Avançado 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 12. Salve o arquivo e volte ao Excel para prosseguir. 8 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 Excel 2 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: 2007 Avançado Senac São Paulo texto, ao abrir o , e será exibida a a, e é nesta janela sultado. etapas seguintes, Excel 2007 Avançado 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. Excel 2007 Avançado 10 Senac São Paulo ANOTAÇÕES Excel 2007 Avançado 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. o Do Access: Excel 2007 Avançado 12 Senac São Paulo 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. Excel 2007 Avançado Senac São Paulo Na versão 2007 aparecerá a guia D 7 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: Excel 2007 Avançado 14 Senac São Paulo ANOTAÇÕESExcel 2007 Avançado 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: 16 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õ Excel 2 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 2007 Avançado Senac São Paulo , então você está ta tela difere das unidades. ê terá a janela a rece, , cê terá acesso a eguir: Excel 2007 Avançado 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: 9. Clique em avançar e teremos a janela a seguir: Excel 2007 Avançado 18 Senac São Paulo 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: 13. Dê avançar e terá a seguinte janela: Excel 2007 Avançado 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: Excel 2007 Avançado 20 Senac São Paulo 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. Excel 2007 Avançado 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 cente, clique na opção Mais, e Excel 2007 Avançado 22 Senac São Paulo 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: 5. Assim você visualizará a janela: Excel 2007 Avançado 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: Excel 2007 Avançado 24 Senac São Paulo 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: Excel 2007 Avançado 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. Excel 2007 Avançado 26 Senac São Paulo ANOTAÇÕESExcel 2007 Avançado 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. Excel 2007 Avançado 28 Senac São Paulo Excel 2007 Avançado 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 Excel 2007 Avançado 30 Senac São Paulo 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: Excel 2007 Avançado 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 D1 e ative a opção selecionar tudo, ou a opção Limpar Filtro de Cidade, como segue: 32 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 Excel 2 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: 2007 Avançado Senac São Paulo modo filtro como ados, e no grupo dição e clique na Excel 2007 Avançado 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 15. Salve as alterações para que esta planilha possa ser utilizada em outras atividades. Excel 2007 Avançado 34 Senac São Paulo ANOTAÇÕES Excel 2007 Avançado 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. 36 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 Excel 2 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. 2007 Avançado Senac São Paulo elecione a opção elecione a lista finidas, como os e de classificar os urso, clicando em rquia que precisa Excel 2007 Avançado Senac São Paulo 37 6. Assim você visualizará a janela: 7. Dê ok para finalizar e confira com o resultado a seguir: 8. Salve a pasta de trabalho para usarmos nas atividades posteriores.Excel 2007 Avançado 38 Senac São Paulo ANOTAÇÕES Excel 2007 Avançado 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 40 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 Excel 2 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. 2007 Avançado Senac São Paulo ntando para cima, alificada e dentro Excel 2007 Avançado 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 r: 42 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 Excel 2 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: 2007 Avançado Senac São Paulo m campo esteja er a ordem, caso o fomos até a cor e tivéssemos mais Excel 2007 Avançado 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. Excel 2007 Avançado 44 Senac São Paulo ANOTAÇÕES Excel 2007 Avançado 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 Excel 2007 Avançado 46 Senac São Paulo 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: Excel 2007 Avançado 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çãoCopiar para outro local, como segue: 48 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, Excel 2 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 2007 Avançado Senac São Paulo 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, mo segue: Excel 2007 Avançado 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: Excel 2007 Avançado 50 Senac São Paulo 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: Excel 2007 Avançado 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: 30. Clique novamente no botão, , para retornar a janela Filtro Avançado, como segue: Excel 2007 Avançado 52 Senac São Paulo 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: 34. Dê ok para finalizar e verifique os resultados a seguir: Excel 2007 Avançado 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, Bdextrair, Bdmáx, Bdmín, bdcontara. Excel 2007 Avançado 54 Senac São Paulo Excel 2007 Avançado 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. Excel 2007 Avançado 56 Senac São Paulo 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: Excel 2007 AvançadoSenac 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: 10. Para concluir arraste a função para as demais células e verifique o resultado a seguir: 58 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 Excel 2 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: 2007 Avançado Senac São Paulo 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á Excel 2007 Avançado 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: 15. Salve as alterações para prosseguir a atividade. Excel 2007 Avançado 60 Senac São Paulo 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: = ESQUERDA(Original!D4;2) Excel 2007 Avançado 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: =EXT.TEXTO(Original!D4;3;2) Excel 2007 Avançado 62 Senac São Paulo 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: 29. Salve as alterações. Excel 2007 Avançado 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: ocê deverá selecionar a opção Excel 2007 Avançado 64 Senac São Paulo 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: Excel 2007 Avançado Senac São Paulo 65 10. Selecione a opção transpor e dê ok para finalizar, acompanheo resultado a seguir: 11. O que era coluna passa a ser representado em linha, como você pode observar na imagem anterior. Excel 2007 Avançado 66 Senac São Paulo ANOTAÇÕES Excel 2007 Avançado 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 Excel 2007 Avançado 68 Senac São Paulo 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: Excel 2007 Avançado 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) Excel 2007 Avançado 70 Senac São Paulo 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 é: =bdmédia(banco de dados; rótulo da coluna em questão; critérios) Excel 2007 Avançado 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 Excel 2007 Avançado 72 Senac São Paulo 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: Excel 2007 Avançado 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 selecioneo 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: =BDMÍN(geral;'Controle de Produtos'!D1;Critérios!A1:A2) Excel 2007 Avançado 74 Senac São Paulo 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: Excel 2007 Avançado 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 =BDEXTRAIR(geral;'Controle de Produtos'!A1;Critérios!A10:B11) 76 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 Excel 2 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) 2007 Avançado Senac São Paulo 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é dos a seguir: Excel 2007 Avançado 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: Fornecedor Doces do Brasil S/A: Excel 2007 Avançado 78 Senac São Paulo =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 =BDCONTARA(geral;'Controle de Produtos'!A1;Critérios!A10:A11) Excel 2007 Avançado 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. Excel 2007 Avançado 80 Senac São Paulo Excel 2007 Avançado 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,
Compartilhar