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,
| = DESPLAZAMIENTO (StartCell, COINCIDIR (RowLookupValue, RowLookupRange, 0), COINCIDIR (ColLookupValue, ColLookupRange, 0)) |
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 laStartCell.
RowLookupRange:Este es el rango en el que desea buscar RowLookupValue. Es el rango de abajo StartCell (A2: A10).
ColLookupValue: Este es el valor de búsqueda que desea encontrar en 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 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 sobre 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 Enter.
| = COMPENSACIÓN (B1, COINCIDIR (M1, B2: B10,0), COINCIDIR (M2, C1: I1,0)) |
Cuando presiona el botón Enter, obtiene el resultado de inmediato. La venta realizada ID 1004 en el mes de junio es 177.
¿Como funciona?
El trabajo de la función DESPLAZAMIENTO 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 a la derecha) y devuelve su valor.
Aquí tenemos fórmulaDESPLAZAMIENTO (B1, COINCIDIR (M1, B2: B10,0), COINCIDIR (M2, C1: I1,0)).
La primera función COINCIDIR devuelve el índice de M1 (1004) en el rango B2: B10, que es 4. Ahora la fórmula es,DESPLAZAMIENTO (B1,4, COINCIDIR (M2, C1: I1,0)).
La siguiente función COINCIDIR devuelve el índice de M2 ('Jun') en el rango C1: I1, que i 7. Ahora la fórmula esDESPLAZAMIENTO (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 el 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.
Así que sí, chicos, así es como pueden usar la función DESPLAZAMIENTO y COINCIDIR para BUSCAR valores en tablas de Excel. Espero que haya sido lo suficientemente explicativo. Si tiene alguna duda con respecto a este artículo o cualquier otro tema relacionado con Excel / VBA, pregúnteme en la sección de comentarios a continuación.
Utilice INDICE y COINCIDIR para buscar valor:La fórmula INDEX-MATCH se utiliza para buscar de forma dinámica y precisa un valor en una tabla determinada. Esta es una alternativa a la función BUSCARV y supera las deficiencias de la función BUSCARV.
Suma por grupos de DESPLAZAMIENTO en Filas y Columnas: La función OFFSET se puede utilizar para sumar grupos de celdas dinámicamente. Estos grupos pueden estar en cualquier parte de la hoja.
Cómo recuperar cada valor enésimo de un rango en Excel: Usando la función OFFSET de Excel podemos recuperar valores de filas o columnas alternas. Esta fórmula toma la ayuda de la función FILA para alternar entre filas y la función COLUMNA para alternar en columnas.
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.
Articulos populares:
50 accesos directos de Excel para aumentar su productividad | Acelera tu tarea. Estos 50 accesos directos le permitirán trabajar aún más rápido en Excel.
Cómo utilizar la función BUSCARV de 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 Excel Función COUNTIF| Cuente valores con condiciones usando esta asombrosa función. No necesita filtrar sus datos para contar valores específicos. La función CONTAR.SI es esencial para preparar su tablero.
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.