Mini Motor de Búsqueda en Excel sin Macros

 En este post vamos a continuar con el tema “búsqueda inteligente en Excel post10/post18/post19”. El objetivo es crear un sistema de búsqueda en Excel sin la necesidad de utilizar macros, solo con la ayuda de algunas funciones. Este sistema de búsqueda permitirá que introduciendo una palabra clave nos haga una lista de sugerencias para poder elegir el dato que nos interesa, tipo "motor de búsqueda en Excel". 
La formula principal que utilizamos para hacer nuestra mini aplicación es “Buscarv”. Y luego utilizándolo en forma inteligente podemos crear lo que necesitamos.
Click en la imagen para ver el video o en este link.
 
Descripción del Funcionamiento
Para empezar tenemos una base de datos dónde está la información que nos interesa. De esta información, para agilizar nuestro trabajo muchas veces queremos ubicarla mediante un código. Sin embargo al ser extensa la base de datos, normalmente es difícil recordar los códigos para eso es que necesitamos una forma de encontrar la información más eficientemente.
Para este ejemplo suponemos que tenemos una concesionaria de autos Audi, BMW y Alfa Romeo. En la base de datos se listan los diferentes modelos de cada marca, pero obviamente son muchos modelos.
Como ven en la lista tenemos el ID del producto, en este caso el modelo, la cantidad disponible y el precio. Además en la primera columna tenemos un dato auxiliar, en esta  columna se verifica que la palabra clave buscada se encuentra en la descripción del Marca/Modelo. Si lo encuentra lo cuenta, sino mantiene el valor anterior, como ven en la figura. La fórmula que se utiliza para el primer dato, por ejemplo suponiendo que el segundo dato del ID está en la celda “H9“, es la siguiente:
“=SI((CONTAR.SI($I9;"*"&$B$9&"*"))=1;G8+1;G8)”
La columna auxiliar en este caso la utilizamos delante por conveniencia, como ya van a ver. Ya que vamos a referir a ese número auxiliar para la búsqueda.
Ahora vamos a ver el dashboard o panel de control en donde utilizamos el motor de búsqueda.
Vamos a ver qué es lo que tenemos:
En la parte superior tenemos lo que sería el formulario de búsqueda por ID. En dónde introducimos el ID y nos muestra los datos del artículo. En este caso, solo tres datos que son la Marca y el modelo, la cantidad en stock y el precio de lista.
En la parte inferior se encuentra el formulario de búsqueda que nos hará más fácil la tarea de encontrar el artículo que necesitamos.
Como ven en la imagen anterior, para esta búsqueda nos muestra 4 datos. Cada dato tiene una etiqueta de titulo que nos dice de que datos se trata. Básicamente para este caso queremos que nos muestre el ID, la descripción del artículo, la cantidad y el precio. Es decir, dos o tres datos que nos identifica claramente el artículo. La primera columna de los datos vuelve a ser la posición que es un dato auxiliar que necesitamos.

La lógica del formulario de búsqueda es el siguiente:
  • Necesitamos mostrar los datos de un artículo en particular, y no recordamos el código.
  • Tenemos algunos datos del artículo, por ejemplo una parte del nombre del modelo o la marca por ejemplo.
  • Introducimos la palabra clave que nos parece en la celda de búsqueda.
  • Nos muestra una lista de sugerencias.
  • Observamos cuál es el artículo que estamos buscando, que está identificado con el ID.
  • Introducimos el ID en el formulario de arriba. 
  • Finalmente logramos mostrar la información del artículo solicitado.

Proceso de creación
Para la creación tengamos en cuenta la siguiente imagen.
Para crear el formulario de búsqueda por ID, simplemente en cada celda utilizamos la función “BuscarV” correspondiente al dato buscado.
  • Modelo/Artículo: “=BUSCARV($B$5;$H$8:$K$25;2;FALSO)”
  • Cantidad: “=BUSCARV($B$5;$H$8:$K$25;3;FALSO)”
  • Precio: “=BUSCARV($B$5;$H$8:$K$25;4;FALSO)”
Para crear el formulario del “motor de búsqueda” se usan formulas similares. Solamente hay que tener en cuenta lo siguiente:
  • La celda dónde se introduce la palabra clave es la “B9”.
  • Este sistema puede mostrar una lista de hasta 10 coincidencias, es decir, 10 posiciones. El número de posición es el que ayuda a generar  la lista, se usa como Identificación transitoria de las coincidencias. Como ven en la base de datos que tenemos a la derecha se nos ordena la info dándole una numeración relativa  a cada dato. En la imagen como hay cuatro coincidencias hay cuatro posiciones.
  •   Teniendo en cuenta lo anterior, utilizando como dato de búsqueda la posición, buscamos en la base de datos a que dato corresponde cada posición. Por ejemplo para la posición 1: “BUSCARV ($A11;$G$8:$I$24;2;FALSO)”. El resto de los datos se obtienen del resultado de esta fórmula, es decir el ID del producto.
  •  Todas las celdas contienen esta fórmula. Deberemos utilizar un condicional para que no nos muestre dato si no encuentra la posición, con lo cual para el primer dato nos quedaría: “=SI($A11=0;0;BUSCARV($A11;$G$8:$I$24;2;FALSO))”. Es decir, que si no encuentra posición no muestra nada en la celda.
  • Para saber cuántas coincidencias hay en la lista de datos utilizamos un “Contar.Si” que está en el titulo de la columna “Aux” en la base de datos. Esto lo realizamos para saber de antemano cuantos datos vamos a tener.
  • En la columna posición en la lista de coincidencias utilizamos un condicional para no mostrar más posiciones que la encontradas con la formula anterior. Para el segundo dato de la celda “A12” tenemos: “=SI(Y($G$7>=2;$B$9>0);2;0)”
Ahora, como la intensión de este artículo es no utilizar macros ni mucho menos y así demostrar que no es necesario amplio conocimiento ni la utilización de formulas desconocidas.
Siguiendo los pasos anteriores tenemos finalizado nuestro sistema de búsqueda. Si no queremos estar escribiendo el código cada vez que encontramos el articulo podemos utilizar una herramienta adicional, un “control de formulario” ,para hacer más fácil la carga del código sin estar escribiéndolo en la celda del ID.
Para introducir el control debemos tener activada la ficha “Programador”. En la cinta elegimos, dentro de controles de formulario, el “botón de opción”. Debemos introducir un botón de opción por cada fila de las 10 disponibles para la lista de coincidencias. Luego  en opciones del control, vinculamos a la celda “E9”.
Para no tener que escribir el ID del articulo la celda del ID la vinculamos a la celda “E9” o directamente vinculamos los controles a la celda del ID. Con lo cual cada vez que encontramos el dato que necesitamos presionamos arriba del control y automáticamente se completa el ID mostrando la info. Genial No!
Recuerden que esto es un ejemplo para ver cómo funciona el método pero lo pueden utilizar para cualquiera de sus mini-aplicaciones.
Pueden descargar algunos ejemplos (plantillas/templates) de la zona de Descargas. Si tienen alguna duda o consulta pueden hacerlo a través del formulario de contacto.
Les recomendamos que sigan el blog por alguno de los medios, mail o por redes sociales, para recibir las actualizaciones y novedades.
¡Éxitos!

Keys:
  • Search Engine in Excel 
  • Motor de búsqueda en Excel sin macros
  • Búsqueda inteligente sin macros.
  • Sistema de búsqueda en Excel sin macros
  • Formulario de búsqueda en Excel sin macros

46 comentarios:

Alberto Diaz dijo...

Les agradeceria enormemente, si pudsieran disponible para descarga el archivo del minimotor de busqueda sin macros, he intentado hacerlo como se explica en el blog, pero "me pierdo" en los lugares donde debo poner cada formula, me ayudaria mucho el archivo para ver las dependencias de estas.

Mil gracias

Juan Pablo Torres dijo...

Hola Alberto, como estas! Próximamente estará disponible la descarga. Sino escríbenos con el formulario de contacto. Sds

JOHANA BEDOYA dijo...

Hola, donde lo puedo descargar?

Juan Pablo Torres dijo...

Hola Johana, cómo estas! No esta subido todavía en la zona de descargas. Por favor escribe medinate el formulario de control y te remitimos la plantilla. Saludos

JOHANA BEDOYA dijo...

Disculpa, me podrías indicar donde queda dicho formulario, no veo la opción por ningún lado. Perdón la ignorancia.

Juan Pablo Torres dijo...

Hola Johana, no te preocupes que no hay problema. En la solapa de paginas, en la parte de arriba del blog donde dice "Contacto" sino nos dejas tu mail. Saludos

JOHANA BEDOYA dijo...

Hola, mi e-mail es johana199118@hotmail.com te agradezco mucho tu colaboración. :)

Marlon J. dijo...

Hola , si también intento hacer una lista de clientes y una formula para su búsqueda. Y aun no logro el resultado.
El blog es excelente pero por favor podrías compartir el ejemplo en excel.

Espero lograrlo hacer...

Juan Pablo Torres dijo...

Hola Marlon, cómo estas! Muchas gracias por escribir y tus comentarios. Todavía no esta disponible en la zona de descargas pero si nos escribes con el formulario de contacto lo enviamos. Saludos

Percy Leon dijo...

Felicitaciones, esta bueno, podes enviarme a mi correo percy@contumacino.com para ver si me guío y hago uno para mi?

saludos y gracias

GERARDO JESUS HERNANDEZ SANGUINO dijo...

HOLA ME SALIO CASI TODO BIEN,

LA PREGUNTA ES COMO OCULTO LOS NÚMEROS "0" QUE ME SALEN CUANDO NO HAY REGISTRO

Juan Pablo Torres dijo...

Hola Gerardo, gracias por los comentarios!
Puedes hacerlo de dos maneras, usar un condicional dentro de las formulas o usar un formato condicional para que te oculte los errores y los ceros. Recuerda que puedes escribirnos directamente a través del formulario de contacto del blog. Saludos

Oskar Mata dijo...

Esta genial este mini buscador me encantaría poder consultar la plantilla.

Jakers dijo...

Hola, me salió todo muy bien :)
Encontré su blog por primera vez y su artículo fue la solución a mi problema de hace días. Es justo lo que buscaba, porque estoy comenzando a conocer Excel.
No podía dejar de saludarlos y darles las gracias.

Juan Pablo Torres dijo...

Muchas Gracias por tus comentarios Jakers!!

Saludos

Claudio dijo...

Que tal compañero
Muy bueno tu tutorial, tengo una duda como hacer para que al colocar un numero te aparaezca automaticamte con los tres ceros a la izquierda

Juan Pablo Torres dijo...

Hola amigo, gracias por el comentario! Para agregar ceros vas a formato de celdas y en personalizada, donde dice tipos pones "0000". De esta manera le decís que te muestre el número con ese formato. Saludos

Fernando Ortega Valdivia dijo...

Hola amigo soy el mismo que te escribio en tu canal de youtube, tengo una tabla con cuatro datos
1-un numero o id
2-un numero (q es el q quiero buscar)
3-Una cantidad en pesos
4-Una fecha de cuando se dio ese precio
Lo que intento hacer es buscar un numero especifico y que me muestre los q tengo en la base de datos
Ya estube buscando y no lo eh encontrado tu motor de busqueda me agrado mucho solo q me busca una palabra ya intente poner algun codigo y no me muestra nada cres que me puedas ayudar porfavor

Fernando Ortega Valdivia dijo...

Mi correo es guero_13_red@hotmail.com

Hebert Ludin Neyra Calle dijo...

Esta bien el aplicativo men... porsiaca cual es la contraseña del libro uhmmm si serias tan amable de darmela mi email es helubris@gmail.com. Gracias de antemano

Juan Pablo Torres dijo...

Hola amigo, cómo estas! Gracias por escribir!
En cuanto a tu consulta, en la plantilla solo se protegen los objetos para no modificarlos por error. Además te recordamos que la plantilla sirve para ver los elementos, las formulas, etc. de modo de poder crear uno propio. Saludos

Andrew Archis dijo...

El ejemplo esta muy bueno, no he encontrado otro parecido y de esa forma felicitaciones, pero he intentado hacerlo y nada no me sale, sera que me pueden pasar el archivo le agardecedira gracias. andrew.archis@gmail.com

Juan Pablo Torres dijo...

Hola amigo, cómo estas! Gracias por escribir!
ANIMO, aquí esta todo lo que necesitas. Solo lo tienes que intentar y quizás aprendas algo nuevo en el camino , además la satisfacción de hacerlo uno mismo es algo muy bueno. Sin embargo en la zona de descargas esta la plantilla para verla y aprender de ella. Saludos

Juan Pablo Torres dijo...

Hola amigo, cómo estas! Gracias por escribir!
ANIMO, aquí esta todo lo que necesitas. Solo lo tienes que intentar y quizás aprendas algo nuevo en el camino , además la satisfacción de hacerlo uno mismo es algo muy bueno. Sin embargo en la zona de descargas esta la plantilla para verla y aprender de ella. Saludos

Alexander Lara dijo...

Buen día! Excelente post! tengo muchas consultas, pero bueno quizá la que mas me interesa es saber si tienes disponible el archivo (descargable) me ayudarías mucho!

Juan Pablo Torres dijo...

Hola amigo, cómo estas! Gracias por comentar! En la zona de descargas esta la plantilla donde podrás ver los elementos y las formulas pero recuerda que lo bueno de esto sería que intentes construir lo que necesitas a tu medida ya que eso solamente es una plantilla para mostrar una idea. Saludos

Esteban dijo...

DISCULPE TENGO UN PEQUEÑO PROBLEMA CON MI FORMULARIO YA QUE MI BASE DE DATOS SON MEDICAMENTOS Y SON PRODUCTOS REPETIDOS PERO CON DIFERENTE PRESENTACION Y ENTONCES CUANDO BUSCO MI PRODUCTO SOLO ME SALE EL PRIMERO QUE TIENE COMO CONICIDENCIA Y LOS DEMAS ME LOS REPITE PERO CON LA MISMA PRESENTACION DEL PRIMERO COMO LE HAGO

Juan Pablo Torres dijo...

Hola Amigo, cómo estas! Gracias por escribirnos!
Supongo que dices que tienes el mismo medicamento pero tienes diferentes marcas y presentaciones, es decir , 10 mg, 50 mg, etc. Por decir un ejemplo. Esta mini aplicación debes adaptarla de acuerdo justamente a lo que necesites, en este ejemplo nosotros utilizamos una base de ejemplo para que no sea al aire y se entienda la idea pero nada impide que ordenes y organice de acuerdo a tus necesidades. Es justo la idea. Inténtalo de nuevo y sino puedes escribirnos directamente mediante el formulario de contacto y te ayudamos. Saludos

Daniel Moises Garcia Aviles dijo...

Hola buenas tardes, me ha gustado enormemente su descripcion y su informacion me sirvio de base para realizar un proyecto que en lo personal tenia varios meses atorado, por ahora uso office 2013 y fijese que al poner en las sentencias 0 para cuando arroje los datos asi se queda en 0, le he modificado dicho 0 por "", y me ha quedado perfecto, pero aun no logro ocultar el formato de la tabla como bordes y colores, ando medio atorado en esto y les agradeceria su ayuda, danyboyz311@gmail.com, de antemano muchas gracias.

Juan Pablo Torres dijo...

Hola amigo Daniel, muchas gracias por tus comentarios!
Lo que te falta es lo más sencillo, que son los formatos condicionales. En ese rango puedes crearlo, hay una regla que es teniendo en cuenta el valor de la celda. Si el valor es cero o vacío eliges el formato que quieres que tenga la fuente o los bordes, etc.
Esperamos tus comentarios. Recuerda que nos puedes escribir directamente mediante el formulario de contacto.
Saludos!

Carlos Meza dijo...

Hola, Como hago para que en la lista de resultados de la busqueda aparescan las ligas(Hiperlinks) que estan en alguna de mi columnas de la base de datos?

Gracias.

Juan Pablo Torres dijo...

Hola, cómo estas amigo! Gracias por comentar!
En cuanto a tu consulta, deberías agregar otra columna de datos y en ella utilizar un buscarV o otra formula para reverenciar y mostrar el resultado dependiendo de la búsqueda, tal cual esta ahora. Es el mismo procedimiento.
Esperamos tus comentarios.
Saludos

Ghost Writer dijo...

Tengo una consulta, antes que nada agradezco dicho aporte y pude hacer bien las fórmulas del buscador por medio de ID... Mi consulta tiene que ver con el tema del ID, porque yo en el número del ID tengo un HIPERVÍNCULO con un PDF que me muestra el producto en cuestión pero cuando hago la búsqueda no me salta con el hipervínculo anexado, hay alguna forma de que el hipervínculo también salga en los resultados de la búsqueda?...

Desde ya muchas gracias y disculpen las molestias...

Atte.

Juan Pablo Torres dijo...

Hola amigo, cómo estas! Gracias por escribirnos!
En cuánto a tu consulta, se podría pero debes construir el hipervinculo como formula no vincularlo directamente y no deberías tener problemas.
Esperamos tus comentarios.
Saludos
P.D.: tenemos un artículo pendiente de algo similar, en cualquier momento lo subiremos.

Unknown dijo...

Excelente post, lo probé con una lista de 600 productos y me funciona perfecto!!!
Una pregunta, como debo hacer para que busque palabras sin importar su orden de escritura, me explico? En el ejemplo que das imaginemos que existe un Audi Coupé X y quiero listar TODOS los Audi que son Coupé. Es decir, busco Audi coupé, pero me busca lo que está escribo "identicamente" y solo me traería este que agrego y no el que dice Audi TT Coupé... puedo separar las palabras Audi y Coupé y que busque si existen ambas en Marca/Modelo?
Estoy intentando hacerlo, pero mi experiencia es escasa :S
Saludos y te felicito por el blog.
Daro

Juan Pablo Torres dijo...

Hola amigo, cómo estas! Gracias tus comentarios!
En cuanto a tu consulta, todo se puede hacer pero siempre es mas complejo. Si te fijas en la "lista de artículos" del blog, hay otros métodos de búsqueda y hay uno para buscar palabras frases pero crece la complejidad.
Esperamos tus comentarios.
Saludos

Willy Uceda dijo...

Buen día! ,amigo me ayudarías muchísimo si me podrías dar la dirección de descarga, gracias.

Juan Pablo Torres dijo...

Hola amigo, cómo estas! Gracias por tus comentarios!
En la solapa "Descargas" están las plantillas disponibles, ahí lo puedes descargar.
Saludos

ronald pantaleon dijo...

Hola podria enviarme a mi correo ronaldpantaleon341@gmail.com el Minimotor de búsqueda en excel xfa gracias

Juan Pablo Torres dijo...

Hola amigo, cómo estas! Revisa la solapa de descargas.
Saludos

Joselp dijo...

Hola queria hacer la misma busqueda pero con numeros no con letras,no se si me entiendes. Por favor una respuesta mi correo es edson.1.lazarte@gmail.com
gracias de antemano.

Juan Pablo Torres dijo...

Hola amigo, cómo estas! Gracias por escribirnos!
En cuanto a tu consulta, básicamente es lo mismo pero con números. Tendrías que modificar algo del valor buscado para que no te tire error el buscarv solamente.
Esperamos tus comentarios.
Saludos

Fernando Palacios dijo...

Que tal amigo
Donde puedo ver los pasos a seguir para vincular los botones con los resultados
y como hacer visible e invislible la tabla de resultados
Muy buen video (Y)
Saludos.

Juan Pablo Torres dijo...

Hola amigo, cómo estas! Gracias por comentar!
Esta todo en el artículo y en el vídeo.
Saludos

Noldi Vives dijo...

Hola, he estado revisando tu tutorial pero no más no puedo hacerlo... Me interesaría que me vendieras el archivo para yo usarlo con una BD que tengo ya que ando sobre tiempos y quiero facilitarme un poco la carga... te dejo mi dato: noldi@candyandcake.com.mx

Juan Pablo Torres dijo...

Hola amigo, cómo estas! Gracias por tus comentarios!
Revisa por favor la zona de descargas y la tienda. Sino encuentras lo que necesitas nos escribes mediante el formulario de contacto.
Saludos