Excel Avançado
Antonio Fernando Cinto
Wilson Moraes Góes
Novatec Editora
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.
Construa o layout da figura 1.1, digite o nome dos produtos, bem como as médias
de venda do ano (2003) 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.
Figura 1.1 – Planilha de produtos.
13
14
Excel Avançado
Selecione o intervalo de células (C6 a F11). Clique em Dados e selecione Validação...
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).
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
15
Capítulo 1 • Comandos e funções
Clique na guia Alerta de erro (Figura 1.4).
Figura 1.4 – Validação de dados: Alerta de erro.
Campo
Ação
Estilo:
Selecione: Parar
Título:
Mensagem de erro:
Digite: 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.
16
Excel Avançado
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.
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.
Capítulo 1 • Comandos e funções
17
1.2.3 Mínimo
Vamos verificar a menor venda por produto.
1. Clique na célula I6 e digite =MÍNIMO(.
2. Selecione o intervalo C6:F6 e pressione Enter.
3. Repita a função de AutoPreenchimento conforme exemplificado na figura 1.6.
1.2.4 Desvio padrão
Desvio padrão é uma medida do grau de dispersão dos valores em relação à média.
Vamos calcular o desvio padrão das vendas por produto.
1. Clique na célula J6 e digite =DESVPAD(.
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
2003 foi superior ou inferior à das vendas de 2004 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, clique em Inserir e selecione Função...
A caixa de diálogo Colar Função (Figura 1.7) será aberta. Em Categoria da função,
clique na opção Todas.
Figura 1.7 – Colar função.
18
Excel Avançado
Em seguida, role a barra de rolagem da caixa Nome da 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
Valor_se_falso
Digite: Superior
Digite: Inferior
Onde:
• B6 é a média das vendas de 2003;
• G6 é a média das vendas de 2004;
• B6+B6*$C$3/100 é o cálculo da porcentagem da média das vendas de 2003
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 formos utilizar a alça de preenchimento,
o endereço da célula (C3) não será alterado. Para colocarmos o sinal $, ou
seja, travarmos 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.
19
Capítulo 1 • Comandos e funções
1.4 Formatação condicional
Em seguida, vamos formatar um intervalo baseado em condições.
Clique na célula K6.
No menu Formatar, clique em Formatação condicional... Preencha a caixa de diálogo Formatação condicional como na figura 1.9.
Figura 1.9 – Formatação condicional.
Campo
Ação Condição 1
Selecione: O valor da célula é
Selecione: igual a
Digite: Superior
Clique no botão Formatar... e selecione a cor azul
Para inserir a Condição 2, clique no botão Adicionar >>.
Campo
Ação Condição 2
Selecione: O valor da célula é
Selecione: igual a
Digite: Inferior
Clique no botão Formatar... e selecione a cor vermelho
Clique em OK
Utilize a alça de preenchimento para preencher a formatação condicional e a
fórmula SE até a célula K11.
Agora só nos resta formatar os dados numéricos com nenhuma casa decimal.
Selecione o intervalo de células B6:J11, clique no menu Formatar e escolha a opção
Células; na caixa de diálogo Formatar células, clique na guia Número e preencha os
campos como na figura 1.10.
20
Excel Avançado
Figura 1.10 – Formatar células.
Campo
Ação
Categoria
Selecione: Número
Casas decimais
Digite: 0
Pronto. Sua planilha ficou igual à da figura 1.11:
Figura 1.11 – Planilha pronta.