[Excel] – Função SOMASE OU

Técnica para agrupar dados em análises no Excel capa
[Excel] Agrupar e classificar situações iguais
30 de setembro de 2018
Classificar automaticamente com matriciais números repetidos capa
Classificar dados repetidos no Excel com fórmula
6 de outubro de 2018

[Excel] Somase OU

[Excel] – Função SOMASE OU

Não existe no Excel uma forma padrão de utilizar as funções Somase OU juntas, pois a mesma tem por definição atender todas as restrições que lhe são impostas.

Por isso, caso você queira soma todos os valores de um mês e determinadas regiões não será possível realizar a soma utilizando apenas uma função. Veja também nossas planilhas e cursos em loja.guiadoexcel.com.br

Uma das formas de realizar a SOMASE com OU é utilizando uma função matricial no Excel.

Veja abaixo o exemplo aonde aplicamos a fórmula e também a explicação do seu funcionamento que abre portas para várias outras aplicações com fórmulas matriciais.

[Excel] Fórmula Somase OU

Função somase ou

Fórmula SOMASE OU

Para o nosso exemplo temos uma lista simples com nomes aleatórios e valores que precisamos somar se houver um ou mais nomes na lista de condições.

A lista para a soma condicional OU

Somase ou

Lista de dados SOMASE OU

Na lista acima, disposta no endereço de B8 à C21 temos uma lista com nomes e valores, poderiam ser vendedores, regiões ou qualquer outra informação e valores.

O objetivo é somar os valores que atendam na coluna B os valores abaixo.

Somase ou

Condições SOMASE OU

Nesta pequena lista de condições, que poderia ser bem maior sem problemas, nós temos os valores de c, h e d.

Devem ser então somados os valores que houverem na lista e sejam c ou h ou d, retornando na soma os valores da coluna C.

A fórmula para soma OU

As funções SOMASE ou SOMASES não têm a possibilidade de serem utilizadas com funções condicionais OU.

Por isso criamos uma função MATRICIAL.

Uma função matricial é uma função que trabalha de forma diferente da maioria das fórmulas do Excel.

Função matricial é uma fórmula no Excel aonde os cálculos são realizados por célula e não para o intervalo. Por exemplo, se o valor da célula B8 for igual a h então retornar o valor C8. E assim para todas as células do intervalo.

Para a solução deste problema utilizamos a fórmula matricial abaixo.

Somase ou

Fórmula SOMASE OU Excel

Fórmula: {=SOMA(SE(ÉERROS(CORRESP($B$9:$B$21;$E$9:$E$11;0));0;1)*($C$9:$C$21))}

Entendendo a fórmula de SOMASE OU

Como citado acima utilizamos uma fórmula matricial para a soma, vamos entender por partes como ela funciona.

Somase ou

Solução matricial SOMASE OU

Fórmula: {=SOMA(SE(ÉERROS(CORRESP($B$9:$B$21;$E$9:$E$11;0));0;1)*($C$9:$C$21))}

  1. CORRESP($B$9:$B$21;$E$9:$E$11;0): Nesta parte da fórmula temos uma busca utilizando o Corresp verificando célula por célula se existe a célula de B9 até B21 no intervalo de E9:E11, lembrando que está sendo comparado um a um. Resultado: {#N/D;#N/D;#N/D;#N/D;#N/D;#N/D;1;3;#N/D;#N/D;#N/D;2;2}
  2. SE(ÉERROS(CORRESP($B$9:$B$21;$E$9:$E$11;0));0;1): Caso não encontre o valor o Excel retornará, senão 1. É isto que está sendo realizado pelas funções SE(ÉERROS. O retorno da função CORRESP poderá ser o número da linha ou senão um erro. Para evitar que sejam utilizados os números das linhas é substituído pelo 1. Resultado: {0;0;0;0;0;0;1;1;0;0;0;1;1}
  3. *($C$9:$C$21): Nesta parte é multiplicado o valor de cada uma das linhas da matriz resultado anterior por pela lista de valores correspondente. Resultado: {1;10;4;5;6;7;8;9;10;11;12;13;14}
  4. SE(ÉERROS(CORRESP($B$9:$B$21;$E$9:$E$11;0));0;1)*($C$9:$C$21): Aqui temos então a multiplicação das matrizes de 1 e 0 pelos valores correspondentes de cada uma das linhas. Resultado: {0;0;0;0;0;0;8;9;0;0;0;13;14}
  5. {=SOMA(: Esta função irá simplesmente somar os valores da resultante da lista do item 4. Perceba que temos também o operador {, ele indica a fórmula matricial. Toda vez que utilizamos uma fórmula matricial é necessário que sejam pressionadas as teclas CTRL+SHIFT+ENTER para que o Excel entenda como tratar a fórmula. Resultado: 44.

Conclusão

Com a fórmula matricial foi possível foi possível resolver a soma condicional, mas ela também abre uma ampla gama de possibilidades para resolver muitos outros problemas com fórmulas. Vide o CORRESP que permite que sejam tratados valores de listas de formas diferentes retornando listas de dados para células.

Download

Para o download do exemplo utilizado neste artigo preencha o seu nome e e-mail no formulário abaixo.

Abraço

Marcos Rieper


Clique aqui e leia mais sobre Excel VBA. https://www.guiadoexcel.com.br/vba/ O Guia do Excel foi criado por Marcos Rieper e oferece artigos, dicas, tutoriais e modelos de planilhas prontas. Aqui você encontra tudo sobre Excel, seja de nível básico, intermediário,  avançado e VBA. O Guia do Excel oferece diversos materiais completamente gratuitos para download. Navegue em nosso site e confira! Conheça também a nossa Loja do Excel https://loja.guiadoexcel.com.br/
Cursos

Curso Excel Básico – 1 ano de acesso

R$99,00

COMPRAR
Cursos

Curso Excel Completo – Curso Excel Básico + Curso Excel Avançado – Acesso Vitalício

R$218,00 R$179,00

COMPRAR
Cursos

Curso Excel Master – Curso Excel Básico + Curso Excel Avançado + Curso VBA Excel + LP – Acesso Vitalício

R$357,00 R$249,00

COMPRAR
Cursos

Curso Excel PRO – Curso Excel Avançado + Curso VBA Excel + Lógica de programação – Acesso Vitalício

R$258,00 R$199,00

COMPRAR

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Inscreva-se no nosso canal do Youtube!


Junte-se ao nosso canal do Youtube. Começamos em abril de 2016, mas já temos mais de 06:00 h de treinamentos gratuitos e este número irá aumentar. Vídeos novos todos os sábados.