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

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.

Neste artigo você verá como sumarizar dados filtrados utilizando funções do Excel como aplicado pela função Subtotal.

Para realizar esta tarefa serão utilizadas as funções SOMARPRODUTO, SE, SUBTOTAL, DESLOC e LIN, e esta função será matricial.

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

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

Subtotal com somase relatório

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

Subtotal com somase

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:

Subtotal com somase 1 condição

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:

Subtotal com somase explicação 1

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.

Somase com subtotal Excel

DIGITE O SEU EMAIL PARA FAZER O DOWNLOAD DOS ARQUIVOS

Abraço

Marcos Rieper