Búsqueda basada en una lista desplegable

Transcripción del vídeo

Cuando hacemos una búsqueda sobre nuestros datos en Excel, es completamente normal que nos equivoquemos al momento de ingresar el valor buscado y por lo tanto obtendremos un error en la búsqueda.

Una manera de evitar esos errores es usando una lista desplegable la cual tendrá aquellos valores de búsqueda que son válidos y eso disminuirá en gran medida la probabilidad de obtener un error.

Los datos de ejemplo que tenemos en esta ocasión son una lista de productos de una pequeña tienda de herramientas.

En la primera columna tenemos el nombre del producto y en seguida su clave, la marca y el precio.

Nuestro objetivo será crear una lista desplegable en la columna G la cual nos permitirá elegir cualquiera de los productos disponibles y automáticamente se mostrará la clave, la marca y el precio del producto en las columnas de la derecha.

Para este ejemplo es importante tener los encabezados de columnas de los resultados con el mismo nombre que las columnas en los datos de origen, ya que eso nos ayudará a obtener todos los resultados con una misma fórmula.

Mas adelante daré la explicación detallada sobre este requisito, cuando lleguemos al detalle de la construcción de nuestra fórmula.

En este momento tenemos unos datos tabulares, pero es muy conveniente convertirlos a una tabla de Excel ya que eso ocasionará que los nuevos datos que agreguemos también serán añadidos a las listas desplegables de manera automática.

Para convertir estos datos en una tabla, debes seleccionar una celda de los datos y en la pestaña insertar, dentro del grupo tablas, debemos hacer clic en el botón Tabla.

Excel detectará automáticamente el rango de datos y solo nos queda pulsar el botón Aceptar y se habrá creado la tabla que tiene el nombre Tabla1 tal como lo observas en la pestaña Diseño de tabla.

Ahora estamos listos para hacer nuestra búsqueda basada en una lista desplegable.

El primer paso es crear la lista desplegable en la celda G2 y para eso iré a la pestaña Datos y dentro del grupo Herramientas de datos, pulsaré el botón validación de datos.

Se abrirá un cuadro de diálogo y elegiré la opción Lista y en el cuadro de texto Origen, pulsaré el botón de selección de rango y seleccionaré las celdas de la columna producto.

De nuevo pulsaré el botón de selección y regresaré al cuadro de diálogo y terminaremos pulsando el botón Aceptar.

Con esto puedes notar que la celda G2 ya muestra la lista desplegable con los elementos de la columna Productos, sin embargo, no visualizamos el texto completo de cada elemento de la lista, pero eso se resuelve haciendo más ancha la columna G.

Una vez que hemos incrementado el ancho de la columna, podremos ver adecuadamente cada uno de los elementos.

Puedes notar que los elementos de la lista aparecen en el mismo orden que los datos de origen, pero si quiero tenerlos en orden alfabético será suficiente con ordenar los datos de origen haciendo clic en el botón de filtro de la columna Producto y eligiendo la opción Ordenar de A a Z.

Al desplegar de nuevo nuestra lista tendremos los productos en orden alfabético.

Voy a seleccionar el producto “Cinta Doble Cara” para probar la funcionalidad de búsqueda con este producto.

Comencemos con la creación de la fórmula en la celda H2.

Ingresaré el símbolo igual y para este ejemplo usaremos la función BUSCARV, pero te recuerdo que es totalmente factible hacer la búsqueda con la combinación de funciones INDICE/COINCIDIR.

En este caso, usaremos BUSCARV porque nuestra búsqueda está basada en el nombre del producto que es la primera columna de la tabla y la función BUSCARV será suficiente.

El primer argumento de la función es el valor buscado y que es precisamente el valor de la lista desplegable de la celda G2 así que incluiré la referencia a dicha celda.

Para esta referencia voy a fijar la columna G, ya que, al copiar la fórmula a las celdas de la derecha quiero que la columna permanezca fija.

El segundo argumento de la función BUSCARV es la matriz de búsqueda y seleccionaré todos los datos de la tabla y observarás que Excel coloca automáticamente el nombre Tabla1 que es el nombre de nuestra tabla.

El tercer argumento es la columna que queremos obtener como resultado y que en este caso es la columna Clave que es la segunda columna de los datos así que puedo ingresar el número 2 en este argumento.

Por ahora voy a dejar fijo este número para mostrarte el problema que nos va a generar al copiar la fórmula a la derecha.

Pero antes de eso voy a terminar con esta función BUSCARV ingresando el valor FALSO como su cuarto argumento para pedirle a la función que realice una búsqueda exacta.

Cerraré el paréntesis de la función y al pulsar Enter, obtenemos la clave del producto seleccionado en la lista desplegable de la izquierda.

Vamos a revisar la razón por la cual el número 2 que ingresamos en la fórmula, nos ocasionará un problema al momento de copiarla.

Arrastraré el controlador de relleno a la celda de la derecha y la función BUSCARV nos devolverá de nuevo la clave del producto.

Esto se debe a que, al copiar la fórmula seguimos teniendo el número 2 en el tercer argumento de la función, el cual ocasiona que la función BUSCARV devuelva la segunda columna de los datos, que es precisamente la Clave.

No importa si sigo copiando la fórmula a la derecha, siempre obtendremos el mismo resultado.

Una solución es ingresar manualmente en cada una de las fórmulas el número 3 y el número 4 respectivamente de manera que obtengamos la columna correspondiente, pero voy a aprovechar este ejemplo para enseñarte un método que nos permitirá obtener el número de columna de manera dinámica.

Voy a pulsar el botón Deshacer dos veces para quedarnos solo con la fórmula de la celda H2.

En lugar de ingresar el número 2 como el tercer argumento, necesitamos otra fórmula que nos ayude a obtener dicho número de columna de manera dinámica y para eso usaremos la función COINCIDIR la cual nos permitirá comparar el nombre en la celda H1 con los encabezados de columna de los datos de origen.

El primer argumento de la función COINCIDIR es el valor buscado que se encuentra en la celda H1, así que incluiré la referencia a esa celda, pero voy a fijar la fila de manera que siempre se haga la comparación con los valores de la fila 1.

El segundo argumento es la matriz de búsqueda y voy a seleccionar los encabezados de columna de la Tabla1.

Puedes notar la nomenclatura que usa Excel y que es la referencia estructurada para referirse exclusivamente a los encabezados de columna de la Tabla1.

El tercer y último argumento de la función COINCIDIR es el tipo de coincidencia que queremos usar y para nuestra fórmula queremos una coincidencia exacta.

Cierro el paréntesis de la función COINDICIR y con esto hemos reemplazado el número 2 por una fórmula que obtendrá dicho número basándose en el nombre de la columna.

Pulsaré el botón Introducir y obtenemos correctamente la clave del producto, pero a diferencia de la fórmula anterior, al momento de copiarla a la derecha obtendremos tanto la marca como el precio del producto.

Vamos avanzando con la construcción de nuestra fórmula y ya solo nos falta un último ajuste, y es que, cuando no hemos seleccionado un producto de nuestra lista desplegable, obtenemos un mensaje de error en nuestras fórmulas.

Voy a pulsar de nuevo el botón Deshacer para quedarnos solo con la fórmula de la celda H2 y la única modificación que haré será tomar toda nuestra fórmula y dársela a la función SI.ERROR como su primer argumento y de esa manera sabremos el momento exacto en que obtenemos un error.

El segundo argumento de la función SI.ERROR es el mensaje que queremos desplegar cuando se encuentra el error, pero yo quiero dejar la celda en blanco, así que ingresaré un par de comillas dobles, pero tu puedes ingresar cualquier mensaje de error personalizado.

Pulsaré el botón Introducir y de nuevo obtenemos correctamente la Clave del producto.

Buscar en Excel con una lista desplegable

Copiaré la fórmula a la derecha y obtendremos la marca y el precio y si por alguna razón elimino el producto seleccionado, las celdas se muestran vacías porque la función SI.ERROR está haciendo correctamente su trabajo.

Para finalizar, voy a copiar la lista desplegable en todas las celdas de la columna G, así que activaré la celda G2 y pulsaré el atajo Ctrl+C  y en seguida seleccionaré las celdas inferiores y pulsaré el atajo CTRL+V y ahora todas las celdas tienen una lista desplegable.

Haré lo mismo con las fórmulas, y para eso seleccionaré las celdas H2 y hasta la celda J2 y las copiaré hacia abajo.

En seguida podemos elegir cualquier valor de la columna G y automáticamente obtendremos los resultados en las columnas de la derecha.

Voy a elegir varios productos de manera que observes que nuestras fórmulas devuelven correctamente los datos en las columnas Clave, Marca y Precio.

Puedes comprobar los resultados fácilmente ya que en este momento tenemos los datos de origen en la misma hoja.

Observa que la columna Precio no está mostrando los valores con el formato Moneda, pero eso lo resolveremos fácilmente seleccionado las celdas de la columna y en la pestaña Inicio, dentro del grupo Número, elegimos el formato Moneda y finalmente removeré los decimales que no estamos usando en estos datos.

Para finalizar te mostraré 2 beneficios que hemos obtenido al haber convertido nuestros datos de origen en una tabla de Excel.

El primer beneficio es con los datos nuevos. Voy a ingresar los datos de un nuevo producto, los cuales son incluidos automáticamente en la tabla, pero también serán incluidos en las listas desplegables sin que hagamos alguna modificación.

Puedes observar que el nuevo elemento aparece al final de la lista, ya que esa es su posición dentro de la tabla, pero si queremos incluirla en el orden alfabético solo será necesario volver a ordenar los datos y el nuevo producto estará incluido en el listado alfabético.

El segundo beneficio que hemos obtenido al convertir nuestros datos en una tabla, es que podemos moverlos fácilmente de ubicación sin perder la funcionalidad de las listas desplegables y las fórmulas.

Voy a crear una nueva hoja en el libro y en seguida moveré la tabla a esta nueva hoja.

Para eso debo seleccionar toda la tabla, lo cual podemos hacer con el ratón, pero yo activaré una celda de los datos y pulsaré el atajo Ctrl+E una vez para seleccionar los datos y de nuevo Ctrl+E para incluir los encabezados de columna.

En seguida pulsaré el atajo Ctrl+X para cortar la tabla y activaré la Hoja2 y pulsaré el atajo Ctrl+V para pegarla y con eso hemos movido los datos de origen.

Al regresar a la Hoja1 puedes notar que nuestras fórmulas siguen funcionando correctamente.

Voy a seleccionar las columnas A a la F y haré clic derecho en el encabezado de columna para elegir la opción Eliminar y ahora tendremos nuestros datos en la Hoja2 y nuestras fórmulas en la Hoja1.

Puedo elegir nuevos productos en la primera columna y nuestras fórmulas seguirán funcionando correctamente.

Si por alguna razón ingresamos un producto que no se encuentra en la lista de productos, la validación de datos de las listas nos mostrará un mensaje de error diciendo que dicho valor no está permitido.

De esta manera hemos usado las listas desplegables para mejorar nuestras búsquedas en Excel.

Te invito a implementar esta técnica en tus hojas de Excel lo que mejorará en gran medida su funcionamiento.