Método 1. Fórmulas
Vamos começar, para o aquecimento, com a opção mais simples – fórmulas. Se tivermos uma pequena tabela classificada por data como entrada, para calcular o total em uma coluna separada, precisamos de uma fórmula elementar:
A principal característica aqui é a complicada fixação do intervalo dentro da função SUM – a referência ao início do intervalo é feita absoluta (com cifrões) e ao final – relativa (sem dólares). Assim, ao copiar a fórmula para a coluna inteira, obtemos um intervalo em expansão, cuja soma calculamos.
As desvantagens desta abordagem são óbvias:
- A tabela deve ser ordenada por data.
- Ao adicionar novas linhas com dados, a fórmula terá que ser estendida manualmente.
Método 2. Tabela dinâmica
Este método é um pouco mais complicado, mas muito mais agradável. E para agravar, vamos considerar um problema mais sério – uma tabela de 2000 linhas de dados, onde não há ordenação pela coluna de data, mas há repetições (ou seja, podemos vender várias vezes no mesmo dia):
Convertemos nossa tabela original em um atalho de teclado “inteligente” (dinâmico) Ctrl+T ou equipe Home – Formatar como uma tabela (Início — Formatar como Tabela), e então construímos uma tabela dinâmica nela com o comando Inserir – Tabela Dinâmica (Inserir - Tabela Dinâmica). Colocamos a data na área de linhas no resumo, e o número de mercadorias vendidas na área de valores:
Observe que, se você tiver uma versão não muito antiga do Excel, as datas serão agrupadas automaticamente por anos, trimestres e meses. Se você precisar de um agrupamento diferente (ou não precisar dele), poderá corrigi-lo clicando com o botão direito do mouse em qualquer data e selecionando comandos Agrupar / Desagrupar (Agrupar / Desagrupar).
Se você deseja ver os totais resultantes por períodos e o total em execução em uma coluna separada, faz sentido lançar o campo na área de valor Esgotado novamente para obter uma duplicata do campo - nele vamos ativar a exibição dos totais em execução. Para fazer isso, clique com o botão direito do mouse no campo e selecione o comando Cálculos Adicionais - Total Acumulado (Mostrar valores como - totais em execução):
Lá você também pode selecionar a opção de crescimento de totais em porcentagem, e na próxima janela você precisa selecionar o campo para o qual irá o acúmulo – no nosso caso, este é o campo de data:
As vantagens desta abordagem:
- Uma grande quantidade de dados é lida rapidamente.
- Nenhuma fórmula precisa ser inserida manualmente.
- Ao alterar os dados de origem, basta atualizar o resumo com o botão direito do mouse ou com o comando Dados – Atualizar tudo.
As desvantagens decorrem do fato de que este é um resumo, o que significa que você não pode fazer o que quiser nele (inserir linhas, escrever fórmulas, construir diagramas, etc.) não funcionará mais.
Método 3: consulta de energia
Vamos carregar nossa tabela “inteligente” com dados de origem no editor de consultas do Power Query usando o comando Dados - Da Tabela/Intervalo (Dados — Da Tabela/Intervalo). Nas últimas versões do Excel, aliás, ele foi renomeado – agora se chama Com folhas (Da folha):
Em seguida, realizaremos os seguintes passos:
1. Classifique a tabela em ordem crescente pela coluna de data com o comando Ordernar ascendente na lista suspensa de filtros no cabeçalho da tabela.
2. Um pouco mais tarde, para calcular o total acumulado, precisamos de uma coluna auxiliar com o número da linha ordinal. Vamos adicioná-lo com o comando Adicionar Coluna - Coluna de Índice - De 1 (Adicionar coluna — coluna de índice — de 1).
3. Além disso, para calcular o total acumulado, precisamos de uma referência à coluna Esgotado, onde estão nossos dados resumidos. No Power Query, as colunas também são chamadas de listas (lista) e para obter um link para ela, clique com o botão direito do mouse no cabeçalho da coluna e selecione o comando Detalhamento (Mostrar detalhes). A expressão que precisamos aparecerá na barra de fórmulas, consistindo no nome da etapa anterior #”Índice adicionado”, de onde pegamos a tabela e o nome da coluna [Vendas] desta tabela entre colchetes:
Copie esta expressão para a área de transferência para uso posterior.
4. Excluir mais último passo desnecessário Esgotado e adicione uma coluna calculada para calcular o total em execução com o comando Adicionando uma Coluna – Coluna Personalizada (Adicionar coluna — coluna personalizada). A fórmula que precisamos ficará assim:
Aqui a função Lista.Intervalo pega a lista original (coluna [Vendas]) e extrai elementos dele, começando do primeiro (na fórmula, isso é 0, pois a numeração no Power Query começa do zero). O número de elementos a serem recuperados é o número da linha que tiramos da coluna [Índice]. Portanto, esta função para a primeira linha retorna apenas uma primeira célula da coluna Esgotado. Para a segunda linha – já as duas primeiras células, para a terceira – as três primeiras, etc.
Bem, então a função Lista.Soma soma os valores extraídos e obtemos em cada linha a soma de todos os elementos anteriores, ou seja, total cumulativo:
Resta excluir a coluna Índice que não precisamos mais e carregar os resultados de volta para o Excel com o comando Home – Close & Load to.
O problema está resolvido.
Rápido e Furioso
Em princípio, isso poderia ter sido interrompido, mas há uma pequena mosca na pomada – o pedido que criamos funciona na velocidade de uma tartaruga. Por exemplo, no meu PC que não é o mais fraco, uma tabela de apenas 2000 linhas é processada em 17 segundos. E se houver mais dados?
Para acelerar, você pode usar o buffer usando a função especial List.Buffer, que carrega a lista (lista) fornecida como um argumento na RAM, o que acelera bastante o acesso a ela no futuro. No nosso caso, faz sentido armazenar em buffer a lista #”Índice adicionado”[Vendido], que o Power Query precisa acessar ao calcular o total em execução em cada linha de nossa tabela de 2000 linhas.
Para fazer isso, no editor do Power Query na guia Principal, clique no botão Editor Avançado (Home – Editor Avançado) para abrir o código-fonte de nossa consulta na linguagem M incorporada ao Power Query:
E então adicione uma linha com uma variável lá Minha lista, cujo valor é retornado pela função de buffer e, na próxima etapa, substituímos a chamada à lista por esta variável:
Depois de fazer essas alterações, nossa consulta se tornará significativamente mais rápida e lidará com uma tabela de 2000 linhas em apenas 0.3 segundos!
Outra coisa, né? 🙂
- Gráfico de Pareto (80/20) e como construí-lo no Excel
- Pesquisa de palavra-chave em buffer de texto e consulta no Power Query