você provavelmente vai se deparar com a necessidade de executar totais se você está lidando com qualquer tipo de dados diários.Imagine que acompanha as vendas todos os dias. Seus dados contém uma linha para cada data com uma quantidade total de vendas, mas talvez você queira saber o total de vendas para o mês em cada dia. Este é um total de execução, é a soma de todas as vendas até e incluindo as vendas atuais dias.

neste post vamos cobrir várias maneiras de calcular um total de execução para os seus dados diários. Vamos explorar como usar fórmulas de planilha, tabelas pivot, pivot de energia com DAX e consulta de energia.

também vamos explorar o que acontece com o cálculo total em execução ao inserir ou excluir linhas de dados e como atualizar os resultados.

obtenha o arquivo com todos os exemplos.

Executar Totais com uma Fórmula Simples

É possível criar uma base total de execução de fórmula usando o operador+.

No entanto, vamos precisar de usar duas fórmulas diferentes para fazer o trabalho.

1. =C3 será a primeira fórmula e estará apenas na primeira linha do total em execução.
2. = C4+D3 estará na segunda linha e pode ser copiado para baixo das linhas restantes para o total de execução.

Isso causaria um #valor! erro ao aparecer no total de execução, uma vez que o + não consegue lidar com os valores de texto. Evitamos isso com uma fórmula diferente na primeira linha que não faz referência à célula acima.

O que acontece ao total de execução quando inserimos ou apagamos linhas nos nossos dados?

inserir uma nova linha irá resultar num intervalo no total em execução. Para corrigir isso, vamos precisar copiar a fórmula para baixo a partir da primeira célula acima das linhas recém-inseridas até a última linha.

apagar quaisquer linhas irá resultar em #REF! erros desde que a remoção de uma linha significa a remoção de uma célula referenciada pela fórmula abaixo dela. Para corrigir isso, precisamos copiar a fórmula da última célula livre de erro até a última linha.

executando totais com uma fórmula de soma

podemos evitar o constrangimento de usar duas fórmulas diferentes na nossa coluna total em execução, utilizando a função de soma em vez do operador+. Quando a função SUM encontra uma célula de texto, ela irá tratá-la da mesma forma que a embora contivesse um 0.

desta forma podemos usar a seguinte fórmula uniformemente para cada linha, incluindo a primeira linha.

= soma (C3, D2)

esta fórmula fará referência ao cabeçalho da coluna contendo texto para a primeira linha, mas este ok como é tratado como um 0.

Ao inserir ou apagar linhas, ainda vamos encontrar os mesmos problemas com as células em branco e erros. Podemos corrigi-los da mesma forma que com a execução de totais no método de fórmula simples.

correr os totais com uma gama parcialmente Fixa

outra opção com a função de soma é apenas referenciar a coluna de vendas e utilizar uma referência de gama parcialmente fixa.

Se usarmos a seguinte fórmula =SUM($C$3:C3), podemos copiar e colar isto para o intervalo. Não referenciará nenhum cabeçalho de coluna e o intervalo referenciado crescerá para cada linha.

infelizmente, isto também terá os mesmos problemas (e soluções) com a inserção ou exclusão de linhas.

executando totais com um intervalo relativo nomeado

podemos evitar os problemas com a inserção e exclusão de linhas dos nossos dados se usarmos um intervalo relativo nomeado. Isto irá referir-se à célula directamente acima, não importa quantas linhas introduzimos ou apagamos.

Este é um truque que envolve temporariamente mudar o estilo de referência do Excel de A1 para R1C1. Em seguida, definir um intervalo nomeado usando 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 por quão longe estão da célula usando a referência. Por exemplo, RC refere-se à célula 2 up 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 referencial com a fórmula =RC.

para mudar o estilo de referência, vá para a página do ficheiro e escolha as opções. Vá à secção de fórmulas no menu Opções do Excel e assinale a caixa de estilo de referência R1C1 e depois carregue no botão OK.

Agora podemos adicionar o nosso intervalo nomeado. Vá à página de fórmulas da Fita do Excel e escolha o comando Definir o nome.

inserir um nome como” acima ” como o nome da Gama. Adicione a fórmula =RC no Refers to input e carregue no botão OK.

Agora podemos mudar o Excel de volta para o estilo de referência padrão. Vá para a guia Arquivo > Opções de Fórmula seção > desmarque o estilo de referência L1C1 caixa de >, em seguida, pressione o botão OK.

Agora podemos usar a fórmula =soma(,acima) na nossa coluna total em execução.

O intervalo indicado acima referir-se-á sempre à célula directamente acima. Quando inserirmos ou excluirmos linhas, o intervalo relativo nomeado ajustar-se-á em conformidade e não será necessária qualquer acção.

de facto, se colocarmos os nossos dados numa tabela do Excel, então a fórmula irá preencher automaticamente para quaisquer novas linhas, uma vez que a fórmula é uniforme para toda a coluna. Não é necessária qualquer acção para copiar quaisquer fórmulas.

correr totais com uma tabela Pivot

p > tabelas Pivot são super úteis para resumir qualquer tipo de dados. Há mais para eles do que apenas adicionar, contar e encontrar médias. Existem muitos outros tipos de cálculos embutidos, e há realmente um cálculo total em execução!

primeiro, precisamos inserir uma tabela pivot com base nos dados. Seleccione uma célula dentro dos dados e vá para a página Inserir e escolha o comando pivotable. Em seguida, vá através da janela de criar Pivotável para escolher onde você quer a tabela pivot, seja em uma nova planilha ou em algum lugar em uma existente.

adicione o campo da data na área das linhas da tabela pivot, adicionando então o campo de vendas à área dos valores da tabela pivot. Agora adicione outra instância do campo de vendas na área de linhas. actualmente, deveríamos ter dois campos de vendas idênticos, sendo um deles a soma das vendas rotuladas2. Podemos mudar o nome deste rótulo a qualquer momento, simplesmente digitando sobre ele com algo como executar Total.

carregue com o botão direito em qualquer um dos valores da soma do campo Sales2 e seleccione Mostrar o valor à medida que escolher executar o Total em.

queremos mostrar o total de execução por data, por isso na próxima janela temos de seleccionar a data como Campo Base.é isso, agora temos um novo cálculo que mostra o total de execução de nossas vendas dentro da tabela pivô.

O Que Acontece se adicionarmos ou apagarmos uma linha nos nossos dados de origem, como é que isto afecta o total de execução? Os cálculos da tabela pivot são dinâmicos e levarão em conta quaisquer novos dados em seu cálculo total de execução, nós apenas precisaremos atualizar a tabela pivot.

carregue com o botão direito em qualquer ponto da tabela de pivô e escolha a opção Actualizar no menu.

executando totais com pivô de potência e medidas DAX

os primeiros passos para isso são exatamente os mesmos usando uma tabela de pivô regular.

seleccione uma célula dentro dos dados e vá para a página Inserir e escolha o comando Pivotável.

Quando chegar ao menu Criar Pivotável, assinale a opção Adicionar estes dados ao modelo de dados para adicionar os dados ao modelo de dados e permitir a sua utilização com pivot.

coloque o campo da data na área das linhas e o campo de vendas na área dos valores da tabela pivot.

com pivô de potência, vamos precisar criar quaisquer cálculos extras que queremos usando a linguagem DAX. Carregue com o botão direito no nome da tabela na janela de Campos Pivotáveis e 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 execução total da medida.

1. na janela de medida, precisamos adicionar um nome de medida. Neste caso, podemos nomear a nova medida como total de execução.
2. Também precisamos adicionar a fórmula acima na caixa de fórmulas.
3. a coisa legal sobre o pivô de potência é a capacidade de atribuir um formato de número 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.pressione o botão OK e a nova medida será criada.

   

   haverá um novo campo listado na janela de Campos Pivotáveis. Ele 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 com o total de execução quando adicionamos ou removemos dados da tabela de origem? Assim como uma tabela de pivô regular, nós simplesmente precisamos clicar com o botão direito na tabela de pivô e selecionar Refresh para atualizar o cálculo.

   executando totais com uma consulta de energia

   também podemos adicionar totais de execução aos nossos dados usando a consulta de energia.

   

   primeiro precisamos importar a tabela para a consulta power. Seleccione a tabela de dados e vá para a página de dados e escolha a opção da tabela/intervalo. Isto irá abrir o editor de pesquisas de energia.

   

   a seguir podemos classificar os nossos dados por data. Este é um passo opcional que podemos adicionar para que, se mudarmos a ordem dos nossos dados de origem, o total de execução ainda aparecerá por data.

   carregue no filtro comuta no cabeçalho da coluna de data e escolha Ordenar ascendente a partir das opções.

   

   é necessário adicionar uma coluna de índice. Isto será usado no cálculo total em execução mais tarde. Vá para a página Adicionar Coluna e carregue na pequena seta ao lado da coluna Índice para inserir um índice a partir de 1 na primeira linha.

   

   é necessário adicionar uma nova coluna à nossa consulta para calcular o total em execução. Vá para a página Adicionar uma coluna e escolha o comando personalizado Da Coluna.

   

   podemos nomear a coluna como total de execução e adicionar a seguinte fórmula.

   Lista.Sum (List.Range (#”Added Index”,0,))

   a lista.A função Range cria uma lista de valores a partir da coluna de vendas a partir da primeira linha (0.º item) que abrange um número de linhas com base no valor da coluna de índice.

   A lista.A função Sum adiciona então esta lista de valores que é o nosso total de execução.

   

   Carregue com o botão direito no cabeçalho da coluna e seleccione Remover das opções.

   temos o nosso total de execução e terminámos com o editor de consultas. Podemos fechar a consulta e carregar os resultados em uma nova folha de trabalho. Vá para a página pessoal do editor de pesquisas e carregue no botão Fechar & carregar.

   

   O Que Acontece com o total de execução quando adicionamos ou removemos linhas dos nossos dados de origem? Vamos precisar atualizar a tabela de saída da consulta de energia para atualizar o total de execução com as alterações. Carregue com o botão direito em qualquer ponto da mesa 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 power irá Ordenar por data e devolver a ordem correta por data para o total de execução.

   conclusões

   Existem muitas opções diferentes para calcular os totais em execução no Excel.

   exploramos opções incluindo fórmulas na planilha, tabelas pivot, fórmulas DAX de pivot de potência e consulta de potência. 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 lida facilmente com a inserção ou remoção de novas linhas de dados. Outras soluções como tabelas pivot, DAX e consulta power são mais robustas e lidar inserir ou excluir linhas de dados facilmente, mas são mais difíceis de configurar.

   é bom estar ciente dos prós e contras de cada método e escolher o mais adequado. Se você não estiver inserindo ou apagando novos dados, então fórmulas de planilha pode ser o caminho a seguir.