En esta oportunidad vamos a enseñarte dos ejercicios prácticos y útiles para utilizar la función DESREF. Si quieres conocer más a fondo esta función dale clic aquí…

En el primero aprenderás a actualizar una lista despegable automáticamente, por otro lado, el segundo ejercicio es muy interesante porqué podrás crear listas despegables dependientes de valores presentes en otras listas.

Actualizar automáticamente listas despegables

Las listas despegables son herramientas muy utilizadas en tanto labores diarias administrativas como operativas, por tanto, conocerlas y utilizarlas es vital para realizar tus labores con éxito. Si quieres saber más de las listas despegables da clic en este anuncio….

Para actualizar una lista despegable vamos a apoyarnos de fórmulas, están van a detectar cualquier modificación en el rango sobre el cual se basa nuestra lista despegable y por tanto se actualizará automáticamente.

Queremos crear una lista a partir de los países presentes en esta tabla, pero periódicamente estarán añadiéndose nuevos registros. Por ejemplo, aquí se aumentaron dos países:

Pasos para crear una lista despegable automática

1. Como dije antes nos apoyaremos de una fórmula, vamos a utilizar la función DESREF para reconocer los datos de la tabla.
2. Debemos reconocer el inicio de nuestro rango, en este caso B3 – no queremos que se desplace hacia ninguna fila o columna por eso tendrán valor 0 ambas – sin embargo, vamos a requerir del argumento ALTO para seleccione el número de celdas resultante de la función CONTARA. Lo sé es confuso, por eso te explicaré paso a paso la cronología de esta fórmula:

Sintaxis función DESREF: =DESREF(Ref, Filas, Columnas, [Alto], [Ancho])

Ref (B3) –> Celda de inicio para la selección.

Filas y columnas (0) –> Queremos mantener la selección en dicha celda, por eso indicamos 0 filas y columnas de movimiento.

[Alto] CONTARA(B:B) -1 –> Vamos a determinar el número de celdas no vacías dentro de la

columna B y luego le restaremos uno para no considerar el título.

 Fórmula final:  =DESREF(B3,0,0,CONTARA(B:B)-1)

Como puedes ver, hemos utilizado también la función CONTARA, lo hacemos porque necesitamos contar las celdas no vacías dentro de toda la columna B.

Entonces ubica el inicio del rango, queda congelada la selección en B3, calcula y le resta 1 al número de celdas no vacías dentro la de la columna B, por ultimo desde la celda de inicio selecciona hacia abajo el mismo número de celdas calculado previamente y las selecciona.

3. Una vez acabada la fórmula que nos devolverá el rango de celdas adecuado, podemos iniciar con la creación de la lista desplegable.

Pestaña datos –> Validación de datos

Se mostrará el cuadro de diálogos de validación de datos y dentro de la pestaña configuración seleccionamos la opción Lista e ingresamos la fórmula en la barra inferior de la ventana.

Y listo ya tenemos nuestra lista despegable, para actualizarla bastará con agregar elementos a la lista lo mismo sucederá si eliminas alguno, la única condición es que los datos sean continuos, es decir no deben existir celdas en blanco dentro del rango.

De esta manera la función DESREF nos ayuda a obtener el rango de celdas que contienen los elementos que formarán parte de nuestra lista desplegable en Excel.

Crear listas desplegables dependientes de otras listas

Para el segundo ejercicio práctico, vamos a crear las típicas listas desplegables dependientes entre sí, o sea el contenido de una dependerá del valor seleccionado en la anterior. Para esto utilizaremos las funciones DESREF, COINCIDIR y CONTAR.SI para vincular nuestras listas.

Seguiremos utilizando países como el ejercicio anterior pero ahora vamos a clasificarlos por los continentes Asia, Europa, América y África. Ya estos datos están cargados en una lista despegable. Una vez que se elija un continente de la primera lista, la segunda mostrará los países que pertenecen a dicho continente.

Pasos para crear listas dependientes

1. Como indicamos, necesitaremos de las funciones DESREF – COINCIDIR – CONTAR.SI y de una lista despegable extra para lograr nuestro objetivo.
2. Debemos seleccionar la celda donde empezará a recorrer, o sea E3 – reconocer el inicio del rango correspondiente a la selección de la lista en G2, quedarnos en la misma columna, sin embargo, vamos a requerir del argumento ALTO para seleccionar el número de registros correspondientes al valor seleccionado en G3.

Sintaxis función DESREF: =DESREF(Ref, Filas, Columnas, [Alto], [Ancho])

Explicación de la fórmula:

Ref (E3) –> Ubicación de inicio.

Fila (COINCIDIR(G2,D3:D20,0)-1) –> Calcula la posición de inicio de los registros correspondientes al valor seleccionado en G3, luego el número de posición será utilizado para desplazarse tantas filas hacia abajo.

Columnas (0) –> Queremos mantenernos en dicha columna, por eso indicamos O columnas de movimiento.

[Alto] CONTAR.SI(D3:D20,G2) –> Contar cuantos registros coinciden con el valor seleccionado en G3, luego el número calculado  será utilizado para seleccionar las filas pertenecientes al rango final.

 =DESREF(E3,COINCIDIR(G2,D3:D20,0)-1,0,CONTAR.SI(D3:D20,G2))

Entonces la formula se posiciona en E3, con ayuda de coincidir se moverá hasta el inicio de los registros correspondientes al valor seleccionado en G3, se queda en la misma columna y por ultimo selecciona todos registros correspondientes al valor de la primera lista.

3. Una vez acabada la fórmula que nos devolverá el rango de celdas adecuado podemos iniciar con la creación de la lista desplegable.

Pestaña datos –> Validación de datos

Se mostrará el cuadro de diálogos de validación de datos y dentro de la pestaña configuración seleccionamos la opción Lista e ingresamos la fórmula en la barra inferior de la ventana y listo tenemos nuestra lista despegable dependiente.

Y listo ya tenemos nuestra lista despegable dependiente.

Llegamos al final de este post y como habrás notado no es nada complicado realizar esto, toma un poco de tiempo, pero con la práctica lo manejarás al revés y al derecho. Además, recuerda que podría facilitarte tus labores y ahorrar tiempo valioso para el desarrollo de otras.

 

Olvidaba decirte…Si deseas implementar este u otro desarrollo a tu medida solo debes escribirnos… ¡Es fácil y rápido! Debajo se encuentra tanto el e-mail como nuestro número de contacto… ¡Un placer en ayudarte!