Les somme prod et les messages variables

LES SOMMEPROD

Ces formules permettent de remplir des tableaux avec des couleurs et des formes conditionnelles (MFC) selon le résultat en chiffres des formules.

 

 

Le fonctionnement du fichier :

A l’ouverture du fichier un message sur les réservations à venir dans les 5 jours s’affiche

 

Les réservations se font en saisie directe dans l’onglet planning.

 

A la fin d’une saisie de ligne , en colonne K, la ligne se colore en bleu si la réservation est en cours, ou en gris si la réservation est passée.

Une macro s’éxécute à la fin d’une ligne de saisie ou d’une modification pour déterminer le statut de la chambre réservé, avec « Ok complet », « Ok reste x lits » ou « doublon » si la chambre a déjà été réservée.

Enfin l’onglet planning affiche les réservations selon les 3 semaines à venir et à partir de la date que l’on saisit en Q3.

 

Les explications des sommes prod

Cellules en vert (jours sans réservation)

Si on prend l'exemple de la ligne 21 et colonne M, on a 1 comme résultat en clic droit/format de cellule et donc un vert qui indique aucune réservation pour la chambre de la ligne 21, soit la chambre 37.

 

Si on décompose la formule des SOMMEPROD pour la comprendre,

=SOMMEPROD(('Saisie réservation'!$E$2:$E$100<=M$6)*('Saisie réservation'!$F$2:$F$100>=M$6))  donne

• Le nombre de jours de réservation (Avec tous les lits) des chambres <= M6 (le 11/06) pour la date de début SOMMEPROD(('Saisie réservation'!$E$2:$E$100<=M$6), entre la 1ére et derniére ligne
• Le nombre de jours de réservation >= M6 pour la date de fin SOMMEPROD(('Saisie réservation'!$F$2:$F$100>=M$6)

En les multipliant on obtient le nombre de jours de réservation pour la chambre le 11 juin, soit 5 jours entre la ligne 2 et 100

=SOMMEPROD(('Saisie réservation'!$E$2:$E$100<=M$6)*('Saisie réservation'!$F$2:$F$100>=M$6))

Le résultat de cette formule, qui correspond au nombre de lignes de réservation donne 1

=SOMMEPROD(('Saisie réservation'!$I$2:$I$100=$B21)*('Saisie réservation'!$K$2:$K$21=$A21))

Si on combine les 2 lignes de formules, on obtient le nombre de jours de réservation pour la chambre 37  pour le 11 juin, soit 0.

=SOMMEPROD(('Saisie réservation'!$E$2:$E$100<=M$6)*('Saisie réservation'!$F$2:$F$100>=M$6)*('Saisie réservation'!$I$2:$I$100=$B21)*('Saisie réservation'!$K$2:$K$100=$A21))

Et le résultat de la formule (Ou on ajoute "1-" devant SOMMEPROD) donne bien 1

=SI($A21<>"";1-SOMMEPROD(('Saisie réservation'!$E$2:$E$100<=M$6)*('Saisie réservation'!$F$2:$F$100>=M$6)*('Saisie réservation'!$I$2:$I$100=$B21)*('Saisie réservation'!$K$2:$K$100=$A21));"")

Cellules en jaunes (jours avec réservation(s))

Le résultat de la formule en ligne 21 et colonne P, soit pour le 14/06 et chambre 37 donne 1

=SOMMEPROD(('Saisie réservation'!$E$2:$E$100<=P$6)*('Saisie réservation'!$F$2:$F$100>=P$6)*('Saisie réservation'!$I$2:$I$100=$B21)*('Saisie réservation'!$K$2:$K$100=$A21))

Et le résultat de la formule (Ou on ajoute1- devant SOMMEPROD) donne bien 0 =SI($A21<>"";1-SOMMEPROD(('Saisie réservation'!$E$2:$E$100<=P$6)*('Saisie réservation'!$F$2:$F$100>=P$6)*('Saisie réservation'!$I$2:$I$100=$B21)*('Saisie réservation'!$K$2:$K$100=$A21));"")

Cellules en rouges (Doublons de réservation)

Le résultat de la formule en ligne 22 et colonne R, soit pour le 16/06 et chambre 39 donne 2 lignes de réservation.

=SOMMEPROD(('Saisie réservation'!$E$2:$E$100<=R$6)*('Saisie réservation'!$F$2:$F$100>=R$6)*('Saisie réservation'!$I$2:$I$100=$B22)*('Saisie réservation'!$K$2:$K$100=$A22))

Et le résultat de la formule (Ou on ajoute1- devant SOMMEPROD) donne bien -1 =SI($A21<>"";1-SOMMEPROD(('Saisie réservation'!$E$2:$E$100<=R$6)*('Saisie réservation'!$F$2:$F$100>=R$6)*('Saisie réservation'!$I$2:$I$100=$B22)*('Saisie réservation'!$K$2:$K$100=$A22));"")

Le fichier et la saisie de quelques formules en bas du planning vous aideront à comprendre.

LA REDACTION D'UN MESSAGE VARIABLE

 

      L'explicatif pdf comporte les commentaires en vert, comme dans une macro.     

Dans le fichier, la macro va s'éxécuter à l'ouverture du fichier.

Private Sub Workbook_Open()

'Déclaration des variables (date, texte en string)

           Dim Inc As Integer, DLig As Long, Lig As Long, Wst As Worksheet, ws As Worksheet

           Dim LaDate As Date, Msg As String, sTmp(3) As String, sForm As String

          Application.ScreenUpdating = False

          'Définir les feuilles

          Set Wst = ThisWorkbook.Worksheets("Saisie réservation")

          Set ws = Sheets("Planning")       

         DLig = Wst.Range("B" & Application.Rows.Count).End(xlUp).Row   'Dernière ligne de la feuille

         'Message d'accueil

        Application.WindowState = xlMaximized 'agrandit la fenêtre du message

      'Incrémente des lignes

       Inc = 0

       Vérification des réservations avec une boucle

      For Lig = 2 To DLig

             ' Récupérer la date si elle existe

             If Wst.Range("E" & Lig).Value <> "" Then LaDate = Wst.Range("E" & Lig).Value

                    'Ecart avec la date actuelle < 5 jours

                    If DateDiff("d", Date, LaDate) >= 0 And DateDiff("d", Date, LaDate) <= 5 Then

                             'Incrémenter le nombre de formations en alerte

                              Inc = Inc + 1

                            'Ligne de message correspondante

                            sForm = sForm & " N° " & Lig - 1 & " - concernant " & Wst.Range("B" & Lig).Value & Chr(10)

                     End If

           'End If

  Next Lig

           ' Message simple si aucune réservation ne correspond ou si les dates ne sont plus récentes

           If Inc = 0 Then

                   MsgBox ("Pas de réservation dans les CINQ jours avenir")

                  Exit Sub

           End If 

       'Début du message non variable, partie fixe début      

       sTmp(1) = "Attention #1 réservation#2 :" & vbCr

       sTmp(2) = "#3 une date de réservation qui commence dans les 5 jours à venir  "    

      'remplacement si inc > 1 (s'il y a plusieurs lignes concernées). partie1 = #1 partie2 = #2 partie3 = #3

      Msg = Replace(Replace(sTmp(1), "#1", IIf(Inc > 1, "les", "la")), "#2", IIf(Inc > 1, "s", ""))

     'affichage message fixe avec modifs pluriels, puis un retour à la ligne, puis les lignes variables

      Msg = Msg & vbCr & sForm

      'affichage partie fixe fin avec les modifs pluriels

      Msg = Msg & vbCr & vbCr & Replace(sTmp(2), "#3", IIf(Inc > 1, "ont", "A"))

      'Entête de message fenêtre

       MsgBox Msg, vbInformation, "ATTENTION...réservation à venir..."

      'Effacer les variables objet

      Set Wst = Nothing

sortie:

    Application.ScreenUpdating = True

End Sub

Cet explicatif en pdf 

LES_SOMME_PROD_ET_LES_MESSAGES_VARIABLES

Le fichier Excel

Planning_somme_prod_reservation_chambres