Función OFFSET de Excel

La función OFFSET de Excel devuelve una referencia a una celda o un rango de celdas que está desplazado desde una celda específica por un número determinado de filas y columnas.

Sintaxis

=OFFSET (referencia, filas, cols, [altura], [ancho])

Argumentos

Valor devuelto

La función OFFSET devolverá una referencia de celda desplazada desde un punto de partida dado.

Notas de la función

1. Se devolverá el valor de error #¡VALOR! cuando la referencia se refiera a un rango de celdas discontinuas.
2. Se devolverá el valor de error #¡REF! cuando las filas y columnas desplazadas superen los límites de la hoja de trabajo.

Ejemplos

Ejemplo 1: Uso básico de la función OFFSET

Devuelve una referencia a una celda con la siguiente fórmula:
=OFFSET(B2,3,1)

En este caso, B2 es el punto de partida, los números 3 y 1 significan que se mueven 3 filas hacia abajo y 1 columna hacia la derecha desde la celda B2, y finalmente se devuelve el valor en C5. Ver captura de pantalla:

Devuelve una referencia a un rango de celdas con la siguiente fórmula:
=OFFSET(B2,3,1,2,2)

En este caso, obtendrá los resultados de un rango de 2 x 2 que está 3 filas debajo y 1 columna a la derecha de la celda B2.

Nota: Ocurrirá un error #¡VALOR! cuando seleccione solo una celda para aplicar la función OFFSET para devolver un rango de celdas. Necesita seleccionar un rango de 2 x 2 (por ejemplo, 4 celdas en blanco), ingresar la fórmula y presionar las teclas Ctrl + Shift + Enter para obtener los resultados.

Ejemplo 2: Usar la función OFFSET para sumar un rango de valores

Como mencionamos anteriormente, si intenta usar la función OFFSET como =OFFSET(B2,3,1,2,2) por sí sola en una sola celda, devolverá un error #¡VALOR!. Sin embargo, si combina las funciones SUM y OFFSET como se muestra en la siguiente captura de pantalla, devolverá directamente la suma de los valores en el rango C5:D6.

1. Seleccione una celda en blanco, copie la siguiente fórmula en ella y presione la tecla Enter para obtener el resultado.
=SUM(OFFSET(B2,3,1,2,2)))

Ejemplo 3: Sumar columnas basadas en ciertos criterios

Como se muestra en la siguiente captura de pantalla, ¿cómo obtener el total de ventas de Mango de domingo a martes? Por favor, pruebe lo siguiente.

1. Seleccione una celda en blanco, copie la siguiente fórmula en ella y presione la tecla Enter para obtener el resultado.
=SUM(OFFSET(C2:E2,MATCH(G4,B3:B8,),))

Notas:

1. En la fórmula anterior, MATCH(G4,B3:B8) busca Mango y devuelve su posición en el rango B3:B8. Veamos, Mango se encuentra 5 filas debajo de la celda B2, por lo que devuelve el número 5;
2. Como el resultado de MATCH es 5, y la última coma aquí representa 0, la función OFFSET ahora se muestra como OFFSET(C2:E2,5,0), lo que significa que el desplazamiento comienza desde el rango C2:E2, se mueve 5 filas hacia abajo y 0 columnas hacia la derecha para obtener la referencia al rango C7:E7;
3. Luego, la función SUM se muestra como SUM(C7:E7), y finalmente devuelve la suma de los valores en el rango C7:E7.

Más ejemplos

¿Cómo sumar cada n filas hacia abajo en Excel?

¿Cómo crear un rango con nombre dinámico en Excel?

¿Cómo devolver el valor de una celda cada cinco o cada n filas en Excel?