Baixe o app para aproveitar ainda mais
Prévia do material em texto
EXCEL BÁSICO E INTERMEDIÁRIO Federação das Indústrias do Estado de Minas Gerais - FIEMG SENAI - CATAGUASES 2015 Presidente da FIEMG Olavo Machado Júnior Diretor Regional do SENAI Cláudio Marcassa Gerente de Educação Profissional Edmar Fernando de Alcântara Federação das Indústrias do Estado de Minas Gerais - FIEMG Serviço Nacional de Aprendizagem Industrial - SENAI Departamento Regional de Minas Gerais Centro de Formação Profissional “José Ignácio Peixoto” EXCEL BÁSICO E INTERMEDIÁRIO Elaboração Carlos Henrique de Oliveira Monteiro André Unidade Operacional Centro de Formação Profissional “José Ignácio Peixoto” SENAI - Cataguases 2015 © 2015. SENAI. Departamento Regional de Minas Gerais SENAI/MG Centro de Formação Profissional “José Ignácio Peixoto” Ficha Catalográfica SENAI Serviço Nacional de Aprendizagem Industrial Departamento Regional de Minas Gerais FIEMG Av. do Contorno, 4456 Bairro Funcionários 30110-916 – Belo Horizonte Minas Gerais 5 Sumário 1. Explorando a Janela do Excel ............................................................................ 8 1.1. Acessando o Excel .................................................................................................... 8 1.2. Área de trabalho do Excel .......................................................................................... 8 1.3. Ajuda ........................................................................................................................ 11 1.4. Entendendo Melhor a Guia Arquivo .......................................................................... 12 1.5. Inserindo dados ........................................................................................................ 17 1.6. Usando modelos prontos para cria planilhas ............................................................ 19 1.6.1. A faixa de Opções ................................................................................................. 20 1.6.1.1. Guias da faixa de opções ................................................................................... 21 2. Editando uma planilha ....................................................................................... 22 2.1. Navegando pela planilha ativa .................................................................................. 22 2.2. Navegando entre as planilhas .................................................................................. 22 2.3. Alterando o Nome das Planilhas............................................................................... 23 3. Formatando Células .......................................................................................... 28 4. Trabalhando com Fórmulas .............................................................................. 36 5. Utilizando Funções ............................................................................................ 39 6. Classificando e filtrando dados ......................................................................... 42 7. Elementos Gráficos ........................................................................................... 45 8. Imprimindo planilhas ......................................................................................... 50 9. Verificando ortografia e gramática .................................................................... 50 10. Fórmulas com referências Absolutas e Mistas ................................................ 56 11. Múltiplas Planilhas e Múltiplas Pastas............................................................. 57 11.1. Múltiplas Planilhas .................................................................................................. 57 11.2. Múltiplas Pastas ..................................................................................................... 58 12. Funções de data e hora ................................................................................... 59 12.1. Entendendo a Data................................................................................................. 59 12.1.1. Manipulação de Data ........................................................................................... 59 12.1.2. Função ABS() ...................................................................................................... 59 12.1.3. Função HOJE().................................................................................................... 60 12.1.4. Função Dia.da.Semana() ..................................................................................... 60 12.1.4.1. Funções dia(), mês(), ano() .............................................................................. 61 12.2. Entendendo a Hora................................................................................................. 61 6 12.2.1. Manipulação de Hora........................................................................................... 62 12.2.2. Função agora() .................................................................................................... 62 12.2.3. Funções hora(), minuto(), segundo() ................................................................... 62 12.2.4. Fazendo cálculo com horas ................................................................................. 62 12.3. Fazendo cálculo com data e hora ........................................................................... 63 13. Banco de Dados .............................................................................................. 64 13.1. Função BDMÉDIA() ................................................................................................ 65 13.2. Função BDCONTAR() ............................................................................................ 66 13.3. Função BDMIN()..................................................................................................... 66 13.4. Função BDMÁX() ................................................................................................... 67 13.5. Função BDSOMA() ................................................................................................. 67 14. Gráficos ........................................................................................................... 69 14.1. O que são gráficos? ............................................................................................... 69 14.2. Tipos de gráficos .................................................................................................... 69 14.2.1. Gráfico de Colunas .............................................................................................. 69 14.2.2. Gráfico de Linhas ................................................................................................ 69 14.2.3. Gráfico de Pizza .................................................................................................. 70 14.2.4. Gráfico em Barra ................................................................................................. 70 14.2.5. Gráfico de Área ................................................................................................... 70 14.2.6. Gráfico de Dispersão (XY) ................................................................................... 70 14.2.7. Gráficos de Ações ...............................................................................................70 14.2.8. Gráfico de Superfície ........................................................................................... 71 14.2.9. Gráficos de Rosca ............................................................................................... 71 14.2.10. Gráfico de Bolhas .............................................................................................. 71 14.2.11. Gráfico de Radar ............................................................................................... 71 15. Tabela Dinâmica.............................................................................................. 72 15.1. Criar um relatório de tabela dinâmica ..................................................................... 72 15.2. Campo Calculado ................................................................................................... 77 15.3. Item Calculado ....................................................................................................... 79 16. Gráfico Dinâmico ............................................................................................. 81 17. Funções de Procura ........................................................................................ 82 17.1. Função PROCH() ................................................................................................... 82 17.2. Função PROCV() ................................................................................................... 84 17.3. Função INDICE() .................................................................................................... 86 17.3.1. Forma matricial .................................................................................................... 86 7 17.3.2. Forma de Referência ........................................................................................... 86 17.4. Função CORRESP() ............................................................................................... 87 18. Função Condicional =SE()............................................................................... 89 19. Referências Bibliográficas ............................................................................... 90 8 11.. EExxpplloorraannddoo aa JJaanneellaa ddoo EExxcceell 1.1. Acessando o Excel Há várias formas de acessar o Excel. A mais utilizada é clicar no botão Iniciar, Todos os programas, Microsoft Office, Microsoft Excel 2010. Usualmente criamos um atalho na área de trabalho para facilitar seu acesso. 1.2. Área de trabalho do Excel Logo que abrimos o Excel, uma pasta de trabalho é criada automaticamente com o nome de Pasta1 e é visualizada uma janela. Guia Arquivo – Clicando sobre a guia Arquivo, aparecerão funções como Salvar, Imprimir, Fechar e Sair entre outras, que são as funções mais usadas; Você poderá personalizar a Barra de Ferramentas de Acesso Rápido, clicando na Caixa de listagem (lista de opções disponíveis). Para ativar ou desativar um comando, basta clicar sobre ele. 9 Barra de Título – Mostra o nome da pasta de trabalho que está aberta e o título do programa; Botão Ajuda – Acessa a ajuda do Office Online ou a local (salva no computador). Controles da janela – Controles para Minimizar, Maximizar (ficar do tamanho da tela), Rest. Tamanho (restaurar o tamanho da janela de acordo com o que foi definido anteriormente pelo usuário) e Fechar a janela. Você poderá utilizá-los para o Excel ou para cada pasta de trabalho aberta; Faixa de Opções – É o local onde estão os principais comandos do Excel, separados por guias: Arquivo, Página Inicial, Inserir, Layout da Página, Fórmulas, Dados, Revisão e Exibição; Caixa de Nomes – Exibe o nome da célula ativa, formado pela letra da coluna e o número da linha. Por exemplo, A1. Caso várias células estejam selecionadas, será exibido o nome da primeira; Célula – Área de dados limitada por Linhas e Colunas; Célula Ativa – Célula que receberá o conteúdo a ser digitado; Linha – Conjunto de células na posição horizontal; Coluna – Conjunto de células na posição vertical; Intervalo de Célula – Duas ou mais células selecionadas; Inserir Função – Insere uma fórmula predefinida que simplifica e reduz as fórmulas na planilha; Barra de Fórmulas – Local onde os valores digitados (números, fórmulas e textos) serão visualizados e editados; 10 Expandir Barra de Fórmulas – Aumenta a visualização da Barra de fórmulas. Utilizado quando se há uma fórmula extensa na pasta de trabalho. Está localizado à direita dela; Divisores de Planilha Vertical e Horizontal – Dividem a planilha vertical ou horizontalmente para facilitar a visualização dos dados. Estão localizados nas barras de rolagem vertical e horizontal respectivamente; Barras de Rolagem – Permitem navegar pela planilha, quando ela for maior que a tela; Botões de Movimentação Entre Planilhas – Permitem movimentar-se entre as planilhas que não estão sendo visualizadas na guia de planilhas; Guia de Planilhas – Exibe os nomes das planilhas que fazem parte da pasta e permite inserir novas planilhas; Barra de Status – Mostra informações sobre a planilha atual, tais como se a célula está sendo editada, se o conteúdo está pronto etc. Fica localizada na margem inferior da tela; Modos de Exibição – As opções de exibição são encontradas à direita da Barra de status e permitem alterar a visualização da planilha. São as seguintes: Objeto Função Normal - Exibir a planilha no modo normal. Layout a Página - Exibir a planilha da forma como será impressa. Visualização de quebra de página - Mostrar as quebras de página que existirem na planilha. Controle de Zoom - Aumentar ou diminuir a visualização do documento. Há três formas de utilizar esse controle: 1 - Clicar sobre o valor da porcentagem de zoom atual e escolher o valor desejado; 2 - Clicar sobre os botões Reduzir ou Ampliar zoom; 3 - Clicar no botão Zoom entre - e + e arrastá-lo até a posição desejada. 11 1.3. Ajuda Se precisar de ajuda, basta clicar no botão Ajuda , localizado no canto superior direito da janela, ou pressionar a tecla F1. Abrir-se-á, então, uma janela. Objetivo Descrição Função Voltar e Avançar Voltar ou Avançar para outra pesquisa, feita anteriormente. Parar Parar uma pesquisa, antes que ela seja completamente apresentada. Atualizar Atualizar a pesquisa, mostrando novos resultados, caso existam. Residência Voltar para a página inicial da janela de Ajuda. Imprimir Imprimir o conteúdo da área de pesquisas. Alterar tamanho da fonte Alterar o tamanho da fonte, que, por padrão, é médio. Os tamanhos predefinidos são: máximo, maior, médio, menor e mínimo. Mostrar / Ocultar sumário Mostrar ou exibir o sumário dos tópicos de ajuda. Manter visível / Nem sempre visível Exibir a janela de ajuda por cima do documento utilizado (Manter visível). Ela será oculta se você alternar para outro aplicativo ou minimizá-la. A opção Nem sempre visível abrirá a janela na barra de tarefas do Windows. 12 Pesquisar Permitir a digitação do assunto a ser pesquisado. Para efetuar a pesquisa, deve-se clicar no botão Pesquisar. Ao clicar na seta para abrir a lista de opções, podemos obter o seguinte: 1. Conteúdo do Office.com, que trará ajuda do site da Microsoft, ou seja, a mais atualizada. 2. Conteúdo deste computador, que abrirá a ajuda instalada junto com o Pacote Office. 1.4. Entendendo Melhor a Guia Arquivo Ao clicar na guia Arquivo > Informações, encontraremos o seguinte ambiente. 13 Principais Funções: Salvar – Para salvar (gravar) uma pasta de trabalho, faça o seguinte: 1. Clique na guia Arquivo. 2. Em seguida, clique em Salvar. Vamos salvar a pasta de trabalhoque acabamos de criar, com o nome de Teste_salvar. Vale lembrar que a pasta de trabalho é salva com as alterações realizadas até o momento da ação de salvar, isto é, tudo o que você fizer depois disso não estará salvo, enquanto não clicar novamente em Salvar. O arquivo terá a extensão .xlsx, que é inserida automaticamente pelo Excel. Não se esqueça de que todas as planilhas serão salvas e não apenas a atual. Sempre que um arquivo for salvo pela primeira vez, esta caixa será apresentada para definição de: Nome do arquivo, Tipo de arquivo e Local. Salvar como – Para salvar uma pasta de trabalho no Excel, você deve clicar na guia Arquivo e, em seguida, em Salvar Como . Se for necessário salvar a pasta de trabalho com outro nome ou em outro local, faça o seguinte: 1. Clique na guia Arquivo. 2. Em seguida, clique no botão Salvar como. Digite o nome desejado e/ou selecione o local desejado (HD, CD, Pen drive etc.). 14 3. Salve a pasta de trabalho com o nome Teste_salvar_como.xlsx 4. Feche a pasta de trabalho. Abrir – Há uma área com o título Recente. Nessa área, aparecerão os últimos arquivos acessados com o ícone no canto direito . Para fixar uma pasta de trabalho na lista dos mais recentes, basta clicar neste ícone, que apresentará a seguinte forma : Ao clicar no botão Abrir, aparecerá uma janela. Nela, você deve procurar o arquivo que deseja abrir. Abra o arquivo Teste_salvar. Fechar – Existem várias formas de encerrar o Excel. Uma delas é clicar na guia Arquivo e no botão Fechar. Caso haja alguma alteração em sua pasta de trabalho que não tenha sido salva, aparecerá a seguinte mensagem: 15 Feche a pasta de trabalho sem salvá-la. Novo – Essa opção abre uma janela. Nela, você pode escolher um modelo (layout) para o novo documento. O modelo padrão é Pasta de trabalho em branco. Imprimir – Para imprimir uma planilha, é necessário ter uma impressora conectada ao computador ou a uma rede local. Ao clicar no botão Imprimir, aparecerão três grupos (Imprimir, Impressora e Configurações) e a Visualização da impressão do lado direito. No grupo imprimir é possível definir o número de cópias. Em Impressora serão apresentadas as impressoras disponíveis. Além disso, você poderá adicionar uma nova impressora ou realizar uma impressão em arquivo 16 Em Configurações, pode-se selecionar o que é necessário imprimir (Planilhas Ativas, Toda a pasta de trabalho, Somente o que está selecionado). 17 Na mesma área, é possível definir quais páginas deverão ser impressas. A próxima configuração permite escolher lados que deverão ser impressos. Ainda em Configurações, estão disponíveis as definições de orientação de página. Para configurar um novo formato de margem, clique na opção Margens Personalizadas. Para Visualizar Impressão, clique na guia Arquivo e, em seguida, Imprimir. Observe que a visualização ficará disponível a direita da tela. Este recurso lhe permite conferir como ficará seu documento. O zoom fica disponível no canto inferior direito da tela através do botão Aplicar Zoom a página e para visualizar as demais páginas clique no botão Próxima Página/Página Anterior. Para voltar à visualização normal, clique em qualquer guia da faixa de opções. 1.5. Inserindo dados Por padrão, a pasta de trabalho é criada com três planilhas. Cada uma delas tem um nome na guia de planilha: Plan1, Plan2 e Plan3. Os dados são inseridos na planilha ativa. Para navegar entre elas, basta clicar sobre seu nome. 18 Agora, vamos inserir alguns dados na pasta Teste_salvar.xlsx. Na planilha Plan1, digite as informações de acordo com a figura abaixo, esses dados serão utilizados posteriormente para praticarmos os demais comandos. 1 - Digite os dados e pressione a tecla ENTER para mover o cursor para a célula abaixo ou a tecla TAB para mover o cursor para a célula à direita. Ao começar a digitar, você verá o conteúdo na célula e na Barra de Fórmulas. Se você pressionar a tecla ESC, o conteúdo digitado será cancelado. Você deve ter observado que o conteúdo da célula B1 – Nome do Produto e B2, B4, B6, B10, B11 – é maior que a largura da coluna e “invadiu” as células da coluna C. Isso acontece, porque as células da coluna C estão vazias. Assim que elas forem preenchidas, o conteúdo será visualizado parcialmente (figura 41). Mais adiante, veremos como resolver esse problema. Pode-se observar que a célula A1 aparece com o conteúdo “cortado”. 2 – Insira os dados Preço Unitário e Estoque. 19 3 – Salve a pasta de trabalho. 1.6. Usando modelos prontos para cria planilhas A seguir, vamos ver alguns modelos de planilhas que podem ser feitos com o auxílio do Excel. Para acessá-los, faça o seguinte: 1. Clique na guia Arquivo. 2. Clique no botão Novo. 3. Antes de efetuar o download do arquivo, é necessário escolher a categoria, então, em Modelos do Office.com, clique na categoria desejada para que sejam carregados os modelos relacionados. 4. Para este exemplo, clique na categoria Orçamentos. 5. Após clicar na categoria Orçamentos, ficarão disponíveis as opções relacionadas. 6. Escolha Orçamento de faculdade. 7. Clique em baixar. 8. O Download irá começar e o modelo será carregado automaticamente. 20 9. Salve a pasta criada com o nome Orçamento_modelo_faculdade.xlsx. 1.7. A faixa de Opções A Faixa de Opções mostra os comandos mais utilizados agrupados por temas, para que você não precise procurá-los em vários menus, facilitando o trabalho. A seguir, apresentamos a Faixa de Opções e como trabalhar com ela. O que há na Faixa de Opções? Há três componentes básicos na Faixa de Opções: Guias – Há oito guias básicas na parte superior. Cada uma representa uma área de atividade e apresenta os comandos reunidos por grupos. Por exemplo, a guia Página Inicial contém todos os comandos que você utiliza com mais frequência. E os botões Recortar, Copiar e Colar estão no grupo Área de transferência. Grupos – Cada guia tem vários grupos, que mostram os itens relacionados em conjunto. Comandos – Um comando é um botão, uma caixa para inserir informações ou um menu. 21 1.7.1. Guias da faixa de opções Arquivo – Acessa a área de gerenciamento de arquivos chamada Backstage. Nessa guia, por exemplo, estão os comandos para criar, salvar e imprimir arquivos, além dos que permitem alterar as configurações do Excel. Resumindo, tudo aquilo que se faz para uma pasta de trabalho e não na pasta de trabalho está nesta guia. Página Inicial – Área de transferência, Fonte, Alinhamento, Número, Estilo, Células e Edição. Inserir – Planilhas, Ilustrações, Gráficos, Minigráficos, Link, Texto e Símbolos. Layout da Página – Temas, Configurar Página, Dimensionar para Ajustar, Opções de Planilha e Organizar. Fórmulas – Biblioteca de Funções, Nomes Definidos, Auditoria de Fórmulas e Cálculo. Dados – Obter Dados Externos, Conexões, Classificar e Filtrar, Ferramentas de Dados e Estrutura de Tópicos. Revisão – Revisão de Texto, Idioma, Comentários e Alterações. Exibição – Modo de Exibição de Pasta de Trabalho, Mostrar/Ocultar, Zoom, Janela e Macros. Alguns grupos têm uma seta diagonal no canto inferior direito , chamada de Iniciador de Caixa de Diálogo. Ao clicar nela, você verá mais opções relacionadas a esse grupo. 22 22.. EEddiittaannddoo uummaa ppllaanniillhhaa 2.1. Navegando pela planilha ativa Existem várias maneiras de navegar pela planilha ativa. Além da barra de rolagem, que vimos anteriormente, e do mouse, podemos utilizar as seguintes teclas: Tecla Objetivo Home Ir para o início da linha Tab Avançar uma coluna Shift+Tab Retroceder uma coluna Enter Avançar uma linha Shift+Enter Retroceder uma linha Ctrl+Seta paraDireita Ir para a última coluna da planilha Ctrl+Seta para Baixo Ir para a última linha da planilha Page Down Avançar tela Page Up Retroceder tela Ctrl+Home Ir para a primeira célula Ctrl+ End Ir para a última célula com conteúdo 2.2. Navegando entre as planilhas Você pode selecionar rapidamente uma planilha diferente, clicando em seu nome na Guia de Planilhas. Caso a planilha desejada não esteja visível, clique nos botões de rolagem de guias para exibi-la. 23 2.3. Alterando o Nome das Planilhas Para alterar o nome da planilha da maneira mais utilizada, basta dar um clique duplo sobre o nome dela na Guia de Planilhas, digitar o nome desejado e pressionar a tecla ENTER. Podemos alterar também clicando com o botão direito sobre o nome da guia e indo na opção renomear. 2.4. Selecionando partes da planilha desejada Como fazer? Objetivo Clique na célula desejada. Selecionar uma célula Clique na primeira célula e arraste o mouse até a última. Selecionar células sequenciais Clique na primeira célula e mantenha pressionada a tecla CTRL, clique nas demais células. Selecionar células intercaladas Clique no número da linha desejada (1, 2,...n). O cursor deverá ter esta forma: Selecionar a linha inteira Clique no nome da coluna desejada (A, B, ...,n). O cursor deverá ter esta forma: Selecionar a coluna inteira Clique no botão , à esquerda da coluna A. Selecionar a planilha toda 2.5. Selecionando planilhas Para selecionar duas ou mais planilhas adjacentes (próximas), clique no nome da primeira, mantenha pressionada a tecla SHIFT e clique na última planilha que deseja selecionar. Para selecionar duas ou mais planilhas não adjacentes (não próximas/intercaladas), clique no nome da primeira, mantenha pressionada a tecla CTRL e clique nas outras planilhas que deseja selecionar. 24 Para selecionar todas as planilhas de uma pasta de trabalho clique com o botão direito do mouse no nome de uma planilha e em Selecionar Todas as Planilhas. 2.6. Excluindo dados Você poderá substituir o conteúdo da célula, clicando sobre ela e digitando um novo conteúdo. Se precisar corrigir algum caractere ou número, dê um clique duplo na célula desejada ou pressione a tecla F2. Observe que o cursor ficará piscando no final do conteúdo da célula. Além disso podemos ir na barra de fórmulas e alterar o valor da fórmula (conteúdo). 2.7. Alterando o tamanho das colunas Quando temos dados que ultrapassam o tamanho delimitado de uma coluna, podemos fazer o ajuste do tamanho das colunas para que não “percam” as informações. Temos três formas: 1. Posicione o ponteiro do mouse do lado direito do nome da coluna que queira ajustar, ele mudará para o formato: . Arraste-o até a largura desejada e solte-o. 2. Selecione toda as colunas que queira ajustar, posicione o ponteiro do mouse do lado direito do nome de uma das colunas, ele mudará para o formato: . Arraste-o até a largura desejada e solte-o. 3. Acesse o menu Página inicial, o grupo Formatar e faça o ajuste de sua preferência. 25 2.8. Alterando a altura das linhas Da mesma forma que aprendemos com as colunas, quando temos dados que ultrapassam o tamanho delimitado de uma linha, podemos fazer o ajuste do tamanho das colunas para que não “percam” as informações. Temos três formas: 1. Posicione o ponteiro do mouse do lado direito do nome da coluna que queira ajustar, ele mudará para o formato: . Arraste-o até a largura desejada e solte-o. 2. Selecione toda as colunas que queira ajustar, posicione o ponteiro do mouse do lado direito do nome de uma das colunas, ele mudará para o formato: . Arraste-o até a largura desejada e solte-o. 3. Acesse o menu Página inicial, o grupo Formatar e faça o ajuste de sua preferência. 2.9. Inserindo células Toda vez que necessitarmos inserir uma nova CÉLULA em uma planilha, precisamos parar sobre uma célula próxima de onde gostaríamos de acrescentar, clicar com o botão direito do mouse, e acessar a opção inserir. 26 A caixa de diálogo irá aparecer e podemos deslocar as células para cima, para baixo, inserir linha e inserir coluna. 2.10. Inserindo colunas Podemos acessar da mesma forma que fizemos ao inserir células ou simplesmente clicar com o botão direito sobre o local ao lado de onde quer inserir (nome da coluna) e escolher a opção inserir. 2.11. Inserindo linhas Da mesma forma que fizemos nas linhas, podemos acessar da mesma forma que fizemos ao inserir células ou simplesmente clicar com o botão direito sobre o local ao lado de onde quer inserir (nome da linha) e escolher a opção inserir. 2.12. Inserindo planilha Se as três planilhas criadas automaticamente não forem suficientes para os registros de seus dados, você poderá inserir quantas forem necessárias. O limite é a memória do computador. Observe a guia de planilhas e veja que, após a última delas, há um botão para essa finalidade. 2.13. Excluindo Célula, colunas e linhas Da mesma forma como fizemos o processo de inserir, porém usaremos a opção excluir. 27 2.14. Excluindo planilha Para excluir uma planilha, clique sobre o nome dela com o botão direito do mouse, escolha a opção exclui. 28 33.. FFoorrmmaattaannddoo CCéélluullaass Um recurso do Office 2010 que facilita a formatação do documento é o preview ou pré- visualização de uma alteração sem ter que aplicá-la. Por exemplo, para alterar o tipo de fonte (letra), conforme você passa o mouse pelas fontes instaladas, a célula selecionada é, automaticamente, visualizada como ficará. E isso vale para todas as opções de formatação: cor, alinhamento, tamanho, estilo etc. Utilizaremos os comandos da guia Página Inicial para trabalhar com as formatações mais comuns de caracteres e números, que incluem mudança de fonte, cor, tamanho, formato de data e moeda dentre outras. 3.1. Alterando Fonte, Cor e Tamanho do caractere ou número Todos esses recursos estão no agrupamento Fonte. Recurso Objetivo Alterar o estilo de fonte. (padrão Calibri) Alterar o tamanho da fonte. (padrão 11) Este recurso pode ser utilizado também com os botões: Plano de fundo. (padrão transparente) Cor da Letra. (padrão preto) Negrito. Itálico. Sublinhado simples. Sublinhado duplo. 29 3.2. Alterando o alinhamento da célula Todos esses recursos estão no agrupamento Alinhamento. Recurso Objetivo Alinhar em cima. Alinhar no meio. Alinhar embaixo. Alinhar texto à esquerda. Centralizar. Alinhar texto à direita. Quebrar texto automaticamente. (dimensiona o texto para o tamanho da célula) Mesclar e Centralizar células. (juntar mais de uma célula). Diminuir e aumentar o recuo do texto. Girar o texto para a posição desejada. 3.3. Alterando a formatação dos valores numéricos Todos esses recursos estão no agrupamento Número. Recurso Objetivo Abre um menu de estilos. Sendo eles: 30 Exclui a formatação existente. Insere duas casas decimais ao número. Insere o símbolo monetário selecionado e casas decimais correspondentes ao número. Existem vários símbolos: $ (dólar), € (euro), ¥ (chinês). É semelhante ao estilo moeda, porém alinha os símbolos monetários. Converte um número em data no formato dd/mm/aaaa. Converte um número ou uma data abreviada no formato por extenso: dia da semana, dia do mês e ano. Formata a hora de acordo com o estilo selecionado Multiplica o valor por 100 e exibe o resultado com o símbolo de porcentagem. Converte um número decimal em fracionário. Converte um número em sua correspondente notação científica. Converte um número em texto da forma como ele foi digitado. Abre um menu com mais modelos de cada estilodescrito acima. Separador de milhar. Aumentar casas decimais. Diminuir casas decimais. 31 3.4. Inserindo bordas O excel tem a linha de grade para marcação das células, porém os contornos (bordas) não parecem durante a impressão dos dados, caso queira apresentar as bordas, acesse o recurso que está no grupo Fonte. 3.5. Copiando uma formatação Assim que a formatação de uma célula estiver adequada, ela poderá ser copiada para outras de uma maneira muito simples. Esse recurso no Excel é dado com o nome de pincel. Para fazermos isso: 1- Clique sobre a célula que gostaria de copiar; 2- Acesse o grupo Área de Trabalho o recurso pincel . 32 3- Clique sobre a célula que quer aplicar o recurso. Outra forma de fazer é copiar e fazer uma colagem especial de formatação. 3.6. Movendo células, colunas ou linhas. Você pode mover qualquer área selecionada de sua planilha, realizando uma operação de recortar e colar: recorte o conteúdo para excluí-lo do local atual e, em seguida, cole-o no novo local. 3.7. Copiando Células, Colunas ou Linhas Os procedimentos para copiar conteúdos selecionados são praticamente os mesmos de recortar. A diferença é que o conteúdo será mantido na posição original. 3.8. Utilizando Estilos Rápidos No grupo Estilo, entre outras opções, temos o grupo Estilos de Células que exibe diversas combinações de formatação. Podemos acessar os estilos clicando em: 33 3.9. Criando um novo Estilo Rápido O botão Novo Estilo de Célula lhe permite criar estilos rápidos e aplicá- los na área selecionada. 3.10. Localizando e substituindo dados Imagine que, em uma planilha ou pasta de trabalho, você tivesse que procurar ou substituir um conteúdo que aparecesse 50 vezes, um de cada vez. Isso levaria bastante tempo, não é mesmo? Um recurso que o auxilia a fazer isso é o de localização e substituição rápida de conteúdos. Para isso, utilizaremos os comandos do grupo Edição, na guia Página Inicial. 34 3.11. Localizando Conteúdos Para localizar: 1. Clique sobre Localizar e Selecionar. 2. Clique em Localizar 3. Informe o texto e clique em localizar tudo ou localizar próximo. * Localizar tudo apresenta um grid abaixo com todas as células que contém o recurso. ** Localizar próxima localizar de vagar cada ocorrência. 4. O botão Opções >> apresenta alguns recursos para ajudar na consulta. 3.12. Substituindo Conteúdos A diferença do Localizar para o Substituir é que teremos dois novos botões, Substituir tudo e Substituir. 35 3.13. Ir para um ponto específico Você pode usar o comando Ir para a fim de localizar e selecionar rapidamente todas as células que contêm tipos de dados específicos (como fórmulas) ou somente células que atendam a critérios específicos (como a última célula da planilha que contém dados ou formatação). Podemos acessar no grupo Edição > Ir para. Ou através do atalho F5. 36 44.. TTrraabbaallhhaannddoo ccoomm FFóórrmmuullaass 4.1. O que são Fórmulas? Fórmulas são equações que executam cálculos sobre valores em uma planilha. As fórmulas são inseridas em uma célula e devem iniciar-se com o sinal de igual (=). Podem conter um ou todos os seguintes elementos: Funções – Fórmulas pré-desenvolvidas que assumem um valor ou vários valores, executam uma operação e retornam um valor ou vários valores. Use as funções para simplificar e reduzir fórmulas, especialmente as que executam cálculos longos e complexos. Operadores – Sinais ou símbolos que especificam o tipo de cálculo a ser executado dentro de uma expressão. Existem operadores matemáticos, de comparação, de concatenação e de referência. Referências de célula – Conjunto de coordenadas que a célula abrange em uma planilha. Por exemplo, a referência da célula que aparece na interseção da coluna B e linha 3 é B3. Constantes – Valores que não são calculados e, portanto, não são alterados. Por exemplo, = A5 * 2. O número 2 é constante. Os operadores especificam o tipo de cálculo que pode ser efetuado com os elementos de uma fórmula. Há uma ordem padrão segundo a qual os cálculos ocorrem, mas você pode mudar essa ordem, utilizando parênteses. 4.2. Operadores Matemáticos Para efetuar operações matemáticas básicas, como adição, subtração ou multiplicação, combinar números e produzir resultados numéricos, use esses operadores aritméticos: Símbolo Operação Exemplo + Adição =123+456 - Subtração ou valor negativo =123-456 * Multiplicação =123*456 37 / Divisão =123/456 % Porcentagem =123*10% ^ Exponenciação =123^10 4.3. Operadores de Comparação Você pode comparar dois valores, usando os operadores de comparação. Quando dois valores são comparados, o resultado é um valor lógico, VERDADEIRO ou FALSO. Símbolo Operação Exemplo = Igual a =A1=B1 > Menor que =A1<B1 < Maior que =A1>B1 >= Maior ou igual a =A1>=B1 <= Menor ou igual a =A1<=B1 <> Diferente de =A1<>B1 4.4. Operadores de Concatenação Use o & (e comercial para concatenar uma ou mais sequências de caracteres de texto para produzir um único texto contínuo). Exemplo: 4.5. Operadores de referência Combinam intervalos de células para cálculos. Símbolo Operação Exemplo : Determina intervalo de células entre duas referências. =SOMA(A1:F1) (células de A1 até F1) 38 ; Une diversas referências em uma fórmula. =SOMA(B2;B4;C10) 4.6. Utilizando Fórmulas Podemos trabalhar com fórmulas, como vimos toda fórmula inicia com (=). Após preenchermos a fórmula, podemos copiar para as demais células. Não necessita digitar fórmula por fórmula (quando elas são semelhantes). 4.7. Copiando Fórmulas Como citado anteriormente, não precisamos ficar digitando fórmulas semelhantes, a maneira mais rápida de copiar uma fórmula é posicionar o apontador do mouse no canto inferior direito da célula, até que ele assuma a forma de uma cruz , clique e segure, arraste até a última célula. 39 55.. UUttiilliizzaannddoo FFuunnççõõeess O Excel possui várias funções. As fórmulas pré-gravadas facilitam a execução de procedimentos complexos com números, datas, horários, texto etc. É muito simples, mas, com tantas funções disponíveis, como encontrar a necessária? Algumas funções são mais conhecidas que outras. Por exemplo, Soma, Média, Valor Máximo, Valor Mínimo e Contar células que contêm Número. Essas funções estão disponíveis na guia Página Inicial, no grupo Edição, caixa de opções AutoSoma . Todas as funções têm uma sintaxe a ser obedecida, ou seja, a forma como devem ser digitadas ou inseridas: Sintaxe: =FUNÇÃO(ARGUMENTO1;ARGUMENTO...;ARGUMENTOFINAL), onde: = - Inicialização de todas as funções; FUNÇÃO - Nome da função a ser utilizada. ( ) - Todas as funções devem iniciar-se e finalizar-se com parênteses. Argumentos - Os argumentos indicam os dados a serem utilizados no cálculo da função. ; - Separa cada argumento da função. 5.1. Função SOMA Podemos utilizá-la acessando o AutoSoma ou digitando na barra de cálculos. Sintaxe: =SOMA() Resultado: soma dos argumentos passados. 5.2. Função MÉDIA Podemos utilizá-la acessando o AutoSoma e trocando para média ou digitando na barra de cálculos. Sintaxe: =MÉDIA() Resultado: Calcula a média dos argumentos passados. 40 5.3. Outras Funções Além da Soma e Média podemos “pegar” no AutoSoma as funções: =CONT.NÚM() =MÁXIMO() =MÍNIMO() Além dessas funções, podemos acessar a opção Mais Funções ou o botão Inserir Funções a frente da barra de fórmulas. 5.4. Utilizando Funções mais Específicas Caso você queira usar uma fórmula mais específica e não sabe o nome e seus argumentos, podemos usar o recurso de inserir fórmula . Podemos procurar usando algumaspalavras descritivas para tentarmos localizá-la. Exemplo: Pesquisar sobre somar, clica em ir. 41 Apresenta todas as funções que tem o descritivo da pesquisa. Clicando sobre a função, olhamos uma descrição detalhada, clique em OK. Passaremos os argumentos necessários, veja que no final da caixa mostra o detalhes do argumento. 42 66.. CCllaassssiiffiiccaannddoo ee ffiillttrraannddoo ddaaddooss 6.1. Classificando Dados. Classificar dados em ordem crescente ou decrescente é fácil. Basta você saber por qual dado deseja fazer a classificação. Acessar a guia Dados, grupo Classificar e Filtrar. Recurso Objetivo Classificar uma coluna em ordem crescente. Classificar uma coluna em ordem decrescente. Abrir a caixa de diálogo para classificação detalhada. Classificar por mais de um nível. 6.2. Classificando dados por mais de um nível Recurso Objetivo Adicionar um outro nível de classificação. Excluir um nível. 43 Copiar o nível. Posicionar níveis. (mudar de ordem) Abrir a caixa de diálogo Opções. Caso a primeira linha contenha o cabeçalho dos dados e não uma informação. 6.3. Filtrando Dados Quando se trabalha com planilhas pequenas, pode ser fácil visualizar os dados. Mas imagine uma planilha com 50.000 produtos. Visualizar todos os itens que custem menos que R$ 8,00 ou todos que tenham estoque entre 10 e 20 unidades etc. pode tornar-se muito difícil. Para situações como essas, o Excel conta com um recurso que permite filtrar os dados de acordo com nossas necessidades. Esse recurso chama-se Filtro. Para usarmos, selecione a linha que queira aplicar o filtro. Clique na Guia Dados, Filtro. 44 O recurso filtro será ativado e aparecerá umas setas em cada coluna, além de classificações, ao clicar na seta (filtro) podemos executar algumas operações. 45 77.. EElleemmeennttooss GGrrááffiiccooss Embora o Excel 2010 não tenha como função principal a utilização de efeitos gráficos, é possível inseri-los nas planilhas. Há dois tipos básicos de elementos gráficos que podem ser usados para aprimorar as planilhas: imagens e objetos de desenho. Imagens são elementos gráficos gerados por softwares que não fazem parte do pacote Office. Por exemplo, fotos digitalizadas e imagens de sites. Objetos de desenho são elementos gráficos gerados no pacote Office. Como exemplo, podemos citar formas e gráficos. Na guia Inserir estão os grupos disponíveis para você incluir esses e outros itens na sua planilha, como cabeçalhos, rodapés, caixas de texto, WordArt, símbolos etc. 7.1. Inserindo imagens As imagens podem ser inseridas ou copiadas de diversas fontes. As mais utilizadas são as digitalizadas e de sites. 7.2. Editando imagens Por padrão, o Excel incorpora imagens em um documento de acordo com o arquivo original. Nas versões anteriores, trabalhar com imagens era uma tarefa complicada, pois não existia a possibilidade de fazer alterações de cores e, muito menos, de colocar efeitos para deixá-las mais atraentes. Essa tarefa se tornou bem mais simples a partir da versão Excel 2007, com o uso das opções da guia Formatar. 46 Quando uma imagem é selecionada, a guia contextual Ferramentas de Imagem aparece na barra de título, e a guia Formatar se abre na Faixa de Opções. 7.3. Inserindo clip-art Clip-arts são arquivos de mídia como imagens, formas, sons, filmes, desenhos, fotos etc., armazenados em uma biblioteca do Microsoft Office. O uso desses elementos é público, porém eles não podem ser comercializados. Por isso, é importante sempre atentar aos direitos autorais. Para inserir uma imagem, utilizaremos a guia Inserir, no grupo Ilustrações, e o botão Clip-art. Esse botão abre uma guia lateral apresentando as operações. 47 7.4. Inserindo Formas Formas são elementos que você pode inserir e que ajudam a organizar as informações. Entre elas, encontramos as seguintes: Formas usadas Recentemente, Linhas, Retângulos, Formas básicas, Setas largas, Formas de Equação, Fluxogramas, Textos Explicativos, Estrelas e faixas. Arrisco-me a dizer que é o recurso de ilustração mais utilizado. 48 7.5. Editando formas Como vimos anteriormente, a cada tipo de objeto gráfico utilizado, a guia Formatar disponibiliza grupos e opções diferentes. 7.6. Inserindo SmartArt Um elemento gráfico SmartArt é uma representação visual de informações e ideias. É possível escolher entre diversos layouts para comunicar a mensagem de forma rápida, fácil e efetiva. A maioria das pessoas elabora documentos que contêm apenas texto, embora ilustrações e gráficos possam melhorar o entendimento das informações a serem transmitidas. Criar ilustrações com qualidade pode ser difícil, principalmente se você não for designer. Com o SmartArt é possível criá-las mais facilmente. Dessa forma, você poderá dispor de seu tempo para concentrar-se no conteúdo. 49 7.7. Inserindo WordArt WordArt é uma galeria de estilos de texto que podem ser adicionados à planilha para criar efeitos decorativos. Também é possível converter um texto existente em WordArt. 50 88.. IImmpprriimmiinnddoo ppllaanniillhhaass Como vimos anteriormente, ao clicar na guia Arquivo e no botão Imprimir, teremos algumas opções de impressão. 8.1. Visualização de Impressão Antes de imprimir qualquer documento, é interessante ter uma visão de como ele ficará impresso. Além de permitir isso, a opção Visualização de impressão possibilita a execução de alguns ajustes. Visualize informações iniciais na página 15 até 17. No grupo Imprimir, temos o botão Imprimir, que tem a mesma função mostrada na Figura 31. Temos também o botão Configurar Página, que abre uma caixa de diálogo com várias guias. A primeira refere-se a configurações de páginas. 51 A segunda refere-se às margens. . A terceira refere-se ao cabeçalho/rodapé. 52 A quarta e última refere-se à planilha. 53 99.. VVeerriiffiiccaannddoo OOrrttooggrraaffiiaa Assim que uma planilha é finalizada, é essencial fazer uma boa revisão para analisar atentamente tudo o que foi feito. Para isso, pode-se utilizar a guia Revisão, composta pelos seguintes grupos: Revisão de Texto – Permite verificar erros ortográficos e gramaticais, pesquisar em dicionários, traduzir o texto para outros idiomas e ainda contar as palavras do documento. Idioma – Permite a tradução da planilha para outro idioma de acordo com o tradutor Microsoft Translator. Comentários – Permite inserir informações na planilha, que podem ou não ser impressas, com o objetivo de esclarecer possíveis dúvidas quanto ao conteúdo. Alterações – Permite trabalhar com opções de proteção e compartilhamento, tanto de planilha quanto da pasta de trabalho. 9.1. Corrigindo ortografia Por padrão, o Excel não verifica a ortografia automaticamente, enquanto o conteúdo das células é digitado. Por essa razão, é bom fazer uma revisão final antes de entregar a planilha a seu destinatário. Porém, nem sempre o que o Excel identifica como erro ortográfico está realmente errado. Assim, você pode aceitar ou não a mudança sugerida. Acesse o recurso A caixa de diálogo será mostrada, apresentando o erros encontrado e suas possíveis soluções. 54 A palavra que o Excel entende como grafada erroneamente aparecerá destacada na caixa de texto Não consta no dicionário e, na caixa Sugestões, serão apresentadas várias opções para corrigi-la. Escolha a que melhor se adapta ao conteúdo e clique em Alterar. Você ainda poderá clicar nos seguintes botões: Recurso Função Ignorar uma vez Ignorar a sugestão de mudançapara a primeira ocorrência localizada. Ignorar tudo Ignorar a sugestão de mudança para todas as ocorrências localizadas. Adicionar ao dicionário Adicionar a palavra indicada ao dicionário do Excel, para que seja aceita como correta. Alterar Altera a primeira ocorrência localizada. Alterar todas Alterar todas as ocorrências de uma vez. AutoCorreção O Excel substitui a ocorrência sinalizada automaticamente, utilizando a primeira sugestão. Desfazer a última Desfazer a última correção realizada. O dicionário padrão utilizado para a verificação é o Português (Brasil). Ele poderá, contudo, ser alterado, clicando-se na caixa de opções Idioma do dicionário. Ao término das correções, será exibida a seguinte mensagem: 55 56 1100.. FFóórrmmuullaass ccoomm rreeffeerrêênncciiaass AAbbssoolluuttaass ee MMiissttaass Ao copiar uma fórmula, automaticamente são alteradas as referências contidas nas fórmulas. Isto ocorre quando as referências são relativas. O caractere $ é utilizado para tornar absoluta ou mista a referência de uma célula no momento em que estiver sendo editada uma fórmula. Como exemplo, iremos montar a planilha abaixo: Usaremos fórmulas nas células E2, E3, E4 e D5. A célula D5 contém a soma de D2:D4; =soma(D2:D4) A célula E2 conterá a divisão de D2 por D5; =D2/D5 A célula E3 conterá a divisão de D3 por D5; =D3/D5 A célula E4 conterá a divisão de D4 por D5; =D4/D5 Ou seja, poderemos escrever a primeira fórmula e arrastar até a última correto? Porém ao puxar a referência utilizada é relativa, com isso, ao puxarmos a célula a segunda linha moverá D5 para D6, a terceira D6 para D7 e assim por diante, invalidando assim o cálculo. Para solucionar esse problema, usamos referências absolutas ou mistas, iremos utilizar a referência absoluta para esse caso. A célula E2 conterá a divisão de D2 por D5; =D2/$D$5 A célula E3 conterá a divisão de D3 por D5; =D3/$D$5 A célula E4 conterá a divisão de D4 por D5; =D4/$D$5 Escreveremos a primeira fórmula e puxaremos até a última linha. Para alternar as referências podemos utilizar o atalho F4. Os elementos possíveis são: D5 Relativa Não fixa linha nem coluna $D5 Mista Fixa apenas a coluna, permitindo a variação da linha D$5 Mista Fixa apenas a linha, permitindo a variação da coluna $D$5 Absoluta Fixa linha e coluna 57 1111.. MMúúllttiippllaass PPllaanniillhhaass ee MMúúllttiippllaass PPaassttaass 11.1. Múltiplas Planilhas É possível fazermos vinculações entre planilhas quando necessitamos buscar dados em outra. Por exemplo, caso necessitamos pegar os orçamentos de cada fornecedor e colocarmos uma coluna para cada item, considerando que sabemos aonde a informação está na planilha temos: - A estrutura padrão do FORNECEDOR é: - A estrutura padrão do RESUMO é: No resumo, teremos que vincular o total de cada fornecedor/produto. Porém a cotação pode mudar, e necessitamos fazer isso novamente toda vez que ocorrer a mudança. Com isso usamos vinculações, vamos ao trabalho! Planilha RESUMO, célula C3 conterá =FORNECEDOR1!D2 onde: FORNECEDOR1 é o nome da planilha; D2 é a célula a ser vinculada; ! é o delimitador entre planilha e célula; Puxe a fórmula até a célula C5. Faça o mesmo para FORNECEDOR2 e FORNECEDOR3. 58 11.2. Múltiplas Pastas Podemos necessitar vincular uma informação que esteja em outra pasta, imagine assim como o exemplo anterior que temos três fornecedores porém cada fornecedor tem uma planilha diferente. Cada pasta de fornecedor (fornecedor1.xlsx) terá a planilha COTACAO com o modelo: Para vincular a pata usamos a fórmula: =[fonecedor1.xlsx]COTACAO!D2 onde: COTACAO é o nome da planilha; D2 é a célula a ser vinculada; ! é o delimitador entre planilha e célula; fornecedor1.xlsx é a pasta de trabalho referenciada; [] é o delimitador de nova pasta de trabalho; Caso o nome da planilha ou da pasta tenha espaços ou caracteres especiais, usamos o delimitador ‘ para assegurar a correção dos dados ficando: =‘[fonecedor1.xlsx]COTACAO’!D2 59 1122.. FFuunnççõõeess ddee ddaattaa ee hhoorraa 12.1. Entendendo a Data Alguma vez você já deve ter se deparado com a seguinte situação: “O primeiro pagamento será daqui a 45 dias. Como calcular a data do vencimento?”. No Excel 2010, a resolução é muito simples, pois basta somarmos à data atual, o valor 45. Isso é possível, pois para cada data inserida em uma planilha, um número serial é atribuído a ela. Por exemplo, a data 23/07/2015 é equivalente ao número serial 42208. Para visualizar esse número, altere o formato da data para Geral. A contagem inicia na data 01/01/1900, que corresponde ao número serial 1, e a data limite é 31/12/9999, cujo número serial é 2958465. A data pode ser representada 23/07/2015 ou 23-07-2015. 12.1.1. Manipulação de Data Para resolvermos o caso de mais 45 dias usaremos a seguinte planilha: A fórmula da célula B3 será =B1+B2 Para adição e subtração de datas podemos uma apenas + e – diretamente na célula que gostaríamos calcular. Caso não tenhamos a célula de origem, podemos fazer o cálculo direto utilizando “” (aspas). ="23/07/2015"+B2 12.1.2. Função ABS() Suponhamos que necessitamos saber a diferença entre duas datas, podemos utilizar a subtração, porém caso o resultado seja negativo, teremos o seguinte retorno: 60 Isso ocorre porque não existe data negativa. O resultado dessa operação será -10949. Para garantirmos que o resultado seja sempre positivo, podemos usar a fórmula =ABS() que nos retorna o valor absoluto de um número, ou seja, o número sem sinal. =ABS(B1-B2) 12.1.3. Função HOJE() Essa função retorna a data do dia, configurada no computador local ou no servidor. Essa função pode ser empregada quando queremos calcular a diferença entre a data atual e outra data. Pode também ser utilizada nos cálculos de datas futuras, somando-se a ela o número de dias desejados. Por exemplo, para criar um calendário de pagamento com prazos de 15, 30, 45, 60 e 90 dias: 12.1.4. Função Dia.da.Semana() Essa função retorna o dia da semana correspondente a uma data. O dia é dado como um inteiro, variando, por padrão, de 1 (domingo) a 7 (sábado). Sintaxe: =Dia.da.semana(núm_série; retornar_tipo), onde: núm_série – Célula ou fórmula que contém a data do dia que se está buscando encontrar. 61 retornar_tipo – É um número que determina o tipo do valor retornado. retornar_tipo Número retornado 1 ou omitido Números 1 (domingo) a 7 (sábado), como nas versões anteriores do Microsoft Excel. 2 Números 1 (segunda-feira) a 7 (domingo). 3 Números 0 (segunda-feira) a 6 (domingo). 12.1.4.1. Funções dia(), mês(), ano() dia() retorna o dia da célula, Sintaxe: =dia(núm_série) mês() retorna o mês da célula, Sintaxe: =mês(núm_série) ano() retorna o ano da célula, Sintaxe: =ano(núm_série) onde, núm_série – Célula ou fórmula da qual se quer extrair a hora. Não se esqueça de que, de acordo com o formato da célula, esses valores podem aparecer de diferentes formas. 12.2. Entendendo a Hora Assim como as datas, as horas são representadas por um número serial. O Excel 2010 armazena a hora como sendo uma fração do dia, isto é, um número entre 0 (zero) e 1 (um) para horas entre zero e 24. Esse número refere-se ao horário dividido por 24. Por exemplo, 6 horas são 0,25 (6 dividido por 24). Portanto, 6 horas são um quarto do dia. A hora pode ser escrita utilizando : (dois pontos) para dividir hora, minuto, segundo e , (vírgula) para os centésimos. 62 12.2.1. Manipulação de Hora Dado o exemplo: Iremos manipular algumas situações. 12.2.2. Função agora() Sintaxe: =agora(). Similar a função hoje(), essa função retorna informação atual do sistema, se uma célula estiverformatada para horas, irá retornar a hora atual. 12.2.3. Funções hora(), minuto(), segundo() hora() retorna a hora da célula, Sintaxe: =hora(núm_série) minuto() retorna o minuto da célula, Sintaxe: =minuto(núm_série) segundo() retorna o segundo da célula, Sintaxe: =segundo(núm_série) onde, núm_série – Célula ou fórmula da qual se quer extrair a hora. Não se esqueça de que, de acordo com o formato da célula, esses valores podem aparecer de diferentes formas. 12.2.4. Fazendo cálculo com horas Sabendo como as horas são interpretadas, fica fácil entender como são feitos os cálculos com elas. Para saber o tempo decorrido entre um horário e outro, é só subtrair um de outro. Se estiver registrado 07:28 na célula B1 e 11:30 na célula B2, a fórmula =B1-B2 retornará 4:02, que é a diferença entre os horários. Assim como as 63 datas, as horas devem ser colocadas entre aspas, se forem digitadas diretamente nas fórmulas 12.3. Fazendo cálculo com data e hora Para representar data e hora em uma mesma célula, temos que utilizar o espaço entre a data e hora. Como já sabemos como o Excel interpreta datas e horas, vejamos o procedimento para trabalhar com ambas em uma mesma fórmula. Vamos supor que, a serviço, você vá viajar de carro para a Bahia, com saída prevista para o dia 31/07/2015, às 8:00, e chegada para o dia 03/08/2015, às 15:00. Para calcular o tempo de viagem, é preciso subtrair a data da partida da data da chegada e somar a diferença entre os horários. Para isso, usaremos a seguinte planilha: Na célula E3 usaremos a fórmula =(C3-A3)+(D3-B3) que nos retornará 3,291666667 Podemos mudar a apresentação do cálculo para o tipo [h]:mm que representa total de horas e minutos gastos. 64 1133.. BBaannccoo ddee DDaaddooss No trabalho com planilhas é comum termos que saber qual é o valor total de um ou mais itens de uma lista ou banco de dados. Você já se deparou com uma situação em que precisava saber qual era a quantidade vendida ou comprada de um produto em determinado período, ou qual foi o maior valor pago por uma mercadoria específica em um ano, mês ou semana, ou em qualquer outro período? Situações como essas e muitas outras envolvendo banco de dados são frequentes no dia a dia do desenvolvimento de planilhas. Para resolver questões desse gênero, existem as funções de Banco de Dados. Para utilizar uma dessas funções, é necessário haver uma lista ou banco de dados e uma região de critérios, que podem estar na mesma planilha ou em outras planilhas e até mesmo em pastas (arquivos) diferentes. As funções de Banco de Dados BDMÉDIA, BDCONTAR, BDMÍN, BDMÁX, BDSOMA e BDVARP possuem os mesmos argumentos, descritos a seguir: BANCO DE DADOS – É o intervalo de células da lista ou do banco de dados. Um banco de dados é uma lista de dados relacionados, cujas linhas de informações relacionadas são os registros e as colunas de dados são os campos. A primeira linha da lista contém os rótulos de cada coluna. CAMPO – Indica a coluna que será usada na função. O campo pode ser estabelecido como texto com o rótulo da coluna entre aspas, como “Idade” ou “Rendimento”, ou como um número (sem aspas) que represente a posição da coluna dentro da lista: 1 para a primeira coluna, 2 para a segunda coluna e assim por diante. CRITÉRIOS – Intervalo de células que contém as condições especificadas. Você pode usar qualquer intervalo para o argumento de critérios, desde que ele inclua pelo menos um rótulo de coluna e pelo menos uma célula abaixo do rótulo de coluna para especificar uma condição para a coluna. Usaremos como modelo a planilha abaixo: 65 Para todos os casos usaremos o critério: 13.1. Função BDMÉDIA() Essa função calcula a MÉDIA dos valores em um campo (coluna) de registros em uma lista ou banco de dados que atendam às condições especificadas. A sintaxe a seguinte: =BDMÉDIA(bancodados;campo;critérios) Iremos extrair da base a média de idade do sexo feminino e o médico seja pediatra. =BDMÉDIA(A1:E8;3;G1:H2) Onde, Banco de dados (A1:E8) é a base de consulta; Campo (3) é a posição da coluna que gostaríamos de fazer a média; Critérios (G1:H2) é a combinação de consulta dos dados; A resposta final será: 8,5 para os critérios que escolhemos de exemplo. 66 13.2. Função BDCONTAR() Essa função conta os valores em um campo (coluna) de registros em uma lista ou banco de dados que atendam às condições especificadas. A sintaxe a seguinte: =BDCONTAR(bancodados;campo;critérios) Iremos extrair da base a contagem de pacientes do sexo feminino e o médico seja pediatra. =BDCONTAR(A1:E8;3;G1:H2) Onde, Banco de dados (A1:E8) é a base de consulta; Campo (3) é a posição da coluna que gostaríamos de fazer a média; Critérios (G1:H2) é a combinação de consulta dos dados; A resposta final será: 2 para os critérios que escolhemos de exemplo. 13.3. Função BDMIN() Essa função retorna o valor mínimo em um campo (coluna) de registros em uma lista ou banco de dados que atendam às condições especificadas. A sintaxe a seguinte: =BDMIN(bancodados;campo;critérios) Iremos extrair da base a contagem de pacientes do sexo feminino e o médico seja pediatra. =BDMIN(A1:E8;3;G1:H2) Onde, Banco de dados (A1:E8) é a base de consulta; Campo (3) é a posição da coluna que gostaríamos de fazer a média; Critérios (G1:H2) é a combinação de consulta dos dados; A resposta final será: 8 para os critérios que escolhemos de exemplo. 67 13.4. Função BDMÁX() Essa função retorna o valor máximo em um campo (coluna) de registros em uma lista ou banco de dados que atendam às condições especificadas. A sintaxe a seguinte: =BDMÁX(bancodados;campo;critérios) Iremos extrair da base a contagem de pacientes do sexo feminino e o médico seja pediatra. =BDMÁX(A1:E8;3;G1:H2) Onde, Banco de dados (A1:E8) é a base de consulta; Campo (3) é a posição da coluna que gostaríamos de fazer a média; Critérios (G1:H2) é a combinação de consulta dos dados; A resposta final será: 9 para os critérios que escolhemos de exemplo. 13.5. Função BDSOMA() Essa função retorna o valor máximo em um campo (coluna) de registros em uma lista ou banco de dados que atendam às condições especificadas. A sintaxe a seguinte: =BDSOMA(bancodados;campo;critérios) Iremos extrair da base a contagem de pacientes do sexo feminino e o médico seja pediatra. =BDSOMA(A1:E8;3;G1:H2) Onde, Banco de dados (A1:E8) é a base de consulta; Campo (3) é a posição da coluna que gostaríamos de fazer a média; 68 Critérios (G1:H2) é a combinação de consulta dos dados; A resposta final será: 17 para os critérios que escolhemos de exemplo. 69 1144.. GGrrááffiiccooss 14.1. O que são gráficos? Podemos dizer que os gráficos representam os dados das planilhas por meio de imagens, tornando-os visuais. Em vez de analisar colunas de números em uma planilha, é possível entender, instantaneamente, o que os dados significam. 14.2. Tipos de gráficos Você deve selecionar o tipo de gráfico que vai tornar seus dados mais claros, enfáticos e informativos. O Excel oferece uma ampla variedade de tipos de gráficos e métodos simples para selecioná-los e visualizá-los. Um gráfico transmite sua mensagem rapidamente. Com um gráfico, é possível apresentar os dados de uma planilha, evidenciando comparações, padrões e tendências. Por exemplo, é possível mostrar, instantaneamente, se as vendas caíram ou aumentaram de um trimestre para outro. Para acessarmos esse recurso vamos em Inserir > Gráficos. 14.2.1. Gráfico de Colunas São úteis para ilustrar comparações entre itens. Em gráficos de colunas, geralmente, as categorias são organizadas ao longo do eixo horizontal (X), e os valores ao longo do eixo vertical (Y). 14.2.2. Gráfico de LinhasPodem exibir dados contínuos ao longo do tempo, definidos em relação a uma escala comum, e que, portanto, são ideais para mostrar tendências a intervalos iguais. Nesse tipo de gráfico, os dados de categorias são distribuídos uniformemente ao longo do eixo horizontal, enquanto os dados de valores são distribuídos igualmente ao longo do eixo vertical. 70 14.2.3. Gráfico de Pizza Exibe o quanto cada valor representa sobre o valor total. São utilizados quando há apenas uma série de dados a serem comparados e todos os valores são positivos. 14.2.4. Gráfico em Barra Ilustram comparações entre itens individuais. Considere a utilização de gráficos de barras, quando os rótulos dos eixos forem longos e para comparar múltiplos valores. 14.2.5. Gráfico de Área Enfatizam a magnitude da mudança no decorrer do tempo e podem ser usados para chamar atenção para o valor total ao longo de uma tendência. Exibindo a soma dos valores, o gráfico de área mostra também a relação das partes com um todo. 14.2.6. Gráfico de Dispersão (XY) Apresentam dois eixos de valores, mostrando um conjunto de dados numéricos ao longo do eixo horizontal (eixo X) e outro ao longo do eixo vertical (eixo Y). Combinam esses valores em pontos de dados únicos e os exibem em intervalos irregulares ou agrupamentos. Comumente, esse tipo de gráfico é usado para exibir e comparar valores numéricos, como dados científicos, estatísticos e de engenharia. 14.2.7. Gráficos de Ações Como o próprio nome sugere, gráficos de ações são usados, mais frequentemente, para ilustrar a flutuação de preços no mercado de Ações. No entanto, também podem ser usados para fins científicos. Por exemplo, você poderia usar um gráfico de ações para indicar a flutuação de temperaturas diárias ou anuais. Para criar esse tipo de gráfico, os dados devem ser organizados em ordem correta. 71 14.2.8. Gráfico de Superfície Gráficos de superfície são úteis quando você deseja encontrar combinações vantajosas entre dois conjuntos de dados. Como em um mapa topográfico, cores e padrões indicam áreas num mesmo intervalo de valores. 14.2.9. Gráficos de Rosca Assim como os gráficos de pizza, os de rosca exibem a relação das partes com o todo, podendo, entretanto, conter mais de uma série de dados. Como não são de fácil leitura, em seu lugar, acabam sendo utilizados os gráficos de colunas ou barras empilhadas. 14.2.10. Gráfico de Bolhas Em gráficos de bolhas, podem ser inseridos dados que se encontram organizados em colunas nas planilhas, de tal forma que valores de X sejam listados na primeira coluna e valores de Y correspondentes sejam listados em colunas adjacentes. Por exemplo, você poderia organizar seus dados. 14.2.11. Gráfico de Radar Gráficos de radar comparam os valores agregados de várias séries de dados. Cada série de dados tem cor ou padrão exclusivo e é representada na legenda do gráfico. É possível inserir uma ou mais séries de dados em um gráfico. 72 1155.. TTaabbeellaa DDiinnââmmiiccaa Um relatório de tabela dinâmica é utilizado para resumir, analisar, explorar e apresentar dados de resumo e para visualizar comparações, padrões e tendências facilmente. O relatório de tabela dinâmica permite tomar decisões corretas sobre dados críticos. 15.1. Criar um relatório de tabela dinâmica Para criar um relatório de tabela dinâmica ou gráfico dinâmico, você deve se conectar à fonte de dados e inserir o local do relatório. Essa fonte não deve conter colunas ou linhas vazias. Por exemplo, linhas ou colunas em branco que são usadas para separar um bloco de dados de outro devem ser removidas. Para entender essa ferramenta, faça o seguinte: 1. Crie duas planilhas, a primeira chamada Dados e a Segunda chamada Relatório. 2. DADOS conterá a estrutura PACIENTE, DATA NASCIMENTO, SEXO, IDADE, MÉDICO 3. Clique sobre qualquer célula preenchida da planilha DADOS; 4. Vá na guia Inserir; 5. No grupo Tabelas, clique em Tabela Dinâmica; 73 6. Clique em Tabela Dinâmica; 7. A caixa de diálogo Criar Tabela Dinâmica é exibida. 8. Escolha os dados que deseja analisar: Caso opte em pegar dados de uma tabela/intervalo, informe o intervalo. Como já estávamos sobre uma célula preenchida o Excel preenche automaticamente o intervalo, confirma e corrija o que for necessário. 9. Escolha onde deseja que o relatório da tabela dinâmica seja colocado: No Planilha ou especificar um existente, caso escolha o existente, entre com o nome da planilha e sua célula correspondente. 10. Clique em OK. A Tabela Dinâmica é criada sobre uma célula. Um relatório é criado vazio na planilha escolhida e a caixa de diálogo Lista de campos da tabela dinâmica é aberta. 74 Agora, você está pronto para criar o relatório de tabela dinâmica. Os campos selecionados para o relatório dependem do que você deseja saber. Precisamos saber 5 princípios básicos: 1- Filtro do Relatório – colocamos os campos referentes aos filtros que gostaríamos de executar; 2- Rótulo de Coluna – colocamos os campos que queremos listar em coluna; 3- Rótulo de Linha – colocamos os campos que queremos listar em linha; 4- Valores – será a informação que queremos analisar. Podemos simplesmente escolher o campo, clicar e levar até o local desejado ou clicar com o botão direito do mouse, e escolher para onde inserir. Com esses dados podemos gerar várias informações diferentes, vamos à alguns exemplos. 75 Exemplo 1: Quantos pacientes temos por sexo? Podemos responder transportando em rótulo de linha ou rótulo de coluna o campo Sexo, além disso, precisamos transportar o campo que necessitamos executar a contagem, por padrão campos com caractere executa a contagem padrão, teremos: Que resultará em: Exemplo 2: Temos pacientes com mesma idade? Em qual categoria? Podemos responder transportando me rótulo de coluna a Idade e rótulo de linha o Médico, além disso, precisamos transportar o campo que necessitamos executar a contagem, pode ser o Idade. Como o campo Idade é número, o Valor padrão é soma, para trocar, clique sobre o campo dentro do núcleo Valores, escolha a opção Configurações do Campo de Valor... 76 Na tela de configurações do Campo de Valor podemos ajustar como o campo será apresentado, o nome e o formato. Escolha Contagem em Resumir campo de valor por. Teremos como resultado a seguinte tabela. 77 15.2. Campo Calculado Imagine que você gostaria de criar uma nova coluna (campo) que contenha um cálculo referente a outros campos, por exemplo ao invés de termos a idade dos pacientes tivéssemos a data de nascimento, e o campo calculado iria calcular com base em uma fórmula a idade, vamos ao exemplo: 1 – Fonte de dados; 2 – Insira uma tabela dinâmica com os dados do item 1; 78 3 – Puxe Paciente para dentro de rótulo de linha; 4 – Sobre a tabela dinâmica criada vá à guia Ferramentas de Tabela Dinâmica > Cálculos > Campos, Itens e Conjuntos > Campo Calculado; 5 – Defina o campo e a fórmula a ser usada, iremos criar o campo Idade_Calc que terá a fórmula: =INT(("23/07/2015"-'Data Nascimento')/365,5); 6 – Como o retorno é inteiro, foi entregue a soma porém o formato ficou como data; 7 – Acesse o item no agrupamento valores e troque a configuração de campo de valor... do camo idade_calc para o formato numero; 79 8 – Clique em OK e veja a alteração realizada na tabela dinâmica; Repare que não podemos jogar em Rótulos ou em Filtros o nosso Campo Calculado. 15.3. Item Calculado Quando necessitamos fazer um agrupamento de itens em um relatório de tabela dinâmica, utilizamos o Item Calculado, para acessar esse recurso vá à guia 80 Ferramentas de Tabela Dinâmica> Cálculos > Campos, Itens e Conjuntos > Item Calculado. Um item entra como um novo “produto” dentro do campo que estamos trabalhando. 81 1166.. GGrrááffiiccoo DDiinnââmmiiccoo A criação de um gráfico dinâmico é similar à criação de uma tabela dinâmica, só precisamos deixar claro que a diferença de processo entre eles é que rótulo de coluna vira Campos de Legenda e rótulos de linha vira campos de eixo. Temos para o exemplo anterior o gráfico: Um gráfico dinâmico tem sempre uma tabela por traz dele. O que temos de novo é a guia Analisar dentro de Ferramentas de Gráfico Dinâmico. Os Botões de campos são os dados que aparecem na plotagem do gráfico dinâmico, onde podemos executar filtro nos eixos e nas legendas. 82 1177.. FFuunnççõõeess ddee PPrrooccuurraa Essa categoria é composta por funções que localizam valores em uma lista ou retornam sua referência. Existem diversas funções nessa categoria. Dentre elas, vamos conhecer algumas de grande utilidade no desenvolvimento de planilhas inteligentes. A seguir, você conhecerá as funções PROCH, PROCV, ÍNDICE e CORRESP. Em muitos casos, elas trabalham em conjunto, proporcionando excelentes resultados. 17.1. Função PROCH() Essa função localiza um valor na linha superior de uma tabela ou matriz de valores e retorna um valor na mesma coluna de uma linha especificada na tabela ou matriz. Use PROCH quando seus valores de comparação estiverem localizados em uma linha ao longo da parte superior de uma tabela de dados e você quiser observar um número específico de linhas mais abaixo. A sintaxe é a seguinte: =PROCH(valor_procurado;matriz_tabela;núm_índice_lin;[procurar_interv alo]) onde: Valor_procurado – É o valor a ser localizado na primeira linha da tabela. Pode ser um valor, uma referência ou uma sequência de caracteres de texto. Matriz_tabela – É uma tabela de informações onde os dados devem ser procurados. Use uma referência para um intervalo ou um nome de intervalo. » Os valores na primeira linha de matriz_tabela podem ser texto, números ou valores lógicos. » Se procurar_intervalo for VERDADEIRO, os valores na primeira linha de matriz_tabela deverão ser colocados em ordem ascendente: ...-2, -1, 0, 1, 2,... , A-Z, FALSO, VERDADEIRO. Em caso contrário, PROCH pode não retornar o valor correto. Se procurar_intervalo for FALSO, a matriz_tabela não precisará ser ordenada. » Textos em maiúsculas e minúsculas são equivalentes. 83 » Classifique os valores em ordem crescente, da esquerda para a direita. Para obter mais informações. Núm_índice_lin – É o número da linha em matriz_tabela de onde o valor correspondente deve ser retirado. Um núm_índice_lin equivalente a 1 retorna o valor da primeira linha na matriz_tabela, um núm_índice_lin equivalente a 2 retorna o valor da segunda linha na matriz_tabela e assim por diante. » Se núm_índice_lin for menor do que 1, PROCH retornará o valor de erro #VALOR!; » Se núm_índice_lin for maior do que o número de linhas na matriz_tabela, PROCH retornará o valor de erro #REF!. Procurar_intervalo – É um valor lógico que específica se você quer que PROCH localize uma correspondência exata ou aproximada. » Se VERDADEIRO (1) ou omitido, uma correspondência aproximada é retornada. Em outras palavras, se uma correspondência exata não for localizada, o valor maior mais próximo que seja menor que o valor_procurado será retornado. » Se FALSO (0), PROCH encontrará uma correspondência exata. Se nenhuma correspondência for localizada, o valor de erro #N/D será retornado. No exemplo a seguir, temos uma tabela de consultas que contém o tipo de médico (Linha 2). Na segunda tabela, o tipo de médico (D6) é definido por meio da idade do paciente (C6) que foi pesquisada na primeira tabela. 84 Iremos calcular a idade, calculando a diferença da data atual com a data de nascimento, com o valor adquirido, iremos dividir por 365,5 e pegar a parte inteira. Ou seja, Para C6 =INT(($B$12-B6)/365,5) Para D6 =PROCH(C6;$B$1:$D$2;2;1) 17.2. Função PROCV() Essa função localiza um valor na primeira coluna de uma matriz de tabela e retorna um valor na mesma linha de outra coluna na matriz da tabela. O V em PROCV significa vertical. Use PROCV em vez de PROCH, quando os valores da comparação estiverem posicionados em uma coluna à esquerda ou à direita dos dados que você deseja procurar. A sintaxe é a seguinte: =PROCV(valor_procurado;matriz_tabela;núm_índice_col;[procurar_inter valo]) Onde: Valor_procurado – É o valor a ser localizado na primeira linha da tabela. Pode ser um valor, uma referência ou uma sequência de caracteres de texto. Matriz_tabela – É uma tabela de informações onde os dados devem ser procurados. Use uma referência para um intervalo ou um nome de intervalo. » Os valores na primeira coluna de matriz_tabela podem ser texto, números ou valores lógicos. » Se procurar_intervalo for VERDADEIRO, os valores na primeira coluna de matriz_tabela deverão ser colocados em ordem ascendente: ...-2, -1, 0, 1, 2,... , A-Z, FALSO, VERDADEIRO. Em caso contrário, PROCV pode não retornar o valor correto. Se procurar_intervalo for FALSO, a matriz_tabela não precisará ser ordenada. » Textos em maiúsculas e minúsculas são equivalentes. Núm_índice_col – É o número da linha em matriz_tabela de onde o valor correspondente deve ser retirado. Um núm_índice_col equivalente a 1 retorna o valor da primeira coluna na matriz_tabela, um núm_ índice _lin equivalente a 2 retorna o valor da segunda coluna na matriz_tabela e assim por diante. 85 » Se núm_índice_col for menor do que 1, PROCV retornará o valor de erro #VALOR!; » Se núm_índice_col for maior do que o número de linhas na matriz_tabela, PROCV retornará o valor de erro #REF!. Procurar_intervalo – É um valor lógico que específica se você quer que PROCV localize uma correspondência exata ou aproximada. » Se VERDADEIRO (1) ou omitido, uma correspondência aproximada é retornada. Em outras palavras, se uma correspondência exata não for localizada, o valor maior mais próximo que seja menor que o valor_procurado será retornado. » Se FALSO (0), PROCV encontrará uma correspondência exata. Se nenhuma correspondência for localizada, o valor de erro #N/D será retornado. No exemplo a seguir, teremos que retornar o endereço de uma pessoa, com base no que for escolhido na célula C14. Para C15 =PROCV(C14;$A$6:$D$10;2;0) Para C16 =PROCV(C14;$A$6:$D$10;3;0) Para C17 =PROCV(C14;$A$6:$D$10;4;0) Desta vez utilizamos o procurar_intervalo como 0 pois precisamos do retorno exato da função de procura. 86 17.3. Função INDICE() Essa função retorna um valor ou a referência para um valor de dentro de uma tabela ou intervalo. Há duas formas da função ÍNDICE: matricial e de referência. 17.3.1. Forma matricial Retorna o valor de um elemento em uma tabela ou matriz, selecionadas pelos índices de número de linha e coluna. Use a forma de matriz se o primeiro argumento de ÍNDICE for uma constante de matriz. A sintaxe é a seguinte: =ÍNDICE(matriz;núm_linha;núm_coluna), onde em que: Matriz – É um intervalo de células ou uma constante de matriz. » Se a matriz contiver apenas uma linha ou coluna, o argumento núm_linha ou núm_coluna correspondente será opcional. » Se a matriz tiver mais de uma linha e mais de uma coluna e apenas núm_linha ou núm_coluna for usado, ÍNDICE retornará uma matriz referente à linha ou coluna inteira da matriz. Núm_linha – Seleciona a linha na matriz a partir da qual um valor deverá ser retornado. Se núm_linha for omitido, núm_coluna será obrigatório. Núm_coluna – Seleciona a coluna na matriz a partir da qual um valor deverá ser retornado. Se núm_coluna for omitido, núm_linha será obrigatório. 17.3.2. Forma de Referência Retorna a referência
Compartilhar