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.

Ganadores del premio Nóbel por categoría.

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.

Se copian registros únicos en base a la columna Categoría.

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)

Ésta fórmula se usará para obtener nuestra primera lista de validación.

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.

Asignar fórmula como Lista de validación.

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].

Fórmula para segunda Lista de validación.

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).

Añadimos una nueva categoría.

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).

Se añade la categoría Excel a la lista de categorías.

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.

Listas de validación dependientes en Excel

Figura 8. Lista de validación dependientes en Excel.

Descargar el archivo de ejemplo

Descargar el ejemplo EXCELeINFO – Validación de datos en Excel #6 – Listas dependientes.rar

VER PARTE #5 – VALIDAR EMAIL

You may also like...

  • willy

    gracias por sus clases, son muy completas y utiles.
    como copiar datos de excel (numericos) que esten entre paragrafos o palabras, sin copiar textos y sin correr el raton por todo la pagina.