En este artículo, aprenderemos cómo usar la función OFFSET en Excel.
Que es Función OFFSET?
La función de compensación es una función de recuperación de información. Si tiene una tabla enorme que contiene ciertos datos y desea recuperar datos de ella, la mejor manera de hacerlo es insertar una fórmula de compensación para la tabla en esa hoja de trabajo u otra hoja en el mismo libro de trabajo. Por lo tanto, una vez que haya creado la tabla de recuperación de compensación, simplemente debe ingresar los datos, por ejemplo, "mes" y obtendrá las "ventas" o "ingresos" de ese mes de esa tabla.
Revisar la tabla es otra opción, pero ¿consideraría la opción si tiene hojas y hojas de datos que contienen una tabla de datos de 1000 columnas? Aquí es donde el desplazamiento se combina con otras funciones.
El concepto de tabla dinámica proviene del desplazamiento. Una tabla dinámica es una tabla general y de esa combinación específica de datos y gráficos se obtiene cuando se requiere. También se pueden crear tablas dinámicas de la misma manera. Para estas tablas, tendrá que crear la fórmula de compensación de Excel
Sintaxis de OFFSET
| = DESPLAZAMIENTO (referencia, filas, columnas, [alto], [ancho]) |
Filas - le pide a Excel que mueva el número de filas para obtener la información. En este caso, tiene que moverse por toda la mesa y dejar un espacio en blanco indicado por una coma (,). De lo contrario, para mover una columna hacia adelante, dé un valor como 1 y para mover una columna antes, dé un valor -1.
Columnas - esto es para llevar la función al número de columnas. El sistema de valores es el mismo que para la fila.
Altura - la altura de la mesa, en este caso A11.
Ancho - ancho de la mesa, en este caso D11.
Después de configurar la función de compensación, es importante ejecutar una prueba para eliminar cualquier error.
Tomemos una tabla que contenga Nombre, ID de correo electrónico, fecha de nacimiento y edad. La tabla comienza en la celda A1, que contiene el encabezado "Nombre". Los datos se ejecutan para, por ejemplo, A11. Y las filas van hasta D1. Toda la tabla corre A1: D11. Desea un sistema de recuperación que le proporcione el nombre cuando ingresa la identificación de correo electrónico, o la identificación de correo electrónico cuando ingresa el nombre junto con otros detalles. Creas una fórmula satisfaciendo los 5 argumentos.
El punto de referencia es la celda desde la que debe comenzar la búsqueda. En este caso debería ser A2. Ese es el estándar general.
Ejemplo:
Por ejemplo, usted da la referencia de B4 y el desplazamiento de la fila 0, y el desplazamiento de la columna 1, luego se devolverá el valor de C4. ¿Confuso? Veamos algunos ejemplos más. El último ejemplo de la función OFFSET indica cómo SUMA dinámicamente.
Función OFFSET para obtener valor de derecha e izquierda de una celda
Tenemos esta mesa.
Ahora, si quiero obtener el valor de 2 celdas directamente a B2 (lo que significa D2). Escribiré esta fórmula OFFSET:
| = DESPLAZAMIENTO (B2,0,2) |
La función DESPLAZAMIENTO moverá 2 celdas a la derecha de la celda B4 y devolverá su valor. Ver imagen a continuación
Si quiero obtener el valor de 1 celda a la izquierda a B2 (lo que significa A2). Escribiré esta fórmula OFFSET:
| = DESPLAZAMIENTO (B2,0, -1) |
El signo menos (-) indica el desplazamiento para moverse en reversa.
Función OFFSET para obtener valor desde abajo y arriba de una celda
Entonces, nuevamente en la tabla anterior, si quiero obtener el valor de 2 celdas abajo a B3 (lo que significa B5). Escribiré esta fórmula OFFSET:
| = DESPLAZAMIENTO (B2,2,0) |
Si quiero obtener el valor de 1 celda a la izquierda a B2 (lo que significa A2). Escribiré esta fórmula OFFSET:
| = DESPLAZAMIENTO (B2, -2,0) |
- Consejo profesional: Trate un OFFSET como un puntero de gráfico donde la referencia es el origen y la fila y la columna son los ejes xey.
Función OFFSET para sumar rango dinámicamente
Veamos este ejemplo.
En la tabla anterior Total fila al final de la tabla. Con el tiempo, agregará filas a esta tabla. Luego, deberá cambiar el rango de suma en la fórmula. Aquí podemos tomar la ayuda de la función OFFSET para sumar dinámicamente. Actualmente en la celda C11 tenemos = SUMA (C2: C10). Reemplace esto con la siguiente fórmula.
| = SUMA (C2: DESPLAZAMIENTO (C11, -1,0)) |
Esta fórmula solo toma la primera fila de datos y luego suma el rango por encima de la fila total.
Desplazamiento para obtener matriz
La función OFFSET tiene dos argumentos opcionales, [altura] y [ancho]. No los tome por sentado. Cuando se proporciona la función OFFSET con argumentos [altura] y [ancho], devuelve una matriz bidimensional. Si solo se pasa uno de ellos como argumento, devuelve una matriz dimensional de valores.
Si escribe esta fórmula en cualquier celda:
| = SUMA (DESPLAZAMIENTO (C1,1,0,9,3)) |
Sumará valores en el rango desde C2 hasta 9 filas a continuación y 3 columnas a la derecha.
DESPLAZAMIENTO (C1,1,0,9,3): Esto se traducirá en {8,8,7; 6,1,2; 8,5,8; 5,1,5; 8,3,5; 8,3,9; 8,9,2; 3,5 , 4; 6,6,5}.
Entonces, en conclusión, el desplazamiento es una función muy útil de Excel que puede ayudarlo a resolver muchos hilos estrangulados. Déjeme saber cómo usa la función OFFSET en su fórmula y dónde le ayudó más.
La función de búsqueda es la misma que la de compensación, pero utiliza funciones como Match, Counta e IF para trabajar en una tabla dinámica.
El uso de offset en Excel requiere habilidad y buen conocimiento de las funciones de Excels y cómo funcionan juntas. Llevarlo a cabo sin ningún error es el mayor obstáculo.
Aquí están todas las notas de observación sobre el uso de la fórmula.
Notas:
- Esta fórmula funciona tanto con texto como con números.
- Hay cinco argumentos del desplazamiento que deben satisfacerse para obtener una información libre de errores. Si escribe una fórmula incorrecta, se produce un error de referencia.
Espero que haya entendido Qué es la función de compensación de Excel y cómo usarla en Excel. Explore más artículos sobre la búsqueda de Excel y los valores de coincidencia usando fórmulas aquí. Si te gustaron nuestros blogs, compártelo con tus amigos en Facebook. Y también puedes seguirnos en Twitter y Facebook. Nos encantaría saber de usted, háganos saber cómo podemos mejorar, complementar o innovar nuestro trabajo y hacerlo mejor para usted. Escríbanos al sitio de correo electrónico.
Función VLOOKUP para calcular la calificación en Excel : Calcular las calificaciones IF y IFS no son las únicas funciones que puede utilizar. VLOOKUP es más eficiente y dinámico para tales cálculos condicionales. Para calcular las calificaciones usando VLOOKUP podemos usar esta fórmula.
17 cosas sobre Excel VLOOKUP : VLOOKUP se usa más comúnmente para recuperar valores coincidentes, pero VLOOKUP puede hacer mucho más que esto. Aquí hay 17 cosas sobre VLOOKUP que debe saber para usar de manera efectiva.
BUSCAR el primer texto de una lista en Excel : La función BUSCARV funciona bien con caracteres comodín. Podemos usar esto para extraer el primer valor de texto de una lista dada en Excel. Aquí está la fórmula genérica.
BÚSQUEDA fecha con último valor en la lista : Para recuperar la fecha que contiene el último valor usamos la función BUSCAR. Esta función busca la celda que contiene el último valor en un vector y luego usa esa referencia para devolver la fecha.
Cómo recuperar el último precio en Excel : Es común actualizar los precios en cualquier negocio y es obligatorio utilizar los precios más recientes para cualquier compra o venta. Para recuperar el último precio de una lista en Excel usamos la función BUSCAR. La función BUSCAR obtiene el último precio.
Articulos populares:
50 atajos de Excel para aumentar su productividad : Acelere su tarea. Estos 50 accesos directos le permitirán trabajar aún más rápido en Excel.
Cómo usar la función SI en Excel : La declaración IF en Excel verifica la condición y devuelve un valor específico si la condición es VERDADERA o devuelve otro valor específico si es FALSO.
Cómo utilizar la función BUSCARV en Excel : Esta es una de las funciones más utilizadas y populares de Excel que se utiliza para buscar valores de diferentes rangos y hojas.
Cómo usar la función CONTAR.SI en Excel : Cuente valores con condiciones usando esta asombrosa función. No necesita filtrar sus datos para contar valores específicos. La función Countif es esencial para preparar su tablero.
Cómo utilizar la función SUMIF en Excel : Esta es otra función esencial del tablero. Esto le ayuda a resumir valores en condiciones específicas.