Calcul plage horaire planning avec condition donnée text Le sujet est résolu

Y compris Power BI, Power Query et toute autre question en lien avec Excel
D
DannyRun
Jeune membre
Jeune membre
Messages : 12
Inscrit le : 23 mars 2018
Version d'Excel : 2007 fr

Message par DannyRun » 30 octobre 2018, 16:55

Bonjour à tous,

après avoir expérimenter plusieurs méthodes, je cale pour ajouter une fonction supplémentaire à un planning hebdomadaire qui fonctionne du feu de dieux. C'est d'ailleurs sur le forum ou j'ai trouvé les solutions à sa création et à sa mise en œuvre.

En colonne B, on a les créneaux horaires par tranche de 15mn de B2 à B66.
On colorie les plages horaires de C2 à I66 (7 jours de la semaine, en colonne C à I) et cela nous affiche le total des heures coloriées par jours de C67 à I67. Cela nous affiche aussi en format text les créneaux horaires matin de C68 à I68 et ceux de l'après midi en C69 à I69. La macro est liée à un bouton.
Dans l'utilisation du planning, il est nécessaire d'indiquer à la main un lieu d'intervention qu'on nommera "EV" pour les plages horaires concernées, une alerte le rappelle d'ailleurs si l'utilisateur ne met rien.

L'objectif ici est d'ajouter en cellule C100 à I100 le total des heures de travail par jour correspondant aux plages horaires pour lesquels l'utilisateur a indiqué "EV" (peu importe la cellule dans laquelle il indique "EV" à partir du moment ou la mention se trouve à l'intérieur de la plage, Valeur "J" ds la macro). IL peu y avoir aussi les 2 plages horaires par jour ou l'utilisateur notera la mention EV.

Je pense que la solution doit tenir compte de la macro actuelle, qui fonctionne très bien mais à voir s'il ne faut pas explorer une autre piste !!
Private Sub CommandButton4_Click()
Dim V, L&, C&, R&, N%, S$, D As Date, J$
        V = Application.Match("TOTAUX", Columns(2), 0)
        If IsNumeric(V) Then L = V - 1 Else Beep: Exit Sub
        Cells(L, 3).Resize(, 7).Interior.ColorIndex = xlNone
        Cells(L + 1, 3).Resize(3, 7).ClearContents
    For C = 3 To 9
            R = 1
        For N = 1 To 2
            Do
                R = R + 1:   If R = L Then Exit For
                V = Cells(R, C).Interior.ColorIndex
            Loop While V = xlNone
                S = Replace(Cells(R, 2).Text, ":", "h") & " / "
                D = Cells(R, 2).Value
            While Cells(R + 1, C).Interior.ColorIndex = V
                R = R + 1
            Wend
                Cells(L + 1, C).Value = Cells(L + 1, C).Value + Cells(R + 1, 2).Value - D
                Cells(L + 2, C)(N).Value = S & Replace(Cells(R + 1, 2).Text, ":", "h")
        Next
            V = Application.CountA(Cells(L + 2, C).Resize(2))
         If V Then If Application.CountA(Cells(2, C).Resize(L - 1)) <> V Then J = IIf(J > "", J & " & ", "") & Cells(C).Value
                 
    Next
         If J > "" Then MsgBox "Veillez préciser le temps de travail EV pour les jours " & J, vbExclamation, " Horaires :"
End Sub 
N'hésitez pas, je peux envoyer un fichier si besoin
J'ai tenté plusieurs solutions mais je tourne en rond...
Je vous remercie par avance pour vos conseils et lumières

DannyRun
j
jmd
Fanatique d'Excel
Fanatique d'Excel
Messages : 10'599
Appréciations reçues : 250
Inscrit le : 8 décembre 2007
Version d'Excel : 365 + PowerBI

Message par jmd » 31 octobre 2018, 20:18

bonjour

on voudrait un fichier de 5 colonnes et 5 lignes et le résultat attendu
Apprenez les fonctions d'Excel.
Exemple "Mettre sous forme de tableau", TCD, "Récupérer des données".
Apprendre les fonctionnalités "récentes".
D
DannyRun
Jeune membre
Jeune membre
Messages : 12
Inscrit le : 23 mars 2018
Version d'Excel : 2007 fr

Message par DannyRun » 1 novembre 2018, 09:47

Bonjour,
je vous joins un fichier ds lequel vous trouverez un tableau avec 5 lignes et 5 colonnes et le résultat souhaité
Compte tenu qu'il est difficile de tout expliquer sur seulement 5 lignes et 5 colonnes, le second onglet reprend le planning ds lequel il faut intégrer la nouvelle fonctionnalité expliquée au 1er onglet.
Merci par avance pour vos conseils

Danny Run
ESSAI PLANNING.xlsm
(29.4 Kio) Téléchargé 26 fois
j
jmd
Fanatique d'Excel
Fanatique d'Excel
Messages : 10'599
Appréciations reçues : 250
Inscrit le : 8 décembre 2007
Version d'Excel : 365 + PowerBI

Message par jmd » 1 novembre 2018, 11:13

re

1/
règle pour toute ta vie d'Excellien : ne JAMAIS se baser sur des couleurs pour stocker des informations
les couleurs variables sont à faire par MFC
les couleurs figées pour toujours sont à faire au clavier/souris

2/
mettre EV au clavier/souris dans CHAQUE cellule voulue

3/
donc il faut mettre un texte (à toi de voir) dans les cellules qui sont actuellement vertes et vides

4/
donc mettre en MFC vert les cellules non vides

et tout est bien plus simple : les sommes d'heures sont de simples NBVAL(), ou mieux : un TCD car ainsi tu as un onglet de vison des totaux.

à te relire
Apprenez les fonctions d'Excel.
Exemple "Mettre sous forme de tableau", TCD, "Récupérer des données".
Apprendre les fonctionnalités "récentes".
D
DannyRun
Jeune membre
Jeune membre
Messages : 12
Inscrit le : 23 mars 2018
Version d'Excel : 2007 fr

Message par DannyRun » 1 novembre 2018, 11:37

merci pour cet lecture de l'outil
Toutefois, une petite précision
Cet outil est à destination de bénévoles d'associations voire de retraités qui ne maitrisent peu ou pas du tout excel. L'outil fonctionne très bien depuis 6 mois et ils se sont habitués à l'outil car il est adapté à leur niveau d'utilisation : Mettre des couleur dans des plages horaires et valider par un bouton

la couleur qu'ils peuvent mettre ds le planning sont variables, le vert était un exemple et je ne souhaite pas que les calculs reposent sur les données EV que les utilisateurs ajoutent au clavier/souris dans CHAQUE cellule voulue. Ils vont oublier de mettre EV ds ds cellules et les calculs d'heures des plages horaires risquent d'être faux. Il est important que ce soit l'outil qui fasse les calculs.
L'idée de passer par une formule NBVAL() ou un TCD plutôt qu'une macro est bonne mais cela demande en l'occurrence que l'utilisateur interagisse sur les calculs mais dans la cas présent, cela n'est pas envisageable.

Merci pour votre retour
j
jmd
Fanatique d'Excel
Fanatique d'Excel
Messages : 10'599
Appréciations reçues : 250
Inscrit le : 8 décembre 2007
Version d'Excel : 365 + PowerBI

Message par jmd » 1 novembre 2018, 13:18

re

maintenant qu'ils ont pris de mauvaises habitudes, je ne peux plus t'aider (à récupérer l'information "couleur" est hors de mes compétences)

note : NBVAL est une formule, toi tu la saisis, mais eux ne font que la lire (comme une addition). Idem pour les TCD.

bon courage
je passe la parole à d'autres forumeurs

amitiés
Apprenez les fonctions d'Excel.
Exemple "Mettre sous forme de tableau", TCD, "Récupérer des données".
Apprendre les fonctionnalités "récentes".
D
DannyRun
Jeune membre
Jeune membre
Messages : 12
Inscrit le : 23 mars 2018
Version d'Excel : 2007 fr

Message par DannyRun » 1 novembre 2018, 15:08

Effectivement, le VBA est devenue nécessaire vu le faible niveau des utilisateurs mais merci d'avoir pris le temps d'apporter cette analyse, j'espère avoir le retour d'autres forumeurs qui pourront m'aider...
Amitiés
Avatar du membre
LouReeD
Contributeur
Contributeur
Messages : 7'073
Appréciations reçues : 370
Inscrit le : 14 octobre 2014
Version d'Excel : 2013 FR, 2016 FR
Contact :
Téléchargements : Mes applications

Message par LouReeD » 2 novembre 2018, 16:09

Bonsoir,

ci joint une proposition sans VBA mais avec ajout d'une colonne "texte" des créneaux horaires et de deux lignes de calculs intermédiaires dont les résultats sont repris en ligne 100 avec une addition.
Les deux lignes (102 et 103) ainsi que la colonne (M) sont masquées pour éviter des erreurs de manipulations.
Le fichier :
ESSAI PLANNING_LouReeD.xlsm
(33.14 Kio) Téléchargé 19 fois
En "réponse" aux remarques de jmd, je dirais simplement que quelque fois je ne m'occupe seulement d'essayer de répondre à la demande sans me préoccuper de savoir si le fichier est "à reprendre entièrement" ou pas.
Mais il est vrai que quelque fois... ;-)
Bonne fin de semaine @ tous !

@ bientôt

LouReeD
Quelques règles à lire ICI ;;)
______________________________________________________Vous pouvez allez faire un tour sur : Index de "Mes applications" ;;)
D
DannyRun
Jeune membre
Jeune membre
Messages : 12
Inscrit le : 23 mars 2018
Version d'Excel : 2007 fr

Message par DannyRun » 2 novembre 2018, 17:01

Bonsoir LouReed,

un grand merci pour ta réponse et le job
Ça fonctionne nickel !! Cela va changer la vie pour certains utilisateurs (retraités...) qui ne sont pas tjrs à l'aise avec excel mais colorier les plages horaires et y mettre "EV" à l'intérieur sera une formalité pour eux :good: et surtout sécuriser les calculs...
J'ai regardé les formules en ligne 101 et 102, et celle là, je ne l'ai pas vu venir !! et moi qui croyais être à l'aise avec les formules sur excel et bien du coup je repars en maternel... La combinaison de ERREUR avec INDIRECT et EQUIV, fallait y penser !! Bravo !! Je n'ai pas encore tout compris mais comme pour ta macro, je vais m'y pencher pour bien comprendre... Ton analyse et ton approche sont tjrs intéressantes et reflètent indéniablement une grande expérience :wink:
Encore un grand merci et un bon weekend
A bientôt

DannyRun
Avatar du membre
LouReeD
Contributeur
Contributeur
Messages : 7'073
Appréciations reçues : 370
Inscrit le : 14 octobre 2014
Version d'Excel : 2013 FR, 2016 FR
Contact :
Téléchargements : Mes applications

Message par LouReeD » 2 novembre 2018, 17:38

Merci pour ce retour, qui hélas (pour moi) ne reflète pas la réalité ! :lol:
La formule se sert des données extraites par la macro, c'est à dire la détermination des deux éventuelles plages de travail.
En cherchant le texte de début de créneau qui grâce à EQUIV et la nouvelle colonne en "texte" des plages horaires, nous permet de trouver la première ligne de cette plage, le deuxième EQUIV qui lui recherche la valeur de la fin du créneau nous permet de connaître également la dernière ligne de cette même plage.
Le tout mélangé avec INDIRECT qui permet de mettre en "variable" des référence de plage on peut utiliser NB.SI avec en critère "EV" sur la première plage variable s'il y a et c'est pourquoi un soupçon de SIERREUR en cas de plage inexistante et j'ai même mis un "*1" afin de transformer la valeur d'une cellule vide en une valeur = à 0 car le fait de la multiplier par un transforme le vide en 0...

Une deuxième formule pour la deuxième période qui elle aussi est extraite par macro, et on additionne les deux résultats ce qui donne le total d'activité à condition d'un EV.

@ bientôt

LouReeD
Modifié en dernier par LouReeD le 4 novembre 2018, 11:44, modifié 1 fois.
Quelques règles à lire ICI ;;)
______________________________________________________Vous pouvez allez faire un tour sur : Index de "Mes applications" ;;)
Répondre
  • Sujets similaires
    Réponses
    Vues
    Dernier message