Excel Avançado 2ª Edição Antonio Fernando Cinto Wilson Moraes Góes
Novatec
Copyright © 2015 da Novatec Editora Ltda. Todos os direitos reservados e protegidos pela Lei 9.610 de 19/02/1998. É proibida a reprodução desta obra, mesmo parcial, por qualquer processo, sem prévia autorização, por escrito, do autor e da Editora. Editor: Rubens Prates Capa: Casa de Tipos Revisão gramatical: Wilton Vidal de Lima Editoração eletrônica: Carolina Kuwabata Assistente editorial: Priscila A. Yoshimatsu ISBN: 978-85-7522-426-7 IG20150319 Histórico de impressões: Março/2015 Janeiro/2013 Novembro/2011 Abril/2011 Maio/2010 Setembro/2009 Outubro/2008 Setembro/2007 Junho/2006 Novembro/2005
Segunda edição Oitava reimpressão Sétima reimpressão Sexta reimpressão Quinta reimpressão Quarta reimpressão Terceira reimpressão Segunda reimpressão Primeira reimpressão Primeira edição (ISBN: 85-7522-080-2)
Novatec Editora Ltda. Rua Luís Antônio dos Santos 110 02460-000 – São Paulo, SP – Brasil Tel.: +55 11 2959-6529 Email: novatec@novatec.com.br Site: www.novatec.com.br Twitter: twitter.com/novateceditora Facebook: facebook.com/novatec LinkedIn: linkedin.com/in/novatec IG20150319
capítulo 1
Comandos e funções
No primeiro capítulo, abordaremos um estudo de caso em que uma empresa do segmento agrícola deseja prever suas vendas para o próximo ano. Para tal, utilizaremos funções e recursos, como: validação de dados, média, máximo, mínimo, desvio-padrão, função SE e formatação condicional.
1.1 Validação de dados Neste exemplo hipotético, teremos como cenário uma empresa fornecedora de produtos para o segmento agrícola que deseja prever o comportamento de venda de seus produtos para o próximo ano. Diante disso, seus gerentes optaram pela construção da planilha da figura 1.1.
Figura 1.1 – Planilha de produtos.
16
Capítulo 1 ■ Comandos e funções
17
Construa o layout da figura 1.1, digite o nome dos produtos, bem como as médias de venda do ano (2012) anterior. Depois da construção do layout e com o intuito de prevenir a digitação de dados inconsistentes nos quatro trimestres, devemos, então, validar a entrada de dados neste intervalo de células. Selecione o intervalo de células (C6 a F11). Clique na Guia Dados e no botão Validação de dados. Preencha a caixa de diálogo Validação de dados conforme as indicações a seguir. Clique na guia Configurações (Figura 1.2).
Figura 1.2 – Validação de dados: Configurações.
Campo Ação Permitir: Selecione: Número inteiro Dados: Selecione: entre Mínimo: Digite: 0 Máximo: Digite: 500
Clique na guia Mensagem de entrada (Figura 1.3).
18
Excel Avançado – 2ª Edição
Figura 1.3 – Validação de dados: Mensagem de entrada.
Campo Ação Título: Digite: Ajuda. Mensagem de entrada: Digite a frase: Digite um valor entre 0 e 500
Clique na guia Alerta de erro (Figura 1.4).
Figura 1.4 – Validação de dados: Alerta de erro.
Capítulo 1 ■ Comandos e funções
19
Campo Ação Estilo: Selecione: Parar Título: Digite: Erro Mensagem de erro: Digite: Número fora dos limites
Clique em OK. Agora vamos testar a validação digitando as quantidades trimestrais, como na figura 1.5. Note que se você digitar um valor fora do intervalo permitido (0 a 500), receberá uma mensagem de erro.
Figura 1.5 – Digitando as quantidades vendidas.
1.2 Média, máximo, mínimo e desvio-padrão Depois da validação, chegou o momento de calcularmos a média, máximo, mínimo e desvio-padrão.
1.2.1 Média Vamos verificar a média das vendas por produto. 1. Clique na célula G6 e digite =MÉDIA(. 2. Selecione com o mouse o intervalo C6:F6 e pressione Enter. Note que não é necessário fechar o parêntese, visto que o Excel faz isso de maneira automática.
20
Excel Avançado – 2ª Edição
3. Depois é só usar a função de Autopreenchimento (Figura 1.6) para aplicar a fórmula nas demais células.
Figura 1.6 – Autopreenchimento.
1.2.2 Máximo Vamos verificar a maior venda por produto. 1. Clique na célula H6 e digite =MÁXIMO(. 2. Selecione o intervalo C6:F6 e pressione Enter. 3. Repita a função de Autopreenchimento conforme exemplificado na figura 1.6.
1.2.3 Mínimo Vamos verificar a menor venda por produto. 1. Clique na célula I6 e digite =MÍN(. 2. Selecione o intervalo C6:F6 e pressione Enter. 3. Repita a função de Autopreenchimento conforme exemplificado na figura 1.6.
Capítulo 1 ■ Comandos e funções
21
1.2.4 Desvio-padrão Desvio-padrão é uma medida do grau de dispersão dos valores em relação à média. Temos duas fórmulas para calcular o desvio-padrão: • DESVPAD.A – Calcula o desvio-padrão de uma amostra. • DESVPAD.P – Calcula o desvio-padrão da população total. Vamos calcular o desvio-padrão das vendas por produto. 1. Clique na célula J6 e digite =DESVPAD.P(. 2. Selecione o intervalo C6:F6 e pressione Enter. 3. Repita a função de Autopreenchimento conforme exemplificado na figura 1.6.
1.3 Função SE Agora, com o auxílio da função lógica SE, vamos avaliar se a média das vendas de 2012 foi superior ou inferior à das vendas de 2013 acrescidas da expectativa de crescimento (10%). Caso a resposta seja positiva, a função deverá retornar à palavra Superior e, caso seja negativa, a função deverá retornar à palavra Inferior. Posicione o cursor na célula K6, na guia Fórmulas clique em Inserir Função... A caixa de diálogo Inserir função (Figura 1.7) será aberta. Em Categoria, selecione Tudo.
Figura 1.7 – Inserir função.
22
Excel Avançado – 2ª Edição
Em seguida, role a barra de rolagem da caixa Selecione uma Função até encontrar a função lógica SE. Depois de selecionar a função SE, clique na opção OK da caixa de diálogo e siga os passos como exemplificado na figura 1.8.
Figura 1.8 – Preenchimento da função SE.
Campo Ação Teste_lógico Digite: B6+B6*$C$3/100>G6 Valor_se_verdadeiro Digite: Superior Valor_se_falso Digite: Inferior
Em que: • B6 é a média das vendas de 2012. • G6 é a média das vendas de 2013. • B6+B6*$C$3/100 é o cálculo da porcentagem da média das vendas de 2012 acrescidas de 10%. Note que colocamos $ antes e depois de C ($C$3), dado que esse símbolo serve para travar a célula, ou seja, ela passa a ter uma referência absoluta, e, sendo assim, quando utilizarmos a alça de preenchimento, o endereço da célula (C3) não será alterado. Para colocarmos o sinal $, ou seja, travar a célula, basta, após a digitação do endereço da célula (C3), pressionar a função F4 na parte superior do teclado. Clique em OK.
Capítulo 1 ■ Comandos e funções
23
1.4 Formatação condicional Em seguida, vamos formatar um intervalo baseado em condições. 1. Selecione o intervalo K6:K11. 2. Na guia Página Inicial, clique em Formatação condicional e selecione Gerenciar Regras... 3. Na caixa de diálogo Gerenciador de Regras de Formatação Condicional clique no botão Nova Regra, preencha a caixa de diálogo Nova Regra de Formatação como na figura 1.9.
Figura 1.9 – Nova Regra de Formatação.
Campo Ação Tipo de regra Selecione: Formatar apenas células que contenham Descrição da regra Selecione: O valor da célula é Selecione: igual a Digite: Superior Clique no botão Formatar... e selecione a cor azul Clique no botão OK
Para inserir a próxima condição, clique novamente no botão Nova regra e preencha a caixa de diálogo Nova Regra de Formatação do seguinte modo:
24
Excel Avançado – 2ª Edição Campo Ação Tipo de regra Selecione: Formatar apenas células que contenham Descrição da regra Selecione: O valor da célula é Selecione: igual a Digite: Inferior Clique no botão Formatar... e selecione a cor vermelha Clique no botão OK
Clique em OK. O Gerenciador de Regras de Formatação Condicional deve estar como a figura 1.10.
Figura 1.10 – Gerenciador de Regras de Formatação Condicional.
Agora só nos resta formatar os dados numéricos com nenhuma casa decimal. Selecione o intervalo de células B6:J11, clique com o botão direito no intervalo selecionado e escolha a opção Formatar células...; na caixa de diálogo Formatar Células, clique na guia Número e preencha os campos como na figura 1.11.
Capítulo 1 ■ Comandos e funções
25
Figura 1.11 – Formatar Células.
Campo Ação Categoria Selecione: Número Casas decimais Digite: 0
Pronto. Sua planilha ficou igual à da figura 1.12:
Figura 1.12 – Planilha pronta.