Se sua planilha do Excel incluir cálculos baseados em um intervalo variável de células, use as funções SUM e OFFSET juntas em uma fórmula SUM OFFSET para simplificar a tarefa de manter os cálculos atualizados.
As instruções neste artigo se aplicam ao Excel para Microsoft 365, Excel 2019, Excel 2016, Excel 2013 e Excel 2010.
Crie uma faixa dinâmica com as funções SUM e OFFSET
Se você usar cálculos para um período de tempo que muda continuamente - como determinar as vendas do mês - use a função DESLOC no Excel para configurar um intervalo dinâmico que muda à medida que os números de vendas de cada dia são adicionados.
Por si só, a função SUM geralmente pode acomodar a inserção de novas células de dados no intervalo que está sendo somado. Uma exceção ocorre quando os dados são inseridos na célula onde a função está localizada atualmente.
No exemplo abaixo, os novos números de vendas para cada dia são adicionados na parte inferior da lista, forçando o total a descer continuamente uma célula cada vez que os novos dados são adicionados.
Para acompanhar este tutorial, abra uma planilha do Excel em branco e insira os dados de exemplo. Sua planilha não precisa ser formatada como no exemplo, mas certifique-se de inserir os dados nas mesmas células.
Se apenas a função SUM for usada para totalizar os dados, o intervalo de células usado como argumento da função precisaria ser modificado toda vez que novos dados fossem adicionados.
Usando as funções SUM e OFFSET juntas, o intervalo totalizado torna-se dinâmico e muda para acomodar novas células de dados. A adição de novas células de dados não causa problemas porque o intervalo continua a se ajustar à medida que cada nova célula é adicionada.
Sintaxe e Argumentos
Nesta fórmula, a função SUM é usada para totalizar o intervalo de dados fornecido como argumento. O ponto inicial desse intervalo é estático e é identificado como a referência da célula ao primeiro número a ser totalizado pela fórmula.
A função OFFSET está aninhada dentro da função SUM e cria um ponto final dinâmico para o intervalo de dados totalizado pela fórmula. Isso é feito definindo o ponto final do intervalo para uma célula acima do local da fórmula.
A sintaxe da fórmula é:
=SOMA(Início do intervalo:OFFSET(Referência, Linhas, Colunas))
Os argumentos são:
- Range Start: O ponto inicial do intervalo de células que será totalizado pela função SUM. Neste exemplo, o ponto de partida é a célula B2.
- Reference: A referência de célula necessária usada para calcular o ponto final do intervalo. No exemplo, o argumento Reference é a referência de célula para a fórmula porque o intervalo termina uma célula acima da fórmula.
- Rows: O número de linhas acima ou abaixo do argumento Reference usado no cálculo do deslocamento é obrigatório. Esse valor pode ser positivo, negativo ou definido como zero. Se o local do deslocamento estiver acima do argumento Reference, o valor será negativo. Se o deslocamento estiver abaixo, o argumento Rows será positivo. Se o deslocamento estiver localizado na mesma linha, o argumento será zero. Neste exemplo, o deslocamento começa uma linha acima do argumento Reference, portanto, o valor do argumento é negativo (-1).
- Cols: O número de colunas à esquerda ou à direita do argumento Reference usado para calcular o deslocamento. Esse valor pode ser positivo, negativo ou definido como zero. Se o local do deslocamento estiver à esquerda do argumento Reference, esse valor será negativo. Se o deslocamento for para a direita, o argumento Cols será positivo. Neste exemplo, os dados totalizados estão na mesma coluna da fórmula, portanto, o valor desse argumento é zero.
Use a fórmula SUM OFFSET para o total de dados de vendas
Este exemplo usa uma fórmula SUM OFFSET para retornar o total das vendas diárias listadas na coluna B da planilha. Inicialmente, a fórmula foi inserida na célula B6 e totalizou os dados de vendas de quatro dias.
O próximo passo é mover a fórmula SUM OFFSET uma linha para baixo para dar espaço para o total de vendas do quinto dia. Isso é feito inserindo uma nova linha 6, que move a fórmula para a linha 7.
Como resultado da movimentação, o Excel atualiza automaticamente o argumento Reference para a célula B7 e adiciona a célula B6 ao intervalo somado pela fórmula.
- Selecione a célula B6, que é o local onde os resultados da fórmula serão exibidos inicialmente.
-
Selecione a guia Fórmulas da faixa de opções.
Image -
Escolha Matemática e Trilogia.
Image -
Selecione SUM.
Image - Na caixa de diálogo Function Arguments, coloque o cursor na caixa de texto Number1.
-
Na planilha, selecione a célula B2 para inserir esta referência de célula na caixa de diálogo. Este local é o ponto final estático para a fórmula.
Image - Na caixa de diálogo Function Arguments, coloque o cursor na caixa de texto Number2.
-
Digite OFFSET(B6, -1, 0). Esta função OFFSET forma o ponto final dinâmico para a fórmula.
Image -
Selecione OK para completar a função e fechar a caixa de diálogo. O total aparece na célula B6.
Image
Adicione os dados de vendas do dia seguinte
Para adicionar os dados de vendas do dia seguinte:
- Clique com o botão direito do mouse no cabeçalho da linha 6.
-
Selecione Inserir para inserir uma nova linha na planilha. A fórmula SUM OFFSET desce uma linha até a célula B7 e a linha 6 agora está vazia.
Image - Selecione a célula A6 e insira o número 5 para indicar que o total de vendas do quinto dia está sendo inserido.
-
Selecione a célula B6, digite $1458.25 e pressione Enter.
Image - Cell B7 atualiza para o novo total de $ 7.137,40.
Quando você seleciona a célula B7, a fórmula atualizada aparece na barra de fórmulas.
=SOMA(B2:OFFSET(B7, -1, 0))
A função OFFSET possui dois argumentos opcionais: Height e Width, que não foram usados neste exemplo. Esses argumentos informam à função OFFSET a forma da saída em termos do número de linhas e colunas.
Ao omitir esses argumentos, a função usa a altura e a largura do argumento Reference, que, neste exemplo, tem uma linha de altura e uma coluna de largura.
