Como Usar a função SOMASE Excel (SUMIF)

0
57598
detalhe da fórmula somase no excel
LUZ Prime

Esse é o meu segundo post da série “Como gerir melhor usando uma função do Excel” (veja o meu post sobre a PROCV – função de busca). Cada vez mais eu quero te mostrar o quanto sua vida pode ser mais fácil e prática se você usar da maneira correta uma planilha de Excel. Dessa vez, eu vou falar da importância do SOMASE para gerir melhor qualquer área da sua empresa.

Essa função serve para fazer soma de valores condicionados a critérios que você pode escolher. Ao terminar de ler esse post você vai aprender como usar a função SOMASE Excel para qualquer situação empresarial e poderá baixar uma planilha de exercícios com gabarito sobre a função SOMASE.

Para começar, vamos começar vendo alguns exemplos do uso da SOMASE na gestão de empresas:

1. Exemplo de uso do SOMASE em planilha de Prospecção de Clientes

Muitas empresas tem equipes de vendas ou áreas responsáveis por receitas. Nesses casos, podemos trabalhar com um time comercial que visita ou recebe visitas de clientes para negociar a venda de um determinado serviço. Vamos dar uma olhada na nossa planilha de Prospecção de Clientes abaixo:

Planilha de prospecção de clientes - aba de funil de vendas

LUZ Prime

Nela, você tem uma lista de todos os contatos realizados com clientes (coluna B). Observe que esses contatos poderiam ser visitas dos seus clientes na sua empresa (se você tem uma Agência de Viagens por exemplo) ou clientes visitados (se você tem uma solução em software ou uma consultoria). Obviamente, todos os clientes terão diversas informações cadastradas como o vendedor responsável (coluna F), serviço oferecido (coluna H), valores (coluna I) e em que etapa do funil o cliente está.

Agora imagine que você tem 10 vendedores prospectando clientes e quiser saber o valor total negociado por cada um deles. Somar os valores na mão daria um trabalho chato e demorado demais toda vez que você quisesse acessar essa informação. É por isso que existe a função SOMASE (ou SUMIF no inglês). Com ela, você poderá fazer somas de valores de forma automática condicionada a critérios que você escolha na planilha. Nesse caso, queremos fazer a soma de todos os valores negociados condicionados ao nome do vendedor responsável. Vamos ver como funciona:

Resultados e indicadores do funil de vendas usando a função SOMASE no excel

Na nossa planilha criamos uma nova aba (4. Meses, Vendedores e Área) com uma tabela com o nome dos vendedores na coluna G e a soma do valor negociado na coluna K como indicado na imagem acima. A partir da lista dos vendedores, com o uso da fórmula SOMASE, automaticamente o excel soma os valores de cada vendedor na aba de Prospecção e leva todas as informações correspondentes para as células da coluna K. Vamos ver o resultado mais de perto:

Tabela de indicadores usando a fórmula somase no excel

Então, neste primeiro exemplo, para conseguir a soma dos valores, utilizamos a construção das fórmulas dessa maneira:

  • Para o primeiro vendedor:
    =SOMASE(‘3. Prospecção’!$F$8:$F$500;‘4. Meses, Vendedores e Área’!G8;’3. Prospecção’!$I$8:$I$500)
  • Para o segundo vendedor:

    =SOMASE(‘3. Prospecção’!$F$8:$F$500;‘4. Meses, Vendedores e Área’!G9;’3. Prospecção’!$I$8:$I$500)

Perceba que a 2ª parte de cada fórmula (itens em azul) possui numerações diferentes para cada item procurado (G8 e G9). Isso ocorre porque esse é o critério utilizado para condicionar a soma e, nesse caso, o Excel recebe uma mensagem do tipo: “Toda vez que você ver o valor G8 (ou G9, ou etc. – que é exatamente o nome do vendedor), você deve somar o valor correspondente”.

Você também deve ter percebido que a primeira e última parte da fórmula referenciam a aba de Prospecção. Isso ocorre porque os valores analisados estão preenchidos naquela aba.

Como você viu, a função SOMASE é prática e funcional. Ela tem uma variação muito útil que não abordaremos a fundo nesse post, mas que vale a pena conhecer, que é a função SOMASES (SUMIFS). Ela é exatamente igual a SOMASE, só que a soma fica condicionada a mais de um critério para acontecer. No nosso exemplo, você poderia condicionar a soma de valores ao nome do vendedor (critério 1) e ao fato de ele ter conseguido fechar a compra (critério 2), mostrando a soma de valores vendidos de fato.

Vamos mostrar mais um exemplo de uso da SOMASE.

2. Exemplo de uso de SOMASE em planilha de Fluxo de Caixa 

Agora vamos ver o exemplo de uma planilha de Fluxo de Caixa. Aqui, você encontrará entradas (receitas) e saídas (despesas) da sua empresa. Essa é a visualização que você teria dos seus lançamentos:

Fluxo de caixa usando a fórmula somase do excel

Veja que todos os itens (de receitas e despesas), possuem uma série de dados e informações relativos a eles como data (coluna B), valor (coluna E e F) e status (coluna G). Se você quiser saber a soma de todos os valores, condicionados ao critério se o status está pago ou não, basta usar a função SOMASE (ou SUMIF no inglês). Vamos ver como a planilha deve ficar:

tabela de fluxo de caixa real usando a fórmula somase do excel

Na nossa planilha de Fluxo de Caixa criamos uma nova aba (6. Fluxo de Caixa Real) que já tem uma tabela pronta com fórmulas SOMASE. A partir do uso das abas de lançamentos, automaticamente o excel fará uma soma condicionada ao critério Status. Se esse estiver marcado como pago, ele faz a soma, se não, ele não “puxa” o valor da outra aba. Vamos ver o resultado:

fórmula somase na tabela de fluxo de caixa

Neste segundo exemplo, para ter o valor da soma dos meses, fizemos as fórmulas dessa maneira:

  • Para o mês de Janeiro:

    =SOMASE(‘3. Jan’!$G$11:$G$500;”Pago”;‘3. Jan’!$E$11:$E$500)

  • Para o mês de Fevereiro:

    =SOMASE(‘3. Fev’!$G$11:$G$500;”Pago”;‘3. Fev’!$E$11:$E$500)

Nesse caso, cada mês está em uma aba e, todos os meses tem uma estrutura idêntica. Por causa disso, as fórmulas são exatamente iguais, com exceção das referências aos meses (partes em azul).

Outras Aplicações

Você ainda poderia usar a fórmula SOMASE do excel para fazer análises e somas praticamente para qualquer lista de valores que você possua. Por exemplo, é possível fazer somas de grupos de receitas ou despesas, de centros de custos ou de qualquer outro critério que você queira. Abaixo eu mostrei uma imagem da soma condicional de tipos de despesas domésticas com a fórmula SOMASE na nossa planilha de finanças pessoais (esses dados são puxados de uma outra aba):

uso da fórmula somase na planilha de finanças pessoais

3. Entendendo a função SOMASE conceitualmente no Excel:

O que vem escrito quando você inserir a função SOMASE (soma condicionada) é SOMASE (intervalo; critérios; [intervalo_soma])

detalhe da fórmula somase no excel

Onde:

intervalo: É o intervalo de células onde será feita a análise dos dados. Aqui você deve ter a lista onde vai mandar o Excel procurar o seu critério. Nos nossos exemplos, eram as colunas F (exemplo 1 – Prospecção) e coluna G (exemplo 2 – Fluxo de Caixa). Se você quer saber a soma dos valores do vendedor 1, seu intervalo deve ser a coluna onde você registra o nome dos vendedores.

critério: É o valor de referência para validar a condição que você definir. Eles podem existir na forma de um número, expressão, referência de célula, texto ou função. Por exemplo, os critérios podem ser expressos como 32 (número), “Jardel Souza” (expressão), B5 (célula), etc. Nos nossos exemplos, os critérios eram os nomes dos vendedores (exemplo 1) e a expressão “Pago” (exemplo 2)

NotaQualquer critério de texto ou de símbolos lógicos e matemáticos deve estar entre aspas duplas (). Se os critérios forem numéricos, as aspas duplas não serão necessárias.

intervalo_soma: Esse é o intervalo de valores que serão somados se o campo correspondente do item “intervalo” for o mesmo que o critério. Pareceu confuso? Mas não é…veja na imagem abaixo.

tabela simples para uso da formula somase no excel

Se utilizamos a seguinte fórmula =SOMASE(A1:A19;”Moradia”;B1:B19), então conseguimos ver que a coluna A tem os valores do meu intervalo e que o meu critério é “Moradia”. Então todas as vezes que “Moradia” aparecer na coluna A, o valor da coluna B correspondente à linha será somado. Nesse caso os valores das linhas 2 (R$600), 8 (R$1500) e 15 (R$120) devem ser somados, já que nas linhas 2, 8 e 15 a coluna A apresenta o critério “Moradia”.

4. Agora vamos ver o passo a passo na prática de como usar SOMASE

Para fazer o passo a passo na prática de como montar a função SOMASE na sua planilha, eu vou utilizar uma planilha do zero como se fosse a minha planilha de gastos pessoais, mas pense nela apenas como a estrutura geral para aplicar na  sua planilha excel.

1. Estrutura

Você vai precisar ter uma estrutura de tabela, com pelo menos 2 colunas (na nossa eu coloquei 3 só para você ver que isso não gerará problema algum). Uma delas será o seu intervalo e a outra o intervalo de soma. Como estamos falando de uma função que vai somar valores condicionalmente, é importante que o seu intervalo de soma tenha números (pode ser quantidade, valores monetários, etc).

Para ficar menos abstrato, vamos ver a tabela da nossa planilha que estava zerada e agora tem 3 colunas: a primeira com itens de gastos, a segunda com os meus centros de custos pessoais (moradia, saúde, transporte, etc.) e a terceira com os valores que hipoteticamente eu gastei no último mês:

tabela simples com 3 colunas para uso da formula somase no excel

Qualquer tabela que você tenha com pelo menos 2 colunas (sendo uma de valores e outra de análise de critérios) pode ser utilizada para a função SOMASE.

2. Inserindo a fórmula

Agora que você já organizou a estrutura da sua planilha, você precisa criar uma nova tabela (que pode ser feita em uma nova aba ou não) para inserir a função SOMASE.  Para ficar mais visual, vou criar na mesma aba da nossa tabela principal, veja:

tabela criada para uso da fórmula somase no excel

Veja que na nossa estrutura, selecionei 5 critérios diferentes. Para cada critério iremos inserir uma função SOMASE exatamente igual às outras, mas com o critério buscado diferentes. Agora que já temos a estrutura, podemos inserir a função. Para isso, você escolherá a célula onde quer adicionar a mesma. Vamos começar com a soma de valores para o custo Moradia (célula F5).

tabela com uso especifico da formula somase

Nessa célula vamos colocar a fórmula =SOMASE(B2:B19;E5;C2:C19), como exposto abaixo:

formula somase no detalhe

Nessa fórmula, podemos ver que:

– intervalo = B2:B19 – que é exatamente a lista de classificação dos centros de custos pessoais

– critérios = E5 – que representa a expressão “Moradia”. Se quisesse, poderia adicionar só a expressão, sem problemas

– intervalo_soma – C2:C19 – esta é a lista de valores que serão somados, sempre que na linha correspondente tiver o critério em questão

Se preferir, use o assistente de fórmulas. Para isso, Clique na guia Fórmulas > Inserir função> SOMASE. Lá, você terá esses mesmos 3 campos para preencher:

Janela de ajuda para criação de fórmula somase no excel

Ao preencher a fórmula corretamente e apertar a tecla enter, veja que automaticamente aparecerá o valor da soma correto:

3. A melhor maneira de utilizar

Se você continuar aplicando a fórmula SOMASE para as outras células (Saúde, Alimentação, Transporte e Entretenimento) teremos a tabela completamente preenchida com todas as informações:

tabela completa com fórmulas somase

Usando a SOMASE excluindo um critério apenas

Se você quiser, é possível somar todas células de um determinado intervalo excluindo apenas um valor que não te interesse. Para isso, você precisa usar um comando de exceção: <> : significa exceto – nesse caso, serão somados os demais valores exceto os que estiverem contidos neste critério.

Usando o nosso exemplo acima, poderíamos querer saber quanto gastamos com exceção dos gastos com entretenimento. Para isso, iremos usar a função: =SOMASE(B2:B19;”<>Entretenimento”;C2:C19), onde irá ser somado todos os valores, menos os correspondentes ao critério entretenimento.

Deixe a sua opinião e sugestões

Quais outras fórmulas ou funcionalidades do Excel você gostaria de conhecer mais? Deixe um comentário pra mim que eu com certeza vou responder.

Teste seus conhecimentos

Baixe agora uma planilha de exercícios com a função SOMASE com gabarito.

Nós temos algumas planilhas já prontas que utilizam a fórmula SOMASE:

1) Orçamento

2) Fluxo de Caixa Avançado

3) Prospecção de Clientes

LUZ Prime