[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.

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

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.

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.

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.

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

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}
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}
*($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}
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}
{=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.

Baixe a planilha

Abraço

Marcos Rieper

Curso Excel Completo – Do Básico ao VBA

Quer aprender Excel do Básico, passando pela Avançado e chegando no VBA? Clique na imagem abaixo:

Avalie este post