Curso Validación de datos en Excel – Parte #6 – Listas dependientes – @EXCELeINFO
VER PARTE #5 – VALIDAR EMAIL
Hola a todos. Hemos llegado al final de este curso de Validación de datos en Excel, y para esta último ejercicio les tengo preparado un truco que muchos seguramente han buscado. Haremos dos listas de validación, pero lo genial de este ejercicio es que la segunda lista de validación dependerá del contenido de la primera. Para lograrlo usaremos las siguientes funciones: DESREF, COINCIDIR, CONTAR.SI, y CONTARA.
Ver video Validacion de datos en Excel Parte #6 – Listas dependientes
Suscríbete al canal de EXCELeINFO en YouTube para aprender más de Excel y macros.
El ejercicio
Tenemos una lista de personas que han ganado el premio Nobel, separadas por categoría. Deseamos que al elegir de una lista la categoría, en otra lista de muestren los ganadores del galardón.
Figura 1. Ganadores del premio Nobel por categoría.
Obtener lista de categorías (registros únicos).
Para obtener una lista de registros únicos de la columna Categoría lo haremos con Filtro Avanzado, siguiente los siguientes pasos:
1. Elegimos la tabla de ganadores.
2. En la pestaña Datos elegimos Avanzadas.
3. En el formulario validamos que el rango elegido sea [$A$1:$G$1029].
4. Rango de criterios [$A$1].
5. Seleccionamos Copiar a otro lugar y seleccionamos [$F$8].
6. Marcamos la opción solo registros únicos.
7. Aceptar.
Figura 2. Se copian registros únicos en base a la columna Categoría.
Primer Lista de validación (Categorías).
En el paso anterior vimos cómo obtener registros únicos de las categorías. Ahora les mostraré una fórmula para obtener una lista en base a las categorías y cada vez que se añaden alguna categoría a dicha lista, la lista de validación la tomará en cuenta ya la mostrará. Aunque la fórmula la muestro en una celda, recordemos para pasar fórmulas a Validación de datos, es recomendable primero probarlas en celdas.
La fórmula que usaremos para nuestra primera lista de validación será:
=DESREF(F8,1,0,CONTARA(F:F)-1)
Figura 3. Ésta fórmula se usará para obtener nuestra primera lista de validación.
Ahora que probamos que la fórmula funciona seguimos los siguientes pasos para crear la primera lista de validación:
1. Seleccionamos la celda [D3].
2. En la pestaña Datos elegimos Validación de datos.
3. En Permitir elegimos Lista.
4. Pegamos la fórmula anterior.
5. Aceptar.
Figura 4. Asignar fórmula como Lista de validación.
Segunda lista de validación Nombre de ganadores en base a categoría
Ahora toca obtener el nombre de los ganadores de cada Premio Nobel en base a la categoría elegida en la primera Lista de validación. Dejaremos claros algunos hechos para entender mejor la fórmula que usaremos.
Hecho 1. La tabla de Galardones se encuentra en la columna A y columna B.
Hecho 2. La primer Lista de validación se encuentra en el rango [D3].
Para la segunda lista de Validación usaremos la siguiente fórmula, considerando los 2 hechos anteriores:
=DESREF(A1,COINCIDIR(D3,A:A,0)-1,1,CONTAR.SI(A:A,D3),1)
Nota: Si la anterior fórmula la usamos en una celda, hay que considerar que es una fórmula matricial por lo que para aceptar la fórmula debemos presionar [Ctrl] + [Shift] + [Enter].
Figura 5. Fórmula para segunda Lista de validación.
Lo mágico
Por último toca comprobar la magia de éstas fórmulas. Vamos a añadir la categoría de Excel (sólo para pruebas).
Figura 6. Añadimos una nueva categoría.
La misma categoría la añadimos a nuestra lista de categorías (para la primer lista de validación).
Figura 7. Se añade la categoría Excel a la lista de categorías.
Y ahora vemos cómo se agrega la categoría EXCEL a la primer lista de validación y se rellena la segunda Lista de validación.
Figura 8. Lista de validación dependientes en Excel.
Descargar el archivo de ejemplo
EXCELeINFO – Validación de datos en Excel #6 – Listas dependientes.rar
