XL 2016 Décompte de valeurs précises avec bornage

[albuqs]

Bonsoir à tous,

Première contribution de ma part et - comme c'est surprenant - je viens faire appel à cette communauté qui m'a déjà souvent sorti de la panade sans même le savoir (grâce soit rendue à l'outil de recherche).

Dans le cadre d'un projet visant à faciliter le pilotage de la planification du personnel d'une structure, je développe un classeur qui, étant destiné à des usagers aux compétences bureautiques limitées mais néanmoins investis de solides responsabilités, se veut aussi user interface friendly que possible.

Cette configuration me pose des contraintes d'architecture (notamment le fait de ne pas pouvoir convertir la plage de saisie de données en tableau structuré en raison des dates qui sont en entête et seraient par conséquent converties en texte, ce qui ne m'arrange pas et ne me facilite pas la vie.... Mais là n'est pas le propos vu que j'ai pu trouver des solutions de contournement pour exploiter les données.

Pour résumer le problème, chaque ligne correspond à une personne, chaque colonne correspond à un jour, à l'intersection d'une ligne et d'une colonne l'utilisateur inscrit un code alphanumérique simple servant à identifier le type de prestation fournie par la personne. En gros mes données sur les personnes sont sous forme de tableau structuré, en revanche ma plage de saisie, qui est accolée, n'est qu'une plage nommée. Des codes spécifiques servent à identifier les jours de repos ou de vacances, une case vide ainsi qu'un certain nombre d'autres codes correspondent à des jours travaillés.

Mon souhait serait d'identifier le nombre de jours de travails consécutifs afin de pouvoir exploiter ce dernier par la suite.

Mon approche se heurte à la notion de "bornage" à laquelle je fais référence en objet, je m'explique:

En considérant la série suivante (T = travail, R = repos) T T R R T T T T R R T T T T T R R

En l'état actuel j'étais parti de l'idée de considérer une plage J-3 à J+3 afin d'identifier sur 7 jours combien de jours étaient effectivement travaillés. Pour ce faire j'ai eu la brillante idée de travailler sur la base d'un 7 - somme(nb(tous les codes des jours de congés). Cette approche me renvoie effectivement le nombre de jours travaillés sur la période définie, l'informatique fais donc ce que je lui demande... MAIS, dans la série présentée ci-dessus:

T T R R T T T T R R T T T T T R R

Le résultat renvoyé pour le jour marqué en gras est, logiquement, de: 5
Mon souhait serait que le résultat renvoyé soit de 4, sachant que la série est interrompue par deux jours de repos, lesquels sont certes salvateurs pour son destinataire mais peu pratiques dans l'élaboration de mon projet.

Ci-joint un classeur schématisant l'ensemble.
A l'échelle 1:1 des validations de données et des MFC sont appliquées, des userform sont présents, un nombre incalculable -quoique- de formules et datas en tous genre sont extraites, tout fonctionne bien jusque là....

L'objectif technique étant exposé, je passe rapidement sur l'objectif final utilisateur: après traitement du résultat, offrir à l'utilisateur un indice (probablement un smiley content, moyen content, pas content, pas content du tout) résultant de l'évaluation du nombre de jours travaillés entre deux congés ET de pouvoir exploiter les données brutes (pas de soucis pour tout ça, ma requête porte donc uniquement sur la problématique exposée ci-dessus)

Dans mon approche à base de si, de sommes et de nb j'évaluais chaque jour dans une réplique de la plage de saisie calendaire.

Bref, vous l'aurez compris, l'architecture même du fichier ne me simplifie pas la vie, et comme dirait l'autre: plus c'est simple pour les gens qui utilisent, plus c'est compliqué pour ceux qui développent.

Cet indice n'est jamais qu'une des nombreuses données qui seront extraites du fichier, mais pour le solde, jusque là tout va bien.

Ouvert à toutes solutions!

Je vous transmets volontiers la formule utilisée jusque là, mais sans intérêt à mon sens puisque ne répondant pas aux besoin. Probablement qu'une approche VBA s'impose (?).

En bon franco-vbdilletantiste ça donnerait quelque chose du genre:

Depuis activecell fais de l’offset à gauche jusqu’à rencontrer X, Y ou Z et ensuite refais la même à droite jusqu’à rencontrer X, Y, Z et finalement dis moi combien de cellules t’as parcouru en tout entre les deux bornes.

Mais même si ça ne renvoie le résultat pour une cellule donnée, je peux m’imaginer qu’il va falloir faire une boucle pour qu’il parcoure un range déterminé et rende compte de chaque résultat pour qu’ils soit inscrits en dur…. Restera encore à faire en sorte que ça s’actualise lorsqu’une valeur change dans la plage de référence…. Bref ça dépasse allègrement mes compétences en VBA je l’admets….

Au plaisir de vous lire,

 

[xUpsilon]

Bonjour,

Si je comprends bien, tu veux, pour un T1 donné, compter le nombre de T associés à la série dont T1 fait partie. En gros, quel que soit le jour donné, renvoyer d'une série de combien de jours T ce jour fait partie.

Que faire lorsque le jour recherché est un R ? Faut-il renvoyer 0 ?

Bonne journée,

 

[albuqs]

Bonjour xUpsilon,

Merci de ton interêt.
C'est exactement ça et s'agissant du R j'ai réalisé après coup avoir omis ce détail dans ma diatribe.

Donc en résumé:

Oui je souhaite que pour un T1 donné, compter l'ensemble des T de la série dont T fait partie.
Effectivement pour un R la valeur renvoyée peut être 0.

(Variante 1: Pour compliquer un peu les choses, on pourrait considérer que pour R, s'il est isolé alors il renvoie une valeur, s'il appartient au minimum à une série de 2 R il en renvoie une autre. Ca serait le must, mais ça ne représente pas une priorité. Aussi si ça complexifie trop la tâche je m'en passerai)

Ma contrainte majeure est que plusieurs valeurs peuvent être considérées comme R, de même plusieurs valeurs (ou absence de valeur) peuvent être considérées comme T.

Ex: vide, PAP, POP, 58, 70 sont considérés comme T tandis que 99, 38, TRW sont considérés comme R.

Les valeurs ci-dessus sont illustratives (les codes réels obéissent à la même logique), et je suis lié par ces derniers car similaires à ceux employés de longue date dans un programme parallèle (celui servant à la planification de détail, avec horaires et autres).

En somme, dans la variante principale:

-> Calculer le nombre de T appartenant à une série et renvoyer ce dernier.
-> R = 0

La variante 1 a de mon point de vue l'avantage de pouvoir travailler directement avec des valeurs numériques correspondantes (série de 4 jours = 4).

Dans cette variante :

-> Calculer le nombre de T appartenant à une serie et renvoyer ce dernier
-> Calculer le nombre de R appartenant à une série et renvoyer ce dernier

Dans la variante 2, si on ne souhaite pas créer le tableau du tableau du tableau, contraint, à nouveau à mon avis, à travailler avec des identifiants (un système de quotation) pour chaque résultat, ex:

R = A
Série de 2R minimum = B
De 1 à 4 T = C
5 T = D
6 T = E
7 T = F
Plus que 7 T = G

En espérant avoir été clair,

A+ et merci d'avance pour ta (vos) réflexion(s).

 

[xUpsilon]

Re,

Voici ma proposition :
- Une feuille base de données comportant deux tableaux : les codes correspondant à T et les codes correspondant à R
- En VBA, deux variables alimentées par ces tableaux qui seraient 2 strings de concaténation de tous les codes
- On boucle à gauche et à droite de la cellule observée pour voir si la valeur de la cellule est trouvée dans le string de concat
- Ainsi, on peut gérer en entrée le cas Si T ou Si R séparément

Si ça te convient, je te propose de refaire un fichier avec ces listes, et aussi d'indiquer où le résultat de cette fonction doit être retrouvé.

Bonne journée,

 

[albuqs]

Re,

- Ci-joint un fichier avec une feuille contenant les deux tableaux contenant les listes.
- Une feuille pouvant servir à recueillir les résultats.

Actuellement je bosse avec une réplique du tableau située sur la même feuille, avec un offset de 636 vers la droite pour aller chercher le résultat correspondant à la cellule active. Un peu moche comme architecture, mais elle a le mérite de simplifier le rappel des résultats....

Je laisse dans le fichier une réplique des colonnes dates dans la feuille principale et te laisse juge de la méthode à employer, soit:

-> renvoyer les résultats sur la même feuille
-> renvoyer les résultats sur une autre feuille

Bien à toi,

 

[xUpsilon]

Bonjour,

Déjà désolé pour le retard.
Voici une solution ci-joint :
- on compte le nombre de jours travaillés dans l'intervalle travaillé observé
- pour gérer les infinis (vu que rien signifie que la personne a travaillé), j'ai mis une limite de 20j. C'est à dire que si il y a une ligne avec 20 cellules vides à droite, on comptera 20 jours travaillés. Même chose vers la gauche, ce qui correspond à une valeur maximale tolérée de 40 jours travaillés de suite, ce qui me parait assez réaliste.
- résultats dans la dernière feuille
- surveiller et modifier les constantes dans le module standard pour adapter au vrai fichier

Bonne journée,

 

[albuqs]

Bonsoir,

Déjà désolé pour le retard

Quand pas d'obligation, pas de délai, pas de délai... pas de palais, heu pas de retard.
Merci infiniment pour ton job.

Je teste en implémentant dans mon fichier demain et je te fais un retour (et idéalement j'en profiterai pour marquer le sujet comme résolu .

Bonne soirée!

 

[albuqs]

Re,

Bon, finalement j'ai quand même tenté le coup dans la foulée.

Sur le principe la fonction fonctionne et renvoie le résultat, bon point. Donc déjà, merci pour le job!

En revanche deux constatations:

1. Le recalcul sur feuille accueillant les fonctions n'est pas opéré sur la base d'un changement dans la feuille source (on parle donc de calcul automatique) mais uniquement lors de la réactivation de la feuille accueillant les saisies ; Impliquant de devoir sortir de la feuille puis y revenir pour obtenir des données à jour dans la feuille de réception.
   
   
2. L'utilisation de la fonction matricielle sur un plage aussi importante que celle qui m'occupe (366 colonnes sur laquelle la formule est appliquée [01.01 -> 31.12 tenant compte de la possibilité d'avoir une année bissextile ; p.s: ce paramètre n'a pas a être pris en compte de toute façon, je m'arrange) et de 105 lignes (correspondant à l'effectif piloté) implique un temps de calcul relativement (excessivement) long, de l'ordre de 15 à 20 secondes dans de bonnes conditions (le pc est tranquille, pas de surchauffe, il a fait sa sieste et son rot, toussa toussa...).

Aussi je me demandais s'il n'était pas envisageable de provoquer le calcul à l'évènement selection change de la feuille de réception des données, il s'agirait dans ce cas de lui demander de recalculer la cellule de destination (dont les coordonnées seraient les même dans la feuille de réception) ainsi que les, disons 5 ou 10 cellules à gauche et à droite. Peut-être que le temps de traitement pour une réactualisation d'une plage de 10 à 20 cellules seraient pour ainsi dire transparent (?).

J'ai également testé en ramenant la plage traitée à 5 cellules plutôt que 20 de chaque côté, on gagne du temps, mais c'est toujours pas très jouissif en terme d'ux.

Pour rappel dans l'énoncé de départ, l'objectif est qu'en naviguant à travers les cellules au clavier, des indicateurs soient mis à jour dans un usf en mode non-modal (lequel est fonctionnel), il s'agirait donc de récupérer la valeur générée par la fonction et de la traiter afin de fourni un indicateur supplémentaire. Le fait donc que les cellules adjacentes au résultat de la cellule modifiée ne s'actualisent pas fausse l'indicateur.

Exemple:

- Sur la base de mes saisies en feuille saisie de J5 à P5, les cellules en feuille résultat de J5 à P5 ont une valeur de 5 retournée par la fonction
- Je modifie une valeur dans la feuille de saisie en J5 pour y insérer un code "repos"
- Sur la feuille résultat J5 renvoie désormais 0, en revanche K5 à P5 sont restés bloqués à 5 alors que le nouveau résultat attendu est désormais de 4.

Actuellement comme dit, il faut d'une part sortir de la feuille de saisie et y revenir pour que J5 à P5 soient actualisés, mais qui plus est le temps de traitement est de 14 secondes sur la base de 5 cellules parcourues de part et d'autre de la cellule active.

Du côté des forums anglophones, pas mal de problématiques similaires mais pas égales traitées... un certain nombre de "how to restart a count loop" de solutionnés, mais jusque là tous les compteurs n'allaient que dans une direction et de manière progressive, à savoir vers l'avant en incrémentant les valeurs une à une, contrairement au cas qui m'occupe.

Pour l'anecdote, aussi mauvais que je puisse être, j'ai réglé le faux problème du décompte dans le vent en bout de plage (premier et dernier jour du calendrier) en ajoutant un START et un STOP avec valeur de jour de repos dans la variable, ce qui permet de considérer un tour à zéro sans "fausser" le décompte des différents codes.

Enfin voilà, pour les trucs simples je me débrouille, pour le reste j'appelle à la rescousse et j'apprends...

Edit: Je suis 100% transparent, donc pour info, fervent défendeur de l'intelligence collective, me suis permis de lancer le sujet sur un autre forum également en:

1. te remerciant de la solution que tu as trouvé et des développements futurs éventuels sur le sujet et en mentionnant ce fil ;
2. en m'engageant à clore les deux fils simultanéement et en y mentionnant la solution adoptée lorsqu'une issue pérenne aura été rencontrée.

La démarche a été très mal perçue. Aussi le fil a été clos de l'autre côté. Sans rancunes.

Au plaisir de te lire ,

 

[xUpsilon]

Bonjour,

Voici ci-joint une solution qui ne passe non plus par une formule dans la cellule (qui serait donc mise à jour à chaque recalcul de feuille) mais par un évènement "Worksheet_Change".
Pour déclencher un calcul :
- Saisir dans la feuille de saisie (ici Feuil1)
- Dans la feuille de traitement des données, on recompte pour la cellule équivalente le nombre de jours travaillés, puis on recompte aussi pour les 3 cellules à gauche de la cellule équivalente, et pour les 3 cellules à droite de cette cellule
- Les données sont donc insérées dans la feuille de traitement en tant que valeur figée et non en tant que formule

Bonne journée,

 

[albuqs]

Hello,

Tu es un Dieu .

C'était la petite touche finale au projet, la petite cherry on the cake.

Ca va tout seul maintenant!

Merci infiniment!

 

[albuqs]

Bonsoir xUpsilon, bonsoir le forum,

Grâce à vous j'ai pu bien dépatouiller le tout et j'arrive gentiment au bout du projet.

Sur les derniers peaufinages je me suis aperçu que le code d'xUpsilon n'était fonctionnel que sur des cellules uniques modifiées manuellement (donc target), mais sitôt que l'on modifie quelques cellules en même temps, le résultat du schmilblick n'était pris en compte que pour la première cellule de .selection.

J'essaie en vain de faire dire quelque chose comme ça au code:

Boucle sur chaque cellule de la sélection et applique le bordel.

Le bout de code en l'état est le suivant:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual


Dim rWSRange As Range

Set rWSRange = Worksheets("DataRepTour").Cells(Target.Row, Target.Column)

rWSRange.Value = CompteJoursTravailles(Target)

For nCol = 1 To 3
    rWSRange.Offset(0, nCol).Value = CompteJoursTravailles(Target.Offset(0, nCol))
    If rWSRange.Column - 1 * nCol > 2 Then
        rWSRange.Offset(0, -1 * nCol).Value = CompteJoursTravailles(Target.Offset(0, -1 * nCol))
    End If
Next nCol

Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

Il est niquel pour des saisies individuelles.

J'ai dans un usf un petit gligli qui permet de faire des saisies en bloc.

Private Sub userform_Initialize()


 Dim xRg As Range
    Set xRg = Range("ValDonCodes")
    Me.ComboBox1.List = Application.WorksheetFunction.Transpose(xRg)

End Sub

Private Sub CommandButton1_Click()

code = ComboBox1.Value
Selection.Value = code

Unload Me

End Sub

Objectif:

Que les calculs opérés sur worksheet_change puissent également s'appliquer lorsque des valeurs sont insérées au moyen de l'usf.

J'entrevois deux possibilités:

1. Laisser le worksheet_change tel quel, et reproduire ses effets en codant directement sur le commandbutton1 de l'usf en faisant boucler sur cellule de la Selection (redondant, mais ça fonctionnerait).
2. Modifier le worksheet_change pour qu'il boucle directement sur chaque cellule de la selection, qu'elle soit unique ou pas.

Dans les deux cas mes tentatives sont restées vaines, si quelqu'un a une idée ?

Bien à vous et bonne soirée!

 

[albuqs]

Bon,

Une fois n'est pas coutume, j'ai trouvé la solution... comme quoi il suffisait de demander pour que la magie opère (j'ai quand même l'impression d'être la gars au bout du fil qui appelle la hotline et qui lui dit "Attendez attendez, ça fonctionne de nouveau, merci beaucoup!" avant même que l'opérateur n'ait fait quoique que ce soit.)

Donc pour les intéressés:

Dim C As Range
Dim S As Range
Dim rWSRange As Range

Set S = Selection

For Each C In S.Cells

Set rWSRange = Worksheets("DataRepTour").Cells(C.Row, C.Column)
rWSRange.Value = CompteJoursTravailles(C)

For nCol = 1 To 3
    rWSRange.Offset(0, nCol).Value = CompteJoursTravailles(C.Offset(0, nCol))
    If rWSRange.Column - 1 * nCol > 2 Then
        rWSRange.Offset(0, -1 * nCol).Value = CompteJoursTravailles(C.Offset(0, -1 * nCol))
    End If
Next nCol

Next C