Como valores de búsqueda con Excel OFFSET-MATCH Función
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 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 i 7. Ahora la fórmula esOFFSET (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.
Así que sí, chicos, así es como pueden usar la función OFFSET y MATCH 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.
Artículos relacionados:
link: / lookup-formulas-use-index-and-match-to-lookup-value [Use INDEX y MATCH para buscar valor]: * 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 utilizar 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-incrementa-your-productividad [50 accesos directos de Excel para aumentar su productividad] | Acelera tu tarea. Estos 50 atajos lo harán trabajar aún más rápido en Excel.
enlace: / fórmulas-y-funciones-introducción-de-función-vlookup [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. link: / tips-countif-in-microsoft-excel [Cómo usar]
enlace: / fórmulas-y-funciones-introducción-de-vlookup-function [Excel]
enlace: / tips-countif-in-microsoft-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.
link: / excel-formula-and-function-excel-sumif-function [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.