Descarga el libro de trabajo de ejemplo
Este tutorial demuestra cómo utilizar el Función de compensación de Excel en Excel para crear un desplazamiento de referencia a partir de una celda inicial.
Descripción general de la función OFFSET
La función OFFSET comienza con una referencia de celda definida y devuelve una referencia de celda con un número específico de filas y columnas desplazadas de la referencia original. Las referencias pueden ser una celda o un rango de celdas. Offset también le permite cambiar el tamaño de la referencia a un número determinado de filas / columnas.
(Observe cómo aparecen las entradas de la fórmula)
Sintaxis y entradas de la función IFERROR:
| 1 | = DESPLAZAMIENTO (referencia, filas, columnas, alto, ancho) |
referencia - La referencia de celda inicial de la que desea compensar.
filas - El número de filas a compensar.
cols - El número de columnas a compensar.
altura - OPCIONAL: Ajusta el número de filas en la referencia.
ancho - OPCIONAL: Ajusta el número de columnas en la referencia.
¿Qué es la función OFFSET?
La función OFFSET es una de las funciones de hoja de cálculo más poderosas, ya que puede ser bastante versátil en lo que crea. Le da al usuario la capacidad de definir una celda o rango en una variedad de posiciones y tamaños.
PRECAUCIÓN: La función OFFSET es una de las funciones volátiles. La mayoría de las veces, cuando está trabajando en su hoja de cálculo, la computadora solo volverá a calcular una fórmula si las entradas han cambiado sus valores. Sin embargo, una función volátil recalcula cada vez que haga un cambio en cualquier celda. Se debe tener precaución para asegurarse de no causar un gran tiempo de recálculo debido al uso excesivo de la función volátil o al tener muchas celdas dependientes del resultado de una función volátil.
Ejemplos de filas básicas
En cada uso de la función OFFSET, debe proporcionar un punto de partida o un ancla. Veamos esta tabla para ayudar a comprender esto:
Usaremos "Bob" en la celda B3 como nuestro punto de anclaje. Si quisiéramos tomar el valor justo debajo de (Charlie), diríamos que queremos cambiar la fila en 1. Nuestra fórmula se vería así
| 1 | = DESPLAZAMIENTO (B3, 1) |
Si quisiéramos subir de nivel, sería un cambio negativo. Puede pensar en esto como si el número de fila disminuye, por lo que debemos restar. Por lo tanto, para obtener el valor anterior (Adam), escribiríamos
| 1 | = DESPLAZAMIENTO (B2, -1) |
Ejemplos de columnas básicas
Continuando con la idea del ejemplo anterior, agregaremos otra columna a nuestra tabla.
Si quisiéramos llamar al maestro para Bob, podríamos usar la fórmula
| 1 | = DESPLAZAMIENTO (B2, 0, 1) |
En este caso, dijimos que queremos compensar cero filas (también conocido como permanecer en la misma fila) pero queremos compensar 1 columna. Para las columnas, un número positivo significa desplazarse hacia la derecha y los números negativos significa desplazarse hacia la izquierda.
OFFSET y PARTIDO
Suponga que tiene varias columnas de datos y desea brindarle al usuario la posibilidad de elegir de qué columna obtener los resultados. Puede usar la función INDICE, o puede usar OFFSET. Dado que COINCIDIR devolverá la posición relativa de un valor, tendremos que asegurarnos de que el punto de anclaje esté a la izquierda de nuestro primer valor posible. Considere el siguiente diseño:
En B2, escribiremos esta fórmula:
| 1 | = COMPENSACIÓN (B2, 0, COINCIDIR (A2, $ C $ 1: $ F $ 1, 0)) |
El PARTIDO se verá "Feb" en el rango C1: F1 y lo encontrará en el 2Dakota del Norte celda. El OFFSET luego desplazará 1 columna a la derecha de B2 y tomará el valor deseado de 9. Tenga en cuenta que OFFSET no tiene problemas para usar la misma celda que contiene la fórmula como punto de anclaje.
NOTA: Esta técnica podría usarse como reemplazo de BUSCARV o BUSCARH cuando desee devolver un valor desde la izquierda / arriba de su rango de búsqueda. Esto se debe a que OFFSET puede realizar compensaciones negativas.
OFFSET para obtener un rango
Puedes usar el 4th y 5th argumentos en la función DESPLAZAMIENTO para devolver un rango en lugar de una sola celda. Suponga que desea sumar 3 columnas en esta tabla.
| 1 | = PROMEDIO (DESPLAZAMIENTO (A1, COINCIDIR (F2, A2: A5,0), 1,1,3)) |
En F2, seleccionamos el nombre de un estudiante para el que queremos obtener sus puntajes promedio en las pruebas. Para hacer esto, usaremos la fórmula
| 1 | = PROMEDIO (DESPLAZAMIENTO (A1, COINCIDIR (F2, A2: A5,0), 1,1,3)) |
El MATCH buscará en la columna A nuestro nombre y devolverá la posición relativa, que es 3 en nuestro ejemplo. Veamos cómo se evaluará esto. Primero, el OFFSET se irá abajo 3 filas desde A1 y 1 columna hasta la Derecha desde A1. Esto nos coloca en la celda B3.
| 1 | = PROMEDIO (COMPENSACIÓN (A1, 3, 1, 1, 3)) |
A continuación, cambiaremos el tamaño del rango. El nuevo rango tendrá B3 como celda superior izquierda. Tendrá 1 fila de alto y 3 columnas de alto, lo que nos dará el rango B4: D4.
| 1 | = PROMEDIO (DESPLAZAMIENTO (A1,3, 1, 1, 3)) |
Tenga en cuenta que, si bien puede colocar legítimamente valores negativos en los argumentos de compensación, solo puede usar valores no negativos en los argumentos de tamaño.
Al final, nuestra función PROMEDIO ve:
| 1 | = PROMEDIO (B4: D4) |
Por lo tanto, obtenemos nuestra solución de 86.67
OFFSET con SUM dinámica
Debido a que OFFSET se usa para buscar una referencia, en lugar de apuntar directamente a la celda, es más útil cuando se trata de datos que tienen filas agregadas o eliminadas. Considere la siguiente tabla con un total en la parte inferior
| 1 | = SUMA (B2: B4) |
Si hubiéramos usado una fórmula SUM básica aquí de "= SUM (B2: B4)" y luego insertamos una nueva fila para agregar un registro para Bill, tendríamos la respuesta incorrecta
En su lugar, pensemos en cómo resolver esto desde el punto de vista de Total. Realmente queremos tomar todo, desde la celda B2 hasta la celda justo por encima de nuestro total. La forma en que podemos escribir esto en una fórmula es hacer un desplazamiento de fila de -1. Por lo tanto, usamos esto como fórmula para nuestro total en la celda B5:
| 1 | = SUMA (B2: DESPLAZAMIENTO (B5, -1,0)) |
Esta fórmula hace lo que acabamos de describir: comience en B2 y vaya a 1 celda por encima de nuestra celda total. Puede ver cómo después de agregar los datos de Bill, nuestro total se actualiza correctamente.
DESPLAZAMIENTO para obtener los últimos N elementos
Supongamos que está registrando ventas mensuales, pero desea poder ver los últimos 3 meses. En lugar de tener que actualizar manualmente sus fórmulas para seguir ajustando a medida que se agregan nuevos datos, puede usar la función OFFSET con COUNT.
Ya mostramos cómo puede usar OFFSET para tomar un rango de celdas. Para determinar cuántas celdas necesitamos cambiar, usaremos COUNT para encontrar cuántas números están en la columna B. Veamos nuestra tabla de muestra.
| 1 | = SUMA (COMPENSACIÓN ($ B $ 1, CUENTA (B: B) - $ E $ 1 + 1,0, $ E $ 1,1)) |
Si comenzamos en B1 y desplazamos 4 filas (el recuento de números en la columna B), terminaríamos en la parte inferior de nuestro rango, B5. Sin embargo, dado que OFFSET no puede cambiar el tamaño con un valor negativo, necesitamos hacer algunos ajustes para terminar en B3. La ecuación general para esto será hacer
| 1 | CONTAR (…) - N + 1 |
Tomamos el recuento de toda la columna, restamos todas las que queramos devolver (ya que cambiaremos el tamaño para tomarlas) y luego sumamos 1 (ya que esencialmente estamos comenzando nuestro desplazamiento en la posición cero).
Aquí puede ver que hemos configurado un rango para obtener la suma, el promedio y el máximo de los últimos N meses. En E1, ingresamos el valor de 3. En E2, nuestra fórmula es
| 1 | = SUMA (COMPENSACIÓN ($ B $ 1, CUENTA (B: B) - $ E $ 1 + 1,0, $ E $ 1,1)) |
La sección resaltada es nuestra ecuación general que acabamos de discutir. No necesitamos compensar ninguna columna. Luego, cambiaremos el tamaño del rango para que tenga 3 celdas de alto (determinado por el valor en E1) y 1 columna de ancho. Nuestro SUM luego toma este rango y nos da el resultado de $ 1.850. También hemos demostrado que puede calcular el promedio de max de este mismo rango simplemente cambiando la función externa de SUM a lo que requiera la situación.
Listas de validación dinámica OFFSET
Usando la técnica que se muestra en el último ejemplo, también podemos construir rangos con nombre que podrían usarse en la validación de datos o gráficos. Esto puede ser útil cuando desea configurar una hoja de cálculo pero espera que nuestras listas / datos cambien de tamaño. Supongamos que nuestra tienda está empezando a vender fruta y actualmente tenemos 3 opciones.
Para crear un menú desplegable de Validación de datos que podamos usar en otros lugares, definiremos el rango llamado MyFruit como
| 1 | = $ A $ 2: COMPENSACIÓN ($ A $ 1, COUNTA ($ A: $ A) -1, 0) |
En lugar de COUNT, estamos usando COUNTA ya que estamos tratando con valores de texto. Sin embargo, debido a esto, nuestro COUNTA será uno más alto, ya que contará la celda del encabezado en A1 y dará un valor de 4. Sin embargo, si lo compensamos con 4 filas, terminaríamos en la celda A5, que está en blanco. Para ajustar esto, restamos el 1.
Ahora que tenemos nuestra configuración de rango con nombre, podemos configurar alguna validación de datos en la celda C4 usando un tipo de lista, con fuente:
| 1 | = MyFruit |
Tenga en cuenta que el menú desplegable solo muestra nuestros tres elementos actuales. Si luego agregamos más elementos a nuestra lista y volvemos al menú desplegable, la lista muestra todos los elementos nuevos sin que tengamos que cambiar ninguna de las fórmulas.
Precauciones con el uso de OFFSET
Como se mencionó al principio de este artículo, OFFSET es una función volátil. No notará esto si lo está usando en solo unas pocas celdas, pero si comienza a involucrarlo en cientos de cálculos y rápidamente notará que su computadora gasta una cantidad notable de tiempo recalculando cada vez que realiza algún cambio. .
Además, debido a que OFFSET no nombra directamente las celdas que está mirando, es más difícil para otros usuarios venir más tarde y cambiar sus fórmulas si es necesario.
En su lugar, sería recomendable utilizar tablas (introducidas en Office 2007) que permiten referencias estructurales. Esto ayudó a los usuarios a poder dar una única referencia que se ajustaba automáticamente en tamaño a medida que se agregaban o eliminaban nuevos datos.
La otra opción para usar en lugar de OFFSET es la poderosa función INDICE. INDEX le permite construir todos los rangos dinámicos que vimos en este artículo sin el problema de ser una función volátil.
Notas adicionales
Utilice la función DESPLAZAMIENTO para devolver un valor de celda (o un rango de celdas) compensando un número determinado de filas y columnas de una referencia inicial. Cuando se busca solo una celda, las fórmulas de DESPLAZAMIENTO logran el mismo propósito que las fórmulas de ÍNDICE, utilizando una técnica ligeramente diferente. El verdadero poder de la función OFFSET radica en su capacidad para seleccionar un rango de celdas para usar en otra fórmula.
Cuando se utiliza la función DESPLAZAMIENTO, se define una celda de inicio inicial o un rango de celdas. Luego, indica el número de filas y columnas a compensar de esa celda inicial. También puede cambiar el tamaño del rango; sumar o restar filas o columnas.
Regrese a la lista de todas las funciones en Excel
OFFSET en Google Sheets
La función OFFSET funciona exactamente igual en Google Sheets que en Excel:
