Fórmula Excel SUM e OFFSET

Use SUM e OFFSET para atopar totais para rangos dinámicos de datos

Se a súa folla de cálculo Excel inclúe cálculos baseados nun intervalo cambiante de celas, empregando as funcións SUM e OFFSET xuntas nunha fórmula SUM OFFSET simplifica a tarefa de manter os cálculos actualizados.

Crea un intervalo dinámico coas funcións SUM e OFFSET

Se usa cálculos por un período de tempo que cambia continuamente (por exemplo, o total das vendas do mes), a función OFFSET permítelle configurar un rango dinámico que continúa cambiando a medida que se engadan as cifras de vendas de cada día.

Por si só, a función SUM adoita acomodar novas celas de datos que se están inserindo no rango que se resume.

Existe unha excepción cando os datos son inseridos na cela onde está a función.

Na imaxe de exemplo que acompaña a este artigo, engádense as novas cifras de vendas de cada día no fondo da lista, o que obriga ao total a baixar unha cela cada vez que se engadan os novos datos.

Se a función SUM se utilizaba por si mesma para completar os datos, sería necesario modificar o rango de celas empregadas como argumento da función cada vez que se engadían novos datos.

Ao usar as funcións SUM e OFFSET xuntas, con todo, o rango que se suma é dinámico. Noutras palabras, cambia para acomodar novas celas de datos. A adición de novas celas de datos non causa problemas porque o rango segue a axustarse a medida que se engade cada nova cela.

Sintaxe e Argumentos

Consulte a imaxe que acompaña este artigo a seguir xunto con este tutorial.

Nesta fórmula, úsase a función SUM para completar o intervalo de datos que se fornece como argumento. O punto de inicio deste rango é estático e identifícase como a referencia de cela ao primeiro número que se vai completar coa fórmula.

A función OFFSET está aniñada dentro da función SUM e úsase para crear un punto final dinámico no intervalo de datos que contén a fórmula. Isto faise executando o punto final do intervalo a unha cela por riba da localización da fórmula.

Sintaxe da fórmula:

= SUM (Inicio de rango: OFFSET (Referencia, filas, Cols))

Rango de inicio - (necesario) o punto de partida para o rango de celas que se sumará á función SUM. Na imaxe de exemplo, esta é a cela B2.

Referencia - (obrigatorio) a referencia de cela empregada para calcular o punto final do rango que está localizado en moitas filas e columnas. Na imaxe de exemplo, o argumento de referencia é a referencia da célula para a mesma fórmula xa que sempre queremos que o intervalo finalice unha cela por riba da fórmula.

Filas : (requirido) o número de filas por riba ou por baixo do argumento de referencia empregado para calcular o desprazamento. Este valor pode ser positivo, negativo ou definido en cero.

Se a situación do desprazamento está por riba do argumento de referencia , este valor é negativo. Se está a continuación, o argumento Rows é positivo. Se o desprazamento está situado na mesma liña, este argumento é cero. Neste exemplo, o desprazamento comeza unha fila por riba do argumento de referencia , polo que o valor para este argumento é negativo (-1).

Cols : (requerido) o número de columnas á esquerda ou á dereita do argumento de referencia empregado para calcular o desprazamento. Este valor pode ser positivo, negativo ou definido en cero

Se a situación do desprazamento está á esquerda do argumento de referencia , este valor é negativo. Se á dereita o argumento Cols é positivo. Neste exemplo, os datos que se están sumando están na mesma columna que a fórmula, polo que o valor para este argumento é cero.

Usando a fórmula SUM OFFSET para os datos de vendas totais

Este exemplo usa unha fórmula SUM OFFSET para devolver o total para os valores de vendas diarios enumerados na columna B da folla de cálculo.

Inicialmente, a fórmula ingresouse na cela B6 e totalizou os datos de vendas durante catro días.

O seguinte paso é mover a fórmula SUM OFFSET nunha fila para dar espazo ao total de vendas do quinto día.

Isto conséguese inserindo unha nova fila 6, que move a fórmula ata a fila 7.

Como resultado do movemento, Excel cambia automaticamente o argumento de referencia á cela B7 e engade a cela B6 ao rango sumado pola fórmula.

Introducir a fórmula SUM OFFSET

1. Prema na célula B6, que é o lugar onde inicialmente se mostrarán os resultados da fórmula.
2. Fai clic na pestana Fórmulas do menú da cinta .
3. Escolla Math & Trig da cinta para abrir a lista despregábel da función.
4. Fai clic en SUM na lista para abrir o diálogo da función.
5. No cadro de diálogo, prema na liña Número 1 .
6. Fai clic na cela B2 para ingresar esta referencia de cela na caixa de diálogo. Esta localización é o punto final estático para a fórmula;
7. No cadro de diálogo, prema na liña Número2 .
8. Introduza a seguinte función OFFSET: OFFSET (B6, -1,0) para formar o punto final dinámico para a fórmula.
9. Faga clic en Aceptar para completar a función e pechar o cadro de diálogo.

O total de $ 5679.15 aparece na cela B7.

Cando fai clic na cela B3, aparece a función completa = SUM (B2: OFFSET (B6, -1,0)) na barra de fórmulas situada enriba da folla de cálculo.

Engadindo os datos de vendas do próximo día

Para engadir os datos de vendas do día seguinte:

1. Fai clic co botón dereito do rato no cabeceira da fila para a fila 6 para abrir o menú contextual.
2. No menú, prema en Inserir para inserir unha nova fila na folla de cálculo.
3. Como resultado, a fórmula SUM OFFSET baixa ata a cela B7 e a fila 6 está baleira.
4. Fai clic na cela A6 .
5. Introduza o número 5 para indicar que se está ingresando o total de vendas do quinto día.
6. Fai clic na cela B6.
7. Escriba o número $ 1458.25 e prema a tecla Enter no teclado.

A célula B7 actualízase co novo total de $ 7137.40.

Cando fai clic na cela B7, a fórmula actualizada = SUM (B2: OFFSET (B7, -1,0)) aparece na barra de fórmulas.

Nota : A función OFFSET ten dous argumentos opcionais: Altura e Ancho, que se omitiron neste exemplo.

Estes argumentos pódense usar para contar a función OFFSET a forma da saída en función de que hai tantas filas de alto e tantas columnas de ancho.

Ao omitir estes argumentos, a función, por defecto, usa o alto e ancho do argumento de referencia no seu lugar, que neste exemplo ten unha fila alta e unha columna de ancho.