Hotel San Martín *****

-



En el siguiente ejercicio proponemos realizar una pequeña aplicación para gestionar las reservas de un hotel, con tres hojas:

  • Hotel, donde:
    • Contamos con un formulario donde introducir los datos para la gestión de las reservas: creación, modificación y/o cancelación
    • Gráfico que nos indica las habitaciones disponibles (no coloreadas) para las fechas solicitadas por el cliente.
    • Listado de habitaciones ocupadas en dichas fechas
  • Reservas: relación histórica de todas las operaciones registradas. Los datos se recogen en una Tabla de Excel, lo que facilita el trabajo posterior.
  • Tarifas: importe por persona y noche en función de la temporada y servicio contratado (sólo habitación, habitación + desayuno, Media pensión, o pensión completa
A continuación te dejo una serie de indicaciones para poder llevar a cabo el trabajo. ¿Te atreves?
La plantilla inicial la puedes encontrar aquí.
Si quieres que te facilite PDF con todos los pasos para su resolución escríbeme a  rbruixola@dssanalitic.com.
Recuerda, si quieres estar al tanto de las nuevas publicaciones no olvides suscribirte.

Herramientas utilizadas

Formato condicional

Se han establecido formatos condicionales para indicar la disponibilidad de las habitaciones:
  • Si el número de la habitación coincide con una de las habitaciones ya reservada dentro de las fechas indicadas por el cliente, esta se coloreara de color Naranja, Énfasis 2, Clara 60%. En este caso utilizamos la función COINCIDIR, para determinar el formato.
  • Todas aquellas habitaciones no reservadas, en las fechas indicadas por el cliente, y que el número de huéspedes que puede alojar es inferior al solicitado por el cliente se mostrarán de color gris.
Si no se indica ninguna fecha, por defecto el gráfico muestra las habitaciones disponibles a fecha de hoy.

Función FILTRAR().

Con esta función, nueva disponible en Microsoft 365, obtendremos el número de las habitaciones ocupadas, que serán las que cumplan estas condiciones:
  • Que la fechas de entrada, solicitada por el cliente, este comprendida entre las fechas de entrada y salida de las habitaciones ya reservadas.  
  • O, que la fechas de entrada, solicitada por el cliente, este comprendida entre las fechas de entrada y salida de las habitaciones ya reservadas.
En el caso, de aun no estar indicadas fechas de entrada y salida. Solo se filtraran aquellas habitaciones que ha día de hoy estén ocupadas.

Si no disponemos de esta función, podremos desarrollar el trabajo mediante filtros avanzados

Función COINCIDIR().

Utilizamos está función, en el formato condicional creado para determinar las habitaciones ocupadas

Función BUSCARV()

Utilizaremos esta función para determinar el importe total de la reserva, buscando el servicio solicitado en la hoja "Tarifas".

Función SI()

Para determinar que cálculos hacer cuando no se han definido datos para la reserva, y cuales hacer cuando los datos de la reserva están disponibles.

Formulario de reserva.

El formulario ha sido diseñado con las siguientes características:
  • Los campos: Nº de reserva y Fecha de Reserva, se cumplimentan mediante una macro.
  • El campo Nº de personas, admite un mínimo de 1 y un máximo de 8 (es la máxima capacidad de las habitaciones del sexto piso).
  • El campo tipo de alojamiento, esta compuesto por una lista de validación:
    • 1 Sólo habitación.
    • 2 Hab + desayuno
    • 3 Media pensión
    • 4 Pensión completa
  • El campo número de tarjeta tiene formato personalizado para que los 16 números de la tarjeta se muestren agrupados de 4 en 4 y separados por un espacio.
  • El campo Fecha caducidad solo muestra mes y año: mm/aa

Macros

las macros utilizadas las hemos dividido en dos módulos. En el módulo 1 recogemos aquellas macros que realizamos mediante la grabadora de macros; y en el módulo 2 las que han exigido programación. No obstante a continuación os dejo el código de cada una de las macros.

Módulo 1. Borrar

Sub Borrar()

' Borrar Macro: borra todos los datos del formulario'

    Application.ScreenUpdating = False
    Range("C4:C15").Select
    Selection.ClearContents
    Range("C5").Select
    Application.ScreenUpdating = True
End Sub

Módulo 1. Editar

Sub Editar()
'
' Editar Macro: Busca el número de reserva indicado y muestra todos los relativas a dicha reserva
    
    Range("C5").Select
    ActiveCell.FormulaR1C1 = "=VLOOKUP(R4C,Tabla3,Hotel!RC[-2],0)"
    Range("C5").Select
    Selection.Copy
    Range("C6:C15").Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("C6:C15").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("C6").Select
    Application.ScreenUpdating = True
End Sub

Módulo 1. Registrar

Sub Registrar()
'
' Traslada los datos del formulario a la tabla de la hoja Reservas

    Application.ScreenUpdating = False
    Sheets("Reservas").Select
    Range("B3").Select
    Selection.ListObject.ListRows.Add (1)
    Sheets("Hotel").Select
    Range("C4").Select
    Range("C4:C15").Select
    Selection.Copy
    Sheets("Reservas").Select
    Range("B3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=True
    Range("N3").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "Reservada"
    Range("A3").Select
    Sheets("Hotel").Select
    Range("C5").Select
    Application.ScreenUpdating = True
End Sub

Módulo 1. Fecha

Sub Fecha()
'
' Escribe automáticamente la fecha de hoy en el campo fecha de reserva

    Application.ScreenUpdating = False
    Range("C5").Select
    ActiveCell.FormulaR1C1 = "=TODAY()"
    Range("C5").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Range("C6").Select
    Application.ScreenUpdating = True
    
End Sub

Módulo 1. Consecutivo

Sub Consecutivo()
'
' Asigna automáticamente un número correlativo a cada reserva

    Application.ScreenUpdating = False
    Range("C4").Select
    ActiveCell.FormulaR1C1 = "=R[-3]C[-1]+1"
    Range("C4").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B1").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("C4").Select
    Application.CutCopyMode = False
    Range("C5").Select
    Application.ScreenUpdating = True
End Sub

Módulo 2. Reservar

Sub Reservar()
'Guarda la reserva en la tabla y borra el contenido del formulario

    Application.ScreenUpdating = False

'Macro que evita que se ejecute macro secundaria si existen celdas especificas vacías
    existe = False
    For Each C In Range("C6:C15") 'Cambiar la C por la columna donde esta las celdas a evaluar. cambiar ("C5:C15") por las celdas a evaluar
        If C.Value = "" Then 'Cambiar C por la columna identificada más arriba
            celdas = celdas & " " & C.Address(False, False) 'Cambiar C por la columna identificada más arriba
            existe = True
        End If
    Next
    If existe Then
        MsgBox "Falta información obligatoria en las celdas : " & celdas 'mensaje
        Exit Sub
    End If
    
      'A continuación colocar el nombre de las macros (Consecutivo, Fecha, Registrar).
    
    Consecutivo
    Fecha
    Registrar
           
    'Sigue la macro
    
    MsgBox "El dato se guardó", vbInformation, "GUARDAR" 'Entre las primeras comillas mensaje que se muestra si su macro se ejecutó satisfactoriamente.
    
    'A continuación colocar el nombre de la macro limpiar
    Borrar
    
    Application.ScreenUpdating = False
    'Fin de la macro
End Sub

Módulo 2. Cancelar

Sub CANCELAR()

'Cambia el estado de la reserva a Cancelado

'A continuación colocar la macro Editar

Editar

Application.ScreenUpdating = False

Dim lista As New Collection

Set h1 = Sheets("Hotel") 'Cambiar "Hotel" por el nombre de la hoja que contiene el n° de registro que se desea eliminar

Set h2 = Sheets("Reservas") 'Cambiar "Reservas" por el nombre de la hoja de donde se va a eliminar el n° de registro buscado más arriba

'

dato = h1.[c4] 'En corchetes [C5] colocar la celda del n° de registro que se desea eliminar

If dato = "" Then

MsgBox "Indicar número de reserva" 'Mensaje

Exit Sub

End If

'

u = h2.Range("B" & Rows.Count).End(xlUp).Row 'Cambiar "B" por la columna donde se encuentra el n° de registro buscado en la base de datos hoja "Reservas"  para eliminar

'

existe = False

For i = 2 To u

        If UCase(h2.Cells(i, "B")) = UCase(dato) Then 'Cambiar "B" por la columna donde se encuentra el n° de registro buscado en la base de datos hoja "Reservas"  para eliminar

        existe = True

        lista.Add i

    End If

Next i

'Continúa la macro

If existe Then

    cf = MsgBox("Desea cancelar la reserva n°: " & Range("C4"), vbInformation + vbYesNo, "AVISO") 'Cambiar "C5" por la celda que contiene el n° de registro que se desea eliminar en la hoja "principal"

    If cf = vbYes Then

Set r = h2.Columns("B") 'Cambiar "B" por la columna que contiene el n° de registro que se desea eliminar en la hoja "Reservas"

Set b = r.Find(h1.[C4], lookat:=xlWhole) 'Cambiar [C4] por la celda que contiene el n° de registro que se desea eliminar en la hoja "registros"

If Not b Is Nothing Then

    h2.Cells(b.Row, "N") = "Cancelado" 'Cambiar "N" por la celda donde se quiere poner el mensaje "Cancelado"

    End If

    MsgBox "Reserva cancelada con éxito", vbInformation 'Mensaje

    End If

Else

MsgBox "No se encontró el código: " & Range("C4") 'Cambiar "C4" por la celda que contiene el n° de registro que se desea eliminar en la hoja "principal"

Exit Sub

End If

'A continuación colocar la macro limpiar

Borrar

'fin de la macro

Application.ScreenUpdating = True

End Sub

Módulo 2. Modificar

Sub MODIFICAR()
' funciona en combinación con la macro Editar, en primer lugar editar una reserva, y una vez hechos los cambios los traslada a la tabla de reservas

Application.ScreenUpdating = False

Set h1 = Sheets("Hotel") 'Cambiar "Hotel" por la hoja que contenga el n° de registro que se desee reemplazar
Set h2 = Sheets("Reservas") 'Cambiar "Reservas" por la hoja que contenga la base de datos donde se encuentre el registro que se desea reemplazar

cf = MsgBox("Desea reemplazar el registro?", vbInformation + vbYesNo, "AVISO") 'Mensaje
If cf = vbYes Then

'Estas cuatros líneas seguidas son para que la macro se detenga si falta información en la celda especificada
If h1.[c4] = "" Then 'Cambiar [D11] por la celda que contenga el n° de registro que se quiere reemplazar
MsgBox "Indique número de reserva" 'Mensaje que se muestra si la celda B2 está vacía
Exit Sub
End If

If h1.[c5] = "" Then 'Cambiar [D11] por la celda que contenga el n° de registro que se quiere reemplazar
MsgBox "Indique fecha de reserva" 'Mensaje que se muestra si la celda B2 está vacía
Exit Sub
End If
'
If h1.[c6] = "" Then 'Cambiar [D12] por la siguiente celda que se desee reemplazar
MsgBox "Indique fecha de entrada" 'Mensaje que se muestra si la celda D12 está vacía
Exit Sub
End If
'
If h1.[c7] = "" Then 'Cambiar [D13] por la siguiente celda que se desee reemplazar
MsgBox "Indique fecha de salida" 'Mensaje que se muestra si la celda D13 está vacía
Exit Sub
End If
'
If h1.[c8] = "" Then 'Cambiar [D14] por la siguiente celda que se desee reemplazar
MsgBox "Indique Nº personas" 'Mensaje que se muestra si la celda D14 está vacía
Exit Sub
End If
'
If h1.[c9] = "" Then 'Cambiar [D15] por la siguiente celda que se desee reemplazar
MsgBox "Indique nº habitación" 'Mensaje que se muestra si la celda D15 está vacía
Exit Sub
End If
'
If h1.[c10] = "" Then 'Cambiar [D16] por la siguiente celda que se desee reemplazar
MsgBox "Seleccione servicio" 'Mensaje que se muestra si la celda D16 está vacía
Exit Sub
End If
'
If h1.[c11] = "" Then 'Cambiar [D17] por la siguiente celda que se desee reemplazar
MsgBox "Indique Nombre" 'Mensaje que se muestra si la celda D17 está vacía
Exit Sub
End If
'
If h1.[c12] = "" Then 'Cambiar [D18] por la siguiente celda que se desee reemplazar
MsgBox "Indique Apellidos" 'Mensaje que se muestra si la celda D18 está vacía
Exit Sub
End If
'
If h1.[c13] = "" Then 'Cambiar [D18] por la siguiente celda que se desee reemplazar
MsgBox "Indique nº de tarjeta" 'Mensaje que se muestra si la celda D18 está vacía
Exit Sub
End If
'
If h1.[c14] = "" Then 'Cambiar [D18] por la siguiente celda que se desee reemplazar
MsgBox "Indique Fecha Caducidad Tarjeta" 'Mensaje que se muestra si la celda D18 está vacía
Exit Sub
End If
'
If h1.[c15] = "" Then 'Cambiar [D18] por la siguiente celda que se desee reemplazar
MsgBox "Indique una dirección de correo electrónico" 'Mensaje que se muestra si la celda D18 está vacía
Exit Sub
End If

Set r = h2.Columns("B") 'Cambiar "B" por la columna donde se encuentra el n° de registro a reemplazar en la base de datos
Set b = r.Find(h1.[c4], lookat:=xlWhole) 'Cambiar [D9] por la celda donde esta el n° de registro que se desea reemplazar

If Not b Is Nothing Then 'Cambiar B por la columna donde están los datos que se desean reemplazar
    
    h2.Cells(b.Row, "C") = h1.[c5] 'Cambiar C por la columna donde están los datos que se desea reemplazar con los datos de la hoja "Principal". Cambiar [D12] por la celda seguida del n° de registro en la hoja "Principal" que se desea reemplazar
    h2.Cells(b.Row, "D") = h1.[c6]
    h2.Cells(b.Row, "E") = h1.[c7]
    h2.Cells(b.Row, "F") = h1.[c8]
    h2.Cells(b.Row, "G") = h1.[c9]
    h2.Cells(b.Row, "H") = h1.[c10]
    h2.Cells(b.Row, "I") = h1.[c11]
    h2.Cells(b.Row, "J") = h1.[c12]
    h2.Cells(b.Row, "K") = h1.[c13]
    h2.Cells(b.Row, "L") = h1.[c14]
    h2.Cells(b.Row, "M") = h1.[c15]
      
    MsgBox "Su reserva se ha modificado con éxito", vbInformation 'Mensaje
Else
    MsgBox "El código no existe", vbInformation 'Mensaje
    Exit Sub
End If
'A continuación colocar la macro limpiar
Borrar

'Continúa la macro
End If
    Application.ScreenUpdating = True
    
End Sub

Comentarios

Publicar un comentario

Entradas populares de este blog

Si(), SI.CONJUNTO() y BUSCARV()

-
Imagen
 Tres formas de resolver el mismo problema. En el ejercicio que planteamos deseamos conocer el importe a pagar por cada uno de los pasajeros del vuelo Dallas-Bogotá.  El precio del billete está marcado en 220 USD, pero se aplica una serie de descuentos en función de la edad del pasajero, datos que tenemos recogidos en la tabla Tarifas: Por otro lado tenemos el listado de pasajeros con su fecha de nacimiento y edad: Solución con la función SI() La función SI(prueba lógica; [valor_si_verdadero];[valor_si_falso]), dispone de tres argumentos: Prueba lógica . En este apartado de la función nos solicita que le planteemos " una pregunta"  cuya respuesta sea "Sí"  o "No" . Valor si verdadero.  En este segundo argumento, nos pide que indiquemos que valor, función u operación deseamos realizar en el caso de que la respuesta a la pregunta sea Sí . Valor si falso. Por último, en el tercer argumento, nos pide que indiquemos que valor, función u operación deseamos reali...
Leer más

Tablero KANBAN

-
Imagen
Un tablero Kanban es una herramienta para mapear y visualizar un flujo de trabajo. Las versiones más simples constan de tres columnas: para distinguir las tareas que están pendientes de hacer, las que están en marcha y las que ya están hechas. Mientras que las filas representan los diferentes tipos de actividad. Su lógica es simple, si en una columna entran las tareas más rápido de lo que salen el trabajo comenzará a acumularse y el problema se hará visible para todo el equipo, Habiendo libertad para crear tantas columnas o subsecciones que se necesiten para visualizar un flujo de trabajo, según el proyecto, empresa o actividad; en este post tan solo vamos a mostrar una forma de como construir un modelo, con el fin de que puedas aplicar el modo a tus necesidades. Vamos a desarrollar el trabajo de dos modos en función de si somos usuarios de 365 o no. En ambos casos partimos de la misma tabla  Actividades . Aquí te puedes descargar el archivo. También puedes este post en YouTube El...
Leer más

Buscar y traer múltiples imágenes en Excel

-
Imagen
  La función BUSCARV, nos permite obtener información, en base a un valor buscado, y esa información puede estar en otro libro o en otra hoja de cálculo. En este ejemplo, contamos con una plantilla, en la que, seleccionando el nombre del equipo, se mostrarán los ID, de cada uno de los miembros de dicho equipo, así como la información relativa de cada uno de ellos. Esta información la tenemos disponible en la hoja BASE, así como una imagen de cada uno de ellos. Pero, además, queremos que nos muestre una imagen de cada uno de los integrantes del equipo. Para ello en la hoja Base, tenemos el nombre de la imagen, tal y como está guardada en la carpeta Emoticones. El archivo con el que estamos trabajando está dentro de la carpeta Ejemplo Macros , que a su vez contiene la carpeta Emoticones, donde están las imágenes de cada uno de los jugadores. Observa que los nombres de los archivos de la carpeta Emoticones, coinciden con los que figuran en la columna foto de hoja BASE. Así p...
Leer más