Baixe o app para aproveitar ainda mais
Prévia do material em texto
INSTRUTOR: SÉRGIO RICARDO BARBOSA MARTINS Curso de Excel Profissional 3 Índice Capítulo 01........................................................................................6 Teclas de Atalho...............................................................................6 Operadores de Cálculos em Fórmulas.............................................11 Operadores de Comparação.............................................................12 Operadores de Referência................................................................13 Menus e Barras de Ferramentas.......................................................14 Comandos Especiais – Colar especial..............................................15 Comando Ir Para..............................................................................20 Comando Inserir Nome....................................................................23 Formatação Condicional - Fórmula.................................................25 Capítulo 2.........................................................................................27 Sub Total..........................................................................................27 Protegendo Células Específicas.......................................................31 Fórmula Condicional........................................................................34 Capítulo 3.........................................................................................48 Validação.........................................................................................48 Funções PROCV e PROCH............................................................54 Consolidação de dados....................................................................56 Capítulo 4........................................................................................59 Macros – Reduzindo Tempo nas Rotinas do Trabalho...................59 Importação de Texto........................................................................62 Tabela Dinâmica..............................................................................73 Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 4 Gráfico Dinâmico............................................................................83 Gráfico Personalizado.....................................................................85 Capítulo 5.......................................................................................87 Solver.............................................................................................87 Função Subtotal..............................................................................92 Ferramentas de Análise..................................................................94 Função CONVERTER...................................................................98 Trabalhando com Datas e Horas...................................................104 Bibliografia...................................................................................108 Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 5 Curso de Excel Profissional Introdução O Microsoft Excel é um programa que contém várias ferramentas e inúmeras funções internas que dão liberdade ao usuário de criar planilhas dos mais variados tipos. Sua operação e flexibilidade permitem realizar quase todo tipo de controle onde o limite para cada criação é a imaginação do usuário somada ao seu conhecimento. A prática decorrente do uso contínuo como também o estudo do Excel são fundamentais para o aprendizado da funcionalidade desse programa. Objetivo Aprendizado das ferramentas avançadas do Excel 2003 e seus comandos mais específicos. Redução no tempo de trabalho de algumas rotinas através da construção de macros e fórmulas condicionais. A concentração na realização dos exercícios é imprescindível para um bom aproveitamento do treinamento. Metodologia Instrução teórica na apostila eletrônica com exercícios passo a passo no Excel 2003. O programa do curso apresentará exemplos que são parte cotidiana do controle e planejamento de dados. O selo que aparece ao lado do tópico, significa que o assunto faz parte da prova de certificação da Microsoft de especialista e experto em Excel. Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 6 Capítulo 01 1.1 Antes de iniciarmos o curso é interessante termos o conhecimento das teclas de atalho e dos operadores de cálculos nas fórmulas e comandos do Excel, Tabelas 01 e 02. Nova pasta de trabalho CTRL+O Abrir arquivo CTRL+A Imprimir CTRL+P Copiar CTRL+C Recortar CTRL+X Colar CTRL+V Inserir planilha SHIFT+F11 Fechar janela CTRL+F4 Fechar o Excel Alt+F4 Localizar e substituir CTRL+U Selecionar tudo CTRL+T Desfazer última ação CTRL+Z Mover para a próxima planilha CTRL+PgDn Mover para a planilha anterior CTRL+PgUp Selecionar a planilha atual e a seguinte SHIFT+CTRL+PgDn Selecionar a planilha atual e a anterior SHIFT+CTRL+PgUp Ir para o início da planilha CTRL+Home Ir para o final da planilha CTRL+End Inserir Gráfico F11 Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 7 Inserir um hiperlink CTRL+K Inserir data atual CTRL+; Inserir hora atual CTRL+SHIFT+: Caractere de centavo de dólar ALT+0162 Caractere de libra esterlina ALT+0163 Caractere de euro ALT+0128 Ocultar linhas selecionadas CTRL+9 Exibir novamente as linhas ocultas CTRL+SHIFT+( Ocultar colunas selecionadas CTRL+0 Exibir novamente as colunas selecionadas CTRL+SHIFT+) Inserir macro ALT+F8 Selecionar região corrente CTRL+SHIFT+* Selecionar matriz que contém a célula ativa CTRL+/ Selecionar todas as células com comentários CTRL+SHIFT+O Selecionar as células que não correspondem ao valor da célula ativa, em uma linha selecionada CTRL+\ Selecionar as células que não correspondem ao valor da célula ativa, em uma coluna selecionada CTRL+SHIFT+| Selecionar todas as células referidas diretamente por fórmulas CTRL+[ Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 8 Selecionar todas as células referidas direta ou indiretamente por fórmulas CTRL+SHIFT+{ Selecionar todas as céluas que contêm fórmulas que fazem referência direta à célula ativa CTRL+] Selecionar todas as céluas que contêm fórmulas que fazem referência direta ou indireta à célula ativa CTRL+SHIFT+} Selecionar as célulasvisíveis na seleção atual ALT+; Estender a seleção em uma célula SHIFT+Seta Estender a seleção até última célula não vazia na mesma linha ou coluna CTRL+SHIFT+tecla de direção Estender a seleção até o início da linha SHIFT+Home Estender a seleção até o início da planilha CTRL+SHIFT+Home Estender a seleção até a última célula usada CTRL+SHIFT+End Estender a seleção uma tela para baixo SHIFT+PgDn Estender a seleção uma tela para cima SHIFT+PgUp Criar nomes com base em rótulos de linha e coluna CTRL+SHIFT+F3 Inserir uma fórmula como fórmula de matriz CTRL+SHIFT+ENTER Inserir fórmula de autosoma com a função ALT+= Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 9 soma Calcular a planilha ativa SHIFT+F9 Calcular todas as planilhas em todas as pastas de trabalho abertas, independentemente delas terem sido ou não alteradas desde o último cálculo CTRL+ALT+F9 Verifica as fórmulas dependentes e depois calcula todas as células em todas as pastas de trabalho, inclusive as células que não estão marcadas para serem calculadas CTRL+ALT+SHIFT+F9 Exibir Auto-filtro da coluna atual ALT+seta para baixo Inserir fórmulas ou valores nas células selecionadas CTRL+Enter Fechar Auto-filtro da coluna atual ALT+seta para cima Tabela 01 Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 10 FORMATAÇÃO VIA TECLADO Formatar estilos ALT+' Formatar células CTRL+1 Formatar número Geral CTRL+SHIFT+~ Formatar Moeda com duas casas decimais CTRL+SHIFT+$ Formatar Porcentagem sem casas decimais CTRL+SHIFT+% Formatar Exponencial com duas casas decimais CTRL+SHIFT+^ Formatar Data CTRL+SHIFT+# Formatar Hora CTRL+SHIFT+@ Formatar número com duas casas decimais, separador de milhar e sinal (-) para valores negativos CTRL+SHIFT+! Negrito CTRL+N Itálico CTRL+I Sublinhado CTRL+S Formatar borda superior ALT+T Formatar borda inferior ALT+B Formatar borda esquerda ALT+L Formatar borda direita ALT+R Formatar linhas divisórias horizontais ALT+H Tabela 02 Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 11 1.2 Operadores de cálculos em fórmulas Os operadores especificam o tipo de cálculo que você deseja efetuar nos elementos de uma fórmula. O Microsoft Excel inclui quatro tipos diferentes de operadores de cálculo: aritméticos, de comparação, texto e referência. Operadores aritméticos - Para efetuar operações matemáticas básicas, como adição, subtração ou multiplicação, combinam números e produzem resultados numéricos, use os seguintes operadores aritméticos. Tabela 03. Operador aritmético Significado Exemplo + (sinal de adição) Adição 3+3 – (sinal de subtração) Subtração Negação 3–1 –1 * (sinal de multiplicação) Multiplicação 3*3 / (sinal de divisão) Divisão 3/3 % (símbolo de percentagem) Percentagem 20% ^ (sinal de exponenciação) Exponenciação 3^2 (igual a 3*3) Tabela 03 Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 12 1.3 Operadores de comparação Você pode comparar dois valores com os seguintes operadores. Quando dois valores são comparados usando esses operadores, o resultado é um valor lógico, ou VERDADEIRO ou FALSO. Tabela 04. Operador de comparação Significado Exemplo = (sinal de igual) Igual a A1=B1 > (sinal de maior do que) Maior do que A1>B1 < (sinal de menor do que) Menor do que A1<B1 >= (sinal de maior ou igual a) Maior ou igual a A1>=B1 <= (sinal de menor ou igual a) Menor ou igual a A1<=B1 <> (sinal de diferente) Diferente A1<>B1 Tabela 04 1.4 Operador de concatenação de texto Use o 'E' comercial (&) para agrupar, ou concatenar, uma ou mais seqüências de caracteres de texto para produzir um único texto.Tabela 05. Operador de texto Significado Exemplo & (E comercial) Conecta ou concatena dois valores para produzir um valor de texto contínuo "north" & "wind" produz "Northwind" Tabela 05 Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 13 1.5 Operadores de referência Combine intervalos de células para cálculos com os seguintes operadores.Tabela 06. Operador de referência Significado Exemplo : (dois-pontos) Operador de intervalo, que produz uma referência a todas as células entre duas referências, incluindo as duas referências B5:B15 ; (ponto-e-vírgula) Operador de união, que combina diversas referências em uma referência SOMA(B5:B15;D5:D15) Tabela 06 1.6 Menus e Barra de Ferramentas Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 14 Barra do Menu Principal: ferramentas principais da planilha. Barra Padrão: ferramentas para edição direta da planilha. Barra de Formatação: formatação da planilha. Barra de Fórmulas: criação de cálculos da planilha. Campo de nome Barra de Fórmula No Campo de nome podemos definir o nome de uma lista num banco de dados, ou uma tabela para referência de valores ou simplesmente posicionarmos o cursor em um endereço de célula. Podemos também estabelecer o nome de uma fórmula. Na Barra de fórmulas criamos os cálculos necessários para o desenvolvimento lógico da planilha. Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 15 1.7 Comandos Especiais: 1.7.1 Colar especial → Valores No Exemplo1, uma célula da planilha contendo fórmula poderá ser copiada e em seguida colada como valor, extraindo assim a fórmula existente anteriormente, Figura 01. A Fórmula Agora() exibe data e hora atual na célula B1. Figura 01 Posicione o cursor na célula B1 depois clique em Editar e selecione Copiar. A célula B1 entrará no modo de edição, Figura 02. Modo de edição. Figura 02 Depois clique em Editar, Colar especial, Figura 03. Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 16 Figura 03 Na caixa Colar especial selecione Valores e clique em OK, Figura 04. Figura 04 Tecle em Esc para sairdo modo de edição. A célula B1 passa a ter um valor de data onde antes era uma fórmula, Figura 05. Essa operação é muito útil quando temos que copiar uma planilha com fórmulas, de um determinado arquivo, para outra planilha em outro Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 17 arquivo sem ter que deixar vínculos entre as duas, apenas colando os valores. Figura 05 1.7.2 Colar Especial → Operação Esse comando atribui as quatro operações básicas da matemática na edição copiar e colar. No Exemplo2 temos uma posição de célula editada na opção Adição, Figura 06. Vamos copiar a área C6:C9 e em seguida Colar especial, Operação e a opção Adição no intervalo F6:F9. Os valores do intervalo C6:C9 foram adicionados aos valores do intervalo F6:F9. Essa operação também pode ser feita de uma planilha para outra ou de um arquivo para outro da mesma forma. Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 18 Figura 06 1.7.3 Colar Especial → Transpor Para copiar dados que estão organizados na posição vertical (colunas) e colar na posição horizontal (linhas) ou vice e versa existe a opção Transpor do comando Colar especial, Exemplo3, Figura 07. Opção Transpor Figura 07 Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 19 No Exemplo3 a área A1:B5 que se encontra organizada em duas colunas, com os respectivos rótulos de coluna Item e Valor, foi copiada e colada na área A8:E9, organizadas em duas linhas, Figura 08. Para editar no modo inverso o procedimento é o mesmo do índice 1.7.3. Figura 08 Com esses três exemplos podemos nos orientar para as demais opções do Colar especial, ou seja, selecionando a opção Fórmulas você irá colar apenas as fórmulas da planilha ou escolhendo Formatos será colada apenas a formatação da célula. Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 20 1.8 Comando Ir para Para selecionar células específicas de uma determinada região é mais prático utilizar o comando Ir para no menu Edição, Exemplo4, Figura 09. Selecione uma célula da região corrente (dados) da planilha. Figura 09 Abra o Exemplo4, clique em Editar e depois Ir para. Abrirá a caixa para seleção especial. Clique em Especial, Figura 09. Figura 10 Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 21 A caixa Ir para especial abrirá com opções de edição da região corrente da planilha, Figura 11. Selecione Região atual e clique em OK. Figura 11 Você também pode selecionar a região atual da planilha através do teclado utilizando Ctrl+* ou Ctrl+t ilustrada na Figura 12. Figura 12 Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 22 No próximo passo clique em Editar, Ir para, Especial e por fim, Em branco, depois clique em OK. A região com células em branco será selecionada, Figura 13. Figura 13 Com as células em branco selecionadas vamos inserir a data da célula A2 para o intervalo A3:A8. Digite a data 16/07/06 e pressione Ctrl+Enter no teclado, Figura 14. Figura 14 Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 23 1.9 Comando Inserir Nome Abra o Exemplo5, clique em A1, selecione a área corrente com Ctrl+* no teclado, no menu Inserir, aponte para Nome e, em seguida, clique em Definir, Figura 15. Figura 15 Na caixa Definir nome escreva Setembro como o nome da lista que você selecionou. Em seguida clique em OK, Figura 16. Agora ao ativar o campo de nome e selecionar a opção Setembro, Figura 17, a lista contendo a área A1:B31 será selecionada. Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 24 Figura 16 Caixa de nome Figura 17 Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 25 1.10 Formatação condicional → Fórmula A formatação condicional com fórmula estabelece um determinado formato de célula e sua alteração de valor através de uma fórmula. Abra o Exemplo6 clique em Ferramentas, Opções, desmarque Linhas de grade e clique em OK. Selecione o intervalo de célula A2:B25 depois clique em Formatar e Formatação condicional. Na caixa que se abre escolha a opção da Condição 1 a “A fórmula é” e digite a fórmula =$A2<>$A3 no espaço ao lado, Figura 18. Figura 18 Clique no botão Formatar e escolha a aba Borda. Dê um clique na borda inferior da caixa de célula e confirme em OK, Figura 19. Clique em OK na caixa de Formatação condicional. Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 26 Dê um clique na borda inferior da célula. Figura 19 Figura 20 Agora as datas diferentes estão separadas por uma linha marcada na borda inferior da célula, Figura 20. Experimente inserir um novo registro com data diferente ou data igual e veja o que acontece. Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 27 Capítulo 2 2.1 Subtotal A ferramenta Subtotal resume os dados numa lista, os quais são definidos pelo usuário através da caixa de opções com os seus respectivos rótulos de colunas. No menu Dados clique em Subtotais, Figura 21. Figura 21 Na caixa Subtotais, na opção “A cada alteração em:” selecione Data. Em “Usar função:” escolha Soma e na opção “Adicionar subtotal a:” marque Valor, Figura 22. Clique em OK para confirmar. Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 28 Figura 22 Os valores são somados na coluna Valor a cada alteração na Data, Figura 23. A formatação condicional por fórmula no intervalo A2:C25 exibe o subtotal de forma mais destacada. Os botõesà esquerda da lista maximizam ou minimizam o resumo dos dados. Figura 23 Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 29 Funções de resumo para listas com Subtotais Você pode usar qualquer uma das funções abaixo para resumir os dados em uma lista. Tabela 07. Use esta função Para resumir Soma A soma dos valores em uma lista. Esta é a função padrão para dados numéricos. Contar O número de itens em uma lista. Esta é a função padrão para dados não numéricos. Média A média dos valores em uma lista. Máx O maior valor em uma lista. Mín O menor valor em uma lista. Produto O resultado da multiplicação de todos os valores em uma lista. Cont.Núms O número de registros ou linhas em uma lista que contém dados numéricos. DesvPad Uma estimativa do desvio padrão de uma população, onde a lista é a amostra. DesvPadp O desvio padrão de uma população onde a lista é a população inteira. Var Uma estimativa da variância de uma população onde a lista é a amostra. Varp A variância de uma população onde a lista é a população inteira. Tabela 07 Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 30 Dica: se você deseja apenas exibir o valor total de um intervalo de células, use o recurso AutoCálculo no Microsoft Excel. Quando você seleciona células, o Excel exibe a soma do intervalo na barra de status, que é a área horizontal no Excel abaixo da janela da planilha. Se a barra de status não for exibida, clique em Barra de status no menu Exibir. Figura 24 Com essas duas células selecionadas, o AutoCálculo exibe o total de (R$ 90,00) na barra de status. O AutoCálculo também pode efetuar outros tipos de cálculos para você. Quando você clica com o botão direito do mouse na barra de status, um menu de atalho aparece. Você pode calcular a média ou o valor mínimo ou máximo no intervalo selecionado. Se você clicar em Cont.Núm, o AutoCálculo conta as células que contêm números. Se você clicar em Cont.Valores, o AutoCálculo conta o número de células preenchidas. Sempre que você iniciar o Excel, o AutoCálculo será redefinido para a função SOMA. Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 31 2.2 Protegendo células específicas Você pode proteger algumas células pré-selecionadas de intervalo sem que seja necessária a proteção total da planilha. Abra o Exemplo8 e clique entre a coluna A e a linha 1 da planilha, Figura 25. Clique aqui para selecionar toda a região da planilha Figura 25 Após a seleção da área abra o menu Formatar, Células clique na aba Proteção e desmarque a opção Travada, Figura 26. Clique em OK. Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 32 Figura 26 Neste caso desejamos proteger apenas a área com fórmulas da planilha, para isso, selecione o intervalo B17:B19 da planilha Proteção, abra a caixa para Formatar células através do Ctrl+1 e em seguida marque Travada e Oculta, Figura 27. Clique em OK. Nota: se você marcar as opções Travada e Oculta na caixa Formatar células então, além de proteger as células, poderá também ocultar a fórmula existente nas células protegidas na barra de Fórmulas. Figura 27 Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 33 Agora abra o menu Ferramentas, Proteger, Proteger planilha e digite a senha “1”, confirme a senha e clique em OK. Ao tentarmos digitar um valor qualquer na célula B18 o Excel emite um aviso de “Atenção” onde não é possível editar a célula protegida, Figura 28. Note que a barra de fórmula, oculta a fórmula existente na célula B18. As demais células ficam liberadas para edição. A fórmula não é exibida na barra de fórmula. Intervalo de Célula B17:B19 protegida por senha contra edição Figura 28 Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 34 2.3 Fórmula Condicional Sobre Fórmulas: As fórmulas calculam valores segundo uma ordem específica. Uma fórmula no Microsoft Excel sempre começa com um sinal de igual (=). O sinal de igual informa ao Excel que os caracteres a seguir constituem uma fórmula. Depois do sinal de igual estão os elementos a serem calculados, que são separados por operadores de cálculo. O Excel calcula a fórmula da esquerda para a direita, de acordo com uma ordem específica para cada operador da fórmula. Você pode alterar a ordem das operações usando parênteses. No exemplo abaixo, os parênteses na primeira parte da fórmula forçam o Excel a calcular B4+25 primeiro e, em seguida, dividir o resultado pela soma dos valores nas células D5, E5 e F5. =(B4+25)/SOMA(D5:F5) As regras para uma fórmula condicional são as mesmas, a diferença é que você impõe uma condição para o cálculo a ser realizado. É importante criar uma seqüência lógica dentro da fórmula condicional, para que o resultado não seja equivocado ou de erro. Normalmente uma fórmula condicional é construída por tentativas, dificilmente consegue-se o resultado desejado na primeira edição. Para facilitar a criação da fórmula você deve ter em mente de forma literal, a lógica do resultado desejado. Por exemplo: se a venda, com o valor localizado na célula A1, for maior ou igual a R$ 500,00 então “Ganho”, se não “Perda”. Na barra de fórmula do Excel 2003 essa condição ficaria assim: =SE(A1>=500; “Ganho”; “Perda”). 2.3.1 A Função SE Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 35 A tabela do Exemplo9 consta de 10 alunos e suas respectivas notas, relativas aos quatro bimestres do ano letivo, Figura 29. A primeira condição a ser criada deve registrar no intervalo G2:G11 se o aluno está aprovado ou não. A lógica a ser utilizada fica da seguinte forma: se a média do aluno for maior ou igual a 5, então “Aprovado” se for menor do que 5 então “Reprovado”. Figura 29 Após posicionar o cursor na célula G2 vamos inserir a lógica escolhida. Clique no botão Colar função localizado na Barra padrão. Na caixa Colar função selecione na Categoria da função a opção Lógica e em Nome da função a opção SE, Figura 30. Um assistente para a construção da fórmula é aberto, Figura 31. Clique na caixa de argumento do campo de Teste_lógico e selecione a célula F2 clique novamente na caixa de argumento e digite a condição F2>=5, depois escreva “Aprovado” no Valor_se_verdadeiro e “Reprovado” no campo Valor_se_falso. Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.comCurso de Excel Profissional 36 Figura 30 Fórmula editada Caixa de argumento Figura 31 A análise do teste é Booleana, ou seja, verdadeiro (1) e falso (0). Se a média do aluno for maior ou igual a 5 então ela é verdadeira, ou Aprovado, mas se a média for inferior a 5 então ela falsa e o resultado será Reprovado. Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 37 Em seguida arraste a fórmula G2 até G11, Figura 32. Figura 32 O segundo passo é estabelecer a lógica da coluna Avaliação. Essa coluna avalia a classe de nota do aluno, ou seja, se a nota for maior ou igual a 7,5 então classe “A”, se a nota for maior ou igual 5 então classe “B”, se a nota for maior ou igual a 2,5 então classe “C” e se a nota for maior ou igual a 0 então a classe será “D”. Da mesma forma, separar as classes seria: 1. De 7,5 a 10,0 – Classe A 2. De 5 a 7,4 – Classe B 3. De 2,5 a 4,9 – Classe C 4. De 0 a 2,4 – Classe D Vale lembrar que o Excel avalia a fórmula da esquerda para a direita. Na Figura 33 a fórmula é editada diretamente na barra de fórmula e segue uma seqüência lógica que obtém o resultado esperado. É importante testar a fórmula condicional, mesmo que não exista erro aparente, para evitar um resultado inconsistente no cálculo. Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 38 Figura 33 A fórmula utilizada =SE(F2>=7,5;“A”;SE(F2>=5; “B”;SE(F2>=2,5; “C”; “D”))) exige o fechamento de parêntese para cada condição inserida. Existe a teoria de que podemos inclui até 16 “SE” na barra de fórmula, porém, depende da lógica utilizada. Após a construção da fórmula condicional na coluna H basta copiar para as demais células no intervalo B3:B11 dando um clique duplo no canto inferior direito da célula B2, Figura 34. Para copiar a fórmula para as demais células da coluna dê um clique duplo no canto inferior direito da célula B2. Figura 34 Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 39 2.3.2 A Função CONT.SE Calcula o número de células não vazias em um intervalo que corresponde a determinados critérios. CONT.SE(intervalo;critérios) Abra o Exemplo10 e posicione o cursor na célula H13. O objetivo é calcular o número de alunos aprovados e reprovados no intervalo de célula H13:H14. Clique no botão Colar função e selecione a função CONT.SE na categoria Estatística. Dentro do assistente de fórmula, Figura 35, clique na caixa de argumento Intervalo e selecione a área G2:G11, confirme clicando na caixa de argumento mais uma vez. Da mesma forma clique no campo Critérios e escreva Aprovado, não é preciso digitar aspas entre o nome escrito, o Excel coloca automaticamente. O resultado é 5 alunos aprovados. Clique na célula H14 e repita o procedimento explicado, mas desta vez, escreva Reprovado no campo de Critérios. Figura 35 Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 40 2.3.3 A Função SOMASE A função de planilha SOMASE verifica um valor em um intervalo e soma todos os valores correspondentes em outro intervalo. SOMASE possui três argumentos: o intervalo a ser verificado, o valor a ser usado no intervalo (o critério) e o intervalo que contém os valores a serem somados. =SOMASE(intervalo;critério;intervalo_soma) No Exemplo11 o objetivo é somar as vendas de cada vendedor. Clique na célula E2 e depois em Colar função. No campo Categoria da função, selecione Matemática e trigonométrica e SOMASE no campo Nome da função. No assistente de fórmula selecione A2:A24 no campo Intervalo, D2 no campo Critérios (Ana) e B2:B24 no campo Intervalo_soma, Figura 36. Figura 36 Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 41 A vendedora Ana vendeu R$ 415,00 registrados na célula E2. Para estender a fórmula da célula E2 para o intervalo E3:E5 e saber o valor das vendas dos outros vendedores, é necessário observar que o intervalo de soma e critério terá que ser o mesmo, não poderá variar. Para isso as células do intervalo deverão estar travadas por linha na referência absoluta, Figura 37. Figura 37 2.3.4 A função Soma Condicional O Assistente de soma condicional cria rapidamente fórmulas que contêm várias condições. Você pode usar o mouse para definir as condições e o assistente adicionará a fórmula à sua planilha. Abra o Exemplo 12. No assistente, especifique o local da lista, a condição a ser verificada e o local para o resultado. O assistente cria uma fórmula de matriz que calcula o resultado para você. Se precisar alterar a condição, poderá usar o assistente novamente e substituir os resultados no local originalmente especificado. O Assistente de soma condicional é um programa suplementar fornecido com o Excel. Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 42 Para instalar um suplemento clique no menu Ferramentas e Suplementos. Ao abrir a caixa de Suplementos selecione, para este caso, Soma condicional e clique em OK, Figura 38. Figura 38 Em algumas versões, um aviso pedindo permissão para a instalação poderá ser exibido, Figura 39, clique em Sim. Figura 39 Após a instalação do suplemento Soma condicional, clique em Ferramentas, Assistente, Soma Condicional, Figura 40. Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 43 Figura 40 A caixa do Assistente de soma condicional é aberta. A fórmula será construída em quatro etapas. A primeira etapa é selecionar a área da lista de planilha, Figura 41. Normalmente o excel seleciona a área automaticamente, mas talvez você precise selecionar o que vai depender da célula ativa estar ou não na região corrente da planilha. Figura 41 Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 44 Clique em Avançar. Na segunda etapa, em Coluna a somar, escolha a opção Valor. Para este caso vamos selecionar e adicionar a data 02/08/06, o Setor 1 e a região Norte, Figura 42. Figura 42 Clique em Avançar. A terceira etapa traz a opção de como a soma será exibida. Você pode copiar apenas a fórmula para célula escolhida ou a fórmula com os valores condicionais, Figura 43. Figura 43 Instrutor: Sérgio Ricardo BarbosaMartins sergiorbmartins@hotmail.com Curso de Excel Profissional 45 A quarta ou última etapa, pede para você selecionar em qual célula será inserida a fórmula da soma condicional. Clique na caixa de argumento, selecione G1 e confirme em OK. Figura 44 Na Figura 45 temos a fórmula matricial construída apresentando o resultado R$ 31.200,00 para as condições de soma pré-estabelecidas. Figura 45 A coluna “A”, a qual contém as datas, vem definida na fórmula matricial através de outra fórmula de função DATA.VALOR que calcula uma data específica. Podemos substituir essa condição na fórmula da soma condicional por outra condição mais simples. Dentro da Barra de fórmula, selecione Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 46 DATA.VALOR(“02/08/2006”) e clique na célula F1. No teclado pressione Ctrl+Shift+Enter para manter a fórmula como uma matriz. Agora a célula F1 é a sua referência de data para a fórmula, Figura 46. Digite em F1 a data 02/08/06 e veja que o valor somado é de R$ 31.200,00. Digitando a data 04/08/06 o valor passa a ser R$ 16.000,00. Note que as demais condições continuam valendo para a fórmula matricial, ou seja, calcular o Setor 1 e a região Norte. Figura 46 2.3.5 Função E (And) e OU (Or) São funções lógicas que geralmente são associadas a outras fórmulas condicionais. O exemplo13 demonstra como podemos utilizar a função E e OU, Figura 47. A lógica E (And) é satisfeita se todas as condições forem verdadeiras por outro lado, a lógica OU (Or) é válida se uma ou mais condições forem verdadeiras. Figura 47 Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 47 A lógica da fórmula na célula D2, do campo Situação, utiliza a condição OU e diz que se ou a Medição 1 ou a Medição 2 for maior ou igual a 70 então exibir na célula D3 o aviso “Acima” caso contrário exibir o aviso “Normal”. Na célula E2, do campo Ação, a fórmula diz que se a Medição 1 e a Medição 2 apresentarem valores abaixo de 70 então deve ser exibido o aviso “Manter” mas, se as duas medições registrarem valores acima de 70 então, o aviso “Parar” deve ser exibido. Caso as condições citadas para a fórmula em E2 não forem satisfeitas então “Observar” como aviso da terceira condição, Figura 48. Figura 48 Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 48 Capítulo 3 3. Validação A ferramenta validação é utilizada para definir a forma que os dados serão inseridos na célula. Você pode condicionar a entrada dos valores a um intervalo de data ou hora, a uma lista, um número inteiro ou decimal, comprimento de texto ou um valor personalizado. O Exemplo14 traz duas listas de valores pré-definidos na plan2. Uma lista se chama “Nomes” e a outra “Matrícula”. Na planilha 1 ou Plan1 do Exemplo14, selecione a área A2:A10, clique em Dados, Validação e na caixa que se abre selecione Lista no campo Permitir, depois escreva no campo Origem o nome da lista “Nomes” (=Nomes), Figura 49. Deixe marcados Ignorar em branco e Dropdown na célula. Figura 49 Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 49 Agora os valores da lista “Nomes” são exibidos na célula ativa A2, Figura 50. A lista “Nomes” será exibida nas células ativadas do intervalo A2:A10. Figura 50 Seguindo o mesmo procedimento para a inserção da lista “Nomes”, faça a validação da lista “Matrícula” no intervalo de célula B2:B10. Deverá ficar como mostrado na Figura 51. Figura 51 Agora vamos configurar uma mensagem de erro para dados não válidos das listas existentes nos campos Nome e Matrícula. Selecione a área A2:A10 e clique em Dados e Validação. Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 50 Na caixa para Validação de dados, clique na aba Alerta de erro. No campo Estilo selecione Parar. Em Título escreva “Atenção” e no campo de Mensagem de erro escreva: “Digite ou selecione um nome da lista”, Figura 52. Figura 52 Ao tentar digitar um nome que não se encontra na lista a mensagem de erro é exibida e o valor não é aceito, Figura 53. Figura 53 Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 51 3.1 Validação – Evitando um valor repetido Você pode evitar a digitação de um valor repetido numa coluna. Selecione o intervalo A2:A10 do Exemplo15, abra o menu Dados, Validação, no campo Permitir selecione Personalizada e no campo Fórmula digite =NÃO(CONT.SE($A$2:$A$10;A2)>1), Figura 54. Depois clique na aba Alerta de erro e no campo Título escreva “Atenção” e no campo Mensagem de erro digite “Esse nome já foi cadastrado”, Figura 55. Ao tentar digitar um valor repetido no intervalo A2:A10 o excel exibe a mensagem personalizada de erro. Figura 54 O “Não” da fórmula é uma função de negação, se for verdadeiro então Não e se for Falso então Sim. Se a fórmula CONT.SE encontrar um valor maior do que 1 para os valores da coluna A então é calculado como Falso e não é válido. Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 52 3.2 Validação – Condicionando uma lista à outra lista No Exemplo16 precisamos condicionar uma lista à outra. A plan2 traz três listas cadastradas. A lista Locação seleciona o Setor que determinará as duas listas de Equipamentos, Equip1 ou Equip2, da coluna à direita. Selecione o intervalo A2:A10 e abra o menu Dados, Validação. Escolha a opção Lista no campo Permitir e depois escreva o nome da lista Locação no campo Origem, Figura 55. Clique em OK. Figura 55 Na coluna B selecione a área B2:B10, abra o menu Dados, Validação. Na caixa Validação de dados determine no campo Permitir a opção Lista e depois no campo Origem escreva a fórmula =SE(A2="Setor 1";Equip1;Equip2), Figura 56. Essa fórmula estabelece que se o setor escolhido na coluna A for o Setor 1 então a lista aberta na célula adjacente da coluna B será Equip1 caso contrário Equip2, ou seja, o Excel entende que a outra opção, sendo a Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 53 primeira condição falsa, é a lista Equip2, Figura 57. Note que dentro da fórmulaos nomes escritos Equip1 e Equip2 não estão entre aspas, isso acontece porque estamos nos referindo a uma lista e não a um texto. Figura 56 Figura 57 Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 54 3.3 Funções PROCV e PROCH A fórmula PROCV quer dizer Procura Vinculada Vertical e a fórmula PROCH significa Procura Vinculada Horizontal. Vamos comentar neste curso sobre a função PROCV. A função PROCV associa o valor de uma célula de coluna a um valor contido em uma lista. O Exemplo17 contém na Plan2 duas listas cadastradas, Nomes e Matrícula. Abra o menu Inserir, Nome e Definir. Na caixa Definir nome selecione Matrícula, clique em Excluir e OK. Agora selecione a área A2:B11 da Plan2 e digite no campo de nome a palavra Cadastro, Figura 58. Campo de nome Figura 58 Após criar o nome Cadastro ative a Plan1 e clique em B2. Digite a fórmula =PROCV(A2;Cadastro;2;0), essa função está associando ao nome da coluna A à matrícula da coluna B. A fórmula está dizendo que o nome presente em A2 deverá ser relacionado com a segunda coluna da lista Cadastro, ou seja, a sua matrícula correspondente. Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 55 Analisando a fórmula criada temos: 1. A2 – Valor_procurado - é o valor a ser procurado na primeira coluna da matriz. 2. Cadastro – Matriz_tabela – é a tabela de informações em que os dados são procurados, uma lista por exemplo. 3. 2 – Num_índice_coluna – é o número da coluna em matriz_tabela a partir do qual o valor correspondente deve ser retornado. 4. 0 – Procurar_intervalo – é um valor lógico que especifica se você quer que PROCV encontre a correspondência exata ou uma correspondência aproximada. Arraste a fórmula de B2 até B10, o erro #N/D é exibido e ocorre quando um valor não se encontra disponível para uma função ou fórmula. Como as células ao lado da coluna A, estão em branco, a fórmula PROCV na coluna B fica sem referência, Figura 59. Figura 59 Para retirar o erro exibido na coluna B podemos criar uma condição na fórmula PROCV para ser calculada quando a célula ao lado, coluna A, contiver algum valor. Sendo assim a fórmula seria Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 56 modificada para =SE(A2=””;””;PROCV(A2;Cadastro;2;0)). Agora o cálculo PROCV só será realizado se a coluna A exibir células não vazias, Figura 60. Figura 60 3.4 Consolidação de Dados Significa resumir os dados de várias planilhas em apenas uma posição de célula. Você pode utilizar, entre as mais usadas, a função Soma, Média, Máximo, Mínimo, Cont. Num e Cont. Valores. O Exemplo18 apresenta uma pasta com cinco planilhas, quatro delas, representam os dias do mês e uma o resultado mensal consolidado. Na célula B2, da planilha Mês, vamos calcular a soma da saída de peças no Mês e na célula C2 a média mensal de cada peça, Figura 61. Você pode usar o assistente para consolidação dos dados, mas existe uma forma mais rápida e econômica de resumir os dados. Através de fórmulas simples conseguimos somar ou extrair a média, por exemplo, de centenas de planilhas. É necessário que as planilhas estejam organizadas de forma sincronizada, ou seja, de preferência manter as células na mesma posição. Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 57 Colunas onde os dados serão resumidos Planilha onde os dados do mês serão calculados. Figura 61 Na célula B2 da planilha Mês digite a fórmula =Soma(‘01:04’!B2). Essa função estabelece a soma dos valores localizados na célula B2 das planilhas 01,02,03 e 04. O Excel entende que as planilhas 01 e 04 são os extremos da soma e portanto, você pode inverter a planilha 02 pela 03 e a soma será feita da mesma forma. No entanto se você inverter de posição a planilha 04 pela 03, por exemplo, o intervalo de soma será modificado e a planilha 03 não fará parte do cálculo. Na célula C2 da planilha Mês digite a fórmula =Média(’01:04’!B2). Essa função estabelece a média dos valores localizados na célula B2 das planilhas 01,02,03 e 04, Figura 62. Para calcular o Máximo ou o Mínimo, por exemplo, basta substituir o Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 58 nome da função na fórmula. Após ter inserido as fórmulas para soma e para média nas células B2 e C2 respectivamente arraste-as para as demais células. Figura 62 Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 59 Cápitulo 4 4.1 Macros – Reduzindo Tempo nas Rotinas do Trabalho Caso você execute uma determinada tarefa várias vezes no Microsoft Excel, é possível automatizá-la com uma macro. A macro é uma seqüência de comandos e funções armazenados em um módulo do Visual Basic e pode ser executada sempre que você precisar executar a tarefa. Quando você grava uma macro, o Excel armazena informações sobre cada etapa realizada à medida que você executa uma seqüência de comandos. Em seguida, você executa a macro para repetir, ou “reproduzir”, os comandos. Antes de gravar a macro, planeje as etapas e os comandos que você deseja executar. Se cometer um erro durante a gravação da macro, as correções feitas também são gravadas. Ao gravar macros, o Visual Basic armazena cada uma em um novo módulo anexado a uma pasta de trabalho. Por exemplo, se você insere com freqüência seqüências de caracteres de texto extensas nas células, você pode gravar uma macro para formatar essas células de maneira que o texto retorne automaticamente esse formato. Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 60 4.1.1 Como gravar uma macro No menu Ferramentas, aponte para Macro e, em seguida, clique em Gravar nova macro. Figura 63 Na caixa Nome da macro, insira um nome para a macro, Figura 63. O primeiro caractere do nome da macro deve ser uma letra. Os demais caracteres podem ser letras, números ou caracteres sublinhados. Não são permitidos espaços no nome de uma macro; um caractere sublinhado funciona da mesma forma que um separador de palavras. Para executar a macro pressionando uma tecla de atalho do teclado, insira uma letra na caixa Tecla de atalho. Use CTRL+ letra (para as letras minúsculas) ou CTRL+SHIFT+ letra (para as letras maiúsculas), onde letra representa qualquer tecla de letra do teclado. A letra da tecla de atalho usada não pode ser um número ou caractereespecial, como @ ou #. A tecla de atalho substituirá quaisquer teclas de atalho padrão do Microsoft Excel enquanto a pasta de trabalho que contém a macro estiver aberta. Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 61 Na caixa Armazenar macro em, clique no local onde você deseja gravar a macro. Para incluir uma descrição da macro, digite a descrição na caixa Descrição. Inserindo a barra de ferramentas do Visual Basic. Na barra do menu principal clique em Exibir, Barra de ferramentas e Visual Basic, Figura 64. Figura 64 Dê um clique duplo na faixa de nome para a barra de ferramentas se posicionar no cabeçalho do Excel. Reproduzir Macro Gravar Macro Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 62 5.3 Importação de Texto Abra o arquivo Exemplo19. Os pedidos do mês estão no arquivo Mês2007.txt. A primeira tarefa é abrir o arquivo dividindo-o em colunas durante o processo. Na barra de ferramentas Visual Basic, dê um clique no botão Gravar macro, digite “ImportarTexto” como o nome da macro e, depois, dê um clique em OK. Clique em Restaurar a janela da planilha e dê um clique no botão Abrir arquivo, em seguida abra a pasta DADOS/Excel Avançado e digite Mês2007.txt na caixa de Nome do arquivo e depois dê um clique em Abrir, Figura 65. Figura 65 Digite o nome do arquivo de texto aqui, mesmo que não seja um arquivo do Excel. O passo 1 do Assistente de Importação de texto aparece, Figura 66. As três primeiras fileiras do arquivo contêm o título do relatório e uma linha em branco; portanto, mude o valor de Iniciar importação na linha para 4. Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 63 As outras opções padrão no Assistente de importação de texto são convenientes para esse arquivo; portanto, dê um clique em Concluir. O arquivo de texto se abre, com as colunas divididas em colunas do Excel. Figura 66 Linha 4 para pular as fileiras do início do arquivo. No menu Janela clique em Comparar lado a lado com Exemplo19 e em seguida arraste a parte inferior da janela de modo que você possa ver as guias da parte debaixo da pasta de trabalho Exemplo19. Em seguida, arraste a guia da pasta Mês2007 para baixo, na frente da guia Plan1 da pasta de trabalho Exemplo19, Figura 67. Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 64 Arraste a guia Mês2007 daqui... ...para cá, movendo a planilha para a pasta de trabalho Exemplo19. Figura 67 A planilha mês2007 se move para a pasta de trabalho Exemplo19, e a pasta Mês2007.txt desaparece (pois ela perde sua única planilha, e uma pasta não pode existir sem pelo menos uma planilha). Clique em Maximizar a janela da planilha. A linha 2 contém sinais de igualdade que você não precisa. Selecione a linha 2, clique o botão direito do mouse e selecione Excluir, Figura 68. Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 65 Figura 68 Agora você deve ter o arquivo importado dividido em colunas e isento de linhas estranhas. Clique em A1 e depois Crtl+Shift+* para selecionar a região atual. Com a macro ImportarTexto ainda gravando, clique no menu Editar, depois Ir para, Especial e Em branco, Figura 69. Figura 69 Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 66 Com essa operação apenas as células em branco serão selecionadas, Figura 70. Figura 70 O recurso interno Ir para especial do Excel pode economizar muito trabalho na construção da macro. Você quer agora preencher cada uma das células selecionadas com uma fórmula que aponte para a célula acima dela. Normalmente, quando você introduz uma fórmula, o Excel coloca a fórmula na célula ativa. Entretanto você pode fazer o Excel colocar uma fórmula em todas as células selecionadas simultaneamente. Com as células em branco selecionadas e D3 como a célula ativa, digite um sinal de igualdade (=) e, em seguida, pressione a Seta para cima, para apontar a célula D2. A referência de célula D2 – quando encontrada na célula D3 – significa, na verdade, “uma célula acima de mim, na mesma coluna”. Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 67 Pressione Ctrl+Enter para preencher a fórmula em todas as células corretamente selecionadas, Figura 71. Quando mais de uma célula estiver selecionada, se você digitar uma fórmula e pressionar Ctrl+Enter, essa fórmula será copiada em todas as células da seleção. Se você pressionar Enter sem manter a tecla Ctrl pressionada, a fórmula irá somente para a única célula ativa. Cada célula selecionada contém a nova fórmula. Figura 71 Não é necessário que a lista permaneça com as fórmulas, portanto, pressione Ctrl+Shift+* para selecionar a região corrente. Escolha o menu Editar e clique em Copiar. Em seguida, escolha o menu Editar, clique em Colar especial, clique na opção Valores e depois confirme clicando em OK. Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 68 Pressione a tecla Esc para sair do modo de cópia e, em seguida, selecione a célula A1. Pare de gravar a macro ImportarTexto, Figura 72. Parar macro Figura 72 Nota: Se você testar essa macro diversas vezes terá várias cópias da planilha Mês2007, as novas cópias serão chamadas automaticamente de Mês2007 (2), Mês2007 (3) e assim por diante. Para verificarmos o código gravado pela macro ImportarTexto, pressione Alt+F11. A tela do VBA (Visual Basic Application) abrirá exibindo o ambiente de criação VBA. Outra forma de visualizar o código a macro ImportaTexto que criamos é pressionar no teclado Alt+F8 para abrir a caixa de macros. Nesse caso basta selecionar a macro desejada e clicar em Entar. Todo esse processo de importação de texto que fizemos foi gravado no VBA o qual montou as linhas de instrução necessárias para a execução da macro. Se você entrou no VBA através do ALT+F11 então clique em Módulos e dê um clique duplo em Módulo1. O código de gravação é visualizado no ambiente de criação VBA com toda a seqüência da operação realizada na importação do texto, Figura 73. Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.comCurso de Excel Profissional 69 Figura 73 Comentando sobre o código que criamos, as explicações estão na fonte azul: Sub ImportarTexto() → A sub-rotina ImportarTexto ‘ ‘ ImportarTexto Macro → Comentário. Não interfere no código quando seguido de um apóstrofo. A fonte do texto fica verde ‘ Macro gravada em 19/08/2006 por CVRD ‘ ActiveWindow.WindowState = xlNormal → Restaurar janela With ActiveWindow → com a instrução Ativar janela... .Top = 1.75 → mover a janela para cima em 1.75 pontos de tela Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 70 .Left = 5.5 → mover a janela para esquerda em 5.5 pontos de tela End With → fim de “com a instrução Ativar janela...” ChDir “C:\DADOS\Excel Avançado\Treinamento\Exemplos” Workbooks.OpenText Filename:= _ → abrindo uma pasta de trabalho “C:\DADOS\Excel Avançado\Treinamento\Exemplos\Mês2006.txt”, Origin:=xlWindows _ , StartRow:=4, DataType:=xlFixedWidth, FieldInfo:=Array(Array(0, 1), Array( _ 8, 1), Array(20, 1), Array(26, 1), Array(36, 1), Array(45, 1), Array(55, 1), Array(64, 1)) → importando o texto Mês2006 e determinando a importação a partir da quarta linha da lista With ActiveWindow → com a instrução Ativar janela...(planilha Mês2006.xls) .Top = 1.75 → mover a janela para cima em 1.75 pontos de tela .Left = 18.25 → mover a janela para esquerda em 18.25 pontos de tela End With → fim de “com a instrução Ativar janela...” Windows(“Exemplo19.xls”).Activate → ativando a planilha Exemplo19.xls With ActiveWindow → com a instrução Ativar janela... .Top = 11.5 → mover a janela para cima em 11.5 pontos de tela .Left = 1.75 → mover a janela para esquerda em 1.75 pontos End With → fim de “com a instrução Ativar janela...” Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 71 Windows(“Mês2006.txt”).Activate → ativando a planilha Mês2006.xls With ActiveWindow → com a instrução Ativar janela... .Top = -5 → mover a janela para cima em –5 pontos de tela .Left = 28 → mover a janela para esquerda em 28 pontos End With → fim de “com a instrução Ativar janela...” Sheets(“Mês2006”).Select → Selecionando a planilha Mês2006 Sheets(“Mês2006”).Move → Movendo a planilha Mês2006... Before:=Workbooks(“Exemplo19.xls”).Sheets(1) → …movendo a planilha Mês2006 para a planilha Exemplo19 ActiveWindow.WindowState = xlMaximized → maximizando a janela da planilha. Rows(“2:2”).Select → selecionando a linha 2 da planilha Selection.Delete Shift:=xlUp → deletando a linha 2 Range(“A1”).Select → clicando na célula A1 Selection.CurrentRegion.Select → selecionando toda a região corrente da lista Selection.SpecialCells(xlCellTypeBlanks).Select → selecionando apenas as células em branco na lista Selection.FormulaR1C1 = “=R[-1]C” → fazendo referência a uma linha acima da mesma coluna Selection.CurrentRegion.Select → selecionando toda a região corrente da lista Selection.Copy → copiando a região �um�ccionada Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 72 False, Transpose:=False → colando especial na região selecionada apenas os valores Application.CutCopyMode = False → saindo do modo de edição (Esc do teclado) Range(“A1”).Select → selecionando a célula A1 End Sub → fim da sub-rotina ImportarTexto Você pode incluir uma sub-rotina dentro de outra sub-rotina. Vamos supor que após o término da construção da macro ImportarTexto você percebeu que faltou uma instrução final, ou seja, ativar a Plan2 por exemplo. Não é preciso repetir toda a gravação da macro, basta gravar esse último comando e incorporar à macro já existente. Inicie a gravação da macro AtivarPlan, selecione a Plan2 e pare a gravação. Agora é só incorporar a macro AtivarPlan a macro ImportarTexto. Sendo assim o código final ficaria como exibido na Figura 74. A macro AtivarPlan foi incorporada a seqüência da macro ImportarTexto antes da instrução End Sub (fim da sub-rotina). Figura 74 Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 73 4.3 Tabela Dinâmica Um relatório de tabela dinâmica é uma tabela interativa que você pode usar para resumir rapidamente grandes quantidades de dados. Você pode girar suas linhas e colunas para ver resumos diferentes dos dados de origem, filtrar os dados por meio da exibição de páginas diferentes ou exibir os detalhes de áreas de interesse. Use um relatório de tabela dinâmica quando você desejar comparar totais relacionados, especialmente quando você tiver uma longa lista de valores a serem resumidos e desejar comparar vários fatos sobre cada valor. Use relatórios de tabela dinâmica quando desejar que o Microsoft Excel faça a classificação, e a totalização por você, Figura 75. Para criar um relatório de tabela dinâmica, use o Assistente de tabela dinâmica e gráfico dinâmico, como um guia para localizar e especificar os dados de origem que você desejar para analisar e criar a estrutura do relatório. Você pode, em seguida, usar a barra de ferramentas Tabela dinâmica para organizar os dados dentro dessa estrutura. Figura 75 Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 74 Quanto aos Dados de origem de um relatório de tabela dinâmica você pode criar esse tipo de relatório a partir de uma lista do Microsoft Excel, de um banco de dados externo, de várias planilhas do Excel ou de outro relatório de tabela dinâmica. Para o nosso caso, vamos utilizar a planilha Mês2007. Abra o Exemplo20, clique em A1 da planilha Mês2007. Em seguida clique no menu Dados, Relatório de tabela e gráfico dinâmicos, Figura 76. Figura 76 A caixa do assistente com a etapa 1 é aberta. Na pergunta “Onde estão os dados que você deseja analisar?”, deixe selecionada a opção Banco de dados ou lista do Microsoft Excel e para a pergunta “Que tipo de relatório você deseja criar?” mantenha marcada a opção Tabela dinâmica, Figura 77. Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 75 Figura 77 No próximo passo o assistente faz a pergunta “Onde estão os dados que você deseja usar?” e seleciona a área automaticamente. Se os dados estiverem em outra pasta ou arquivo clique em Procurar caso contrário, clique em Avançar Figura 78. Figura 78 Após selecionar a área corrente, na terceira e última etapa, o assistentefará a seguinte pergunta: “Onde você deseja colocar a tabela dinâmica?”, para este exemplo deixe marcada a opção Nova planilha, Figura 79. Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 76 Figura 79 Se você clicar em Concluir o Excel criará o relatório de tabela dinâmica em vazio, o qual poderá ser montado através da barra de ferramentas da Tabela dinâmica. Caso você opte por clicar em Layout uma nova caixa será aberta para a construção do novo modelo de tabela dinâmica, Figura 80. Figura 80 Na caixa de Layout arraste o botão Estado, localizado a direita, para o campo Página, depois arraste o botão Canal e Categoria para o campo Linha e por último arraste o botão Qdade (quantidade) para o campo Dados. A construção do relatório deverá ficar igual à Figura 81. Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 77 Figura 81 Nota: dando um clique duplo no botão Soma de Qdade, localizado no campo Dados, você abrirá a caixa Campo da tabela dinâmica e poderá escolher qual a operação utilizada para Resumir os dados. Clique em OK confirmando Soma de Qdade, Figura 82. Figura 82 Clique em OK na caixa de Layout e em Concluir na caixa da terceira etapa. O relatório é criado em uma nova planilha, Plan4. Para visualizar melhor o relatório retire as linhas de grade da Plan4. O relatório resume os dados por Estado, Canal e Categoria somando a quantidade negociada, Figura 83. Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 78 Nova planilha criada. Figura 83 4.4.1 Classificando os Dados na TD Você pode classificar os dados em ordem crescente ou decrescente na tabela dinâmica. Dê um clique duplo no campo de coluna Canal da TD e depois, na caixa que se abre, clique em Avançado, Figura 84. Em Opções de AutoClassificação selecione a opção Decrescente e em Usando campo selecione a opção Soma de Qdade. Agora o Total do Campo de coluna Canal está classificado da Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 79 maior para a menor quantidade, mesmo que a tabela seja atualizada com novos valores a classificação levará em conta o primeiro maior total da quantidade. Figura 84 Repita a operação de classificação decrescente para o campo Categoria. O relatório final ficará igual ao da Figura 85. Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 80 Figura 85 Analisando o relatório observamos que o Canal com a maior quantidade é o Atacado e na Categoria relacionada ao Atacado, a Arte tem a maior quantidade. Os dados foram analisados levando em conta todos os Estados. Vamos filtrar NV no campo Estado para vermos a quantidade por localidade. Note que agora o Varejo é o Canal com a maior quantidade e a Categoria com a maior quantidade, relacionada ao Varejo, é o Esportes, Figura 86. Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 81 Figura 86 Para visualizar os dados tendo a primeira coluna Categoria e a segunda coluna como Canal, basta clicar em Categoria e arrastar para a esquerda do campo Canal, com isso temos a visão do relatório por Categoria e depois Canal, Figura 87. Você pode também formatar as linhas de subtotal, modificando a cor de fundo da célula ou da fonte. Dê um clique ao lado esquerdo da linha de subtotal e formate a fonte para negrito e em seguida a cor de fundo da célula para cinza 25%. Essa formatação acompanhará a tabela em qualquer Atualização. Não é possível manter uma formatação condicional na TD, ela se desfaz a cada atualização do relatório. Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 82 Figura 87 Para cada novo registro na lista ou no banco de dados, planilha Mês2007, você terá que atualizar o relatório TD, para isso, clique com o botão direito sobre qualquer parte da TD e selecione Atualizar. Para inserção rotineira de dados a área de cálculo da TD deverá contemplar toda a lista de registros, portanto, mantenha o intervalo de dados com uma margem de área suficiente para a atualização constante. Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 83 5.3 Gráfico Dinâmico Um relatório de gráfico dinâmico combina a praticidade de resumo interativo dos dados no relatório de tabela dinâmica com o apelo visual e os benefícios de um gráfico. Pode-se girar um relatório de gráfico dinâmico para que seja possível visualizar os mesmos dados de formas distintas. Após a construção da tabela dinâmica, baseada na planilha Mês2006, vamos construir um gráfico dinâmico. Clique com o botão direito sobre a tabela dinâmica e selecione Gráfico dinâmico. O gráfico de coluna é criado automaticamente. Por configuração interna o tipo de gráfico é de barras, Figura 88. Figura 88 Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 84 Você pode interagir com o gráfico dinâmico clicando nos botões de campo e selecionando a opção desejada. Vamos clicar no campo Estado, selecionar WA e confirmar em OK, Figura 89. Figura 89 Os dados relativos ao Estado WA são visualizados no gráfico. A alteração realizada no gráfico dinâmico é automaticamente sincronizada com a tabela dinâmica. Você pode modificar a posição dos campos no Gráfico dinâmico tal qual é feito com o relatório de tabela dinâmica. Após a construção do gráfico fica a critério do usuário a formatação personalizada lembrando que, após a atualização o Gráfico voltará a sua formatação original. Para manter o gráfico com a formatação personalizada é necessário que você o salve como um gráfico personalizado. Instrutor: Sérgio Ricardo Barbosa Martins sergiorbmartins@hotmail.com Curso de Excel Profissional 85 4.6 Gráfico personalizado Com o gráfico Gráf1 aberto personalize a formatação do Gráfico dinâmico ao seu gosto ou necessidade. Depois clique com o botão direito do mouse sobre a área do gráfico e selecione, Tipo de gráfico, Tipos personalizados, marque Definido
Compartilhar