Faire apparaître un message dans une cellule en fonction de différents critères

**niiiiiiiiiico** · 14/03/2013, 07h14

Bonjour à tous,

J'ai un fichier avec ligne par ligne des résultats de contrôles pour des clients donnés. Ces clients, en fonction de certaines problématiques peuvent être de sensibilité vert, orange ou rouge. Voici les conditions pour passer d'une classe à une autre :

Un client vert devient orange dès lors qu'un contrôle est non conforme
Un client orange devient rouge dès lors que deux contrôles sur les 5 derniers sont non conformes
Un client rouge redevient orange dès lors que 5 contrôles consécutifs sont conformes
Un client orange redevient vert dès lors que 15 contrôles consécutifs sont conformes.

j'ai donc un critère sur le nom du client, sur sa couleur et sur la conformité de contrôle sur les X derniers

Je pensais m'en sortir avec des formules simples du type si(sommeprod((b51:b55=client1)*(d51:d55=orange)*(t51:t55="non conforme"))>=2;"client passe rouge";"")....le problème c'est que les 5 derniers contrôles de client 1 sont pas forcément les 5 dernières lignes puisque mon onglet traite tous mes clients...et du coup ça coince et je sèche... (je ne veux pas de macros qui trierait mes clients par ordre alpha, je les veux par ordre de contrôle)

Du coup s'il faut passer par du vba je sais pas comment m'y prendre pour la simple et bonne raison que je ne connais absolument pas vba...

En PJ un extrait de mon fichier avec quelques explications

Si quelqu'un pouvait m'aider ça serait vraiment très très sympa !!!

Un grand merci pour m'avoir lu jusque là !!

**vpovpo** · 14/03/2013, 23h52

Votre pb paraît intéressant mais un exposé plus claire et un exemple simplifié seraient bien venues

**niiiiiiiiiico** · 15/03/2013, 07h43

Bonjour,

En fait, j'ai des établissements qui vont contrôler des dépôts de client selon 4 critères (clé de contrôle, lecture de cab, erreur edi et fausse direction dans les pré-tris)

Ces clients sont classés en 3 catégories (vert, orange, rouge) de manière assez empirique par rapport aux problématiques que les établissements connaissent.

La conformité ou non conformité d'un dépôt va se faire sur la base du niveau de qualité acceptable qui est tiré de la norme ISO 2859.1. Jusqu'ici tout est automatique, en fonction de la taille du lot et de la couleur du client, l'outil donne l'échantillon à contrôler et le nombre d'anomalies maximales tolérées. Lorsque le site reporte le nombre d'anomalies constatées, si l'une des 4 est supérieure, le contrôle est non conforme.

La classification de départ (vert, orange et rouge) est empirique et fonction de l'historique constaté. Au fur et à mesure que des contrôles vont se faire, les résultats obtenus vont permettre à des clients orange de passer vert ou rouge, des clients rouge de passer orange, des clients vert de passer orange. Ces changements sont décrits dans la norme iso 2859.1 et doivent se faire selon ces critères :

Un client vert devient orange dès lors qu'un contrôle est non conforme (colonne AE du fichier)
Un client orange devient rouge dès lors que deux contrôles sur les 5 derniers sont non conformes
Un client rouge redevient orange dès lors que 5 contrôles consécutifs sont conformes
Un client orange redevient vert dès lors que 15 contrôles consécutifs sont conformes.

Ces exemples ont été faits à la main dans le fichier du premier post (colonnes AJ - AM). Prenons en un :

En cellule AF125 j'ai écrit à la main "orange > rouge" car le critère des 2 contrôles non conformes sur les 5 derniers a été satisfait. Ces 5 derniers dépôts sont en ligne 120, 121, 123, 124, 125. Du coup je peux pas utiliser des formules simples en prenant les 5 dernières lignes car sur mes 5 dernières lignes, je n'ai pas forcément le même client. Je dois prendre la ligne en cours + les 4 précédentes dont le nom du client est le même. C'est cette subtilité que je n'arrive pas à traduire en formule.

J'espère avoir clarifié le problème

J'ai mis un 2ème exemple en PJ très simplifié pour le coup

Merci de m'avoir lu et m'avoir répondu !!

**goldstar** · 15/03/2013, 10h04

Bonjour,

Ci-joint votre fichier avec en colonne AF un bouton pour mettre à jour la colonne AF.

Pour tester, j'ai egalement ajouté en colonne AN un test de couleur. Vous pouvez bien sûr modifier le code pour qu'il n'alimente plus cette colonne.

**niiiiiiiiiico** · 15/03/2013, 10h46

Bonjour,

Un grand, grand merci !!!

J'ai quelques bugs, par exemple :

En AF 123, 129 j'ai le message vert > orange alors que le client est déjà orange

En AF141 j'ai le message orange > vert mais c'est de ma faute j'ai mal exposé le critère. En fait je passe d'orange à vert quand j'ai 15 dépôts consécutifs conforme en étant orange. Quand je suis rouge, il faut d'abord passer orange (avec 5 dépôts) pour ensuite passer vert (avec 15 dépôts). Dis autrement les lignes 126, 127, 128, 130 et 131 ne doivent pas être comptées (pour le critère orange > vert) car le client est en sensibilité rouge. Elles sont comptées pour passer de rouge à orange. C'est donc en AF148 que le critère est satisfait.

En AF147, je n'ai pas le message orange > rouge mais là aussi j'ai mal exposé... j'ai bien 2 non conformes (lignes 129 et 147) ici sur les 4 derniers glissant (lignes 122, 129, 142, 147). En fait, j'aurais du dire 2 non conformes sur les les 2, 3, 4 ou 5 derniers

Mais pour le reste ça m'a l'air tout bon, merci beaucoup !!!!!

**goldstar** · 15/03/2013, 10h55

Re.

Le fichier correspond au test pour le client Redoute.
Donc jusqu'à la ligne 121 le client est VERT et passe à l'orange en ligne 123.

La ligne 129 correspond au client 3 Suisses.

Pour le reste je vais essayé de regarder. Pas simple le truc

Cordialement

**niiiiiiiiiico** · 15/03/2013, 11h08

Re,

Merci !!

En fait le client démarre orange en ligne 117, il aurait pu démarrer vert ou rouge

**goldstar** · 15/03/2013, 13h24

Une autre petite question:

Comment est déterminée la couleur avec laquelle le client demarre ?

Pour le fichier joint j'ai mis ORANGE pas defaut.

**niiiiiiiiiico** · 15/03/2013, 13h40

Je n'ai pas encore arbitré. Je pourrais partir du vert puisque l'orange commence dès le 1er non conforme ou alors de manière plus empirique en fonction de l'historique de chaque client

**goldstar** · 15/03/2013, 13h46

Je n'avais pas vu la reponse.

J'ai modifié mon message avant votre réponse.

Je remets le fichier.

**niiiiiiiiiico** · 17/03/2013, 14h52

Un grand merci de consacrer tout ce temps pour m'aider !!

Bonjour,

J'ai regardé attentivement, il n'y aurait apparemment qu'un petit bug.

Si je filtre sur 3 suisses et que je mets F122 en vert, je n'ai pas le message vert > orange en AF 122

Merci et bonne fin de week end !!

**goldstar** · 15/04/2013, 08h56

Bonjour,

Désolé de ne pas avoir repondu avant mais j'ai été pas mal occupé.

Par contre il me faudrait le fichier pour que je regarde le bug.

Cordialement

**niiiiiiiiiico** · 19/04/2013, 16h04

Bonjour,

Un exemple, en PJ, le message vert > orange ne fonctionne pas.

Merci !!!

**goldstar** · 21/04/2013, 21h06

Bonjour

Voici le fichier modifié.

Cordialement

**niiiiiiiiiico** · 22/04/2013, 16h01

Bonjour,

Merci beaucoup !!

Malheureusement, il y a un bug. dans le fichier en PJ j'ai un message en AF161 alors qu'il ne devrait pas en avoir (il n'y a pas 5 dépôts conformes consécutifs pour le client verbaudet puisqu'en ligne 159 c'est non conforme

De la même manière, en AF162, je n'ai pas le message vert > orange, alors qu'il devrait apparaître.

Encore merci !!

**goldstar** · 24/04/2013, 15h32

Bonjour,

En espérant que cette version fonctionne.

**niiiiiiiiiico** · 24/04/2013, 17h01

Bonjour,

Malheureusement là aussi ça coince, j'ai rajouté une colonne en mettant manuellement les messages pour comparaison avec les messages automatiques générés par la macro

En tout cas merci d'être si patient !!

**goldstar** · 26/04/2013, 10h15

Bonjour

Encore un fichier

**niiiiiiiiiico** · 26/04/2013, 14h43

Bonjour,

J'ai fait une toute légère modif, mais les premiers essais me semblent concluants ! Un grand grand merci !!

Faire apparaître un message dans une cellule en fonction de différents critères [Toutes versions]

Macros et VBA Excel

Discussions similaires

Partager

Partager