Excel OFFSET Función

El excel Función de desplazamiento devuelve una referencia a una celda o un rango de celdas que se desplaza de una celda específica por un número determinado de filas y columnas.

Sintaxis

=OFFSET (reference, rows, cols, [height], [width])

Argumentos

Valor de retorno

La función DESPLAZAMIENTO devolverá un desplazamiento de referencia de celda desde un punto de inicio dado.

Notas de función

1. # ¡VALOR! El valor de error volverá cuando la referencia se refiera a un rango de celdas discontinuas.
2. #REF! El valor de error regresará cuando las filas y columnas desplacen la referencia sobre el borde 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 mover 3 filas hacia abajo y 1 columna a la derecha desde la celda B2, y devolver el valor en C5 finalmente. 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 del rango 2 x 2 que está 3 filas por debajo y 1 columna a la derecha de la celda B2.

Nota:: # ¡VALOR! Se producirá un error cuando solo seleccione una celda para aplicar la función DESPLAZAMIENTO para devolver un rango de celdas. Debe seleccionar un rango de 2 x 2 (dice 4 celdas en blanco), ingrese la fórmula y presione el Ctrl + cambio + Enviar claves para obtener los resultados.

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

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

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

Ejemplo 3: sumar columnas según ciertos criterios

Como se muestra en la siguiente captura de pantalla, ¿cómo obtener las ventas totales de Mango de domingo a martes? Intente lo siguiente.

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

Notas:

1. En la fórmula anterior, MATCH(G4,B3:B8) está buscando Mango y regresando su posición en el rango B3: B8. Veamos, Mango ubica 5 filas debajo de la celda B2, por lo que devuelve el número 5;
2. Como el resultado COINCIDIR es 5, y la última coma aquí representa 0, la función DESPLAZAMIENTO ahora se muestra como OFFSET(C2:E2,5,0), lo que significa que el desplazamiento comienza desde el rango C2: E2, mueva 5 filas hacia abajo y 0 columna 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 en Excel?

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

¿Cómo devolver el valor de la celda cada cinco o enésima fila en Excel?