Nouveau casse-tête

Bonjour tout le monde !

Cela fait un petit moment que nous n'avons pas eu de casse-tête, alors je vous en propose 1 ! (en vrai, c'est juste un point sur lequel je galère, mais je vous le mets sous forme de jeu pour vous motiver, mais chuut !)

C'est un exemple simplifié de mon fichier. J'ai raccourci quelques formules et enlevé les liens pour que tout fonctionne ici.

Dans les colonnes impaires, nous avons des horaires. De minuit (0) à 8h du matin (lignes 4-5), et de 21h15 à minuit (1) lignes 8-9.
Dans les colonnes paires, un petit calcul qui ne nous occupera pas ici.

Il faut que je calcule du tant au tant de chaque mois le nombre d'heures effectuées. C'est donc la somme de toutes les données ligne 9 - ligne 8 + ligne 7 - 6 + ligne 5 - 4.
La ligne 11 sert à indiquer que la personne n'est pas là. Si elle est remplie par du texte, il faut donc ne pas additionner les heures de ce jour-là pour le soir, mais pas non plus pour le lendemain matin. Sauf que si on met le code AM, les heures doivent compter quand même.

Je tourne en rond depuis des heures/jours. Je suis preneur d'une solution intéressante. J'ai en tout 13 tronçons, pour 8 personnes, avec des MFC, of course. Donc ça commence déjà à devenir gourmand. Et il faudrait avoir les infos en temps réel, sans avoir à le mettre à jour manuellement. C'est pourquoi je ne me suis pas orienté vers une macro événementielle pour calculer ça (et parce que je suis plus à l'aise avec les formules en temps normal).

Je vous laisse mes essais pathétiques dans le fichier en orange et vert.

Si je ne suis pas clair, n'hésitez pas à m'interpeller.

Par avance merci.

{=SOMME(SI(C11:AP11="";C9:AP9;0))-SOMME(SI(C11:AP11="";C8:AP8;0))+SOMME(SI(C11:AP11="";C7:AP7;0))-SOMME(SI(C11:AP11="";C6:AP6;0))+SOMME(SI(C11:AP11="";C5:AP5;0))-SOMME(SI(C11:AP11="";C4:AP4;0))*SOMME(SI(ESTNUM(CHERCHE("AM*";C11:AP11));1;0))}
<p></p>

Salut,

Un essai : utiliser la fonction "calculHeures" avec pour argument la cellule où le nom est rempli (ici A4).

On ne passe pas par une macro évènementielle mais on utilise quand même du VBA

Salut BibuNesco !

Merci beaucoup pour cette proposition, c'est super !

Pour le VBA, cela ne pose pas de souci. J'étais en train de me résoudre à l'envisager de plus en plus pour ce cas. Ta solution marche super bien pour les différentes personnes à la verticale.
Le souci, et c'est de ma faute je ne l'ai pas précisé, c'est que la structure pour le reste du tableau est la suivante :

DU 1er au 20 pour le 1er bloc, puis du 21 au 20 du mois d'après pour tous les autres. Ils sont tous séparés par la colonne de total (qui fait bien suer) entre le 20 et le 21.

Du coup je ne vois pas comment adapter ta solution là :(

En tout cas, c'est déjà génial, je vais décortiquer cela.

Une solution pas compliquée serait de rajouter dans les arguments de la fonction les bornes du mois dont tu veux calculer les heures.

Un truc comme ceci :

=calculHeures(cellule comportant le nom de la personne, cellule de début de mois, cellule de fin de mois)

Mais en terme d'implémentation sur ta feuille après je ne sais pas si ça serait le top :/

Oui, bonne idée !

La date de fin serait toujours dans la colonne juste à gauche de la cellule dans laquelle on met la fonction. Ça ferait donc juste la date de début à caler manuellement.

Franchement, une fois que la trame sera en place, ça ne bougera plus. Je m'en accomoderais parfaitement 😁

Je vais essayer d'utiliser le "Application.Caller" pour récupérer l'adresse de la cellule dans laquelle tu écris la fonction, et ainsi en déduire les bornes.

Je reviens vers toi rapidement :)

Salut,

Voici le code adapté, la seule nouvelle contrainte est de bien écrire "Total heures" dans les colonnes concernées (je m'en sers comme d'une condition).

Function calculHeures(rangeNom As Range)

    Application.Volatile

    nomCalcul = rangeNom.Value

    Dim Ws_Active As Worksheet
    Set Ws_Active = ActiveSheet

    derligActive = Ws_Active.Range("A" & Rows.Count).End(xlUp).Row

    For i = 1 To derligActive
        If Ws_Active.Range("A" & i).Value = nomCalcul Then
            ligneNom = i
        End If
    Next i

    borneSupCol = Application.Caller.Column - 2     ' Récupère la colonne de la cellule où est inscrite la fonction (-2 pour avoir la colonne finale                                                     ' des heures

    For i = borneSupCol To 1 Step -1                ' on boucle vers la gauche

        If Ws_Active.Cells(3, i).Value = "Total heures" Then        ' dès qu'une cellule est égale à "Total heures"
            borneInfCol = i + 1                     ' on a notre borne inférieure
        End If

    Next i

    ligneMatinInf = ligneNom
    ligneMatinSup = ligneNom + 1
    ligneSoirInf = ligneNom + 4
    ligneSoirSup = ligneNom + 5

    ligneCritere = ligneNom + 7

    totalHeures = 0

    For i = borneInfCol To borneSupCol Step 2

        If Ws_Active.Cells(ligneMatinSup, i).Value <> "" And Ws_Active.Cells(ligneMatinInf, i).Value <> "" Then
            heuresMatin = Ws_Active.Cells(ligneMatinSup, i).Value - Ws_Active.Cells(ligneMatinInf, i).Value
            totalHeures = totalHeures + heuresMatin
        End If

        If Ws_Active.Cells(ligneSoirSup, i).Value <> "" And Ws_Active.Cells(ligneSoirInf, i).Value <> "" Then
            heuresSoir = Ws_Active.Cells(ligneSoirSup, i).Value - Ws_Active.Cells(ligneSoirInf, i).Value
            totalHeures = totalHeures + heuresSoir
        End If

    Next i

    For i = borneInfCol To borneSupCol Step 2

        If i <> dercolActive Then

            If Ws_Active.Cells(ligneCritere, i).Value <> "" And Ws_Active.Cells(ligneCritere, i).Value <> "AM" Then

                If Ws_Active.Cells(ligneSoirSup, i).Value <> "" And Ws_Active.Cells(ligneSoirInf, i).Value <> "" Then
                    heuresSoir = Ws_Active.Cells(ligneSoirSup, i).Value - Ws_Active.Cells(ligneSoirInf, i).Value
                    totalHeures = totalHeures - heuresSoir
                End If

                If Ws_Active.Cells(ligneMatinSup, i + 2).Value <> "" And Ws_Active.Cells(ligneMatinInf, i + 2).Value <> "" Then
                    heuresMatin = Ws_Active.Cells(ligneMatinSup, i + 2).Value - Ws_Active.Cells(ligneMatinInf, i + 2).Value
                    totalHeures = totalHeures - heuresMatin
                End If

            End If

        Else

            If Ws_Active.Cells(ligneCritere, i).Value <> "" And Ws_Active.Cells(ligneCritere, i).Value <> "AM" Then

                If Ws_Active.Cells(ligneSoirSup, i).Value <> "" And Ws_Active.Cells(ligneSoirInf, i).Value <> "" Then
                    heuresSoir = Ws_Active.Cells(ligneSoirSup, i).Value - Ws_Active.Cells(ligneSoirInf, i).Value
                    totalHeures = totalHeures - heuresSoir
                End If

            End If

        End If

    Next i

    calculHeures = totalHeures

End Function

PS: Petite question en prime, si dans la ligne des absences ou "AM", les heures du soir et du lendemain matin ne comptent pas.

En cas de transition de mois, je dois quand même prendre en compte les heures du lendemain matin ? (et ainsi donc 4 colonnes plus au lieu de seulement deux dans un milieu de mois ?)

Bibu

Je pense aussi pouvoir affirmer que la plage s'étend de la cellule juste à gauche de la cellule dans laquelle est la fonction, et remonte sur la gauche jusqu'à la 1ère cellule fusionnée rencontrée, puis offset de + 2 colonnes. C'est clair dans ma tête, mais pas dit que ça ressorte bien..

Ça doit pouvoir aider pour les bornes ?

En tout cas, y'a pas urgence. C'est sympa de m'aider, vas-y à ton rythme.

Ah OK, tu vas plus vite à changer le code que moi à te donner les infos. Bravo :)

Oui, j'ai fait en sorte que "Total heures" soit un point de repère fixe, je m'en sers aussi dans mes fonctions, donc pas de souci là-dessus. C'est un point de repère sûrement plus exploitable que rencontrer une cellule fusionnée.

Si une personne travaille la nuit du 20 au 21, de 21h15 à minuit les heures comptent pour le tronçon actuel, et les heures du 21 au matin compteront pour la période suivante. Et oui, si la personne est en Arrêt Maladie ce jour-là, les heures compteront 4 colonnes plus loin.

Via formules c'était pénible, ça coupait toute la symétrie. Du coup il a fallu feinter et utiliser l'équiv de la veille pour pas mal de choses.

=ET(EST.IMPAIR(COLONNE(C4));ESTTEXTE(DECALER($A$3;8;EQUIV(C$3-1;$B$3:B$3;0))))

Ok super,

Voilà le code mis à jour (qui normalement devrait fonctionner) :

Function calculHeures(rangeNom As Range)

    Application.Volatile

    nomCalcul = rangeNom.Value

    Dim Ws_Active As Worksheet
    Set Ws_Active = ActiveSheet

    derligActive = Ws_Active.Range("A" & Rows.Count).End(xlUp).Row

    For i = 1 To derligActive
        If Ws_Active.Range("A" & i).Value = nomCalcul Then
            ligneNom = i
        End If
    Next i

    borneSupCol = Application.Caller.Column - 2     ' Récupère la colonne de la cellule où est inscrite la fonction

    For i = borneSupCol To 1 Step -1                ' on boucle vers la gauche

        If Ws_Active.Cells(3, i).Value = "Total heures" Then        ' dès qu'une cellule est égale à "Total heures"
            borneInfCol = i + 1                     ' on a notre borne inférieure
        End If

    Next i

    ligneMatinInf = ligneNom
    ligneMatinSup = ligneNom + 1
    ligneSoirInf = ligneNom + 4
    ligneSoirSup = ligneNom + 5

    ligneCritere = ligneNom + 7

    totalHeures = 0

    For i = borneInfCol To borneSupCol Step 2

        If Ws_Active.Cells(ligneMatinSup, i).Value <> "" And Ws_Active.Cells(ligneMatinInf, i).Value <> "" Then
            heuresMatin = Ws_Active.Cells(ligneMatinSup, i).Value - Ws_Active.Cells(ligneMatinInf, i).Value
            totalHeures = totalHeures + heuresMatin
        End If

        If Ws_Active.Cells(ligneSoirSup, i).Value <> "" And Ws_Active.Cells(ligneSoirInf, i).Value <> "" Then
            heuresSoir = Ws_Active.Cells(ligneSoirSup, i).Value - Ws_Active.Cells(ligneSoirInf, i).Value
            totalHeures = totalHeures + heuresSoir
        End If

    Next i

    For i = borneInfCol To borneSupCol Step 2

        If i <> borneSupCol Then

            If Ws_Active.Cells(ligneCritere, i).Value <> "" And Ws_Active.Cells(ligneCritere, i).Value <> "AM" Then

                If Ws_Active.Cells(ligneSoirSup, i).Value <> "" And Ws_Active.Cells(ligneSoirInf, i).Value <> "" Then
                    heuresSoir = Ws_Active.Cells(ligneSoirSup, i).Value - Ws_Active.Cells(ligneSoirInf, i).Value
                    totalHeures = totalHeures - heuresSoir
                End If

                If Ws_Active.Cells(ligneMatinSup, i + 2).Value <> "" And Ws_Active.Cells(ligneMatinInf, i + 2).Value <> "" Then
                    heuresMatin = Ws_Active.Cells(ligneMatinSup, i + 2).Value - Ws_Active.Cells(ligneMatinInf, i + 2).Value
                    totalHeures = totalHeures - heuresMatin
                End If

            End If

        Else

            If Ws_Active.Cells(ligneCritere, i).Value <> "" And Ws_Active.Cells(ligneCritere, i).Value <> "AM" Then

                If Ws_Active.Cells(ligneSoirSup, i).Value <> "" And Ws_Active.Cells(ligneSoirInf, i).Value <> "" Then
                    heuresSoir = Ws_Active.Cells(ligneSoirSup, i).Value - Ws_Active.Cells(ligneSoirInf, i).Value
                    totalHeures = totalHeures - heuresSoir
                End If

                If Ws_Active.Cells(ligneMatinSup, i + 4).Value <> "" And Ws_Active.Cells(ligneMatinInf, i + 4).Value <> "" Then
                    heuresMatin = Ws_Active.Cells(ligneMatinSup, i + 4).Value - Ws_Active.Cells(ligneMatinInf, i + 4).Value
                    totalHeures = totalHeures - heuresMatin
                End If

            End If

        End If

    Next i

    calculHeures = totalHeures

End Function

Bibu

Re,

Après tests, j'ai mis +2 au lieu de +1 ici, parce qu'on saute toujours 2 colonnes

For i = borneSupCol To 1 Step -1                ' on boucle vers la gauche
        MsgBox (Ws_Active.Cells(3, i).Value)
        If Ws_Active.Cells(3, i).Value = "Total heures" Then        ' dès qu'une cellule est égale à "Total heures"
            borneInfCol = i + 2                     ' on a notre borne inférieure
        End If

Par contre, ça ne fonctionne malheureusement pas. Quand il boucle de borneSupColà 1, il passe par dessus la cellule "Total heures" comme si de rien n'était. J'ai mis une msgbox pour vérifier, il passe bien par-dessus, mais ne le détecte pas :(

Rebonjour tout le monde,

@Bibu, par ailleurs, la fonction renvoie une erreur si une macro est lancée dans le fichier (ou tout simplement qu'on effectue une action n'importe où), et en attendant qu'on vienne actualiser l'affichage de l'onglet sur lequel se trouve la fonction. Je ne me l'explique pas.