Lista dependiente con validación de datos en Excel

Este tema es muy ben tratado en internet. En primer lugar cuando hablamos de lista de validación de datos, nos referimos a las listas tipo Combobox que se pueden crear en una celda con validación de datos. Y cuando hablamos de dependiente nos referimos que de acuerdo al valor que toma una celda, podemos obtener diferentes valores en la lista.

En este artículo vamos a ver un método distinto al tradicional que podemos encontrar en internet. Este método prevé obtener las listas directamente desde la base de datos ordenada, no es necesario como en la forma tradicional de hacer un arreglo con las diferentes listas.

Click en la imagen para ver el video o en este link.

Antes de entrar con el método más avanzado que queremos mostrar, vamos a ver el método clásico que van a ver en internet. Este método tiene ciertas desventajas que veremos.

 La ventaja de este método es que es fácil de entender, la desventaja es que si tenemos muchas listas es incomodo y arduo porque debemos construir todas las listas. Y para eso necesitamos una hoja auxiliar. Por cada variable nueva, necesitamos crear una lista nueva. Además podemos cometer errores, olvidar datos, etc. Otra desventaja podría ser que los datos no se agregan automáticamente a las lista, es decir que cuando introducimos un nuevo dato sino esta prevista en las listas, este no se ve en la lista de validación.

Listas dependientes – Método Tradicional:

Partimos que tenemos una concesionaria de autos por ejemplo. Tenemos tres marcas y varios modelos por marca. En este caso 3 marcas (BMW, AUDI, ALFA ROMEO).

En el método tradicional tenemos los datos ordenados de esta manera.

Como ven hay varias columnas, una de marcas y por cada marca tenemos una lista de los modelos. En este caso tenemos solo 3 modelos  que serían nuestras variables, entonces es bien simple.

Como ven tenemos las listas de validación ya creadas, a la derecha. En la primera tenemos la opción de elegir entre las tres marcas y en la segunda, de acuerdo a la marca nos muestra el modelo. Ahora vamos a ver cómo construirla.

El paso 1 sería crear nombres para cada lista, el de las marcas le podemos llamar “Marcas” como se ve en la imagen.

Paso 2, A las listas de modelos las podemos llamar, tal cual se ve en la imagen, “Audi”, “BMW”, y “Alfa_Romeo”. Este último con el guión ya que los nombres no aceptan espacios. Recuerden que para crearlo deben seleccionar el rango y a la izquierda de la barra de formulas escribir el nombre que quieran. La otra forma sería con el administrador de nombres pero la primer es más simple.

Paso 3, para crear la primera lista de “Marcas” vamos a la celda de marcas luego a la solapa de “Datos” luego seleccionamos “Validación de datos”, se abre un formulario, elegimos “Lista” y en la formula escribimos el nombre dado a la lista de “Marcas” con lo que nos queda la lista de Marcas , tal muestra la figura.

Paso 4, ahora vamos a crear la lista para el modelo correspondiente a la marca seleccionada en la primer lista.

Usamos la función “Indirecto(“$H$5”)”. Es decir que nos trae la lista que esta en esa celda. Es decir si esta BMW por ejemplo, nos mostrara el rango nombra con ese nombre que se corresponde a los modelos de la marca BMW.

Así termina el método clásico para hacer listas desplegables dependientes. El método más avanzado no varía mucho en cuánto al procedimiento pero tenemos una formular más extensa en la validación.

Listas dependientes – Método avanzado:

Partimos del ejemplo de base de datos que teníamos del Mini Motor de Búsqueda en Excel sin macros. Es decir que tenemos una lista de elementos, productos, en este caso autos. Siempre en toda base de datos tenemos una lista ordenada en filas y columnas con valores, con un título para cada columna. Algunas veces elegimos mostrar los datos de una manera determinada pero siempre hay una base de datos. Las columnas que tenemos son ID, Marca, Modelo, Cantidad y precio.

Para nuestro ejemplo tenemos pocos elementos pero podemos tener miles, y además estos elementos se pueden ir incrementando, suponiendo que se ingresa un producto nuevo no listado.
Paso 1, tenemos nuestra base de datos en forma de lista. Esta base de datos esta ordenada, como se ve en la figura. Si no es así este método no funciona por lo que deberán ordenarla. Así quedan agrupadas las mismas marcas como se ve. Si son elementos estáticos lo hacen una sola vez y queda listo sino pueden utilizar una macro para hacerlo cada vez que ingresan un dato nuevo, por ejemplo.

Paso 2, tenemos dos celdas en donde se encuentran nuestras litas. En la primera listamos las marcas, en este caso como son 3, podemos escribirlas directamente, como muestra la figura.

La forma de introducir una lista es, en la barra de formulas de la validación de datos introducir los elementos separados por una coma “,”. Para nuestro ejemplo “Audi, BMW, Alfa Romeo”. Por supuesto si son muchos elementos deben buscar otra forma más elegante pero para nuestro ejemplo sirve.

También como ven en la figura, en la configuración de la validación de datos  configuramos un mensaje de entrada para evitar errores. Ya que si cambia la información de la marca siempre debemos verificar la lista de modelos y elegir uno, de otra manera mostraría datos erróneos en la celda de la derecha.

Paso 3, para esta parte vamos a utilizar varias formulas. Para evitarnos los arreglos que vimos con el método clásico.

En la figura se ve una formula, ahora vamos a tratar de explicar los elementos.

Lo que vamos a hacer es usar una formula muy útil de Excel por la cual podemos hacer referencia a un rango de celdas teniendo en cuenta una celda o un rango. Es como tomar como referencia una celda para poder encontrar otras.

La función más importante que vamos a usar “DESREF” o referencias relativas. La función Desref sirve para referenciar un rango de celdas, de acuerdo a otro rango u otra celda.

Si no entienden esta fórmula no se preocupen, hay muchos sitios en internet donde pueden encontrar mas info.

Los datos que necesitamos saber son los siguientes:

·         Coincidir: cuando elegimos un modelo, cómo tenemos en la base de datos ordenada todos los autos de la misma marca agrupados, podemos encontrar la posición relativa del primer elemento que coincide con el modelo que elegimos. Tener en cuenta el rango completo de datos de la base de datos. El dato encontrado sería el que se ve con bordes rojos  y punteados.

·         Contar si: nuevamente al tener agrupados los elementos de la misma marca, podemos saber cuanto son con la formula contar.si. Tener en cuenta el rango completo de datos de la base de datos.

·         Dirección: lo que hacemos con esta formula es construir la dirección de donde se encuentra el primer dato que nos interesa. El dato que esta a la derecha del dato encontrado con coincidir, el primer dato de la lista, que sería nuestra variable. El resto de los datos de la función DIRECCIÓN se mantendría constante para la misma base de datos.

·         Indirecto: esta función la vamos a utilizar dentro de la validación pero la mostramos igual para que vean como funciona. Una vez construida la dirección con INDIRECTO vemos el dato que tiene esa dirección, indirectamente.                Como habíamos dicho, es el primer dato de la lista de modelos que nos interesa.

·         Desref: por ultimo vamos a utilizar nuestra función principal. La formula tiene como primer parámetro una REF, la referencia que vamos a utilizar es el indirecto que ya habíamos hablado, que tiene en cuenta la dirección del primer dato de la lista de modelos. Como ese mismo dato es tomado como referencia, la fila y la columna son “0”. Luego especifico el alto que es lo que obtuvimos con el Contar.Si y el ancho es de una columna. De esta manera el resultado de esta formula, si se pudiera mostrar, sería lo que esta sombreado en naranja. En una celda no lo puede mostrar, a menos que sea como dato de otra formula, pero lo vemos como una lista en la validación.

Con todos los elementos vistos podemos construir una aplicación interesante. Esperamos que les sirvan y le gusten las ideas.

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:

·         Lista dependiente de Validación de datos.

·         Listas dependientes con Desref.

·         Listas de Validación Dinámicas Dependientes con Excel

·         Excel Dependent Data Validation lists

·         Excel Dependent Dropdowns from a Sorted List

·         Use validation to create dependent lists – Excel

·         Dynamic Dependent Drop Down List in Excel