Baixe o app para aproveitar ainda mais
Prévia do material em texto
Excel 2010 – Módulo III – Curso Avançado – Aprenda Através de Exemplos Práticos Autor 01: José Eduardo Chamon Módulo III Excel 2010 - Avançado Autor: José Eduardo Chamon e-mail: edu@siteamigo.com Site: http://www.ensinandoexcel.com.br � Nota sobre direitos autorais: Este E-book é de autoria de José Eduardo Chamon, sendo comercializado diretamente através do site www.juliobattisti.com.br ou através do site de leilões Mercado Livre: www.mercadolivre.com.br ou diretamente com o autor. Ao adquirir este E-book você tem o direito de lê-lo na tela do seu computador e de imprimir uma cópia para o seu uso pessoal. É vetada a distribuição deste arquivo, mediante cópia ou qualquer outro meio de reprodução, para outras pessoas. Se você recebeu este E-book através do e-mail ou via ftp de algum site da Internet, ou através de um CD de Revista ou via email recebido de um “amigo”, saiba que você está com uma cópia pirata, não autorizada. A utilização de uma cópia pirata, não autorizada, é crime de Violação de Direitos Autorais conforme Leis 9118 e 9610, sujeita a pena de 2 a 5 anos de Cadeia. Denuncie o site ou revista que está disponibilizando a cópia, através do e-mail webmaster@juliobattisti.com.br O valor cobrado por este E-book é praticamente simbólico, pelas horas e horas de trabalho que ele representa. Novos cursos somente podem ser desenvolvidos graças a honestidade de pessoas que adquirem o arquivo do curso e não o distribuem livremente para outras pessoas. Se você recebeu uma cópia deste arquivo sem tê-la adquirido diretamente com o autor, seja honesto, entre em contato com o autor, através do e-mail webmaster@juliobattisti.com.br, para regularizar esta cópia. Ao regularizar a sua cópia você irá remunerar, mediante uma pequena quantia, o trabalho do autor e incentivar que novos trabalhos sejam disponibilizados. Se você tiver sugestões sobre novos cursos que gostaria de ver disponibilizados, entre em contato pelo e-mail: webmaster@juliobattisti.com.br. Visite periodicamente o site www.juliobattisti.com.br para ficar por dentro das novidades: Mais de 2000 e-books, cursos, livros e vídeo aulas sobre diversos assuntos. Artigos e dicas sobre Certificações da Microsoft. Artigos sobre Carreira e Trabalho. Centenas de Livros sobre os mais Variados Assuntos. Dicas de livros e sites sobre diversos assuntos. Simulados gratuitos, em português, para os exames da Microsoft. PIRATARIA É CRIME, COM PENA DE CADEIA DE ACORDO COM A LEI 9118. EU AGRADEÇO PELA SUA HONESTIDADE. SE VOCÊ COMPROU UMA CÓPIA DESTE CURSO, DIRETAMENTE COM O AUTOR, NÃO DISTRIBUA CÓPIAS PARA OUTRAS PESSOAS. SE VOCÊ RECEBEU UMA CÓPIA ILEGAL DESTE ARQUIVO, NÃO ADQUIRIDA DIRETAMENTE COM O AUTOR JÚLIO BATTISTI, ENTRE EM CONTATO E REGULARIZE A SUA CÓPIA. ESTE E-BOOK NÃO PODE SER FORNECIDO EM UM CD OU DVD DE NENHUMA REVISTA SE VOCÊ OBTEVE UMA CÓPIA DESTE E-BOOK ATRAVÉS DO E-MULE, KAZAA, MORPHEUS OU OUTRO PROGRAMA DE COMPARTILHAMENTO DE ARQUIVOS, SAIBA QUE VOCÊ ESTÁ COM UMA CÓPIA ILEGAL, NÃO AUTORIZADA USAR UMA CÓPIA NÃO AUTORIZADA É CRIME DE VIOLAÇÃO DE DIREITOS AUTORAIS, COM PENA PREVISTA DE CADEIA, CONFORME LEI 9118 VOCÊ SÓ PODE USAR ESTE E-BOOK SE VOCÊ COMPROU ELE DIRETAMENTE COM O AUTOR: JÚLIO BATTISTI Cada arquivo PDF tem um número de série interno que é único para cada Cópia. O número de série está associado com o teu nome, que foi quem comprou esta cópia dos cursos. Se esta cópia for colocada para livre download na Internet, eu tenho como saber quem foi que colocou e saberei quem processar por Violação de Direito Autoral, conforme previsto nas Leis 9118 e 9610. Por isso peço que não repasse cópia destes cursos para outros usuários. Eles são de uso individual. PIRATARIA É CRIME, COM PENA DE CADEIA. EU AGRADEÇO PELA SUA HONESTIDADE. SE VOCÊ COMPROU UMA CÓPIA DESTE CURSO, DIRETAMENTE EM WWW.JULIOBATTISTI.COM.BR OU DIRETAMENTE COM O AUTOR, NÃO DISTRIBUA CÓPIAS PARA OUTRAS PESSOAS. SE VOCÊ BAIXOU UMA CÓPIA DESTE ARQUIVO USANDO UM SOFTWARE TAL COMO O E-MULE OU O KAZAA, SAIBA QUE VOCÊ ESTÁ COM UMA CÓPIA PIRATA, ILEGAL. USAR UMA CÓPIA ILEGAL É CRIME DE VIOLAÇÃO DE DIREITOS AUTORAIS. ESTE ARQUIVO NÃO PODE SER DISTRIBUIDO GRAVADO EM UM CD OU DVD DE REVISTA OU LIVRO. A ÚNICA MANEIRA DE OBTER ESTE ARQUIVO É COMPRANDO DIRETAMENTE COM O AUTOR OU ATRAVÉS DO SITE WWW.JULIOBATTISTI.COM.BR SE VOCÊ RECEBEU UMA CÓPIA ILEGAL DESTE ARQUIVO, NÃO ADQUIRIDA DIRETAMENTE PELOS MEIOS DESCRITOS NO INÍCIO DA PÁGINA, ENTRE EM CONTATO ATRAVÉS DO E-MAIL WEBMASTER@JULIOBATTISTI.COM.BR E REGULARIZE A SUA CÓPIA. � Algumas palavras do autor: Este curso poderá ser feito por quem já utiliza o Microsoft Office Excel no seu dia a dia, de versões anteriores ou que estejam utilizando a versão 2010. Nosso objetivo é prover ensinamentos do Excel 2010 no nível avançado aprimorando os conceitos da nova versão. Todos os exemplos/exercícios aqui apresentados, foram feitos de forma simples para ser de fácil aprendizagem. O conteúdo abrange quase todos os requisitos para você utilizar o máximo do Excel em sua máquina. Neste módulo você irá aprender algumas funções como por exemplo: Datam, calcular diferença entre duas datas, ProcV, ProcH, Desloc, gráficos: velocímetro, pizza, barras com tendência, uso do Word junto com o Excel, formatação condicional e filtros. Um bom estudo a todos e espero, sinceramente, que este curso possa ajudá-los a enriquecer seus conhecimentos e proporcionar uma melhor posição no mercado de trabalho. � Excel 2010 – MÓDULO III Curso Avançado Incrementando Seus conhecimentos Aperfeiçoando A sua prática � ÍNDICE . Função Datam.....................................................................................003 . Diferença entre Datas...........................................................................004 . Função ProcV......................................................................................005 . Exercício de fixação 1 - parte final . Função ProcH..................................................................................... 008 . Exercício de fixação 2 - parte final . Função Desloc.................................................................................... 011 . Carregar Arquivo Texto e Retirar Itens Duplicados.................................. 016 . Gráfico – Dinâmico – Gráfico de Velocímetro.......................................... 020 . Gráfico – Estático .............................................................................. 026 . Gráfico de Pizza........................................................................ 026 . Gráfico de Barras com linha de tendência...................................... 031 . Gráfico de Barras mostrando dois valores...................................... 037 . Montando uma Carta no Word com Dados do Excel................................. 042 . Formatação Condicional....................................................................... 048 . Filtros Automáticos............................................................................. 057 . Filtros Avançados............................................................................... 059 . Exercícios - Prática . Exercício fixação 1 – Função ProcV............................................... 063 . Exercício fixação 2 – Função ProcH.............................................. 064 . Exercícios - Respostas . Exercício fixação 1 – Função ProcV............................................... 066 . Exercício fixação 2 – Função ProcH............................................... 067 � FUNÇÃO DATAM OBJETIVO Esta função tem como finalidade retornar uma data acrescida ou subtraída de uma quantidade de meses. Esta função faz parte do suplementoFerramentas de análise do Excel. Na barra de fórmulas clique em fx e no item Data e Hora encontrará a função DATAM. SINTAXE DA FUNÇÃO DATAM(data_inicial; meses) Data_inicial Identifica uma data inicial. meses Identifica o número de meses que se deseja somar ou subtrair. EXEMPLO ►Arquivo 1 � FUNÇÃO DATADIF OBJETIVO Esta função tem o objetivo de efetuar cálculos entre duas datas. SINTAXE DA FUNÇÃO DATADIF (Data inicial ; Data final ; Formato) Data inicial Identifica a data inicial a ser calculada (menor data). Data final Identifica a data final a ser calculada. Formato Identifica o formato em que se deseja a resposta. Y Número de anos completos no período M Número de meses completos no período D Número de dias no período MD Diferença entre os dias na data_inicial e data_final. Os meses e anos são ignorados YM Diferença entre os meses na data_inicial e data_final. Os dias e anos são ignorados YD Diferença entre os dias na data_inicial e data_final. Os anos são ignorados EXEMPLO ►Arquivo 2 � FUNÇÃO PROCV OBJETIVO O objetivo desta função é o de procurar o primeiro valor na coluna mais a esquerda de um conjunto de células informadas, que satisfaça uma determinada condição. SINTAXE DA FUNÇÃO PROCV(Valor_procurado ; Matriz_tabela ; Núm_índice_lin ; Procurar_intervalo) Valor_procurado Identifica o valor que se deseja procurar (o que eu quero). Matriz_tabela Identifica o conjunto de valores em que se deseja efetuar a pesquisa. Núm_índice_lin Identifica a coluna na qual se deseja obter o valor. Procurar_intervalo Poderá ser identificado por dois valores: verdadeiro ou falso. Verdadeiro retorna o valor mais próximo que for encontrado, já o falso retornará o valor exato da procura. � EXEMPLO I ►Arquivo 3 Na célula A7 vamos pesquisar qual foi a nota exata da Prova 1 para Pedro Henrique. Se o nome Pedro Henrique estivesse duas vezes neste exemplo, o primeiro seria o selecionado. Vejamos a fórmula que foi utilizada: =PROCV(A7;A2:C5;3;FALSO) B10 – Valor a ser procurado, neste caso Pedro Henrique A2:C5 – Intervalo onde se deseja procurar o conteúdo Pedro Henrique. Note que a primeira coluna é a coluna a ser pesquisada. 3 – Identifica qual é a coluna que se deseja trazer o valor. FALSO – Identifica que a busca por este valor deva ser exata e não aproximada. O resultado obtido foi 4, equivale ao valor da célula C3, pois foi à primeira ocorrência encontrada de Pedro Henrique na tabela. Note que no parâmetro foi usado o termo FALSE, ou seja, irá trazer o valor exatamente igual ao procurado. ►Arquivo 3 � EXEMPLO II No caso abaixo, vamos encontrar o percentual de comissão baseado no valor da venda em um determinado mês. Note que no parâmetro foi usado o termo VERDADEIRO, ou seja, irá trazer o maior valor mais próximo do valor procurado. ►Arquivo 3 EXEMPLO III No exemplo abaixo temos uma tabela de aging de datas, baseado nos vencimentos de cada linha. ►Arquivo 3 EXEMPLO IV Quando a coluna estiver em ordem alfabética e o valor como VERDADEIRO, irá retornar a última ocorrência da pesquisa. ►Arquivo 3 FUNÇÃO PROCH OBJETIVO O objetivo desta função é o de procurar um valor em uma linha especifica de um conjunto de células informadas, que satisfaça uma determinada condição. SINTAXE DA FUNÇÃO PROCH(Valor_procurado ; Matriz_tabela ; Núm_índice_lin ; Procurar_intervalo) Valor_procurado Identifica o valor que se deseja procurar. Matriz_tabela Identifica o conjunto de valores em que se deseja efetuar a pesquisa. Núm_índice_lin Identifica a linha na qual se deseja obter o valor. Procurar_intervalo Poderá ser identificado por dois valores: verdadeiro ou falso. Verdadeiro retorna o valor mais semelhante na linha inferior, já o falso retornará o valor exato da procura. � EXEMPLO I ►Arquivo 4 Na célula B10 vamos pesquisar qual foi a nota exata da Prova 1 da pesquisa que irá retornar o conteúdo da linha 8. Vejamos a fórmula que foi utilizada: =PROCH(B10;B1:G8;8;FALSO) B10 – Valor a ser procurado, neste caso Prova 1 B1:G8 – Intervalo onde se deseja procurar o conteúdo Prova 1. 8 – Identifica qual é a linha que se deseja trazer o valor. FALSO – Identifica que a busca por este valor deva ser exata e não aproximada. � EXEMPLO II ►Arquivo 4 � FUNÇÃO DESLOC OBJETIVO Retorna o valor de uma célula que esta afastada de um determinado valor. SINTAXE DA FUNÇÃO DESLOC (Ref; Lins; Cols; Altura; Largura) Ref Identifica a célula na qual irá dar início ao deslocamento. Lins Identifica o número de linhas acima ou abaixo a ser deslocado. Cols Identifica o número de colunas acima ou abaixo a ser deslocado. Altura Identifica a altura em número de linhas na qual se deseja que o resultado se apresente. Largura Identifica a largura em número de colunas na qual se deseja que o resultado se apresente. � EXEMPLO I No exemplo 101 ( a célula A8 que apresenta o valor 101 – significa que a partir da célula C2, irá buscar duas células abaixo e deslocar uma célula para a esquerda. No exemplo Maria ( a célula A10 que apresenta o texto Maria – significa que a partir da célula C2, irá buscar duas células abaixo e deslocar duas células para a esquerda. ►Arquivo 5 EXEMPLO II Abra uma nova planilha. Na Plan2 crie uma lista com 100 valores. ►Arquivo 5 Na Plan1 crie uma tabela conforme figura abaixo. Entre no menu Desenvolvedor, Inserir e use a Guia Controles de Formulário, a opção Barra de Rolagem. � Coloque a barra na coluna D, de forma que ocupe as 10 linhas de nossa tabela. Na barra, clique com o botão direito do mouse e escolha a opção formatar controle. Na Aba propriedades, escolha a opção Mover mas não dimensionar com células e depois na aba Controle, preencha conforme os dados abaixo. Na Plan3, coloque na célula C5 o texto Posição Atual e na célula D5 o valor 1. Este valor vai indicar em qual posição estamos no barra de rolagem. Na Plan1, digite os valores abaixo. Na célula B6 =DESLOC(Plan2!A1;Plan3!$D$5;0;1;1) Na célula C6 =DESLOC(Plan2!B1;Plan3!$D$5;0;1;1) Na célula E6 =DESLOC(Plan2!C1;Plan3!$D$5;0;1;1) Arraste ás células B e C até completar o quadro. Faça o mesmo de E6 a I6 e depois arraste até completar a tabela. Formate as células E6 a I15. Entre no menu, formatar, células, personalizado e digite #.##0_ ;[Vermelho]-#.##0 Pronto, basta testar a sua planilha. O que fizemos aqui chama-se Dashboard Tabela Scrool. � CARREGAR ARQUIVO TEXTO E RETIRAR ITENS DUPLICADOS OBJETIVO Tem como finalidade separar coluna de dados e retirar itens duplicados. PROCEDIMENTO Abra o bloco de notas (botão iniciar, executar, digite notepad e tecle enter) e digite os dados conforme figura abaixo. Os dados abaixo estão dispostos da seguinte maneira: Nome Idade Estado Civil Profissão. Nome que o nome Maria da Silva foi repetido propositalmente. ►Avancado.txt Você pode copiar os dados e colar na célula A1, ou salvar o arquivo no seu Desktop e abri-lo no Excel. � Selecione todos os dados. Copie e cole na célula A1. Selecione a coluna A. No menu clique em Dados, Texto para Colunas. � Escolha a opção delimitado, Ponto e vírgula e concluir. Note que Maria da Silva apareceu 2 vezes. Vamos falar para o Excel que se o nome que estiver na coluna A se repetir, ele deverá excluir a linha que estiver em duplicidade. Selecione as colunas de A a D. � Clique no menu, Dados, Remover Duplicadas. Escolha o item que deverá ser verificada a duplicidade. Neste Exemplo, clique na coluna A. GRÁFICO DE VELOCÍMETRO OBJETIVO Montar um gráfico de desempenho – velocímetro – conforme a figura abaixo. PROCEDIMENTO ►Arquivo 6 No menu, selecione inserir,gráfico e escolha o tipo Rosca. � � Clique no gráfico e no menu superior clique em Layout e Título do Gráfico Escolha a opção acima do gráfico � � GRÁFICO DE PIZZA OBJETIVO Montar um gráfico de Pizza – conforme a figura abaixo. ►Arquivo 7 PROCEDIMENTO Mude o nome de sua folha de dados para Suporte_1_2 � No menu, escolha a opção: Inserir, Pizza, Pizza Destacada 3D Mude o nome de sua folha de dados para Suporte_1_2 Do lado esquerdo da janela selecionar fontes de dados, escolha os dados do mês de fevereiro (C5 a C13). No Eixo Horizontal, selecione as células com os nomes das frutas (A5 a A13). Sobre os valores clique com o botão direito do mouse e escolha a opção fonte para mudar a fonte dos dados. Em seguida, clique no gráfico e no menu, escolha a opção Layout. Insira um título para o gráfico. � GRÁFICO DE BARRAS COM LINHA DE TENDÊNCIA OBJETIVO Montar um gráfico de barras com linha de tendência – conforme a figura abaixo. � PROCEDIMENTO Mude o nome de sua folha de dados para Suporte_4_5 ►Arquivo 8 No menu, escolha a opção: Inserir, Colunas, Colunas Agrupadas � � � GRÁFICO DE BARRAS MOSTRANDO DOIS VALORES OBJETIVO Montar um gráfico de barras mostrando dois valores – conforme a figura abaixo. � PROCEDIMENTO Mude o nome de sua folha de dados para Suporte_6 ►Arquivo 9 � MONTANDO UMA CARTA NO WORD COM DADOS DO EXCEL OBJETIVO Montar uma carta no Word, utilizando os dados do Excel. Procedimento Monte uma planilha conforme os dados do Excel. ►Arquivo 10 Salve esta planilha em seu Desktop como nome de Carta.xlsx� Abra o Word. Crie uma carta no modelo que deseja. Entre no menu, Correspondências, Iniciar Mala Direta e escolha a opção Cartas. Entre no menu, Correspondências, Iniciar Mala Direta e escolha a opção Assistente de Mala Direta passo a passo. Escolha o item Cartas. Utilizar o documento atual. No item usar uma lista existente, clique em procurar. Selecione o arquivo desejado (no Desktop – Carta.xlsx). Escolha a folha de dados onde estão os seus dados. Ex. Plan1$ � Vai obter uma tela conforme a figura abaixo. Clique em Ok � Para inserir os campos clique no menu, correspondências, Inserir Campo de Mesclagem. Veja como cada campo aparece em seu texto. � Para visualizar seus dados, clique em Visualizar Resultados. E depois em Concluir e Mesclar para a impressão. � FORMATAÇÃO CONDICIONAL Monte uma planilha conforme o exemplo abaixo. ►Arquivo 11 Vamos criar uma lista com as opções distintas da coluna C: novo, usado e reparado. Nas células H1, H2 e H3 preencha os dados com novo, usado e reparado. � Na célula E3 digite Status e na célula F3 monte uma lista com os itens de H1 a H3. Clique na célula F3, no menu Dados, Validação de Dados, Validação de Dados, escolha a opção Lista e selecione o intervalo de H1 a H3, conforme a figura abaixo. Selecione as células de A2 a C2. No menu, Início, escolha a opção Formatação Condicional, Nova Regra e escolha a opção: Usar uma fórmula para determinar quais células devem ser formatadas Digite a seguinte fórmula =SE(ÉERRO(PROCURAR($F$3;$C2;1));1;0)=0 Na opção Formatar, escolha uma cor qualquer � Selecione as células de A2 a C2. No menu Início, clique em Formatar Pincel e selecione o restante das células para ter a mesma formatação. � Para testar, altere a célula F3. � Você também poderá criar linhas formatadas em forma de barras. Basta ir à sua coluna de valores, selecionar os dados e ir no menu Início, Formatar Condicional e escolher a opção Barra de Dados. Veja o exemplo abaixo. � Você e seus amigos jogaram vários cartões na Sena e você ficou responsável pela conferência dos jogos. Vamos criar uma maneira fácil e rápida de conferir todos os seus cartões. Vamos criar uma tabela igual ao exemplo abaixo. ►Arquivo 11 Clique na célula B1. No menu, clique em Início, Formatação Condicional, Nova Regra. Escolha a opção: Usar uma fórmula para determinar quais células devem ser formatadas Digite a seguinte fórmula =cont.se($B$8:$G$8;B2)>0 Em Formatar escolha uma cor � Selecione as células de B2. No menu Início, clique em Formatar Pincel e selecione o restante das células para ter a mesma formatação. Veja o resultado. Mude os valores da linha 8 e veja o que acontece. � Vamos formatar a planilha abaixo com uma linha de cada cor. Crie uma planilha conforme o exemplo abaixo. ►Arquivo 11 Selecione toda a planilha. No menu, clique em Início, Formatação Condicional Nova Regra. Escolha a opção: Usar uma fórmula para determinar quais células devem ser formatadas Digite a seguinte fórmula =mod(lin();2)=0 Em Formatar escolha uma cor Clique novamente em Nova Regra Escolha a opção: Usar uma fórmula para determinar quais células devem ser formatadas Digite a seguinte fórmula =mod(lin();2)=0 Em Formatar escolha uma cor � Entre novamente em Formatação Condicional e altere as regras, onde era LIN() coloque COL() � FILTROS AUTOMÁTICOS Monte uma planilha conforme o exemplo abaixo. ►Arquivo 12 Para utilizarmos o filtro automático, execute os seguintes passos: - Selecione a 1a. linha da tabela - Selecione no menu início a opção Ordenar e Filtrar Vamos obter o seguinte resultado � Escolhendo o filtro empresa, vamos obter as seguintes opções � FILTROS AVANÇADOS - Filtros avançados, são os filtros que podem usar um critério avançado, como por exemplo: fórmulas. - É necessário deixar algumas linhas em branco acima da tabela que desejamos fazer o filtro. - Os cabeçalhos deverão estar escritos de igual forma à tabela. - Os filtros avançados são sensíveis as letras maiúsculas e minúsculas, vale uma ou outra condição. - Os campos de cabeçalho podem ser duplicados Vamos supor como exemplo a seguinte tabela. Note que deixamos algumas linhas em branco. ►Arquivo 13 � Vamos criar o cabeçalho do filtro com os mesmos dados do cabeçalho da tabela Basta preencher os dados e clicar no menu dados e em filtros avançados. � Vamos obter os dados conforme figura abaixo. Para cancelar os filtros, entre no menu, dados e clique em limpar. � Excel 2010 EXERCÍCIOS DE FIXAÇÃO prática Aperfeiçoando A sua prática � EXERCÍCIO DE FIXAÇÃO – 1 – PROCV Monte uma planilha igual à figura abaixo e preencha os dados faltantes. ►Arquivo 14 � EXERCÍCIO DE FIXAÇÃO – 2 – PROCH Monte uma planilha igual à figura abaixo e preencha os dados faltantes. ►Arquivo 15 � Excel 2010 EXERCÍCIOS DE FIXAÇÃO RESPOSTAS Aperfeiçoando A sua prática � EXERCÍCIO DE FIXAÇÃO – 1 - PROCV � EXERCÍCIO DE FIXAÇÃO – 2 - PROCH �PAGE � Página � PAGE �1�/� NUMPAGES �74� É Proibido o uso deste Material em Sala de Aula e/ou Para Ministrar Treinamentos.
Compartilhar