Búfalo Informática - Utilizando Funções Avançadas para Cálculos no Excel

Usando Funções Avançadas para Cálculos no Excel:

O Excel nos fornece uma série de recursos que permitem facilitar o nosso trabalho diário, isso todo mundo sabe. O que poucos sabem é como é simples usar as funções para nos auxiliar nesse dia-a-dia.
A maioria das pessoas acaba subutilizando o Excel, ou usando-o como uma simples calculadora, complicando, quando poderia ser mais fácil.
Um exemplo:
Imagine uma loja que tenha dezenas de funcionários espalhados em várias regiões e queira fazer um cálculo do total de vendas desses funcionários, um total de vendas por região e para complicar, descobrir quanto cada um desses funcionários vai receber de comissão baseado nesse total de vendas.
Normalmente, geram-se tabelas diversas para cada um desses objetivos, o que vem a ser mais trabalhoso do que fazer tudo de uma “tacada” só.
Utilizando o Excel, podemos criar uma única tabela na qual conste: o nome dos vendedores, os produtos vendidos e as regiões que eles trabalham e as vendas de cada um deles.
Como no exemplo abaixo:

Montada a tabela, mas ainda preciso de uma informação, preciso do total de cada produto vendido pelo vendedor. Para isso, devo efetuar um cálculo simples de multiplicação.
Devo multiplicar a quantidade pelo preço do produto (= E4*F4) – na coluna Total.

Bom, agora já podemos começar a complicar a coisa. Como eu tinha dito acima, eu quero obter dessa tabela diversos relatórios: quero saber o total de vendas por vendedor, por região e por produto. Além disso, quero saber quanto de comissão o vendedor vai receber (seguindo um critério, que toma por base o total de vendas do mesmo). Complicadinho, né? Mas é só na aparência.
A segunda parte da nossa tabela é digitar o que se espera obter da mesma, por isso, irei montar três tabelinhas a parte com os itens do relatório:

Para calcular o total de vendas por vendedor, preciso realizar uma soma condicional, isso é, eu só quero que o programa calcule o total da vendedora X. Para isso, uma formúla não seria eficiente, preciso de uma Função.
Só relembrando, funções são recursos semelhantes às formúlas, mas com uma capacidade maior de resolver o nosso problema, primeiro que as mesmas não se restringem apenas ao cálculo, embora não se furtem a isso, se necessário, mas também nos oferecem resoluções mais trabalhadas.
Como no nosso exemplo. Neste caso, preciso de uma função chamada SOMASE ( o próprio nome já diz a que veio – essa função realiza uma Soma, Se algo for cumprido) – é uma soma condicional.
Veja se ela realmente se aplica ao nosso caso: Como já havia dito – preciso calcular o total das vendas do vendedor X. Então, preciso que o programa busque na coluna dos vendedores todas as referências ao vendedor X e faça uma relação com a coluna Total do Preços dos produtos vendidos.

A função SOMASE possui uma estrutura básica que se mantêm inalterável.

=SOMASE(Xni:Xnf;”nome”;Yni:Ynf)

Traduzindo:

Xni:Xnf = endereço inicial (Xni) até o endereço final (Xnf) da coluna onde se encontra o ítem a ser comparado – no caso do nosso exemplo – o endereço inicial da coluna de vendedores até o endereço final (A4:A20)

“nome” = Nome do ítem a ser comparado – no caso, o nome do vendedor, não esquecer das aspas e de digitar exatamente igual o nome se encontra na coluna pesquisada.

Yni:Ynf= Endereço inicial (Yni) até o endereço final (Ynf) da coluna onde se encontram os valores a serem somados – no caso do nosso exemplo – o endereço inicial da coluna total até o endereço final (F4:F20)

Ficando o resultado final assim:

=SOMASE(A4:A20;”Vendedor”;F4:F20)

Devemos aplicar a função para cada um dos vendedores, só trocando o nome, o primeiro é Maria, o segundo Alfredo etc.

Não adianta “puxar” pela alça de preenchimento, pois os endereços devem permanecer sempre os mesmos.

Um macete é “prender” as linhas e assim podemos arrastar e somente ir substituindo os nomes dos vendedores.

Ex:
=SOMASE(A$4:A$20;”Vendedor”;F$4:F$20)

Devemos repetir o mesmo procedimento para gerar relatórios dos totais de Produtos e Regiões, só lembrando de trocar a coluna dos vendedores para a de total de Produto e a de Região.

Agora vamos descobrir quanto cada vendedor irá receber. Para isso vou estipular uma condição: Se o vendedor vendeu mais de R$ 5.000,00 receberá 1% de comissão mais o salário que é R$ 500,00, se vendeu abaixo disso, a comissão será de 1,5% mais o salário.

Quando trabalhamos com condição, usamos a Função SE, que estipula que algo só acontecerá se a condição for cumprida.
A função SE possui uma estrutura bastante simples de se entender, e é ela a chave para que nunca mais esqueçámos como usá-la.
Estrutura da Função SE:
=SE(Teste_lógico;Valor_se_Verdadeiro;Valor_se_Falso)
Sendo Teste Lógico a nossa condição, o valor se verdadeiro é o que acontecerá caso a condição seja atendida e o valor se falso é o que acontecerá se a condição não for atendida.

Dessa maneira temos que transpor o que desejamos obter para dentro dessa estrutura. O primeiro passo seria, então, descobrir qual é o Teste Lógico.

Na Frase : Se o vendedor vendeu mais de R$ 5.000,00 receberá 1% de comissão mais o salário que é R$ 500,00, se vendeu abaixo disso, a comissão será de 1,5% mais o salário – qual é o Teste Lógico? O que desejamos testar?
Preste atenção no inicio da frase: total de vendas do Vendedor ser maior que R$ 5.000,00 – então esse seria o teste lógico.
Se esse total for maior que R$ 5.000,00, o vendedor receberá 1% de comissão (desse total), acrescido de R$ 500,00 – Esse então seria o valor se verdadeiro e por fim, caso o total de vendas seja inferior a R$ 5.000,00 então a comissão seria de 1,5% (desse total), mais R$ 500,00.
Ficando assim a nossa Função:

Acontece que total de vendas possui um endereço e devemos substituir o texto pelo endereço do mesmo, olhe na tabela e veja em que lugar se encontra o total de vendas por vendedor.

No nosso exemplo, o total está situado na coluna B, linha 26. Então nossa Função ficaria exatamente assim:

Bastaria puxar e teríamos a informação do salário dos vendedores:

Resolvendo assim todas os desafios da planilha, agora é só salvar.