Función de compensación de Excel (tabla de contenido)

  • OFFSET en Excel
  • Fórmula OFFSET en Excel
  • ¿Cómo usar la función OFFSET en Excel?

OFFSET en Excel

La función OFFSET en Excel ayuda al usuario a devolver una celda o rango de celdas que es un no especificado. de filas y columnas de una celda. Podemos especificar el no. de filas y no. de columnas a devolver.

A continuación se muestra la fórmula OFFSET en Excel:

La función OFFSET en Excel consta de los siguientes argumentos:

  • Referencia: es el argumento a partir del cual queremos basar el desplazamiento. Puede ser una referencia de celda o un rango de celdas adyacentes, si no, Offset devuelve el # ¡VALOR! Valor de error
  • Filas: es el no. de filas para compensar. Si usamos un número positivo, entonces se desplaza a las filas de abajo y si se usa un número negativo, entonces compensa las filas de arriba.
  • Columnas: es para no. de columnas para compensar. Entonces, es el mismo concepto que Filas, si usamos un número positivo, entonces se desplaza a las columnas a la derecha y si se usa un número negativo, entonces, se desplaza a las columnas a la izquierda.
  • Altura: este argumento es opcional. Debería ser un número positivo. Es un número que representa el número de filas en la referencia devuelta.
  • Ancho: este también es un argumento opcional. Debe ser un número positivo. Es un número que representa el número de columnas en la referencia devuelta.

Notas:

  • La función OFFSET en Excel devuelve el #REF! valor de error, si las filas y columnas compensan la referencia sobre el borde de la hoja de trabajo.
  • En la función OFFSET, se supone que tiene la misma altura o anchura que una referencia si se omite la altura o la anchura.
  • OFFSET devuelve una referencia, en realidad no mueve ninguna celda o rango de celdas ni cambia la selección. Se puede usar con cualquier función que espere un argumento de referencia.

¿Cómo usar la función OFFSET en Excel?

La función OFFSET en Excel es muy simple y fácil de usar. Comprendamos el funcionamiento de la función OFFSET en Excel mediante algún ejemplo de fórmula OFFSET.

Puede descargar esta plantilla Excel de la función OFFSET aquí - Plantilla Excel de la función OFFSET

Ejemplo 1

La fórmula Offset devuelve una referencia de celda basada en un punto de partida, filas y columnas que especificamos. Podemos verlo en el siguiente ejemplo:

= DESPLAZAMIENTO (A1, 3, 1)

La fórmula le dice a Excel que considere la celda A1 para el punto de partida (referencia), luego mueva 3 filas hacia abajo (filas) y 1 columna hacia la izquierda (argumento de columnas). Después de esto, la fórmula OFFSET devuelve el valor en la celda B4.

La imagen a la izquierda muestra que la ruta de la función y la captura de pantalla a la derecha demuestran cómo podemos usar la fórmula OFFSET en tiempo real. La diferencia entre las dos fórmulas es que la segunda (a la derecha) incluye una referencia de celda (D1) en el argumento de la fila. Pero dado que la celda D1 contiene el número 1, y aparece exactamente el mismo número en el argumento de filas de la primera fórmula, ambos arrojarían un resultado idéntico: el valor en B2.

Ejemplo # 2 Uso de la función OFFSET para la suma

Como sabemos que la función OFFSET también se puede usar con la otra función de Excel, por lo tanto, en este ejemplo veremos con la función SUMA.

En la imagen anterior, supongamos que tenemos que encontrar la suma de las marcas y luego también podemos usar la función DESPLAZAMIENTO.

La fórmula está abajo:

Entonces, en la fórmula anterior D5 es la primera celda donde comienzan los datos, que es una referencia como celda inicial. Después de eso, el valor de las filas y la columna es 0, por lo que podemos ponerlo como 0, 0. Luego hay 4, lo que significa 4 filas debajo de la referencia para la cual Excel necesita calcular la suma y luego, por fin, es 1, significa que hay 1 columna de ancho.

Podemos referirnos a la imagen de arriba para correlacionar el ejemplo explicado.

Y a continuación se muestra el resultado:

Solo tenemos que presionar Enter después de ingresar una fórmula en la celda requerida y luego tenemos el resultado.

Cosas para recordar sobre la función OFFSET en Excel

  • La función OFFSET en Excel solo devuelve una referencia, no mueve ninguna celda o rango de celdas en real.
  • Si la fórmula OFFSET devuelve un rango de celdas, las filas y columnas siempre se refieren a la celda superior izquierda en el rango devuelto.
  • La referencia debe incluir una celda o un rango de celdas; de lo contrario, la fórmula devolverá el error #VALOR.
  • Si las filas y columnas especificadas sobre el borde de la hoja de cálculo, la fórmula Offset en Excel devolverá el #REF! error.
  • La función OFFSET se puede usar dentro de cualquier otra función de Excel que acepte una referencia de celda o rango en sus argumentos.

Por ejemplo, si estamos usando la fórmula = OFFSET (A1, 3, 1, 1, 3) por sí sola, ¡dará un #VALOR! Error, ya que un rango de retorno (1 fila, 3 columnas) no cabe en una celda. Sin embargo, si lo clasificamos en la función SUMA como se muestra a continuación:

= SUMA (DESPLAZAMIENTO (A1, 3, 1, 1, 3))

La fórmula Offset devuelve la suma de valores en una fila por un rango de 3 columnas que está 3 filas debajo y 1 columna a la derecha de la celda A1, es decir, el total de valores en las celdas B4: D4.

Como podemos ver, el total de 36 + 63 + 82 de Mar resaltado es igual a 181, que se produjo como resultado en G2 después de aplicar la fórmula de suma y compensación. (resaltado en amarillo)

Uso de la función OFFSET en Excel

Como hemos visto que lo que la función OFFSET realmente hace con la ayuda del ejemplo anterior, podemos tener preguntas que ¿Por qué molestarse en interpretar la fórmula OFFSET que es un poco compleja, por qué no simplemente usar una referencia directa como sum o B4: D4?

La función OFFSET en Excel es muy buena para lo siguiente:

  • Obteniendo el rango desde la celda inicial:

A veces, no sabemos la dirección exacta o real del rango, solo sabemos desde dónde comienza desde una celda determinada. En este caso, podemos usar la función OFFSET, que es fácil de usar.

  • Crear rango dinámico:

Las referencias como el ejemplo anterior B1: C4 son estáticas, significa que siempre se refieren a un rango determinado o fijo. Pero en algunos casos, las tareas son más fáciles de realizar con rangos dinámicos. Es especialmente útil cuando trabajamos con el cambio de datos. Por ejemplo, podríamos tener una hoja de cálculo donde se inserte o agregue una nueva fila o columna todos los días / semana / mes, en este caso, la función OFFSET ayudará.

Limitaciones y alternativas de la función OFFSET en Excel

Como todas y cada una de las fórmulas en Excel tienen sus propias limitaciones y alternativas, hemos visto cómo y cuándo usar la función OFFSET en Excel.

A continuación se detallan las limitaciones críticas de la función OFFSET en Excel:

  • Función hambrienta de recursos:

Significa que siempre que haya un cambio en los datos de origen, la fórmula se volverá a calcular para todo el conjunto, lo que mantiene a Excel ocupado durante más tiempo. Si hay una hoja de cálculo pequeña, no afectará tanto, pero si hay varias hojas de cálculo, Excel puede tardar un tiempo en volver a calcular.

  • Dificultad en la revisión:

Como sabemos, las referencias devueltas por la función Offset son dinámicas o cambiantes y pueden contener una gran fórmula, que puede ser difícil de corregir o editar según los requisitos.

Alternativas de la función OFFSET en Excel:

  • Función ÍNDICE:

La función ÍNDICE en Excel también se puede usar para crear un rango dinámico de referencias. No es exactamente lo mismo que OFFSET. Pero al igual que OFFSET, la función INDEX no es tan volátil, por lo tanto, no ralentizará el Excel, que es una limitación para OFFSET.

  • Función indirecta:

También podemos usar la función INDIRECTO para crear un rango dinámico de referencias de muchas fuentes, como valores de celdas , texto y los rangos con nombre.

Artículos recomendados

Esta ha sido una guía para OFFSET en Excel. Aquí discutimos la fórmula OFFSET en Excel y cómo usar la función OFFSET en Excel junto con ejemplos prácticos y una plantilla de Excel descargable. También puede consultar nuestros otros artículos sugeridos:

  1. MS Excel: Función de BUSCAR
  2. El mejor ejemplo de la función Excel XIRR
  3. Guía increíble sobre la función NO en Excel
  4. IPMT en Excel con ejemplos

Categoría:

Consejos de Excel