Baixe o app para aproveitar ainda mais
Prévia do material em texto
Excel Avançado 2013 Sidney Verginio da Silva EXCEL AVANÇADO Página | 1 Sidney Verginio da Silva sid.si@hotmail.com Sobre o autor: SIDNEY VERGINIO DA SILVA Bacharel em Sistemas de Informação e Pós-Graduado MBA em Gestão de Tecnologia da Informação. Atua na área de Planejamento e Gestão de Processos e Projetos, Desenvolvimento de sistemas, Auditoria de Sistemas e Segurança de TI, também ministrando palestras e cursos sobre estes temas. Professor de cursos de graduação nas disciplinas de Programação de Computadores, Sistemas de Informação e Pacote Office. ©Todos os direitos deste material são reservados ao autor. Permitida a reprodução desde que citada a fonte. EXCEL AVANÇADO Página | 2 Sidney Verginio da Silva sid.si@hotmail.com Prezado aluno(a), Os softwares do tipo “Office” são cada vez mais presentes em nosso dia-a-dia. Seja para pesquisa, apresentação de trabalhos, relatórios ou qualquer outra atividade rotineira, utilizamos diariamente estas poderosas ferramentas. Especificamente sobre o Microsoft Excel, este está presente na vida de milhões de pessoas, seja para armazenamento e análise de dados, utilização para demonstrações financeiras, planejamento orçamentário, controles de estoque e caixa, enfim, muitas são aplicações deste software. Pessoas e empresas utilizam desta ferramenta para as mais diversas finalidades. Mais do que isso: já encontrei empresas que eram gerenciadas utilizando planilhas do Excel. É para atender a esta constante necessidade que apresento este Guia de Excel Avançado. Os diversos conteúdos aqui abordados visam fornecer a você conhecimento sobre as ferramentas mais utilizadas do Excel, visando proporcionar um arcabouço que certamente lhe ajudará muito em seu dia-a-dia. Nas próximas página você encontrará diversas explicações e exemplos sobre como utilizar as ferramentas do Excel. Explore a fundo o conteúdo deste Guia, implemente os exemplos citados e utilize toda sua criatividade para potencializar ainda mais suas rotinas. Um grande abraço e mãos à obra! Prof. Sidney Verginio da Silva sid.si@hotmail.com EXCEL AVANÇADO Página | 3 Sidney Verginio da Silva sid.si@hotmail.com Sumário 1 PLANILHA ELETRÔNICA ...................................................................................................................... 8 1.1 Componentes de uma planilha ................................................................................................................ 8 1.2 Os tipos de planilha ...................................................................................................................................10 2 MICROSOFT OFFICE EXCEL ................................................................................................................ 11 2.1 O ambiente de trabalho do Excel ............................................................................................................11 2.2 Faixa de Opções .........................................................................................................................................14 2.3 O Botão Office ............................................................................................................................................26 2.4 Trabalhando com Planilhas ......................................................................................................................28 2.5 Colunas, Linhas e Células ..........................................................................................................................31 2.6 Tipos De Dados ..........................................................................................................................................35 2.7 Formatação de Células – Dimensões ...................................................................................................... 37 2.8 Formatação de Células - Layout e dados ............................................................................................... 37 2.8.1 Estilos de Célula ................................................................................................................................... 43 2.9 Espaço de Trabalho ....................................................................................................................................46 3 REFERÊNCIAS ABSOLUTAS E RELATIVAS ............................................................................................ 48 4 FÓRMULAS E FUNÇÕES .................................................................................................................... 50 4.1 Fórmulas e Funções Matemáticas ..........................................................................................................50 4.2 Funções pré-definidas ..............................................................................................................................54 4.2.1 SOMA ..................................................................................................................................................... 54 4.2.2 MÉDIA .................................................................................................................................................... 57 4.2.3 MULT ...................................................................................................................................................... 57 4.2.4 MÍNIMO ................................................................................................................................................. 58 4.2.5 MÁXIMO ................................................................................................................................................ 58 4.2.6 DIVISÃO .................................................................................................................................................. 59 4.2.7 EXPONENCIAÇÃO (Exponencial) E RAIZ ......................................................................................... 59 4.2.8 PORCENTAGEM .................................................................................................................................... 60 4.2.9 ARRED .................................................................................................................................................... 60 EXCEL AVANÇADO Página | 4 Sidney Verginio da Silva sid.si@hotmail.com 4.2.10 TRUNCAR ............................................................................................................................................ 61 4.2.11 INT ........................................................................................................................................................ 61 4.2.12 SUBTOTAL ............................................................................................................................................ 61 4.2.12 CONT.VALORES ................................................................................................................................... 63 4.2.13 CONT.SE ............................................................................................................................................... 63 4.2.14 SOMASE ............................................................................................................................................... 64 4.2.15 CONT.NÚM .........................................................................................................................................65 4.3 Correção de Erros ......................................................................................................................................66 4.4 Funções de Data/Hora ..............................................................................................................................66 4.5 Funções de Texto ....................................................................................................................................... 70 4.6 Funções Lógicas ......................................................................................................................................... 72 4.6.1 SE ............................................................................................................................................................. 72 4.6.2 Função E ................................................................................................................................................ 75 4.6.3 Função OU ............................................................................................................................................ 76 4.6 4 SE com OU ............................................................................................................................................. 77 5 TABELAS DO EXCEL ........................................................................................................................... 78 5.1 Criar ou excluir uma tabela do Excel em uma planilha ........................................................................80 5.2 Formatar uma tabela do Excel .................................................................................................................82 6 COMENTÁRIOS ................................................................................................................................. 89 7 NOMEANDO INTERVALOS ................................................................................................................. 91 7.1 Definir um nome ........................................................................................................................................91 7.2 Gerenciador de Nomes .............................................................................................................................92 8 VÍNCULOS COM OUTRAS PLANILHAS OU ARQUIVOS ........................................................................ 94 9 VALIDAÇÃO DE DADOS ...................................................................................................................... 96 EXCEL AVANÇADO Página | 5 Sidney Verginio da Silva sid.si@hotmail.com 10 FILTROS ........................................................................................................................................ 100 10.1 Filtrar Texto ............................................................................................................................................. 100 10.2 Filtrar números ...................................................................................................................................... 103 10.3 Filtrar datas ou horas ............................................................................................................................ 106 10.4 Filtrar por cor de célula, cor de fonte ou conjunto de ícones ........................................................ 107 10.5 Filtrar por seleção .................................................................................................................................. 107 10.6 Removendo valores duplicados .......................................................................................................... 108 11 CLASSIFICAÇÃO ............................................................................................................................. 110 11.1 Classificar texto ...................................................................................................................................... 110 11.2 Classificar números ............................................................................................................................... 111 11.3 Classificar datas ou horas ..................................................................................................................... 112 11.4 Classificar cor de célula, cor de fonte ou ícones .............................................................................. 112 11.5 Classificar linhas .................................................................................................................................... 114 11.6 Por cor de célula, cor de fonte ou ícone de célula ........................................................................... 115 11.7 Classificar por mais de uma coluna ou linha ..................................................................................... 115 11.8 Classificar uma coluna em um intervalo de células sem afetar outros ........................................ 117 12 FORMATAÇÃO CONDICIONAL ........................................................................................................ 119 12.1 Formatar todas as células usando uma escala em duas cores....................................................... 119 12.2 Formatar todas as células usando uma escala de três cores ......................................................... 122 12.3 Formatar todas as células usando barras de dados ......................................................................... 125 12.4 Formatar todas as células usando um conjunto de ícones ............................................................. 128 12.5 Formatar células com textos, números ou valores de data e hora ............................................... 131 12.6 Formatar apenas valores mais altos ou mais baixos ........................................................................ 134 12.7 Formatar apenas valores acima ou abaixo de uma média ............................................................. 136 12.8 Formatar apenas valores únicos ou duplicados ............................................................................... 139 12.9 Regras Avançadas .................................................................................................................................. 140 12.10 Localizar células com formatos condicionais .................................................................................. 143 13 PROTEÇÃO DE PLANILHAS ............................................................................................................ 145 13.1 Proteger elementos de uma planilha ................................................................................................. 145 13.2 Ocultar fórmulas .................................................................................................................................... 147 EXCEL AVANÇADO Página | 6 Sidney Verginio da Silva sid.si@hotmail.com 13.3 Proteger elementos de uma pasta de trabalho ................................................................................ 148 13.4 Desproteger a planilha ......................................................................................................................... 149 13.5 Para bloquear apenas algumas células da planilha ......................................................................... 149 14 LOCALIZAÇÃO E SUBSTITUIÇÃO ..................................................................................................... 151 14.1 Localizar .................................................................................................................................................. 151 14.2 Substituir .................................................................................................................................................151 15 FUNÇÕES DE BANCO DE DADOS ................................................................................................... 153 15.1 PROCH ..................................................................................................................................................... 153 15.2 PROCV ..................................................................................................................................................... 155 16 SUBTOTAIS .................................................................................................................................... 161 17 ESTRUTURAS DE TÓPICOS ............................................................................................................. 165 18 TABELA DINÂMICA ....................................................................................................................... 173 18.1 Criar ou excluir um relatório de tabela dinâmica ou de gráfico dinâmico ................................... 174 18.2 Trabalhando com um relatório de tabela dinâmica ......................................................................... 178 18.3 Gráfico dinâmico ................................................................................................................................... 179 19 FORMULÁRIO DE DADOS .............................................................................................................. 185 20 CONTROLES DE FORMULÁRIOS..................................................................................................... 189 20.1 Caixa de seleção .................................................................................................................................... 189 20.2 Botão de opção ...................................................................................................................................... 191 20.3 Caixa de listagem e caixa de combinação .......................................................................................... 192 20.4 Barras de rolagem e botões de rotação ............................................................................................. 196 20.5 Rótulos e Caixas de Texto ..................................................................................................................... 201 20.6 Caixa de grupo ....................................................................................................................................... 202 EXCEL AVANÇADO Página | 7 Sidney Verginio da Silva sid.si@hotmail.com 21 MACROS ....................................................................................................................................... 204 21.1 Gravar uma macro ................................................................................................................................. 204 21.2 Atribuir uma macro a um objeto, a um elemento gráfico ou a um controle .............................. 205 21.3 Excluir uma macro ................................................................................................................................. 206 21.4 Exemplo de criação de macro ............................................................................................................. 206 21.5 Referências Relativas e Absolutas em macros .................................................................................. 208 22 GRÁFICOS ..................................................................................................................................... 210 22.1 Elementos de um gráfico ..................................................................................................................... 211 22.2 Criar um gráfico básico ......................................................................................................................... 211 22.3 Alterar o Layout ou o Estilo de um Gráfico ....................................................................................... 215 22.4 Adicionar ou remover títulos ou rótulos de dados .......................................................................... 217 22.5 Legendas ................................................................................................................................................. 220 22.6 Exibir ou ocultar eixos ou linhas de grade do gráfico ...................................................................... 221 22.7 Mover ou Redimensionar um Gráfico ................................................................................................ 223 22.8 Salvar e aplicar modelos de gráficos .................................................................................................. 224 22.9 Opções avançadas de gráficos ............................................................................................................ 225 22.10 Gráfico de combinação ...................................................................................................................... 226 22.11 Imprimir um gráfico em uma planilha ............................................................................................. 231 23 CONFIGURAÇÃO E IMPRESSÃO DE PÁGINA ................................................................................... 233 FINALIZANDO .................................................................................................................................... 237 EXCEL AVANÇADO Página | 8 Sidney Verginio da Silva sid.si@hotmail.com 1 PLANILHA ELETRÔNICA Uma Planilha eletrônica é um tipo de programa de computador que utiliza tabelas para realização de cálculos ou apresentação de dados. Cada tabela é formada por uma grade composta de linhas e colunas. O nome eletrônica se deve à sua implementação por meio de programas de computador. As planilhas são utilizadas principalmente para aplicações financeiras e pequenos bancos de dados. 1.1 Componentes de uma planilha Célula Elemento indicado pelo cruzamento entre uma linha e uma coluna. Células são o componente elementar de uma planilha eletrônica. Toda a informação, como valores e fórmulas, deve ser colocada em alguma célula para poder ser utilizada. Para identificarmos uma célula, normalmente utilizamos o nome da coluna seguido do nome da linha. Por exemplo, se tomarmos a coluna de nome A e a linha de número 10, neste cruzamento teremos a célula A10. Valores São os dados possíveis de serem digitados nas células, como valores numéricos (números inteiros ou decimais, data, hora), textos e fórmulas (expressões e funções). Fórmulas e Operadores Uma fórmula define como deve ser calculado o valor de uma célula. O conceito de fórmula é basicamente o conceito de fórmula matemática, e dá as planilhas eletrônicas seu principal motivo de existência: simplificar e calcular automaticamente valores. As fórmulas podem ser fórmulas aritméticas ou mais avançadas, usando funções internas do Excel. Essas funções oferecem desde mecanismos de simplificação da construção de fórmulas (como a função SOMA) até funções matemáticas ou estatísticas bastante complexas. Um exemplo de fórmula é "=A1+A2+A3+A4+A5", que pode ser substituída pela função"=SOMA(A1:A5)", que é mais prático de usar. Importante: toda fórmula ou função deve sempre iniciar com sinal de igual ( = ). Já os operadores são os símbolos matemáticos que operam cálculos. Podem ser EXCEL AVANÇADO Página | 9 Sidney Verginio da Silva sid.si@hotmail.com para realizar as operações básicas ou para testes lógicos. Operador Ação - Subtração + Adição * Multiplicação / DivisãoOperador Operação > Maior que >= Maior ou igual < Menor que <= Menor ou igual = Igual <> Diferente Tal como na Matemática, os operadores possuem uma ordem de execução: Prioridade Operação 1ª Operadores relacionais 2ª Multiplicação e Divisão 3ª Adição e Subtração Para alterar esta ordem, usamos parênteses. Por exemplo: = 6 + 5 * 1 → resulta em 11 = ( 6 + 6 ) * 1 → resulta em 12 Testes Lógicos Testes lógicos utilizam operadores lógicos ( <, >, =, etc.) para criar alguma relação entre valores ou células, devolvendo sempre valores do tipo Verdadeiro ou Falso. Ou seja, dado um teste qualquer, este sempre retornará FALSO ou VERDADEIRO. Por exemplo, ao digitar a fórmula = 7 < 6 e teclar ENTER, a planilha eletrônica mostrará o valor FALSO, pois é FALSO que 7 é menor que 6. EXCEL AVANÇADO Página | 10 Sidney Verginio da Silva sid.si@hotmail.com 1.2 Os tipos de planilha Existem vários softwares de Planilha Eletrônica no mercado: pagos, gratuitos, para plataformas Windows ou Linux e até mesmo planilhas eletrônicas que podem ser utilizadas pela web, por meio do navegador de internet. A preferência pelo uso fica a cargo do usuário. Neste guia, serão abordadas as funções de planilhas eletrônicas utilizando o Microsoft Office Excel, pois esse é sem dúvida o softwares de planilha eletrônica mais utilizado no mundo. EXCEL AVANÇADO Página | 11 Sidney Verginio da Silva sid.si@hotmail.com 2 MICROSOFT OFFICE EXCEL O Microsoft Office Excel (nome completo do Excel) é um programa de planilha eletrônica produzido pela Microsoft para computadores que utilizam o sistema operacional Microsoft Windows e também computadores Macintosh da Apple. Seus recursos incluem uma interface intuitiva e ferramentas de cálculo e de construção de gráficos que, juntamente com marketing agressivo da Microsoft, tornaram o Excel um dos mais populares aplicativos de computador até hoje. É, com grande vantagem, o aplicativo de planilha eletrônica dominante desde a versão 5 em 1993 e sua inclusão como parte do Microsoft Office. A primeira versão do Excel foi lançada para o Mac em 1985 e a primeira versão para Windows foi lançada em novembro de 1987. A Lotus foi lenta em trazer o 1-2-3 (até então concorrente do Excel) ao Windows e, por volta de 1988, o Excel havia começado a passar o 1-2-3 em vendas e ajudou a Microsoft a alcançar a posição de liderança no desenvolvimento de software para o PC. A Microsoft aumentou sua vantagem com lançamento regular de novas versões, aproximadamente a cada dois anos. A versão atual para a plataforma Windows é o Excel 15, também chamado de Microsoft Excel 2013. O Microsoft Office Excel oferece uma série de recursos para analisar, comunicar, compartilhar e gerenciar informações, a fim de que sejam tomadas decisões mais bem informadas. 2.1 O ambiente de trabalho do Excel Um documento (arquivo) do Excel chama-se pasta; cada pasta pode ter uma ou mais planilhas. A predefinição do programa é a de criar automaticamente três planilhas em branco por cada pasta nova. É possível criar novas planilhas ou mesmo excluir planilhas em cada pasta de trabalho. EXCEL AVANÇADO Página | 12 Sidney Verginio da Silva sid.si@hotmail.com Área de trabalho do Excel A quadrícula virtual da folha de cálculo eletrônica consiste numa série de células que são fruto do cruzamento de linhas horizontais, numeradas de 1 até 1.048.576, e de colunas verticais, com denominações de A at é XFD, num total de 17.179.869.184 células, que podem conter números, datas, texto e/ou fórmulas e funções. Um poderoso módulo de geração de gráficos possibilita a apresentação e análise de dados de forma visual. Os gráficos produzidos pelo Excel podem ser usados na própria folha de cálculo, exportados para outras aplicações (como o Word) ou até gravados como imagens. EXCEL AVANÇADO Página | 13 Sidney Verginio da Silva sid.si@hotmail.com EXCEL AVANÇADO Página | 14 Sidney Verginio da Silva sid.si@hotmail.com 2.2 Faixa de Opções O Excel traz em seu layout Guias onde é possível ter acesso a todos os seus recursos. Dentro das guias, temos os grupos de comandos e dentro destes os comandos que realizam as operações desejadas: 1 – Guias: Existem sete guias na parte superior. Cada uma representa tarefas principais executadas no Excel. 2 – Grupos: Cada guia tem grupos que mostram itens relacionados reunidos. 3 – Comandos: Um comando é um botão, uma caixa para inserir informações ou um menu. Os principais comandos do Excel estão na primeira guia, a guia Início. Os comandos dessa guia são aqueles que a Microsoft identificou como os mais comumente usados quando as pessoas realizam tarefas básicas com planilhas. Por exemplo, os comandos Colar, Recortar e Copiar são organizados primeiramente na guia Início, no grupo Área de Transferência. Os comandos de formatação de fonte estão próximos, no grupo Fonte. Comandos para centralizar o texto ou alinhar texto à esquerda ou à direita estão no grupo Alinhamento e comandos para inserir e excluir células, linhas, colunas e planilhas estão no grupo Células. Os grupos reúnem todos os comandos necessários para um tipo específico de tarefa e, durante toda a tarefa, eles permanecem em exibição e disponíveis, em vez de serem ocultados em menus. Esses comandos vitais ficam visíveis acima do seu espaço de trabalho. EXCEL AVANÇADO Página | 15 Sidney Verginio da Silva sid.si@hotmail.com GUIA INÍCIO - Grupo Área de transferência - Grupo Fonte EXCEL AVANÇADO Página | 16 Sidney Verginio da Silva sid.si@hotmail.com - Grupo Alinhamento - Grupo Número EXCEL AVANÇADO Página | 17 Sidney Verginio da Silva sid.si@hotmail.com - Grupo Estilo - Grupo Células - Grupo Edição EXCEL AVANÇADO Página | 18 Sidney Verginio da Silva sid.si@hotmail.com GUIA INSERIR - Grupo Tabelas - Grupo Ilustrações EXCEL AVANÇADO Página | 19 Sidney Verginio da Silva sid.si@hotmail.com - Grupo Gráficos EXCEL AVANÇADO Página | 20 Sidney Verginio da Silva sid.si@hotmail.com - Grupo Texto GUIA LAYOUT - Grupo Temas Altera o design geral do documento inteiro, incluindo cores, fontes e efeitos. EXCEL AVANÇADO Página | 21 Sidney Verginio da Silva sid.si@hotmail.com - Grupo Configurar Página - Grupo Dimensionar para ajustar Realiza o dimensionamento manual para impressão do documento desejado - Grupo Opções de Planilha Opções relacionadas às linhas de grade e título das células em razão de impressão ou visualização EXCEL AVANÇADO Página | 22 Sidney Verginio da Silva sid.si@hotmail.com - Grupo Organizar GUIA FÓRMULAS - Grupo Funções Grupo destinado a trabalhar com diversas modalidadesde funções a serem trabalhadas na planilha EXCEL AVANÇADO Página | 23 Sidney Verginio da Silva sid.si@hotmail.com - Grupos Nomes - Grupo Auditoria de Fórmulas - Grupo Opções de cálculo Opções relacionadas a como o Excel trabalhará com cálculos digitados na planilha. EXCEL AVANÇADO Página | 24 Sidney Verginio da Silva sid.si@hotmail.com GUIA DADOS - Grupo Obter Dados Externos Grupo destinado ao trabalho de obter dados externos de programas como Access, internet, arquivo de texto ou outros softwares de dados. - Grupo Conexões Trabalha diretamente com a fonte de dados externa interligada a planilha do Excel - Grupo Classificar e Filtrar EXCEL AVANÇADO Página | 25 Sidney Verginio da Silva sid.si@hotmail.com - Grupo Ferramentas de Dados - Grupo Estrutura de Tópicos Os comandos na Faixa de Opções são aqueles utilizados com mais freqüência. Em vez de mostrar todos os comandos a todo momento, o Excel 2007 mostra alguns comandos quando eles podem ser necessários, em resposta a uma ação efetuada. Por exemplo, se não houver um gráfico em uma planilha, os comandos para trabalhar com gráficos não são necessários. EXCEL AVANÇADO Página | 26 Sidney Verginio da Silva sid.si@hotmail.com 2.3 O Botão Office Do lado esquerdo do ambiente de trabalho do Excel, está um botão de forma circular com o símbolo do Office. Nele, se agrupam muitas funcionalidades que antes sem encontravam no menu “Arquivo”, tais como Abrir, Salvar, Imprimir, etc. As três primeiras opções (Novo, Abrir e Salvar) e a última (Fechar) são diretas; as restantes, que têm uma pequena seta (triângulo) à frente, têm sub-opções. Comandos do botão Office EXCEL AVANÇADO Página | 27 Sidney Verginio da Silva sid.si@hotmail.com Comando Novo: cria uma nova pasta de trabalho no Excel, seja ela em branco ou baseada em algum modelo pré-definido. Comando Abrir: abre uma pasta de trabalho já salva anteriormente. Comando Salvar: salva a pasta ativa no momento. Comando Salvar como: exibe opções adicionais, do lado direito. Pode clicar diretamente no botão Salvar Como (ou a tecla F12) para abrir o diálogo de gravação. Para salvar sua pasta de trabalho de forma que a mesma seja compatível com versões anteriores do Excel (97-2003), use o comando “Salvar Como”. Comando Imprimir: pode ser usado diretamente ou pressionando simultaneamente Ctrl+P, o que fará surgir a caixa de diálogo de impressão – o formato varia ligeiramente conforme a sua impressora. Pode também escolher, do lado direito, uma das opções relacionadas com a impressão, nomeadamente impressão rápida (ou seja, direto para a impressora, sem nenhum diálogo adicional) e pré-visualização da página a imprimir. Comando Preparar: reúne funcionalidades relacionadas com as propriedades do documento (autor, título, etc.) e possibilidades de proteção, entre outras. Comando Enviar: destina-se a enviar o seu documento para outra pessoa através de correio eletrônico ou fax – neste caso, através de um serviço de fax da internet . Comando Publicar: é especialmente útil para utilizadores empresariais que possuam servidores ou outras áreas partilhadas onde os seus documentos possam ser usados por mais do que uma pessoa. Comando Fechar: fecha o documento ativo e mantém o Excel aberto. EXCEL AVANÇADO Página | 28 Sidney Verginio da Silva sid.si@hotmail.com 2.4 Trabalhando com Planilhas No Excel, cada novo arquivo é uma “Pasta” e cada “Pasta” tem várias “Planilhas”. Estas “Planilhas” são a área de trabalho do Excel propriamente dita. Na barra inferior da área de trabalho do Excel pode-se verificar quais as planilhas que se encontram na pasta. A predefinição é de três planilhas com os nomes Plan1, Plan2 e Plan3. Para trabalhar com alguma planilha basta clicar sob seu nome. É possível alterar a ordem da sua exibição, bastando para tal clicar num dos separadores e, sem largar o botão esquerdo do mouse, arrastá-lo para outra posição. Inserir uma nova planilha Para inserir uma nova planilha, siga um destes procedimentos: Para inserir rapidamente uma nova planilha ao final das planilhas existentes, clique na guia Inserir Planilha na parte inferior da tela. Para inserir uma nova planilha na frente de uma planilha existente, clique com o botão direito do mouse na guia de uma planilha existente e, em seguida, clique em Inserir. Na guia Geral, clique em Planilha e clique em OK. Inserir várias planilhas ao mesmo tempo 1. Mantenha a tecla SHIFT pressionada e selecione o mesmo número de guias de planilha existentes das planilhas a serem inseridas na pasta de trabalho aberta. Por exemplo, se desejar adicionar três novas planilhas, selecione três guias de planilha das planilhas existentes. 2. Na guia Página Inicial, no grupo Células, clique em Inserir e, em seguida, clique em Inserir Planilha. Renomear uma planilha 1. Na barra da Guia de planilha, clique com o botão direito do mouse na guia da planilha que deseja renomear e clique em Renomear. 2. Digite o novo nome e tecle ENTER. EXCEL AVANÇADO Página | 29 Sidney Verginio da Silva sid.si@hotmail.com Excluir uma ou mais planilhas 1. Selecione a(s) planilha(s) que deseja excluir.Clique com o botão direito na guia de uma ou mais planilhas que deseja excluir e, em seguida, clicar em Excluir. Mover ou copiar uma planilha É possível mover ou copiar uma pasta de trabalho para outro local de uma pasta de trabalho (arquivo do Excel) ou para outra pasta de trabalho. Entretanto, tenha cuidado ao mover ou copiar uma planilha: os cálculos ou gráficos que estão baseados nos dados da planilha podem se tornar imprecisos se a planilha for movida. Da mesma forma, se uma planilha movida ou copiada for inserida entre as planilhas mencionadas por uma referência de fórmula 3Dos dados dessa planilha poderão ser incluídos no cálculo. 1. Para mover ou copiar planilhas para outra pasta de trabalho, verifique se a pasta de trabalho está aberta no Microsoft Office Excel. 2. Na pasta de trabalho que contém as planilhas a serem movidas ou copiadas, selecione as planilhas. 3. Clique com o botão direito do mouse em uma guia de planilha selecionada e, em seguida, clicar em Mover ou Copiar no menu de atalho. 4. Na lista Para pasta, siga um destes procedimentos: Clique na pasta de trabalho para a qual moverá ou copiará as planilhas selecionadas. Clique em nova pasta a fim de mover ou copiar as planilhas selecionadas para uma nova pasta de trabalho. 5. Na lista Antes da planilha, siga um destes procedimentos: Clique na planilha antes da qual se deseja inserir as planilhas movidas ou copiadas. Clique em mover para o fim para inserir as planilhas movidas ou copiadas após a última planilha da pasta de trabalho e antes da guia Inserir Planilha. 6. Para copiar as planilhas em vez de movê-las, marque a caixa de seleção Criar uma cópia. Para mover planilhas na pasta de trabalho atual, arraste as planilhas selecionadas ao longo da linha das guias das planilhas. Para copiá-las, mantenha pressionada a tecla CTRL e arraste as planilhas; solte o botão do mouse antes de soltar a tecla CTRL. EXCEL AVANÇADO Página | 30Sidney Verginio da Silva sid.si@hotmail.com Ocultar ou exibir planilhas ou pastas de trabalho É possível ocultar qualquer planilha em uma pasta de trabalho para removê-la do modo de exibição. Também é possível ocultar a janela de uma pasta de trabalho para removê-la do seu espaço de trabalho. Os dados nas janelas de planilhas e pastas de trabalho ocultas não ficam visíveis, mas ainda poderão ser referenciados de outras planilhas e pastas de trabalho. É possível pode exibir janelas de planilhas ou pastas de trabalho ocultas conforme o necessário. Por padrão, todas as janelas de pastas de trabalho abertas serão exibidas na barra de tarefas, mas será possível ocultá-las ou exibi-las nessa barra conforme o necessário. Ocultar uma planilha 1. Selecione as planilhas que deseja ocultar. 2. Na guia Início, no grupo Células, clique em Formatar. 3. Em Visibilidade, clique em Ocultar e Reexibir e clique em Ocultar Planilha. Exibir uma planilha oculta 1. Na guia Início, no grupo Células, clique em Formatar. 2. Em Visibilidade, clique em Ocultar e Reexibir e clique em Reexibir Planilha. 3. Na caixa Reexibir planilha, clique duas vezes no nome da planilha oculta que deseja exibir. Ocultar uma janela de pasta de trabalho Na guia Exibir, no grupo Janela, clique em Ocultar. Ao sair do Excel, será exibida uma solicitação para confirmar se as alterações deverão ser salvas na janela de pasta de trabalho oculta. Clique em Sim se desejar que a janela da pasta de trabalho esteja oculta na próxima vez em que for abrir a pasta de trabalho. EXCEL AVANÇADO Página | 31 Sidney Verginio da Silva sid.si@hotmail.com Exibir uma janela de pasta de trabalho oculta 1. Na guia Exibir, no grupo Janela, clique em Reexibir. Se a opção Reexibir não estiver disponível, a pasta de trabalho não conterá janelas de pastas de trabalho ocultas. 2. Em Reexibir pasta de trabalho, clique duas vezes na janela de pasta de trabalho que se deseja exibir. 2.5 Colunas, Linhas e Células A área de trabalho do Excel é uma “folha” quadriculada formada pela interseção de linhas com designações numéricas (do lado esquerdo) e de colunas designadas por letras (no topo da folha). Numa planilha com milhares de células poderá ser difícil encontrar a célula desejada. Para isso o Excel possui uma caixa de nome, onde pode é possível digitar diretamente a célula para onde pretende ir (seguido de Enter). Por exemplo, ao digitar na caixa de nome o valor B$, o cursor é movido para esta célula. EXCEL AVANÇADO Página | 32 Sidney Verginio da Silva sid.si@hotmail.com Selecionar, cortar, copiar e colar No Excel, as funções de seleção, corte, cópia e colagem de dados são parecidas com as do Microsoft Word. Quando pretende-se copiar um determinado valor que se encontra numa célula, valor esse que foi obtido de forma automática pelo Excel, através de uma fórmula, precisamos saber o que realmente queremos copiar: apenas o valor ou a fórmula? E, como as células podem ter comentários anexados, pretendemos também copiá-los? A primeira coisa que tem de saber é que a cópia de elementos de uma folha de cálculo funciona, à princípio, como a cópia de quaisquer outros elementos num documento de texto, por exemplo. Isto é, tudo o que aprendeu sobre clicar, arrastar o mouse e soltar, para selecionar uma área num texto, aplica-se igualmente numa folha de cálculo: clique numa célula e, sem largar o mouse, arraste-o na horizontal e na vertical, de forma a selecionar a área que pretende. EXCEL AVANÇADO Página | 33 Sidney Verginio da Silva sid.si@hotmail.com E para selecionar células que não são contínuas basta, ao realiza as seleções com o mouse, pressionar a tecla Ctrl do teclado. Na figura acima, foram selecionadas as células de B5 até D5 e de B8 até D8. Note como nas réguas de designação das linhas e colunas há uma mudança de cor para ajudar a visualizar as células selecionadas. Depois de selecionadas as células pretendidas, é possível copiá-las para qualquer outra zona da mesma planilha ou até para outra planilha da mesma pasta ou noutra pasta de trabalho (arquivo) qualquer. É possível utilizar as teclas convencionais para copiar (Ctrl+C), cortar (Ctrl+X) e colar (Ctrl+V) ou recorrer ao menu de contexto com o botão direito do mouse. Este último é preferível no caso de pretender utilizar comando Colar especial: Ao selecionar um conjunto de células, e depois de escolher a opção Copiar, surge de imediato, na célula copiada, uma borda tracejada em movimento que indica claramente qual a área a copiar. Para inserir os dados copiados, clique com o botão direito do mouse, na célula de destino e escolha a opção Colar. Uma vez que as células podem conter mais do que apenas dados simples, o comando Colar Especial permite escolher exatamente o que pretende-se colar. Clicando com o botão direito do mouse na célula de destino, e escolhendo a opção Colar Especial, surge uma caixa de diálogo que permite escolher o que deseja-se colar. EXCEL AVANÇADO Página | 34 Sidney Verginio da Silva sid.si@hotmail.com Inserir e apagar linhas e colunas Pode ser necessário acrescentar linhas e colunas numa planilha onde já haja muitos dados e fórmulas. 1. Para introduzir uma linha no meio de valores já introduzidos, comece por clicar no número da linha, do lado esquerdo da folha. A nova linha será inserida acima da linha selecionada. Ou seja, a nova linha vai assumir a mesma numeração da linha selecionada e todos os valores descem uma posição. EXCEL AVANÇADO Página | 35 Sidney Verginio da Silva sid.si@hotmail.com 2. Depois, clique com o botão direito do mouse, e escolha a opção Inserir. O resultado é uma linha nova que, contudo, não altera os resultados anteriores. Muito embora estas células de totais tenham fórmulas com referências a determinadas células, e estas tenham mudado de posição, o Excel muda as fórmulas automaticamente, evitando assim uma alteração dos resultados. O mesmo processo é válido para as colunas. 2.6 Tipos De Dados Como já mencionado, é possível inserir 4 tipos de dados nas células: - Numéricos: valores numéricos que podem ser introduzidos diretamente pelo usuário ou gerados automaticamente, através de fórmulas. - Texto: valores não numéricos que Excel trata como sendo apenas texto. Regra geral, tudo o que o Excel não reconheça e trate como dados numéricos é assumido como texto. - Datas e horas: dados numéricos ou alfanuméricos (conjugação de algarismos e letras) que são reconhecidos pelo Excel como data e/ou hora e que podem ate ser formatados de forma a serem automaticamente atualizados de acordo com a data real. - Fórmulas: as fórmulas são o aspecto mais importante do Excel, pois é através delas que realizamos cálculos. Introdução de dados Para Introduzir dados numa célula basta selecionar a célula pretendida com o mouse ou teclado, digitar os valores pretendidos e teclar ENTER. Os valores introduzidos na célula surgem também na Barra de fórmulas e vice-versa, o que permite introduzir os EXCEL AVANÇADO Página | 36 Sidney Verginio da Silva sid.si@hotmail.com valores a partir de qualquer destes pontos. Uma vez introduzido o valor pretendido, basta mudar o cursor para outro local, clicando noutra célula ou “saltar” de célula usando as teclas direcionais do teclado. Introdução de dados num conjunto de células Pode também repetir a introdução de dadosnum conjunto mais alargado de células, sem ter de repetir célula a célula. Para isso basta selecionar as células pretendidas, introduzir o valor numa delas e concluir pressionando CTRL+ENTER. Introdução de séries de dados – dados repetidos Ocorre quando deseja-se introduzir os mesmos dados em mais do que uma célula, isto é, células que se estendem para cima, para baixo ou para cada um dos lados da célula selecionada. Para isso: 1. Introduza o valor pretendido numa célula à sua escolha. 2. Posicione o cursor no canto inferior direito da célula, de modo a que o cursor se transforme numa cruz. 3. Arraste o cursor para os lados, para cima ou para baixo de forma preencher as células com o mesmo valor. EXCEL AVANÇADO Página | 37 Sidney Verginio da Silva sid.si@hotmail.com Note como surge um filete cinzento em torno das células afetadas pela sua seleção ou mesmo tempo que uma pequena caixa indica qual o valor que vai ser introduzido. 2.7 Formatação de Células – Dimensões Uma vez introduzidos novos valores nas células, estes poderão não caber no espaço predefinido. No Excel 2007 isto acontece quando a largura da coluna é inferior ao comprimento dos dados inseridos em alguma de suas células. Neste caso, o Excel apresenta a célula com o caractere #. Para alterar a largura da colua: 1. Posicione o mouse entre colunas até surgir o símbolo ; 2. Clique e arraste até a largura pretendida; 3. Solte o botão do mouse O mesmo processo pode ser feito para as linhas. Neste caso, ao posicionar o cursor na base da linha 6 e arrastá-lo, muda-se a altura desta linha 2.8 Formatação de Células - Layout e dados Este recurso é útil para dar ênfase a determinados valores numa planilha ou apresentar de forma mais organizada os dados armazenados nas células. A forma mais simples de realizar isto é recorrer ao grupo de comandos Fonte da Guia Início. A atribuição de formatos é sempre feita após a seleção de células ou de grupo de células a formatar. Pode-se fazer várias formatações básicas de forma rápida apenas EXCEL AVANÇADO Página | 38 Sidney Verginio da Silva sid.si@hotmail.com com o mouse. Dentre eles: bordas, fonte (tamanho, tipo de letra e cor), alinhamento do texto na células, recuo, etc. A caixa de diálogo Formatar Células permite alterar o formato de números e do texto nas células selecionadas de uma planilha. Para acessar, selecione a(s) célula(s) que deseja alterar e clique com o botão direito -> Formatar células. Também é possível clicar na seta do grupo Fonte, Alinhamento ou Número. EXCEL AVANÇADO Página | 39 Sidney Verginio da Silva sid.si@hotmail.com GUIA NÚMERO Use as opções na guia Número para aplicar um formato de número específico aos números nas células da planilha. Categoria Clique em uma opção na caixa Categoria e selecione as opções desejadas para especificar um formato de número. A caixa Exemplo mostra a aparência das células selecionadas com a formatação escolhida. Clique em Personalizado se quiser criar os seus próprios formatos personalizados para números, como códigos de produtos. Clique em Geral se quiser retornar para um formato de número não específico. Exemplo: Exibe o número na célula ativa na planilha de acordo com o formato de número selecionado. Casas decimais: Especifica até 30 casas decimais. Esta caixa está disponível apenas para as categorias Número, Moeda, Contábil, Porcentagem e Científico. Usar separador de milhar (,): Marque esta caixa de seleção para inserir um separador de milhar. Esta caixa de seleção está disponível apenas para a categoria Número. Números negativos: Especifica o formato no qual deseja que os números negativos sejam exibidos. Esta opção está disponível apenas para as categorias Número e Moeda. Símbolo: Selecione o símbolo da moeda que deseja usar. Esta caixa está disponível apenas para as categorias Moeda e Contábil. Tipo: Selecione o tipo de exibição que deseja usar para um número. Essa lista está disponível apenas para as categorias Data, Hora, Fração, Especial e Personalizado. Localidade (local) Selecione um idioma diferente que deseja usar para o tipo de exibição de um número. Esta caixa de listagem está disponível apenas para as categorias Data, Hora e Especial. GUIA ALINHAMENTO Use as opções na guia Alinhamento para alterar o alinhamento do conteúdo da célula, posicionar o conteúdo na célula e alterar a direção desse conteúdo. EXCEL AVANÇADO Página | 40 Sidney Verginio da Silva sid.si@hotmail.com Alinhamento de Texto Horizontal: Selecione uma opção na lista Horizontal para alterar o alinhamento horizontal do conteúdo das células. Por padrão, o Microsoft Office Excel alinha texto à esquerda, números à direita, enquanto os valores lógicos e de erro são centralizados. O alinhamento horizontal padrão é Geral. As alterações no alinhamento dos dados não alteram os tipos de dados. Vertical: Selecione uma opção na caixa de listagem Vertical para alterar o alinhamento vertical do conteúdo das células. Por padrão, o Excel alinha o texto verticalmente na parte inferior das células. O alinhamento vertical padrão é Geral. Recuo: Recua o conteúdo das células a partir de qualquer borda da célula, dependendo das opções escolhidas em Horizontal e Vertical. Cada incremento na caixa Recuo equivale à largura de um caractere. Orientação: Selecione uma opção em Orientação para alterar a orientação do texto nas células selecionadas. As opções de rotação poderão não estar disponíveis se forem selecionadas outras opções de alinhamento. Graus: Define o nível de rotação aplicado ao texto na célula selecionada. Use um número positivo na caixa Graus para girar o texto selecionado da parte inferior esquerda para a superior direita na célula. Use graus negativos para girar o texto da parte superior esquerda para a inferior direita na célula selecionada. Controle de texto Quebrar texto automaticamente: Quebra o texto em várias linhas dentro de uma célula. O número de linhas depende da largura da coluna e do comprimento do conteúdo da célula. Reduzir para caber: Reduz o tamanho aparente dos caracteres da fonte para que todos os dados de uma célula selecionada caibam dentro da coluna. O tamanho dos caracteres será ajustado automaticamente se alterar a largura da coluna. O tamanho de fonte aplicado não será alterado. Mesclar Células: Combina duas ou mais células selecionadas em uma única célula. A referência de célula de uma célula mesclada será a da célula superior esquerda da faixa original de células selecionadas. EXCEL AVANÇADO Página | 41 Sidney Verginio da Silva sid.si@hotmail.com Direção do Texto: Selecione uma opção na caixa Direção do Texto para especificar a ordem de leitura e o alinhamento. A configuração padrão é Contexto, mas é possível alterá-la para Da Esquerda para a Direita ou Da Direita para a Esquerda. GUIA FONTE Use as opções na guia Fonte para alterar a fonte, o estilo de fonte, o tamanho da fonte e outros efeitos de fonte. Fonte: Selecione o tipo da fonte para o texto nas células selecionadas. A fonte padrão é Calibri. Estilo da Fonte: Selecione o estilo da fonte para o texto nas células selecionadas. O estilo de fonte padrão é Normal. Tamanho: Selecione o tamanho da fonte para o texto nas células selecionadas. Digite qualquer número entre 1 e 1.638. O tamanho de fonte padrão é 11. Os tamanhos disponíveis na lista Tamanho dependem da fonteselecionada e da impressora ativa. Sublinhado: Selecione o tipo de sublinhado que deseja usar para o texto nas células selecionadas. O sublinhado padrão é Nenhum. Cor: Selecione a cor que deseja usar para as células ou o texto selecionados. A cor padrão é Automático. Fonte Normal: Marque a caixa de seleção Fonte Normal para redefinir o estilo, o tamanho e os efeitos da fonte com o estilo Normal (padrão). Efeitos: Permite que sejam selecionados um dos seguintes efeitos de formatação: - Tachado Marque esta caixa de seleção para exibir o texto em células selecionadas como tachado. - Sobrescrito Marque esta caixa de seleção para exibir o texto em células selecionadas como sobrescrito. - Subscrito Marque esta caixa de seleção para exibir o texto em células selecionadas como subscrito. Visualização Veja um exemplo de texto que é exibido com as opções de formatação que selecionadas. EXCEL AVANÇADO Página | 42 Sidney Verginio da Silva sid.si@hotmail.com GUIA BORDA Use as opções na guia Borda para aplicar uma borda ao redor de células selecionadas em um estilo e uma cor. Linha Selecione uma opção em Estilo para especificar o tamanho e o estilo de linha de uma borda. Para alterar o estilo de linha de uma borda já existente, selecione a opção de estilo de linha desejada e clique na área da borda no modelo de Borda onde quiser que o novo estilo de linha seja exibido. Predefinições: Selecione uma opção de borda predefinida para aplicar bordas nas células selecionadas ou removê-las. Cor: Selecione uma cor da lista para alterar a cor das células selecionadas. Borda: Clique em um estilo de linha na caixa Estilo e clique nos botões em Predefinições ou em Borda para aplicar as bordas nas células selecionadas. Para remover todas as bordas, clique no botão Nenhuma. Note que também é possível clicar nas áreas da caixa de texto para adicionar ou remover bordas. GUIA PREENCHIMENTO Use as opções na guia Preenchimento para preencher as células selecionadas com cores, padrões e efeitos de preenchimento especiais. Plano de Fundo: Selecione uma cor de plano de fundo para células selecionadas usando a paleta de cores. Efeitos de preenchimento: Selecione este botão para aplicar gradiente, textura e preenchimentos de imagem em células selecionadas. Mais Cores Selecione este botão para adicionar cores que não estão disponíveis na paleta de cores. Cor do Padrão: Selecione uma cor de primeiro plano na caixa Cor do Padrão para criar um padrão que usa duas cores. Estilo do Padrão: Selecione um padrão na caixa Estilo do Padrão para formatar células selecionadas com um padrão que usa as cores que são selecionadas nas caixas Cor de Plano de Fundo e Cor Padrão. Exemplo: Veja um exemplo das opções de cor, efeitos de preenchimento e de padrões que selecionar. EXCEL AVANÇADO Página | 43 Sidney Verginio da Silva sid.si@hotmail.com GUIA PROTEÇÃO Use as opções na guia Proteção para bloquear ou ocultar células antes de proteger a planilha. Bloqueadas Impede que as células selecionadas sejam alteradas, movidas, redimensionadas ou excluídas. O bloqueio das células apenas terá efeito quando a planilha estiver protegida. Ocultas: Oculta uma fórmula em uma célula para que ela não seja exibida na barra de fórmulas quando a célula for selecionada. Se selecionar essa opção, ela apenas terá efeito depois que planilha estiver protegida. Depois de bloquear e ocultar os dados nas células selecionadas, faça o seguinte para proteger a planilha: 1. Na guia Revisão, no grupo Alterações, clique em Proteger Planilha. 2. Certifique-se de que a caixa de seleção Proteger a planilha e o conteúdo de células bloqueadas esteja marcada. 3. Na caixa Permitir que todos os usuários desta planilha possam, selecione as atividades que os usuários poderão executar em uma planilha protegida que contém células bloqueadas. 2.8.1 Estilos de Célula Um estilo de célula é um conjunto definido de características de formatação, como fontes e tamanhos de fonte, formatos de número, bordas de célula e sombreamento de célula. EXCEL AVANÇADO Página | 44 Sidney Verginio da Silva sid.si@hotmail.com O Microsoft Office Excel possui vários estilos internos de célula que podem ser aplicados ou modificados. Também é possível modificar ou duplicar um estilo de célula para criar um estilo de célula personalizado. Aplicar um estilo de célula 1. Selecione as células que deseja formatar. 2. Na guia Início, no grupo Estilos, clique em Estilos de Célula. 3. Clique no estilo de célula que deseja aplicar. Criar um estilo de célula personalizado 1. Na guia Início, no grupo Estilos, clique em Estilos de Célula. 2. Clique em Novo Estilo de Célula. 3. Na caixa Nome do estilo, digite um nome apropriado para o novo estilo de célula. EXCEL AVANÇADO Página | 45 Sidney Verginio da Silva sid.si@hotmail.com 4. Clique em Formatar. 5. Nas várias guias da caixa de diálogo Formatar Células, selecione a formatação que deseja e então clique em OK. 6. Na caixa de diálogo Estilo, em Estilo Inclui (Por Exemplo), desmarque as caixas de seleção de qualquer formatação que não deseja incluir no estilo de célula. Criar um estilo de célula modificando o estilo de célula existente 1. Na guia Início, no grupo Estilos, clique em Estilos de Célula. 2. Siga um destes procedimentos: Para modificar um estilo de célula existente, clique com o botão direito do mouse no estilo de célula e então clique em Modificar. Para criar uma duplicata do estilo de célula existente, clique com o botão direito do mouse no estilo de célula e então clique em Duplicar. 3. Na caixa Nome do estilo, digite um nome apropriado para o novo estilo de célula. Um estilo de célula duplicada e um estilo de célula renomeado são adicionados à lista de estilos de célula personalizados. Se não renomear o estilo de célula interno, ele será atualizado com as alterações que forem efetuadas. 4. Para modificar o estilo de célula, clique em Formato. EXCEL AVANÇADO Página | 46 Sidney Verginio da Silva sid.si@hotmail.com 5. Nas várias guias da caixa de diálogo Formatar Células, selecione a formatação que deseja e então clique em OK. 6. Na caixa de diálogo Estilo, em Estilo Inclui, marque ou desmarque as caixas de seleção de qualquer formatação que deseja ou não incluir no estilo de célula. Remover um estilo de célula dos dados da planilha Este procedimento “limpa” a formatação de estilo que foi dada a uma célula. 1. Selecione as células formatadas com o estilo de célula que você deseja remover. 2. Na guia Início, no grupo Estilos, clique em Estilos de Célula. 3. Em Bom, Ruim e Neutra, clique em Normal. Excluir um estilo de célula predefinido ou personalizado Pode-se excluir um estilo de célula predefinido ou personalizado para removê-lo da lista de estilos de célula disponíveis. Quando se exclui um estilo de célula, ele também é removido de todas as células que estão formatadas com ele. 1. Na guia Início, no grupo Estilos, clique em Estilos de Célula. 2. Para excluir um estilo de célula predefinido ou personalizado e removê-lo de todas as células formatadas com ele, clique com o botão direito no estilo da célula e clique em Excluir. 2.9 Espaço de Trabalho É possível salvar um instantâneo do layout atual de todas as janelas de pasta de trabalho abertas e organizadas em um arquivo de espaço de trabalho (.xlw). Quandoum arquivo de espaço de trabalho for aberto, o Microsoft Office Excel abrirá todas as pastas de trabalho e as exibirá no layout que foi salvo, tal como uma fotografia das pastas e planilhas abertas. Para isso, 1. Abra as pastas de trabalho que deseja salvar em um espaço de trabalho. 2. Organize as janelas de pasta de trabalho exatamente como deseja exibi-las ao abrir o espaço de trabalho, alterando o tamanho de cada janela de pasta de trabalho conforme necessário. 3. Na guia Exibir, no grupo Janela, clique em Salvar Espaço de Trabalho. EXCEL AVANÇADO Página | 47 Sidney Verginio da Silva sid.si@hotmail.com 4. Na caixa Nome do arquivo, digite um nome para o arquivo de espaço de trabalho. O nome padrão é resume.xlw, mas é possível renomeá-lo. 5. Ao abrir o arquivo salvo, o Excel mostrará as planilhas organizadas tal como foram salvas no espaço de trabalho. EXCEL AVANÇADO Página | 48 Sidney Verginio da Silva sid.si@hotmail.com 3 REFERÊNCIAS ABSOLUTAS E RELATIVAS Ao copiar uma fórmula =A1*B1 para as linhas abaixo, o Excel entende que as fórmulas devem ser relativas, ou seja, mudam de acordo com as linhas. No exemplo usado =A1*B1 sendo relativas, mudam para =A2*B2, =A3*B3 e assim por diante. Mas quando um determinado endereço não deve ser alterado (ou seja, absoluto), antes de copiar deve-se usar o símbolo $ (ou pressionar F4) na fórmula que deseja-se copiar. Exemplo: Na fórmula =A1*B1 é desejável que, ao copiar para as linhas abaixo o endereço B1 permaneça absoluto, ou fixo: =A1*B$1. Notee que o símbolo $ está antes do número 1 que identifica a linha. Depois de copiado, as fórmulas ficam assim: =A2*B$1 =A3*B$1 =A4*B$1 =A5*B$1 Ao copiar uma fórmula para outras colunas, a regra é a mesma, ou seja, deve-se colocar o símbolo $ na fórmula na frente da letra que identifica a coluna que deve permanecer fixa. Usando o exemplo =A1*A2, ao copiar para as outras colunas, as demais ficariam: =B1*B2 =C1*C2 =D1*D2 Colocando o símbolo $ antes do A1, =$A1*A2, depois de copiado, ficaria: =$A1*B2 =$A1*C2 =$A1*D2 Para fixar a coluna e a linha usa-se =$A$1*A2, pois assim, ao copiar esta fórmula EXCEL AVANÇADO Página | 49 Sidney Verginio da Silva sid.si@hotmail.com para baixo ou para o lado na planilha, o endereço $A$1 sempre ficará fixo. Note no exemplo abaixo que, ao copiar a fórmula para as linhas abaixo, o Excel atualizou a fórmula de acordo com as referências das linhas, o que neste caso, é um erro. Para corrigir, basta inserir $ no endereço B7 da primeira linha, que é a célula que deve permanecer fixa neste cálculo, ficando a fórmula da seguinte forma: =B2/$B$7. Ao copiar para as próximas linhas, o Excel atualiza a referência da primeira célula (B3, B4...), mantendo fixa a referência da célula com o total ($B$7): EXCEL AVANÇADO Página | 50 Sidney Verginio da Silva sid.si@hotmail.com 4 FÓRMULAS E FUNÇÕES 4.1 Fórmulas e Funções Matemáticas Uma das grandes vantagens em se utilizar planilhas eletrônicas para trabalhar com dados são os cálculos e análises que podemos fazer com os mesmos. Para tanto, podemos criar fórmulas utilizando operadores aritméticos (soma, subtração, multiplicação, divisão), ou ainda, utilizar as inúmeras funções internas do Excel. Para indicar que determinada célula vai ser utilizada para realizar um cálculo, devemos primeiramente digitar o sinal de igual “=”. Digitar qualquer fórmula ou função sem este sinal resulta em uma mensagem de erro, ou então, o Excel pode entender que está sendo digitado um texto qualquer, como “25-6”, sem que isso gere um resultado. Outro detalhe importante é sempre digitar a fórmula ou função onde se quer apresentar o resultado. Portanto, para exibir o resultado de uma soma na célula E5, devemos digitar a fórmula da soma nesta célula. Fórmula e Função Embora pareçam iguais, e por vezes são confundidas, fórmula e função são coisas diferentes. Vejamos: FÓRMULA Pode ser entendida como uma expressão matemática que realiza determinado cálculo. Deve ter Operadores e Operandos: = 25 + 52 OPERANDO OPERADOR SINAL INDICATIVO DE CÁLCULO EXCEL AVANÇADO Página | 51 Sidney Verginio da Silva sid.si@hotmail.com Os operandos são os valores (números) que utilizamos para realizar o cálculo. Já Operadores são símbolos matemáticos que permitem fazer cálculos e comparações entre as células. Os operadores do Excel são: Operador de texto Significado Exemplo & (E comercial) Conecta dois valores para produzir um valor de texto contínuo ("North"&"wind") Operador de comparação Significado Exemplo = Igual a A1=B1 > Maior que A1>B1 < Menor que A1<B1 >= Maior ou igual a A1>B1 <= Menor ou igual a A1<B1 <> Diferente de A1<>B1 Operador aritmético Significado Exemplo + Adição 3+3 – Subtração Negação 3–1 –1 * Multiplicação 3*3 / Divisão 3/3 % Porcentagem 20% ^ (acento circunflexo) Exponenciação 3^2 EXCEL AVANÇADO Página | 52 Sidney Verginio da Silva sid.si@hotmail.com FUNÇÃO Funções são fórmulas pré-definidas pelo Excel. Permitem realizar de forma simples inúmeros cálculos e análises. As funções reduzem a carga de trabalho do usuário, já que trazem internamente diversas fórmulas que seriam extremamente trabalhosas de serem construídas. Uma função possui a seguinte estrutura: = SOMA (25;52) Toda função tem um nome. Os argumentos são dados que passamos para as funções para que estas realizem os cálculos. Podem ser passados através de valor (como número 25) ou por referência (como endereço da célula A5). As funções podem ter de 1 até vários argumentos, ou mesmo, nenhum, como no caso da função =AGORA(), que retorna a hora e data atual. A quantidade de argumentos é informada pelo Excel quando usamos o comando “Inserir Função” ou quando digitamos o nome da função: Neste exemplo, o Excel informa que podemos inserir diversos argumentos (valores) para a função SOMA, já que o texto explicativo apresenta os valores seguido por reticências (num1, num2...). Importante notar que toda função “retorna” um valor. Ao digitar a função e seus valores e teclar “ENTER”, no lugar da função será apresentado o resultado da mesma. A função pode ser vista na barra de funções.Nesta barra podemos editar a função. Também é possível editá-la selecionando a célula onde está a função e pressionando a tecla “F2”. NOME DA FUNÇÃO ARGUMENTOS DA FUNÇÃO SINAL INDICATIVO DE CÁLCULO EXCEL AVANÇADO Página | 53 Sidney Verginio da Silva sid.si@hotmail.com As funções devem sempre ser inseridas na célula onde se deve mostrar o resultado. No exemplo acima, para exibir a soma acima dos valores, devemos digitá-la na célula C2. Para inserir qualquer função, basta começar a digitar o nome dela na célula que o Excel automaticamente sugere a função. Também é possível utilizar o assistente, clicando na aba “Fórmulas” e depois no botão “Inserir Função”. Atenção !! Para inserir apenas células específicas, os nomes devem ser inseridos separados por ponto-e-vírgula - ; Para inserir todas as células de um intervalo, separamos os nomes das células por dois pontos - : = SOMA (A1 ; E1) => resulta na soma apenas das células A1 e E1.= SOMA (A1 : E1) => resulta na soma de todas as células que existem entre A1 e E1 (A1, B1, C1, D1, E1). Exemplos Fórmulas Simples No caso de realizar cálculos simples, com poucas células, é possível criar operações básicas indicando simplesmente o nome das células e a operação a realizar. Exemplo: ao introduzir =E5+E6, o Excel automaticamente soma os valores das células E5 e E6; quando alterar os valores em algumas destas células, o resultado altera- se automaticamente. EXCEL AVANÇADO Página | 54 Sidney Verginio da Silva sid.si@hotmail.com É possível introduzir o nome das células digitando manualmente através do teclado, ou clicando nelas com o mouse. O Excel indica através de cores diferentes quais são as células selecionadas. 4.2 Funções pré-definidas Além dos operadores aritméticos simples, o Excel suporta fórmulas mais avançadas através de funções. O Excel possui centenas de funções. Vejamos algumas fórmulas e funções mais utilizadas no dia-a-dia. 4.2.1 SOMA A função SOMA permite somar o conteúdo de duas ou mais células e é especialmente útil para séries de células. Vejamos um exemplo simples: O objetivo desta planilha é calcular o valor total de cada produto (quantidade multiplicada por valor unitário) e depois o total de todos os produtos. Para o total de cada produto precisamos utilizar o operador de multiplicação (*). No caso do Tablet temos a quantidade que está na célula A8 e o valor unitário está na célula C8. Nosso cálculo será feito na célula D4, que é célula onde deverá constar o total. Poderíamos fazer o seguinte cálculo: =3*200. Isso traria o resultado, porém se a quantidade ou o Valor Unitário fossem alterados seria preciso fazer novamente o cálculo. EXCEL AVANÇADO Página | 55 Sidney Verginio da Silva sid.si@hotmail.com O correto é então é fazer =A8*C8. Com isso multiplicamos referenciando as células, independente do conteúdo dela, e o Excel fará a multiplicação, desde que ali se tenha um número. Observe que ao fazer o cálculo este é colocado também na barra de fórmulas, e mesmo após pressionar ENTER, ao clicar sobre a célula onde está o resultado, é possível ver como se chegou ao resultado pela barra de fórmulas. Para o cálculo do teclado é necessário então fazer o cálculo da segunda linha A5*C5 e assim sucessivamente. Veja que a coluna representada pela letra não muda, muda-se somente o número que representa a linha, e se esta planilha tivesse uma grande quantidade de produtos, repetir o cálculo seria cansativo e com certeza sujeito a erros. Quando há uma sequência grande de cálculos, o Excel permite que se faça um único cálculo, e, ao posicionar o cursor do mouse no canto inferior direito da célula, o cursor se transforma em uma cruz. Ao arrastar esta cruz pelas células seguintes, o Excel copia a EXCEL AVANÇADO Página | 56 Sidney Verginio da Silva sid.si@hotmail.com fórmula nas células, alterando o número da linha de acordo com a posição da célula. Calculando a Soma Total Para calcular o total poderia se utilizar o seguinte cálculo: D4+D5+D6+D7+D8. Porém isso não seria nada pratico em planilhas maiores. É aqui que podemos utilizar o recurso das funções. No caso a função a ser utilizada é a função SOMA, e sua estrutura é =SOMA(CelIni:Celfim), ou seja, inicia-se com o sinal de igual (=), escreve-se o nome da função, abrem-se parênteses, clica-se na célula inicial da soma e arrasta-se até a última célula a ser somada (ou digita-se manualmente as células). Este intervalo é representado pelo sinal de dois pontos (:), e fecham-se os parênteses. Embora seja possível fazer manualmente na célula o Excel possui um assistente de função que facilita e muito a utilização das mesmas em sua planilha. Na ABA Inicio do Excel dentro do grupo Edição existe o botão de SOMA: A primeira função é justamente Soma, então clique na célula vazia embaixo dos valores e clique no botão de função. EXCEL AVANÇADO Página | 57 Sidney Verginio da Silva sid.si@hotmail.com Observe conforme a imagem que o Excel acrescenta a soma e o intervalo de células, pressione ENTER e o cálculo é realizado. 4.2.2 MÉDIA Esta função é escrita com acento no “e” (e, tal como todas as outras funções, pode ser escrita com caracteres maiúsculos ou minúsculos). Esta calcula a média aritmética simples dos valores constantes nas células indicadas como argumento. Em outras palavras, a função soma os valores das células e divide pelo número de células com valores. 4.2.3 MULT Esta função multiplica valores constantes nas células. Seria o mesmo que inserir uma fórmula = C4*D4*E4*F4. Neste caso, podemos simplificar escrevendo = MULT(C4:F4). EXCEL AVANÇADO Página | 58 Sidney Verginio da Silva sid.si@hotmail.com 4.2.4 MÍNIMO Mostra o valor mínimo de uma seleção de células. Exemplo: Vamos utilizar essa função para saber os valores mínimos nas características dos atletas. Nesta planilha clique na célula abaixo da coluna de idade na linha de valores máximos (E16), e monte a seguinte função =MÍNIMO(E4:E13). Com essa função O Excel busca no intervalo das células E4 à E13 qual é o valor mínimo encontrado. 4.2.5 MÁXIMO Similar a função anterior, esta mostra o MAIOR valor em uma seleção de células. Pode-se utilizar essa função para saber qual é a maior idade, o maior peso e a maior altura. Seguindo o mesmo exemplo anterior, basta clicar na célula abaixo da coluna de idade na linha de valores máximos (E15) e montar a seguinte função =MAXIMO(E4:E13). Com essa função O Excel no intervalo das células E4 à E13 qual é o valor máximo encontrado. EXCEL AVANÇADO Página | 59 Sidney Verginio da Silva sid.si@hotmail.com 4.2.6 DIVISÃO Para efetuar divisões, pode-se utilizar o operador de divisão: /. No entanto, ao realizar cálculos com este operador, o resultado será um número inteiro no caso de divisão exata, ou um número decimal caso contrário. Caso a intenção seja obter apenas a parte inteira de uma divisão, é possível utilizar a função = QUOCIENTE (). Veja a diferença: = 20 / 3 => o resultado será 6,6667 = QUOCIENTE (20;3) => o resultado será 6. = 20 / 2 => o resultado será 10 = QUOCIENTE (20;2) => o resultado será 10. 4.2.7 EXPONENCIAÇÃO (Exponencial) E RAIZ Também é possível calcular raízes e potências utilizando o Excel. Para calcular a raiz de um número, basta usar a função =RAIZ(), inserindo como argumento o número. Para potências, pode-se utilizar o operador de Exponenciação (^) ou a função = POTENCIA(num, potencia), da seguinte forma: Calcular o quadrado de 4: = (4^2) => 16, ou = POTÊNCIA (4;2) = 16 Calcular o cubo de 3: = (3^3) => 27, ou = POTÊNCIA (3;3) => 27 Calcular a raiz de 64: = RAIZ (64) => 8 EXCEL AVANÇADO Página | 60 Sidney Verginio da Silva sid.si@hotmail.com 4.2.8 PORCENTAGEM Calcular porcentagem de valores pode ser feito usando o operador de Porcentagem %. Por exemplo, imagine que precisamos calcular uma comissão de 20% do valor total de venda dos itens abaixo: Primeiramente, somamos o valor total da venda dos 2 itens. Multiplicamos então este valor por 20%. De forma mais prática, podemos utilizar uma função juntamente com o operador %. Vejamos: Faça no Excel e veja o resultado. 4.2.9 ARRED A função ARRED(num; num_digitos) arredonda um número para um número especificado
Compartilhar