Cómo utilizar la función OFFSET de Excel -

Resumen

La función DESPLAZAMIENTO de Excel devuelve una referencia a un rango construido con cinco entradas: (1) un punto de partida, (2) un desplazamiento de fila, (3) un desplazamiento de columna, (4) una altura en filas, (5) un ancho en columnas. OFFSET es útil en fórmulas que requieren un rango dinámico.

Propósito

Crear un desplazamiento de referencia desde el punto de partida dado

Valor devuelto

Una referencia de celda.

Sintaxis

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

Argumentos

• referencia : el punto de partida, proporcionado como referencia de celda o rango.
• filas : el número de filas que se desplazarán por debajo de la referencia inicial.
• cols : el número de columnas que se desplazarán a la derecha de la referencia inicial.
• altura : (opcional) la altura en filas de la referencia devuelta.
• ancho : (opcional) el ancho en columnas de la referencia devuelta.

Versión

Excel 2003

Notas de uso

La función DESPLAZAMIENTO de Excel devuelve un rango dinámico construido con cinco entradas: (1) un punto de partida, (2) un desplazamiento de fila, (3) un desplazamiento de columna, (4) una altura en filas, (5) un ancho en columnas.

El punto de partida (el argumento de referencia ) puede ser una celda o un rango de celdas. Los argumentos de filas y columnas son el número de celdas que se deben "desplazar" desde el punto de partida. Los argumentos de alto y ancho son opcionales y determinan el tamaño del rango que se crea. Cuando se omiten la altura y el ancho , se toman de forma predeterminada la altura y el ancho de referencia .

Por ejemplo, para hacer referencia a C5 comenzando en A1, la referencia es A1, las filas son 4 y las columnas son 2:

=OFFSET(A1,4,2) // returns reference to C5

Para hacer referencia a C1: C5 de A1, la referencia es A1, las filas son 0, las columnas son 2, la altura es 5 y el ancho es 1:

=OFFSET(A1,0,2,5,1) // returns reference to C1:C5

Nota: el ancho podría omitirse, ya que el valor predeterminado será 1.

Es común ver a OFFSET envuelto en otra función que espera un rango. Por ejemplo, a SUM C1: C5, comenzando en A1:

=SUM(OFFSET(A1,0,2,5,1)) // SUM C1:C5

El propósito principal de OFFSET es permitir que las fórmulas se ajusten dinámicamente a los datos disponibles oa la entrada del usuario. La función DESPLAZAMIENTO se puede utilizar para crear un rango dinámico con nombre para gráficos o tablas dinámicas, a fin de garantizar que los datos de origen estén siempre actualizados.

Nota: La documentación de Excel indica que la altura y el ancho no pueden ser negativos, pero los valores negativos parecen haber funcionado bien desde principios de la década de 1990. La función OFFSET en Google Sheets no permitirá un valor negativo para los argumentos de altura o ancho.

Ejemplos

Los ejemplos siguientes muestran cómo se puede configurar OFFSET para devolver diferentes tipos de rangos. Estas pantallas se tomaron con Excel 365, por lo que OFFSET devuelve una matriz dinámica cuando el resultado es más de una celda. En versiones anteriores de Excel, puede usar la tecla F9 para verificar los resultados devueltos por OFFSET.

Ejemplo 1

En la siguiente pantalla, usamos OFFSET para devolver el tercer valor (marzo) en la segunda columna (oeste). La fórmula en H4 es:

=OFFSET(B3,3,2) // returns D6

Ejemplo # 2

En la siguiente pantalla, usamos OFFSET para devolver el último valor (junio) en la tercera columna (norte). La fórmula en H4 es:

=OFFSET(B3,6,3) // returns E9

Ejemplo # 3

A continuación, usamos OFFSET para devolver todos los valores en la tercera columna (Norte). La fórmula en H4 es:

=OFFSET(B3,1,3,6) // returns E4:E9

Ejemplo # 4

A continuación, usamos OFFSET para devolver todos los valores de mayo (quinta fila). La fórmula en H4 es:

=OFFSET(B3,5,1,1,4) // returns C8:F8

Ejemplo # 5

A continuación, usamos OFFSET para devolver el valor de abril, mayo y junio para la región Oeste. La fórmula en H4 es:

=OFFSET(B3,4,2,3,1) // returns D7:D9

Ejemplo # 6

A continuación, usamos OFFSET para devolver el valor de abril, mayo y junio para el oeste y el norte. La fórmula en H4 es:

=OFFSET(B3,4,2,3,2) // returns D7:E9

Notas

• OFFSET solo devuelve una referencia, no se mueven celdas.
• Tanto las filas como las columnas pueden proporcionarse como números negativos para invertir su dirección de desplazamiento normal: las columnas negativas desplazadas hacia la izquierda y las filas negativas desplazadas arriba.
• OFFSET es una "función volátil" - se recalculará con cada cambio de hoja de trabajo. Las funciones volátiles pueden hacer que los libros de trabajo más grandes y complejos se ejecuten lentamente.
• OFFSET mostrará #REF! valor de error si el desplazamiento está fuera del borde de la hoja de trabajo.
• Cuando se omite el alto o el ancho, se usa el alto y el ancho de referencia .
• OFFSET se puede utilizar con cualquier otra función que espere recibir una referencia.
• La documentación de Excel dice que la altura y el ancho no pueden ser negativos, pero los valores negativos funcionan.

Videos relacionados

Cómo crear un rango dinámico con nombre con OFFSET En este video, veremos cómo crear un rango dinámico con nombre usando la función OFFSET, la forma más común de crear un rango dinámico de nombres con una fórmula.