Vai provavelmente deparar-se com a necessidade de totais de execução se estiver a lidar com qualquer tipo de dados diários.
Imagine que rastreia as vendas todos os dias. Os seus dados contêm uma linha para cada data com um total de vendas, mas talvez queira saber o total de vendas para o mês em cada dia. Este é um total corrente, é a soma de todas as vendas até e incluindo as vendas dos dias actuais.
Neste post vamos cobrir múltiplas formas de calcular um total corrente para os seus dados diários. Vamos explorar como utilizar fórmulas de folhas de trabalho, tabelas pivot, power pivot com DAX e power query.
Vamos também explorar o que acontece ao cálculo do total em execução ao inserir ou apagar linhas de dados e como actualizar os resultados.
Obtenha o ficheiro com todos os exemplos.
Table of Contents
Totais Correndo com uma Fórmula Simples
É possível criar uma fórmula básica de cálculo total correndo usando o operador +.
No entanto, precisaremos de usar duas fórmulas diferentes para fazer o trabalho.
- =C3 será a primeira fórmula e estará apenas na primeira fila do total em execução.
- =C4+D3 estará na segunda fila e poderá ser copiado para as restantes filas do total em execução.
A fórmula na nossa primeira fila não pode adicionar a célula acima dela ao total, uma vez que contém um valor de texto para um título de coluna. Isto faria aparecer um erro #VALUE! no total em execução, uma vez que o + não consegue lidar com valores de texto. Evitamos isto com uma fórmula diferente na primeira linha que não faz referência à célula acima.
O que acontece ao total em execução quando inserimos ou apagamos linhas nos nossos dados?
Inserir uma nova linha irá resultar numa lacuna no total em execução. Para corrigir isto, teremos de copiar a fórmula da primeira célula acima das filas recém inseridas até à última fila.
Apagar qualquer linha resultará em #REF! erros, uma vez que apagar uma linha significa apagar uma célula referenciada pela fórmula abaixo dela. Para corrigir isto, precisaremos de copiar a fórmula da última célula sem erros até à última linha.
Executar Totais com uma Fórmula SUM
Podemos evitar o embaraço de utilizar duas fórmulas diferentes na nossa coluna de totais em execução, utilizando a função SUM em vez do operador +. Quando a função SUM encontra uma célula de texto, tratá-la-á da mesma forma como se contivesse um 0,
Desta forma, podemos usar a seguinte fórmula uniformemente para cada linha, incluindo a primeira linha.
=SUM(C3,D2)
Esta fórmula fará referência ao título da coluna que contém o texto para a primeira linha, mas isto ok pois é tratado como um 0,
Ao inserir ou apagar linhas, encontraremos ainda os mesmos problemas com células em branco e erros. Podemos corrigi-los da mesma forma que com totais em execução no método de fórmula simples.
Totais em Execução com um Intervalo Parcialmente Fixo
Outra opção com a função SUM é fazer referência apenas à coluna de Vendas e utilizar uma referência de intervalo parcialmente fixo.
Se utilizarmos a seguinte fórmula =SUM($C$3:C3), podemos copiar e colar isto no intervalo. Não referirá nenhum cabeçalho de coluna e o intervalo referenciado crescerá para cada linha.
Felizmente, isto também terá os mesmos problemas (e soluções) com a inserção ou eliminação de linhas.
Totais de Execução com um Intervalo Nomeado Relativo
Podemos evitar os problemas com a inserção e eliminação de linhas dos nossos dados se utilizarmos um intervalo nomeado por um parente. Isto irá referir-se à célula directamente acima, não importa quantas linhas inserimos ou apagamos.
Este é um truque que envolve a mudança temporária do estilo de referência do Excel de A1 para R1C1. Em seguida, definir um intervalo nomeado utilizando a notação R1C1. Em seguida, mudar o estilo de referência de volta para A1.
No estilo de referência R1C1, as células são referidas pela distância a que se encontram da célula utilizando a referência. Por exemplo, =RC refere-se à célula 2 acima e 3 à direita da célula usando esta fórmula.
Podemos usar esta referência relativa para criar um intervalo nomeado que é sempre uma célula acima da célula de referência com a fórmula =RC.
Para mudar o estilo de referência, ir para o separador Ficheiro e depois escolher Opções. Ir para a secção Fórmula no menu Opções do Excel e marcar a caixa do estilo de referência R1C1 e depois premir o botão OK.
Agora podemos adicionar a nossa gama nomeada. Ir para o separador Fórmula da fita Excel e escolher o comando Definir nome.
Inserir um nome como “Above” como o nome do intervalo. Adicionar a fórmula =RC nas Referências a introduzir e premir o botão OK.
P>Podemos agora mudar o Excel de volta para o estilo de referência padrão. Ir para o separador Ficheiro > Opções a secção Fórmula > desmarcar a caixa de estilo de referência R1C1 > depois premir o botão OK.
Agora podemos usar a fórmula =SUM(,Above) na nossa coluna de execução total.
O intervalo nomeado Above irá sempre referir-se à célula directamente acima. Quando inserimos ou apagamos linhas, o intervalo nomeado relativo irá ajustar-se em conformidade e nenhuma acção é necessária.
De facto, se colocarmos os nossos dados numa Tabela Excel, então a fórmula irá preencher automaticamente quaisquer novas linhas, uma vez que a fórmula é uniforme para toda a coluna. Nenhuma acção é necessária para copiar para baixo qualquer fórmula.
Totais de execução com uma Tabela Pivot
As tabelas Pivot são super úteis para resumir qualquer tipo de dados. Há mais do que apenas adicionar, contar e encontrar médias. Há muitos outros tipos de cálculos incorporados, e há de facto um cálculo total em execução!
P>Primeiro, precisamos de inserir uma tabela pivot com base nos dados. Seleccione uma célula dentro dos dados e vá para o separador Inserir e escolha o comando PivotTable. Depois passar pela janela Criar Tabela PivotTable para escolher onde pretende a tabela pivot, seja numa nova folha de trabalho ou algures numa já existente.
Adicionar o campo Data na área Linhas da tabela pivot, depois adicionar o campo Vendas na área Valores da tabela pivot. Adicionar agora outra instância do campo Vendas na área de Linhas.
Devemos agora ter dois campos de Vendas idênticos, sendo um deles rotulado Soma de Vendas2. Podemos renomear esta etiqueta em qualquer altura, simplesmente digitando por cima com algo como Soma de Vendas2 e seleccionar Mostrar Valor Como depois escolher Mostrar Total Em.
Queremos mostrar o total em execução por data, por isso na janela seguinte precisamos de seleccionar Data como o Campo Base.
É isso, temos agora um novo cálculo que mostra o total em execução das nossas vendas dentro da tabela pivot.
O que acontece se adicionarmos ou apagarmos uma linha nos nossos dados de origem, como é que isto afecta o total em execução? Os cálculos da tabela pivot são dinâmicos e levarão em conta quaisquer novos dados no seu cálculo total em execução, só precisamos de actualizar a tabela pivot.
P>Clicar direito em qualquer lugar dentro da tabela pivot e escolher Actualizar a partir do menu.
Running Totals with Power Pivot and DAX Measures
Os primeiros passos para isto são exactamente os mesmos usando uma tabela pivot regular.
Seleccionar uma célula dentro dos dados e ir para o separador Inserir e escolher o comando PivotTable.
Quando chegar ao menu Create PivotTable, marque a caixa Add this data to the Data Model para adicionar os dados ao modelo de dados e activá-lo para utilização com o pivot de alimentação.
Colocar o campo Data na área Linhas e o campo Vendas na área Valores da tabela pivot.
Com power pivot, teremos de criar quaisquer cálculos extra que queiramos utilizando a linguagem DAX. Clique com o botão direito do rato sobre o nome da tabela na janela Campos PivotTable, depois seleccione Adicionar Medida para criar um novo cálculo. Nota, isto só está disponível com o modelo de dados.
=CALCULATE ( SUM ( Sales ), FILTER ( ALL (Sales ), Sales Agora podemos criar a nossa nova medida total em execução.
- Na janela Medida, precisamos de adicionar um Nome de Medida. Neste caso, podemos nomear a nova medida como Running Total.
- Precisamos também de adicionar a fórmula acima na caixa de Fórmula.
- O mais fixe do pivot de potência é a capacidade de atribuir um formato numérico a uma medida. Podemos escolher o formato de Moeda para a nossa medida. Sempre que utilizarmos esta medida numa tabela pivot, o formato será automaticamente aplicado.
Pressiona o botão OK e a nova medida será criada.
Haverá um novo campo listado na janela Campos da Tabela PivotTable. Tem um pequeno ícone fx à esquerda para denotar que é uma medida e não um campo regular nos dados.
Podemos usar este novo campo como qualquer outro campo e arrastá-lo para a área de Valores para adicionar o nosso cálculo total em execução na tabela pivot.
O que acontece ao total em execução quando adicionamos ou removemos dados da tabela de origem? Tal como uma tabela pivot regular, precisamos simplesmente de clicar com o botão direito do rato sobre a tabela pivot e seleccionar Actualizar para actualizar o cálculo.
Totais em execução com uma consulta de potência
Podemos também adicionar totais em execução aos nossos dados usando a consulta de potência.
P>Primeiro precisamos de importar a tabela para a consulta de potência. Seleccionar a tabela de dados e ir para o separador Dados e escolher a opção De Tabela/Amplitude. Isto abrirá o editor de consulta de energia.
P>Próximo, podemos ordenar os nossos dados por data. Este é um passo opcional que podemos adicionar para que, se alterarmos a ordem dos nossos dados de origem, o total em execução ainda aparecerá por data.
Clicar no filtro alternar no título da coluna de data e escolher Sortar Ascendente a partir das opções.
Precisamos de adicionar uma coluna de índice. Esta será utilizada no cálculo total em curso mais tarde. Ir para o separador Adicionar Coluna e clicar na pequena seta junto à Coluna de Índice para inserir um índice começando em 1 na primeira linha.
Precisamos de adicionar uma nova coluna à nossa consulta para calcular o total em execução. Ir para o separador Adicionar coluna e escolher o comando Coluna personalizada.
Podemos nomear a coluna como Total em Execução e adicionar a seguinte fórmula.
A Lista.A função Range.Sum cria uma lista de valores da coluna Vendas a partir da 1ª linha (0º item) que abrange um número de linhas com base no valor da coluna índice.
A função List.Sum adiciona então esta lista de valores que é o nosso total em execução.
Já não precisamos da coluna índice, ela serviu o seu propósito e podemos removê-la. Clique com o botão direito no título da coluna e seleccione Remover das opções.
Temos o nosso total em execução e terminamos com o editor de consulta. Podemos fechar a consulta e carregar os resultados para uma nova folha de trabalho. Vá ao separador Home do editor de consultas e prima o botão Close & botão Load.
O que acontece com o total em execução quando adicionamos ou removemos linhas dos nossos dados de origem? Teremos de actualizar a tabela de saída da consulta de energia para actualizar o total em execução com as alterações. Clique com o botão direito do rato em qualquer parte da tabela e escolha Actualizar para actualizar a tabela.
Com o passo de ordenação opcional acima, se adicionarmos datas fora de ordem aos dados de origem, a consulta de alimentação irá ordenar por data e devolver a ordem correcta por data para o total em execução.
Conclusões
Existem muitas opções diferentes para calcular os totais em execução no Excel.
Explorámos opções incluindo fórmulas na folha de trabalho, tabelas pivot, fórmulas power pivot DAX, e power query. Alguns oferecem uma solução mais robusta ao adicionar ou remover linhas dos dados, outros métodos oferecem uma implementação mais fácil.
Fórmulas simples na folha de trabalho são fáceis de configurar mas não tratam facilmente da inserção ou eliminação de novas linhas de dados. Outras soluções como tabelas pivot, DAX e consulta de energia são mais robustas e lidam facilmente com a inserção ou eliminação de linhas de dados, mas são mais difíceis de configurar.
É bom estar atento aos prós e contras de cada método e escolher o mais adequado. Se não vai inserir ou apagar novos dados, então as fórmulas da folha de trabalho podem ser o caminho a seguir.