Mise en forme conditionnelle avec plusieurs conditions Résolu/Fermé

Question

Bonjour, 

J'ai écumé les forums et les pages Google en vain, peut-être pourrez vous m'aider

Je travaille pour 3 magasins qui ont des articles en commun
Chaque article a un code état commun (A, B, C) et des codes assortiments propres à chacun (M11 M12 M13 / M21 M22 M23 / M31 M32 M33)

J'ai donc un tableau qui donne cela :
Code article / Code état / Code assortiment magasin 1 (M1X) / magasin 2 (M2X) / magasin 3 (M3X)

Je voudrais faire une (ou plusieurs) mises en forme conditionnelle pour arriver à ce résultat :
 - est en couleur : les articles qui ont code état A ET code magasin M11 et/ou M21 et/ou M31
 - est en couleur : les articles qui ont code état B ET code magasin M12 et/ou M22 et/ou M32
 - est en couleur : les articles qui ont code état C ET code magasin M13 et/ou M23 et/ou M33
 - est en couleur rouge : les articles qui ne correspondent pas à 1 des 3 critères énoncés ci dessous. Exemple : article en code état A et code magasins M11 + M21 + M32

Pour résumer l'article en code état A doit être rattaché à AU MOINS 1c ode magasin de type M (numéro de magasin 1 2 ou 3) et le dernier chiffre 1
B = M(numéro magasin)2
C = M(numéro magasin)3

J'espère avoir été assez clair dans le fonctionnement

J'arrive faire une mise en forme conditionnelle sur un critère (code état = A ou B ou C) mais pas sous condition que les codes magasins soient MX1 sur au moins 1 magasin (ou 2 ou les 3)

Je compte sur vous pour trouver une solution, je suis sur que c'est possible 



Configuration: Windows / Chrome 79.0.3945.130

PapyLuc51 · Answer

Bonjour,

Je viens de voir que tu as modifié ta question, quelles est/sont la/les couleur(s) pour les bonnes combinaisons ?

Cordialement

Stormoker · Answer

Bonjour PapyLuc51,

En soit, peu importe les couleurs, je sais que je pourrais les adapter par la suite à mon gré

J'ai surtout modifier les critères car je m'etais trompé sur sur et/ou

Voici une capture d'écran concrète de mon fichier


J'ai voulu simplifier dans mon premier post pour ne pas se perdre dans le technique mais je détaille : 
 - code état = soit permanent (Code état A) soit non permanent (code état S D I R B)
 - code magasin = soit permanent (A0 SP CP) soit non permanent (AH S0 SH SS CH CS)

1er code en vert = état B (non permanent) + magasins S0 / SS (non permanent) + vide = OK
2ème code en vert = état B (non permanent) + magasins vide = OK
3ème code en vert = état A (permanent) + magasins A0 / CP / SP (permanent) = OK
4ème code en vert = état A (permanent) + magasin 1 A0 (permanent) + magasins 2 et 3 CS / SS (non permanent) = OK (au moins 1 magasin permanent)
5ème code en rouge = état A (permanent) + 3 magasins non permanent (S0 / CS / SS)

J'espère que c'est plus clair ainsi

PapyLuc51 · Answer

Bonsoir,

Après plusieurs tentatives de compréhension, j'avoue que j'en suis toujours au même point, je vais arrêter de ramer sinon je vais attaquer la falaise ;-)

J'ai noté des incohérences dans la photo ; ce qui j'ai encadré correspond la première coloration en vert. Normal ou pas qu'il n'y ai pas de coloration sur ces lignes?




cordialement

Stormoker · Answer

Bonjour,

Je n'avais mis en couleur que quelques exemples via mon outil de capture d'écran, mais je voudrais que le résultat final soit comme ça :

chaque ligne sera colorée soit vert soit rouge, car le but est de pouvoir faire ressortir les lignes qui ont des erreurs

PapyLuc51 · Answer

Bonjour,

Bon je suis désolé mais je laisse tomber ; trop de ET() / OU() à mettre en place. C'est une usine à gaz.
Il va falloir repenser ta méthode et la simplifier au max pour arriver à quelque chose.

En espérant qu'un autre intervenant comprenne le cheminement pour te venir en aide.

Cordialement

Stormoker · Answer

Bonjour,

J'ai peut-être réussi à trouver une alternative, qui reste à peaufiner selon moi


On peut voir sur la capture d'écran ci dessus que j'ai du adapter ma base de données
Code état = permanent (=1) ou non permanent (=0)
Code assortiment magasin (ou niveau de gamme) = permanent (=1) ou non permanent (=0)

J'ai créé un tableau d'équivalence dans une feuille à part (peut-être fais dans un classeur à part avec IMPORTRANGE) puis RECHERCHEV pour appliquer ces équivalences

Si état =1 et somme(niveau gamme magasin) =1 -> alors OK
Si état =0 et somme(niveau gamme magasin) =0 -> alors OK
Sinon -> PB

J'ai donc commencé par créer ma formule conditionnelle comme suit :
=SI(ET(G2=1;SOMME(I2+K2+M2)>=1);"OK";SI(ET(G2=0;SOMME(I2+K2+M2)=0);"OK";"PB"))

=SI(ET(etat=1;SOMME(niveau gamme)>=1; alors "OK"; sinon SI(ET(etat=0;SOMME(niveau gamme)=0; alors "OK"; sinon "PB"))

Puis j'ai retranscrit cette formule conditionnelle en 2 mises en forme conditionnelles :
Sur ma colonne "ARTICLE", MEFC, formule personnalisée
=ET($G2=1;SOMME($I2+$K2+$M2)>=1)
=ET($G2=0;SOMME($I2+$K2+$M2)=0)


Ce que j'aimerais maintenant améliorer :
1/. Ne pas avoir à retranscrire les codes état et niveau de gamme de lettres à chiffres==> est-ce possible ?
2/. Pouvoir combiner les 2 formules personnalisées de la MEFC en une seule
3/. Pouvoir faire une MEFC si le résultat est faux, j'ai réussi à mettre en vert les résultats corrects, mais comment faire pour mettre en évidence les erreurs


L'idée est d'ensuite transmettre ce mode opératoire aux équipes pour l'appliquer de façon systématique. Il faut donc trouver le moyen le plus simple et intuitif possible

PapyLuc51 · Answer

Ça me paraît plus simple 

S'il n'y a que ces deux formules pour constituer le VRAI de la coloration en vert ; à tenter cette combinaison

=OU(ET($G2=1;SOMME($I2+$K2+$M2)>=1);ET($G2=0;SOMME($I2+$K2+$M2)=0))

et pour le rouge à tenter

=OU(ET($G2=1;SOMME($I2+$K2+$M2)>=1)="FAUX";ET($G2=0;SOMME($I2+$K2+$M2)=0)="FAUX")

Je ne peux pas vérifier si j'ai bon n'ayant pas la copie du fichier.

Cordialement

Stormoker · Answer

Alors la formule pour le vert c'est nickel, pour le rouge ca ne marche pas Mais en changeant les critères de ta formule "VERT" j'ai réussi à en déduire la ROUGE =OU(ET($G2=1;SOMME($I2+$K2+$M2)=0);ET($G2=0;SOMME($I2+$K2+$M2)>=1)) Merci beaucoup, c'est déjà mieux de n'avoir plus que 2 MEFC Par contre, en transcrivant les lettres en chiffres j'arrive à mes bons résultats, mais en voulant utiliser uniquement les lettres ça ne fonctionne pas comme voulu J'ai commencé par remplacer les cellules vides par un code fictif (ici appelé NF) où NF = niveau de gamme non permanent Pour info, et pour ceux à qui ça pourrait servir, faire rechercher cellule vide / remplacer par (donnée au choix) ne fonctionne pas sur Google Sheet, merci les forums anglais car il faut rechercher ^\s*$ puis remplir par ce qu'on veut remplacer (ici NF") et cocher respecter la casse + rechercher à l'aide d'expressions régulières Cela me donne donc désormais ce fichier : Formule utilisée lorsque les lettres étaient retranscrites en chiffres : =SI(ET(G2=1;SOMME(I2+K2+M2)>=1);"OK";SI(ET(G2=0;SOMME(I2+K2+M2)=0);"OK";"PB")) Formule utilisée en prenant en compte uniquement les lettres (A0/S0/CP/CS/.../...) : =SI(ET(F2="A";OU(H2="A0";J2="CP";L2="SP"));"OK";SI(ET(F2<>"A";OU(H2<>"A0";J2<>"CP";L2<>"SP"));"OK";"PB")) Pour moi, la deuxième formule est juste car si je la décompose : =SI(ET(code état =A; (et) OU(niveau gamme magasin 1 =permanent;(ou) ngm2 =permanent; (ou) ngm3 =permanent)); (alors) "OK"; (sinon) SI(ET(code état (différent de) <> A; (et) OU(ngm1<> (différent de) permanent; (ou) ngm2<> (différent de) permanent; (ou) ngm3<> (différent de) permanent)) ; (alors) "OK" ; (sinon) "PB") Pour autant, on voit que par exemple le 3e code 2301185 : - état = B (non permanent) - Niveau Gamme Magasin 1 = A0 (permanent) - Niveau Gamme Magasin 2 = CH (non permanent) - Niveau Gamme Magasin 3 = NF (fictif = non permanent) Donc mon résultat devrait être "PB" car état <> A ET ngm1 = A0 En écrivant ce phrase je pense que le soucis vient de l'utilisation de OU car en effet sur ce même exemple, état <> A et au moins un critère suivant est <> d'un NGM permanent (ici NGM 2 et 3 respectivement CH et NF) Existe-t-il une formule qui demande à prendre en compte au moins critère recherché ? Merci encore pour toute l'aide apportée, j'ai déjà énormément avancé en 24h alors que j'étais bloqué dessus depuis plusieurs jours

Stormoker · Answer

Je viens de résumer les différents cas possibles et les résultats qui en découlent


On voit très clairement qu'on a 1 seul cas où si article = A le résultat peut être OK (cas où les niveaux de gammes doivent être tous P permanent)
Et à l'inverse, 1 seul cas où si l'article <> A le résultat peut être PB (cas où les niveaux de gammes sont tous Permanent également)

Est-ce qu'en tournant le problème de cette façon on arriverait pas mieux à trouver une solution et une formule en conséquence ?

PapyLuc51 · Answer

Bonsoir,

Avec ce dernier exemple je suis parti sur du ET(NB.SI())

Voir les formules sur ce petit exemple (changer les références de cellules selon tableau réel)

https://www.cjoint.com/c/JEDqHm1gBzf

Cordialement

Stormoker · Answer

Bonjour Papyluc, Je n'arrive pas à maitriser la formule NB.SI, j'arrive à une erreur d'analyse de formule sur Google Sheets ... Dommage, mais je suis sur qu'on n'est pas loin ! Cette formule =SI(ET($F4="A";OU($H4="A0";$J4="CP";$L4="SP"));"OK";SI(ET($F4<>"A";XOR($H4<>"A0";$J4<>"CP";$L4<>"SP"));"OK";"PB")) fonctionne super bien tant qu'un niveau de gamme soit permanent, si j'ai 2 niveaux de gamme permanent, alors le résultat est faux, mais je ne sais pas pourquoi, il doit manquer un critère ou un paramètre dans cette formule pour la rendre juste, je continue à faire des test

PapyLuc51 · Answer

Bonjour,

Si tu le peux, envoie une copie (sans données confidentielles) de ton tableau en y incluant toutes les possibilités pour l'une et l'autre des deux couleurs. 
C'est mieux que d'essayer de faire quelque chose avec des captures d'écran.

Cordialement

Stormoker · Answer

Bonjour,

Voici le lien du document : https://docs.google.com/spreadsheets/d/16cNXLxFQLNAjHK3KG52YvGzYA5NenN0uu-XTeByGDoQ/edit#gid=1836275648

BDD Brute = l'export tel que je l'extrait
BDD Analyse = nos différents essais
Légende = conversion lettres en chiffres

Stormoker · Answer

Oui les couleurs sont bonnes, elles sont appliquées via 2 mises en forme conditionnelles rouge = erreur / vert = OK

courage, et merci de continuer à chercher avec moi comment y arriver :)

PapyLuc51 · Answer

Bonjour Stormoker

La nuit portant conseil ; je me demande si c'est bien utile de se casser la tête pour la coloration en vert puisque tout ce qui ne provoque pas un problème (rouge) est OK. 

Soit tu colores tes cellules en vert sans MFC soit tu laisse en blanc. 

Personnellement je laisserais sans couleur pour mieux faire ressortir le rouge des PB ; et je me concentrerais sur cette MFC

Dans ton message #2

code magasin = permanent (A0 SP CP)

J'ai bien compris (enfin je crois) que pour le magasin 1 le code permanent est "A0" mais pour les deux autres  codes "SP" est-il réservé qu'au magasin 2 et "CP" qu'au magasin 3 ou peut-il y avoir inversion pour ces 2 magasins ?

Cordialement

Stormoker · Answer

Bonjour Papyluc,

En effet, si cela simplifie de ne faire ressortir que les erreurs ou que les bons paramétrages, ça me convient très bien, si ça peut aider

Concernant les codes, l'état est général aux trois magasins, mais l'assortiment est propre à chacun et sont indépendants entre eux
Magasin 1 : assortiment A0 (permanent) ou S0 / SH (non permanent) et pas d'autres codes
Magasin 2 : assortiment CP (permanent) ou CS / CH / CX (non permanent) et pas d'autres codes
Magasin 3 : assortiment SP (permanent) ou SS / SH (non permanent) et pas d'autres codes
J'ai remplacé les codes assortiments vides par un code fictif NF car Google Sheet ne prend pas en comprend les cellules vides. NF est donc le seul code commun, mais je peux le changer en attribuant un code unique par magasin, ce n'est pas un problème (exemple AF / CF / SF)

PapyLuc51 · Answer

C'est plus facile que je ne le croyais mais avec toutes ces données qui s'ajoutaient je m'y perdais un peu la formule que j'ai installée sur ton tableau Sheets =OU(ET($B2="A";$C2<>"A0";$E2<>"CP";$G2<>"SP");ET($B2<>"A";OU($C2="A0";$E2="CP";$G2="SP"))) Sur ce tableau excel j'ai copier les deux BDD pour avoir la comparaison en visu En espérant que ça te convienne. Si c'est le cas n'oublie pas le clic sur Résolu. Cordialement

Stormoker · Answer

Oui c'est ça !!!! c'est super, merci encore !!!
Par contre je ne trouve aucun bouton "RESOLU" sur la page

Mise en forme conditionnelle avec plusieurs conditions

18 réponses

Discussions similaires