Décompte de valeurs précises (série) avec bornage en colonne

Bonjour à tous,

D'ordinaire les réponses apportées aux questions des autres et/ou les ressources disponibles me permettent de m'en sortir, cependant aujourd'hui je sèche.

Par soucis de transparence je débute en vous informant du fait que j'ai d'ors et déjà lancé un SOS sur un autre forum d'aficionados ; un des membre - que je profite pour remercier ici également - a d'ailleurs proposé son aide et apporté une solution, qui malheureusement en l'état ne satisfait pas pleinement à la problématique posée.

Je précise encore qu'à ma connaissance il n'a pas lâché l'affaire et vous pose le lien vers le fil de discussion afin de voir ce qui a été envisagé jusque en fin de message. So, work in progress.

Pour le solde voici la problématique et son contexte (copié-collé du fil de l'autre forum):

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,

Les précisions suivantes ont été apportées suites aux interrogations du malheureux bienveillant qui s'est penché sur la question:

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,

Mon bienfaiteur m'a offert une solution basée sur une fonction personnalisée, laquelle boucle à gauche et à droite en vérifiant si les cellules matchent avec une partie d'une concaténation de la liste des valeurs T qui est contenue dans une variable.

Sa solution me renvoie effectivement la bonne valeur... mais (!):

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

Le recalcul sur la feuille (dans son intégralité) 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. A noter que la cellule accueillant le résultat est, elle, mise à jour dès qu'une valeur est modifiée sur la feuille de saisie (logique me direz-vous).

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...

Ici le lien vers le fil original:

EDIT: je ne peux pas poster de lien pour l'heure, sujet sur Excel-Downloads "Décompte de valeurs précises avec bornage"

Et enfin le fichier schématique préparé à l'occasion de ce dernier :

Et le fichier qui a été créé par mon bienfaiteur:

J'ai bien conscience qu'il n'est pas très friendly de lancer des demandes multiples à travers la toile, cependant au vu de la particularité de la demande je me dis que l'intelligence collective et l'émulation démultipliée sont probablement mes meilleures alliées

Je continue évidement à suivre ce qu'il se passe du côté de la personne qui m'appuie actuellement, et, bonne foi oblige, si solution il y a de l'autre côté, je viendrai clore le présent fil en résolu en y adjoignant la solution retenue.

Dans tous les cas, si il y a des courageux, vos avis sont les bienvenus !

Merci d'avance et au plaisir de vous lire,

Bonjour albuqs

Je vous invite à lire la charte du forum [A LIRE AVANT DE POSTER]
qui vous aidera dans vos demandes et réponses sur ce forum et notamment :

• Ne postez pas la même question sur un autre forum pour éviter de faire perdre bêtement du temps aux membres sur un problème qui peut être déjà résolu sur l'autre forum. L'inverse est également valable, si vous avez déjà posé votre question sur un autre forum, ne créez pas un doublon sur ce forum (à moins d'avoir clôturé le sujet sur l'autre forum).

De plus vu du roman déposé je suis désolé, mais je me vois dans l'obligation de clôturer le sujet

Merci de votre compréhension

Cordialement