Cómo implantar Validación de datos de dos niveles

Uno de los problemas con el que nos encontramos al trabajar con listas de validación de datos es la organización de los elementos. De forma habitual nos limitamos a seleccionar el rango de celdas que queremos que aparezca en la lista sin dar importancia al orden en el que se encuentran. Esto no importa cuando los elementos de la lista son pocos. Cuando son un número considerable o tienen varios niveles de organización resulta más complicado encontrarlos.

Hoy te enseñaré a crear una validación de datos organizada en dos niveles para que encontrar el elemento buscado sea más fácil.

1. Disposición de los datos

Supón que tienes una lista con las tres ciudades más pobladas de los países más poblados de Europa. Para que la validación de datos funcione debes tenerlos en el siguiente formato:

Si utilizas una tabla en lugar de un rango, mejor que mejor. Así podrás aumentar la lista de ciudades sin modificar ningún otro parámetro. (La columna de población no es necesaria. La he puesto simplemente por aportar algún dato más.)

Consejo: En cuanto crees una tabla o una tabla dinámica te recomiendo que le cambies el nombre (la del ejemplo se llama ‘Validación’).

Si necesitas ayuda para crear la tabla echale un vistazo a este artículo:

• Tablas de Excel para principiantes

2. Crear una tabla dinámica a partir de los datos

A continuación hay que crear una tabla dinámica a partir de la tabla anterior.

Simplemente sitúate encima de la tabla anterior y haz clic en la ficha Insertar > Tabla dinámica:

Para ver más fácilmente el funcionamiento colocala en la misma hoja que los datos de la tabla original (en la práctica real no lo aconsejo, es mejor tener la tabla dinámica en un lugar donde no afecte al resto de datos en caso de utilizar filtros).

Sitúa los campos ‘País’ y ‘Ciudad’ en el área de Filas.

Ahora hay que modificar la disposición de los datos. Haz clic en cualquier parte de la tabla dinámica. A continuación dirígete a la ficha contextual Diseño, haz clic en el botón Diseño de informe y selecciona la opción Mostrar en formato de esquema.

Ahora oculta los totales generales de la tabla.

¿Por qué esta disposición de datos y no la que viene por defecto? Gracias a esta forma de colocar los datos podrás mostrar las ciudades con una sangría debajo de su país.

¿Necesitas ayuda para crear la tabla? Haz clic aquí para ver un tutorial.

3. Extraer la lista mediante una fórmula

Ahora que ya tienes la tabla en el formato adecuado hay que extraer la información en una lista. Para ello tienes que usar dos fórmulas muy simples:

Para extraer todos los datos

Observa la fórmula:

=SI(F3<>””;F3;SI(G3<>””;REPETIR(” “;5)&G3))

La fórmula pregunta si F3 no se encuentra vacía.

Si no está vacía la fórmula devuelve el valor de F3.

Si se encuentra vacía devuelve el valor de la celda de la derecha aplicándole una sangría de cinco espacios.

Introduce la fórmula anterior en la celda donde quieras comenzar la lista y, a continuación arrástrala hacia abajo hasta que consideres oportuno (con 50 filas será más que suficiente, aunque dependerá de la extensión de la tabla).

Como puedes ver en la siguiente imagen las celdas inferiores muestran el valor FALSO, que indica que la tabla ha finalizado.

Obviamente no querrás que en la lista de validación aparezca la palabra FALSO, por tanto hay que eliminarla con la función FILTRAR.

Para eliminar las celdas vacías

Eliminar las celdas con FALSO es sencillo. Observa la fórmula:

=FILTRAR($J$3:$J$52;$J$3:$J$52<>FALSO)

La función devuelve el contenido del rango de la columna J siempre que el valor de la celda no sea FALSO. El resultado es una matriz dinámica, de la cual te aprovecharás en el último paso.

4. Establecer la validación de datos

El último paso es aplicar la validación a una o varias celdas, por ejemplo a M3.

Selecciona la ficha Datos > Validación de datos. A continuación selecciona Lista como en el desplegable Permitir y en el cuadro Origen escribe =K3#.

El símbolo # indica que la validación debe tener en cuenta toda la matriz que comienza en la celda K3 (el recuadro azul de la imagen anterior).

Si quieres profundizar más en la validación de datos haz clic en los siguientes artículos:

• Validación de datos para principiantes
• Cómo crear una validación de datos
• 4 ejemplos de validación de datos con fórmulas

El resultado final debe ser similar a este:

Si has seguido los cuatro pasos (o si has descargado el archivo en el pie del post) puedes jugar a introducir nuevos países o nuevas ciudades, hacer modificaciones, eliminar datos, etc. Verás que siempre funciona tras actualizar la tabla dinámica.

Conclusión

Esta solución es una adaptación de la dada por un usuario del foro de Ayuda Excel a una consulta similar. Como puedes imaginar es posible anidar tres, cuatro o los niveles que necesites haciendo las modificaciones oportunas a la fórmula del paso 3.

¿Te ha sido de utilidad? ¿Lo podrás aplicar a tus tareas diarias? Házmelo saber en los comentarios.