Función OFFSET de Excel: ejemplos y usos de fórmulas

La función OFFSET de Excel es una herramienta muy útil que nos permite desplazarnos en una hoja de cálculo y seleccionar un rango de celdas en base a un punto de referencia. En este artículo, exploraremos ejemplos y usos prácticos de esta fórmula, mostrándote cómo puedes maximizar tu eficiencia y productividad al utilizarla en tus proyectos. ¡Descubre todo lo que la función OFFSET puede hacer por ti!

Espero que no te hayas aburrido con tanta teoría. De todos modos, ahora llegamos a la parte más interesante: los usos prácticos de la función OFFSET.

Funciones de Excel DESPLAZAMIENTO y SUMA

El ejemplo que comentamos hace un momento demuestra el uso más simple de COMPENSACIÓN Y SUMA. Ahora, veamos estas funciones desde otro ángulo y veamos qué más pueden hacer.

Ejemplo 1. Una fórmula dinámica SUMA / COMPENSACIÓN

Cuando trabaje con hojas de trabajo que se actualizan continuamente, es posible que desee tener una fórmula SUMA que seleccione automáticamente todas las filas recién agregadas.

Supongamos que tiene datos de origen similares a los que ve en la captura de pantalla a continuación. Cada mes se agrega una nueva fila justo encima de la fórmula SUMA y, naturalmente, querrás incluirla en el total. En general, hay dos opciones: actualizar el rango en la fórmula SUMA cada vez manualmente o hacer que la fórmula COMPENSACIÓN lo haga por usted.
Función OFFSET de Excel: ejemplos y usos de fórmulas

Dado que la primera celda del rango a sumar se especificará directamente en la fórmula SUMA, solo debes decidir los parámetros para la función OFFSET de Excel, que obtendrá esa última celda del rango:

  • Reference – la celda que contiene el total, B9 en nuestro caso.
  • Rows – la celda justo encima del total, que requiere el número negativo -1.
  • Cols – es 0 porque no desea cambiar la columna.

Entonces, aquí va el patrón de fórmula SUMA / COMPENSACIÓN:

=SUMA(primera celda:(COMPENSAR(celda con total-1,0)

Modificada para el ejemplo anterior, la fórmula tiene el siguiente aspecto:

=SUM(B2:(OFFSET(B9, -1, 0)))

Y como se demuestra en la siguiente captura de pantalla, funciona perfectamente:
Función OFFSET de Excel: ejemplos y usos de fórmulas

Ejemplo 2. Fórmula OFFSET de Excel para sumar las últimas N filas

En el ejemplo anterior, supongamos que desea saber la cantidad de bonificaciones de los últimos N meses en lugar del total general. También desea que la fórmula incluya automáticamente cualquier fila nueva que agregue a la hoja.

Para esta tarea, usaremos DESPLAZAMIENTO de Excel en combinación con las funciones SUMA y CONTAR/CONTARA:

=SUM(OFFSET(B1,COUNT(B:B)-E1+1,0,E1,1))

o

=SUM(OFFSET(B1,COUNTA(B:B)-E1,0,E1,1))
Función OFFSET de Excel: ejemplos y usos de fórmulas

Los siguientes detalles pueden ayudarle a comprender mejor las fórmulas:

  • Reference – el encabezado de la columna cuyos valores desea sumar, celda B1 en este ejemplo.
  • Rows – para calcular el número de filas a compensar, utilice la función CONTAR o CONTARA.

    COUNT devuelve el número de celdas de la columna B que contienen números, de los cuales se restan los últimos N meses (el número es la celda E1) y se suma 1.

    Si CONTAR es su función preferida, no necesita agregar 1, ya que esta función cuenta todas las celdas que no están vacías y una fila de encabezado con un valor de texto agrega una celda adicional que nuestra fórmula necesita. Tenga en cuenta que esta fórmula funcionará correctamente sólo en una estructura de tabla similar: una fila de encabezado seguida de filas con números. Para diferentes diseños de tabla, es posible que necesite realizar algunos ajustes en la fórmula COMPENSACIÓN/CONTARA.

  • Cols – el número de columnas a compensar es cero (0).
  • Height – el número de filas a sumar se especifica en E1.
  • Width – 1 columna.

Usando la función OFFSET con PROMEDIO, MAX, MIN

De la misma manera que calculamos las bonificaciones para el últimos N meses, puedes obtener un promedio de los últimos N días, semanas o años así como encontrar sus valores máximos o mínimos. La única diferencia entre las fórmulas es el nombre de la primera función:

=AVERAGE(OFFSET(B1,COUNT(B:B)-E1+1,0,E1,1))

=MAX(OFFSET(B1,COUNT(B:B)-E1+1,0,E1,1))

=MIN(OFFSET(B1,COUNT(B:B)-E1+1,0,E1,1))
Función OFFSET de Excel: ejemplos y usos de fórmulas

El beneficio clave de estas fórmulas sobre el PROMEDIO(B5:B8) o MAX(B5:B8) habitual es que no tendrá que actualizar la fórmula cada vez que se actualice la tabla fuente. No importa cuántas filas nuevas se agreguen o eliminen en su hoja de trabajo, las fórmulas de DESPLAZAMIENTO siempre se referirán al número especificado de las últimas celdas (las más bajas) de la columna.

Fórmula de Excel OFFSET para crear un rango dinámico

Utilizada junto con COUNTA, la función OFFSET puede ayudarle a crear un rango dinámico que puede resultar útil en muchos escenarios, por ejemplo, para crear listas desplegables actualizables automáticamente.

La fórmula OFFSET para un rango dinámico es la siguiente:

=OFFSET(Sheet_Name!$A$1, 0, 0, COUNTA(Sheet_Name!$A:$A), 1)

En el centro de esta fórmula, se utiliza la función CONTAR para obtener el número de celdas que no están en blanco en la columna de destino. Ese número va al argumento de altura de OFFSET y le indica cuántas filas devolver.

Aparte de eso, es una fórmula de compensación normal, donde:

  • Referencia es el punto inicial desde el cual basa el desplazamiento, por ejemplo Hoja1!$A$1.
  • Filas y Cols Ambos son 0 porque no hay columnas ni filas para compensar.
  • Ancho es 1 columna.

Nota. Si está creando un rango dinámico en la hoja actual, no es necesario incluir el nombre de la hoja en las referencias, Excel lo hará automáticamente al crear el rango nombrado. De lo contrario, asegúrese de incluir el nombre de la hoja seguido del signo de exclamación como en este ejemplo de fórmula.

Una vez que haya creado un rango dinámico con nombre con la fórmula COMPENSACIÓN anterior, puede usar la Validación de datos para crear un menú desplegable dinámico que se actualizará automáticamente tan pronto como agregue o elimine elementos de la lista de origen.
Función OFFSET de Excel: ejemplos y usos de fórmulas

Para obtener una guía detallada paso a paso sobre cómo crear listas desplegables en Excel, consulte los siguientes tutoriales:

DESPLAZAMIENTO Y BUSCARV de Excel

Como todo el mundo sabe, las búsquedas verticales y horizontales simples se realizan con la función BUSCARV o BUSCARH, respectivamente. Sin embargo, estas funciones tienen demasiadas limitaciones y a menudo tropiezan con fórmulas de búsqueda más potentes y complejas. Entonces, para realizar búsquedas más sofisticadas en sus tablas de Excel, debe buscar alternativas como ÍNDICE, COINCIDIR y COMPENSACIÓN.

Ejemplo 1. Fórmula de DESPLAZAMIENTO para una búsqueda V izquierda en Excel

Una de las limitaciones más notorias de la función BUSCARV es la imposibilidad de mirar a la izquierda, lo que significa que BUSCARV solo puede devolver un valor a la derecha de la columna de búsqueda.

En nuestra tabla de búsqueda de muestra, hay dos columnas: nombres de meses (columna A) y bonificaciones (columna B). Si desea obtener un bono para un mes determinado, esta sencilla fórmula BUSCARV funcionará sin problemas:

=VLOOKUP(B1, A5:B11, 2, FALSE)

Sin embargo, tan pronto como intercambie las columnas en la tabla de búsqueda, esto dará como resultado inmediatamente el error #N/A:
Función OFFSET de Excel: ejemplos y usos de fórmulas

Para manejar una búsqueda en el lado izquierdo, necesita una función más versátil a la que realmente no le importe dónde reside la columna de retorno. Una de las posibles soluciones es utilizar una combinación de funciones ÍNDICE y COINCIDIR. Otro enfoque es utilizar COMPENSACIÓN, COINCIDIR y FILAS:

COMPENSAR(tabla de búsquedaFÓSFORO(valor de búsquedaCOMPENSAR(tabla de búsqueda0, búsqueda_col_offsetFILAS(tabla de búsqueda), 1) ,0) -1, retorno_col_offset1, 1)

Dónde:

  • búsqueda_col_offset – es el número de columnas que se van a mover desde el punto inicial a la columna de búsqueda.
  • Return_col_offset – es el número de columnas que se van a mover desde el punto inicial hasta la columna de retorno.

En nuestro ejemplo, la tabla de búsqueda es A5:B9 y el valor de búsqueda está en la celda B1, el desplazamiento de la columna de búsqueda es 1 (debido a que estamos buscando el valor de búsqueda en la segunda columna (B), necesitamos mover 1 columna a a la derecha desde el principio de la tabla), el desplazamiento de la columna de retorno es 0 porque estamos devolviendo valores de la primera columna (A):

=OFFSET(A5:B9, MATCH(B1, OFFSET(A5:B9, 0, 1, ROWS(A5:B9), 1) ,0) -1, 0, 1, 1)

Sé que la fórmula parece un poco torpe, pero funciona 🙂
Función OFFSET de Excel: ejemplos y usos de fórmulas

Ejemplo 2. Cómo hacer una búsqueda superior en Excel

Como es el caso de BUSCARV que no puede mirar hacia la izquierda, su contraparte horizontal, la función BUSCARH, no puede mirar hacia arriba para devolver un valor.

Si necesita escanear una fila superior en busca de coincidencias, la fórmula COMPENSACIÓN DE COINCIDENCIA puede ayudar nuevamente, pero esta vez tendrá que mejorarla con la función COLUMNAS, así:

COMPENSAR(tabla de búsqueda, return_row_offsetFÓSFORO(valor de búsquedaCOMPENSAR(tabla de búsqueda, lookup_row_offset0, 1, COLUMNAS(tabla de búsqueda)), 0) -1, 1, 1)

Dónde:

  • compensación_fila_búsqueda – el número de filas para pasar desde el punto inicial a la fila de búsqueda.
  • Desplazamiento_fila_retorno – el número de filas para pasar desde el punto inicial a la fila de retorno.

Suponiendo que la tabla de búsqueda es B4:F5 y el valor de búsqueda está en la celda B1, la fórmula es la siguiente:

=OFFSET(B4:F5, 0, MATCH(B1, OFFSET(B4:F5, 1, 0, 1, COLUMNS(B4:F5)), 0) -1, 1, 1)

En nuestro caso, el desplazamiento de la fila de búsqueda es 1 porque nuestro rango de búsqueda está 1 fila hacia abajo desde el punto inicial, el desplazamiento de la fila de retorno es 0 porque devolvemos coincidencias de la primera fila de la tabla.
Función OFFSET de Excel: ejemplos y usos de fórmulas

Ejemplo 3. Búsqueda bidireccional (por valores de columna y fila)

La búsqueda bidireccional devuelve un valor basado en coincidencias tanto en las filas como en las columnas. Y puede utilizar la siguiente fórmula matricial de búsqueda doble para encontrar un valor en la intersección de una determinada fila y columna:

=DESPLAZAMIENTO(tabla de búsquedaFÓSFORO(valor de búsqueda de filaCOMPENSAR(tabla de búsqueda0, 0, FILAS(tabla de búsqueda), 1), 0) -1, COINCIDIR (valor de búsqueda de columnaCOMPENSAR(tabla de búsqueda0, 0, 1, COLUMNAS(tabla de búsqueda)), 0) -1)

Dado que:

  • La tabla de búsqueda es A5:G9
  • El valor a coincidir en las filas está en B2.
  • El valor a coincidir en las columnas está en B1.

Obtiene la siguiente fórmula de búsqueda bidimensional:

=OFFSET(A5:G9, MATCH(B2, OFFSET(A5:G9, 0, 0, ROWS(A5:G9), 1), 0)-1, MATCH(B1, OFFSET(A5:G9, 0, 0, 1, COLUMNS(A5:G9)), 0) -1)

No es lo más fácil de recordar, ¿verdad? Además, esta es una fórmula matricial, así que no olvides presionar Ctrl + Mayús + Intro para ingresarlo correctamente.
Función OFFSET de Excel: ejemplos y usos de fórmulas

Por supuesto, esta larga fórmula OFFSET no es la única forma posible de realizar una doble búsqueda en Excel. Puede obtener el mismo resultado utilizando las funciones BUSCARV Y COINCIDIR, SUMPRODUCTO o ÍNDICE Y COINCIDIR. Incluso existe una forma sin fórmulas: emplear rangos con nombre y el operador de intersección (espacio). El siguiente tutorial explica todas las soluciones alternativas con todo detalle: Cómo realizar una búsqueda bidireccional en Excel.

Error 403 The request cannot be completed because you have exceeded your quota. : quotaExceeded

Función OFFSET de Excel: ejemplos y usos de fórmulas

Si eres usuario frecuente de Microsoft Excel, es posible que hayas oído hablar de la función OFFSET. Esta función es una de las muchas herramientas poderosas que ofrece Excel para realizar cálculos y análisis de datos de manera eficiente. En este artículo, exploraremos en profundidad la función OFFSET y mostraremos ejemplos prácticos de su uso.

¿Qué es la función OFFSET de Excel?

La función OFFSET es una fórmula avanzada en Excel que permite desplazarse o “deslizar” en una hoja de cálculo para seleccionar un rango de celdas específico. La sintaxis básica de la función OFFSET es:

=OFFSET(rango_inicial, desplazamiento_filas, desplazamiento_columnas, [alto], [ancho])

La parte más importante de esta fórmula es el rango inicial desde donde comienza a desplazarse, seguido del número de filas y columnas a desplazarse. También se pueden especificar el alto y el ancho del rango seleccionado, pero son opcionales.

Usos comunes de la función OFFSET

  1. Creación de gráficos dinámicos: La función OFFSET es especialmente útil al crear gráficos que necesitan actualizarse automáticamente a medida que se agregan nuevos datos a la hoja de cálculo. Puedes utilizar OFFSET para definir el rango de datos que se utilizará en el gráfico y asegurarte de que se ajuste automáticamente a medida que se agreguen más filas o columnas.
  2. Segmentación de datos: Si tienes una gran cantidad de datos en una hoja de cálculo y deseas segmentarlos o analizar solo una parte específica, la función OFFSET te permite seleccionar fácilmente un rango de celdas basado en ciertos criterios. Por ejemplo, puedes usar OFFSET para seleccionar solo los datos de un mes específico o de un departamento en particular.
  3. Referencias dinámicas: La función OFFSET también es útil cuando necesitas crear referencias dinámicas a celdas o rangos en una fórmula. Puedes utilizar OFFSET para desplazarte automáticamente a través de los datos y asegurarte de que tu fórmula siempre se refiera a la posición correcta.

Estos son solo algunos ejemplos de los muchos usos prácticos de la función OFFSET en Excel. A medida que te familiarices más con esta fórmula, encontrarás formas creativas de utilizarla en tus propias hojas de cálculo.

Si deseas aprender más sobre la función OFFSET de Excel, te recomendamos consultar los siguientes recursos:

Con estos recursos adicionales, podrás aprovechar al máximo la función OFFSET y llevar tus habilidades de Excel al siguiente nivel.

Deja un comentario