Baixe o app para aproveitar ainda mais
Prévia do material em texto
Microsoft Excel Intermediário 2016 1 Apostila de Excel Intermediário 2016 Microsoft Excel Intermediário 2016 2 Índice 1. FORMATAÇÃO CONDICIONAL ................................................................................................ 4 2. VALIDAÇÃO DE DADOS ........................................................................................................... 6 2.1. Validação da turma ......................................................................................................... 7 2.2. Validação Do Nome ....................................................................................................... 12 2.3. Validação Da Data De Nascimento ................................................................................ 14 2.4. Validação Da Idade ........................................................................................................ 15 2.5. Validação Da Data De Contratação ............................................................................... 15 2.6. Validação do CPF ........................................................................................................... 16 2.7. Validação da Situação.................................................................................................... 16 3. CONGELAR PAINÉIS .............................................................................................................. 17 4. PREENCHENDO CÉLULAS AUTOMATICAMENTE................................................................... 18 5. FORMATAÇÕES AUTOMÁTICAS DE DATA E HORA ............................................................... 19 5.1. Personalizando números ............................................................................................... 19 6. REFERÊNCIAS RELATIVAS ...................................................................................................... 20 7. REFERÊNCIAS ABSOLUTAS .................................................................................................... 21 8. DATA E HORA........................................................................................................................ 23 8.1. Fazendo cálculos com datas .......................................................................................... 24 8.2. Fazendo cálculos com hora ........................................................................................... 24 8.3. Fazendo cálculos com data e hora ................................................................................ 24 9. DIA TRABALHO TOTAL .......................................................................................................... 26 10. DIA TRABALHO.................................................................................................................... 27 11. FIM MÊS .............................................................................................................................. 28 12. SE ........................................................................................................................................ 29 12.1. Exemplo 1 .................................................................................................................... 29 12.2. Exemplo 2 .................................................................................................................... 30 12.3. Exemplo 3 .................................................................................................................... 31 13. SEERRO ............................................................................................................................... 32 14. FUNÇÕES ANINHADAS ........................................................................................................ 33 14.1 Se 1 ............................................................................................................................... 34 14.2. Se 2 .............................................................................................................................. 35 14.3. Se E .............................................................................................................................. 36 14.4. Se Ou ........................................................................................................................... 37 15. CTRL+ENTER ....................................................................................................................... 38 Microsoft Excel Intermediário 2016 3 15.1 Exemplo 1 ..................................................................................................................... 38 15.2 Exemplo 2: .................................................................................................................... 40 Autoria ...................................................................................................................................... 43 Bibliografia................................................................................................................................ 44 Microsoft Excel Intermediário 2016 4 1. FORMATAÇÃO CONDICIONAL No Excel existem diferentes ferramentas que permitem formatar células, alterando, cor da fonte, bordas, preenchimento etc. A partir de agora você conhecerá maneiras de formatar as células automaticamente, com base em uma condição preestabelecida. Usaremos uma tabela com lista de pendências: As células quem contém o prazo ficarão coloridas numa escala de: ➢ vermelho – para as datas mais próximas da atual até ➢ verde – para as datas mais distantes. Para isso, selecione as células referentes ao prazo, de B3 até B10. Após isso, vá até a guia Início e depois em Formatação Condicional. Nesse botão, escolha Escalas de Cor e selecione o primeiro item, chamado Escalas de Cores Verde – Amarela – Vermelha. Microsoft Excel Intermediário 2016 5 Depois disso é só classificar a coluna B do Mais Antigo para o Mais Novo. Teremos o seguinte resultado: Microsoft Excel Intermediário 2016 6 2. VALIDAÇÃO DE DADOS A validação de dados é um recurso do Excel que você pode usar para definir quais dados podem ou devem ser inseridos em uma célula. Você pode configurar a validação de dados para impedir que os usuários insiram dados errados. Se preferir, pode criar mensagens informando que dados incorretos foram inseridos. Também pode criar mensagens de instruções para ajudar os usuários. Exemplo: Vamos validar a seguinte planilha: Microsoft Excel Intermediário 2016 7 ➢ Turma: Só pode conter os valores de J até Q; ➢ Nome: Mínimo de cinco caracteres; ➢ Data Nascimento: maior que 1989; ➢ Idade: Será calculado automaticamente; ➢ Data Contratação: Não pode ser maior que a data atual; ➢ CPF: Deve conter exatamente 11 caracteres; ➢ Situação: Ativo e Desligado. 2.1. Validação da turma Selecione as células que serão validadas. No primeiro caso, será a primeira coluna, o intervalo B3:B9. Com as células selecionadas, clique na guia Dados e selecione Validação de Dados no grupo Ferramentas de Dados. A seguinte tela aparecerá: Microsoft Excel Intermediário 2016 8 Temos três abas: ➢ Configurações: Onde definimos os critérios de validação com os itens que serão permitidos. Microsoft Excel Intermediário 2016 9 ➢ Mensagem de entrada: Onde definimos a mensagem que aparecerá ao selecionar a célula validada. ➢ Alerta de erro: Onde definimos uma mensagem quando inserem dados inválidos. Voltemos à primeira aba, Configurações. Como a coluna Turma só pode ser um texto com valores entre J e Q, faremos o seguinte: Microsoft Excel Intermediário 2016 10 Obs.: Os itens do campo Fonte deverão ser separados por; (ponto e vírgula) Na próxima aba definiremos a Mensagem de entrada. Microsoft Excel Intermediário 2016 11 E na última definiremos a mensagem de alerta caso os dados tenham sido inseridos de maneira incorreta. A planilha ficará assim: Caso algum valor esteja errado: Microsoft Excel Intermediário 2016 12 2.2. Validação Do Nome Selecione as células referentes à coluna Nome. Vá até a guia Dados e clique em Validação de Dados. Preencha a seguinte desta maneira: Microsoft Excel Intermediário 2016 13 Ficará desta maneira: Microsoft Excel Intermediário 2016 14 2.3. Validação Da Data De Nascimento É necessário inserir a data no formato dia/mês/ano. Obs.: A data de início deve ser completa, com dia, mês e ano (este, preferencialmente com quatro números, como 2019 ao invés de 19) Microsoft Excel Intermediário 2016 15 2.4. Validação Da Idade Nesse caso, vamos usar uma fórmula para calcular automaticamente a idade baseada na célula que contém a data de nascimento. =INT((HOJE()-D3)/365,25) Obs.: D3 é a célula que contém a data de nascimento. Altere-a, caso necessário. 2.5. Validação Da Data De Contratação Obs.: Veremos o funcionamento da função =HOJE() posteriormente. Microsoft Excel Intermediário 2016 16 2.6. Validação do CPF 2.7. Validação da Situação Microsoft Excel Intermediário 2016 17 3. CONGELAR PAINÉIS Esse Recurso trava linhas e/ou colunas especificas no Excel. Ao rolar a planilha para baixo ou para o lado, as colunas e linhas congeladas sempre estarão visíveis na tela. Para isso, clique na guia Exibição e depois em Congelar Painéis, e por fim no item Congelar Painéis. Na planilha, siga um destes procedimentos: ➢ Para bloquear linhas, selecione a linha abaixo da qual deseja que a divisão apareça. ➢ Para bloquear colunas, selecione a coluna à direita da qual deseja que a divisão apareça. ➢ Para bloquear tanto linhas quanto colunas, clique na célula abaixo e à direita de onde deseja que a divisão apareça. Para bloquear a primeira linha e as três primeiras colunas, mantendo o cabeçalho e o nome do aprendiz visíveis, clique na célula da segunda linha (que é a número 3 na nossa planilha) e da quarta coluna (D), a D3. Microsoft Excel Intermediário 2016 18 Depois siga o procedimento informado anteriormente: clique na Guia Exibição, depois em Congelar Painéis e no botão Congelar Painéis. 4. PREENCHENDO CÉLULAS AUTOMATICAMENTE Às vezes é necessário preencher um grupo de células com valores sequenciais. Por exemplo, com números de 1 a 100, dias da semana, meses do ano etc. O Excel possui um recurso que permite definir um ou dois valores e os demais serão preenchidos automaticamente. Vamos ver um exemplo: 1. Digite e selecione os dados, conforme mostra a figura abaixo. 2. Clique na alça de preenchimento. Microsoft Excel Intermediário 2016 19 3. Arraste o mouse até a linha 10 e observe que os números são preenchidos automaticamente. 4. Solte o mouse e veja o resultado 5. FORMATAÇÕES AUTOMÁTICAS DE DATA E HORA Através da opção de personalizar é possível utilizar uma formatação de exibição que não esteja pré-definida pelo Excel, ou seja, caso não encontre uma formatação que atenda a sua necessidade, talvez seja necessário personalizar/criar uma. 5.1. Personalizando números Formatos personalizados de célula permitem que você configure a exibição de valores como desejar. Para que possamos personalizar os números de nossas células, devemos clicar na guia Início, grupo Números, selecionar Mais Formatos de Números e depois no item Personalizado, como mostram as figuras. Microsoft Excel Intermediário 2016 20 Ou clicar com o botão direito do mouse na célula ou intervalo desejado, depois clique em Propriedades. Nesse campo, digite as seguintes combinações: ➢ Hora: 00”:”00 ➢ Data: 00”/”00”/”0000 ➢ CPF: 000”.”000”.”000”-“00 6. REFERÊNCIAS RELATIVAS Ocorrem quando o cálculo é realizado com valores que estão na mesma coluna, porém em linhas diferentes. Microsoft Excel Intermediário 2016 21 Exemplo: =C3*D3 Vamos multiplicar o conteúdo de C3 pelo conteúdo de D3. Arrastando pela alça no canto da célula, o próximo cálculo seria =C4*D4 e assim por diante. As colunas permanecem as mesmas, mudam apenas a linha. Então o cálculo é realizado com relação à linha selecionada. 7. REFERÊNCIAS ABSOLUTAS Quando é necessário realizar um cálculo com uma lista de valores na planilha e todos sendo multiplicados ou divididos por um valor constante apenas em uma só célula. Microsoft Excel Intermediário 2016 22 Exemplo: Uma referência absoluta de célula em uma fórmula, como $F$3, sempre se refere a uma mesma célula em um local específico. Se a posição da célula que contém a fórmula se alterar, a referência absoluta permanecerá a mesma. Se você copiar a fórmula ao longo de linhas ou colunas, a referência absoluta não mudará. ➢ COMISSÃO Na primeira linha funciona, mas no momento em que arrastarmos o cursor para o cálculo das linhas logo abaixo, o Excel muda os endereços e cria as novas fórmulas =B4*F4, =B5*F5 e assim por diante. Veja o que há nos endereços F4 e F5. Outros valores ou textos. Então como informar o Excel que o endereço F3 tem que permanecer fixo? Ao invés de digitar =B3*F3, use o símbolo do cifrão ($) na fórmula. Coloque o cifrão na célula que ficará fixa no cálculo. A nova fórmula será =B3*$F$3 Isso torna o endereço F3 uma Referência Absoluta. Microsoft Excel Intermediário 2016 23 ➢ SALÁRIO É a comissão + salário Notem que ao arrastar a fórmula o mesmo problema ocorre. Temos que travar a célula que contém o salário. =C3+$F$5 A planilha ficará assim 8. DATA E HORA Alguma vez, você já se deparou com a seguinte situação: “O primeiro pagamento será daqui há a 180 dias. Como calcular a data do vencimento?” No Excel isso é muito simples, pois basta somarmos à data atual o valor 180. Isso é possível pois cada data inserida em uma planilha é um número serial. Por exemplo, a data 01/02/2015 é equivalente ao número serial 41306. Para visualizar esse número, altere o formato da data para Geral. A contagem inicia-se pela data 01/01/1900, que corresponde ao número serial 1, e a data limite é 31/12/9999, cujo número serial é 2.958.465. Microsoft Excel Intermediário 2016 24 8.1. Fazendo cálculos com datas Veja que simples resolver a situação do pagamento dentro de 180 dias. Numa planilha digite a data de hoje na célula A2. Na célula B2, digite a fórmula =A2+180. 8.2. Fazendo cálculos com hora Para saber o tempo decorrido entre um horário e outro, basta subtrair um de outro. Se estiver registrado 18:45 na célula B1 e 11:10 na célula B2, a fórmula =B1-B2 retornará 7:25, que é a diferença entre os horários 8.3. Fazendo cálculos com data e hora Agora, que você já sabe como o Excel interpreta datas e horas, vejamos o procedimento para trabalhar com ambas em uma mesma fórmula. Vamos supor que, a Microsoft Excel Intermediário 2016 25 serviço, você vá viajar de carro com saída prevista para o dia 20/11/19, às 8h, e chegada para o dia 23/11/19, às 14h. 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. =(C3-A3)+(D3-B3) Para visualizar o total de horas, altere o formato para [h]:mm, conforme mostra a próxima imagem. Microsoft Excel Intermediário 2016 26 9. DIA TRABALHO TOTAL Saber quantos dias úteishá em um período de tempo é útil para escritórios que precisam descobrir o número de dias a serem trabalhados em determinado mês e calcular o número de vales-transportes que cada funcionário deverá receber naquele mês. A imagem abaixo ilustra como deverá ser feita a fórmula. Na célula B4 insira a fórmula =DIATRABALHOTOTAL(B2;B3;H2:H9) A fórmula utilizada exclui do cálculo os sábados e os domingos, o que elimina a necessidade de fazê-lo manualmente. Você só precisa retirar manualmente do cálculo os feriados, incluindo-os no último campo da fórmula. Microsoft Excel Intermediário 2016 27 A fórmula DIATRABALHOTOTAL também funciona para meses e anos diferentes. Por exemplo, você pode utilizá-la para verificar quantos dias você trabalhou desde a sua admissão na empresa até a data atual. 10. DIA TRABALHO Retorna a data final a partir de uma data inicial e quantidade de dias somados. Os dias úteis excluem fins de semana e quaisquer datas identificadas como feriados. Use DIATRABALHO para excluir os fins de semana ou feriados ao calcular as datas de vencimento de fatura ou o número de dias de trabalho executado. Sintaxe =DIATRABALHO(DATA_INICIAL;DIAS;FERIADOS) ➢ DATA_INICIAL: é uma data que representa a data inicial. ➢ DIAS: é o número de dias úteis antes ou depois de data_inicial. ➢ FERIADOS: é uma lista opcional com uma ou mais datas a serem excluídas do calendário de trabalho, como feriados municipais, estaduais ou federais. Na célula B5, digite a seguinte fórmula: =DIATRABALHO(B3;B4;F2:F9) Microsoft Excel Intermediário 2016 28 11. FIM MÊS Retorna a data do último dia do mês antes ou depois de um dado número de meses. Use FIMMÊS para calcular as datas de vencimento que caem no último dia do mês. =FIMMÊS(DATA_INICIAL;MESES) ➢ DATA_INICIAL: uma data que representa a data de início. ➢ MESES: é o número de meses antes ou depois de data_inicial. Microsoft Excel Intermediário 2016 29 12. SE A função SE é utilizada para fazer testes condicionais de fórmulas e valores de células. =SE(teste_lógico;[valor_se_verdadeiro];[valor_se_falso]) Esta função retorna o valor definido no parâmetro valor_se_verdadeiro se o resultado do teste for verdadeiro ou o valor definido no parâmetro valor_se_falso se o resultado do teste for falso. 12.1. Exemplo 1 =SE(A2<0;”Negativo”;”Positivo”) Se o teste_lógico A2<0 for verdadeiro, então retornará dentro da célula o valor Negativo. Se o teste_lógico A2<0 for falso, então retornará dentro da célula o valor Positivo. Microsoft Excel Intermediário 2016 30 12.2. Exemplo 2 Neste exemplo, pagaremos uma comissão de 10% a um vendedor se as vendas ultrapassarem R$ 10.000,00, caso contrário, somente 5%. Microsoft Excel Intermediário 2016 31 Na célula B3 consta o total de vendas do mês de janeiro e em C3 vamos incluir a fórmula para calcular o valor da comissão. =SE(B3>10000;10%;5%) 12.3. Exemplo 3 =SE(A3>B3;”Acima do orçamento”;”OK”) Se o teste_lógico A3>B3 for verdadeiro, então retornará dentro da célula a frase Acima do orçamento. Se o teste_lógico A3>B3 for falso, então retornará dentro da célula a palavra OK. Microsoft Excel Intermediário 2016 32 13. SEERRO A Função SEERRO verifica se uma expressão ou valor resulta em um erro padrão do Excel. Se for, o resultado será um valor alternativo (valor_se_erro), caso contrário o resultado será a própria conta. =SEERRO(VALOR; VALOR_SE_ERRO) ➢ VALOR: Pode se referir a uma célula ou fórmula que será testada; ➢ VALOR_SE_ERRO: Valor ou expressão a ser retornada se o argumento inicial retornar um erro. Tratar um erro significa dar opções de correções para uma fórmula que inevitavelmente irá retornar um erro como resposta. No exemplo abaixo a coluna D calcula o percentual que o frete representou em relação ao valor da nota fiscal. A célula D2 contém a fórmula: =C2/B2 Observe que nas linhas onde não existem lançamentos a coluna D retorna o erro #DIV!. No Excel, uma célula vazia como em B5, B6 e B7 é considerado zero, e não é possível dividir um número por zero. Microsoft Excel Intermediário 2016 33 Para corrigir o erro substitua a fórmula da célula D2 por esta: =SEERRO(C2/B2;"") e arraste para as outras células da coluna. O Excel interpreta a fórmula da seguinte forma: Se houver um erro em C2/B2 deixe a célula vazia (""), caso contrário retorne a divisão de C2/B2. 14. FUNÇÕES ANINHADAS Funções Aninhadas são quando inserimos mais de uma função dentro da função já iniciada, igual fizemos no cálculo de idade no item Validação de Dados. Microsoft Excel Intermediário 2016 34 14.1 Se 1 Neste caso, como temos mais de duas opções de resposta, teremos que usar uma função Se dentro da outra. =SE(F2>=7;"APROVADO";SE(F2<5;"REPROVADO";"EXAME")) Microsoft Excel Intermediário 2016 35 14.2. Se 2 =SE(B2>=90;"A";SE(B2>=80;"B";SE(B2>=70;"C";"D"))) Microsoft Excel Intermediário 2016 36 14.3. Se E Imagine que é professor e tem a idade e altura dos seus alunos. Agora como vai haver uma competição de basquete só para quem tem 18 ou mais, gostaria de saber quem pode ir e quais são os mais altos, digamos uma altura de 1,50m ou mais. Estas funções poderão ajudar! =SE(E(B2>=18;C2>=1,5);"Participará";"Não Participará") Como pode ver na imagem acima na célula B2 está a idade do jogador e na C2 a altura. Depois é só utilizar a função SE e E, colocando a condição de que B2 tem que ser superior ou igual a 18 e a C2 superior ou igual a 1,5, e colocar, em primeiro lugar, o texto que deverá ser mostrado caso as duas condições sejam verdadeiras. E bastará uma delas não ser verdadeira para que seja falsa a condição e aí irá mostrar o texto Não competirá. Agora pode ver os resultados finais: o Márcio reúne todas as condições, ao contrário do João que para além de ter menos de 15 anos também não tem 1,70m de altura. Microsoft Excel Intermediário 2016 37 14.4. Se Ou Tem o mesmo funcionamento da função SE E, porém, basta um argumento ser verdadeiro para que o primeiro argumento da função SE seja executado. =SE(OU(B3>=3;C3>1,55);"Sim";"Não") Microsoft Excel Intermediário 2016 38 15. CTRL+ENTER No Excel este atalho é muito útil para preencher várias células de uma única vez. 15.1 Exemplo 1 Na planilha abaixo temos o número de vendas de produtos por filial. Em algumas filiais, alguns produtos não tiveram vendas. Precisamos preencher as células vazias com o número zero. 1. Selecione o intervalo de células que serão preenchidos. Microsoft Excel Intermediário 2016 39 2. Agora vamos selecionar dentro deste intervalo as células em branco. Para isso iremos usar o comando Ir Para Especial (tecla F5), opção Em branco. Com isso, teremos a seguinte seleção: 3. Para terminar, digite o número 0 na célula ativa. Microsoft Excel Intermediário 2016 40 4. Para preencher todas as células selecionadas com o mesmo valor Ctrl + Enter. 15.2 Exemplo 2: Microsoft Excel Intermediário 2016 41 No exemplo, temos um pequeno cadastro de pedidos que mostra o Estado em que foi feito o pedido, o vendedor e o Valor. O Estado e o Vendedor aparecem cadastrados apenas a primeira vez que aparecem. Precisamos fazer um relatório que demonstre o valor faturado por Estado e/ou por Vendedor. Para isso temos que preencher todas as células vazias com seus respectivos Estados e Tipos. Vamos utilizar o mesmo aprendizado do Exemplo anterior. 1. Selecionar o intervalo a ser preenchido. 2. Dentro deste intervalo vamos selecionaras células em branco, para isso iremos usar o comando Ir para, Especial (tecla F5) , escolha a Opção, Em branco. Microsoft Excel Intermediário 2016 42 O Excel deixará dentro do bloco uma célula ativa (a primeira célula vazia a direita da área selecionada). Nessa célula deveremos copiar o conteúdo da célula de cima com uma fórmula. Basta digitar o sinal de = (igual) para começar a fórmula e depois clique na célula acima para fazer referência a célula de cima que está preenchido com Pedro. Depois é só digitar Ctrl+Enter. http://ontraining.com.br/wp-content/uploads/2013/05/IMG13.png http://ontraining.com.br/wp-content/uploads/2013/05/IMG14.png Microsoft Excel Intermediário 2016 43 Autoria Apostila desenvolvida pela equipe da Alt M Treinamentos. Para mais informações acesse: www.altmtreinamentos.com.br http://www.altmtreinamentos.com.br/ Microsoft Excel Intermediário 2016 44 Bibliografia www.aprenderexcel.com.br www.baboo.com.br/tutorial www.excelavancado.com.br support.office.com www.elirodrigues.com tudosobreexcel.wordpress.com blog.luz.vc www.ev.org.br
Compartilhar