Fórmula de Excel OFFSET (Tabla de contenido)

  • ¿Qué es la fórmula OFFSET en Excel?
  • ¿Cómo usar la fórmula OFFSET en Excel?

¿Qué es la fórmula OFFSET en Excel?

La fórmula OFFSET en Excel proporciona una celda o un rango de celdas rectangulares dinámicas como una salida que es un subconjunto del rango total dado con un número especificado de filas, columnas, alto y ancho.

Sintaxis de la fórmula OFFSET en Excel

Explicación de la función OFFSET en Excel:

  • referencia: El punto de partida para OFFSET o podemos decir un rango de celdas que puede considerarse como la base para OFFSET.
  • filas : el número de filas para DESPLAZAR por debajo o por encima de la referencia base.
  • cols: el número de columnas para DESPLAZAR a la derecha o izquierda de la referencia base.
  • height: número de filas en la referencia devuelta.
  • ancho: Número de columnas en la referencia devuelta.

La altura y el ancho son los parámetros opcionales para esta función y se pueden usar para determinar el tamaño de la referencia creada.

¿Cómo usar la fórmula OFFSET en Excel?

Ahora aprenderemos cómo escribir una fórmula de desplazamiento para obtener el valor de búsqueda. Comprendamos la fórmula de desplazamiento con algunos ejemplos en Excel.

Puede descargar esta plantilla de Excel de OFFSET aquí - Plantilla de Excel de OFFSET

Fórmula de Excel OFFSET - Ejemplo # 1

Supongamos que tenemos datos como se indica a continuación.

  • En la celda E2, ingrese la siguiente fórmula:

= DESPLAZAMIENTO (A1, 11, 1)

  • Una vez que haya terminado con la fórmula, presione Entrar y vea la salida.

Lo que hizo esta fórmula es que tomó la referencia de la celda A1 (año) y se movió hacia abajo 11 filas después de A1 (es decir, la fila no. 12) y luego obtuvo el valor que es la coordenada de la 11ma fila después de A1 y la primera columna ( columna A = 0 columna B = 1 en este caso. Como sabemos, podemos ir tanto a la derecha como a la izquierda). Que es $ 141.27 millones.

Fórmula de Excel OFFSET - Ejemplo # 2

Ahora veremos otro ejemplo de una celda de referencia para un año específico. Ponga la siguiente fórmula en la celda E3:

= DESPLAZAMIENTO (A1, 11, 0)

  • Una vez que haya terminado con la fórmula, presione Entrar y vea la salida.

Si ha visto la fórmula, es la misma que la anterior, el único cambio está en la referencia de columna. La referencia de columna 0 (Cero) significa que el valor se capturará de la columna A para la 11ª fila después de A1 (que es 2010).

Fórmula de Excel OFFSET - Ejemplo # 3

En este ejemplo, calcularemos la suma de las últimas N filas usando la función de compensación de Excel. La fórmula general para resumir los últimos N números es:

= SUMA (DESPLAZAMIENTO (A1, CONTEO (A: A), 0, -N))

Para resumir las últimas N observaciones de sus datos, puede usar la función SUMA junto con las funciones DESPLAZAMIENTO y CONTEO juntas. En este ejemplo, resumiremos los últimos 5 puntos de datos. Tenga en cuenta que cuando decimos N, es una generalización de cualquier valor entero. Por lo tanto, puede calcular los últimos 3, los últimos 4, los últimos 5, etc., según su conjunto de datos y sus requisitos.

Hagámoslo para nuestro conjunto de datos:

  • Ingrese la siguiente fórmula en la celda E2 del archivo de Excel.

= SUMA (DESPLAZAMIENTO (B1, CONTEO (B: B), 0, -5))

  • Haga clic en Entrar para ver la salida.

Como ya hemos visto, la función OFFSET de Excel genera rangos dinámicos rectangulares cuando se le da una referencia inicial.

  • En este caso, la referencia inicial se proporciona en términos de B1 (como primer argumento para la función OFFSET).
  • Utilizamos la función COUNT porque necesitamos la suma de observaciones del último valor. La función COUNT ayuda a OFFSET a determinar cuántas observaciones (filas) hay en la columna B. Cuenta todos los valores numéricos en la columna B (19 es el recuento).
  • Con un recuento de 19, la fórmula OFFSET se convierte en:

= DESPLAZAMIENTO (B1, 19, 0, -5)

  • Esta fórmula comienza la compensación en B1 con 19 filas y la misma columna (0). Luego, el parámetro de altura -5 ayuda a esta función a extender el rango hacia atrás en 5 filas (ver el signo negativo asociado con 5).
  • El rango dinámico de las últimas cinco observaciones se proporciona como una entrada para la función de suma. Lo que rinde al sumar los de la columna B.

Fórmula de Excel OFFSET - Ejemplo # 4

En este ejemplo, calcularemos las ventas medias de los últimos 3 años. La función OFFSET puede usarse como argumento para calcular lo mismo.

  • Ponga la siguiente fórmula en la celda E2:

= MEDIANA (DESPLAZAMIENTO (B1, CONTEO (B: B), 0, -3))

  • Presione Entrar para ver la salida.

Esta fórmula funciona en líneas similares a la anterior.

  • OFFSET Crea un rango dinámico de las últimas tres observaciones de la columna B con la ayuda de la función COUNT (= OFFSET (A1, 19, 0, -3)).
  • Esto se puede dar como salida a la función MEDIANA. Lo que luego clasifica las observaciones en orden ascendente o descendente y luego selecciona el valor más medio como se ve en la salida ($ 212.07).

Fórmula de Excel OFFSET - Ejemplo # 5

Aquí calcularemos el año medio usando la función MEDIANO y DESPLAZAMIENTO.

  • Use la siguiente fórmula para lo mismo en la celda E3:

= MEDIANA (DESPLAZAMIENTO (A1, CONTEO (A: A), 0, -3))

  • Haga clic en Entrar para ver la salida.

Lo mismo ha sucedido aquí. La fórmula tomó un rango dinámico creado por la función OFFSET con la ayuda de COUNT. Y lo usó como argumento para una función MEDIANA que luego ordenó los valores del año en orden ascendente o descendente y luego dio la salida como 2017.

El uso de la fórmula OFFSET está en la creación de los rangos dinámicos. Siempre es mejor trabajar en rangos dinámicos en lugar de estáticos. Como es posible que tenga datos que se agregan de vez en cuando. Por ejemplo, Employee Master. Sus datos maestros de empleados siempre aumentarán de vez en cuando, ya que debe haber algunos nuevos empleados que se unan. Cada vez que usa la misma fórmula no tiene ningún sentido. En lugar de eso, cree un rango que sea dinámico usando la función OFFSET y vea la magia. Ciertamente te ahorrará tiempo.

Cosas para recordar

  • La función OFFSET solo devuelve una referencia a un rango particular de celdas en Excel. No mueve las celdas de sus posiciones originales.
  • Por defecto, esta función funciona en la parte superior (en términos de filas) y en el lado derecho (en términos de columnas). Sin embargo, puede proporcionarse con argumentos negativos para revertir la funcionalidad predeterminada. El argumento de fila igual a negativo ayuda a compensar los datos de abajo hacia arriba y el argumento de col negativo ayuda a compensar los datos a la izquierda.
  • La fórmula OFFSET puede ralentizar la funcionalidad de Excel ya que es una fórmula que se recalcula cada vez que hay un cambio en la hoja de trabajo.
  • Se generará el error #REF si su rango está fuera del rango de su libro de trabajo.
  • Cualquier otra función que espere una referencia puede usar la función OFFSET dentro de ella.

Artículos recomendados

Esta es una guía para la fórmula OFFSET en Excel. Aquí discutimos cómo usar la función OFFSET en Excel junto con ejemplos prácticos y una plantilla de Excel descargable. También puede consultar nuestros otros artículos sugeridos:

  1. ¿Cómo ajustar el texto en Excel?
  2. Guía simple para ordenar en Excel
  3. Función de búsqueda en Excel
  4. ¿Cómo usar la función FIND en Excel?

Categoría:

Consejos de Excel