✅ Función OFFSET de Excel - Ejemplos de fórmulas + video GRATIS

Tabla de contenido

Función de compensación de Excel (ejemplo + video)
Comprensión de los conceptos básicos de la función OFFSET de Excel
Función OFFSET de Excel: ejemplos
Alternativas de la función OFFSET de Excel
Función OFFSET de Excel - Tutorial en vídeo

Función de compensación de Excel (ejemplo + video)

Cuándo usar la función OFFSET de Excel

La función DESPLAZAMIENTO de Excel se puede utilizar cuando desee obtener una referencia que desplace el número especificado de filas y columnas desde el punto de partida.

Lo que devuelve

Devuelve la referencia a la que apunta la función OFFSET.

Sintaxis

= DESPLAZAMIENTO (referencia, filas, columnas, [alto], [ancho])

Argumentos de entrada

referencia - La referencia de la que desea compensar. Puede ser una referencia de celda o un rango de celdas adyacentes.
filas - el número de filas a compensar. Si usa un número positivo, se desplaza a las filas de abajo, y si se usa un número negativo, entonces se desplaza a las filas de arriba.
cols - el número de columnas a compensar. Si usa un número positivo, se desplaza a las columnas de la derecha, y si se usa un número negativo, se desplaza a las columnas de la izquierda.
[altura] - este es un número que representa el número de filas en la referencia devuelta.
[ancho] - este es un número que representa el número de columnas en la referencia devuelta.

Comprensión de los conceptos básicos de la función OFFSET de Excel

La función OFFSET de Excel es posiblemente una de las funciones más confusas de Excel.

Tomemos un ejemplo simple de un juego de ajedrez. Hay una pieza en el Ajedrez llamada Torre (también conocida como torre, marqués o rector).

[Imagen cortesía de Wonderful Wikipedia]

Ahora, como todas las demás piezas de ajedrez, una torre tiene un camino fijo en el que puede moverse por el tablero. Puede ir recto (hacia la derecha / izquierda o arriba / abajo del tablero), pero no puede ir en diagonal.

Por ejemplo, supongamos que tenemos un tablero de ajedrez en Excel (como se muestra a continuación), en una casilla determinada (D5 en este caso), la Torre solo puede ir en las cuatro direcciones resaltadas.

Ahora bien, si te pido que lleves la torre de su posición actual a la resaltada en amarillo, ¿qué le dirás a la torre?

Le pedirás que dé dos pasos hacia abajo y dos hacia la derecha … ¿no es así?

Y esa es una aproximación cercana de cómo funciona la función OFFSET.

Ahora veamos qué significa esto en Excel. Quiero comenzar con la celda D5 (que es donde está la Torre) y luego ir dos filas hacia abajo y dos columnas a la derecha y buscar el valor de la celda allí.

La fórmula sería:
= DESPLAZAMIENTO (desde dónde empezar, cuántas filas hacia abajo, cuántas columnas hacia la derecha)

Como puede ver, la fórmula del ejemplo anterior en la celda J1 es = DESPLAZAMIENTO (D5,2,2).

Comenzó en D5, luego bajó dos filas y dos columnas a la derecha y llegó a la celda F7. Luego devolvió el valor en la celda F7.

En el ejemplo anterior, observamos la función OFFSET con 3 argumentos. Pero hay dos argumentos opcionales más que se pueden utilizar.

Veamos un ejemplo simple aquí:

Suponga que desea usar la referencia a la celda A1 (en amarillo) y desea hacer referencia a todo el rango resaltado en azul (C2: E4) en una fórmula.

¿Cómo harías eso usando un teclado? Primero iría a la celda C2 y luego seleccionaría todas las celdas en C2: E4.

Ahora veamos cómo hacer esto usando la fórmula OFFSET:

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

Si usa esta fórmula en una celda, devolverá un # ¡VALOR! error, pero si ingresa al modo de edición y selecciona la fórmula y presiona F9, verá que devuelve todos los valores que están resaltados en azul.

Ahora veamos cómo funciona esta fórmula:

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

El primer argumento es la celda donde debería comenzar.
El segundo argumento es 1, que le dice a Excel que devuelva una referencia que ha sido DESPLAZADA en 1 fila.
El tercer argumento es 2, que le dice a Excel que devuelva una referencia que ha sido DESPLAZADA por 2 columnas.
El cuarto argumento es 3. Esto especifica que la referencia debe cubrir 3 filas. Esto se llama argumento de altura.
El quinto argumento es 3. Esto especifica que la referencia debe cubrir 3 columnas. Esto se llama argumento de ancho.

Ahora que tiene la referencia a un rango de celdas (C2: E4), puede usarla dentro de otra función (como SUM, COUNT, MAX, AVERAGE).

Con suerte, tiene un buen conocimiento básico del uso de la función OFFSET de Excel. Ahora echemos un vistazo a algunos ejemplos prácticos del uso de la función OFFSET.

Función OFFSET de Excel: ejemplos

Aquí hay dos ejemplos del uso de la función OFFSET de Excel.

Ejemplo 1: encontrar la última celda llena en la columna

Suponga que tiene algunos datos en una columna como se muestra a continuación:

Para encontrar la última celda de la columna, use la siguiente fórmula:

= DESPLAZAMIENTO (A1, RECUENTO (A: A) -1,0)

Esta fórmula asume que no hay valores aparte de los que se muestran y estas celdas no tienen una celda en blanco. Funciona contando el número total de celdas que están llenas y compensa la celda A1 en consecuencia.

Por ejemplo, en este caso, hay 8 valores, por lo que COUNT (A: A) devuelve 8. Desplazamos la celda A1 por 7 para obtener el último valor.

Ejemplo 2: creación de un menú desplegable dinámico

Puede ampliar los conceptos que se muestran en el Ejemplo 1 para crear rangos dinámicos con nombre. Estos podrían ser útiles si está utilizando los rangos con nombre en fórmulas o al crear menús desplegables y es probable que agregue / elimine datos de la referencia que hace el rango con nombre.

Aquí hay un ejemplo:

Tenga en cuenta que el menú desplegable se ajusta automáticamente cuando se agrega o elimina el año.

Esto sucede porque la fórmula que se utiliza para crear el menú desplegable es dinámica e identifica cualquier adición o eliminación y ajusta el rango en consecuencia.

He aquí cómo hacer esto:

Seleccione la celda donde desea insertar el menú desplegable.
Vaya a Datos -> Herramientas de datos -> Validación de datos.
En el cuadro de diálogo Validación de datos, dentro de la pestaña Configuración, seleccione Lista en el menú desplegable.
En la fuente, ingrese la siguiente fórmula: = OFFSET (A1,0,0, COUNT (A: A), 1)
Haga clic en Aceptar.

Veamos cómo funciona esta fórmula:

Los primeros tres argumentos de la función OFFSET son A1, 0 y 0. Esto esencialmente significa que devolvería la misma celda de referencia (que es A1).
El cuarto argumento es para la altura y aquí la función COUNT devuelve el número total de elementos en la lista. Asume que no hay espacios en blanco en la lista.
El quinto argumento es 1, lo que indica que el ancho de la columna debe ser uno.

Notas adicionales:

OFFSET es una función volátil (utilícela con precaución).
- Vuelve a calcular cada vez que el libro de Excel está abierto o siempre que se activa un cálculo en la hoja de trabajo. Esto podría aumentar el tiempo de procesamiento y ralentizar su libro de trabajo.
Si se omite el valor de alto o ancho, se toma como el de la referencia.
Si filas y cols son números negativos, entonces la dirección de desplazamiento se invierte.

Alternativas de la función OFFSET de Excel

Debido a algunas de las limitaciones de la función OFFSET de Excel, es posible que desee considerar alternativas a la misma:

Función ÍNDICE: La función ÍNDICE también se puede utilizar para devolver una referencia de celda. Haga clic aquí para ver un ejemplo sobre cómo crear un rango dinámico con nombre usando la función INDICE.
Tabla de Excel: si usa referencias estructuradas en la tabla de Excel, no debe preocuparse por la adición de nuevos datos y la necesidad de ajustar las fórmulas.