Listas desplegables con valores dependientes

martes, febrero 22, 2011

Supongamos que queremos crear un formulario en Excel con dos celdas: hora de comienzo y hora de finalización. Las horas las elegimos de celdas desplegables, que creamos fácilmente con validación de datos. Pero en nuestro modelo queremos que las horas que aparezcan en la la celda “hora de finalización” dependan de la hora elegida en la celda “hora de comienzo


Para lograr que la lista desplegable de la celda “Hasta” se adapte a la hora introducida en la celda “De” tenemos que usar rangos dinámicos que crearemos con la función DESREF.

Los pasos para crear este modelo:



En el rango A3:A26 definimos las horas del día (en este ejemplo usamos una escala de una hora, pero podemos usar medias horas o cualquier otra escala). Definimos el nombre “rngHorarioSencillo” que se refiere a este rango.

Definimos el nombre “celdaDeSencillo” que se refiere a la celda E4.

En la celda C8 ponemos la fórmula

=COINCIDIR(celdaDeSencillo,rngHorarioSencillo,0)

y creamos el nombre “controlHora2Sencillo” que se refiere a la celda C8. Como alternativa podemos crear un nombre que contenga la fórmula en lugar de ponerla en una celda. En mi opinión, el uso de celdas nos permite crear modelos más claros y fáciles de administrar.

Para crear la lista desplegable de la celda “De” usamos el nombre “rngHorarioSencillo”



Para crear la lista desplegable en la celda “Hasta” usamos un rango dinámico definido por esta fórmula:

=DESREF(sencillo!$A$3,controlHora2Sencillo,,CONTARA(rngHorarioSencillo)-controlHora2Sencillo)

Otro detalle que podemos agregar es un evento que borre el contenido de la celda “Hasta” cuando cambiamos el valor de la celda “De”. En el módulo Vbe de la hoja ponemos este código

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = Range("celdaDeSencillo").Address Then
        Target.Offset(0, 1).ClearContents
    End If
End Sub



Finalmente ocultamos las columnas A y B.


Un caso más complejo es cuando queremos que el modelo incluya varias líneas donde la hora de comienzo de una línea depende de la hora de finalización de la línea anterior


En este caso emplearemos prácticamente la misma técnica, pero en lugar de celdas de control incluiremos las fórmulas en nombres.

En la primer celda del formulario definimos la lista desplegable con el nombre “rngHorario”, que se refiere a todo el rango de horas



Ahora tenemos que definir los controles para las celdas “De” y “Hasta”. En el caso de “De” tenemos que referirnos a la celda “Hasta” inmediata anterior (la que se encuentra una fila por arriba). Esto lo hacemos definiendo la fórmula en un nombre pero asegurándonos que la referencia a la fila sea relativa (sin el signo $).

Al definir el control para el campo “De” seleccionamos la celda D5 y definimos el nombre “hora1Ref” con esta fórmula

=COINCIDIR(complejo!$E4,rngHorario,0)

Para definir el control para el campo “Hasta” seleccionamos previamente la celda E4 y definimos el nombre “hora2Ref” con esta fórmula

=COINCIDIR(complejo!$D4,rngHorario,0)

Estos controles nos sirven ahora para definir los rangos dinámicos de las listas desplegables.

Para las celdas D5:D9 definimos el nombre “rngHora1Comp” con la fórmula

=DESREF(complejo!$A$3,hora1Ref,,CONTARA(rngHorario)-hora1Ref)

Para las celdas E4:E9 definimos el nombre “rngHora2Comp” con la fórmula

=DESREF(complejo!$A$3,hora2Ref,,CONTARA(rngHorario)-hora2Ref)

El archivo con el ejemplo puede descargarse aquí.

Todo sobre listas desplegables en Excel, técnicas avanzadas y descarga gratuita de ejemplos  en la Caja de Herramientas Excel - Listas Desplegables de JLD. Ver la nota o ir a la página de descarga de la guía.

14 comments:

Juan Ignacio,  09 marzo, 2011 15:28  

Buenos días,

Al hilo del tema de los rangos, tengo el siguiente problema: en una hoja con varios miles de filas, tengo que usar varias fórmulas (CONTAR.SI, BUSCARV,...) en rangos fijos que vayan por ejemplo de 100 en 100 ($A$1$:$A$100; $A$101:$A$200, y así hasta completar los varios miles de filas) ¿Hay alguna forma de automatizar ésto sin tener que cambiar las fórmulas para cada rango? Muchas gracias por tu tiempo

Un saludo

Jorge L. Dunkelman 09 marzo, 2011 20:14  

¿Por qué hay que usar CONTAR.SI o BUSCARV? ¿Se trata de algún ejercicio?
Cuando se trata de analizar miles de filas, funciones como CONTAR.SI o BUSCARV pueden ser muy ineficientes.
Por ejemplo, podrías agregar un campo auxiliar a tu tabla para distinguir los rangos y luego usar una tabla dinámica hacer el análisis.

Jose Riu,  18 enero, 2012 16:51  

me intereso este post, pero no comprendi la forma de reemplazar las funciones que son ieficientes
"¿Por qué hay que usar CONTAR.SI o BUSCARV? ¿Se trata de algún ejercicio?
Cuando se trata de analizar miles de filas, funciones como CONTAR.SI o BUSCARV pueden ser muy ineficientes.
Por ejemplo, podrías agregar un campo auxiliar a tu tabla para distinguir los rangos y luego usar una tabla dinámica hacer el análisis."

podrias darme un ejemplo?

desde ya , miles de gracias por todo lo aprendido en tu blog.

Jorge L. Dunkelman 18 enero, 2012 21:45  

La consulta de Juan Ignacio se refería al uso intensivo de funciones como BUSCARV que pueden ser muy lentas. Las tablas dinámicas son mucho más eficientes y hay que aprovecharlas siempre que se pueda. Por ejemplo, totalizar las ventas de una empresa por sucursal en una base de datos de varios miles de filas (o decenas de miles) se hace en una fracción de segundo, lo que usando fórmulas podría demorar considerablemente más.

Marbella Garcia 12 julio, 2012 01:59  

Hola tengo un problemita, como puedo hacer que los valores desplegables con texto no valor numerico se cuenten y que en una hoja final del libro que contiene 58 hojas se sumen estos valores pero de todas las hojas, y poder crear graficas con datos de otras hojas.

Jorge L. Dunkelman 12 julio, 2012 21:27  

En lugar de dividir los datos en 58 hojas, te sugiero poner todos los datos en una única hoja que haga las veces de base de datos. Tendrías que agregar un campo para identificar la categoría (supongo que cada hoja contiene los datos de una sucursal o una provincia o algo así).
Una vez organizados los datos en es forma, una tabla dinámica hace los cálculos en una fracción de segundo y con facilidad. El gráfico lo creas basándote en el reporte dinámico, lo que te llevará otros 5 o 6 segundos de trabajo.

Anónimo,  10 septiembre, 2012 22:43  

Hola mi problema es el siguiente cuando en los rangos dejo algunas celdas en blanco no me toma todos los datos en la lista desplegable y yo quiero que me tome todos los que están antes de la celda en blanco (esos los incluye) la celda en blanco (también la incluye) y los datos que están debajo de ésta (que solo toma uno, el siguiente, los demás datos no los toma. EJ: tengo esta lista:
Nombres
Juan
Pedro
Jose

Diana
Pablo

Maria

Solo me toma hasta Diana.
Muchas gracias por ayudarme!
Saludos.

Anónimo,  10 septiembre, 2012 22:44  

Hola! que tal? resulta que necesito poner dos macros que limpien dos celdas que estan nombradas diferentes y no me deja. porque puede ser? como modifico la segunda macro para que borre el contenido de otra celda? Todo esta en la misma planilla. Saludos, Gracias por ayudarme!!!

Jorge L. Dunkelman 11 septiembre, 2012 15:25  

Hola,

fijate en la técnica que muestro en esta nota.

Jorge L. Dunkelman 11 septiembre, 2012 15:33  

Respecto a limpiar las dos celdas en la misma hoja, te envié la respuesta por mail privado.

Juan Fernandez 05 abril, 2013 23:31  

Hola Jorge.
En una Hoja tengo una validación de datos que depende de otra validación. He agregado un evento para que cambie el contenido de la validación dependiente al cambio el valor de la primera.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
On Error Resume Next
Set rng = ActiveSheet.Range("br172")
If Intersect(Target, rng) Is Nothing Then Exit Sub
Range("bt173") = "1"
End Sub.

Los valores son números.
Hasta aquí el resultado es el esperado.

Sucede que en esa misma Hoja tengo una segunda validación de datos dependiente de un desplegable.
He repetido el código, con los cambios necesarios, y el editor de VB me dice que hay un error de compilación, que "se ha detectado un nombre ambiguo:Worksheet_Change".
¿alguna sugerencia?
Gracias.

Jorge Dunkelman 06 abril, 2013 19:59  

¿Has repetido el código con los cambios o creado un nuevo evento Worksheet_Change? El nuevo código debe estar dentro del único evento Worksheet_Change que puede haber en la hoja.

Juan Fernandez 07 abril, 2013 18:58  

Escribí dos eventos Worksheet_Change. No sabía que solo podía haber uno.
Quería que el valor en bt173 se borrara o se pusiera en 1 (el valor mínimo en el desplegable) cuando variara el valor en br172. Y que sucediera lo mismo en bt203 respecto a bs202.

Jorge Dunkelman 07 abril, 2013 22:33  

De la misma manera que no puedes poner dos subs (rutinas) con el mismo nombre; se produce una situación ambigua. Todo los que tienes que hacer es poner los dos códigos bajo el mismo evento.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP