Calcul plage horaire planning avec condition donnée text
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
bonjour
on voudrait un fichier de 5 colonnes et 5 lignes et le résultat attendu
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
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
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
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
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
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 :
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
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
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
Encore un grand merci et un bon weekend
A bientôt
DannyRun
Merci pour ce retour, qui hélas (pour moi) ne reflète pas la réalité !
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
Merci pour ces précisions et là tout devient beaucoup plus clair mais du coup le mythe en a pris un coup
A bientôt
DannyRun