Cómo utilizar la función OFFSET de Excel

El Excel OFFSET devuelve un rango de celdas de referencia que están desplazadas (de ahí el nombre) de una celda de referencia. En otras palabras, se especifica el desplazamiento en términos de número de filas y columnas, y también se puede indicar el tamaño de la matriz a la que se desea hacer referencia.

Sintaxis

• referencia es la celda inicial a partir de la cual se desplazan los resultados
• filas es el número de filas que desea desplazar de la celda de referencia
• cols es el número de columnas que desea desplazar de la celda de referencia
• altura es la altura de la matriz que desea devolver. Por defecto es 1
• anchura es la anchura de la matriz que desea devolver. Por defecto es 1

Cómo utilizar la función OFFSET de Excel

Ahora mostraré la función en acción. Echa un vistazo al siguiente ejemplo.

Tengo unos datos de una tienda de ropa que indican cuántas unidades se han vendido de cada artículo, por talla. Utilizo la función OFFSET para referenciar datos dentro de esta tabla.

Como puede ver, escribo la función =OFFSET(A1,4,2) para extraer los datos que quiero de la tabla. En este caso, empiezo con una celda de referencia A1, luego le digo a la función que se desplace 4 filas hacia abajo y 2 filas hacia el otro lado. Como resultado, realiza ese desplazamiento y devuelve los resultados de la celda C5.

Si quiero obtener las ventas de camisetas grandes, puedo utilizar la fórmula =OFFSET(A1,2,3). Esta función es la misma que en el caso anterior, excepto que le digo a la función que se desplace 2 filas hacia abajo y 3 columnas hacia la derecha de la celda A1.

Observe que no he introducido los dos parámetros opcionales, [height] y [width]. Esto es porque sólo quiero devolver 1 celda, por lo que no tengo que introducir nada para estos ya que por defecto a 1.

Sin embargo, si quisiera devolver varias celdas, tendría que especificar las dimensiones de la salida deseada.

Utilización de OFFSET con otras funciones

La función OFFSET de Excel también puede combinarse con otras funciones.

En este ejemplo, combino OFFSET con la función SUM para obtener el total de ventas de Ropa grande.

La fórmula es la siguiente =SUMA(OFFSET(A1,2,3,7,1)). Esta vez, introduzco 7 para el parámetro de altura. Esto hace que la función devuelva el rango D3:D9, que abarca todos los elementos grandes. Luego los suma todos con la función SUMA.

Si sigues confundido con este ejemplo, esto es lo que le decimos a la función que haga: empieza en la celda A1, luego muévete 2 filas hacia abajo, 3 filas a lo ancho, y devuelve los resultados de un array de 7 (alto) por 1 (ancho).

Por último, la función SUMA suma esta matriz (D3:D9).

Uso de OFFSET para crear un rango dinámico

Uno de los principales usos de la función OFFSET es crear un rango dinámico. Un rango dinámico es un rango que se actualiza automáticamente cuando se añaden nuevos datos.

Eche un vistazo al siguiente ejemplo. Aquí, utilizo la función OFFSET para crear un rango dinámico de toda la tabla que se actualizará si se insertan nuevos datos.

Por último, lo combino con la función AVG para obtener las ventas medias por artículo.

Esto se consigue con la fórmula =MEDIA((A2:OFFSET(D10,-1,0,))). Utilizo la función OFFSET para identificar el límite inferior del intervalo que quiero promediar.

El rango va desde la celda A2 (fija) hasta una celda por encima de D10. El beneficio aquí es que si inserto una nueva fila, la función se actualizará automáticamente:

La fórmula es la siguiente =MEDIA((A2:OFFSET(D11,-1,0,))). El OFFSET se actualizó de D10 a D11, lo que mantuvo toda la tabla incluida en el cálculo de la media. No cambié la fórmula, sólo añadí una nueva fila (zapatos) y la función se actualizó automáticamente.

Espero que ahora sepas cómo y cuándo utilizar la función OFFSET.

Función OFFSET

¡Y ya está! Utilice la función OFFSET para devolver un rango de referencia de celdas que están desplazadas de una celda de referencia.

Si desea obtener más información sobre las distintas fórmulas de Google Sheets, eche un vistazo a nuestra entrada del blog sobre la fórmula ARRAY_CONSTRAIN en Google Sheets.

También puede consultar las entradas del blog relacionadas a continuación.