Excel função Subtotal com Somase – Subtotal+Somase

Neste artigo será demonstrado como utilizar a função subtotal com somase, pois o Excel nativamente não possui esta possibilidade.

Como fazer contse, contses, somase, somases, mediases… etc. Qualquer destas funções condicionais usando o Excel com fórmulas?

Você verá duas formas de fazer isto neste artigo.

A primeira é utilizar as funções SOMARPRODUTO, SE, SUBTOTAL, DESLOC e LIN de modo matricial.

A segunda forma é bem mais simples que é utilizando uma coluna auxiliar facilitando os cálculos.

Problema

Digamos que a você tenha uma lista com dados que você realiza um filtro e quer totalizar em uma fórmula somente os valores que estão sendo apresentados. Neste caso você utilizaria a função Subtotal.

Agora você tem um problema aonde nesta mesma lista você deseja sumarizar não somente pelos campos visíveis, mas também deseja que eles atendam a determinadas condições, como nas funções SOMASE, CONT.SE, SOMASES, CONT.SES, MÉDIASE e MÉDIASES, neste caso você precisará fazer uso desta lógica que explicaremos a seguir.

No nosso problema, um mesmo dono possui 4 empresas que atuam em todas as regiões do estado.

Foi emitido o relatório de vendas do último ano e o diretor de vendas precisa que os dados sejam estruturados em uma análise que permita o filtro das informações conforme suas necessidades, e que conforme o filtro for realizado sejam exibidos o total de vendas de cada empresa e também o total por cada região.

Solução 1 Subtotal com Somases

Então foi disponibilizada a seguinte lista de dados com as informações das vendas, separadas por empresa, ano, mês e região:

Á partir desta planilha utilizamos a função remover duplicatas na coluna A e D e chegamos a seguinte lista que estruturamos para os subtotais:

Agora precisamos inserir as funções para a soma de total por empresa, e total por empresa e região, e que ao filtrar os dados do relatório estes dados sejam atualizados de forma automática.

Para o preenchimento da coluna Total utilizamos a seguinte função, que explicaremos abaixo:

A função SOMARPRODUTO tem por finalidade realizar a soma de valores conforme determinadas condições, por exemplo: =SOMARPRODUTO(-(A:A=I3);-(B:B=J3);-(G:G=K3);(E:E)), neste caso, somar quando o valor da coluna E quando o valor da coluna A for igual á célula I3, o valor da coluna B for igual á J3 e o valor da coluna G for igual a célula K3.

Desta forma inserimos na primeira parte desta fórmula a seguinte função: SE($A$9:$A$30002=A2;$E$9:$E$30002).

Neste caso temos que, SE o valor das células de A9 á A30002 for igual á A2 então retornar o valor das células E9 á E30002, conforme a linha selecionada. Por exemplo, se na célula A9 o valor for igual á A2 então a célula E2 será retornada em uma lista, se o valor da célula A10 for diferente de A2 então não será retornada. Veja:

Observação: Esta situação somente é real porque esta é uma função que será calculada como Matricial.

Na segunda parte da função que totaliza os dados filtrados por empresa temos a seguinte fórmula *SUBTOTAL(3;DESLOC($A$9;LIN($A$9:$A$30002)-LIN($A$9);)).

Nesta função temos o uso da função SubTotal que aplica uma determinada função somente aos dados filtrados em uma tabela. Neste caso utilizamos a opção 3, que é uma contagem de valores, este valor está multiplicando o valor selecionado na parte anterior da função que já foi explicada.

Agora temos a parte que faz com que a operação de subtotal com somase seja possível: DESLOC($A$9;LIN($A$9:$A$30002)-LIN($A$9);).

A função acima faz com que o Excel entenda que os dados filtrados devem ser entendidos como dados matriciais e não como uma lista dados normal para a função subtotal. Para isso deve ser aplicada a função Desloc e selecionada a primeira linha da lista de dados, em seguida é definida quantas linhas o range de dados deve ser deslocado, no caso apontamos para LIN() que é uma função que retorna o número de uma determinada linha e selecionamos os dados de A9 á A30002 e diminuímos a quantidade de linhas da célula A9, ou seja: 29994-1 = 29993, ou seja deslocará o intervalo de $A$9 para $A$9:$A$30002, somando o total de linhas.

Observação: Para obtermos uma função de subtotal com somase este último artifício é uma parte importante, porque faz com que o Excel entenda que os dados devem ser utilizados como dados matriciais e não como uma lista normal de dados.

Após a fórmula estar completa é necessário que ao invés de pressionar ENTER, sejam pressionadas as teclas CTRL+SHIFT+ENTER fazendo com que o Excel interprete a fórmula como uma fórmula matricial.

Da mesma forma por regiões fizemos a mesma função, incluindo mais uma função SE, que irá validar se a região é a mesma da célula determinada para a função.

{=SOMARPRODUTO(SE($D$9:$D$30002=C$1;SE($A$9:$A$30002=$A2;$E$9: $E$30002))*SUBTOTAL(3;DESLOC($A$9;LIN($A$9:$A$30002)-LIN($A$9);)))}

Na função acima, leia-se a parte em negrito da seguinte maneira: Se o valor das células D9:D30002 (Regiões) for igual ao da célula C1 (Norte), então, aí entra em uma outra função SE e verifica se o valor das células A9:A30002 (Empresas) for igual ao da célula A2 (Roupas infantis Ltda) então retornar o valor da célula de E9 á E30002.

Solução 2 – Somases com subtotal

A segunda solução consiste em usarmos uma coluna auxiliar na tabela de dados e à partir dela identificarmos e a informação está visível ou não.

Como fazemos isso? Siga os passos:

1. Adicione uma coluna auxiliar na sua lista ou tabela, no exemplo usamos uma tabela, o que é recomendável, pois se os dados forem atualizados a coluna irá acompanhar os dados.
2. Nesta coluna adicione a seguinte função =SUBTOTAL(3;[@Empresa]), substitua empresa por qualquer campo que tenha informação na sua tabela. Este cálculo fará com que retorne 1 ou 0, no caso de estar visível retornará 1 senão 0.                                                   

   

3. Agora nos campos de soma utilize uma função SOMASES fazendo as condicionais normalmente, mas definindo como uma das condições que a coluna Auxiliar tenha o valor 1. Desta forma você terá a função de agregação condicional SOMASES, CONTSE, qualquer uma e com SUBTOTAL.

   

4. Por fim pode usar também o recurso de segmentação de dados para que desta forma você tenha assim um filtro visual e dinâmico, caso tenha optado por usar uma tabela.

Download da planilha Exemplo

Para fazer o download da planilha exemplo já preenchida basta assinar a nossa newsletter gratuita.

Você receberá e-mails periódicos com novos artigos, planilhas e cursos, podendo sair quando quiser.

Baixe a planilha

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: