Na aula passada aprendemos sobre a função soma, função média, função máximo e função mínimo.
Nesta aula iremos aprender sobre a função SOMASE, função SOMASES, os operadores relecionais, função SE, função PROCV, função PROCH e a ferramenta inserir função.
Função SOMASE
A função SOMASE é uma função matemática que permite efetuar a soma em um intervalo de células de acordo com um critério ou condição. Sua sintaxe é a seguinte:
SOMASE(intervalo;critério;intervalo_soma)
– intervalo: intervalo de células onde o critério será procurado.
– critério: condição que define os valores que serão somados. Pode ser um número, uma expressão, uma referência a uma célula, um texto ou uma função. Deve ser informado entre aspas se for um valor alfanumérico ou se for uma expressão formada por operadores relacionais (>, <, >=, <=, <>).
– intervalo_soma: intervalo de células que serão somadas de acordo com o critério informado.
- Na célula K7 vamos inserir a função SOMASE para somar o valor total de vendas para o mês de abril. Dessa forma os argumentos que devem ser informados são os seguintes:
– intervalo: coluna dos meses, ou seja, o intervalo B3:B16.
– critério: o mês deve ser abril e deve ser informado como: “Abril”.
– intervalo_soma: coluna do valor total, ou seja, o intervalo F3:F16.
Assim, a fórmula que deve ser inserida na célula K7 é a seguinte:
=SOMASE(B3:B16;”Abril”;F3:F16)
- Utilizando o mesmo raciocínio, na célula K8, a fórmula para calcular o valor total de vendas em Maio será:
=SOMASE(B3:B16;”Maio”;F3:F16)
- Na célula K9 insira a fórmula para calcular o valor total de vendas em Junho.
- Na célula K10 insira a fórmula para calcular o valor total de vendas para Copacabana.
- Na célula K12 insira a fórmula para calcular o valor total de vendas para Leblon.
Função SOMASES
A função SOMASES é uma função matemática que permite efetuar a soma em um intervalo de células de acordo com mais de um critério ou condição. Sua sintaxe é a seguinte:
SOMASES(intervalo_soma;intervalo_critério1;critério1; intervalo_critério2;critério2;…)
– intervalo_soma: intervalo de células que serão somadas de acordo com os critérios informados.
– intervalo_critério1: intervalo de células onde o critério1 será procurado.
– critério1: condição que define os valores que serão somados. Pode ser um número, uma expressão, uma referência a uma célula, um texto ou uma função. Deve ser informado entre aspas se for um valor alfanumérico ou se for uma expressão formada por operadores relacionais (>, <, >=, <=, <>).
– intervalo_critério2: intervalo de células onde o critério1 será procurado.
– critério2: condição que define os valores que serão somados. Pode ser um número, uma expressão, uma referência a uma célula, um texto ou uma função. Deve ser informado entre aspas se for um valor alfanumérico ou se for uma expressão formada por operadores relacionais (>, <, >=, <=, <>).
É possível informar até 127 pares de intervalo/critério.
- Na célula K11 vamos inserir a função SOMASES para somar o valor total de vendas em Copacabana no mês de abril. Dessa forma os argumentos que devem ser informados são os seguintes:
– intervalo_soma: coluna do valor total, ou seja, o intervalo F3:F16.
– intervalo_critério1: coluna do bairro, ou seja, o intervalo A3:A16.
– critério1: o bairro deve ser Copacabana e deve ser informado como: “Copacabana”.
– intervalo_critério2: coluna dos meses, ou seja, o intervalo B3:B16.
– critério2: o mês deve ser abril e deve ser informado como: “Abril”.
Assim, a fórmula que deve ser inserida na célula K11 é:
=SOMASES(F3:F16;A3:A16;”Copacabana”;B3:B16;”Abril”)
Vamos formatar a planilha para mostrar em vermelho o bairro que efetuou a maior venda.
- Selecione a coluna Bairro, ou seja, o intervalo B3:B16.
- Selecione a ferramenta Formatação Condicional, Nova Regra e, na janela apresentada, selecione Usar uma fórmula para determinar quais células devem ser formatadas.
- No campo Formatar valores em que esta fórmula é verdadeira, digite (Figura 67):
=F3=$K$5
DICA: Para não errar a digitação da fórmula, ao invés de digitar os endereços das células desejadas clique sobre elas.
- Clique no botão Formatar.
- Na guia Fonte selecione cor Vermelho e Negrito.
- Na guia Preenchimento selecione a cor rosa:
- Clique em OK e novamente em OK.
- Execute o mesmo procedimento para formatar todas as colunas da planilha. No final, você terá o seguinte resultado:
OPERADORES RELACIONAIS
Os operadores relacionais permitem estabelecer relações entre elementos, sendo os seguintes:
Função SE
A função SE é uma função lógica e tem como objetivo retornar um valor de acordo com o resultado de testes condicionais. Sua sintaxe é a seguinte:
SE(teste_lógico;valor_se_verdadeiro;valor_se_falso)
– teste_lógico: condição que será avaliada, podendo ser falsa ou verdadeira.
– valor_se_verdadeiro: valor que será colocado na célula se o resultado do teste lógico for verdadeiro.
– valor_se_falso: valor que será colocado na célula se o resultado do teste lógico for falso.
Crie a seguinte planilha:
Para calcular a planilha considere o seguinte:
– Se o equipamento é devolvido no mesmo dia, será cobrada uma diária.
– Se o cliente é VIP, terá desconto no valor a pagar.
- Vamos calcular o número de diárias, que é a Data de Devolução – Data Aluguel. No entanto, devemos colocar uma condição nesse cálculo, que é:
Se a data de devolução é igual à data de aluguel, então o número de diárias é igual a 1.
A fórmula será colocada na célula E5. Assim:
– teste_lógico: verificar se as datas são iguais, ou seja, D5=C5.
– valor_se_verdadeiro: se as datas forem iguais, então colocar o valor 1 na célula.
– valor_se_falso: se as datas forem diferentes, calcular o número de diárias, ou seja:
D5-E5
Clique na célula E5 e digite a seguinte fórmula:
=SE(D5=C5;1;D5-C5)
- Copie a fórmula para todos os clientes.
- Calcular o valor total como sendo a multiplicação do número de diárias pelo valor da diária.
Atenção! Na fórmula o endereço da célula que contém o valor da diária deve ser absoluto, deve ser fixado para que a cópia fique correta.
Na célula F5 digite a fórmula:
=E5*$B$2
- Copie a fórmula para todos os clientes.
- Calcular o valor do desconto como sendo o valor total multiplicado pelo percentual de desconto. Devemos colocar uma condição nessa fórmula, pois somente os clientes VIP têm desconto.
A fórmula será colocada na célula G5. Assim:
– teste_lógico: verificar se o cliente é VIP, ou seja, B5=”VIP”
– valor_se_verdadeiro: se o cliente é VIP então tem desconto, ou seja, a fórmula que será colocada na célula é:
F5*$B$3
Veja que o endereço da célula que contém o percentual de desconto está fixado.
– valor_se_falso: se o cliente não for VIP, não tem desconto, ou seja, o valor deve ser 0 (zero).
Na célula G5 digite a fórmula:
=SE(B5=”VIP”;F5*$B$3;0)
- Copie a fórmula para os demais clientes.
- Calcular o valor a pagar como sendo o valor total – desconto.
- Grave a planilha como
A planilha completa deve ser como a seguinte:
EXERCÍCIO
Carregue a pasta Médias que você gravou anteriormente e insira uma nova coluna chamada SITUAÇÃO, que vai mostrar se o aluno está aprovado ou reprovado (Figura 73).
- A condição para que o aluno seja aprovado é que a média geral seja maior ou igual a 6,0. Então, na célula K2 digite a fórmula:
=SE(I2>=6;”Aprovado”;”Reprovado”)
- Copie a fórmula para todos os alunos.
- Utilizando a ferramenta Formatação Condicional, mostre em vermelho a situação dos alunos reprovados, e em azul a situação dos alunos aprovados.
- Vamos utilizar mais um recurso da formatação condicional para exibir ícones na coluna média geral de acordo com os valores. Selecione a região I2:I14.
- Selecione a ferramenta Formatação Condicional, Nova Regra…
- Na janela apresentada selecione os campos exatamente como mostrado na figura a seguir.
Veja que as estrelas estão indicando os valores de média >=8 (estrela preenchida), média < 8 e >= 6 (estrela preenchida pela metade) e média < 6 (estrela sem preenchimento.
A planilha calculada é a seguinte:
Função PROCV
A função PROCV é uma função de pesquisa que permite efetuar busca de valores em linhas de um intervalo de células da planilha. Sua sintaxe é a seguinte:
PROCV(valor_procurado;intervalo;número_coluna;[tipo_procura]
– valor_procurado: valor que vai ser procurado na primeira coluna do intervalo de células.
– intervalo: intervalo de células que contém os dados que serão procurados. A primeira coluna desse intervalo deve conter o valor_procurado.
– número_coluna: número da coluna do intervalo que contém o valor que deve ser retornado. Esse número corresponde ao número da coluna dentro do intervalo, e não à identificação da coluna dentro da planilha inteira.
– tipo_procura: argumento opcional que indica de que forma será efetuada a procura do valor na primeira coluna do intervalo, se a correspondência deve ser exata ou aproximada.
Se for Verdadeiro ou omitido, a procura pelo valor poderá ser de forma exata ou aproximada. Nesse caso os valores da primeira coluna devem estar classificados em ordem crescente.
Se o argumento for Falso, a procura pelo valor será de forma exata.
Crie a seguinte planilha:
Essa planilha calcula o IMC (Índice de Massa Corporal) da pessoa e coloca o resultado (mensagem) na coluna Resultado.
A fórmula para calcular o IMC é a seguinte:
IMC = Peso / Altura2
- Na célula D3 insira a seguinte fórmula:
=C3/B3^2
- Copie a fórmula para as demais células.
- Agora vamos preencher a coluna Resultado com a mensagem obtida da tabela de IMC. Para isso vamos utilizar a função PROCV, que vai procurar pelo valor calculado do IMC na tabela e trazer a respectiva mensagem.
Clique na célula E3. Os argumentos que serão passados na função são os seguintes:
– valor_procurado: valor calculado do IMC da pessoa que, nesse caso, está na célula D3. Esse valor será procurado na primeira coluna da tabela de IMC.
– intervalo: toda a tabela de IMC, ou seja, a região H2:I17. Como a fórmula será copiada para as demais células e o intervalo tem que ser sempre o mesmo, é necessário fixá-lo. Então, o argumento para intervalo deve ser digitado como: $H$2:$I$17.
– número_coluna: número da coluna que contém a informação que desejamos obter, no caso, a mensagem referente ao IMC procurado. Na tabela de IMC essa informação está na segunda coluna. Logo, o argumento que deve ser passado é 2.
– tipo_procura: a procura será feita pelo valor aproximado. Note que a primeira coluna, onde será procurado o IMC, não apresenta valores exatos, mas indica faixas de valores. Então esse argumento não será informado.
Assim, na célula E3, digite a seguinte fórmula:
=PROCV(D3;$H$2:$I$7;2)
- Copie a fórmula para as demais células. Você terá o seguinte resultado:
- Grave a pasta como IMC
EXERCÍCIO
Crie a seguinte planilha:
O objetivo da planilha é calcular quanto o funcionário tem para receber. Na tabela DESCONTOS temos o percentual de imposto e o valor para vale refeição que devem ser descontados de acordo com as faixas salariais na coluna Salário.
- O valor do imposto é o salário multiplicado pelo percentual de imposto, que será obtido pela função PROCV. Na célula C4 digite a fórmula:
=B4*PROCV(B4;$G$5:$I$9;2)
- Copie a fórmula para as demais células.
- O valor que será descontado para vale refeição também será obtido pela função PROCV. Na célula D4 digite a fórmula:
=PROCV(B4;$G$5:$I$9;3)
- Copie a fórmula para as demais células.
- Para calcular o valor a receber, devemos subtrair o valor do imposto e vale refeição do salário. Na célula E4 digite a fórmula:
=B4-C4-D4
- Salve a planilha com o nome
A planilha calculada é a seguinte:
Função PROCH
A função PROCH é uma função de pesquisa que permite efetuar busca de valores em colunas de um intervalo de células da planilha. Sua sintaxe é a seguinte:
PROCH(valor_procurado;intervalo;número_linha;[tipo_procura]
– valor_procurado: valor que vai ser procurado na primeira linha do intervalo de células.
– intervalo: intervalo de células que contém os dados que serão procurados. A primeira linha desse intervalo deve conter o valor_procurado.
– número_linha: número da linha do intervalo que contém o valor que deve ser retornado. Esse número corresponde ao número da linha dentro do intervalo, e não à identificação da linha dentro da planilha inteira.
– tipo_procura: argumento opcional que indica de que forma será efetuada a procura do valor na primeira linha do intervalo, se a correspondência deve ser exata ou aproximada.
Se for Verdadeiro ou omitido, a procura pelo valor poderá ser de forma exata ou aproximada. Nesse caso os valores da primeira linha devem estar classificados em ordem crescente.
Se o argumento for Falso, a procura pelo valor será de forma exata.
A diferença entre as funções PROCV e PROCH é que na primeira a procura é feita nas colunas, enquanto a segunda efetua a procura nas linhas.
Para exemplificar vamos recalcular o IMC utilizando a função PROCH.
Abra a pasta IMC e modifique-a de acordo com a figura a seguir.
Veja que agora colocamos uma outra tabela IMC, mas de forma horizontal. Na segunda coluna Resultado, vamos mostrar as mensagens utilizando a função PROCH.
- Clique na célula F3 e digite a fórmula:
=PROCH(D3;$B$14:$G$15;2)
- Copie a fórmula para as demais células. Veja que o resultado é o mesmo obtido com a função PROCV.
Utilizando a ferramenta Inserir Função
Até agora, sempre digitamos a função diretamente dentro da célula. No entanto, podemos utilizar a ferramenta Inserir Função que irá guiar, passo a passo, a construção da função na célula. Essa ferramenta é útil quando não sabemos exatamente qual função devemos utilizar naquele momento, pois permite escolher entre os vários tipos a mais adequada à situação.
Crie a seguinte planilha:
Vamos inserir na célula H2 a função SOMASE para calcular a quantidade de roteadores vendidos. Para isso, na guia Fórmulas selecione Inserir Função.
A seguinte janela será apresentada:
Nessa janela podemos procurar pela função, selecionar uma categoria, selecionar a função. Veja que é mostrada uma breve descrição da função selecionada. No campo Procure por uma função digite SOMASE, clique no botão Ir e, a seguir, com a função SOMASE selecionada, clique em OK.
Você terá a seguinte janela para informar os argumentos da função:
- Nessa janela temos todas as informações necessárias para criar corretamente a nossa função. Clique no campo Intervalo e selecione com o mouse o Intervalo de células que deve ser considerado para procurar o critério (B2:B13)
- Agora você vai informar o critério, que é o valor que deve ser procurado para executar a soma. Clique na célula G2 da planilha.
- Por fim você vai indicar qual é o intervalo de células que devem ser somadas. Selecione a região C2:C13. Após informar todos os argumentos, você terá:
Veja que nessa janela você tem todas as informações sobre a função: argumentos, resultado da fórmula, ajuda.
- Faça o mesmo procedimento para calcular as quantidades vendidas de Pen Drive e SSD, bem como para calcular a maior venda com a função MÁXIMO.
O resultado será o seguinte:
