total em execução (também conhecido comosoma cumulativa) é muito comumente usado em muitos casos.Este é um indicador que informa qual é a soma dos valores até agora.
Por exemplo, se você tiver dados de vendas mensais, um total em execução informará quantas vendas foram feitas desde o primeiro dia do mês até uma data específica.
Existem outras situações em que os totais são frequentemente usados, como calcular saldos de caixa em extratos bancários/livros, calcular calorias em planos de refeições, etc.
No Microsoft Excel, existem várias maneiras diferentes de calcular os totais em execução.
O método escolhido também depende da estrutura dos dados.
Por exemplo, se você tiver dados tabulares simples, poderá 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, abordarei todos elesCalcular um total em execução no Excelmétodos diferentes.
Então vamos começar!
Conteúdo
Calcular totais em execução usando dados tabulares
Se você tiver dados tabulares (ou seja, tabelas no Excel que não são convertidas em tabelas do Excel), poderá usar algumas fórmulas simples para calcular os totais em execução.
Use o operador de adição
Suponha que você tenha dados de vendas por data e queira在 na coluna CCálculoExecução total.
Aqui estão os passos para fazer isso.
Passo 1 – Na célula C2, a primeira célula para a qual você deseja executar o total, insira
= B2
Isso apenas obtém o mesmo valor de vendas na célula B2.
Etapa 2– Na célula C3, insira a seguinte fórmula:
= C2 + B3
Etapa 3– Aplicar a fórmula a toda a coluna.Você pode usar a alça de preenchimento para selecioná-la e arrastá-la ou simplesmente copiar e colar a célula C3 em todas as células restantes (isso ajustará automaticamente a referência e fornecerá o resultado correto).
Isso lhe dará o resultado mostrado abaixo.
Este é um método muito simples que funciona bem na maioria dos casos.
A lógica é simples - cada célula pega o valor nela (que é a soma acumulada até o dia anterior) e adiciona o valor na célula adjacente (que é o valor de vendas do dia atual).
Há apenas uma desvantagem - se você excluir qualquer linha existente neste conjunto de dados, todas as células abaixo retornarão um erro de referência (#REF!)
Se o seu conjunto de dados tiver essa possibilidade, use o próximo método usando a fórmula SUM
Use SUM com referências de célula parcialmente bloqueadas
Suponha que você tenha dados de vendas por data e queira calcular um total corrente na coluna C.
Abaixo está a fórmula SUM que lhe dará um total em execução.
= SUM ($ 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 esta referência não muda quando copio a mesma fórmula na célula abaixo.Então, quando você copiar a fórmula na célula abaixo, a fórmula mudará para SUM($B$2:B3)
- B2 - Essa é a segunda parte da referência, é uma referência relativa, ou seja, quando eu copio a fórmula para baixo ou para a direita, isso se ajusta.Então ao copiar a fórmula na célula abaixo, o valor passa a ser B3
O bom dessa abordagem é que, se você excluir qualquer linha do conjunto de dados, essa fórmula se ajustará e ainda fornecerá o total de execução correto.
Calcular o total em execução na planilha do Excel
Ao trabalhar com dados tabulares no Excel, é melhor convertê-los em uma tabela do Excel.Facilita o gerenciamento de dados e também facilita o uso de ferramentas como Power Query e Power Pivot.
O uso de uma tabela do Excel oferece vários benefícios, como referências estruturadas (que facilitam muito a referência de dados na tabela e o uso em fórmulas) e o ajuste automático de referências à medida que você adiciona ou remove dados da tabela.
Embora você ainda possa usar a fórmula acima que mostrei em uma planilha do Excel, deixe-me mostrar algumas maneiras melhores de fazer isso.
Suponha que você tenha uma tabela do Excel como a abaixo e queira calcular um total em execução na coluna C.
Aqui está a fórmula para fazer isso:
=SOMA(DadosVendas[[#Cabeçalhos],[Venda]]:[@Venda])
A fórmula acima pode parecer um pouco longa, mas você não precisa escrevê-la sozinho.O que você vê em uma fórmula de soma é chamado de referência estruturada e é uma maneira eficiente de o Excel fazer referência a pontos de dados específicos em uma tabela do Excel.
Por exemplo, SalesData[[#Headers],[Sale]] refere-se ao cabeçalho Sales na tabela SalesData (SalesData é o nome da tabela do Excel que dei quando criei a tabela)
Considerando que [@Sale] se refere ao valor em uma célula na mesma linha da coluna Venda.
Estou apenas explicando 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.
Aqui estão os passos para fazer isso:
- Na célula C2, digite =SOMA(
- Selecione a célula B1, que é o cabeçalho da coluna com valor de venda.Você pode usar o mouse ou usar as teclas de seta.Você notará que o Excel insere automaticamente uma referência estruturada à célula
- Adicionar: (dois pontos)
- Selecione a célula B2.O Excel inserirá automaticamente referências estruturadas às células novamente
- Feche o colchete e pressione Enter
Você também notará que não precisa copiar a fórmula na coluna inteira, a planilha do Excel faz isso para você automaticamente.
Outro benefício dessa abordagem é que, se você adicionar um novo registro a esse conjunto de dados, a planilha do Excel calculará automaticamente um total em execução para todos os novos registros.
Embora tenhamos incluído os cabeçalhos das colunas na fórmula, lembre-se de que a fórmula ignora o texto do cabeçalho e considera apenas os dados da coluna
Calcular totais em execução com o Power Query
O Power Query é uma ferramenta incrível quando se trata de conectar-se a bancos de dados, extrair dados de várias fontes e transformá-los antes de colocá-los no Excel.
Se você já estiver usando o Power Query, é mais eficiente adicionar totais em execução ao transformar os dados no próprio Editor do Power Query (em vez de primeiro obter os dados no Excel e adicionar os totais em execução usando qualquer um dos métodos acima).
Embora não haja uma funcionalidade interna no Power Query para adicionar totais em execução (eu gostaria que houvesse), você ainda pode fazer isso com uma fórmula simples.
Suponha que você tenha uma tabela do Excel com esta aparência e queira adicionar totais em execução a esses dados:
Aqui estão os passos para fazer isso:
- Selecione qualquer célula em uma tabela do Excel
- clique em dados
- Na guia Obter e Transformar, clique no ícone de Tabela/Intervalo.Isso abrirá a tabela no Editor do Power Query
- [Opcional] Se sua coluna de data ainda não estiver classificada, clique no ícone de filtro na coluna de data e 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 revelar mais opções)
- Clique na opção "De 1".Fazer isso adicionará uma nova coluna de índice que começará em 1 e inserirá números incrementados em 1 em toda a coluna
- Clique no ícone Colunas Personalizadas (também na guia Adicionar Colunas)
- Na caixa de diálogo Colunas Personalizadas que é aberta, insira um nome para a nova coluna.Neste exemplo vou usar o nome "Running Total"
- No campo Fórmula de coluna personalizada, insira a seguinte fórmula:List.Sum(List.Range(#"Índice adicionado"[Venda],0,[Índice]))
- Verifique se há uma caixa de seleção na parte inferior da caixa de diálogo que diz "Nenhum erro de sintaxe detectado"
- Clique OK.Isso adicionará uma nova coluna de total em execução
- soltar coluna de índice
- Clique na guia Arquivo e, em seguida, clique em Fechar e carregar
As etapas acima inserirão uma nova planilha em sua pasta de trabalho com uma tabela com totais em execução.
Agora, se você acha que esses são muitos passos em comparação com a abordagem anterior com fórmulas simples, você está certo.
Se você já tem um conjunto de dados e tudo o que precisa fazer é adicionar totais em execução, é melhor não usar o Power Query.
O uso do Power Query faz sentido em situações em que você precisa extrair dados de um banco de dados ou combinar dados de várias pastas de trabalho diferentes e também adicionar totais em execução a eles no processo.
Além disso, depois de automatizar isso com o Power Query, na próxima vez que seu conjunto de dados for alterado, você não precisará fazer isso novamente, basta atualizar a consulta e ela fornecerá resultados com base no novo conjunto de dados.
Como é que isso funciona?
Agora deixe-me explicar rapidamente o que acontece com essa abordagem.
A primeira coisa que fazemos no Power Query Editor é inserir uma coluna de índice que começa em 1 e é incrementada à medida que desce pela célula.
Fazemos isso porque precisamos usar essa coluna ao calcular o total acumulado em outra coluna que é inserida na próxima etapa.
Em seguida, inserimos uma coluna personalizada e usamos a seguinte fórmula
List.Sum(List.Range(#"Índice adicionado"[Venda],0,[Índice]))
Esta é uma fórmula List.Sum que lhe dará a soma do intervalo especificado nela.
O intervalo é especificado usando a função List.Range.
A função List.Range toma como saída o intervalo especificado na coluna de venda e o intervalo varia de acordo com o valor do Índice.Por exemplo, para o primeiro registro, o intervalo é o primeiro valor de venda.Esse intervalo se expande à medida que você desce a célula.
Então, para a primeira célula.List.Sum só lhe dará a soma do primeiro valor de venda, para a segunda célula lhe dará a soma dos dois primeiros valores de venda, e assim por diante.
Embora essa abordagem funcione bem, ela pode se tornar muito lenta para grandes conjuntos de dados (milhares de linhas).
Calcular totais em execução com base em critérios
Até agora, vimos exemplos de cálculo de um total corrente de todos os valores em uma coluna.
Mas, em alguns casos, você pode querer calcular um total em execução para um registro específico.
Por exemplo, abaixo tenho um conjunto de dados onde quero contar o total de impressoras e scanners em duas colunas diferentes.
Isso pode ser feito usando a fórmula SUMIF, que calcula um total em execução enquanto garante que as condições especificadas sejam atendidas.
Aqui está a fórmula para fazer isso para a coluna da impressora:
=SUMIF($C$2:C2,$D$1,$B$2:B2)
Da mesma forma, para calcular o total de execução do scanner, use a seguinte fórmula:
=SUMIF($C$2:C2,$E$1,$B$2:B2)
Na fórmula acima, usei SUMIF, que me dará uma soma em um intervalo quando as condições especificadas forem atendidas.
A fórmula leva três parâmetros:
- alcance: Este é o intervalo de condições que serão verificados em relação à condição especificada
- critérios: este é o critério que só será verificado se este critério for atendido, então o valor no terceiro parâmetro, será somado o intervalo de soma
- [intervalo_soma]: este é o intervalo da soma dos valores a serem adicionados quando a condição for atendida
Além disso, emalcance和intervalo_somabloqueei a segunda parte da referência para que, à medida que descemos na célula, o intervalo continue se expandindo.Isso nos permite apenas considerar e adicionar valores até esse intervalo (daí um total em execução).
Nesta fórmula, utilizo as colunas de cabeçalho (Impressoras e Scanners) como critério.Você também pode codificar o padrão se os títulos das colunas não forem exatamente iguais ao texto padrão.
Se você precisar verificar várias condições, poderá usar a fórmula SOMASES.
Executando totais em uma tabela dinâmica
Se você quiser adicionar um total em execução aos resultados da Tabela Dinâmica, poderá fazê-lo facilmente usando a funcionalidade interna em Tabelas Dinâmicas.
Suponha que você tenha uma tabela dinâmica como a abaixo com datas em uma coluna e valores de vendas em outra.
Aqui estão as etapas para adicionar uma coluna adicional que mostrará as vendas acumuladas por data:
- Arraste o campo Venda e solte-o na área Valor.
- Isso adicionará outra coluna com o valor de vendas
- Clique na opção Total Sales 2 na área Valor
- Clique na opção Configurações do campo de valor
- Na caixa de diálogo Configurações do campo de valor, altere Nome personalizado para Total em execução
- Clique na guia "Exibir valor como"
- No menu suspenso Exibir valor como, selecione a opção "Running Total in"
- Nas opções de Campos básicos, certifique-se de que Data esteja selecionada
- Clique OK
As etapas acima alterarão a segunda coluna de vendas para uma coluna "total em execução".
Portanto, esses são alguns dos métodos que você pode usar para calcular totais em execução no Excel.Se você tiver dados tabulares, poderá usar fórmulas simples e, se tiver tabelas do Excel, poderá usar fórmulas que utilizam referências estruturadas.
Também abordei como calcular totais em execução usando o Power Query e as tabelas dinâmicas.
Espero que você tenha achado este tutorial útil.
