Total em execução (também chamado soma cumulativa) é comumente usado em muitas situações. É uma métrica que informa qual é a soma dos valores até agora.
Por exemplo, se você tiver os dados de vendas mensais, um total corrente dirá quantas vendas foram feitas até um dia específico a partir do primeiro dia do mês.
Existem também algumas outras situações em que o total corrente é frequentemente usado, como calcular seu saldo de caixa em seus extratos bancários / razão, contar calorias em seu plano de alimentação, etc.
No Microsoft Excel, existem várias maneiras diferentes de calcular os totais em execução.
O método escolhido também depende de como seus dados são estruturados.
Por exemplo, se você tem dados tabulares simples, pode usar uma fórmula SOMA simples, mas se tiver uma tabela do Excel, é melhor usar referências estruturadas. Você também pode usar o Power Query para fazer isso.
Neste tutorial, vou cobrir todos esses métodos diferentes para calcular totais em execução no Excel.
Então vamos começar!
Calculando Total Corrente com Dados Tabulares
Se você tiver dados tabulares (ou seja, uma tabela no Excel que não é convertida em uma tabela do Excel), você pode usar algumas fórmulas simples para calcular os totais corridos.
Usando o operador de adição
Suponha que você tenha dados de vendas por data e deseja calcular o total corrente na coluna C.
Abaixo estão as etapas para fazer isso.
Passo 1 - Na célula C2, que é a primeira célula onde você deseja o total parcial, insira
= B2
Isso simplesmente obterá os mesmos valores de venda na célula B2.
Passo 2 - Na célula C3, insira a fórmula abaixo:
= C2 + B3
etapa 3 - Aplique a fórmula a toda a coluna. Você pode usar a alça de Preenchimento para selecionar e arrastar ou simplesmente copiar e colar a célula C3 em todas as células restantes (o que ajustaria automaticamente a referência e forneceria o resultado correto).
Isso lhe dará o resultado conforme mostrado abaixo.
É um método muito simples e funciona bem na maioria dos casos.
A lógica é simples - cada célula pega o valor acima dela (que é a soma cumulativa até a data anterior) e adiciona o valor na célula adjacente a ela (que é o valor de venda para aquele dia).
Há apenas uma desvantagem - caso você exclua qualquer uma das linhas existentes neste conjunto de dados, todas as células abaixo retornariam um erro de referência (#REF!)
Se isso for uma possibilidade com seu conjunto de dados, use o próximo método que usa a fórmula SUM
Usando SUM com Referência de Célula Parcialmente Bloqueada
Suponha que você tenha dados de vendas por data e deseja calcular o total corrente na coluna C.
Abaixo está a fórmula SUM que lhe dará o total corrente.
= SOMA ($ B $ 2: B2)
Deixe-me explicar como essa fórmula funciona.
Na fórmula SUM acima, usei a referência para adicionar como $ B $ 2: B2
- $ B $ 2 - esta é uma referência absoluta, o que significa que quando copio a mesma fórmula nas células abaixo, esta referência não vai mudar. Portanto, ao copiar a fórmula na célula abaixo, a fórmula mudaria para SUM ($ B $ 2: B3)
- B2 - esta é a segunda parte da referência que é uma referência relativa, o que significa que isso se ajustaria conforme copio a fórmula para baixo ou para a direita. Então, ao copiar a fórmula na célula abaixo, este valor se tornará B3
Leia também: Referências de células absolutas, relativas e mistas no Excel
A grande vantagem desse método é que, no caso de você excluir qualquer uma das linhas do conjunto de dados, essa fórmula se ajustaria e ainda forneceria os totais corretos corretos.
Calculando o total em execução na tabela do Excel
Ao trabalhar com dados tabulares no Excel, é uma boa ideia convertê-los em uma tabela do Excel. Isso torna muito mais fácil gerenciar os dados e também torna mais fácil usar ferramentas como o Power Query e o Power Pivot.
Trabalhar as tabelas do Excel traz benefícios como referências estruturadas (o que torna muito fácil referir-se aos dados da tabela e usá-los em fórmulas) e ajuste automático das referências caso você adicione ou exclua dados da tabela.
Embora você ainda possa usar a fórmula acima que mostrei em uma tabela do Excel, deixe-me mostrar alguns métodos melhores para fazer isso.
Suponha que você tenha uma tabela Excel conforme mostrado abaixo e deseja calcular o total corrente na coluna C.
Abaixo está a fórmula que fará isso:
= SUM (SalesData [[# Headers], [Sale]]: [@ Sale])
A fórmula acima pode parecer um pouco longa, mas você não precisa escrevê-la sozinho. o que você vê na fórmula de soma são chamados de referências estruturadas, que é a maneira eficiente do Excel de se referir a pontos de dados específicos em uma tabela do Excel.
Por exemplo, SalesData [[# Headers], [Sale]] refere-se ao cabeçalho Sale na tabela SalesData (SalesData é o nome da tabela Excel que dei quando criei a tabela)
E [@Venda] se refere ao valor na célula na mesma linha da coluna Venda.
Acabei de explicar isso aqui para sua compreensão, mas mesmo que você não saiba nada sobre referências estruturadas, ainda pode criar essa fórmula facilmente.
Abaixo estão as etapas para fazer isso:
- Na célula C2, insira = SUM (
- Selecione a célula B1, que é o cabeçalho da coluna que possui o valor de venda. Você pode usar o mouse ou as teclas de seta. Você notará que o Excel insere automaticamente a referência estruturada para aquela célula
- Adicione um: (símbolo de dois pontos)
- Selecione a célula B2. O Excel iria inserir novamente automaticamente a referência estruturada para a célula
- Feche o colchete e pressione Enter
Você também notará que não precisa copiar a fórmula em toda a coluna, uma tabela do Excel faz isso automaticamente para você.
Outra grande vantagem desse método é que, no caso de você adicionar um novo registro neste conjunto de dados, a tabela do Excel calcularia automaticamente o total corrente para todos os novos registros.
Embora tenhamos incluído o cabeçalho da coluna em nossa fórmula, lembre-se de que a fórmula ignoraria o texto do cabeçalho e consideraria apenas os dados da coluna
Calculando o total em execução usando o Power Query
O Power Query é uma ferramenta incrível quando se trata de conectar-se a bancos de dados, extrair os dados de várias fontes e transformá-los antes de colocá-los no Excel.
Se você já está trabalhando com o Power Query, seria mais eficiente adicionar totais corridos enquanto transforma os dados no próprio editor do Power Query (em vez de primeiro obter os dados no Excel e, em seguida, adicionar os totais corridos usando qualquer um dos itens acima métodos cobertos acima).
Embora não haja nenhum recurso embutido no Power Query para adicionar totais corridos (o que eu gostaria que houvesse), você ainda pode fazer isso usando uma fórmula simples.
Suponha que você tenha uma tabela do Excel conforme mostrado abaixo e deseja adicionar os totais corridos a esses dados:
Abaixo estão as etapas para fazer isso:
- Selecione qualquer célula na tabela do Excel
- Clique em Dados
- Na guia Get & Transform, clique no ícone da tabela / intervalo. Isso abrirá a tabela no editor do Power Query
- [Opcional] Caso sua coluna Data ainda não esteja classificada, clique no ícone de filtro na coluna Data e, a seguir, clique em Classificar em ordem crescente
- Clique na guia Adicionar coluna no editor do Power Query
- No grupo Geral, clique no menu suspenso Coluna de índice (não clique no ícone Coluna de índice, mas na pequena seta preta inclinada ao lado para mostrar mais opções)
- Clique na opção ‘De 1’. Isso adicionará uma nova coluna de índice que começaria de um e inserirá números incrementando por 1 em toda a coluna
- Clique no ícone ‘Coluna Personalizada’ (que também está na guia Adicionar Coluna)
- Na caixa de diálogo da coluna personalizada que se abre, insira um nome para a nova coluna. neste exemplo, usarei o nome ‘Total em execução’
- No campo Fórmula da coluna personalizada, insira a fórmula abaixo: List.Sum (List.Range (# ”Índice adicionado” [Venda], 0, [Índice]))
- Certifique-se de que há uma caixa de seleção na parte inferior da caixa de diálogo que diz - "Nenhum erro de sintaxe foi detectado"
- Clique OK. Isso adicionaria uma nova coluna de total em execução
- Remova a coluna do índice
- Clique na guia Arquivo e depois em ‘Fechar e Carregar’
As etapas acima inserem uma nova planilha em sua pasta de trabalho com uma tabela que contém os totais corridos.
Agora, se você está pensando que essas são etapas demais em comparação com os métodos anteriores de uso de fórmulas simples, você está certo.
Se você já tem um conjunto de dados e tudo o que precisa fazer é adicionar os totais em execução, é melhor não usar o Power Query.
Usar o Power Query faz sentido quando você precisa extrair dados de um banco de dados ou combinar dados de várias pastas de trabalho diferentes e, no processo, também adicionar totais corridos a ele.
Além disso, depois de fazer essa automação usando o Power Query, da próxima vez que seu conjunto de dados for alterado, você não precisará fazer isso novamente, você pode simplesmente atualizar a consulta e ela fornecerá o resultado com base no novo conjunto de dados.
Como é que isso funciona?
Agora, deixe-me explicar rapidamente o que acontece neste método.
A primeira coisa que fazemos no editor do Power Query é inserir uma coluna de índice começando em um e incrementando em um conforme vai descendo pelas células.
Fazemos isso porque precisamos usar essa coluna enquanto calculamos o total corrente em outra coluna que inseriremos na próxima etapa.
Em seguida, inserimos uma coluna personalizada e usamos a fórmula abaixo
List.Sum (List.Range (# "Added Index" [Sale], 0, [Index]))
Esta é uma fórmula List.Sum que forneceria a soma do intervalo especificado nela.
E esse intervalo é especificado usando a função List.Range.
A função List.Range fornece o intervalo especificado na coluna de venda como saída e esse intervalo muda com base no valor do índice. Por exemplo, para o primeiro registro, o intervalo seria simplesmente o valor da primeira venda. E conforme você desce nas células, esse intervalo se expande.
Então, para a primeira célula. List.Sum forneceria apenas a soma do valor da primeira venda e, para a segunda célula, forneceria a soma dos dois primeiros valores de venda e assim por diante.
Embora esse método funcione bem, fica muito lento com grandes conjuntos de dados - milhares de linhas. Se você estiver lidando com um grande conjunto de dados e quiser adicionar totais corridos a ele, dê uma olhada neste tutorial que mostra outros métodos que são mais rápidos.Calculando o total em execução com base nos critérios
Até agora, vimos exemplos em que calculamos o total corrente para todos os valores em uma coluna.
Mas pode haver casos em que você deseja calcular o total corrente para registros específicos.
Por exemplo, abaixo eu tenho um conjunto de dados e desejo calcular o total em execução para impressoras e scanners separadamente em duas colunas diferentes.
Isso pode ser feito usando uma fórmula SOMASE que calcula o total em execução enquanto garante que a condição especificada seja atendida.
Abaixo está a fórmula que fará isso para as colunas da impressora:
= SOMASE ($ C $ 2: C2, $ D $ 1, $ B $ 2: B2)
Da mesma forma, para calcular o total em execução para Scanners, use a fórmula abaixo:
= SUMIF ($ C $ 2: C2, $ E $ 1, $ B $ 2: B2)
Nas fórmulas acima, usei SOMASE, o que me daria a soma em um intervalo quando os critérios especificados forem atendidos.
A fórmula leva três argumentos:
- alcance: este é o intervalo de critérios que seria verificado em relação aos critérios especificados
- critério: este é o critério que seria verificado apenas se este critério fosse atendido, então os valores no terceiro argumento, que é o intervalo de soma, seriam adicionados
- [intervalo_soma]: este é o intervalo de soma a partir do qual os valores seriam adicionados se os critérios fossem atendidos
Além disso, no alcance e intervalo_soma argumento, bloqueei a segunda parte da referência, de modo que, à medida que descemos as células, o intervalo continua se expandindo. Isso nos permite apenas considerar e adicionar valores até essa faixa (portanto, totais contínuos).
Nesta fórmula, usei a coluna de cabeçalho (Impressora e Scanner) como critério. você também pode codificar os critérios se os cabeçalhos das colunas não forem exatamente iguais ao texto dos critérios.
No caso de haver várias condições que precisam ser verificadas, você pode usar a fórmula SUMIFS.
Total de execução em tabelas dinâmicas
Se desejar adicionar totais em execução em um resultado de Tabela Dinâmica, você pode fazer isso facilmente usando uma funcionalidade embutida em Tabelas Dinâmicas.
Suponha que você tenha uma Tabela Dinâmica conforme mostrado abaixo, onde eu tenho a data em uma coluna e o valor de venda na outra coluna.
Abaixo estão as etapas para adicionar uma coluna adicional que mostrará o total acumulado das vendas por data:
- Arraste o campo Venda e coloque-o na área Valor.
- Isso adicionará outra coluna com os valores de vendas
- Clique na opção Soma da Venda2 na área de Valor
- Clique na opção ‘Configurações do campo de valor’
- Na caixa de diálogo Configurações do campo de valor, altere o nome personalizado para ‘Totais em execução’
- Clique na guia ‘Mostrar valor como’
- Na lista suspensa Mostrar valor como, selecione a opção ‘Total em execução em’
- Nas opções do campo Base, certifique-se de que a Data esteja selecionada
- Clique OK
As etapas acima mudariam a segunda coluna de venda para a coluna Total em execução.
Portanto, essas são algumas das maneiras que você pode usar para calcular o total em execução no Excel. Se você tiver dados em formato tabular, poderá usar fórmulas simples e, se tiver uma tabela do Excel, poderá usar fórmulas que fazem uso de referências estruturadas.
Também cobri como calcular o total em execução usando o Power Query e em tabelas dinâmicas.
Espero que você tenha achado este tutorial útil.
