Excel - Formula para armar Diagrama de Gantt con 3 condicionantes

   
Vista:

Formula para armar Diagrama de Gantt con 3 condicionantes

Publicado por v2gerardo (4 intervenciones) el 12/07/2017 04:17:05
Buen día,
Estoy armando fórmula para armar Diagrama de Gantt bajo 3 condicionantes para conocer la ocupación de habitaciones de un Hotel:

Dónde:
Columna I = Nombre de la Habitación
Columna J = Fecha de Llegada
Columna K = Fecha de Salida
(D3 = fecha del día)
(C4 = Nombre de la Habitación, debe coincidir con los de la columna I

=IF(AND(RESERVAS!$J$2:$J$107<=CALENDARIO!D$3)*(RESERVAS!$K$2:$K$107>=CALENDARIO!D$3)*(RESERVAS!$I$2:$I$107=CALENDARIO!$C4),1,0)

Los resultados me los debe dar en 1 ó 0.

Cualquier sugerencia es bienvenida.
Muchas Gracias!
Valora esta pregunta
Me gusta: Está pregunta es útil y esta claraNo me gusta: Está pregunta no esta clara o no es útil
0
Responder
Imágen de perfil de Rafael

Formula para armar Diagrama de Gantt con 3 condicionantes

Publicado por Rafael (29 intervenciones) el 12/07/2017 15:59:21
En principio para montar el diagrama de gantt lo que necesitas son períodos entre dos fechas para de esta manera puedas ver la ocupación del hotel. La fórmula que utilizas por lo que veo en versión inglés del Excel lo que te va a devolver es si las fechas están fuera del día actual o de la fecha dada en D3 te devolverá 1 en caso contrario 0 pero no te dará ningún periodo. Necesitaría saber con algo más de exactitud lo que quieres conseguir para saber guiarte mejor sobre la fórmula que quieres o necesitas. Para montar un diagrama de gantt te dejo un enlace a mi web donde explico esto:
http://officepostit.es/tutoriales/excel-tutoriales/diagrama-gantt-excel/

Sí me das más datos te ayudaré encantado. Un saludo.

Rafa
http://officepostit.es
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar

Formula para armar Diagrama de Gantt con 3 condicionantes

Publicado por Gerardo (4 intervenciones) el 13/07/2017 00:57:38
Muchas gracias Rafa,
Espero puedas ver las imágenes que adjunto.
Tengo a cargo las ocupaciones de las habitaciones de un hotel, por lo que necesito que en una tabla de Excel y con fórmulas pueda devolver 1 y 0 dependiendo si la habitación está ocupada para la fecha determinada en la columna o no.

Hoja 1: Calendario (Diagrama Manual)
1er Columna: Nombre de Habitaciones.
1er Fila: Fecha del día.
En cada celda deberá ir la fórmula condicional para que devuelva 1 y 0 dependiendo si la habitación está ocupada para la fecha indicada por cada columna.

Hoja 2: Listado de Reservaciones.

Condiciones para la fórmula:
Que coloque 1 si la habitación está ocupada en la fecha indicada.
Que coloque 0 si la habitación está desocupada en la fecha indicada.
Si solo sí coincide dentro del periodo de fechas.
Si solo sí coincide el nombre de la habitación de la columna en la hoja 2 con la de la tabla del Diagrama de la hoja 1.

Espero me haya dado a entender.
De nuevo muchas gracias!

Capture1
Capture2
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
1
Comentar
Imágen de perfil de Rafael

Formula para armar Diagrama de Gantt con 3 condicionantes

Publicado por Rafael (29 intervenciones) el 13/07/2017 12:35:02
Hola de nuevo, creo que para lo que quieres gestionar en principio lo más ideal es una macro, te adjunto un archivo con la macro que he realizado. En principio siempre va a pasar por todas las reservas, esto se puede suprimir añadiendo alguna columna que indique que está procesada a través de la macro, pero no lo he gestionado ya que no se hasta donde podrás modificar tu plantilla. De todos modos es un proceso automatizado que no demorará mucho en tiempo. Prueba y me comentas que tal te funciona y si te sirve. Con fórmulas el proceso es mucho más engorroso y por supuesto tendrás que andar metiendo columnas de referencias para poder tener el dato.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
Sub ActualizaHAB()
 
Application.ScreenUpdating = False 'Ocultamos el proceso
'Seleccionamos pestaña calendario, contamos cuantas habitaciones tenemos
    Sheets("Calendario").Select
    HABITACIONES = WorksheetFunction.CountA(Range("A:A")) - 1
'Seleccionamos pestaña reservas y contamos cuantas reservas tenemos
    Sheets("Reservas").Select
    RESERVAS = WorksheetFunction.CountA(Range("A:A"))
 
'El primer bucle realiza paso por todas las reservas, esto podría reducirse
'añadiendo algun campo mas en la pestaña Reservas para identificar la reserva procesada.
    For i = 2 To RESERVAS Step 1
 
'Aquí empieza todo el bloque de gestion de la reserva
SIGUIENTERESERVA:
        Sheets("Reservas").Select
        HAB_RESERVADA = Range("A" & i).Value 'Adquiere Habitación Reservada
        FECHA_ENTRADA = Range("B" & i).Value 'Adquiere Fecha de Entrada
        NOCHES = Range("D" & i).Value - 1 'Adquiere numero de noches, entiendo que el día de salida no debe contabilizarse como ocupada
        Sheets("Calendario").Select
        For x = 1 To HABITACIONES Step 1 'Comenzamos el paso por las habitaciones para realizar las reservas
            Range("A" & x + 3).Select
            If ActiveCell.Value = HAB_RESERVADA Then 'Cuando coincida la Habitación Reservada con la que tenemos realizara los cambios
                FILA = ActiveCell.Row
                Range("B3").Select
                For y = 1 To 366 Step 1 'Pasa por todos los días del año para anotar la reserva
                    If ActiveCell.Value = FECHA_ENTRADA Then 'Si coincide las fechas empieza a realizar la reserva
                        COLUMNA = Split(ActiveCell.Address, "$")(1)
                        Range(COLUMNA & FILA).Activate
                        ActiveCell.Value = 1
                        For w = 1 To NOCHES Step 1
                            ActiveCell.Offset(0, 1).Activate
                            ActiveCell.Value = 1
                            If w = NOCHES Then
                                i = i + 1
                                GoTo SIGUIENTERESERVA 'Una vez hecha la reserva pasa al inicio de nuevo
                            End If
                        Next
                    End If
                ActiveCell.Offset(0, 1).Activate
                Next
            End If
        Next
    Next
    Application.ScreenUpdating = True 'Mostramos proceso
    MsgBox "Todas las reservas procesadas"
 
End Sub

Este es el código utilizado. Lo he dejado muy sencillo para que puedas entenderlo fácilmente como funciona.

Espero te sirva, un saludo.

Rafa
http://officepostit.es
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar

Formula para armar Diagrama de Gantt con 3 condicionantes

Publicado por Gerardo (4 intervenciones) el 14/07/2017 15:56:35
Te adjunto tu Obra Maestra!
Muchas Muchas Gracias! Me parece que funciona perfecto. A simple vista parece que funciona todo muy bien, pero tengo que revisarlo detenidamente ya que para la última habitación creo que no le devolvió ninguna reservación.
Te adjunto tu Obra Maestra!
> Veo que el botón de Actualizar aparece repetidas veces, sabes a qué se debe?

Para cuando puedas te invito a Punta Cana, R.D. :)
Saludos desde acá!
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar
Imágen de perfil de Rafael

Formula para armar Diagrama de Gantt con 3 condicionantes

Publicado por Rafael (29 intervenciones) el 15/07/2017 07:33:27
Le he estado echando un ojo a lo que me decías y vamos por partes.

El motivo de que no te esté cogiendo la última habitación es porque el conteo de reservas lo hace con las habitaciones, por lo que estoy viendo hay reservas sin habitación. Si esto puede suceder (que me consta que sí), tendremos que hacer el conteo de reservas con otro dato, por ejemplo con el número de reserva:

1
RESERVAS = WorksheetFunction.CountA(Range("A:A"))

Lo de los botones, ¿es posible que los hayas copiado y pegado?, es decir si has copiado un rango donde se encontrase el botón también te lo has llevado, porque no le encuentro sentido a que existan mas. Una vez que los elimino no vuelven a salir ya que la macro no hace eso.

Por último lo de no rellenar la última habitación se debe a esto:

1
HABITACIONES = WorksheetFunction.CountA(Range("C4:C51")) - 1

En mi ejemplo contaba los encabezados, por eso tiene el "-1" tu ya lo eliminas directamente seleccionando ese rango, con quitar ese -1 debería estar arreglado.

Adicional te he agregado este código:

1
Range("D4:IL51").ClearContets

Lo que te hace es que primero te borra lo que ya existe ya que tenemos todas las habitaciones reservadas, así si alguna se borra porque se elimina o se pierde esa reserva, no la debe mantener y evitamos posibles reservas falsas. Aunque no debería ser lo más habitual.

Te adjunto nuevamente el archivo para que dispongas de la nueva versión.

Un saludo!!

Rafa
http://officepostit.es
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
0
Comentar

Formula para armar Diagrama de Gantt con 3 condicionantes

Publicado por Gerardo (4 intervenciones) el 16/07/2017 00:41:13
Rafa,
Muchisimas gracias! Entiendo quedó más que excelente.
Nosotros estámos en Punta Cana, República Dominicana. Si llegaras a darte una vuelta por acá, puedes contar con nosotros para tu opción de hospedaje!
En verdad he quedado muy agradecido.
Un Saludo,
Valora esta respuesta
Me gusta: Está respuesta es útil y esta claraNo me gusta: Está respuesta no esta clara o no es útil
1
Comentar