Esta es una forma fácil y divertida de crear una lista de validación de datos de dos niveles o hasta más dentro de Excel.
Aquí puedes ver la tabla de muestra que se usará para este ejemplo y que nos servirá para jugar con los datos o fórmulas para poder aprender más.
Puedes descargar el libro aquí mismo.
Lo primero será preparar nuestra fuente de datos, por lo que vamos a necesitar tener datos en este formato para usar esta técnica de dos niveles desplegables.
Preprocesamiento en dinámica.
Continuaremos creando una tabla dinámica en un diseño de esquema. Insertaremos una tabla dinámica desde nuestra validación de datos en formato de esquema con nivel 1 y nivel 2 en el área de etiquetas de fila. Deshabilitaremos cualquier gran total y subtotales, lo que nos dará esto:
Ahora lo que necesitamos son fórmulas para obtener la estructura de dos niveles.
Nos referiremos a los objetos de la tabla dinámica para generar la estructura de dos niveles. Vamos a necesitar dos fórmulas, las cuales serán simples de escribir.
Función IF y los niveles.
La primera será una fórmula IF, la lógica usada es la siguiente:
- If, si la columna no está en blanco, entonces obtendrá ese valor
- Else, si Ciudad tampoco está en blanco, prefijará cinco espacios al valor de ciudad y regresará eso
- Else, regresará FALSE
La siguiente fórmula nos servirá:
=IF(E3<>””,E3,IF(E3<>””,REPT(” “,5)&E3))
Nuestra tabla dinámica es desde E3. Arrastraremos esta fórmula abajo tantas filas como sea el tamaño de nuestros datos.
Función FILTER y los excluidos
La segunda fórmula, FILTER, excluirá todos los valores FALSE.
En un rango de cincuenta celdas, esta fórmula va a remover todos los valores FALSE. La síntaxis que usaremos será esta:
=FILTER($H$3:$H$53,$H$3:$H$53<>FALSE)
Aquí tenemos una ilustración que explica ambas fórmulas.
Reglas de validación de datos
Solo nos queda establecer la regla de validación. Solo seleccionaremos la celda en donde queremos que aparezca el menú desplegable de la validación de datos de dos niveles y usaremos la pestaña «Data» y el botón de «Validación».
Estableceremos el tipo de validación como «lista» y usaremos el rango de split como lista. Por ejemplo, en mi libro de trabajo la fórmula de filtro está en la celda P27, por lo que la lista DV será $P$27#.
Y solo como extra, podemos añadir formato condicional para que nos muestre errores. Aun cuando nuestra validación de dos niveles ya es muy útil, podemos usar formato condicional para mostrarnos errores cuando el usuario elija un objeto «nivel 1» en lugar de uno «nivel 2».
Que es algo como esto:
Para establecer la regla de formato condicional debemos hacer lo siguiente:
- En la celda adyacente a la celda de validación, escribiremos una fórmula IF que nos regresará un ícono de palomita o cruz con un mensaje.
=IF(LEFT(P3,1)=" ","P","O")
- Colorearemos la letra en color rojo.
- Ahora aplicaremos la regla de formato condicional en la celda para que convierta el color en verde si lo que tenemos es una palomita.
Y en lugar de tener cinco espacios, podemos usar formas con forma de cuadrados como «∟⊢» para crear la impresión de una estructura de árbol, que se vería algo así:
Aunque es una herramienta vieja, siempre existen nuevas formas creativas de presentar la validación de datos en Excel, y tus controles son parte fundamental de un libro dinámico.
Cualquier duda nos leemos abajo.
