Contenido
- Buscar en Excel por múltiples criterios
- Extraemos los valores 2do, 3ro, etc. usando BUSCARV
- Recuperar todas las repeticiones del valor deseado
- Búsqueda de XNUMXD por fila y columna conocidas
- Uso de múltiples BUSCARV en una fórmula
- Sustitución dinámica de datos de diferentes tablas usando BUSCARV e INDIRECTO
En la segunda parte de nuestro tutorial sobre la función VPR (BUSCARV) en Excel, analizaremos algunos ejemplos que te ayudarán a dirigir todo el poder VPR para resolver las tareas de Excel más ambiciosas. Los ejemplos asumen que ya tiene un conocimiento básico de cómo funciona esta característica. Si no es así, quizás le interese comenzar con la primera parte de este tutorial, que explica la sintaxis y el uso básico. VPR. Bueno, comencemos.
Buscar en Excel por múltiples criterios
Función VPR en Excel es una herramienta realmente poderosa para realizar búsquedas de un valor particular en una base de datos. Sin embargo, existe una limitación significativa: su sintaxis le permite buscar solo un valor. ¿Qué sucede si desea buscar por múltiples condiciones? Encontrará la solución a continuación.
Ejemplo 1: Buscar por 2 criterios diferentes
Supongamos que tenemos una lista de pedidos y queremos encontrar Cantidad de bienes (cant.), basado en dos criterios: Nombre del cliente (Cliente) y Название продукта (Producto). El asunto se complica por el hecho de que cada uno de los compradores ordenó varios tipos de bienes, como se puede ver en la siguiente tabla:
función regular VPR no funcionará en este escenario porque devolverá el primer valor que encuentre que coincida con el valor de búsqueda dado. Por ejemplo, si desea saber la cantidad de un artículo dulcesordenado por el comprador Jeremy Hill, escribe la siguiente fórmula:
=VLOOKUP(B1,$A$5:$C$14,3,FALSE)
=ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)
– esta fórmula devolverá el resultado 15correspondiente al producto Manzanas, porque es el primer valor que coincide.
Hay una solución simple: cree una columna adicional en la que combine todos los criterios deseados. En nuestro ejemplo, estas son las columnas Nombre del cliente (Cliente) y Название продукта (Producto). No olvide que la columna fusionada siempre debe ser la columna más a la izquierda en el rango de búsqueda, ya que es la columna de la izquierda que la función VPR mira hacia arriba cuando busca un valor.
Entonces, agrega una columna auxiliar a la tabla y copia la siguiente fórmula en todas sus celdas: =B2&C2. Si desea que la cadena sea más legible, puede separar los valores combinados con un espacio: =B2&» «&C2. Después de eso, puedes usar la siguiente fórmula:
=VLOOKUP("Jeremy Hill Sweets",$A$7:$D$18,4,FALSE)
=ВПР("Jeremy Hill Sweets";$A$7:$D$18;4;ЛОЖЬ)
or
=VLOOKUP(B1,$A$7:$D$18,4,FALSE)
=ВПР(B1;$A$7:$D$18;4;ЛОЖЬ)
donde esta la celda B1 contiene el valor concatenado del argumento valor de búsqueda (valor_buscado) y 4 - Argumento col_index_num (column_number), es decir, el número de la columna que contiene los datos a recuperar.
Ejemplo 2: BUSCARV por dos criterios con la tabla que se ve en otra hoja
Si necesita actualizar la tabla principal (Tabla principal) agregando datos de la segunda tabla (Tabla de búsqueda), que se encuentra en otra hoja o en otro libro de Excel, puede recopilar el valor deseado directamente en la fórmula que inserta en la mesa principal.
Como en el ejemplo anterior, necesitará una columna auxiliar en la tabla de búsqueda con los valores combinados. Esta columna debe ser la columna más a la izquierda en el rango de búsqueda.
Entonces la fórmula con VPR podría ser así:
=VLOOKUP(B2&" "&C2,Orders!$A&$2:$D$2,4,FALSE)
=ВПР(B2&" "&C2;Orders!$A&$2:$D$2;4;ЛОЖЬ)
Aquí, las columnas B y C contienen nombres de clientes y nombres de productos, respectivamente, y el enlace Pedidos!$A&$2:$D$2 define una tabla para buscar en otra hoja.
Para que la fórmula sea más legible, puede darle un nombre al rango de vista y luego la fórmula se verá mucho más simple:
=VLOOKUP(B2&" "&C2,Orders,4,FALSE)
=ВПР(B2&" "&C2;Orders;4;ЛОЖЬ)
Para que la fórmula funcione, los valores en la columna más a la izquierda de la tabla que está viendo deben combinarse exactamente de la misma manera que en los criterios de búsqueda. En la figura anterior, combinamos los valores u2bu2 y pusimos un espacio entre ellos, de la misma manera que debes hacerlo en el primer argumento de la función (BXNUMX& “” & CXNUMX).
¡Recuerda! Función VPR limitado a 255 caracteres, no puede buscar un valor que tenga más de 255 caracteres. Tenga esto en cuenta y asegúrese de que la longitud del valor deseado no supere este límite.
Estoy de acuerdo en que agregar una columna auxiliar no es la solución más elegante y no siempre aceptable. Puede hacer lo mismo sin la columna auxiliar, pero eso requeriría una fórmula mucho más compleja con una combinación de funciones ÍNDICE (ÍNDICE) y PARTIDO (MÁS EXPUESTO).
Extraemos los valores 2do, 3ro, etc. usando BUSCARV
Ya lo sabes VPR puede devolver solo un valor coincidente, más precisamente, el primero encontrado. Pero, ¿qué sucede si este valor se repite varias veces en la matriz vista y desea extraer el segundo o el tercero? ¿Y si todos los valores? El problema parece complicado, ¡pero la solución existe!
Suponga que una columna de la tabla contiene los nombres de los clientes (Nombre del cliente) y la otra columna contiene los productos (Producto) que compraron. Tratemos de encontrar los artículos 2, 3 y 4 comprados por un cliente determinado.
La forma más fácil es agregar una columna auxiliar antes de la columna nombre del cliente y rellénelo con los nombres de los clientes con el número de repetición de cada nombre, por ejemplo, Juan Pérez1, Juan Pérez2 etc. Haremos el truco con la numeración usando la función COUNTIF (COUNTIF), dado que los nombres de los clientes están en la columna B:
=B2&COUNTIF($B$2:B2,B2)
=B2&СЧЁТЕСЛИ($B$2:B2;B2)
Después de eso puedes usar la función normal. VPRpara encontrar el orden requerido. Por ejemplo:
- Encuentre 2º artículo pedido por el cliente Dan Brown:
=VLOOKUP("Dan Brown2",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown2";$A$2:$C$16;3;ЛОЖЬ) - Encuentre 3º artículo pedido por el cliente Dan Brown:
=VLOOKUP("Dan Brown3",$A$2:$C$16,3,FALSE)=ВПР("Dan Brown3";$A$2:$C$16;3;ЛОЖЬ)
De hecho, puede ingresar una referencia de celda como valor de búsqueda en lugar de texto, como se muestra en la siguiente figura:
Si solo estas buscando 2-e repetición, puede hacerlo sin la columna auxiliar creando una fórmula más compleja:
=IFERROR(VLOOKUP($F$2,INDIRECT("$B$"&(MATCH($F$2,Table4[Customer Name],0)+2)&":$C16"),2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;ДВССЫЛ("$B$"&(ПОИСКПОЗ($F$2;Table4[Customer Name];0)+2)&":$C16");2;ИСТИНА);"")
En esta fórmula:
- $ F $ 2 – una celda que contiene el nombre del comprador (no se modifica, tenga en cuenta que el enlace es absoluto);
- $ segundo $ - columna nombre del cliente;
- Table4 – Tu mesa (este lugar también puede ser un rango regular);
- $ C16 – la celda final de su tabla o rango.
Esta fórmula encuentra solo el segundo valor coincidente. Si necesita extraer las repeticiones restantes, use la solución anterior.
Si necesita una lista de todas las coincidencias, la función VPR esto no es un ayudante, ya que solo devuelve un valor a la vez: período. Pero Excel tiene una función. ÍNDICE (ÍNDICE), que puede hacer frente fácilmente a esta tarea. Cómo se verá esa fórmula, aprenderá en el siguiente ejemplo.
Recuperar todas las repeticiones del valor deseado
Como se ha mencionado más arriba VPR no puede extraer todos los valores duplicados del rango escaneado. Para hacer esto, necesita una fórmula un poco más compleja, compuesta por varias funciones de Excel, como ÍNDICE (ÍNDICE), SMALL (Pequeño y FILA (LÍNEA)
Por ejemplo, la siguiente fórmula encuentra todas las repeticiones del valor de la celda F2 en el rango B2:B16 y devuelve el resultado de las mismas filas en la columna C.
{=IFERROR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3)),"")}
{=ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3));"")}
Ingrese esta fórmula de matriz en varias celdas adyacentes, como las celdas F4: F8como se muestra en la siguiente figura. El número de celdas debe ser igual o superior al máximo número posible de repeticiones del valor buscado. No olvides hacer clic Ctrl + Shift + Enterpara ingresar la fórmula matricial correctamente.
Si está interesado en comprender cómo funciona, profundicemos un poco en los detalles de la fórmula:
Parte 1:
IF($F$2=B2:B16,ROW(C2:C16)-1,"")
ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"")
$F$2=B2:B16 – comparar el valor de la celda F2 con cada uno de los valores del rango B2:B16. Si se encuentra una coincidencia, entonces la expresión CADENA(C2:C16)-1 devuelve el número de la línea correspondiente (valor -1 le permite no incluir la línea de encabezado). Si no hay coincidencias, la función IF (SI) devuelve una cadena vacía.
resultado de la función IF (SI) habrá tal matriz horizontal: {1,"",3,"",5,"","","","","","",12,"","",""}
Parte 2:
ROW()-3
СТРОКА()-3
Aquí la función FILA (LINE) actúa como un contador adicional. Como la fórmula se copia en las celdas F4:F9, restamos el número 3 del resultado de la función para obtener el valor 1 en la celda F4 (línea 4, restar 3) para obtener 2 en la celda F5 (línea 5, resta 3) y así sucesivamente.
Parte 3:
SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))
Función SMALL (PEQUEÑO) devuelve no h el valor más pequeño en la matriz de datos. En nuestro caso, qué posición (desde la más pequeña) devolver está determinada por la función FILA (LÍNEA) (ver Parte 2). Entonces, para una celda F4 función PEQUEÑO({matriz},1) devoluciones 1º (más pequeño) elemento de matriz, es decir 1. para celular F5 devoluciones 2º el elemento más pequeño de la matriz, es decir 3, etc.
Parte 4:
INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,""),ROW()-3))
ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;"");СТРОКА()-3))
Función ÍNDICE (ÍNDICE) simplemente devuelve el valor de una celda específica en una matriz C2:C16. para celular F4 función ÍNDICE ($ C $ 2: $ C $ 16) regresará Manzanas F5 función ÍNDICE ($ C $ 2: $ C $ 16) regresará dulces y así sucesivamente.
Parte 5:
IFERROR()
ЕСЛИОШИБКА()
Finalmente, ponemos la fórmula dentro de la función. IFERROR (IFERROR), porque es poco probable que esté satisfecho con el mensaje de error #at (#N/A) si la cantidad de celdas en las que se copia la fórmula es menor que la cantidad de valores duplicados en el rango que se está visualizando.
Búsqueda de XNUMXD por fila y columna conocidas
Realizar una búsqueda XNUMXD en Excel implica buscar un valor por un número de fila y columna conocido. En otras palabras, está extrayendo el valor de la celda en la intersección de una fila y columna en particular.
Entonces, pasemos a nuestra tabla y escribamos una fórmula con una función VPR, que encontrará información sobre el costo de los limones vendidos en marzo.
Hay varias formas de realizar una búsqueda XNUMXD. Consulta las opciones y elige la que más te convenga.
Funciones BUSCARV y COINCIDIR
Puedes usar un montón de funciones. VPR (BUSCARV) y MÁS EXPUESTO (COINCIDIR) para encontrar el valor en la intersección de los campos Название продукта (cuerda) y Mes (columna) de la matriz en cuestión:
=VLOOKUP("Lemons",$A$2:$I$9,MATCH("Mar",$A$1:$I$1,0),FALSE)
=ВПР("Lemons";$A$2:$I$9;ПОИСКПОЗ("Mar";$A$1:$I$1;0);ЛОЖЬ)
La fórmula anterior es una función regular. VPR, que busca una coincidencia exacta del valor "Limones" en las celdas A2 a A9. Pero como no sabe en qué columna están las ventas de marzo, no podrá establecer el número de columna para el argumento de la tercera función. VPR. En su lugar, se utiliza la función MÁS EXPUESTOpara definir esta columna.
MATCH("Mar",$A$1:$I$1,0)
ПОИСКПОЗ("Mar";$A$1:$I$1;0)
Traducida al lenguaje humano, esta fórmula significa:
- Buscamos a los personajes “Mar” – argumento valor de búsqueda (valor de búsqueda);
- Mirando en las celdas de A1 a I1 – argumento lookup_array (matriz_buscada);
- Devolver coincidencia exacta – argumento tipo de concordancia (tipo de concordancia).
Usar 0 en el tercer argumento dices funciones MÁS EXPUESTO busque el primer valor que coincida exactamente con el valor que está buscando. Esto es equivalente al valor FALSO (FALSO) para el cuarto argumento VPR.
Así es como puede crear una fórmula de búsqueda bidireccional en Excel, también conocida como búsqueda bidimensional o búsqueda bidireccional.
Función SUMPRODUCT
Función SUMPRODUCT (SUMAPRODUCTO) devuelve la suma de los productos de las matrices seleccionadas:
=SUMPRODUCT(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar"),$A$2:$I$9)
=СУММПРОИЗВ(($A$2:$A$9="Lemons")*($A$1:$I$1="Mar");$A$2:$I$9)
Funciones INDICE y MATCH
En el próximo artículo explicaré estas funciones en detalle, así que por ahora solo puedes copiar esta fórmula:
=INDEX($A$2:$I$9,MATCH("Lemons",$A$2:$A$9,0),MATCH("Mar",$A$1:$I$1,0))
=ИНДЕКС($A$2:$I$9;ПОИСКПОЗ("Lemons";$A$2:$A$9;0);ПОИСКПОЗ("Mar";$A$1:$I$1;0))
Rangos con nombre y el operador de intersección
Si no le gustan todas esas fórmulas complejas de Excel, puede que le guste esta manera visual y memorable:
- Seleccione la tabla, abra la pestaña Fórmulas (Fórmulas) y haga clic en Crear a partir de la selección (Crear a partir de la selección).
- Revisa las cajas Fila superior (en la línea de arriba) y Columna izquierda (en la columna de la izquierda). Microsoft Excel asignará nombres a los rangos de los valores en la fila superior y la columna izquierda de su hoja de cálculo. Ahora puede buscar usando estos nombres directamente sin crear fórmulas.
- En cualquier celda vacía, escribe =nombre_fila nombre_columna, por ejemplo así:
=Limones Mar
… o viceversa:
= Mar Limones
Recuerda que los nombres de las filas y columnas deben estar separados por un espacio, que en este caso funciona como el operador de intersección.
Cuando ingresa un nombre, Microsoft Excel mostrará una información sobre herramientas con una lista de nombres coincidentes, al igual que cuando ingresa una fórmula.
- Prensa Participar y comprobar el resultado
En general, cualquiera que sea el método anterior que elija, el resultado de una búsqueda bidimensional será el mismo:
Uso de múltiples BUSCARV en una fórmula
Sucede que la tabla principal y la tabla de búsqueda no tienen una sola columna en común, y esto le impide usar la función habitual VPR. Sin embargo, hay otra tabla que no contiene la información que nos interesa, pero tiene una columna común con la tabla principal y la tabla de consulta.
Echemos un vistazo al siguiente ejemplo. Tenemos una tabla principal con una columna. SKU (nuevo), donde desea agregar una columna con los precios correspondientes de otra tabla. Además, tenemos 2 tablas de búsqueda. El primero (Tabla de búsqueda 1) contiene números actualizados SKU (nuevo) y nombres de productos, y el segundo (Tabla de búsqueda 2): nombres de productos y números antiguos SKU (antiguo).
Para agregar precios de la segunda tabla de búsqueda a la tabla principal, debe realizar una acción conocida como doble VPR o anidado VPR.
- Escribe una función VPR, que encuentra el nombre del producto en la tabla Tabla de búsqueda 1usando SKU, como el valor deseado:
=VLOOKUP(A2,New_SKU,2,FALSE)=ВПР(A2;New_SKU;2;ЛОЖЬ)Aquí Nuevo_SKU - rango nombrado $A:$B en la mesa Tabla de búsqueda 1, 2 – esta es la columna B, que contiene los nombres de los productos (ver la imagen de arriba)
- Escribir una fórmula para insertar precios de una tabla Tabla de búsqueda 2 basado en nombres de productos bien conocidos. Para hacer esto, pegue la fórmula que creó anteriormente como el valor de búsqueda para la nueva función VPR:
=VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)=ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)Aquí Precio - rango nombrado $ A: $ C en la mesa Tabla de búsqueda 2, 3 es la columna C que contiene los precios.
La siguiente figura muestra el resultado devuelto por la fórmula que creamos:
Sustitución dinámica de datos de diferentes tablas usando BUSCARV e INDIRECTO
Primero, aclaremos qué queremos decir con la expresión “Sustitución dinámica de datos de diferentes tablas” para asegurarnos de que nos entendemos correctamente.
Hay situaciones en las que hay varias hojas con datos del mismo formato y es necesario extraer la información necesaria de una determinada hoja, dependiendo del valor que se ingresa en una celda determinada. Creo que es más fácil explicar esto con un ejemplo.
Imagina que tienes informes de ventas de varias regiones con los mismos productos y en el mismo formato. Quiere encontrar cifras de ventas para una región específica:
Si solo tiene dos informes de este tipo, puede usar una fórmula vergonzosamente simple con funciones VPR и IF (SI) para seleccionar el informe deseado para buscar:
=VLOOKUP($D$2,IF($D3="FL",FL_Sales,CA_Sales),2,FALSE)
=ВПР($D$2;ЕСЛИ($D3="FL";FL_Sales;CA_Sales);2;ЛОЖЬ)
Lugar:
- $D$2 es una celda que contiene el nombre del producto. Tenga en cuenta que aquí usamos referencias absolutas para evitar cambiar el valor de búsqueda al copiar la fórmula a otras celdas.
- $ D3 es una celda con el nombre de la región. Estamos usando una referencia de columna absoluta y una referencia de fila relativa porque planeamos copiar la fórmula a otras celdas en la misma columna.
- FL_Sales и CA_Ventas – los nombres de las tablas (o rangos con nombre) que contienen los informes de ventas correspondientes. Por supuesto, puede usar los nombres de hoja habituales y las referencias de rango de celdas, por ejemplo 'Hoja FL'! $ A $ 3: $ B $ 10, pero los rangos con nombre son mucho más convenientes.
Sin embargo, cuando hay muchas tablas de este tipo, la función IF no es la mejor solución. En su lugar, puede utilizar la función INDIRECT (INDIRECTO) para regresar al rango de búsqueda deseado.
Como usted probablemente sabe, la función INDIRECT se usa para devolver un enlace dado por una cadena de texto, que es exactamente lo que necesitamos ahora. Entonces, reemplace audazmente en la fórmula anterior la expresión con la función IF vincular con la función INDIRECT. Aquí hay una combinación VPR и INDIRECT funciona muy bien con:
=VLOOKUP($D$2,INDIRECT($D3&"_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"_Sales");2;ЛОЖЬ)
Lugar:
- $D$2 – esta es una celda con el nombre del producto, no se modifica debido al enlace absoluto.
- $ D3 es la celda que contiene la primera parte del nombre de la región. En nuestro ejemplo, este FL.
- _Ventas – la parte común del nombre de todos los rangos o tablas con nombre. Cuando se combina con el valor en la celda D3, forma el nombre completo del rango requerido. A continuación hay algunos detalles para aquellos que son nuevos en la función. INDIRECT.
Cómo funcionan INDIRECTO y BUSCARV
Primero, déjame recordarte la sintaxis de la función. INDIRECT (INDIRECTO):
INDIRECT(ref_text,[a1])
ДВССЫЛ(ссылка_на_текст;[a1])
El primer argumento puede ser una referencia de celda (estilo A1 o R1C1), un nombre de rango o una cadena de texto. El segundo argumento determina qué estilo de enlace está contenido en el primer argumento:
- A1si el argumento es CÓDIGO VERDADERO (VERDADERO) o no especificado;
- R1C1, Si FCOMO E (FALSO).
En nuestro caso, el enlace tiene el estilo A1, por lo que puede omitir el segundo argumento y centrarse en el primero.
Así que volvamos a nuestros informes de ventas. Si recuerda, cada informe es una tabla separada ubicada en una hoja separada. Para que la fórmula funcione correctamente, debe nombrar sus tablas (o rangos) y todos los nombres deben tener una parte común. Por ejemplo, así: CA_Ventas, FL_Ventas, TX_Ventas y así. Como puede ver, “_Sales” está presente en todos los nombres.
Función INDIRECT conecta el valor en la columna D y la cadena de texto "_Sales", indicando así VPR en qué tabla buscar. Si la celda D3 contiene el valor "FL", la fórmula buscará en la tabla FL_Ventas, si “CA” – en la tabla CA_Ventas y así sucesivamente.
El resultado de las funciones VPR и INDIRECT será el siguiente:
Si los datos se encuentran en diferentes libros de Excel, debe agregar el nombre del libro antes del rango con nombre, por ejemplo:
=VLOOKUP($D$2,INDIRECT($D3&"Workbook1!_Sales"),2,FALSE)
=ВПР($D$2;ДВССЫЛ($D3&"Workbook1!_Sales");2;ЛОЖЬ)
Si la funcion INDIRECT hace referencia a otro libro, ese libro debe estar abierto. Si está cerrado, la función informará de un error. #¡ÁRBITRO! (¡#SSYL!).