Fórmula de Excel SUM y OFFSET

Cómo encontrar los totales para rangos dinámicos de datos Si su hoja de cálculo de Excel incluye cálculos basados ​​en un rango cambiante de celdas, el uso de las funciones SUMA y DESPLAZAMIENTO juntas en una fórmula SUMA DESPLAZADA simplifica la tarea de mantener los cálculos actualizados.

Cómo encontrar los totales para rangos dinámicos de datos

Si su hoja de cálculo de Excel incluye cálculos basados ​​en un rango cambiante de celdas, el uso de las funciones SUMA y DESPLAZAMIENTO juntas en una fórmula SUMA DESPLAZADA simplifica la tarea de mantener los cálculos actualizados.

Cree un rango dinámico con las funciones SUMA y DESPLAZAMIENTO

Si usa cálculos durante un período de tiempo que cambia continuamente, como el total de ventas para el mes, la función OFFSET en Excel le permite 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 suma.

Una excepción ocurre cuando los datos se insertan en la celda donde se encuentra actualmente la función.

En la imagen de ejemplo anterior, las nuevas cifras de ventas para cada día se agregan al final de la lista, lo que obliga al total a desplazarse continuamente hacia abajo una celda cada vez que se agregan los nuevos datos.

Si la función SUMA se usara sola para totalizar los datos, sería necesario modificar el rango de celdas utilizadas como argumento de la función cada vez que se agregaran datos nuevos.

Sin embargo, al usar las funciones SUMA y DESPLAZAMIENTO juntas, el rango totalizado se vuelve dinámico. En otras palabras, cambia para acomodar 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

Consulte la imagen de arriba para seguir este tutorial.

En esta fórmula, la función SUMA se usa para totalizar el rango de datos suministrados como argumento. El punto de inicio para este rango es estático y se identifica como la referencia de celda al primer número que totalizará la fórmula.

La función OFFSET está anidada dentro de la función SUMA y se usa para crear un punto final dinámico para el rango de datos totalizados por la fórmula. Esto se logra al establecer el punto final del rango en una celda sobre la ubicación de la fórmula.

La sintaxis de la fórmula:

 = SUMA (Inicio de rango: DESPLAZAMIENTO (Referencia, Filas, Cols)) 
  • Range Start : se requiere el punto de partida para el rango de celdas que se sumará a la función SUMA. En la imagen de ejemplo, esta es la celda B2.
  • Referencia : la referencia de celda requerida utilizada para calcular el punto final del rango se encuentra a muchas filas y columnas de distancia. En la imagen de ejemplo, el argumento Referencia es la referencia de celda para la fórmula en sí porque siempre desea que el rango termine una celda sobre la fórmula.
  • Filas : 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 establecido en cero. Si la ubicación del desplazamiento está por encima del argumento Referencia , el valor es negativo. Si está 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 de este argumento es negativo (-1).
  • Cols : se requiere el número de columnas a la izquierda o derecha del argumento de referencia utilizado para calcular el desplazamiento. Este valor puede ser positivo, negativo o establecido en cero. Si la ubicación del desplazamiento está a la izquierda del argumento Referencia, este valor es negativo. Si a la derecha, el argumento de Cols es positivo. En este ejemplo, los datos que se totalizan están en la misma columna que la fórmula, por lo que el valor de este argumento es cero.

Uso de la fórmula SUMA DE DESPLAZAMIENTO para datos de ventas totales

Este ejemplo utiliza una fórmula SUMA DE COMPENSACIÓN 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 SUMA DE COMPENSACIÓN hacia abajo una fila 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 hacia abajo a la fila 7.

Como resultado del movimiento, Excel actualiza automáticamente el argumento de referencia en la celda B7 y agrega la celda B6 al rango sumado por la fórmula.

Entrar en la fórmula SUMA DE DESPLAZAMIENTO

  1. Haga clic en la celda B6, que es la ubicación donde se mostrarán inicialmente los resultados de la fórmula.
  2. Haga clic en la pestaña Fórmulas del menú de la cinta.
  3. Elija Math & Trig de la cinta para abrir la lista desplegable de funciones.
  4. Haga clic en SUM en la lista para abrir el cuadro de diálogo de la función.
  5. En el cuadro de diálogo, haga clic en la línea Número1 .
  6. Haga clic en la celda B2 para ingresar esta referencia de celda en el cuadro de diálogo. Esta ubicación es el punto final estático para la fórmula.
  7. En el cuadro de diálogo, haga clic en la línea Número2 .
  8. Ingrese la siguiente función de DESPLAZAMIENTO: DESPLAZAMIENTO (B6, -1,0) para formar el punto final dinámico para la fórmula.
  9. Haga clic en Aceptar para completar la función y cerrar el cuadro de diálogo.

El total aparece en la celda B7.

Agregar los datos de ventas del día siguiente

Para agregar los datos de ventas del día siguiente:

  1. Haga clic derecho en el encabezado de la fila de la fila 6 para abrir el menú contextual.
  2. En el menú, haga clic en Insertar para insertar una nueva fila en la hoja de trabajo.
  3. Como resultado, la fórmula SUMA DE DESPLAZAMIENTO se mueve hacia abajo a la celda B7 y la fila 6 ahora está vacía.
  4. Haga clic en la celda A6 .
  5. Ingrese el número 5 para indicar que se ingresa el total de ventas para el quinto día.
  6. Haga clic en la celda B6.
  7. Escriba el número $ 1458.25 y presione la tecla Entrar en el teclado.

La celda B7 se actualiza al nuevo total de $ 7137.40.

Cuando hace clic en la celda B7, la fórmula actualizada = SUMA (B2: OFFSET (B7, -1,0)) aparece en la barra de fórmulas.

La función OFFSET tiene dos argumentos opcionales: Altura y Ancho , que no se utilizaron en este ejemplo.

Estos argumentos se pueden usar para indicarle a la función OFFSET la forma de la salida en términos de tantas filas altas y tantas columnas anchas.

Al omitir estos argumentos, la función, de manera predeterminada, utiliza el alto y el ancho del argumento de referencia, que, en este ejemplo, tiene una fila de alto y una columna de ancho.

Rate article
labsfabs.com
Add a comment