Desplegable que se actualiza automáticamente

 La utilización de desplegables en una hoja de cálculo tienen una gran importancia por varios motivos:

• Una facilita la introducción de datos, 
• Segunda, garantiza que un determinado dato siempre se escriba de una determinada forma, lo cual es muy importante si luego vamos a utilizar funciones de búsqueda.

Seguramente muchos ya sabéis como hacer un lisa desplegable en Excel, (si no es así, no hay ningún problema, lo vemos ahora mismo), pero el objeto de este post es comentar como hacer que esta lista se actualice de forma automática cuando se incorpore un nuevo valor; y sobre todo, para los usuarios de Microsoft 365, como hacerlo de una forma muy rápida.

Datos de partida.

Contamos con los siguientes datos de un centro de formación (puedes descargarte el archivo aquí)

Y queremos hacer una desplegable, que posteriormente utilizaremos en un cuadro de control, que nos permita seleccionar el nombre de uno de los cursos impartidos por el centro, para ver todos los datos relacionados con ese curso.

Crear un desplegable.

Si no lo has hecho nunca o no lo recuerdas, ahora verás que sencillo es:

• Nos situamos en la celda donde queremos que se muestre el desplegable:
• Abrimos la pestaña datos y en el bloque herramienta de datos seleccionamos el comando validación de datos

• En la venta emergente que nos aparece abrimos el desplegable que hay lado del texto "Cualquier valor", y presionamos en la opción Lista.

• En el apartado origen tendremos que seleccionar el rango de datos que queremos conforme nuestra lista. Y presionamos el botón Aceptar.

Una vez hecho en esa celda tan sólo se podrán escribir o seleccionar los datos que existan en ese rango de datos.

Pero, si selecciono el rango de datos que se indica en la imagen anterior (toda la columna de cursos), el desplegable me mostrará cursos repetidos, pues en dicha columna no hay datos únicos.

Con lo cual para conseguir un desplegable con elementos únicos deberíamos depurar primero el origen de datos en un lugar aparte, no puedo hacerlo en la propia tabla de datos alumnos y cursos, pues perdería información.

Depurar datos.

Siguiendo con el ejemplo, vamos a copiar los datos de la columna de cursos en otra parte de nuestro libro de calculo, puede ser en la misma hoja o en una hoja nueva.

Para ello seleccionamos toda la columna "Cursos", como se trata de una tabla, al poner el cursor encima del encabezado "Cursos", veréis que la forma del cursor cambia a una flecha de color negro.

En ese momento, haciendo un clic con el ratón (botón izquierdo), queda seleccionada la columna.

A continuación CTRL+C, o botón derecho y copiar

Nos vamos a la celda donde lo queremos pegar, y CTRL+V, o botón derecho y pegar.

Una vez pegada y estando seleccionados todos los datos, desde la pestaña Datos, en el bloque de herramientas de datos, hacemos clic en el comando Quitar duplicados.

Si no tiene encabezados, como es mi caso, simplemente Aceptar. Si los tuviera, marcamos la opción y aceptamos.

Se nos informa del número de valores duplicados que ha encontrado y quitado. Nos reduce la listado a valores únicos.

Ahora tan solo nos queda cambiar el origen de datos del desplegable que habíamos construido.

Volvemos a la celda donde está el desplegable de nuevo pestaña Datos, bloque Herramientas de datos y utilizamos el botón Validación de datos.

En la ventana que parece quitamos el origen anterior y seleccionamos el nuevo rango de datos (ahí dónde sólo hay valores únicos) y aceptamos.

Nuestra lista desplegable ya muestra sólo valores únicos.

Pero, que pasa si el centro añade en la tabla un nuevo curso. ¡Nuestro desplegable no lo contempla!

Podríamos hacerlo manualmente: añade el nuevo curso al listado de datos únicos vuelve a modificar el desplegable,... Uaahhh, que rollo....

Desplegable que se actualiza automáticamente

Si no dispones de Microsoft 365, ahora te cuento como solucionarlo. En caso contrario, sáltate una cuantas líneas y verás que fácil es.

No dispongo de Microsoft 365

En este caso, lo primero que haremos es:

• Vamos a la columna donde tenemos el origen: "la relación de cursos únicos".
• Si no tiene encabezado, crea uno.
• Una vez creado colócate en la celda abajo del encabezado.
• Abre la pestaña Insertar, y clica en el comando crear Tabla. 

• Marcamos que La tabla tienen encabezados y aceptamos

• Ahora seleccionamos los datos de la tabla que hemos creado, sin tomar el encabezado.

• Nos situamos en el cuadro de nombres (en la esquina izquierda superior de la zona de trabajo.

• Dónde pone Tabla2, clicamos, y le asignamos un nombre (no tiene que tener espacios) por ejemplo: Miscursos. Y una vez escrito presionamos la tecla Enter.
• Hecho esto vamos a la celda donde tenemos el desplegable y hacemos de nuevo en el comando validación de datos.
• Eliminamos el rango que tenemos en el apartado origen y en lugar de un rango ponemos = y el nombre que hemos escrito anteriormente ("Miscursos"), o presionado la tecla F3 (con la tecla Función "Fn" presionada, si dispones de esa tecla), nos muestra la siguiente ventana:

• Marcamos el nombre, y aceptamos.

• Nuevamente aceptamos

Hagamos un prueba, le añadimos a la tabla de cursos únicos, un nuevo curso "Gestión de almacén", para ellos nos ponemos debajo del último registro y los escribimos. Al salir de la celda, verá que el registro se incluido dentro de la tabla. Prueba ahora el desplegable y verá como te muestra ese último curso.

Pero aún no está del todo automatizado, pue si incluimos un curso nuevo en la tabla de alumnos y cursos, luego tenemos que tenemos que escribirlo de nuevo en la tabla de cursos únicos. Para evitarlo, lo mejor será que todas las celdas de la columnas cursos de la tabla de alumnos y cursos se cumplimente con desplegables. De este modo, tan sólo tendremos que escribir el curso nuevo en la tabla de cursos únicos. Para ello.

• Seleccionamos todas las celdas de la columna Cursos de la Tabla de alumnos y cursos. 
• Accedemos al botón validación de datos
• Creamos una lista y como origen escribimos =Miscursos.

Ahora en la Tabla de alumnos y cursos, cada vez que nos situemos en un celda de la columna Cursos, veréis que se muestra el símbolo para abrir un desplegable.

Además cuando incorporemos un nuevo registro a la tabla de alumnos cuando tengamos que cumplimentar el campo curso ya podremos utilizar el desplegable

Trabajo con Microsoft 365

Enhorabuena, ahora verá que sencillo es.

Función UNICOS()

Sitúate en la celda donde quieres que se muestren el listado de cursos únicos Y ESCRIBE:

Los parámetros encerrados entre corchetes son opcionales:

Matriz. Es el rango o Tabla del que deseamos obtener los valores únicos.

[by_col]. Parámetro opcional que nos indica si deseamos comparar por fila (FALSO) o por columna (VERDADERO).

[exactly_once]. Parámetro opcional que nos permite elegir entre si deseamos mostrar los valores que ocurren sólo una vez (VERDADERO) o incluir todos los valores únicos (FALSO).

En nuestro caso solo vamos a indicar la matriz, la columna Cursos. Nos situamos encima del encabezado cursos:

• Hacemos clic con el botón derecho del ratón.
• Cerramos paréntesis y Enter

=UNICOS(Tabla3[Curso])

Veremos como se muestran los cursos sin repeticiones

Si te fijas, verás que cuando estas en la la primera celda "Administrativo contable", la función que se ve en la barra de formulas aparece de color negro, mientras que si te sitúas en cualquiera de las otros registros que están por debajo, esta se muestra de un color grisáceo.

Esto es por que la fórmula sólo está en esa primera celda.

Si además, queremos que nos muestre el nombre de los cursos ordenados alfabéticamente, antes de la función UNICOS(), escribimos la función ORDENAR(), de este modo:

=ORDENAR(UNICOS(Tabla3[Curso]))

Y el listado nos quedará de este modo:

Vamos ya ha crear ese desplegable. No situamos en la celda donde queremos hacer el desplegable. Clicamos en el botón Validar datos

Elegimos la opción de Lista y en origen, marcamos la celda donde hemos escrito nuestra función, seguida de #

Si sólo marcamos la celda de la formula, el desplegable sólo muestra el primer registro. Si se nos ocurre marcar como origen todo el rango de cursos, al añadir nuevos cursos y el rango crecer, el desplegable no los verá. Por eso, al añadirle el símbolo # le estamos diciendo que tenga en consideración todos los datos que arroja la función.

Hagamos una comprobación. Nos situamos al final de la tabla de alumnos y cursos y escribimos un nuevo registro:

Veamos que le pasa a los datos que arroja nuestra función =ORDENAR(UNICOS()) y el desplegable:

Automáticamente se ha incorporado el nuevo curso "Secretariado internacional", y además aparece ordenado alfabéticamente.

Sencillo¡¡¡ ¿verdad?

Si te ha sido útil y crees que le puede interesar a alguien compártelo y, si todavía no te has suscrito, no olvides hacerlo para recibir notificación de nuevas entradas.