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

El resultado del artículo de hoy es una lista de validación de datos organizada en dos niveles. Una forma inusual pero efectiva de ordenar los elementos de una lista.
Validación datos 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:

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:

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.

Validacion-de-datos-dos-niveles
Título: Validacion-de-datos-dos-niveles (0 clics)
Tamaño: 16 KB

2 comentarios en “Cómo implantar Validación de datos de dos niveles”

  1. Muy bueno Sergio. Gracias
    De paso consulto: ¿No hay alguna manera de que las fórmulas adapten la extensión del rango afectado de manera dinámica? Es decir, que la primera que usted extiende hasta 50 filas hacia abajo (y entonces devuelve FALSO en muchas filas) directamente se aplique a la longitud del rango que corresponda y no a un número fijo definido (50 en este caso)?

    1. ¡Hola Pablo!
      En la publicación hablo de ello. Simplemente transforma el rango de celdas en una tabla y haz referencia al nombre de la tabla. Si aumentan los registros, aumentarán también los elementos de la validación. Eso sí, ten en cuenta que si dejas un registro de la tabla vacío, en la validación aparecerá el hueco en blanco.

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

Sergio

Sergio

La destreza y el perfeccionismo quizá sean las dos virtudes que me permiten ayudar a mis clientes a facilitar las tareas administrativas de sus negocios.

Descarga la plantilla de calendario anual 2022

Gratis

Introduce tu nombre y tu correo para descargarla

INFORMACIÓN BÁSICA SOBRE PROTECCIÓN DE DATOS

 

Responsable: Sergio Andrés Celemín

Finalidad: Gestionar y enviar información de boletines y promociones a través de correo electrónico.

Legitimación: Consentimiento del interesado.

Destinatarios: Tus datos se encuentran alojados en mi hosting Hetzner Online GmbH.

Derechos: Puedes ejercitar en cualquier momento tus derechos de acceso, rectificación, supresión, oposición y demás derechos legalmente establecidos a través del email sergio@ayudaexcel.com.

Información adicional: Encontrarás más información en la política de privacidad.

Descarga la plantilla de calendario compacto 2022

Gratis

Introduce tu nombre y tu correo para descargarla

INFORMACIÓN BÁSICA SOBRE PROTECCIÓN DE DATOS

 

Responsable: Sergio Andrés Celemín

Finalidad: Gestionar y enviar información de boletines y promociones a través de correo electrónico.

Legitimación: Consentimiento del interesado.

Destinatarios: Tus datos se encuentran alojados en mi hosting Hetzner Online GmbH.

Derechos: Puedes ejercitar en cualquier momento tus derechos de acceso, rectificación, supresión, oposición y demás derechos legalmente establecidos a través del email sergio@ayudaexcel.com.

Información adicional: Encontrarás más información en la política de privacidad.

Descarga la plantilla de calendario cuatrienal 2022

Gratis

Introduce tu nombre y tu correo para descargarla

INFORMACIÓN BÁSICA SOBRE PROTECCIÓN DE DATOS

 

Responsable: Sergio Andrés Celemín

Finalidad: Gestionar y enviar información de boletines y promociones a través de correo electrónico.

Legitimación: Consentimiento del interesado.

Destinatarios: Tus datos se encuentran alojados en mi hosting Hetzner Online GmbH.

Derechos: Puedes ejercitar en cualquier momento tus derechos de acceso, rectificación, supresión, oposición y demás derechos legalmente establecidos a través del email sergio@ayudaexcel.com.

Información adicional: Encontrarás más información en la política de privacidad.

Descarga la plantilla de calendario idiomas 2022

Gratis

Introduce tu nombre y tu correo para descargarla

INFORMACIÓN BÁSICA SOBRE PROTECCIÓN DE DATOS

 

Responsable: Sergio Andrés Celemín

Finalidad: Gestionar y enviar información de boletines y promociones a través de correo electrónico.

Legitimación: Consentimiento del interesado.

Destinatarios: Tus datos se encuentran alojados en mi hosting Hetzner Online GmbH.

Derechos: Puedes ejercitar en cualquier momento tus derechos de acceso, rectificación, supresión, oposición y demás derechos legalmente establecidos a través del email sergio@ayudaexcel.com.

Información adicional: Encontrarás más información en la política de privacidad.

Descarga la plantilla de calendario lineal 2022

Gratis

Introduce tu nombre y tu correo para descargarla

INFORMACIÓN BÁSICA SOBRE PROTECCIÓN DE DATOS

 

Responsable: Sergio Andrés Celemín

Finalidad: Gestionar y enviar información de boletines y promociones a través de correo electrónico.

Legitimación: Consentimiento del interesado.

Destinatarios: Tus datos se encuentran alojados en mi hosting Hetzner Online GmbH.

Derechos: Puedes ejercitar en cualquier momento tus derechos de acceso, rectificación, supresión, oposición y demás derechos legalmente establecidos a través del email sergio@ayudaexcel.com.

Información adicional: Encontrarás más información en la política de privacidad.

Descarga la plantilla de calendario semanal 2022

Gratis

Introduce tu nombre y tu correo para descargarla

INFORMACIÓN BÁSICA SOBRE PROTECCIÓN DE DATOS

 

Responsable: Sergio Andrés Celemín

Finalidad: Gestionar y enviar información de boletines y promociones a través de correo electrónico.

Legitimación: Consentimiento del interesado.

Destinatarios: Tus datos se encuentran alojados en mi hosting Hetzner Online GmbH.

Derechos: Puedes ejercitar en cualquier momento tus derechos de acceso, rectificación, supresión, oposición y demás derechos legalmente establecidos a través del email sergio@ayudaexcel.com.

Información adicional: Encontrarás más información en la política de privacidad.

Ebook De 0 a 100 con macros y VBA

De 0 a 100 con macros y VBA

Esta oferta no es para siempre...

¡no la desaproveches!