Fórmulas de SUMA y COMPENSACIÓN de Excel

Si su hoja de cálculo de Excel contiene cálculos basados ​​en rangos cambiantes de celdas, use las funciones SUMA y COMPENSACIÓN juntas en una fórmula SUMA-COMPENSACIÓN para que sea más fácil mantener sus cálculos actualizados.

Las instrucciones de este artículo se aplican a Excel para Microsoft 365, Excel 2023, Excel 2016, Excel 2013 y Excel 2010.

Cree un rango dinámico usando las funciones SUM y OFFSET

Trabaje con cálculos durante un período de tiempo que cambia constantemente, por ejemplo, B. Para encontrar las ventas del mes actual, use la función COMPENSACIÓN en Excel para establecer un rango dinámico que cambia a medida que se agregan los números de ventas cada día.

La función SUM en sí misma a menudo puede acomodar la inserción de nuevas celdas de datos dentro del rango sumado. Se lanza una excepción al pegar datos en la celda donde se encuentra actualmente la función.

En el siguiente ejemplo, los nuevos datos de ventas de cada día se agregan al final de la lista, lo que hace que el total general se desplace 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 datos de muestra. No es necesario formatear la tabla en el ejemplo, pero asegúrese de ingresar los datos en las mismas celdas.

Si solo usa la función SUMA para agregar datos, deberá cambiar el rango de celdas utilizadas como argumentos de la función cada vez que agregue datos nuevos.

Al usar las funciones SUMA y COMPENSACIÓN juntas, el rango de acumulación se vuelve dinámico y cambia para acomodar nuevas celdas de datos. Agregar nuevas celdas de datos no causará ningún problema, ya que el rango continuará ajustándose a medida que se agrega cada nueva celda.

Sintaxis y parámetros

En esta fórmula, la función SUMA se utiliza para sumar el rango de datos proporcionado como argumento. El punto de partida de este rango es estático y se identifica como una referencia de celda al primer número al que se agrega la fórmula.

La función OFFSET está anidada dentro de la función SUM y crea puntos finales dinámicos para el rango de datos que resume la fórmula. Esto se logra estableciendo el punto final del rango en la celda que se encuentra arriba de la posición de la fórmula.

La sintaxis de la fórmula es:

=SUMA(rango inicial: DESPLAZAMIENTO(referencia,fila,columna)) 
 

Los argumentos son:

• área de inicio: El punto de inicio del rango de celdas que agregará la función SUMA. En este ejemplo, el punto de partida es la celda B2.
• Referirse a: La referencia de celda deseada para calcular el final del rango. En el ejemplo, el parámetro de referencia es la referencia de celda de la fórmula porque el rango termina una celda arriba de la fórmula.
• La línea: Se requiere el número de filas por encima o por debajo del parámetro de referencia utilizado para calcular el desplazamiento. El valor puede ser positivo, negativo o puesto a cero. El valor es negativo si la posición de compensación es mayor que el parámetro de referencia. El parámetro Filas es positivo si el desplazamiento es menor. Si el desplazamiento está en la misma línea, el argumento es nulo. En este ejemplo, el desplazamiento comienza una línea por encima del parámetro Referencia, por lo que ese parámetro tiene un valor negativo (-1).
• ascuas: El número de columnas a la izquierda o derecha del parámetro de referencia utilizado para calcular el desplazamiento. El valor puede ser positivo, negativo o puesto a cero. El valor es negativo si la posición de desplazamiento está a la izquierda del parámetro Referencia. Si el desplazamiento es correcto, el parámetro cols es positivo. En este ejemplo, los datos para agregar están en la misma columna que la fórmula, por lo que el parámetro es nulo.

Use la fórmula SUM OFFSET para obtener datos de ventas totales

Este ejemplo usa la fórmula SUM OFFSET para devolver la suma de las ventas diarias enumeradas en la columna B de la hoja de cálculo. Primero, la fórmula se ingresa en la celda B6 y resume cuatro días de datos de ventas.

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 hace insertando una nueva línea 6, que mueve la fórmula a la línea 7.

Como resultado del movimiento, Excel actualiza automáticamente el parámetro de referencia en la celda B7 y agrega la celda B6 al rango que suma la fórmula.

1. Seleccionar celda B6, donde se muestran inicialmente los resultados de la fórmula.

2. Seleccionar pestaña fórmula Bufanda.

   

3. Elegir Matemáticas y Trigonometría.

   

4. Elegir añadir.

   

5. En diálogo parámetros de función, coloque el cursor en el cuadro de texto Numero 1.

6. En la tabla, seleccione la celda B2 Introduzca esta referencia de celda en el cuadro de diálogo. Esta posición es el punto final estático de la fórmula.

   

7. En diálogo parámetros de función, coloque el cursor en el cuadro de texto Número 2.

8. Meterse en Desplazamiento (B6,-1.0). Esta función OFFSET forma el punto final dinámico de la fórmula.

   Relacionado: ¿Qué pasó con la barra de bits?

   

9. Elegir aceptar Salga de la función y cierre el cuadro de diálogo. El total aparece en la celda B6.

   

Agregar datos de ventas para el día siguiente

Agregar datos de ventas para el día siguiente:

1. Haga clic con el botón derecho en el encabezado de fila de la fila 6.

2. Elegir insertar Inserta una nueva fila en la tabla. La fórmula SUM OFFSET baja una fila hasta la celda B7, la fila 6 ahora está vacía.

   

3. Seleccionar celda A6 e ingrese el número 5 Indica que se está ingresando el total de ventas del quinto día.

4. Seleccionar celda B6, Meterse en $1458.25, entonces presione Meterse en.

   

5. La celda B7 se actualiza con un nuevo total de $7.137,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 parámetros opcionales: alto y ancho, que no se usan en este ejemplo. Estos argumentos le indican a la función OFFSET la forma de la salida en función del número de filas y columnas.

Al omitir estos parámetros, la función usa la altura y el ancho de los parámetros de referencia, que en este caso tienen una fila de alto y una columna de ancho.