Fórmula desplazamiento en Microsoft Excel
VLOOKUP, HLOOKUP y INDEX-MATCH son formas famosas y comunes de buscar valores en una tabla de Excel. Pero estas no son las únicas formas de buscar valores en Excel. En este artículo, aprenderemos cómo usar la función DESPLAZAMIENTO junto con la función COINCIDIR en Excel. Sí, lo leyó bien, usaremos la infame función OFFSET de Excel para buscar un cierto valor en una tabla de Excel.
Fórmula genérica
|
= |
Lea esto con atención:
StartCell: esta es la celda inicial de la tabla de búsqueda. Digamos que si desea buscar en el rango A2: A10, StartCell será A1.
RowLookupValue: este es el valor de búsqueda que desea encontrar en las filas debajo de StartCell. RowLookupRange: este es el rango en el que desea buscar RowLookupValue. Es el rango por debajo de StartCell (A2: A10). ColLookupValue: este es el valor de búsqueda que desea encontrar en las columnas (encabezados).
ColLookupRange: este es el rango en el que desea buscar ColLookupValue. Es el rango en el lado derecho de StartCell (como B1: D1).
Entonces, basta de teoría. Comencemos con un ejemplo.
Ejemplo: búsqueda de ventas usando la función OFFSET y MATCH De la tabla de Excel Aquí tenemos una tabla de muestra de las ventas realizadas por diferentes empleados en diferentes meses.
Ahora nuestro jefe nos pide que demos las ventas realizadas por Id 1004 en el mes de junio. Hay muchas formas de hacerlo, pero como estamos aprendiendo acerca de la fórmula OFFSET-MATCH, las usaremos para buscar el valor deseado.
Ya tenemos la fórmula genérica anterior. Solo necesitamos identificar estas variables en esta tabla.
StartCell es B1 aquí. RowLookupValue es M1. RowLookupRange es B2: B1o (rango por debajo de la celda de inicio).
ColLookupValue está en la celda M2. ColLookupRange es C1: I1 (Rango de encabezado a la derecha de StartCell).
Hemos identificado todas las variables. Pongámoslos en una fórmula de Excel.
Escriba esta fórmula en la celda M3 y presione el botón Intro.
Cuando presiona el botón Enter, obtiene el resultado rápidamente. La venta realizada ID 1004 en junio mes es 177.
¿Cómo funciona?
El trabajo de link: / counting-the-offset-function-in-excel [OFFSET function] es alejarse de la celda inicial dada a la fila y columnas dadas y luego devolver el valor de esa celda. Por ejemplo, si escribo OFFSET (B1,1,1), la función OFFSET irá a la celda C2 (1 celda hacia abajo, 1 celda hacia la derecha) y devuelve su valor.
Aquí, tenemos la fórmula
|
OFFSET(B1,MATCH(M1,B2:B10,0),MATCH(M2,C1:I1,0)) |
El primer link: / lookup-formulas-excel-match-function [MATCH function] devuelve el índice de M1 (1004) en el rango B2: B10, que es 4.
Ahora la fórmula es
|
OFFSET(B1,4,MATCH(M2,C1:I1,0)) |
La siguiente función COINCIDIR devuelve el índice de M2 (‘Jun’) en el rango C1: I1, que I7. Ahora la fórmula es OFFSET (B1,4,7). Ahora, la función OFFSET simplemente mueve 4 celdas hacia abajo a la celda B1 que la lleva a B5. Luego, la función OFFSET se mueve a 7 a la izquierda a B5, que lo lleva a I5. Eso es. La función OFFSET devuelve un valor de la celda I5 que 177.
¿Ventajas de esta técnica de búsqueda?
Esto es rápido La única ventaja de este método es que es más rápido que los otros métodos. Lo mismo se puede hacer usando la función INDEX-MATCH o la función VLOOKUP. Pero es bueno conocer algunas alternativas.
Puede ser útil algún día.
Aquí hay algunas notas sobre el uso de la fórmula de compensación en Excel.
Notas:
-
OFFSET solo devuelve una referencia, no se mueven celdas.
-
Tanto las filas como las columnas se pueden proporcionar como números negativos para invertir su dirección de desplazamiento normal: las columnas negativas desplazadas hacia la izquierda y las filas negativas desplazadas arriba.
-
OFFSET es una «función volátil» – se recalculará con cada cambio de hoja de trabajo. Las funciones volátiles pueden hacer que los libros de trabajo más grandes y complejos se ejecuten lentamente.
-
OFFSET mostrará #REF! valor de error si el desplazamiento está fuera del borde de la hoja de trabajo.
-
Cuando se omite el alto o el ancho, se usa el alto y el ancho de referencia.
-
OFFSET se puede utilizar con cualquier otra función que espere recibir una referencia.
-
La documentación de Excel dice que la altura y el ancho no pueden ser negativos, pero los valores negativos funcionan.
Espero que este artículo sobre cómo usar la fórmula de compensación en Microsoft Excel sea explicativo. Encuentre más artículos sobre valores de búsqueda y fórmulas de Excel relacionadas 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 a [email protected].
Artículos relacionados:
link: / lookup-formulas-use-index-and-match-to-lookup-value [Use INDEX and MATCH to Lookup Value]: La fórmula INDEX-MATCH se usa para buscar de forma dinámica y precisa un valor en una tabla dada. Esta es una alternativa a la función BUSCARV y supera las deficiencias de la función BUSCARV.
link: / tips-sum-by-offset-groups-in-rows-and-columns [Suma por grupos OFFSET en filas y columnas]: La función OFFSET se puede usar para sumar grupos de celdas de forma dinámica. Estos grupos pueden estar en cualquier parte de la hoja.
link: / lookup-formulas-retrieving-every-nth-value-in-a-range [Cómo recuperar cada enésimo valor en un rango en Excel]: Usando la función OFFSET de Excel podemos recuperar valores de filas alternas o columnas. Esta fórmula toma la ayuda de la función FILA para alternar entre filas y la función COLUMNA para alternar en columnas.
link: / counting-the-offset-function-in-excel [Cómo usar la función OFFSET en Excel]: La función OFFSET es una poderosa función de Excel que muchos usuarios subestiman. Pero los expertos conocen el poder de la función OFFSET y cómo podemos usar esta función para hacer algunas tareas mágicas en la fórmula de Excel. Estos son los conceptos básicos de la función OFFSET.
Artículos populares:
link: / keyboard-formula-shortcuts-50-excel-shortcuts-to-aumentar-su-productividad [50 accesos directos de Excel para aumentar su productividad]: Sea más rápido en su tarea. Estos 50 atajos lo harán trabajar aún más rápido en Excel.
link: / formulas-and-functions-Introduction-of-vlookup-function [Cómo usar la función VLOOKUP de Excel]: Esta es una de las funciones más utilizadas y populares de Excel que se usa para buscar valores de diferentes rangos y hojas .
link: / tips-countif-in-microsoft-excel [Cómo usar]
enlace: / fórmulas-y-funciones-introducción-de-vlookup-function [Excel]
link: / tips-countif-in-microsoft-excel [Función COUNTIF]: Cuenta valores con condiciones usando esta función asombrosa.
No necesita filtrar sus datos para contar valores específicos. La función CONTAR.SI es esencial para preparar su tablero.
link: / excel-formula-and-function-excel-sumif-function [Cómo usar la función SUMIF en Excel]: Esta es otra función esencial del tablero. Esto le ayuda a resumir valores en condiciones específicas.