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.
Buenos días,
ResponderBorrarAl 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
¿Por qué hay que usar CONTAR.SI o BUSCARV? ¿Se trata de algún ejercicio?
ResponderBorrarCuando 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.
me intereso este post, pero no comprendi la forma de reemplazar las funciones que son ieficientes
ResponderBorrar"¿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.
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.
ResponderBorrarHola 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.
ResponderBorrarEn 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í).
ResponderBorrarUna 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.
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:
ResponderBorrarNombres
Juan
Pedro
Jose
Diana
Pablo
Maria
Solo me toma hasta Diana.
Muchas gracias por ayudarme!
Saludos.
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!!!
ResponderBorrarHola,
ResponderBorrarfijate en la técnica que muestro en esta nota.
Respecto a limpiar las dos celdas en la misma hoja, te envié la respuesta por mail privado.
ResponderBorrarHola Jorge.
ResponderBorrarEn 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.
¿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.
ResponderBorrarEscribí dos eventos Worksheet_Change. No sabía que solo podía haber uno.
ResponderBorrarQuerí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.
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.
ResponderBorrar