Excel - Formula para armar Diagrama de Gantt con 3 condicionantes

Pos: 101

Val: 23

Formula para armar Diagrama de Gantt con 3 condicionantes

Publicado por v2gerardo (10 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 clara

No me gusta: Está pregunta no esta clara o no es útil

Responder

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 clara

No me gusta: Está respuesta no esta clara o no es útil

Comentar

Pos: 101

Val: 23

Formula para armar Diagrama de Gantt con 3 condicionantes

Publicado por Gerardo (10 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

Valora esta respuesta

Comentar

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.

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

HabitacionesHotel.zip(16,8 KB)

Valora esta respuesta

Comentar

Pos: 101

Val: 23

Formula para armar Diagrama de Gantt con 3 condicionantes

Publicado por Gerardo (10 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á!

HabitacionesHotel.rar(132,7 KB)

Valora esta respuesta

Comentar

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:

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:

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:

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

HabitacionesHotel.zip(125,7 KB)

Valora esta respuesta

Comentar

Pos: 101

Val: 23

Formula para armar Diagrama de Gantt con 3 condicionantes

Publicado por Gerardo (10 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

Comentar

Pos: 101

Val: 23

Formula para armar Diagrama de Gantt con 3 condicionantes

Publicado por Gerardo (10 intervenciones) el 22/10/2019 00:40:25

Hola Rafa!
Cuanto tiempo, espero estés bien. Seguís dispuesto para una consulta?
Tengo otra Macro que arreglar, y la verdad es que me he atorado.
De estar disponible te pido por favor le puedas echar un ojo a dónde esté el error.
Quedo de ti, Saludos!

Valora esta respuesta

Comentar

formulas para estadisticas

Agrupar filas excel ampliando columnas de datos.