Si su hoja de cálculo de Excel incluye cálculos que se basan en un rango cambiante de celdas, use las funciones SUMA y COMPENSACIÓN juntas en una fórmula de COMPENSACIÓN DE SUMA para simplificar la tarea de mantener los cálculos actualizados.
Las instrucciones de este artículo se aplican a Excel para Microsoft 365, Excel 2019, Excel 2016, Excel 2013 y Excel 2010.
Cree un rango dinámico con las funciones SUM y OFFSET
Si usa cálculos para un período de tiempo que cambia continuamente, como determinar las ventas del mes, use la función COMPENSACIÓN en Excel para configurar un rango dinámico que cambia a medida que se agregan las cifras de ventas de cada día.
Por sí misma, la función SUMA generalmente puede acomodar la inserción de nuevas celdas de datos en el rango que se está sumando. Se produce una excepción cuando los datos se insertan en la celda donde se encuentra actualmente la función.
En el siguiente ejemplo, las nuevas cifras de ventas de cada día se agregan al final de la lista, lo que obliga al total a desplazarse continuamente una celda hacia abajo cada vez que se agregan nuevos datos.
Para seguir este tutorial, abra una hoja de cálculo de Excel en blanco e ingrese los datos de muestra. No es necesario formatear su hoja de cálculo como en el ejemplo, pero asegúrese de ingresar los datos en las mismas celdas.
Si solo se utiliza la función SUMA para sumar los datos, el rango de celdas utilizado como argumento de la función deberá modificarse cada vez que se agreguen nuevos datos.
Al usar las funciones SUM y OFFSET juntas, el rango totalizado se vuelve dinámico y cambia para adaptarse a nuevas celdas de datos. La adición de nuevas celdas de datos no causa problemas porque el rango continúa ajustándose a medida que se agrega cada nueva celda.
Sintaxis y argumentos
En esta fórmula, la función SUMA se usa para sumar el rango de datos suministrado como argumento. El punto de inicio de este rango es estático y se identifica como la referencia de celda al primer número que se totalizará mediante la fórmula.
La función OFFSET está anidada dentro de la función SUM y crea un punto final dinámico para el rango de datos totalizados por la fórmula. Esto se logra estableciendo el punto final del rango en una celda por encima de la ubicación de la fórmula.
La sintaxis de la fórmula es:
=SUMA(Rango inicial:DESPLAZAMIENTO(Referencia, Filas, Columnas))
Los argumentos son:
- Range Start: El punto de inicio para el rango de celdas que será totalizado por la función SUMA. En este ejemplo, el punto de partida es la celda B2.
- Reference: la referencia de celda requerida utilizada para calcular el punto final del rango. En el ejemplo, el argumento Referencia es la referencia de celda para la fórmula porque el rango termina una celda arriba de la fórmula.
- Rows: Se requiere el número de filas por encima o por debajo del argumento de referencia utilizado para calcular el desplazamiento. Este valor puede ser positivo, negativo o establecerse en cero. Si la ubicación de desplazamiento está por encima del argumento de referencia, el valor es negativo. Si el desplazamiento está por debajo, el argumento Filas es positivo. Si el desplazamiento se encuentra en la misma fila, el argumento es cero. En este ejemplo, el desplazamiento comienza una fila por encima del argumento Referencia, por lo que el valor del argumento es uno negativo (-1).
- Cols: el número de columnas a la izquierda o a la derecha del argumento de referencia utilizado para calcular el desplazamiento. Este valor puede ser positivo, negativo o establecerse en cero. Si la ubicación del desplazamiento está a la izquierda del argumento Referencia, este valor es negativo. Si el desplazamiento es a la derecha, el argumento Cols es positivo. En este ejemplo, los datos que se suman están en la misma columna que la fórmula, por lo que el valor de este argumento es cero.
Use la fórmula SUM OFFSET para obtener el total de datos de ventas
Este ejemplo utiliza una fórmula de COMPENSACIÓN DE SUMA para devolver el total de las cifras de ventas diarias enumeradas en la columna B de la hoja de trabajo. Inicialmente, la fórmula se ingresó en la celda B6 y totalizó los datos de ventas durante cuatro días.
El siguiente paso es mover la fórmula SUM OFFSET una fila hacia abajo para dejar espacio para el total de ventas del quinto día. Esto se logra insertando una nueva fila 6, que mueve la fórmula a la fila 7.
Como resultado del movimiento, Excel actualiza automáticamente el argumento Referencia a la celda B7 y agrega la celda B6 al rango sumado por la fórmula.
- Seleccione la celda B6, que es la ubicación donde se mostrarán inicialmente los resultados de la fórmula.
-
Seleccione la pestaña Fórmulas de la cinta.
Image -
Seleccione Matemáticas y Trigonometría.
Image -
Seleccione SUMA.
Image - En el cuadro de diálogo Argumentos de función, coloque el cursor en el cuadro de texto Número1.
-
En la hoja de trabajo, seleccione la celda B2 para ingresar esta referencia de celda en el cuadro de diálogo. Esta ubicación es el punto final estático de la fórmula.
Image - En el cuadro de diálogo Argumentos de función, coloque el cursor en el cuadro de texto Número2.
-
Introduzca DESPLAZAMIENTO(B6, -1, 0). Esta función OFFSET forma el punto final dinámico de la fórmula.
Image -
Seleccione OK para completar la función y cerrar el cuadro de diálogo. El total aparece en la celda B6.
Image
Agregar los datos de ventas del día siguiente
Para agregar los datos de ventas del día siguiente:
- Haga clic con el botón derecho en el encabezado de la fila 6.
-
Seleccione Insertar para insertar una nueva fila en la hoja de trabajo. La fórmula SUM OFFSET baja una fila hasta la celda B7 y la fila 6 ahora está vacía.
Image - Seleccione la celda A6 e ingrese el número 5 para indicar que se está ingresando el total de ventas del quinto día.
-
Seleccione la celda B6, ingrese $1458.25, luego presione Enter.
Image - La celda B7 se actualiza al nuevo total de $7137.40.
Cuando selecciona la celda B7, la fórmula actualizada aparece en la barra de fórmulas.
=SUMA(B2:DESPLAZAMIENTO(B7, -1, 0))
La función OFFSET tiene dos argumentos opcionales: Alto y Ancho, que no se usaron en este ejemplo. Estos argumentos le indican a la función OFFSET la forma de la salida en términos del número de filas y columnas.
Al omitir estos argumentos, la función usa la altura y el ancho del argumento Referencia, que, en este ejemplo, tiene una fila de alto y una columna de ancho.
