Autoria QI Escolas e Faculdades Autor Thiago Espindola Cury Diagramação Thiago Espindola Cury Todos os direitos reservados QI Editora Ltda Av. Alberto Bins, 320 Centro Porto Alegre-RS CEP 90030-140 Sumário AULA 1......................................................... 7 INTRODUÇÃO .................................................... 7 CONHECENDO A TELA DO EXCEL .............. 8 FORMATAÇÃO DE CÉLULAS ........................ 19 ATIVIDADES ...................................................... 31 ANOTAÇÕES..................................................... 33 AULA 2....................................................... 34 INICIANDO AS FÓRMULAS ........................ 34 MATEMÁTICA E USO DE PARÊNTESES .. 36 CRIANDO FÓRMULAS SIMPLES................ 36 ATIVIDADES ...................................................... 39 ANOTAÇÕES..................................................... 40 AULA 3....................................................... 41 O QUE SÃO FUNÇÕES? ................................ 41 FUNÇÃO SOMA .............................................. 41 FUNÇÃO MÉDIA.............................................. 44 ATIVIDADES ...................................................... 46 ANOTAÇÕES..................................................... 47 AULA 4....................................................... 48 FUNÇÃO MÁXIMO ......................................... 48 FUNÇÃO MÍNIMO .......................................... 50 ATIVIDADES ...................................................... 52 ANOTAÇÕES..................................................... 53 AULA 5....................................................... 54 FUNÇÃO CONT.SE ......................................... 54 FUNÇÃO SOMASE.......................................... 56 ATIVIDADES ...................................................... 58 ANOTAÇÕES..................................................... 59 AULA 6....................................................... 60 FUNÇÃO SE ....................................................... 60 ATIVIDADES ...................................................... 63 ANOTAÇÕES..................................................... 64 AULA 7 ...................................................... 65 FUNÇÃO SE(SE()) ...............................................65 ATIVIDADES ......................................................68 ANOTAÇÕES .....................................................69 AULA 8 ...................................................... 70 FUNÇÕES PROCV E PROCH........................70 ATIVIDADES ......................................................73 ANOTAÇÕES .....................................................74 AULA 9 ...................................................... 75 FILTRO .................................................................75 FORMATAÇÃO CONDICIONAL .................79 VALIDAÇÃO DE CÉLULAS ..............................83 ATIVIDADES ......................................................89 ANOTAÇÕES .....................................................90 AULA 10..................................................... 91 GRÁFICOS ..........................................................91 ATIVIDADES ......................................................98 ANOTAÇÕES .....................................................99 AULA 11................................................... 100 REVISÃO .......................................................... 100 ANOTAÇÕES .................................................. 102 AULA 12................................................... 103 AVALIAÇÃO FINAL ...................................... 103 ANOTAÇÕES .................................................. 104 Profissional QI O QUE VAMOS APRENDER: Introdução Conhecendo a tela do Excel Formatação de células AULA 1 INTRODUÇÃO O MICROSOFT EXCEL 2013 é um dos programas que compõem o pacote Office 2013 da Microsoft. É utilizado para criação e edição de planilhas eletrônicas. Com ele, podemos criar planilhas dinâmicas com os mais diversos recursos, como fórmulas que vão das mais simples até as mais complexas utilizando a matemática financeira. Podemos criar planilhas para calcular o valor das horas trabalhadas por um funcionário, bem como seu salário, comissão e férias, por exemplo. Iniciando o Microsoft Excel MICROSOFT EXCEL Editor de planilhas Para iniciar o Excel devemos seguir os seguintes passos: 1. Clicar no Menu Iniciar localizado na barra de tarefas do Windows; 2. Apontar para opção “Todos os Programas”; 3. O sistema exibirá um menu; 4. Clicar na opção “Microsoft Office 2013”; 5. O sistema abrirá um novo menu; 6. Clicar na opção “Excel 2013”. Microsoft Excel 2013 7 Profissional QI CONHECENDO A TELA DO EXCEL Menu Arquivo Ele substitui o botão Office das versões anteriores. Ao clicar no Menu Arquivo serão exibidas as seguintes opções: Informações, Novo, Abrir, Salvar, Salvar como, Imprimir, Compartilhar, Exportar, Fechar, Conta e Opções. Microsoft Excel 2013 8 Profissional QI Barra de Ferramentas de Acesso Rápido É uma barra de ferramentas que pode ser personalizada conforme nossas preferências, adicionando ou removendo botões. Como representado na figura, encontramos três botões. Para personalizá-la devemos seguir os seguintes passos: 1. Clicar com o botão esquerdo do mouse sobre a seta para baixo dessa barra; 2. Serão apresentadas opções para adição ou remoção de comandos já estabelecidos pelo programa; 3. Clicar na opção desejada. Vamos praticar Vamos modificar a barra de acesso rápido: Inserir duas opções: “Novo” e “Abrir”; o Clicar sobre a seta da barra de acesso rápido; o Escolher a opção “Novo”, depois, a opção “Abrir”; Retirar a opção “Desfazer”. o Para retirá-la é preciso clicar na seta da barra de acesso rápido e Barra de Título. Microsoft Excel 2013 9 Profissional QI Barra de Título Exibe o nome do programa (Excel) e também o nome do documento ativo, nesse caso pasta 1. Guia/Faixa de opções Utilizada para localizar rapidamente os comandos necessários para executar uma tarefa. Os comandos são organizados em grupos, reunidos por guias. Para melhorar a organização, algumas guias são exibidas somente quando necessário. Exemplo Guia Página Inicial Exemplo Grupo Fonte Exemplo Seta expansora do Grupo Fonte Microsoft Excel 2013 10 Profissional QI O que é uma Planilha? Planilha é o mesmo que tabela. Por isso, a área de edição do Excel é quadriculada, formada por linhas e colunas que dão origem às células. Uma planilha criada no Excel é chamada de dinâmica, pois ao efetuarmos alterações nos dados, os novos valores serão calculados, atualizando os dados da planilha. Numeração de linhas Sempre teremos no Excel essa numeração à esquerda da tela que começa no número 1 e vai até o número 1.048.576. Ela serve para indicar em qual linha estamos. Indicação de colunas Assim como a numeração de linhas, temos também a indicação de colunas no Excel, com o seguinte detalhe: essa indicação ocorre através de letras, começando na letra “A” até a combinação “XFD”, somando um total de 16.384 colunas. Essa barra estará sempre na parte superior da planilha. Microsoft Excel 2013 11 Profissional QI Célula A célula é a resultado do cruzamento entre uma coluna com uma linha. Sempre que temos essa união formamos uma célula no Excel. A indicação de uma célula se dará através da referência de uma coluna com uma linha. Microsoft Excel 2013 12 Profissional QI Exemplo A1 Essa célula é resultado do encontro da coluna A com a linha de número 1. Endereço de célula Neste local temos o endereço da célula que está selecionada no atual momento. Se for a seleção de um conjunto de células será mostrado somente o endereço da primeira célula. Barra de Fórmulas Nessa barra visualizamos as fórmulas de alguma célula selecionada. Podemos criar fórmulas digitando diretamente nessa barra ou editando uma fórmula através dela. Microsoft Excel 2013 13 Profissional QI Guia de Planilhas Em um arquivo do Excel podemos ter mais de uma planilha. Isso pode ser visto na Guia de Planilhas. Por padrão, quando abrimos um arquivo do Excel ele vem com apenas uma planilha chamada “Plan1”. Podemos inserir ou renomear a planilha. Após inserir mais de uma planilha a opção excluir será habilitada. Para realizar qualquer uma destas alterações devemos seguir os seguintes passos: 1. Clicar com o botão direito do mouse sobre guia da planilha(Plan1); 2. O programa abrirá as opções conforme a figura; 3. Clicar sobre a opção desejada. DICA Também podemos clicar no “botão mais” localizado a direita da Plan1. Esse botão adicionará uma nova planilha. Microsoft Excel 2013 14 Profissional QI Nível de ZOM Basta clicar e arrastar a seta para ajustar o nível de zoom, conforme sua preferência. Esta opção pode ser encontrada na barra de status, localizada na parte inferior da tela do excel, no canto direito. ZOOM Ampliação de imagem ou texto. Conhecendo as Ferramentas de Formatação FORMATAÇÃO Configuração de estilos de um texto, planilha ou slides. Durante a formatação podemos configurar o tipo de letra, cores, etc. Para realizar qualquer tipo de FORMATAÇÃO no texto é necessário selecioná-lo, ou seja, marcá-lo primeiro. Para aplicar uma formatação no Excel devemos seguir os seguintes passos: 1. Selecionar as células que desejamos modificar; 2. Após selecionarmos as células devemos clicar sobre o botão da opção desejada, na Guia “Página Inicial". Microsoft Excel 2013 15 Profissional QI Fonte Altera o tipo da letra. Podemos escolher entre diversos formatos de letras. Tamanho da fonte Altera o tamanho da fonte, ou seja, diminui ou aumenta o tamanho da fonte. Negrito Destaca a palavra, deixando o formato das letras maior, mais largo. Exemplo: Excel Itálico Destaca a palavra deixando-a um pouco mais caída para a direita. Aplica o formato itálico ao texto selecionado. Exemplo: Excel Sublinhado Sublinha o texto selecionado. Você também pode escolher o estilo da linha e a cor, clicando na seta para baixo. Exemplo: Excel Cor da fonte Altera cor da letra. Microsoft Excel 2013 16 Profissional QI Alinhamento de texto à Esquerda: Alinha o texto selecionado à esquerda da célula. Exemplo: Alinhamento de texto Centralizado: Alinha o texto selecionado no centro da célula. Exemplo: Alinhamento de texto à Direita: Alinha o texto selecionado à direita da célula. Exemplo: DICA Para voltar para a célula A1 de qualquer célula basta pressionar CTRL + HOME. Microsoft Excel 2013 17 Profissional QI Teclas de Atalho para os Menus Teclas de atalho são combinações de teclas usadas para executar determinadas tarefas, como salvar arquivos, fechar e abrir programas, etc. O Excel contém diversas teclas de atalho. Para ativá-las devemos pressionar a tecla ALT que, quando pressionada, mostrará sobre os menus quais são as suas teclas de atalho. Na figura abaixo, temos o exemplo de como fica o Excel depois de pressionada a tecla ALT. Microsoft Excel 2013 18 Profissional QI FORMATAÇÃO DE CÉLULAS Alinhamento de células No Excel encontramos opções de alinhamento do conteúdo das células. Nessas opções é possível fazer alinhamentos verticais, diagonais e horizontais nas células selecionadas. O alinhamento em uma tabela cria um diferencial deixando a tabela em destaque em determinadas células. Para alinhar o conteúdo das células devemos seguir os seguintes passos: 1. Clicar na guia “Página Inicial”; 2. Clicar na seta expansora de diálogo. Microsoft Excel 2013 do grupo Alinhamento - O programa abrirá uma caixa 19 Profissional QI Clicar na guia “Alinhamento”; vamos observar a caixa “Orientação”. Nela, encontramos a posição do conteúdo da célula em graus. Como podemos observar, o espaço “Orientação” apresentado na imagem acima está em zero grau, ou seja, o conteúdo mantém um alinhado horizontal. 4. Para modificar o alinhamento devemos clicar na linha ao lado da palavra “Texto” e mover a linha para cima ou para baixo. 3. Microsoft Excel 2013 20 Profissional QI Mesclando células Na guia “Página Inicial”, além de alinharmos as células podemos mesclá-las, ou seja, mesclar células é fazer com que várias células se transformem em uma única célula. Para mesclar células devemos: 1. Selecionar as células que queremos mesclar; 2. No grupo de opções “Alinhamento” da guia “Página Inicial”, marcamos a opção “Mesclar e Centralizar”; O intervalo de células selecionadas ficará mesclado: Microsoft Excel 2013 21 Profissional QI Vamos praticar Selecionar as células A1, B1 e C1 na guia “Alinhamento”. A seguir, marcar a opção mesclar células; Após mesclar as células, centralizar e colocar o título em negrito; Selecionar as células A3, B3 e C3 e aplicar um alinhamento de 90 graus. Para realizar essas formatações devemos: o Clicar na guia “Página Inicial”; o Clicar na seta expansora do grupo “Fonte”. O programa abrirá uma caixa de diálogo; o Clicar na guia “Alinhamento” desta caixa de diálogo; o Posicionar o cursor na caixa “Orientação” e definir o alinhamento para 90 graus; Após aplicar o alinhamento, colocar a fonte em negrito; Salvar essa planilha com o nome de “Exemplo 1” no local de sua escolha. Bordas da planilha Quando o Excel 2013 é aberto aparece com linhas de grade. Porém, essas linhas que aparecem em sua abertura não vão sair em uma impressão, porque elas são apenas para separar uma célula da outra. Podemos personalizar o contorno de nossa planilha com bordas que são aplicadas através de uma opção chamada “Bordas”. Para adicionar bordas a uma tabela devemos seguir os seguintes passos: 1. Selecionar as células da nossa tabela; 2. Clicar na guia “Página Inicial”; 3. Clicar na seta expansora do grupo “Fonte”; O programa abrirá uma caixa de diálogo; 4. Clicar na guia “Borda” desta caixa de diálogo; Microsoft Excel 2013 22 Profissional QI Nessa guia encontramos as seguintes opções para personalizar a borda das nossas planilhas: Estilo Devemos escolher qual o estilo de linha ou tracejado será aplicado à célula ou sequência de células selecionadas. Microsoft Excel 2013 23 Profissional QI Cor Clicando nesta opção podemos definir a cor da borda da nossa planilha. Para definir a cor devemos seguir os seguintes passos: 1. Clicar na caixa logo abaixo da opção cor; o programa abrirá uma paleta de cores; 2. Clicar sobre a cor desejada. Predefinições Encontramos três tipos de bordas predefinidas que são: Nenhuma: Clicando nesta opção removemos as bordas externas e internas da tabela. Contorno: Clicando nessa opção inserimos bordas no contorno da tabela. Interna: Clicando nessa opção inserimos bordas no interior da tabela. Microsoft Excel 2013 24 Profissional QI Bordas Nesta opção podemos editar as bordas manualmente clicando nas opções de bordas individuais: somente superior, inferior, direita, entre outras. Visualização Simplesmente exibe o que você está formatando no momento. Como mostrado na imagem ao lado, visualizamos a aplicação de uma borda superior e uma borda lateral. Vamos praticar Utilizando a planilha “Exemplo 1” vamos aplicar as seguintes formatações: Selecionar as células A1, B1 e C1, e aplicar bordas somente no contorno; Em seguida, selecionar o intervalo de A3:C7 (toda planilha menos o título) e aplicar bordas no contorno um pouco mais grossas e bordas internas tracejadas; Após aplicar as formatações vamos atualizar a planilha salvando-a novamente. Microsoft Excel 2013 25 Profissional QI Formatação de números Para melhorar a visualização do conteúdo das tabelas podemos personalizar o modo como o Excel exibe os dados. Porém, não podemos utilizar alguns caracteres nas planilhas, como por exemplo, o $. Ao invés disso, podemos fazer uma formatação de números nas células, para que o Excel exiba-os da maneira mais adequada. Essa formatação do conteúdo de uma célula deve ser feito de acordo com o tipo de dado que está na célula ou que será inserido nela. Para modificar o formato de um número em uma célula devemos seguir os seguintes passos: 1. Clicar na guia “Página Inicial”; 2. Em seguida, clicar na seta expansora do grupo “Número”; o programa abrirá uma caixa de diálogo; por padrão a Guia “Número” aparecerá selecionada; 3. Escolher a categoria desejada, como por exemplo, Data, Hora e porcentagem. 4. Ainda podemos configurar o tipo de dados que a célula receberá ou a quantidade de casas decimais. 5. Após definirmos as formatações pressionamos o botão Ok. Microsoft Excel 2013 26 Profissional QI Exemplo Em uma célula que contém VALORES MONETÁRIOS devemos formatar os números na Categoria “Moeda”, ou seja, se na célula houver o valor 6, após formatar em Moeda, o Excel passará a exibir R$ 6,00. Para formatar os números devemos seguir os seguintes passos: 1. Selecionar a(s) célula(s) a ser(em) formatada(s); 2. Na guia “Página Inicial”, clicar na seta expansora do grupo “Número”; O sistema abrirá uma caixa de diálogo; Por padrão a Guia “Número” aparecerá selecionada; 3. Clicar na categoria desejada, como por exemplo, a categoria “Moeda” citada acima; 4. Conforme a categoria escolhida é possível definir a quantidade de casas decimais, o símbolo que será usado e o como o número será apresentado. 5. Depois de realizadas todas as formatações pressionar o botão ok. VALORES MONETÁRIOS Dinheiro VALORES MONETÁRIOS dinheiro Categoria Padrões disponíveis para formatação do conteúdo das células. Microsoft Excel 2013 27 Profissional QI Exemplo O Exemplo serve apenas para mostrar a visualização das formatações definidas. Casas Decimais Nesta opção determinamos quantas casas decimais desejamos obter depois da vírgula. Como podemos observar no exemplo abaixo, foram determinadas duas casas decimais. Números negativos FORMATO DE NÚMERO NEGATIVO Quando um número negativo é digitado ou quando o resultado de algum cálculo apresentar valores negativos. Nesta opção podemos personalizar o formato do número que será digitado na célula. Nela encontramos a opção para configuração de simbologia de números negativos nas categorias “Número” e “Moeda”. Para definir o tipo de FORMATO devemos clicar sobre o formato de nossa preferência. VALORES MONETÁRIOS dinheiro Microsoft Excel 2013 28 Profissional QI Símbolos Nessa opção podemos definir qual símbolo será utilizado nas categorias contábil e moeda. Para definir o tipo de símbolo, devemos clicar sobre o estilo de nossa preferência. Vamos praticar Vamos abrir novamente o “Exemplo 1” e fazer as seguintes formatações: Alterar o formato da data conforme a figura; Selecionar os valores da coluna “Salário” e aplicar formato de “Moeda” para que apareçam com o símbolo R$ e duas casas decimais; Novamente vamos atualizar a planilha salvando-a. Preenchimento de células No Excel podemos aplicar uma cor ou até mesmo efeitos de preenchimento em uma ou em várias células (seleção). Para aplicar um efeito de preenchimento devemos seguir os seguintes passos: Microsoft Excel 2013 29 Profissional QI 1. Clicar na guia “Página Inicial”; 2. Clicar na seta expansora do grupo “Fonte”; O programa abrirá uma caixa de diálogo; 3. Escolher a guia “Preenchimento”; 4. Clicar nas cores disponíveis da paleta de cores; Ainda podemos aplicar efeitos diferentes clicando em “Efeitos de Preenchimento”. Podemos escolher um efeito chamado de “Gradiente” (que permite a escolha de mais de uma cor para criar esse efeito). Para aplicar esse tipo de efeito devemos seguir os seguintes passos: 1. Clicar no botão “Efeitos de Preenchimento”; O programa abrirá uma caixa de diálogo somente com a guia “Gradiente”; 2. Clicar sobre as caixas “Cor 1” e “Cor 2”, para escolher as cores do efeito gradiente; 3. O programa abrirá uma paleta de cores; 4. Clicar sobre a cor de nossa preferência. 5. Clicar no botão Ok. Microsoft Excel 2013 30 Profissional QI Podemos observar na figura acima que, além da definição das cores, também podemos modificar a posição do sombreamento. Vamos praticar Vamos abrir novamente o “Exemplo 1” e aplicar as seguintes formatações: Aplicar preenchimento gradiente nos títulos da tabela criada anteriormente; Aplicar um preenchimento simples, ou seja, sem nenhum efeito; Observar como ficou a nossa planilha de Funcionários. Salvar novamente a planilha para mantê-la atualizada. ATIVIDADES 1. Crie uma planilha conforme o exemplo abaixo: Microsoft Excel 2013 31 Profissional QI 2. Em seguida, modifique o nome de “Plan1” para “Formatação”; 3. Crie então a segunda planilha na Plan2: 4. Modifique o nome da “Plan2” para “Alinhamento”; 5. Salve a planilha com o nome de “Atividade 1”. REFERÊNCIAS MICROSOFT OFFICE. <office.microsoft.com/pt-br/excel> VALORES MONETÁRIOS dinheiro Microsoft Excel 2013 32 Profissional QI ANOTAÇÕES Microsoft Excel 2013 33 Profissional QI AULA 2 O QUE VAMOS APRENDER: Iniciando as fórmulas Matemática e uso de parênteses Criando fórmulas simples INICIANDO AS FÓRMULAS As fórmulas são equações que calculam os valores das células. Todas as fórmulas do Excel 2013, sem nenhuma exceção, começam com o sinal de igual “=” seguido das referências das células juntamente com os operadores matemáticos para obtermos um determinado resultado. Microsoft Excel 2013 OPERADORES ARITMÉTICOS OPERADOR + * / % ^ SIGNIFICADO adição subtração multiplicação divisão porcentagem exponenciação EXEMPLO 3+3 3-1 3*3 3/3 20% 3^2 34 Profissional QI : (Dois Pontos) Os dois pontos no Excel significam o intervalo com todas as células existentes entre esse intervalo de duas células. Podemos dizer que o dois pontos podem ser substituídos pela palavra “até”, isto é, podemos ler o intervalo A1:B5 da seguinte maneira: A1 até B5, ou seja, engloba o total de 10 células, são elas: A1, A2, A3, A4, A5, B1, B2, B3, B4 e B5. ; (Ponto e vírgula) Combina diversas células, porém não abrange a todas entre si, apenas as selecionadas. Podemos dizer que o ponto e vírgula pode ser substituído pela vogal “e”, isto é, podemos ler o intervalo A1;B5 da seguinte maneira, A1 e B5, ou seja, engloba somente duas células, sendo elas: A1 e B5. Microsoft Excel 2013 35 Profissional QI MATEMÁTICA E USO DE PARÊNTESES Devemos ter cuidado, porque no Excel, assim como na nossa matemática, as fórmulas serão executadas da esquerda para a direita, seguindo a ordem de cálculo dos operadores matemáticos. Podemos também criar fórmulas mais complexas com o uso dos parênteses, assim, determinado cálculo é executado antes de outro numa mesma fórmula. CRIANDO FÓRMULAS SIMPLES Para criar cálculos no Excel é muito simples. Podemos, por exemplo, calcular a soma de duas células digitando o sinal de =, a referência da primeira célula, um sinal de mais + e a referência da segunda célula. Para visualizar o resultado, pressione enter. A fórmula aparecerá na barra de fórmulas, onde podemos editá-la sempre que necessário. Microsoft Excel 2013 36 Profissional QI 1º Exemplo Esse exemplo é relacionado com a média de um aluno. Para fazer a média da Nota 1 e da Nota 2 precisamos fazer uma fórmula no Excel. Essa fórmula depende dos operadores aritméticos e parênteses. Para fazer essa fórmula devemos seguir os seguintes passos: 1. Crie uma planilha conforme a imagem: 2. 3. 4. 5. 6. 7. 8. 9. 10. Clique na célula C2 e digite o sinal de igual “=”; Abrir parênteses; Clicar na célula A2; Adicionar o sinal de mais “+”; Clicar na célula B2; Fechar parênteses; Adicionar o sinal de divisão “/”; Digitar o número “2”, pois vamos calcular uma média entre dois valores; Pressionar a tecla Enter. Após pressionarmos a tecla Enter, o Excel vai calcular a soma das duas células e dividir o resultado por 2. Como podemos observar na figura abaixo, na célula C2 (onde foi inserido a fórmula) ficou a nota 8,5, que é o resultado da fórmula “=(A2+B2)/2”. 2º Exemplo Vamos supor que precisamos calcular o INSS de um funcionário de uma empresa que tenha um salário de R$ 494,22, considerando uma ALÍQUOTA de 8%. O valor do cálculo deverá ser descontado dos R$ 494,22. INSS Instituto Nacional de Seguridade Social: são contribuições comuns a todos os trabalhadores ALÍQUOTA Percentual a ser descontado do salário de acordo com tabela da Previdência VALORES MONETÁRIOS dinheiro Microsoft Excel 2013 37 Profissional QI Para calcular esse valor devemos: 1. 2. 3. 4. 5. 6. Clicar na célula C2; Inserir o sinal de igual “=”; Clicar na célula A2; Inserir o sinal de multiplicação “*”; Clicar na célula B2; Em seguida pressionar a tecla Enter; Será calculado 8% do salário do funcionário, que seria o valor da célula A2 multiplicada pelo valor da célula B2. Esse resultado será mostrado na célula C2, onde foi inserida a fórmula. Em seguida vamos calcular o Salário Final e, para isso, devemos seguir os seguintes passos: 1. Clicar na célula D2; 2. Inserir o sinal de igual “=”; 3. Clicar na célula A2; 4. Inserir o sinal de subtração “-”; 5. Clicar na célula C2; 6. Pressionar a tecla Enter; Será calculada a subtração do salário do funcionário pelo valor de desconto de INSS, ou seja, 8% do seu salário. Essa subtração será feita através do valor da célula A2 menos o valor da célula C2, ficando conforme a figura. Microsoft Excel 2013 38 Profissional QI Vamos praticar Vamos criar a tabela a seguir; Vamos calcular na coluna D o total de horas trabalhadas para cada funcionário; Para calcularmos o total de horas trabalhadas, devemos multiplicar os dias trabalhados pelas horas por dia, por exemplo, =B2*C2. REFERÊNCIAS MICROSOFT OFFICE. <office.microsoft.com/ptbr/excel> ATIVIDADES 1. Abra o Excel e crie a tabela abaixo, seguindo as mesmas formatações: VALORES MONETÁRIOS dinheiro 2. 3. 4. 5. 6. Calcule na coluna D a quantidade de horas trabalhadas diariamente; Calcule na coluna F o valor diário que a funcionária “Rosicler” receberá; Calcule na célula F11 o valor total a receber semanalmente. Renomeie a Plan1 com o nome de Cartão Ponto; Salve a planilha com o nome de “Cartão Ponto” na pasta de sua preferência. Microsoft Excel 2013 39 Profissional QI ANOTAÇÕES Microsoft Excel 2013 40 Profissional QI O QUE VAMOS APRENDER: AULA 3 O que são funções? Função Soma Função Média O QUE SÃO FUNÇÕES? No Excel, além de criarmos as nossas próprias fórmulas e cálculos matemáticos, podemos utilizar as FUNÇÕES prontas que ele nos fornece. Para utilizar uma função temos que começar com o sinal de “=”. Após o sinal de igual digitamos o nome da função e, logo em seguida, iniciamos um parêntese, que deve ser colocado após as referências das células que queremos calcular e, ao final, fechamos com outro parêntese para encerrarmos a função. Quando finalizamos a edição da nossa função precisamos pressionar a tecla Enter, para que a mesma execute o cálculo e coloque o resultado na célula onde a função foi digitada. FUNÇÃO interpretação de uma fórmula matemática FUNÇÃO SOMA A função soma tem por finalidade somar um determinado INTERVALO de dados. Microsoft Excel 2013 INTERVALO Seleção de duas ou mais células 41 Profissional QI Sintaxe da função Soma =SOMA(intervalo) Exemplo Utilizando a função soma conseguimos somar o total das vendas dos funcionários (do exemplo), através da seguinte fórmula: =soma(D3:D7) Microsoft Excel 2013 42 Profissional QI Para inserir esta fórmula devemos seguir os seguintes passos: 1. Clicar na célula que desejamos obter o resultado do cálculo, nesse caso a célula D9; 2. Digitar o sinal de = (igual); 3. Digitar a expressão que representa o cálculo que queremos fazer, nesse caso a soma, e então abrimos um parêntese; 4. Selecionar os dados que queremos calcular, nesse caso o intervalo D3:D7, e fechamos o parêntese; 5. Pressionar a tecla Enter. O intervalo de D3:D7, nesse caso, dirá exatamente os valores das vendas dos funcionários. A função soma vai somar todos os valores das células que estão entre esses intervalos. Vamos praticar Criar a tabela abaixo, seguindo as mesmas formatações de cores e bordas; Calcular nas células B12, C12 e D12 a soma das colunas “Gasto com salários”, “Vendas” e “Gasto com Vale Transporte”; Em seguida, vamos salvar essa planilha com o nome de “Despesas Mensais”. Microsoft Excel 2013 43 Profissional QI FUNÇÃO MÉDIA A função média realiza a média do intervalo de células em questão. O detalhe é que ela automaticamente soma os conteúdos das células e divide pelo número de células selecionadas. Sintaxe da função MÉDIA =MEDIA(intervalo) Exemplo No exemplo estamos calculando a média das vendas dos vendedores através da fórmula: =média(D3:D7) Para inserir esta fórmula devemos seguir os seguintes passos: 1. Clicar na célula que desejamos obter o resultado do cálculo, nesse caso a célula D9; 2. Digitar o sinal de = (igual); 3. Digitar a expressão que representa o cálculo que queremos fazer (nesse caso a média) e abrir parênteses; 4. Selecionar os dados que queremos calcular, nesse caso o intervalo D3:D7; 5. Pressionar a tecla Enter. O intervalo incluído na fórmula é o intervalo que será utilizado para realizar o cálculo da média das vendas dos funcionários. Microsoft Excel 2013 44 Profissional QI A função média automaticamente soma os conteúdos das células e divide pelo número de células somadas, calculando efetivamente a média do intervalo selecionado. Vamos praticar Vamos abrir a planilha “Despesas Mensais” e aplicar as seguintes modificações; Colocar o item “Média” na célula A13 da nossa planilha; Após inserir a opção “Média”, vamos calcular o valor médio das colunas gastos com salários, vendas e gastos com vale transporte; Em seguida, vamos atualizar nossa planilha salvando novamente. REFERÊNCIAS MICROSOFT OFFICE. <office.microsoft.com/pt-br/excel> VALORES MONETÁRIOS dinheiro Microsoft Excel 2013 45 Profissional QI ATIVIDADES 1. Faça uma planilha conforme a figura abaixo, aplicando as mesmas formatações de bordas, alinhamento, sombreamentos e cores. a) Calcule o valor diário que a funcionária Rosicler vai receber, considerando que o valor da sua hora é R$ 12,74, conforme a célula B3; b) Calcule na célula mesclada E6, o total de horas trabalhadas; c) Calcule no intervalo de G7:G12, o valor a receber de horas extras; d) Calcule na célula G13, o valor total a receber por horas extras; e) Calcule na célula mesclada I6, o valor referente ao Total de Horas Extras; f) Calcule na célula B16, o repouso remunerado; g) Calcule na célula B17, o valor total a receber; h) Salve a planilha com o nome de “Cartão Ponto”. Microsoft Excel 2013 46 Profissional QI ANOTAÇÕES Microsoft Excel 2013 47 Profissional QI O QUE VAMOS APRENDER: AULA 4 Função Máximo Função Mínimo FUNÇÃO MÁXIMO Esta função retorna o maior valor de uma seleção de células no Excel. Podemos usar essa fórmula para descobrirmos o funcionário mais antigo da empresa, por exemplo. Microsoft Excel 2013 48 Profissional QI Sintaxe da função MÁXIMO =MAXIMO(intervalo) Exemplo Para achar o valor máximo do intervalo na planilha abaixo, devemos digitar, na célula D5, a seguinte fórmula: =maximo(B4:B10) Para inserir a função devemos seguir os seguintes passos: 1. Clicar na célula que desejamos obter o resultado do cálculo, nesse caso a célula D5; 2. Digitar o sinal de =; 3. Digitar a expressão que representa o cálculo que queremos fazer, nesse caso a função “máximo”, e então abrimos parêntese; 4. Selecionar os dados que queremos calcular, nesse caso o intervalo B4:B10 (intervalo onde queremos achar a maior data de admissão) e então fechamos parêntese. 5. Pressionar a tecla Enter. Vamos praticar Vamos desenvolver a planilha da imagem abaixo aplicando as mesmas formatações apresentadas; Após digitarmos a planilha vamos calcular o valor máximo nas células B12, C12 e D12; Então salvamos a planilha com o nome “Controle de Gastos”. Microsoft Excel 2013 49 Profissional QI FUNÇÃO MÍNIMO Ao contrário da função máximo, a função mínimo tem por objetivo retornar o menor valor de uma seleção de células. Podemos usar essa fórmula para descobrir o funcionário mais novo da empresa, por exemplo. Sintaxe da função MÍNIMO =MINIMO(intervalo) Microsoft Excel 2013 50 Profissional QI Exemplo Para achar o menor valor (valor mínimo) do intervalo de células na planilha acima, digitamos na célula D8 a seguinte fórmula: =minimo(B4:B10) Para inserir essa fórmula devemos seguir os seguintes passos: 1. Clicar na célula que desejamos obter o resultado do cálculo, nesse caso a célula D8; 2. Digitar o sinal de = (igual); 3. Digitar a expressão que representa o cálculo que queremos fazer (nesse caso a função “mínimo”) e então, abrimos parêntese; 4. Selecionar os dados que queremos calcular, nesse caso o intervalo B4:B10 (intervalo onde queremos achar a menor data de admissão) e então fechamos o parêntese; 5. Pressionar a tecla Enter. Vamos praticar Abra a planilha de “Controle de Gastos” e aplique as seguintes modificações; Inserir na linha 13 o “Valor Mínimo”; Após inserirmos o “Valor Mínimo”, vamos calcular o valor mínimo das células B13, C13 e D13; Após efetuarmos os cálculos vamos salvar a planilha para atualizá-la. Microsoft Excel 2013 51 Profissional QI ATIVIDADES 1. Desenvolva uma planilha conforme a planilha da figura abaixo, seguindo as mesmas configurações. a) b) c) d) e) f) Calcule nas células F12, G12 e H12 a soma dos salários referentes aos meses listados; Calcule nas células F13, G13 e H13 a média dos salários; Mostre a maior e a menor jornada de trabalho; Mostre a data de admissão do funcionário mais antigo e do mais novo; Renomeie a Plan1, com o nome “Salário Vendedores”; Salve a planilha com o nome “Salário Setor Vendas”. REFERÊNCIAS MICROSOFT OFFICE. <office.microsoft.com/pt-br/excel> VALORES MONETÁRIOS dinheiro Microsoft Excel 2013 52 Profissional QI ANOTAÇÕES Microsoft Excel 2013 53 Profissional QI AULA 5 O QUE VAMOS APRENDER: Função cont.se Função somase FUNÇÃO CONT.SE Essa função tem por finalidade contar células através de um critério pré-estabelecido na própria fórmula. Importante: o critério para a função fazer a contagem de quantas células contém o valor especificado tem que estar sempre entre aspas duplas, mesmo sendo um teste com números. Lembrese: sempre entre aspas. Sintaxe =cont.se(intervalo;“critério”) Exemplo Na planilha da Empresa Xyz, se quisermos saber quantos funcionários da Administração a empresa possui devemos digitar na célula B10 a seguinte fórmula: =cont.se(B3:B7;“Adm”) Microsoft Excel 2013 54 Profissional QI Para inserir essa fórmula devemos seguir os seguintes passos: 1. Clicar na célula que desejamos obter o resultado do cálculo, nesse caso a célula B10; 2. Digitar o sinal de =; 3. Digitar a expressão que representa o cálculo que queremos fazer (nesse caso a função “cont.se”), e então abrir um parêntese; 4. Selecionar os dados que queremos calcular, nesse caso o intervalo B3:B7 (intervalo onde queremos contar a quantidade de setor Adm.); 5. Inserir um ponto e vírgula para separar a seleção do critério, e então digitar entre aspas o texto Adm. e fechar parênteses; 6. Pressionar a tecla Enter. Nesse caso o Excel retornará o número três, pois são três células que contém esse valor. Importante A função “cont.se()” conta somente quantas células têm determinado valor, ela não soma o conteúdo das células. Vamos praticar Vamos criar uma planilha conforme a figura abaixo. Microsoft Excel 2013 Calcular na célula E13 a quantidade de funcionários que recebem Insalubridade; Calcular na célula E14 a quantidade de funcionários que não recebem Insalubridade; Calcular na célula E15 a quantidade de funcionários que recebem adicional noturno. Salvar essa planilha com o nome de “Insalubridade e Adicional Noturno”. 55 Profissional QI FUNÇÃO SOMASE Essa função, como o próprio nome se refere “soma” “se”, ou seja, ela soma um determinado conjunto de células selecionadas “se” estiver de acordo com determinado critério. A fórmula procura na coluna do intervalo do critério por determinado critério pré-estabelecido em “critério”; após separar as células que se enquadram em determinado critério, ela utilizará outra coluna com os valores a serem somados. Sintaxe =somase(intervalo do critério;“critério”;intervalo que seria somado) Exemplo Na planilha da Emprea Xyz - para somar os salários separados por setor, utilizamos a seguinte fórmula (para somar somente os salários de Administração): =somase(B3:B7;“Adm”;D3:D7) Para inserir essa fórmula devemos seguir os seguintes passos: 1. Clicar na célula que desejamos obter o resultado do cálculo, nesse caso a célula D9; 2. Digitar o sinal de = (igual); Microsoft Excel 2013 56 Profissional QI 3. Digitar a expressão que representa o cálculo que queremos fazer (nesse caso a função “somase”) e abrir parêntese; 4. Selecionar os dados do critério, nesse caso o intervalo de B3:B7; 5. Digitar qual o critério para o Excel separar do intervalo B3:B7 (somente o critério que desejamos, nesse caso “Adm”); 6. Selecionar o intervalo que será realmente somado, nesse caso o D3:D7; 7. Pressionar a tecla Enter. Nesse exemplo serão somados os salários de três funcionários do setor de Adm. Resumindo, a fórmula vai procurar no intervalo de B3:B7 pelo critério “Adm”. Após encontrar e separar os valores, ela vai para o intervalo “D3:D7” somar os salários dos funcionários do setor “Adm”. Vamos praticar Vamos abrir a planilha “Insalubridade e Adicional Noturno” e acrescentar uma tabela conforme a figura abaixo: Calcular na célula B13 o total dos salários dos funcionários que recebem Insalubridade; Na célula B14 o total dos salários dos funcionários que não recebem insalubridade; Calcular o total dos salários dos funcionários que recebem adicional noturno, na célula B15; Na célula B16, calcular o total dos funcionários que não recebem adicional noturno; Vamos salvar novamente a planilha para atualizá-la. REFERÊNCIAS MICROSOFT OFFICE. <office.microsoft.com/pt-br/excel> VALORES MONETÁRIOS dinheiro Microsoft Excel 2013 57 Profissional QI ATIVIDADES 1. Desenvolva uma planilha como a da imagem abaixo, usando as mesmas formatações: a) Calcule nas células da coluna D o valor que será pago aos funcionários que recebem insalubridade, considerando que esses valores serão pagos para os cargos de Auxiliar de Serviços Gerais - equivalente a 10% do seu salário e Segurança - equivalente a 40% do seu salário; b) Calcule o valor da comissão paga somente aos Vendedores, equivalente a 2% do seu salário; c) Calcule o valor do adicional noturno de 20%; d) Após calcular todos os adicionais, calcule o valor total que cada funcionário receberá; e) Calcule os valores totais de insalubridade, comissão e adicional noturno; f) Calcule o valor total das despesas com salários que a empresa pagará; g) Salve a planilha com o nome de “Pagamento de Funcionários”. Microsoft Excel 2013 58 Profissional QI ANOTAÇÕES Microsoft Excel 2013 59 Profissional QI O QUE VAMOS APRENDER: AULA 6 Função SE FUNÇÃO SE Esta função é muito importante porque utiliza um teste condicional, ou seja, ela testa uma condição. Se por acaso essa condição for verdadeira ela faz o que estará definido em “valor se verdadeiro”, se por ventura essa condição for falsa, ela fará o que estará definido em “valor se falso”. Para montar uma condição necessitamos que ela utilize os operadores relacionais. Microsoft Excel 2013 60 Profissional QI OPERADORES RELACIONAIS OPERADOR SIGNIFICADO EXEMPLO = > < >= <= <> igual a maior do que menor do que maior ou igual a menor ou igual a diferente A1=C1 A1>C1 A1<C1 A1>=C1 A1<=C1 A1<>C1 Com os operadores podemos fazer testes entre as referências das células, conforme os exemplos citados na tabela acima. Sintaxe =SE(condição;valor verdadeiro;valor falso) Exemplo Como exemplo, temos uma planilha de comissões. Para sabermos se o funcionário receberá ou não comissão, necessitamos da função “se”. Na célula C4 foi digitada a seguinte fórmula: =se(B4>=600;“Sim”;“Não”) A condição “B4>=600” retorna dois resultados: ou o B4 é maior ou igual ao número 600, ou B4 é menor que 600, ou seja, notem que nos referimos ao conteúdo da célula B4. Microsoft Excel 2013 61 Profissional QI Sempre preparamos a fórmula utilizando somente a referência da célula, e não o valor dela. Trabalhando com o endereço da célula deixamos nossa tabela dinâmica. Se o resultado de B4>=600 for verdadeiro, isto é, o conteúdo da célula B4 for maior ou igual a 600, a função retornará o valor que será a palavra “SIM”, caso contrário, se for menor que 600, a fórmula retornará a palavra “NÃO”. Vamos praticar Desenvolver uma planilha conforme a figura abaixo, usando as mesmas formatações; Calcular o valor da insalubridade que cada funcionário poderá receber; Na coluna “Situação Final” vamos definir que os funcionários que têm o percentual de insalubridade menor que 15% não receberão o adicional, caso contrário, receberão; Calcular o valor total das insalubridades; Calcular o valor médio do total de insalubridades. Salvar a planilha com o nome “Insalubridade”. REFERÊNCIAS MICROSOFT OFFICE. <office.microsoft.com/pt-br/excel> VALORES MONETÁRIOS dinheiro Microsoft Excel 2013 62 Profissional QI ATIVIDADES 1. Desenvolva uma planilha conforme a figura abaixo, seguindo as mesmas formatações: a) Calcule 6% de desconto nos salários dos funcionários na coluna “D” do salário; b) Na coluna “E”, defina se o salário do funcionário sofrerá algum desconto, considerando se o valor do VT foi inferior a 6% do salário a descontar, caso contrário, não deverá ser feito nenhum desconto; c) Calcule na coluna “F” o valor total com desconto; d) Calcule na tabela “Gastos com VT” o menor e maior valor gasto com VT, o total Gasto e a média de gastos com VT; e) Calcule na coluna “H” o total de salários com desconto de VT; f) Salve a planilha com o nome de “Planilha VT”. Microsoft Excel 2013 63 Profissional QI ANOTAÇÕES Microsoft Excel 2013 64 Profissional QI O QUE VAMOS APRENDER: AULA 7 Função SE(SE()) FUNÇÃO SE(SE()) A função “se” tem uma variação. Nela, temos apenas duas respostas para cada teste: ou é verdadeiro ou é falso. Mas, podemos utilizar um recurso que é o ENCADEAMENTO. Podemos colocar as funções “se” uma dentro da outra, simplesmente para poder fazer mais testes condicionais, quando houver mais de duas possibilidades. Sintaxe ENCADEAMENTO Aninhamento, ou seja, mais de uma função Se dentro de outra VALORES MONETÁRIOS dinheiro =SE(condição;valor verdadeiro;se(segunda condição;valor verdadeiro;valor falso)) Observação Precisamos reparar que na função acima onde está na cor verde é exatamente o início de outra função “se” dentro de outra. Exemplo 1 Para testar se um valor é positivo ou não, necessitamos apenas de um “se” simples. O teste será com duas respostas: ou o valor da célula será positivo, ou negativo. =se(célula>0;“Valor positivo”;“Valor Negativo”) Para testar se o valor é nulo não conseguimos colocar esses testes em uma função “se” simples, temos que utilizar o encadeamento da função “se”. Exemplo 1 - para testar se é positivo, negativo ou nulo. =se(célula>0;“Valor positivo”;se(célula<0;“Valor negativo”;“Valor nulo”)) Microsoft Excel 2013 65 Profissional QI Exemplo 2 No exemplo 2 (figura acima) temos a utilização de uma função “se” encadeada. A Empresa adota uma tabela de premiação para os vendedores com o seguinte critério: Se o vendedor vendeu menos que R$ 500,00, ganha o valor das vendas mais R$ 50,00 de bônus. Se o vendedor vendeu menos que R$ 2.000,00, ganha o valor das vendas mais R$ 250,00 de bônus. Se o vendedor vendeu qualquer valor acima de R$ 2.000,00, ganha o valor das vendas mais R$ 500,00. Fórmula =se(C7<500;C11;se(C7<2000;C12;C13)) No seguinte teste: “C7<500”, é efetuada uma verificação para testar se o valor da venda realizado pelo funcionário foi menor que R$ 500,00. Se por acaso for menor, ele receberá R$ 50,00. O valor de cinquenta reais está na célula com a seguinte referência: C11. Se por acaso não for menor que R$ 500,00, ele vai entrar no segundo “se”, fazendo um segundo teste condicional, “C7<2000”. Se as vendas forem menores que R$ 2.000,00 o vendedor receberá R$ 250,00 de bônus, que está na célula com a seguinte referência: C12. Caso contrário, ou seja, - se não - (o valor das vendas não for inferior a R$ 500,00 e não for menor que R$ 2.000,00), será a última opção, que será o C13. O vendedor então ganhará o valor de R$ 500,00, que está na célula C13. Microsoft Excel 2013 66 Profissional QI Vamos praticar Desenvolver uma planilha conforme a figura da planilha abaixo, seguindo as mesmas formatações. Calcular na coluna “Comissão” o valor da comissão que cada funcionário receberá; Calcular na coluna “Valor a Receber” o valor que cada funcionário receberá. Salvar a planilha com o nome de “Comissões Vendedores”. REFERÊNCIAS MICROSOFT OFFICE. <office.microsoft.com/pt-br/excel> VALORES MONETÁRIOS dinheiro Microsoft Excel 2013 67 Profissional QI ATIVIDADES 1. Desenvolva uma planilha conforme a figura da planilha abaixo, seguindo as mesmas formatações: a) Calcule na coluna “Valor do Desconto” o desconto de INSS a ser descontado do salário de cada funcionário; b) Busque os percentuais de descontos atualizados na Internet e preencha a coluna desconto; c) Calcule na coluna “D” o “valor a receber” que cada funcionário receberá com desconto. Lembre-se você deve diminuir o valor do desconto do salário; d) Salve a planilha com o nome de “Tabela INSS”. Microsoft Excel 2013 68 Profissional QI ANOTAÇÕES Microsoft Excel 2013 69 Profissional QI AULA 8 O QUE VAMOS APRENDER: Funções PROCV e PROCH FUNÇÕES PROCV E PROCH As funções “procv” e “proch” procuram por determinado valor em outra planilha no mesmo arquivo, ou até mesmo uma planilha em outro arquivo. A diferença entre “procv” e “proch” é a maneira de buscar os dados. O “v” do “procv” significa que ele buscará os dados em uma tabela “vertical”, ou seja, ele vai realizar a busca coluna a coluna. Enquanto o “h” do “proch” significa que ele realizará a busca em uma determinada tabela horizontalmente, isto é, vai realizar a busca linha a linha. Sintaxe =PROCH(valor procurado;intervalo tabela;linha que está o valor) =PROCV(valor procurado;intervalo tabela;coluna que está o valor) Exemplo No exemplo temos duas tabelas: na primeira será calculado o “Valor Total” do salário que cada funcionário receberá, e na segunda temos as informações necessárias para completar a primeira tabela como: Código, Nome do Vendedor e Salário. Para buscar os valores na segunda tabela utilizamos a seguinte fórmula: =procv (A5;G13:I19;2) Microsoft Excel 2013 70 Profissional QI A5 É o código que temos na primeira tabela. Notem que na segunda temos os mesmos códigos, por isso, utilizamos ele como valor “procurado”, ou seja, através do código buscaremos os outros valores na segunda tabela. G13:I19 Este é o intervalo da segunda tabela. Nela, indicamos para a função “procv” onde está a tabela que tem os valores que desejamos buscar. 2 O número “2” na fórmula é onde indicamos para o “procv” em qual coluna ele encontrará o valor que desejamos. Nesse caso, estamos procurando pelo nome do vendedor, que está na segunda coluna da tabela, por isso, colocamos na fórmula o número dois. Vamos praticar Criar a tabela conforme a figura abaixo; Microsoft Excel 2013 71 Profissional QI Através da função “procv” vamos procurar o valor do reajuste do valor da hora dos funcionários. Para realizar essa procura, vamos utilizar o valor das “Horas-semanais” dos funcionários (como valor procurado) e a nossa tabela de procura será a “Tabela Reajuste”. Na coluna “E” vamos calcular o valor da hora já com o seu reajuste. Após preencher a tabela com os valores reajustados, vamos calcular o valor final, ou seja, a quantidade de horas trabalhadas vezes o valor da hora com seu reajuste. Não esqueça de salvar a planilha! REFERÊNCIAS MICROSOFT OFFICE. <office.microsoft.com/pt-br/excel> VALORES MONETÁRIOS dinheiro Microsoft Excel 2013 72 Profissional QI ATIVIDADES 1. Desenvolva uma planilha conforme a figura abaixo, seguindo as mesmas formatações: a) Procure (através da função “proch”) o Nome e o Setor dos funcionários através do código; b) Em seguida, procure (através da função “procv”) na tabela “do INSS” o desconto que o funcionário vai ter através do Salário; c) Depois de realizar as procuras através das funções, calcule na coluna “F” o “Salário Final” dos funcionários; d) Salve a planilha com o nome “Folha de Pagamento”. Microsoft Excel 2013 73 Profissional QI ANOTAÇÕES Microsoft Excel 2013 74 Profissional QI O QUE VAMOS APRENDER: AULA 9 Filtro Formatação condicional Validação de células FILTRO O Filtro, que em algumas versões anteriores do Office foi chamado de Auto-Filtro, serve para filtrar os dados da planilha conforme as condições escolhidas. Essa é uma ótima ferramenta quando Microsoft Excel 2013 75 Profissional QI estamos trabalhando com uma planilha muito extensa. Podemos manipular os dados da nossa planilha através de filtros inseridos na parte superior dela. Inserindo Filtro Para inserir um Filtro devemos seguir os seguintes passos: 1. Selecionar a tabela; 2. Clicar na guia “Dados”; 3. Clicar na opção “Filtro” no grupo “Classificar e Filtrar”; Exemplo Filtrando dados de uma tabela Selecionamos o intervalo: A2:E9 (como podemos notar, não foi selecionado o título da planilha porque não é necessário para realizar a filtragem dos dados). Depois de inserido o Filtro, ficará ao lado de cada coluna uma seta direcionada para baixo, ou seja, uma caixa que contém uma lista de opções. Para realizar a filtragem de dados, devemos clicar na caixa com opções, referente à coluna que queremos filtrar os dados. Microsoft Excel 2013 76 Profissional QI 1. Na figura a seguir clicamos na caixa referente à coluna “Turno”; 2. Após clicarmos no menu referente à coluna “Turno”, as opções “Diurno” e “Noturno” aparecerão, assim podemos escolher qual turno será filtrado; Vamos observar que foi selecionada somente a opção “Noturno”. 3. Depois de termos escolhido a opção clicamos em ok; Na figura abaixo temos exatamente a representação de como ficou a tabela após a inserção do Filtro e a Filtragem somente pelo turno Noturno. A seta referente à coluna TURNO mudou após ser escolhido somente o turno Noturno. Filtragem simultânea Podemos fazer mais de um filtro simultaneamente na mesma tabela para visualizar somente os dados desejados. No próximo exemplo foram feitas duas filtragens em uma única tabela. Microsoft Excel 2013 77 Profissional QI Quando é feita uma filtragem, as linhas que são descartadas ficam escondidas e não aparecem os números referentes a elas. Retirando um filtro da tabela Para retirar um Filtro de uma tabela devemos seguir os seguintes passos: 1. Selecionar a tabela; 2. Clicar na guia “Dados”; 3. Clicar novamente na opção “Filtro” no grupo “Classificar e Filtrar”; Vamos praticar Desenvolver uma planilha conforme a figura abaixo. Inserir Filtro - para isso, devemos selecionar o intervalo A4:D11; Filtrar os dados da coluna Turno; Filtrar os dados de todos os funcionários que trabalham à noite; Microsoft Excel 2013 78 Profissional QI Aplicar uma filtragem simultânea, filtrando os dados dos funcionários que trabalham durante o dia e ganham R$ 800,00; Remover o Filtro da tabela; Salvar a planilha com o nome de “Funcionários”. FORMATAÇÃO CONDICIONAL A Formatação Condicional tem como principal objetivo destacar determinados valores de uma célula ou de um intervalo de células de nosso interesse. O Excel faz a formatação (seja ela de cores ou preenchimento de células), através de condições pré-estabelecidas ou, simplesmente, pelo maior valor da célula. Podemos formatar as células condicionalmente através de regras, de barras indicando o maior valor, por escalas de cor, por conjunto de ícones ou por regras específicas definidas na opção Nova Regra. Microsoft Excel 2013 79 Profissional QI Para inserir uma formatação condicional devemos seguir os seguintes passos: 1. Selecionar a célula que receberá a formatação; 2. Clicar na guia “Página Inicial”; 3. Clicar na opção “Formatação Condicional” do grupo “Estilo”; 4. O programa exibirá opções de formatações condicionais; 5. Clicar na opção desejada, como a barra de dados, por exemplo. Exemplo de Formatações Condicionais Barra de Dados No exemplo acima foi configurado uma formatação condicional chamada de Barras de Dados, onde o tamanho da barra que aparece em azul é referente ao valor da célula. Microsoft Excel 2013 80 Profissional QI Escalas de Cor No exemplo abaixo foi configurado uma formatação condicional chamada de Escalas de Cor. Nela, são utilizadas duas ou três cores para representar os valores das células. Conjuntos de Ícones No exemplo abaixo foi configurado uma formatação condicional chamada de Conjuntos de Ícones (figuras), onde é utilizado um conjunto de ícones. Cada ícone desse conjunto representa um valor da célula. Microsoft Excel 2013 81 Profissional QI Vamos praticar Abrir a planilha “Funcionários”; Inserir uma Formatação Condicional na coluna “Salário”, escolhendo a formatação “Barras de Dados”, clicando nas “Barras da cor Azul”; Observar que a célula com maior valor será preenchida quase que totalmente; Clicar na opção “Conjuntos de Ícones” e escolher a opção “4 setas (Coloridas)”; Observar na figura abaixo como ficou a nossa planilha. Após visualizarmos a Formatação Condicional, vamos salvar novamente a planilha para atualizá-la. Microsoft Excel 2013 82 Profissional QI VALIDAÇÃO DE CÉLULAS No Excel 2013 temos a opção de fazer uma validação de células, ou seja, indicar para o programa quais dados serão aceitos em determinadas células selecionadas. Podemos fazer validações para as células não aceitarem números negativos, validação de sexo como Masculino e Feminino, uma data inferior ou superior a uma determinada data definida, entre outros tipos de validações. Para fazer uma validação de células devemos seguir os seguintes passos: 1. Selecionar o intervalo que desejamos validar; 2. Clicar na guia “Dados”; 3. Clicar na opção “Validação de Dados” no grupo “Ferramentas de Dados”; 4. Escolher a opção “Validação de Dados”. 5. Aparecerá a caixa de Diálogo com o nome “Validação de Dados”. Essa caixa terá três guias, que são: Configurações, Mensagem de Entrada e Alerta de Erro. Configurações Na guia configurações escolhemos o que vamos permitir no intervalo de células selecionado. Nesta opção escolhemos quais dados serão permitidos no intervalo de dados selecionado. Algumas opções, entre as mais importantes são: números decimais, hora, data e criar uma lista de opções. Microsoft Excel 2013 83 Profissional QI Opção Lista Escolhendo a opção Lista, será exibida uma área com o nome de “Fonte:”. Nessa área digitamos quais opções desejamos que apareça na célula, porém, as opções têm que ser separadas por “ponto e vírgula”. Após digitarmos as opções é só clicar em ok. As opções aparecerão conforme a figura abaixo: Microsoft Excel 2013 84 Profissional QI Opção Decimal Escolhendo a opção “Decimal” na opção “Permitir”, serão exibidas duas áreas: uma chamada “mínimo”, e outra chamada “máximo”. Nessas áreas digitamos qual será o valor mínimo e qual será o valor máximo que a célula, ou as células, aceitarão que o usuário digite. Nesse exemplo o valor mínimo digitado foi de zero (0), e o valor máximo foi dez (10), simulando que, no intervalo de células selecionado, só serão digitadas (apenas) as notas de alunos, onde os valores variam de 0 a 10. Mensagem de Entrada Nesta guia escolhemos se queremos que seja mostrada uma mensagem para o usuário quando ele clicar nas células. Se a opção “Mostrar” for marcada, temos que inserir um Título para a Mensagem e digitá-la na caixa de baixo. Microsoft Excel 2013 85 Profissional QI Alerta de erro A guia Alerta de erro é a última guia a ser configurada. Nela, escolhemos qual será a caixa de diálogo de ERRO que será mostrada para os usuários da planilha. Temos três tipos de mensagens de erros para o usuário, são eles: Parar A opção parar não permite que entrem valores errados nas células de forma alguma. Enquanto não for digitado um valor permitido pela célula, sempre aparecerá a mensagem de erro. Escolhendo essa opção, sempre que o usuário digitar algum valor que a célula não permita aparecerá a seguinte tela: Para configurar devemos preencher 2 itens, são eles: “Título” e “Mensagem de erro”. Microsoft Excel 2013 86 Profissional QI 1. Título da mensagem de erro. 2. Mensagem de erro. Importante Escolhendo parar, a célula não aceitará o valor errado até que o usuário digite um valor válido, segundo as permissões da célula. Informações A opção “Informações” apenas informa quando o usuário digitar um valor errado na célula que foi validada. Escolhendo essa opção, sempre que o usuário digitar algum valor que a célula não permita aparecerá a seguinte tela: Se clicarmos no botão OK, a célula aceitará o valor que foi digitado mesmo sendo errado. Se clicarmos em Cancelar, a célula não aceitará o valor que foi digitado. Microsoft Excel 2013 87 Profissional QI Aviso A opção “Aviso” apenas informa quando o usuário digitar um valor errado na célula que foi validada. Escolhendo essa opção, sempre que o usuário digitar algum valor que a célula não permita aparecerá a seguinte tela: Nessa opção, a caixa de erro que aparece pergunta se desejamos continuar ou não. Clicando no botão “Sim”, a célula aceitará o valor que foi digitado mesmo que ele esteja errado. Se for clicado em “Não”, voltará para a célula esperando que seja digitado outro valor. Se por acaso for clicado em “Cancelar”, apagará o valor digitado como se nada tivesse acontecido. Vamos praticar Abra a planilha “Funcionários”. Apagar os valores da coluna “Salário” e “Turno” (conforme exemplo na figura ao lado); Em seguida, validar a coluna “Salário” (intervalo D5:D11), permitindo que o valor digitado seja somente maior do que R$ 250,00; Vamos fazer uma validação permitindo somente valores “Decimais” sendo só os “Maiores ou iguais a 250”. Vamos também criar uma “Mensagem de Entrada” e configurar o Alerta de erro, para ser exibida uma mensagem quando houver um erro na digitação. Validar a coluna “Turno”. Na opção “Permitir”, a seguir, vamos selecionar a opção “Lista”. Nela, será exibida a opção “Fonte:”, então digitaremos os seguintes valores: Diurno, Noturno, Integral; Vamos configurar uma “Mensagem de Entrada” e um “Alerta de erro”, para os possíveis erros de digitação. Em seguida, escolher os turnos clicando na coluna “Turno”; Completar as colunas “Salário” e “Turno”, testando a validação; Por último, salvar a planilha REFERÊNCIAS MICROSOFT OFFICE. <office.microsoft.com/pt-br/excel> novamente para atualizá-la. Microsoft Excel 2013 88 Profissional QI ATIVIDADES 1. Crie uma planilha conforme a figura abaixo, seguindo as mesmas formatações: a) Faça uma validação na coluna “Salário” permitindo que o valor digitado seja entre R$ 1.000,00 e R$ 2.500,00. Selecionando a coluna B, clique no grupo ferramentas de dados opção “Validação”; b) Faça uma validação na coluna INSS, permitindo que o valor digitado seja de 8% a 15%; c) Faça uma validação na coluna VT permitindo que o valor digitado seja maior ou igual a 6%; d) Faça uma validação na coluna “Hora Extra” permitindo que o valor digitado seja menor que R$ 150,00; e) Faça uma validação na coluna “Adicional Noturno” permitindo que o valor digitado seja maior que R$ 200,00; f) Faça uma validação na coluna Insalubridade ou periculosidade permitindo que o valor digitado seja de 10% a 40%; g) Calcule o salário líquido, que é a soma do salário, hora extra, adicional noturno e insalubridade ou periculosidade, diminuído pelo valor do INSS e do VT; h) Insira um filtro e filtre os dados da funcionária Patrícia; i) Aplique uma formatação condicional na coluna “Salário Líquido”, escolhendo a opção “Escalas de Cor”; j) Aplique outra formatação condicional na coluna Salário Líquido escolhendo agora a opção “Conjunto de Ícones”, marcando os Sinalizadores (as bandeirinhas); k) Salve a planilha com o nome de “Folha de Pagamento”. Microsoft Excel 2013 89 Profissional QI ANOTAÇÕES Microsoft Excel 2013 90 Profissional QI O QUE VAMOS APRENDER: AULA 10 Gráficos GRÁFICOS No Excel temos um importante recurso que é a criação de gráficos para apresentar, de outra forma, as informações de uma tabela. Temos inúmeros estilos de gráficos, todos com o mesmo propósito. Microsoft Excel 2013 91 Profissional QI Para criarmos um gráfico devemos seguir os seguintes passos: 1. Selecionamos que dados da planilha queremos que apareça no gráfico; Na figura acima selecionamos as colunas A (Nome do Funcionário) e a coluna C (Salário); vamos criar um gráfico mostrando somente o nome dos funcionários e seus respectivos salários. 2. Clicar na guia “Inserir”; 3. Clicar no tipo de gráfico desejado, no grupo “Gráficos”; no nosso exemplo utilizamos o tipo “Pizza”. Microsoft Excel 2013 92 Profissional QI Podemos escolher ainda outros tipos de gráficos clicando na seta expansora do grupo “Gráficos”. Ao clicarmos na seta expansora, o programa abrirá uma caixa de diálogo com duas opções: “Gráficos recomendados” e “Todos os Gráficos”. A opção “Todos os Gráficos” é dividida em duas partes: esquerda e direita. Na parte da esquerda encontram-se todos os tipos de Gráficos e na parte da direita podemos visualizar todos os subtipos de Gráfico escolhido na parte da esquerda. A observação é que os gráficos que são inseridos no Excel 2013 ficam na planilha onde foram selecionados os dados para o mesmo. Quando o gráfico fica na planilha onde está a tabela, falamos que ele foi salvo como OBJETO na planilha. Podemos escolher se queremos o gráfico como OBJETO na mesma planilha ou em nova PLANILHA exclusiva. Microsoft Excel 2013 93 Profissional QI DICA No Microsoft Excel podemos criar o gráfico selecionando o conteúdo que desejamos e pressionando apenas uma tecla. Vamos visualizar o exemplo a seguir: VALORES MONETÁRIOS dinheiro A partir desse exemplo vamos selecionar o que desejamos para criar o gráfico, conforme a figura abaixo: Após selecionar os dados devemos pressionar a tecla “F11”. O gráfico será criado automaticamente em uma nova planilha. Além disso, colocará o título e, nesse caso, o salário, conforme a figura abaixo: Microsoft Excel 2013 94 Profissional QI Vamos praticar Criar a planilha apresentada abaixo, com as suas respectivas formatações: Inserir um gráfico do tipo “Pizza 3D”, representando o valor do 13º salário a ser pago e os nomes dos funcionários; Em seguida, inserir um gráfico do tipo “Colunas”, representando o valor do 13º salário por Setores; Salvar essa planilha com o nome de “Décimo Terceiro”. DICA Após inserir o gráfico do tipo pizza, vamos escolher um layout para ele. VALORES MONETÁRIOS Para isso devemos dinheiro seguir os seguintes passos: 1. Selecionar o gráfico clicando nele; 2. Clicar na guia “Design”; 3. Em seguida, clicar na grupo “Layout Rápido” e escolher a opção “Layout 1”. Microsoft Excel 2013 95 Profissional QI 4. O gráfico ficará conforme a figura: Modificando o local do gráfico Para modificar e colocar o gráfico em uma nova planilha devemos seguir os seguintes passos: 1. Clicar com o botão direito do mouse em cima do gráfico; quando clicarmos em cima do gráfico aparecerá o menu da figura logo abaixo: Microsoft Excel 2013 96 Profissional QI 2. Clicar em “Mover Gráfico” - opção que o moverá para uma planilha separada, ficando assim melhor distribuído e com um bom tamanho; o programa abrirá uma caixa de diálogo chamada “Mover Gráfico”; 3. Clicar na opção “Nova planilha”; lá será habilitado o espaço para colocar o nome do gráfico; 4. Digitar o nome da planilha onde o gráfico ficará; 5. Em seguida, clicar no botão ok; o gráfico será movido para a nova planilha. Microsoft Excel 2013 97 Profissional QI Vamos praticar Abrir a planilha “Décimo Terceiro”; Modificar os dois gráficos criados anteriormente para que fiquem em planilhas separadas; Salvar a planilha novamente para atualizar a alteração. REFERÊNCIAS MICROSOFT OFFICE. <office.microsoft.com/pt-br/excel> ATIVIDADES 1. Desenvolva uma planilha como a VALORES MONETÁRIOS dinheiro representada abaixo: a) Faça uma “Validação de Células” do tipo “Lista” na coluna Setores com os seguintes itens: RH; CPD; Financeiro; Compras; Vendas; Administração; b) Após validar as células, preencha conforme a planilha; c) Crie um gráfico de pizza que mostre a quantidade de funcionários por setor; d) Crie um gráfico de barras 3D que mostre a quantidade de funcionários que entrarão em férias por setor; e) Mova o segundo gráfico uma planilha separada; f) Faça uma Formatação Condicional de sua escolha nas colunas “B” e C”; g) Salve a planilha com o nome de “Funcionários de Férias”; Microsoft Excel 2013 98 Profissional QI ANOTAÇÕES Microsoft Excel 2013 99 Profissional QI AULA 11 REVISÃO DICA Preocupe-se em fazer primeiro as fórmulas com os respectivos cálculos, deixando para o final a parte de bordas, formatações, sombreamento e alinhamento de células. VALORES MONETÁRIOS dinheiro Microsoft Excel 2013 100 Profissional QI LEIA ATENTAMENTE TODAS AS QUESTÕES: 1. Abra o Excel e digite a planilha conforme a figura. 2. Na coluna “Nome”, utilize a fórmula “procv” para buscar o nome dos funcionários na tabela “Funcionários”; 3. Na coluna “Sexo”, faça uma validação de células do tipo "Lista" com as opções Masc e Fem; 4. Obs.: Separe uma opção da outra com ";" (ponto e vírgula); 5. Na coluna “Setor”, utilize a função “procv” para buscar o setor dos funcionários na tabela “Funcionários”; 6. Na coluna “Salário Bruto” também utilize a função “procv” para buscar o salário dos funcionários na tabela “Funcionários”; 7. Na coluna “INSS” busque através da função “procv “o desconto de INSS na tabela “INSS”; 8. Em “Desc. INSS” faça um cálculo do salário bruto vezes a coluna de %INSS, menos o salário bruto do funcionário; 9. Em “Férias” calcule as dos funcionários do setor "Adm". Se for Adm, o cálculo será o salário bruto acrescido de um terço 1/3 do salário bruto do funcionário, caso contrário, zero (0); 10. Na coluna “Salário Final” calcule (através da fórmula soma) a soma da coluna “Férias” com a coluna “Salário Final”; 11. Na tabela “Totais” faça a soma, a média e ache o menor e o maior valor das colunas “Férias” e “Salário Final”; 12. Na tabela “Total de Salários por Setores” calcule, através da função “somase”, os totais dos setores: Adm. e Diretoria; 13. Calcule também a quantidade de Funcionários do setor Administrativo e da Diretoria utilizando a fórmula cont.se(); 14. Faça uma Formatação Condicional do tipo “Barra de Dados” na coluna “Salário Final”; 15. Faça um gráfico de sua escolha, exibindo os totais dos “Salários por Setor” (crie o gráfico na própria planilha, como objeto); 16. Faça outro gráfico do tipo "Colunas", exibindo os nomes dos Funcionários e seus Salários Brutos e finais; Microsoft Excel 2013 101 Profissional QI 17. Faça uma Formatação Condicional na coluna “Férias” do tipo "Conjuntos de Ícones", opção "3 Símbolos (Não Circulados)"; 18. Insira um Filtro somente na planilha FOLHA DE PAGAMENTO (A3:I11); 19. Filtre a planilha para mostrar somente às mulheres (Sexo: fem) do setor Administrativo (adm); 20. Renomeie a Planilha para "FOLHA DE PAGAMENTO"; 21. Salve a planilha com o nome de "REVISÃO FINAL". ANOTAÇÕES Microsoft Excel 2013 102 Profissional QI AULA 12 AVALIAÇÃO FINAL LEIA ATENTAMENTE TODAS AS QUESTÕES 1. Abra o Excel e digite a planilha conforme a figura: 2. Na coluna “Vale Transporte” faça uma validação aceitando somente "S" ou "N". 3. Na coluna “Valor do Vale Transporte”, calcule-o com base na tabela "Percentuais para Cálculos". 4. Na coluna INSS calcule o valor do INSS, com base na tabela "Percentuais para Cálculos". Microsoft Excel 2013 103 Profissional QI 5. Na coluna “Bonificação”, calcule a bonificação somente para os funcionários com salário maior que R$ 1.000,00. Use a tabela "Percentuais para Cálculos" e função "SE". 6. Na coluna “Salário Líquido”, calcule o total dos salários líquidos de cada funcionário. 7. Calcule o valor médio da coluna “Salário Líquido”. 8. Calcule o maior valor da coluna “Salário Líquido”. 9. Calcule a quantidade de funcionários listados na folha de pagamento. 10. Calcule a soma dos salários líquidos dos funcionários que tenham mais de 2 filhos. QUESTÃO BÔNUS Na folha de pagamento individual, faça “procv” para que quando digitarmos um código de um determinado funcionário sejam mostradas às informações do mesmo. ANOTAÇÕES Microsoft Excel 2013 104