Este episódio mostra três maneiras de fazer totais corridos.
Um total parcial é, para uma lista de valores numéricos, uma soma dos valores da primeira linha para a linha do total corrido. Os usos comuns de um total corrente são em um livro de cheques ou em uma folha de contabilidade. Há muitas maneiras de criar um total em execução - duas delas são descritas a seguir.
A técnica mais simples é, em cada linha, somar o total corrente da linha acima ao valor da linha. Portanto, a primeira fórmula na linha 2 é:
=SUM(D1,C2)
A razão de usarmos a função SUM é porque, na primeira linha, estamos olhando para o cabeçalho da linha acima. Se usarmos a fórmula mais simples e intuitiva de, =D1+C2então um erro será gerado porque o valor do cabeçalho é texto versus numérico. A mágica é que a função SUM ignora os valores de texto, que são adicionados como valores zero. Quando a fórmula é copiada para todas as linhas em que um total parcial é desejado, as referências de célula são ajustadas de acordo:
A outra técnica também usa a função SUM, mas cada fórmula soma todos os valores da primeira linha até a linha que exibe o total acumulado. Neste caso, usamos um cifrão ($) para fazer da primeira célula na referência uma referência absoluta, o que significa que não é ajustada quando copiada:
Ambas as técnicas não são afetadas pela classificação e exclusão de linhas, mas, ao inserir linhas, a fórmula deve ser copiada nas novas linhas.
O Excel 2007 introduziu a Tabela, que é uma reimplementação da Lista no Excel 2003. As tabelas introduziram uma série de recursos muito úteis para tabelas de dados, como formatação, classificação e filtragem. Com a introdução de Tabelas, também foi fornecida uma nova forma de referenciar as partes de uma Tabela. Esse novo estilo de referência é chamado de referência estruturada.
Para converter o exemplo acima em uma Tabela, selecionamos os dados que queremos incluir na Tabela e pressionamos Ctrl + T. Depois de exibir um prompt pedindo para confirmarmos o intervalo da Tabela e se existem cabeçalhos existentes ou não, o Excel converte os dados em uma tabela formatada:
Observe que as fórmulas que inserimos anteriormente permanecem as mesmas.
Um dos recursos úteis que o Tables oferece é a formatação automática e a manutenção de fórmulas à medida que as linhas são adicionadas, removidas, classificadas e filtradas. É a manutenção da fórmula em particular que iremos nos concentrar e que pode ser problemática. Para manter as tabelas funcionando enquanto são manipuladas, o Excel utiliza colunas calculadas que são colunas com fórmulas como a coluna D no exemplo acima. Quando novas linhas são inseridas são adicionadas à parte inferior, o Excel preenche automaticamente as novas linhas com a fórmula “padrão” para aquela coluna. O problema com o exemplo acima é que o Excel se confunde com as fórmulas padrão e nem sempre as manipula corretamente. Isso se torna aparente quando novas linhas são adicionadas à parte inferior da tabela (selecionando a célula inferior direita na tabela e pressionando TAB):
Essa deficiência é resolvida usando a referência estruturada mais recente. A referência estruturada elimina a necessidade de fazer referência a células específicas usando o estilo de referência A1 ou R1C1 e, em vez disso, usa nomes de coluna e outras palavras-chave para identificar e fazer referência às partes de uma Tabela. Por exemplo, para criar a mesma fórmula de total em execução usada acima, mas usando referência estruturada, temos:
=SUM(INDEX((Sales),1):(@Sales))
Neste exemplo, temos uma referência ao nome da coluna, “Vendas”, junto com o sinal de arroba (@) para fazer referência à linha na coluna em que a fórmula está localizada, que também é conhecida como a linha atual.
Para implementar o primeiro exemplo acima, onde adicionamos o valor total em execução na linha anterior ao valor das vendas na linha atual, você pode usar a função OFFSET:
=SUM(OFFSET((@(Running Total)),-1,0),(@Sales))
Se os valores usados para calcular o total corrente estiverem em duas colunas, por exemplo, uma para "Débitos" e outra para "Créditos", a fórmula é:
=SUM(INDEX( (Credit),1):(@Credit))- SUM(INDEX( (Debit),1):(@Debit))
Aqui, estamos usando a função INDEX para localizar as células de crédito e débito da primeira linha e somando a coluna inteira até e incluindo os valores da linha atual. O total corrente é a soma de todos os créditos até e incluindo a linha atual menos a soma de todos os débitos até e incluindo a linha atual.
Para obter mais informações sobre referências estruturadas em particular e Tabelas em geral, recomendamos o livro Tabelas Excel: Um Guia Completo para Criar, Usar e Automatizar Listas e Tabelas de Zack Barresse e Kevin Jones.
Quando pedi aos leitores que votassem em suas dicas favoritas, as tabelas eram populares. Agradecimentos a Peter Albert, Snorre Eikeland, Nancy Federice, Colin Michael, James E. Moede, Keyur Patel e Paul Peton por sugerirem esse recurso. Peter Albert escreveu a dica bônus de referências legíveis. Zack Barresse escreveu a dica bônus Running Totals. Quatro leitores sugeriram o uso de OFFSET para criar faixas de expansão para gráficos dinâmicos: Charley Baak, Don Knowles, Francis Logan e Cecelia Rieb. As tabelas agora fazem a mesma coisa na maioria dos casos.
Assistir vídeo
- Este episódio mostra três maneiras de fazer totais corridos
- O primeiro método tem uma fórmula diferente na linha 2 do que todas as outras linhas
- O primeiro método é = Esquerda na linha 2 e = Esquerda + Cima nas linhas 3 a N
- Se você tentar usar a mesma fórmula, obterá um erro #Value com = Total + Número
- Método 2 usa
=SUM(Up,Left)ou=SUM(Previous Total,This Row Amount) - SUM ignora o texto para que você não obtenha um erro VALUE
- O método 3 usa um intervalo de expansão:
=SUM(B$2:B2) - Os intervalos de expansão são legais, mas são lentos
- Leia o artigo Charles Williams sobre velocidade de fórmula do Excel
- O terceiro método é um problema quando você usa Ctrl + T e adiciona novas linhas
- O Excel não consegue descobrir como escrever a fórmula
- As soluções alternativas requerem algum conhecimento de referência estruturada em tabelas
- A solução alternativa 1 é o lento
=SUM(INDEX((Qty),1):(@Qty)) - A solução alternativa 2 é o volátil
=SUM(OFFSET((@Total),-1,0),(@Qty)) - (@Qty) refere-se a Qty nesta linha
- (Qty) refere-se a todos os valores Qty
Transcrição de vídeo
Aprenda Excel para Podcast, Episódio 2004 - Totais Executados
Estarei enviando um podcast para este livro inteiro. Clique nesse I no canto superior direito para se inscrever.
Ei, bem-vindo de volta ao netcast místico celular. Sou Bill Jelen. Agora, neste tópico do livro, tive a contribuição de meu amigo Zach Parise. Por falar em tabelas Excel, Zach é o maior especialista mundial em tabelas Excel. Ele escreveu um livro sobre tabelas do Excel, mas primeiro vamos falar sobre a execução de totais não em tabelas.
Então, quando penso em totais corridos, há três maneiras diferentes de fazer os totais corridos, e a maneira que sempre comecei é na primeira linha, você apenas diz, traga o valor. Tão igual ao que quer que esteja à minha esquerda. Tudo bem, então este formato aqui é apenas = B2. Todos esses são textos de fórmula aqui no canto direito, então você está vendo o que estamos usando e, a partir daí, é uma pequena fórmula simples igual ao valor anterior, mais o valor atual certo e copie-o para baixo , mas você sabe agora, temos o problema de que são necessárias duas fórmulas diferentes e você sabe, em uma situação perfeita, que tem exatamente a mesma fórmula em todo o caminho, e a razão de termos uma fórmula diferente na primeira linha é que quando você tenta somar igual 7 mais a palavra total, é um erro de valor,mas o melhor trabalho aqui é não apenas usar left plus up, mas usar = (SUM) do valor anterior mais a quantidade nesta linha, e ver que alguns estão longe o suficiente para ignorar os textos. Isso permite a mesma fórmula. todo o caminho para baixo.
Tudo bem então foi quando eu estava começando a usar o Excel, eu estava usando isso e então descobri o intervalo de expansão, o intervalo de expansão diz que vamos fazer L $ 2: L2 e o que acontece é que isso sempre começa na linha 2, mas então vai para a linha atual. Então, quando você vê como isso funciona quando é copiado, sempre iniciamos a linha 2, mas descemos para a linha atual e este se tornou meu método favorito. Eu pensei, oh, isso é muito mais sofisticado e quando entramos em Opções do Excel, vamos para a guia Fórmulas e escolha R1C1 em Estilo de referência. Tudo bem, veja, R1C1, todas essas fórmulas são exatamente iguais em todo o caminho. Não sei se você entende R1C1, é bom saber que temos fórmulas R1C1 idênticas em todo o caminho.
Vamos voltar. Então, esse método aqui é o método que eu gostava, até que Charles Williams, um MBP do Excel da Inglaterra, que tem um artigo incrível sobre velocidade de fórmula, velocidade de fórmula do Excel, desmascarou completamente esse método. Este método, digamos que você tenha 10.000 linhas, cada fórmula está olhando para duas referências. Então você está olhando para 20.000 referências, mas este aqui está olhando para duas, este está olhando para três, este está olhando para quatro, este está olhando para cinco e o último está olhando para 10.000 referências, e é terrivelmente mais lento e então parei de usar esse método.
Em seguida, leio Zack no livro de Kevin Jones sobre tabelas do Excel e descubro outro problema com esse método. Portanto, um dos recursos úteis que as tabelas oferecem é 'a formatação automática e as linhas de manutenção de fórmulas são adicionadas, removidas, classificadas e filtradas'. Tudo bem, é uma citação do livro dele. E para adicionar uma linha a uma tabela, basta ir até a última célula da tabela e pressionar tab. Então tudo está funcionando aqui. Estamos reduzidos a 70, isso é incrível, e então A104 e colocarei 100 aqui. Tudo bem, então esse 70 deveria mudar para 170 e muda, mas este 70 não deveria ter mudado em nada. Tudo bem, 68 + 2 não é um 170. Vou fazer de novo. Um 104 e colocar mais cem no último está certo. Esses dois não estão certos. Tudo bem, então temos uma situação estranha que se você 'Ao usar essa fórmula e você converter em tabela, começará a adicionar linhas, o total em execução não funcionará. Quão ruim é isso?
Tudo bem, então Zack oferece duas soluções alternativas e ambas requerem um pouco de conhecimento de como as referências de estrutura funcionam. Vamos apenas ter uma nova coluna aqui e se eu quiser fazer quantidade, quantidade igual, certo, de modo que = (@ Qty) diga quantidade nesta linha. Legal, bem, há outro tipo de referência onde usamos o Qty sem o @. Veja isso. Então = SUM (INDEX ((Qty), 1: (@ Qty)) significa todas as quantidades e vamos dizer que queremos SUM da primeira quantidade, então (INDEX ((Qty), 1 diz que primeiro valor aqui, até a quantidade de linha atual, e isso está usando uma versão realmente especial do índice, quando o índice é seguido por dois pontos, ele na verdade muda para uma referência de célula. Tudo bem, esta solução alternativa está, infelizmente, violando a regra de Charles Williams de, nós 'você vai ter que olhar para cada referência, então quando você conseguir 10.000 linhas isso vai ficar muito, muito lento.
Zach tem outra solução alternativa que não viola o problema de Charles Williams, mas está usando o temido OFFSET. OFFSET é uma função volátil, então cada vez que você calcula algo, OFFSET vai recalcular e tudo na linha do OFFSET vai recalcular. É uma ótima maneira de bagunçar completamente suas fórmulas, e o que isso está fazendo, está dizendo, estamos tirando o total desta linha, subindo uma linha, sobre zero colunas e então o que isso está fazendo é dizer: pegue o total da linha anterior e então adicionaremos a quantidade desta linha. Tudo bem, agora tudo está olhando para duas referências de cada vez, mas infelizmente o OFFSET está introduzindo funções voláteis.
Bem, aí está, mais do que você sempre quis saber sobre Totais em execução. Acho que minha opinião final aqui é usar esse método, porque parece apenas dois. A mesma fórmula até o fim e suas referências de tabela estruturadas funcionarão.
Para esta exploração e outras 39 dicas realmente boas, dê uma olhada neste livro XL, as 40 melhores dicas de Excel de todos os tempos.
Recapitulando este episódio, falamos sobre três maneiras de fazer totais corridos. O primeiro método tem uma fórmula diferente, linha 2, de todas as outras linhas. É igual à esquerda na linha 2 e igual à esquerda mais acima nas linhas 3 a N, mas se você tentar e apenas usar a mesma fórmula, igual à esquerda mais acima, totalmente para baixo, você obterá um erro #Value . Então = SUM (Up, Left), que é o total anterior, mais este roteiro, que funciona muito bem, sem erros de valor e, em seguida, o intervalo de expansão que eu amo. Eles são legais, mas até eu ler o artigo de Charles Williams sobre a forma de velocidade do Excel. Então comecei a odiar essas referências em expansão. Ele também tem um problema quando você usa CTRL T e adiciona novas linhas. O Excel não consegue descobrir como expandir essa fórmula, como adicionar novas linhas. Adoro esta dica, vá até a última célula da tabela e pressione Tab,isso adicionará uma nova linha e então falamos sobre algumas referências estruturadas, onde estamos usando quantidade nesta linha e então todas as quantidades. = SUM (OFFSET ((@ Total), - 1,00, (@ Qty)).
Ok, quero agradecer a Zach por contribuir com essa dica. Eu quero te agradecer por passar por aqui. Nos vemos na próxima vez para outro netcast de.
⇬ Fazer download do arquivo
Baixe o arquivo de amostra aqui: Podcast2004.xlsx
