Criação de fórmulas e funções
Dica 025
Construindo fórmulas
condicionais
falsa. O ponto-e-vírgula serve para separar um item do outro. Neste exemplo de
controle de notas, o teste lógico será a
verificação da média do aluno, se passar ou for igual a cinco, a mensagem de
“Aprovado” deve aparecer, se não a mensagem de “Reprovado” deve aparecer.
3 Sendo assim, a fórmula ficaria:
eprovado”).
=SE(F2>=5;”Aprovado”;”Reprovado”).
=SE(F2>=5;”Aprovado”;”R
Ao construir suas planilhas e as fórmulas que a compõem, você vai perceber a
necessidade de criar condições para controlar se um cálculo deve ser feito ou não.
Numa planilha simples de controle de notas, por exemplo, você pode apresentar a
situação do aluno no final do semestre: se
ele foi aprovado, reprovado ou se ficou de
recuperação. Para fazer essa análise, a média final do aluno é testada, e dependendo do seu valor, uma das três mensagens
é exibida. A função usada para realizar
essa tarefa é a conhecida função SE.
4 Mas, dessa maneira há apenas duas
possibilidades de determinar a situação do
aluno: ou ele foi aprovado ou foi reprovado. Para acrescentar mais possibilidades
nesta fórmula, você deve criar o que recebe o nome de funções aninhadas, ou seja,
uma função dentro da outra. Observe que
se o aluno tem uma média maior que cinco, o Excel exibe direto a mensagem de
Aprovado. Porém este aluno pode ter média exatamente igual a cinco ou dez. Tanto
um quanto o outro exibe a mesma mensagem de aprovado, mas o correto é mostra
recuperação para médias até 7 e só desse
valor em diante mostrar “Aprovado”.
1 Construa uma planilha para controle de
notas com 4 colunas contendo os valores das notas dos 4 bimestres. Utilize,
por exemplo, da célula B2 até a célula
E2
E2. Em seguida, gere a média na quinta
coluna. Na célula G2 digite a quantidade
de faltas desse aluno. Na próxima célula você deve colocar o status do aluno.
5 No lugar da mensagem “Aprovado” na sua
fórmula, você deve criar outra função SE,
pois é preciso fazer outro teste (maior que
sete ou não). A fórmula final ficaria assim:
=SE(F2>=5;SE(F2>=7;”Aprovado”;”
Recuperação”);”R
eprovado”)
ecuperação”);”Reprovado”)
eprovado”)..
2 A estrutura da função SE é a seguinte:
=SE(teste lógico; resultado se verdadeiro; resultado se falso).
Onde o teste lógico é a condição ou o teste a ser verificado, o resultado se verdadeiro é o que deve ser feito caso a condição for verdadeira e resultado se falso é
o que deve ser feito caso a condição for
6 Para completar a fórmula, acrescente
mais um teste para verificar se as faltas
do aluno não ultrapassam 10. Como já existe um teste lógico, que é o da média, você
terá que aninhar mais uma função para
atingir esse resultado. Para isso você deve
usar a função E :
=SE(E(F2>=5;G2<10);SE(F2>=7;”Aprovado”;
”R
ecuperação”);”R
eprovado”).
”Recuperação”);”R
ecuperação”);”Reprovado”).
026
a-21-49.p65
26
14/3/2003, 14:36
Criação de fórmulas e funções
Dica 026
Adicionando
valores
As operações de soma no Excel vão
muito além daquele simples recurso de
Autosoma. Você pode, por exemplo, fazer um teste lógico e, dependendo do
seu resultado, fazer ou não a soma de
um intervalo. Além disso, é possível, através de duas tabelas, procurar por um
determinado valor e só somar as informações que lhe interessam; por exemplo, somar apenas os produtos vendidos por um determinado vendedor de
uma determinada região. Aprenda a
usar as funções de SOMA do Excel:
1 Para somar informações selecionadas
por algum critério, utilize a função
SOMASE
SOMASE. Sua estrutura difere um pouco
da tradicional função SOMA
SOMA. O primeiro
argumento da função é o intervalo a ser
verificado. O segundo argumento é o que
deve ser encontrado e o terceiro e último
argumento necessário é o intervalo de
soma, que muitas vezes é o mesmo do
intervalo de busca.
2 Por exemplo, gere uma tabela com 10
nomes de vendedores (repita o nome de
alguns) da célula A1 até a célula A10. Do
lado de cada nome digite a quantidade vendida por cada um.
3 Na célula B11, digite =SOMASE( para
iniciar a fórmula. O primeiro argumento
será o intervalo de A1 até A10, pois nele
será procurado o nome de um dos vendedores. O segundo argumento será um dos
nomes
entre aspas que
você cadastrou
e o terceiro ar-
gumento é o intervalo de valores a serem
somados, no caso de B1 até B10. A fórmula final fica:
=SOMASE(A1:A10;”Martins”;B1:B10).
4 Dessa forma, serão somados somente
os valores que correspondem ao vendedor Martins.
5 Se você tem duas tabelas e deseja pegar a informação de uma e procurar na
outra, você pode usar a função BDSOMA
BDSOMA.
Por exemplo, imagine uma tabela contendo quatro campos com os seguintes nomes: vendedor, vendas, produtos e região. A segunda tabela deve ter a mesma
estrutura de cabeçalhos da primeira, ou
seja, com títulos iguais e na mesma seqüência. Assim, você pode procurar todos os produtos vendidos por um vendedor e somar seu valor.
6 Cadastre na primeira tabela todos os
nomes de vendedores, os valores de suas
vendas, os nomes dos produtos e a região
(Sul, Leste, Oeste, Centro, Norte). Na segunda tabela, digite um dos nomes de vendedores e digite um nome de produto em
suas respectivas colunas.
7 Em uma célula à parte, digite a função:
=BDSOMA(. O primeiro argumento dela
é a tabela na qual as informações estão
cadastradas. Selecione todas as células
que fazem parte da primeira tabela, inclusive os cabeçalhos. O segundo argumento é o que a função deve retornar,
neste caso é vendas. O terceiro e último
argumento mostram a tabela que contém os critérios da pesquisa. Selecione
toda a segunda tabela, inclusive os cabeçalhos. A fórmula vai ficar:
=BDSOMA(A1:D37;”vendas”;A40:D41).
027
a-21-49.p65
27
14/3/2003, 14:36
Criação de fórmulas e funções
Dica 027
Funções para
busca de dados
Fazer pesquisas dos dados cadastrados
é uma tarefa muito praticada por quem
acessa a planilha. Imagine uma planilha
que contenha as conversões de preços
de produtos, contando com impostos e
frete. Sabemos que o valor do frete varia de acordo com a região a ser entregue. Se for, por exemplo, no Norte do
País, o frete vale 5% sobre o preço original do produto, bem como a porcentagem para cada região, já fixa. Neste
caso, o ideal é criar uma tabela à parte.
1 Crie a tabela principal contendo os camreço de
pos: Nomes dos produtos, P
Preço
compra, R
egião de destino, F
rete e P
re
Região
Frete
Pre
re-ço de venda
venda, começando pela célula A1
A1.
Preencha com informações, exceto as coreço de venda.
lunas Frete e P
Preço
2 Abaixo, crie outra tabela, contendo:
Região e Porcentagem
orcentagem. Digite as cinco
regiões nacionais e ao lado de cada uma
a porcentagem correspondente.
3 A função usada para buscar a porcentagem correta na lista é a função PROCV
PROCV.
Então, na coluna de frete, na célula D2
D2,
digite =PROCV(.
4 Em seguida, digite o que deve ser procurado, no caso a célula C2
C2. O segundo
argumento é o intervalo de células que
contém a informação procurada. Selecione todas as células que fazem parte da
segunda tabela. Em seguida, coloque o
sinal de cifrão na frente das letras e números da referência.
5 O último argumento é o número da coluna dentro da tabela que interessa saber,
ou seja, é a coluna que contém o valor que
deve ser retornado. Neste exemplo seria a
segunda coluna, pois nela está o valor da
porcentagem. A fórmula ficaria assim:
=PROCV(C2;$A$15:$B$19;2).
6 Assim, o valor da porcentagem que
corresponde à região de frete é calculado
na célula D2
D2. Arraste a alça de preenchimento até a última célula da tabela.
7 Na última coluna, calcule o preço de venda do produto, ou seja, acrescente o valor
do frete ao preço de compra. Assim:
=B2+(B2*D2).
8 Selecione a segunda tabela (da célula
A15 até B19), pressione Ctrl + C para
copiar. Clique na célula A21 e clique em
Editar>Colar especial... Selecione a opção Transpor e clique em OK
OK..
9 Clique na célula F2 para encontrar o
frete usando outra função. Digite:
=PROCH(C2;$A$ 21:$E$22;2)
21:$E$22;2). O primeiro argumento é o que
será procurado. O segundo argumento é a referência à nova tabela, na
qual o valor será procurado. E o terceiro argumento é o número da linha dessa tabela que
deve ser retornado pela
função.
028
a-21-49.p65
28
14/3/2003, 14:36
Criação de fórmulas e funções
Dica 028
Convertendo
as medidas
Todo mundo já teve aula de Física no
colégio e certamente aprendeu várias fórmulas para converter uma coisa em outra, como por exemplo, temperatura. No
entanto, poucos imaginam que existe um
programa, como o Excel, que faz isso
automaticamente sem que você precise
lembrar dessas fórmulas. No Excel, você
consegue converter unidades de medidas, como temperaturas, distância, etc.
1 A principal função usada para fazer conT . Para usá-la, você
versões é a CONVER
CONVERT
precisa informar basicamente três argumentos. O primeiro é o valor a ser convertido, o segundo é a unidade de medida atual
e o terceiro é a unidade de medida que você
quer converter. Digite 45 na célula A1
A1..
2 Em seguida, digite na
T(.
célula B1: =CONVER
=CONVERT(.
5 Da mesma forma, se a conversão fosse ao contrário, ou de Grau Fahrenheit
para outra medida, a abreviatura de
Fahrenheit seria a letra F.
6 Todos os tipos de medidas possuem
uma abreviatura. Por exemplo, para converter metros em polegadas, use:
=CONVER
T (A1;”m
”;”in
”).
=CONVERT
(A1;”m”;”in
”;”in”).
7 A conversão de horas em minutos fica
=CONVER
T(A1 ;”hr
”;”mn
”).
assim:=CONVER
=CONVERT(A1
;”hr”;”mn
”;”mn”).
8 Já de grama para libra massa ficaria:
=CONVERT(A1;”g”;”sg”).
9 Para saber o símbolo de cada unidade
de medida, acesse a ajuda do Excel e
T.
pesquise sobre a função CONVER
CONVERT
3 Continue construindo a
fórmula e informe o valor
a ser convertido; neste
caso é a célula A1.
4 Agora, informe a unidade de medida do número
e depois a unidade desejada para a conversão.
Cada unidade de medida
possui uma abreviação.
Basta colocá-la entre aspas, para que a fórmula
fique correta. Por exemplo, para converter graus
Celsius em Kelvin, digite
“C”;”K”
“C”;”K”, pois a abreviatura de Celsius é C e de
Kelvin é K. Neste caso, a
fórmula final ficaria:
=CONVERT(A1;”C”;”K”)
=CONVERT(A1;”C”;”K”)..
029
a-21-49.p65
29
14/3/2003, 14:36