Vimos que los distintos tipos de listas que podemos utilizar en Excel son:
- Celdas con validación de datos (menú Datos)
- Controles dibujados (menú Desarrollador o Programador)
- Controles en un Userforms.
Entonces, si no queremos utilizar macros en nuestro libro optaremos por el modelo de celdas con Validación de datos.
NOTA: desde el VIDEO pueden observar el paso a paso.
1- Primero vamos a colocar una lista de meses para asignarla a la primer celda, que en el ejemplo es C4. Desde menú Datos, Validación de datos optar por Lista y en Origen seleccionar o ingresar el que corresponda (en el ejemplo es M1:M12)
2- Luego vamos a copiar esa lista de meses y con Pegado especial, Transponer, la ubicaremos a partir de fila 1 para contar con un rango de meses y sus días en forma vertical.
3- Seleccionaremos el rango de títulos (en el ejemplo O1:Z1) y desde el menú Fórmulas, Administrador de Nombres, Nuevo le asignaremos el nombre MESES.
4- Completamos las columnas con días (vencimientos, feriados, cumpleaños, etc)
Por ejemplo, seleccionamos el mes de Abril con sus días y desde menú Fórmulas, Administrador de nombres dejaremos el nombre seleccionado y ajustaremos el rango para que inicie en fila 2. Esto se hará con cada mes de la tabla.
6- Solo nos resta validar la segunda celda, la que será dependiente de la primera. Es decir, que al seleccionar algún mes desde C4, en otra celda nos muestre los días que le corresponden a ese mes.
Aquí necesitaremos de la función INDIRECTO, es decir que busque el contenido de C4 y según eso despliegue el rango de igual nombre.
Observemos entonces el resultado obtenido. Al seleccionar Abril nos mostrará en el siguiente desplegable el rango con ese mismo nombre.
Lo visto hasta aquí es perfecto para listas de rangos fijos, por ejemplo País-Ciudades.
Pero en otros casos quizás necesitemos ir agregando datos a los rangos, en nuestro ejemplo más días en algunos meses.
Por lo tanto necesitaremos rangos dinámicos. Y las modificaciones que realizaremos entonces son, a partir del punto 5:
5b - Dejaremos los rangos asociados con cantidad suficiente de celdas, podría ser hasta 31, 100 o directamente toda la columna:
6b- Utilizaremos para el Origen en la Validación de datos de la celda dependiente, la función DESREF que devuelve la referencia de un rango, que es un número de filas y columnas de una referencia dada.
La función tiene los siguientes argumentos:
=DESREF(ref, filas, col, [alto], [ancho])
filas: es el número de filas, hacia arriba o hacia abajo de la ref.
columnas: es el número de columnas hacia derecha o izquierda de la ref.
[alto]: es un argumento opcional que nos permite indicar la cantidad de filas con datos que tiene la columna.
[ancho]: indica la cantidad de columnas que mostraremos, que como en este caso es 1 sola columna la omitimos.
Nuestra fórmula para la validación de la celda dependiente quedará entonces así, según el modelo de la imagen anterior:
=DESREF(O1;1;COINCIDIR(C4;MESES;0)-1;CONTARA(INDIRECTO($C$4))-1)
ref: O1 que es la 1er celda de la tabla de MESES.
filas: 1 fila hacia abajo porque los datos empiezan en fila 2.
columnas: la buscaremos en el rango MESES según el valor de C4 con la función COINCIDIR
.
COINCIDIR(C4;MESES;0) -1
Como la función COINCIDIR nos devolverá el número de columna dentro del rango MESES, para ENERO nos devolverá 1.
Pero el argumento 'columnas' indica el número de columnas que nos desplazamos a derecha, siendo para ENERO 0 columnas, por eso en la fórmula DESREF restamos 1.
[alto]: buscaremos dentro de la columna la cantidad de celdas ocupadas, utilizando la función CONTARA, donde el rango será el que tenga el nombre del contenido de C4. Entonces si en C4 seleccionamos ABRIL buscará la cantidad de celdas ocupadas en rango de nombre ABRIL y le restamos 1 para omitir el título.
CONTARA(INDIRECTO($C$4))-1
Ver VIDEO N° 8.
Descargar libro con ejemplo desde aquí.
No hay comentarios.:
Publicar un comentario