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

15
Subtotal com somase explicação 3

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

Seu nome (obrigatório)

Seu e-mail (obrigatório)

Abraço

Marcos Rieper

15 COMENTÁRIOS

  1. Amigo, boa tarde, estou tetando fazer esta formula desde as 10 das manhã, e não estou conseguindo sempre da erro, inclusive baixei a planilha a sua planilha, e se clica na célula da formula, e editar quando da enter, ela tbm da erro, esse calculo não serve mais? Existe outra, estou necessitando com urgência…

    Meu Excel é o 2013.

  2. Brother,

    Estou tentando fazer uma resolução e não consigo com a formula.

    Vamos lá, tenho a seguinte situação:

    COMP / DESCRIÇÃO / INF

    Tenho esses 3 campos, e a coluna INF pode ter 3 informações: MELHOR, MENOR E IGUAL, ao realizar o filtro no COMP por exemplo, preciso que venha qtd total de cada um : MELHOR, MENOR E IGUAL.

    MELHOR:
    MENOR:
    IGUAL:

    Pode me ajudar?

    Obrigado,

  3. Gostei muito da ideia, mas porque os valores da planilha de resultados muda cada vez que um filtro é aplicado e retirado?
    Fiz o teste selecionando a Empresa roupas infantis. Os valores mudam, quando deveria apenas desaparecerem os valores das outras empresas. E quando retiro o filtro, outros valores aparecem, diferentes dos valores iniciais.

    Pode me esclarecer?

  4. Bom dia Marcos, Tenho um problema para resolver com uma planilha , cuja solução é exatamente esta, no entanto, não coloquei corretamente o problema. Ocorre que quando o mesmo filtro é aplicado e retirado, mais de duas vezes, os resultados não são iguais, mesmo não havendo alteração da planilha de dados. Isso está ocorrendo na sua planilha de exemplo. A sua planilha vem, originalmente, com o filtro aplicado para a região leste, apresentando os seguintes valores: 112806, 110617, 109646 e 100423, respectivamente para as células E2 à E5. Quando retiro esse filtro os valores aparecem totalizados em todas as células. OK. Ai volto a aplicar o filtro para a região leste, e os valores retornados são diferentes, sem haver alteração na planilha de dados. Faça o teste por favor. Acada retirada e/ou aplicação dos mesmos filtros, os resultados apresentados se mostram diferentes. Essa é a questão que coloco para você verificar.Eu posso te enviar minha planilha para você me orientar?

  5. Olá Marcos tudo bem? Parabéns pelo post, achei incrível, mas infelizmente não consegui aplicar ao que queria. Será que poderia me ajudar?

    Meu caso é mais simples, vou tentar explicar: tenho dois centros de custo, preciso saber o subtotal de entrada e saída de cada um deles, ou seja, “subtotal dos valores menores que zero” e “subtotal dos valores maiores que zero”

    Será que poderia me ajudar qual a melhor maneira?
    Obrigado e parabéns novamente!

  6. Olá Marcos, boa tarde! Parabéns pelo post, acho magnifico as funções do Excel e como essa ferramenta pode nos ajudar, porém não consegui aplicar a fórmula como ela deve ser. Meu problema é o seguinte, tenho um relatório de custos de um projeto inteiro, onde em uma célula, eu indico se o item é materia prima (MP), serviços (SV), consumiveis (CONS), ou lista antecipada (LA), e em outra coluna tenho a informação da quantidade de peças a serem utilizadas no projeto, no entanto quero que o total apresentado desconsidere os valores onde tiver SV e LA, e que o subtotal seja indicado de acordo com o filtro. Será que poderia me ajudar? Desde já agradeço.

  7. Olá Marcos … Parabéns pela iniciativa. A título de contribuição gostaria de deixar registrado que alguns de nós estamos tendo dificuldade no momento de habilitar a Matriz, onde vc diz que é necessário pressionar CRTL+Enter…. Aí após eu tentar um pouco consegui habilitar a matriz pressionando CTRL+Shift+Enter.
    Abraços

  8. Srs.
    Existe alguma função que possa contar apenas as células que estão com valores >0, ao utilizar um filtro? tipo o que o subtotal faz?
    Agradeço toda a ajuda.

DEIXE UMA RESPOSTA

Please enter your comment!
Please enter your name here